1、1,插入数据行 7-1,InSert INTO 列名 Values ,INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (张青裁,上海松江,6,ZQCS,0),2,插入数据行 7-2,注意事项1:每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；,INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (张青裁),代码错误,3,插入数据行 7-3,注意事项2:每个数据值的数据类型、精度和小数位
2、数必须与相应的列匹配；,INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (张青裁,上海松江,ZQC,ZQCS, ZG),代码错误,4,插入数据行 7-4,注意事项3:不能为标识列指定值，因为它的数字是自动增长的；,INSERT INTO Students (SCode,SName,SAddress,SGrade,SEmail,SSEX) VALUES (32,张青裁,上海松江,6,ZQCS,0),代码错误,5,插入数据行 7-5,注意事项4:如果在设计表的时候就指定了某列不允许为空，则必须插入数据；,INSERT
3、INTO Students (SAddress,SGrade,SEmail,SSEX) VALUES (上海松江,6,ZQCS,0),代码错误,6,插入数据行 7-6,注意事项5:插入的数据项，要求符合检查约束的要求,INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (张青裁,上海松江,6,ZQC,0),代码错误,7,插入数据行 7-7,注意事项6:具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值,INSERT INTO Students (SName,SAddress,SGrade,SEmail
4、,SSEX) VALUES (张青裁,DEFAULT,6,ZQCS,0),8,小结1,编写SQL语句，把你自己的信息插入到学员信息表中,9,插入多行数据 4-1,INSERT INTO TongXunLu (姓名,地址,电子邮件) SELECT SName,SAddress,SEmail FROM Students,InSert Into (列名) Select From ,10,插入多行数据 4-2,SELECT Students.SName,Students.SAddress,Students.SEmail INTO TongXunLu FROM Students,Select (列名)
5、Into From ,该语句可以执行两次吗？,11,插入多行数据 4-3,SELECT Students.SName,Students.SAddress,Students.SEmail，IDENTITY(int,1,1) As StudentID INTO TongXunLuEX FROM Students,Select Into插入多行数据的时候，如何插入新的标识列？,SELECT IDENTITY(数据类型,标识种子,标识增长量) AS 列名 INTO 新表 FROM 原始表,12,插入多行数据 4-4,INSERT STUDENTS (SName,SGrade,SSex) SELECT
6、张可,7,1 UNION SELECT 李扬,4,0 UNION SELECT 杨晓,2,0 UNION SELECT 汤美,3,0 UNION SELECT 苏三东,7,1 UNION SELECT 王立岩,3,1 UNION SELECT 张伟,7,1 UNION SELECT 陈刚,4,1 UNION SELECT 王娟娟,7,0,InSert Into (列名) Select Union Select Union ,13,更新数据行,Update Set Where ,UPDATE Students SET SSEX = 0,UPDATE Students SET SAddress =
7、北京女子职业技术学校家政班 WHERE SAddress = 北京女子职业技术学校刺绣班,UPDATE Scores SET Scores = Scores + 5 WHERE Scores = 95,14,小结2,编写SQL语句，更新学员信息表中全部女同学的地址为“北京奥运村”,15,删除数据行 3-1,Delete From Where ,DELETE FROM Students WHERE SName =张青裁,DELETE FROM 学员信息表 WHERE 学号 =0010012,运行错误,16,删除数据行 3-2,以下的删除语句可以执行吗？,DELETE SCode FROM Stu
8、dents,17,删除数据行 3-3,Truncate Table ,TRUNCATE TABLE Students,DELETE FROM Students,=,18,总结,为学员成绩表中增加一行数据，学员张伟Java的考试成绩为80分，SQL语句怎么写？ 更新成绩表，所有低于60分成绩都加5分的SQL语句怎么写？ 复制学员表Students为StudentsInfo，包含表结构和全部的数据，SQL语句怎么写？,19,目标,掌握简单子查询的用法 掌握IN子查询的用法 掌握EXISTS子查询的用法 应用T-SQL进行综合查询,20,什么是子查询,学员信息表,问题： 编写T-SQL语句，查看年龄
9、比“李斯文”大的学员，要求显示这些学员的信息 ？,分析： 第一步：求出“李斯文”的年龄； 第二步：利用WHERE语句，筛选年龄比“李斯文”大的学员；,21,什么是子查询,实现方法一：采用T-SQL变量实现,DECLARE age INT -定义变量，存放李斯文的年龄 SELECT age=stuAge FROM stuInfoWHERE stuName=李斯文 -求出李斯文的年龄 -筛选比李斯文年龄大的学员 SELECT * FROM stuInfo WHERE stuAgeage GO,22,什么是子查询,实现方法二：采用子查询实现,SELECT * FROM stuInfo WHERE s
10、tuAge( SELECT stuAge FROM stuInfo where stuName=李斯文) GO,子查询,子查询在WHERE语句中的一般用法：SELECT FROM 表1 WHERE 字段1 (子查询)外面的查询称为父查询，括号中嵌入的查询称为子查询 UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个,23,使用子查询替换表连接,问题：查询笔试刚好通过（60分）的学员。,学员信息表和成绩表,24,使用子查询替换表连接,实现方法一：采用表连接,SELECT stuName FROM stuI
11、nfo INNER JOIN stuMarksON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam=60 GO,内连接（等值连接）,25,使用子查询替换表连接,实现方法二：采用子查询,SELECT stuName FROM stuInfo WHERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60) GO,子查询,一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换 子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据 表连接更适合于查看多表的数据,26,
12、IN子查询,问题：查询笔试刚好通过的学员名单。,如何解决？,27,IN子查询,解决方法：采用 IN 子查询,SELECT stuName FROM stuInfo WHERE stuNo IN(SELECT stuNo FROM stuMarks WHERE writtenExam=60) GO,将号改为IN,IN后面的子查询可以返回多条记录 常用IN替换等于（）的比较子查询,28,IN子查询,问题： 查询参加考试的学员名单,学员信息表和成绩表,分析： 判断一个学员是否参加考试其实很简单，只需要查看该学员对应的学号是否在考试成绩表stuMarks中出现即可,29,IN子查询,/*-采用IN子查
13、询参加考试的学员名单-*/ SELECT stuName FROM stuInfoWHERE stuNo IN (SELECT stuNo FROM stuMarks) GO,演示：使用IN子查询,参考语句,30,NOT IN子查询,问题： 查询未参加考试的学员名单,分析： 加上否定的NOT 即可,31,EXISTS子查询,例如：数据库的存在检测,IF EXISTS（SELECT * FROM sysDatabases WHERE name=stuDB）DROP DATABASE stuDB CREATE DATABASE stuDB .建库代码略,32,EXISTS子查询,IF EXISTS
14、 （子查询）语句,EXISTS子查询的语法：,如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false) EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换,33,EXISTS子查询,问题： 检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分；否则，每人允许提5分,分析： 是否有人笔试成绩达到80分以上，可以采用EXISTS检测,34,EXISTS子查询,/*-采用EXISTS子查询，进行酌情加分-*/ IF EXISTS (SELECT * FROM stuMarks WHERE writtenExa
15、m80)BEGINprint 本班有人笔试成绩高于80分，每人加2分，加分后的成绩为：UPDATE stuMarks SET writtenExam=writtenExam+2SELECT * FROM stumarksEND ELSEBEGINprint 本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩：UPDATE stuMarks SET writtenExam=writtenExam+5SELECT * FROM stumarksEND GO,演示：使用EXISTS子查询,参考语句,35,NOT EXISTS子查询,问题： 检查本次考试，本班如果没有一人通过考试（笔试和机试成绩
16、都60分），则试题偏难，每人加3分，否则，每人只加1分,分析： 没有一人通过考试，即不存在“笔试和机试成绩都60分”，可以采用NOT EXISTS检测,36,NOT EXISTS子查询,IF NOT EXISTS (SELECT * FROM stuMarks WHERE writtenExam60 AND labExam60)BEGINprint 本班无人通过考试，试题偏难，每人加3分，加分后的成绩为：UPDATE stuMarks SET writtenExam=writtenExam+3,labExam=labExam+3SELECT * FROM stuMarksEND ELSEBEG
17、INprint 本班考试成绩一般，每人只加1分，加分后的成绩为：UPDATE stuMarks SET writtenExam=writtenExam+1,labExam=labExam+1SELECT * FROM stuMarksEND GO,参考语句,演示：使用NOT EXISTS子查询,37,T-SQL语句的综合应用,学员信息表和成绩表,应到人数：5人,实到人数4人，缺考1人,38,T-SQL语句的综合应用,如何实现？,本次考试的缺考情况,比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分 。加分后重新统计通过情况,统计通过率,39,T-SQL语句的综合应用,
18、1.提示: 使用子查询统计缺考情况： 应到人数：SELECT count(*) FROM stuInfo 实到人数：SELECT count(*) FROM stuMarks,2.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过 1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（ LEFT JOIN ）；SELECT stuNameFROM stuInfo LEFT JOIN stuMarks 2)要求新加一列“是否通过（isPass）”，可采用CASE END。为了便于后续的通过率统计，通过则为1，没通过为0SELECT isPass=CASE W
19、HEN writtenExam=60 THEN 1ELSE 0 END 3)要求保存提取（查询）的结果，可以使用我们曾学习过的SELECT INTO newTable语句，生成新表并保存数据,40,T-SQL语句的综合应用,3.比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过97分： 1) 使用IF语句判断笔试还是机试偏低，决定对笔试还是机试提分； 2) 使用WHILE循环给每个学员加分，缺考的除外，当最高分超过97分时退出循环； 3)因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新isPass（是否通过）列。UPDATE newTable
20、SET isPass=CASEWHEN writtenExam=60 and labExam=60 THEN 1ELSE 0END,41,T-SQL语句的综合应用,4.提分后，统计学员的成绩和通过情况： 1）使用别名实现中文字段名，即SELECT 姓名=stuName,学号=stuNo 2）如果某个学员的成绩为NULL(空)，则替换为”缺考”，否则原样显示； 3）isPass列中的1替换为是，0替换为否；SELECT ,机试成绩=CASEWHEN labExam IS NULL THEN 缺考ELSE convert(varchar(5),labExam)END,是否通过=CASE WHEN
21、isPass=1 THEN 是ELSE 否END,42,T-SQL语句的综合应用,5.提分后统计学员的通过率情况： 1）通过人数：因为通过用1表示，没通过用0表示，所以isPass列的累加和即是通过人数； 2）通过率：同理，isPass列的平均值*100即是通过率；,43,T-SQL参考语句,/*-本次考试的原始数据-*/ -SELECT * FROM stuInfo -SELECT * FROM stuMarks /*-统计考试缺考情况-*/ SELECT 应到人数=(SELECT count(*) FROM stuInfo) , -应到人数为子查询表达式的别名实到人数=(SELECT co
22、unt(*) FROM stuMarks) ,缺考人数=(SELECT count(*) FROM stuInfo)-(SELECT count(*) FROM stuMarks),44,T-SQL参考语句,/*-统计考试通过情况，并将结果存放在新表newTable中-*/ IF EXISTS(SELECT * FROM sysobjects WHERE name=newTable)DROP TABLE newTable SELECT stuName,stuInfo.stuNo,writtenExam ,labExam ,isPass=CASE WHEN writtenExam=60 and
23、labExam=60 THEN 1ELSE 0ENDINTO newTable FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo -SELECT * FROM newTable -查看统计结果，可用于调试,45,T-SQL参考语句,/*-酌情加分：比较笔试和机试平均分，决定加哪门-*/ DECLARE avgWritten numeric(4,1) DECLARE avgLab numeric(4,1) SELECT avgWritten=AVG(writtenExam) FROM newTableWHERE wri
24、ttenExam IS NOT NULL SELECT avgLab=AVG(labExam)FROM newTable WHERE labExam IS NOT NULL IF avgWritten=97BREAKEND ELSE 略 -循环给笔试加分，最高分不能超过97分,46,T-SQL参考语句,-因为提分，所以需要更新isPass（是否通过）列的数据 UPDATE newTable SET isPass=CASEWHEN writtenExam=60 and labExam=60 THEN 1ELSE 0END -SELECT * FROM newTable -可用于调试,/*-显示考
25、试最终通过情况-*/ SELECT 姓名=stuName,学号=stuNo ,笔试成绩=CASE WHEN writtenExam IS NULL THEN 缺考ELSE convert(varchar(5),writtenExam)END,机试成绩=CASEWHEN labExam IS NULL THEN 缺考ELSE convert(varchar(5),labExam)END,是否通过=CASE WHEN isPass=1 THEN 是ELSE 否ENDFROM newTable,47,T-SQL参考语句,/*-显示通过率及通过人数-*/ SELECT 总人数=count(*) ,通过人数=SUM(isPass),通过率=(convert(varchar(5),AVG(isPass*100)+%) FROM newTable,48,总结,总结我们曾学习过的查询，合并多个表中的数据的方法有三种： 联合(Union)合并多个数据表中的行 子查询将一个查询包含到另一个查询中 联接合并多个数据表中的列 通过在子查询中使用EXISTS子句，可以对子查询中的行是否存在进行检查 IN子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否在某个范围,
