T-SQL多表查询、数据更改及内置函数

多表查询 使用TOP限制结果集行数 T-SQL插入数据 T-SQL修改数据 T-SQL删除数据 SQL Server内置函数 一、多表查询 1.内连接(inner join):只有连接双方都满足连接条件的行,才能出现在结果集 查询:格式:FROM 表1 [INNER] JOIN 表2 ON 连接条件 其中,连接条件:[<表名1.>]<列名> <比较运算符> [<表名2.>]<列名> 例6-37 查询学生及其选课的详细信息。 SELECT * FROM Student JOIN SC ON Student.Sno = SC.Sno 例6-38 查询计算机系学生的修课情况, 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 列出学生的名字、所修课的课程号和成绩。 SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Dept = '计算机系' FROM子句中,为表指定别名:<原表名> [AS] <表别名>。查询语句中其他地方(甚至是早于FROM子句的SELECT后面)就可以直接写别名,既可以简化书写、也便于对不同表中的列(尤其有同名列的时候)进行区分 例6-39 查询“信息管理系”选修了“计算机文化学”课程的学生信息,列出学生的姓名和考试成绩。 SELECT S.Sname, SC.Grade FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON SC.Cno=C.Cno WHERE S.Dept ='信息管理系' AND C.Cname='计算机文化学' 例6-40 查询所有选修了Java课程的学生,列出学生姓名和他们所在的系。 SELECT S.Sname, S.Dept FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C on SC.Cno = C.Cno WHERE C.Cname = 'Java' 例6-41 统计每个系的考试平均成绩,列出系名、平均成绩。 SELECT S.Dept, A VG(SC.Grade) 平均成绩 FROM Student S JOIN SC ON S.Sno = SC.Sno GROUP BY S.Dept 例6-42 统计计算机系每个学生的选课门数、平均成绩、最高成绩和最低成绩。 SELECT S.Sno, COUNT(*) 选课门数, A VG(SC.Grade) 平均成绩, MAX(SC.Grade) 最高成绩, MIN(SC.Grade) 最低成绩 FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE S.Dept = '计算机系' GROUP BY S.Sno 自连接查询:一种特殊的内连接,指相互连接的表在物理上是一张表,但在逻辑上将其看成是两张表(通过对表取别名来实现)。 FROM 表1 AS T1 –-将表1视作逻辑上的T1 JOIN 表1 AS T2 –-将表1同时视作逻辑上的T2 例6-43 查询与刘晨在同一个系学习的学生姓名及所在系。 解1:SELECT S2.Sname, S2.Dept FROM Student S1 JOIN Student S2 ON S1.Dept = S2.Dept WHERE S1.Sname = '刘晨' AND S2.Sname <> '刘晨' 解2:SELECT Sname, Dept FROM Student WHERE Dept = (Select Dept FROM Student WHERE Sname = '刘晨') AND Sname <> '刘晨' 2.左外连接查询 FROM 表1 LEFT [OUTER] JOIN 表2 ON <连接条件> 将表1全部行输出,但表2要满足连接条件 例6-46 查询全体学生的选课情况(包括未选项课程的学生),列出学号、姓名、课程号和成绩。 SELECT S.Sno, S.Sname, SC.Cno, SC.Grade FROM Student S LEFT JOIN SC ON S.Sno=SC.Sno 例6-47 查询没有人选的课程的编码及课程名。 SELECT C.Cno, C.Cname FROM Course C LEFT JOIN SC ON C.Cno = SC.Cno WHERE SC.Cno IS NULL 3.右外连接查询 FROM 表1 RIGHT [OUTER] JOIN 表2 ON <连接条件> 将表2全部行输出,但表1要满足连接条件 例6-46 查询全体学生的选课情况(包括未选项课程的学生),列出学号、姓名、课程号和成绩。(用右外连接) SELECT S.Sno, S.Sname, SC.Cno, SC.Grade FROM SC RIGHT JOIN Student S ON SC.Sno=S.Sno 例6-47 查询没有人选的课程的编码及课程名(用右外连接) SELECT C.Cno, C.Cname FROM SC RIGHT JOIN Course C ON SC.Cno = C.Cno WHERE SC.Cno IS NULL 4.多表查询-全外连接 二、使用TOP限制结果集行数 1.查询时有时候只希望看到结果集的前面几行数据、而不是全部数据。如我们可能只希望列出成绩排前三名的学生,或者查看选修人数最多的前3门课程。 限制固定行数:select top 5 学号, 姓名from 学生表 限制行数所占百分比:select top 20 percent 学号, 姓名from 学生表 例6-52 查询单科成绩最高的前3名(不分科目、不考虑第3名并列),列出学号、课程号和成绩。 SELECT TOP 3 Sno, Cno, Grade FROM SC ORDER BY Grade DESC 例6-53 查询Java考试成绩最高的前3名学生的姓名、所在系和Java考试分数。前3名若有名次并列一并列出。 SELECT TOP 3 WITH TIES S.Sname, S.Dept, SC.Grade FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON SC.Cno = C.Cno WHERE C.Cname = 'Java' ORDER BY SC.Grade DESC 例6-54 查询选课人数最少的两门课程(不包括没人选的课程),列出课程号和选课人数。 SELECT TOP 2 WITH TIES Cno, COUNT(*) AS 选课人数 FROM SC GROUP BY Cno ORDER BY COUNT(*) 例6-55 查询在计算机系选课门数超过2门的学生中,考试平均成绩最高的前两名(包括并列的情况),列出学生学号、选课门数、考试平均成绩。 SELECT TOP 2 WITH TIES SC.Sno, COUNT(Cno) 选课门数, A VG(Grade) 平均成绩FROM SC JOIN Student S ON SC.Sno=S.Sno WHERE S.Dept = '计算机系' GROUP BY SC.Sno HA VING COUNT(Cno)>2 ORDER BY A VG(Grade) DESC 将查询结果保存到新表中 当使用SELECT语句查询数据时,结果集被保存在内存中。如果希望将查询结果保存到一个表中,则可以在SELECT语句中包含INTO子句: SELECT 查询列表序列INTO <新表名> FROM 数据源 ……-- 其他行过滤、分组等语句 <新表名>:要存放查询结果的表名,可以是永久表,也可以是内存中的临时表。临时表又可分为局部临时表(表名以一个#开始)和全局临时表(表名以两个#开始),前者只能在当前数据库连接中使用,而后者可以为所有数据库连接所使用。 例6-57、6-58 三、 1.T-SQL插入数据 INSERT [ TOP (expression) [PERCENT] ] [INTO] 插入的表或视图名 { [ ( 插入列名列表) ] { V ALUES ( ( { DEFAULT | NULL | expression } [ , …n] ) [ , …n] | SELECT 语句} } V ALUES子句:表示插入一行并给插入列名列表中的每一列指定值。如果插入表中的某一列未在“插入列名列表”中,则该列有默认值用默认值、否则允许空就用空值 SELECT 子句:插入数据来自另一个查询,此时可插入多行,插入行数还要受TOP谓词限制 使用T-SQL语句插入单行数据 例6-60 将一条新生信息插入到Student表中,该新生学号为0821105,姓名为陈冬,性别为男,1991年6月23日出生,信息管理系学生。 INSERT INTO Student --不指定列名,表示全部列 V ALUES ( '0821105', '陈冬', '男', '1991/6/23', '信息管理系') --值的顺序与列的定义顺序一致 例6-62 将一条新生信息插入到Student表中,该新生学号为0811105,姓名为李丽,性别为女,出生日期暂缺,计算机系学生。 INSERT INTO Student (Sno, Sname, Sex, Dept) V ALUES ( ‘08121105’, ‘李丽’, ‘女’, ‘计算机系’) /* 值按照指定列的顺序赋值,缺少的列用缺省值或DBMS用其他方法能确定的值填充*/ 使用T-SQL语句插入多行数据 例6-61 在SC中插入3条新记录,不指定列。 INSERT INTO SC V ALUES ('0821105', 'C001', 90), ('0821105', 'C002', 80), ('0821105', 'C004', NULL) 例6-64 统计每门课程的平均成绩,并把统计结果保存到一个新表中。 CREATE TABLE AveGrade ( Cno CHAR(6), AvgGrade SMALLINT) INSERT INTO AveGrade SELECT Cno, Avg(Grade) FROM SC GROUP BY Cno 例6-65 将单科最高的6个成绩信息插入一个新表。 CREATE TABLE Top_Grade ( Sname nchar(5), Dept nvarchar(20), Cname nvarchar(20), Grade tinyint ) 2.使用T-SQL语句更新数据行(T-SQL修改数据) UPDATE [ TOP (表达式) [PERCENT] ] 表名或视图名 SET { 列名= { 表达式| DEFAULT | NULL } } [ , …n ] [ FROM {来源表} [ , …n] ] [ WHERE 更新行应该满足的条件] 其中各参数含义: TOP (表达式) [ PERCENT]:指定将要更新的行数或行百分比 表名或视图名:要更新数据的表或视图的名称 SET:指定要更新的列的列表 列名= { 表达式| DEFAULT | NULL } :用表达式的值、该列的默认值或NULL值来替换当前值。如果使用了DEFAULT但该列没有定义默认值,则在该列允许为空时用NULL替换 FROM {来源表}:指定为更新操作提供条件的表名或视图名 例6-66 将所有的学生成绩加10分。 UPDATE SC SET Grade = Grade + 10 例6-67 将“C001”课程的学分改为5. UPDATE Course SET Credit=5 WHERE Cno = 'C001' 例6-68 将计算机系全体学生的成绩加5分。 UPDATE SC SET Grade = Grade + 5 FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE S.Dept = '计算机系' 例6-69 将Java课程改为第2学期开设、3学分。 UPDATE Course SET Semester = 2, Credit = 3 WHERE Cname = 'Java' 例:从学生选课表中随机抽取10名学生,将其成绩减半。 -- 类似于例6-70 UPDATE TOP (10) SC SET Grade = Grade / 2 3.T-SQL删除数据 DELETE [ TOP (表达式) [ PERCENT] ] [ FROM ] 待删除数据的表名或视图名 [ FROM <源表名> [ , …n ] ] [ WHERE <查询条件> ] 其中各参数含义同UPDATE。 例6-71 删除料品表中的全部记录。 DELETE FROM Materials -- Materials 表还存在,但没有数据 例6-72 删除所有考试成绩不及格的学生的选课记录。 DELETE SC WHERE Grade < 60 例6-73 删除计算机系考试成绩不及格的学生的选课记录。 DELETE SC FROM SC JOIN Student S ON SC.Sno = S.Sno WHERE S.Dept = '计算机系' AND SC.Grade < 60 例删除SC中10%的数据。(类似于例6-74) DELETE TOP (10) PERCENT FROM SC