结构化查询语言SQL.ppt

1、结构化查询语言SQL,结构化查询语言 结构化查询语言SQL(StruCtured Query Language)是关系型数据库的标准语言，包括数据定义、数据操纵、数据查询和数据控制四个部分。,1 数据定义,一、创建数据表,CREATE TABLE 学生表 (学号 C(12), 姓名 C(8), 性别 L, 出生年月 D, 评分 N(3, 0),e.g. 4-1 创建新表学生表.dbf，表中有学号 C(12)、姓名 C(8)、性别 L、出生年月 D、评分 N(3, 0)五个字段。,二、修改数据表,1. 添加字段,e.g. 4-2 在学生表.dbf中，添加新字段备注 M。,ALTER TABLE

2、学生表 ADD 备注 M,ALTER TABLE 学生表 ADD 家庭地址 C,ALTER TABLE 学生表 ADD 数学成绩 N(5,1),2. 修改字段,e.g. 4-3 在学生表.dbf中，把字段性别 L更改为性别 C(2)。,ALTER TABLE 学生表 ALTER 性别 C(2),3. 字段重命名,e.g. 4-4 在学生表.dbf中，把字段出生年月重命名为出生日期。,ALTER TABLE 学生表 RENAME 出生年月 TO 出生日期,3. 删除字段,e.g. 4-5 在学生表.dbf中，删除字段备注。,ALTER TABLE 学生表 DROP 备注,四、删除数据表,e.g.

3、 4-8 删除表学生表.dbf。,DROP TABLE 学生表,2 数据操纵,一、插入记录,e.g. 4-28在学生表.dbf中插入一条新记录。,INSERT INTO 学生表(学号, 姓名, 性别, 出生日期, 评分) VALUES(S0201111, 徐畅, 女, 1998/06/25, 88),INSERT INTO 学生表 VALUES(S0201111, 徐畅, 女, 1998/06/25, 88),二、修改记录,e.g. 4-29在学生表.dbf中把“张放”的评分增加3。,UPDATE 学生表 SET 评分=评分+3 WHERE 姓名=张放,三、删除记录,e.g. 4-30在学生表

4、.dbf中删除学生表“徐畅”的记录。,DELETE FROM 学生表 WHERE 姓名=徐畅,3 数据查询,一、投影查询,e.g. 4-9 在学生表.dbf中查询所有记录。,SELECT 学号, 姓名, 性别, 出生日期, 评分 FROM 学生表,SELECT * FROM 学生表,e.g. 4-10 在学生表.dbf中查询所有不重复的记录，输出学号、姓名和评分，并将 “学号”指定为“学生表编号” 。,SELECT DISTINCT 学号 AS 学生表编号, 姓名, 评分 FROM 学生表,二、条件查询 SQL使用WHERE子句指定查询条件。,SELECT * FROM 学生表 WHERE 评

5、分=80,1.简单条件查询,e.g. 4-11 在学生表.dbf中查询评分大于等于80的学生表记录。,e.g. 4-12 在选课表.dbf中查询选修课程 “C120”或“C140”，并且成绩大于等于80的学生表记录。,2.多重条件查询 在WHERE子句中指定多个查询条件时，必须用逻辑运算符AND、OR或NOT将其连接成复合的逻辑表达式。,SELECT * FROM 选课表 WHERE (课程号=C120 OR 课程号=C140) AND 成绩=80,3.确定范围 使用“BETWEEN AND”进行范围查询。,SELECT * FROM 学生表 WHERE 评分 BETWEEN 80 AND 9

6、0,e.g. 4-13 在学生表.dbf中查询评分在80至90之间的学生表记录。,SELECT * FROM 学生表 WHERE 评分=80 AND 评分=90,e.g. 4-14 在选课表.dbf中查询选修课程“C120”或“C140”的学生表记录。,4.确定集合 使用“IN”进行集合查询。,SELECT * FROM 选课表 WHERE 课程号=C120 OR 课程号=C140,SELECT * FROM 选课表 WHERE 课程号 IN (C120, C140),e.g. 4-15 在选课表.dbf中查询所有姓“陈”的学生表记录。,5.部分匹配查询 使用“LIKE”和通配符进行部分匹配查

7、询（模糊查询）。,SELECT * FROM 学生表 WHERE 姓名 LIKE 陈%,三、统计查询 SQL使用集合函数对一组值进行计算，返回单个值。,e.g. 4-16在选课表.dbf中查询课程号为“C120”的记录，输出课程号和选修该课程的人数。,SELECT 课程号, COUNT(*) AS 选修人数 FROM 选课表 WHERE 课程号=C120,e.g. 4-17在选课表.dbf中查询课程号为“C120”的记录，输出课程号、成绩的最高分和最低分、以及它们之间相差的分数。,SELECT 课程号,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分 ,MAX(成绩)-MIN(成绩)

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

9、学生表记录，输出学号和选课表门数。,SELECT 学号, COUNT(*) AS 选课表门数 FROM 选课表 GROUP BY 学号 HAVING COUNT(*)=2,五、查询排序 SQL使用ORDER BY子句对查询结果进行排序。,e.g. 4-20在选课表.dbf中查询选修了课程“C130” 并且成绩最高的3名学生表的记录，输出学号和成绩。,SELECT TOP 3 学号, 成绩 FROM 选课表 WHERE 课程号=C130 ORDER BY 成绩 DESC,TOP必须与ORDER BY一起使用。,六、连接查询 连接查询用于对两个或者两个以上的表进行查询，包括内连接(INNER JO

10、IN) 、外连接(OUTER JOIN) 等。,1.内连接,SELECT 学生表.学号, 姓名, 课程号 FROM 学生表, 选课表 WHERE 学生表.学号=选课表.学号 AND 课程号=C130,e.g. 4-21在学生表.dbf和选课表.dbf中查询选修课程“C130”的学生表记录，输出学号、姓名和课程号。,2.外连接,SELECT 学生表.学号,姓名, 课程号; FROM 学生表; INNER JOIN 选课表 ON 学生表.学号=选课表.学号 WHERE 课程号=c130,七、嵌套查询 在一个查询的WHERE子句中包含另一个SELECT-FROM-WHERE查询块，此查询块称为子查询

11、，包含子查询的语句称为父查询。,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中查询选修“C130”的学生表记录，输出学号、姓名。,SELECT 学号, 姓名 FRO

12、M 学生表 WHERE 学号 IN (SELECT 学号 FROM 选课表 WHERE 课程号=C130),SELECT 学号, 姓名 FROM 学生表 WHERE NOT EXISTS (SELECT * FROM 选课表 WHERE 选课表.学号=学生表.学号),e.g. 4-26在学生表.dbf和选课表.dbf中查询没有选课表的学生表记录，输出学号、姓名。,八、查询结果的输出 在查询中使用INTO或TO子句指定如何保存查询结果。,e.g. 4-27在学生表.dbf中按评分由高到低查询学生表的记录，输出到表xs.dbf。,SELECT * FROM 学生表 ORDER BY 评分 DESC

13、 INTO TABLE xs,练习,1.有图书表TS.DBF，其内容如下,记录号书名出版单位单价 1VFP数据库电子科大20.0 2计算机基础四川大学25.0 3信号与系统电子科大28.0 4计算机网络电子科大27.0 5计算机原理四川大学21.0 6现代汉语四川师大23.0 7操作系统西南交大24.0,UPDATE TS SET 单价=单价+8 WHERE 出版单位=四川师大 SELECT 出版单位, AVG(单价) AS 单价 FROM TS GROUP BY 出版单位 INTO DBF ABC SELECT * FROM ABC WHERE 单价28 ORDER BY 单价 DESC I

14、NTO DBF ABC1 USE ABC1 ? 出版单位 RETU,阅读下列程序,(1)新表ABC的记录有（C） A 7B 5C 4D 3 (2)最后显示的出版单位是（A） A电子科大B四川大学 C西南交大D四川师大 (3)函数AVG( )的功能是（C） A统计个数B求单价的总和 C求单价的平均D是错误的函数 (4)新表ABC1的记录有（A） A 3B 4C 5D 6,2.有如下的三个表：,以下SQL语句完成查询，按照姓名升序排序显示不重复的姓名、课程名称和成绩；请完善语句： seleCt distinCt 姓名, 课程名称, 成绩; from 学生表, 选课表, 课程; where 学生表.

15、学号=选课表.学号 .and. 选课表.课程号=课程.课程号; order by 姓名,学生表（学号，姓名，性别，出生年月） 选课表（学号，课程号、成绩） 课程（课程号，课程名称，课时）,3.在教室管理数据库中有3个表：,1)为教师表的“教师号”建立候选索引，索引名为JSH ALTER TABLE 教师 ADD UNIQUE 教师号 TAG JSH 2)将所有教师的工资上调20% UPDATE 教师 SET 工资=工资*1.2 3)查询所授课程的可是80的教师的教师号、姓名和课程名。 SELECT 教师.教师号,姓名,课程名 FROM 教师 INNER JOIN 授课 INNER JOIN 课程 ON 课程.课程号=授课.课程号 ; ON 教师.教师号=授课.教师号 WHRER 课时=80,教师(教师号 C(6),姓名 C(8),职称 C(10),年龄 N(3),工资 N(4) 授课(教师号 C(6),课程号 C(4) 课程(课程号 C(4),课程名 C(10),课时 N(3),4 查询设计器,一、查询文件 查