《数据库 - SQL Server开发》第4章 SQL Server中常用的SQL语句

4.1概述

 数据完整性
 实体完整性：主属性不能为空值，例如选课表中学号和课程号不能为空
 参照完整性：表中的外键取值为空或参照表中的主键
 用户定义完整性：取值范围或非空限制，例如：性别（男女），年龄（0-130）

 表连接
 自然连接：与等值连接（a.id=b.id）相比，连接后的表只有一列id，而不是两列（a.id和b.id）。
 半连接：与等值连接（a.id=b.id）相比，连接后的表只有A表的列，被B表“多次匹配”列会显示为一行。
 左外连接：left join
 右外连接：right join
 全外连接：full join
 全内连接：inner join
 SQL语言的构成
 DDL语言：数据定义，定义基本表、视图、索引；
 DML语言：数据操纵，查询、增加、修改、删除
 DCL语言：权限

图1

4.2创建、删除数据库

 创建数据库
要想通过代码方式创建数据库，点击【新建查询】，输入以下代码。

--创建数据库  
create database zgsDB  
on primary --默认就属于primary文件组，可省略  
(  
    name = 'zgsDB', --数据文件的逻辑名称  
    filename = 'D:\zgsDB.mdf', --数据文件的物理名称  
    size = 3mb, --数据文件的初始大小  
    --maxsize = 100mb --数据文件增长的最大值  
    filegrowth = 1mb --数据文件的增长量  
)  
log on  
(  
    name = 'zgsDB_log', --日志文件的逻辑名称  
    filename = 'D:\zgsDB_log.ldf', --日志文件的物理名称  
    size = 1mb, --日志文件的初始大小  
    filegrowth = 10% --日志文件的增长率  
)  

 删除数据库
要想删除数据库，点击【新建查询】，输入以下代码。

--删除数据库  
use master  
if exists(select * from sysdatabases where name = 'zgsDB')  
drop database zgsDB  

4.3创建、删除表

 创建表
要想创建表，点击【新建查询】，输入以下代码。

--创建表  
create table Emploee  
(  
    Id int identity(1,1) primary key, --主键 标识从1开始增长为1  
    Name nvarchar(10) not null, --不能为空  
    Description nvarchar(100) --可以为空  
)

 删除表
要想删除表，点击【新建查询】，输入以下代码。

use zgsDB  --切换表
--删除表  
if exists(select * from sysobjects where name = 'zgsDB')  
drop database Emploee  

4.3数据的增、删、改、查

4.3.1添加数据

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

--第一种方式
insert into 表名(列名1,列名2) values(值1,值2)
insert into Class(ClassName, ClassDesc) values('4班','史上最牛x的班级,以后都是最牛的')

--第二种
insert into 表名 values(值)
insert into Class values('1班','比逊色了一些')

--第三种方式,一次性插入多条数据
insert into Class values('4班','比逊色了一些')
insert into Class values('5班','比逊色了一些')
insert into Class values('6班','比逊色了一些')
insert into Class values('7班','比逊色了一些')

--第四种方式--一次插入多条数据
insert into Class(ClassName, ClassDesc)
select '1班','不知道什么样' union
select '2班','啥样啊1' union
select '2班','啥样啊2' union
select '2班','啥样啊3' 

【注】添加数据显示的受影响的行数。

4.3.2删除数据

格式：delete from 表名 [where 条件];
 只能对整个元组操作，不能只删除某些属性上的值；
 只能对一个关系（表）起作用，若要从多个关系（表）中删除元组，则必须对每个关系分别执行删除命令。

--第一种,数据没了,表还在,但是id接着删除前的id继续加1
delete from class where classid=15

--第二种,表没了,数据也没了
drop table class--轻易不要尝试

--第三种方式,表存在,数据没了,id从1开始
truncate table class 

【注】delete和truncate的区别：
(1)truncate语句非常高效。由于truncate操作采用按最小方式来记录日志，所以效率非常高，对于几百万条数据使用truncate删除只要几秒，而对于delete则可能需要几个小时。
(2)truncate会把表中的自动编号重置为默认值
(3)truncate不会触发delete触发器

4.3.3修改数据

【格式1】

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

【格式2】

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

update 表名 set 列1=值1,列2=值2   
update Class set ClassDesc='都是好厉害的'  
--修改或者更新表中的数据返回的是受影响的行数  
update Class set ClassDesc='都很厉害啊' where ClassId<>1  
  
--把名字为李三的人 他的性别变成女 同时年龄变成12  
update student set TSGender=0 ,TSAge=12 where TSName='李三'  
--年龄大于25岁的所有的人年龄再加1  
update student set TSAge=TSAge+1 where TSAge>25  

【注意】update语句一次只能操作一个表。

4.3.4查询数据

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

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

4.3.4.1单表查询

group by 只有出现在group by子句中的属性，才可出现在select子句中。
用order by子句对查询结果按照一个或多个列的值进行升/降排列输出,升序为ASC；降序为desc,空值将作为最大值排序。

select * from student --查询表中所有列的数据  
select tsname,tsage,tsgender from student --查询特定列的数据  

--给查询出的数据的列标题起别名  
--第一种方式  
select tsname as '姓名',tsage as '年龄' from student  
--第二种方式  
select tsname as 姓名,tsage as 年龄 from student  
--第三种方式  
select 名字=tsname,年龄=tsage from student  
select 名字=tsname,年龄=tsage from student where TSGender=1  

--查询所有的数据中的前10条数据，数据库中的数据是已无序的方式存储  
select top 10 * from student  
--查询年龄最小的5个学生的信息  
select top 5 * from student order by TSAge  
--从大到小排序 查询的是 年龄最大的百分之10的学生信息  
select top 10 percent * from student order by TSAge desc --10%  
select * from student  

--去除重复  
select distinct TSName from student  

--聚合函数实际就是将多行数据合并为一行，其中NULL值不参与聚合函数的计算  
--查询总共多少条的数据  
select COUNT(*) from student  
  
--查询的是最高的数学成绩  
select MAX(TSMath) from TblScore  

--数学成绩最高的这个人的英语成绩和数学成绩  
select MAX(tsmath),TSEnglish from TblScore --错误写法  
select top 1 tsenglish,TSMath from TblScore order by TSMath desc --正确写法  
  
--成绩表中数学成绩最低的是多少  
select MIN(TSEnglish) from TblScore  
  
--总和  
select SUM(tsenglish) from TblScore  
  
--平均值  
select AVG(tsenglish) from TblScore  
  
--聚合函数可以联合使用  
select   
    MAX(tsenglish) as 英语最高成绩,  
    MIN(tsenglish) as 最低成绩,  
    SUM(tsenglish) as 总和,  
    AVG(tsenglish) as 平均成绩  
from TblScore  

--区间查询  
--between and  
--第一种方式  
select * from student where TSAge>=20 and TSAge<=30 and TSGender=1  
--第二种方式  
select * from student where TSGender=1 and TSAge between 19 and 29 --优先用between and，已经过内部优化了，查询速度快  
  
--in  
--第一种方式  
select * from student where TClassId=1 or TClassId=2 or TClassId=3  
--第二种方式  
select * from student where TClassId in(1,2,3) --优先用in<span style="font-family: Arial, Helvetica, sans-serif;">，已经过内部优化了，查询速度快</span>  

--模糊查询  
--查询的是以张开头的所有的名字  
select * from tblstudent where TSName like '张%' --%匹配任意多个字符  
--查询的是以张开头的名字，只有两个字  
select * from tblstudent where TSName like '张_' --_匹配一个字符  
  
--查询的是以张开头的名字，三个字  
--第一种方式  
select * from TblStudent where tSName like '张%' and LEN(tSName)=3  
--第二种方式  
select * from tblstudent where TSName like '张__'  
  
--第2个字符为字母  
select * from TblStudent where tSName like '张[a-z]%'  
select * from TblStudent where TSName like '%张%'  
select * from TblStudent where TSName like '张[%]%' --第2个字符为%  
select * from TblStudent where tSName like '张[0-9]_'  
select * from TblStudent where tSName like '张[^0-9]_' --第2个字符不为数字  
select * from TblScore where english = null  

--空值查询  
--错误写法select * from TblScore where TSEnglish = null  
select * from TblScore where TSEnglish is null  
--错误写法select * from TblScore where TSEnglish != null  
select * from TblScore where TSEnglish is not null  

--排序  
--英语成绩高的按数学成绩排  
select * from TblScore order by TSEnglish desc,TSMath desc  
select * ,总成绩=(TSEnglish+TSMath)from TblScore order by (tsEnglish+tsMath) desc  

--分组查询  
--例1：请从学生表中查询出每个班的班级Id和班级人数  
select tclassid as 班级的id,COUNT(*) as 班级的人数 from student group by TClassId  
having COUNT(*)>3  
--having 是在分组后再次进行筛选.......  
--例2：请从学生表中查询出每个班的班级Id和班级中男同学的人数   
select tclassid as 班级的id,COUNT(*) as 班级的人数 from student where TSGender=1 group by TClassId  

--case end分组查询  
select    
数学成绩=  
case  
    when TSEnglish>100 then '优'  
    when TSEnglish>90 then '良'  
    when TSEnglish>80 then '中'  
    when TSEnglish>70 then '及格'  
    when TSEnglish is NULL then '缺考'  
    else '不及格'  
end  
from TblScore  
  
select (case when a>b then a else b end),(case when b>c then b else c end ) from t8  

--把一个查询的结果放在另外一个查询中使用叫子查询  
select * from (select * from student where TSGender=1) as t where TSAge>20

 having 与 where的区别
where 决定哪些元组被选择参加运算，作用于关系中的元组；
having 决定哪些分组符合要求，作用于分组；

4.3.4.2连接查询

连接查询包括：多表连接查询、单表连接查询(自连接)、外连接查询、嵌套查询4种
 连接条件一
[表名1.] 列名1 比较运算符 [表名2.]列名2
 连接条件二
[表名1.]列名1 between [表名2.]列名2 and [表名2.]列名3
连接条件中的列名称为连接字段，对应的连接字段应是可比的。
执行过程：采用表扫描的方法，在表1中找到第一个元组，然后从头开始扫描表2，查找到满足条件的元组即进行串接并存入结果表中；再继续扫描表2，依次类推，直到表2末尾。再从表1中取第二个元组，重复上述的操作，直到表1中的元组全部处理完毕。

4.3.4.2.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.3.4.2.2 外连接查询

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

4.3.4.2.3 嵌套查询

 在select … from … where语句结构的where子句中可嵌入一个select语句块
 其上层查询称为外层查询或父查询，其下层查询称为内层查询或子查询
 SQL语言允许使用多重嵌套查询
 在子查询中不允许使用order by子句
 嵌套查询的实现一般是从里到外，即先进行子查询，再把其结果用于父查询作为条件
 返回单个值的子查询
例：求与“刘力”同一个系的学生名，年龄
方法一：

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 = "刘力";

 返回一组值的子查询
例：求选修“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");

 多重子查询
例：求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 ("张三", "里司");

 在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）

4.3.4.3带有exists的相关子查询

 不相关子查询：子查询的查询条件不依赖于父查询的称为不相关子查询。
 相关子查询：子查询的查询条件依赖于外层父查询的某个属性值的称为相关子查询，带exists 的子查询就是相关子查询
 exists表示存在量词，带有exists的子查询不返回任何记录的数据，只返回逻辑值“True” 或“False”
例：求所有选修了“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 *
 exists + 子查询用来判断该子查询是否返回元组
 当子查询的结果集非空时，exists 为“True”；当子查询的结果集为空时，exists为“False”。
 not exists ：若子查询结果为空，返回“TRUE”值，否则返回“FALSE”
例：查询选修了所有课程的学生的姓名（续）

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
        )
);

4.3.4.4 SQL的集合操作

 属性个数必须一致、对应的类型必须一致；
 属性名可以不一致， 最终结果集采用第一个结果的属性名；
 缺省为自动去除重复元组，除非显式说明ALL；
 order by放在整个语句的最后；
 “并”操作，例：查询计算机系的学生或者年龄不大于19岁的学生，并按年龄倒排序。

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

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

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

 “差”操作，例：查询选修课程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")
) 

 插入的同时实现查询

//在数据库中@@开头表示全局变量
insert into ClassInfo values('1班',"50");select @@classID;
insert into ClassInfo values('1班',"50") output @classID;
//第一个语句最后的select后可以跟多个字段
//第二个语句output后可以输出多个字段

4.4对列的增、删、改和约束

对列的操作使用alter关键字。

--手动删除一列(删除EmpAddress列)  
alter table Employees drop column EmpAddress  
--手动增加一列(增加一列EmpAddr varchar(1000))  
alter table Employees add EmpAddr varchar(100)  
--手动修改一下EmpEmail的数据类型(varchar(200))  
alter table Employees alter column EmpEmail varchar(200)  
--为EmpId增加一个主键约束  
alter table Employees add constraint PK_Employees_EmpId primary key(EmpId)  
--非空约束,为EmpName增加一个非空约束  
alter table Employees alter column EmpName varchar(50) not null  
--为EmpName增加一个唯一约束  
alter table Employees add constraint UQ_Employees_EmpName unique (EmpName)  
insert into Employees values('小虎','男',23,'xh@itcast.cn','北京')  
--为性别增加一个默认约束，默认为'男'  
alter table Employees add constraint DF_Employees_EmpGender default('男') for EmpGender  
--为年龄增加一个检查约束：年龄必须在1-120岁之间，含岁与岁。  
alter table Employees add constraint CK_Employees_EmpAge check(EmpAge>=1 and EmpAge<=120)  
--创建一个部门表，然后为Employee表增加一个DepId列。  
alter table Employees add DepId int not null  
--增加外键约束  
alter table Employees add constraint FK_Employees_Department_DepId foreign key(DepId) references Department(DepId)

4.5表的复制或备份

表的复制或备份。

--将student表复制到allstudent表，其中allstudent是没有的，通过执行sql语句会自动创建  
--第一种方式：复制表的结构同时也复制原表内的数据  
select * into allstudent from student  
  
--第二种方式：只拷贝表的结构，不复制表中的数据，但是执行效率低  
select * into allstudent from student where 1<>1  
  
--第三种方式：只拷贝表的结构，不复制表中的数据，但是执行效率高  
select top 0 * into allstudent from student  
  
--第四种方式：向已存在的表备份数据（前提是两个表的结构相同，并且要插入数据的表提前建好了）  
insert into allstudent select * from student

4.6数据定义

创建课程表

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);

常用的索引:唯一索引和聚簇索引
 唯一索引
 对于已含重复值的属性列不能建UNIQUE索引；
 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。
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;