T-SQL 语句（三）—— 数据（记录）操作

-- 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 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_bkINSERT TOP 10 INTO employee_bk(name,age)    SELECT name,age FROM employee ORDER BY ID DESC -- 向 employee_bk 表插入 employee 表的前10条记录 -- 以下语句可用于拷贝数据数据，获取数据并将age全部改成age+2INSERT employee_bk(name,age)     SELECT name,age+2 FROM      (SELECT TOP (3) * FROM employee ORDER BY ID DESC) AS M      ORDER BY M.id ASCINSERT INTO user_proc_info DEFAULT VALUES -- 插入默认值记录INSERT user_proc_info([进程 ID],[定线程上下文 ID],进程状态,登录名,主机名,[阻塞进程的系统进程 ID],数据库名,运行命令,[请求 ID])    exec sp_who -- Insert 语句里使用 exec 子句

二、UPDATE 操作

UPDATE employee SET name ='Peter' WHERE id = 1UPDATE 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 则不能恢复

四、SELECT 操作

SELECT TOP 5 FROM employeeSELECT TOP 5 PERCENT * FROM employeeSELECT *,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 -- 排除相同 priceSELECT $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

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 = 9SELECT * 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 INSELECT * 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.idWITH 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 NULLSELECT 1 WHERE NULL = NULL -- FALSESELECT 1 WHERE NULL = 1 -- FALSESELECT 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' -- FALSESET ANSI_NULLS OFFSELECT 1 WHERE NULL = NULL -- NULL 比较，此时返回 TRUESELECT 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 子句插入并返回数据