DDL代表数据定义语言，是一种有助于创建数据库模式的SQL命令。而，DML代表数据操作语言

DDL

创建数据库与日志

CREATE DATABASE Students --数据库名称
ON
(
    NAME='Students',--数据库名称
    FILENAME='d:\Students.MDB',--数据库文件存储位置
    SIZE=20MB,--数据库初始大小
    FILEGROWTH=2MB,--数据库增量
    MAXSIZE=100MB--数据库最大容量
)
LOG ON
(
    NAME='Students_LOG',--数据库日志文件名
    FILENAME='d:\Students_LOG.MDL',--日志存储位置
    SIZE=20MB,--日志默认大小
    FILEGROWTH=2MB,--日志文件增量
    MAXSIZE=100MB--日志文件最大大小    
)

创建表

CREATE TABLE StuInfo
(
    Id INT IDENTITY(1,1) PRIMARY KEY,--主键，自增，自增量为1
    StuNo NVARCHAR(5),
    StuName NVARCHAR(10) NOT NULL,
    StuSex CHAR(2) NOT NULL,
    StuAge INT NOT NULL,
    StuSeat INT NOT NULL
)

修改字段

Alter Table  [DBName].[dbo].[TableName]  ALTER COLUMN  ColumnName  nvarchar(1000)

增加字段

ALTER TABLE test ADD phone varchar(30)

修改表字段排序规则

ALTER TABLE dbo.MyTable ALTER COLUMN MyString VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8;

删除约束

alter table Laser_Cogradient_Faile  drop constraint DF__Laser_Cog__IpAdd__1DB135A6

删除字段

ALTER TABLE Laser_Cogradient_Faile DROP COLUMN IpAdd

添加主键约束

alter table 表名 add constraint 约束名 primary key(列名)

添加唯一约束

alter table 表名 add constraint 约束名 unique(列名)

添加默认约束

alter table 表名 add constraint 约束名 unique(列名)

添加外键约束

alter table 表名 add constraint 约束名 foreign key(列名) references 关联的表名(关联的列名，一般是主键)

DML

查询

未整理

SQL SERVER 判断字段是否包含非时间
方法一：使用ISDATE函数

SELECT *FROM your_tableWHERE ISDATE(your_column) = 0;

方法二：使用TRY_CONVERT函数

SELECT *FROM your_tableWHERE TRY_CONVERT(datetime, your_column) IS NULL;

特殊查询

树形结构查询

一般部门与菜单之类的数据存在父子级关系，通过一个关系查询父子级数据

WITH Tree
 AS(
 SELECT Id,DepaName,ParentId,Cdt FROM Depa (NOLOCK) WHERE DepaName='IT部'
 UNION ALL
 SELECT tab.Id,tab.DepaName,tab.ParentId,tab.Cdt  FROM Depa tab (NOLOCK) INNER JOIN Tree ON tab.ParentId=Tree.Id
 )
 SELECT * FROM Tree

多参数模糊查询

在开发过程中可能遇到多参数模糊查询的情况

• 通过代码拼接 ( field like '%AA%' OR field like '%BB%')
• 通过表与 [EXISTS](#运算符###### EXISTS)运算符+LIKE函数
• 通过表与 [EXISTS](#运算符###### EXISTS)运算符+CHARINDEX函数
• 示例如下

  --被模糊匹配表
  DECLARE @Tb1 TABLE 
  (
  VAL VARCHAR(30)
  );
  INSERT @Tb1 VALUES('我爱学习！');
  INSERT @Tb1 VALUES('我爱工作!');
  INSERT @Tb1 VALUES('我爱自然!');
  INSERT @Tb1 VALUES('我爱祖国!');
  --匹配参数表
  DECLARE @Tb2 TABLE 
  (
  VAL VARCHAR(30)
  );
  INSERT @Tb2 VALUES('学习');
  INSERT @Tb2 VALUES('工作');
  INSERT @Tb2 VALUES('自然');
  --字符串包含模式模糊匹配
  SELECT VAL FROM @Tb1 T1 WHERE 
  EXISTS(SELECT 1 FROM @Tb2 T2 WHERE 
  CHARINDEX(T2.VAL,T1.VAL)>0
  )
  --LIKE查询模式模糊匹配
  SELECT VAL FROM @Tb1 T1 WHERE 
  EXISTS(SELECT 1 FROM @Tb2 T2 WHERE 
  T1.VAL LIKE '%'+T2.VAL+'%'
  )

连表 (JOIN)

视图(VIEW)

T-SQL

存储过程(PROCEDURE)

运算符

EXISTS

EXISTS 运算符是一个逻辑运算符，用于检查子查询是否返回任何行。 如果子查询返回一行或多行，则EXISTS 运算符返回 TRUE 。
在此语法中，子查询仅是 SELECT 语句。子查询返回行后， EXISTS 运算符返回 TRUE 并立即停止处理。请注意，即使子查询返回 NULL 值， EXISTS 运算符也会计算为 TRUE

QL中EXISTS与IN的使用及效率
EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引。但要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。

系统函数

CHARINDEX

写SQL语句我们经常需要判断一个字符串中是否包含另一个字符串，但是SQL SERVER中并没有像C#提供了Contains函数，不过SQL SERVER中提供了一个叫CHAEINDX的函数，顾名思义就是找到字符（char）的位置（index），既然能够知道所在的位置，当然就可以判断是否包含在其中了。
通过CHARINDEX如果能够找到对应的字符串，则返回该字符串位置，否则返回0。

• 基本语法如下：

  • CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
  • expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000
  • expressionToSearch ：用于被查找的字符串。
  • start_location：开始查找的位置，为空时默认从第一位开始查找。

• 可以增加参数决定是否大小写敏感

• SQL SERVE提供了特殊的关键字用于查询时区分大小写，其中CS为Case-Sensitve的缩写。

• 我们也可以这样说明是大小写不敏感，其中CI是Case-InSensitve的缩写，即大小写不敏感，当然我们没必要多此一举。

• PATINDEX也可以用来判断一个字符串中是否包含另一个字符串，两种的差异在于，前者是全匹配，后者支持模糊匹配。

--1.简单用法（返回6）
select charindex('test','this Test is Test')
--2.增加开始位置（返回14）
select charindex('test','this Test is Test',7)
--3.大小写敏感（返回0）
select charindex('test','this Test is Test'COLLATE Latin1_General_CS_AS)
--4.大小写不敏感（返回6）
select charindex('Test','this Test is Test'COLLATE Latin1_General_CI_AS)

PATINDEX

• 和CHARINDEX类似，PATINDEX也可以用来判断一个字符串中是否包含另一个字符串，两种的差异在于，前者是全匹配，后者支持模糊匹配。
  • PATINDEX也允许支持大小写敏感，做法和CHARINDEX一样，此处不再累述

1.简单示例 （返回结果3）
select PATINDEX('%ter%','interesting data')
2.简单示例2（返回结果8）
select PATINDEX('%t_ng%','interesting data')

时间格式化

使用Convert转换时间格式

使用Format转换时间格式

--部门表
CREATE TABLE Depa
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    DepaName NVARCHAR(45),--部门名称
    DepaDesc NVARCHAR(45),--部门描述
    ParentId INT,--上级部门Id
    Cud VARCHAR(35),--创建人Id
    Cdt DATETIME DEFAULT(GETDATE()),--创建时间
    Uud VARCHAR(35),--修改人Id
    Udt DATETIME,--修改时间
)
--插入数据
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(1, N'小米科技', NULL, NULL, NULL, '2023-07-19 14:12:19.860', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(2, N'销售部', NULL, 1, NULL, '2023-07-19 14:15:41.350', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(3, N'IT部', NULL, 1, NULL, '2023-07-19 14:15:41.350', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(4, N'法务部', NULL, 1, NULL, '2023-07-19 14:15:41.350', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(5, N'财务部', NULL, 1, NULL, '2023-07-19 14:15:41.350', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(6, N'后勤部', NULL, 1, NULL, '2023-07-19 14:15:41.350', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(7, N'人事部', NULL, 1, NULL, '2023-07-19 14:15:41.350', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(8, N'研发部', NULL, 3, NULL, '2023-07-19 14:16:45.737', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(9, N'运维部', NULL, 3, NULL, '2023-07-19 14:16:45.737', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(10, N'前端组', NULL, 8, NULL, '2023-07-19 14:26:00.360', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(11, N'后端组', NULL, 8, NULL, '2023-07-19 14:26:00.360', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(12, N'后端开发', NULL, 11, NULL, '2023-07-19 14:28:09.887', NULL, NULL);
INSERT INTO master.dbo.Depa
(Id, DepaName, DepaDesc, ParentId, Cud, Cdt, Uud, Udt)
VALUES(13, N'大数据开发', NULL, 11, NULL, '2023-07-19 14:28:09.887', NULL, NULL);

系统相关查询

查询SQL执行记录与时长

SELECT TOP 100 (total_elapsed_time / execution_count)/1000 N'平均时间ms', total_elapsed_time/1000 N'总花费时间ms', total_worker_time/1000 N'所用的CPU总时间ms', total_physical_reads N'物理读取总次数', total_logical_reads/execution_count N'每次逻辑读次数' , total_logical_reads N'逻辑读取总次数',total_logical_writes N'逻辑写入总次数', execution_count N'执行次数', SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset
    WHEN -1 THEN
    DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) N'执行语句' , creation_time N'语句编译时间' ,last_execution_time N'上次执行时间'
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset
    WHEN -1 THEN
    DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) NOT LIKE '?tch%'
        AND last_execution_time > '2021-02-04 1:40:00.077'
ORDER BY  total_elapsed_time / execution_count DESC

获取系统信息 (获取前端连接的IP地址，计算机名等信息)

如何在SqlServer中获取前端连接的IP地址，计算机名等信息 - 郭大侠1 - 博客园

如果你对SqlServer的系统函数或视图不太了解，这个功能看起来好像比较复杂，而实际上， SqlServer的动态管理视图已经给我们提供了这些信息，下面我们来看两个动态管理视图。

1、Sys.dm_exec_Sessions

比如说，我们要看那些主机有连接到了sqlserver服务器，可以使用下面的sql语句：

select distinct host_name from sys.dm_exec_Sessions

要看那些用户已连接到sqlserver服务器：

select distinct login_name from sys.dm_exec_Sessions

当然，利用下面的列，我们可以获得更多想要的客户端信息

这个视图中提供了所有连接sqlserver的客户端的一些信息，下面是Sys.dm_exec_Sessions返回的列：

2、Sys.dm_exec_connections

比如，我要查看当前连接的客户端IP与sqlserver所在服务器的IP，可以用下面的sql查询：
@@spid的作用是返回当前进程的会话ID

select client_net_address '客户端IP',local_net_address '服务器的IP' from sys.dm_exec_connections where Session_id=@@spid

这个视图返回了连接sqlserver服务器上面的每个连接的详细信息，下面是Sys.dm_exec_connections返回的列：

文档

# 设置或更改数据库排序规则

使用技巧

查找数据插入来源

给表增加字段赋默认值为获取客户端 IP

ALTER TABLE Laser_Cogradient_Faile ADD IpAdd varchar(30) default(CONVERT(nvarchar,CONNECTIONPROPERTY('client_net_address')))