Sql常用sql语句收藏夹

***https://www.tuicool.com/articles/eyIB7z***

***https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-2017***

1.查看数据库连接数据

SHOW PROCESSLIST

2.查看当前数据库版本

# 查询当前数据库版本
select @@VERSION

3.返回不是时间类型的数据：isdate

select csj, ISDATE(csj) from dbo.t_gh where ISDATE(csj) = 0 order by csj asc;

4.查找两条相同的收费信息记录：

 --1:
 select count(*),id from receipt group by id having count(*)>1;
 --2:
 select *,ROW_NUMBER() over(partition by [Message] order by [Message] ASC) as rnk from [dbo].[Log]

5.根据所有分院查询各院的患者数量：

select distinct s.建档门诊,COUNT(s.病人编号) as 病人总数量
from dbo.病人基本信息 AS s
group by s.建档门诊

View Code

6.查询数据库下所有的表，表数据行数，表所占空间大小

①简单型，表名，表行数

select a.name,b.rows
from sysobjects As a join sysindexes As b 
on a.id=b.id
where(a.type='U') AND (indid in(0,1))
order by b.rows
DESC

View Code

②中间型，表名，行数，所占空间大小

 1 SELECT
 2 TableName = obj.name,
 3 TotalRows = prt.rows,
 4 [SpaceUsed] = CAST(SUM(alloc.used_pages)*8 as FLOAT)/1024
 5 FROM sys.objects obj
 6 JOIN sys.indexes idx on obj.object_id = idx.object_id
 7 JOIN sys.partitions prt on obj.object_id = prt.object_id
 8 JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
 9 WHERE
10 obj.type = 'U' AND idx.index_id IN (0, 1)
11 GROUP BY obj.name, prt.rows
12 ORDER BY SUM(alloc.used_pages)*8 DESC

View Code

③复杂性

 1 CREATE TABLE #tablespaceinfo
 2     (
 3       nameinfo VARCHAR(500) ,
 4       rowsinfo BIGINT ,
 5       reserved VARCHAR(20) ,
 6       datainfo VARCHAR(20) ,
 7       index_size VARCHAR(20) ,
 8       unused VARCHAR(20)
 9     )  
10  
11 DECLARE @tablename VARCHAR(255);  
12  
13 DECLARE Info_cursor CURSOR
14 FOR
15     SELECT  '[' + [name] + ']'
16     FROM    sys.tables
17     WHERE   type = 'U';  
18  
19 OPEN Info_cursor  
20 FETCH NEXT FROM Info_cursor INTO @tablename  
21  
22 WHILE @@FETCH_STATUS = 0
23     BEGIN 
24         INSERT  INTO #tablespaceinfo
25                 EXEC sp_spaceused @tablename  
26         FETCH NEXT FROM Info_cursor  
27     INTO @tablename  
28     END 
29  
30 CLOSE Info_cursor  
31 DEALLOCATE Info_cursor  
32  
33 --创建临时表
34 CREATE TABLE [#tmptb]
35     (
36       TableName VARCHAR(50) ,
37       DataInfo BIGINT ,
38       RowsInfo BIGINT ,
39       Spaceperrow AS ( CASE RowsInfo
40                          WHEN 0 THEN 0
41                          ELSE DataInfo / RowsInfo
42                        END ) PERSISTED
43     )
44 
45 --插入数据到临时表
46 INSERT  INTO [#tmptb]
47         ( [TableName] ,
48           [DataInfo] ,
49           [RowsInfo]
50         )
51         SELECT  [nameinfo] ,
52                 CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
53                 [rowsinfo]
54         FROM    #tablespaceinfo
55         ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC  
56 
57 
58 --汇总记录
59 SELECT  [tbspinfo].* ,
60         [tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'
61 FROM    [#tablespaceinfo] AS tbspinfo ,
62         [#tmptb] AS tmptb
63 WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]
64 ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC  
65 
66 DROP TABLE [#tablespaceinfo]
67 DROP TABLE [#tmptb]

View Code

7.查询患者简称唯一的数据：

SELECT count(*), NamePY FROM koaladb.t_patient 
group by 
NamePY having count(*) = 1;

8.with

use my_school
go

IF EXISTS(select * from sysobjects where name='Payroll')
drop table Payroll
go

create table Payroll (PayrollId int ,departmentId int ,wages decimal(10,2))
go
insert into Payroll values(1,10,5500.00)
insert into Payroll values(2,10,4500.00)
insert into Payroll values(3,20,1900.00)
insert into Payroll values(4,20,4800.00)
insert into Payroll values(5,40,6500.00)
insert into Payroll values(6,40,14500.00)
insert into Payroll values(7,40,44500.00)
insert into Payroll values(8,50,6500.00)
insert into Payroll values(9,50,7500.00)
go

-- 查询dbo.Payroll表莫个部门的最高奖金或者最低奖金
with x as
(
-- departmentId，根据部门编号进行分组排序，wages：根据奖金进行降序排序，rak生成每组内部排序后的顺序编号
SELECT *, Row_Number() OVER (partition by departmentId ORDER BY wages desc) rnk FROM dbo.Payroll
)
select max(rnk) from x where departmentId='10'
go

-- 查询dbo.Payroll表中的最高奖金
with x as
(
-- departmentId，根据部门编号进行分组排序，wages：根据奖金进行降序排序，rak生成每组内部排序后的顺序编号
SELECT *, Row_Number() OVER (partition by departmentId ORDER BY wages desc) rnk FROM dbo.Payroll
)
select max(rnk) from x
go

View Code

9.REPLACE()函数

--以xx替换abcdsdsajk中的‘cd’
select replace('abcdsdsajk','cd','xx')GO
-- 以x替换123345中的3
select replace('123345','3','x')GO
--以JPG替换dbo.ImageSouce表中ImageSouce字段下的所有jpg
select REPLACE(ImageSouce,'jpg','JPG') from dbo.ImageSouce

View Code

10.删除数据库中所有表保留指定表

declare @tablename NVARCHAR(100)
declare drop_tab cursor for
SELECT  b.name 
FROM    sysindexes AS a
        INNER JOIN sys.tables AS b ON b.object_id = a.id
WHERE   indid IN ( 0, 1 );
open drop_tab
fetch next from drop_tab into @tablename
while (@@fetch_status=0)
  begin
    IF(@tablename<>'影相表') --排除个别表
    BEGIN
    EXEC('drop table '+@tablename)
    END
    fetch next from drop_tab into @tablename
  end
close drop_tab
deallocate drop_tab
go

View Code

11.以一个表的数据更新另外一个表字段数据

# p 更新的表
update p
set p.patientID=isnull(p1.patientID,''),
p.Name=p1.Name,
p.Mobile=p1.Mobile
from user p
inner join pat p1
on p.ID=p1.ID

# p 更新的表
update p
set p.patientID=isnull(p1.PatientID,''),p.PatientName=p1.PatientName,p.Telephone=p1.Telephone
from t_patient as p,[dbo].[pat] as p1
where p.PatientIdentity=p1.PatientIdentity

View Code

12.执行sql脚本生成数据库：

-- 在执行sql前创建一个跟备份的数据库名字一样的数据库再执行sql
osql -S 127.0.0.1 -U sa -P sa -i d:\test.sql

View Code

13.sql分页节流

# order by 必须有
select * from tableName order by id offset 0 rows fetch next 500 rows only

View Code

14.清空表中数据

①.常用方式

# 常用
delete from #tblDelete;

# 快速方式
truncate table #tblDelete;

View Code

②.清除所有表的数据，保留数据表之间的外键约束，重置数据自增（id=1）

if( object_id('pr_DataClear') is not null )  
    drop procedure pr_DataClear  
go  
create procedure pr_DataClear  
as  
begin transaction  
    declare @cTblName varchar(128)  
    declare cur_Clear cursor for select rtrim(name) from sysobjects where type = 'U' order by crdate desc  
    open cur_Clear  
    declare @cSQL varchar(255)  
    fetch next from cur_Clear into @cTblName  
    while( @@fetch_status = 0)  
    begin  
        set @cSQL = 'delete from ' + @cTblName  
        print @cSQL  
        exec( @cSQL )  
        if( ident_seed(@cTblName) is not null )  
        begin  
            dbcc checkident( @cTblName, reseed, 0 )  
            print '有种子且成功重置为1'  
        end  
        fetch next from cur_Clear into @cTblName  
    end  
    close cur_Clear  
    deallocate cur_Clear  
commit  
go  
-- 执行存储过程，清空所有表数据  
exec pr_DataClear  


-- 数据库收缩
dbcc shrinkdatabase(数据库)  
dbcc updateusage(数据库)  

-- 截断日志 （2008不支持）
backup log 数据库 with no_log  

ALTER DATABASE 数据库 set recovery simple
go
DBCC SHRINKFILE('数据库日志逻辑名称',0,TRUNCATEONLY)
GO
ALTER DATABASE 数据库 SET RECOVERY FULL
GO

-- 查看表空间(概数)  
select object_name(id) as 表名, (rtrim(8*reserved/1024) + 'MB') as 总量, (rtrim(8*dpages/1024) + 'MB') as 已使用,  
    (rtrim(8*(reserved-dpages)/1024) + 'MB') as 未使用, (rtrim(8*dpages/1024-rows/1024*minlen/1024) + 'MB' ) as 空隙  
    from sysindexes  
    where indid=1  
    order by reserved desc 
go

--博客：https://bbs.csdn.net/topics/310228785

View Code

15.根据主键查出重复的数据进行删除

-- 查询是否有重复的数据
select count(*),billidentity from Order_List group by billidentity having  count(*)>1

-- 根据主键进行删除
with x as
(
select*,ROW_NUMBER() over(partition by payidentity order by paydate desc) as rnk from [dbo].[PayHistory]
)
delete from x where rnk>1

View Code

16.Row_Number() OVER

use my_school
go

IF EXISTS(select * from sysobjects where name='Payroll')
drop table Payroll
go

create table Payroll (PayrollId int ,departmentId int ,wages decimal(10,2))
go
insert into Payroll values(1,10,5500.00)
insert into Payroll values(2,10,4500.00)
insert into Payroll values(3,20,1900.00)
insert into Payroll values(4,20,4800.00)
insert into Payroll values(5,40,6500.00)
insert into Payroll values(6,40,14500.00)
insert into Payroll values(7,40,44500.00)
insert into Payroll values(8,50,6500.00)
insert into Payroll values(9,50,7500.00)
go


-- 需求：根据部门分组，显示每个部门的工资等级
-- departmentId，wages：根据部门编号进行分组排序，rak生成每组内部排序后的顺序编号
SELECT *, Row_Number() OVER (partition by departmentId ORDER BY wages desc) rank FROM dbo.Payroll

View Code

17.拼接删除数据库语句

-- 批量生成删除数据库语句
select 'drop database ' +name from master..sysdatabases

-- 批量生成查询数据下所有表语句
select 'select * from ' + name from sysobjects  where xtype='U'

select 'select * from ' + a.name from (select a.name,b.rows from  sysobjects as a join sysindexes as b
on a.id=b.id
where (a.type='U') and (indid in(0,1)) and b.rows>0) a

18. 查询，实现相同id的name以，分隔拼成字符串，id和value都为字段名称（类似mysql中的GROUP_CONCAT()函数）

use my_school
go

IF EXISTS(select * from sysobjects where name='Temp')
drop table Temp
go

create table Temp(Id int ,Price varchar(10))
go

-- 插入测试数据
INSERT INTO temp VALUES(1, 'AA'); 
INSERT INTO temp VALUES(1, 'BB'); 
INSERT INTO temp VALUES(2, 'CC'); 
INSERT INTO temp VALUES(2, 'DD'); 
INSERT INTO temp VALUES(2, 'FF');
go

-- 查询，实现相同id的name以，分隔拼成字符串，id和value都为字段名称
select Id,
       [Price]=STUFF((
       select ',' + [Price] from temp t where t.Id=temp.Id 
                            FOR XML path('')),1 ,1 ,''
                            
                    )

from temp
group by Id

View Code

19.开启远程查询支持

exec sp_configure 'show advanced options' ,1
reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

View Code

20.查询表的外键（依赖的表）：清空莫个表数据时候存在依赖时无法进行truncate

-- 查询Manager表的外键
select name 
from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id 
where f.parent_object_id=object_id('dbo.Manager')

--删除对应的外键
ALTER TABLE dbo.Manager DROP constraint FK_MANAGER_RELATIONS_MANAGERR

View Code

21.使用虚表来游动数据

-- 原数据表：t_Count
-- 虚表：table1
-- 将原数据集清空保留部分数据
--保留的数据保存进虚表table1中
select * into table table1 from t_Count where id<501

--清空t_Count表
truncate table t_Count

-- 将虚表的数据重新写入t_Count中
inset into t_Count select Name，Age，Gender，Class from table1

-- 为了防止错误：
--An explicit value for the identity column in table 'dbo.t_Count' can only be specified when a column list is used and IDENTITY_INSERT is ON.

--在重新写入虚表数据时候把对应的列加上即可（主键忽略）

View Code

22.将表中莫个字段存在的大写字符转化为小写

UPDATE pathpict  SET Fname=LOWER(Fname)

-- 大写 Upper

23.获取当天时间段的数据

select top 20 * from [dbo].[Log] as s where CONVERT(date,GETDATE())=CONVERT(date, s.Date) order by id ASC

24.外键约束数据不存在查询

-- 查询a表与b表外键相连而不存在外键的数据
select * from a 
where  not exists (select CodID from b where b.CodID= a.CodID)

25.PATINDEX()函数与ISNUMERIC()函数

26.sqlserver中update接子查询语句

-- 同表下更新status状态
update a set a.Status=0 from MTM_UDS_BAK a inner join (select Id from MTM_UDS_BAK where Id=1061405)b
on a.ID=b.ID

27.merge更新

--创建测试数据库
create table tap1
    (    id int ,  name varchar(20) );
go

create table tap2
    (  id int ,  name varchar(20) );
go

insert into tap1( id, name ) values ( 1, 'a' ), ( 2, null ), ( 3, 'c' ), ( 4, 'd' ), ( 5, 'e' );
insert into tap2( id, name ) values ( 1, 'a1' ), ( 2, 'b1' ), ( 3, 'c1' );


--merge更新
merge tap1 a --要更新的目标表 
    using tap2 b --源表 
    on a.id = b.id and a.name is null--更新条件（即主键）
when matched --如果匹配，更新 
    then update set a.name = b.name
when not matched 
    -- insert后不接表名
    then insert（id, name） values ( 2, 'b' ); --如果两个条件都不匹配，插入。此语句必须以分号结束

View Code

28.查看指定数据库中表的字段属性

select a.name 表名,b.name 字段 ,c.name 属性,c.length 长度 from sysobjects a, syscolumns b, systypes c 
where
a.id=b.id and a.name='Goods' and a.xtype='U' and b.xtype=c.xtype

posted on 2018-12-14 09:58 ArSang-Blog 阅读(371) 评论(0) 编辑收藏举报