SQL Server中常用的SQL语句

本文主要介绍SQL Server中常用的SQL语句。

1、概述

名词

笛卡尔积、主键、外键

数据完整性

表连接

SQL语言的构成

2、查询概述

查询包括：单表查询、连接查询、带有exists的相关子查询、集合操作四中。select...from常用语句执行过程

select…            ⑤    投影
from…              ①    table→内存
where…             ②    选取元组
group…             ③    分组
having…            ④    选择分组
[{union|…}         ⑥    查徇结果的集      合运算
select…  ]         ①～⑤
order by…          ⑦    排序输出

3、单表查询

group by 只有出现在group by子句中的属性，才可出现在select子句中。

用order by子句对查询结果按照一个或多个列的值进行升/降排列输出,升序为ASC；降序为desc,空值将作为最大值排序

having 与 where的区别

4、连接查询

连接查询包括：多表连接查询、单表连接查询(自连接)、外连接查询、嵌套查询4种

连接条件一

[表名1.] 列名1 比较运算符  [表名2.]列名2

连接条件二

[表名1.]列名1  between [表名2.]列名2 and [表名2.]列名3

连接条件中的列名称为连接字段，对应的连接字段应是可比的。

执行过程：采用表扫描的方法，在表1中找到第一个元组，然后从头开始扫描表2，查找到满足条件的元组即进行串接并存入结果表中；再继续扫描表2，依次类推，直到表2末尾。再从表1中取第二个元组，重复上述的操作，直到表1中的元组全部处理完毕。

4.1 单表连接（自连接）

用表别名把一个表定义为两个不同的表进行连接。

例：查找至少选修了2号和4号课程的学生的学号

select FIRST.sno
from SC as FIRST, SC as SECOND 
where FIRST.Sno=SECOND.Sno and FIRST.cno='s2' and SECOND.cno='4'

4.2 外连接查询

外连接查询包括：Left join、right join、full join

4.3 嵌套查询

4.3.1 返回单个值的子查询

例：求与“刘力”同一个系的学生名，年龄

方法一：
select Sname, Sage 
from student 
where Sdept = (select sdept from student where  Sname = "刘力");

方法二：
select FIRST.Sname, FIRST.Sage 
from Student FIRST, Student SECOND
where FIRST.Sdept = SECOND.Sdept AND SECOND.Sname = "刘力";

4.3.2 返回一组值的子查询

例：求选修“C6”课程且成绩超过90分的学生

方法一：
select * from 
student 
where sno IN (select sno from SC where Cno="C6" AND Grade>90);

方法二（连接查询 ）：
select student.*		
from student，SC
where Student.Sno=SC.Sno AND Cno="C6" AND Grade>90;

例：求比计算机系中某一学生年龄小的其他系的学生

方法一：
select * 
from student 
where
    sdept!="CS" AND  
    sage < ANY (select Sage from Student where Sdept="CS");
	
方法二：
select  *
from Student
where   
   Sdept!=’CS’ AND  
   Sage < (select MAX(Sage) from Student where Sdept="CS");

4.3.3 多重子查询

例：求D01部门中工资与国贸系中任意职工相同的职工姓名和工资

表结构：
Teacher(tno, tname, salary, dno)
Department(dno, dname)

查询语句：
select Tname，Salary
from Teacher
where 
    Dno = "D01" AND 
    salary IN(
        select salary from  teacher
        where Dno =（select DNO from department where  Dname="国贸")
    );

例：求工资介于“张三”与“里司”两个之间的职工

select *
from teacher
where
    Salary >= (select MIN(Salary) from teacher where Tname IN ("张三", "里司")) AND  
    Salary <= (select MAX(Salary) from teacher where Tname IN ("张三", "里司");

4.3.4 在from语句中使用子查询，对查询结果定义表名及列名

例：求平均成绩超过80分的学号及平均成绩

select Sno, avg_G
from (select Sno, avg(Grade) from SC group by Sno) AS RA(Sno, avg_G)
where avg_G > 80;

AS RA(Sno, avg_G)，为查询作为定义表名（RA）和列名（Sno, avg_G）

5、带有exists的相关子查询

例：求所有选修了“C1”课程的学生名。

不相关子查询：    
select Sname from student where sno IN ( select sno from SC where Cno = "C1" );
 
相关子查询
select Sname from student
where exists (select * from SC where student.sno=SC.sno AND Cno = "C1" );

相关子查询执行过程：先在外层查询中取student表的第一个元组（记录），用该记录的相关的属性值（在内层where子句中给定的）处理内层查询，若外层的where子句返回‘TRUE’值，则此元组送入结果的表中。然后再取下一个元组；重复上述过程直到外层表的记录全部遍历一次为止。

例：查询选修了所有课程的学生的姓名（续）

select Sname
from student
where not exists (
    select * 
    from Course
    where not exists (
        select * from SC where 
        student.sno=SC.sno AND Course.Cno=SC.Cno
    )
);

例：查询至少选修了S1所选的全部课程的学生名

select Sname
from student
where not exists(
    select *
    from SC SCX			
    where 
        SCX.sno="s1" AND 
        not exists (
            select *
            from SC SCY
            where student.sno=SCY.sno AND SCX.Cno=SCY.Cno
        )
);

6、SQL的集合操作

6.1 “并”操作，例：查询计算机系的学生或者年龄不大于19岁的学生，并按年龄倒排序。

select * from student where Sdept="CS"
UNION
select * from student where AGE <= 19
order by AGE desc

6.2 “交”操作，例：查询计算机系的学生并且年龄不大于19岁的学生，并按年龄倒排序。

(select * from student where Sdept = "CS") 
INTERSECT
(select * from student where AGE <= 19)
order by AGE desc

6.3 “差”操作，例：查询选修课程1但没有选修课程2的学生。

select Sname, Sdept
from student
where sno IN (
    (select sno from SC where Cno="1")
    EXCEPT
    (select sno from SC where Cno="2")
)

7、插入操作

格式：insert  into  表名[(列名1,…)]  values  (列值1,…)

插入一已知元组的全部列值

insert into student values("2003001", "陈冬", 18, "男", "电商", "管理学院", "徐州");

插入一已知元组的部分列值

insert into SC(Sno，Cno) values ("2003001", "C003");

插入子查询的结果

例：设关系S_G(Sno,avg_G),把平均成绩大于80的男生的学号及平均成绩存入S_G中

insert into S_G(sno，avg_G) (
    select sno, avg(GRADE)
    from SC
    where Sno IN (select Sno from Student where SEX="男")
    group  by  Sno
    having  avg(GRADE) > 80
);

8、删除操作

格式： 
delete from 表名 [where 条件];

9、修改操作

update语句一次只能操作一个表。

格式1：
update 表名 [别名]
set 列名 = 表达式, ...
[where 条件];

格式2：
update 表名 [别名]
set (列名, ...) = (子查询)
[where 条件];

例：工种为SALESMEN的职工的工资改为工种平均工资的110%

update EMPLOYEE
set Salary = (select 1.1 * avg(Salary) from EMPLOYEE where JOB="SALESMEN")
where JOB="SALESMEN";

例：将所有学生的年龄增加1岁

update student set Sage=Sage+1;

10、数据定义

创建课程表

create table SC (
    sno CHAR(6) not null,
    Cno CHAR(6) not null,
    Grade smallint default null
)
primary key (sno,Cno)
foreign key (sno) references student(sno)
foreign key (Cno) references Course(Cno)
check (Grade between 0 AND 100);

常用的索引:唯一索引和聚簇索引

唯一索引

create UNIQUE INDEX  Stusno ON Student(Sno ASC);

聚簇索引

建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致

create CLUSTER INDEX Stusname ON Student(Sname);

在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。Sql server中的表示方式create clustered index。某些DMBS不支持聚簇索引，所以用前一定要查使用说明。

删除索引

删除索引时，系统会从数据字典中删去有关该索引的描述。

DROP INDEX [表名.]<索引名>;

例：删除Student表的Stusname索引

DROP INDEX Student.Stusname;

11、视图

例：建立电商系学生的视图

create view ec_student
as 
select sno, sname, age from student where dept="ec"

删除视图

DROP VIEW <视图名>

一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用DROP VIEW语句将他们一一删除。