首部图片：By Ayla Verschueren on unsplash.com * * * 本文是博主学习郝斌老师的SQL Sqever的笔记。***不推荐***看这个视频…… 注意，文章中所有**网页链接**来自互联网，笔者不保证其信息的有效性、安全性，**请自行甄别！** * * * # 安装SQLServer ## 安装SQLServer 2016 - 软件安装可以参考这篇博客：[sqlserver入门到精通(2016安装教程)](https://www.cnblogs.com/huhongy/p/12450371.html) ## 安装SSMS(SQL Server Management Studio) - 搜索软件，进入官网下载页面。 - 笔者找到的下载链接：[https://aka.ms/ssmsfullsetup](https://aka.ms/ssmsfullsetup) # 介绍 ## 什么是链接 - Microsoft SQL Management Studio 只是一个可以访问后台数据库的客户端。 - 数据库与数据库访问软件是可以分开的。 ## 学习数据库的三个方面 - 数据库是如何存储数据的？ - 字段，记录，表，约束（主键、外键、唯一键、非空、check、default、触发器） - 数据库是如何操作数据的？ - insert、update、delate、存储过程、函数、触发器 - 数据库是如何显示数据的？ - SELECT # 基本操作 ## 链接数据库 - 打开软件，点击链接。 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/f95a947b9d1841c4b4852d730ba4f542~tplv-k3u1fbpfcp-watermark.image?) ## 新建数据库 ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b584ac1b53f8432b8123ad2d25b5cb30~tplv-k3u1fbpfcp-watermark.image?) - 在“对象资源管理器”中，选中“数据库”并点击右键，选中“新建数据库” ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1e90888f9fef4a0db19091debcde0dd8~tplv-k3u1fbpfcp-watermark.image?) - 点击确定 ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b89be424fc21493786a244f909ee3f6e~tplv-k3u1fbpfcp-watermark.image?) ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/17caa9969fb141568e3bcdf36bfa1cb6~tplv-k3u1fbpfcp-watermark.image?) ## 分离数据库 - 右键选中数据库，“任务”，“分离” ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8db2b02bca8b425085ab70eb4dc51b9c~tplv-k3u1fbpfcp-watermark.image?) - 选择断开连接，根据情况选择是否更新，最后点击确定 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/613878be01184c66883bf9e03b8fa298~tplv-k3u1fbpfcp-watermark.image?) - 分离后，就可以把数据库文件拷到其他地方去了。 ## 附加数据库 - “数据库”右键选择“附加”，在新打开的窗口中点击“添加”按钮，选择附加的数据库。 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b654fb077aa74a36909ba758fc193ef6~tplv-k3u1fbpfcp-watermark.image?) ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ddf69022fff9441b833625b816c48b3c~tplv-k3u1fbpfcp-watermark.image?) - 选择后，点击确定。数据库附加成功。 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a8b5cb4c79a646758db58c840495875c~tplv-k3u1fbpfcp-watermark.image?) ## 删除数据库 - 选中数据库，右键“删除” ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b75822b184da4709b0c2ec9eb600d08d~tplv-k3u1fbpfcp-watermark.image?) - 点击“确定”即可删除。如果删除失败，把“关闭现有链接”勾选上再删除。 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d37b0904e35d4ce1b1dac41969fcf221~tplv-k3u1fbpfcp-watermark.image?) ## 新建、登录账号 - 如图，在对象资源管理器中可以查看当前用户。 ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/bc298285756840e3aa0effea1149fe0e~tplv-k3u1fbpfcp-watermark.image?) - 新建用户： 1. 选中“用户名”，右键点击，选择“新建用户名” ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1f5cd602455541718d634bcf6679766b~tplv-k3u1fbpfcp-watermark.image?) 2. 设置用户名、密码。点击确定，就新建了一个用户。（如果失败，就把“强制密码过期”取消勾选。） ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/fe833ca6ea1c47928d264c374cc3a231~tplv-k3u1fbpfcp-watermark.image?) 3. 断开连接、重新连接。 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/346465923ebf4a3899ed5cd70ebf713c~tplv-k3u1fbpfcp-watermark.image?) 4. 成功登录 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/7e38285dbbdf4f17aa6854c30a0fd79f~tplv-k3u1fbpfcp-watermark.image?) - 一般安装SQLServer都会设置密码，建议使用默认登录。（用户名一般都是“sa”）。 ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a5ac0f3b2939434fb0f64194db2ceadb~tplv-k3u1fbpfcp-watermark.image?) ## 建表 - 选中“表”，右键，新建，表 ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/18bf9d2641544ec6b71fe7a9585174f2~tplv-k3u1fbpfcp-watermark.image?) - 设置属性。 ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/2f9f78dbe0eb45a783981ca473d0017f~tplv-k3u1fbpfcp-watermark.image?) ## 编辑表 - 选择表，右键，编辑表。 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4513ea7454884087a8417811d6c869d1~tplv-k3u1fbpfcp-watermark.image?) ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/50c02ebffff244ca95310703ff1aacdf~tplv-k3u1fbpfcp-watermark.image?) ## 设置索引，外键 - 右键点击相应的字段即可设置 ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/7440d8a07f54488c88a43595549697be~tplv-k3u1fbpfcp-watermark.image?) # 数据的存储 ## 表的相关数据 - 字段（列、属性）：一个事物的某一个静态特征（属性） - 记录（元组）：字段的组合，表示的是一个具体的事物 - 表：记录的组合。表示的是同一类型事物的集合。 - 字段是事物的属性，记录是事物本身，表是事物的集合。 ## SQL创建表 ```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](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/82f1c44b14974173a3172507044cfa37~tplv-k3u1fbpfcp-watermark.image?) ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4a726615cdf544cd86fad681132d8785~tplv-k3u1fbpfcp-watermark.image?) ## 什么是约束 - 定义：对一个表中的属性操作的限制叫做约束 - 分类： - 主键约束：不允许重复元素，避免数据冗余 - 外键约束：通过外键约从语法上保证了本事物所关联的其他事物一定是存在的 - 事物与事物的联系是通过外键联系起来的 ## CHECK 约束 ```sql 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 约束 -- 保证事物的属性一定有一个值 ```sql stu_sec NCHAR(1) DEFAULT ('男') -- ()可以省略 ''是字符串 ``` ```sql -- 如果插入数据时没有给出这个属性值，那么会使用默认值 INSERT INTO student(stu_id,stu_sal) VALUES(3,6000); ``` ## UNIQUE 约束 - 每一个记录的该属性值唯一。 - 允许其中一个唯一为空。不允许有多个空。 - Oracle 允许多个为空 ```sql stu_name NVARCHAR(200) UNIQUE ``` ### 主键与唯一约束 - 主键默认非空且唯一。 ```sql stu_id INT PRIMARY KEY IDENTITY, -- IDENTITY 会自动增长 stu_name NVARCHAR(50) UNIQUE NOT NULL, -- 设置主键时常使用数字编号当主键 -- 不要使用业务中诸如用户名、姓名等当主键。 ``` ## 表与约束 - 数据库是通过表来解决事务的存储问题的 - 数据库是通过约束来解决事务取值的有效性和合法性的问题 - 建表的过程就是指定事物属性及其事物属性各种约束的问题 ## 什么是关系 - 定义：表与表之间的关系 - 实现方式：外键 - 分类：一对一，一对多，多对多 ### 一对一 - 即可以把表A的主键设置成B的外键，也可以反过来 ### 一对多 - A与B一对多，常常对B添上外键。把表A的主键充当B的外键。 ### 多对多 - 班级-老师：多对多关系 - 常常在二者之间加入一个表：班级-关系表-老师。即：N-1-N关系 - 班级一张表、老师一张表、班级与老师的关系——课程——也是一张表。 ```sql 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例子可以在这里下载：[https://www.cnblogs.com/fenglinyu/p/11067097.html](https://www.cnblogs.com/fenglinyu/p/11067097.html) ## 计算列 ```sql SELECT * FROM emp; -- * 表示所有的 -- FROM emp 表示从emp表中查询 ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6be9089a66dd49b38bad63a2fa5f043d~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT empno, ename FROM emp; -- 从emp表中查询对应列empno、ename ``` ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/92b440857e5942e1858821211618f8db~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT ename, sal*12 AS "年薪", sal "月薪",job FROM emp; -- AS 重命名 ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/06e2a6bd5ba64b76884d4fd1bd8a293e~tplv-k3u1fbpfcp-watermark.image?) - 注意：上面的重命名SQL Server 中还可以用单引号括起来。然而Oracle 只能用双引号 ```sql SELECT 5 FROM emp; -- 输出的行数是emp表中的行数，每行只有一个字段，值为5 -- 共有14行 ``` ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/35bb523c7b9b4f68a6952caafadf85c5~tplv-k3u1fbpfcp-watermark.image?) ## DISTINCT - 不允许重复的。 ```sql SELECT DISTINCT deptno FROM emp; ``` ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e874b2236fb64fbfb924abef252b0531~tplv-k3u1fbpfcp-watermark.image?) - `DISTINCT`可以过滤掉重复的空值 ```sql SELECT DISTINCT comm FROM emp; ``` ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d6650d168e6f42e1b1d6fb4f2226d988~tplv-k3u1fbpfcp-watermark.image?) - 整体过滤 ```sql SELECT DISTINCT comm, deptno FROM emp; -- 把comm与deptno组合过滤 -- 不要理解成“先过滤comm重复的，再把结果结合对应的deptno输出” ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0ff925f5a72542e99bfd497799fb2d2d~tplv-k3u1fbpfcp-watermark.image?) - 下面这一条语句是错误的： ```sql SELECT deptno, DISTINCT comm FROM emp; -- 可以这样想 -- 首先写出deptno，输出时肯定把所有的元组输出出来了 -- 那这样后面的DISTINCT就没有意义了。 ``` ## BETWEEN ```sql SELECT * FROM emp WHERE sal=5000; -- 输出：7839 KING PRESIDENT NULL 1981-11-17 00:00:00.000 5000 NULL 10 ``` ```sql -- 查找工资在1500到3000之间（包括1500和3000）的所有员工的信息。 SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000; -- 等价于 SELECT * FROM emp WHERE sal>=1500 AND sal<=3000; ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/fdb913e2135a478e84a59fcfe4786205~tplv-k3u1fbpfcp-watermark.image?) ```sql -- 查找工资小于1500或大于3000的所有员工的信息。 SELECT * FROM emp WHERE sal NOT BETWEEN 1500 AND 3000; -- 等价于 SELECT * FROM emp WHERE sal<1500 OR sal>3000; ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0ee79bc38f3a45bca6eb0a7d605be6ea~tplv-k3u1fbpfcp-watermark.image?) ## IN - 属于若干个孤立的值。 ```sql 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](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/51e4a4d7f9314a03a70701b2821ee9fc~tplv-k3u1fbpfcp-watermark.image?) ```sql 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](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/2d174772ee9c485da83e3648d9988f11~tplv-k3u1fbpfcp-watermark.image?) ## TOP ```sql SELECT TOP 2 * FROM emp; ``` ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e0a806b7076e46a18623faf1b46413f0~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT TOP 15 PERCENT * FROM emp; -- 总共14行，输出15% ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/3379eb61dffe4f37b0952fe438660dc6~tplv-k3u1fbpfcp-watermark.image?) ## 小练习 - 把工资在1500到3000之间工资最高的4个输出 ```sql SELECT TOP 4 * FROM emp WHERE sal BETWEEN 1500 AND 3000 ORDER BY sal DESC -- ORDER BY 排序 -- DESC 降序 ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/717613829af04f29995b6110d57a510a~tplv-k3u1fbpfcp-watermark.image?) ## NULL - null不能参与<>、!=、=运算！ ```sql -- 输出奖金非空的员工的信息 SELECT * FROM emp WHERE comm <> null; SELECT * FROM emp WHERE comm != null; SELECT * FROM emp WHERE comm = null; -- 上面语句输出为空！Error！ ``` ```sql SELECT * FROM emp WHERE comm IS NULL; ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a15efd3c2e6e494687a7d8f035e223c7~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp WHERE comm IS NOT NULL; ``` ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/2183fd257aae4750b4b3ba54032c1d62~tplv-k3u1fbpfcp-watermark.image?) - NULL 不能参与数学运算！ ```sql SELECT empno,ename,sal*12+comm "年薪" FROM emp; -- 由于有的人奖金（comm）为NULL，其年薪为NULL，显然错误。 ``` ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ffb1b620ace84181b318ccb1402c95ad~tplv-k3u1fbpfcp-watermark.image?) ## ORDER BY ```sql SELECT * FROM emp ORDER BY sal; -- 默认按照升序排序 ``` ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d1a2199ed125409bb591fad5d392a959~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp ORDER BY deptno,sal; -- 先按照顺序排列第一项，第一项相同的再排第二项 ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4fef26fe74d44ce99886dba7326c2646~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp ORDER BY deptno DESC,sal ASC; -- deptno降序排列，sal升序排列 -- ASC一般可以省略不写，默认按照升序排列 ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0b824d9b22f14a93bcfac9659ee7b9e4~tplv-k3u1fbpfcp-watermark.image?) ## 模糊查询 - `WHERE 字段 LIKE 匹配` - 通配符： - `%` 任意长度字符（≥0） - `_` 一个字符 - `[a-f]` a到f之间的任意单个字符 - `[a,b,c]` a,b,c之间的任意单个字符 - `[^a-f]` 不是a到f之间的任意单个字符 - 注意，匹配字段使用单引号括起来 ```sql SELECT * FROM emp WHERE ename LIKE '%A%' -- 查询包含A的内容 ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9974fc5832204c96b8bc6f1b2fcf7a22~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp WHERE ename LIKE 'A%' -- 查询首字母为A的内容 ``` ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d11eb656fdf74dbeba05442653b41fba~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp WHERE ename LIKE '_A%' -- 第二个字母为A的输出 ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d1e1c79535074e3783b7a948676a83fd~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp WHERE ename LIKE '_[E-L]%' -- 第二个字母为E-L的输出 ``` ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/65b4e5c6d1f4454aaf1e61c8207cd2ae~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp WHERE ename LIKE '_[A,L,P]%' -- 第二个字母为A或L或P的输出 ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/3e973b50cd554d58a31e2a77ac113f1a~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp WHERE ename LIKE '_[^E-L ]%' -- 第二个字母不是E到L的输出 ``` ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/436479c8621645118637e0bb1e0ec7ee~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT * FROM emp WHERE ename LIKE '_[^A,L,P]%' -- 第二个字母不是A、L、P的输出 ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ef6784d4910548bb9d0779a6062f265f~tplv-k3u1fbpfcp-watermark.image?) - 匹配特殊符号 ``` SELECT * FROM student WHERE name LIKE `%\%%` ESCAPE '\'; -- 包含符号%的数据 -- ESCAPE 后面的字符作为转义字符的开始。最好使用`\`。 ``` ## 聚合函数 ### 函数的分类 - 单行函数：每行返回一个值 - 多行函数：多行返回一个值。 - 聚合函数是多行函数 ### 聚合函数们 - COUNT() 记录的个数 - MAX() 最大值 - MIN() 最小值 - AVG() 平均数 - SUM() 求和 ```sql 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](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/fa591afcdd814a959fb60be69b2046e7~tplv-k3u1fbpfcp-watermark.image?) ### 注意的问题 ```sql SELECT max(sal) AS "最高工资",min(sal) AS "最低工资",COUNT(*) "员工人数" FROM emp; ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/73f4279276234700891bee265c4e94f0~tplv-k3u1fbpfcp-watermark.image?) ```sql SELECT max(sal),lower(ename) FROM emp; -- 错误！单行函数与多行函数不能混用！ ``` ```sql SELECT max(sal),ename FROM emp; -- 这样也不行！ ``` ##分组查询 # GROUP BY - GROUP BY：分组。 - 输出每一行表示一个组。 ```sql SELECT deptno,ename FROM emp GROUP BY deptno -- 错误 -- 因为GRUOP BY 之后，输出的分组的信息 -- ename 是组内部的信息。 -- 报错： -- 选择列表中的列 'emp.ename' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。 ``` - 总结：使用GROUP BY后只能出现分组后的整体信息，不能出现组内的详细信息。 ```sql SELECT deptno, avg(sal) AS "部门平均水平", ename FROM emp GROUP BY deptno -- 这个语句是错误的： -- 选择列表中的列 'emp.ename' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。 ``` - 当有多个分组时，输出以GROUP BY最后一个属性分成最小分组，聚合函数按最小分组输出。 - 聚合函数通常用于反应分组的信息。 ```sql 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](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ec7ac551adeb48ed8c7a6207f74dbd00~tplv-k3u1fbpfcp-watermark.image?) - 空值会被分组吗？ ```sql SELECT comm, COUNT(*) FROM emp GROUP BY comm ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/c603c3083a124692afedbea775a2b1c1~tplv-k3u1fbpfcp-watermark.image?) ## HAVING - HAVING用于对分组后的数据进行筛选。 - 输出部门平均工资大于1500的部门的部门编号与其平均工资： ``` SELECT deptno , AVG(sal) AS "平均工资" FROM emp GROUP BY deptno HAVING AVG(sal) > 2000 ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/3f62ba61e60441c2a15132ece4d36ed2~tplv-k3u1fbpfcp-watermark.image?) ### HAVING 与 WHERE - 把姓名不包含A的所有员工按部门分组，再统计输出部门平均工资大于3000的部门编号与其工资平均水平 ```sql SELECT deptno, AVG(sal) AS "平均工资" FROM emp WHERE ename NOT LIKE '%A%' GROUP BY deptno HAVING AVG(sal) > 2000 ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6dca498305fa4b0d9e6a4c104e376e64~tplv-k3u1fbpfcp-watermark.image?) - 相当于先使用WHERE对原来表中数据进行筛选，然后GROUP BY对deptno进行分组，最后对分组的结果使用HAVING进行筛选。 - HAVING过滤分组的信息，所以必须先要有GROUP BY。如果没有GROUP BY，那吗HAVING就把结果当做一组来处理。 - WHERE、HAVING的顺序不能变化！ ## INTO ```sql SELECT .... INTO table_name .... ``` - 将查询结果保存在一个新表中 # 连接查询 ## 连接查询简述 - 定义：讲两个表或者两个以上的表以一定的连接条件连接起来，从中检索出满足条件的数据。 ## 笛卡尔积 ```sql SELECT * FROM emp,dept; ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/5933b18c775742c6997946df23d57b1d~tplv-k3u1fbpfcp-watermark.image?) - 输出内容为emp表的每个记录与dept表中每个记录组合的结果。即笛卡尔积。 ```sql SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno; ``` ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ee03308c1d634cc0b1b0e12a8f851bfb~tplv-k3u1fbpfcp-watermark.image?) - 使用WHERE可以对笛卡尔积进行筛选，筛选出emp表下deptno等于dept表下deptno的记录。此时的记录就是每位员工的信息以及其所属分部的信息。 ## JOIN ON - 内连接：只返回满足连接条件的记录 ```sql 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](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/fa07b2e19f8c42d5beb7ebcee1c3a264~tplv-k3u1fbpfcp-watermark.image?) - 这里JOIN默认是内连接。格式是`INNER JOIN ...`。`INNER`一般省略。 - 输出工资大于2000的员工名称、工资以及他们的部门名称 - 注意JOIN的位置！ ```sql 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](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/61233f80b30e44d8ae15e2f2c26775bc~tplv-k3u1fbpfcp-watermark.image?) ## 多表连接 - 输出工资大于2000的员工的姓名和部门的名称以及工资的等级 - salgrade表的内容： ![图片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/28ebb523432d45c69bf60cd46213cf72~tplv-k3u1fbpfcp-watermark.image?) - 答案： ```sql 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](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/69e538acf1cb430fb42b476f90e09f1a~tplv-k3u1fbpfcp-watermark.image?) ## 练习 ### 输出名字不包含字母O中工资最高的前三名的每个员工的姓名、工资、工资等级、部门名称 ```sql 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](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a7f455556625466aa5f97cafaf947277~tplv-k3u1fbpfcp-watermark.image?) ### 查找每个部门的编号，输出该部门的所有员工的平均工资与平均工资的等级 ```sql 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](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/dc99fb41fcbb48ccba6683064c1f6717~tplv-k3u1fbpfcp-watermark.image?) ### 查找每个部门，输出该部门的编号、名称、该部门的所有员工的平均工资与平均工资的等级 ```sql 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](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1f9e1f7f2a3747618b4972de88674524~tplv-k3u1fbpfcp-watermark.image?) ### 输出emp表中所有领导的姓名 ```sql SELECT ename FROM emp WHERE empno IN ( SELECT mgr FROM emp ) ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/394b04d29f764908a5da616f3f3b48b8~tplv-k3u1fbpfcp-watermark.image?) ### 输出emp表中所有非领导的姓名 ```sql SELECT ename FROM emp WHERE empno NOT IN ( SELECT mgr FROM emp ) ``` - 上面的语句是错误的。因为mgr会有空值。IN与null组合会带来问题。 ```sql SELECT ename FROM emp WHERE empno NOT IN ( SELECT mgr FROM emp WHERE mgr IS NOT NULL ) ``` ![图片.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8c5474ccccc2443b9357412acd7697a2~tplv-k3u1fbpfcp-watermark.image?) ### 平均薪水最高的部门与部门的平均工资 ```sql SELECT TOP 1 deptno , AVG(sal) FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC ``` ![图片.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/184df92ee1524d448a882107aa6dcdb7~tplv-k3u1fbpfcp-watermark.image?) ### 排除掉工资最低的一个人后，输出剩下人中工资最低的三名员工的信息 ```sql 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 ``` ## 顺序总结 ```sql SELECT ... FROM ... JOIN B ON .... JOIN C ON .... GROUP BY .... HAVING .... ORDER BY .... ``` ## 外连接 - 外连接：不但返回满足条件的记录，部分不满足条件的记录也会返回 ## 左外连接 - `LEFT JOIN ..` - 左表的记录与右表的每一条记录匹配，如果匹配成功输出左右两表连接的记录。如果右表中有多行匹配，那么输出多行。如果右表中没有能够匹配左表的记录，那么输出坐标左表的记录，右表对应的记录为空。 ```sql SELECT * FROM emp "E" LEFT JOIN dept "D" ON "E".deptno = "D".deptno ``` ## 完全连接 - `FULL JOIN` - 输出两个表中匹配的所有行的记录。 - 左表中那些在右表中找不到匹配的行的记录，这些记录的右边全为null - 右表中那些在左表中找不到匹配的行的记录，这些记录的左边全为null ## 交叉连接 - `CROSS JOIN` - 就是笛卡尔积 ## 自连接 - 自己与自己连接 ## 联合 - 输出每个员工的姓名、工资和领导的姓名 ```sql SELECT "E".ename "name", "E".sal "Salary", "D".sal "Manager" FROM emp "E" LEFT JOIN emp "D" ON "E".mgr="D".empno ``` # 分页查询 - 工资从高到低排序，输出工资排名7-9员工的信息。 ```sql 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](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/931dbccecbc94342a4fdb81244256718~tplv-k3u1fbpfcp-watermark.image?) # 视图 ## 视图概念 ```sql CREATE VIEW v$_emp_1 AS SELECT deptno,AVG(sal) "avg_sal" FROM emp GROUP BY deptno SELECT * FROM v$_emp_1 ``` ![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/072f327a3c52471db524f2beb7615ff7~tplv-k3u1fbpfcp-watermark.image?) - 可以理解为创建了一个SELECT语句。逻辑上当做一个表。 - 使用视图可以简化查询。避免了代码的冗余。 ## 视图的格式 ```sql CREATE VIEW 名字 -- SELECT 前面不能加 BGEIN SELECT 语句 -- SELECT 后面不能加 END ``` ## 视图的优缺点 - 简化查询，增加数据的保密性 - 缺点：增加数据库维护成本。并且视图只是简化查询，不会加快查询速度。 - 注意：创建视图的SELECT语句中必须为所有的计算列指定别名。视图不是物理表，而是虚拟表。不建议通过更新视图来更新原始数据。 # 事务 ## 概念 - 事务主要用来保证数据的合理性和并发处理的能力！ - 避免数据处理不合理的中间状态。 ## 创建事务 - 开始事务：`BEGIN TRANSACTION` - 提交事务：`COMMIT TRANSACTION` - 回滚事务：`ROLLBACK TRANSACTION` # 索引 - 优化查询。可以加快查询速度。 # 存储过程 # 游标 # TL_SQL `不是没有更新完，而是原教程已结束。` `2022年11月13日`