T-SQL即Transact-SQL,是SQL在 Microsoft SQL Server上的增强版。
主要记录一些没有特别注意到的语法特性和使用细节
数据库表创建后,
可通过系统存储过程 sp_help
查看已有表结构信息
[EXECUTE] sp_help [table_name]
Tips:
EXECUTE
可简写为EXEC
;EXECUTE
-- 添加列
ALTER TABLE <table_name>
ADD <column_name> <date_type>[(length)] [NULL | NOT NULL]
-- 修改列
ALTER TABLE <table_name>
ALTER COLUMN <column_name> <date_type>[(length)] [NULL | NOT NULL]
-- 删除列
ALTER TABLE <table_name>
DROP COLUMN <column_name>
EXEC sp_rename <origin_object_name>, <new_object_name>
Demo:
-- 将 user 表重命名为 new_user
EXEC sp_rename 'user', 'new_user'
-- 将 user 表的列名 name 重命名为 name_zh
EXEC sp_rename 'user.name', 'name_zh'
ALTER TABLE <table_name>
ADD [CONSTRAINT 约束名] [PRIMARY KEY | UNIQUE] (column_name 1[,....n])
ALTER TABLE <table_name> DROP CONSTRAINT 约束名
Demo:
ALTER TABLE user
ADD CONSTRAINT pk_name_age PRIMARY KEY(name, age);
ALTER TABLE user
ADD CONSTRAINT uniq_idcard UNIQUE(id_card);
ALTER TABLE user
DROP CONSTRAINT pk_name_age;
ALTER TABLE user
DROP CONSTRAINT uniq_idcard;
PRIMARY KEY
字段。数据库默认建立;UNIQUE
字段。数据库默认建立;FOREIGN KEY
字段;聚集索引(Clustered Index): 对物理数据页中的数据按列顺序排序,然后重新存储到磁盘上
非聚集索引(NonClustered Index): 虽包含按生序排序的键值,但不影响表中数据记录的实际排列顺序。对表进行下列操作时会重建该表现存非聚集索引:
唯一索引:
复合索引:
EXEC sp_helpindex <table_name>
显示预估查询计划
SET SHOWPLAN_ALL ON|OFF
-- or SET SHOWPLAN_TEXT ON|OFF
GO
SELECT * FROM user WHERE id = 1;
GO
显示实际查询计划
SET STATISTICS PROFILE ON|OFF
SELECT <字段列表> [INTO 新表] FROM <表/视图>...
使用该方式可以直接将查询列数据存放在新表中,而不用提前声明该新表。
Tips:
Demos
-- 创建不存在的新表
SELECT name, age
INTO new_user
FROM old_user old
WHERE old.id > 100
ORDER BY old.name DESC;
-- 创建临时表
SELECT name, age
INTO #temp_user
FROM old_user old;
-- 查询新建表或临时表
SELECT * FROM new_user;
SELECT * FROM #temp_user;
如果想创建一个和原表一样的空数据表,可以使用下面的方式
-- WHERE返回false即可,目的只是为了保留表结构
SELECT *
INTO same_empty_user
FROM old_user
WHERE 1 > 2
SELECT [TOP n | TOP n PERCENT] 列名1 [,...n]
FROM <表/视图> ...
Demos:
/**
if table user have 100 records and id from 1 to 120.
1. TOP 10 return 10 records and id from 1 to 10
2. TOP 10 PRECENT return 12 records and id from 1 to 12.
*/
SELECT TOP 10 * FROM user ORDER BY id;
SELECT TOP 10 PRECENT * FROM user ORDER BY id;
该语句查询时不包含参数边界.
涉及空值的查询时,只能使用IS NULL
语句,而不能使用 = 操作符
SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2
-- or
SELECT 列名列表 FROM 表名1, 表名2
也叫非限定连接(笛卡尔乘积)
SELECT 列名列表 FROM 表1 [INNER] JOIN 表2 ON 表1.列名 = 表2.列名
-- or
SELECT 列名列表 FROM 表1, 表2 WHERE 表1.列名 = 表2.列名
也叫自然连接。连接条件通常是主外键方式,只获取满足条件的数据
以关联的一方(LEFT or RIGHT)或者两方(FULL)作为基础表进行查询,不匹配的部分参照主关联方进行数据补齐。
SELECT 列名列表 FROM 表1 LEFT JOIN 表2 ON 表1.列名 = 表2.列名
以 表1 作为基础表关联 表2 。
SELECT 列名列表 FROM 表1 RIGHT JOIN 表2 ON 表1.列名 = 表2.列名
以 表2 作为基础表关联 表1 。
不难发现,右连接 表1 和 表2 与左连接 表2 和 表1 是等价的。
SELECT 列名列表 FROM 表1 FULL [OUTER] JOIN 表2 ON 表1.列名 = 表2.列名
表1 关联 表2, 并将表1 和 表2 多余的数据也返回
所谓自连接也就是自己关联自己进行关联查询。
查询语句1
UNION [ALL]
查询语句2
ORDER BY
子句对最终结果集进行排序,同时排序字段只能使用第一个结果集返回的字段UPDATE table_alias
SET table_alias.column = ? [, table_alias.column = ?]
FROM table [AS] table_alias
[INNER | LEFT | RIGHT ] JOIN ref_table [AS] ref_alias ON table_alias.column = ref_alias.column ...
[WHERE]
Demo:
UPDATE main
SET main.last_name = pr.last_name, main.first_name = pr.first_name
FROM user main
INNER JOIN person pr ON pr.person_id = main.user_id
WHERE main.last_name IS NULL AND main.first_name IS NULL;
DElETE table_alias
FROM table [AS] table_alias
[INNER | LEFT | RIGHT ] JOIN ref_table [AS] ref_alias ON table_alias.column = ref_alias.column ...
[WHERE]
Demo:
DElETE main
FROM user main
INNER JOIN person pr ON pr.person_id = main.user_id
WHERE main.last_name IS NULL;
TRUNCAT
删除速度快于 DELETE
, 但删除数据不可被恢复(只将对表数据页面的释放操作记录到日志);DELETE
操作在删除每一行时都把删除操作记录写入日志,因而可以通过事务回滚;常规LIKE模糊匹配
SELECT * FROM user WHERE name like '%jack%'
可替换为
SELECT *
FROM user
CROSS APPLY( SELECT CHARINDEX('jack',name) name_index ) name_criteria
WHERE name_criteria.name_index > 0
批处理由多条语句的组合。 GO 语句表示一个批处理的结束
GO
语句必须单独存在,不能含其他SQL语句和注释。批处理语句规则:
CREATE
命令可以在单个批处理命令中执行,但 CREATE [ DATABASE | TABEL | INDEX ]
除外;EXECUTE | EXEC
;字符串拼接使用 + 号。当有
NULL
字符时,则整个表达式返回NULL.表达式SELECT 'a' + NULL
将返回NULL
/* BEGIN...END 将多条语句组合成语句块,并将其视作一个单一语句 */
IF @name IS NOT NULL
BEGIN
UPDATE user SET name = @name WHERE id = 1
SELECT * FROM user
END
/* IF 语句可嵌套使用,嵌套层数没有限制 */
IF @name = 'test'
PRINT 'this is a test'
ELSE IF @name = 'jack' AND age > 20
PRINT 'hello young man, jack!'
ELSE
PRING 'hello!'
/* CASE 简单表达式 */
CASE gender
WHEN 0 THEN 'Female'
WHEN 1 THEN 'Male'
-- ELSE 'Unknown'
END AS Gender_Desc
/*
CASE 搜索表达式.
ELSE可以省,此时如果没有匹配的则返回NULL
*/
CASE
WHEN gender = 0 THEN 'Female'
WHEN gender = 1 THEN 'Male'
ELSE 'Unknown'
END AS Gender_Desc
WAITFOR {DELAY 'time' | TIME 'time'}
-- 等待10s执行查询语句
WAITFOR DELAY '00:00:10'
SELECT * FROM user
-- 指定时间执行查询语句
WAITFOR TIME '10:00:00'
SELECT * FROM user
WHILE 逻辑表达式
BEGIN
{语句或语句块1}
[CONTINUE]
{语句或语句块2}
[BREAK]
{语句或语句块3}
END
表达式为真时循环执行。
/* Output:
S0001: 1
S0001: 2 between 2 and 5
S0001: 3 between 2 and 5
S0001: 4 between 2 and 5
S0001: 5 between 2 and 5
S0001: num eq 6. the end
S0001: 6
*/
DECLARE @num TINYINT = 1
WHILE 1 = 1
BEGIN
IF @num < 2
BEGIN
PRINT @num
SET @num += 1
CONTINUE
END
IF @num = 6
BEGIN
PRINT 'num eq 6. the end'
BREAK
END
PRINT CONVERT(NVARCHAR(10), @num) + ' between 2 and 5'
SET @num += 1
END
PRINT @num
利用系统表 SYSOBJECTS 或系统函数 Object_Id 检验存储过程是否存在。
IF Object_Id('usp_sp', 'P') IS NOT NULL
DROP PROCEDURE usp_sp
IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'usp_sp' and type = 'P')
DROP PROCEDURE usp_sp
-- 临时表
IF Object_Id('tempdb..#usp_tempsp', 'P') IS NOT NULL
DROP PROCEDURE #usp_tempsp
系统表 SYSOBJECTS.type 字段说明:
type 取值 | 说明 | type 取值 | 说明 |
---|---|---|---|
C | check约束 | D | 默认值或default约束 |
F | 外键约束 | FN | 标量函数 |
IF | 嵌套表函数 | K | 主健约束或Unique约束 |
L | 日志 | P | 存储过程 |
PK | 主键约束 | R | 规则 |
RF | 复制筛选器存储过程 | S | 系统表 |
TR | 触发器 | U | 用户表 |
V | 试图 | X | 扩展存储过程 |