SQL Server（三）—— 数据（记录）操作

-- DEMO 表
CREATE TABLE employee(
	id INT NOT NULL IDENTITY(1,1),
	name NVARCHAR(20) NOT NULL UNIQUE,
	age INT NOT NULL DEFAULT 18,
	dept_id INT,
	header_id INT
)

CREATE TABLE employee_bk(
	id INT NOT NULL IDENTITY(1,1),
	name NVARCHAR(20) NOT NULL UNIQUE,
	age INT NOT NULL DEFAULT 18,
	dept_id INT
)

CREATE TABLE ProductImg(
	product_id INT NOT NULL,
	jpg VARBINARY(MAX) NOT NULL
)

CREATE TABLE user_proc_info(
	编号 int PRIMARY KEY IDENTITY(1,1),
	[进程 ID] smallint,
	[定线程上下文 ID] smallint,
	进程状态 nchar(30),
	登录名 nchar(128),
	主机名 nchar(128)	,
	[阻塞进程的系统进程 ID] nchar(5),
	数据库名 nchar(128),
	运行命令 nchar(16),
	[请求 ID] int,
	查询时间 smalldatetime DEFAULT getdate()
)

一、 INSERT 操作

SET IDENTITY_INSERT employee ON--允许插入标识列
GO 
INSERT INTO employee(id,name,age) VALUES(1,'张三'，12) -- 按列顺序插入数据，使用IDENTITY_INSERT 进行标识插入语句时 employee 的 id 列必须显示指明

SET IDENTITY_INSERT employee OFF -- 禁止插入标识列
GO 
INSERT INTO employee(age,name) VALUES(17,'李梅') -- 插入数据
INSERT INTO employee_bk(name,age)
	SELECT name,age FROM employee -- 从 employee 表获取数据插入备份表 employee_bk
INSERT TOP 10 INTO employee_bk(name,age)
	SELECT name,age FROM employee ORDER BY ID DESC -- 向 employee_bk 表插入 employee 表的前10条记录 
	
-- 以下语句可用于拷贝数据数据，获取数据并将age全部改成age+2
INSERT employee_bk(name,age)
	 SELECT name,age+2 FROM 
	 (SELECT TOP (3) * FROM employee ORDER BY ID DESC) AS M 
	 ORDER BY M.id ASC
	 
INSERT INTO user_proc_info DEFAULT VALUES -- 插入默认值记录


INSERT user_proc_info([进程 ID],[定线程上下文 ID],进程状态,登录名,主机名,[阻塞进程的系统进程 ID],数据库名,运行命令,[请求 ID])
	EXEC sp_who -- Insert 语句里使用 exec 子句

INSERT INTO ProductImg(product_id,jpg) SELECT 1,BulkColumn FROM OPENROWSET(BULK 'D:\images\products\mobile.jpg',SINGLE_BLOB) AS x

二、UPDATE 操作

UPDATE employee SET name ='Peter' WHERE id = 1
UPDATE employee_bk SET name = e.name,age = e.age 
	FROM employee e
	WHERE (employee_bk.id = e.id) -- 引用外表数据更新

三、DELETE 操作

DELETE FROM employee WHERE id > 20 
DELETE employee FROM dept WHERE (employee.deptId = dept.Id) -- 引用外部字段值来删除记录		
TRUNCATE TABLE employee -- 相当于 DELETE employee，但 DELETE 操作有记录到事务日志可恢复，而 TRUNCATE 则不能恢复
WHILE(SELECT COUNT(*) FROM employee) > 0
BEGIN
	DELETE TOP(500) FROM employee --分块删除
END

四、SELECT 操作

SELECT TOP 5 Id '编号',name '姓名' FROM employee
SELECT TOP 5 PERCENT * FROM employee
SELECT *,age + 18 AS [age after 18 years] FROM employee 
SELECT TOP 10 WITH TIES * FROM employee ORDER BY age DESC -- 已经查询到的记录之外，存在于排序最后一条记录 age 相同的记录时，使用 WITH TIES （“平手，打成平局”）可以将其加入已查询的记录用于返回
SELECT DISTINCT product_id,ship_addr FROM [order] -- 查看不重复的记录
SELECT AVG(DISTINCT price) FROM product -- 排除相同 price
SELECT $IDENTITY FROM employee -- 查询标识列
SELECT $ROWGUID FROM employee -- 查询 GUID 列
SELECT column_name + ' IS NULL AND ' 
	FROM INFORMATION_SCHEMA.columns
	WHERE table_name = 'Employee'
	ORDER BY ORDINAL_POSITION
SELECT name,age FROM S1 WHERE S1.age IN (SELECT age FROM S1  WHERE S1.name='Tom') -- 使用子查询

JOIN 子句

SELECT * 
	FROM employee INNER JOIN dept
	ON employee.dept_id = dept.id -- 关联查询，除 [INNER] JOIN,还有 LEFT [OUTER] JOIN,RIGTH [OUTER] JOIN,FULL [OUTER] JOIN 等
SELECT o.id,p.name,p.price,o.num,p.price * o.num as price
	FROM order AS o INNER JOIN
	product as p ON o.product_id = p.id -- 使用别名，定义计算列
SELECT c.name,h.name as Master
	FROM employee AS c LEFT OUTER JOIN
	employee AS h ON c.header_id = h.id -- 自关联 JOIN	

WHERE 子句

SELECT * FROM employee WHERE id > 7 AND dept_id = 9
SELECT * FROM employee WHERE year(getdate()) - year(entrydate) > 5 -- 获取入职超过5年的员工
SELECT * FROM employee WHERE header_id IS NULL -- 使用 IS NULL 
SELECT * FROM employee WHERE id IN (1,2,4,9) --  使用 IN 
SELECT * FROM employee WHERE id NOT IN (3,4,9) -- 使用 NOT IN
SELECT * FROM employee WHERE id NOT IN (SELECT * FROM employee_bk) -- 使用外表
SELECT * FROM employee WHERE name LIKE '%三%' -- 模糊查询，支持 %(任意)、_(单个)、[](范围内) 和 [^](范围外) 四通通配符，使用[]对通配符进行字符文字转义，如 LIKE '[]]ab' => ']ab'
SELECT * FROM employee WHERE EXISTS(SELECT * FROM employee WHERE id == 99) -- EXISTS 用来检查子查询是否有结果返回
SELECT * FROM employee WHERE id > ANY(SELECT 8) -- 只要子查询中任意记录均满足条件则返回真
SELECT * FROM employee WHERE id > ALL(SELECT 8) -- 只有当子查询所有记录均满足条件返回真

GROUP BY 子句

SELECT dept.name,count(employee.id) as num
	FROM dept INNER JOIN 
	employee ON dept.id = employee.dept_id,
	GROUP BY dept.id
WITH CUBE -- 对结果进行汇总，最后一行记录应为 (NULL,所有员工数)	

Having 子句

SELECT dept.name,count(employee.id) as num
	FROM dept INNER JOIN 
	employee ON dept.id = employee.dept_id,
	GROUP BY dept.id
	HAVING count(employee.id) > 10 -- 只获取员工数大于 10 的分组

使用 Compute 归类

SELECT * FROM [order] COMPUTE SUM(delivery_fee) -- 返回两个结果集，一个是数据表，一个是只有一行一列的运费结果集
SELECT * FROM [order] COMPUTE SUM(delivery_fee) BY receiver_id -- 系统为每个receiver_id 创建两个结果集

UNION

######　　UNION 使用条件：
######　　　１、所有查询的列数和列的顺序必须相同；
######　　　２、要合并的类型可以不同但是必须兼容；
######　　　３、合并的字段名以第一个查询结果为准；

SELECT 1 UNION SELECT 2 -- 合并多个查询结果，

SELECT INTO

SELECT * INTO employee_bk2 FROM employee -- 可用于数据表备份
SELECT * INTO employee_bk3 FROM employee WHERE 1=0 -- 复制表结构创建新表
SELECT * FROM db1.dbo.employee
	JOIN db2.dbo.order
	ON db2.dbo.employee.id = db2.dbo.order.emp_id -- 跨库查询
SELECT * FROM db1.dbo.employee as e
	JOIN db2.dbo.order as o
	ON e.id = o.emp_id

NULL 的使用

1、NULL 表示未知的值，对 NULL 进行运算结果仍为 NULL；
　　2、默认情况下与 NULL 的比较都返回 FALSE；
　　3、将 ANSI_NULLS 设为 OFF时NULL可进行比较，NULL间的比较返回TRUE，其他情况比较均返回 FALSE
　　4、对含有NULL的字段排序时 NULL 字段用于使最小的值
　　5、汇总函数（如SUM、AVG等）会自动忽略 NULL的值

SELECT * FROM employee WHERE dept_id IS NOT NULL
SELECT 1 WHERE NULL = NULL -- FALSE
SELECT 1 WHERE NULL = 1 -- FALSE
SELECT 1 WHERE ISNULL(NULL,'1') = '1' -- TRUE,ISNULL函数 如果空返回后面的默认值
SELECT 1 WHERE ISNULL(1,'1') = '1' __ TRUE,ISNULL返回了数字 1与字符串'1' 比较相等
SELECT 1 WHERE ISNULL(2,'1') = '1' -- FALSE
SET ANSI_NULLS OFF
SELECT 1 WHERE NULL = NULL -- NULL 比较，此时返回 TRUE
SELECT name,ISNULL(remark,'暂无备注') FROM employee -- ISNULL 起到返回默认值的作用

WITH 公用表表达式

 WITH #tmp(name,age) as(
	SELECT name,age FROM employee WHERE id < 6
 )
 SELECT * FROM #tmp

五、OUTPUT 子句

UPDATE employee SET name = '老舅' OUTPUT INSERTED.* WHERE id = 1  -- UPDATE 使用 OUTPUT 子句
INSERT INTO employee(name,age) OUTPUT INSERTED.id,inserted.age,getdate() as insertedtime SELECT '高飞',12 -- 使用 output 子句插入并返回数据

六、Merge