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, AVG(SC.Grade) 平均成绩

FROM Student S JOIN SC ON S.Sno = SC.Sno GROUP BY S.Dept

例6-42 统计计算机系每个学生的选课门数、平均成绩、最高成绩和最低成绩。

SELECT S.Sno, COUNT(*) 选课门数, AVG(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) 选课门数, AVG(Grade) 平均成绩 FROM SC JOIN Student S ON SC.Sno=S.Sno WHERE S.Dept = '计算机系'

GROUP BY SC.Sno HAVING COUNT(Cno)>2 ORDER BY AVG(Grade) DESC

将查询结果保存到新表中

当使用SELECT语句查询数据时，结果集被保存在内存中。如果希望将查询结果保存到一个表中，则可以在SELECT语句中包含INTO子句： SELECT 查询列表序列 INTO <新表名> FROM 数据源

?? -- 其他行过滤、分组等语句

<新表名>：要存放查询结果的表名，可以是永久表，也可以是内存中的临时表。临时表又可分为局部临时表（表名以一个#开始）和全局临时表（表名以两个#开始），前者只能在当前数据库连接中使用，而后者可以为所有数据库连接所使用。

例6-57、6-58

三、

1.T-SQL插入数据 INSERT

[ TOP (expression) [PERCENT] ] [INTO] 插入的表或视图名 {

[ ( 插入列名列表) ]

{ VALUES ( ( { DEFAULT | NULL | expression } [ , ?n] ) [ , ?n] | SELECT 语句 } }

VALUES子句：表示插入一行并给插入列名列表中的每一列指定值。如果插入表中的某一列未在“插入列名列表”中，则该列有默认值用默认值、否则允许空就用空值

SELECT 子句：插入数据来自另一个查询，此时可插入多行，插入行数还要受TOP谓词限制

使用T-SQL语句插入单行数据

例6-60 将一条新生信息插入到Student表中，该新生学号为0821105，姓名为陈冬，性别为男，1991年6月23日出生，信息管理系学生。 INSERT INTO Student --不指定列名，表示全部列

VALUES ( '0821105', '陈冬', '男', '1991/6/23', '信息管理系') --值的顺序与列的定义顺序一致 例6-62 将一条新生信息插入到Student表中，该新生学号为0811105，姓名为李丽，性别为女，出生日期暂缺，计算机系学生。

INSERT INTO Student (Sno, Sname, Sex, Dept)

VALUES ( ‘08121105’, ‘李丽’, ‘女’, ‘计算机系’) /* 值按照指定列的顺序赋值，缺少的列用缺省值或DBMS用其他方法能确定的值填充 */ 使用T-SQL语句插入多行数据

例6-61 在SC中插入3条新记录，不指定列。 INSERT INTO SC VALUES ('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

本文来源：https://www.bwwdw.com/article/7y5v.html