SQL Server的数据库是有组织的数据的集合，这种数据集合具有逻辑结构并得到数据库系统的管理的维护。
数据库由包含数据的基本表和对象（如视图，索引，存储过程和触发器等）组成,其主要用途时处理数据管理活动产生的信息.



使用T-SQL语句创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create database zhangboSTU
on primary	//数据库文件
(
	name = 'zhangbo_data',
	filename = 'C:\data\zhangbo_data.mdf',
	size = 5MB,
	maxsize = 20MB,
	filegrowth = 20%	//曾长方式
)
log on		//日志文件
(
	name = 'zhangbo_log',
	filename = 'C:\data\zhangbo_log.log',
	size = 2MB,
	maxsize = 10MB,
	filegrowth = 2MB
)

使用T-SQL语句修改数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//增加数据库空间
alter database 数据库名
modify file
(name = 逻辑文件名，,
size = 文件大小)
//增加数据库文件
alter database 数据库名
add file | add log file
(

)
//删除数据库文件
alter database 数据库名
remove file 文件名

使用T-SQL语句删除数据库

1
drop database 数据库名

使用T-SQL语句创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table course(
	cno char(4) primary key not null,	//主键
	cname char(10),
	classhour tinyint,
	credit tinyint
)
create table student(
	sno char(7) primary key not null,
	sname nvarchar(10) not null,
	ssex nchar(1) not null,
	sage tinyint ,
	en_time datetime,
	specialty nvarchar(50),
	grade nvarchar(50)
)

使用T-SQL语句修改表

1
2
3
4
5
6
7
8
use teaching
go
alter table student
alter column sname nvarchar(20) NULL
alter table course
add teacher nvarchar(20)
alter table student
drop column sage

使用T-SQL语句创建主键和外键约束

1
2
3
4
5
6
7
8
9
create table sc
(
	sno char(7) not null,
	cno char(4)not null,
	score int
	constraint pk_ss primary key(sno,cno),
	constraint stu_foreign foreign key(sno) references student(sno),
	constraint cou_foreign foreign key(cno) references course(cno)
)

在修改表时设置主键约束

1
2
alter table student
add constraint pk_st primary key(sno)

在修改表时设置UNIQUE约束

1
2
alter table student
add constraint uk_st unique(id)
在使用UNIQUE约束时应考虑的问题

UNIQUE约束所在列允许空值，但是主键约束所在的列不允许空值；
在一个表可以有多个UNIQUE约束；
UNIQUE约束强制在指定的列上创建一个唯一性索引

在修改表时定义默认值约束

1
2
alter table student
add constraint dk_stu default(getdate()) for en_time with values

在修改表时创建CHECK约束

1
2
alter table sc
add constraint cj_constraint CHECK(score>=0 and score<=100)

使用T-SQL语句插入数据

1
2
insert into score(sno,cno,score)
values(130200,'C01',90),(130220,'C02',50)

使用T-SQL语句修改数据

1
update score set cno='C03' where sno='130220'

使用T-SQL语句删除数据

1
delete student where sno='130200'

使用MERGE语句插入，修改，删除数据

1
2
3
4
5
merge ProductNew as d using Product as s
on s.ProductID = d.ProductID
when not matched then insert(ProductID,ProductName,price)
values(s.ProductID,s.ProductName,price);
select * from ProductNew

使用T-SQL语句投影查询

1
2
select sno as 学号 , sname 姓名 from student
select sno,cno,score150 = score *1.5 from sc

使用T-SQL语句选择查询

1
2
3
4
5
6
7
select * from sc where score>=60
select * from student where specialty='计算机' and ssex='男'
select * from student where specialty='计算机' or ssex='男' 
select * from sc where score not between  80 and 90
select * from student where specialty in('计算机','通信')
select * from student where sname like '张%'
select * from sc where score is null

使用T-SQL语句分组和汇总

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select ssex , COUNT(ssex) as 人数 from student
group by ssex
select sno ,COUNT(cno) as 学修课程数 from sc
group by sno having COUNT(cno)>=2
select AVG(score) as 平均成绩 from sc where sno=1302001
select cno,AVG(score) as 平均成绩 ,count(sno) as 选课人数 from sc
group by cno
with cube
select specialty,ssex,count(*) as 人数 from student
group by specialty,ssex
with rollup
select specialty,ssex,count(*) as 人数 from student
group by ssex,specialty
with cube
CUBE和ROLLUP的使用

cube指定在结果集内不仅包含由GROUP BY提供的行，还包含汇总行
汇总行针对每个可能的组和子组组合在结果集内返回
rollup指定在结果集内不仅包含由GROUP BY提供的行，还包含汇总行，按层次结构顺序，从组内的最低级别到最高级别汇总行
使用CUBE和ROLLUP时，不支持区分性聚合函数，例如AVG，COUNT等

内连接和全连接

1
2
3
4
5
6
7
select student.sname,sc.cno,sc.score from student inner join sc on student.sno=sc.sno
select a.sname,b.cno,b.score from student as a inner join sc as b on a.sno=b.sno
where a.specialty='计算机' 
order by a.sname
compute avg(b.score) by a.sname
select * from student a inner join student b
on a.sname=b.sname and a.sno<>b.sno

外连接

1
2
3
select student.*,sc.cno,sc.score from student left join sc on student.sno=sc.sno
select course.* ,sc.score,student.sname,student.sno from course full join sc on course.cno=sc.cno
full join student on student.sno=sc.sno

子查询

1
2
3
4
5
6
7
8
select a.sno,a.sname,a.specialty
from student a,student b
where a.specialty=b.specialty and b.sname = '沈艳'
select sname,score from student,sc where student.sno=sc.sno and cno = 'C001' and sc.score >(
	select score from sc where cno = 'C001' and sno =(
		select sno from student where sname = '郑丽'
	)
)
CAST函数

使用CAST函数进行数据类型转换时，在下列情况下能够被接受：

（1）两个表达式的数据类型完全相同。
（2）两个表达式可隐性转换。
（3）必须显式转换数据类型。
1
2
CAST('12.5' AS int)
cast(@i as varchar)
SQL Server—查询表结构

在SQL Server中不同于在MySQL或者Oracle中使用desc来查询数据表的表结构，而是使用如下两条语句查询：

1
2
sp_help student
sp_columns student

内嵌表值函数

1
2
3
4
5
create function stu_func(@specialty nvarchar(50)) returns table
as return 
(select a.sno,a.sname,b.cno,b.score from student a,sc b where specialty=@specialty and a.sno=b.sno)

select * from stu_func('计算机')

多语句表值函数

1
2
3
4
5
6
create function stu_score(@no char(7)) returns @score table(xs_no char(7),xs_name char(6),kc_name char(10),cj int,xf int)
as
begin
	insert into @score select s.sno,s.sname,c.cname,c.credit,sc.score from student s,course c,sc sc where s.sno=sc.sno and c.cno=sc.cno and s.sno=@no
return 
end

创建无参存储过程

1
2
3
4
5
6
7
8
9
10
11
12
create procedure stu_avg
as
select sno,AVG(score) as 'avgstore' from sc
group by sno
go
create procedure getstu @number char(7)
as
select stu.sno,sname,specialty,s.cno,s.score from sc s,student stu
where s.sno=@number and s.sno=stu.sno
compute avg(score)
go
exec getstu 1302001

创建带output参数的存储过程

1
2
3
4
5
6
7
create procedure avg_price @gn varchar(20) , @avgp int output
as
	select @avgp=AVG(price) from goods where gname=@gn 
declare @avgprice int
exec avg_price '电视',@avgprice output
print cast(@avgprice as char(10))
go

启动或禁用表上所有触发器

1
2
3
alter table student
disable trigger all
enable trigger all

事务

1
2
3
4
begin transaction	定义一个事务的开始
commit tran			提交一个事务
rollback tran		回滚事务
save transaction	在事务内设置保存点