数据库原理及应用课件：第6章 数据操作语言1

1、数据库基础与实践技术（SQL Server 2008）第六章第6章 数据操作语言6.1 数据查询语句6.2 数据更改功能2查询语句基本结构SELECT - 需要哪些列 FROM - 来自于哪些表 WHERE - 根据什么条件 GROUP BY HAVING ORDER BY 3/130课前练习学生表S（学号sno，姓名sname，班级sclass，年龄sage，性别Sgender）课程表C（课程号cno，课程名cname，学分credit，授课教师cteacher）选修表SC （学号sno，课程号cno，成绩score）1查询年龄不大于20岁的女学生的全部信息。2查询周老师教授的课程名称。3查

2、询选修课程“数据库”并且得分在80分以上的学生学号。4查询选修了5号课程的学生的学号和姓名。5查询课程“数据库”的成绩，并按学号排序。6. 查询每位学生的平均分。 6.1 数据查询语句6.1.1 查询语句基本结构 6.1.2 单表查询6.1.3 多表连接查询 6.1.4 使用TOP限制结果集6.1.5 将查询结果保存到新表中5/1306.1.1 查询语句基本结构SELECT - 需要哪些列 FROM - 来自于哪些表 WHERE - 根据什么条件 GROUP BY HAVING ORDER BY 6/1306.1.2 单表查询1.选择表中若干列2.选择表中的若干元组3.对查询结果排序4.使用聚

3、合函数统计数据5.对数据进行分组统计7/1301.查询全部列如果要查询表中的全部列，可以使用两种方法：在中列出所有的列名；如果列的显示顺序与其在表中定义的顺序相同，则可以简单地在中写星号“*”。8/1302.查询结果去掉重复行SELECT语句不会自动去掉结果中的重复行，如果要求结果中不出现行，必须要明确地用DISTINCT指出。9/1303.常用的where查询条件查询条件谓 词比较运算符=, , =, , =, （或!=）确定范围BETWEEN AND, NOT BETWEEN AND确定集合IN, NOT IN字符匹配LIKE, NOT LIKE 空值IS NULL, IS NOT NUL

4、L多重条件AND, OR104.字符串匹配（LIKE）列名 NOT LIKE ESCAPE 匹配串中可包含如下通配符：%（百分号）：匹配0个或多个字符。_（下划线）：匹配一个字符。 ：匹配方括号中的任何一个字符。：不匹配方括号中的任何一个字符。“转义字符”是任何一个有效的字符。11/1305.涉及空值的查询空值不是一个确定的值，所以不可以用等于或不等于来比较或衡量；空值只能说是空值（IS NULL）或不是空值（IS NOT NULL）。12/1306.聚合函数COUNT（*）：统计表中元组的个数。COUNT（DISTINCT ）：统计列值个数SUM（）：计算列值的和值（必须是数值型列）。AVG

5、（）：计算列值的平均值（必须是数值型列）。MAX（）：得到列值的最大值。MIN（）：得到列值的最小值。【说明】除COUNT（*）外，其他函数在计算过程中均忽略NULL值。【使用】聚合函数不能出现在WHERE子句中。13/1307.group by子句说明 GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的列别名。带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列和统计函数。15/1307.group by子句说明 7.group by子句说明 学生表S（学号sno，姓名sname，性别sex，出生日期birthdate，所在系dept）课程表

6、C（课程号cno，课程名cname，学分credit，授课学期semester）选修表SC （学号sno，课程号cno，成绩grade）单表查询练习1.查询黄同学和郭同学的基本信息2.在学生表中查询学号的最后一位不是2、3、5的学生信息3.统计选修“JAVA”课程的人数4.查询选修课程的人数5.查询平均成绩大于80分的学生学号（按平均成绩降序排列）6.查询选修以DB_开头的课程的学生学号和姓名7.查询每个系的男女生人数。单表查询练习8.按多个列分组。统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。SELECT dept, Sex, Count(*) as

7、 人数, Max(datediff(year, birthdate, getdate() as 最大年龄 FROM S GROUP BY dept, Sex ORDER BY dept20/130单表查询练习单表查询练习6.1.3 多表连接查询一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，主要包括：内连接。外连接：左外连接、右外连接、全外连接。22/130内连接与外连接示意图A与B的左外连接结果： A + CA与B的右外连接结果： B+ CA与B的内连接结果： C23/130A与B的全外连接结果： A +B+ C1.内连接同时涉及多个表的查询称为连接

8、查询。连接条件（连接谓词 ）连接字段连接条件的一般格式：. . 比较运算符：=、=、=、!=、. BETWEEN . AND .【注意】连接条件中的各连接字段类型必须是可比的，但不必是相同的。1.内连接1.内连接一. 等值连接与自然连接二.自身连接三.非等值连接四.复合条件连接一、等值与自然连接查询1.等值连接连接运算符为 = 的连接操作 . = .任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用非同名属性名时可以加也可以省略表名前缀。 一、等值与自然连接查询 【例2】 查询每个学生及其选修课程的情况。SELECT S.*，SC.*FROM S，SCWHERE S.Sno = SC

9、.Sno；这是等值连接,连接条件是等值条件SELECT *FROM S inner join SCon S.Sno = SC.Sno；这是等值连接,连接条件是等值条件一、等值与自然连接查询081102081101S表081101081101SC表081101081101结果表满足则连接S.Sno=SC.Sno的条件满足？等值连接操作的执行过程：一、等值与自然连接查询2.自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。二、等值与自然连接查询【例3】 对例2用自然连接完成。 SELECT S.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM S，SC W

10、HERE S.Sno = SC.Sno；去掉重复的一个Sno公共属性Sno上的等值条件二、自身连接 一个表与其自己进行连接，称为表的自身连接。是特殊的内连接。相互连接的表物理上为同一张表。必须为两个表取别名，使之在逻辑上成为两个表。 FROM 表1 AS T1 - 在内存中生成“T1” JOIN 表1 AS T2 - 在内存中生成“T2”必须对属性使用别名前缀【例4】 查询至少被两个学生选的课程的课程号。SELECT DISTINCT a.Cno FROM SC a JOIN SC b ON a.Cno = b.Cno AND a.Sno != b.Sno二、自身连接 三、非等值连接查询连接运

11、算符 不是 = 的连接操作【例5】检索年龄比张立同学小的学生姓名 select s1.sname from s s1 join s s2 on s1.birthdates2.birthdate where s2.sname=张立 连接条件不是等于号1.内连接【例6】统计每个系的学生的考试平均成绩。SELECT Dept, AVG(grade) as AverageGrade FROM S JOIN SC ON S.Sno = SC.Sno GROUP BY Dept35/130多表连接查询进行分组2.外连接外连接会返回FROM子句中提到的至少一个表的所有行，只要这些行符合任何WHERE或HAV

12、ING搜索条件。 左外连接FROM 表1 LEFT OUTER JOIN 表2 ON 右外连接 FROM 表1 RIGHT OUTER JOIN 表2 ON 全外连接FROM 表1 FULL OUTER JOIN 表2 ON 36/1302.外连接【例7】 查询没人选的课程的课程名。 SELECT Cname FROM C LEFT JOIN SC ON C.Cno = SC.Cno WHERE SC.Cno IS NULL37/1302.外连接【例8】查询计算机系没有选课的学生，列出学生姓名和性别。SELECT Sname,Dept,Cno,grade FROM S LEFT JOIN SC

13、 ON S.Sno = SC.Sno WHERE Dept = 计算机系 AND SC.Sno IS NULL38/1302.外连接【例9】 统计计算机系每个学生的选课门数，包括没有选课的学生。SELECT S.Sno AS 学号, COUNT(SC.Cno) AS 选课门数 FROM S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系 GROUP BY S.Sno39/1302.外连接select * from T1 full join T2 on T1.T1_C11 = T2.T2_C1140/1309.查询选修课程“JAVA”并且得分在8

14、0分以上的学生学号。10.查询选修了C005号课程的学生的学号和姓名。11.查询选修课程“数据结构”的成绩，并按学号排序。连接查询练习6.1.4 使用TOP限制结果集行数TOP (expression) percent WITH TIES expression ：指定返回行数的数值表达式。TOP n percnet：取查询结果的前n%行。WITH TIES：包括并列的结果。【说明】1.TOP谓词写在选择列之前，distinct之后。2.如果使用了WITH TIES谓词，则要求必须使用ORDER BY子句对查询结果进行排序。42/130示例【例10】查询考试成绩最高的三个成绩，列出学号、课程号和

15、成绩。SELECT TOP 3 Sno, Cno, Grade FROM SCORDER BY Grade DESC43/130示例【例11】 查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。SELECT TOP 2 WITH TIES Cno, COUNT(*) 选课人数 FROM SC GROUP BY Cno ORDER BY COUNT(Cno) ASC44/130示例【例12】 查询计算机系选课门数超过2门的学生中，考试平均成绩最高的前2名（包括并列的情况）学生的学号、选课门数和平均成绩。 SELECT TOP 2 WITH TIES S.Sno, COUN

16、T(*) 选课门数,AVG(Grade) 平均成绩 FROM S JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系 GROUP BY S.sno HAVING COUNT(*) 2 ORDER BY AVG(Grade) DESC45/1306.1.5 将查询结果保存到新表中SELECT 查询列表序列 INTO FROM 数据源 46/130【说明】根据查询列表序列的内容创建一个新表，并将查询的结果按列对应顺序保存到该新表中。局部临时表和全局临时表47/1301.在表名前面加“#”或“#”符号，表示创建的表是临时表。2.临时表的生命周期较短，当用户断开与该

17、数据库的连接时，服务器会自动删除它们。3.#表示创建的是本地临时表 ，只能由创建者用。4.#表示创建的是全局临时表，所有用户都能用。5.临时表创建完成后均放在tempdb系统数据库。示例【例13】将计算机系的学生信息保存到#ComputerStudent局部临时表中。SELECT Sno, Sname, Sex, birthdate，dept INTO #ComputerStudent FROM S WHERE Dept = 计算机系48/130示例【例14】统计每个学期（semester）开设的课程总门数，将结果保存到永久表Cno_Count表中SELECT Semester, COUNT(

18、*) C_Count INTO Cno_CountFROM C GROUP BY Semester49/1306.2 数据更改功能6.2.1 插入数据6.2.2 更新数据6.2.3 删除数据50/1306.2.1 插入数据INSERT TOP ( expression ) PERCENT INTO table_or_view_name ( column_list ) VALUES ( ( DEFAULT | NULL | expression ,.n ) ,.n )| SELECT statement 51/130【说明】值与列名按顺序对应，要求值类型与列数据类型一致。对语句中无值对应的列名赋

19、NULL，default值，标识列值或计算列值。如果没有指明列名表，则新插入记录的值的顺序必须与表中列的顺序一致。单个insert语句最多可以插入的行数为1000 。52/1306.2.1 插入数据示例【例15】插入多行数据。在SC表中插入3条新记录，学号均为“0821105”，选修的课程号分别为“C001”、“C002”和“C004”，成绩分别为：90、88和NULL。INSERT INTO SC VALUES (0821105, C001, 90), (0821105, C002, 80), (0821105, C004, NULL)53/130示例【例16】 将数据插入到含标识列的表中。

20、（1）创建含标识列的表。CREATE TABLE T1 ( column_1 int IDENTITY, column_2 VARCHAR(10);（2）插入2行数据。INSERT T1 VALUES (Row #1);set indentity_insert t1 onINSERT T1 VALUES (2,Row #2);54/130示例【例17】 使用 SELECT语句插入数据。统计每门课程的平均成绩，并把统计结果保存到一个新表中。（1）建新表CREATE TABLE AveGrade( Cno CHAR(6), AvgGrade SMALLINT ) （2）插入数据 INSERT INTO Deptage SELECT Cno, AVG(Grade) FROM SC GROUP BY Cno55/1306.2.2 更新数据UPDATE TOP ( expression ) PERCENT table_or_view_name SET column_name = express