SQL课后小笔记

数据定义：creat drop alter
数据查询： select
数据操纵： insert update delete
数据控制：grant revoke

数据类型

整数型：按照取值范围从大到小，包括bigint，int，smallint，tinyint，bit，就是整数

数值型：分两种，一种是精确数值型，numeric，decimal 和近似数值型float，real
以numeric为例子，格式为numeric(5,1)，即表示数据长度为5，小数位为小数点后一位，多的话为四舍五入。
float和real用来存储数据的近似值，当数值的位数太多时，可用它存取数值的近似值。

日期型：按照时间范围从大到小，包括datetime和smalldatetime，smalldatetime可以精确到秒，还有一个常用的日期型是date，这种数据类型只显示日期，不显示时间

字符型：包括char，varchar，nchar，nvarchar，text，ntext。

数据定义篇

创建数据库

用SQL命令创建数据库格式如下：

creat database 数据库名称
[on
[filegroup 文件组名称]
(
 name=数据文件逻辑名称,
 filename='路径+数据文件名',
 size=数据文件初始大小,
 maxsize=数据文件最大容量,
 filegrowth=数据文件自动增长容量,
)]
[log on
(
	name=日志文件逻辑名称,
	filename='路径+日志文件名',
	size=日志文件初始大小,
	maxsize=日志文件最大容量,
	filegrowth=日志文件自动增长容量,
)
]
[collate 数据库校验方式名称]
[for attach]

用 [ ]括起来的内容，表示创建数据库的过程中可以选用或者不选用，例如，在创建数据库的过程中，如果只是用第一条“creat datebase 数据库名称”语句，DBMS将会按照默认的属性创建数据库。

修改数据库

可以使用“alter database 数据库名称”命令修改数据库，先介绍主要参数：
add file：向数据库中添加数据文件
add log file：想数据库中添加日志文件
remove file：从数据库中删除逻辑文件，并删除物理文件，如果文件不为空，则无法删除
modify file：指定要修改的文件
add filegroup：向数据库中添加文件组
remove filegroup：想数据库中删除文件组，若文件组费控，无法将其删除，需要先从文件组中删除所有文件
modify filegroup：修改文件组名称，设置文件组的制度（read_only）或者读写（read_write）属性，指定文件组为默认文件组
alter database 命令可以在数据库中添加或删除文件或文件组，更改数据库属性或其文件盒文件组，更改数据库排序规则和设置数据库选项。
举几个例子了解上述如何使用：

修改Teach数据库中的Teach_data文件增容方式为一次增加20mb

alter database Teach
modify file
(
	name=Teach_data,
	filegrowth=20
)

给数据库Teach添加一个次要数据文件，逻辑名称Teach_datanew，存放在e盘根目录下，文件名为Teach_datanew.ndf，数据文件初始大小100MB，最大容量为200MB，文件自动增长容量为10MB

alter database Teach
add file
(
	name=Teach_datanew,
	filename='E:\Teach_datanew.ndf',
	size=100,
	maxsize=200,
	filegrowth=10
)

从Teach数据库中删除上例刚增加的次要数据文件

alter database Teach
remove file Teach_datanew

删除数据库

drop database 数据库名称

查看数据库信息

1.可以使用系统存储过程Sp_helpdb来显示数据库结构，其语法如下：

sp_helpdb 数据库名称

使用sp_helpdb系统存储过程可以显示指定数据库的信息，如果不指定数据库名称，则会显示在master.dbo.sysdatabases表中存储的所有数据库信息，命令执行成功会返回0，否则返回1。

2.可以使用存储过程sp_helpfile来显示当前数据库中的文件信息，其语法如下：

sp_helpfile 文件名称

如果不指定文件名称，则会显示当前数据库中所有的文件信息。

3.可以使用系统存储过程sp_helpfilegroup来显示当前数据库中的文件组信息，其语法如下：

sp_helpfilegroup 文件组名称

如果不指定文件组名称，则会显示当前数据库中所有的文件组信息

数据操作篇

创建数据表

其实创建数据表也是属于数据定义的内容，即是对型的操作，其sql语法格式如下：

create table <表名>
(
	<列名> <数据类型> [Default] [列约束],
	.....
	<列名> <数据类型> [Default] [列约束],
)

default：若某字段设置有默认值，则当该字段未被输入数据时，以该默认值自动填入该字段
数据类型：在sql中用如下所示的格式来表示数据类型以及它所采用的长度，精度和小数位数，其中的N代表长度，P代表精度，S表示小数位数。
binary(N) ------------binary(10) //二进制字符串型
char(N)----------------char(20) //定长字符型
numeric(P,[S])---------------numeric(8,3) // 精确数值型，数据长度为8，小数点后有四位，例如12345.678

例子：

create table s
(
	sno int,
	sname varchar(10),
	sex char(2) default '男'
)

定义数据表的约束：

在sql server中，对于数据表的约束分为列约束和表约束，其中列约束时对某一个特定列的约束，包括在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名，而定义表约束时必须指出要约束的列的名称。
完整性约束的基本语法格式为：

[constraint <约束名>] <约束类型>

约束名：约束不指定名称时，系统会给定一个名称
约束类型：在定义完整性约束时必须指定完整性约束的类型，在sql server中可以定义5中类型的完整性约束，下面分别加以介绍：

NULL / NOT NULL 约束

其语法格式如下：

[constraint <约束名>] [null | not null]

[例子] 建立一个s表，对 sno 字段进行not null，不准为空约束

create table
(
	//sno varchar(10) constraint s_cons not null
	两者意思相同，constraint s_cons可省略，只不过下面这个由系统指定名称
	sno varchar(10) not null
)

其中s_cons 为指定的约束名称，有了 not null 约束，在 s表中录入数据，当sno为空时，系统将给出错误信息，无not null约束时，系统缺省为null

unique 约束（唯一约束）

unique约束用于指明基本表在某一个列或多个列的组合上的取值必须唯一，不能重复
其语法格式如下：

[constraint <约束名>] unique

[例子]建立一个s表，定义sn为唯一键

create table s
(
	//sn varchar(10) constraint sn_uniq unique
	两者意思相同,constraint sn_uniq可省略，只不过下面这个由系统指定名称
	sn varchar(10) unique
)

[例子]建立一个s表，定义sn+sage为唯一键，此约束为表约束

create table s
(
	sn varchar(10),
	sex varchar(2) default '男'，
	sage int
	unique(sn,sage)
	两者意思相同，constraint s_uniq 可省略，上句为系统自动分配名字
	//constraint s_uniq unique(sn,sage)
)

primary key 约束 (主键约束)

primary key 约束用于定义基本表的主键，起唯一标识作用，其值不能为null，也不能重复，一次来保证实体的完整性,该约束既可以表约束也可以列约束
其列定义语法如下：

[constraint <约束名> ] primary key

其表定义语法如下：

[constraint <约束名> ]primary key (<列名>,<列名>) //主键只能有一个，这里多个列名的意思为联合主键

[例子] 建立一个s表，定义Sno为s的主键

create table s
(
	Sno varchar(10) primary key
)

[例子]建立一个sc表，定义sno+cno为sc的主键

create table sc
(
	sno varchar(10),
	cno int
	primary key (sno,cno)
	//主键只能有一个，这里多个列名的意思为联合主键
)

foreign key 约束（外键约束）

foreign key 约束指定某一列或几列作为外部键，其中包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表成为主表，系统保证从表在外部键上的取值时主表中某一个主键值或唯一值，或者取空值。
其语法格式如下：

[constraint <约束名>] foreign key references <主表名> （<列名>[,<列名>]）

[例子]建立一个sc表，定义 sno，cno为sc的外部键，主表分别时s表和c表

create table sc
(
	sno varchar(10) not null foreign key references s(sno),
	cno varchar(10) not null foreign key references c(cno)
)

check 约束

check约束用来检查字段值所允许的范围，如一个字段只能输入整数，而且限定在一个范围内，一次来保证域的完整性。
在建立check约束时，需要考虑几点因素
1.一个表可以有多个check
2.每个字段只能定义一个check约束
3.在多个字段上定义的check约束必须为表约束
4.当执行insert，update语句时，check约束将验证数据
其语法如下：

[constraint <约束名>] check （<条件>）

[例子]建立一个sc表，定义score的取值范围为0-100之间

create table sc
(
	sno varchar(10) not null,
	score numeric(4,1) check (score>=0 and score<=100)
)

修改数据表：

sql使用 alter table 命令来完成这一功能，有如下三种修改方式

add(添加)

add方式用于新增新列和完整性约束，定义方式与alter database 语句方式基本相同。
其语法格式如下：

alter table <表名> add( <列定义> | <完整性约束>)

[例子]在s表中增加一个班号列，和地址列

alter table s
add
class_no varchar(10),
address varchar(20)

[例子]在sc表中添加完整性约束定义，使score在0-100之间

alter table sc add check(score>=0 and score <=100)

alter(修改)

alter方式可用于修改某些列，其语法格式如下：

alter table <表名>
alter column <列名> <数据类型> [null || not null]

[例子]把s表中的sn列加宽到12字符

alter table 
alter column sn varchar(12)

使用此方法有限制
1.不能改变列名
这里其实不太对，可以通过以下语句更改列名

alter table <表名> rename column <旧> to <新>

2.不能将含有空值的列的定义修改为not null约束
3.若列已有数据，则不能减少该列宽度以及改变其数据类型
4.只能修改null /not all约束，其他类型约束在修改之前按必须先将约束删除

drop（删除）

drop方式只用于删除完整性约束定义，其语法格式为：

alter table <表名> drop constraint <约束名>

[例子]删除s表的主键

alter table s drop constraint s_prim

删除基本表

drop table <表名>

查看数据表

sp_help <表名>

单关系(表)的数据查询

无条件查询

语法格式如下：

select [<投影1> <投影2> <...> ] from <表名>
//只会查询到投影列的内容
select * from <表名>
//将全部的内容查询到

上例查询为不使用where子句的无条件查询，也称作投影查询.

消除重复行

有时候查询某一个东西的时候有非常多重复的内容，则可以使用关键字distinct消去重复行。

select distinct * from <表名>

别名

即时查询时，会看到原本的列名产生了变化，变成了别名

select <列名> as <别名> from <表名>

例子：sc

select sno as name from sc

显示效果如下

单关系表条件查询

常用的比较运算符

运算符	含义
=,>,<,<=,>=,!=,<>	比较大小
and or not	与或非，优先级由高到低时not，and，or
between and	确定范围
in	确定集合
like	模糊查找，字符匹配
is null	空值

比较大小

[例子] 查询大于85分的同学的学号和成绩

select sno,score from sc where score>85

多重条件查询

当 where 子句需要指定一个以上的查询条件时，则需要使用逻辑运算符and，or，not将其连接成为复合的逻辑表达式

[例子]查询sc表中选修了c1或者c2，并且分数大于85分的学生的学号（sno），课程号（cno），成绩（score）

select sno,cno,score from sc where (cno ='c1' or cno='c2') and(score>=85)

确定范围

[例子] 查询teacher表中工资(sal)在1000到1500元之间的教师的教师号（Tno）和姓名（name）

select Tno,name from teacher where sal between 1000 and 1500

确定集合

[例子]查询选修c1或c2的学生的学生学号和成绩

select sno,score from sc where cno in('c1','c2')

模糊查找

以上各例子属于完全匹配查询，当不知道完整精确的值时，用户可以通过like关键字来进行模糊查找，like定义的一般格式为：

<属性名> like <字符串常量>

其中属性名必须为字符型，字符串常量中的字符可以包含通配符，利用这些通配符，可以进行模糊查询

通配符	功能	含义
%	代表0个或多个字符	‘ab%’，‘ab’后可接任意字符
_（下划线）	代表一个字符	‘a_b’，‘a’与‘b’之间可有一个字符
[ ]	表示在某一范围的字符	[0-9]，0到9之间的字符
[^]	表示不在某一范围的字符	[^0-9]，不在0到9之间的字符

[例子] 查询教师中第二个汉字是“力”字的姓名

select name from teacher where name like '_力%'

常用库函数及统计

函数名称	功能
avg	按列计算平均值
sum	按列计算值的总和
max	求一列中的最大值
min	求一列中的最小值
count	按列值统计个数

[例子]求学号为s1的学生的学生的总分和平均分

select SUM(score) as sumscore,AVG(score) AS avgscore
//不写别名的话会显示无列明，因为这是计算得出的列
from sc
where (SNo='S1')

[例子]求选修了C1号课程的最高分，最低分，及之间相差的分数

select MAX(score) as max, MIN(score) as min,MAX(score) - min(score) as diff
from sc
where (cNo='C1')

分组查询

group by 子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值，
[例子] tc表查询每个教师的教师号（tno）以及任课（cno）的门数

select tno,COUNT(cno) as count_cno
from tc
group by tno

若在分组后还要按照一定的条件进行筛选，则需要使用having 子句
[例子]sc表查询选修两门以上含两门课程的学生的学号和选修门数

select sno,COUNT(cno) as count_cno
from sc
group by SNO
having COUNT(cno)>=2

group by子句按某值进行分组，所有具有相同值的元组为一组，然后再使用库函数对组中的元素进行计算
当一个sql查询中同时使用where 语句，group语句，having语句时，其顺序为where，接着group by，最后having，where和having 子句根本区别在于作用的对象不同，where作用于基本表，从中选择满足条件的元组，having 作用于组，选择满足条件的组

查询结果的排序（降序/升序）

当需要对查询结果排序时，应该使用order by 子句，order by子句必须出现其他子句之后，排序方法可以指定，desc为降序，asc为升序，缺省时为升序

[例子]sc表查询选修c1课程（cno）的学生学号（sno）和成绩（cno），并按成绩降序排列

select SNO,SCORE
from SC
where CNO='C1'
order by score desc

[例子]查询选修了c2，c3，c4或c5课程的学号，课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排序

select sno,cno,score
from SC
where CNO in('C2','C3','C4','C5')
order by SNO asc,SCORE desc

显示效果如下：

多关系表连接查询

表的连接方式

表的连接方式分两种
1.表之间满足一定条件的行进行连接时，from子句指明进行连接的表名，where子句指明连接的列名以及连接的条件，即内连接查询
2.利用关键字join进行连接，即外连接查询
关键字join具体的连接方法分为以下几种
inner join显示符合条件的记录，此为默认值
left outer join 成为左连接，直接理解左边为主表即可，想以左边的为基准，此时右边有不符合的条件数据行时以null来显示
right outer join 成为左连接，直接理解右边边为主表即可，想以右边的为基准，此时左边有不符合的条件数据行时以null来显示
full outer join 显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，缺乏数据以null来显示

内连接查询

[例子]查询刘伟老师所讲授的课程，要求列出i教师号，教师名，课程号（p102）

select T.tno,tn,cno
from T,TC
where T.TNO=TC.TNO AND TN='刘伟'

这里tn=‘刘伟’为查询条件，而T.TNO=TC.TNO为连接条件，tno为连接字段，

[例子]查询所有选课学生的学号，姓名，选课名称以及成绩（p103）

select s.sno,SN,cn,score
from SC,C,s
where s.SNO=SC.SNO and SC.CNO=C.CNO

[例子]查询每门课程的课程号，课程名和选课人数

select SC.CNO,CN,COUNT(SNO) as 选课人数
from SC,C
where SC.CNO=C.CNO 
group by sC.CNO,CN

外连接查询（关键字 join）

在内连接中，不满足连接条件的元组不能作为查询结果输出，比如学生a表有若干数据，主键为学号，s1到s6，选课b表有若干数据，其中也有学号，和相对应的选课号，但是学号只有在s1和s5，如果将两表使用a.学号=b.学号的条件进行内连接，会导致s6学生消失，这种情况则推荐外连接查询。

在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列，不符合连接条件的列，将被填上null值。

外部连接分为左外部连接和右外部连接两种，以主表方向区分，主表在左边，则称为左外部连接，使用关键字left outer join ，主表在右边，则称为右外部连接，使用关键字right outer join 。

内部连接语法格式如下：

左：

select 投影内容
from 主表
left outer join 从表
on 连接条件

右：

select 投影内容
from 从表
right outer join 主表
on 连接条件

[例子]查询所有学生的学号，姓名，选课，成绩（没有选课的学生选课信息为null）(p103)

select s.SNO,sn,cn,score
from s
left outer  join sc
on S.sno=sc.sno
left outer join c
on C.CNO=sc.CNO

自连接查询

一个表与其自身连接，称之为表的自身连接,通常时自己表的同类东西对比同类东西，需要划分两个，在进行对比
[例子]查询所有比“刘伟”工资高的教师姓名，工资，和刘伟的工资（p.104）

select x.tn,x.sal as '工资',y.SAL as '刘伟的工资'
from t as x,t as y
where x.SAL>y.SAL and y.TN='刘伟'

显示效果如下：

普通子查询

普通子查询的执行顺序是：首先执行子查询，然后把子查询的结果作为父查询的查询条件值。

只返回一个值的普通子查询

因为只返回一个值可以使用比较运算符。
[例子] 查询与“刘伟”老师职称相同的教师号，姓名

select tno,tn
from t
where prof=(
	select prof from t where tn='刘伟'
)

首先执行子查询，返回了刘伟老师的职称，就一个值，接着执行父查询

只返回一组值的普通子查询

这种情况就没办法使用比较运算符了，因为子查询返回的是一组数据，是一个集合，得使用关键字 any或 all，具体例子如下：
（any 其实就是关键字 in，用法是其中任意一个符合）
（all的含义如字面意思，全部）
[any例子] 查询讲授课程号为c5的教师姓名

select tn
from t
where
( tno = any(select tno from tc where cno='C5')
) 

这里子查询的结果是所有选修了c5课程的老师号，是一个集合，并不是单个数值，这里any的作用是tno只要和集合中任意一个符合即可。

[all例子]查询其他系中比计算机系所有教师工资都高的教师姓名和工资

select tn,sal
from t
where sal> all(select SAL from t where DEPT='计算机')

这里all的作用代表全部，即sal要比当前集合里的所有元素都要大

相关子查询

有时子查询的查询条件需要引用父查询表中的属性值，我们把这类查询成为相关子查询
相关子查询的顺序为：首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询，查询的结果可能会成为一个集合，然后父查询根据子查询返回的结果判断此行是否满足查询条件，对即放入结果集合中，一直重复执行这一过程，直到处理完父查询表中的每一行数据。
由此可以看出在相关子查询中，子查询的执行次数是由父查询表的行数决定的

[例子] 查询不讲授课程号为C5的教师名称

select tn
from t
where ('C5' not in (select cno from TC where TNO=t.TNO))

首先读取父查询的第一行数据，接着将第一行数据和子查询的每行数据匹配找到对应的数据形成集合，接着判断条件not in，对则保存到结果集，接着下一行，然后重复此操作。

利用exists关键字进行相关子查询

此外，使用exists也可以进行相关子查询，exists是表示存在的量词，带有exists的子查询不返回任何实际数据，它只能得到 “真” 或 “假” 两种逻辑值，即当子查询的查询结果集合为非空时，外层的where返回 true，反之返回 false
[例子] 用含有exists的语句完成查询授课程号为C5的教师姓名

select tn
from t
where exists(select * from TC where TNO=T.TNO AND CNO='C5')

反之，要查询没有教c5课程的教师姓名也非常简单，在exists前加not 即可

select tn
from t
where not exists(select * from TC where TNO=T.TNO AND CNO='C5')

[重点例子]查询选修了全部课程的学生

select sn
from s
where ( not exists (select *
					 from C
					  where not exists(select * 
					  					from SC 
					  					where SNO=S.SNO 
					  					and CNO=C.CNO
)))

其他类型查询

集合运算查询

说句实在话就是并集，将两个或多个查询通过关键字 union 进行拼接起来

select name from student where sno='s1'
union
select name from teacher where tno='t1'

最后将两个查找结果拼接在一起

存储查询结果到表中

使用select ··· into ····语句可以将查询结果存储到一个新建的数据库表或者临时表

select SNO as '学号',sum(score) as '总分'
into stu_table
from sc
group by sno

即可将每个学号和其总分存储到名为stu_table的表中

对表的增删改

插入数据

insert into 表名(列名1，列名2，列名3,···) values(值1,值2,值3,···) 
//这个是插入你想插入的数据，并给未赋值的列取null值
insert into 表名 values(值1,值2,值3,···) 
//给所有列赋值
//也可以表给表传值
insert into softstu(sno,sname)
select sno,sname
from stu 
where mno='02'

修改数据

update 表名 set 列名=表达式 where 条件
//例如想修改李伟的年龄为17岁
update student set age=17 where name='李伟'
//例如想将全部学生年龄加一岁
update student set age=age+1

where后跟条件，即可找到想找到的对象，可以根据子查询的模式进行修改

删除数据

delete from 表名 <where 条件>
//当没写where 条件时，则默认删除表中的所有数据
//例如删除刘伟老师的记录
delete from teacher where name='刘伟'

视图

创建视图

create view <视图名> as 查询结果集

例如创建一个计算机系教师情况的视图compter_teacher

create view compter_teacher as select * from teacher where dept='计算机系'

修改视图

alter view 视图名（修改列） as  查询结果集

选定视图选定需要修改的列进行修改

alter view compter_teacher(name) as select name  from teacher 

删除视图

drop view 视图名
//例如删除compter_teacher视图
drop view compter_teacher

查询视图

视图定义后，其对视图的操作和对基本表的操作一样
比如有一个所有老师数据的基本表 teacher 和一个只有计算机老师的视图compter_teacher
他们查询职称为计算机教授的代码分别如下：

select name from teacher where dept='计算机' and prof='教授'
select name from compter_teacher where prof='教授'

更新视图

由于视图是一张虚表，所以对视图的更新，最终转换成对基本表的更新，其更新操作包括添加，修改，删除数据，其语法格式与对基本表的更新操作一样

//添加数据
insert into compter_teacher(tno,name,prof) values ('T8'，'李丹','副教授')
//修改数据
update compter_teacher set Prof='副教授' where name ='刘伟'
//删除数据
delete from compter_teacher where name='刘伟'

以上对视图的操作都会变成对基本表的操作

索引

创建索引

//创建唯一索引
create unique index <索引名> on <表名(列名1，列名2)>
//例如表sc在sno和cno上建立唯一索引
create unique index scindex on sc(sno,cno)
//聚集索引
create clustered index <索引名> on <表名(列名)>
//例如为教师表T在tn上建立聚集索引
create clustered index tindex on t(tn)

删除索引

//将基于某表或视图的某索引进行删除
drop index <索引名> on <表名或视图名>

查看索引

//查看sc表的索引
exec sp_helpindex sc

规则

规则与check约束很相似，相比之下，在alter table 或create table 命令中使用的check约束是更标准的限制列值的方法，但check约束不能直接作用于用户自定义数据类型，规则和约束是可以同时使用的，标的列可以有一个规则及多个check约束

规则语法

语法如下：

create rule 规则名 as  规则   //创建规则
exec sp_bindrule '规则名'  '表名.列名' //使用规则
exec sp_unbindrule '表名.列名'  //解除该列的规则限制
drop rule '规则名'  //删除规则，前提是规则必须先将与其绑定的对象解除绑定

[例子]创建学生年龄规则，年龄必须大于等于18并且小于等于50

create rule age_rule as @age>=18 and @age<=50 //创建规则
exec sp_bindrule 'age_rule'  'S.age' //使用规则到列名age上
exec sp_unbindrule 'S.ahe' //取消对列名的规则限制
drop rule age_rule  //删除规则，前提是规则必须先将与其绑定的对象解除绑定

默认

所谓默认值，就是用户在表中添加数据的时候，如果没有给出一个准确的值，这是sql会自动使用的值

默认语法

语法如下：

create default 默认名 as 默认值 //创建默认
exec sp_bindefault,默认名,'表名.列名' //对该列添加默认
exec sp_unbindefault '表名.列名' //对该列解除默认的限制
drop default 默认名 //删除默认,前提是默认必须先将与其绑定的对象解除绑定

[例子]

create default birthday_defa as '1990-1-1' //创建默认
exec sp_bindefault,birthday_defa,'s.birthday' //对该列添加默认
exec sp_unbindefault 's.birthday' //对该列解除默认的限制
drop default birthday_defa //删除默认，前提是默认必须先将与其绑定的对象解除绑定