数据库操作语句.ppt

《数据库操作语句.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,Sno,Sdep

2、t 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 有重复行！有重复行！要去掉要去掉结结果表中的重果表中的重复复行，可用行，可用DISTINCTDISTINCTSELECT DISTINCT Sno FROM SC 2.查询满足条件的元组 查询条件查询条件谓谓 词词比较运算符=,=,=,（或!=）NOT+比较运算符 确定范围BETWEENAND,NOT BETWEENAND确定集合IN,N

4、OT IN字符匹配LIKE,NOT LIKE 空值IS NULL,IS NOT NULL逻辑谓词）AND,OR比较大小例7查询计算机系全体学生的姓名。SELECT Sname FROM Student WHERE Sdept=计算机系例8查询年龄在20岁以下的学生的姓名及年龄。SELECT Sname,Sage FROM Student WHERE Sage 20例9查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade 60确定范围用BETWEENAND和NOT BETWEENAND是逻辑运算符，可以用来查找属性值在或不在指定范围内的

5、元组，其中BETWEEN后边指定范围的下限，AND后边指定范围的上限。BETWEENAND的格式为：列名|表达式 NOT BETWEEN 下限值 AND 上限值如果列或表达式的值在不在下限值和上限值范围内，则结果为True，表明此记录符合查询条件。示例例10查询年龄在2023岁之间的学生的姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23例11查询年龄不在2023之间的学生姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT

6、BETWEEN 20 AND 23确定集合使用IN运算符。用来查找属性值属于指定集合的元组。格式为：列名 NOT IN(常量1,常量2,常量n)当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；NOT IN：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录 示例例12查询信息系、数学系和计算机系学生的姓名和性别。SELECT Sname,Ssex FROM Student SELECT Sname,Ssex FROM Student WHERE Sdept WHERE Sdept ININ(信息系信息系,数学系数学系,计算

7、机系计算机系)例13查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。SELECT Sname,Ssex FROM Student SELECT Sname,Ssex FROM Student WHERE Sdept WHERE Sdept NOT INNOT IN(信息系信息系,数学系数学系,计算机系计算机系)字符匹配使用LIKE运算符一般形式为：列名 NOT LIKE 匹配串中可包含如下四种通配符：_：匹配任意一个字符；%：匹配0个或多个字符；：匹配 中的任意一个字符；对于连续字母的匹配，例如匹配abcd，可简写为a-d ：不匹配 中的任意一个字符 示例例14查询姓张的学生的详细信

8、息。SELECT*FROM Student SELECT*FROM Student WHERE Sname WHERE Sname LIKELIKE 张张%例15查询学生表中姓张、李和刘的学生的情况。SELECT*FROM Student SELECT*FROM Student WHERE Sname WHERE Sname LIKELIKE 张李刘张李刘%例16查询名字中第2个字为小或大的学生的姓名和学号。SELECT Sname,Sno FROM Student SELECT Sname,Sno FROM Student WHERE Sname WHERE Sname LIKELIKE _

9、 _小大小大%示例（续）例17查询所有不姓“王”也不姓“张”的学生姓名SELECT Sname FROM Student SELECT Sname FROM Student WHERE Sname WHERE Sname NOT LIKENOT LIKE 王张王张%或者：或者：SELECT Sname FROM Student SELECT Sname FROM Student WHERE Sname LIKE WHERE Sname LIKE 王张王张%或者：或者：SELECT Sname FROM Student SELECT Sname FROM Student WHERE Sname

10、NOT LIKE WHERE Sname NOT LIKE 王王%AND Sname NOT LIKE AND Sname NOT LIKE 张张%示例（续）例18查询姓“王”且名字是2个字的学生姓名。SELECT Sname FROM StudentSELECT Sname FROM Student WHERE Sname LIKE WHERE Sname LIKE 王王_ _ 示例（续）例19查询姓王且名字是3个字的学生姓名SELECT Sname FROM Student SELECT Sname FROM Student WHERE Sname LIKE WHERE Sname LIK

11、E 王王_ _ 注意：尾随空格的处理。注意：尾随空格的处理。SELECT Sname FROM Student SELECT Sname FROM Student WHERE rtrim(Sname)LIKE WHERE rtrim(Sname)LIKE 王王_涉及空值的查询空值（NULL）在数据库中表示不确定的值。例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。判断某个值是否为NULL值，不能使用普通的比较运算符。判断取值为空的语句格式为：列名 IS NULL判断取值不为空的语句格式为：列名 IS NOT NULL 示例例20查询没有考试成绩的学生的

12、学号和相应的课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL例21查询所有有考试成绩的学生的学号和课程号。SELECT Sno,Cno 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或

13、：SELECT Sname,Sdept,Sage FROM Student WHERE Sdept IN(计算机系,信息系)AND Sage=20 4.1.2 简单查询#3.对查询结果进行排序 对查询结果进行排序可对查询结果进行排序。排序子句为：ORDER BY ORDER BY ASC|DESC ,ASC|DESC ,说明：按进行升序（ASC）或降序（DESC）排序。示例例22将学生按年龄的升序排序。SELECT*FROM Student ORDER BY Sage例23查询选修了c02号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECT Sno,Grade FROM SCWHE

14、RE Cno=c02 ORDER BY Grade DESC 例24查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT*FROM StudentORDER BY Sdept,Sage DESC 4.1.2 简单查询4.使用计算函数汇总数据 使用计算函数汇总数据 SQL提供的计算函数有：COUNTCOUNT（*）：统计表中元组个数；COUNTCOUNT（DISTINCT DISTINCT ）：统计本列列值个数；SUMSUM（DISTINCT DISTINCT ）：计算列值总和；AVGAVG（DISTINCT DISTINCT ）：计算列值平均值；MAXM

15、AX（DISTINCT DISTINCT ）：求列值最大值；MINMIN（DISTINCT DISTINCT ）：求列值最小值。上述函数中除上述函数中除COUNTCOUNT（*）外，其他函数在计算）外，其他函数在计算过程中均忽略过程中均忽略NULLNULL值。值。示例例25统计学生总人数。SELECT COUNT(*)FROM Student 例26统计选修了课程的学生的人数。SELECT COUNT(DISTINCT Sno)FROM SC例27 计算9512101号学生的考试总成绩之和。SELECT SUM(Grade)FROM SC WHERE Sno=9512101 示例（续）例28计

16、算C01号课程学生的考试平均成绩。SELECT AVG(Grade)FROM SC WHERE Cno=C01例29查询选修了C01号课程的学生的最高分和最低分。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

17、课程号,COUNT(Sno)as 选课人数 FROM SC GROUP BY Cno 对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。SnoSnoCnoCnoGradeGrade951201951201C01C018080951201951201C02C027878951202951202C01C019090952103952103C02C028888952103952103C01C018585952103952103C03C039191952103952103C04C047474CnoCnoCount(Sno)Count(S

18、no)C01C013 3C02C022 2C03C031 1C04C041 1SnoSnoCnoCnoGradeGrade951201951201C0C01 18080951202951202C0C01 19090952103952103C0C01 18585951201951201C02C027878952103952103C02C028888952103952103C03C039191952103952103C04C047474例31查询每名学生的选课门数和平均成绩。SELECT Sno as 学号,COUNT(*)as 选课门数,AVG(Grade)as 平均成绩 FROM SC GRO

19、UP BY Sno2.使用HAVINGHAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。例32查询修了3门以上课程的学生的学号SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3 示例例33查询修课门数等于或大于4门的学生的平均成绩和选课门数。SELECT Sno,AVG(Grade)平均成绩,COUNT(*)修课门数 FROM SC GROUP BY Sno HAVING COUNT(*)=4 4.1.3 多表连接查询 若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询连

20、接查询包括内连接、外连接和交叉连接等。连接基础知识连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：2必须是可比的必须是可比的内连接SQL-92 内连接语法如下：SELECT FROM 表名 INNER JOIN 被连接表 ON 连接条件执行连接操作的过程：执行连接操作的过程：首先取表首先取表1 1中的第中的第1 1个元组，然后从头开始扫描表个元组，然后从头开始扫描表2 2，逐一查找满足连接条件的元组，逐一查找满足连接条件的元组，找到后就将表找到后就将表1 1中的第中的第1 1个元组与该元组拼接起来，个元组与该元组拼接起来，形成结果表中的一个元组。形成结果表中的一个元组。表表2

21、 2全部查找完毕后，再取表全部查找完毕后，再取表1 1中的第中的第2 2个元组，然个元组，然后再从头开始扫描表后再从头开始扫描表2 2，重复这个过程，直到表重复这个过程，直到表1 1中的全部元组都处理完毕中的全部元组都处理完毕为止。为止。例36.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。SELECT Sname,Cno,Grade FROM Student JOIN SC ON Student.Sno=SC.Sno WHERE Sdept=计算机系 例37.查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。SELECT Sname,Cname,

22、Grade FROM Student s JOIN SC ON s.Sno=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.查询与刘晨在同

23、一个系学习的学生的姓名和所在的系。SELECT S2.Sname,S2.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.

24、列名 表2.列名(+)例40.查询学生的修课情况，包括修了课程的学生和没有修课的学生。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 查询总是使用圆括号括起来。使用子查询进行基于集合的测试使用子查询

25、进行基于集合的测试的语句的一般格式为：列名 NOT IN(子查询)示例例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,S

26、name FROM Student WHERE Sno 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

27、)使用子查询进行存在性测试 一般使用EXISTS谓词。带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。例45.查询选修了c01号课程的学生姓名。SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=c01)注意注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。注2:由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。上句的处理过程1.找

28、外层表Student表的第一行，根据其Sno值处理内层查询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 ()VALU

29、ES（值表）功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序或列名表顺序赋给对应列名。注意值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。如果后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。示例例1将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中。INSERT INTO Student VALUES(9521105,陈冬,男,18,信息系)例2在SC表中插入一新记录，成绩暂缺。INSERT INTO SC(Sno,Cno,XKLB)VALUES(9521105,c01,必修)实际插入的值为

30、：(9521105,c01，NULL，必修)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 1）用子查询实现）用子查询实现UPDATE SC SET Grade=Grade+5UPDATE SC SET Grade=Grade+

31、5 WHERE Sno IN WHERE Sno IN(SELECT Sno FROM Student(SELECT Sno FROM Student WHERE Sdept=WHERE Sdept=计算机系计算机系)（2 2）用多表连接实现）用多表连接实现UPDATE SC SET Grade=Grade+5UPDATE SC SET Grade=Grade+5 FROM SC JOIN Student ON SC.Sno=FROM SC JOIN Student ON SC.Sno=Student.SnoStudent.Sno WHERE Sdept=WHERE Sdept=计计算机系算机

32、系 4.2.3 删除数据 用DELETE语句实现格式：DELETE FROM WHERE 无条件删除例1.删除所有学生的选课记录。DELETE FROM SC 有条件删除（1）基于本表条件的删除。例2删除所有不及格学生的修课记录。DELETE FROM SC WHERE Grade 60 基于其他表条件的删除例3删除计算机系不及格学生的修课记录。（1）用子查询实现DELETE FROM SCDELETE FROM SC WHERE Grade 60 AND Sno IN(WHERE Grade 60 AND Sno IN(SELECT Sno FROM Student SELECT Sno FROM Student WHERE Sdept=WHERE Sdept=计计算机系算机系 )（2）用多表连接实现DELETE FROM SC DELETE FROM SC FROM SC JOIN Student ON SC.Sno=Student.Sno FROM SC JOIN Student ON SC.Sno=Student.SnoWHERE Sdept=WHERE Sdept=计计算机系算机系 AND Grade 60AND Grade 60