第六章_SQL语言的应用

第六章 SQL语言的应用 章目录 6 1SQL语言概述6 2数据定义6 3数据查询6 4数据操纵 20世纪80年代初 美国国家标准协会 ANSI 开始着手制定SQL标准 最早的ANSI标准于1986年完成 它也被叫做SQL86 SQL标准的出台使SQL作为标准关系数据库语言的地位得到了加强 随后 SQL标准几经修改和完善 其间经历了SQL89 SQL92 一直到最近的SQL99等多个版本 每个新版本都较前面的版本有重大改进 SQL99更是包含了对诸如面向对象 递归 触发器等概念的支持 目前 各主流数据库产品采用的SQL标准是1992年制定的SQL92 6 1SQL语言概述 按照ANSI的规定 SQL被作为关系数据库的标准语言 SQL语句可以用来执行各种各样的操作 目前流行的关系数据库管理系统 如Qracle Sybase SQLServer VisualFoxPro等都采用了SQL语言标准 而且很多数据库都对SQL语句进行了再开发和扩展 SQL语言具有如下特点 1 SQL是一种一体化的语言 尽管设计SQL的最初目的是查询 数据查询也是其最重要的功能之一 但SQL决不仅仅是一个查询工具 它集数据定义 数据查询 数据操纵和数据控制功能于一体 可以独立完成数据库的全部操作 2 SQL语言是一种高度非过程化的语言 它没有必要一步步地告诉计算机 如何 去做 而只需要描述清楚用户要 做什么 SQL语言就可以将要求交给系统 自动完成全部工作 3 SQL语言非常简洁 虽然SQL语言功能很强 但它只有为数不多的9条命令 CREATE DROP ALTER SELECT INSERT UPDATE DELETE GRANT REVOKE 另外SQL的语法也非常简单 它很接近英语自然语言 因此容易学习和掌握 4 SQL语言可以直接以命令方式交互使用 也可以嵌入到程序设计语言中以程序方式使用 现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中 使用起来更方便 VisualFoxPro就是如此 这些使用方式为用户提供了灵活的选择余地 此外 尽管SQL的使用方式不同 但SQL语言的语法基本是一致的 VisualFoxPro在SQL方面支持数据定义 数据查询和数据操纵功能 SQL虽然在各种数据库产品中得到了广泛的支持 但迄今为止 它只是一种建议标准 各种数据库产品中所实现的SQL在语法 功能等方面均略有差异 本章讲述VisualFoxPro中SQL的语法 功能与应用 6 2 1建立表结构 命令格式是 CREATETABLE DBF NAME FREE NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE REFERENCES TAG NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE REFERENCES TAG FROMARRAY 6 2数据定义 参数说明 表名1 要建立的表的名称 FREE 如果当前已经打开一个数据库 这里所建立的新表会自动加入该数据库 除非使用参数 FREE 说明该新表作为一个自由表不加入当前数据库 如果没有打开的数据库 该参数无意义 字段名1 字段名2 所要建立的新表的字段名 在语法格式中 两个字段名之间的语法成分都是对一个字段的属性说明 包括 类型 说明字段类型 宽度及小数位数 字段宽度及小数位数 NULL NOTNULL 该字段是否允许 空值 其默认值为NULL 即允许 空 值 CHECK条件表达式 用来检测字段的值是否有效 这是实行数据库的一种完整性检查 ERROR出错显示信息 当完整性检查有错误 即条件表达式的值为假时的提示信息 应当注意 当为一个表的某个字段建立了实行完整性检测的条件表达式后 在对该数据表输入数据时 系统会自动检测所输入的字段值是否使条件表达式为假 当有一个数据使其为假时 系统自动显示这里所提示的出错信息 DEFAULT表达式 为一个字段指定的默认值 PRIMARYKEY 指定该字段为关键字段 非数据库表不能使用该参数 UNIQUE 指定该字段为一个侯选关键字段 注意 指定为关键或侯选关键的字段都不允许出现重复值 这称为对字段值的唯一性约束 REFERENCES表名 这里指定的表作为新建表的永久性父表 新建表作为子表 TAG标识 父表中的关联字段 若缺省该参数 则默认父表的主索引字段作为关联字段 数组名 用指定数组的值建立输入表 从以上命令格式可以看出 用CREATETABLE命令建立表可以完成用表设计器完成的所有功能 除了建立表的基本功能外 它还包括满足实体完整性的主关键字 主索引 PRIMARYKEY 定义域完整性的CHECK约束及出错提示信息ERROR 定义默认值的DEFAULT等 另外还有描述表之间联系的FOREIGNKEY和REFERENCES等 例利用SQL命令建立学生管理数据库 其中包含3个表 学生表 选课表和课程表 操作步骤如下 1 用CREATE命令建立数据库 CREATEDATABASED 学生管理 2 用CREATE命令建立学生表 CREATETABLE学生 学号C 5 PRIMARYKEY 姓名C 8 入学成绩N 5 1 CHECK 入学成绩 0 ERROR 成绩应该大于0 3 建立课程表 CREATETABLE课程 课程号C 5 PRIMARYKEY 课程名C 20 学分N 1 4 建立选课表 CREATETABLE选课 学号C 5 课程号C 5 成绩ICHECK 成绩 0AND成绩 100 ERROR 成绩值的范围0 100 DEFAULT60 FOREIGNKEY学号TAG学号REFERENCES学生 FOREIGNKEY课程号TAG课程号REFERENCES课程 利用SQL命令建立数据库 注意 用SQLCREATE命令新建的表自动在最小可用工作区打开 并可以通过别名引用 新表的打开方式为独占方式 忽略SETEXCLUSIVE的当前设置 如果建立自由表 当前没有打开的数据库或使用了FREE 则很多选项在命令中不能使用 如NAME CHECK DEFAULT FOREIGNKEY PRIMARYKEY和REFERENCES等 6 2 2删除表删除表的SQL命令是 DROPTABLE DROPTABLE命令直接从磁盘上删除所指定的表文件 如果指定的表文件是数据库中的表并且相应的数据库是当前数据库 则从数据库中删除了表 否则虽然从磁盘上删除了表文件 但是记录在数据库文件中的信息却没有删除 此后会出现错误提示 所以要删除数据库中的表时 最好应使数据库是当前打开的数据库 在数据库中进行操作 6 2 3修改表结构 修改表结构的命令是ALTERTABLE 该命令有3种格式 格式1 ALTERTABLE ADD ALTER COLUMN NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE REFERENCES TAG 功能 添加或修改已有的字段例为课程表增加一个整数类型的学时字段 ALTERTABLE课程 ADD学时ICHECK 学时 16 ERROR 学时应该大于16 格式2 ALTERTABLE ALTER COLUMN NULL NOTNULL SETDEFAULT SETCHECK ERROR DROPDEFAULT DROPCHECK 功能 定义 修改和删除有效性规则以及默认值定义 例删除学时字段的有效性规则 ALTERTABLE课程ALTER学时DROPCHECK 格式3 ALTERTABLE DROP COLUMN SETCHECK ERROR DROPCHECK ADDPRIMARYKEYTAG FOR DROPPRIMARYKEY ADDUNIQUE TAG FOR DROPUNIQUETAG ADDFOREIGNKEYTAG FOR REFERENCES TAG DROPFOREIGNKEYTAG SAVE RENAMECOLUMNTO 该格式的命令可以删除指定字段 DROP COLUMN 修改字段名 RENAMECOLUMN 修改指定表的完整性规则 包括主索引 外关键字 候选索引及表的合法值限定的添加与删除 例删除课程表中的学时字段 ALTERTABLE课程DROPCOLUMN学时ALTERTABLE课程renaCOLUMN学时to总学时 VisualFoxPro的SQLSELECT命令的语法格式是 SELECT ALL DISTINCT AS AS FROM AS INNER LEFT OUTER RIGHT OUTER FULL OUTER JOIN AS ON INTO TOFILE ADDITIVE TOPRINTER PROMPT TOSCREEN 6 3数据查询 PREFERENCE NOCONSOLE PLAIN NOWAIT WHERE AND AND OR AND OR GROUPBY HAVING UNION ALL SELECT命令 ORDERBY ASC DESC ASC DESC 6 3 1基本查询 所谓基本查询是指无条件查询 其格式是 SELECT ALL DISTINCT AS AS FROM 别名1 别名2 其中ALL表示输出所有记录 包括重复记录 DISTINCT表示输出无重复结果的记录 当选择多个数据库表中的字段时 可使用别名来区分不同的表 显示列名的作用是在输出结果中 如果不希望使用字段名 可以根据要求设置一个名称 选项可以是字段名 表达式或函数 表名代表要查询的表 例列出学生名单 OPENDATABASED 学生管理 SELECT FROM学生 命令中的 表示输出所有字段 数据来源是学生表 所有内容以浏览方式显示 例列出所有学生姓名 去掉重名 SELECTDISTINCT姓名AS学生名单FROM学生 SELECT命令中的选项 不仅可以是字段名 还可以是表达式 也可以是一些函数 SELECT命令可操纵的函数很多 例将所有的学生入学成绩四舍五入 只显示学号 姓名和入学成绩 SELECT学号 姓名 ROUND 入学成绩 0 AS 入学成绩 FROM学生 注意 这个结果不影响数据库表中的结果 只是在输出时通过函数计算输出 例求出所有学生的入学成绩平均分 SELECTAVG 入学成绩 AS 入学成绩平均分 FROM学生 由以上两例可见 直接使用VisualFoxPro提供的各种SQL函数在输出时进行计算 便可得到相应的输出结果 6 3 2带条件查询 WHERE是条件语句关键字 是可选项 其格式是 WHERE 其中条件表达式可以是单表的条件表达式 也可以是多表之间的条件表达式 表达式用的比较符为 等于 不等于 精确等于 大于 大于等于 小于 小于等于 例求出湖南学生入学成绩平均分 SELECT籍贯 AVG 入学成绩 AS 入学成绩平均分 FROM学生WHERE籍贯 湖南 条件表达式是指查询的结果集合应满足的条件 如果某行条件为真就包括该行记录 下面是可用于条件表达式中几个特殊运算符的意义和使用方法 ALL ANY BETWEENANDEXISTS IN或者IN LIKESOME 例列出非湖南籍的学生名单 SELECT学号 姓名 籍贯FROM学生WHERE籍贯 湖南 以上命令的功能等同于 SELECT学号 姓名 籍贯FROM学生WHERE籍贯 湖南 或SELECT学号 姓名 籍贯FROM学生WHERENOT 籍贯 湖南 例列出江苏籍和贵州籍的学生名单 SELECT学号 姓名 籍贯FROM学生WHERE籍贯IN 江苏 贵州 以上命令的功能等同于 SELECT学号 姓名 籍贯FROM学生WHERE籍贯 江苏 OR籍贯 贵州 例列出入学成绩在560分到650分之间的学生名单 SELECT学号 姓名 入学成绩FROM学生WHERE入学成绩BETWEEN560AND650 在以上命令的功能等同于 SELECT学号 姓名 专业FROM学生WHERE入学成绩 560AND入学成绩 650 例列出所有的姓赵的学生名单 SELECT学号 姓名FROM学生WHERE姓名LIKE 赵 以上命令的功能等同于 SELECT学号 姓名 专业FROM学生WHERE姓名 赵 例列出所有成绩为空值的学生学号和课程号 SELECT学号 课程号FROM选课WHERE成绩ISNULL 在以上命令中 使用了运算符ISNULL 该运算符是测试字段值是否为空值 在查询时用 字段名IS NOT NULL 的形式 而不能写成 字段名 NULL 或 字段名 NULL 6 3 3嵌套查询 有时候一个SELECT命令无法完成查询任务 需要一个子SELECT的结果作为条件语句的条件 即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令 这种查询称为嵌套查询 通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询 把嵌入子查询多于一层的查询称为多层嵌套查询 VisualFoxPro只支持单层嵌套查询 1 返回单值的子查询 例列出选修 数据库原理 的所有学生的学号 SELECT学号FROM选课WHERE课程号 SELECT课程号FROM课程WHERE课程名 数据库原理 上述SQL语句执行的是两个过程 首先在课程表中找出 数据库原理 的课程号 比如 01001 然后再在选课表中找出课程号等于 01101 的记录 列出这些记录的学号 2 返回一组值的子查询 若某个子查询返回值不止一个 则必须指明在WHERE子句中应怎样使用这些返回值 通常使用条件ANY 或SOME ALL和IN 1 ANY运算符的用法 例列出选修 01101 课的学生中期末成绩比选修 01102 的最低成绩高的学生的学号和成绩 SELECT学号 成绩FROM选课WHERE课程号 01101 AND成绩 ANY SELECT成绩FROM选课WHERE课程号 01102 该查询必须做两件事 先找出选修 01102 课的所有学生的期末成绩 比如说结果为87和72 然后在选修 01101 课的学生中选出其成绩高于选修 01102 课的任何一个学生的成绩 即高于72分 的那些学生 2 ALL运算符的用法 例列出选修 01101 课的学生 这些学生的成绩比选修 01102 课的最高成绩还要高的学生的学号和成绩 SELECT学号 成绩FROM选课WHERE课程号 01102 AND成绩 ALL SELECT成绩FROM选课WHERE课程号 01102 该查询的含义是 先找出选修 01102 课的所有学生的成绩 比如说结果为87和72 然后再在选修 01102 课的学生中选出其成绩中高于选修 01102 课的所有成绩 即高于87分 的那些学生 3 IN运算符的用法 例列出选修 数据库原理 或 软件工程 的所有学生的学号 SELECT学号FROM选课WHERE课程号IN SELECT课程号FROM课程WHERE课程名 数据库原理 OR课程名 软件工程 IN是属于的意思 等价于 ANY 即等于子查询中任何一个值 6 3 4多表查询 在一个表中进行查询 一般说来是比较简单的 而在多表之间查询就比较复杂 必须处理表和表间的联接关系 1 等值联接 等值联接是按对应字段的共同值将一个表中的记录与另一个表中的记录相联接 例输出所有学生的成绩单 要求给出学号 姓名 课程号 课程名和成绩 SELECTa 学号 a 姓名 b 课程号 c 课程名 b 成绩 FROM学生a 选课b 课程c WHEREa 学号 b 学号ANDb 课程号 c 课程号 学生成绩查询结果 例列出男生的选课情况 要求列出学号 姓名 课程号 课程名 授课教师和学分数 SELECTa 学号 a 姓名AS学生姓名 b 课程号 c 课程名 e 姓名AS教师姓名 c 学分 FROM学生a 选课b 课程c 授课d 教师e WHEREa 学号 b 学号ANDb 课程号 c 课程号ANDc 课程号 d 课程号 ANDd 教师号 e 教师号ANDa 性别 男 男生选课情况查询结果 列出至少选修 01101 课和 01102 课学生学号 Selea 学号from选课a 选课b Whera 学号 b 学号anda 课程号 01101 andb 课程号 01102 2 非等值联接 例列出选修 01102 课的学生中 期末成绩大于学号为 200109 的学生该门课成绩的那些的学号及其成绩 SELECTa 学号 a 成绩FROM选课a 选课b WHEREa 成绩 b 成绩ANDa 课程号 b 课程号ANDb 课程号 01102 ANDb 学号 200109 6 3 5联接查询 VisualFoxPro提供的SELECT命令 在FROM子句中提供一种称之为联接的子句 联接分为内部联接和外部联接 外部联接又分为左外联接 右外联接和全外联接 1 内部联接 InnerJoin 所谓内部联接是指包括符合条件的每个表格中的记录 也就是说是所有满足联接条件的记录都包含在查询结果中 1 内部连接 InnerJoin 只有满足连接条件的记录包含在结果中 如图所示 例列出少数民族学生的学号 课程号及成绩 SELECTa 学号 b 课程号 b 成绩FROM学生a 选课b WHEREa 学号 b 学号ANDa 少数民族否 如果采用内部联接方式 则命令 SELECTa 学号 b 课程号 b 成绩FROM学生aINNERJOIN选课b ONa 学号 b 学号WHEREa 少数民族否 所得到的结果完全相同 2 外部联接 OuterJoin 1 左外联接 也叫左联接 LeftOuterJoin 其系统执行过程是左表的某条记录与右表的所有记录依次比较 若有满足联接条件的 则产生一个真实值记录 若都不满足 则产生一个含有NULL值的记录 接着 左表的下一记录与右表的所有记录依次比较字段值 重复上述过程 直到左表所有记录都比较完为止 联接结果的记录个数与左表的记录个数一致 左连接 LeftOuterJoin 左表中某记录与右表所有记录比较字段值 若有满足 连接条件 的 则产生一个真实记录 若都不满足 则产生一个含 Null 值的记录 直至右表所有记录比较完 如图所示 2 右外联接 也叫右联接 RightOuterJoin 其系统执行过程是右表的某条记录与左表的所有记录依次比较 若有满足联接条件的 则产生一个真实值记录 若都不满足 则产生一个含有NULL值的记录 接着 右表的下一记录与左表的所有记录依次比较字段值 重复上述过程 直到左表所有记录都比较完为止 联接结果的记录个数与右表的记录个数一致 右连接 RightOuterJoin 右表中某记录与左表所有记录比较字段值 若有满足 连接条件 的 则产生一个真实记录 若都不满足 则产生一个含 Null 值的记录 直至左表所有记录比较完 如图所示 3 全外联接 也叫完全联接 FullJoin 其系统执行过程是先按右联接比较字段值 然后按左联接比较字段值 重复记录不记入查询结果中 6 3 6查询结果处理 1 排序输出 ORDER SELECT的查询结果是按查询过程中的自然顺序给出的 因此查询结果通常无序 如果希望查询结果有序输出 需要下面的子句配合 ORDERBY ASC DESC ASC DESC 其中排序选项可以是字段名 也可以是数字 字段名必须是主SELECT子句的选项 当然是FROM中的字段 数字是表的列序号 第1列为1 ASC指定的排序项A按升序排列 DESC指定的排序项按降序排列 例按性别顺序列出学生的学号 姓名 性别 课程名及成绩 性别相同的再先按课程后按成绩由高到低排序 SELECTa 学号 a 姓名 a 性别 c 课程名 b 成绩FROM学生a 选课b 课程c WHEREa 学号 b 学号ANDb 课程号 c 课程号ORDERBYa 性别 c 课程名 b 成绩DESC 查询结果的排序输出 2 重定向输出 INTO INTO是可选项 表示查询结果可以重定输出方向 其格式如下 INTO TOFILE ADDITIVE TOPRINTER 其中 为有如下3种形式 ARRAY 将查询结果存到指定数组名的内存变量数组中 CURSOR 将输出结果存到一个临时表 游标 这个表的操作与其他表一样 不同的是 一旦被关闭就被删除 DBF TABLE 将结果存到一个表 如果该表已经打开 则系统自动关闭它 如果SETSAFETYOFF 则重新打开它不提示 如果没有指定后缀 则默认为 dbf 在SELECT命令执行完后 该表为打开状态 TOFILE ADDITIVE 将结果输出到指定文本文件 ADDITIVE表示将结果添加到文件后面 在输出的文件中 系统可以自动处理重名的问题 如不同文件同字段名用文件名来区分 表达式用EXP A EXP B等来自动命名 SELECT函数用函数名来辅助命名 TOPRINTER 将结果送打印机输出 例将例6 25的查询结果保存到test1 txt文本文件中 SELECTa 学号 a 姓名 a 性别 c 课程名 b 成绩FROM学生a 选课b 课程c WHEREa 学号 b 学号ANDb 课程号 c 课程号ORDERBYa 性别 c 课程名 b 成绩DESCTOFILEtest1 test1 txt文件中的内容参见图6 5 但文件格式为文本文件 例查询学生所学课程和成绩 输出学号 姓名 课程名和成绩 并将查询结果存入testtable表中 SELECTa 学号 a 姓名 b 课程名 b 成绩FROM学生a 成绩b WHEREa 学号 b 学号INTOCURSORtest SELECTa 学号 a 姓名 b 课程名 a 成绩FROMtesta 课程b WHEREa 课程号 b 课程号INTOTABLEtesttableORDERBYa 学号 3 输出合并 UNION 输出合并是指将两个查询结果进行集合并操作 其子句格式是 UNION ALL 其中ALL表示结果全部合并 若没有ALL 则重复的记录将被自动取掉 合并的规则是 1 不能合并子查询的结果 2 两个SELECT命令必须输出同样的列数 3 两个表各相应列出的数据类型必须相同 数字和字符不能合并 4 仅最后一个中可以用ORDERBY子句 且排序选项必须用数字说明 例列出选修 01101 或 01102 课程的所有学生的学号 SELECT学号FROM选课WHERE课程号 01101 UNIONSELECT学号FROM 选课WHERE课程号 01102 4 分组统计 GROUP 与筛选 HAVING 查询结果可以分组 其格式是 GROUPBY 其中可以是字段名 SQL函数表达式 也可以是列序号 最左边为1 筛选条件格式是 HAVING HAVING子句与WHERE功能一样 只不过是与GROUPBY子句连用 用来指定每一分组内应满足的条件 而WHERE子句作用于表或视图 例分别统计男女人数 SELECT性别 COUNT 性别 FROM学生GROUPBY性别 例分别统计男女中少数民族学生人数 SELECT性别 COUNT 性别 FROM学生GROUPBY性别WHERE少数民族否 如果把命令写成如下形式 统计的结果就是错误的 SELECT性别 COUNT 性别 FROM学生 GROUPBY性别HAVING少数民族否 例列出成绩平均分大于80分的课程号Sele课程号 avg 成绩 from选课groupby课程号 Havingavg 成绩 80 6 4 1插入记录 VisualFoxPro支持两种SQL插入命令 其格式是 格式1 INSERTINTO 字段名1 VALUES 该命令在指定的表尾添加一条新记录 其值为VALUE