第六章 SQL查询语言的使用

1、第六章 SQL查询语言的使用6.1 SQL基础知识n定义：定义：SQL是结构化查询语言的缩写，是关系是结构化查询语言的缩写，是关系数据库的标准数据语言。数据库的标准数据语言。nSQL语言的核心是查询，同时它还拥有数据定语言的核心是查询，同时它还拥有数据定义和数据修改功能。义和数据修改功能。nSQL的使用方式：的使用方式：q以命令方式交互使用，又称为以命令方式交互使用，又称为交互式交互式SQL；q嵌入程序设计语言中以程序方式使用，又称为嵌入程序设计语言中以程序方式使用，又称为嵌入嵌入式式SQL。6.2 SQL的数据定义功能n创建表q格式：格式：CREATE TABLE CREATE TABLE

2、FREE ( FREE ( 1 (,小数小数) NULL|NOT NULL ) NULL|NOT NULL CHECK CHECK ERROR ERROR DEFAULT DEFAULT PRIMARY KEY|UNIQUE PRIMARY KEY|UNIQUE ,2 q说明：说明：n此命令中可以使用的数据类型有此命令中可以使用的数据类型有C(n)C(n)、N(nN(n，d)d)、D D、T T、L L、M M、G G等。等。nPRIMARY KEYPRIMARY KEY：用于定义主索引，：用于定义主索引，只能用于数只能用于数据库表；据库表；nCHECKERRORCHECKERROR：用于定义

3、有效性规则和出错：用于定义有效性规则和出错提示信息，提示信息，只能用于数据库表；只能用于数据库表；nDEFAULTDEFAULT：用于定义默认值，：用于定义默认值，只能用于数据库只能用于数据库表表；nFREEFREE：用于指明所建表是一个自由表，不添：用于指明所建表是一个自由表，不添加到当前数据库中加到当前数据库中nNULLNULL或或NOT NULLNOT NULL；用于说明属性允许或不允；用于说明属性允许或不允许为空值；许为空值；nUNIQUEUNIQUE：用于建立候选索引，但不是惟一索：用于建立候选索引，但不是惟一索引。引。q实例实例1 1：用此命令建立一自由表：用此命令建立一自由表“图

4、书图书”表。表。图书（总编号图书（总编号 C(6)C(6)，分类号，分类号 C(8)C(8)，书名，书名 C(16)C(16)，作者，作者 C(6)C(6)，出版单位，出版单位 C(20)C(20)，单价，单价 N(6,2)N(6,2)），其中总编号是候选索引。），其中总编号是候选索引。q解答：解答：crea table crea table 图书图书 free (free (总编号总编号 C(6) C(6) UNIQUE,UNIQUE,分类号分类号 C(8),C(8),书名书名 C(16),C(16),作者作者 C(6),C(6),出版单位出版单位 C(20),C(20),单价单价 N(6N

5、(6，2)2)n实例实例2：用此命令在图书管理数据库下建立：用此命令在图书管理数据库下建立“新新图书图书”表。表。 新新图书（总编号图书（总编号 C(6)，书名，书名 C(16)，作者，作者 C(6)，出版单位出版单位 C(20)，出版日期，出版日期 D，单价，单价 N(6,2)），），其中总编号是主索引，为单价字段添加有效性规其中总编号是主索引，为单价字段添加有效性规则，并且单价字段的值大于则，并且单价字段的值大于0，输入出错，给出，输入出错，给出错提示：错提示：“单价是正数！单价是正数！”。解答：解答：crea table crea table 新图书新图书 ( (总编号总编号 C(6)

6、C(6) PRIMARY KEY, PRIMARY KEY, 书名书名 C(16),C(16),作者作者 C(6),C(6),出版单出版单位位 C(20),C(20),单价单价 N(6N(6，2) CHECK 2) CHECK 单价单价0 ERROR 0 ERROR “单价是正数单价是正数!”)!”)n修改表的结构修改表的结构1、增加字段增加字段格式：ALTER TABLE ADD (宽度,小数位) 功能：在表中增加新字段，并定义字段属性。实例1：为学生表增加年龄字段。ALTER TABLE 学生表 ADD 年龄 N(2)实例2：为人才库的ZYTC.DBF增加一个数值型的年龄字段N(2)，且加

7、上有效性检查和错误信息提示ALTER TABLE ZYTC ADD 年龄 N(2) CHECK 年龄=0 ERROR “年龄应大于等于0!” 2、修改字段修改字段格式：ALTER TABLE ALTER (宽度,小数位)功能：修改表中字段的属性。例：修改学生表中年龄字段的属性为C(3)。ALTER TABLE 学生表 ALTER 年龄 C(3)3、删除字段删除字段ALTER TABLE DROP (宽度,小数位)例：删除学生表中年龄字段。ALTER TABLE 学生表 DROP 年龄4、修改字段名修改字段名ALTER TABLE RENAME TO 例：将学生表中学号字段名改为学生学号。ALT

8、ER TABLE 学生表 RENAME 学号 TO 学生证号n删除表删除表格式：DROP TABLE 表名功能：直接从磁盘上删除由表名指定的表文件。q说明：n表名所指定的表是数据库中的表，并且该数据库是当前数据库，则既从磁盘删除表文件，也从数据库中删除该表。n用此命令删除数据库中的表时，应先打开数据库，然后进行删除。若该表所属的数据库不是当前库，使用此命令删除表时，只从磁盘上删除了表文件，但记录在数据库文件中的信息却没有删除，以后会出现错误提示。n若删除自由表，则直接删除。q实例：删除人才库中的ZYTC表。DROP TABLE ZYTC插入数据（插入数据（INSERT）q格式格式1 1：INS

9、ERT INTO INSERT INTO (1， ) VALUES() VALUES(1， )2)q功能：向指定表中插入记录。功能：向指定表中插入记录。q说明：说明：n要求在命令中给定要插入的具体记录值；要求在命令中给定要插入的具体记录值；n属性名要由一对圆括号括起来；属性名要由一对圆括号括起来；n在在VALUESVALUES后的一对圆括号内给出具体的记录值；后的一对圆括号内给出具体的记录值；n在在VALUESVALUES后列出的具体值要与表名后指定的字段后列出的具体值要与表名后指定的字段名一一对应。名一一对应。q实例：向实例：向CJDACJDA表中插入记录。表中插入记录。INSERT INT

10、O CJDA(INSERT INTO CJDA(编号编号, ,成果名称成果名称, ,成果类别成果类别, ,成果出处成果出处) VALUES(sy02030,Vf) VALUES(sy02030,Vf谈谈,论文论文,计算机应用计算机应用) )q格式格式2 2：INSERT INTO INSERT INTO FROM ARRAY FROM ARRAY |FROM MEMVAR|FROM MEMVARq说明：说明：nFROM ARRAY FROM ARRAY 从所指定的数组中插入从所指定的数组中插入记录的值；记录的值；nFROM FROM MEMVARMEMVAR根据与字段名同名的内存变量来根据与字

11、段名同名的内存变量来插入记录值；如果同名的内存变量不存在插入记录值；如果同名的内存变量不存在, ,那那么相应的字段值为默认值或空值。么相应的字段值为默认值或空值。更新数据（更新数据（UPDATE）q格式：格式：UPDATE UPDATE SET SET =1， = WHERE WHERE q功能：更新指定数据表中满足条件的记录的值。功能：更新指定数据表中满足条件的记录的值。q说明：说明：n一次可更新表中一个或多个字段的值；一次可更新表中一个或多个字段的值；n需更新的字段在需更新的字段在SETSET短语后列出；短语后列出；n用用WHEREWHERE短语限定对满足条件的记录进行更新；短语限定对满足

12、条件的记录进行更新；若无此短语，则默认对表中的全部记录进行若无此短语，则默认对表中的全部记录进行更新。更新。6.3 SQL的数据查询功能n格式：格式：略略。n基本形式：基本形式：SELECTFROMWHERESELECTFROMWHERE，多个查询，多个查询块可嵌套使用。块可嵌套使用。n注意：必须掌握的短语的含义：注意：必须掌握的短语的含义：qSELECTSELECT说明要查询的数据；说明要查询的数据；qFROMFROM说明要查询的数据来自哪个或哪些表，即说明要查询的数据来自哪个或哪些表，即可以对单个或多个表进行查询；可以对单个或多个表进行查询；qWHEREWHERE说明查询的条件，即选择记录

13、的条件；说明查询的条件，即选择记录的条件；qGROUP BYGROUP BY用于对查询结果进行分组；用于对查询结果进行分组；qHAVINGHAVING短语必须接在短语必须接在GROUP BYGROUP BY的后面使用，用的后面使用，用来限定分组必须满足的条件；来限定分组必须满足的条件；qORDER BYORDER BY短语用来对最终的查询结果进行排序。短语用来对最终的查询结果进行排序。qDISTINCT DISTINCT 消除重复的记录。消除重复的记录。qAS AS 给查询结果的列名重新命名。给查询结果的列名重新命名。简单查询n基于基于单个表单个表的查询，得到的查询结果数据来自的查询，得到的查

14、询结果数据来自一个一个表。表。格式：格式：SELECT DISTINCT SELECT DISTINCT FROM FROM WHERE WHERE n例例1 1：从学生表中查询学号，姓名，出生日期。：从学生表中查询学号，姓名，出生日期。 SELE SELE 学号，姓名，出生日期学号，姓名，出生日期 FROM FROM 学生表学生表n例例2 2：从学生表中查询录取分数大于：从学生表中查询录取分数大于600600分的学分的学生的学号，姓名。生的学号，姓名。 SELE SELE 学号，姓名学号，姓名 FROM FROM 学生表学生表 WHERE WHERE 录取录取分数分数600600例3：从选课

15、表中查询学号,课程号,成绩。SELE * FROM 选课表n去掉学号的重复值SELE DISTINCT 学号 FROM 选课表例4：从学生表中查询学号，姓名，录取分数+20。SELE 学号，姓名，录取分数+20 AS 新分数 FROM 学生表n确定范围确定范围例1：从学生表查询分数在600到610的学生。SELE * FROM 学生表 WHERE 录取分数 BETWEEN 600 AND 610例2：从学生表查询分数不在600到610的学生。SELE * FROM 学生表 WHERE 录取分数 NOT BETWEEN 600 AND 610n确定集合确定集合例1：从学生表查询分数为610，61

16、2，618，595分的学生。SELE * FROM 学生表 WHERE 录取分数 IN （610，612，618，595）例2：从学生表查询分数不为610，612，618，595分的学生。SELE * FROM 学生表 WHERE 录取分数 NOT IN （610，612，618，595）q模糊查询模糊查询:%:%表示任意长度的字符串；表示任意长度的字符串； _ _表示任意一个字符或一个汉字。表示任意一个字符或一个汉字。q查询所有姓张的学生的学号，姓名。查询所有姓张的学生的学号，姓名。SELE SELE 学号学号, ,姓名姓名 FROM FROM 学生表学生表 WHERE WHERE 姓名姓名

17、 LIKE LIKE 张张%q查询编号的第三个字符为查询编号的第三个字符为8 8的学生的学号的学生的学号, ,姓名。姓名。SELE SELE 学号学号, ,姓名姓名 FROM FROM 学生表学生表 WHERE WHERE 学号学号 LIKE LIKE _8%_8%q查询姓名的第二个字是小的人的学号，姓名。查询姓名的第二个字是小的人的学号，姓名。SELE SELE 学号学号, ,姓名姓名 FROM FROM 学生表学生表 WHERE WHERE 姓名姓名 LIKE LIKE _小小%统计查询统计查询说明：统计查询是通过计算函数实现的，在使说明：统计查询是通过计算函数实现的，在使用时计算函数被放

18、在用时计算函数被放在SELECTSELECT短语后。短语后。SQLSQL提供提供的的计算函数如下：计算函数如下：例1：查询学生表中最高录取分数，最低录取分数，平均分数。SELE MAX(录取分数) AS 最高分, MIN(录取分数) AS 最低分, AVG(录取分数) AS 平均分 FROM 学生表例2：统计女生的人数。SELE COUNT(*) AS 女生人数 FROM 学生表WHERE 性别=女例3：统计选课表中的学生人数。 SELE COUNT(DISTINCT 学号) AS 学生人数 FROM 选课表例4：统计录取分数超过600分的学生人数。SELE COUNT(*) AS 分数超过6

19、00的学生人数 FROM 学生表 WHERE 录取分数600分组查询分组查询q说明：n使用GROUP BY短语可以实现分组查询；n查询过程：首先按GROUP BY后面的字段进行分组，然后对每个分组进行统计查询。GROUP BY短语通常跟在WHERE短语后，若无WHERE，则跟在FROM之后。而HAVING 跟在GROUP BY之后。 n若一个查询中既有WHERE又有HAVING，则先用WHERE限定表中的记录，对满足条件的记录进行分组，然后用HAVING限定分组，满足条件的分组才作为查询的结果。例1：从选课表中查询选修各门课程的学生人数。SELE 课程号,COUNT(学号) AS 选修该课程人

20、数 FROM 选课表 GROUP BY 课程号例2：从选课表中查询选修同一门课程超过4人的课程号及学生人数。SELE 课程号,COUNT(学号) AS 选修该课程人数 FROM 选课表 GROUP BY 课程号 HAVING COUNT(*)4等价于：SELE 课程号,COUNT(学号) AS 选修该课程人数 FROM 选课表 GROUP BY 课程号 HAVING 选修该课程人数4例3：在学生表中分别查询男女生的最高录取分数及平均录取分数。SELECT 性别,MAX(录取分数) AS 最高分数,AVG(录取分数) AS 平均分数 FROM 学生表 GROUP BY 性别例4：在学生表中查询出

21、男女团员的最高录取分数及最低录取分数和人数。SELECT 性别,团员,MAX(录取分数) AS 最高分数,MIN(录取分数) AS 最低分数,COUNT(*) AS 人数 FROM 学生表 WHERE 团员 GROUP BY 性别 例5：从选课表中查询课程号为c110，c120，c130的最高成绩。SELE 课程号,MAX(成绩) AS 最高分 FROM 选课表 GROUP BY 课程号 HAVING 课程号 IN (c110,c120,c130)等价于:SELE 课程号,MAX(成绩) AS 最高分 FROM 选课表 WHERE 课程号 IN (c110,c120,c130) GROUP B

22、Y 课程号例6：在学生表中分别统计男女生的人数。SELE 性别,COUNT(*) AS 人数 FROM 学生表 GROUP BY 性别n查询的排序查询的排序格式：ORDER BY 字段名1ASC|DESC，字段名2ASC|DESC例1：在学生表中查询所有男生的记录，并按录取分数的降序排。SELE * FROM 学生表 WHERE 性别=男 ORDER BY 录取分数 DESC例2:在选课表中统计各门课程的平均成绩,查询的结果先按平均成绩的降序排,成绩相同的再按课程号的升序排。SELE 学号,课程号,AVG(成绩) AS 平均成绩 FROM 选课表 GROUP BY 课程号 ORDER BY 平

23、均成绩 DESC,课程号n查询前面部分记录查询前面部分记录TOP N PERCENT例1：从学生表中查询录取分数最高的2个学生的记录。SELE * TOP 2 FROM 学生表 ORDER BY 录取分数 DESC例2：从学生表中查询出录取分数最高的前30%的学生记录。SELE * TOP 30 PERCENT FROM 学生表 ORDER BY 录取分数 DESC联接查询：联接查询：是一类基于多个表的查询，查询的结果数据出自多个表，即FROM之后有多个表。注意：nSELECT之后的多个字段可以来自不同的表，如果不同的表中含有相同的字段名，就必须用”别名.字段名”引用。n两个表通过相同的字段名

24、进行联接，两个表中同名字段的值相等是联接条件，将其置于WHERE短语后，只有满足条件时才出现在查询结果中。例1：从授课表和课程表中查询各个教师担任课程的课程号及课程名。SELE 教师号,授课表.课程号,课程名 FROM 授课表,课程表 WHERE 授课表.课程号=课程表.课程号例2：对上例用别名来简化。SELE 教师号,AA.课程号,课程名 FROM 授课表 AA,课程表 BB WHERE AA.课程号=BB.课程号注意：在定义别名时，不能加定界符。例3：查询各个教师的教师号，姓名，所上课程号，课程名。SELE AA.教师号,姓名,BB.课程号,课程名 FROM 教师表 AA,授课表 BB,课

25、程表 CC WHERE AA.教师号=BB.教师号 AND BB.课程号=CC.课程号例4：查询刘雨的学号，姓名，选修的课程的课程号及成绩。SELE 学生表.学号,姓名,课程号,成绩 FROM 学生表,选课表 WHERE 学生表.学号=选课表.学号 AND 姓名=刘雨自联接查询：自联接查询：将同一个表与自身进行联接，称为自联接。注意：在实现自联接操作时，必须为表起别名。注意：在实现自联接操作时，必须为表起别名。例1：查询录取分数大于“王小平”的录取分数的学生的学号、姓名和录取分数。SELE A.学号,A.姓名,A.录取分数 FROM 学生表 A,学生表 B WHERE A.录取分数B.录取分数

26、 AND B.姓名=王小平例2：查询与王小平性别相同的学生的学号，姓名，性别。SELE A.学号,A.姓名,A.性别 FROM 学生表 A,学生表 B WHERE A.性别=B.性别 AND B.姓名=王小平超联接查询：超联接查询：基于多个表的查询。超联接有：左联接、右联接、内联接和全联接。n左联接：在结果表中包含第一个表中满足条件的所有记录，如果第二个表中有满足联接条件的记录，则返回相应的值，否则返回空值。n右联接：在结果表中包含第二个表中满足条件的所有记录，如果第一个表中有满足联接条件的记录，则返回相应的值，否则返回空值。n内连接n全连接q相应短语：nINNER JOIN=JOIN，是内联

27、接；nLEFT JOIN为左联接；nRIGHT JOIN为右联接；nFULL JOIN为全联接；nON后面是联接条件。q注意：ON短语要紧接在JOIN短语后面，WHERE短语置于ON短语的后面。例1(内联接)：查询王小平的学号，姓名，选修的课程号，成绩。SELE 学生表.学号,姓名,课程号,成绩 FROM 学生表 JOIN 选课表 ON 学生表.学号=选课表.学号 WHERE 姓名=王小平 等价于：SELE 学生表.学号,姓名,课程号,成绩 FROM 学生表 , 选课表 WHERE 学生表.学号=选课表.学号 AND 姓名=王小平 例2（左联接）：查询所有教师的教师号，姓名，所上课程的课程号。

28、SELE 教师表.教师号,姓名,课程号 FROM 教师表 LEFT JOIN 授课表 ON 教师表.教师号=授课表.教师号例3(右联接)：查询上了课的教师的教师号，姓名，课程号。SELE 教师表.教师号,姓名,课程号 FROM 教师表 RIGHT JOIN 授课表 ON 教师表.教师号=授课表.教师号例4（全联接）：查询上了课的教师的教师号，姓名，课程号。SELE 教师表.教师号,姓名,课程号 FROM 教师表 FULL JOIN 授课表 ON 教师表.教师号=授课表.教师号嵌套查询：嵌套查询：是一类基于多个表的查询，是指相关的条件涉及多个关系。注意：n在嵌套查询中，有两个SELECTFROM

29、查询块，即内层查询块和外层查询块，但它的最终查询结果仍只出自由外层查询的FROM短语后所指定的表；n内层查询块一定要用一对圆括号括起来；n若内层查询的结果可能多于1个值，要用运算符“IN”，而不用运算符“=”；nVF只支持内外两层嵌套查询；n通配符*表示所有字段；n各种查询的结果可以进行排序，但只能对最终查询结果进行。例1：查询与王小平性别相同的学生的情况。SELE * FROM 学生表 WHERE 性别=(SELE 性别 FROM 学生表 WHERE 姓名=王小平)例2：查询录取分数最高的学生的学号，姓名，录取分数。SELE 学号,姓名,录取分数 FROM 学生表 WHERE 录取分数=(SELE MAX(录