--create table student
--(
-- [sid] int identity(1,1) primary key, --学号
-- sname nvarchar(50) not null, --姓名
-- idcard nvarchar(50), --身份证号
-- nation nvarchar(50), --民族
-- birthday datetime, --生日
-- dpt_id int, --院系编号
-- award money, --奖金
--)
--create table department
--(
-- dpt_id int identity(1,1),
-- dptName nvarchar(50) not null
--)
--insert into department values('开发部')
--insert into department values('测试部')
--insert into department values('服务部')
--insert into department values('人事部')
----建立约束
--alter table department add constraint pk_dptid primary key(dpt_id)
--alter table student add constraint df_nation default '汉族' for nation --创建默认约束
--alter table student add constraint uq_card unique(idcard) --创建唯一约束
--alter table student add constraint ck_birth check(birthday>='1993-1-1' AND birthday<='2018-1-1') --检查约束
--alter table student add constraint fk_dpt foreign key(dpt_id) references department(dpt_id) --外键约束
----添加记录
--insert into student values('张三','777',default,'2000-01-01',1,100000)
--insert into student values('李四','666',default,'2000-01-01',1,10000)
----添加记录
--insert into student values('张三','11111',default,'2000-1-1',1,10000)
--insert into student values('李四','22222',default,'2002-1-1',2,10000)
--insert into student values('王五','33333',default,'2002-1-1',3,10000)
--insert into student values('赵六','44444',default,'2002-1-1',2,10000)
--insert into student values('钱七','55555',default,'2002-1-1',1,10000)
--insert into student values('张三三','31231',default,'2002-1-1',null,10000)
----删除学号为2的学生
--delete from student where [sid]='2'
----更新学号为1的学生的薪资
--update student set award='10000' where [sid]='1'
----合并查询
--select s.[sid] '学号',s.sname '姓名', s.idcard '身份证号', s.nation '民族',s.birthday '出生年月',d.dptName '部门', s.award '奖金'
--from student as s,department d
--where s.dpt_id=d.dpt_id
----查询前三条记录
--select top 3 * from student
----查询前20%的记录
--select top 20 percent * from student
----过滤重复的姓名
--select distinct sname from student
------删除学号大于1的员工
----delete from student where [sid]>1
----查询姓张的员工
--select * from student where sname like '张%'
--select * from student where sname like '张_'
----查询姓名中带'三'的员工
--select * from student where sname like '_[三]%'
--select * from student where sname like '_[三]_'
----给部门1的员工涨薪10%
--update student set award=award*1.1 where dpt_id=1
----计算1号部门的总工资
--select sum(award) '1号部门工资和' from student where dpt_id=1
----给编号1的员工涨薪10%
--update student set award=award*1.1 where [sid]=1
----获取1号部门的平均工资
--select avg(award) '1号部门平均工资' from student where dpt_id=1
----获取1号部门的最高工资
--select MAX(award) '最高工资' from student where dpt_id=1
----获取1号部门的最低工资
--select Min(award) '最低工资' from student where dpt_id=1
----查询1号部门和2号部门的员工
--select * from student where dpt_id=1 or dpt_id=2
--select * from student where dpt_id in (1,2)
----查询不是1,2部门的员工
--select * from student where dpt_id not in (1,2)
----查询01年-05年出生的员工
--select * from student where birthday between '2001-1-1' and '2005-12-31'
----查询01年以前和05以后出生的员工
--select * from student where birthday not between '2001-1-1' and '2005-12-31'
----分组查询（子查询）
----统计各部门的员工人数
--select dptName '部门', count(*) '人数'
--from(
-- select s.*,d.dptName
-- from student s, department d
-- where s.dpt_id=d.dpt_id
--) as p
--group by dptName
----统计各部门的平均工资
--select dptName '部门', avg(award) '平均工资'
--from(
----多表联合查询
-- select s.*,d.dptName
-- from student s, department d
-- where s.dpt_id=d.dpt_id
--) as p
--group by dptName
----排序（升序，降序）
--select * from student order by [sid] asc
--select * from student order by [sid] desc
----删除部门4的员工
--delete from student where dpt_id=4
----多表连接：内连接+外连接
----内连接（左右的交集）
--select s.*,d.dptName
--from student s
--inner join department d on s.dpt_id=d.dpt_id
----左外连接
--select s.*,d.dptName
--from student s
--left outer join department d on s.dpt_id=d.dpt_id
----右外连接
--select s.*,d.dptName
--from student s
--right outer join department d on s.dpt_id=d.dpt_id
----全外连接（左+右的并集）
--select s.*,d.dptName
--from student s
--full outer join department d on s.dpt_id=d.dpt_id
--create table point(
-- [sid] int,
-- [did] int,
-- fs int
--)
--go
--insert into point values('1','1','70')
--insert into point values('9','1','70')
--insert into point values('10','2','70')
--insert into point values('11','3','70')
--insert into point values('12','2','70')
--insert into point values('13','1','70')
----student
----1 张三 777 汉族 2000-01-01 00:00:00.000 1 12100.00
----9 张三 11111 汉族 2000-01-01 00:00:00.000 1 11000.00
----10 李四 22222 汉族 2002-01-01 00:00:00.000 2 10000.00
----11 王五 33333 汉族 2002-01-01 00:00:00.000 3 10000.00
----12 赵六 44444 汉族 2002-01-01 00:00:00.000 2 10000.00
----13 钱七 55555 汉族 2002-01-01 00:00:00.000 1 11000.00
----15 张三三 31231 汉族 2002-01-01 00:00:00.000 NULL 10000.00
----
----department
----1 开发部
----2 测试部
----3 服务部
----4 人事部
----
----point
----1 1 70
----9 1 70
----10 2 70
----11 3 70
----12 2 70
----13 1 70
----多表查询（内查询）
--select s.sname,d.dptName,p.fs
--from point p
--inner join student s on p.sid=s.sid
--inner join department d on d.dpt_id=p.did
----子查询：嵌套查询，可以代替多表查询（子查询效率不高）
----查询开发部门的员工信息
----多表查询
--select s.*, d.dptName
--from student s
--inner join department d on s.dpt_id = d.dpt_id
--where d.dptName='开发部'
----嵌套查询
--select s.*,d.dptName
--from student s,department d
--where s.dpt_id=(
-- select dpt_id
-- from department
-- where dptName='开发部'
--) and d.dptName='开发部'
----子查询：exists和not exists
----exists检查sqlServer中是否包含指定的数据库对象
--if(exists(select * from sysobjects where name='student'))
--drop table student
----查询获取1号部门得分的员工信息
--select *
--from student s
--where exists (
-- select *
-- from point p where p.did=1 and s.[sid]=p.[sid]
--)
----查询没有获取1号部门得分的员工信息
--select *
--from student s
--where not exists (
-- select *
-- from point p where p.did=1 and s.[sid]=p.[sid]
--)
--查询非汉族的学生
--insert into student values('张三打算','13213312','回族','2000-1-1',1,10000)
--select * from student where nation!='汉族'
----查询在01年以前出生的汉族学生
--select * from student where birthday<'2001-1-1' and nation='汉族'
----查询01年以后出生的学生
--select * from student where birthday>'2001-12-31'
----查询年龄最大的三名员工
--select top 3 * from student
--order by birthday asc
----查询所有学生中的最大年龄
--select year(GETDATE())-year(MIN(birthday)) from student
----分组的过滤查询
----以部门为单位，统计学生人数，人数达到2人
--select dpt_id,count(*)
--from student
--group by dpt_id
--having count(*) >= 2
----查询student表，department表，point表
--select * from student
--select * from department
--select * from point
--常用系统函数=字符串函数+数学函数+日期函数+其他函数
--字符串函数
--select LEN(' 312 312 ') --只算前面的空格
--select DATALENGTH(' 312 312 ') --前后都算
--select SUBSTRING('abchello',4,5) --从位置4开始，5个字符长度
--select right('abchello',5) --从右往左，5个字符长度
--select UPPER('abc') --转大写
--select LOWER('ABC') --转小写
--select REVERSE('abcd') --逆置
--select REPLACE('我有一个梦','梦','dream') --替换
--select DATALENGTH(ltrim(' abd ')) --去除左边空格
--select DATALENGTH(rtrim(' abd ')) --去除右边空格
--select DATALENGTH(LTRIM(rtrim(' abd '))) --去除两边的空格
--select CHARINDEX('world','helloworld') --查询子字符串的位置
--数学函数
--select ABS(-100) --绝对值
--select CEILING(3.14) --向上取整
--select FLOOR(3.14) --向下取整
--select PI() --PI
--select ROUND(3.14,0) --四舍五入，保留0位整数
--select RAND() --随机生成0-1的小数
--select POWER(2,3) --2的3次方
--select sqrt(9) --根号9
--select SIGN(-100) --返回参数的正负，1代表正，-1代表负
----其他函数
--select NEWID() --GUID
--select DB_NAME() --当前数据库名称
--select SUSER_NAME() --当前登录名
--select USER_NAME() --当前数据库的拥有者
----排名函数：编号从1开始，后面一次+1，中间不会跳跃数字
--select ROW_NUMBER() over(order by [sid] desc) as '自动编号', * from student
----日期函数
--select GETDATE() --获取当前时间
--select DATENAME(MM,getDate()) --获取当前的月份
--select DATEPART(YYYY,GETDATE()) --获取当前的年份
--select DATEPART(dd,getDate()) --获取当前的第几天
--select DATEPART(dy,getDate()) --获取本年的第几天
--select DATEPART(wk,getDate()) --获取本年的第几周
--select DATEPART(qq,getDate()) --获取本年的第几季度
--select DATEPART(hh,getDate()) --获取当前时间的时
--select DATEPART(mi,getDate()) --获取当前时间的分
--select DATEPART(ss,getDate()) --获取当前时间的秒
----求时间差
--select DATEDIFF(YYYY,'1997-12-28',GETDATE())
--select DATEDIFF(MM,'1997-12-28',GETDATE())
--select DATEDIFF(DD,'1997-12-28',GETDATE())
----日期加减运算
--select DATEADD(MM,1,GetDate())
--select DATEADD(MM,-1,GetDate())
----举例：查询学生并且自动计算年龄
--select s.sname,DATEDIFF(YYYY,birthday,GETDATE()) '年龄' from student s
----获取下个月过生日的学生记录
--update student set birthday='1994-9-7' where [sid]=1
--select * from student
--where DATEPART(mm,birthday)-DATEPART(mm,GETDATE())=1
----将年龄过了20岁的人年轻一岁
--update student set birthday=DATEADD(yyyy,1,birthday)
--where DATEDIFF(yyyy,birthday,getdate())>=20
--select * from student
----类型转换函数cast，convert
--select cast('123' as int)+321
--select CONVERT(int,'123')+321
----打印
--print('helloWorld'+str(123,4))
----年月日完整时间
--select CONVERT(nvarchar(50),getdate(),21) --2022-08-23 15:31:45.310
--select convert(varchar(50),getdate(),10) --08-23-22
--select convert(varchar(50),getdate(),14) --23 08 2022 15:33:16:913
----T-SQL编程
----打印语句
--print('Hello')
----变量的定义和赋值：declare @变量名 变量类型
--declare @age int
--set @age=20
--select @age=21
--print(@age)
----把1号学生的生日赋值给变量（set和select的区别）
--declare @birthday datetime
--select @birthday=birthday from student where [sid]=1
--print(convert(nvarchar(50),@birthday,21))
----不同的类型变量，同时打印，需要格式转换
--print(cast(@age as nvarchar(50))+'岁')
--变量=局部变量+全局变量（SQL编程中，系统定义好的）
--常用的全局变量
--print @@error
--print @@identity
----举例
--select * from course
--insert into course values('美术')
--select @@identity
----@@rowcount:所受到影响的行数
----举例
--delete from course where cid in(6,7)
--select @@rowcount
--go语句
--1）等待go语句之前代码执行完成之后，才能执行后面的代码
--create database dbtest
--go
--use dbtest
--create table table1()
--2）批处理结束的一个标志
--declare @num int --@num作用范围全局
--set @num=100
--set @num=200
-----------区别------------
--declare @num int --@num作用范围go之前
--set @num=100
--go
--set @num=200 --报错
--分支结构
--case
--when 条件 then 结果
--else 结果
--end
----循环结构（while）
----1）循环打印1-10
--declare @i int =1
--declare @sum int =0
--while @i<=10
--begin
-- set @sum+=@i
-- set @i+=1
--end
--print @sum
--2）循环打印九九乘法表
----特殊字符：char(9):制表符;char(10):换行符
--declare @i int =1
--declare @j int =1
--declare @str nvarchar(100) =''
--while @i<10
--begin
-- while @j<=@i
-- begin
-- set @str+= cast(@i*@j as nvarchar(10)) +'='+ cast(@i as nvarchar(10))+'*'+cast(@j as nvarchar(10))+char(9)
-- set @j+=1
-- end
-- print @str
-- set @j=1
-- set @i+=1
-- set @str = ''
--end
--简单的if if-else case-when 类似Switch while循环
--if if-else 条件判断语句
----判断奇偶性
--declare @a int
--set @a=14
--if(@a %2=0)
--begin
-- print '偶数'
--end
--else
--begin
-- print '奇数'
--end
--create table userInfo(
-- account nvarchar(50) primary key,
-- [password] nvarchar(50)
--)
--go
--insert into userInfo values('张三','123456')
--insert into userInfo values('李四','123123')
--declare @account nvarchar(50)
--declare @password nvarchar(50)
--set @account='张三'
--set @password='123456'
--declare @count int
--select @count=count(*)
--from userInfo
--where account=@account and [password]=@password
--if(@count>0)
--begin
-- print '登陆成功'
--end
--else
--begin
-- print '账号或密码错误'
--end
----表变量的定义和应用
--declare @tab table
--(
-- id int identity(1,1),
-- name nvarchar(50)
--)
--insert into @tab values('张三')
--insert into @tab values('李四')
----select sname
----from student
--select * from @tab
----分页
----假设每页5条数据
----查询第一页
--select top 5 * from student
----查询第二页数据
--select top 5 * from student
--where [sid] not in (1,2,3,4,5)
-------------分页--第二页--------------
--select top 5 * from student
--where [sid] not in (select top 5 [sid] from student)
-------------分页--第三页--------------
--select top 5 * from student
--where [sid] not in (select top 10 [sid] from student)
------------分页方案1-top方式-------------------------
--declare @PageSize int=5
--declare @PageIndex int=2
--select top (@PageSize) * from student
--where [sid] not in (select top ((@PageIndex-1)*@PageSize) [sid] from student)
------------分页方案2-row_number--------------
--declare @PageSize int=3
--declare @PageIndex int=3
--select * from (select ROW_NUMBER() over(order by [sid]) RowId, * from student) tmp
--where RowId between ((@PageIndex-1)*@PageSize)+1 and ((@PageIndex)*@PageSize)
------------事务------------------
--begin transaction--开始事务
--declare @myError int=0
--第一段SQL语句
--set @myError+=@@ERROR
--第二段SQL语句
--set @myError+=@@ERROR
--……
--if @myError=0
-- begin
-- commit transaction
-- print '成功'
-- end
--else
-- begin
-- rollback transaction --回滚
-- print '失败'
-- end
---------------索引---------------
--创建索引的方式
--通过显示的Create Index命令
--在创建约束时作为隐含的对象
--1.主键约束（聚集索引）
--2.唯一约束（唯一索引）
---------1.主键约束（聚集索引）-----------
--给AccountInfo表中的AccountCode字段添加索引
--create unique nonclustered index index_code
--on AccountInfo(AccountCode) --唯一约束（unique），非聚集索引（nonclustered）
--with(……)
--索引查看（sys.indexes)
--select * from sys.indexes where name='index_code'
--删除索引
--drop index index_code on AccountInfo
--显示指定索引进行查询
--select * from AccountInfo with(index=index_code)
--where AccountCode=''
---------------视图------------
----虚拟表（一般不会对视图进行修改数据）
----创建视图
--create view View_student
--as
-- select sname 姓名,dptName 部门 from student s,department d
-- where d.dpt_id=s.dpt_id
--go
----调用视图
--select * from View_student
----删除视图
--drop view View_student
-----------游标（指针）----------
--游标的分类
--静态游标（Static）：在操作游标的时候，数据发生变化，游标不变
--动态游标（Dynamic）：在操作游标的时候，数据发生变化，游标中数据发生变化，默认值
--键集驱动游标（KeySet）：在操作游标的时候，被标识的列发生改变，游标中数据改变,其他列改变,游标数据不变。
----创建游标(cursor:游标关键字；scroll:滚动游标，没有scroll，只可以向下移动)
--declare mycur cursor scroll
--for select sname from student
----游标的打开
--open mycur
----提取某行数据
--select * from student
--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 @sname nvarchar(50)
--fetch absolute 4 from mycur into @sname
--print @sname
----遍历游标
--declare @sname nvarchar(50)
--fetch first from mycur into @sname --第一行
------@@fetch_status:0:提取成功，-1:失败，-2:不存在
--while @@FETCH_STATUS=0
-- begin
-- print '提取成功：'+ @sname
-- fetch next from mycur into @sname --下移一行
-- end
----利用游标进行数据的修改和删除
-------修改----
--select * from student
--fetch first from mycur
--update student set dpt_id=4 where current of mycur
------删除------
--fetch absolute 2 from mycur
--delete from student where current of mycur
----关闭游标
--close mycur
----删除游标
--deallocate mycur
----创建指向某行多列的游标，循环显示多列数据
--declare mycur cursor scroll
--for select sname,dpt_id from student
--open mycur
--declare @sname nvarchar(50)
--declare @dpt_id int
-------warnning------
--fetch first from mycur into @sname,@dpt_id --第一行
------@@fetch_status:0:提取成功，-1:失败，-2:不存在
--while @@FETCH_STATUS=0
-- begin
-- print '提取成功：'+ @sname+' 部门'+cast(@dpt_id as nvarchar(10))
-- fetch next from mycur into @sname,@dpt_id --下移一行
-- end
--close mycur
--select * from student
-----------------函数------
----系统函数+自定义函数（标量值函数+表值函数）
--create function GetSum(参数列表)returns 类型
--as
--begin
--sql语句
--return
--end
--------------举例-标量值函数（无参数）-----
----删除函数
--drop function GetSum
----创建函数
--create function GetSum() returns float
--as
-- begin
-- declare @sum float
-- select @sum=(select sum(fs) from point)
-- return @sum
-- end
----运行函数
--select dbo.GetSum()
------------------举例-标量值函数（带参数）---------------------
--create function GetName(@sid int) returns nvarchar(50)
--as
-- begin
-- declare @sname nvarchar(50)
-- select @sname=sname from student where [sid]=@sid
-- return @sname
-- end
--select dbo.GetName(11)
-------------举例-表值函数---
--方案一--------
--create function GetTableRecord(@sid int,@dpt_id int) returns @result table
--(
-- sname nvarchar(50),
-- dptName nvarchar(50),
-- fs int
--)
--as
--begin
-- insert into @result
-- select sname 姓名, dptName 部门,fs 评分
-- from point p
-- inner join student s on s.[sid]=p.[sid]
-- inner join department d on d.dpt_id=p.did
-- where p.[sid]=@sid and p.did=@dpt_id
-- return
--end
--select * from GetTableRecord('1','1')
-------------举例-表值函数---
----方案二（局限：函数体内只能有return+sql查询结果。不能有begin-end）--------
--create function GetTableRecord2(@sid int,@dpt_id int)
--returns table
--as
-- return
-- select sname 姓名, dptName 部门,fs 评分
-- from point p
-- inner join student s on s.[sid]=p.[sid]
-- inner join department d on d.dpt_id=p.did
-- where p.[sid]=@sid and p.did=@dpt_id
--go
--select * from GetTableRecord('1','1')
------------------------------------------------
--drop function GetAge
--create function GetAge(@birth datetime) returns int
--as
-- begin
-- declare @age int
-- set @age = year(GETDATE())-YEAR(@birth)
-- if MONTH(GETDATE())<=MONTH(GETDATE())
-- begin
-- if(Month(GETDATE())=MONTH(@birth))
-- begin
-- if(DAY(GETDATE())<DAY(@birth))
-- begin
-- set @age-=1
-- end
-- end
-- else
-- begin
-- set @age-=1
-- end
-- end
-- return @age
-- end
--select dbo.GetAge('1997-12-28')
--------------触发器------------------
-----instead of触发器：在执行操作之前被执行
-----after触发器：在执行操作之后被执行
----create trigger 触发器名 on 表名 after/instead of insert/select/update/delete
----as
------
----go
------举例：假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表里找不到
------则自动添加部门信息，部门名称为“新部门”
--create trigger tri_InsertStudent on student after insert
--as
-- if not exists(select * from department where dpt_id=(select dpt_id from inserted))
-- begin
-- insert into department values(
-- --(select dpt_id from inserted),
-- '新部门')
-- end
--go
------删除触发器
--drop trigger tri_InsertStudent
----添加数据（有外键，报错）
--insert into student values('Mike213','999231',default,'2012-1-1',5,10000)
------------存储过程-------------
----1.没有输入参数，没有输出参数的存储过程
--create proc proc_名称
--as
-- SQL语句
--go
----运行存储过程
--exec pro_名称
----2.有输入参数，没有输出参数的存储过程
--create proc proc_名称
--@参数1 类型
--@参数2 类型
--as
-- SQL语句
--go
----运行存储过程
--exec pro_名称 参数1,参数2
----3.有输入参数，没有输出参数，但是有返回值的存储过程（返回值必须是整数）
--create proc proc_名称(
--@参数1 类型,
--@参数2 类型)
--as
-- SQL语句
-- return int类型参数
--go
----declare @参数 int
----运行存储过程
--exec @参数=pro_名称 参数1,参数2
--select @参数
----4.有输入参数，有输出参数的存储过程
--create proc proc_名称
--@参数1 类型
--@参数2 类型
--@参数3 类型 output --output代表输出参数
--@参数4 类型 output --output代表输出参数
--as
-- SQL语句
--go
------运行存储过程
--declare @参数1 类型 = 值
--declare @参数2 类型= 值
--declare @参数3 类型
--declare @参数4 类型
----exec pro_名称 参数1,参数2,参数3 output,参数4 output
--select @参数3
--select @参数4
----5.具有同时输入和输出参数的存储过程
--create proc proc_名称
--@参数1 类型
--@参数2 类型
--@参数3 类型 output --output代表输出参数
--@参数4 类型 output --output代表输出参数
--as
-- SQL语句
--go
------运行存储过程
--declare @参数1 类型 = 值
--declare @参数2 类型 = 值
-----------区别-----------
--declare @参数3 类型 =值
--------------------------
--declare @参数4 类型
----exec pro_名称 参数1,参数2,参数3 output,参数4 output
--select @参数3
--select @参数4
浙公网安备 33010602011771号