【MSSQLServer】 T-SQL 整理

/*
第一学期SQL Server应用开发
第三章 SQL Server数据管理
 3.3 使用T-SQL插入数据
  3.3.1 使用Insert插入数据行
  3.3.2 一次插入多行数据
 3.4 使用T-SQL更新数据
 3.5 使用T-SQL删除数据
  3.5.1 使用delete删除数据
  3.5.2 使用truncate table 删除数据
第4章 数据查询(1)
 4.2 T-SQL查询基础
  4.2.2 使用select语句进行查询
 4.3 查询排序
 4.4 在查询中使用函数
  4.4.1 字符串函数
  4.4.2 日期函数
  4.4.3 数学函数
  4.4.4 系统函数
第五章 数据查询(2)
 5.1 模糊查询
  5.1.1 使用like进行模糊查询
  5.1.2 使用between在某个范围内进行查询
 5.2 SQL Server中的聚合函数
  5.2.1 SUM(求和)
  5.2.2 AVG(平均值)
  5.2.3 MAX/MIN(最大值/最小值)
  5.2.4 count(计数)
 5.3 分组查询
  5.3.1 使用Group by进行分组查询
  5.3.2 使用having子句进行分组
 5.4 多表连接查询d
  5.4.1 多表连接查询的分类
  5.4.2 内联接查询
  5.4.3 外联接查询
*/

/*
第三章 SQL Server数据管理

*/
--3.3 使用T-SQL插入数据
---3.3.1 使用insert插入数据行
insert [into] <表名>[列名] values <值列表>
insert into jobs(job_id,job_desc,min_lvl,max_lvl) values(1,'aa',12,12)

---3.3.2 一次插入多行数据
----1.通过Insert select 语句将现有表中的数据添加到物理表a中
insert into a(a.job_id) select job_id from jobs

----2.通过select into 语句将现有表中的数据添加到虚拟表aaa中
select jobs.job_id,jobs.job_desc,jobs.min_lvl,jobs.max_lvl into aaa from jobs 

select indentity(int,1,1) as 列名 into 新表 from 原始表

----3.通过union关键字并数据进行插入与insert into.....select的效果一样
insert a(job_id)
select 111 union
select 211 union
select 311

--3.4 使用T-SQL更新数据
update <表名> set <列名 = 更新值> [where <更新条件>]
update jobs set jobs_desc = 'aa'
update jobs set job_desc = job_desc+'aa' where job_id = 1

--3.5 使用T-SQL删除数据
---3.5.1 使用delete删除数据
--delete from <表名> [where <删除条件>]
delete from jobs where job_id = 1

---3.5.2 使用truncate table 删除数据
truncate table a


/*
第4章 数据查询(1)
*/
--4.2 T-SQL查询基础
---4.2.2 使用select语句进行查询

select <列名> from <表名> [where <查询条件表达式>] [order by <排序的列名> [asc or desc]]
----1. 查询所有的数据行和列
select * from jobs
----2. 查询部分行列 ----条件查询
select job_id,job_desc,min_lvl,max_lvl from jobs where job_id = 1
----3. 在查询中使用列名
select job_id as 编号,job_desc as 描述 from jobs where job_id <> 1
----4. 查询空行
select job_id from jobs where job_desc is null
----5. 在查询中使用常量列
select 编号=job_id,描述=job_desc,'无列名' as 其他 from jobs
----6. 查询返回限制的行数
select top 5 job_id,job_desc from jobs where job_id <> 1
-----6.1使用percent关键字限制%
select top 50 percent job_id from jobs where job_id <> 2

--4.3 查询排序
select job_id as 编号,job_desc as 描述,min_lvl as 最小值,max_lvl as 最大值 from jobs where min_lvl > 100 order by 

max_lvl
---合并查询 job_desc1 +'1'+ job_desc2 表内容连接查询(字符串 整型和字符串 不能匹配)
---查询非重复项
select max_lvl as 描述 from jobs union
select max_lvl as 描述 from aaa

--4.4 在查询中使用函数
---4.4.1 字符串函数
/*
charindex	用来寻找一个制定的字符串在另一个字符串中的起始位置
		select charindex('accp','my accp course',1)
		返回：4

len     	返回传递给它的字符串长度
		select len('sql server 课程')
		返回：13

lower		把传递给它的字符串转换成小写
		select lower('SQL Server 课程')
		返回：sql server 课程

upper		把传递给它的字符串转换成大写
		select upper('sql server 课程')
		返回：SQL SERVER 课程

ltrim		消除字符左边的空格
		select ltrim(' sql server 课程 ')
		返回：'sql server 课程 '(后面的空格保留)

rtrim		消除字符右边的空格
		select rtrim(' sql server 课程 ')
		返回：' sql server 课程'(前面的空格保留)

right		从字符串右边返回指定数目的字符
		select right('买买提.吐尔松',3)
		返回：吐尔松

replace		替换一个字符串中的字符
		select replace('莫勒可且.杨兰','兰','蓝')
		返回：莫勒可且.杨蓝

stuff		在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串
		select stuff('ABCDEFG',3,3,'我的音乐我的世界')
		返回：A我的音乐我的世界EFG
*/
		select charindex('accp','my accp course',1)
		select len('sql server课程')
		select lower('SQL Server 课程')
		select upper('sql server 课程')
		select ltrim(' sql server 课程 ')
		select rtrim(' sql server 课程 ')
		select right('买买提.吐尔松',3)
		select replace('莫勒可且.杨兰','兰','蓝')
		select stuff('ABCDEFG',2,3,'我的音乐我的世界')

---4.4.2 日期函数
/*
getdate		取得当前的系统日期
		select getdate()
		返回：今天的日期

dateadd		将制定的数值添加到指定的日期部分后的日期
		select dateadd(mm,4,'01/01/09')
		返回：以当前的日期格式返回01/05/09

datediff	两个日期之间的制定日期部分的区别
		select datediff(mm,'01/01/09','01/03/10')
		返回：2

datename	日期中指定日期部分的字符串形式
		select datename(dw,'01/02/2000')
		返回：Saturday(星期6)

datepart	日期中指定日期部分的整数形式
		select datepart(day,'01/15/2000')
		返回：15
*/
		select getdate()
		select dateadd(mm,4,'01/01/09')
		select datediff(mm,'01/01/09','01/03/10')
		select datediff(yyyy,'07/01/09','01/02/10')
		select datename(dw,getdate())
		select datepart(day,'01/16/2000')

---4.4.3 数学函数
/*
abs		取数值表达式的绝对值
		select abs(-43)	
		返回：43

ceiling		取大于或等于指定数值、表达式的最小整数
		select ceiling(43.5)
		返回：44

floor		取小于或等于制定表达式的最大整数
		select floor(43.5)
		返回：43

power		取数值表达式的幂值(平方)
		select power(5,2)
		返回：25

round		将数值表达式的四舍五入为指定精度
		select round(43.543,1)
		返回：43.500

sign		对于正整数返回+1，对于负数返回-1，对于0则返回0
		select sign(-43)
		返回：-1

sqrt		取浮点表达式的平方根
		select sqrt(9)
		返回：3.0
*/
		select abs(-43)	
		select ceiling(43.5)
		select floor(43.5)
		select power(5,3)
		select round(43.543,1)
		select sign(-43)
		select sqrt(9)

---4.4.4 系统函数
/*
convert		用来转变数据类型
		select convert(varchar(5),12345)
		返回：字符串12345

current_user	返回当前用户的名字
		select current_user
		返回：你登录的用户名

datalength	返回用于指定表达式的字节数
		select datalength('中国A联盟')
		返回：5

host_name	返回当前用户所登录的计算机名字
		select host_name()
		返回：你登录的计算机的名字

system_user	返回当前所登录的用户名称
		select system_user
		返回：你当前所登录的用户名

user_name	从给定的用户ID返回用户名
		select user_name(1)
		返回：从任意数据库中返回"dbo"
*/
		select convert(varchar(5),12345)
		select current_user
		select datalength('中国A联盟')
		select host_name()
		select system_user
		select user_name(1)

/*
第五章 数据查询(2)
*/
--5.1 模糊查询
---5.1.1 使用like进行模糊查询
select * from jobs where job_desc like 'a%'
---查询id不是1的
select * from jobs where job_id like '[^1]%'

---5.1.2 使用between在某个范围内进行查询
select * from jobs where job_id between 1 and 5

---5.1.3 使用In在列举值内进行查询
select job_id as 编号,job_desc as 描述 from jobs where job_id in (1,2,3,4) order by job_id

--5.2 SQL Server中的聚合函数
---5.2.1 SUM(求和)
select sum(job_id) from jobs where job_id in (1,2,3,4)
---5.2.2 AVG(平均值)
select avg(min_lvl) from jobs where job_id in (1,2,3,4,5,6)
---5.2.3 MAX/MIN(最大值/最小值)
select max(max_lvl) as 最大值, min(min_lvl) as 最小值 from jobs where min_lvl>10
---5.2.4 count(计数)
select count(*) as 总数 from jobs where job_id <10

--5.3 分组查询
---5.3.1 使用Group by进行分组查询
select avg(min_lvl) as 平均数 from jobs group by min_lvl
---5.3.2 使用having子句进行分组
/*
where: 子句从数据源中去掉不符合其搜索条件的数据；
group by：子句搜索数据行到各个组中，统计函数为各个组计算统计值；
having: 子句去掉不符合其组搜索条件的各组的搜索行
*/
select min_lvl from jobs where min_lvl > 20 group by min_lvl having count(min_lvl)>2

--5.4 多表连接查询
---5.4.1 多表连接查询的分类
----1.内联接 inner join
----2.外联接 
-----1).左外联接：left join 或 left outer join
-----2).右外联接：right join 或 right outer join
-----3).完整外联接： full join 或 full outer join
----3.交叉联接

---5.4.2 内联接查询
----1.在where子句中指定联接条件
select Students.SName,Score.CourseID,Score.Score from Students,Score where Students.SCode = Score.StudentID
----2.在From子句中使用join..on
select S.SName,C.CourseID,C.Score from Students as S inner join Score as C on (S.Scode = C.StudentID)

---5.4.3 外联接查询
----1. 左外联接查询(Students主表，Score从表)Students主表所有的数据都会被列出 如果从表中没有与主表对应的数据则为NULL
select S.SName,C.CourseID,C.Score from Students as S left outer join Score as C on S.Scode = C.StudentID

----2. 右外联接查询(Titles从表，Publishers从表)Publishers主表所有的数据都会被列出 如果从表中没有与主表对应的数据则为

NULL
select Titles.Title_id,Titles.Title,Publishers.Pub_name from Titles right outer join Publishers on Titles.Pub_id = 

Publishers.Pub_id

/*
第二学期 SQL Server数据库设计和实现
第二章 数据库的实现
 2.1 T-SQL语句回顾
  添加数据
  修改数据
  查询数据
  删除数据
 2.2 使用SQL语句创建和删除数据库
  2.2.1 创建数据库
  2.2.2 删除数据库
 2.3 使用SQL语句创建和删除表
  2.3.1 创建表
  2.3.2 删除表
 2.4 使用SQL语句创建和删除约束
  2.4.1 添加约束
  2.4.2 删除约束
 2.5 使用SQL语句创建登录
  2.5.1 创建登录账户
  2.5.2 创建数据库用户
  2.5.3 向数据库用户授权
第三章 T-SQL编程
 3.1 使用变量
  3.1.1 局部变量
  3.1.2 全局变量
 3.2 输出语句
 3.3 逻辑控制语句
  3.3.1 IF-ELSE 条件语句
  3.3.2 WHILE 循环语句
  3.3.3 CASE多分支语句
 3.4 批处理语句
第四章 高级查询
 4.1 简单子查询
 4.2 IN和NOT IN子查询
 4.3 EXISTS 和 NOT EXISTS 子查询
第五掌 事务、索引和视图
 5.1 事务
 5.2 索引
  5.2.1 什么是索引
  5.2.2 如何创建索引
 5.3 视图
  5.3.2 如何创建视图
第六章 存储过程
 6.2 常用的系统存储过程
 6.3 用户自定义存储过程
  6.3.1 创建不带参数的存储过程
  6.3.2 创建带输出参数的存储过程
  6.3.3 创建带输出参数的存储过程
  6.3.4 处理错误信息
*/
--第二章 数据库的实现
--2.1 T-SQL语句回顾
---添加数据
insert into jobs(job_id,job_desc,min_lvl,max_lvl) values(1,'bbb',12,12)
---修改数据
update jobs set job_desc = 'bbbbc' where job_id = 1
---查询数据
select job_id,job_desc from jobs where job_id > 4 order by job_desc 
---删除数据
delete from jobs where job_id = 2

--2.2 使用SQL语句创建和删除数据库
---2.2.1 创建数据库
/*
create database 数据库名
  on [primary]
  (
    <数据文件参数>,[……n]
    [name = 逻辑文件名,]
    filename = 物理文件名
    [,size = 大小]
    [,maxsize = {最大容量|unlimited}]
    [,filegrowth = 增长量]
    [,……n]
    [<文件组参数>]
    filegroup 文件组名 <文件参数>[,……n]
  )
  [log on]
  (
    {<日志文件参数>,[……n]}
  )
*/
create database stuDB
on primary
(
  name = 'stuDB_data',
  filename = 'D:\project\stuDB_data.mdf',
  size = 5mb,
  maxsize = 100mb,
  filegrowth = 15%
),
(
  /*多个数据文件*/
)
log on
(
  name = 'stuDB_log',
  filename = 'D:\project\stuDB_log.ldf',
  size = 2mb,
  filegrowth = 1mb
),
(
  /*多个日志文件*/
)
go

---2.2.2 删除数据库
--drop database 数据库名
drop database stuDB
--完整T-SQL
use pubs
go
if exists (select * from sysdatabases where name = 'stuDB')
	drop database stuDB
create database stuDB 
on 
(
...
)
log on 
(
...
)
go

--2.3 使用SQL语句创建和删除表
---2.3.1 创建表
use stuDB
go
create table stuInfo
(
  stuName varchar(20) not null,
  stuNo char(6) not null,
  stuAge int not null,
  stuID numeric(18,0) --身份证号，numeric(18,0)代表18位数字，小数位数为0
  stuSeat smallint identity(1,1), 
  stuAddress text
)
go
create table stuMarks
(
  ExamNo char(7) not null,
  stuNo char(6) not null,
  writtenExam int not null,
  LabExam int not null
)
go

---2.3.2 删除表
drop table stuInfo
--完整T-SQL
use stuDB
go
if exists(select * from sysobjects where name = 'stuInfo')
	drop table stuInfo
create table stuInfo
(
...
)
go

--2.4 使用SQL语句创建和删除约束
---2.4.1 添加约束
/*
alter table 表名
add constraint 约束名 约束类型 具体的约束说明
*/
----添加主键约束(stuNo作为主键)
alter table stuInfo
add constraint PK_stuNo primary key (stuNo)
----添加唯一约束(身份证号唯一)
alter table stuInfo
add constraint UQ_stuID unique (stuID)
----添加默认约束,(如果地址不详，默认为"地址不祥")
alter table stuInfo
add constraint DF_stuAddress default ('地址不祥') for stuAddress
----添加检查约束，要求年龄只能在15-40岁之间
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)
----添加外键约束(主表stuInfo和从表stuMarks建立关系,关联字段为stuNo)
alter table stuMarks
  add constraint FK_stuNo
    foreign key (stuNo) references stuInfo(stuNo)
go

---2.4.2 删除约束
/*
alter table 表名
  drop constraint 约束名
*/
alter table stuInfo
  drop constraint DF_stuAddress

--2.5 使用SQL语句创建登录
---2.5.1 创建登录账户
--exec sp_grantlogin 'windos 域名\域账户'
--exec sp_addlogin '账户名','密码'
exec sp_grantlogin 'G\cjf'
exec sp_addlogin 'congcong','2664'
go

---2.5.2 创建数据库用户
--exec sp_grantdbaccess '登录账户','数据库用户'
/*--在stuDB数据库中添加两个用户--*/
use stuDB
go
exec sp_grantdbaccess 'G\cjf','stuDB'
exec sp_grantdbaccess 'zhangsan','zhangsanDBUser'

--2.5.3 向数据库用户授权
授权的语法为：
grant 权限 [on 表名] to 数据库用户
use stuDB
/*--为zhangsanDBUser分配对表stuInfo的select,insert,update权限--*/
grant select,insert,update on stuInfo to zhangsanDBUser

/*--为S26301DBUser分配建表的权限--*/
grant create table to S26301DBUser

--第三章 T-SQL编程
--3.1 使用变量
---3.1.1 局部变量
--语法：declare @variable_name DataType
declare @name varchar(20)
declare @seat int
--局部变量赋值两种方法 set语句或select语句
--语法：set @variable_name = value或 select @variable = value
---3.1.2 全局变量
print @@error			--最后一个T-SQL错误的错误号
print @@identity 		--最后一次插入的标识值
print @@language		--当前使用的语言的名称
print @@max_connections		--可以创建的同时连接的最大数据
print @@rowcount		--受上一次SQL语句影响的行数
print @@servername		--本地服务器的名称
print @@servicename		--该计算机上的SQL服务的名称
print @@timeticks		--当前计算机上每刻度的微秒数
print @@trancount		--当前连接打开的事务数
print @@version			--SQL Server的版本信息

--3.2 输出语句
--print 局部变量或字符串
--select 局部变量 AS 自定义列名
--3.3 逻辑控制语句
---3.3.1 IF-ELSE 条件语句
/*语法：
  if(条件)
	语句或语句块
  else
	语句或语句块
*/
---3.3.2 WHILE 循环语句
/*语法:
  while (条件)
	语句或语句块
	[break]
*/
---3.3.3 CASE多分支语句
/*语法：
  case
    when 条件1 then 结果1
    when 条件2 then 结果2
  end
*/
--3.4 批处理语句
go

--第四章 高级查询
---4.1 简单子查询
--实现1：采用T-SQL变量实现，SQL语句如下
declare @age int
select @age=stuAge from stuInfo where stuName='cjf'
select * from stuInfo where stuAge>@age
go
--实现2：采用子查询实现，SQL语句如下
select * from stuInfo
  where stuAge>@age
---示例2：
select * from stuInfo
  where stuAge>(select stuAge from stuInfo where stuName='cjf')
---示例3：采用表连接
select stuName from stuInfo inner join stuMarks
  on stuInfo.stuNo = stuMarks.stuNo where writtenExam=60
go
---示例4：采用子查询
select stuName from stuInfo
  where stuNo=(select stuNo from stuMarks where writtenExam=60)
go

--4.2 IN和NOT IN子查询
---示例5： 采用IN子查询
select stuName from stuInfo
  where stuNo in (select stuNo from stuMarks where writtenExam=60)
go

--4.3 EXISTS 和 NOT EXISTS 子查询
if exists(select * from sysdatabases where name='stuDB')
  drop database stuDB
create database stuDB
...
if exists(子查询)
  语句

--第五掌 事务、索引和视图
--5.1 事务
/*Transact-SQL使用下列语句来管理事务
  开始事务：begin transaction
  提交事务：commit transaction
  回滚事务: roolback transaction
*/
--5.2 索引
---5.2.1 什么是索引
--索引:是SQL Server编排数据的内部方法，它为SQL Server提供一中方法来编排查询数据的路由。
/*索引三大类
  唯一索引、主键索引、聚集索引
*/
---5.2.2 如何创建索引
/* 语法
create [unique][clustered][nonclustered] index index_name
  on table_name (column_name[,column_name].....)
    [with
       fillfactor=x
    ]
*/

--5.3 视图
---5.3.2 如何创建视图
/*语法：
create view view_name
  as 
    <select 语句>
*/

--第六章 存储过程
--6.2 常用的系统存储过程
/*   系统存储过程名		      说明
	sp_databases		列出服务器上的所有数据库
	sp_helpdb		报告有关指定数据库或所有数据库的信息
	sp_renamedb		更改数据库的名称
	sp_tables		返回当前环境下可查询的对象的列表
	sp_columns		返回某个表列的信息
	sp_help			返回某个表的所有信息
	sp_helpconstraint	查看某个表的约束
	sp_helpindex		查看某个表的索引
	sp_stored_procedures	列出当前环境中的所有存储过程
	sp_password		添加或修改登录账户的密码
	sp_helptext		显示默认值、未加密的存储过程、用户自定义的存储过程、触发器或视图的实际文本
*/
exec sp_databases
exec sp_helpdb
exec sp_renamedb
exec sp_tables
exec sp_columns
exec sp_help
exec sp_helpconstraint
exec sp_helpindex
exec sp_stored_procedures
exec sp_password
exec sp_helptext
--扩展
exec xp_cmdshell 'mkdir d:\bank'，no_output--无输出

--6.3 用户自定义存储过程
---6.3.1 创建不带参数的存储过程
/*语法：
create procedure 存储过程名
	[{@参数1 数据类型}[= 默认值] [output],
	....,
	 {@参数n 数据类型}[= 默认值] [output]
	]
  as
	SQL 语句
*/
if exists(select * from sysobjects where name = 'proc_stu')
  drop procedure proc_stu
go

create procedure proc_stu
 as
...
go
exec proc_stu

---6.3.2 创建带输入参数的存储过程
if exists(select * from sysobjects where name='proc_stu')
  drop procedure proc_stu
go

create procedure proc_stu
  @writtenPass int=60,
  @labPass int=60
as
...
go
exec proc_stu 60,55
--exec proc_stu @labPass=55,@writtenPass=60

--exec proc_stu --都采用默认值：笔试和即使及格线都为60分
--exec proc_stu 64 --机试采用默认值：笔试及格线64分，机试及格线60分
--exec proc_stu 60,55 --都不采用默认值
--错误的调用方式： exec proc_stu ,55 --希望笔试采用默认值，机试及格线55分
--正确的调用方式： exec proc_stu @labPass=55 --笔试采用默认值，机试及格线55分

---6.3.3 创建带输出参数的存储过程
if exists(select * from sysobjects where name='proc_stu')
  drop procedure proc_stu
go

create procedure proc_stu
  @notpassSum int output,--output关键字，否则视为输入参数
  @writtenPass int=60, --默认参数放后
  @labPass int=60 --默认参数放后
  as
  ....
go

declare @sum int
exec proc_stu @sum output,64

---6.3.4 处理错误信息
if(not @writtenPass between 0 and 100) or (not @labPass between 0 and 100)
  begin
    raiserror('错误！',16,1)
    return 
  end

--第七章 触发器
--7.3 如何创建触发器
---7.3.1 创建INSERT、DELETE、UPDATE触发器
/*语法：
create trigger Trigger_name
  on table_name
    [with encryption]--加密 可以防止触发器作为SQL Server复制的一部分发布
    for {[delete,insert,update]}
     as
	SQL 语句
*/