首部图片：By Ayla Verschueren on unsplash.com

本文是博主学习郝斌老师的SQL Sqever的笔记。不推荐看这个视频…… 注意，文章中所有网页链接来自互联网，笔者不保证其信息的有效性、安全性，请自行甄别！

安装SQLServer

安装SQLServer 2016

软件安装可以参考这篇博客：sqlserver入门到精通(2016安装教程)

安装SSMS(SQL Server Management Studio)

搜索软件，进入官网下载页面。
笔者找到的下载链接：aka.ms/ssmsfullset…

介绍

什么是链接

Microsoft SQL Management Studio 只是一个可以访问后台数据库的客户端。
数据库与数据库访问软件是可以分开的。

学习数据库的三个方面

数据库是如何存储数据的？
- 字段，记录，表，约束（主键、外键、唯一键、非空、check、default、触发器）
数据库是如何操作数据的？
- insert、update、delate、存储过程、函数、触发器
数据库是如何显示数据的？
- SELECT

基本操作

链接数据库

打开软件，点击链接。

图片.png

新建数据库

图片.png

在“对象资源管理器”中，选中“数据库”并点击右键，选中“新建数据库”

图片.png

点击确定

图片.png

分离数据库

右键选中数据库，“任务”，“分离”

图片.png

选择断开连接，根据情况选择是否更新，最后点击确定

图片.png

分离后，就可以把数据库文件拷到其他地方去了。

附加数据库

“数据库”右键选择“附加”，在新打开的窗口中点击“添加”按钮，选择附加的数据库。

图片.png

选择后，点击确定。数据库附加成功。

图片.png

删除数据库

选中数据库，右键“删除”

图片.png

点击“确定”即可删除。如果删除失败，把“关闭现有链接”勾选上再删除。

图片.png

新建、登录账号

如图，在对象资源管理器中可以查看当前用户。
新建用户：
1. 选中“用户名”，右键点击，选择“新建用户名”
2. 设置用户名、密码。点击确定，就新建了一个用户。（如果失败，就把“强制密码过期”取消勾选。）
3. 断开连接、重新连接。
4. 成功登录
一般安装SQLServer都会设置密码，建议使用默认登录。（用户名一般都是“sa”）。

建表

选中“表”，右键，新建，表
设置属性。

编辑表

选择表，右键，编辑表。

图片.png

设置索引，外键

右键点击相应的字段即可设置

数据的存储

表的相关数据

字段（列、属性）：一个事物的某一个静态特征（属性）
记录（元组）：字段的组合，表示的是一个具体的事物
表：记录的组合。表示的是同一类型事物的集合。
字段是事物的属性，记录是事物本身，表是事物的集合。

SQL创建表

USE Temp_1106;
-- Temp_1106是已经创建好的数据库
CREATE table dept
(
	dept_id int primary key,
	dept_name nvarchar(100),
	dept_address nvarchar(100)
)

CREATE TABLE emp
(
	-- 这里是注释
	-- 括号括起来
	emp_id int constraint pk_emp_id primary key,
	-- 主键名：emp_id 类型：int（整形）
	-- 约束（constraint）：主键约束：pk_emp_id
	emp_name nvarchar(20) not null,
	-- emp_name：
	--     类型：nvarchar(20)
	--           长度为20的可变字符串
	--           n代表可存储中文等其他字符
	--     not null:不能为空
	emp_sex nchar(1),
	dept_id int constraint fk_dept_id foreign key references dept(dept_id)
	-- foreign key:外键
	-- references ：外键来源：
)

图片.png

什么是约束

定义：对一个表中的属性操作的限制叫做约束
分类：
- 主键约束：不允许重复元素，避免数据冗余
- 外键约束：通过外键约从语法上保证了本事物所关联的其他事物一定是存在的
- 事物与事物的联系是通过外键联系起来的

CHECK 约束

CREATE TABLE student{
	stu_id INT PRIMARY KEY,
	stu_sal INT CHECK (stu_sal >= 1000 AND stu_sal <= 8000 ),
}
INSERT INTO student VALUES(1,1000)
INSERT INTO student VALUES(2,10000)
-- 这一句报错，报错信息：
-- INSERT 语句与 CHECK 约束"CK__student__stu_sal__286302EC"冲突。该冲突发生于数据库"Temp_1106"，表"dbo.student", column 'stu_sal'。

保证事物的取值在合法的范围内。

DEFAULT 约束

-- 保证事物的属性一定有一个值

stu_sec NCHAR(1) DEFAULT ('男')
-- ()可以省略 ''是字符串

-- 如果插入数据时没有给出这个属性值，那么会使用默认值
INSERT INTO student(stu_id,stu_sal) VALUES(3,6000);

UNIQUE 约束

每一个记录的该属性值唯一。
允许其中一个唯一为空。不允许有多个空。
- Oracle 允许多个为空

stu_name NVARCHAR(200) UNIQUE

主键与唯一约束

主键默认非空且唯一。

stu_id INT PRIMARY KEY IDENTITY,
-- IDENTITY 会自动增长
stu_name NVARCHAR(50) UNIQUE NOT NULL,
-- 设置主键时常使用数字编号当主键
-- 不要使用业务中诸如用户名、姓名等当主键。

表与约束

数据库是通过表来解决事务的存储问题的
数据库是通过约束来解决事务取值的有效性和合法性的问题
建表的过程就是指定事物属性及其事物属性各种约束的问题

什么是关系

定义：表与表之间的关系
实现方式：外键
分类：一对一，一对多，多对多

一对一

即可以把表A的主键设置成B的外键，也可以反过来

一对多

A与B一对多，常常对B添上外键。把表A的主键充当B的外键。

多对多

班级-老师：多对多关系
常常在二者之间加入一个表：班级-关系表-老师。即：N-1-N关系
班级一张表、老师一张表、班级与老师的关系——课程——也是一张表。

CREATE TABLE banji
(
	banji_id INT PRIMARY KEY,
	banji_num INT NOT NULL,
	banji_name NVARCHAR(100)
)

CREATE TABLE jiaoshi
(
	jiaoshi_id INT PRIMARY KEY,
	jiaoshi_name NVARCHAR(200)
)

CREATE TABLE banji_jiaoshi_mapping
(
	banji_id INT CONSTRAINT fk_banji_id FOREIGN KEY REFERENCES banji(banji_id),
	jiaoshi_id INT FOREIGN KEY REFERENCES jiaoshi(jiaoshi_id),
	kecheng NVARCHAR(20),
	CONSTRAINT pk_banji_id_jiaoshi_id PRIMARY KEY (banji_id,jiaoshi_id,kecheng)
	-- 整体作为一个主键
)

数据关系图

主键

定义：能够唯一表示一个事物的一个字段或者多个字段的组合，被称为主键。
含有主键的表成为主键表
主键通常都是整数，不建议使用字符串当主键。
主键的值通常是不允许修改的，除非本记录被删除

外键

定义：如果一个表中的若干字段是来自另外若干表的主键或唯一键，则这个若干字段就是外键。
外键通常来自另外表的主键而不是唯一键，因为唯一键有可能是null。
外键不一定是来自另外的表，也可能来自本表。
- 例如一张主键为员工编号的员工表，如果有一列属性为上司编号，那么这个上司编号的内容就来自本表中的员工编号。
含有外键的表叫外键表，外键字段来自的那一张表叫做主键表。

主键表与外键表的删除

先删除外键，再删除主键。

查询

课程所使用的Scott例子可以在这里下载：www.cnblogs.com/fenglinyu/p…

计算列

SELECT * FROM emp;
-- * 表示所有的
-- FROM emp 表示从emp表中查询

图片.png

SELECT empno, ename FROM emp;
-- 从emp表中查询对应列empno、ename

图片.png

SELECT ename, sal*12 AS "年薪", sal "月薪",job FROM emp;
-- AS 重命名

图片.png

注意：上面的重命名SQL Server 中还可以用单引号括起来。然而Oracle 只能用双引号

SELECT 5 FROM emp;
-- 输出的行数是emp表中的行数，每行只有一个字段，值为5
-- 共有14行

图片.png

DISTINCT

不允许重复的。

SELECT DISTINCT deptno FROM emp;

图片.png

DISTINCT可以过滤掉重复的空值

SELECT DISTINCT comm FROM emp;

图片.png

整体过滤

SELECT DISTINCT comm, deptno FROM emp;
-- 把comm与deptno组合过滤
-- 不要理解成“先过滤comm重复的，再把结果结合对应的deptno输出”

图片.png

下面这一条语句是错误的：

SELECT deptno, DISTINCT comm FROM emp;
-- 可以这样想
-- 首先写出deptno，输出时肯定把所有的元组输出出来了
-- 那这样后面的DISTINCT就没有意义了。

BETWEEN

SELECT *  FROM emp
WHERE sal=5000;
-- 输出：7839	KING	PRESIDENT	NULL	1981-11-17 00:00:00.000	5000	NULL	10

-- 查找工资在1500到3000之间（包括1500和3000）的所有员工的信息。
SELECT * FROM emp
WHERE sal BETWEEN 1500 AND 3000;
-- 等价于
SELECT * FROM emp
WHERE sal>=1500 AND sal<=3000;

图片.png

-- 查找工资小于1500或大于3000的所有员工的信息。
SELECT * FROM emp
WHERE sal NOT BETWEEN 1500 AND 3000;
-- 等价于
SELECT * FROM emp
WHERE sal<1500 OR sal>3000;

图片.png

IN

属于若干个孤立的值。

SELECT * FROM emp WHERE sal IN (1000,1500,3000,5000);
-- 等价于
SELECT * FROM emp
WHERE sal=1000 OR sal=1500 OR sal=3000 OR sal=5000;

图片.png

SELECT * FROM emp WHERE sal NOT IN (1000,1500,3000,5000);
-- 等价于
SELECT * FROM emp
WHERE sal!=1000 AND sal!=1500 AND sal!=3000 AND sal!=5000;
-- 不等于：!=或者<>
SELECT * FROM emp
WHERE sal<>1000 AND sal<>1500 AND sal<>3000 AND sal<>5000;

图片.png

TOP

SELECT TOP 2 * FROM emp;

图片.png

SELECT TOP 15 PERCENT * FROM emp;
-- 总共14行，输出15%

图片.png

小练习

把工资在1500到3000之间工资最高的4个输出

SELECT TOP 4 *
FROM emp
WHERE sal BETWEEN 1500 AND 3000
ORDER BY sal DESC
-- ORDER BY 排序
-- DESC 降序

图片.png

NULL

null不能参与<>、!=、=运算！

-- 输出奖金非空的员工的信息
SELECT * FROM emp WHERE comm <> null;
SELECT * FROM emp WHERE comm != null;
SELECT * FROM emp WHERE comm = null;
-- 上面语句输出为空！Error！

SELECT * FROM emp WHERE comm IS NULL;

图片.png

SELECT * FROM emp WHERE comm IS NOT NULL;

图片.png

NULL 不能参与数学运算！

SELECT empno,ename,sal*12+comm "年薪" FROM emp;
-- 由于有的人奖金（comm）为NULL，其年薪为NULL，显然错误。

图片.png

ORDER BY

SELECT * FROM emp ORDER BY sal; 
-- 默认按照升序排序

图片.png

SELECT * FROM emp
ORDER BY deptno,sal;
-- 先按照顺序排列第一项，第一项相同的再排第二项

图片.png

SELECT * FROM emp
ORDER BY deptno DESC,sal ASC;
-- deptno降序排列，sal升序排列
-- ASC一般可以省略不写，默认按照升序排列

图片.png

模糊查询

WHERE 字段 LIKE 匹配
通配符：
- % 任意长度字符（≥0）
- _ 一个字符
- [a-f] a到f之间的任意单个字符
- [a,b,c] a,b,c之间的任意单个字符
- [^a-f] 不是a到f之间的任意单个字符
注意，匹配字段使用单引号括起来

SELECT *
FROM emp
WHERE ename LIKE '%A%'
-- 查询包含A的内容

图片.png

SELECT *
FROM emp
WHERE ename LIKE 'A%'
-- 查询首字母为A的内容

图片.png

SELECT *
FROM emp
WHERE ename LIKE '_A%'
-- 第二个字母为A的输出

图片.png

SELECT *
FROM emp
WHERE ename LIKE '_[E-L]%'
-- 第二个字母为E-L的输出

图片.png

SELECT *
FROM emp
WHERE ename LIKE '_[A,L,P]%'
-- 第二个字母为A或L或P的输出

图片.png

SELECT *
FROM emp
WHERE ename LIKE '_[^E-L ]%'
-- 第二个字母不是E到L的输出

图片.png

SELECT *
FROM emp
WHERE ename LIKE '_[^A,L,P]%'
-- 第二个字母不是A、L、P的输出

图片.png

匹配特殊符号

SELECT * FROM student
WHERE name LIKE `%\%%` ESCAPE '\';
-- 包含符号%的数据
-- ESCAPE 后面的字符作为转义字符的开始。最好使用`\`。

聚合函数

函数的分类

单行函数：每行返回一个值
多行函数：多行返回一个值。
- 聚合函数是多行函数

聚合函数们

COUNT() 记录的个数
MAX() 最大值
MIN() 最小值
AVG() 平均数
SUM() 求和

SELECT 
	COUNT(*) AS "ALL",
	COUNT(deptno) AS "ALL_deptno",
	COUNT (DISTINCT deptno) AS "deptno",
	COUNT (DISTINCT comm) AS "comm"
	-- comm中含有null，COUNT 不包括 null
FROM emp;

图片.png

注意的问题

SELECT max(sal) AS "最高工资",min(sal) AS "最低工资",COUNT(*) "员工人数" FROM emp;

图片.png

SELECT max(sal),lower(ename) FROM emp;
--  错误！单行函数与多行函数不能混用！

SELECT max(sal),ename FROM emp;
--  这样也不行！

##分组查询

GROUP BY

GROUP BY：分组。
- 输出每一行表示一个组。

SELECT deptno,ename
FROM emp
GROUP BY deptno
-- 错误
-- 因为GRUOP BY 之后，输出的分组的信息
-- ename 是组内部的信息。
-- 报错：
-- 选择列表中的列 'emp.ename' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

总结：使用GROUP BY后只能出现分组后的整体信息，不能出现组内的详细信息。

SELECT deptno, avg(sal) AS "部门平均水平", ename
FROM emp
GROUP BY deptno
-- 这个语句是错误的：
-- 选择列表中的列 'emp.ename' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

当有多个分组时，输出以GROUP BY最后一个属性分成最小分组，聚合函数按最小分组输出。
聚合函数通常用于反应分组的信息。

SELECT 
	deptno,
	job,
	AVG(sal) AS "该部门此工作的平均工资",
	COUNT(*) AS "部门人数",
	SUM(sal) AS "总公司",
	MIN(sal) AS "最低工资"
FROM emp
GROUP BY deptno, job
-- 先对deptno分组，再对job分组
-- avg(sal)统计的是deptno组中job小组对应的平均工资

图片.png

空值会被分组吗？

SELECT comm, COUNT(*)
FROM emp
GROUP BY comm

图片.png

HAVING

HAVING用于对分组后的数据进行筛选。
输出部门平均工资大于1500的部门的部门编号与其平均工资：

SELECT deptno , AVG(sal) AS "平均工资"
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000

图片.png

HAVING 与 WHERE

把姓名不包含A的所有员工按部门分组，再统计输出部门平均工资大于3000的部门编号与其工资平均水平

SELECT deptno, AVG(sal) AS "平均工资"
FROM emp
WHERE ename NOT LIKE '%A%'
GROUP BY deptno
HAVING AVG(sal) > 2000

图片.png

相当于先使用WHERE对原来表中数据进行筛选，然后GROUP BY对deptno进行分组，最后对分组的结果使用HAVING进行筛选。
HAVING过滤分组的信息，所以必须先要有GROUP BY。如果没有GROUP BY，那吗HAVING就把结果当做一组来处理。
WHERE、HAVING的顺序不能变化！

INTO

SELECT ....
INTO table_name
....

将查询结果保存在一个新表中

连接查询

连接查询简述

定义：讲两个表或者两个以上的表以一定的连接条件连接起来，从中检索出满足条件的数据。

笛卡尔积

SELECT * FROM emp,dept;

图片.png

输出内容为emp表的每个记录与dept表中每个记录组合的结果。即笛卡尔积。

SELECT * 
FROM emp,dept
WHERE emp.deptno=dept.deptno;

图片.png

使用WHERE可以对笛卡尔积进行筛选，筛选出emp表下deptno等于dept表下deptno的记录。此时的记录就是每位员工的信息以及其所属分部的信息。

JOIN ON

内连接：只返回满足连接条件的记录

SELECT
	"E".ename "员工姓名",
	"D".dname "部门名称"
	-- 使用别名"E"、"D"代替表emp、dept
	-- 重命名AS省略，直接重命名
FROM emp "E"
	-- 对表emp重命名为"E"
JOIN dept "D" ON "E".deptno = "D".deptno;
	-- JOIN跟上要连接（结合）的表格
	-- 将两个表中的deptno字段作为连接条件
	-- 由于都叫做deptno，为了区分
	-- 分别在 FROM 与 JOIN处加上别名

图片.png

这里JOIN默认是内连接。格式是INNER JOIN ...。INNER一般省略。
输出工资大于2000的员工名称、工资以及他们的部门名称
注意JOIN的位置！

SELECT "E".ename "Name" , "E".sal  "Salary", "D".dname
FROM emp "E"
JOIN dept "D" ON "E".deptno="D".deptno
WHERE "E".sal>=2000

图片.png

多表连接

输出工资大于2000的员工的姓名和部门的名称以及工资的等级
salgrade表的内容：

图片.png

答案：

SELECT "E".ename, "D".dname,"S".grade
FROM emp "E"
JOIN dept "D" ON "E".deptno="D".deptno
JOIN salgrade "S" ON "E".sal>= "S".losal AND "E".sal<=hisal
WHERE "E".sal>=2000

图片.png

练习

输出名字不包含字母O中工资最高的前三名的每个员工的姓名、工资、工资等级、部门名称

SELECT TOP 3
	"E".ename,
	"E".sal,
	"S".grade,
	"D".dname
FROM emp "E"
JOIN dept "D"
	ON "E".deptno="D".deptno
JOIN salgrade "S"
	ON "E".sal BETWEEN "S".LOSAL AND "S".hisal
WHERE "E".ename NOT LIKE '%O%'
ORDER BY "E".sal DESC

图片.png

查找每个部门的编号，输出该部门的所有员工的平均工资与平均工资的等级

SELECT "T".deptno "部门编号","avg_sal" "部门平均工资","S".grade "工资等级"
FROM(
	SELECT
		deptno,
		AVG(sal) "avg_sal"
	FROM emp
	GROUP BY deptno
) "T"
JOIN salgrade "S"
	ON "T".avg_sal BETWEEN "S".losal AND "S".hisal

图片.png

查找每个部门，输出该部门的编号、名称、该部门的所有员工的平均工资与平均工资的等级

SELECT "T".deptno "部门编号","D".dname,"avg_sal" "部门平均工资","S".grade "工资等级"
FROM(
	SELECT
		deptno,
		AVG(sal) "avg_sal"
	FROM emp
	GROUP BY deptno
) "T"
JOIN salgrade "S"
	ON "T".avg_sal BETWEEN "S".losal AND "S".hisal
JOIN dept "D"
	ON "T".deptno="D".deptno

图片.png

输出emp表中所有领导的姓名

SELECT ename
FROM emp
WHERE empno IN (
	SELECT mgr
	FROM emp
)

图片.png

输出emp表中所有非领导的姓名

SELECT ename
FROM emp
WHERE empno NOT IN (
	SELECT mgr
	FROM emp
)

上面的语句是错误的。因为mgr会有空值。IN与null组合会带来问题。

SELECT ename
FROM emp
WHERE empno NOT IN (
	SELECT mgr
	FROM emp
	WHERE mgr IS NOT NULL
)

图片.png

平均薪水最高的部门与部门的平均工资

SELECT TOP 1 deptno , AVG(sal)
FROM emp
GROUP BY deptno
ORDER BY AVG(sal) DESC

图片.png

排除掉工资最低的一个人后，输出剩下人中工资最低的三名员工的信息

SELECT TOP 3 "E".empno , "E".ename,"E".sal
FROM emp "E"
WHERE "E".empno NOT IN(
	SELECT TOP 1 empno
	FROM emp
	ORDER BY sal
)
ORDER BY "E".sal

SELECT TOP 3 "E".empno , "E".ename,"E".sal
FROM (
	SELECT empno,ename,sal
	FROM emp
	WHERE sal > (SELECT MIN(sal) FROM emp)
) "E"
ORDER BY "E".sal

顺序总结

SELECT ...
FROM ...
JOIN B ON ....
JOIN C ON ....
GROUP BY ....
HAVING ....
ORDER BY ....

外连接

外连接：不但返回满足条件的记录，部分不满足条件的记录也会返回

左外连接

LEFT JOIN ..
左表的记录与右表的每一条记录匹配，如果匹配成功输出左右两表连接的记录。如果右表中有多行匹配，那么输出多行。如果右表中没有能够匹配左表的记录，那么输出坐标左表的记录，右表对应的记录为空。

SELECT * 
FROM emp "E"
LEFT JOIN dept "D"
	ON "E".deptno = "D".deptno

完全连接

FULL JOIN
- 输出两个表中匹配的所有行的记录。
- 左表中那些在右表中找不到匹配的行的记录，这些记录的右边全为null
- 右表中那些在左表中找不到匹配的行的记录，这些记录的左边全为null

交叉连接

CROSS JOIN
就是笛卡尔积

自连接

自己与自己连接

联合

输出每个员工的姓名、工资和领导的姓名

SELECT
	"E".ename "name",
	"E".sal "Salary",
	"D".sal "Manager"
FROM emp "E"
LEFT JOIN emp "D"
	ON "E".mgr="D".empno

分页查询

工资从高到低排序，输出工资排名7-9员工的信息。

SELECT TOP 3 *
FROM emp
WHERE empno NOT IN(
	SELECT TOP 6 empno FROM emp ORDER BY SAL desc
	-- 排除前6名
)
ORDER BY sal DESC

图片.png

视图

视图概念

CREATE VIEW v$_emp_1
AS
	SELECT deptno,AVG(sal) "avg_sal"
	FROM emp
	GROUP BY deptno

SELECT * FROM v$_emp_1

图片.png

可以理解为创建了一个SELECT语句。逻辑上当做一个表。
使用视图可以简化查询。避免了代码的冗余。

视图的格式

CREATE VIEW 名字
	-- SELECT 前面不能加 BGEIN
	SELECT 语句
	-- SELECT 后面不能加 END

视图的优缺点

简化查询，增加数据的保密性
缺点：增加数据库维护成本。并且视图只是简化查询，不会加快查询速度。
注意：创建视图的SELECT语句中必须为所有的计算列指定别名。视图不是物理表，而是虚拟表。不建议通过更新视图来更新原始数据。

事务

概念

事务主要用来保证数据的合理性和并发处理的能力！
避免数据处理不合理的中间状态。

创建事务

开始事务：BEGIN TRANSACTION
提交事务：COMMIT TRANSACTION
回滚事务：ROLLBACK TRANSACTION

索引

优化查询。可以加快查询速度。

存储过程

游标

TL_SQL

不是没有更新完，而是原教程已结束。

2022年11月13日

郝斌 SqlServer 基础课程笔记

安装SQLServer