SqlServer数据库

1、判断数据库中是否存在，若存在，先删除后创建

if(object_id('tempdb..#temp_bill') is not null) begin drop table #temp_bill end;

1.1、删除student表

drop table student --删除表的数据和表的结构
truncate table student -- 只是清空表的数据，，但并不删除表的结构，student表还在只是数据为空

2、日期函数

--(yy,yyyy 年;qq,q 季度;mm,m 月;dy,y年中的日;dd,d 日;wk,ww 周;dw,w星期;hh 小时;mi,n分钟;ss,s秒)
select DATEADD(DY,-1,'2023-04-27') dy,
	   DATEDIFF(D,'2023-04-26','2023-04-27') d,
       DATEPART(year, '2020-11-10') [datepart], --返回的是整数
       DATENAME(year, '2020-11-10') [datename]; --返回的是字符串
select GETDATE()
SELECT  CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, GETDATE()) ), -1),121)--本周开始时间
SELECT  CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, GETDATE()) ), 5),121)--本周结束时间

--本周第一天（星期一）
SELECT   DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
--本周最后一天（星期天）  
select   dateadd(wk,datediff(wk,0,getdate()),6)
--本周第一天（星期天）
SELECT DATEADD(day, 1 - DATEPART(weekday, getdate()), getdate()) AS FirstDayOfWeek
--本周最后一天（星期六）
SELECT DATEADD(day, 7 - DATEPART(weekday, getdate()), getdate()) AS LastDayOfWeek
--每月最后一天
SELECT DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0)) AS LastDayOfMonth
--获取当前年的最后一天
select dateadd(year,datediff(year, 0, dateadd(year, 1, getdate())), -1)
--获取当前年的月数
SELECT number  日期 FROM MASTER..spt_values 
WHERE TYPE='P' and number>0 and number<=datepart(MM,convert(varchar(10),
dateadd(YEAR,datediff(YEAR,0,dateadd(YEAR,1,getdate())),-1),120))
--获取当前年的周数(只需更改datepart中的MM改成WK)

2-1、获取当前时间过去的12个月份

SELECT DISTINCT CONVERT(varchar(7), DATEADD(MONTH, -number, GETDATE()), 120) AS month
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 0 AND 11
ORDER BY month ASC

3、窗口函数

select *,
sum(cast(a4 as int)) over(partition by a3),
ROW_NUMBER() over(partition by a3 order by a3)
from aaa where a2 = '王五'

4、将多行结果展示在同一行

4-1、FOR XML PATH 的作用是将查询结果集以XML形式展现，将多行的结果，展示在同一行，

select cityname from [dbo].[city] where pid = 3 FOR XML PATH('')

4-2、通过字符拼接后可以把xml信息清除，并以指定的字符进行分割：

select ','+ cityname  from [dbo].[city] where pid = 3 FOR XML PATH('')

4-3、STUFF(character_expression , start , length , replaceWith_expression)

select  pid,stuff((select ','+ cityname  from [dbo].[city] as c  where b.pid = c.pid  FOR XML PATH('')),1,1,'') 
as a from [dbo].[city] as b group by pid order by pid 

5、some,any,all的用法

SOME 和 ANY 是相同的，它们表示与子查询中的任何一个值进行比较即为 TRUE。
ALL 表示与子查询中的所有值进行比较都为 TRUE。
ALL：对所有数据都满足条件，整个条件才成立 ，例如：5000大于所有返回的薪水
ANY 和 SOME：只要有一条数据满足条件，整个条件成立，例如，5000大于薪水中的3000, 10000, 7000。SOME 的作用和 ANY 一样。

select * from bbb
where b2 > any(select b2  from bbb where b2 >= '2' and b2 <= '3')--some,any,all

6、行转列

select cs002,cs003,cs004,cs005 from f_ceshi(nolock)
--方法:
SELECT  cs002 '姓名',
max(CASE cs003 WHEN '语文' then cs004 ELSE 0 END) '语文',
max(CASE cs003 WHEN '数学' then cs004 ELSE 0 END) '数学',
max(CASE cs003 WHEN '英语' then cs004 ELSE 0 END) '英语',
max(CASE cs003 WHEN '生物' then cs004 ELSE 0 END) '生物',
max(CASE cs003 WHEN '物理' then cs004 ELSE 0 END) '物理'
,cs005
FROM f_ceshi
GROUP BY cs002,cs005

7、列转行

select cs002,cs003,cs004,cs005,cs006 from f_ceshi(nolock)
--方法:
select * from( 
  select cs002,course='cs003',score=cs003 from f_ceshi  
  union all
  select cs002,course='cs004',score=cs004 from f_ceshi  
  union all
  select cs002,course='cs005',score=cs005 from f_ceshi  
  union all
  select cs002,course='cs006',score=cs006 from f_ceshi)stu_Info
order by cs002, 
--下面是为了按照jave、C#、python 的格式输出
case course 
	when 'cs003' then 1 
	when 'cs004' then 2 
	when 'cs005' then 3 
	when 'cs006' then 4
end

7-1、一个单元格有多个值的拆分成多行

例子1：

select requestid,workflowid,receivedpersonids from 表名 where workflowid=**6 and requestid = 24**7

select distinct a.requestid,a.workflowid
,SUBSTRING(cast(a.receivedpersonids as varchar),number,
CHARINDEX(',',cast(a.receivedpersonids as varchar)+',',number)-number) as protestxx
from 表名 a  with(nolock) ,
(select * from .master..spt_values with(nolock) where type='p' and number between 0 and 50) f_ht07
where  SUBSTRING(','+cast(a.receivedpersonids as varchar),number,1)=','
and workflowid=**6 and requestid = 24**7

例子2：

with fyj_plm_cfh (verid,filename, filepath, wjj1, hm, fjd) AS (
    SELECT verid,filename,filepath, SUBSTRING(filepath, 1, CHARINDEX('\', filepath) - 1) wjj1,
    SUBSTRING(filepath, CHARINDEX('\', filepath) + 1, 1000) hm, 1 fjd
    FROM fyj_plm_cfq 
    UNION ALL
    SELECT verid,filename,filepath, SUBSTRING(hm, 1, CHARINDEX('\', hm) - 1) wjj1,
    SUBSTRING(hm, CHARINDEX('\', hm) + 1, 1000) hm,  fjd+1  fjd
    FROM fyj_plm_cfh
    WHERE CHARINDEX('\', hm) > 0
)

行转列

SELECT * FROM
(sELECT verid,filename,filepath, wjj1,fjd FROM fyj_plm_cfh) AS src
PIVOT
(MAX(wjj1) FOR fjd IN ([1],[2],[3],[4],[5],[6])
) AS pivot_table

8、内连接查询两种方法

 select .... from 表1 as s  inner join 表2  as c on s.=c.
 select .... from 表1 as s ,表2 as c where  s.=c.

9、数据库完整性

1. 域完整性:检查约束() 默认约束 非空约束
2. 实体完整性:主键约束 唯一约束 标识约束
3. 引用完整性:外键约束
4. 自定义完整性:触发器

10、数据库操作

 增insert into 表名(列1,列2...) values(值1,值2...)
 删delete from 表名 where 条件
 改update 表名 set 列1 = 新值,列2=新值...where 条件
 查
 select 列1,列2...
 from 表名 
 where 条件 
 group by 列 
 having 条件 
 order by 列[asc,desc]

10-1，循环增加多列

DECLARE @i INT = 90
WHILE @i <= 200
BEGIN
    DECLARE @columnName NVARCHAR(50) = 'ABF' + RIGHT('000' + CAST(@i AS VARCHAR(3)), 3)
    DECLARE @sql NVARCHAR(MAX) = 'ALTER TABLE 表名 ADD ' + @columnName + ' 数据类型;' 
    EXEC sp_executesql @sql
    SET @i = @i + 1
END

11、数据的回滚与提交

begin tran
update f_ceshi set cs002 = '一级-二级-3-三级-3' where cs001 = 14
select cs001,cs002,cs003 from f_ceshi(nolock) 
rollback tran --回滚操作
commit tran --提交操作

12、远程服务器连接

 select * from sys.servers

select * from 
OPENDATASOURCE('SQLOLEDB','Data Source=123.123.1.1;User ID=aa ;Password= 111').SNMES_SYS.dbo.POWPAA 

13、添加约束的SQL语句

use FineRP
go
select * from sysobjects where name = 'aaa'
--添加主键约束
alter table student add constraint PK_studentno primary key(studentno)
--唯一约束
alter table student add constraint UQ_identitycard unique(identitycard)
--默认约束
alter table student add constraint DF_address default('地址不详') for address
--检查约束
alter table student add constraint CK_borndate check(borndate > '1990-01-01')
--外键约束
alter table student add constraint FK_gradeid foreign key(gradeid) references grade(gradeid)

14、变量(局部变量)(全局变量)

14-1、局部变量加一个@

use FineRP
go
declare @a2 varchar(10)
declare @a3 varchar(9)
set @a2='王五'
set @a3='语文'
select @a2=a4 from aaa where a2=@a2 and a3='数学'
print @a2

14-2、全局变量加两个@@

print '当前服务器名称'+@@servername
print '版本号'+@@version

select @@SERVERNAME as 服务器名
select @@VERSION as 版本

15、练习

declare @shape varchar(2)
set @shape='☆'
print @shape
print @shape+@shape
print @shape+@shape+@shape
print @shape+@shape+@shape+@shape

–逻辑结构

16、事务

use FineRP
if exists(select * from sysobjects where name = 'bank')
drop table bank    
go
create table bank
(
	customername varchar(20) primary key,
	currentmoney money
)
go
alter table bank add constraint CK_currentmoney check(currentmoney>=1)

go
insert into bank values('张三',1000)
insert into bank values('李四',1)

update bank set currentmoney=currentmoney-1000 where customername='张三'
update bank set currentmoney=currentmoney+1000 where customername='李四'

17、使用事务处理转账

begin transaction
declare @errorsum int
set @errorsum = 0
update bank set currentmoney=currentmoney-400 where customername='张三'
set @errorsum = @errorsum+@@ERROR  --打印错误的SQL语句累加,如果@errorsum=0,sql语句无错
update bank set currentmoney=currentmoney+400 where customername='李四'
set @errorsum = @errorsum+@@ERROR

if(@errorsum>0)
begin 
print '转账失败'
rollback transaction
end

else 
begin
print '转账成功'
commit transaction
end
go

select * from bank
go

18、先判断该存储过程数据库里面有没有,有打印yes,没有则no

if exists (select * from sysobjects where name= 'fj_abc ' and xtype ='P')
print 'yse'
else
print 'no'
--删除存储过程
drop procedure fj_abc 

19、创建存储过程(不带参数)

--**创建不带参数的存储过程**
create procedure fj_abc as
select cs001,cs002,cs003,cs004,cs005,cs006 from f_ceshi(nolock)

20、执行存储过程

execute fj_abc

21、.创建存储过程(带输入参数)

create procedure fj_abc 
@id int
as
select cs001,cs002,cs003,cs004,cs005,cs006 from f_ceshi(nolock) where cs001 = @id

22、执行带参数的存储过程(存储过程名称之后,空格加上参数,多个参数中间以逗号分隔)(注意:参数赋值是,第一个参数可以不写参数名称,后面传入参数,需要明确传入的是哪个参数名称)

execute fj_abc 1

23、带传出参数的存储过程

create proc fj_abc
@cs002 varchar(10) out    --不带out，将被默认为传入参数！
as 
begin
select @cs002=cs002 from f_ceshi 
where cs001='1'    --传出参数只能是一个值，如果不带条件地查找，得到的数值是一个列表，将只取最后一个值
end

24、执行带传出参数的存储过程

declare @cs003 varchar(10)   --声明一个变量，用于接收存储过程传出的参数
exec fj_abc @cs003 out     --调用时，需要右边附带用于接收传出参数的变量
select @cs003 as '存储过程传出的参数' --取得传出的参数，并自定义列名

25、if else嵌套用法

declare @num int --定义变量
set @num=1 --赋值变量
if(@num>10)

begin
select '不大于10' 
end

else 

begin
if(@num<1)
select '不是小于0的数' 
else
print @num
end

26、case when嵌套用法

declare @num1  int --定义变量
set @num1=111 --赋值变量
select @num1,
case
when @num1<=100 then 
case
when @num1>=80 then 'A'
when @num1>=60 then 'B'
else 'C' end

when @num1>=200 then '优秀'
else 'haha'
end

27、创建视图

使用SQL语句创建视图
if exists(select * from sysobjects where name = 'view_name')
drop table view_name
--创建视图
create view view_name
as
<select 语句>
--使用SQL语句查看视图
select * from view_name

28、创建索引unique 唯一索引,CLUSTERED 聚集索引,NONCLUSTERED 非聚集索引

use MySchool
go
if exists(select name from sysindexes where name ='IX_Student_StudentName' )
drop index Student.IX_Student_StudentName
go
create unique
   index IX_Student_StudentName
   on Student(StudentName)
   WITH FILLFACTOR=30 --填充因子:指定一个0-100之间的值,表示索引页填充的百分比
go

29、带有索引的查询

select * from student with(index = IX_Student_StudentName)
 where studentname like '李%'

30、最简单的游标使用(提取aaa表中的第一行的数据)

declare curxm cursor  --declare cursor声明游标
for
select * from aaa
open curxm          --打开游标
fetch next from curxm     --fetch语句提取数据
close curxm         --关闭游标
deallocate curxm         --deallocate语句删除游标

31、复杂游标 (0 fetch语句执行成功 , -1 fetch语句执行失败, -2 被读取的记录不存在)

set nocount on   --不计数
declare @sto char(6),@sub char(8)

declare curxm cursor  
for select a2,a3 from aaa
open curxm  

fetch  from curxm into @sto,@sub
while @@FETCH_STATUS=0  --循环
begin 
print @sto+':'+@sub   --打印信息 (可换成更新删除语句)
fetch  from curxm  into @sto,@sub

end
close curxm         
deallocate curxm   

32、获取数据库的表名和对应表的列名

select a.name 列名,a.id,b.name 表名 from syscolumns a 
left join sysobjects  b on a.id = b.id
where b.xtype='u'

33、随机抽取几条记录(例如随机取一条数据)

select top 1 * from fyjabj(nolock) order by newid()

34、递归查询

--通过根节点向下查找该根节点下的子节点
with temp(id,departmentName)  as 
( select id,departmentName from SYS_Department where id = 2
union all select a.id,a.departmentName from SYS_Department a inner join temp on a.parentId = temp.id ) 
select * from temp
 
--通过子节点向上查找该子节点的根节点
with temp(id,departmentName)  as 
( select id,departmentName from SYS_Department where id = 2
union all select a.id,a.departmentName from SYS_Department a inner join temp 
--调换查询字段(向上查找和向下查找的区别就是on后面的顺序调换一下即可)
on a.id = temp.parentId ) 
select * from temp

35、创建自定义函数

自定义函数(标量值函数,表格值函数包括(内联表格值函数定义格式,多语句表值函数定义格式))
a.标量值函数返回的是一个数据类型值，
内联表值函数返回的是一个table，而多语句表值函数返回的是一个table的变量（类似前面两个的结合）；
b.语法的结构：标量值函数和多语句表值函数都是要有begin…end，内联表值函数就没有；
c.调用：标量函数要写成在dbo.function_name；

35-1、标量值函数例子

go
CREATE FUNCTION function_fceshi(@date_into int) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS varchar(20)   --返回返回值的数据类型
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
as
BEGIN
	declare @result_name varchar(20)
	select  @result_name = cs002 from f_ceshi where cs001 = @date_into   
  RETURN  @result_name
END

35-2、执行标量值函数

 select dbo.function_fceshi(3) name;

35-3、内联表格值函数定义格式：

CREATE FUNCTION function_fceshi(@date_into varchar(8)) 
 
RETURNS table
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
as
  RETURN  select  cs001,cs002,cs003,cs004,cs005 from f_ceshi where cs001 = @date_into

35-4、执行内联表格值函数

select * from [dbo].[function_fceshi](1);

35-5、多语句表值函数

go
CREATE FUNCTION function_fceshi(@date_into varchar(8)) 
RETURNS @table_test table(date varchar(8),ID varchar(20),name varchar(20))
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
as
begin
  insert @table_test select  cs001,cs002,cs003 from f_ceshi where cs001 = @date_into
   RETURN 
end

35-6、执行多语句表值函数

select * from [dbo].[function_fceshi](1);

35-7、删除自定义函数

drop function function_fceshi  

36、查看产生死锁的表：

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'

37、“杀死”死锁进程：

kill spid

38、sys.sysprocesses用于存储当前正在运行的进程信息

select 
	b.dbid, b.name, a.spid, a.blocked, cpu xx, * 
from sys.sysprocesses a (nolock)  
	full join master.sys.sysdatabases b (nolock) on a.dbid = b.dbid 
where a.status = 'runnable                      '
order by cpu desc

39、可以获取有关指定进程正在执行的 SQL 语句的信息，这对于诊断和调试正在运行的查询或进程非常有用。

EventType：表示当前执行语句的事件类型。
Parameters：表示当前执行语句的参数。

DBCC INPUTBUFFER(177)

40、索引

在数据库中，索引通常可以在以下几个维度进行划分：

. 按照存储方式分：聚簇索引和非聚簇索引。

这主要关系到数据的物理存储结构。聚簇索引决定了表中数据的物理存储顺序，表数据按照聚簇索引键的值进行排序，表只有一个聚簇索引；非聚簇索引，又被称为二级索引，它的索引结构和数据存储位置是分离的，一个表可以有多个非聚簇索引。

. 按照唯一性划分：普通索引和唯一索引。

普通索引允许索引列中存在重复值；唯一索引则要求索引列中的值必须是唯一的，不过也允许为空。

创建唯一索引：CREATE UNIQUE INDEX index_name ON table_name(column_name);

.按照完整性划分：主键索引。

主键索引是一种特殊的唯一索引，它不仅要求索引列的值是唯一的，而且不允许为空。

创建主键索引：
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ....,
   PRIMARY KEY(column1)
);

. 按照检索方式划分：全文索引。

全文索引用于执行全文搜索，也就是查找出现在任何一个字段中的值，而不仅仅是查找在一个具体字段中的值。

创建全文索引：CREATE FULLTEXT INDEX index_name ON table_name(column_name);

. 按照列数划分：单列索引和复合索引。

单列索引是最普通的索引，它是基于单一列进行索引；复合索引则是基于多个列的组合进行索引。

创建单列普通索引：CREATE INDEX index_name ON table_name(column_name);
创建复合索引：CREATE INDEX index_name ON table_name(column_name1, column_name2);

在具体的数据库系统中，还可能有其他更细微的索引类型区分，比如位图索引、空间索引等。这些索引的设计都是为了满足不同场景下的查询效率优化需求。

删除索引：DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;