SQL Server基础

1.特点：

(1).综合一体，集DDL,DCL,DML语言于一体，可以独立完成数据库生命周期中的全部活动，包括定义关系模式，录入数据以及建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，在数据库投入运行后，还可以根据需要随时修改模式，既不影响数据库的运行，又使系统具有良好的可扩充性。

(2).

DML 数据操纵语言

​ Insert 插入 insert into 表名 （列,列，…） values (对应的值，对应的值)

​ Update 更新 update 表名 set 列名=值, 列名=值 where 条件

​ Delete 删除 delete from 表名 where 条件
DDL 数据定义语言 创建数据库及其对象 Create/Alter/Drop Database/Table/View/Proc/Index
DCL 数据控制语言 用于授予或回收访问数据库某种特权，对数据实行监视等 commit 提交 rollback 回滚 grant 授权

(3).非过程性

只需指明做什么，而不必指明怎么做，因此无需了解存取路径

(4).既是自含式语言，又是嵌入式语言

(5).面向集合的操作方式

查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合

T-SQL：Transact-SQL 基于SQL（Structured Query Language） 结构化查询语言，用于应用程序和数据库之间沟通的编程语言。Sql Server 支持的，脚本语言。SQL语言：一种有目的的编程语言，用于存取数据、查询、更新和管理关系数据库，高级的非过程化的编程语言。作用：可以完成移植，提高数据访问效率，完成对数据的相关处理。

2.数据库的数据类型

数值型

int	4个字节（-2147483648~2147483647）
smallint	2个字节（-32768~32767）
tinyint	1个字节（0-255）

二进制型

bit	一个字节，存储0/1/null的二进制字段
binary	固定长度的二进制数据，最多8000字节
varbinary(n)	可变长度的二进制数据，最多8000字节
varbianry(max)	可变长度的二进制数据，最多2GB字节
image	可变长度的二进制数据，最多2GB字节

浮点型

float	近似数值，存在精度损失，可存储153的可变精度的浮点值（124占用4字节存储空间，25~53占用8字节存储空间）
decimal	精确数值，不存在精度损失，最大精度38。decimal(p,q),默认（18，2）1<=p<=38,0<=q<=p
real	近似数值，存在精度损失，最大精度38，占用4个字节

货币型

smallmoney	精确到小数点后4位，占用4位存储空间
money	精确到小数点后4位，占用8位存储空间

字符串型

char（n)	每个字符1个字节存储空间，可存储1~8000个定长字符串，字符串长度为n
varchar(n)	可变长度的字符串，最多8000字节
varchar(max)	可变长度的字符串，最多1073,742,824字节
text	可变长度的字符串，最多2GB字节

unicode字符串

nchar（n)	每个字符2个字节存储空间，可存储1~4000个定长字符串，字符串长度为n
nvarchar(n)	可变长度的字符串，最多4000字节
nvarchar(max)	可变长度的字符串，最多1073,742,824/2字节
ntext	可变长度的字符串，最多2GB字节

前面带n的，存储中文汉字、英文、数字，1个长度存储大小为2个字节，不带n,中文1个长度，英文2个长度，容易乱码

日期型

datetime2	精度更高，100纳秒 ，6-8字节
datetime	0.00333s,8字节
smalldatetime	精度1分钟，时间范围小，4个字节
date	仅仅存储日期，1天，3个字节
time	仅仅存储时间，100ns，3-5个字节

3.数据库常用对象

数据库常用对象：

1.表 包含数据库中所有数据的对象，行和列组成 ，用于组织和存储数据。

2.字段 表中的列 一个表可以有多个列，自己的属性：数据类型（决定了该字段存储哪种类型的数据），大小（长度）

3.视图 （虚拟表）一张或多张表中导出的表用户查看数据的一种方式，结构和数据是建立在对表的查询基础之上的。

4.索引 为了给用户提供一种快速访问数据的途径，索引是依赖于表而建立，检索数据时，不用对整个表进行扫描，可以快速找到所需的数据。

(普通索引、唯一索引、候选索引、聚集索引、非聚集索引)

5.存储过程 是一组为了完成特定功能的SQL语句的集合（可以有查询、插入、修改、删除），编译后， 存储在数据库中，以名称进行调用， 当调用执行时，这些操作就会被执行。

6.触发器 在数据库中，属于用户定义的SQL事务命令集合，针对于表来说，当对表执行增删改操作时，命令就会自动触发而去执行。

7.约束 对数据表的列，进行的一种限制。可以更好的规范表中的列。

8.缺省值 对表中的列可以指定一个默认值，当进行插入时，如果没有为这个列插入值，那么就会自动以预先设置默认值进行自动补充。

9.用户 管理员用户（可以对数据库进行修改删除），普通用户（只能进行查看）

10.图表 数据库表之间的关系示意图，利用它可以编辑表和表之间的关系

4.创建表及主外键

1.工具创建表 列 数据类型 是否null

一个表中，会存很多条记录，需要一个列来唯一标识一条数据。
主键：唯一标识一条数据值 ，不能重复 不能为空

标识列：一个列设置成标识列，它就不能再手动插入，插入时，自动生成的。这个列，类型必须是不带小数的数值型整型
标识列：标识种子 第一条记录标识列的值 100 增量 3 delete 删除了数据，再插入，就会出现不连续,transact连续，但慎用

2.创建主键 联合主键 唯一标识

​ 创建一个主键，同时自动创建了一个聚集索引

3.创建外键：

一般在两个表之间要建立关联时候，创建一个列创建为外键，它在另一个表必须是主键
外键：DeptId UserInfos 外键表 DeptInfos 主键表

两个表一旦建立外键关系，外键表里的对应的外键列，它的值必须是它对应的主键表里的主键值，如果你想插入一个主键中不存在 的值，你是插入不进去的。一个表里可以有多个外键，也可以没有，一个表只能有一个主键，也可以没主键，但一般都会设置一个主键。

5.数据库约束

1.约束定义:规定表中的数据规则。如果存在违反约束的数据行为，行为就会被阻止。在什么时候可以创建约束呢？使用软件创建，创建表之后， 使用脚本创建表：可以在创建的过程中，也可以在创建后再来建立约束。

2.分类

主键 Primary Key约束 唯一性、非空，不能修改外键

Foreign Key约束 加强两个表的一列或多列数据之间的连接的。 先建立主表的主键，然后再定义从表中的外键。只有主表中的主键才能被从表用来作为外键使用。主表限制了从表更新和插入的操作。当删除主表中的某条数据，应该是先删除从表中相关的数据，再删除主表。

Unique约束 唯一性约束 确保表中的一列数据没有相同的值。与主键约束相似，但又不同。主键只能有一个，但一个表中可以定义多个唯一约束。唯一键可以为NULL，但主键不可以

Check约束：通过逻辑表达式来判断数据的有效性，用来限制输入一列或多列的值的范围。

Default约束：默认值约束。用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果没有设置默认值，系统就会默认为NULL。

6.创建数据库

数据库

​ 系统数据库 记录所有系统级信息，如master,还记录所有其他数据库的存在，数据库文件的位置，SQL的初始化信息

​ 用户数据库：示例数据库和用户创建的数据库

​ 创建数据库的时候要使用master数据库

​ use master --选择要操作的数据库

​ go–批处理命令–

数据库的物理存储结构：以文件的形式存储在磁盘中

数据库文件

​ 主数据文件----.mdf,只能有一个,至少要有一个

​ 次要数据文件-----.ndf，主数据库文件存不下，就可以用他存，可以没有，可以多个

​ 事务日志文件-----.ldf，至少要有一个，记录了每一个事务的开始、改变和取消

数据库文件组

（一个文件只能存在于一个文件组，一个文件组也只能被一个数据库使用）

​ 主文件组 包含主数据文件和未指定组的其他文件

​ 用户自定义的文件组 首次创建数据库或以后修改数据库时明确创建的任何文件组

文件组中只能包含数据文件，日志文件不属于任何文件组，文件组中的文件不自动增长，除非文件组中的文件都没有可用空间、

创建数据库

create database TestNewBase --数据库名称 

on primary --主文件组,如果没有指定主文件，那么脚本开始的第一个文件为主文件

 ( 

 name=TestNewBase,--数据库主要数据文件的逻辑名

 filename=**'**D:\课件\DBase\TestNewBase.mdf**'**,--主要数据文件的路径(绝对路径)

 size=5MB,--数据库主要文件的初始大小，未指定的话，将与model文件大小相同

maxsize=unlimited，未指定将增长到磁盘变满

 filegrowth=1MB--文件的增量，若未指定MB、KB、%，则默认为MB

 ) 

Filegroup Exe4_2Group1

{

name=TestNewBase1,

filename='D:\课件\DBase\TestNewBase1.mdf\',

size=5MB,

maxsize=unlimited，

 filegrowth=1MB

}---创建数据库的同时，创建了文件组

log on --创建日志文件，如果没有指定，将自动创建，数据文件大小为综合的25%或512KB，取两者中的较大者

( 

name='TestNewBase_log',--数据库日志文件的逻辑名

 filename='D:\课件\DBase\TestNewBase_log.ldf',--日志文件的路径(绝对路径)

 size=1MB,--数据库日志文件的初始大小 ，未指定的话，大小为1MB

maxsize=unlimited，未指定将增长到磁盘变满

filegrowth=10%--日志文件的增量，若未指定MB、KB、%，则默认为MB

 )

 go

注意：以上go语句不是T-SQL中的一个语句，而是可为SQL查询编辑器识别的命令，它用来通知执行go之前的多个SQL语句

删除数据库

-------可以一次删除一个或多个数据库

drop database TestNewBase 

go

修改数据库

Alter DataBase xxxx

Add flie

(

name=

filename=

size=

filegrowth=

)

///////////////////////////////////////

Alter DataBase xxxx

remove file---------删除文件描述和物理文件

(

name=

filename=

size=

filegrowth=

)

////////////////////////////////////////

Alter DataBase xxxx

modify flie------------修改给定文件的属性

(

name=     ,--选择文件

filename=                        ----------------进行修改

size=                    对现有文件进行修改，指定容量的大小必须大于当前容量的大小

filegrowth=

)

//////////////////////////////////////////////

Alter DataBase xxxx

modify name=xxxxxx------------修改数据库的名字

注意：应确保除了自己之外没有人现在在使用该数据库，才可以进行名字的修改

//////////////////////////////////////////////

Alter DataBase xxxx

Add filegroup xxxx----文件组之前没有

go

Add flie

(

name=

filename=

size=

filegrowth=

)

to  filegroup xxxx

//////////////////////

Alter DataBase xxxx

Add flie

(

name=

filename=

size=

filegrowth=

)

to  filegroup xxxx-----文件组已有



Alter DataBase xxxx

modify  filegroup xxxx default---将其设置为默认文件组

remove  filegroup xxxx-----从系统中删除文件组

===============================================================

Alter DataBase xxxx

Add  log flie

(

name=

filename=

size=

filegrowth=

)

===================================================================

分离数据库（少用）

sp_detach_db'xxxxxxxx'

将其从SQL服务器中分离出去

附加数据库（少用）

—将数据文件添加到服务器中

use master

go

create database xxxx

on

(

fliename='xxxxxxx'

)

for attach

7.表的创建和管理

use TestBase

 go

 --创建表

 create table ProductInfos

 ( 

Id int identity(1001,1) primary key not null, --标识种子，增量

ProNo varchar(50) not null,

ProName nvarchar(50) not null, 

TypeId int not null, 

Price decimal(18,2) default (0.00) not null,

ProCount int default (0) null 

) 

go

create table ProductType

 ( 

TypeId int identity(1,1) primary key not null, 

TypeName nvarchar(20) not null 

)

 go

create table ProductInfos
(
   ID int IDENTITY(1,1) primary key   not null,
   ProNo varchar( 20 ) unique not null,
   ProName nvarchar(20)  not null,
   TypeId int FOREIGN KEY REFERENCES ProductType(TypeId) not null,
   Price DECIMAL(18,2)check (Price<500) DEFAULT (0.00) not null,
   ProCount int DEFAULT(0) null
  )
go

create table ProductInfos
(
ID int IDENTITY(1,1)    not null,
ProNo varchar( 20 )  not null,
ProName nvarchar(20)  not null,
TypeId int  not null,
Price DECIMAL(18,2) not null,
ProCount int  null
//primary(ID),
//FOREIGN KEY (TYPEID)REFERENCES PRODUCTTYPE(TYPEID)
)
go
ALTER TABLE PRODUCTINFOS ADD CONSTRAINT PK_PRODUCTINFOS PRIMARY KEY(ID)
ALTER TABLE PRODUCTINFOS ADD CONSTRAINT FK_PRODUCTINFOS FOREIGN KEY(TYPEID) REFERENCES PRODUCTTYPE(TYPEID)
ALTER TABLE PRODUCTINFOS ADD CONSTRAINT UQ_PRODUCTINFOS_PRONO UNIQUE (PRONO)
ALTER TABLE PRODUCTINFOS ADD CONSTRAINT UQ_PRODUCTINFOS_PRONAME UNIQUE (PRONAME)--第三行的uq和第四行的下标必须不同，不然就是参数不同
ALTER TABLE PRODUCTINFOS ADD CONSTRAINT CK_PRODUCTINFOS CHECK(PRICE<500)
ALTER TABLE PRODUCTINFOS ADD CONSTRAINT DF_PRODUCTINFOS DEFAULT (0) FOR PROCOUNT
ALTER TABLE PRODUCTINFOS ADD CONSTRAINT DF_PRODUCTINFOS_PRICE DEFAULT (0.00) FOR PRICE
ALTER TABLE PRODUCTINFOS DROP CONSTRAINT PK_PRODUCTINFOS 

细节点（少用）：又是需要创建一些中间表，来临时存储数据，在创建表时，使用“#”（在当前数据库使用）或“##”（在所有数据库内使用）

8.修改表

ALTER TABLE PRODUCTINFOS ADD PROREMARK NVARCHAR(MAX) NULL--在新增加字段时，不管表中是否有数据，新增加的字段值一律为欸null
ALTER TABLE PRODUCTINFOS DROP COLUMN PROREMARK
ALTER TABLE PRODUCTINFOS ALTER COLUMN PROREMARK VARCHAR(20)--修改列的属性

注意：ALTER TABLE 只允许添加满足下述条件的列: 列可以包含 Null 值；或者列具有指定的 DEFAULT 定义；或者要添加的列是标识列或时间戳列；或者，如果前几个条件均未满足，则表必须为空以允许添加此列。

9.删除表

Drop table xxx

不能删除有主键被别的表引用的表，必须先删除外键表，再删除主键表，如果想同时删除，必须列出引用表，再列主键表

直接在原来的脚本基础上进行修改先把脚本代码进行修改，再删除原来的表，然后再执行创建表的脚本代码这种方式，一般情况都不采用，后果很严重。除非表里数据不重要或是空表，可以使用上面这种方式。

修改列：一般不要修改列名，在设计时，定义好就不要改了。 修改的是数据类型，是否为空

修改列名 一般慎用 exec sp_rename ‘ProductInfos.ProCount’,‘Count’,‘column’

10.数据库完整性管理

实体完整性、参照完整性、用户定义完整性

实体完整性：

​ 标识列（identity(初始值，步长值))自动形成一个唯一的值

​ 主键约束（primary key)

​ 唯一性约束（unique)指定一个列或者多个列的组合具有唯一性，防止在列中输入重复的数据

​ 唯一性索引

用户自定义完整性：（限制用户向列中输入的内容）

​ default( )

​ check( )

​ not null

参照完整性

​ foreign key

11.插入数据

(1)

insert into表名 （*）

value（有多少个*就必须填满），（），（）……

INSERT INTO PRODUCTINFOS (PRONO,PRONAME,Typeid,PROCOUNT)
VALUES ('F', 'TT',1,1),( 'B','短袖',1,2),('C','长袖',1,3),('D', '卫衣',1,4)

(2)用select插入多行

insert into表名 （#,#）

select #,#

select #,#

INSERT INTO PRODUCTTYPE (TYPENAME)
SELECT '实用类6'
SELECT '实用类7'
SELECT '实用类8'
SELECT '实用类9'

INSERT INTO PRODUCTINFOS (PRONO,PRONAME,Typeid,PROCOUNT)
SELECT  'T','PP',4,2 UNION
SELECT  'H','短',5,2 UNION
SELECT  'V','长',6,3 UNION
SELECT  'L', '卫',7,4 

union 去除重复值

union all 允许重复值

insert into 表名（*）

select 有多少个*就必须填满 union all

select 有多少个*就必须填满 union all

select 有多少个*就必须填满 union

select 有多少个*就必须填满

注意：

用select插入多行 和多行，必须加上union /union all。

两种方法：要么选列名（可排除那些允许空值的列，但是不允许为空值的列必须写），不指定列名名的要全部插入

浮点型数据不能插入空值，否则会报错

12.克隆表

1. 目标表在数据库已经存在

   insert into Test(MName) —目标表

   select TypeName from ProductType --源表

2. 目标表之前数据库中不存在,执行操作时自动创建的

   select TypeName into Test2 --目标表

   from ProductType --源表

   insert into test2(typename,id)
   select typename ,typeid from producttype
   

select TYPENAME INTO TEST2
from producttype

13.更新数据

更新数据 update 几乎不会不用where条件 –主键不可以修改 --如果不加where条件,会把整张表的数据都修改了

update TEST2 
set typename ='ssss'
where id=10

14.删除数据

1)删除数据 delete from Table名 不加条件,会删除整个表数据,几乎都要加where条件

标识列 值还是接着删除前的值而自增,而不是从初始值开始

delete语句会造成标识列的值不连续

delete from Test
where Id = 20

如果我们想删除数据,让标识列的值恢复到初始值,怎么办?

2)truncate table Test 

表数据清空,恢复到初始化,标识列也恢复

表面上看,和delete from Test 效果一样

但truncate效率比delete from Test高.delete每删除一条数据,都会在日志里记录.truncate不会记录日志,不激活触发器,drop truncate 是即时操作,不能rollback,delete update insert 事务中,可以恢复.

–慎用truncate 一旦删除,不能恢复.

15.查询数据

1.查询所有数据

select * from UserInfos

2.查询表的部分数据

select id,prono,proname,typeid,price,procount
from productinfos
where id=18

3.列命别名为什么需要命别名 —解决需要显示中文列名

三种命名方式

select id as编号,prono,产品名称=proname,typeid 产品类型编号,price,procount
from productinfos

4.排序

主键,默认就有排序功能 从小到大

–升序 asc --降序 desc 空值最先显示

–不管是否有条件,还是分组,order by 永远放在最后

select UserId,UserName,Age from UserInfos order by Age,UserId

select id 编号,prono,proname 产品名称,typeid 产品类型编号,price,procount
from productinfos
order by id desc

select id 编号,prono,proname 产品名称,typeid 产品类型编号,price,procount
from productinfos
order by id asc

5.模糊查询和范围查询

% 0个或多个

_ 匹配单个字符

[] 范围匹配 括号中所有字符中的一个

[^] 不在括号中所有字符之内的单个字符

select *
from productinfos
where proname like '帽%'

select *
from productinfos
where proname like '__'

select *
from productinfos
where proname like 'T[ABTZ]'

select *
from productinfos
where proname like 'T[A|B|T|Z]'

select *
from productinfos
where proname like 'T[A-Z]'

不在这个范围的
select *
from productinfos
where proname like 'T[^A-S]

select *
from productinfos
where id between  18 and 28

select *
from productinfos
where prono in('',''),id not in(1229,2221,6007)

6.前面多少条、百比比

select top 10 *
from UserInfos 

select top 100 percent * 
from UserInfos

16.聚合函数

对一组值执行计算并返回单一的值,五种聚合函数：count 记录个数 sum 求和 avg 求平均 max 最大值 min 最小值

经常与select语句中group by的having语句结合使用

group by中不能放置聚合函数

having放置聚合函数

select sum(id) 编号总和
from productinfos

--注意 average是无效的
select avg(id) 编号平均
from productinfos

select max(id) 最大编号值
from productinfos

select count(*) 记录数
from productinfos

select count(id) 记录数
from productinfos

select min(id) 最小id值
from productinfos

在列名前加distinct,可以去除重复

逻辑查询的顺序 （非、且、或）

空值比较只能用 is null/is not null,而不能写成=null

17.分组查询和筛选

将查询结果按group by中指定列进行分组，该列值相等为一组,若在分组后还要按照一定条件进行筛选，用having语句

Select cno,avg(grade)as 平均成绩
from score 
group by cno
having avg(grade)>=80 --分组后的筛选条件 
order by cno desc

having语句作用于组，必须与group by子句连用，当然group by子句可以单独出现

where语句作用于基本表或视图

18.连接查询

自连接

查询选修“数据库原理”（课程号为08181192）课程的成绩高于学号2015874144的学生的成绩的所有学生信息，并按成绩从高到低排序

select x.*
from score x ,score y
where x.cno='08181192'and x.grade>y.grade and y.cno='o8181192'and y.sno='2015874144'
order by x.grade desc

查询每一课的间接先修课（先修课的先修课）

select x.cno,y.pcno
from course x ,course y
where x.pcno=y.cno 

内连接(inner join)

使用比较运算符进行表间数据的比较，分为等值连接和不等值连接

等值连接

select score,cno,cname,grade
from course,score
where course.cno=score.cno

select A.sno,A.sname,A.gender,C.cname,C.cno,B.grade
from student A inner join score B on A.sno=B.sno
               inner join course C on B.cno=c.cno
where A.gender='男'

等价于

select A.sno,A.sname,A.gender,C.cname,C.cno,B.grade
from student A,score B,course C 
where A.gender='男',A.sno=B.sno,B.cno=c.cno

不等值连接

select x.*
from score x inner join score y on  x.grade>y.grade and x.cno=y.cno
where  y.cno='o8181192'and y.sno='2015874144'
order by x.grade desc

外连接（outer join）

左外连接 left outer join

右外连接 right outer join

全外连接 full outer join

select B.score,A.cno,a.cname,B.grade
from course A left outer join score B on A.cno=B.cno

select B.score,A.cno,a.cname,B.grade
from course A right outer join score B on A.cno=B.cno

select B.score,A.cno,a.cname,B.grade
from course A full outer join score B on A.cno=B.cno

交叉连接（笛卡尔积）

没有where语句，返回所有数据行的笛卡尔积

select student.*,score.*
from student cross join score

19.嵌套查询

一个select查询语句不够用，即用上嵌套，在主嵌套中的where中连接另一个查询语句（由内向外查询）

单值嵌套查询（子查询的返回结果为一个值）

select sno,grade
from score
where cno=(
  select cno
  from course
  where cname='数据库原理'
)

等价于

select sno,grade
from score,course
where score.cno=course.cno and cname='数据库原理'

多值嵌套查询（子查询的返回值是一个数集）（any、all、in)

查询与“xxx”在同一个学院实习的学生(这里的in当如果限制学生不能双修学位时其实也是可以用=)

select sno,sname,depart
from student
where depart in(
   select  depart
   from student
   where sname='xxx'
)

等价于

select A.sno,A.sname,A.depart
from student A,student B
where A.depart=B.depart and B.sname="xxx"

查询选修了课程名为“数据结构"课程的学生的学号和姓名

select sno,sname
from student
where sno in(
   select sno
   from score
   where cno in(
      select cno
      from course
      where cname='数据结构'
   )
)

等价于

select A.sno,A.sname
from student A,score B,course C 
where C.cname='数据结构',A.sno=B.sno,B.cno=c.cno

找出每个学生超过他选修课程平均成绩的课程号

select cno
from score x
where grade>=(
  select avg(grade)
  from score y
  where y.sno=x.sno
)

等价于

select x.cno
from score x,score y
where x.sno=y.sno and x.grade>y.avg(grade)

all谓词

查询其他学院中比信息工程学院某一学生年龄小的学生的姓名和年龄

select age,sname
from student
where  age<any(
  select age
  from student
  where depart=(
    select no
    from  department
    where name='信息工程学院'
  )
)
  And depart<>(
    select no
    from department
    where name='信息工程学院'
  )

查询其他学院中比信息工程学院所有学生年龄小的学生的姓名和年龄

select age,sname
from student
where  age<all(
  select age
  from student
  where depart=(
    select no
    from  department
    where name='信息工程学院'
  )
)
  And depart<>(
    select no
    from department
    where name='信息工程学院'
  )

以上两例用聚合函数实现

select age,sname
from student
where  age<(
  select max(age)
  from student
  where depart=(
    select no
    from  department
    where name='信息工程学院'
  )
)
  And depart<>(
    select no
    from department
    where name='信息工程学院'
  )

和

select age,sname
from student
where  age<(
  select min(age)
  from student
  where depart=(
    select no
    from  department
    where name='信息工程学院'
  )
)
  And depart<>(
    select no
    from department
    where name='信息工程学院'
  )

20.集合查询

限定条件

（1）子结果集要有相同的结构

（2）子结果集的列数必须相同

（3）子结果集对应的数据类型必须可以兼容

（4）每个子结果集不能包含order by 和 compute 子句，因为它是对整个运算后的结果排序，而不是对单个数集

并集（union)

union 去除重复值

union all 允许重复值

查询信息工程学院的学生或年龄不大于19岁的学生

select *
from student
where depart='001'
union
select *
from student
where age<=19

交集（intersect)

查询信息工程学院的学生与年龄不大于19岁的学生的交集

select *
from student
where depart='001'
intersect
select *
from student
where age<=19

差集（except)

自动删除重复值

查询信息工程学院的学生与年龄不大于19岁的学生的差集

select *
from student
where depart='001'
except
select *
from student
where age<=19

21.select语句编写顺序和执行顺序

1.SQL Select 语句完整的执行顺序
(1) FROM 子句组装来自不同数据源的数据。
(2)WHERE 子句基于指定的条件对记录行进行行筛选。
(3)GROUP BY 子句将数据划分为多个分组。
(4)使用聚集函数进行计算。
(5)使用 HAVING 子句筛选分组。
(6)计算所有的表达式。

(7)SELECT 的字段。
(8)使用 ORDER BY 对结果集进行排序
2.一个 WHERE 语句各个部分的执行顺序
(8)SELECT(9)DISTINCT(11)<TOP_specification>< select lisp >

(1)FROM < left table>
(3)<join_type>JOIN < right table>.(2)ON < join condition>
(4)WHERE <where_condition>

(5)GROUP BY <group_by_lisi>

(6) WITH (CUBE|ROLLUP)

(7)HAVING <having_condition>

(10)ORDER BY <order by list
3.表达式的执行顺序
(1)先执行等号左边是变量的表达式(A类)，再执行等号左边是列名的表达式(B类)例如:
UPDATE tablename
SET columnNamc-@variable,@variable=@variable+l
先执行@variable-@variable+1，再执行 columnName=@variable
(2)如果有多个A类(或B类)表达式，按从左到右的顺序执行 A 类(或 B 类)表达式。例如:
UPDATE tablenamc
SET columnName=@variable,@variable-@variable+1,@variable=2*@variable
@variable。
先执行@variable=@variable+1，再执行@variable-2*@variable，最后执行 columnName=
(3)列名所代表的值永远是原值。例如:
UPDATE tablename
SET columnName=columnName+
【例 3.102】分析以下 SQL 语句能否执行成功:

SELECT sno,COUNT(sno) AS TOTAL

FROM student

GROUP BY sno

HAVING TOTAL>2

它不能执行成功，因为 HAVING的执行顺序在 SELECT之上，实际执行顺序如下：
1.FROM student
2.GROUP BY sno
3.HAVING TOTAL>2
4.SELECTsno,COUNT(sno)AS TOTAL
很明显，TOTAL 是在最后一句 SELECTsn0,COUNT(sno)AS TOTAL执行过后生成的新别名。因此，在 HAVING TOTAL>2 执行时是不能识别 TOTAL 的。

22.索引

1.索引的概述

索引是一种单独的，物理的、对数据库表中一列或多列的值进行排序的存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

当表中有大量记录时，若要对表进行查询，第一种搜索信息的方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作:第二种搜索信息的方式是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记求。
索引的优点主要有:
(1)大大加快数据的检索速度。
(2)创建唯一性索引，保证数据库表中每一行数据的唯一性。

(3)加速表和表之间的连接。
(4)在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

索引的缺点主要有
(1)索引需要占用物理空间。
(2)当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，降低了数据的维护速度
2，索引的分类
根据索引的存储结构不同，将其分为聚集索引和非聚集索引两类。

(1)聚集索引
聚集索引(Clustered)是将数据行的键值在数据表内排序并存储对应的数据记录，使得数据表的物理顺序与索引顺序一致。由于数据记录按聚集索引键的次序存储，因此聚集索引查找数据很快。但创建聚集索引时需要重排数据，所需空间相当于数据所占用空间的 120%。

由于一个表中的数据只能按照一种顺序来来存储，所以一个表中只能创建一个聚集索引
(2)非聚集索引。
非聚集索引(Non-Clustered)具有完全独立于数据行的结构。数据存储在一个地方，存储在另一个地方。使用非聚集索引不用将物理数据页中的数据按键值排序，通俗地说，"不会影响数据表中记录的实际存储顺序。在非聚集索引内，每个键值项都有指针指向包含该键值的数据行。一个表中可以有一个或多个非聚集索引。
当一个表中既要创建聚集索引又要创建非聚集索引时，应先创建聚集索引，再创建非集索引，因为创建聚集索引时将改变数据记录的物理在放顾序。
唯一索引要求建立索引的字段值不能重复，也就是在表中不允许两行具有相同的值引也可以不是唯一的，非唯一-索引的多行可以共京同一键值
聚集索引和非聚集索引都可以是唯一的。创建主键(PRIMARY KEY)或唯一性(UNIQUE)约束时，数据库引擎会自动为指定的列创建唯一索引
3.索引的管理

(1)创建索引:

CLUSTERED 聚集索引，如果没有指定建立聚集索引，则创建非聚集索引

not clustered 非聚集索引

unique 唯一性索引（唯一性缩影使用的列应该设置为not null,因为在创建唯一性索引时null会被认为重复）

create unique index Stusno
  on student(sno)
  
create unique index Coucno
  on course(cno)
  
create unique index SCno
  on score(cno DESC,sno ASC)

create clustered index Susname
  on student(sname)

(2)删除索引

当某个时期基本表中数据更新频繁或者某个索引不再需要时，需要删除部分索引

drop index 表名，索引名

(3)修改索引

Alter index  索引名  rename to 新的索引名

23.视图（虚拟表）

视图是关系数据库中提供给用户以多种角度观察数据库中数据的重要机制

视图是从一个或多个表中导出的表，所对应的数据不进行实际存储，可以像表一样，对其进行查、改、删

优点：

(1)为用户集中数据，简化用户的数据的查询和处理

(2)屏蔽数据库的复杂性

(3)简化用户权限的管理，秩序授予权限，而不必指定用户能使用的特定列

(4)便于数据共享

(5)可以重新组织数据以便输出到其他应用程序中

1. 视图的创建

   视图中的select语句不能包括：

   order by,除非有 top关键字

   into

   option

   引用临时变量或表变量

   create view v_student_2
   AS
     select student,sno,sname,cno,grade
     from student,score
     where student.depart='001' and student.sno=score.sno
   with check option
   

   create view v_student_1
   AS
    select *
    from student
    where gender='女'
   

   with check option的作用

   有：强制针对视图执行的所有数据的修改语句都必须符合select中所设置的条件，限制了其学生的信息必须是信息工程学院的

   没有：创建这个视图之后，仍然可以向视图中插入性别任意的数据

   create view v_student_avg
   AS
     select sno AS 学号，AVG(grade)AS平均成绩
     from score
     group by sno
   

2.视图的更新(增删改查)

数据可以更新的视图称为可更新视图，更新是有条件的

(1)任何修改（insert、update、delete）都只能引用一个基本表的列

(2)视图中被修改的列必须直接引用表列中的基础数据，不能通过任何其他方式对这些列进行派生

如，聚合函数，计算（集合查询和交叉连接）、不受group by、having、dinstinct,top所在位置不会与with check option一起使用

所以，能修改v_studnet_avg中的”平均成绩“=======不能

update v_student_1
 set specialty='software enginerring'
 where  sno='2015874123'

select *
from v_student_1
where gender='女',平均成绩>80

不允许删除修改里面的数据，索引视图删除了，会影响基础表对应的基础表数据也被删除了

3.视图的修改

将已经选择的数据进行修改

Alter view v_student_1
AS 
  select *
  from student
  where gender='男'，平均成绩>80

4.视图的删除

Drop view v_student_1