数据库的增删改查课件.ppt

1、回顾回顾数据类型分哪两种类型数据类型分哪两种类型装箱和拆箱的关系装箱和拆箱的关系简单类型以及其字节数与范围简单类型以及其字节数与范围课程目标课程目标示例数据库示例数据库:学生学生-课程数据库课程数据库 学生表：学生表：Student(SnoStudent(Sno，SnameSname，SsexSsex，SageSage，Sdept)Sdept)课程表：课程表：Course(CnoCourse(Cno，Cname)Cname)学生选课表：学生选课表：SC(SnoSC(Sno，CnoCno，Grade)Grade)2.12.1添加数据添加数据使用使用insertinsert语句添加数据的一般格式：

2、语句添加数据的一般格式：insert into insert into 列名列名 values values 省略列名省略列名-指定值的顺序必须与表中的列顺序一致指定值的顺序必须与表中的列顺序一致只有部分列包含值只有部分列包含值insert into 员工信息表员工信息表(姓名姓名,性别性别,职务职务)values(张三张三,男男,经理经理)insert into 员工信息表员工信息表values(张三张三,经理经理,男男)insert into 员工信息表员工信息表(姓名姓名,职务职务)values(李四李四,null)使用使用select into select into 语句复制表的结构

3、和数据语句复制表的结构和数据 select select*into into 普通职员表普通职员表from from 员工信息表员工信息表where where 职务职务=职员职员 select intoselect into和和insert intoinsert into都可以添加多行数据都可以添加多行数据,两者的两者的区别区别 insert into insert into 语句中用于添加数据的目标表要求已经语句中用于添加数据的目标表要求已经存在存在 select into select into 语句会创建新表语句会创建新表,要求不存在同名的表要求不存在同名的表2.22.2更新数据更新数

4、据updateupdate语句语句:可更新表中单行可更新表中单行,行组或所有行的数据行组或所有行的数据一般格式为：一般格式为：update update 表名表名 set set 列名列名1=1=新值新值1,1,列名列名2=2=新值新值2 where 2 where 条件条件 update update 员工信息表员工信息表set set 职务职务=职员职员 where where 姓名姓名=张三张三 update update 图书信息图书信息set set 库存数量库存数量=库存数据库存数据+20+20where where 出版社编号出版社编号=1=12.32.3删除数据删除数据 使用使

5、用deletedelete语句删除行语句删除行 delete from delete from 表名表名 where where 条件条件delete from delete from 教师信息表教师信息表where where 职务职务 is null is null 注意注意:如果如果deletedelete语句没有语句没有wherewhere条件条件,则删除表中所有行则删除表中所有行,但保留表的结构但保留表的结构delete delete 新职员表新职员表 使用使用truncate tabletruncate table删除表中所有行删除表中所有行truncate table trunc

6、ate table 新职员表新职员表 用用truncatetruncate和和deletedelete都可以删除表的所有行都可以删除表的所有行,两者的区别两者的区别truncatetruncate语句不写日志语句不写日志,执行速度比执行速度比deletedelete语句快语句快,但但不可回滚不可回滚数据查询是数据库中最常见的操作。数据查询是数据库中最常见的操作。SQLSQL语言提供语言提供SELECTSELECT语语 句，通过查询操作可得到所需的信息。句，通过查询操作可得到所需的信息。SELECTSELECT语句的一般格式为：语句的一般格式为：2.42.4查询数据查询数据SELECT SELE

7、CT FROM FROM WHERE WHERE GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY ASCASC或或DESCDESC SELECTSELECT语句的执行过程是语句的执行过程是:根据根据WHEREWHERE子句的检索条件，从子句的检索条件，从FROMFROM子句指定的基本表子句指定的基本表或视图中选取满足条件的元组，再按照或视图中选取满足条件的元组，再按照SELECTSELECT子句中指定子句中指定的列，投影得到结果表。的列，投影得到结果表。如果有如果有GROUPGROUP子句，则将查询结果按照子句，则将查询结果按照 1相同的值相

8、同的值进行分组。进行分组。如果如果GROUPGROUP子句后有子句后有HAVINGHAVING短语，则只输出满足短语，则只输出满足HAVINGHAVING条件的元组。条件的元组。如果有如果有ORDERORDER子句，查询结果还要按照子句，查询结果还要按照 2的值进行的值进行排序。排序。例3-23 查询全体学生的学号、姓名和年龄。SELECT SNo,SN,AgeFROM S 例3-24 查询学生的全部信息。SELECT*FROM S 例3-25 查询选修了课程的学生号。SELECT DISTINCT SNo FROM SC例3-26 查询全体学生的姓名、学号和年龄。SELECT SN Name

9、,SNo,Age FROM SSELECT SN AS Name,SNo,Age2.4.2 2.4.2 条件查询条件查询运算符含义=,=,=,!=,比较大小AND,OR,NOT多重条件BETWEEN AND确定范围IN确定集合LIKE字符匹配IS NULL空值2.4.2.1 2.4.2.1 比较大小比较大小 例例2.5 2.5 查询选修课程号为查询选修课程号为C1C1的学生的学号和成绩的学生的学号和成绩 SELECT SNO,SCORE SELECT SNO,SCORE FROM SC FROM SC WHERE WHERE CNO=C1CNO=C1 例例2.6 2.6 查询成绩高于查询成绩高

10、于8585分的学生的学号、课程号和成绩。分的学生的学号、课程号和成绩。SELECT SNO,CNO,SCORE SELECT SNO,CNO,SCORE FROM SC FROM SC WHERE WHERE SCORE85SCORE85 2.4.2.2 2.4.2.2 多重条件查询多重条件查询 当当WHEREWHERE子句需要指定一个以上的查询条件时，则需要使子句需要指定一个以上的查询条件时，则需要使用逻辑运算符用逻辑运算符ANDAND、OROR和和NOTNOT将其连结成复合的逻辑表达式。将其连结成复合的逻辑表达式。其优先级由高到低为：其优先级由高到低为：NOTNOT、ANDAND、OROR

11、，用户可以使用括号，用户可以使用括号改变优先级。改变优先级。例例2.7 2.7 查询选修查询选修C1C1或或C2C2且分数大于等于且分数大于等于8585分学生的的学号、分学生的的学号、课程号和成绩。课程号和成绩。SELECT SNOSELECT SNO，CNOCNO，SCORE SCORE FROM SC FROM SC WHERE WHERE（CNO=C1 CNO=C1 OROR CNO=C2 CNO=C2 ANDAND SCORE=85 SCORE=85）)2.4.2.3 2.4.2.3 确定范围确定范围例例2.82.8查询工资在查询工资在10001000至至15001500间的教师的教师

12、号姓名及职称间的教师的教师号姓名及职称SELECT TNO,TN,PROF SELECT TNO,TN,PROF FROM T FROM T WHERE SAL WHERE SAL BETWEENBETWEEN 1000 1000 ANDAND 1500 1500 等价于等价于 SELECT TNO,TN,PROFSELECT TNO,TN,PROFFROM T FROM T WHERE WHERE SAL1000 AND SAL1000 AND SAL1500 例例3.93.9查询工资不在查询工资不在10001000至至15001500之间的教师的教师号、姓名之间的教师的教师号、姓名及职称。

13、及职称。SELECT TNO,TN,PROFSELECT TNO,TN,PROFFROM T FROM T WHERE SAL WHERE SAL NOT BETWEENNOT BETWEEN 1000 1000 ANDAND 1500 1500 2.4.2.42.4.2.4确定集合确定集合利用利用“IN”IN”操作可以查询属性值属于指定集合的元组操作可以查询属性值属于指定集合的元组。例例3.10 3.10 查询选修查询选修C1C1或或C2C2的学生的学号、课程号和成绩。的学生的学号、课程号和成绩。SELECT SNO,CNO,SCORE SELECT SNO,CNO,SCORE FROM S

14、C FROM SC WHERE CNO WHERE CNO ININ(C1,C2)(C1,C2)此语句也可以使用逻辑运算符此语句也可以使用逻辑运算符“OR”OR”实现。实现。SELECT SNO,CNO,SCORE SELECT SNO,CNO,SCORE FROM SC FROM SC WHERE CNO=C1 WHERE CNO=C1 OROR CNO=C2 CNO=C2 利用利用“NOT IN”NOT IN”可以查询指定集合外的元组。可以查询指定集合外的元组。例例2.11 2.11 查询没有选修查询没有选修C1C1，也没有选修，也没有选修C2C2的学生的学号、课的学生的学号、课程号和成绩

15、。程号和成绩。SELECT SNO,CNO,SCORE SELECT SNO,CNO,SCORE FROM SC FROM SC WHERE CNO NOT IN(C1,C2)WHERE CNO NOT IN(C1,C2)等价于：等价于：SELECT SNO,CNO,SCORE SELECT SNO,CNO,SCORE FROM SC FROM SC WHERE CNO!=C1 AND CNO!=C2 WHERE CNO!=C1 AND CNO!=C2 最后一行也可以写成：最后一行也可以写成：WHERE WHERE（CNOC1)AND(CNOC2)CNOC1)AND(CNOC2)2.4.2.5

16、 2.4.2.5 部分匹配查询部分匹配查询 上例均属于完全匹配查询，当不知道完全精确的値时，用上例均属于完全匹配查询，当不知道完全精确的値时，用户还可以使用户还可以使用LIKELIKE或或NOT LIKENOT LIKE进行部分匹配查询（也称模进行部分匹配查询（也称模糊查询）。糊查询）。LIKELIKE定义的一般格式为：定义的一般格式为：LIKE LIKE 属性名必须为字符型属性名必须为字符型，字符串常量的字符可以包含如下两，字符串常量的字符可以包含如下两个特殊符号：个特殊符号：通配符功能实例%代表0个或多个字符ab%,ab后可连接任意字符串_下划线代表一个字符a_b,a与b间可以有一个字符表

17、示在某一范围内的字符0-9,09之间的字符表示不在某一范围内的字符0-9,不在09之间的字符%：表示任意知长度的字符串；表示任意知长度的字符串；_ _：表示任意单个字符。表示任意单个字符。例例3.12 3.12 查询所有姓张的教师的教师号和姓名。查询所有姓张的教师的教师号和姓名。SELECT TNO,TN SELECT TNO,TN FROM T WHERE TN FROM T WHERE TN LIKE LIKE 张张%例例3.13 3.13 查询姓名中第二个汉字是查询姓名中第二个汉字是“力力”的教师号和姓名。的教师号和姓名。SELECT TNO,TN SELECT TNO,TN FROM

18、T FROM T WHERE TN LIKE _ WHERE TN LIKE _力力%2.4.2.6 2.4.2.6 空值查询空值查询 某个字段没有值称之为某个字段没有值称之为具有空值（具有空值（NULLNULL）。）。通常没有为一个通常没有为一个列输入值时，该列的值就是空值。列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间空值不同于零和空格，它不占任何存储空间。例如，某些。例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。考试成绩为空值，这与参加考试，成绩为零分

19、的不同。例例3.14 3.14 查询没有考试成绩的学生的学号和相应的课程号。查询没有考试成绩的学生的学号和相应的课程号。SELECT SNO,CNO SELECT SNO,CNO FROM SC FROM SC WHERE SCORE IS NULL WHERE SCORE IS NULL 注意：这里的空值条件为注意：这里的空值条件为IS NULLIS NULL，不能写成，不能写成SCORE=NULLSCORE=NULL。2.4.3 2.4.3 常用库函数及统计汇总查询常用库函数及统计汇总查询SQLSQL提供了许多库函数增强了基本检索能力。提供了许多库函数增强了基本检索能力。常用的常用的库函数

20、库函数，如表，如表3.23.2所示：所示：函数名称功能AVG按列计算平均值SUM按列计算值的总和MAX求一列中的最大值MIN求一列中的最小值COUNT按列值同济个数例例3.15 3.15 求学号为求学号为S1S1学生的总分和平均分。学生的总分和平均分。SELECT SELECT SUM(SCORE)SUM(SCORE)AS TotalScore,AVG(SCORE)AS AveScoreAS TotalScore,AVG(SCORE)AS AveScoreFROM SC FROM SC WHERE(SNO=S1)WHERE(SNO=S1)注意：函数注意：函数SUMSUM和和AVGAVG只能对只

21、能对数值型字段数值型字段进行计算。进行计算。例例2.162.16求选修求选修C1C1号课程的最高分、最低分及之间相差的分数号课程的最高分、最低分及之间相差的分数 SELECT MAX(SCORE)AS MaxScore,MIN(SCORE)AS SELECT MAX(SCORE)AS MaxScore,MIN(SCORE)AS MinScore,MAX(SCORE)-MIN(SCORE)AS Diff MinScore,MAX(SCORE)-MIN(SCORE)AS Diff FROM SC FROM SC WHERE(CNO=C1)WHERE(CNO=C1)例例3.17 3.17 求计算机系

22、学生的总数求计算机系学生的总数 SELECT COUNT(SELECT COUNT(*)FROM S FROM S WHERE DEPT=WHERE DEPT=计算机计算机 例例3.18 3.18 求学校中共有多少个系求学校中共有多少个系 SELECT COUNT(DISTINCT DEPT)AS DeptNum SELECT COUNT(DISTINCT DEPT)AS DeptNum FROM S FROM S 注意：加入关键字注意：加入关键字DISTINCTDISTINCT后表示消去重复行，可计算字段后表示消去重复行，可计算字段“DEPT“DEPT“不不同值的数目。同值的数目。COUNT

23、COUNT函数对空值不计算，但对零进行计算函数对空值不计算，但对零进行计算。例例2.192.19统计有成绩同学的人数统计有成绩同学的人数 SELECT COUNT(DISTINCT SNO)SELECT COUNT(DISTINCT SNO)FROM SC FROM SC WHERE SCORE IS NOT NULL WHERE SCORE IS NOT NULL 上例中成绩为零的同学计算在内，没有成绩（即为空值）上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。的不计算。例例2.20 2.20 利用特殊函数利用特殊函数COUNT(COUNT(*)求计算机系学生的总数求计算机系学生

24、的总数 SELECT COUNT(SELECT COUNT(*)FROM S FROM S WHERE DEPT=WHERE DEPT=计算机计算机 COUNTCOUNT（*）用来统计元组的个数，不消除重复行，不允许）用来统计元组的个数，不消除重复行，不允许使用使用DISTINCTDISTINCT关键字。关键字。2.4.4 2.4.4 分组查询分组查询GROUP BYGROUP BY子句可以将查询结果按属性列或属性列组合在子句可以将查询结果按属性列或属性列组合在行的行的方向方向上进行分组，每组在属性列或属性列组合上具有相同上进行分组，每组在属性列或属性列组合上具有相同的值。的值。例例3.21

25、3.21 查询各位教师的教师号及其任课的门数。查询各位教师的教师号及其任课的门数。SELECT TNO,COUNT(SELECT TNO,COUNT(*)AS C_NUM)AS C_NUM FROM TC G FROM TC G ROUP BY TNO ROUP BY TNO GROUP BYGROUP BY子句按子句按TNOTNO的值分组，所有具有相同的值分组，所有具有相同TNOTNO的元组为一的元组为一组，对每一组使用函数组，对每一组使用函数COUNTCOUNT进行计算，统计出各位教师任进行计算，统计出各位教师任课的门数。课的门数。若在分组后还要按照一定的条件进行筛选，则需使用若在分组后还

26、要按照一定的条件进行筛选，则需使用HAVINHAVIN子句。子句。例例2.22 2.22 查询选修两门以上课程的学生学号和选课门数查询选修两门以上课程的学生学号和选课门数 SELECT SNO,COUNT(SELECT SNO,COUNT(*)AS SC_NUM )AS SC_NUM FROM SC FROM SC GROUP BY SNO GROUP BY SNO HAVING COUNT(HAVING COUNT(*)=2 )=2 GROUP BYGROUP BY子句按子句按SNOSNO的值分组，所有具有相同的值分组，所有具有相同SNOSNO的元组为的元组为一组，对每一组使用函数一组，对每

27、一组使用函数COUNTCOUNT进行计算，统计出每位学生进行计算，统计出每位学生选课的门数。选课的门数。HAVINGHAVING子句去掉不满足子句去掉不满足COUNTCOUNT（*）=2=2的组。的组。当在一个当在一个SQLSQL查询中同时使用查询中同时使用WHEREWHERE子句，子句，GROUP BY GROUP BY 子句子句和和HAVINGHAVING子句时，其顺序是子句时，其顺序是WHEREWHEREGROUP BYGROUP BYHAVINGHAVING。WHEREWHERE与与HAVINGHAVING子句的根本区别子句的根本区别在于作用对象不同：在于作用对象不同：WHEREWHE

28、RE子子句作用于句作用于基本表或视图基本表或视图，从中选择满足条件的元组；，从中选择满足条件的元组；HAVINGHAVING子句作用于子句作用于组组，选择满足条件的组，必须用于，选择满足条件的组，必须用于GROUP BYGROUP BY子句子句之后，但之后，但GROUP BYGROUP BY子句可没有子句可没有HAVINGHAVING子句。子句。2.4.5 2.4.5 查询的排序查询的排序当需要对查询结果排序时，应该使用当需要对查询结果排序时，应该使用ORDER BYORDER BY子句子句 ORDERORDERBYBY子句必须出现在其他子句之后。排序方式可以指定，子句必须出现在其他子句之后。

29、排序方式可以指定，DESCDESC为降序，为降序，ASCASC为升序为升序，缺省时为升序，缺省时为升序 例例3.23 3.23 查询选修查询选修C1 C1 的学生学号和成绩，并按成绩降序排列。的学生学号和成绩，并按成绩降序排列。SELECT SNO,SCORE SELECT SNO,SCORE FROM SC FROM SC WHERE CNO=C1 WHERE CNO=C1 ORDER BY SCORE DESC ORDER BY SCORE DESC 例例3.243.24查询选修查询选修C2C2、C3C3、C4C4或或C5C5课程的学号、课程号和成课程的学号、课程号和成绩，查询结果按学号升

30、序排列，学号相同再按成绩降序排列。绩，查询结果按学号升序排列，学号相同再按成绩降序排列。SELECT SNO,CNO,SCORE SELECT SNO,CNO,SCORE FROM SC FROM SC WHERE CNO IN(C2,C3,C4,C5)WHERE CNO IN(C2,C3,C4,C5)ORDER BY ORDER BY SNOSNO,SCORESCORE DESC DESC 例例2.25 2.25 求选课在三门以上且各门课程均及格的学生的学号求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。及其总成绩，查询结果按总成绩降序列出。SELECT S

31、NO,SUM(SCORE)AS TotalScore SELECT SNO,SUM(SCORE)AS TotalScore FROM SC FROM SC WHERE SCORE=60 WHERE SCORE=60 GROUP BY SNO HAVING COUNT(GROUP BY SNO HAVING COUNT(*)=3 )=3 ORDER BY SUM(SCORE)DESC ORDER BY SUM(SCORE)DESC 此语句为分组排序，执行过程如下此语句为分组排序，执行过程如下：1.1.（FROMFROM）取出整个）取出整个SC SC 2.2.（WHEREWHERE）筛选）筛选SC

32、ORE=60SCORE=60的元组的元组 3.3.（GROUP BYGROUP BY）将选出的元组按）将选出的元组按SNOSNO分组分组 4.4.（HAVINGHAVING）筛选选课三门以上的分组）筛选选课三门以上的分组 5.5.（SELECTSELECT）以剩下的组中提取学号和总成绩）以剩下的组中提取学号和总成绩 6.6.（ORDER BYORDER BY）将选取结果排序）将选取结果排序 练习查询年龄不在查询年龄不在20-2320-23岁之间的学生姓名、系别和年龄。岁之间的学生姓名、系别和年龄。SELECT SnameSELECT Sname，SdeptSdept，SageSageFROM

33、StudentFROM StudentWHERE Sage NOT BETWEEN 20 AND 23WHERE Sage NOT BETWEEN 20 AND 23；书店来了一位新员工杜康书店来了一位新员工杜康,职务为部门主管职务为部门主管,如何向员如何向员工信息表工信息表(员工编号员工编号,姓名姓名,职务职务)中添加他的信息中添加他的信息?杜康从部门主管升为部门经理杜康从部门主管升为部门经理,如何更新他的信息如何更新他的信息?杜康离职了杜康离职了,如何删除他的信息如何删除他的信息?在一个在一个SQLSQL语句中，语句中，group bygroup by、wherewhere、havinghaving这三部分正这三部分正确使用次序是什么？确使用次序是什么？select intoselect into和和insert intoinsert into者可以添加多行数据者可以添加多行数据,两者的两者的区别是什么区别是什么?用用truncatetruncate和和deletedelete都可以删除表的所有行都可以删除表的所有行,两者的区别两者的区别是什么是什么?wherewhere和和havinghaving的作用都是筛选数据的作用都是筛选数据,它们的它们的 区别是什么区别是什么?小结3.3 变量3.3 变量与常量3.3 变量与常量3.3 变量与常量3.3 变量与常量