SQL知识点汇总

----------向表中插入一条记录-----------
–insert into 表名（列1，列2，列3） values(值1，值2，值3)

------------更新语句-------------
–update 表名 set 列=新值，列2=新值2，… where 条件

–启动某个表的“自动编号列”手动插入值的功能
set identity_insert TblClass on

------------删除数据-------------
–delete from 表名 where …–如果不加where条件则删除表格中所有数据 delete之后自动编号列的值不会回到初始值
–truncate table 表名–删除表格所有数据,并且自动编号列值回到初始值

------------将表中列的值显示为别的---------------
select case when 条件 then 新值1 else 新值2 end as 列的新名,列2 from 表

------------------------------------------------数据检索----------------------------------------------
-----------为查询结果集中的列起别名-----------
select
tsID as 学生编号,
tsName 学生姓名,
tsGender ‘学生性别’,
班级=tsClassID,
婚否=‘否’–可以在查询结果中随便加一列，并赋值
from TblStudent

-----------创建一列存放当前系统时间-------------
select 列名=getdate()

-----------distinct关键字,针对已经查询出的结果然后去除重复(查询的所有列都相同)------------
select distinct 列 from 表

-----------Top关键字，前多少，一般配合排序order by 一起使用------------
–取年龄最大的两个人（top 的数字如果是表达式，一定要用括号将表达式括起来）
select top 2 * from TblStudent order by tsAge desc
–年龄最大的前40%
select top 40 percent * from TblStudent order by tsAge desc
–排序
select * --3
from TblStudent --1
where tsAge>20 --2
order by tsAge desc,tsClassID desc

-----------------------------------------------聚合函数----------------------------------------
–对数据进行分析汇总
select * from TblStudent
–统计出所有人的年龄的总和
select sum(tsAge) as 年龄总和 from TblStudent
–统计表中一共有多少条记录
select count() as 总的人数 from TblStudent
–计算平均年龄
select ((sum(tsAge))/count()) as 平均年龄 from TblStudent --1
select avg(tsAge*1.0) as 平均年龄 from TblStudent --2
–计算年龄最大的
select max(tsAge) as 最大年龄 from TblStudent
–计算年龄最小的
select min(tsAge) as 最小年龄 from TblStudent
–聚合函数的一些问题
–1.不统计空值
–2.avg的分子分母均不算空值

–‘between 20 and 30’ 表示大于等于20,小于等于30
–‘in (2,5,9)’ 表示等于2或者等于5或者等于9

----------------------------------模糊查询（针对字符串）----------------------------------
–通配符：
----------’_’ 表示任意单个字符----------
–查询彭某某的行
select * from TblStudent where tsName like ‘彭__’

----------’%’ 表示任意多个任意字符----------
select * from TblStudent where tsName like ‘彭%’

----------’[]’ 表示筛选范围-----------
select * from TblStudent where tsName like ‘彭[0-9]雄’–只要是彭和雄中间是数字
select * from TblStudent where tsName like ‘彭[a-z]雄’–只要是彭和雄中间是字母，默认不区分大小写

----------’^’ 表示非----------
select * from TblStudent where tsName like ‘彭[^0-9]雄’–只要是彭和雄中间不是数字
select * from TblStudent where tsName not like ‘彭[0-9]雄’–这条和上面一条完全不一样，只要不是彭和雄中间是数字的都行

----------转义------------
–若要以上的字符就表示普通字符需要放在[]中转义，如[%]表示一个%字符，[_]表示一个_字符
select * from TblStudent where tsName like ‘%[%]%’–表示只要名字中带%号的就行
----------自己设定转义符----------
select * from TblStudent where tsName like ‘%/%%’ ESCAPE ‘/’ --设定/为转义符，即搜索名字中带%的

--------------------------------空值处理-----------------------------
select * from TblStudent where tsBirthday is null
select * from TblStudent where tsBirthday is not null

---------------------------------------数据分组group by（和聚合函数）----------------------------------------
–统计班上学生选课,每门课的人数是多少
select
tsClassID as 班级ID,
班级人数=count(*)–该count对每个分组使用
from TblStudent
group by tsClassID

–统计班上学生选课,每门课的男生人数
select
课程ID=tsClassID,
男生人数=count(*)–4
from TblStudent–1
where tsGender=‘女’–2
group by tsClassID–3

–筛选班级人数多于2的班级
select
班级ID=tsClassID,
班级人数=count()–4
from TblStudent–1
group by tsClassID–2
having count()>2–3 该处比4执行早,所以不能使用3中新建的列名
order by 班级人数–5

--------------------------------SQL语句的执行顺序------------------------------
–5>select 5-1>选择列,5-2>distinct,5-3>top(应用top选项最后计算)
–1>from 表
–2>where 条件
–3>group by 列
–4>having 条件
–6>order by 列
–详细：1.from 2.on 3.join 4.where 5.group by 6.with cube或with rollup 7.having 8.select 9.distinct 10.order by 11.top

–要把一个select的结果当做另一个select的集合是，必须先用as 集合名取别名,在另一个select中要用集合名.列名

------------------------------------类的转换函数-------------------------------------
–cast
–convert 部分只能用convert
select 100+1000–输出1100
--------------自动作了类型转换-----------------
select 100+‘1000’–输出1100
select ‘1000’+100–输出1100
--------------手动类型转换---------------
select ‘1000’+‘100’–输出1000100
select cast(‘1000’ as int)+convert(int,‘100’)–输出1100
print 100+1000
--------------时间格式转换---------------
print getdate()–默认美式时间格式
print convert(varchar(100),getdate(),120)–120表示中国时间格式

---------------------------------------联合结果集union--------------------------------
–将多个表的行联合起来
–两个表的列的数目相同，列的数据类型兼容
------------联合两张表的部分列union all不会去除重复行-------------
select tsId from TblStudent
union all
select tClassID from TblClass
------------联合两张表的部分列union自动去除重复行，并重新排序-------------
select tsId from TblStudent
union
select tClassID from TblClass
------------统计并在底部做汇总-------------
select * from TblStudent
select
tsAddress,
人数=count(*)
from TblStudent
group by tsAddress
union all
select ‘总岁数’,sum(tsAge) from TblStudent
order by 人数

--------------将TblStudent复制到还未创建的表TblStudent3中-------------
--------拷贝数据和结构--------
select * into TblStudent3 from TblStudent–创建新表并复制数据以及结构
--------只拷贝结构---------
select top 0 * into TblStudent4 from TblStudent–此时只拷贝结构
select * from TblStudent4
select * into TblStudent5 from TblStudent where 1<>1–利用where条件筛选，此时只拷贝结构
select * from TblStudent
--------------将TblStudent复制到已存在的表TblStudent5中-------------
insert into TblStudent5
select tsName,tsGender,tsAddress,tsAge,tsBirthday,tsIDCard,tsClassID–列中不包括自动编号列
from TblStudent
where tsGender=‘女’

----------------------------------字符串函数-----------------------------------
–1.len() 计算字符的个数
print len(‘hi 大家好啊’)–返回7
–2.datalength() 计算字节数
print datalength(‘hi 大家好啊’)–返回11
print datalength(N’hi 大家好啊’)–返回14
–3.转换大小写
–3.1 upper() 转为大写
print upper(‘Hello,How are you’)
–3.2 lower() 转为小写
print lower(‘Hello,How are you’)
–4.去掉空格
–4.1 ltrim() 去掉左边空格
print ltrim(’ Hello a’)
–4.2 rtrim() 去掉右边空格
print rtrim(’ Hello ‘)
–4.3 去掉两边空格
print rtrim(ltrim(’ Hello '))
–5.字符串截取
–5.1 left() 从左边截取多少字符
print left(‘中华人民从此站起来了’,4)
–5.2 right() 从右边截取多少字符
print right(‘中华人民从此站起来了’,4)
–5.3 substring() 从第几个字符开始截取多少字符
print substring(‘中华人民从此站起来了’,4,2)
print substring(‘中华人民从此站起来了’,-1,4)–从第一个字符开始截取2个字符

-------------------------------日期函数-----------------------------
print getdate()
print sysdatetime()
–1.dateadd() 增加时间
print dateadd(day,200,getdate())–在现在的时间上加上200天
select dateadd(day,200,getdate())
print dateadd(month,200,getdate())–在现在的时间上加上200个月
–2.datediff() 计算两个日期的差
select datediff(hour,‘1995-11-18 10:00:00’,getdate())–年龄
–获取日期的某部分
print datepart(year,getdate())–方式一,返回数字类型
print year(getdate())–方式二,返回数字类型
print datename(year,getdate())–方式三,返回的字符串

use School
select * from TblClass
insert into TblClass values(‘英语’)
delete from TblClass where tClassName=‘java’

---------------------------------------连接查询--------------------------------
----------------内连接inner join------------------
–连接查询,将有外连接的多张表连接展示,只显示匹配的
use School
select * from TblClass
select * from TblStudent inner join TblClass on TblStudent.tsClassID=TblClass.tClassID --on 后面其实为条件，当条件成立的列才显示
select * from TblStudent inner join TblClass on TblStudent.tsClassID<>TblClass.tClassID
–当两个表有重名列时，不能用*，重名列要显示的话用表名.列名
select
TblSTudent.tsID,tsName,tsAge,tClassName,tEnglish
from TblStudent inner join TblClass on TblStudent.tsClassID=TblClass.tClassID
inner join TblScore on TblStudent.tsID=TblScore.tSID
–给表起别名
select
tsID,tsName,tsAge,tClassName
from TblStudent as ps inner join TblClass as pc
on ps.tsClassID=pc.tClassID
-----------------交叉连接cross join------------------------
-----------------外连接------------------
–外连接步骤,1.先找出内连接匹配项2.再展示未匹配项,未匹配的显示null
–左外联left outer join
–显示左边表的所有数据,右边表的匹配数据,不匹配显示null
select
TblSTudent.tsID,tsName,tsAge,tClassName
from TblStudent left join TblClass on TblStudent.tsClassID=TblClass.tClassID
–右外联right outer join
–显示右边表的所有数据,左边表的匹配数据,不匹配显示null
select
TblSTudent.tsID,tsName,tsAge,tClassName
from TblClass right join TblStudent on TblStudent.tsClassID=TblClass.tClassID
–当使用连接查询的时候，如果同时要指定查询的条件，那么一定要使用where语句，不要直接在on条件后面跟and来编写其他查询条件
--------------------自连接-------------------
–表自己和自己连
use City
select
t1.AreaId 城市编号,
t1.AreaName 城市名称,
t2.AreaName 省份名称
from Area as t1
inner join Area as t2 on t1.AreaPID=t2.AreaID

-----------------------使用带参数的sql语句向数据库中插入空值-------------------------

-----------------------case查询,类型c#中的switch------------------------
–正常情况显示增加一列
select
*,头衔=‘菜鸟’
from TblStudent
–第一种 使用case显示增加一列，此种方法可以做大于小于等区间判断
select
*,头衔=case
when tsClassID=1 then ‘菜鸟’
when tsClassID=2 then ‘老鸟’
when tsClassID=20 then ‘大师’
else ‘大佬’
end
from TblStudent
–第二种case显示增加一列,此种方法类似c#中的switch,只能做等于判断
select
*,头衔=case tsClassID
when 1 then ‘菜鸟’
when 2 then ‘老鸟’
when 20 then ‘大师’
else ‘大佬’
end
from TblStudent
–when后跟条件,then后跟值

----------------------------------------索引Index------------------------------------
–1.索引的目的，提高查询效率
–2.1聚集索引（物理）
–2.2非聚集索引（逻辑）
-------------创建聚集索引-------------
–一张表只能有一个聚集索引
create clustered index IxAge on TblStudent(TblAge)
-------------删除索引-------------
drop index TblStudent.IxAge
-------------创建非聚集索引-------------
create nonclustered index IxAge on TblStudent(TblAge)

---------------------------------子查询-------------------------------
-------------独立子查询--------------
select * from TblStudent
where tsClassID=(select tCLassID from TblClass where tClassName=‘通信工程’)
-------------相关子查询--------------
select * from TblStudent
where exists(select * from TblClass where TblClass.tClassID=TblStudent.tsClassID and TblClass.tClassName=‘通信工程’)
–exists()返回bool
----------------------------------分页--------------------------------
--------------用子查询加top分页----------------
–要分页查询或者分页显示,首先要确定按照什么顺序,然后才能确定哪些记录应该在第一页,哪些记录应该在第二页
–每页显示3条
–显示第一页
select top 3 * from TblStudent order by tsAge asc
–显示第二页
select top 3 * from TblStudent where tsID not in
(select top (3*(2-1)) tsID from TblStudent order by tsAge asc)
order by tsAge asc
--------------用row_number()实现分页--------------
–1.为数据排序，然后编号,末尾任然可以进行排序
select
,Rn=ROW_NUMBER() over(order by tsAge desc)
from TblStudent–order by tsAge asc
–2.根据用户要查看的每页记录条数，以及要查看第几页，确定应该查询第几条到第几条
–每页显示3条，查看第四页
select *
from (select ,Rn=ROW_NUMBER() over (order by tsAge desc) from TblStudent ) as t
where t.Rn between 3(4-1)+1 and 34

----------------------------------------T-SQL编程-----------------------------------
–1.声明变量
–declare @变量名 数据类型
–一个变量一个变量声明
declare @name nvarchar(50)
declare @age int
–多个变量一起声明
declare @name nvarchar(50),@age int
–2.为变量赋值
set @name=‘彭振雄’
select @age=18
–3.输出
select ‘姓名’,@name
select ‘年龄’,@age

–while循环
declare @i int=1–声明变量同时赋值
declare @sum int=0
while @i<=100
begin
set @sum=@sum+@i
set @i=@i+1
end
print @sum
begin
print ‘Hello’
set @i=@i+1
end
–begin end相当于c#中while的{}
–跳出循环也是break;continue;

--------if ,else if,else---------
declare @i int=15
if @i>0
begin
print ‘@i>10’
end
else if @i>5
begin
print ‘@i大于5小于10’
end
else
begin
print ‘@i小于5’
end
–计算1-100之间所有奇数的和
declare @n int=1,@sum int=0,@m int=2
while @n<=100
begin
if @n%2<>0
begin
set @sum=@sum+@n
end
set @n=@n+1
end
print @sum
–质数和,下面有点问题
begin
while @m<@n
begin
if @n%@m=0
begin
break;
end
else if @m=@n-1
begin
set @sum=@sum+@n
end
else
begin
set @m=@m+1
end
end
set @n=@n+1
end
print @sum

–一般以@@开头变量是系统变量,无法手动设置修改,只读
print @@version–版本号
print @@error–最后一次执行出错的错误号
print @@language–使用的语言
print @@MAX_CONNECTIONS
print @@ROWCOUNT–上条SQL语句影响的行数

select * from TblStudent
--------------------------------------事务-------------------------------------
–保证几条执行操作同时执行成功或者同时执行失败–银行转账
–1.打开事务
begin transaction
declare @sum int =0
update TblSTudent set tsAge=tsAge+2 where tsName=‘彭振雄’
set @sum=@sum+@@error
update TblStudent set tsAge=tsAge-2 where tsName=‘哈哈(女)’
set @sum=@sum+@@error
if @sum<>0
begin
–程序执行出错，回滚
rollback
end
else
begin
–程序执行没错，提交事务
commit
end
–1）自动提交事务
–当执行一条SQL语句的时候，数据库自动帮我们打开一个事务，当语句执行成功，数据库自动提交事务，执行失败，数据库自动回滚事务
insert
–2）隐式事务
–每次执行一条SQL语句的时候，数据库自动打开一个事务，但是需要我们收到提交或者回滚事务
SET IMPLICIT_TRANSACTIONS{ON|OFF}–打开关闭隐式事务

–3）显式事务
begin transaction
…
commit
…
rollback

------------------------------------------存储过程------------------------------------
–类似C#中的函数方法
–优点
–1.执行速度快
–2.允许模块化程序设计–类似方法的复用
–3.提高系统安全性–防止SQL注入攻击
–4.减少网络流通量–只要传输存储过程的名称

------------------系统存储过程---------------
–sp_databases 列出服务器上的所有数据库
exec sp_databases
–sp_tables 列出服务器上的所有表
exec sp_tables
–sp_renamedb 更改数据库名称
–sp_columns 返回某张表中的所有列
exec sp_columns ‘TblStudent’
—sp_helptext 查看某个存储过程的源代码
exec sp_helptext ‘sp_databases’

---------------------创建自己的存储过程-------------------------
---------创建并执行无参存储过程-------------
–创建
create proc usp_helloworld
as
begin
–这里写执行语句
print ‘helloworld’
end
–执行存储过程
exec usp_helloworld
---------创建并执行带参数的存储过程-----------
create proc usp_add
@n1 int,
@n2 int
as
begin
select @n1+@n2
end
–执行带参存储过程
exec usp_add 100,500
–设置存储过程的参数的默认值
create proc usp_add1
@n1 int =100,
@n2 int
as
begin
select @n1+@n2
end
–执行带默认值的参数的存储过程
exec usp_add1 10,20
exec usp_add1 @n2=10
exec usp_add1 10–该方式错误,未明确赋值参数

------------------带输出参数的存储过程--------------------
create proc usp_show1
@gender nchar(1),
@recordcount int output --输出参数
as
begin
select * from TblStudent where tsGender=@gender
–把查询语句查询到的记录的条数赋值给变量@recordcount
set @recordcount=(select count(*) from TblStudent where tsGender=@gender)
end
–使用
declare @r int–创建参数接收输出
exec usp_show1 ‘男’,@recordcount=@r output
print @r

--------------使用存储过程写一个分页查询--------------
select * from TblStudent
create proc usp_TblSTudent_Page1
@pagesize int =3,
@pageindex int =1,
@recordcount int output,
@pagecount int output
as
begin
select *
from (select ,rn=ROW_NUMBER() over(order by tsAge) from TblStudent) as t
where t.rn between @pagesize(@pageindex-1)+1 and @pagesize*@pageindex
set @recordcount=(select count() from TblStudent)
set @pagecount=ceiling(@recordcount1.0/@pagesize)
end
–使用
declare @rc int,@pc int
exec usp_TblSTudent_Page1 @pagesize=3,@pageindex=2,@recordcount=@rc output,@pagecount=@pc output
print @rc
print @pc

create proc usp_Age
@tsname1 nvarchar(50),
@tsname2 nvarchar(50),
@tsage int
as
begin
begin transaction
declare @sum int =0
update TblStudent set tsAge=tsAge+@tsage where tsName=@tsname1
set @sum=@sum+@@ERROR
update TblStudent set tsAge=tsAge-@tsage where tsName=@tsname2
set @sum=@sum+@@ERROR
if @sum<>0
begin
rollback
end
else
begin
commit
end
end

exec usp_Age @tsname1=‘彭振雄’,@tsname2=‘嘻嘻’,@tsage=1
select * from TblStudent
use School

–存储过程对TblClass表插入数据
create proc usp_insert_TblClass
@tClassName nvarchar(50)
as
begin
insert into TblClass values(@tClassName)
end

–存储过程对TblClass表删除数据
create proc usp_delect_TblClass
@tClassName nvarchar(50)
as
begin
delete from TblClass where tClassName=@tClassName
end

–存储过程对TblClass 表更新数据
create proc usp_update_TblClass
@tClassName nvarchar(50),
@tClassNewName nvarchar(50)
as
begin
update TblClass set tClassName=@tClassNewName where tClassName=@tClassName
end

–存储过程对TblClass表查询数据
create proc usp_select_TblClass
@tClassName nvarchar(50)
as
begin
select * from TblClass where tClassName=@tClassName
end

-----------------------------------------触发器trigger---------------------------------
–进行了某种触发操作后，自动进行触发器中设定的操作
–触发器分类
–DML触发器：对表格数据操作时触发
–DDL触发器：对表格进行操作时触发
–after触发器:语句执行完毕之后触发（常用）
–instead of触发器
–触发器有两个默认表（只能在触发器中能访问到）：
–deleted:存储删除的行(delete、update触发器用到)
–inserted:存储新的数据(insert、update触发器用到)
select top 0 * into TblClass2 from TblClass

------------创建一个触发器-------------
–当TblClass表中删除行,将删除的行插入到TblClass2中
create trigger tri_delete on TblClass
after delete–触发操作进行删除操作之后
as
begin
insert into TblClass2(tClassName)–向TblClass2中插入数据
select tClassName from deleted–插入的数据是deleted表中保存的所有数据
end

–删除TblClass表中一行数据
select * from TblClass2

–查看TblClass2表中是否增加了一行
delete from TblClass where tClassName=‘体育’

use Login
select * from up