Sql常用sql语句收藏夹

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

1.查看数据库连接数据

SHOW PROCESSLIST

2.查看当前数据库版本

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

# 查看所有数据库
select name from master..sysdatabases

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

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

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

select count(*),id from receipt group by id having count(*)>1;

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.PATINDEX()函数与ISNUMERIC()函数

--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go

Create table #T([coent] nvarchar(22))
Insert #T
        SELECT '5' UNION
        SELECT '13,50.00' UNION
        SELECT '￥1500' UNION        
        SELECT '$88' UNION
        SELECT '+1'
Go
--测试数据结束

--判断字段是不是数值型字段
--ISNUMERIC()函数

SELECT  *
FROM #T
WHERE  ISNUMERIC(#T.coent) = 1
go

--查询导入的乱码数据
select a.PatientIdentity,a.PatientID,a.PatientName  from t_patient AS a 
where a.PatientID<>'*(无比例号)' and  ISNUMERIC(a.PatientID)=0
go

--PATINDEX()函数
SELECT  *
FROM    #T
WHERE   PATINDEX('%[^0-9]%', #T.coent) = 0
go

--PATINDEX()函数
SELECT  *
FROM    #T
WHERE   PATINDEX('%[^0-9|.|-|+|.$|]%', #T.coent) = 0
go

View Code

 9.REPLACE()函数

--以xx替换abcdsdsajk中的‘cd’
select replace('abcdsdsajk','cd','xx')GO
-- 以x替换123345中的3
select replace('123345','3','x')GO

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

# 
update p
set p.patientID=isnull(p1.patientID,''),
p.Name=p1.Name,
p.Mobile=p1.Mobile
from user as p,pat as p1
where p.ID=p1.ID

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

 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

 

转载于:https://www.cnblogs.com/yangzhuanzheng/articles/10117844.html