【后端学习】MySQL数据库面试必会知识点

---

前言

无论是求职还是实际工作，做后端不可避免都要跟数据库打交道，因此学好数据库很重要。
学数据库我们一般选择从MySQL入门，讲一下我的学习过程：
1.入门数据库我看的是老杜的MySQL教程讲的很好，可以去B站学习一下。
2.推荐一个刷MYSQL很好的网站，把网站里的题刷了 ，面试考SQL语句问题不大https://sqlzoo.net/wiki/SQL_Tutorial/zh
以下是我在学习过程中总结的重点难点，还有一些面试常问的问题，欢迎关注，一起学习交流。

---

重难点总结

1.连接查询

1.1、什么是连接查询？

​ 从一张表中单独查询，称为单表查询。

​ emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。

​ 这种跨表查询，多张表联合起来查询数据，被称为连接查询。

1.2、连接查询的分类？

​ 根据语法的年代分类：

​ SQL92：1992年的时候出现的语法

​ SQL99：1999年的时候出现的语法

​ 我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)

​ 根据表连接的方式分类：

​ 内连接： 等值连接 非等值连接 自连接

​ 外连接： 左外连接（左连接） 右外连接（右连接）

​ 全连接（省略）

笛卡尔积现象

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是

​ 两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是

​ 一个数学现象。）

怎么避免笛卡尔积现象？

​ 连接时加条件，满足这个条件的记录被筛选出来！

内连接之等值连接。

案例：查询每个员工所在部门名称，显示员工名和部门名？

emp e和dept d表进行连接。条件是：e.deptno = d.deptno

SQL99语法：

​	select  e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

​ sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

​ SQL99语法：

​		select  ...  from a join b on a和b的连接条件 where 筛选条件

内连接之非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select  e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; 
// 条件不是一个等量关系，称为非等值连接。

内连接之自连接

select  a.ename as “员工”, b.ename as “领导名” from emp a join emp b on a. mgr=b.empno;

外连接

外连接（右外连接）：
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

select  e.ename,d.dname from emp e  right join  dept d on e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。

select  e.ename,d.dname from emp e  right outer join  dept d on e.deptno = d.deptno;

带有right的是右外连接，又叫做右连接。

带有left的是左外连接，又叫做左连接。

任何一个右连接都有左连接的写法。

任何一个左连接都有右连接的写法。

三张表，四张表怎么连接？

​ 语法： select ... from a join b on a和b的连接条件 join c on a和c的连接条件 right join d on a和d的连接条件 一条SQL中内连接和外连接可以混合。都可以出现！

2.子查询

2.1、什么是子查询？

​ select语句中嵌套select语句，被嵌套的select语句称为子查询。

2.2、子查询都可以出现在哪里呢？

​	select  ..(select). from 	..(select). where ..(select).

select ename,sal from emp where sal > (select min(sal) from emp);

from子句中的子查询

​ 注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

​ 案例：找出每个岗位的平均工资的薪资等级。

​

2.3 union合并查询结果集

union的效率要高一些。对于表连接来说，每连接一次新表，

​ 则匹配的次数满足笛卡尔积，成倍的翻。。。

​ 但是union可以减少匹配的次数。在减少匹配次数的情况下，

​ 还可以完成两个结果集的拼接。

union在使用的时候有注意事项吗？

​ //错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。

// MYSQL可以，oracle语法严格 ，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。

2.4 limit（非常重要）

​ 按照薪资降序，取出排名在前5名的员工？

​	select  ename,sal from emp order by  sal desc limit 5; //取前5

​	select  ename,sal from emp order by  sal desc limit 0,5;

注意：mysql当中limit在order by之后执行！！！！！！

每页显示pageSize条记录

记公式：

​	limit (pageNo-1)*pageSize , pageSize

2.5 关于DQL语句的大总结：

​	select  ... from ... where ... group by ... having 	... order by ... limit ...

​

​ 执行顺序？

​ 1.from 2.where 3.group by 4.having

​ 5.select 6.order by 7.limit…

3.表的创建（建表）

3.1、建表的语法格式：(建表属于DDL语句，DDL包括：create drop alter)

​ create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);

​	create table 表名(

​		字段名1 数据类型, 

​		字段名2 数据类型, 

​		字段名3 数据类型

​	);

插入数据insert （DML）

​ 语法格式：

​		insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

​ 注意：字段名和值要一一对应。什么是一一对应？

​ 数量要对应。数据类型要对应。

insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');

格式化数字：format(数字, ‘格式’)

​			select ename,format(sal, '$999,999') as sal from emp;

str_to_date：将字符串varchar类型转换成date类型

​ date_format：将date类型转换成具有一定格式的varchar字符串类型。

date和datetime两个类型的区别？

​ date是短日期：只包括年月日信息。

​ datetime是长日期：包括年月日时分秒信息。

insert into t_user(id,name,birth,create_time) values(1,‘zhangsan’,‘1990-10-01’,‘2020-03-18 15:49:50’);

在mysql当中怎么获取系统当前时间？

​ now() 函数，并且获取的时间带有：时分秒信息！！！！是datetime类型的。

​

​		insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

修改update（DML）

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;

删除数据 delete （DML）

​ 语法格式？

​		delete from 表名 where 条件;

3.2 快速创建表？【了解内容】

​	mysql> create table emp2 as select * from emp;

​ 原理：

​ 将一个查询结果当做一张表新建！！！！！

​ 这个可以完成表的快速复制！！！！

​ 表创建出来，同时表中的数据也存在了！！！

​

​	create table mytable as select empno,ename from emp where job = 'MANAGER';

将查询结果插入到一张表当中？insert相关的！！！

insert into dept_bak select * from dept;

3.3 快速删除表中的数据？【truncate比较重要，必须掌握】

delete from dept_bak; //这种删除数据的方式比较慢。

delete语句删除数据的原理？（delete属于DML语句！！！）

​ 表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！

​ 这种删除缺点是：删除效率比较低。

​ 这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

​

​ truncate语句删除数据的原理？

​ 这种删除效率比较高，表被一次截断，物理删除。

​ 这种删除缺点：不支持回滚。

​ 这种删除优点：快速。

3.4 对表结构的增删改？

​

​ 什么是对表结构的修改？

​ 添加一个字段，删除一个字段，修改一个字段！！！

对表结构的修改需要使用：alter

​ 属于DDL语句

​ DDL包括：create drop alter

4、约束（非常重要，五颗星*****）

4.1、什么是约束？

​ 约束对应的英语单词：constraint

​ 在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的

​ 完整性、有效性！！！

​ 约束的作用就是为了保证：表中的数据有效！！

4.2、约束包括哪些？

​ 非空约束：not null

​ 唯一性约束: unique

​ 主键约束: primary key （简称PK）

​ 外键约束：foreign key（简称FK）

​ 检查约束：check（mysql不支持，oracle支持）

​ 我们这里重点学习四个约束：

​ not null

​ unique

​ primary key

​ foreign key

4.3、非空约束：not null

​ 非空约束not null约束的字段不能为NULL。

​ drop table if exists t_vip;

​ create table t_vip(

​ id int,

​ name varchar(255) not null // not null只有列级约束，没有表级约束！

​ );

4.4、唯一性约束: unique

​ 唯一性约束unique约束的字段不能重复，但是可以为NULL。

​ drop table if exists t_vip;

​ create table t_vip(

​ id int,

​ name varchar(255) unique,

​ email varchar(255)

​ );

新需求：name和email两个字段联合起来具有唯一性！！！！

在mysql当中，如果一个字段同时被not null和unique约束的话，

​ 该字段自动变成主键字段。（注意：oracle中不一样！）

4.5、主键约束（primary key，简称PK）非常重要五颗星*****

​ 主键约束的相关术语？

​ 主键约束：就是一种约束。

​ 主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段

​ 主键值：主键字段中的每一个值都叫做：主键值。

​

​ 什么是主键？有啥用？

​ 主键值是每一行记录的唯一标识。

​ 主键值是每一行记录的身份证号！！！

​

​ 记住：任何一张表都应该有主键，没有主键，表无效！！

​ 主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

​ 怎么给一张表添加主键约束呢？

​		drop table if exists t_vip;

​		// 1个字段做主键，叫做：单一主键

​		create table t_vip(

​			id int primary key,  //列级约束

​			name varchar(255)

​		);

一个表中主键约束能加两个吗？

​

drop table if exists t_vip;

​		create table t_vip(

​			id int primary key,

​			name varchar(255) primary key

​		);

​		ERROR 1068 (42000): Multiple primary key defined

​ 结论：一张表，主键约束只能添加1个。（主键只能有1个。）

​ 主键值建议使用：

​ int

​ bigint

​ char

​ 等类型。

​ 不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值？

​		drop table if exists t_vip;

​		create table t_vip(

​			id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！

​			name varchar(255)

​		);

4.6、外键约束（foreign key，简称FK）非常重要五颗星*****

​ 外键约束涉及到的相关术语：

​ 外键约束：一种约束（foreign key）

​ 外键字段：该字段上添加了外键约束

​ 外键值：外键字段当中的每一个值。

​ 注意：

​ t_class是父表

​ t_student是子表

​ 删除表的顺序？

​ 先删子，再删父。

​ 创建表的顺序？

​ 先创建父，再创建子。

​ 删除数据的顺序？

​ 先删子，再删父。

​ 插入数据的顺序？

​ 先插入父，再插入子。

​ 思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？

​ 不一定是主键，但至少具有unique约束。

​ 测试：外键可以为NULL吗？

​ 外键值可以为NULL。

Create table t_student(no int primary key auto_increment,

name varchar(255),

cno int,

foreign key(cno) references t_class(classno));

5.mysql常用的存储引擎

5.1 MyISAM存储引擎？

​ 它管理的表具有以下特征：

​ 使用三个文件表示每个表：

​ 格式文件 — 存储表结构的定义（mytable.frm）

​ 数据文件 — 存储表行的内容（mytable.MYD）

​ 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。

​ 可被转换为压缩、只读表来节省空间

​ 提示一下：

​ 对于一张表来说，只要是主键，

​ 或者加有unique约束的字段上会自动创建索引。

​ MyISAM存储引擎特点：

​ 可被转换为压缩、只读表来节省空间

​ 这是这种存储引擎的优势！！！！

​

​ MyISAM不支持事务机制，安全性低。

5.2 InnoDB存储引擎？

​ 这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。

​ InnoDB支持事务，支持数据库崩溃后自动恢复机制。

​ InnoDB存储引擎最主要的特点是：非常安全。

​ 它管理的表具有下列主要特征：

​ – 每个 InnoDB 表在数据库目录中以.frm 格式文件表示

​ – InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）

​ – 提供一组用来记录事务性活动的日志文件

​ – 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理

​ – 提供全 ACID 兼容

​ – 在 MySQL 服务器崩溃后提供自动恢复

​ – 多版本（MVCC）和行级锁定

​ – 支持外键及引用的完整性，包括级联删除和更新

​

​ InnoDB最大的特点就是支持事务：

​ 以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，

​ 不能很好的节省存储空间。

MEMORY存储引擎？

​ 使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，

​ 这两个特点使得 MEMORY 存储引擎非常快。

​ MEMORY 存储引擎管理的表具有下列特征：

​ – 在数据库目录内，每个表均以.frm 格式的文件表示。

​ – 表数据及索引被存储在内存中。（目的就是快，查询快！）

​ – 表级锁机制。

​ – 不能包含 TEXT 或 BLOB 字段。

​ MEMORY 存储引擎以前被称为HEAP 引擎。

​ MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。

​ MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

怎么给表添加/指定“存储引擎”呢？

​ show create table t_student;

​ 可以在建表的时候给表指定存储引擎。

​

CREATE TABLE `t_student` (

​	 `no` int(11) NOT NULL AUTO_INCREMENT,

​	 `name` varchar(255) DEFAULT NULL,

​	 `cno` int(11) DEFAULT NULL,

​	 PRIMARY KEY (`no`),

​	 KEY `cno` (`cno`),

​	 CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)

​	) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

6.事务

6.1什么是事务？

​ 一个事务其实就是一个完整的业务逻辑。

​ 是一个最小的工作单元。不可再分。

insert

​ delete

​ update

​ 只有以上的三个语句和事务有关系，其它都没有关系。

提交事务：commit; 语句

​ 回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）

怎么将mysql的自动提交机制关闭掉呢？

​ 先执行这个命令：start transaction;

6.2 事务包括4个特性？

​ A：原子性

​ 说明事务是最小的工作单元。不可再分。

​ C：一致性

​ 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，

​ 以保证数据的一致性。

​ I：隔离性

​ A事务和B事务之间具有一定的隔离。

​ 教室A和教室B之间有一道墙，这道墙就是隔离性。

​ A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？

​ D：持久性

​ 事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据

​ 保存到硬盘上！

6.3 事务和事务之间的隔离级别有哪些呢？4个级别

​ 读未提交：
read uncommitted（最低的隔离级别）《没有提交就读到了》

​ 什么是读未提交？

​ 事务A可以读取到事务B未提交的数据。

​ 这种隔离级别存在的问题就是：

​ 脏读现象！(Dirty Read)

​ 我们称读到了脏数据。

​ 这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

​ 读已提交：
read committed《提交之后才能读到》

​ 什么是读已提交？

​ 事务A只能读取到事务B提交之后的数据。

​ 这种隔离级别解决了什么问题？

​ 解决了脏读的现象。

​ 这种隔离级别存在什么问题？

​ 不可重复读取数据。

​ 什么是不可重复读取数据呢？

​ 在事务开启之后，第一次读到的数据是3条，当前事务还没有

​ 结束，可能第二次再读取的时候，读到的数据是4条，3不等于4

​ 称为不可重复读取。

​ 这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。

​ oracle数据库默认的隔离级别是：read committed

​ 可重复读：
repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》

​ 什么是可重复读取？

​ 事务A开启之后，不管是多久，每一次在事务A中读取到的数据

​ 都是一致的。即使事务B将数据已经修改，并且提交了，事务A

​ 读取到的数据还是没有发生改变，这就是可重复读。

​ 可重复读解决了什么问题？

​ 解决了不可重复读取数据。

​ 可重复读存在的问题是什么？

​ 可以会出现幻影读。

​ 每一次读取到的数据都是幻象。不够真实！

​

​ 早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！

​ 读到的是假象。不够绝对的真实。

​ mysql中默认的事务隔离级别就是这个！！！！！！！！！！！

​ 序列化/串行化：
serializable（最高的隔离级别）

​ 这是最高隔离级别，效率最低。解决了所有的问题。

​ 这种隔离级别表示事务排队，不能并发！

​ synchronized，线程同步（事务同步）

​ 每一次读取到的数据都是最真实的，并且效率是最低的。

set global transaction isolation level read uncommitted;

set global transaction isolation level serializable; ………

7.索引

7.1、什么是索引？

​ 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

​ 一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。

​ 索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

​ 对于一本字典来说，查找某个汉字有两种方式：

​ 第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。

​ 效率比较低。

​ 第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个

​ 位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过

​ 索引检索，效率较高。

MySQL在查询方面主要就是两种方式：

​ 第一种方式：全表扫描

​ 第二种方式：根据索引检索。

7.2 在mysql当中，主键上，以及unique字段上都会自动添加索引的！！！！

什么条件下，我们会考虑给字段添加索引呢？

​ 条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）

​ 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。

​ 条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

​ 建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

​ 建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

7.3 、索引怎么创建？怎么删除？语法是什么？

​ 创建索引：

mysql> create index emp_ename_index on emp(ename);

​ 给emp表的ename字段添加索引，起名：emp_ename_index

​ 删除索引：

​		mysql> drop index emp_ename_index on emp;

​ 将emp表上的emp_ename_index索引对象删除。

7.4 索引有失效的时候，什么时候索引失效呢？

​ 失效的第1种情况：

​ select * from emp where ename like '%T';

​ ename上即使添加了索引，也不会走索引，为什么？

​ 原因是因为模糊匹配当中以“%”开头了！

​ 尽量避免模糊查询的时候以“%”开始。

​ 这是一种优化的手段/策略。

​ 失效的第2种情况：

​ 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有

​ 索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个

​ 字段上的索引也会实现。所以这就是为什么不建议使用or的原因。

失效的第3种情况：

​ 使用复合索引的时候，没有使用左侧的列查找，索引失效

​ 什么是复合索引？

​ 两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

失效的第4种情况：

​ 在where当中索引列参加了运算，索引失效。

mysql> create index emp_sal_index on emp(sal);

失效的第5种情况：

​ 在where当中索引列使用了函数

explain select * from emp where lower(ename) = 'smith';

7.5 索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。

索引在数据库当中分了很多类？

​ 单一索引：一个字段上添加索引。

​ 复合索引：两个字段或者更多的字段上添加索引。

​ 主键索引：主键上添加索引。

​ 唯一性索引：具有unique约束的字段上添加索引。

​ …

​ 注意：唯一性比较弱的字段上添加索引用处不大。

8、视图(view)

8.1、什么是视图？

​ view:站在不同的角度去看待同一份数据。

8.2、怎么创建视图对象？怎么删除视图对象？

​ 创建视图对象：

create view dept2_view as select * from dept2;

​ 删除视图对象：

drop view dept2_view;

​ 注意：只有DQL语句才能以view的形式创建。

​ create view view_name as 这里的语句必须是DQL语句;

8.3、用视图做什么？

​ 我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致

​ 原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）

8.4、视图对象在实际开发中到底有什么用？《方便，简化开发，利于维护》

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。

​ 每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？

​ 可以把这条复杂的SQL语句以视图对象的形式新建。

​ 在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。

​ 并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要

​ 修改视图对象所映射的SQL语句。

9、DBA常用命令？

​ 重点掌握：

​ 数据的导入和导出（数据的备份）

​ 其它命令了解一下即可。（这个培训日志文档留着，以后忘了，可以打开文档复制粘贴。）

​

​ 数据导出？

​ 注意：在windows的dos命令窗口中：

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456

​

​ 可以导出指定的表吗？

​			mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

​ 数据导入？

​ 注意：需要先登录到mysql数据库服务器上。

​ 然后创建数据库：create database bjpowernode;

​ 使用数据库：use bjpowernode

​ 然后初始化数据库：source D:\bjpowernode.sql

10、数据库设计三范式

10.1、什么是数据库设计范式？

​ 数据库表的设计依据。教你怎么进行数据库表的设计。

10.2、数据库设计范式共有？

​ 3个。

​ 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

​ 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，

​ 不要产生部分依赖。

​ 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，

​ 不要产生传递依赖。

​ 声明：三范式是面试官经常问的，所以一定要熟记在心！

​ 设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

背口诀：

​ 多对多怎么设计？

​ 多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！

10.3、总结表的设计？

​ 一对多：

​ 一对多，两张表，多的表加外键！！！！！！！！！！！！

​ 多对多：

​ 多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！

​ 一对一：

​ 一对一放到一张表中不就行了吗？为啥还要拆分表？

​ 在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。

10.4、嘱咐一句话：

​ 数据库设计三范式是理论上的。

​ 实践和理论有的时候有偏差。

​ 最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

​ 因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）

​ 有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，

​ 并且对于开发人员来说，sql语句的编写难度也会降低。

​ 面试的时候把这句话说上：他就不会认为你是初级程序员了

11.较复杂指令

11.1 not

// not in 表示不在这几个值当中的数据。

select ename,sal from emp where sal not in(800, 5000, 3000);

not 可以取非，主要用在 is 或 in 中

​ is null

​ is not null

​ in

​ not in

11.2 like

like

​ 称为模糊查询，支持%或下划线匹配

​ %匹配任意多个字符

​ 下划线：任意一个字符。

​ （%是一个特殊的符号，_ 也是一个特殊符号）

​ 找出名字中含有O的？

​		mysql> select ename from emp where ename like '%O%';

找出名字中有“_”的？

​			select name from t_student where name like '%_%'; //这样不行。

​			mysql> select name from t_student where name like '%\_%'; // \转义字符。

例子：查询所有员工薪资，排序？

​	select  ename,sal  from emp order by  sal; // 默认是升序！！！

11.3 怎么降序？

​ 指定降序：

select  ename,sal from emp order by sal desc;

指定升序？

​	select  ename,sal from emp order by sal asc;

11.4 可以两个字段排序吗？或者说按照多个字段排序？

​ 查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，

​ 再按照名字升序排列。

​ select ename,sal from emp order by sal asc, ename asc;
// sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。

关键字顺序不能变：

​ select … from … where … order by …

​ 以上语句的执行顺序必须掌握：

​ 第一步：from

​ 第二步：where

​ 第三步：select

​ 第四步：order by（排序总是在最后执行！）

11.5 单行处理函数常见的有哪些？

lower 转换小写

upper 转换大写

substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）

select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;//首字母大写

concat函数进行字符串的拼接

​		select concat(empno,ename) from emp;

11.6 其他

length 取长度

select length(ename) enamelength from emp;

trim 去空格

select * from emp where ename = trim('  KING');

str_to_date 将字符串转换成日期

date_format 格式化日期

format 设置千分位

case…when…then…when…then…else…end

select  ename, job,  sal as oldsal,

​			(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 

​		from 	emp;

round 四舍五入

select round(1236.567, -2) as result from emp;

rand() 生成随机数

mysql> select round(rand()*100,0) from emp; // 100以内的随机数

注意：NULL只要参与运算，最终结果一定是NULL。为了避免这个现象，需要使用ifnull函数。

​ ifnull函数用法：ifnull(数据, 被当做哪个值)

​ 如果“数据”为NULL的时候，把这个数据结构当做哪个值。

​

​ 补助为NULL的时候，将补助当做0

select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;

【distinct】能把查询结果去除重复记录

12.分组函数（多行处理函数）

​ 多行处理函数的特点：输入多行，最终输出一行。

​ 5个：

​ count 计数

​ sum 求和

​ avg 平均值

​ max 最大值

​ min 最小值

分组函数在使用的时候需要注意哪些？

​第一点：分组函数自动忽略NULL，你不需要提前对NULL进行处理。

第二点：分组函数中count(*)和count(具体字段)有什么区别？

count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。

count(*)：统计表当中的总行数。（只要有一行数据count则++）

因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。

第三点：分组函数不能够直接使用在where子句中。

​ 找出比最低工资高的员工信息。
select ename,sal from emp where sal > min(sal);

为什么分组函数不能直接使用在where后面？

​ select ename,sal from emp where sal > min(sal);//报错。

​ 因为分组函数在使用的时候必须先分组之后才能使用。

​ where执行的时候，还没有分组。所以where后面不能出现分组函数。

第四点：所有的分组函数可以组合起来一起用。

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

分组查询*****

将之前的关键字全部组合在一起，来看一下他们的执行顺序？

Select ... from ... where ... group by ... order by  ...

重点结论：

​ 在一条select语句当中，如果有group by语句的话，

​ select后面只能跟：参加分组的字段，以及分组函数。

​ 其它的一律不能跟。

使用having可以对分完组之后的数据进一步过滤。

​ having不能单独使用，having不能代替where，having必须

​ 和group by联合使用。

select  	deptno,max(sal)  	from  emp  group by  deptno having max(sal) > 3000;

​