SQL 基础技能提升
此篇涉及的 SQL 全部在SQL SERVER 2005 EXPRESS 通过验证
-- 数据库备份
USE MASTER
EXEC SP_ADDUMPDEVICE 'DISK' , 'TESTBACKDB1' , 'E:\T.bak'；
BACKUP DATABASE DB TO TESTBACKDB1
EXEC SP_DROPDEVICE 'TESTBACKDB1'
-- 依据旧表创建新表
SELECT * INTO NEW_TABLE_NAME FROM OLE_TABLE_NAME WHERE 1 = 2 -- 只创建与旧表结构相同表结构
SELECT * INTO NEW_TABLE_NAME FROM OLE_TABLE_NAME -- 复制旧表资料到新表
Eg:
SELECT * INTO TEST2 FROM TEST
-- 添加/删除列 COLUMN
ALTER TABLE TABLE_NAME ADD COLUMN_NAME INT IDENTITY | UNIQUE -- 添加
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME -- 删除
Eg:
ALTER TABLE TEST ADD AGE INT
ALTER TABLE TEST DROP COLUMN AGE
-- 添加/删除约束 CONSTRAINT
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME UNIQUE (COLUMN1_NAME,COLUMN2_NAME)
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY(COLUMN_NAME,COLUMN_NAME2)
ALTER TABLE TABLE_NAME ADD PRIMARY KEY(ID) -- 此方法添加的主键名称无法控制，建议使用上面的方法
ALTER TABLE TABLE_NAME DROP CONSTRAINT_NAME
ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME
Eg:
ALTER TABLE TEST ADD CONSTRAINT CK CHECK(AGE > 18)
INSERT INTO TEST(ID,AGE) VALUES (1,19)
-- 索引的创建和删除
CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1_NAME,COLUMN2_NAME) -- 创建非唯一，非聚集索引
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1_NAME,COLUMN2_NAME) -- 创建唯一，非聚集索引
CREATE UNIQUE CLUSTERED INDEX INDEX_NAME ON TEST(COLUMN1_NAME,COLUMN2_NAME) -- 创建唯一，聚集索引
DROP INDEX TABLE_NAME.INDEX_NAME
-- 复杂的运算符
-- UNION : 合并表查询结果
SELECT GETDATE() UNION SELECT GETDATE() - 1 UNION SELECT GETDATE() - 2
SELECT GETDATE() UNION SELECT GETDATE() - 1 UNION SELECT GETDATE() - 1
SELECT GETDATE() UNION ALL SELECT GETDATE() UNION ALL SELECT GETDATE()
-- EXCEPT : 获取表1中存在且不存在表2中是数据
SELECT GETDATE() EXCEPT SELECT GETDATE()
SELECT GETDATE() UNION ALL SELECT GETDATE() UNION ALL SELECT GETDATE() - 1 EXCEPT SELECT GETDATE() - 1
--INTERSECT : 获取存在与表1且表2中也有同样记录的数据
SELECT 1 A INTERSECT SELECT 1 A
SELECT 1 A INTERSECT SELECT 2 A
-- 分离/附加数据库
EXEC SP_DETACH_DB DATABASE_NAME
EXEC SP_ATTACH_DB DATABASE_NAME, FILE_FULL_PATH
Eg:
EXEC SP_ATTACH_DB 'DB', 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DB.MDF'
-- 数据库重新命名
EXEC SP_RENAMEDB 'OLD_NAME' , 'NEW_NAME'
EXEC SP_RENAME 'OLD_NAME' , 'NEW_NAME', 'DATABASE'
Eg;
EXEC SP_RENAMEDB 'DB' , 'DB2'
EXEC SP_RENAME 'DB2' , 'DB' , 'DATABASE'
-- 重命名表和约束(CHECK，FOREIGN KEY，PRIMARY/UNIQUE KEY)
EXEC SP_RENAME 'OLD_NAME' , 'NEW_NAME', 'OBJECT'
Eg;
EXEC SP_RENAME 'TEST' , 'TEST1' , 'OBJECT'
-- 索引/列 重命名
EXEC SP_RENAME 'TABLE_NAME.OLD_NAME' , 'NEW_NAME', 'INDEX'
EXEC SP_RENAME 'TABLE_NAME.OLD_NAME' , 'NEW_NAME', 'COLUMN'
Eg;
EXEC SP_RENAME 'TEST.IX_ID1' , 'IX_ID', 'INDEX'
-- SQL 语句的循环结构
DECLARE @COUNT INT;
SET @COUNT = 0;
WHILE @COUNT <= 6
BEGIN
SET @COUNT = @COUNT + 1;
IF @COUNT = 2 PRINT 'CHINA';
ELSE IF @COUNT = 3
BEGIN
CONTINUE; -- 退出本次循环执行下次循环，
PRINT 'JANPAN'
END;
ELSE IF @COUNT = 4 PRINT 'AMERICA';
ELSE IF @COUNT = 5
BEGIN
BREAK; -- 退出当前的循环结构，不在执行此循环
PRINT 'GERMAN';
END;
ELSE PRINT 'ENGLAND'
END;
-- CASE 语句结构
DECLARE @COUNT INT;
SET @COUNT = 3;
--WHILE @COUNT < 5
SELECT @COUNT ID,
CASE @COUNT WHEN 1 THEN 'CHINA'
WHEN 2 THEN 'AMERICA'
WHEN 3 THEN 'JANPAN'
WHEN 4 THEN NULL
END AS [NAME];
-- CASE 语句也可以嵌套的在WHERE条件中
-- 创建表详细说明JOIN的用法
create table T1(ID int, [NAME] nvarchar(10));
go
create table T2(ID int, AGE int);
insert into t1(ID,[NAME]) values(1,'LILY');
insert into t1 values(2,'LUCY');
insert into t1 values(3,'JIM');
insert into t2 values(1,18);
insert into t2 values(2,19);
insert into t2 values(4,20);
-- TABLE: T1 TABLE: T2
-- ID NAME ID AGE
-- 1 LILY 1 18
-- 2 LUCY 2 19
-- 3 JIM 4 20
SELECT T1.ID,T1.NAME,T2.AGE FROM T1 JOIN T2 ON T1.ID = T2.ID ORDER BY T1.ID -- 内连接
-- 结果
-- ID NAME AGE
-- 1 LILY 18
-- 2 LUCY 19
SELECT T1.ID,T1.NAME,T2.AGE FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID ORDER BY T1.ID -- 左连接（外连接之一）
-- 结果
-- ID NAME AGE
-- 1 LILY 18
-- 2 LUCY 19
-- 3 JIM NULL
SELECT T1.ID,T1.NAME,T2.AGE FROM T1 RIGHT JOIN T2 ON T1.ID = T2.ID ORDER BY T2.AGE -- 右连接（外连接之一）
-- 结果
-- ID NAME AGE
-- 1 LILY 18
-- 2 LUCY 19
-- NULL NULL 20
SELECT T1.ID,T1.NAME,T2.AGE FROM T1 FULL JOIN T2 ON T1.ID = T2.ID ORDER BY T1.ID,T2.AGE -- 全连接（外连接之一）
-- 结果
-- ID NAME AGE
-- NULL NULL 20
-- 1 LILY 18
-- 2 LUCY 19
-- 3 JIM NULL
SELECT T1.ID,T1.NAME,T2.AGE FROM T1 CROSS JOIN T2 ORDER BY T1.ID,T2.AGE -- 交叉连接
-- 结果: 笛卡尔积形式
-- ID NAME AGE
-- 1 LILY 18
-- 1 LILY 19
-- 2 LUCY 18
-- 2 LUCY 19
-- 多张表的连接
SELECT T1.ID,T2.ID,T3.ID FROM T1 JOIN T2 ON T1.ID = T2.ID JOIN T3 ON T2.ID = T3.ID
-- GROUP BY 与 HAVING 组合
-- 如果group by ,having 基本和where条件结果没什么区别，只是过滤的结果集的时间延迟到where条件之后执行，
-- having只有与group by组合使用，它的用途才可以展现的淋漓尽致
-- 1. 获取表中的重复记录（假设表中只有NAME一列）
SELECT [NAME] FROM T1 GROUP BY [NAME] HAVING COUNT([NAME]) > 1
-- 2. 获取平均分数大于85的学科的名称
SELECT [NAME] FROM T1 GROUP BY [NAME] HAVING AVG(SCORE) > 85
-- ROW_NUMBER() 函数的使用
SELECT ROW_NUMBER() OVER( ORDER BY ITEMID) ,* FROM MSPETSHOP4..ITEM GO
-- 先按分区ID PRODUCTID分组,再按ITEMID排序
SELECT ROW_NUMBER() OVER( PARTITION BY PRODUCTID ORDER BY ITEMID) ,* FROM MSPETSHOP4..ITEM
SELECT SUM(ITEMID) OVER( PARTITION BY PRODUCTID ) ,* FROM MSPETSHOP4..ITEM
