数据库技术与应用单元5 查询和更新数据.ppt

单元5 查询和更新数据,引言,数据库、表创建好后，接下来的工作是对数据进行操作，包括查询数据、插入数据、修改数据和删除数据等。数据操作是数据库工程师和数据库相关岗位人员日常工作中必做的也是最频繁的工作。,【学习任务】,任务1 单表查询任务2 数据汇总统计任务3 多表连接查询任务4 数据更新任务5 使用图形工具进行数据操作,【学习目标】,理解SELECT语句的语法格式；能根据实际需求对单表或多表进行数据查询，熟练编写SELECT语句；能对数据进行汇总计算、分组统计；能熟练使用SQL语句对数据进行更新；能使用图形工具进行数据操作。,任务1,任务1 单表查询,【任务提出】,数据库中最常见的操作是数据查询，可以说数据查询是数据库的核心操作。查询可以对单表进行查询，也可以完成复杂的连接查询和嵌套查询，其中对单表进行查询是数据查询操作中最简单的，所以我们先从单表查询入手学习数据查询操作。,【任务分析】,实现数据查询操作必须使用SQL语言中的SELECT语句。所以先学习和理解SELECT语句，然后针对实际需求对表进行查询。,【相关知识与技能】,1单表查询的SELECT语句SELECT ALL|DISTINCT 目标列表达式 FROM 表名 WHERE 行条件表达式 ORDER BY 排序列 ASC|DESC,【相关知识与技能】,2SELECT语句的含义根据WHERE子句的行条件表达式，从FROM子句指定的表中找出满足条件的行（记录），再按SELECT子句中的列名或表达式选出记录中的字段值形成查询结果。如果有ORDER BY子句，则查询结果还要按照排序列的值进行升序或降序排列。,【任务实施】,1选择表中的若干列（1）查询部分列格式：SELECT 列名，n FROM 表名【例1】查询所有学生的学号和姓名。SELECT Sno,Sname FROM Student【练习1】查询所有课程的课程编号、课程名称和课程学分。,【任务实施】,（2）查询全部列格式：SELECT*FROM 表名【例2】查询全体学生的详细信息。SELECT*FROM Student【练习2】查询所有班级的详细信息。,【任务实施】,（3）为查询结果集内的列指定别名格式1：SELECT 原列名 AS 列别名,n FROM 表名格式2：SELECT 原列名 列别名,n FROM 表名格式3：SELECT 列别名=原列名,n FROM 表名【例3】查询所有学生的学号和姓名，并指定别名为学生学号、学生姓名。SELECT Sno 学生学号,Sname 学生姓名 FROM Student【练习3】查询所有班级的详细信息，并给查询结果各列指定中文意义的别名。,【任务实施】,（4）查询经过计算的列格式：SELECT 计算表达式或列名 FROM 表名【例4】查询所有学生的学号、姓名和出生年份。SELECT Sno,Sname,YEAR(Birth)出生年份 FROM Student【练习4】查询所有学生的学号、姓名和年龄。,【任务实施】,2选择表中的若干行（1）查询满足条件的记录格式：SELECT 目标列表达式 FROM 表名 WHERE 行条件表达式,【任务实施】,1）比较大小【例5】查询所有女生的学号和姓名。SELECT Sno,Sname FROM Student WHERE Sex=女【练习5】查询课程学时超过50学时的课程号和课程名称。【练习6】查询所有在1992年5月10日后（包含1992年5月10日）出生的学生的详细信息。【练习7】查询年龄小于20岁的学生的学号、姓名和出生年月。查询结果应如图5-12所示。,【任务实施】,2）确定范围列名或计算表达式 NOT BETWEEN 下限值 AND 上限值【例6】查询平时成绩在90100之间（包含90和100）的学号和课程编号。SELECT Sno,Cno FROM Score WHERE Uscore BETWEEN 90 AND 100【练习8】查询出生年月在1991年1月1日至1991年5月30日之间的学生的学号和姓名。,【任务实施】,3）确定集合列名或表达式 NOT IN(常量1,常量2,常量n）【例7】查询课程学时为30或60的课程的课程编号和课程名称。SELECT*FROM Course WHERE ClassHour IN(30,60)【练习9】查询所属专业为计算机应用技术、软件技术的班级的班级编号、班级名称及入学年份。,【任务实施】,4）字符匹配列名 NOT LIKE【例8】查询所有姓陈的学生的学号和姓名。SELECT Sno,Sname FROM Student WHERE Sname LIKE 陈%【练习10】查询所有姓陈且名为单个字的学生的学号和姓名。【练习11】查询所有课程名称中含有数据库的课程的课程编号、课程名称。【练习12】查询学生姓名中姓张、林、江的学生的学号和姓名。,【任务实施】,5）涉及空值列名 IS NOT NULL【例9】查询期末成绩现为空的学生的学号和课程编号。SELECT Sno,Cno FROM Score WHERE EndScore IS NULL,【任务实施】,（2）消除取值重复的行SELECT DISTINCT 目标列表达式 FROM 表名【例10】查询期末成绩有不及格的学生的学号。SELECT DISTINCT Sno FROM Score WHERE EndScore60【练习13】查询所有有选课记录的学生的学号。,【任务实施】,（3）限制返回行数SELECT TOP 指定的行数 PERCENT 目标列表达式 FROM 表名【例11】查询返回学生表中的最前面2条记录作为样本数据显示。SELECT TOP 2*FROM Student,【任务实施】,3对查询结果排序ORDER BY 排序列名 ASC|DESC【例12】查询所有学生的详细信息，查询结果按照出生年月降序排列。SELECT*FROM Student ORDER BY Birth DESC,【任务实施】,【练习14】查询选修了课程编号为0901170的课程的学生的学号及其平时成绩，查询结果按照平时成绩升序排列。【练习15】查询所有学生的详细信息，查询结果按照班级编号升序排列，对同一个班的学生按照学号升序排列。【练习16】查询所有学生中年龄最大的那位学生的学号和姓名。,【任务实施】,4多重条件查询【例13】查询班级编号为200901001的班中所有男生的详细信息，查询结果按照学号升序排列。SELECT*FROM Student WHERE ClassNo=200901001 AND SEX=男 ORDER BY Sno ASC【练习17】查询课程的平时成绩或期末成绩超过90分的学生的学号和课程编号，查询结果按照学号升序排列，学号相同的按照课程编号降序排列。【练习18】查询出年龄超过20岁女生的详细信息。,【任务总结】,行条件表达式中的归纳注意点如下：表达式中的字符型常量必须用单引号括起来，但字段名不能用单引号括起来；日期时间型常量须用单引号括起来。如1992年5月10日可使用以下任一格式表示：1992-05-10、1992/05/10、05/10/1992、19920510。范围运算符BETWEENAND的语法格式为：列名 BETWEEN 下限值 AND 上限值；,【任务总结】,列表运算符IN的语法格式为：列名 IN(常量1,常量2,常量n)；模式匹配符LIKE的语法格式为：列名 LIKE；空值判断符IS NULL的语法格式为：列名 IS NULL，不要写成：列名=NULL；如果有多个条件，须使用AND或OR连接。切记出现如下表达式90=Uscore=100。,【拓展练习】,1从Dorm表中查询所有宿舍的详细信息。2从Live表中查询学号为200931010100101学生的住宿信息，包含宿舍编号DormNo、床位号BedNo和入住日期InDate。3从Dorm表中查询所有男生宿舍（宿舍类别DormType为男）的详细信息，结果按照楼栋Build升序排列，楼栋相同的按照宿舍编号DormNo升序排列。4从Live表中查询在2010年9月份入住宿舍的学生的学号Sno、宿舍编号DormNo和床位号BedNo。,【拓展练习】,5从CheckHealth表中查询宿舍编号DormNo为LCB04N101宿舍在2010年10月份的卫生检查情况，结果包含检查时间CheckDate、检查人员CheckMan、成绩Score和存在问题Problem。6从CheckHealth表中查询在2010年10月1日至2010年11月30日之间宿舍卫生检查成绩Score在7080分（包含70、80分）之间的宿舍编号DormNo、检查时间CheckDate和存在问题Problem。7从Dorm表中查询在龙川南苑的宿舍详细信息。（在龙川南苑指楼栋Build包含龙川南苑）。,【拓展练习】,8从Dorm表中查询宿舍电话Tel目前为空的宿舍的宿舍编号DormNo、楼栋Build、楼层Storey和房间号RoomNo。9从Student表中查询所有学生的学号Sno、姓名Sname和年龄，查询结果按照年龄降序排列。10从CheckHealth表中查询2010年10月卫生检查成绩Score最高的宿舍编号DormNo和检查时间CheckDate。,任务2,任务2 数据汇总统计,【任务提出】,在对表数据进行查询中，经常会对数据进行统计计算，如统计个数、平均值、最大最小值、计算总和等操作。另外，还会根据需要对数据进行分开统计汇总，如统计各个班级的人数等操作。,【任务分析】,SQL提供了许多集函数对数据进行各种统计计算。若需要对数据进行分组统计计算，GROUP BY子句就能够实现这种分组统计。,【相关知识与技能】,1集函数,【相关知识与技能】,2分组统计GROUP BY 分组列名3对组筛选HAVING 组筛选条件表达式区分HAVING子句和WHERE子句:HAVING子句是对GROUP BY分组后的组进行筛选，选择出满足条件的组；而WHERE子句是对表中记录进行选择，选择出满足条件的行。HAVING子句中可以使用集函数，一般HAVING子句中的组筛选条件就是集函数。而WHERE子句中绝对不能出现集函数。,【任务实施】,1使用集函数汇总数据【例1】统计Student表中学生的记录数。SELECT COUNT(*)学生记录数 FROM Student【例2】统计出信息工程学院的专业个数。SELECT COUNT(DISTINCT Specialty)信息工程学院专业个数 FROM Class WHERE College=信息工程学院,【任务实施】,【练习1】查询学号为200931010100101学生的所有选修课程的平时成绩的总分和平均分。【练习2】查询课程编号为2003003课程的学生期末成绩的最高分和最低分。,【任务实施】,2进行分组统计【例3】统计各班级学生人数。SELECT ClassNo,COUNT(Sno)班级人数 FROM Student GROUP BY ClassNo【练习3】统计各门课程的选课人数。【练习4】统计各门课程学生的平时成绩平均分、期末成绩平均分。,【任务实施】,3对组进行筛选【例4】查询出课程选课人数超过2人的课程编号。SELECT Cno FROM Score GROUP BY Cno HAVING COUNT(Sno)2【练习5】查询出所有选修课程的平均期末成绩小于50分的学生学号。,【任务总结】,若要对数据库表中数据进行统计计算，可使用集函数。若要对数据进行分组统计计算，使用GROUP BY子句。若在表中数据分组后还要对这些组按条件进行筛选，输出满足条件的组，则使用HAVING子句。SELECT ALL|DISTINCT 目标列表达式 FROM 表名 WHERE 行条件表达式 GROUP BY 分组列名 HAVING 组筛选条件表达式 ORDER BY 排序列 ASC|DESC,【拓展练习】,1从Dorm表中查询所有男宿舍的总床位数。男宿舍指宿舍类别DormType值为男。2从CheckHealth表中查询宿舍编号为LCB04N101宿舍的被检查人员检查的次数。3从CheckHealth表中查询2010年11月份各宿舍的检查成绩的平均值。4从Student表中查询目前男生的人数。5从Student表中查询目前男女生的人数。,【拓展练习】,6从Dorm表中查询出各楼栋的房间数。7从Live表中统计各个宿舍的现入住人数。8从CheckHealth表中统计各宿舍到目前为止的卫生检查的平均成绩。9从CheckHealth表中查询出到目前为止的卫生检查平均成绩超过90分的宿舍编号。10从CheckHealth表中查询宿舍被检查次数超过3次的宿舍编号。,任务3,任务3 多表连接查询,【任务提出】,前面任务1中完成的查询只涉及到一张表。而在实际使用中，查询往往是针对多个表进行的，可能涉及二张或更多张表。例：查询某个班级所有学生数据库原理与技术课程的成绩。,【任务分析】,在关系型数据库中，将这种涉及到两个或两个以上表的查询，称为多表连接查询。连接查询是关系数据库中最重要的查询。连接查询根据返回的连接记录情况，分为“交叉连接”、“内连接”和“外连接”查询。,【相关知识与技能】,1交叉连接交叉连接是将连接的表的所有行进行组合。如两张表进行交叉连接，就是将第一张表的所有记录分别与第二张表的每条记录形成一条新的记录，连接后的结果集中的记录数为两个表的记录数的乘积。其语法格式是：SELECT 目标列表达式 FROM 表名1 CROSS JOIN 表名2【例1】交叉连接Student和Score表。SELECT*FROM Student CROSS JOIN Score从查询结果中可以看出，交叉连接后的结果集中的记录数为两个表的记录数的乘积。而结果中的记录其实并没有意义。所以交叉连接在实际应用中一般是没有意义的，所以使用较少。,【相关知识与技能】,2内连接内连接查询是返回多个表中满足连接条件的记录。根据连接条件中运算符的不同，分为等值连接查询和非等值连接查询。其中等值连接是实际应用中最常见的。等值连接条件通常采用“主键列=外键列”的形式。其一般格式为：FROM 表名1，表名2 WHERE FROM 表名1 INNER JOIN 表名2 ON【例2】连接Student和Score表，返回两张表中满足Sno相同的记录。SELECT*FROM Student JOIN Score ON Student.Sno=Score.Sno,【相关知识与技能】,【例3】查询所有学生的详细信息及其选课信息，查询结果包含两张表中的所有列，但去除重复列。SELECT Student.*,Cno,UScore,EndScoreFROM Student JOIN Score ON Student.Sno=Score.Sno例3中的查询，按照两个表中的相同字段进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，将这类等值连接称为自然连接。,【相关知识与技能】,在内连接查询中，只有满足连接条件的记录才能作为结果输出，但有时用户也希望输出那些不满足连接条件的记录信息.如在上述例3的Student表和Score表的连接，在图5-21的查询结果中没有关于200931010100208学生的信息，原因在于他没有选课，在Score表中没有相应的记录。但是有时我们想以Student表为主体列出每个学生的详细信息及其课程成绩信息，若某个学生没有选课，则只输出他的详细信息，他的课程成绩信息为空值即可。这就需要使用外连接。外连接查询是除返回内部连接的记录以外，还在查询结果中返回左表或右表或左右表中不符合条件的记录。根据连接时保留表中记录的侧重不同分为“左外连接”、“右外连接”和“全外连接”。,【相关知识与技能】,（1）左外连接左外连接是将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回左表中不符合条件的记录，并在右表的相应列中填上NULL。左外连接的一般格式为：FROM 表名1 LEFT OUTER JOIN 表名2 ON【例4】查询所有学生的详细信息及其选课信息，如果学生没有选课，也显示其详细信息。SELECT Student.*，Cno,UScore,EndScoreFROM Student LEFT JOIN Score ON Student.Sno=Score.Sno,【相关知识与技能】,（2）右外连接和左外连接类似，右外连接将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回右表中不符合条件的记录，并在左表的相应列中填上NULL。右外连接的一般格式为：FROM 表名1 RIGHT OUTER JOIN 表名2 ON 可将上述例4的左外连接修改为右外连接来实现。SELECT Student.*,Cno,UScore,EndScoreFROM Score RIGHT JOIN Student ON Student.Sno=Score.Sno,【相关知识与技能】,（3）全外连接全外连接将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回左右两个表中不符合条件的记录，并在左表或右边的相应列中填上NULL。全外连接的一般格式为：FROM 表名1 FULL OUTER JOIN 表名2 ON,【任务实施】,在实际应用中的连接查询一般为内连接查询，而等值连接是实际应用中最常见的，所以下面对内连接进行。1两张表的连接【例5】查询所有学生相关信息，包含学号、姓名、班级编号、班级姓名。SELECT Sno,Sname,Classno,ClassnameFROM Student JOIN Class ON Student.Classno=Class.Classno注：对于在查询引用的多个表中重复的列名必须用表名指定，即表名列名。,【任务实施】,【练习1】查询所有学生选修课程的详细信息，结果包含学号、课程编号、课程名称、课程学分、平时成绩、期末成绩。,【任务实施】,【例6】查询计算机093班学生的学号和姓名。SELECT Sno,SnameFROM Student JOIN Class ON Student.Classno=Class.ClassnoWHERE ClassName=计算机093,【任务实施】,【练习2】查询课程名称中包含数据库的课程的学生成绩，结果包含学号、课程编号、课程名称、平时成绩、期末成绩。【练习3】查询所有课程的课程号、课程名和学生平时成绩，按照课程号升序排列，如果课程号相同，按照平时成绩降序排列。【练习4】查询期末成绩有不及格课程的学生信息，结果包含学号、姓名、班级编号。,【任务实施】,【练习5】查询选修了数据库技术与应用1课程的学生的人数。查询结果应如图5-52所示。【练习6】统计各专业学生的人数，结果包含专业名称、该专业人数。查询结果应如图5-53所示。,【任务实施】,2两张表以上的连接多表连接可能涉及三张表或更多表的连接。连接实现的步骤是：先两张表进行连接形成虚表1，然后虚表1与第三张表进行连接形成虚表2，然后虚表2与第四张表进行连接，最后得到查询结果。【例7】查询所有学生的学号、姓名、班级名称、选修的课程编号及平时成绩。SELECT Student.Sno,Sname,ClassName,Cno,UscoreFROM Class JOIN Student ON Class.ClassNo=Student.ClassNo JOIN Score ON Student.Sno=Score.Sno,【任务实施】,【练习7】查询班级编号为200901001班学生的基本信息及其选课信息，结果包含学号、姓名、性别、课程编号、课程名称。【练习8】查询计算机092班学生的基本信息及其选课信息，结果包含学号、姓名、性别、课程编号、课程名称。,【任务总结】,多表连接查询是关系数据库中最重要的查询。连接查询分为交叉连接、内连接和外连接查询。其中内连接查询是实际应用中最常用的。SELECT ALL|DISTINCT 目标列表达式FROM 表名1 JOIN 表名2 ON 表名1.列名1=表名2.列名2WHERE 行条件表达式GROUP BY 分组列名HAVING 组筛选条件表达式ORDER BY 排序列名 ASC|DESC,【任务总结】,实施查询任务的步骤，可按照以下步骤进行分析逐步实现：步骤1：分析查询涉及的表。包括查询条件和查询结果涉及的表，确定是单表查询还是多表查询。确定FROM子句中的表名。步骤2：如果是多表查询，分析确定表与表之间的连接条件，即确定FROM子句中ON后面的连接条件。步骤3：分析查询是否针对所有记录，还是选择部分行。即对行有没有选择条件，如果是选择部分行，使用WHERE子句，确定WHERE子句中的行条件表达式；步骤4：分析查询是否要进行分组统计计算。如果需要分组统计，则使用GROUP BY子句，确定分组的列名。然后分析分组后是否要对组进行筛选，如果需要，则使用HAVING子句，确定组筛选条件。步骤5：确定查询目标列表达式，即确定查询结果包含的列名或列表达式，即确定SELECT子句后的目标列表达式。步骤6：分析是否要对查询结果进行排序，如果需要排序则使用ORDER BY子句，确定排序的列名和排序方式。,【拓展练习】,1查询所有学生的详细住宿信息，结果包含学号、宿舍编号、楼栋、房间号、床位号、入住日期。2查询住在龙川北苑04南楼栋的学生的学号和宿舍编号。3查询姓名为王康俊学生的住宿信息，结果包含宿舍编号、床位号、入住日期。4查询所有宿舍在2010年10月份的卫生检查情况，结果包含楼栋、宿舍编号、房间号、检查时间、检查人员、成绩、问题。5查询龙川北苑04南楼栋各宿舍的卫生检查平均成绩，结果包含宿舍编号、平均成绩。6查询龙川北苑04南楼栋的宿舍在2010年10月份的卫生检查情况，结果包含宿舍编号、房间号、检查时间、检查人员、成绩、问题。7查询龙川北苑04南楼栋的宿舍在2010年10月份的卫生检查成绩不及格的宿舍个数。8查询所有学生的基本信息及其住宿信息，结果包含学号、姓名、性别、宿舍编号、楼栋、房间号、床位号、入住日期。9查询所有学生的详细信息及其住宿信息，结果包含学号、姓名、性别、班级编号、班级名称、宿舍编号、楼栋、房间号、床位号、入住日期。10查询计算机应用技术专业所有学生的入住信息，结果包含学号、姓名、性别、班级编号、班级名称、宿舍编号、楼栋、房间号、床位号、入住日期。查询结果按照班级编号升序排列，同班的按照学号升序排列。,任务4,任务4 数据更新,【任务提出】,对数据的操作除了常用的查询数据操作外，还包括日常必做的插入数据、修改数据、删除数据等操作。插入数据、修改数据、删除数据操作统称为数据更新。,【任务分析】,在数据操作中，操作的对象都是记录，而不是记录中的某个数据。插入数据指往表中插入一条记录或多条记录。修改数据指对表中现有记录进行修改。删除数据指删除指定的记录。插入记录对应的SQL语句是INSERT语句，修改记录对应的SQL语句是UPDATE语句，删除记录对应的SQL语句是DELETE语句。,【相关知识与技能】,1插入一条记录 其语法格式是：INSERT INTO 表名(列名1,列名2,列名n)VALUES(常量1,常量n)其功能是：将VALUES后面的常量插入到表中新记录的对应列中。其中常量1插入到表新记录的列名1中，常量2插入到列名2中，常量n插入到列名n中。即表名后面列名的顺序与VALUES后面常量的顺序须一一对应。,【相关知识与技能】,2插入查询结果 可通过插入查询结果一次性地成批插入大量数据。其一般格式为：INSERT INTO 表名(列名1,列名2,列名n)SELECT 查询语句其功能是：将SELECT查询语句查询的结果插入到表中。但前提是该表必须已经存在，而且表中的字段数据类型和长度都要与查询结果中的字段一致。,【相关知识与技能】,3使用INTO子句生成表INTO子句实现创建一个新表，并将查询结果存放到该新表中。新表不能事先存在，新表的结构包括列名、数据类型和长度都由SELECT查询语句决定。其语句格式为：SELECT ALL|DISTINCT 目标列表达式INTO 新表名FROM 表名1 JOIN 表名2 ON 表名1.列名1=表名2.列名2WHERE 行条件表达式GROUP BY 分组列名HAVING 组筛选条件表达式ORDER BY 排序列名 ASC|DESCINTO子句置于SELECT子句之后，FROM子句之前。,【相关知识与技能】,4UPDATE语句UPDATE语句的作用是对指定表中的现有记录进行修改。其语句格式为：UPDATE 表名SET 列名1=,列名2=,WHERE 行条件表达式其功能是：对表中满足WHERE条件的记录进行修改，由SET子句将修改后的值替换相应列的值。若不使用WHERE子句，则修改所有记录的指定列的值。可以是具体的常量值，也可以是表达式。【注意】UPDATE后面的表名只能是一个表名。,【相关知识与技能】,5DELETE语句DELETE语句的作用是删除指定表中满足条件的记录。其语句格式为：DELETE FROM 表名WHERE 行条件表达式其功能是：删除表中满足WHERE条件的所有记录。如果不使用WHERE子句，则删除表中的所有记录，但表仍然存在。如果要删除表，则使用DROP TABLE子句。【注意】DELETE FROM后面的表名只能是一个表名。,【任务实施】,1插入一条记录【例1】往Student表中插入一条新记录，其中学号为200931010190125、姓名为陈红，性别为女，班级编号为200901901。INSERT INTO Student(Sno,Sname,Sex,ClassNo)VALUES(200931010190125,陈红,女,20090190)注1：字符型常量和日期时间型常量必须用单引号括起来，数值型常量则不需要单引号括起来。注2：如果表中的某些属性列在INSERT子句中的表名后没有出现，则新记录中的这些列中的值为空值NULL。注3：如果插入表中所有列的数据时，INSERT语句中表名后面的列名可以省略，但插入数据的顺序必须与表中列的顺序完全一致，否则不能省略表名后面的列名。,【任务实施】,思考1：能不能再次运行以上INSERT语句。思考2：下列语句能不能执行 INSERT INTO Student(Sno,Sname)VALUES(200931010190125,陈红)思考3：下列语句能不能执行INSERT INTO StudentVALUES(200931010190120,男,何园,1991/11/18,200901901）,【任务实施】,【练习1】往Class表中插入所在班级的信息，往学生表中插入本人的基本信息。,【任务实施】,2插入查询结果【例3】假如已将班级编号为200901001的班级学生单独建了一个表JSJ，其中包含学号、姓名、性别和出生年月四个字段，字段的数据类型和长度都与Student表相同，现要从Student表中查询出该班学生信息插入到JSJ表中。INSERT INTO JSJ(Sno,Sname,Sex,Birth）SELECT Sno,Sname,Sex,BirthFROM StudentWHERE ClassNo=200901001【注意】通过INSERT语句往表中插入查询结果，可以实现一次性地成批插入大量数据。但前提是新表必须已经存在，而且表中的字段数据类型和长度都要与查询结果中的字段一致。,【任务实施】,2插入查询结果【例3】假如已将班级编号为200901001的班级学生单独建了一个表JSJ，其中包含学号、姓名、性别和出生年月四个字段，字段的数据类型和长度都与Student表相同，现要从Student表中查询出该班学生信息插入到C1表中。INSERT INTO JSJ(Sno,Sname,Sex,Birth）SELECT Sno,Sname,Sex,BirthFROM StudentWHERE ClassNo=200901001【注意】通过INSERT语句往表中插入查询结果，可以实现一次性地成批插入大量数据。但前提是新表必须已经存在，而且表中的字段数据类型和长度都要与查询结果中的字段一致。,【任务实施】,如上述例3中的表JSJ不存在，必须先创建该表，再执行INSERT语句。创建JSJ表的SQL脚本如下所示：USE SchoolIF EXISTS(SELECT*FROM sysobjects WHERE NAME=JSJ and TYPE=U)-判断表是否存在 DROP TABLE JSJ-如果存在，删除该表GOCREATE TABLE JSJ(Snonvarchar(15)PRIMARY KEY,Snamenvarchar(10)NOT NULL,Sex nchar(1)NOT NULL CHECK(SSex=男 or SSex=女),Birthdatetime)GO,【任务实施】,3使用INTO子句创建新表并存放查询结果【例4】创建班级编号为200901002的班级学生信息表，表名为JSJ2。SELECT Sno,Sname,Sex,BirthINTO JSJ2FROM StudentWHERE ClassNo=200901002 INTO子句置于SELECT子句之后，FROM子句之前。,【任务实施】,【练习2】创建数据库技术与应用1课程的选课情况表，表中信息包含学生学号、平时成绩、期末成绩，表名为SjkXk。【练习3】创建课程情况统计表，表中信息包含课程号、选课人数，表名为xkqk。将练习3中创建的表中增加课程名称列。,【任务实施】,创建临时表【例5】创建各门课程的平均期末成绩临时表，要求表中列出课程名称和平均期末成绩。SELECT Cname,AVG(EndScore)INTO#平均成绩表FROM Course,ScoreWHERE Course.Cno=Score.CnoGROUP BY Cname【注意】一般INTO子句用于创建临时表，在表名前加上#号就为临时表，临时表会随着系统的退出而消失。,【任务实施】,【练习】创建各门课程的选课情况临时表，要求表中列出各门课程的课程编号、课程名称、学生的学号、平时成绩和期末成绩。,【任务实施】,4修改一条记录的值【例6】将Sno为200931010100102、Cno为0901170的期末成绩修改为60分。UPDATE ScoreSET EndScore=60WHERE Sno=200931010100102 AND Cno=0901170,【任务实施】,【练习4】增加Sno为200931010100207、Cno为0901170的期末成绩为90分。【练习5】增加Sno为200931010100322、Cno为0901025的平时成绩为80分，期末成绩为84分。,【任务实施】,5修改多条记录的值【例7】将修改了课程编号为2003003且期末成绩小于90分的学生的期末成绩统一加10分。UPDATE ScoreSET EndScore=EndScore+10WHERE Cno=2003003 AND EndScore90,【任务实施】,6级联修改问题：学号为200931010100101的学生休学一年，复学后学号改为201031010100150，由于Student表和Score表都有关于200931010100101学生的信息，因此这两个表都需要修改，保证数据库中数据的一致性。使用两个UPDATE语句进行修改。第一条UPDATE语句修改Student表：UPDATE StudentSET Sno=201031010100150WHERE Sno=200931010100101第二条UPDATE语句修改Score表：UPDATE ScoreSET Sno=201031010100150WHERE Sno=200931010100101 是否正确？调换两条语句的执行顺序，是否正确？,【任务实施】,解决方法：应用级联修改。指级联修改修改主表中主键字段的值，其对应从表中外键字段的相应值自动修改。具体操作：1.在SSMS中将外键关系属性中的“更新规则”设为“层叠”。2.再新student表中的该记录值UPDATE StudentSET Sno=201031010100150WHERE Sno=200931010100101,【任务实施】,【练习6】将课程编号2003003修改为2003180。,【任务实施】,【思考题】将课程数据库技术与应用1的所有课程期末成绩置为0分。分析该习题，涉及到Score和Course表，而UPDATE后面的表名只能是一个表名。该如何实现？请学习拓展知识中的知识点1 带子查询的更新。,【任务实施】,7删除一条或多条记录【例8】删除Sno为200931010100322的学生选修课程编号为0901025的课程的选课记录。DELETE FROM ScoreWHERE Sno=200931010100322 AND Cno=0901025,【任务实施】,【练习7】从课程表中删除课程名称为思政概论的记录。,【任务实施】,8级联删除【例9】因学号为200931010100102的学生退学，在数据库中删除该学生的所有相关记录。第一条DELETE语句删除Student表：DELETE FROM Student WHERE Sno=200931010100102第二条DELETE语句删除Score表：DELETE FROM Score WHERE Sno=200931010100102是否正确？调换两条语句的执行顺序，是否正确？,【任务实施】,解决方法：应用级联删除。级联删除指删除主表中的记录，其对应子表中的相应记录自动删除。具体操作：1.在SSMS中将外键关系属性中的“删除规则”设为“层叠”。2.再删除主表student表中的该记录。DELETE FROM Student WHERE Sno=200931010100102,【任务实施】,【练习8】从课程表中删除课程名称为数据库技术与应用2的记录。,【任务实施】,【思考题】删除课程数据库技术与应用1的所有选课记录。分析该习题，涉及到Score和Course表，而DELETE后面的表名只能是一个表名。该如何实现？请学习拓展知识中的知识点2 带子查询的删除。,【任务总结】,【任务总结】数据更新包括添插入记录、修改记录和删除记录。插入记录的SQL语句为INSERT语句，修改记录的语句为UPDATE语句，删除记录的语句为DELETE语句。在进行数据更新时要保证数据库中数据的一致性，可使用级联修改和级联删除。,【拓展练习】,【拓展练习】1往宿舍表中增加你所在宿舍的信息。2往入住表中增加你入住宿舍的信息。3统计各个宿舍的现入住人数，将统计结果存放到临时表中。4查询所有学生的详细信息及其住宿信息，结果包含学号、姓名、班级名称、所属专业、宿舍编号、楼栋、房间号、床位号、入住日期，并将查询结果存放到临时表中。5查询龙川北苑04南楼栋各宿舍的卫生检查平均成绩，并将查询结果存放到临时表中。6将2010年11月19检查的卫生检查成绩统一加10分。7将宿舍编号XSY01111修改为X01111。8增加宿舍编号为LCN04B310的宿舍电话，电话号码为82266777。9将学号为200931010190118的学生的宿舍调整到宿舍编号为LCN04B408的宿舍中。10学号为200931010190119的学生退学了，从数据库中删除该生的所有相关记录。,