-- 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 使用条件:
###### 1、所有查询的列数和列的顺序必须相同;
###### 2、要合并的类型可以不同但是必须兼容;
###### 3、合并的字段名以第一个查询结果为准;
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