《数据库操作语句.ppt》由会员分享，可在线阅读，更多相关《数据库操作语句.ppt（78页珍藏版）》请在三一办公上搜索。
1、数据库原理与应用教程,第4章 数据操作,4.1 数据查询功能4.2 数据更改功能,4.1 数据查询功能,4.1.1 查询语句的基本结构4.1.2 简单查询4.1.3 多表连接查询4.1.4 子查询,查询语句基本格式,SELECT-需要哪些列 FROM-来自于哪些表 WHERE-根据什么条件 GROUP BY HAVING ORDER BY,4.1.2 简单查询,1.选择表中若干列,之,1.查询指定的列,查询表中用户感兴趣的部分属性列。例1：查询全体学生的学号与姓名。SELECT Sno,Sname FROM Student,例2.查询全体学生的姓名、学号、所在系,SELECT Sname,Sn
2、o,Sdept FROM Student,2.查询全部列,例3查询全体学生的记录SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student等价于：SELECT*FROM Student,3.查询经过计算的列,例4查询全体学生的姓名及其出生年份。SELECT Sname,2010-Sage FROM Student,常量列,例5查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为“出生年份”常量值。SELECT Sname,出生年份:,2010-Sage FROM Student,改变列标题,语法：列名|表达式 AS 列标题或：列标题
3、 列名|表达式例：SELECT Sname 姓名,Year of Birth 出生年份,2010-Sage 年份，FROM Student,4.1.2 简单查询,2.选择表中若干元组,之,1.消除取值相同的行,例6查询选修了课程的学生的学号,SELECT Sno FROM SC,有重复行！,要去掉结果表中的重复行，可用DISTINCT,SELECT DISTINCT Sno FROM SC,2.查询满足条件的元组,比较大小,例7查询计算机系全体学生的姓名。SELECT Sname FROM Student WHERE Sdept=计算机系例8查询年龄在20岁以下的学生的姓名及年龄。SELECT
4、 Sname,Sage FROM Student WHERE Sage 20例9查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade 60,确定范围,用BETWEENAND和NOT BETWEENAND是逻辑运算符，可以用来查找属性值在或不在指定范围内的元组，其中BETWEEN后边指定范围的下限，AND后边指定范围的上限。BETWEENAND的格式为：列名|表达式 NOT BETWEEN 下限值 AND 上限值如果列或表达式的值在不在下限值和上限值范围内，则结果为True，表明此记录符合查询条件。,示例,例10查询年龄在2023岁之间
5、的学生的姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23例11查询年龄不在2023之间的学生姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23,确定集合,使用IN运算符。用来查找属性值属于指定集合的元组。格式为：列名 NOT IN(常量1,常量2,常量n)当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；NOT IN：当列中的值与某个常量值相同时，则
6、结果为False，表明此记录为不符合查询条件的记录,示例,例12查询信息系、数学系和计算机系学生的姓名和性别。SELECT Sname,Ssex FROM Student WHERE Sdept IN(信息系,数学系,计算机系)例13查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN(信息系,数学系,计算机系),字符匹配,使用LIKE运算符一般形式为：列名 NOT LIKE 匹配串中可包含如下四种通配符：_：匹配任意一个字符；%：匹配0个或多个字符；：匹配 中的任意一个字符；对于连续字母的
7、匹配，例如匹配abcd，可简写为a-d：不匹配 中的任意一个字符,示例,例14查询姓张的学生的详细信息。SELECT*FROM Student WHERE Sname LIKE 张%例15查询学生表中姓张、李和刘的学生的情况。SELECT*FROM Student WHERE Sname LIKE 张李刘%例16查询名字中第2个字为小或大的学生的姓名和学号。SELECT Sname,Sno FROM Student WHERE Sname LIKE _小大%,示例（续）,例17查询所有不姓“王”也不姓“张”的学生姓名SELECT Sname FROM Student WHERE Sname N
8、OT LIKE 王张%或者：SELECT Sname FROM Student WHERE Sname LIKE 王张%或者：SELECT Sname FROM Student WHERE Sname NOT LIKE 王%AND Sname NOT LIKE 张%,示例（续）,例18查询姓“王”且名字是2个字的学生姓名。SELECT Sname FROM Student WHERE Sname LIKE 王_,示例（续）,例19查询姓王且名字是3个字的学生姓名SELECT Sname FROM Student WHERE Sname LIKE 王_,注意：尾随空格的处理。SELECT Sna
9、me FROM Student WHERE rtrim(Sname)LIKE 王_,涉及空值的查询,空值（NULL）在数据库中表示不确定的值。例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。判断某个值是否为NULL值，不能使用普通的比较运算符。判断取值为空的语句格式为：列名 IS NULL判断取值不为空的语句格式为：列名 IS NOT NULL,示例,例20查询没有考试成绩的学生的学号和相应的课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL例21查询所有有考试成绩的学生的学号和课程号。SELECT Sno,C
10、no FROM SC WHERE Grade IS NOT NULL,多重条件查询,在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。用AND连接的条件表示必须全部满足所有的条件的结果才为True；用OR连接的条件表示只要满足其中一个条件结果即为True。例21查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM Student WHERE Sdept=计算机系 AND Sage 20,示例（续）,例23查询计算机系和信息系年龄大于等于20岁的学生姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE(Sd
11、ept=计算机系 OR Sdept=信息系)AND Sage=20或：SELECT Sname,Sdept,Sage FROM Student WHERE Sdept IN(计算机系,信息系)AND Sage=20,4.1.2 简单查询#,3.对查询结果进行排序,之,对查询结果进行排序,可对查询结果进行排序。排序子句为：ORDER BY ASC|DESC,说明：按进行升序（ASC）或降序（DESC）排序。,示例,例22将学生按年龄的升序排序。SELECT*FROM Student ORDER BY Sage例23查询选修了c02号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECT
12、Sno,Grade FROM SCWHERE Cno=c02 ORDER BY Grade DESC 例24查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT*FROM StudentORDER BY Sdept,Sage DESC,4.1.2 简单查询,4.使用计算函数汇总数据,之,使用计算函数汇总数据,SQL提供的计算函数有：COUNT（*）：统计表中元组个数；COUNT（DISTINCT）：统计本列列值个数；SUM（DISTINCT）：计算列值总和；AVG（DISTINCT）：计算列值平均值；MAX（DISTINCT）：求列值最大值；MIN（DI
13、STINCT）：求列值最小值。上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。,示例,例25统计学生总人数。SELECT COUNT(*)FROM Student 例26统计选修了课程的学生的人数。SELECT COUNT(DISTINCT Sno)FROM SC例27 计算9512101号学生的考试总成绩之和。SELECT SUM(Grade)FROM SC WHERE Sno=9512101,示例（续）,例28计算C01号课程学生的考试平均成绩。SELECT AVG(Grade)FROM SC WHERE Cno=C01例29查询选修了C01号课程的学生的最高分和最低
14、分。SELECT MAX(Grade),MIN(Grade)FROM SC WHERE Cno=C01注意：计算函数不能出现在WHERE子句中,4.1.2 简单查询,5.对查询结果进行分组计算,之,对查询结果进行分组计算,作用：可以控制计算的级别：对全表还是对一组。目的：细化计算函数的作用对象。分组语句的一般形式：GROUP BY HAVING,1.使用GROUP BY,例30统计每门课程的选课人数，列出课程号和人数。SELECT Cno as 课程号,COUNT(Sno)as 选课人数 FROM SC GROUP BY Cno 对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然
15、后再对每一组使用COUNT计算，求得每组的学生人数。,例31查询每名学生的选课门数和平均成绩。SELECT Sno as 学号,COUNT(*)as 选课门数,AVG(Grade)as 平均成绩 FROM SC GROUP BY Sno,2.使用HAVING,HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。例32查询修了3门以上课程的学生的学号SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3,示例,例33查询修课门数等于或大于4门的学生的平均成绩和选课门数。SELECT Sno,AVG(Grade)平均成绩
16、,COUNT(*)修课门数 FROM SC GROUP BY Sno HAVING COUNT(*)=4,4.1.3 多表连接查询,若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询连接查询包括内连接、外连接和交叉连接等。,连接基础知识,连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：,必须是可比的,内连接,SQL-92 内连接语法如下：SELECT FROM 表名 INNER JOIN 被连接表 ON 连接条件,执行连接操作的过程：首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组
17、与该元组拼接起来，形成结果表中的一个元组。表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，重复这个过程，直到表1中的全部元组都处理完毕为止。,例36.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。SELECT Sname,Cno,Grade FROM Student JOIN SC ON Student.Sno=SC.Sno WHERE Sdept=计算机系,例37.查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。SELECT Sname,Cname,Grade FROM Student s JOIN SC ON s.Sno=
18、SC.Sno JOIN Course c ON c.Cno=SC.Cno WHERE Sdept=信息系 AND Cname=VB,例38查询所有修了VB课程的学生的修课情况，要求列出学生姓名和所在的系。SELECT Sname,Sdept FROM Student S JOIN SC ON S.Sno=SC.Sno JOIN Course C ON C.Cno=SC.cno WHERE Cname=VB,自连接,为特殊的内连接相互连接的表物理上为同一张表。必须为两个表取别名，使之在逻辑上成为两个表。,例39.查询与刘晨在同一个系学习的学生的姓名和所在的系。SELECT S2.Sname,S2
19、.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept=S2.Sdept WHERE S1.Sname=刘晨 AND S2.Sname!=刘晨,外连接,只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。ANSI方式的外连接的语法格式为：FROM 表1 LEFT|RIGHT OUTER JOIN 表2 ON theta方式的外连接的语法格式为：左外连接：FROM 表1,表2 WHERE 表1.列名(+)表2.列名右外连接：FROM 表1,表2 WHERE 表1.列名 表2.列名(+),例40.查询学生的修课情况，包括修了课程的
20、学生和没有修课的学生。SELECT Student.Sno,Sname,Cno,GradeFROM Student LEFT OUTER JOIN SCON Student.Sno=SC.Sno,4.1.4 子查询,在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句的 WHERE 或 HAVING 子句内，或其它子查询中 子查询的 SELECT 查询总是使用圆括号括起来。,使用子查询进行基于集合的测试,使用子查询进行基于集合的测试的语句的一般格式为：列名 NOT IN(子
21、查询),示例,例41.查询与刘晨在同一个系的学生。SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname=刘晨)AND Sname!=刘晨,示例,例42.查询成绩为大于90分的学生的学号、姓名。SELECT Sno,Sname FROM StudentWHERE Sno IN(SELECT Sno FROM SCWHERE Grade 90),例43.查询选修了“数据库基础”课程的学生的学号、姓名。SELECT Sno,Sname FROM Student WHERE Sno
22、 IN(SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname=数据库基础),使用子查询进行比较测试,带比较运算符的子查询指父查询与子查询之间用比较运算符连接，当用户能确切知道内层查询返回的是单值时，可用、=、运算符。,例44.查询修了c02课程且成绩高于此课程的平均成绩的学生的学号和成绩。SELECT Sno,Grade FROM SC WHERE Cno=c02 and Grade(SELECT AVG(Grade)from SC WHERE Cno=c02),使用子查询进行存在性测试,一般使用EXISTS谓词。
23、带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。,例45.查询选修了c01号课程的学生姓名。SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=c01),注意,注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。注2:由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。,上句的处理过程,1.找外层表Student表的第一行，根据其Sno
24、值处理内层查询2.由外层的值与内层的结果比较，由此决定外层条件的真、假3.顺序处理外层表Student表中的第2、3、行。,例46.查询没有选修c01号课程的学生姓名和所在系。SELECT Sname,Sdept FROM Student WHERE NOT EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=c01),4.2 数据更改功能,4.2.1 插入数据 4.2.2 更新数据 4.2.3 删除数据,4.2.1 插入数据,插入单行记录的INSERT语句的格式为：INSERT INTO()VALUES（值表）功能：新增一个符合表结构的数据
25、行，将值表数据按表中列定义顺序或列名表顺序赋给对应列名。,注意,值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。如果后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。,示例,例1将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中。INSERT INTO Student VALUES(9521105,陈冬,男,18,信息系)例2在SC表中插入一新记录，成绩暂缺。INSERT INTO SC(Sno,Cno,XKLB)VALUES(9521105,c01,必修)实际插入的值为：(9521105,c01，NUL
26、L，必修),4.2.2 更新数据,用UPDATE语句实现。格式：UPDATE SET,n WHERE,无条件更新,例1.将所有学生的年龄加1。UPDATE Student SET Sage=Sage+1,有条件更新,1.基于本表条件的更新例2.将9512101学生的年龄改为21岁UPDATE Student SET Sage=21WHERE Sno=9512101,2.基于其他表条件的更新,例3：将计算机系全体学生的成绩加5分。（1）用子查询实现UPDATE SC SET Grade=Grade+5 WHERE Sno IN(SELECT Sno FROM Student WHERE Sdep
27、t=计算机系)（2）用多表连接实现UPDATE SC SET Grade=Grade+5 FROM SC JOIN Student ON SC.Sno=Student.Sno WHERE Sdept=计算机系,4.2.3 删除数据,用DELETE语句实现格式：DELETE FROM WHERE,无条件删除,例1.删除所有学生的选课记录。DELETE FROM SC,有条件删除,（1）基于本表条件的删除。例2删除所有不及格学生的修课记录。DELETE FROM SC WHERE Grade 60,基于其他表条件的删除,例3删除计算机系不及格学生的修课记录。（1）用子查询实现DELETE FROM SC WHERE Grade 60 AND Sno IN(SELECT Sno FROM Student WHERE Sdept=计算机系)（2）用多表连接实现DELETE FROM SC FROM SC JOIN Student ON SC.Sno=Student.SnoWHERE Sdept=计算机系AND Grade 60,
