sql server笔记

最新推荐文章于 2024-07-22 15:12:37 发布

qq_46632377

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量326

点赞数 6

文章标签：数据库笔记 sql

本文链接：https://blog.csdn.net/qq_46632377/article/details/134277291

版权

前言

sql server笔记记录

一、建库，建表

1.创建数据库

第一种方式：脚本代码

if exists(select * from sys.databases where name='DBTEST')
	drop database DBTEST
create database DBTEST
on --数据文件
(
 name='DBTEST',--逻辑名称
 filename='D:\Desktop\学习笔记\DBTEST.mdf',--物理路径和名称
 size=5MB,--文件的初始大小
 filegrowth=2MB--文件增长方式 可以写大小也可以百分比

)
log on --日志文件
(
 name='DBTEST_log', --逻辑名称
 filename='D:\Desktop\学习笔记\DBTEST_log.ldf', --物理路径和名称
 size=5MB,--文件的初始大小
 filegrowth=2MB--文件增长方式 可以写大小也可以百分比
)

第二种方式：创建数据库简写

create database DBTEST1

切换数据库

use DBTEST

2.创建表-修改表结构-维护约束

2.1创建表的基本语法

create table 表名
--(
--	字段名1 数据类型,
--	字段名1 数据类型,
--)

实例：建表（部门，职级，员工）

--建表 （部门，职级，员工）
if exists(select * from sys.objects where name='Department'and type='U')
	drop table Department
create table Department
(
	--部门 primary key主键  identity(1,1)：主动增长，初始值1，正常步长1
	DepartmentId int primary key identity(1,1),
	DepartmentName nvarchar(50) not null,
	DepartmentRemark text

)
--char ：定长 char(10)无论存储数据是否10字节，他都占10字节
--varchar:varchar(10)最多占10字节
--text：长文本
--char varchar text前面加n:存储unicode字符,对中文友好
--varchar(100):存储100个字母或50个汉字
--nvarchar(100):存储100个字母或50个汉字

create table [Rank]
(
	--部门 primary key主键  identity(1,1)：主动增长，初始值1，正常步长1
	RankId int primary key identity(1,1),
	RankName nvarchar(50) not null,
	RankRemark text

)

create table People
(
	--部门 primary key主键  identity(1,1)：主动增长，初始值1，正常步长1
	PeopleId int primary key identity(1,1),
	DepartmentId int references Department(DepartmentId) not null,--引用外键：引用其他表的主键字段
	RankId int references [Rank](RankId) not null,--引用外键：引用其他表的主键字段
	PeopleName nvarchar(50) not null,
	PeopleSex nchar(1) default('男') check(PeopleSex='男'or PeopleSex='女') not  null,
	PeopleBirth smalldatetime not null,
	PeopleSalary decimal(12,2) check(PeopleSalary>=1000 or PeopleSalary<=1000000) not  null,
	PeoplePhone varchar(20) unique not null,
	PeopleAddress varchar(300),
	PeopleAddTime smalldatetime default(getdate()) not null
)

2.2修改表结构

–添加列

alter table 表名 add 新列名 数据类型
--给员工表添加邮箱
alter table People add PeopleEmil varchar(200)

–删除列

alter table 表名 drop column 列名
--删除邮箱列
alter table People drop column PeopleEmil

–修改列

alter table 表名 alter column 列名 数据类型 
--修改地址的varchar（300）为varchar（200）
alter table People alter column PeopleAddress varchar(200)

2.2维护约束

–删除约束

alter table People drop constraint 约束名
--删除一个月薪的约束
alter table People drop constraint CK__People__PeopleSa__403A8C7D

–添加约束（check约束）

alter table People add constraint 约束名 check(表达式)
alter table People add constraint CK__People__PeopleSa1
check(PeopleSalary>=1000 or PeopleSalary<=1000000)

–添加约束（主键约束）

alter table People add constraint 约束名 primary key(列名)

–添加约束（唯一约束）

alter table People add constraint 约束名 unique(列名)

–添加约束（默认值约束）

alter table People add constraint 约束名 default 默认值 for 列名

–添加约束（外键约束）

alter table People add constrant 约束名 foreign key(列名)
references 关联表名(列名即主键)

二、增删改查sql

1.insert语句

示例：

--向部门表插入数据
insert into Department(DepartmentName,DepartmentRemark) 
values('市场部','....')
insert into Department(DepartmentName,DepartmentRemark) 
values('软件部','....')
insert into Department(DepartmentName,DepartmentRemark) 
values('企划部','....')
--简写
insert into Department values('硬件部','....')
--一次性插入
insert into Department(DepartmentName,DepartmentRemark)
select '测试部','....'union
select '实施部','....'union
select '产品部','....'

如果表名为特殊内容即关键词，使用[ ]中括号包住

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

2.update 语句

update 表名 set 字段1=值1,字段2=值2 where 条件
--工资调整 每个人加薪1000
update People set PeopleSalary=PeopleSalary+1000
--给员工编号7的加薪500
update People set PeopleSalary=PeopleSalary+500 
where PeopleId=7
--将软件部（部门编号为2）人员工资低于10000的不调整为10000
update People set PeopleSalary=10000 
where DepartmentId=2 and PeopleSalary<=10000
--修改刘备的工资为以前的2倍地址为北京
update People set PeopleSalary=PeopleSalary*2,PeopleAddress='北京'
where PeopleName='刘备'

3.delete 语句

delete from 表名 where 条件
delete from People
--删除产品部编号6 中工资大于10000的员工
delete from People where DepartmentId=6 and PeopleSalary>10000

–关于删除 drop truncate delete
drop table People 删除表对象
truncate table People 清空数据删除表和数据，重新建表表对象依然存在
delete from People 删除所有数据表对象依然存在
truncate和delete的区别
truncate清空所有数据不能有条件 delete可以删除所有数据也可以带条件删除符合条件的数据
自动编号：假设表中自动编号为1 2 3 4 5
使用truncate清除数据后再添加数据编号仍然为1 2 3 4 5
使用delete删除数据删除的自动编号将永远不存在再添加就变成 6 7 8 9

4.select 语句

4.1 查询所有列和行

select * from Department
select * from [Rank]
select * from People
--查询指定列（姓名，性别 生日 月薪 电话）
select PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone from People
--查询指定列（姓名，性别 生日 月薪 电话）（显示中文列名）
select PeopleName 姓名,PeopleSex 性别,PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话 
from People
--查询员工所在城市（去重--distinct(PeopleAddress)）
select distinct(PeopleAddress) from People 
--假设准备加工资 上调20%，查加工资前和加工资后的员工数据对比
select PeopleId,PeopleName,PeopleSalary 加薪前工资,PeopleSalary*1.2 加薪后工资 
from People

4.2 条件查询条件的符号where

示例： in关键词 in(1,2,3,4)

--查询性别为女的员工信息
select * from People where PeopleSex='女'
--查询工资大于等于10000元的员工信息
select * from People where PeopleSalary>=10000
--查询性别为女且工资大于等于10000元的员工信息
select * from People where PeopleSex='女'and PeopleSalary>=10000
--查询出出生年月在1980年之后 月薪大于一万的女员工
select * from People where PeopleBirth>='1980-1-1'and (PeopleSalary>=10000 and PeopleSex='女')
--查询工资大于等于10000元的员工信息或者工资大于等于8000元的女员工信息
select * from People where PeopleSalary>=10000 or 
(PeopleSalary>=8000 and PeopleSex='女')
--查询月薪在10000-20000员工信息（多条件）
select * from People where PeopleSalary>=10000 and  PeopleSalary<=20000 
select * from People where PeopleSalary between 10000 and 20000
--查询地址在武汉或者北京的员工信息  in关键词 in(1,2,3,4)
select * from People where PeopleAddress='北京'or PeopleAddress='武汉'
select * from People where PeopleAddress in('北京','武汉')

4.3 排序

order by 倒序 asc 正序（默认的可以不写） desc降序
示例：

--查询所有员工信息根据工资排序  倒序 asc 正序（默认的可以不写） desc降序
select * from People order by PeopleSalary desc
--查询所有员工信息根据名字的长度排序
select * from People order by len(PeopleName) desc 
--查询工资最高的五个人的信息
select top 5 * from People order by PeopleSalary desc 
--查询工资最高的10%的员工的信息
select top 10 percent * from People order by PeopleSalary desc

–null 空值
示例：

insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone)
values(5,1,'王五','男','1999-8-8',13785,13888654328)
--查询地址没有填写的员工信息
select * from People where PeopleAddress is null
--查询地址填写的员工信息
select * from People where PeopleAddress is not null

一堆sql题

--查询出80后的员工信息
select * from People where year(PeopleBirth)between 1980 and 1989
--查询30到40岁之间且工资11000--20000之间的员工信息
select * from People where year(getdate())-year(PeopleBirth)between 30 and 40
and (PeopleSalary>=11000 and PeopleSalary<=20000)

--查询星座是巨蟹座的员工信息  （6.22-7.22）
select * from People where (month(PeopleBirth)=6 and day(PeopleBirth)>=22)
or (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='小刘'
)
--查询属鼠的员工信息
select * from People where year(PeopleBirth)%12=7

--查询所有员工信息，添加一列，显示生肖
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

4.4 模糊查询

% 0个字符，1个字符或多个字符 _：匹配有且只有一个字符
–[]:代表匹配范围内 [^]:代表陪陪不在范围内
示例：

select * from Department
select * from [Rank]
select * from People
--查询姓刘的员工信息
select * from People where PeopleName like '刘%'
--查询名字中含有飞的员工信息
select * from People where PeopleName like '%飞%'
--查询名字中含有飞或者五的员工信息
select * from People where PeopleName like '%飞%'or PeopleName like '%五%'
--查询姓刘的员工信息，名字为2个字
select * from People where PeopleName like '刘_'
select * from People where substring(PeopleName,1,1)='刘'
and len(PeopleName)=2
--查询名字最后一个为二，名字共三个字的员工信息
select * from People where PeopleName like '__二'
select * from People where substring(PeopleName,len(PeopleName),1)='二'
and len(PeopleName)=3
--查询电话号码开头1388的员工信息
select * from People where PeoplePhone like '1388%'
--查询电话号码开头138的，第四位为7或8 最后一位为5
select * from People where PeoplePhone like '138[7,8]%8'
--查询电话号码开头138的，第四位为2-5 最后一位为不是2和3
select * from People where PeoplePhone like '138[2,3,4,5]%[^2,3]'
select * from People where PeoplePhone like '138[2-5]%[^2,3]'

4.5 聚合函数

count()求数量 max()求最大值 min()求最小值
sum()求和 avg()求平均值
示例：

--求员工总人数
select count(*) 员工数 from People
--求最大值，求最高工资
select max(PeopleSalary) 最高工资 from People
--求最小值，最最少工资
select min(PeopleSalary) 最少工资 from People
--求和，求所有员工的工资总和
select sum(PeopleSalary) 总工资 from People
--求平均值，求所有员工的平均工资  round(值,小数点保留数) 第三个参数默认0 四舍五入  其他数后边直接不要
select avg(PeopleSalary) 平均工资 from People
select round(avg(PeopleSalary),2) 平均工资 from People
--求数量，最大值，最小值，总和，平均值，显示在一行
select count(*) 员工数,max(PeopleSalary) 最高工资,min(PeopleSalary) 最少工资,
sum(PeopleSalary) 总工资,round(avg(PeopleSalary),2) 平均工资
from People
--查询出宜昌地区的员工人数，总工资，最高工资，最低工资和平均工资。
select  count(*) 员工数,max(PeopleSalary) 最高工资,min(PeopleSalary) 最少工资,
sum(PeopleSalary) 总工资,round(avg(PeopleSalary),2) 平均工资 
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 
--datediff(year,PeopleBirth,getdate()))函数  求差值 第一个参数为返回的为年或月或日，第二个为开始时间，第三个为终止时间
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 count(*) 数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth)) 最小年龄,
avg(year(getdate())-year(PeopleBirth))平均年龄 
from People where PeopleSalary>=10000 and PeopleSex='男'
--统计出所在地在宜昌，山西的所有女员工数量，最大年龄，最小年龄，平均年龄
select '宜昌，山西的女员工' 描述,
count(*) 数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth)) 最小年龄,
sum(year(getdate())-year(PeopleBirth))年龄总和,
avg(year(getdate())-year(PeopleBirth))平均年龄 
from People where PeopleSex='女'and PeopleAddress in('宜昌','山西')
--求出年龄比平均年龄高的人员
select * from People where datediff(year,PeopleBirth,getdate())>
(select avg(datediff(year,PeopleBirth,getdate()))from People)

4.6 分组查询

关键词 group by 使用having+条件为分组条件
示例：

--1 根据员工所在地区分组员工人数，员工工资总和，平均工资，最高工资，最低工资。
--方案一：使用union实现
select '宜昌'地区,count(*) 员工数,max(PeopleSalary) 最高工资,min(PeopleSalary) 最少工资,
sum(PeopleSalary) 总工资,round(avg(PeopleSalary),2) 平均工资
from People where PeopleAddress='宜昌'
union
select '山西'地区,count(*) 员工数,max(PeopleSalary) 最高工资,min(PeopleSalary) 最少工资,
sum(PeopleSalary) 总工资,round(avg(PeopleSalary),2) 平均工资
from People where PeopleAddress='山西'
--方案二：使用group by实现
select PeopleAddress 地区,count(*) 员工数,max(PeopleSalary) 最高工资,min(PeopleSalary) 最少工资,
sum(PeopleSalary) 总工资,round(avg(PeopleSalary),2) 平均工资
from People 
group by PeopleAddress 

--2 根据员工所在地区分组员工人数，员工工资总和，平均工资，最高工资，最低工资。
--1985年及以后出身的员工不参与统计
select PeopleAddress 地区,count(*) 员工数,max(PeopleSalary) 最高工资,min(PeopleSalary) 最少工资,
sum(PeopleSalary) 总工资,round(avg(PeopleSalary),2) 平均工资
from People where PeopleBirth<'1985-1-1'
group by PeopleAddress 
--3 根据员工所在地区分组员工人数，员工工资总和，平均工资，最高工资，最低工资。
--要求筛选出员工人数至少在2人及以上的记录，并在1985年以后的出身的员工不参与统计
select PeopleAddress 地区,count(*) 员工数,max(PeopleSalary) 最高工资,min(PeopleSalary) 最少工资,
sum(PeopleSalary) 总工资,round(avg(PeopleSalary),2) 平均工资
from People where PeopleBirth<'1985-1-1'
group by PeopleAddress
having count(*)>=2

4.7 多表查询

简单多表查询-笛卡尔连接

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

4.7.1 内连接

inner join 关键词 on为连接条件
示例：

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

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

4.7.2外连接–左外接–右外接

左外接：左表为驱动表进行数据显示，主外键关系找不到的数据null代替：右外接同理
示例：

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

4.7.3外连接–全外接

两张表的数据无论是否符合关系都要显示不符合主外键关系用null取代
示例：

select * from People full join Department
on People.DepartmentId=Department.DepartmentId

多表查询综合案例

--1 查询武汉地区所有员工信息，要求显示部门名称和员工的详细资料
select PeopleId 员工编号,DepartmentName 部门名称,PeopleName 员工姓名,
PeopleSex 员工性别,PeopleBirth 员工生日,PeopleSalary 员工月薪,PeoplePhone 员工电话,PeopleAddress 员工地址 from People
left join Department
on People.DepartmentId=Department.DepartmentId
--2 查询武汉地区所有的员工信息，要求显示部门名称，职级名称，以及员工的详细资料（显示中文别名）
select PeopleId 员工编号,RankName  职级名称,People.DepartmentId 部门编号,DepartmentName 部门名称,PeopleName 员工姓名,
PeopleSex 员工性别,PeopleBirth 员工生日,PeopleSalary 员工月薪,PeoplePhone 员工电话,PeopleAddress 员工地址 from People
left join Department
on People.DepartmentId=Department.DepartmentId
left join [Rank]
on People.RankId=Rank.RankId
where People.PeopleAddress='宜昌'
--根据部门分组中统计员工人数，员工工资总和，平均工资，最高工资，最低工资。
select DepartmentName 部门名称,count(*) 员工人数,sum(PeopleSalary) 工资总和,
avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资
from People
inner join Department
on People.DepartmentId=Department.DepartmentId
group by Department.DepartmentId,DepartmentName
--根据部门分组中统计员工人数，员工工资总和，平均工资，最高工资，最低工资。
--平均工资在8000以下的不参与统计，并根据平均工资降序排列
select DepartmentName 部门名称,count(*) 员工人数,sum(PeopleSalary) 工资总和,
avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资
from People
inner join Department
on People.DepartmentId=Department.DepartmentId
group by Department.DepartmentId,DepartmentName
having avg(PeopleSalary)>=8000
order by avg(PeopleSalary) desc

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

4.7.4自连接（自己连自己）

建表插入数据

create table Dept
(
	DeptId int primary key,
	DeptName varchar(50),
	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

通过起别名的形式自己连接自己，表中数据满足相同部门id和上级部门id一致。
–自连接的条件为儿子的父亲的id=父亲的id
–实现一下表结构效果------------
–实现部门编号，部门名称上级部门
– 3 软件研发部软件部
– 4 软件测试部软件部
–…
示例：

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

三、数据库结构设计三范式

第一范式：是对属性具有原子性，不可再分解
–例如联系方式中存入电话和qq两个内容-不是不可再分的最小单元，可分解为电话和qq两个字段
第二范式：是对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖.
–例如：在选课成绩表中出现学生信息，成绩信息，课程信息，不符合唯一性。其中三个为三个实体，可拆为3个表加入外键关系
第三范式：要求任何字段不能有其他字段派生出来，他要求字段没有冗余，即不存在传递依赖
–例如：学生表中出现学生信息和专业信息，二者为间接关系，拆分2张表。在学生表中添加专业编号，解决字段冗余情况

四、表关系（一对一，一对多，多对多）

一对多（专业表，学生表一个专业有多个学生）

一对一（学生基本信息表（学号，姓名，性别），学生详细信息表（电话，邮箱，地址）学生编号在两个表中相同做关联）

多对多（学生信息课程信息）新建一张考试成绩表，将学生和课程关联起来

五、后续学习-银行业务建表脚本

–业务需求说明-
–模拟银行业务,设计简易版的银行数据库表结构,要求可以完成以下基本功能需求
– 1.银行开户(注册个人信息)及开卡(办理银行卡) (一个人可以办理多张银行卡,但是最多只能办理3张银行卡
–2.存钱
–3.查询余额
–4.取钱
–5.转账
–6.查看交易记录
–7.账户挂失
–8.账户注销
–表设计
–1.账户信息表,
–2.银行卡表，
–3.交易信息表(存储存钱和取钱的记录)
–4.转账信息表(存储转账信息记录)
– 5.状态信息变化表(存储银行卡状态1:正常,2:挂失,3:冻结,4:注销)
–表结构设计

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, --账户编号 与信息表成主外键
CardPwd varchar(30) not null, --卡密码
CardMoney money not null, --余额
CardState int not null,--1：正常2：挂失3：冻结4：注销
CardTime smalldatetime default(getdate()) --开卡时间
)
create table CardExchange --交易信息表
(
Exchangeld int primary key identity(1,1), --交易自动编号
CardNo varchar(30) not null, --银行卡号(与银行卡表形成主外键关系)
MoneyInBank money not null, --存钱金额
MoneyOutBank money not null, --取钱金额
ExchangeTime smalldatetime not null, --交易时间
)
create table CardTransfer --转账信息表(存储转账信息记录)
(
Transferld 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:注销)
(
Stateld int primary key identity(1,1),--状态信息自动编号
CardNo varchar(30) not null, --银行卡号(与银行卡表形成主外键关系)
OldState int not null, --银行卡原始状态
NewState int not null, --银行卡新状态
StateWhy varchar(200) not null, --状态变化原因
StateTime smalldatetime not null, --记录产生时间
)

数据插入
– 为刘备，关羽,张飞三个人进行开户开卡的操作
–刘备身份证: 420107198905064135
–关羽身份证: 420107199507104133
–张飞身份证: 420107199602034138

insert into AccountInfo(AccountCode,AccountPhone, RealName,OpenTime)
values('420107198905064135','13554785425','刘备',GETDATE())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225125478544587',1,'123456',0,1)

insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('420107199507104133','13454788854','关羽',GETDATE())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225547858741263',2,'123456',0,1)
insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('420107199602034138','13456896321','张飞',GETDATE())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225547854125656',3,'123456',0,1)


select * from AccountInfo
select * from BankCard


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 = '6225547858741263'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225547858741263',8000,0,GETDATE())
update BankCard set CardMoney = CardMoney + 500000 where CardNo = '6225547854125656'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225547854125656',500000,0,GETDATE())

select * from CardExchange

update BankCard set CardMoney = CardMoney -1000 where CardNo = '6225125478544587'
update BankCard set CardMoney = CardMoney + 1000 where CardNo = '6225547854125656'

insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225547854125656',1000,GETDATE())
select * from  CardTransfer

六、使用t-sql编程

6.1 信息打印

print或者select来打印输出

print 'hello sql'
select 'hello sql'

6.2 变量 1 局部变量 2 全局变量

（1）局部变量：以@开头先声明再使用set赋值
示例：

declare @str varchar(20)
set @str='ilikesql'
--select @str='ilikesql'
print @str

set和select进行赋值的区别
set：赋值变量指定的值
select：一般用于表中查询出的数据赋值给变量如果查询结果有多条取最后一条赋值
例子：select @变量名=字段名from 表名取出当前表的最后一行的某个字段的值给@变量名
（2）全局变量：以@@开头由系统进行定义和维护直接拿来用只读
@@ERROR :返回执行的上-一个语句的错误号
@@IDENTITY ;返回最后插入的标识值
@@MAX_ CONNECTIONS :返回允许同时进行的最大用户连接数
@@ROWCOUNT :返回受上一语句影响的行数
@@SERVERNAME :返回运行SQL Server的本地服务器的名称
@@SERVICENAME :返回SQL Server正在其下运行的注册表项的名称
@@ TRANCOUNT :返回当前连接的活动事务数
@@LOCK_ TIMEOUT :返回当前会话的当前锁定超时设置(毫秒)
举例：
使用全局变量@@IDENTITY返回最后插入的标识值获取主键id

--1 为赵云此人进行开户开卡操作，赵云身份证：420107199904054233
insert into AccountInfo(AccountCode,AccountPhone, RealName,OpenTime)
values('420107199904054233','13554787453','赵云',GETDATE())
declare @AccountId int
set @AccountId=@@IDENTITY
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225125478544588',@AccountId,'123456',0,1)

select * from AccountInfo
select * from BankCard

--2 求出张飞的银行卡号和余额 张飞身份证420107199602034138
select RealName 姓名,CardNo 卡号,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
where AccountCode='420107199602034138'

select * from AccountInfo
select * from BankCard
--使用变量
declare @AccondId int
select @AccondId= (select AccountId from AccountInfo where AccountCode='420107199602034138')
select CardNo 卡号,CardMoney 余额 from BankCard where AccountId= @AccondId

6.3 go 语句

1 等待go语句之前代码执行完成后才能执行后面的代码
示例：

create database DBTEST1
go
use DBTEST1
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, --开户时间
)

2 批处理结束的一个标志
示例：

declare @num int  --@num的作用范围被go隔开 作用在上边代码块
set @num=100
go
set @num=200

6.4 运算符

运算符分为7种
算术运算符：+ - * / %
逻辑运算符：AND OR LIKE BETWEEN IN EXISTS NOT ALL ANY
赋值运算符：=
字符运算符：+
比较运算符：= > < >= <= <>:不等于
位运算符：| & ^
复合运算符：+= -= /= %= *=

6.5 Convert和cast的使用字符串拼接

Convert(数据类型,内容) 在字符串拼接时将数字转换为字符串—
cast(内容 as 数据类型) 同上的一个函数
示例：

--1 已知长方形的长和宽，求长方形的周长和面积
declare @c int =10
declare @k int =5
declare @zc int 
declare @mj int
set @zc=(@c+@k)*2
set @mj=@c*@k
--print '圆周长为'+Convert(varchar(10),@zc)
--print '圆面积为'+Convert(varchar(10),@mj)
print '圆周长为'+cast(@zc as varchar(10))
print '圆面积为'+cast(@mj as varchar(10))

6.6 练习题

--2 查询银行卡状态为冻结，并且余额超过1000000的银行卡信息
select * from BankCard where CardState=3 and CardMoney>1000000
--3 查询出银行卡冻结或余额0的银行卡信息
select * from BankCard where CardState=3 and CardMoney=0
--4 查询出姓名含刘的账户信息以及银行卡信息
select * from AccountInfo
inner join BankCard on AccountInfo.AccountId=BankCard.AccountId
where RealName like '%刘%'
--5 查询出余额在2000到5000之间的银行卡信息
select * from BankCard where CardMoney between 2000 and 5000
--查询出银行卡冻结或注销的银行卡信息
select * from BankCard where CardState in(3,4)
--关羽身份证：420107199507104133  关羽来银行开卡
--查询身份证在账户表是否存在，不存在则开户，存在则不开户不开卡
select * from AccountInfo
select * from BankCard
declare @AccoundId int
if exists(select * from AccountInfo where AccountCode='420107199507104133')
	begin
		select @AccoundId =
		(select AccountId from AccountInfo where AccountCode='420107199507104133')
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values(6225547858741264,@AccoundId,'123456',0,1)
	end
else
	begin
		insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
		values('420107199507104133','13454788854','关羽',GETDATE())
		set @AccoundId=@@IDENTITY
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values(6225547858741264,@AccoundId,'123456',0,1)
	end

--拓展：上边需求添加一个限制，即一个人最多只能开三张卡
declare @AccoundId int --账户编号
declare @CardCount int --卡的个数
if exists(select * from AccountInfo where AccountCode='420107199507104133')
	begin
		select @AccoundId =
		(select AccountId from AccountInfo where AccountCode='420107199507104133')
		select @CardCount =
		(select count(*) from BankCard where AccountId=@AccoundId)
		if @CardCount<=2
			begin
				insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
				values(6225547858741267,@AccoundId,'123456',0,1)
			end
		else
			begin
				print '您名下银行卡太多了 最多只能三张银行卡'
			end
	end
else
	begin
		insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
		values('420107199507104133','13454788854','关羽',GETDATE())
		set @AccoundId=@@IDENTITY
		insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
		values(6225547858741267,@AccoundId,'123456',0,1)
	end

--查询银行卡账户余额 是不是所有账户余额都超过了3000
--ALL和ANY使用判断时要将数字写在前面
if 3000<ALL(select CardMoney from BankCard)
	begin
		print '所有银行卡余额都超过了3000'
	end
else
	begin
		print '不是所有银行卡余额都超过了3000'
	end
--查询所有银行卡余额 是否含有账户余额超过3000000的信息
if 30000000<ANY(select CardMoney from BankCard)
	begin
		print '含有账户余额超过3000000'
	end
else
	begin
		print '不含有账户余额超过3000000'
	end

select * from BankCard
select * from CardExchange

6.7 选择分支结构 if和case的用法

示例：

-- (1 )某用户银行卡号为 "6225547854125656"
--该用户执行取钱操作,取钱5000元,余额充足则进行取钱操作,
--并提示"取钱成功" ,否则提示"余额不足”
declare @balance money
select @balance=
(select CardMoney from BankCard where CardNo='6225547854125656')
if @balance>=5000
	begin
		update BankCard set CardMoney=CardMoney-5000 
		where CardNo='6225547854125656'
		insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
		values('6225547854125656',0,5000,GETDATE())
		print '取钱成功'
	end
else
	begin
		print '余额不足'
	end
--(2)查询银行卡信息,将银行卡状态1,2,3,4分别转换为汉字“正常,挂失，冻结,注销” ，-并且根据银行卡余额显示银行卡等级
--30万以下为“普通用户”,30万及以上为"VIP用户”
--显示列分别为卡号,身份证,姓名,余额，用户等级,银行卡状态。
select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额,
case 
	when CardMoney>=300000 then 'vip用户'
	when CardMoney<300000 then '普通用户'
end 用户等级,
case CardState
	when 1 then '正常'
	when 2 then '挂失'
	when 3 then '冻结'
	when 4 then '注销'
	else '异常'
end 状态
from BankCard
inner join AccountInfo on AccountInfo.AccountId=BankCard.AccountId

6.8 选择分支结构 while的用法

示例：

--1 循环打印1-10
declare @i int=1
while @i<=10
	begin
		print @i
		set @i+=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(2))+char(9)
				set @j+=1
			end
		set @i+=1
		print @str
	end

七、子查询

标量子查询行子查询列子查询表子查询
where型 +标量子查询 or 列子查询 or 列子查询
select型 + 标量子查询
from型+表子查询
示例：

-- ( 1 )关羽的银行卡号为" 6225547858741263"
--查询出余额比关羽多的银行卡信息,显示卡号,身份证,姓名,余额。
方案一：
declare @banlace money
select @banlace=(select CardMoney from BankCard where CardNo='6225547858741263')
select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额 from AccountInfo
inner join BankCard on AccountInfo.AccountId=BankCard.AccountId
where CardMoney>@banlace
--方案二
select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额 from AccountInfo
inner join BankCard on AccountInfo.AccountId=BankCard.AccountId
where CardMoney>
(select CardMoney from BankCard where CardNo='6225547858741263')
-- 2 )从所有账户信息中查询出余额最高的交易明细(存钱取钱信息)。
--多个人余额一样高 只能查出一个
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)
--(5 )关羽的银行卡号为"6225547858741263",查询当天是否有收到转账。
update BankCard set  CardMoney=CardMoney-100 where CardNo='6225547854125656'
update BankCard set  CardMoney=CardMoney+100 where CardNo='6225547858741263'
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime) values ('6225547854125656','6225547858741263',100,GETDATE())

if exists(select * from CardTransfer where CardNoIn='6225547858741263' 
and convert(varchar(22),GETDATE(),23)=convert(varchar(22),TransferTime,23))
	begin
		print '有收到转账'
	end
else
	begin
		print '没有收到转账'
	end

--查询交易次数最多即存款和取款操作的银行卡账户信息
--显示卡号,身份证,姓名,余额，交易次数
select top 1 BankCard.CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额,Mycount 交易次数 from AccountInfo
inner join BankCard on AccountInfo.AccountId=BankCard.AccountId
inner join
(select CardNo 卡号,count(*) Mycount from CardExchange group by CardNo) temp
on temp.卡号=BankCard.CardNo

八、分页

8.1、分页示例-建表

create table Student
(
StuId int primary key identity(1,2), 
StuName varchar(20),
StuSex varchar(4)
)

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

8.2、分页语法 top方式和row_number方式进行分页

推理：

--假设每页5条数据
--查询第一页
select top 5 * from Student
where StuId not in(select top 0 StuId 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)
--规律--
--select top 5 * from Student where StuId not in(select top 页码大小*(当前页-1) StuId from Student)

方案-：
–分页方案1：top方式分页

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)

–分页方案2：使用row_number分页

--假设每页5条数据
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)

九、事务

语法：
begin transaction 开启事务
commit transaction 提交事务
rollback transaction 回滚事务-返回@error不为0 事务中sql有一条执行失败

alter table BankCard add constraint ck_money check(CardMoney>=0)

--人员信息如下: (第二列是身份证号,第三列是银行卡卡号)

--刘备420107198905064135 6225125478544587

--关羽420107199507104133 6225547858741263

--张飞420107199602034138 6225547854125656
select * from BankCard
print @@error
-- ( 1 )假设刘备取款6000 , (添加check约束,设置账户余额必须>=0),

--要求:使用事务实现,修改余额和添加取款记录两步操作使用事务
select * from CardExchange
select * from BankCard
begin transaction
declare @myerror int=0
update BankCard set CardMoney=CardMoney-100 where CardNo='6225125478544587'
set @myerror=@myerror+@@ERROR
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',0,100,GETDATE())
set @myerror=@myerror+@@ERROR
if @myerror=0
	begin
		commit transaction
		print '取款成功'
	end
else
	begin
		rollback transaction
		print '取款失败'
	end

---- ( 1 )假设刘备向张飞转账1000元 , (添加check约束,设置账户余额必须>=0),
--分析步骤-1 张飞添加1000元  2  刘备扣除1000元  3 生成转账记录
--使用事务解决该问题
begin transaction
declare @myerr int =0
update BankCard set CardMoney=CardMoney+1 where CardNo='6225547854125656'
set @myerr=@myerr+@@ERROR
update BankCard set CardMoney=CardMoney-1 where CardNo='6225125478544587'
set @myerr=@myerr+@@ERROR
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225547854125656',1,GETDATE())
if @myerr=0
	begin
		commit transaction
		print '转账成功'
	end
else
	begin
		rollback transaction
		print '转账失败'
	end

select * from BankCard
select * from CardTransfer

十、索引

索引:提高检索查询效率。
SQL SERVER索引类型:按存储结构区分:“聚集索引(又称聚类索引,簇集索引)”, “非聚集索引(非聚类索引,非簇集索引)";
聚集索引:根据数据行的键值在表或视图中的排序存储这些数据行,每个表只有一个聚集索引。
聚集索引是一种对磁盘上实际数据重新组织以按指定的一-列或多列值排序(类似字典中的拼音索引(物理存储顺序)。
非聚集索引:具有独立于数据行的结构,包含非聚集索引键值,且每个键值项都有指向包含该键值的数据行的指针。(类似字典中的偏旁部首索引) ( 逻辑存储顺序)。
SQL SERVER索引其他分类:
-按数据唯-性区分:“唯一索引” ,“非唯-索引” ;按键列个数区分:“单列索引”,“多列索引”。
-创建索引的方式:
1.通过显式的CREATE INDEX命令
2.在创建约束时作为隐含的对象
2.1.主键约束(聚集索引)
2.2.唯-约束(唯-索引）
创建索引语法:
–CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX ON

( [ASC IDESC]…n])

--例子：给AccoundInfo表的AccoundCode字段添加索引
create unique nonclustered index index_code on AccountInfo(AccountCode)
with
(
...,
...,--索引的其他设置用法 如果想学
...
)

–索引查看（sys.indexes）

select * from sys.indexes where name='index_code'

–索引基本示例语法:
–exp :创建-个非聚集索引

create nonclustered index indexAccount on AccountInfo(AccountCode)

删除一个索引 drop index 索引名 on 表名

drop index indexAccount on AccountInfo --删除一-个索引

–查询

select * from AccountInfo with(index=index_code) 
where AccountCode='420107199507104133'

十一、视图

视图可以理解为虚拟表
示例：
编写视图实现创建视图，显示卡号，身份证，姓名，余额

--编写视图实现创建视图，显示卡号，身份证，姓名，余额
create view View_Accound_Card
as
select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
go

删除视图和查询视图（和查询表一样）

drop view View_Accound_Card
select * from View_Accound_Card

十二、游标

游标：定位到结果集中的某一行
游标分类：
静态游标（static）：在操作游标时，数据发生变化，游标中数据不变
(2 )动态游标(Dynamic) :在操作游标的时候,数据发生变化,游标中数据改变,默认值。
(3)键集驱动游标(KeySet) :在操作游标的时候,被标识的列发生改变,游标中数据改变,其他列改变,游标中数据不变。

建个表用

create table Member
(
Memberld int primary key identity(1,1),
MemberAccount nvarchar(20) unique check(len(MemberAccount) between 6 and 12),
MemberPwd nvarchar(20),
MemberNickname nvarchar(20),
MemberPhone nvarchar(20)
)
go
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('liubei','123456', '刘备','4659874564')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('guanyu','123456','关羽','42354234124')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('zhangfei','123456', '张飞','41253445')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('zhangyun','123456','赵云','75675675647')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('machao','123456','马超','532523523')

select * from Member

创建游标（scroll:滚动游标没有scroll，只进）
declare 游标名 cursor （scroll） for （select MemberAccount from Member）
游标在member表的MemberAccount字段移动

declare mycur cursor scroll 
for select MemberAccount from Mem

打开游标

open mycur

提取某行的数据

fetch first from mycur --第一行
fetch last from mycur --最后一行
fetch absolute 2 from mycur --提取第二行（绝对）
fetch relative 2 from mycur --当前行下移二行（相对）
fetch next from mycur --下一行
fetch prior from mycur  --上一行

提取游标数据存入变量，进行查询所有列信息

declare @acc varchar(20)
fetch absolute 2 from mycur into @acc
select * from Member where MemberAccount =@acc

遍历游标

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

利用游标进行数据的修改和删除

select * from Member
fetch absolute 2 from mycur
update Member set MemberPwd='654321' where current of mycur

fetch absolute 2 from mycur
delete from Member where current of mycur

创建指向某行多列的游标，循环显示多列数据

declare mycur cursor scroll 
for select MemberAccount,MemberPwd,MemberNickname from Member
open mycur

遍历多列

declare @acc varchar(20)
declare @pwd varchar(20)
declare @nickname varchar(20)
fetch absolute 1 from mycur into @acc,@pwd,@nickname
--@@fetch_status:0:提取成功 -1:失败  -2：不存在
while @@fetch_status=0
	begin
		print '用户名：' +@acc+',密码：' +@pwd+',昵称：' +@nickname
		fetch next from mycur into @acc,@pwd,@nickname
	end

关闭游标

close mycur

删除游标

deallocate mycur

十三、函数

函数分 1 系统函数（聚合函数等） 2 自定义函数
自定义函数又可以分为1 标量值函数（返回单个值） 2 表值函数（返回查询结果）
自定义函数的使用
标量值函数（返回单个值）：
没有参数，返回标量值：
create function 函数名() returns 返回数据类型
as
bgein

	return 变量

end
–函数调用
select dbo.函数名()
传入参数，有返回值
create function 函数名(变量数据类型) returns 返回数据类型
as
bgein

	return 变量

end
–函数调用
select dbo.函数名(传入的内容)
示例：

--1 编写一个函数求该银行的金额总和（没有参数，返回标量值）
create function GetSumMoney() returns money
as
begin
	declare @sum money
	select @sum=(select sum(CardMoney) from BankCard)
	return @sum
end
--函数调用
select dbo.GetSumMoney()

--2 传入账户标号，返回账户真实姓名
create function GetRealNameById(@accid int) returns varchar(30)
as
begin
	declare @name varchar(30)
	select @name=(select RealName from AccountInfo where AccountId=@accid)
	return @name
end

select dbo.GetRealNameById(1)

表值函数（返回查询结果）：
方案一：（处理复杂逻辑，函数体处理sql查询之外还可以有其他逻辑代码）多声明表值函数
示例：

create function GetRecordByTime(@start varchar(30),@end varchar(30)) 
returns @result table
(
	RealName varchar(20), --真实姓名
	CardNo varchar(30), --银行卡号(与银行卡表形成主外键关系)
	MoneyInBank money , --存钱金额
	MoneyOutBank money , --取钱金额
	ExchangeTime smalldatetime --交易时间
)
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
drop function GetRecordByTime 
select * from GetRecordByTime('2020-1-1','2024-12-12')

方案2：（函数体只能有return+sql查询结果）内嵌式表值函数

create function GetRecordByTime(@start varchar(30),@end varchar(30)) 
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 GetRecordByTime('2020-1-1','2024-12-12')

将函数放在查询语句select后调用查询

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

--用户等级函数
create function GetGrade(@cardmoney money)returns varchar(30)
as
begin
	declare @result varchar(30)
	if @cardmoney>=300000
		set @result='vip用户'
	else
		set @result='普通用户'
	return @result
end

--银行卡状态函数
create function GetState(@state int)returns varchar(30)
as
begin
	declare @result varchar(30)
	if @state=1
		set @result='正常'
	else if @state=2
		set @result='挂失'
	else if @state=3
		set @result='冻结'
	else if @state=3
		set @result='注销'
	else 
		set @result='异常'
	return @result
end

select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额,
dbo.GetGrade(CardMoney) 用户等级,dbo.GetState(CardState) 银行卡状态
from BankCard
inner join AccountInfo on AccountInfo.AccountId=BankCard.AccountId

--5 编写函数 根据出生日期求年龄，年龄求实岁，例如
--生日 2000-5-5 当前2018-5-4 年龄17岁
--生日 2000-5-5 当前2018-5-6 年龄18岁
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
select *,dbo.GetAge(empBirth) 年龄 from Emp

十四、触发器

触发器分类：（1）instead of触发器（2）After触发器
nstead of触发器：在执行操作之前被执行
After触发器：在执行操作之后被执行

触发器缺点：当业务中触发器过多，易发生连锁反应
例如 A表删除触发B表删除触发C表修改

建个表：

create table Department
(
DepartmentId varchar(10) primary key, --主键，自动增长
DepartmentName nvarchar(50), --部门名称
)
create table People
(
Peopleld int primary key identity(1,1), --主键,自动增长
DepartmentId varchar(10), --部]编号,外键,与部门表关联
PeopleName nvarchar(20), --人员姓名
PeopleSex nvarchar(2), --人员性别
PeoplePhone nvarchar(20), --电话,联系方式
)
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,PeopleSex, PeoplePhone)
values('001','刘备','男','13558785478')
insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('001','关羽','男','13558788785')
insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('002','张飞','男','13698547125')

select * from Department
select * from People

触发器使用示例：
在增删改操作后触发器触发内部内容
增删操作，触发器内容中有inserted和deteled获取前或后进行的操作数据的表
select DepartmentId from inserted
select DepartmentId from deteled
改操作分解为先删除，在插入，因此触发器内容中有将改的数据分为了2个表，原来的数据在deleted表，现在的数据在inserted表中。

insert操作

--假设有部门表和员工表，在添加员工的时候，员工的部门编号如果在部门表中找不到
--则自动添加部门信息 部门信息为’新部门‘
create trigger tri_insertPeople on People after insert
as
	if not exists(select * from Department where DepartmentId=(select DepartmentId from inserted))
		begin
			insert into Department(DepartmentId,DepartmentName)
			values((select DepartmentId from inserted),'新部门')
		end
go
--测试触发器
insert into People(DepartmentId,PeopleName,PeopleSex, PeoplePhone)
values('003','赵云','男','13566666478')
insert into People(DepartmentId,PeopleName,PeopleSex, PeoplePhone)
values('006','马超','男','13567777781')

delete操作

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

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

--3 创建一个触发器 删除一个部门的时候判断该部门下是否有员工 有则不删除，没有则删除
drop trigger tir_DeleteDept
create trigger tir_DeleteDept 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
--测试触发器
--软件部没人 删除
delete from Department where DepartmentId='005'
--总经办有人  不删除
delete from Department where DepartmentId='001'

update 操作

4 修改一个部门编号之后，将部门下的所有员工的部门编号同步修改
create trigger tir_UpdateDept on Department after update
as
	update People set DepartmentId=(select DepartmentId from inserted)
	where DepartmentId=(select DepartmentId from deleted)
go

--测试触发器
update Department set DepartmentId='005'where DepartmentId='001'

十五、存储过程–业务sql应该使用事务写在存储过程中

函数与存储过程的区别：函数一般内嵌到查询语句中使用存储过程可以直接被外部程序调用的例如java c#等
存储过程是sql语句和流程控制语句的预编译集合

存储过程分为
1 没有输入参数，没有输出参数的存储过程
示例：

定义存储过程实现查询账户余额最低的银行卡账户信息，显示银行卡号，姓名，账户余额
--方案一
drop proc proc_MinMoney
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
--方案二 余额最低 多个人并列 都可以查出来
create proc proc_MinMoney
as
	select top 1 CardNo,RealName,CardMoney from BanKCard
	inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
	where CardMoney in (select min(CardMoney) from BankCard)
go
exec proc_MinMoney

2 有输入参数，没有输出参数的存储过程
示例：

模拟银行卡存钱过程，传入银行卡号，存钱金额，实现存钱金额
create proc proc_cunqian
@CardNo varchar(30),
@money money
as
	update BankCard set CardMoney=CardMoney+@money
	where CardNo=@CardNo
	insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
	values(@CardNo,@money,0,GETDATE())
go

select * from BankCard
select * from CardExchange
exec proc_cunqian '6225547858741263',1000

3 有输入参数，没有输出参数，但是有返回值的存储过程（返回值必须整数）
示例：

模拟银行卡取钱操作 传入银行卡 取钱金额 实现取钱操作
--取钱成功，返回1，取钱失败返回-1
create proc proc_quqian
@CardNo varchar(30),
@money money
as
	update BankCard set CardMoney=CardMoney-@money
	where CardNo=@CardNo
	if @@ERROR<>0
		return -1
	insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
	values(@CardNo,0,@money,GETDATE())
	return 1
go

declare @returnValue int
exec @returnValue= proc_quqian '6225125478544587',100
select @returnValue

4 有输入参数，有输出参数的存储过程
示例：

查询出某时间段的银行存款信息以及存款总金额 取款总金额
--传入开始时间，结束时间，显示存取交易信息的同时返回存款总金额，取款总金额
create proc proc_selectExChange
	@start varchar(20),
	@end varchar(20),
	@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 @sumOut=(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_selectExChange '2023-1-1','2023-12-12',@sumIn OUTPUT,@sumOut OUTPUT
select @sumIn
SELECT @sumOut

5 具有同时输入输出参数的存储过程
输入输出参数的实现取决于调用时变量是否赋值，若赋值，则输出也可以输入
floor(rand()*10 生成一个0-9的随机数

密码升级，传入用户名和密码，如果用户名和密码正确，并且长度小于8，自动升级为8位密码
select * from BankCard
select floor(rand()*10)
create proc proc_UpPwd
	@CardNo varchar(20),
	@pwd varchar(20) output
as
	if not exists(select * from BankCard where CardNo=@CardNo 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

						end
					update BankCard set CardPwd=@pwd where CardNo=@CardNo
				end
		end
go

declare @pwd varchar(20)='123456'
exec proc_UpPwd '6225125478544587',@pwd output
select @pwd