SqlCollections - 数据管理

--==============================数据管理===============================
--插入记录
INSERT INTO 订单明细(折扣,数量,单价,产品ID,订单ID)
VALUES (0.8,3,26,2,10248)

--允许插入标识列
SET IDENTITY_INSERT 类别 ON
GO

--INSERT INTO批量插入数据
--格式：INSERT INTO 表1(字段1,字段2...) + 返回结果集的语句（如SELECT * FROM 表2、EXEC sp_who）
--表1必须是事先已经存在的表！
CREATE TABLE 雇员通讯录
(
    雇员ID int PRIMARY KEY,
    姓氏 nvarchar(20),
    名字 nvarchar(20)
)
GO
INSERT INTO 雇员通讯录    --INSERT INTO 表1 SELECT 字段1,字段2 FROM 表2
SELECT 雇员ID,姓氏,名字
FROM 雇员
GO
SELECT * FROM 雇员通讯录
GO
TRUNCATE TABLE 雇员通讯录
GO
INSERT INTO 雇员通讯录(雇员ID,姓氏,名字)    --INSERT INTO 表1(字段1,字段2) SELECT 字段1,字段2 FROM 表2
SELECT 雇员ID,姓氏,名字
FROM 雇员
GO
SELECT * FROM 雇员通讯录
GO
DROP TABLE 雇员通讯录
GO

--SELECT INTO批量插入数据
--格式：SELECT 字段1,字段2 INTO 表2 FROM 表1 
--表2必须是事先不存在的表！
SELECT 产品ID,产品名称
INTO #缺货记录
FROM 产品
WHERE 库存量 = 0

--复制表结构的方法
--使用SELECT INTO,但让WHERE返回空结果集
SELECT *
INTO #订单2
FROM 订单
WHERE 0 = 1

--更新记录
UPDATE 产品
SET 单价 = 单价 * 1.5

--关联多表批量更新
UPDATE b
SET b.单价 = a.单价
FROM 产品 a
JOIN 订单明细 b
ON a.产品ID = b.产品ID
WHERE a.产品名称 = N'牛奶'

--删除记录
DELETE 订单 WHERE 订购日期 < '1996.8.1'

--删除所有记录
--TRUNCATE删的快，删除后不能用事务日志恢复
--TRUNCATE删除后，自增长字段会重新开始编号
--若该表是其他表外键指向的表，则不能用TRUNCATE删除数据
TRUNCATE TABLE 订单明细

--关联多表批量删除
DELETE 订单
FROM 雇员 a
JOIN 订单 b
ON a.雇员ID = b.雇员ID
WHERE a.姓氏 = N'李' AND a.名字 = N'芳'

--查看最前记录
SELECT TOP 8 *    --查询出8条记录
FROM 订单明细
ORDER BY 数量 DESC

SELECT TOP 8 WITH TIES *    --WITH TIES显示排名并列的记录，共查询出9条
FROM 订单明细
ORDER BY 数量 DESC

SELECT TOP 10 PERCENT *        --查询出前百分之多少的记录
FROM 订单明细
ORDER BY 数量 DESC

--查询不重复的记录
SELECT DISTINCT 货主城市 FROM 订单

--查询标识列
SELECT $IDENTITY FROM 类别

--查询GUID列
SELECT $ROWGUID FROM 类别

--多表联合查询
--[Inner] Join:只显示符合条件的记录；
--Left [Outer] Join:显示左表中所有的记录，以及右表中符合条件的记录；
--Right [Outer] Join:显示右表中所有的记录，以及左表中符合条件的记录；
--Full [Outer] Join:显示所有表中的记录，包括符合条件和不符合条件的记录；
--Cross Join:将一个表的每条记录与另一个表的每条搭配，不需要On设置条件。

--Join自身
SELECT 雇员.雇员ID,雇员.姓氏,雇员.名字,主管.姓氏,主管.名字
FROM 雇员
LEFT JOIN 雇员 AS 主管
ON 雇员.上级 = 主管.雇员ID

--查询字段为NULL
SELECT *
FROM 雇员
WHERE 上级 IS NULL

--替换NULL值
SELECT 类别ID,类别名称,ISNULL(说明,'暂无说明') AS 说明
FROM 类别

--模糊查询
--通配符：%:0个或多个字符；_:1个字符，可重用；[]:指定范围中的任意单个字符；[^]不属于指定范围内的任意单个字符。
--查询包含通配符的文本，只需将通配符用[]包起来
SELECT * 
FROM 产品
WHERE 产品名称 LIKE '%奶酪'    --以奶酪结尾的
--WHERE 产品名称 LIKE '__奶酪'    --以奶酪结尾，且必须是4个字的
--WHERE 产品名称 LIKE '[麻酱]油'    --麻油或酱油

--有关子查询
SELECT * FROM 
(
SELECT * FROM 订单
--ORDER BY 订单ID    --子查询不允许排序操作（例外：TOP、FOR XML）
)a    --子查询必须取别名

--分组 GROUP BY
SELECT 货主城市,COUNT(订单ID) AS 订单总数,YEAR(订购日期) AS 订购年份
FROM 订单
GROUP BY 货主城市,YEAR(订购日期)
--WITH CUBE    --可选：对GROUP BY列出的所有分组字段进行汇总运算
WITH ROLLUP    --可选：对GROUP BY列出的第一个字段进行汇总运算
ORDER BY 货主城市,YEAR(订购日期)

--HAVING子句
--HAVING与WHERE区别：汇总函数只能在HAVING子句中使用
--形式上：WHERE必定在GROUP BY之前，HAVING必定在GROUP BY之后
SELECT 货主城市,COUNT(订单ID) AS 订单总数
FROM 订单
GROUP BY 货主城市
HAVING COUNT(订单ID) > 20

--UNION合并结果集
SELECT 联系人姓名,地址,电话
FROM 供应商
UNION ALL    --合并所有记录，包括重复记录
SELECT 联系人姓名,地址,电话
FROM 客户
UNION    --去除重复数据
SELECT '张三','aaa','123'    --可以使用UNION加入临时数据