结构化查询语言SQL

1、结构化查询语言SQL结构化查询语言 结构化查询语言SQL(StruCtured Query Language)是关系型数据库的标准语言，包括数据定义、数据操纵、数据查询和数据控制四个部分。SQL的功能命令数据定义CREATE、ALTER、DROP数据操纵INSERT、UPDATE、DELETE数据查询SELECT数据控制(VFP不支持)GRANT、REVOKE1 数据定义一、创建数据表命令CREATE TABLE ( (,),)功能创建数据表。CREATE TABLE 学生表(学号 C(12), 姓名 C(8), 性别 L, 出生年月 D, 评分 N(3, 0)e.g. 4-1 创建新表学生表

2、.dbf，表中有学号 C(12)、姓名 C(8)、性别 L、出生年月 D、评分 N(3, 0)五个字段。二、修改数据表1. 添加字段命令ALTER TABLE ADD (,)功能在表中添加新字段。e.g. 4-2 在学生表.dbf中，添加新字段备注 M。ALTER TABLE 学生表 ADD 备注 MALTER TABLE 学生表 ADD 家庭地址 C ALTER TABLE 学生表 ADD 数学成绩 N(5,1)2. 修改字段命令ALTER TABLE ALTER (,)功能修改字段类型、宽度和小数位数。e.g. 4-3 在学生表.dbf中，把字段性别 L更改为性别 C(2)。ALTER T

3、ABLE 学生表 ALTER 性别 C(2)3. 字段重命名命令ALTER TABLE RENAME TO 功能修改字段名称。e.g. 4-4 在学生表.dbf中，把字段出生年月重命名为出生日期。ALTER TABLE 学生表 RENAME 出生年月 TO 出生日期3. 删除字段命令ALTER TABLE DROP 功能删除字段。e.g. 4-5 在学生表.dbf中，删除字段备注。ALTER TABLE 学生表 DROP 备注四、删除数据表命令DROP TABLE 功能删除数据表。e.g. 4-8 删除表学生表.dbf。DROP TABLE 学生表2 数据操纵一、插入记录命令INSERT IN

4、TO (, )VALUES(, )功能将新记录插入到指定的表中。e.g. 4-28在学生表.dbf中插入一条新记录。INSERT INTO 学生表(学号, 姓名, 性别, 出生日期, 评分)VALUES(S0201111, 徐畅, 女, 1998/06/25, 88)INSERT INTO 学生表VALUES(S0201111, 徐畅, 女, 1998/06/25, 88)学号姓名性别出生日期评分S0201111徐畅女06/25/9888二、修改记录命令UPDATE SET =, WHERE 功能对表中的一行或多行记录的列值进行修改，省略WHERE则修改表的所有记录。e.g. 4-29在学生表

5、.dbf中把“张放”的评分增加3。UPDATE 学生表SET 评分=评分+3WHERE 姓名=张放三、删除记录命令DELETE FROM WHERE 功能删除表中的一行或多行记录，省略WHERE则删除表的所有记录。e.g. 4-30在学生表.dbf中删除学生表“徐畅”的记录。DELETE FROM 学生表WHERE 姓名=徐畅3 数据查询命令SELECT ALL|DISTINCTTOP PERCENT.AS ,.AS FROM !AS INNER|LEFT|RIGHT|FULL JOIN !AS ON INTO TABLE |TO FILE |TO SCREENWHERE AND AND|OR

6、AND|ORGROUP BY ,HAVING ORDER BY ASC|DESC,ASC|DESC功能查询数据表。一、投影查询命令SELECT ALL|DISTINCTTOP .AS , FROM 功能选择字段查询。查询选项说明ALL查询结果允许重复记录。（默认值）DISTINCT查询结果不包含重复记录。TOP 查询结果为满足查询条件的前n条记录，必须与ORDER BY一起使用。*查询结果包含表的所有字段。e.g. 4-9 在学生表.dbf中查询所有记录。SELECT 学号, 姓名, 性别, 出生日期, 评分 FROM 学生表SELECT * FROM 学生表e.g. 4-10 在学生表.db

7、f中查询所有不重复的记录，输出学号、姓名和评分，并将 “学号”指定为“学生表编号” 。SELECT DISTINCT 学号 AS 学生表编号, 姓名, 评分 FROM 学生表二、条件查询 SQL使用WHERE子句指定查询条件。运算符说明举例=、=、=80 and 评分=801.简单条件查询e.g. 4-11 在学生表.dbf中查询评分大于等于80的学生表记录。e.g. 4-12 在选课表.dbf中查询选修课程 “C120”或“C140”，并且成绩大于等于80的学生表记录。2.多重条件查询 在WHERE子句中指定多个查询条件时，必须用逻辑运算符AND、OR或NOT将其连接成复合的逻辑表达式。SE

8、LECT * FROM 选课表WHERE (课程号=C120 OR 课程号=C140) AND 成绩=803.确定范围 使用“BETWEEN AND”进行范围查询。SELECT * FROM 学生表WHERE 评分 BETWEEN 80 AND 90e.g. 4-13 在学生表.dbf中查询评分在80至90之间的学生表记录。SELECT * FROM 学生表WHERE 评分=80 AND 评分=90e.g. 4-14 在选课表.dbf中查询选修课程“C120”或“C140”的学生表记录。4.确定集合 使用“IN”进行集合查询。SELECT * FROM 选课表WHERE 课程号=C120 OR

9、 课程号=C140SELECT * FROM 选课表WHERE 课程号 IN (C120, C140)e.g. 4-15 在选课表.dbf中查询所有姓“陈”的学生表记录。5.部分匹配查询 使用“LIKE”和通配符进行部分匹配查询（模糊查询）。SELECT * FROM 学生表WHERE 姓名 LIKE 陈%通配符说明 %表示任意多个字符 _表示任意一个字符三、统计查询 SQL使用集合函数对一组值进行计算，返回单个值。集合函数功能COUNT(*)求记录的个数COUNT()按列求值的个数SUM ()按列求和（用于数值列）AVG ()按列求平均值（用于数值列）MAX ()求一列中的最大值MIN ()

10、求一列中的最小值e.g. 4-16在选课表.dbf中查询课程号为“C120”的记录，输出课程号和选修该课程的人数。SELECT 课程号, COUNT(*) AS 选修人数FROM 选课表WHERE 课程号=C120e.g. 4-17在选课表.dbf中查询课程号为“C120”的记录，输出课程号、成绩的最高分和最低分、以及它们之间相差的分数。SELECT 课程号,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分,MAX(成绩)-MIN(成绩) AS 相差分数FROM 选课表WHERE 课程号=C120四、分组查询 SQL使用GROUP BY子句对查询结果进行分组，使用HAVING子句在每

11、个分组上选择记录。WHERE与HAVING：WHERE作用于基本表或视图，HAVING作用于分组且必须与GROUP BY一起使用。查询的执行顺序：WHEREGROUP BYHAVING。e.g. 4-18在选课表.dbf中查询每门课程的选修人数，输出课程号和选修人数。SELECT 课程号, COUNT(*) AS 选修人数FROM 选课表GROUP BY 课程号e.g. 4-19在选课表.dbf中查询选修两门或者两门以上课程的学生表记录，输出学号和选课表门数。SELECT 学号, COUNT(*) AS 选课表门数FROM 选课表GROUP BY 学号 HAVING COUNT(*)=2五、查

12、询排序 SQL使用ORDER BY子句对查询结果进行排序。排序选项功能ASC升序排列（默认值）DESC降序排列e.g. 4-20在选课表.dbf中查询选修了课程“C130” 并且成绩最高的3名学生表的记录，输出学号和成绩。SELECT TOP 3 学号, 成绩 FROM 选课表WHERE 课程号=C130ORDER BY 成绩 DESCTOP必须与ORDER BY一起使用。六、连接查询 连接查询用于对两个或者两个以上的表进行查询，包括内连接(INNER JOIN) 、外连接(OUTER JOIN) 等。1.内连接命令SELECT FROM , WHERE AND 功能连接查询。SELECT 学

13、生表.学号, 姓名, 课程号FROM 学生表, 选课表WHERE 学生表.学号=选课表.学号 AND 课程号=C130e.g. 4-21在学生表.dbf和选课表.dbf中查询选修课程“C130”的学生表记录，输出学号、姓名和课程号。2.外连接命令SELECT FROM INNER|LEFT|RIGHT|FULL JOIN ON WHERE 功能连接查询。连接类型说明INNER JOIN内连接，输出两个表匹配的数据行。（默认值）LEFT JOIN左连接，输出两个表匹配的数据行和左表剩余的数据行。RIGHT JOIN右连接，输出两个表匹配的数据行和右表剩余的数据行。FULL JOIN完全连接，输出

14、两个表匹配的数据行、左表剩余的数据行和右表剩余的数据行。SELECT 学生表.学号,姓名, 课程号;FROM 学生表;INNER JOIN 选课表 ON 学生表.学号=选课表.学号 WHERE 课程号=c130七、嵌套查询 在一个查询的WHERE子句中包含另一个SELECT-FROM-WHERE查询块，此查询块称为子查询，包含子查询的语句称为父查询。格式WHERE NOT IN (子查询)WHERE ANY|SOME|ALL (子查询)WHERE NOT EXISTS (子查询)运算符ANYALL大于子查询结果的某个值大于子查询结果的所有值=大于等于子查询结果的某个值大于等于子查询结果的所有值

15、=小于等于子查询结果的某个值小于等于子查询结果的所有值=等于子查询结果的某个值无实际意义!=或无实际意义不等于子查询结果的任何值SELECT 学号, 课程号 FROM 选课表WHERE 学号=(SELECT 学号 FROM 学生表 WHERE 姓名=“刘晓芸)1.返回一个值的子查询e.g. 4-24在学生表.dbf和选课表.dbf中查询“刘小芸”选修的课程，输出学号、课程号。SELECT 学号, 姓名 FROM 学生表WHERE 学号 =ANY(SELECT 学号 FROM 选课表 WHERE 课程号=C130)2.返回一组值的子查询e.g. 4-25在学生表.dbf和选课表.dbf中查询选修

16、“C130”的学生表记录，输出学号、姓名。SELECT 学号, 姓名 FROM 学生表WHERE 学号 IN(SELECT 学号 FROM 选课表 WHERE 课程号=C130)SELECT 学号, 姓名 FROM 学生表WHERE NOT EXISTS(SELECT * FROM 选课表 WHERE 选课表.学号=学生表.学号)e.g. 4-26在学生表.dbf和选课表.dbf中查询没有选课表的学生表记录，输出学号、姓名。八、查询结果的输出 在查询中使用INTO或TO子句指定如何保存查询结果。查询去向说明INTO TABLE 输出查询到数据表INTO CURSOR 输出查询到临时表INTO ARRAY 输出查询到数组TO FILE 输出查询到文本文件TO SCREEN输出查询到屏幕TO PRINTER输出查询到打印机e.g. 4-27在学生表.dbf中按评分由高到低查询学生表的记录，输出到表。SELECT * FROM 学生表ORDER BY 评分 DESCINTO TABLE xs练习1.有图书表，其内容如下记录号书名出版单位单价 1VFP数据库电子科大 2计算机基础四川大学 3信号与系统电子科大 4计算机网络电子科大 5计算机原理四川大学 6现代汉语四川师大 7操作系统西南交大UPDATE TS SET 单价=单价+8WHERE 出版单位=四川师