/*
****** author:Vericlongmore ******
****** update date:-04-05 *****
****** spot:beijing **********
*/
----------------------------- 数据库的有关SQL语句 -------------------------
1、数据库
创建 create database data_name
on primary
(name= ,filename= ,size= ,maxsize= ,filegrowth=),
filegroup [辅助文件组名]
(name= ,filename= ,size= ,maxsize= ,filegrowth=)
log on
(name= ,filename= ,size= ,maxsize= ,filegrowth=)
修改 alter database 原数据库名
modify name=新名字
删除 drop database data_name
2、架构
创建 create schema jiagou
删除 drop schema jiagou
3、辅助文件
添加 alter database data_name
add file(name=file1,filename='d:\file1.ndf',size=10MB,filegrwth=10MB) to filegroup
group1
修改 alter database data_name
modify file(name= ,filename= ,size= ,maxsize= ,filegrowth=)
删除 alter database data_name
remove file file_name
4、日志文件(修改的时候，逻辑名称不能修改)
添加 alter database data_name
add log file (name= ,filename= ,size= ,maxsize= ,filegrowth=)
修改 alter database data_name
modify file(name= ,filename= ,size= ,maxsize= )
5、文件组
添加 alter database data_name
add filegroup group_name
修改 alter database data_name
modify filegroup 原文件组名 name=新文件组名
删除 alter database data_name
remove filegroup 文件组名
--------------------------------- 表的有关SQL语句 --------------------------------
1、表
创建：create table table_name
(
id int identity(1001,3) primary key not null,
st_name nvarchar(10) null,
sex nvarchar(4) default('男'),
gongzi money,
shijian datetime
)
修改表名：exec sp_rename 'table', 'table33' (注意：尽量不要改表名，容易引起其它对象的错误)
删除：drop table table_name
2、表的列（字段）的操作
添加列：alter table table_name
add 列名 列的数据类型 null / not null
删除列：alter table table_name
drop column 列名
修改列的名称： exec sp_rename '表名.字段名', '新的字段名', 'column' (提示：尽量不要改列名，容易引起错误)
修改列的数据类型： alter table table_name
alter column 列名 列的数据类型
3、对数据的操作
插入: insert into table_name(字段1,字段2,字段3) values( 值, 值, 值,)
删除：delete from where stu_name='王伟' and id=3
修改：update table_name set 字段名=值 where id=4
------------------------------------ 主键、外建 (补充) -------------------------
1、创建：
create table class
(
cl_id int primary key,
cl_name nvarchar(10) null
)
create table address
(
add_id int primary key,
add_name nvarchar(10) null
)
create table student
(
stu_id int primary key,
stu_name nvarchar(10) null,
cl_id int foreign key references class(cl_id) null ,
add_id int foreign key references address(add_id) null
)
意义：用于和加强表之间的联系，当在添加,修改和删除数据时，保持几张表中数据的一致性
------------------------------ SQL查询语句 --------------------------------
1、排序
select top(3) * from student order by cl_id desc
2、分组
select class_id ,sum(score) as chengji from student group by class_id
having sum(score)>=250
order by sum(score) desc
提示：聚合函数不能在where中使用，所以才用到了having
3、清空表
truncate table table_name
4、消除重复列
select distinct 列名 from table_name (提示：只能显示出此列，其它列显示不出来)
5、select * from table_name where name in ('张三','李四','王五')
select * from table_name where score in (100,90,87,89,96)
------------------------------- 表联接 ---------------------------------
1、内联接：select * from student as s inner join class
as c on s.cl_id=c.cl_id where .....
2、左外联接：返回第一个已命名的表中符合条件的所有行
select * from student as s left join class as c on s.cl_id=c.cl_id
where ....
3、右外链接：返回第二个已命名的表中符合条件的所有行
select * from student as s right join class as c on s.cl_id=c.cl_id
where .....
4、完全外联接：返回左表、右表中的所有值
select * from student as s full join class as c on s.cl_id=c.cl_id
5、交叉联接：将从被联接的表中返回所有可能的行组合(会生成一个笛卡尔积)
select * from student as s cross join class as c
where ....
6、两个以上的表的联接：
select * from student as s join class as c
on s.cl_id=c.cl_id join address as a on s.add_id=a.add_id
where s.name='张三'
7、union 结合多个表的数据
select stu_id as '学生编号' , stu_name as '名字' from student1
union
select id ,name from student2
union
select s_id , s_name from student3
--------------------------- 子查询 ----------------------
1、把子查询用作派生表
可用子查询产生一个派生表，用于代替where、having、from子句中的表，注意要用别名来引用这个派生表
select s.stu_name,s.cl_id from (select * from student where stu_id >2) as s
where s.stu_name='王二'
2、把子查询用作表达式
在sql语句中，所有使用表达式的地方，都可以用子查询代替，此时子查询必须取值为单个列值的表，于是这个子查询可以代替where子句中包含in关键字的表达式
select * from student where stu_id not in (select id from student where stu_id>2)
3、使用子查询关联数据
关联子查询可以作动态表达式，因为它随着外层查询的每一次变化而变化
例1：
select stu_id,stu_name,cl_id,(select count(stu_id) from student) as 记录条数 from student
例2：
select * from student as s join class as c on s.cl_id=c.cl_id
where not exists(select * from class where c.cl_id>3)
--------------------------- 变量、条件语句、循环语句 --------------------------
1、变量
(1)局部变量---可以select、set进行赋值
例一：declare @i int
select @i=count(*) from student
print @i
例二：declare @sname varchar(4)
set @sname='张三'
print @sname
(2)全局变量---只能用系统提供的，用户不能定义，所以只了解一下书上70页的就可以
2、条件语句
(1) if...else...语句
declare @sex_id int
declare @sex_name nvarchar(4)
select @sex_id=sex from student (where ...)
if @sex_id=1
begin
set @sex_name='男'
end
else
begin
set @sex_name='女'
end
print @sex_name
(2) case语句
select stu_name as 姓名, (case cl_id
when 1 then '一班'
when 2 then '二班'
when 3 then '三班'
when 4 then '四班'
else '暂无班级'
end ) as 班级 from student
3、循环语句
while 语句：
declare @i int
declare @name nvarchar(10)
set @i=1
while @i<13
begin
select @name=stu_name from student where stu_id=@i
print @name
set @name=''
set @i=@i+1
end
------------------------------ 事务 ------------------------------
1、事务的概念
事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。这特别使用于多用户同时操作的数据通信系统：订票、银行、保险公司以及证券交易系统等。需要使用事务的SQL语句通常是更新和删除操作等。
2、创建事务
开始事务：begin transaction
提交事务：commit transaction
回滚事务：rollback transaction
@@error全局变量显示错误号
3、例题
begin transaction /* 开始事务 */
declare @error_sum int /* 错误数量 */
declare @sum int/* 人数 */
declare @a nvarchar(10) /* 转钱者 */
declare @b nvarchar(10) /* 得钱者 */
declare @x int /* 转账金额 */
set @error_sum=0
set @a='于聪'
set @b='许'
set @x=2000
select @sum=count(*)from icbc where users=@a or users=@b
if @sum = 2
begin
set @error_sum=@error_sum+0
end
else
begin
set @error_sum=@error_sum+1
end
update icbc set moneys=moneys-@x where users=@a
set @error_sum=@error_sum + @@error
update icbc set moneys=moneys+@x where users=@b
set @error_sum=@error_sum + @@error
if @error_sum > 0
begin
print '操作失败，执行回滚'
rollback transaction /* 回滚事务 */
end
else
begin
print '操作成功'
commit transaction /* 提交事务 */
end
----------------------------- 视图 -------------------------------
视图就是查询语句的别名，而这个别名就称为视图
视图的分类：标准视图、索引视图、分区视图
1、创建视图
语法：create view 视图名称(列的别名)
as （select 查询语句）
create view v_student(sname,cname)
as (select s.stu_name,c.cl_name from
student as s join class as c on
s.cl_id=c.cl_id)
2、删除视图
drop view 视图名称
3、修改视图
语法跟创建视图是一样的：alter view 视图名(别名1，别名2)
as
(select …………)
4、获取视图的定义语句
exec sp_helptext 视图名称
5、查看视图的列的信息
exec sp_help 视图名称
6、查看视图的虚拟表
select * from 视图名称
7、更改视图内的数据
update from 视图名 set ..... where ....
---------------------------- 存储过程 ----------------------------------
1、创建无参数存储过程
语法： create procedure 过程名
as
sql语句体
例题：create procedure p_student
as
select * from student as s join class as c
on s.cl_id=c.cl_id where s.stu_id>2
2、创建带输入参数的存储过程
语法：create procedure 过程名
@参数1 数据类型(=默认值)，
@参数2 数据类型(=默认值)
as
select 语句体
例题：create procedure p_student
@name nvarchar(10)
as
select * from student as s join class as c
on s.cl_id=c.cl_id where s.stu_name=@name
3、创建带输入、输出参数的存储过程
语法：create procedure 过程名
@参数1 数据类型 output，
@参数2 数据类型(=默认值)
as
sql 语句体
return
例题：create procedure p_stu_cla
@cname nvarchar(10) output，
@sname nvarchar(10)
as
select @cname=c.cl_name from student as s join class as c
on s.cl_id=c.cl_id where s.stu_name=@sname
return
调用：declare @cname nvarchar(10)
exec p_stu_cla @cname output , '王二'
select @cname
4、存储过程的管理
查看存储过程的定义
exec sp_helptext 过程名
查看存储过程的信息
exec sp_help 过程名
删除存储过程
drop procedure 过程名
修改存储过程
alter procedure 过程名
@参数名 数据类型=默认值 output
as
sql 语句
return
------------------------------------- 函数 --------------------------------------
Sql server支持三种用户定义函数：标量函数、内嵌表值函数、多语句表值函数
1、标量函数
标量函数是根据输入参数值的不同来获得不同的函数值，标量函数可以有多个输入参数，但是只能有一个返回值；标量函数体包括一条或多条sql语句，由begin开始，以end 结束；用returns字句定义函数返回值的数据类型，并返回函数值
语法： create function 函数名(标量参数 标量数据类型)
returns 函数返回值的类型
as
begin
函数体
return 变量/标量表达式
end
例题： create function f_count( @sname nvarchar(10) )
returns nvarchar(10)
as
begin
declare @cname nvarchar(10)
select @cname=cl_name from student as s jion class as c
on s.cl_id=c.cl_id where s.stu_name=@sname
return @cname
end
调用函数： declare @name nvarchar(10)
select @name=架构名.f_count('王二')
print @name
2、内嵌表值函数
内嵌表值型函数以返回的都不是一个标量数据，而是一个表，返回的表值函数还可以提供参数化视图功能。
语法： create function 架构.函数名(标量参数 数据类型)
returns table
as
return (select语句)
调用函数：select * from 架构.函数名(参数)
-------------------------------- 约束 -------------------------------------
SQL server 中，用于实现数据完整性的机制有这几种：数据类型、规则和默认值、约束、触发器、XML架构
约束的种类：主键(primary key)约束、外键(foreign key)约束、唯一(unique)约束、核对(check)约束、默认(default)约束
1、主键约束 primary key
直接创建表时创建约束：
create table student
(
sid int identity not null,
sname nvarchar(10)，
constraint 主键名 primary key (sid)
)
在已创建表中添加约束：
alter table 表名
add constraint 主键名 primary key (列名)
例如：add constraint pk_id primary key (sid)
删除主键：
alter table 表名
drop constraint 主键名
2、外键约束 foreign key
直接创建表时创建：
create table student
(
id int identity not null,
sname nvarchar(10),
class_id int ,
constraint 外键名 foreign key (class_id) references 其它表(列名)
)
在已创建表中添加：
alter table 表名
add constraint 外键名 foreign key (列名) references 其它表(列名)
例如：add constraint fk_cid foreign key (class_id) references class(class_id)
删除：
alter table 表名
drop constraint 外键名
例如：drop constraint fk_cid
3、唯一约束 unique
直接创建表时创建：
create table student
(
id int identity not null,
sname nvarchar(10) ,
class_id int ,
constraint 唯一约束名 unique (sname)
)
在已创建表中添加：
alter table 表名
add constraint 唯一约束名 unique (列名)
例如：add constraint uni_name unique (sname)
删除：
alter table 表名
drop constraint 唯一约束名
例如：drop constraint uni_name
4、核对约束 check
直接创建表时创建：
create table student
(
id int identity not null,
sname nvarchar(10) ,
class_id int ,
constraint 核对约束名 check (class_id>0 and class_id<4)
)
在已创建表中添加：
alter table 表名
add constraint 核对约束名 check (列名的约束条件)
例如：add constraint che_id unique (class_id>0 and class_id<4)
删除：
alter table 表名
drop constraint 核对约束名
例如：drop constraint che_id
5、默认约束 default
直接创建表时创建：
create table student
(
id int identity not null,
sname nvarchar(10) ,
class_id int constraint 默认约束名 default(默认值)
)
在已创建表中添加：
alter table 表名
add constraint 默认约束名 default (默认值) for 列名
例如：add constraint df_id default (1002) for class_id
删除：
alter table 表名
drop constraint 默认约束名
例如：drop constraint df_id
---------------------------------------- 触发器 --------------------------------------------
在sql server里面也就是对某个表的一定操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。触发器常用于强制业务规则，它是一种高级约束，通过事件进行触发而被执行
常见的触发器有三种：分别应用于insert,update,delete事件
例如两个表：student学生表（id编号，stuid学号，stu_name学生名字）
library借书表（id编号，stu_id学号，book_name书名）
1、update型触发器
create trigger tri_student on student
after update
as
if update(stu_id)
begin
update library set stu_id=i.stuid from library l ,deleted d, inserted i
where l.stu_id=d.stuid
end
2、delete型触发器
create trigger trg_student on student
after delete
as
delete library from library l,deleted d
where l.stu_id=d.stuid
----------------------------------- 级联更新、删除 -------------------------------------
级联更新、删除是对主键进行的，外键却不能
1、创建级联更新、删除
create table class
(
cid int identity not null,
cname nvarchar(10),
constraint pk_cid primary key(cid)
)
create table student
(
sid int identity not null,
sname nvarchar(10),
cid int ,
constraint fk_cid foreign key (cid) references class (cid)
on delete cascad / on update cascade
)
注：只能对主表class表进行更新、删除时才能实现级联
---------------------------------- 索引 ---------------------------------------
索引是的指表中的数据和其相应存储位置的列表。它类似于书中目录，可以快速地查找表中的数据而不必扫描整个数据表。
1、创建聚集索引
create clustered index 索引名
on 表(列名)
2、创建非聚集索引
create nonclustered index 索引名
on 表(列名)
3、创建唯一、非聚集索引
create unique nonclustered index 索引名
on 表(列名)
4、删除索引
drop index 表名.索引名
注意：删除索引时要注意，如果索引是在create table语句中创建的，只能用alter table语句删除。
如果索引是用create index创建的，可用drop index
5、修改索引的名称
sp_rename ‘表名.旧索引名’，‘新索引名’
注意：尽量不要修改索引的名字，容易破坏脚本和存储过程
