1、查询表中image类型字段的文本值
----将此字段进行转换后查询
select CAST(CAST(content AS VARBINARY(MAX)) AS VARCHAR(MAX)) from kk_Attachs
----查询某个字符在字符串中的位置,如下是查询在content字段中/的位置,从左往右取第一个
select CHARINDEX('/',content)
----查询某个字符在字符串中的位置,取最后一个。reverse是反转的意思,意味取最后一个
select CHARINDEX('/',reverse(content))
----substring函数截取长度。这里第一个参数是字段名,第二个是起始位置,第三个是要取的长度
substring(content,0,len(content)-2)
2、模糊查询时,区分大小写
-----查询语句后加上 collate Chinese_PRC_CS_AI
select * from kk_lib where shenfenzh like '%x' collate Chinese_PRC_CS_AI
3、分组后对各列进行拼接
select code,kk_lib.name,kk_lib.PWGuid from kk_lib ,(select PWGuid,stuff(
(select ',' + ZY_Code from kk_Zy where PWGuid = ppwz.PWGuid for xml path(''))
,1,1,'') as code from kk_Zy ppwz group by PWGuid) t where kk_lib.PWGuid =t.PWGuid
4、取排名的数据(考虑并列),使用 DENSE_RANK() OVER ( ORDER BY aa.cishu DESC ) paiming
select (select name from kk_userinfo where userguid=bb.userguid) '名称',bb.cishu '缴纳次数', bb.paiming '缴纳次数排名'
from (
select aa.userguid ,aa.cishu ,DENSE_RANK() OVER ( ORDER BY aa.cishu DESC ) paiming
from (
select userguid,count(*) cishu from kk_tbuser where bdguid in(select bdguid from cg_bdinfo where
userdate between '2021-04-01 00:00:00' and '2021-09-30 23:59:59') and type='现金缴纳' group by userguid
) aa ) bb where bb.paiming < 51 order by bb.paiming asc
5、查看数据库日志空间
dbcc sqlperf(logspace)
6、查询身份证提取日期
select name,mobile,ShengFenZH,
case when len(ShengFenZH)=15 then
'19' + SUBSTRING(ShengFenZH,7,2) + '-' + SUBSTRING(ShengFenZH,9,2) + '-' + SUBSTRING(ShengFenZH,11,2)
else SUBSTRING(ShengFenZH,7,4) + '-' + SUBSTRING(ShengFenZH,11,2) + '-' + SUBSTRING(ShengFenZH,13,2)
end birthday from kk_user
7、查询各表结构的大小
### 创建一个存储
CREATE PROCEDURE [dbo].[sys_viewTableSpace]
ASBEGIN
SET NOCOUNT ON;
CREATE TABLE [dbo].#tableinfo(
表名 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
记录数 [int] NULL,
预留空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
使用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
索引占用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
未用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
)insert into #tableinfo(表名, 记录数, 预留空间, 使用空间, 索引占用空间, 未用空间)
exec sp_MSforeachtable "exec sp_spaceused '?'"select * from #tableinfo
order by 记录数 descdrop table #tableinfo
END
### 使用的时候直接执行这个存储过程
execute sys_viewTableSpace
8、查询日期字段的年份或月份
----截取年份,得到的结果如2022
datepart(yy,getdate())
---截取月份,得到的结果如11
datepart(mm,getdate())
9、分组后排序取第一条数据
select num,RowGuid,* from(select *,ROW_NUMBER() over(partition by bdguid order by num desc) as new_index from jieguo) a where a.new_index=1
10、连接不同服务器的数据库进行关联查询
----1、在当前系统上创建一个数据库连接,连到新系统数据库,方便后面查询数据
EXEC sp_addlinkedserver
@server = N'MyLinkedServerNewSys',
@srvproduct=N'SqlServer',
@provider=N'SQLOLEDB',
@datasrc=N'10.180.3.153', --服务器IP
@catalog = N'Test1' --数据库名----2、查看是否有这种连接(这一步非必须,只是介绍下可以通过这种方式查看数据)
select * from sys.servers;---3、建立连接
EXEC sp_addlinkedsrvlogin
'MyLinkedServerNewSys', --被访问的服务器别名(与1里面的server名称一致)
'false',
NULL,
'EpointSA', --帐号
'Epoint_xy2020' --密码---4、具体的查询
select * from [MyLinkedServerNewSys].[Test1].dbo.table1
---5、关联其他表时,按常规的查询即可
select name from table2 where rowguid in(select guid from [MyLinkedServerNewSys].[Test1].dbo.table1 )
11、查询数据库中所有表单的数据量
SELECT ROW_NUMBER() OVER (ORDER BY All_Table.NAME) AS NO,
All_Table.NAME AS '表名(EN)',
CONVERT(NVARCHAR(100),ISNULL(TableDesc.[VALUE],'-')) AS '表名(CN)',
TabIndex.ROWS AS '记录数'
FROM SYS.TABLES All_Table
LEFT JOIN SYS.EXTENDED_PROPERTIES TableDesc ON (All_Table.OBJECT_ID = TableDesc.MAJOR_ID AND TableDesc.MINOR_ID = 0)--取表的名称两个条件
INNER JOIN SYSINDEXES TabIndex ON All_Table.OBJECT_ID=TabIndex.ID AND TabIndex.INDID < 2 WHERE 1=1 ORDER BY TabIndex.ROWS DESC