●多表查询

●嵌套查询（子查询）

●UNION查询

●INTO子句

●数据更新

一、多表查询 

    将一个查询同时涉及两个以上的表，称连接查询。 用来连接多个表的条件称连接条件。连接条件中的字段叫做连接字段。

1．自然连接查询

等值连接--连接运算中的运算符是＝

非等值连接--连接运算中的运算符是<,>,>=,<=,!=

例：列出每个学生及其选修课程的详细清单

SELECT  *   FROM 学生表，成绩表  

WHERE 学生表.学号＝成绩表.学号 

2．自身连接

   连接操作的不是两个不相同的表，而是自己和自己连接。

例：列出每一门课程的间接先修课。

   SELECT  C.课程号，K.先修课 ‘间接先修课 ‘

FROM  课程表 C,课程表 K  WHERE  C.先修课＝K.课程号

3．复合条件连接

   在WHERE 子句中有多个连接条件

例：列出选修了“C801”课程并且成绩在85分以上的所有学生的学号,姓名,课程号和成绩

   SELECT  学生表.学号，姓名，课程号，成绩  FROM 学生表，成绩表  WHERE 学生表.学号＝成绩表.学号 AND 课程号=’C801’ AND 成绩>85

二、嵌套查询（子查询）

一个SELECT-FROM-WHERE语句可以嵌套在另外一个查询块的WHERE子句中。这类子查询的执行过程是：首先执行子查询，子查询得到的结果不被显示出来，而是传递给外部查询，作为外部查询的条件，然后执行外部查询。

1．带有IN的子查询

例1：列出和“张成”同学在同一个系的所有学生的清单

SELECT  *  FROM  学生表  WHERE 所在院系 IN(SELECT  所在院系 FROM 学生表 WHERE  姓名=’张成’)  

本例先执行 SELECT  所在院系 FROM 学生表 WHERE  姓名=’张成’

    结果为“计算机”

再执行 SELECT  *  FROM  学生表  WHERE 所在院系 IN  (‘计算机’)

//in可换成“=”，因为查询结果为一个数据

例2：列出选修了“数据库”的学生的学号，姓名，所在院系

  方法1：SELECT  学生表.学号，姓名，所在院系

          FROM 学生表，课程表，成绩表

        WHERE 学生表.学号=成绩表.学号 

AND 课程表.课程号=成绩表.课程号

        AND 课程名=’数据库’

方法2：SELECT学生表.学号，姓名，所在院系

       FROM 学生表 WHERE 学号 IN 

         （SELECT 学号 FROM 成绩表 WHERE 课程号 IN

（SELECT 课程号 FROM 课程表 WHERE 课程名=’数据库’））

2．带有ANY或ALL的子查询

   ANY：表示子查询的某一个值       ALL：表示子查询的所有值    

例1：列出比计算机系某个学生年龄大的学生清单。(只要比一个学生的年龄大)

方法1：SELECT *  FROM 学生表  

WHERE 年龄>ANY(SELECT  年龄  FROM  学生表  

WHERE  所在院系=’计算机’) AND 所在院系!=’计算机’

方法2：SELECT *  FROM 学生表  

WHERE 年龄> (SELECT  MIN(年龄)  FROM  学生表  

WHERE  所在院系=’计算机’) AND 所在院系!=’计算机’

例2：查询所有大于学号“608013301”学生的成绩的学生。要求：列出学生的学号，姓名，性别，课程名和成绩

      SELECT A.学号, 姓名, 性别, 课程名, 成绩

        FROM 学生表 A, 成绩表 B, 课程表 C

        WHERE A.学号=B.学号  AND B.课程号=C.课程号 AND  成绩>ALL(SELECT 成绩 FROM 成绩表    WHERE  学号=’ 608013301’)

3．带有EXITS的子查询

   用EXISTS 和NOT EXISTS关键字用来确定数据是否在查询列表中存在。

相关子查询的处理过程：

取出外层查询中表的第1条记录，根据它与内层查询相关的字段值处理内层查询

若WHERE子句返回值为真，则取这条记录放入结果集中

再检查下一条记录

例1：查询所有选修了课程“C801”的学生的所有信息

方法1：使用复合查询

      SELECT 学生表.* FROM 学生表,成绩表 

WHERE 成绩表.学号=学生表.学号  AND  课程号=’C801’

方法2：使用EXISTS子句

SELECT * FROM 学生表 WHERE EXISTS (SELECT  *  FROM  成绩表  WHERE 学号=学生表.学号  AND 课程号=’C801’)

例2：列出没有选修课程“C801”的学生的所有信息

      SELECT * FROM 学生表 WHERE NOT  EXISTS(SELECT  *  FROM  成绩表  WHERE 学号=学生表.学号  AND 课程号=’C801’)

4．在WHERE子句中使用集合函数

例1：列出年龄最大的学生清单

SELECT  *  FROM  学生表  

WHERE  年龄=(SELECT  MAX(年龄)  FROM  学生表)

例2：按降序显示所有年龄超过平均值的学生名单

     SELECT *  FROM  学生表  WHERE 年龄>(SELECT AVG(年龄)  

FROM 学生表 )  ORDER  BY 年龄 DESC

三、UNION查询

合并两个或多个查询的结果，通过UNION关键字实现。组合的结果集必须满足条件：

字段个数必须相同

结果集中相应字段的数据类型必须兼容

使用UNION将结果表进行逻辑联合时，SQL SERVER会自动将重复的记录去掉。列名来自第一个SELECT语句。

例1：显示计算机系的学生以及性别为女的学生名单

SELECT * FROM 学生表 WHERE 所在院系=’计算机’  

UNION SELECT * FROM 学生表 WHERE 性别=’女’

例2：用UNION子句将学生表中学生的学号、姓名及TEACHER表中教师号、教师姓名组合在一个结果集中。

   CREATE  TABLE TEACHER

(教师号 char(6) not null primary key,

 教师姓名 varchar(8) null)

SELECT 学号 AS ID ,姓名 AS NAME FROM 学生表      ----用AS给字段指定别名

UNION SELECT 教师号,教师姓名 FROM TEACHER

四、INTO 子句

    语法格式：SELECT <字段> INTO <新建表名>

FROM <表名> WHERE <过滤条件表达式>

SELECT INTO的作用是，在查询的基础上创建新表。

说明：1) 这是常用的创建新表和临时表的方法，临时表在表名前加#（局部临时表）

或##（全局临时表）

2) 新表的行和列来自查询结果

例1：创建计算机系学生表

       SELECT *  INTO 计算机系学生  FROM 学生表 WHERE  所在院系=’计算机’

查看方法：1）在企业管理器下查看表计算机系学生；

          2）SELECT  *  FROM  计算机系学生

例2：新建一个表名为学生分类表，内容包括网061和网062的所有学生的名单

      SELECT * INTO  学生分类表 FROM 学生表 WHERE 班级名=’网061’  UNION SELECT * FROM 学生表 WHERE 班级名=’网062’

例3：创建一个临时表

     SELECT *  INTO  #新表  FROM  学生表  WHERE  性别=’女’

五、数据更新

　数据更新有3种操作：

插入记录（Insert）

删除记录（Delete）

修改记录（Update）

1.使用INSERT插入记录

（1）插入一条记录

例：往学生表中插入一条记录

insert into 学生表（学号，姓名） 

values(‘408013101’,’张成’)

（2）插入子查询的结果 

格式：INSERT [INTO] 数据表或视图 [<字段1,>…] 

SELECT [<字段A,>….]

      FROM <表名>

      WHERE <条件表达式>

例：创建一个新表<新学生表>，包括4个字段“学生学号，姓名，性别，所在院系”，然后将学生表中计算机系女学生的记录全部插入到新学生表中，最后显示插入的记录。

     CREATE  TABLE  新学生表

         (学生学号 char(10)  not  null  PRIMARY  KEY,

         姓名  varchar(8) null,

         性别 char(2) null,

         所在院系  varchar(30) null

)

INSERT  INTO  新学生表（学生学号,姓名,性别,所在院系）

     SELECT  学号,姓名,性别,所在院系

     From  学生表

     WHERE  所在院系＝’计算机’  AND  性别＝’女’

注意：

学生表的字段名可以和新学生表不同。但要求列数相同，对应列的类型一致。

2. 使用UPDATE修改记录

格式：UPDATE 更新的数据表或视图 

　　  SET 表达式 WHERE 检索条件

（1）修改一条记录

 例：将学号为“508013101“的学生的年龄改为20

Update 学生表

Set 年龄=20

Where 学号=’508013101’

（2）修改多条记录

例：将学生表中所有学生的所在院系改为‘计算机‘

Update 学生表

Set  所在院系=’计算机’

（3）带子查询的修改语句

例：将计算机全体学生的成绩加8分

方法一：

UPDATE 成绩表 

SET 成绩=成绩＋8

WHERE 学号 IN（SELECT 学号  FROM  学生表 WHERE 所在院系=’计算机’）

方法二：

UPDATE 成绩表 

SET 成绩=成绩＋8

WHERE  (SELECT  所在院系  FROM  学生表  

WHERE  学号＝成绩表.学号)＝‘计算机‘

说明：

　 SET指定需要更新的字段，格式是“SET 变量名=新值表达式” 

　 通过WHERE子句指定需要更新的行；

3. 使用DELETE删除数据行

格式： DELETE 

FROM 数据表或视图 

　　　　WHERE 检索条件

（1）删除一条记录

例：将学号为“508013101“的学生删除

Delete from 学生表

Where 学号=’508013101’

（2）删除多条记录

例：将学生表中电子系的所有学生全部删除

Delete from 学生表

Where 所在院系=’电子’

（3）带子查询的删除语句

例：删除计应053所有学生的选修课程记录　　

方法一：delete from 成绩表

where 学号 IN (select 学号 from 学生表 where 班级名='计应053')

方法二：delete from 成绩表

where '计应053'=(select 班级名 from 学生表 where  学号=成绩表.学号)

说明:　WHERE子句指定需要删除的行；未指定将删除所有行；

　　　若删除表，使用 DROP TABLE语句下载本文