数据库语句积累

上次更新: 2024-12-27 16:42:38

数据库语句积累

主键	单选
SQL SERVER SQL积累
MYSQL 积累
MYSQL数据库安装

‍

SQL SERVER SQL 积累[^1]

权限相关

查询数据库用户

SELECT name AS UserName
FROM sys.database_principals
WHERE type = 'S';

查询特定用户权限

EXEC sp_helprotect @username = 'your_username';

查询数据库中所有用户及其权限

时间相关

SQL 时间判断优化写法

and b.c_effect_dt >= @开始日期
and b.c_effect_dt <dateadd(day, 1, @结束日期)

CONVERT() 时间格式化

SqlSERVER 中将 datetime 转换成字符串的方法(CONVERT) - 离。 - 博客园 (cnblogs.com)

一、CONVERT ([ length ]， <expression[， style])
二、这里注重说明一下 style 的含义：style 是将 DATATIME 和 SMALLDATETIME 数据转换为字符串时所选用的由 SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式；一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到.
三、下表是 SQL Server 系统提供的转换样式编号：

表中‘ ‘表示的含义说明： * 默认值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始终返回世纪数位 (yyyy)。 * 当转换为 datetime 时输入；当转换为字符数据时输出。* ** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换，输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换，输出等同于 style 2。对于从 rea到 character 数据的转换，输出等同于 style 1。
Hijri 是具有几种变化形式的日历系统，Microsoft? SQL Server? 2000 使用其中的科威特算法。
四、不带世纪数位的实例代码（注释的表示非有效的样式号）

SELECT CONVERT(varchar(100), GETDATE(), 0) AS Style0 
SELECT CONVERT(varchar(100), GETDATE(), 1) AS Style1 
SELECT CONVERT(varchar(100), GETDATE(), 2) AS Style2 
SELECT CONVERT(varchar(100), GETDATE(), 3) AS Style3 
SELECT CONVERT(varchar(100), GETDATE(), 4) AS Style4 
SELECT CONVERT(varchar(100), GETDATE(), 5) AS Style5 
SELECT CONVERT(varchar(100), GETDATE(), 6) AS Style6 
SELECT CONVERT(varchar(100), GETDATE(), 7) AS Style7 
SELECT CONVERT(varchar(100), GETDATE(), 8) AS Style8 
SELECT CONVERT(varchar(100), GETDATE(), 9) AS Style9 
SELECT CONVERT(varchar(100), GETDATE(), 10) AS Style10 
SELECT CONVERT(varchar(100), GETDATE(), 11) AS Style11 
SELECT CONVERT(varchar(100), GETDATE(), 12) AS Style12 
SELECT CONVERT(varchar(100), GETDATE(), 13) AS Style13 
SELECT CONVERT(varchar(100), GETDATE(), 14) AS Style14 
--SELECT CONVERT(varchar(100), GETDATE(), 15) AS Style15 
--SELECT CONVERT(varchar(100), GETDATE(), 16) AS Style16 
--SELECT CONVERT(varchar(100), GETDATE(), 17) AS Style17 
--SELECT CONVERT(varchar(100), GETDATE(), 18) AS Style18 
--SELECT CONVERT(varchar(100), GETDATE(), 19) AS Style19 
SELECT CONVERT(varchar(100), GETDATE(), 20) AS Style21 
SELECT CONVERT(varchar(100), GETDATE(), 21) AS Style21 
SELECT CONVERT(varchar(100), GETDATE(), 22) AS Style22 
SELECT CONVERT(varchar(100), GETDATE(), 23) AS Style23 
SELECT CONVERT(varchar(100), GETDATE(), 24) AS Style24 
SELECT CONVERT(varchar(100), GETDATE(), 25) AS Style25 
--SELECT CONVERT(varchar(100), GETDATE(), 26) AS Style26 
--SELECT CONVERT(varchar(100), GETDATE(), 27) AS Style27 
--SELECT CONVERT(varchar(100), GETDATE(), 28) AS Style28 
--SELECT CONVERT(varchar(100), GETDATE(), 29) AS Style29 
--SELECT CONVERT(varchar(100), GETDATE(), 30) AS Style30 
--SELECT CONVERT(varchar(100), GETDATE(), 31) AS Style31

五、带世纪数位的实例代码（注释的表示非有效的样式号）：

SELECT CONVERT(varchar(100), GETDATE(), 100) AS Style100 
SELECT CONVERT(varchar(100), GETDATE(), 101) AS Style101 
SELECT CONVERT(varchar(100), GETDATE(), 102) AS Style102 
SELECT CONVERT(varchar(100), GETDATE(), 103) AS Style103 
SELECT CONVERT(varchar(100), GETDATE(), 104) AS Style104 
SELECT CONVERT(varchar(100), GETDATE(), 105) AS Style105 
SELECT CONVERT(varchar(100), GETDATE(), 106) AS Style106 
SELECT CONVERT(varchar(100), GETDATE(), 107) AS Style107 
SELECT CONVERT(varchar(100), GETDATE(), 108) AS Style108 
SELECT CONVERT(varchar(100), GETDATE(), 109) AS Style109 
SELECT CONVERT(varchar(100), GETDATE(), 110) AS Style110 
SELECT CONVERT(varchar(100), GETDATE(), 111) AS Style111 
SELECT CONVERT(varchar(100), GETDATE(), 112) AS Style112 
SELECT CONVERT(varchar(100), GETDATE(), 113) AS Style113 
SELECT CONVERT(varchar(100), GETDATE(), 114) AS Style114 
--SELECT CONVERT(varchar(100), GETDATE(), 115) AS Style115 
--SELECT CONVERT(varchar(100), GETDATE(), 116) AS Style116 
--SELECT CONVERT(varchar(100), GETDATE(), 117) AS Style117 
--SELECT CONVERT(varchar(100), GETDATE(), 118) AS Style118 
--SELECT CONVERT(varchar(100), GETDATE(), 119) AS Style119 
SELECT CONVERT(varchar(100), GETDATE(), 120) AS Style121 
SELECT CONVERT(varchar(100), GETDATE(), 121) AS Style121 
--SELECT CONVERT(varchar(100), GETDATE(), 122) AS Style122 
--SELECT CONVERT(varchar(100), GETDATE(), 123) AS Style123 
--SELECT CONVERT(varchar(100), GETDATE(), 124) AS Style124 
--SELECT CONVERT(varchar(100), GETDATE(), 125) AS Style125 
SELECT CONVERT(varchar(100), GETDATE(), 126) AS Style126 
SELECT CONVERT(varchar(100), GETDATE(), 127) AS Style127 
--SELECT CONVERT(varchar(100), GETDATE(), 128) AS Style128 
--SELECT CONVERT(varchar(100), GETDATE(), 129) AS Style129 
SELECT CONVERT(varchar(100), GETDATE(), 130) AS Style130 
SELECT CONVERT(varchar(100), GETDATE(), 131) AS Style131

SQL 将 datetime 转化为字符串并截取字符串

select sr_child as '孩子姓名', sr_parents as '家长姓名' ,ss_updatetime as '分配时间', left(ss_updatetime,CHARINDEX(' ',ss_updatetime)-1),SUBSTRING(CONVERT(CHAR(19), ss_updatetime, 120),1,10)as '转换格式并截取后的时间'from dbo.tb_sell_resources,dbo.tb_sell_selldetails where sr_id = ss_rsid and ss_qdstate <1 order by ss_updatetime

CONVERT(VARCHAR(10),a.c_dt, 23)

技巧

去重

SELECT *  FROM (
    SELECT
        c_store_id,
        c_id,
        c_type,
        ROW_NUMBER() OVER (PARTITION BY c_store_id,c_id ORDER BY c_type ASC) AS rn
    FROM
        #ttb_ticket
) AS subquery
WHERE rn = 1

sp_help 表名 —-查看表结构
sp_helptext 存储过程名 —-查看存储过程的定义语句
sp_helptext 函数名 —-查看函数的定义语句
sp_helptext 触发器名 —-查看触发器的定义语句

日常很实用的语句比如：
select * from sysobjects WHERE name LIKE ‘%stu%’ —检索名字包含 stu 的所有对象；
select * from sys.tables where name like … —检索表名
select * from sys.views where name like … —检索视图名
select * from sys.procedures order by modify_date desc –按修改日期倒序查询数据库里的所有存储过程
select object_name(id) from syscomments where text like ‘% 测试 %’
—-查询定义语句里包含“测试”两个字的所有对象名；
这里 object_name 是一个系统内置函数，返回的是对象名。

SQL Server 提供了大量预制的存储过程，这些存储过程用于执行各种数据库管理任务。以下是一些常见的系统存储过程类别及其用途：

信息架构视图：如 sp_columns, sp_tables 等，它们返回关于数据库对象（例如表、列）的信息。
配置和状态信息：如 sp_configure, xp_msver 等，用来查看或设置服务器配置参数。
安全性相关：如 sp_addlogin, sp_grantdbaccess 等，用于用户权限管理和角色分配。
维护和管理任务：如 sp_updatestats, sp_recompile 等，帮助进行索引重建、统计更新等操作。
备份与恢复：如 xp_backupdatabase, xp_restorehistory 等，用于数据备份和历史记录查询。
诊断工具：如 sp_who2, xp_logininfo 等，提供当前活动会话和登录账户的信息。
动态管理视图和函数（DMVs 和 DMFs）: 如 sys.dm_exec_requests, sys.dm_os_host_info 等，提供了运行时的内部状态信息。
扩展存储过程（XP 开头）: 如 xp_cmdshel(已在某些版本中弃用) ，允许从 SQL Server 中执行操作系统命令。
服务代理相关的存储过程：如 sp_send_dbmai发送邮件通知；
全文搜索相关的存储过程：比如 sp_fulltext_database、sp_help_fulltext_catalogs 等等。
请注意，在不同版本的 SQL Server 中可用的预制存储过程可能会有所不同。此外，出于安全考虑，默认情况下某些扩展存储过程可能被禁用。在使用任何系统存储过程之前，请确保了解其功能并检查是否符合你的使用场景。

根据注释查询数据库中的表

SELECT t.name AS TableName, ep.value AS TableComment FROM sys.tables t JOIN sys.extended_properties ep ON t.object_id = ep.major_id WHERE ep.minor_id = 0 AND ep.name = N'MS_Description' AND t.name = 'YourTableName';

事务相关

开始一个新的事务：BEGIN TRANSACTION;
执行一系列数据库操作（例如：INSERT、UPDATE、DELETE）。
如果一切正常，提交事务：COMMIT TRANSACTION;
如果遇到错误或者需要撤销某些更改，可以回滚事务：ROLLBACK TRANSACTION;
此外，在编写存储过程或触发器时，也可以使用 TRY…CATCH 结构来自动处理异常并回滚事务：

BEGIN TRY
    BEGIN TRANSACTION;
    -- 数据库操作...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- 这里可以记录日志或者抛出自定义的错误信息等...
END CATCH

访问其他数据库

在 SQL Server 中访问其他数据库的数据可以通过几种不同的方法实现。如果你提到的是从一个 SQL Server 数据库访问另一个 SQL Server 数据库中的数据，可以使用以下几种方式：

链接服务器（Linked Servers）
- 链接服务器允许你在当前数据库中定义一个指向其他数据库或不同类型的数据库系统的连接。
- 一旦链接服务器创建好，你可以使用 OPENQUERY 函数来执行远程查询。
四部分命名（Four-Part Names）
- 如果两个数据库位于同一服务器实例上，并且你有足够的权限，可以直接使用四部分命名来引用对象。
- 例如：<服务器名>.<数据库名>.<架构名>.<表名>。
  LEFT JOIN zlhq_srv.enjoy_lg_mall.dbo.V_wms_tb_store s

这看起来像是使用了四部分命名的方式访问另一个数据库中的视图。假设 zlhq_srv 是服务器名称，enjoy_lg_mall 是数据库名称，dbo 是模式名称，而 V_wms_tb_store 是视图名称。

如果这是你想要实现的效果，请确保：

当前用户有权限访问目标数据库。
服务器名正确无误，并且两个数据库之间网络可达。

链接数据库

如果是在尝试设置链接服务器，请先运行类似下面的 T-SQL 命令来创建链接服务器：

EXEC master.dbo.sp_addlinkedserver
@server = N’zlhq_srv’, – 链接服务器名
@srvproduct = N’SQL Server’, – 目标服务器产品名
@provider = N’SQLNCLI’, – 提供者
@datasrc = N’< 目标服务器 IP 或名称 >’; – 目标服务器地址

请注意，上述示例中的具体细节如服务器名、数据库名等都需要根据实际情况调整。

查看 SQL Server 中的链接服务器配置

可以使用以下步骤：

通过 SQL Server Management Studio (SSMS) 查看：
- 打开 SSMS 并连接到您的 SQL Server 实例。
- 在对象资源管理器中，展开“服务器对象”节点。
- 展开“链接服务器”节点，您将看到所有已配置的链接服务器。

通过 T-SQL 查询查看： 您可以使用系统目录视图或存储过程来查询链接服务器的详细信息。以下是几个常用的查询示例：
sql

复制代码

-- 查看所有链接服务器的基本信息
SELECT * FROM sys.servers;
-- 查看特定链接服务器的详细信息
EXEC sp_helpserver '链接服务器名称';
-- 查看链接服务器的登录映射
SELECT * FROM sys.linked_logins WHERE server_id = (SELECT server_id FROM sys.servers WHERE name = '链接服务器名称');
-- 查看链接服务器的安全上下文
SELECT * FROM sys.server_principals WHERE type = 'S' AND name LIKE '%链接服务器名称%';

业务相关

进销存取期末数，取小于等于结束日期的最大日期的库存

取期初数，取小于开始日期的最大日期的库存

取发生数数，取开始日期与结束日期之间的汇总数

‍

分析 SQL 查询效率 #Points#

分析 SQL 查询效率通常涉及以下几个方面：

查看查询计划：使用 EXPLAIN 或 EXPLAIN ANALYZE 来查看数据库是如何执行查询的。
优化索引：确保合适的索引被使用来加快查询速度。
查询优化：简化查询，避免不必要的计算和数据检索。
分析运行时间：记录查询的运行时间，找出慢查询。
使用缓存：配置合适的缓存策略。
硬件资源：提升硬件性能，如增加内存、使用更快的硬盘。
数据库配置：调整数据库配置参数，如缓冲区大小、连接数等。

分析 SQL Server 查询效率

分析 SQL Server 查询效率的一般步骤如下：

使用 SQL Server Management Studio (SSMS) 连接到数据库。
执行您的 SQL 查询。
查看查询计划（Query Plan）：
在查询结果窗口中，点击“包含实际执行计划”（Display Estimated Execution Plan）图标。
或者，您可以使用 SET SHOWPLAN_ALL ON 在查询分析器中显示查询计划而不执行查询。
分析查询计划：
查看操作的类型（如扫描、联接、排序等）。
评估操作对资源的使用情况（如 I/O 和 CPU）。
查看是否有不需要的书签查找器（bookmark lookups）或不适当的排序操作。
如果需要，使用 SET STATISTICS IO ON 和 SET STATISTICS TIME ON 来获取更多查询执行的统计信息。
根据查询计划和统计信息进行优化：
更新统计信息。
使用索引提高查询的效率。
重写查询以提高效率。
如果必要，可以重构数据库架构或查询。
示例代码：

-- 查看查询计划但不执行查询
SET SHOWPLAN_ALL ON;
GO
-- 执行您的查询
SELECT * FROM your_table WHERE your_column = 'your_value';
-- 关闭显示查询计划的设置
SET SHOWPLAN_ALL OFF;
GO
-- 查看IO和CPU统计信息
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- 执行查询
SELECT * FROM your_table WHERE your_column = 'your_value';
-- 关闭统计信息设置
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

请注意，优化查询时应该在了解业务逻辑和数据的前提下进行，避免对数据库性能产生不必要的负面影响。

游标使用

在 SQL 中，FETCH NEXT FROM cur INTO @cid 是用来从游标 cur 中获取下一行数据，并将其存储到变量 @cid 中。而 WHILE @@FETCH_STATUS=0 则是用来检查是否还有更多的行可以被提取。
以下是使用这些语句的完整示例：

DECLARE done INT;
DECLARE cid INT;
-- 声明并打开游标
DECLARE cur CURSOR FOR SELECT column_name FROM table_name WHERE condition;
OPEN cur;
-- 初始化完成标志为 0（未完成）
SET done = 0;
-- 获取第一行数据
FETCH NEXT FROM cur INTO @cid;
-- 使用循环遍历所有结果
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 在这里处理每行数据
    -- 获取下一行数据
    FETCH NEXT FROM cur INTO @cid;
END
-- 关闭和释放游标资源
CLOSE cur;
DEALLOCATE cur;

在这个例子中：

done 是一个用于控制循环结束的标志。
cid 是用来存储当前行的数据列值的变量。
开始时通过 OPEN 打开游标，然后进入一个循环，在每次迭代中用 FETCH NEXT FROM ... INTO ...; 提取下一行数据。
如果没有更多行可提取，则 @@FETCH_STATUS 的值为 -1，此时退出循环。
最后关闭并释放游标的资源。

#待确认问题#

‍

# 编程

# 数据库

在路上 🦕

数据库语句积累

数据库语句积累

SQL SERVER SQL 积累[^1]

权限相关

查询数据库用户

查询特定用户权限

查询数据库中所有用户及其权限

时间相关

SQL 时间判断 优化写法

CONVERT() 时间格式化

技巧

去重

根据注释查询数据库中的表

事务相关

访问其他数据库

链接数据库

查看 SQL Server 中的链接服务器配置

业务相关

进销存取期末数，取小于等于结束日期的最大日期的库存

取期初数，取小于开始日期的最大日期的库存

取发生数数，取开始日期与结束日期之间的汇总数

分析 SQL 查询效率 #Points#

分析 SQL Server 查询效率

游标使用

SQL 时间判断优化写法