你苦苦寻找的SQL_server总结来啦

coding_ksy

已于 2023-07-17 12:48:49 修改

阅读量855

点赞数 1

分类专栏： # SQL server 文章标签： sql 数据库 mysql

于 2023-05-15 12:43:59 首次发布

本文链接：https://blog.csdn.net/qq_60498436/article/details/130681757

版权

SQL server 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、数据库的创建

--数据库的创建
create database summary;
use summary;
--删除数据库
--use master;
--drop database summary;

二、表的创建

2.1 表的创建

--表的创建
create table Student
(
	stu_id varchar(12) primary key,--学号
	stu_name varchar(8),--姓名
	stu_class varchar(12),--班级
	stu_major varchar(20),--专业
	stu_department varchar(30)--院系
)

2.2 数据类型

--数据类型

字符类型：char 1位 nchar（n）固定n位 varchar(n) 可变n位 nvarchar(n) n位可变位

日期类型：date

数值型：decimal(n,m)、real、float、double、numeric(n,m)

tinyint(2byte) smallint(4byte) int(8byte) bigint(16byte)

文本类型：text,money,image,binary

2.3 表的操作

--表的操作
--对表的特征所进行的一系列的操作
--增删改查（add、drop、alter、select）
--对表的列操作的第一个单词为alter
--增
alter table Student add stu_age int;
select * from Student;
--删
alter table Student drop column stu_age;
select * from Student;
--改
alter table Student alter column stu_class varchar(20);
--查
--查询表中的单独的列
select stu_id,stu_class from Student;
--查询表中的全部的列
select * from Student;

--对表中的记录进行的操作
--增（insert）的
insert into Student (stu_id,stu_name,stu_class,stu_major,stu_department)
values(312120030416,'张三','软件2104','软件工程','软件学院');
select * from Student;
--当字段包含全部的列，可以省略
insert into Student values(312120030411,'李四','软件2104','软件工程','软件学院');
select * from Student;
--删
--删除所有的记录
--delete from Student 
--删除部分数据
delete from Student where stu_name = '孔绅宇';
select * from Student;
--查找（select）
select stu_id from Student where stu_name = '张三';
--改（update）
update Student set stu_class = '软件2105' where stu_id = 312120030416;
select * from Student;

2.4 部分数据导入到另一个表中去

--一部分的数据导入另一个表中
create table Student2
(
	S_name varchar(4),
	S_sex varchar(1)
)
insert into Student2 select S_name,S_sex from Student1 where left(S_name,1) = 'A';--部分数据插入到表中去
select * from Student2

2.5 表的高级操作

create table sf1(id int,xy varchar(8),bj varchar(20),xh varchar(24),xm varchar(16))
bulk insert sf1
from'd:2104.txt'
with(fieldterminator='\t',rowterminator='\n')
select * from sf1
select distinct * from sf1--去除重复的项
insert into sf1 select * from sf1
select distinct xm from sf1
select distinct top 5 * from sf1 order by xh asc--desc
alter table sf1 add ids int
update sf1 set ids = right(xh,4)

--like:_代表一个字符；%代表多个字符
select distinct * from sf1 where xm like'王%'
select distinct * from sf1 where ids between 1000 and 3000
select distinct * from sf1 where bj in('软件2104')

--注意区分一下几行
select distinct * from sf1 where xm =''
select distinct * from sf1 where xm is null
select distinct * from sf1 where xm is not null
insert into sf1(xm) values('')
delete from sf1 where xm =''
select * from sf1
--改变行名
select xh 序号,xy 学院,bj as 班级,id 学号,xm 姓名,ids 工资 into sf3 from sf1
select xh as 序号,xy as 学院,bj as 班级,id as 学号,xm as 姓名,ids as 工资 into sf7 from sf1
select * from sf3

--cross join 将多个表进行合并的操作
select * from sf1
select xh,xy,bj,id into sf11 from sf1
select xh,xm,ids into sf12 from sf1

select distinct a.id,a.xh,a.xy,b.xm,b.ids into sf13 from sf11 a
cross join sf12 b where a.xh =b.xh
select * from sf13

--对行进行操作
select distinct * into sf14 from sf1 where bj='软件2104' 
--select * from sf15
select distinct * into sf15 from sf1 where bj='软件2103' 
select distinct * from sf14 union select * from sf15 --union 把行并起来
select distinct * from sf14 intersect select * from sf15 --intersect行取交集
select distinct * from sf14 except select * from sf15 --except 相减 sf14-sf15

三、变量与赋值（循环、分支）语句

3.1变量

--变量

--全局变量

系统赋值，用户使用（@@）

--@@error @@version @@servicename @@rowcount

--局部变量

用户赋值并使用的@，先声明（定义，开辟空间），后使用

--declare @i int;

3.2 输出语句

--显示输出的语句
--print--直接显示
--select--以表的形式进行显示
--赋值--set
declare @i int;
set @i = 100;
print @i--直接显示
select @i;--以表的形式进行显示

3.3 判断语句

--判断语句（注意和其它的语言进行区分，在这里是不用带：号的）
--if else
if 2>3
	select 'wrong';
else
	select'right';
--case语句
/*
case when 条件 then 结果 else end
*/
declare @score int,@cls varchar(6);--一个字占两个字节
set @score = 45;
set @cls=
case
when @score>=90 then '优秀'
when @score>=80 then '良好'
when @score>=70 then '中等'
when @score>=60 then '及格'
else '不及格'
end
print @cls

3.4 循环语句

--while begin end
--计算前50项的和
declare @x int,@sum int;
set @x = 1;
set @sum = 0;
while @x <= 50
begin
	set @sum = @sum +@x;
	set @x = @x + 1
end
print @sum;
--计算前100项的乘积
declare @j int,@s bigint;
set @j = 1;
set @s = 1;
while @j<=20
begin
	set @s = @s*@j;
	set @j = @j+1;
end
print @s;
--从上面的运行结果测试中可以看出，计算在第21项开始发生溢出

例题：随机数

--创建一个表，有编号（bh）以及四门科目的成绩

--插入1万条记录，成绩是50-99的随机值

实现代码：

create table Score
(
	bh int,
	score1 int,
	score2 int,
	score3 int,
	score4 int
)
declare @bh int,@score1 int,@score2 int,@score3 int,@score4 int;
declare @k int;
set @k =1;
while @k<=10000
begin
	set @bh =@k;
	set @score1 = rand()*50 + 50;
	set @score2 = rand()*50 + 50;
	set @score3 = rand()*50 + 50;
	set @score4 = rand()*50 + 50;
	insert Score values(@bh,@score1,@score2,@score3,@score4);
	set @k = @k +1;
end
select * from Score;
--删除数据表
--drop table Score
--删除数据表中的所有记录
--delete from Score;

四、函数

4.1 字符型函数

字符和ascall码之间的转化

print ascii('A')--由字符求出相对应的数字

print char(65)--由数字求出ascall码

--一次显示'A-Z'

declare @kk int,@kk1 int;

set @kk =1;

while @kk<=26

begin

print char(@kk + 64);

set @kk = @kk +1;

end

--随机显示A - Z

print char(rand()*26+65);

--大小写转化的函数

print lower('ABCDaals');--大写转化为小写

print upper('abdhshsj');--小写转化为大写

--数字转化为字符串

print str(100);--左边含有大量的空格

print str(100,3);--加上3的目的就是去除掉多余的空格

print 'coding_ksy的工资是：'+str(100,3);

print 'coding_ksy的工资是：'+ltrim(str(100,3));

去除空格

--ltrim()去掉左边的空格

--rtrim()去掉右边的空格

--left(串,位) 取左边的子串

--right(串,位) 取右边的子串

print ltrim('    软件学院');--删除左边的空格

print rtrim('软件学院    ');--删除右边的空格

print left('软件学院',2);

print right('软件学院',2);

declare @q int

set @q=1

while @q<=9999

begin

print 'A'+right('000'+ltrim(str(@q)),4)--这中间存在吞0操作，一共取的是右边的4位

set @q+=1

end

字符串的长度

print len('hello world');

--substring(串，起始坐标，长度)

print substring('abcde',2,4);--这里的下标的起始为1

--统计数量

--count to @i where substring(name,1,1)='李'

--统计性李的人数

--replace(串1，串2，串3)将串1中的串2用串3替换掉

print replace('I am kongshenyu','I am','She is');

4.2 数学函数

--例如：sin、cos、tan、sqrt、asin、acos、ceiling、floor、pi等...
print ceiling(6.7)--向上取整
print floor(6.7)--向下取整
print round(123.456,2)--保留几位小数
print pi()--输出Π
print newid()--随机生成一串字符串

4.3 日期型函数

print getdate()--取当前的日期
--dateadd(year/week/day/month/hour/minute/second,number,date)
print dateadd(minute,8,getdate())--在当前时间的基础之上加上8分钟
--datediff(year/week/day/month/hour/minute/second,date1,date2) 日期相减 date2-date1
print datediff(year,'2022-5-8',getdate());--后面的日期减去前面的日期，返回年份的形式
print(month(getdate()))--输出当前时间的月份

例子模拟随机产生20岁到60岁的出生日期

--日期区间 1963.1.1-2003.12.31

--列出一个表格包含：姓名--出生日期--年龄

--创建一个表格包含姓名、出生日期、年龄

create table Person
(
	P_name varchar(10),
	P_age int,
	P_date date
)
declare @p int,@P_name varchar(10),@P_age int,@P_date date;
set @P_name = char(rand()*26+65);
set @p = 1;
while @p <= 1000
begin
	set @P_name += char(rand()*26+65);
	set @P_name += char(rand()*26+65);
	set @P_name += char(rand()*26+65);
	set @P_name += char(rand()*26+65);
	set @P_name += char(rand()*26+65);
	set @P_date = dateadd(day,rand()*14974,'1963-1-1');
	set @P_age = datediff(year,@P_date,getdate());
	insert Person values(@P_name,@P_age,@P_date);
	set @p += 1;
end
select * from Person

例子

--建立一个表格包含：

--姓名：第一位大写，后三位小写

--性别：'M’/'F’

--出生日期：1990-1-1到2015-12-31

--身高：150-185

--体重：（身高-100）*2

--照片名称:'d:\photo\ph'+姓名

--插入1000条数据

--1.查找今天生日的人姓名

--2.查找姓名第2位为'a'的人的信息

drop table Student1
create table Student1
(
	S_name varchar(4),
	S_sex varchar(1),
	S_date date,
	S_height int,
	S_weight int,
	S_photo varchar(20)
)
declare @St int,@days int,@S_name varchar(4),@sex varchar(1),@S_date date,@S_height int,@S_weight int,@S_photo varchar(20);
set @St = 1;
while @St <= 1000
begin
	set @S_name= char(rand()*26 + 65) +lower(char(rand()*26 + 65)) + lower(char(rand()*26 + 65)) + lower(char(rand()*26 + 65));
	if rand()>=0.5
		set @sex = 'M'
	else
		set @sex = 'F'
	set @days = datediff(day,'1990-1-1','2015-12-31');
	set @S_date = dateadd(day,rand()*@days,'1990-1-1');
	set @S_height = rand()*35 + 150;
	set @S_weight = (@S_height - 100)*2;
	set @S_photo = 'd:\photo\ph' + @S_name;
	insert Student1 values(@S_name ,@sex,@S_date,@S_height,@S_weight,@S_photo);
	set @St =@St+ 1;
end
select * from Student1;
select * from Student1 where S_name like '_a__';--查询名字的第二个字母为'a'
select * from Student1 where month(S_date) =month(getdate());--查询和现在的月份相同出生的
select * from Student1 where (month(S_date) = month(getdate())) and (day(S_date) =day(getdate()));--取出今天出生的人

4.4 聚合函数

--聚合函数
drop table sf1;
create table sf1
(
	xh int,
	xy varchar(8),
	bj varchar(12),
	id varchar(20),
	nm varchar(100)
)
bulk insert sf1
from 'D:21034.txt'
with(fieldterminator='\t',rowterminator='\n');
select * from sf1;

alter table sf1 add ids int;
update sf1 set ids = cast(right(id,5) as int)--类型转化函数cast
--最小值
declare @min int;
select @min = min(ids) from sf1;
print @min

--最大值
declare @max int;
select @max =max(ids) from sf1;
print @max

--平均值
select avg(ids) as 平均值 from sf1

--求和
select sum(ids) as 求和 from sf1 

--计数
select count(ids) as 算数 from sf1

declare @min int;
select @min = min(ids) from sf1
select nm,ids from sf1 where ids = @min

--select 字段 into 新表 from 旧表 where 条件
select xh,id,nm,ids into sf2 from sf1
alter table sf2 add mc int --增加名次一列
select * from sf2

--对表进行排序（order by）
select * from sf2 order by ids asc--升序排列
select * from sf2 order by ids desc--降序排列

--select * from sf2
select * into sf3 from sf2 order by ids desc
select * from sf3


select * into sf4 from sf2 order by ids asc
select xh,id,nm,ids,row_number() over (order by ids) as mc from sf4--重点理解，不太懂

select * from sf1
--group by
select count(*) as 班级人数 from sf1  group by bj
select max(ids) as 最高工资 from sf1 group by bj--统计各个班级的最高工资
select min(ids) as 最低工资 from sf1 group by bj--统计各个班级的最低工资
select avg(ids) as 平均工资 from sf1 group by bj--统计各个班级的平均工资

--练习
--插入1000条数据，包含每个人的基本工资，绩效工资，岗位工资
--其中：基本工资：2000-2500；绩效工资：1000-1500,岗位工资：3000-3500
--创建基本表
create table Person1
(
	nm varchar(10),--姓名
	id varchar(10),--工号
	b_salary int,--基本工资
	j_salary int,--绩效工资
	g_salary int --岗位工资
)
declare @nm varchar(10),@id varchar(10),@b_salary int,@j_salary int,@g_salary int,@ll int;
set @ll =1;
while @ll <=1000
begin
	set @nm =char(rand()*26+65)+lower(char(rand()*26+65))+lower(char(rand()*26+65))+lower(char(rand()*26+65));
	set @id = @ll;
	set @b_salary = rand()*500 + 2000;
	set @j_salary = rand()*500 + 1000;
	set @g_salary = rand()*500 + 3000;
	insert Person1 values(@nm,@id,@b_salary,@j_salary,@g_salary);
	set @ll+=1;
end
select * from Person1
select min(b_salary ) as 基本工资的最大值 from Person1

/*
select nm,id,tl,row_number() over (order by tl) as mc from sf5
select count(*),nm from sf5 group by nm
*/

4.5 自定义函数

五、索引

--索引：create uniquel index index_name2 on table3(column4 asc\desc)

--1是索引类型：unique唯一索引，clustered聚集索引 ,nonclustered

--2是索引名称，3是表名，4是字段名

--asc\desc 升序、降序

--索引后查询速度能提高N个数量级（速度加快）

use ksy
drop table AccountInfo
create table AccountInfo
(
	AccountId int,
	AccountCode varchar(20),
	AccountPhone varchar(20),
	RealName varchar(12)
)
select * from AccountInfo
insert into AccountInfo values(1,'411628200201116118','15936963758','张飞')
insert into AccountInfo values(2,'411628200201116119','15936963759','项羽')
insert into AccountInfo values(3,'411628200201116120','15936963751','刘备')
insert into AccountInfo values(4,'411628200201116121','15936963752','曹操')
select * from AccountInfo
create unique index index_Info on AccountInfo(AccountId)
select * from AccountInfo
drop index index_Info on AccountInfo--删除索引

六、视图

5.1 读入文件(导入表)

--bulk inert tb --txt.text
--from '路径+文件名'
--with(fieldterminator='',rowterminator='')

--drop table tb;
create table tb(inde int,xy varchar(10),zy varchar(12),xh varchar(20),nm varchar(100));
bulk insert tb
from 'd:2104.txt'
--可以找寻到在大文件下的文件，但目前不知道如何找寻具体路径下的文件
with(fieldterminator='\t',rowterminator='\n') --要注意分隔符对应的格式，\t是Tab对应的空格，

--导入表
drop table tb1;
create table tb1(inde int,xy varchar(10),zy varchar(12),xh varchar(20),nm varchar(100));
bulk insert tb1
from 'D:21034.txt'
with(fieldterminator='\t',rowterminator='\n')
select * from tb1;

5.2 创建视图

--创建视图
--select * from tb
--delete from tb
create view tb_view as select nm from tb --创建视图 view
select * from tb_view
select * from tb

七、游标

--游标

--declare cur_name cursor for select * from

--open cur_name

--fetch from cur_name into varible

--fetch next/first/last/absolute(绝对位置)/relative(相对位置) from ... into

--close cur_name

--deallocate cur_name

declare cur_test cursor scroll

for select id,nm from sf1;

declare @sid varchar(30),@sum1 varchar(20),@kl int;

open cur_test

fetch next from cur_test into @sid,@sum1--自动给@@FETCH_STATUS取值

--while @@FETCH_STATUS = 0

begin

set @kl = rand()*69 + 1

fetch absolute @kl from cur_test into @sid,@um1



create table Member

(

MemberId 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)

)

insert Member values('liubei',123456,'刘备','4659874564');

insert Member values('guanyu',123456,'关羽','42354234124');

insert Member values('zhangfei',123456,'张飞','41253445');

insert Member values('zhaoyun',123456,'赵云','75675676547');

insert Member values('machao',123456,'马超','532532532');

select * from Member;

--创建游标(scroll:滚动游标，没有scroll，只进行向下一行移动)

declare mycur cursor scroll

for select MemberAccount from Member

--游标的打开

open mycur

--提取某行的数据

fetch first from mycur--第一行

fetch last from mycur--z最后一行

fetch absolute 2 from mycur --提取第二行

fetch relative 2 from mycur --当前行下移2行

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 1 from mycur

update Member set MemberPwd ='654321' where  current of mycur


fetch absolute 2 from mycur

delete from Member where current of mycur

select * from Member




--关闭游标(游标还可以被再一次的打开)

close mycur


--删除游标（删除游标之后，游标就不存在的了，只能进行重新创建）

deallocate mycur

--创建指向多列的游标

declare mycur cursor scroll

for select MemberAccount,MemberPwd,MemberNickName from Member

open mycur

declare @acc1 varchar(20),@pwd varchar(20),@nickname varchar(20)

fetch absolute 1 from mycur into @acc1,@pwd,@nickname

while @@fetch_status =0

begin

print '用户名:'+@acc1 +',密码:'+@pwd+',昵称:'+@nickname

fetch next from mycur into @acc1,@pwd,@nickname

end


close mycur

deallocate mycur

--1.创建游标(Scroll代表滚动游标，不加Scroll则是只进的，只能支持fetch next)
declare CURSORMember cursor scroll 
for select MemberAccount from Member
```

**打开游标：**

```
open  CURSORMember
```

**提取数据：**

```
fetch first from CURSORMember --结果集的第一行
fetch last from CURSORMember  --最后一行
fetch absolute 1 from CURSORMember --从游标的第一行开始数，第n行。
fetch relative 3 from CURSORMember --从当前位置数，第n行。
fetch next from CURSORMember --当前位置的下一行
fetch prior from CURSORMember --当前位置的上一行
```

**提取数据给变量以供它用（取出第3行用户名，查询该用户详细信息）：**

```
declare @MemberAccount varchar(30)
fetch absolute 3 from CURSORMember into @MemberAccount
select * from Member where MemberAccount = @MemberAccount
```

**利用游标提取所有的账户信息：**

```
--方案一：
fetch absolute 1 from CURSORMember
while @@FETCH_STATUS = 0  --@@FETCH_STATUS=0,提取成功，-1提取失败，-2行不存在
	begin
		fetch next from CURSORMember
	end
	
--方案二：
declare @MemberAccount varchar(30)
--fetch next from CURSORMember into @MemberAccount
fetch absolute 1 from CURSORMember into @MemberAccount
while @@FETCH_STATUS = 0  --@@FETCH_STATUS=0,提取成功，-1提取失败，-2行不存在
	begin
		print '提取成功:' + @MemberAccount
		fetch next from CURSORMember into @MemberAccount
	end
```

**利用游标修改和删除数据：**

```
fetch absolute 3 from CURSORMember
update Member set MemberPwd = '1234567' where Current of CURSORMember

fetch absolute 3 from CURSORMember
delete Member where Current of CURSORMember
```

**关闭游标：**

```
close CURSORMember
```

**删除游标：**

```
deallocate CURSORMember
```

**创建游标指向某行多列数据,并循环显示数据：**

```
--此处如果指向所有数据，可以将for后面的语句修改成select * from Member
declare CURSORMember cursor scroll
for select MemberAccount,MemberPwd,MemberNickname,MemberPhone from Member

open CURSORMember

declare @MemberAccount varchar(30)
declare	@MemberPwd nvarchar(20)
declare	@MemberNickname nvarchar(20)
declare	@MemberPhone nvarchar(20)
fetch next from CURSORMember into @MemberAccount,@MemberPwd,@MemberNickname,@MemberPhone
while @@FETCH_STATUS = 0  --@@FETCH_STATUS=0,提取成功，-1提取失败，-2行不存在
	begin
		print '提取成功:' + @MemberAccount+','+@MemberPwd+','+@MemberNickname+','+@MemberPhone
		fetch next from CURSORMember into @MemberAccount,@MemberPwd,@MemberNickname,@MemberPhone
	end
close CURSORMember

八、事务

创建一个银行业务的数据案例

--为刘备，关羽，张飞三个人进行开户开卡的操作
--三、数据库设计案例
/*
**业务需求说明：**
模拟银行业务，设计简易版的银行数据库表结构，要求可以完成以下基本功能需求：
1.银行开户（注册个人信息）及开卡（办理银行卡）（一个人可以办理多张银行卡，但是最多只能办理3张）
2.存钱
3.查询余额
4.取钱
5.转账
6.查看交易记录
7.账户挂失
8.账户注销
**表设计：**
1.账户信息表：存储个人信息。
2.银行卡表：存储银行卡信息。
3.交易信息表（存储存钱和取钱的记录）
4.转账信息表（存储转账信息记录）
5.状态信息变化表（存储银行卡状态变化的记录，状态有1:正常,2:挂失,3:冻结,4:注销）

use master
--为刘备，关羽，张飞三个人进行开户开卡的操作
--三、数据库设计案例
/*
**业务需求说明：**
模拟银行业务，设计简易版的银行数据库表结构，要求可以完成以下基本功能需求：
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
(
	ExchangeId 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
(
	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, --交易时间
)
--状态信息变化表（存储银行卡状态变化的记录，状态有1:正常,2:挂失,3:冻结,4:注销）
create table CardStateChange
(
	StateId 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

--进行存钱操作，刘备存钱2000元，关羽存钱：8000元，张飞存钱：500000元
select * from AccountInfo
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())

--转账：刘备给张飞转账1000元
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 BankCard
--select * from AccountInfo
--刘备 420107198905064135 6225125478544587
--关羽 420107199507104133 6225547858741263
--张飞 420107199602034138 6225547854125656
--假设刘备取款6000，(添加check约束，设置账户余额必须>=0)
--要求：使用事务实现，修改余额和添加取款记录两步操作使用事务
begin transaction
declare @MyError int = 0
update BankCard set CardMoney = CardMoney-6000 where CardNo = '6225125478544587'
set @MyError = @MyError + @@ERROR
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',0,6000,GETDATE())
set @MyError = @MyError + @@ERROR
if @MyError = 0
begin
commit transaction
print '取款成功'
end
else
begin
rollback transaction
print '余额不足'
end
--假设刘备向张飞转账1000元，(添加check约束，设置账户余额必须>=0)
--分析步骤有三步（1）张飞添加1000元，（2）刘备扣除1000元，（3）生成转账记录
--假设第（1）个步骤执行成功，第（2）个步骤执行失败，则会造成银行损失。
--使用事务解决此问题
begin transaction
declare @Error int = 0
update BankCard set CardMoney = CardMoney -1000 where CardNo = '6225125478544587'
set @Error = @@ERROR + @Error
update BankCard set CardMoney = CardMoney + 1000 where CardNo = '6225547854125656'
set @Error = @@ERROR + @Error
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225547854125656',1000,GETDATE())
set @Error = @@ERROR + @Error
if @Error = 0
begin
commit
print '转账成功'
end
else
begin
rollback
print '转账失败'
end

九、触发器

--触发器分类：（1） “Instead of”触发器（2）“After”触发器
--（1） “Instead of”触发器：在执行操作之前被执行
--（2）“After”触发器：在执行操作之后被执行
--部门
use master
create table Department1
(
	DepartmentId varchar(10) primary key,--部门编号
	DepartmentName nvarchar(50)--部门名称
)
--人员信息
create table People1
(
	PeopleId int primary key identity(1,1),--主键自动增长
	DepartmentId varchar(10),--部门编号，外键，与部门表进行相连
	PeopleName nvarchar(20),--人员姓名
	PeopleSex nvarchar(2),--人员性别
	PeoplePhone nvarchar(20)--电话，联系方式
)
insert into Department1(DepartmentId,DepartmentName) values('001','总经班')
insert into Department1(DepartmentId,DepartmentName) values('002','市场部')
insert into Department1(DepartmentId,DepartmentName) values('003','人事部')
insert into Department1(DepartmentId,DepartmentName) values('004','财务部')
insert into Department1(DepartmentId,DepartmentName) values('005','软件部')
insert into People1(DepartmentId,PeopleName,PeopleSex,PeoplePhone) values('001','刘备','男','13558785478')
insert into People1(DepartmentId,PeopleName,PeopleSex,PeoplePhone) values('001','关羽','男','13558785475')
insert into People1(DepartmentId,PeopleName,PeopleSex,PeoplePhone) values('002','张飞','男','13558785479')
select * from Department1
select * from People1
--exp:添加员工的时候，如果部门编号找不到，则自动添加部门信息，部门名称为"新部门"
create trigger tri_Peopleid_insert on People1
after insert
as
	if not exists(select * from Department1 where DepartmentId =(select DepartmentId from inserted))
		insert into Department1(DepartmentId,DepartmentName) values((select DepartmentId from inserted),'新部门')
go
insert into People1(DepartmentId,PeopleName,PeopleSex,PeoplePhone) values('009','赵云','男','15936963758')
select * from Department1
select * from People1


----exp:触发器实现，删除一个部门的时候将部门下所有员工全部删除
create trigger tri_Department_delete on Department1
after delete
as
	delete from People where DepartmentId = (select DepartmentId from deleted)
go
delete from Department1 where DepartmentName ='新部门'
select * from Department1
select * from People1


--exp:创建一个触发器，删除一个部门的时候判断该部门下是否有员工，有则不删除，没有则删除
drop trigger tri_Department1_delete--删出触发器
create trigger tri_Department1_delete on Department1
Instead of delete
as
	if not exists(select * from People1 where DepartmentId = (select DepartmentId from deleted))
		delete from Department1 where DepartmentId =(select DepartmentId from deleted)
go
insert into Department1 values('006','新部门')
select * from Department1
select * from People1
delete from Department1 where DepartmentId = '001'
delete from Department1 where DepartmentId = '006'

十、存储过程

存储过程（Procedure）是SQL语句和流程控制语句的预编译集合。

（1）没有输入参数，没有输出参数的存储过程。

定义存储过程实现查询出账户余额最低的银行卡账户信息,显示银行卡号，姓名，账户余额

```

--方案一

create proc proc_MinMoneyCard

select top 1 CardNo 银行卡号,RealName 姓名,CardMoney 余额

from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId

order by CardMoney asc

--方案二：（余额最低，有多个人则显示结果是多个）

create proc proc_MinMoneyCard

select CardNo 银行卡号,RealName 姓名,CardMoney 余额

from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId

where CardMoney=(select MIN(CardMoney) from BankCard)

```

执行存储过程：

```

exec proc_MinMoneyCard

```

（2）有输入参数，没有输出参数的存储过程

模拟银行卡存钱操作，传入银行卡号，存钱金额，实现存钱操作

```

create proc proc_CunQian

@CardNo varchar(30),

@MoneyInBank money

update BankCard set CardMoney = CardMoney + @MoneyInBank where CardNo = @CardNo

insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)

values(@CardNo,@MoneyInBank,0,GETDATE())

--go

```

执行存储过程：

```

exec proc_CunQian '6225125478544587',3000

```

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

模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1

```

create proc proc_QuQian

@CardNo varchar(30),

@MoneyOutBank money

update BankCard set CardMoney = CardMoney - @MoneyOutBank where CardNo = @CardNo

if @@ERROR <> 0

return -1

insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)

values(@CardNo,0,@MoneyOutBank,GETDATE())

return 1

```

执行存储过程：

```

declare @returnValue int

exec @returnValue = proc_QuQian '662018092100000002',1000000

print @returnValue

```

（4）有输入参数，有输出参数的存储过程

查询出某时间段的银行存取款信息以及存款总金额，取款总金额，传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额。

```

create proc proc_SelectExchange

@startTime varchar(20), --开始时间

@endTime varchar(20), --结束时间

@SumIn money output, --存款总金额

@SumOut money output --取款总金额

select @SumIn = (select SUM(MoneyInBank) from CardExchange

where ExchangeTime between @startTime+' 00:00:00' and @endTime+' 23:59:59')

select @SumOut = (select SUM(MoneyOutBank) from CardExchange

where ExchangeTime between @startTime+' 00:00:00' and @endTime+' 23:59:59')

select * from CardExchange

where ExchangeTime between @startTime+' 00:00:00' and @endTime+' 23:59:59'

```

执行存储过程：

```

declare @SumIn money --存款总金额

declare @SumOut money --取款总金额

exec proc_SelectExchange '2018-1-1','2018-12-31',@SumIn output,@SumOut output

select @SumIn

select @SumOut

```

（5）具有同时输入输出参数的存储过程

密码升级，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码

```

--有输入输出参数（密码作为输入参数也作为输出参数）

--密码升级，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码

select FLOOR(RAND()*10) --0-9之间随机数

create proc procPwdUpgrade

@cardno nvarchar(20),

@pwd nvarchar(20) output

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+1

end

update BankCard set CardPwd = @pwd where CardNo=@cardno

end

declare @pwd nvarchar(20) = '123456'

exec procPwdUpgrade '6225547854125656',@pwd output

select @pwd

```

coding_ksy

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
你苦苦寻找的SQL_server总结来啦

本文主要是对sql_server的全面总结，有助于大家对SQL数据库的理解，基于代码实现，为后期的编写软件打下良好的基础。（爆肝3天3夜的万字长文）
复制链接

扫一扫

专栏目录

你苦苦寻找的SQL_server总结来啦

一、数据库的创建

二、表的创建

2.1 表的创建

2.2 数据类型

2.3 表的操作

2.4 部分数据导入到另一个表中去

2.5 表的高级操作

三 、变量与赋值（循环、分支）语句

3.1变量

3.2 输出语句

3.3 判断语句

3.4 循环语句

四、函数

4.1 字符型函数

4.2 数学函数

4.3 日期型函数

4.4 聚合函数

4.5 自定义函数

五、索引

六、视图

5.1 读入文件(导入表)

5.2 创建视图

七、游标

八、事务

九、触发器

十、存储过程

三、变量与赋值（循环、分支）语句