T-SQL复习大纲
T-SQL (Microsoft Transact-SQL) 是微软在国际标准结构化查询语言(SQL)的基础上,为SqlServer定义的新的语言。
DML
数据库操纵语言 DML (Data Manipulation Language) SQL允许用户或应用程序通过添加新数据、删除旧数据和修改以前存储的数据对数据库更新
语句 | 功能 | 示例 |
---|---|---|
SELECT | 从数据库表中检索数据行和列 | SELECT * FROM TABLE |
INSERT | 把新的数据记录添加到数据库中 | INSERT INTO TABLE VALUES(val1,val2...) |
DELETE | 从数据库中删除数据记录 | DELETE TABLE WHERE ID=1 |
UPDATE | 修改现有数据库表中的数据 | UPDATE TABLE SET NAME='HELLO' WHERE ID=1 |
DDL
数据库定义语言 DDL (Data Definition Language) SQL让用户定义存储数据的结构和组织,以及数据项之间的关系
语句 | 功能 |
---|---|
CREATE TABLE | 在一个数据库中创建一个数据库表 |
DROP TABLE | 从数据库中删除一个表 |
ALTER TABLE | 修改一个现存表的结构 |
CREATE VIEW | 把一个视图添加到数据库中 |
DROP VIEW | 从数据库中删除视图 |
CREATE INDEX | 为数据库表中的一个字段构建索引 |
DROP INDEX | 从数据库表中的一个字段中删除索引 |
CREATE PROEDURE | 在一个数据库中创建一个存储过程 |
DROP PROEDURE | 从数据库中删除存储过程 |
CREATE TRIGGER | 创建一个触发器 |
DROP TRIGGER | 从数据库中删除触发器 |
CREATE SCHEMA | 向数据库添加一个新模式 |
DROP SCHEMA | 从数据库中删除一个模式 |
CREATE DOMAIN | 创建一个数据值域 |
ALTER DOMAIN | 改变域定义 |
DROP DOMAIN | 从数据库中删除一个域 |
DCL
数据库控制语言 DCL (Data Control Language) 可以使用SQL来限制用户检索,添加修改数据的能力,保护存储的数据不被未授权的用户所访问。
语句 | 功能 |
---|---|
COMMIT | 定义查询游标 |
ROLLBACK | 中止当前事务 |
SET TRANSACTION | 定义当前事务数据的访问特征 |
DRL
数据库检索语言 DRL (Data Retrieval Language) [官方没有这个叫法,写在这里纯属为了整齐] SQL允许用户或应用程序从数据库中检索存储的数据并使用它。
语句 | 功能 |
---|---|
DECLARE | 定义查询游标 |
EXPLAN | 描述查询描述数据访问计划 |
OPEN | 检索查询结果打开一个游标 |
FETCH | 检索一条查询结果记录 |
CLOSE | 关闭游标 |
PREPARE | 为动态执行准备SQL语句 |
EXECUTE | 动态地执行SQL语句 |
DESCRIBE | 描述准备好的查询 |
子查询、嵌套查询
1. 必须写在括号内
2. 必须包含select和from子句
3. 不可以使用conpute和forbrowe
4. 只有使用了top才可以使用orderby
5. 最多嵌套32个级别的查询
常用的SQL
-- 返回自增id
1. select @@IDENTITY
2. select scope_identity()
-- 分页
select * from tduser order by [u_id] desc offset 0 rows fetch next 100 rows only;
-- 查询合并结果集 INION
-- UNION 操作符用于合并两个或多个SELECT 语句的结果集, 如需要查询出所有用户和企业的id与name
select [a].[u_id], [a].[u_name] from tbuser a union select [b].[c_id], [b].[c_name] from tbcompany b;
-- 分组后排序 ROW_NUMBER()与PARTITION
select [u_id],[u_name],ROW_NUMBER() OVER(PARTITION BY [u_id] ORDER BY [u_id]) as num from tbuser where [u_removed]<>0;
-- 示例:根据tbuser表的type字段排序表的order字段
update tbuser set [a].[u_order]=[b].[num] from tbuser as a inner join
(select [u_id],ROW_NUMBER() OVER(PARTITION BY [u_id] ORDER BY [u_id]) as num from tbuser where [u_removed]<>0) as b
on [a].[u_id]=[b].[u_id];
-- NULLIF、ISNULL、IIF 函数的使用
-- NULLIF(expression1,expression2): expression1 == expression2 ? NULL : expression1
-- ISNULL([u_name], 'a'):[u_name] is NULL ? 'a' : [u_name]
-- IIF(expression, true_value, false_value): expression ? true_value : false_value
-- COALESCE(expression1,expression2,expression3,...):返回第一个不为空的expression
查询重复数据
-- 单列重复
SELECT * FROM T_Table WHERE column1 IN (SELECT column1 FROM T_Table GROUP BY column1 HAVING COUNT(column1) > 1);
-- 多列重复
SELECT [a].[column1],[a].[column2],... FROM T_Table a,
(SELECT [column1],[column2],... FROM T_Table GROUP BY [column1],[column2],... HAVING COUNT(1)>1) b
WHERE [b].[column1]=[a].[column1] AND [b].[column2]=[a].[column2] AND ...;
数据压缩
数据量不大但mdf文件很大
-- delete删除的数据空间,无法再次调用,造成数据库数据不大,但mdf文件很大
-- 第一种: SqlServer 数据库右键 -> 任务 -> 收缩 -> 数据库;选择好压缩比例直接开始,过程会比较久
-- 第二种:
-- 1)查看数据库表占用空间
IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
DROP TABLE #TablesSizes
CREATE TABLE #TablesSizes
(TableName sysname ,
Rows BIGINT ,
reserved VARCHAR(100) ,
data VARCHAR(100) ,
index_size VARCHAR(100) ,
unused VARCHAR(100)
)
DECLARE @sql VARCHAR(MAX)
SELECT @sql = COALESCE(@sql, '') + '
INSERT INTO #TablesSizes execute sp_spaceused ''' + QUOTENAME(TABLE_SCHEMA,'[]') + '.' + QUOTENAME(Table_Name, '[]') + ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
PRINT ( @SQL )
EXECUTE (@SQL)
SELECT *
FROM #TablesSizes
ORDER BY Rows DESC
-- 2)以下代码可清理数据库log文件
USE [master]
GO
ALTER DATABASE [lhcloud_dev] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [lhcloud_dev] SET RECOVERY SIMPLE
GO
USE [lhcloud_dev]
GO
DBCC SHRINKFILE (N'lhcloud_dev' , 11, TRUNCATEONLY)
GO