SQL Server数据库语法篇_sql server语法

SQL Server基础语法

一. 数据库的基本操作（命令操作）

1.1 创建数据库

1. create database 数据库名字
-- create databse mi

-- 创建数据库并设置文件大小等操作
2. create database 数据库名字
on primary(
name='数据文件名',
filename='完整路径\数据文件名.mdf',
size=初始大小,
maxsize=最大大小,
filegrowth=文件增长比
)
log on (
name='日志文件名',
filename='完整路径\日志文件名.ldf',
size=初始大小,
maxsize=最大大小,
filegrowth=文件增长比
)
-- 快速刷新界面
ctrl+shift+r 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- 实例1
create databse Mi

-- 实例2
create database Mi
on primary(
	name = 'Mi_data',
	filename = 'E:\SqlServerDate\OthersData\Mi_data.dmf',
	size = 2mb,
	maxsize=10mb,
	filegrowth=1mb
)
log on(
	name = 'Mi_log',
	filename = 'E:\SqlServerDate\OthersData\Mi_log.lmf',
	size = 2mb,
	maxsize=10mb,
	filegrowth=1mb
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JEszw8V1-1669599068547)(C:\Users\Mi ManChi\Desktop\学习札记\后端\SQL\创建数据库.png)]

1.2.查看及修改数据库

1. 查看数据库
exec sp_helpdb 数据库名
exec sp_helpdb Mi

2.修改数据库
alter database 数据库名
add file/add log file /modify file （file为数据库mdf文件名）
1
2
3
4
5
6
7

-- 1.查看Mi数据库信息
exec sp_helpdb Mi

-- 2.修改数据库文件
alter database Mi
modify file( 
  name='Mi_data',
  filename='E:\SqlServerDate\OthersData\Mi_data777.dmf'
  size=10MB
)
-- 注意如果你修改了filename的路径，该数据库将会出现---恢复挂起（先分离则不会）
1
2
3
4
5
6
7
8
9
10
11

1.3.分离、附加和删除数据库

分离：就是让当前这个数据库断开数据库服务，在MSMS管理工具界面中移出数据库，但是在本地的硬盘中还是存在的，只要把当前数据拷走就是了（分离的说白了就是为了能够将当前数据库给手动拷贝走）

附加：使用别人的数据，单纯讲就是物理的导入数据库

sp_xxxx_db都是系统数据库提供的一些工具一样的东西，类似于方法一样，调用了对应方法就会执行相关操作

-- 分离数据库
1.exec  sp_detach_db ' 数据库名'
-- 附加数据库
2.exec sp_attach_db '数据库名','完整路径\数据文件名.mdf'
-- 删除数据库（注意哦，删除不可逆哦,当前使用库删除操作不能完成）
3.drop database 数据库名

-- 判断加删除数据库

1
2
3
4
5
6
7
8
9

-- 1. 分离数据库
exec sp_detach_db Mi 
-- 2.附加数据库
exec sp_attach_db 'Mi','E:\SqlServerDate\OthersData\Mi_data.dmf'

-- 3.删除数据库
drop database Mi

-- 4.判断加删除数据库
if exists (select * from sys.databases where name = 'Mi')
	drop database Mi
1
2
3
4
5
6
7
8
9
10
11

-- 如何导入excle表的数据？

1
2

1.4 备份和还原

SQL Server四种备份方式：完整备份、差异备份、事务日志备份、文件和文件组备份

SQl Server三种恢复模式：完整恢复、大容量日志恢复、简单恢复模式

新建备份设备

use 数据库名
go
exec sp_addumpdevice '磁盘设配'，'备份名称','设备名称物理存储路径'

1
2
3
4

use Mi
go 
ecex sp_addumpdevice 'disk'，'备份设备01','E:\SqlServerDate\数据备份'
1
2
3

备份命令

backup database 数据库名 to 备份设备名称
1

backup database student to 备份设备001
1

数据库还原

restore database 数据库名  from 备份设备名称 with replace
1

restore database student from 备份设备01 with replace
1

二.数据库表的操作

2.1 建表及数据类型

2.1.0 数据库建表

1.建表
-- 切换到目标数据库
use 目标数据库名
create table 表名(
	字段1  数据类型 ,
	字段2  数据类型 
)
-- 创建组合键
create table user(
	Sno char(6) not null ,
	Pno char(6) not null ,
	Jno char(6) not null,
	primary key(Sno,Pno,Jno),
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 切换数据库
use mi


-- 可以判断下，如果有删除，不推荐，怕数据丢失   sys.objects视图下面的表 U用户定义的表
if exists(select * from sys.objects where name = 'miuser' and type ='U' )
	drop table miuser

-- 创建数据表miuser
create table miuser(
	-- 字段1 indentity(初始,步长)
	userId int primary key identity(1,1),
	userName nvarchar(50)not null,
	gender nvarchar(2) not null
)

-- 可以判断下 如果没有就创建
if not exists(select * from sys.objects where name = 'miuser' and type ='U' )
	use mi
	create table miinfo(
	 phoneId int primary key identity(1,1),
	 phoneName nvarchar(20) not null,
	 phoneBrand nvarchar(30) not null
	
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2.1.1 数据类型

1. 整数型 (int)

   userId int primary key identity(1.1)
   1

2. 定长字符（char）

   userName char(10) not null
   -- char(10) 即使存入'ab'两个字节，但它仍然占用10个字节
   1
   2

3. 变长字符（varchar）

   userName varchar(10) not mull
   -- varchar(10) 存入多少占用多少字节 最大为10个字节
   1
   2

4. 长文本类型（text）

   address text not null
   -- text是长文本类型，可以无限制写入，但是执行效率比较低
   1
   2

5. char、varchar、text前加n

   userName nvarchar(100) not null
   -- nvarchar(100) 存储unicode码 
   varchar(100) 存储100个字母，存储50汉字
   nvarchar(100) 存储100个字母，存储100汉字
   1
   2
   3
   4

6. 时间（date、datetime）

   -- datetime可以存储年月日时分秒，当前时间前后都可以
   userBirth datetime not null,
   -- date存储年月日
   userBirth date not null
   
   -- smalldatetime 表示在当前时间之前的时间
   userBirth smalldatetime not null
   1
   2
   3
   4
   5
   6
   7

7. 小数（float，decimal,bit）

   salary decimal(12,2) check(salary >=1000 and salary <= 1000000) not null,-- 薪水
   decimal(总长度，小数位数)	
   bit类型放0和1
   1
   2
   3

三.表结构和约束维护

3.0 查看表结构

-- 语法：exec sp_help 表名
exec sp_help mi
1
2

3.1 表结构修改

1. 添加列

  -- 语法：alter table 表名 add 新列名 数据类型
  alter table userInfo add email nvarchar(20)
1
2

2. 删除列

  -- 语法：alter table 表名 drop column 列名
  alter table userInfo drop email 
1
2

3. 修改列

   -- 语法：alter table 表名 alter column 列名 数据类型
   alter table userInfo alter column phone nvarchar(13)
   
   
   -- 注意：如果该表数据，phone字段数据长度假设添加的是20位的，现在修改位13是会报错的
1
2
3
4
5

3.3 表约束的创建

-- 列名1 数据类型(长度) not  null primary key
-- 列名2 数据类型(长度) not  null references 主键表(主键)
-- 列名3 数据类型(长度) not  null check(列名3='' and 列名3='')
-- 列名4 数据类型(长度) not  null default '默认值'
-- 列名5 数据类型(长度) not  null unique
1
2
3
4
5

3.3 表约束修改

1. 删除约束

      -- 语法：alter table 表名 drop constraint 约束名
      alter table UserInfo drop constraint CK__UserInfo__salary__7C4F7684
      -- 约束名怎么找？
      -- 1. 当前表右键设计表   2.计入设计表后任意位置右键 找到check约束，可以选择手动删除或命令删除
      -- 要想修改某个约束，必须删除当前约束在添加约束
1
2
3
4
5

2. 添加约束

   -- 添加约束
   -- 为salary字段添加  check约束
   alter table UserInfo add constraint  CK__UserInfo__salary66 check(salary >=1000 and salary <=200000)
   ```

   ```sql
   -- 添加（主键）
   -- alter table UserInfo add constraint 约束名 primary key（列名）
   
   -- 添加（唯一）
   -- alter table UserInfo add constraint 约束名 unique（列名）
   
   -- 添加（默认值）
   -- alter table UserInfo add constraint 约束名 default 默认值 for（列名）
   
   -- 添加（外键）
   -- alter table UserInfo add constraint 约束名 foreign key（列名） references 关联表名(列名(主键))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

四.添加数据

数据库创建及操作数据导入脚本

-- 创建数据库
create database UserSystem
on primary(
	name = 'user_data',
	filename = 'E:\SqlServerDate\OthersData\sql\user_data.dmf',
	size = 2mb,
	maxsize=10mb,
	filegrowth=1mb
)
log on(
	name = 'user_log',
	filename = 'E:\SqlServerDate\OthersData\sql\user_log.lmf',
	size = 2mb,
	maxsize=10mb,
	filegrowth=1mb
)

-- 修改数据库 
-- 将主数据文件默认大小修改位10mb，最大为20mb
use UserSystem
alter database UserSystem modify file(
	name = 'user_data',
	size=10mb,
	maxsize=20mb
)


-- 创建Department部门数据表
create table Department(
	DpartmentId int primary key identity(1,1),
	DepartmentName nvarchar(10) not null,
	DepartmentRemark text
)

-- 创建等级数据表
create table [Rank](
	RankId int primary key identity(1,1),
	RankName nvarchar(10) not null,
	RankRemark text
)

-- 创建员工表
create table People(
	DepartmentId int references Department(DpartmentId) not null,-- 部门 引用外键
	RankId int references [Rank](RankId),--职级
	PeopleId int primary key identity(202200,1),-- 员工编号
	PeopleName nvarchar(50) not null,
	PeopleGender nvarchar(1) default('男') check(PeopleGender='男' or PeopleGender='女') not null,
	PeopleBirth smalldatetime not null,
	PeopleSalary decimal(12,2) check(PeopleSalary >=1000 and PeopleSalary <=1000000) ,
	PeoplePhone nvarchar(11)  unique not null,
	PeopleAddress nvarchar(100),
	peopleAddTime smalldatetime default(getdate()),

)

-- 数据添加

-- Department表插入数据
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','........')
insert into Department(DepartmentName,DepartmentRemark)
values('策划部','........')
insert into Department(DepartmentName,DepartmentRemark)
values('市场部','........')
insert into Department(DepartmentName,DepartmentRemark)
values('设计部','........')
insert into Department(DepartmentName,DepartmentRemark)
values('后勤部','........')

 -- 向Rank表
insert into [Rank](RankName,RankRemark)
values('初级','.....')
insert into [Rank](RankName,RankRemark)
values('高级','.....')
insert into [Rank](RankName,RankRemark)
values('中级','.....')


select * from People
select * from [Rank]
select * from Department


--向People表输入数据  ctrl+shift+r刷新界面
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)
values(7,1,'徐宏','男','2000-05-6',6000,'19123929393','中国南昌',getdate(),'124144512@qq.com')
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)
values(3,2,'徐向前','男','1997-09-6',12000,'456346929393','中国广州',getdate(),'999999912@qq.com')
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)
values(1,2,'谢颖儿','女','2000-09-9',8000,'1994329393','中国四川',getdate(),'1888144512@qq.com')
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)
values(4,3,'老王','男','1976-10-23',3000,'999939333','中国黑龙江',getdate(),'99999512@qq.com')
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)
values(2,1,'老六','男','1999-10-23',4500,'1291248143','中国山东',getdate(),'1244559512@qq.com')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

4.1 添加数据

-- 单次向表中添加数据
-- 1.前提得使用当前数据库
-- 语法：insert into 表名(字段1,字段2) value('值','值')
use mi
insert into Department(departmentName,departmentAddress,departmentLeader)
values('市场部','行政大楼2层201','Mr Xiao')

insert into Department (departmentName,departmentAddress,departmentLeader)
values('营销部','行政大楼2层203','Mr hu')

insert into Department (departmentName,departmentAddress,departmentLeader)
values('策划部','行政大楼2层205','Miss hua')


-- 2.简写 前提：字段名对应位置不要打乱
insert into Department valuses('值1','值2')

-- 如果插入的数据个数和字段个数一一对应，可以省略字段的书写

-- 3.一次性插入多条数据
insert into Department(departmentName,departmentAddress,departmentLeader)
select '软件部','行政大楼2层202','Mr Xie' union 
select '测试部','行政大楼2层206','Miss Liu'union
select '实施部','行政大楼2层207','mr Gong'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4.2 数据修改和删除

-- 修改
格式： update 表名 set 字段1 = 值,字段2 = 值，....字段n =值 where 条件 
update student set sname = '张亮'  where sno = 60202011
update Product set price = price + price *0.1 where id = ?????

-- 删除数据
格式1: delete from 表名 where 条件（也可以不带条件，不带条件默认清空表数据，请谨慎）
格式2：drop table 表名 
格式3：truncate from 表名 

1
2
3
4
5
6
7
8
9
10

delete、truncate、drop删除数据的区别？

1. delete删除数据可以带条件，清空数据但表的结构还在；如果表中数据为自动编号，使用delete删除后序号是从下一个开始。即原表序号1,2,3,4,5,6 ，删除记录第6条，再次向表新增一条数据，编号从7开始；也就是说表中不会存在编号为6的记录。

   delete from 表名 where id = 1 and name= 'xxx'
   1

2. truncate删除数据不可以带条件，清空数据但表结构还在；如果表中数据为自动编号，使用truncate删除后序号是从删除的当前序号开始。即原表序号1,2,3,4,5,6 ，删除记录第6条，再次向表新增一条数据，编号从6开始。

   truncate table 表名 
   1

3. drop删除数据，直接删除表结构和数据

   drop table 表名
   1

五.查询数据（重点）

5.1 查询数据（简单查询）

People表

--查询指定列(姓名，性别，出生日期)先写表名会有提示
select PeopleName,PeopleGender,PeopleBirth from People

--查询指定列(姓名，性别，出生日期)并且用别名显示
select PeopleName 姓名,PeopleGender 性别,PeopleBirth 出生日期 from People

-- 查询所在城市（过滤重复）
select  distinct PeopleAddress  from People

--假设准备加工资（上调20%) 查询出加工资后的员工数据
select PeopleName,PeopleGender,PeopleSalary*1.2 加薪后工资  from People 

--假设准备加工资（上调20%) 查询出加工资后和加工资前的员工数据对比
select PeopleName,PeopleGender,PeopleSalary, PeopleSalary*1.2 加薪后工资  from People 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.2查询数据 （条件查询）

-- SQL常用运算符
= :等于，比较是否相等及赋值
!=:比较不等于 （<>）
>:比较大于
<:比较小于
>=:比较大于等于
<=:比较小于等于
IS NUll:比较为空（null是表示此时没写该字段，而不是空值null，如果是空值"" 用=）
IS NOt NUll :比较不为空
in:比较是否再其中
like:模糊查询
BETWEEN....AND.......：比较是否在两者之间
and：逻辑与(两个条件都满足)
or:逻辑或（两个有一个条件表达式成立）
not:逻辑非（条件成立，表达式则不成立；条件不成立，表达式则成立 ）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 查询数据为女的信息
select * from People where PeopleGender = '女'

-- 查询数据为男的，工资大于8000的数据
select * from People where PeopleGender ='男' and PeopleSalary >=8000

-- 查询出出生年月在1990-1-1之后，月薪大于10000的女员工
select * from People where PeopleBirth >= '1990-1-1' and PeopleSalary >=10000 and PeopleGender = '女'

--查询月薪大于10000的，或者月薪大于8000的女员工
select * from People where PeopleSalary >=10000 or (PeopleSalary>=8000 and PeopleGender='女')

-- 查询月薪在8000-12000之的员工姓名、住址和电话（多条件）
select PeopleName,PeopleAddress,PeoplePhone  from People where PeopleSalary >=8000 and PeopleSalary <=120000

-- 查询月薪在8000-12000之的员工姓名、住址和电话（多条件）
select PeopleName,PeopleAddress,PeoplePhone  from People where PeopleSalary between 8000 and 120000


-- 查询出地址在南昌和贵州的员工信息
select * from People where PeopleAddress ='南昌' or PeopleAddress='贵州'

-- 如果涉及条件比较多用in(或者关系)

select * from People where PeopleAddress in('南昌','贵州','黑龙江')

-- 排序

--根据工资降序排序
select * from People order by PeopleSalary desc


--根据工资升序排序（asc默认值）
select * from People order by PeopleSalary asc


-- 根据名字长度降序排序
select * from People order by LEN(PeopleName) desc

-- 根据名字长度降序排序（显示前3条）
select top 3 * from People order by LEN(PeopleName) desc



select * from People
-- 查出工资最高的50%的员工信息
select top 50 percent * from People order by PeopleSalary desc


-- 插入一条数据
insert into People(DepartmentId,[RankId],[PeopleName],[PeopleGender],[PeopleBirth],[PeopleSalary],[PeoplePhone],[peopleAddTime],[PeopleMail])
values(1,1,'老李头','男','1999-12-21',23000,19293459999,GETDATE(),'888883333@qq.com')


-- 查询地址为空值的为null 用is关键字
select * from People where PeopleAddress is null


-- 查询地址为不为空值的为null 用is not关键字
select * from People where PeopleAddress is not null


-- 查询出90后的员工信息
select * from People where PeopleBirth >= '1990-1-1' and PeopleBirth <='1999-1-1'
select * from People where PeopleBirth between  '1990-1-1' and '1999-1-1'
select * from People where  year(PeopleBirth) between 1990 and 1999


-- 查询年龄在20- 30之间，工资在15000-20000的员工信息
-- 当前year(getdate())—year(peopelbirth)
select * from People where
  year(getdate())-year(PeopleBirth) <=30 and year(getdate())-year(PeopleBirth) >=20
and 
  PeopleSalary >= 15000 and PeopleSalary <=20000


-- 查询出星座为巨蟹座的员工信息（6.22-7.22）
select * from People where month(PeopleBirth) = 6 and day(PeopleBirth) >=22 
and 
month(PeopleBirth) =7 and day(PeopleBirth) <=22


-- 子查询  查询出工资比胡九九高的员工信息
select * from People where PeopleSalary > (select PeopleSalary from People where PeopleName ='胡九九')

-- 查询出和老王同一城市的员工
select * from People where PeopleAddress = (select PeopleAddress from People where PeopleName ='老王')


-- 查询出生肖信息为老鼠的员工信息
-- 鼠牛虎兔龙  蛇马 羊 猴 鸡 狗 猪
-- 4 5 6 7 8  9 10 11 0  1  2  3
select * from People where year(PeopleBirth)% 12 = 8


-- 查询出所有员工信息的生肖信息
select * ,
case 
	 when year(PeopleBirth) % 12 =4 then '鼠'
	 when year(PeopleBirth) % 12 =5 then '牛'
	 when year(PeopleBirth) % 12 =6 then '虎'
	 when year(PeopleBirth) % 12 =7 then '兔'
	 when year(PeopleBirth) % 12 =8 then '龙'
	 when year(PeopleBirth) % 12 =9 then '蛇'
	 when year(PeopleBirth) % 12 =10 then '马'
	 when year(PeopleBirth) % 12 =11 then '羊'
	 when year(PeopleBirth) % 12 =0 then '猴'
	 when year(PeopleBirth) % 12 =1 then '鸡'
	 when year(PeopleBirth) % 12 =2 then '狗'
	 when year(PeopleBirth) % 12 =3 then '猪'
	else ''
end '生肖'
from People

-- 查询出所有员工信息的生肖信息
select * ,
case  year(PeopleBirth) % 12
	 when 4 then '鼠'
	 when 5 then '牛'
	 when 6 then '虎'
	 when 7 then '兔'
	 when 8 then '龙'
	 when 9 then '蛇'
	 when 10 then '马'
	 when 11 then '羊'
	 when 0 then '猴'
	 when 1 then '鸡'
	 when 2 then '狗'
	 when 3 then '猪'
	else ''
end '生肖'
from People

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

5.3查询数据（模糊查询）

%:代表匹配0个，1个字符或者多个字符
_:代表匹配有且只有一个字符
[]:代表匹配范围内
[^]:代表匹配不在范围内
1
2
3
4

use UserSystem
select * from People
select * from [Rank]
select * from Department

-- 查询出姓老的员工信息
select * from People where PeopleName like '老%'

--查询出姓名中含有九字的员工信息
select * from People where PeopleName like '%九%'

-- 查询出名字中含有“老”或者是“六”的员工信息
select * from People where PeopleName like '%老'or PeopleName like '%六%'

-- 查询出名字为两个字的胡姓员工信息
select * from People where PeopleName like '胡_'
select * from People where SUBSTRING(PeopleName,1,1) ='胡' and
len(PeopleName) =2

-- 查询名字最后一个字为九的员工信息（假设表中名字长度都为3个）substring(字段,开始位置，截取个数)
select * from People where SUBSTRING(PeopleName,3,1)='九' and
len(PeopleName) =3
select * from People where PeopleName like '__九'（注意这里有两个下划线占位符）

-- 查询电话以191开头的员工信息 
select * from People where PeoplePhone like '191%'
-- 查询电话以191开头,第四位是3或6的电话，最后一位是3的
select * from People where PeoplePhone like '191[3,6]%3'


-- 查询电话以192开头的，中间是7-9的数字一个，结尾不是以678结尾得
select * from People where PeoplePhone like '192[7,8,9]%[^6,7,8]'
select * from People where PeoplePhone like '192[7-9]%[^6-8]'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

5.4 聚合函数

select * from People
-- 求员工总人数
select  count(*) '总人数' from People

-- 求最大值 最高工资
select max(PeopleSalary) '最高工资' from People

--求最小值 最低工资
select min(PeopleSalary) '最低工资' from People

-- 求和 求所有员工工资的总和
select SUM(PeopleSalary)'工资总和' from People

-- 求平均值 求所有员工的平均工资
select Round(avg(PeopleSalary),2)'平均工资' from People
-- 参数2表示保留几位小数
select Round(999.2222,1)


-- 求数量 最高工资 最低工资 平均工资 在一行显示
select  count(*) '总人数',max(PeopleSalary) '最高工资',SUM(PeopleSalary)'工资总和'from People
where PeopleAddress = '中国南昌'


-- 求出比平均工资高的员工信息
select * from People where PeopleSalary > (select ROUND(AVG(PeopleSalary),2)平均工资 from People)

-- 求出数量 最大年龄 最小年龄 年龄总和 年龄平均值
select COUNT(*),
MAX(year(GETDATE())-year(PeopleBirth))最高年龄,
min(year(GETDATE())-year(PeopleBirth))最小年龄,
sum(year(GETDATE())-year(PeopleBirth))年龄总和,
avg(year(GETDATE())-year(PeopleBirth))年龄平均值
from People


-- 方案二
select COUNT(*),
MAX(DATEDIFF(year,PeopleBirth,getdate()))最高年龄,
min(DATEDIFF(year,PeopleBirth,getdate()))最小年龄,
sum(DATEDIFF(year,PeopleBirth,getdate()))年龄总和,
avg(DATEDIFF(year,PeopleBirth,getdate()))年龄平均值
from People

-- 求出月薪在10000以上的男员工的数量，年龄最大值 最小值
select '月薪10000以上'月薪,'男'性别, count(*)数量,max(year(GETDATE())-YEAR(PeopleBirth)) 年龄最大值,min(year(GETDATE())-YEAR(PeopleBirth)) 年龄最小值,avg(year(GETDATE())-YEAR(PeopleBirth))年龄平均值 from People where PeopleSalary >10000
and PeopleGender ='男'


-- 求出年龄比平均年龄大的员工
select * from People 
where YEAR(GETDATE())-YEAR(PeopleBirth) > (select AVG(YEAR(getdate())-year(PeopleBirth))from People)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

5.5 分组查询

--查询所有员工数量、员工工资总和、平均工资、最高工资和最低工资
select count(*) 数量,sum(PeopleSalary) 平均工资,avg(PeopleSalary) 平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
 from People


 -- 方案一 union
 --根据地区查询所有员工数量、员工工资总和、平均工资、最高工资和最低工资
 select '黑龙江' 城市, count(*) 数量,sum(PeopleSalary) 平均工资,avg(PeopleSalary) 平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
 from People where PeopleAddress = '黑龙江'
 union
 select '贵州' 城市, count(*) 数量,sum(PeopleSalary) 平均工资,avg(PeopleSalary) 平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
 from People where PeopleAddress = '贵州'

 -- 方案二 group by 方法
 select PeopleAddress 城市, count(*) 数量,sum(PeopleSalary) 平均工资,avg(PeopleSalary) 平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
 from People 
 group by PeopleAddress

 -- 根据地区查询1999年以后的员工数量、员工工资总和、平均工资、最高工资和最低工资
 select PeopleAddress 城市, count(*) 数量,sum(PeopleSalary) 平均工资,avg(PeopleSalary) 平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
 from People 
 where PeopleBirth > '1999-1-1'
 group by PeopleAddress

 -- 根据地区查询1999年以后的员工数量、员工工资总和、平均工资、最高工资和最低工资
 -- 要求筛选出员工人数至少在2人以上的记录 ，并且1999年后出身的员工不参与统计

 -- 普通条件where后 聚合函数条件写在having后
 --select PeopleAddress 城市, count(*) 数量,sum(PeopleSalary) 平均工资,avg(PeopleSalary) 平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
 --from People 
 --where PeopleBirth > '1999-1-1' and count(*)>=2
 --group by PeopleAddress 

 select PeopleAddress 城市, count(*) 数量,sum(PeopleSalary) 平均工资,avg(PeopleSalary) 平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
 from People 
 where PeopleBirth > '1999-1-1'
 group by PeopleAddress
 having count(*) >=2 -- 放在分组后
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

5.6 多表查询

-- 笛卡尔乘积
select * from People
select * from Department

select * from People,Department
-- 笛卡尔乘积就是两表的记录相乘，存在记录错误


-- 简单多表查询
-- 查询员工信息、显示部门编号
select * from People,Department
where People.DepartmentId = Department.DpartmentId

-- 查询员工信息、显示职级名称
select * from People,[Rank]
where People.RankId = [Rank].RankId

-- 查询员工信息、显示部门编号 、显示职级名称
select * from People,Department,[Rank]
where People.DepartmentId = Department.DpartmentId 
and People.RankId = [Rank].RankId


-- 内连接查询
-- 查询员工信息、显示部门编号
select * from People 
inner join Department on People.DepartmentId = Department.DpartmentId

-- 查询员工信息、显示职级名称
select * from People 
inner join [Rank] on People.RankId = [Rank].RankId

-- 查询员工信息、显示部门编号 、显示职级名称
select * from People
inner join Department on People.DepartmentId = Department.DpartmentId 
inner join [Rank] on People.RankId = [Rank].RankId

-- 简单多表查询和内连接查询共同的特点：不符合主外键关系的数据不会被显示出来


-- 外连接（左外连、右外连、全外连）
-- 查询员工信息、显示部门编号
-- 左外连（以左边为主表进行数据显示、主外键关系找不到的数据用null代替）
select * from People 
left join Department on People.DepartmentId = Department.DpartmentId

select * from Department 
left join People on People.DepartmentId = Department.DpartmentId



-- 查询员工信息、显示职级名称
select * from People 
left join [Rank] on People.RankId = [Rank].RankId


-- 查询员工信息、显示部门编号 、显示职级名称
select * from People
left join Department on People.DepartmentId = Department.DpartmentId 
left join [Rank] on People.RankId = [Rank].RankId


-- 右连接：A left join B = B right join A
select * from People 
left join [Rank] on People.RankId = [Rank].RankId

select * from [Rank] 
right join People on People.RankId = [Rank].RankId

-- 全外连：两张表的数据 无论是否符合关系 都要显示
select * from People 
full join Department on People.DepartmentId = Department.DpartmentId


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

拓展：（group by 和having 的区别）

-- group by和having的区别
1、满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有group by中包含的列

2、having必须和group by一起用，且在group by后面

3、group by、having、order by的使用顺序：group by 、having、order by

4、having是在分好组后找出特定的分组，通常是以筛选聚合函数的结果，如sum(a) > 100等，使用了having必须使用group by，但是使用group by 不一定使用having。

-- where和having的区别
-- where 子句    
1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
2) where 后面不可以使用聚合函数
3) 过滤行

-- having 子句    
1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
2) having 后面可以使用聚合函数
3) 过滤组
4) 支持所有WHERE操作符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5.7 多表查询（综合案例）

select * from People

-- 查询出城市为贵州的员工信息、部门信息
select PeopleName '姓名',DepartmentName '部门',peopleAddTime '加入时间',PeoplePhone '电话',PeopleAddress '地址' from People 
left join Department on People.DepartmentId = Department.DpartmentId where PeopleAddress = '中国贵州'


-- 查询出城市在黑龙江的员工信息 、部门信息以及等级信息
select PeopleName '姓名',People.DepartmentId,DepartmentName '部门',peopleAddTime '加入时间',PeoplePhone '电话',PeopleAddress '地址',RankName '等级名称'
from People
left join [Department] on People.DepartmentId = Department.DpartmentId 
left join [Rank] on People.RankId = [Rank].RankId where PeopleAddress ='中国黑龙江'


-- 根据部门分组统计员工人数、员工工资总和、平均工资、最高工资和最低工资
select DepartmentName 部门, count(*)人数,sum(PeopleSalary) 员工工资总和,avg(PeopleSalary)平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资  
from People 
left join Department on People.DepartmentId = Department.DpartmentId
group by Department.DpartmentId,Department.DepartmentName 


-- 根据部门分组统计员工人数、员工工资总和、平均工资、最高工资和最低工资
-- 平均工资在9000以下的不参与统计，并且根据平均工资降序排序
select DepartmentName 部门, count(*)人数,sum(PeopleSalary) 员工工资总和,avg(PeopleSalary)平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资  
from People left join Department on People.DepartmentId = Department.DpartmentId
group by Department.DpartmentId,Department.DepartmentName 
having  avg(PeopleSalary) >=  9000 
order by  avg(PeopleSalary)


-- 根据部门名称 然后根据职位名称 
-- 分组统计员工人数、员工工资总和、平均工资、最高工资和最低工资
select DepartmentName 部门,RankName 职位名称, count(*)人数,sum(PeopleSalary) 员工工资总和,avg(PeopleSalary)平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资  
from People 
left join Department on People.DepartmentId = Department.DpartmentId
left join [Rank] on [Rank].RankId = People.RankId
group by Department.DpartmentId,Department.DepartmentName ,[Rank].RankId,[Rank].RankName

-- 自连接(自己连接自己)
create table Dept(
	DeptId int primary key,
	DeptName varchar(50) not null,
	ParentId int,
)

-- 一级
insert into Dept(DeptId,DeptName,ParentId) values(1,'软件部',0)
insert into Dept(DeptId,DeptName,ParentId) values(2,'硬件部',0)

-- 二级
insert into Dept(DeptId,DeptName,ParentId) values(3,'软件研发部',1)
insert into Dept(DeptId,DeptName,ParentId) values(4,'软件测试部',1)
insert into Dept(DeptId,DeptName,ParentId) values(5,'软件实施部',1)
insert into Dept(DeptId,DeptName,ParentId) values(6,'硬件研发部',2)
insert into Dept(DeptId,DeptName,ParentId) values(7,'硬件测试部',2)
insert into Dept(DeptId,DeptName,ParentId) values(8,'硬件实施部',2)

select * from Dept

-- 自连接
select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A
inner join Dept B on A.ParentId =  B.DeptId

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

六.数据库的设计

6.1 数据库的三范式

第一范式：是对属性的原子性，要求属性具有原子性，不可再分割

错误表示范

create table student(
	sno int primary key not null,
    sname varchar(20) not null,
    sconnect varchar(50) 
)
insert into student(sno,sname,sconnect) values(1,'小明','QQ:31118534@qq.com;Tel:18273438854')
insert into student(sno,sname,sconnect) values(1,'小花','QQ:75318555@qq.com;Tel:99343535357')

-- 可以看到啥上述字段sconnect中，一个字段中值却含有多个值，不符合属性唯一性
1
2
3
4
5
6
7
8
9

修改

create table student(
	sno int primary key not null,
    sname varchar(20) not null,
    sqq varchar(30) not null,
    stel varchar(30) not null
)
insert into student(sno,sname,sconnect) values(1,'小明','31118534@qq.com','18273438854')
insert into student(sno,sname,sconnect) values(1,'小花','75318555@qq.com','99343535357')

-- 可以看到上述字段sconnect中，将该字段进行分开为两个子字段
1
2
3
4
5
6
7
8
9
10

第二范式：在满足第一范式的前提下，表与表之间存在

6.2 银行数据库业务设计

-- 表结构设计

create table AccountInfo --账户信息表
(
	AccountId int primary key identity(1,1), --账户编号
	AccountCode varchar(20) not null, -- 身份证号
	AccountPhone varchar(20) not null,-- 手机号
	RealName varchar(20) not null,--真实姓名
	OpenTime smalldatetime not null --开卡时间
)


-- 创建银行卡表
create table BankCard
(
	CardNo varchar(30) primary key,--银行卡号
	AccountId int not null, --账户编号（与账户表形成主外键关系，可以省略引用references）
	Cardpwd varchar(30) not null,--银行卡密码
	CardMoney money not null,
	CardState int not null,--1正常 2挂失 3冻结 4 注销
	CardTime smalldatetime default(getdate())--开卡时间

)

-- 创建交易信息表（存储存钱和取钱的记录）
create table CardExchange
(
	ExchangeId int primary key identity(1,1),
	CardNo varchar(30) not null,--（与银行卡形成主外键关系）
	MoneyInBank money not null,
	MoneyOutBank money not null,
	ExchangeTime smalldatetime default(getdate())

)

-- 转账信息表
create table CardTransfer
(
	TransferId int primary key identity(1,1),
	CardNoOut varchar(30) not null,-- 转出银行卡号（与银行表有主外键关系）
	CardNoIn varchar(30) not null,
	TransferMoney money not null,
	TransferTime smalldatetime not null,
)

-- 状态信息表
create table CardStateChange --状态信息变化表（--1正常 2挂失 3冻结 4 注销）
(
	StateId int primary key identity(1,1),
	CardNo varchar(30) not null,--银行卡号（银行表）
	OldState int not null,--原始转态
	NewState int not null,--新状态
	StateWhy varchar(30) not null,--状态变化原因
	StateTime smalldatetime not null,-- 记录产生时间
)


-- 为三人开卡
-- 刘备身份证：360731200006179356 
-- 关羽        360731199812047837 
-- 张飞		   36073119920417937X
-- 婉儿		   110321200008199268
insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('360731200006179356','18707976623','刘备',GETDATE())
insert into BankCard(CardNo,AccountId,Cardpwd,CardMoney,CardState)
values('6225125478544587',1,'123456',0,1)

insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('360731199812047837','18199926688','关羽',GETDATE())
insert into BankCard(CardNo,AccountId,Cardpwd,CardMoney,CardState)
values('6225125478541263',2,'123456',0,1)


insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('36073119920417937X','1916662776','张飞',GETDATE())
insert into BankCard(CardNo,AccountId,Cardpwd,CardMoney,CardState)
values('6225125478549996',3,'123456',0,1)

insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('110321200008199268','15166661923','婉儿',GETDATE())
insert into BankCard(CardNo,AccountId,Cardpwd,CardMoney,CardState)
values('6225125478576669',4,'123456',0,1)

select * from AccountInfo
select * from BankCard
select * from CardExchange
select * from CardTransfer

--进行存钱操作
update BankCard set CardMoney = CardMoney + 2000 where CardNo = '6225125478544587'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',2000,0,GETDATE())

update BankCard set CardMoney = CardMoney + 8000 where CardNo = '6225125478541263'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478541263',8000,0,GETDATE())

update BankCard set CardMoney = CardMoney + 50000 where CardNo = '6225125478549996'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478549996',50000,0,GETDATE())

update BankCard set CardMoney = CardMoney + 100000 where CardNo = '6225125478576669'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478576669',100000,0,GETDATE())

-- 转账 刘给张
update BankCard set CardMoney = CardMoney - 1000 where CardNo ='6225125478544587'
update BankCard set CardMoney = CardMoney + 1000 where CardNo ='6225125478549996'
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225125478549996',1000,GETDATE())
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

七.使用T—SQL编程

7.1 打印、变量、Go

7.1.0信息打印

-- 信息打印
print 'hello sql' -- 消息打印
select 'hello SQL' -- 表格打印
1
2
3

7.1.1 变量声明的使用

局部变量

-- 局部变量：以@开头 ，先声明在赋值
declare @str varchar(20)  -- 声明
set @str ='我爱你'--方式一
-- select @str = '我爱你' --方式二
print @str -- 打印
1
2
3
4
5

两种赋值方式的区别？

set：赋值变量指定的值
select ：一般用于表中查询出的数据赋值给变量，如果结果有多条，取最后一条
select @a = 字段名 from 表名 （把当前表最后一行赋值）

全局变量

-- 以@@开头 由系统进行定义和维护，不能改
@@ERROR:返回执行的上一个语句的错误号
@@IDENTITY:返回最后插入的标识值（特别注意delete删除的编号，是永久不在的）
@@MAX-CONNECTIONS:返回允许同时进行的最大用户连接数
@@ROWCOUNT：返回受上一语句影响的行数
@@SERVERNAME:返回运行SQL server本地服务器的名称
@@SERVICENAME:返回SQL server正在其下运行的注册表项的名称
@@TRANCOUNT：返回当前连接的活动事务数
@@LOCK_TIMEOUT：返回当前会话的当前锁定超时设置（毫秒）

1
2
3
4
5
6
7
8
9
10

例题

--(1)为老王开卡，六娃身份证360663199809239969
insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('360663199809239969','18707976600','六娃',GETDATE())
-- 将会自动调用最后加入的id
declare @AccountId int
set @AccountId = @@IDENTITY
insert into BankCard(CardNo,AccountId,Cardpwd,CardMoney,CardState)
values('6225125478556789',@AccountId,'123456',0,1)

select * from AccountInfo
select * from BankCard

--(2)查出婉儿的银行卡和余额 婉儿身份证：110321200008199268
select CardNo 卡号,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where AccountCode = '110321200008199268'

declare @AccountNo int
select @AccountNo=
(select AccountId from AccountInfo where AccountCode ='110321200008199268')

select CardNo 卡号,CardMoney 余额 from BankCard
where AccountId = @AccountNo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

7.1.2 Go语句的使用

-- （1）等待go语句之前代码完成之后才能执行后面代码
--create database [user]
--go
--use [user]

--  (2)批处理结束的一个标志
declare @num int --@num作用范围全局
set @num =100 
set @num =200
print @num --200

go
declare @num1 int -- @num1作用范围在两个go之间
set @num1 =100
go
-- set @num1 =200

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

7.2 运算符

7.2.0 七种T-SQL中使用的运算符(强类型)

-- （1）已知长方形的长和宽，求周长和面积
declare @length int
set @length = 10 --长
declare @height int  = 5 --宽

declare @perimeter int
declare @area int
set @perimeter = (@length + @height) *2
set @area = @length * @height
-- print  '周长'+ @perimeter -- 报错 类型得转化为字符串
print  '周长'+ Convert(varchar(10),@perimeter)
print  '面积'+ Convert(varchar(10),@area)
 

print  '周长'+ cast(@perimeter as varchar(10))
print  '面积'+ cast(@area as varchar(10))
 

 --（2） 查询出银行卡状态为正常，余额大于5000的用户
 select * from BankCard where CardState = 1 and CardMoney > 5000

 --（3）查询出银行卡状态为正常或者余额大于5000的用户
  select * from BankCard where CardState = 1 or CardMoney > 5000

  --(4） 查询出姓名中含有'刘’的账户信息及银行卡信息
  select * from AccountInfo 
  inner join BankCard on BankCard.AccountId = AccountInfo.AccountId
  where RealName like '%刘%'

 
insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('3606731199809239920','18707976600','翠花',GETDATE())
declare @AccountId int
set @AccountId = @@IDENTITY
insert into BankCard(CardNo,AccountId,Cardpwd,CardMoney,CardState)
values('6225125478556777',@AccountId,'123456',0,1)

--(5) 查询出余额在2000-5000之间的银行卡信息
select * from BankCard where CardMoney between 2000 and 5000

--(6) 查询出银行卡状态为冻结或者注销的银行卡信息
-- select * from BankCard where CardState = 2 or CardState = 3
select * from BankCard where CardState in(3,4)


select * from AccountInfo
select * from BankCard
-- （7） 刘备身份证：360731200006179356 刘备到银行来开户，
--查询身份证在账户表中是否存在，不存在则进行开户开卡，存在则不开户直接开卡

declare @AccountIdd int
if exists (select * from AccountInfo where AccountCode = '360731200006179356')  --存在此人
	begin
		select @AccountIdd=
		(select AccountId from AccountInfo where AccountCode = '360731200006179356')
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225125478556333', @AccountIdd,'123456',0,1)
	end
else --不存在此人
	begin
		insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
		values('360731200006179356','18000772993','刘备',getdate())
		set @AccountIdd = @@IDENTITY
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225125478556333',@AccountIdd,'123456',0,1)
	end


-- （7） 婉儿身份证：110321200008199268 婉儿到银行来开户，
--查询身份证在账户表中是否存在，不存在则进行开户开卡，存在则不开户直接开卡

declare @AccountIdNo int
if exists (select * from AccountInfo where AccountCode = '110321200008199268')  --存在此人
	begin
		select @AccountIdNo=
		(select AccountId from AccountInfo where AccountCode = '110321200008199268')
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225125478556996', @AccountIdNo,'123456',0,1)	
	end
else --不存在此人
	begin
		insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
		values('110321200008199268','19256998934','婉儿',getdate())
		set @AccountIdNo = @@IDENTITY
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225125478556000',@AccountIdNo,'123456',0,1)
	end

-- 拓展：上面要求
-- 添加一个限制即一个人最多只能开3张卡
declare @AccountIdNum int
declare @AccountCount int
if exists (select * from AccountInfo where AccountCode = '110321200008199268')  --存在此人
	begin
		select @AccountIdNum=
		(select AccountId from AccountInfo where AccountCode = '110321200008199268')
		select @AccountCount =
		(select COUNT(*) from BankCard where AccountId = @AccountIdNum )
		if @AccountCount <= 2
			begin
				insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
				values('6225125478556996', @AccountIdNum,'123456',0,1)
			end
		else
			begin
				print'您名下已经有'+ cast(@AccountCount as varchar(20)) +'卡了，超出了开卡限制'
			end
		
	end
else --不存在此人
	begin
		insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
		values('110321200008199268','19256998934','婉儿',getdate())
		set @AccountIdNum = @@IDENTITY
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225125478556996',@AccountIdNum,'123456',0,1)
	end


--（8）查询银行卡账户余额，是不是所有的账户余额都超过了3000
-- 使用all ang数字一定写在后面
if 3000 < All(select CardMoney from BankCard)
	begin 
		print '所有银行卡余额都大于3000'
	end
else
	begin
		print '不是所有银行卡余额都大于3000'
	end


--（9）查询银行卡账户余额，是否含有的账户余额超过了10000
if 80000 < any(select CardMoney from BankCard)
	begin 
		select * from AccountInfo inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
		where CardMoney > 80000
		print '含有银行卡余额大于800000'
	end
else
	begin
		print '所有银行卡余额都不大于100000'
	end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142

7.3 流程控制

7.3.1选择分支结构

select * from BankCard
select * from CardExchange
-- 选择分支结构
--（1） 某用户银行卡为：6225125478549996
--该用户执行取钱操作，取5000，充足则取钱 提示取钱成功
-- 不足则提余额不足
declare @money money
select @money = 
(select CardMoney from BankCard where CardNo = '6225125478549996' ) 
if @money >= 5000
	begin
		update BankCard set CardMoney = CardMoney -5000 where CardNo = '6225125478549996' 
		print '取钱成功'
		insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
		values('6225125478549996',0,1,GETDATE())
	end
else -- 不足5000
	begin
		print '余额为' +cast(@money as varchar(20) )+ '不足5000'
	end


-- 查询银行卡信息，将银行卡状态1,2,3,4分别转换为正常、挂失、冻结、注销
--并根据余额分类显示用户等级
-- 30以下普通用户 30及以上VIP用户
-- 显示列分别卡号、身份证、姓名、余额、用户等级、银行卡状态、

select CardNo 卡号,AccountCode 身份证号,CardMoney 余额,
case
	when CardMoney >=30000 then 'VIP用户'
	else '普通用户'
end 用户等级,
case CardState 
	when 1 then '正常'
	when 2 then '挂失'
	when 3 then '冻结'
	when 4 then '注销'
	else '异常'
end 银行卡状态
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId



-- 循环结构（while）
-- 1.循环打印1-10
declare @num int =1
while @num <=10
begin
	print @num
	set @num  =@num +1
end

-- 2.循环打印九九乘法表
--制表符 char(9) 制表位 char(10) 换行
declare @i int =1
while @i <= 9
begin
	declare @str varchar(1000)=''
	declare @j  int =1
	while @j <= @i
		begin
			set @str =@str +  cast(@i as varchar(1)) + '*' + cast(@j as varchar(1)) 
			+ '=' + cast(@i*@j as varchar(100)) + char(9)
			set @j =@j +1
		end
	set @i  =@i +1
	print @str
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

7.3.2 子查询

select * from AccountInfo
select * from BankCard
--（1）关系卡号：6225125478541263
-- 查询出余额比关羽多的银行卡信息、显示卡号、身份证，姓名、余额
-- 方案一
select CardNo 卡号,AccountCode 身份号,RealName 姓名 ,CardMoney 余额 
from AccountInfo inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
where CardMoney > 
(select CardMoney from BankCard where CardNo = '6225125478541263')

-- 方案二（非子查询）
declare @blance money
select  @blance = 
(select CardMoney from BankCard where CardNo = '6225125478541263')
select CardNo 卡号,AccountCode 身份号,RealName 姓名 ,CardMoney 余额 
from AccountInfo inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
where CardMoney > @blance

-- 2.从所有账户信息中查询出余额最高的交易明细（存取钱明细）
-- 不推荐
select * from CardExchange  inner join
BankCard on CardExchange.CardNo = BankCard.CardNo where  CardMoney = 
(select max(CardMoney) from BankCard )


-- 如果有多个人只能查一个
select * from CardExchange where CardNo =
(select top 1 CardNo from BankCard order by CardMoney desc)


--允许多个人余额最高
select * from CardExchange where CardNo in
(select CardNo from BankCard  where CardMoney=
(select max(CardMoney) from BankCard))

--3.查询出有取款记录的用户的银行卡信息、显示卡号、身份证，姓名、余额
select CardNo 卡号,AccountCode 身份号,RealName 姓名 ,CardMoney 余额 
from AccountInfo inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
where CardNo in
(select  CardNo from CardExchange where MoneyOutBank >0)


-- 4.查询出没有存款记录的用户的银行卡信息、显示卡号、身份证，姓名、余额
select CardNo 卡号,AccountCode 身份号,RealName 姓名 ,CardMoney 余额 
from AccountInfo inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
where CardNo not in
(select  CardNo from CardExchange where MoneyInBank >0)
select * from AccountInfo
select * from BankCard
select * from CardExchange

-- 5.查看关羽卡号：6225125478541263 查看当天是否有转账记录
if exists(select * from CardTransfer where CardNoIn = '6225125478541263'
and CONVERT(varchar(22),getdate(),23) = CONVERT(varchar(22),TransferTime,23))
	begin
		print'存在转账记录'
	end
else
	begin
		print'不存在转账记录'
	end

-- 6.查询出交易次数（存取钱）最多的银行卡信息
--显示：卡号、身份证，姓名、余额、交易次数
select top 1  BankCard.CardNo 卡号,AccountCode 身份号,RealName 姓名 ,CardMoney 余额,Temp.myCount
 from AccountInfo 
inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
inner join 
(select CardNo, count(*) mycount from CardExchange group by CardNo) Temp
on BankCard.CardNo = Temp.CardNo
order by myCount desc

-- 优化
select  BankCard.CardNo 卡号,AccountCode 身份号,RealName 姓名 ,CardMoney 余额,Temp.myCount
 from AccountInfo 
inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
inner join 
(select CardNo, count(*) mycount from CardExchange group by CardNo) Temp
on BankCard.CardNo = Temp.CardNo
where Temp.myCount =
(select max(Temp.myCount) from  
(select CardNo, count(*) mycount from CardExchange group by CardNo )Temp)


-- 7.查询出没有交易记录的银行卡信息
-- 优化
select  BankCard.CardNo 卡号,AccountCode 身份号,RealName 姓名 ,CardMoney 余额
from AccountInfo 
inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
where CardNo not in(select CardNoOut from CardTransfer)
and CardNo not in(select CardNoIn from CardTransfer)

select * from CardTransfer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

7.3.3 分页

create table Student(
	StuId int primary key identity(1,2),
	StuName varchar(20),
	StuSex varchar(4)
)
go
insert into Student(StuName,StuSex) values('刘备','男')
insert into Student(StuName,StuSex) values('诸葛亮','男')
insert into Student(StuName,StuSex) values('司马懿','男')
insert into Student(StuName,StuSex) values('高渐离','男')
insert into Student(StuName,StuSex) values('上官婉儿','女')
insert into Student(StuName,StuSex) values('貂蝉','女')
insert into Student(StuName,StuSex) values('司空震','男')
insert into Student(StuName,StuSex) values('扁鹊','男')
insert into Student(StuName,StuSex) values('小乔','女')
insert into Student(StuName,StuSex) values('杨玉环','女')
insert into Student(StuName,StuSex) values('武则天','女')
insert into Student(StuName,StuSex) values('赵云','男')
insert into Student(StuName,StuSex) values('橘右京','男')
insert into Student(StuName,StuSex) values('老夫子','男')
insert into Student(StuName,StuSex) values('盘古','男')
insert into Student(StuName,StuSex) values('安琪拉','女')
insert into Student(StuName,StuSex) values('妲己','女')
insert into Student(StuName,StuSex) values('瑶','女')
insert into Student(StuName,StuSex) values('东皇太一','男')
select * from Student

-- 分页
-- 假设每页5条 
-- 查询第一页
select top 5 *  from Student

-- 第二页
select top 5 *  from Student where StuId not in(select top 5 StuId from Student)

-- 第三页
select top 5 *  from Student where StuId not in(select top 10 StuId from Student)

-- 分页方案一：top分页
-- select top （页码大小）  * from Student 
-- where StuId not in(select top （页码大小*(当前页-1)） StuId from Student)
declare @pagesize int = 5
declare @pageindex int  =1
select top (@pagesize)  * from Student 
where StuId not in(select top (@pagesize*(@pageindex-1)) StuId from Student)


-- 方案二:row_number分页 
-- select * from 
-- (select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
-- where RowId between (当前页-1) * 页码大小 +1 and 当前页 * 页面大小

declare @pageSize int = 5
declare @pageIndex int  =1
select * from 
(select ROW_NUMBER() over(order by StuId) RowId, * from Student) Temp
where RowId between (@pageIndex-1) * @pageSize +1 and @pageIndex * @pageSize

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

8.事务、索引、视图、卡尺

8.1.1事务的概念和运用

事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，SQLServer能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。

事务通常是以BEGINTRANSACTION开始，以COMMIT或ROLLBACK结束。

select * from BankCard
alter table BankCard add constraint ck_money check(CardMoney >=0)
-- 假设刘备要取款 9.9元 银行卡号为：6225125478544587
begin transaction
declare @myerr int =0
update BankCard set CardMoney = CardMoney -9.9 where CardNo = '6225125478544587'
set @myerr = @myerr + @@ERROR
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',0,9.9,GETDATE())
set @myerr = @myerr + @@ERROR
if @myerr = 0 --正常
	begin
		commit transaction
		print '取款成功'
	end
else		 -- 失败
	begin
		rollback transaction
		print'取款失败'
	end
select * from BankCard
select * from CardExchange
select * from CardTransfer
select * from AccountInfo


-- 刘备向翠花转账1500  刘备：6225125478544587 翠花：6225125478556777
begin transaction
declare @err int = 0
update BankCard set CardMoney = CardMoney -15 where CardNo = '6225125478544587'
set @err = @err + @@ERROR
update BankCard set CardMoney = CardMoney +15 where CardNo = '6225125478556777'
set @err = @err + @@ERROR
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225125478556777',15,GETDATE())
set @err = @err + @@ERROR
if @err =0
	begin 
		commit transaction
		print'转账成功'
	end
else
	begin
		rollback transaction
		print '转账失败'
	end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

8.8.2 索引的概念和运用

1.概念

索引：提高查询效率

索引类型：按存储结构分

1. 聚集索引(聚类索引、簇集索引)：根据数据行的键值在表中或视图中的排序存储这些数据行，每个表只有一个聚集索引。聚集索引是一种对磁盘上实际数据重新组织以按指定的一行或多列值排序（类似字典中的拼音索引）（物理存储顺序）
2. 非聚集索引(非聚类索引、非簇集索引):具有独立数据行的结构，包含非聚集索引键值，且每个键值都有指向包含该键值的数据行的指针。（类似字典中的偏旁部首）（逻辑存储顺序）

其他类型：按数据唯一性区分（1,2） 按键列个数区分（3,4）

			唯一索引   非唯一索引   单列索引   多列索引
1

2.运用

-- 创建索引方式
1.通过显示的create index 命令
2.在创建约束时作为隐含的对象
	1.主键约束（聚集索引）
	2.唯一约束（唯一索引）
1
2
3
4
5

-- 创建索引语法
create [unique] [clustered] [noclustered]
index <index name> on <table or view name> (<column name)[asc|desc][,,,n])
1
2
3

8.8.3 视图

视图:可以理解为虚拟表，然后可以被调用

-- 查询用户卡号、身份证、姓名、余额
select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 from AccountInfo inner join 
BankCard on AccountInfo.AccountId = BankCard.AccountId
go
-- 假设以后经常要查询显示这个
-- 可以创建一个视图
create view view_account 
as
select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 from AccountInfo inner join 
BankCard on AccountInfo.AccountId = BankCard.AccountId
go


-- 使用视图
select * from view_account

--删除视图
drop view  view_account
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

8.4.4 游标

游标：定位到结果集中的某一行

游标分类：

1. 静态游标（static）：在操作游标时，数据发生改变，游标中的数据不变
2. 动态游标（Dynamic）：在操作游标时，数据发生改变，游标也改变
3. 键集驱动游标（keySet）：在操作游标时，被标识的列发生改变，游标中的数据发生改变，其他数据不变

select * from Student
--创建游标（scroll滚动游标，可以前进后退）
declare mycur cursor scroll
for select StuName from Student


--游标打开
open mycur

--提取某一行数据
fetch first from mycur --第一行
fetch last from mycur --最后一行
fetch absolute 2 from mycur --提取第二行
fetch relative 2 from mycur --当前行下移2行
fetch next from mycur -- 下移一行
fetch prior from mycur --上移一行


-- 提取游标数据存入变量，查询所有
declare @acc varchar(10)
fetch absolute 2 from mycur into @acc
select * from Student where StuName = @acc


--遍历游标
declare @num varchar(20)
fetch absolute 1 from mycur into @num
-- @@fetch_status  0：提取成功 -1失败 -2不存在
while @@FETCH_STATUS =0
	begin
		print'提取成功：'+@num
		fetch next from mycur into @num
	end



--利用游标修改数据和删除
fetch absolute 2 from mycur 
update Student set StuName='吕布' where current of mycur
select * from Student

--删除数据
delete from Student where current of mycur
--关闭游标
close mycur
-- 删除游标
deallocate mycur


-- 创建一个多列的游标
declare cur cursor  scroll 
for select StuId,StuName,StuSex from Student
open cur
declare @number1 varchar(20)
declare @number2 varchar(20)
declare @number3 varchar(20)
fetch absolute 1 from cur into @number1,@number2,@number3
	while @@FETCH_STATUS =0
		begin
			print'序号：'+@number1+'姓名：'+@number2+'性别：'+@number3
		    fetch next from cur into @number1,@number2,@number3
		end

close cur
deallocate cur
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

9.函数

函数：①系统函数 ②自定义函数（标量值函数（返回单个值）、表值函数（返回查询结果））

create funciton 函数名(参数) returns 数据类型
as
	being
	函数体
	return ---
	end
-- 调用
select dbo.函数名()
1
2
3
4
5
6
7
8

--编写一个函数求银行总金额
create function GetSum() returns money
as
	begin
		declare @sum money
		select @sum =(select sum(CardMoney) from BankCard)
		return @sum
	end

-- 函数调用
select dbo.GetSum()总金额

go
-- 传入账户身份证号返回账户姓名
create function GetRealName(@code varchar(20)) returns varchar(30)
as
begin
	declare @name varchar(30)
	select @name = (select RealName  from AccountInfo where AccountCode = @code)
	return @name
end
--调用
select * from AccountInfo
select * from CardExchange
select dbo.GetRealName('110321200008199268')
go

-- 传递开始时间和结束时间，返回交易记录（存取钱）
--交易记录中包含真实姓名、卡号、存钱金额、取钱金额、交易时间
-- 方案一（可以处理复杂逻辑）
create function GetExchangTime(@start smalldatetime,@end smalldatetime)
returns @result table
(
	RealName varchar(20),
	CardNo varchar(30),
	MonyInBank money,
	MoneyOutBank money,
	ExchangeTime datetime
)
as
begin
	insert into @result
	select RealName 姓名, CardExchange.CardNo 卡号, MoneyInBank 存钱存钱,
	MoneyOutBank 取钱金额,ExchangeTime 交易时间 from CardExchange 
	inner join BankCard on BankCard.CardNo = CardExchange.CardNo
	inner join AccountInfo on AccountInfo.AccountId = BankCard.AccountId
	where ExchangeTime between @start+ '00:00:00' and @end+ '23:59:59'
	return
end
go
-- 调用
select * from GetExchangTime('2022-1-1','2022-11-26')



drop function GetExchangTime

-- 方案二（函数体内只能return+sql）
create function GetExchangTime(@start smalldatetime,@end smalldatetime)
returns table
as
	return
	select RealName 姓名, CardExchange.CardNo 卡号, MoneyInBank 存钱存钱,
	MoneyOutBank 取钱金额,ExchangeTime 交易时间 from CardExchange 
	inner join BankCard on BankCard.CardNo = CardExchange.CardNo
	inner join AccountInfo on AccountInfo.AccountId = BankCard.AccountId
	where ExchangeTime between @start+ '00:00:00' and @end+ '23:59:59'
go
-- 调用
select * from GetExchangTime('2022-1-1','2022-11-26')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

-- 查询银行卡信息，将银行卡状态1,2,3,4分别转换为正常、挂失、冻结、注销
--并根据余额分类显示用户等级
-- 30以下普通用户 30及以上VIP用户
-- 显示列分别卡号、身份证、姓名、余额、用户等级、银行卡状态、

select CardNo 卡号,AccountCode 身份证号,CardMoney 余额,
case
	when CardMoney >=30000 then 'VIP用户'
	else '普通用户'
end 用户等级,
case CardState 
	when 1 then '正常'
	when 2 then '挂失'
	when 3 then '冻结'
	when 4 then '注销'
	else '异常'
end 银行卡状态
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
go
-- 函数
create function GetRank(@money money) returns varchar(30) --等级方法
as
begin
	declare @str varchar(30)
	if @money >=300000
		set @str = 'VIP用户'
	else
		set @str ='普通用户'
	return @str
end
go

create function GetStatus(@num int) returns varchar(30) --状态方法
as
begin
	declare @str varchar(30)
	if @num =1
		set @str = '正常'
	else if @num =2
		set @str ='挂失'
	else if @num =3
		set @str ='冻结'
	else if @num =4
		set @str ='注销'
	else
		set @num ='错误'
	return @str
end
go

-- 查询银行卡信息，将银行卡状态1,2,3,4分别转换为正常、挂失、冻结、注销
--并根据余额分类显示用户等级
-- 30以下普通用户 30及以上VIP用户
-- 显示列分别卡号、身份证、姓名、余额、用户等级、银行卡状态、

select CardNo 卡号,AccountCode 身份证号,CardMoney 余额,
dbo.GetRank(CardMoney)用户等级,dbo.GetStatus(CardState)银行卡状态
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
go
-- 求实岁 
---例如：2000-1-8生 现在2022-1-7 实岁21  1-8 22
create function GetAge(@birth smalldatetime) returns int
as
begin
	declare @age int
	set @age = YEAR(getdate())-YEAR(@birth)
	if (MONTH(getdate())< MONTH(@birth))
		set @age =@age -1
	if (MONTH(getdate())= MONTH(@birth)) and DAY(getdate()) > DAY(@birth)
	set @age = @age-1
	return @age
end
go
select dbo.GetAge(OpenTime)  年龄 from AccountInfo
select year(GETDATE()) -year(Opentime) from AccountInfo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

10.触发器

触发器作用：对某一张表进行了操作，同时也会对另一张表进行操作

触发器分类：“instead of”触发器 “After”触发器

1. “instead of”触发器“：在执行操作之前被执行
2. After触发器：在执行操作之后被执行

-- 部门表
create table Department(
	DepartmentId varchar(10) primary key not null,
	DepartmentName varchar(20) not null,
)
-- 员工表
create table People(
	PeopleId int primary key identity(1,1) not null,
	DepartmentId varchar(10) not null,
	PeopleName varchar(20) not null,
	PeopleGender char(2) not null,
	PeoplePhone varchar(11) not null,

)
go
insert into Department(DepartmentId,DepartmentName)
values('001','总经办')
insert into Department(DepartmentId,DepartmentName)
values('002','市场部')
insert into Department(DepartmentId,DepartmentName)
values('003','策划部')
insert into Department(DepartmentId,DepartmentName)
values('004','研发部')
insert into Department(DepartmentId,DepartmentName)
values('005','人事部')

insert into People(DepartmentId,PeopleName,PeopleGender,PeoplePhone)
values('001','婉儿','女','19890908896')
insert into People(DepartmentId,PeopleName,PeopleGender,PeoplePhone)
values('001','杨玉环','女','19807976698')
insert into People(DepartmentId,PeopleName,PeopleGender,PeoplePhone)
values('002','张飞','男','19100661568')
insert into People(DepartmentId,PeopleName,PeopleGender,PeoplePhone)
values('003','吕布','男','19990909901')
insert into People(DepartmentId,PeopleName,PeopleGender,PeoplePhone)
values('004','鲁班七号','男','18707976489')
select * from People
select * from Department
go
-- 添加员工时，如果该员工加的部门存在，则直接加入
-- 不存在时，新建一个“新部门”
create trigger tri_insert on People after insert
as
	if not exists(select * from Department where DepartmentId =(select DepartmentId from inserted))--inserted临时表
	begin
		insert into Department(DepartmentId,DepartmentName)
		values((select DepartmentId from inserted),'新部门')
	end
go
-- 测试触发器

insert into People(DepartmentId,PeopleName,PeopleGender,PeoplePhone)
values('002','老夫子','男','19100661500')

insert into People(DepartmentId,PeopleName,PeopleGender,PeoplePhone)
values('006','高渐离','男','19188661512')
go

--（2）触发器实现，删除一个部门的时候将该部门下所有员工删除
create trigger tri_delete on Department after delete
as
	delete from People where DepartmentId = (select DepartmentId from deleted)
go

-- 测试触发器
select * from People
select * from Department
delete from Department where DepartmentId ='006'

go
--（3）触发器实现，删除一个部门的时判断是否有员工，有不删除，没有则删除
create trigger tri_deleteCheck on Department instead of delete
as
	if not exists(select * from People where DepartmentId =(select DepartmentId from deleted) )
	delete from Department where DepartmentId = (select DepartmentId from deleted)
go

--测试触发器
select * from People
select * from Department
delete from Department where DepartmentId='001'

delete from Department where DepartmentId='005'

go
--4.修改部门编号时，员工表中部门编号同时更新
create trigger tri_update on Department after update
as
	update People set DepartmentId = (select DepartmentId from inserted)
	where DepartmentId = (select DepartmentId from deleted)
go

-- 测试触发器
select * from People
select * from Department
update Department set DepartmentId = '006' where DepartmentId = '002'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

11.存储过程

存储过程：是sql语句和流程控制语句的预编译的集合

-- 没有输入参数和没有输出参数的存储过程
-- 定义存储过程查询出账户余额最低的银行卡账户信息、显示银行卡号、姓名、账户余额
drop proc proc_MinMoney
go
create proc proc_MinMoney
as
select top 1  CardNo 卡号,RealName 姓名,CardMoney 余额 from BankCard inner join AccountInfo on
BankCard.AccountId = AccountInfo.AccountId order by CardMoney 
go

-- 使用存储过程
exec proc_MinMoney
go
-- 方案二（多个人余相同，多人展示）
drop proc proc_MinMoneyShow
go
create proc proc_MinMoneyShow
as
select  CardNo 卡号,RealName 姓名,CardMoney 余额 from BankCard inner join AccountInfo on
BankCard.AccountId = AccountInfo.AccountId where CardMoney=(select min(CardMoney) from BankCard)
go

select * from BankCard
exec proc_MinMoneyShow
go

--（2）有输入参数，没有输出参数的存储过程
-- 模拟银行卡存钱操作，传入银行卡，存钱，实现存钱操作，并写入交易记录
create proc proc_saveMoney 
@cardCode varchar(20),
@money money
as
update BankCard set CardMoney = CardMoney + @money where CardNo = @cardCode
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values(@cardCode,@money,0,getdate())
go

--测试
select * from BankCard
select * from CardExchange
go
exec proc_saveMoney '6225125478556789',99999 

go

--（3）有输入参数，没有输出参数的存储过程，但是有返回值得存储过程（返回值必须是整数）
--
-- 模拟银行卡取钱操作，传入银行卡，取钱，实现取钱操作，并写入交易记录
-- 取钱成功返回1 ，取钱失败返回-1
create proc proc_outMoney 
@cardCode varchar(20),
@money money
as
	update BankCard set CardMoney = CardMoney - @money where CardNo = @cardCode
	if @@ERROR <> 0
		return -1 -- 遇到return后面不会执行
	insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
	values(@cardCode,0,@money,getdate())
	return 1
go

declare @num int 
exec @num = proc_outMoney '6225125478556789',1111
select @num 返回值


go
--4.有输入参数，有输出参数的存储过程
-- 查询出某时间段的银行存取款信息及存款总金额，取款总金额
--传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额、取款总金额
drop  proc proc_selectChange
go
create proc proc_selectChange
	@start smalldatetime,
	@end smalldatetime,
	@sumIn money output, --存款总金额
	@sumOut money output
as
	select @sumIn = (select sum(MoneyInBank) from CardExchange where ExchangeTime 
	between @start+ '00:00:00' and @end+ '23:59:59')
	select @sumIn = (select sum(MoneyOutBank) from CardExchange where ExchangeTime 
	between @start+ '00:00:00' and @end+ '23:59:59')
	select * from CardExchange where ExchangeTime between @start+ '00:00:00' and @end+'23:59:59'
go
declare @sumIn money
declare @sumOut money
exec proc_selectChange'2022-6-10','2022-11-30',@sumIn output,@sumOut output
select @sumIn
select @sumOut
go






-- 同时具备输入输出参数
--传入用户名和密码，如果卡号密码正确 并且密码长度小于8，自动升级为8位
select FLOOR(rand()*10)
go
create proc proc_updatePwd 
	@code varchar(20),  --卡号
	@pwd varchar(20) output -- 密码,不是单纯的输出
as
	if not exists(select * from BankCard where CardNo = @code and Cardpwd = @pwd)
	set @pwd = ''
	else
		begin
			if len(@pwd) <8
			begin
				declare @len int = 8 - len(@pwd)
				declare @i int =1
				while @i <= @len
					begin
						set @pwd = @pwd + cast(FLOOR(rand()*10) as varchar(1))
						set @i = @i+1
					end
					update BankCard set Cardpwd = @pwd where CardNo = @code
			end
		end
go



go

declare @pwd varchar(20) ='123456' -- 如果单纯写到存储过程只是单纯地输入，没有输出
exec proc_updatePwd'6225125478556777',@pwd output
select @pwd
select * from BankCard
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130