MySQL笔记【基础篇】

• char 适用于数长度不会发生改变的时候，是定长的，例如：性别，生日

• varchar 当一个字段数据长度不确定，例如：简介、姓名、等都是采用varchar

char是直接开辟一定长度的空间，varchar是根据存储数据的长度动态的开辟空间！

测试案例

#创建表

CREATE TABLE user(

id INT(10),

username VARCHAR(30),

PASSWORD VARCHAR(30) #最后一行不需要,

);

[](()2.删除表：

drop table 表名；（通用）

drop table 表名 if exists 表名； //Oracle不支持这种写法。

使用drop删除表，就像是我们把表中的数据擦干净，但是表的结构还在，可以通过rollback回滚过来数据！

我们如果需要删除大表的话，就需要truncate 来删除，会清空所有数据且执行速度很快，并且永久删除，数据不可恢复！

truncate table name

删除表常用：

drop table if exist 表名（如果存在某个表删除该表）。

[](()3.修改表结构：

用工具：哈哈！设计表选项

（因为在实际开发中表设计好以后，对表结构进行修改是很少的。并且修改表中结构的语句并不会写在java语句当中。）

[](()4.insert：表中插入数据

语法：insert into 表名（字段1，字段2，字段3…）values(value1 , value2 , value3) ;

INSERT INTO dept(deptno,dname,loc)VALUES(50,‘Java开发’,‘ZHENGZHOU’);

需要注意的地方：当一条insert语句执行成功之后，表格必然会多一行记录，及时多的一行记录当中某些字段为null，后期也没办法再通过insert插入数据了，只能通过update更新 ！

**一次插入多行数据：**insert into 表名（字段1，字段2，字段3）values（value1，value2，value3），（value1，value2，value3）

INSERT INTO dept(deptno,dname,loc)VALUES(70,‘大数据开发’,‘ZHENGZHOU’),(60,‘运维部’,‘KAIFENG’);

[](()5.表的复制

了解一下即可！

CREATE TABLE mytable AS SELECT * FROM dept #将将查询结果当作表创建出来。

将查询出来的结果插入道一张表中

INSERT INTO mytable SELECT * FROM dept

[](()6.update：修改表中数据

语法格式：update 表名 set 字段名1 = 值1 ，字段名2 = 值2 …where 条件 ；

注意：没有条件整张表的数据全部更新 ；

UPDATE dept SET dname = ‘测试开发’ ,loc = ‘HUEL’ WHERE DEPTNO = 10 ; #就1个set！

[](()7.delete删除表中数据；

语法格式：delete from 表名 where 条件 ；

注意：没有条件表中数据全部清空！

DELETE FROM dept WHERE deptno = 70 #删除部门号为70的数据

DELETE FROM dept #清空表中数据

[](()8.增删改查有一个术语：CRUD操作

• Create(增加)

• Retrieve(查询)

• Update(修改)

• Delete(删除)

[](()四.约束

---

创建表的时候可以给字段添加相应的约束，约束的目的：保证表中数据的合法性，唯一性，有效性。

1. 非空约束（not null）：约束字段不能为NULL

2. 唯一约束（unique）：约束字段不能重复

3. 主键约束（primary key）：约束字段既不能为NULL也不能重复

4. 外键约束（foreign key）：阿里巴巴手册，已经禁止使用外键了

5. 检查f约束（check）：Oracle数据库有check约束，但是Mysql没有。

注意：我们主要还是记住英文，平时使用的都是英文！

[](()1.非空约束 not null

CREATE TABLE mytable(

id INT(10),

username VARCHAR(30) NOT NULL, #添加非空约束

PASSWORD VARCHAR(30)

);

测试

insert into mytable(id,password) values(1,‘123’) ; #由于我们的的用户字段要求非空，所以会执行失败

ERROR 1364(HY000) : Filed ‘username’ doesn’t have a default value

[](()2.唯一约束(unique)

列级约束

表级约束

测试

[](()3.主键约束(primary key) *

必须记住：一张表的主键约束只有一个 ；最好与别的字段不相关，仅仅代表唯一标识 ！

CREATE TABLE mytable(

id INT(10) PRIMARY KEY, #添加主键约束【属于列级约束】

username VARCHAR(30),

email VARCHAR(30)

);

测试

【表级约束】

主键值自增：在primary key后面添加auto_increment（从1开始，递增）；

了解：Oracle也提供一个自增序列叫做序列（sequence）；

[](()4.外键约束（foreign key）

关于外键约束的相关术语

• 外键约束:foreign key

• 外键字段:添加有外键约束的字段

• 外键值:外键字段中的每一个值。

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

顺序要求:

• 删除数据的时候,先删除子表,再删除父表。

• 添加数据的时候,先添加父表，在添加子表。

• 创建表的时候,先创建父表,再创建子表。

• 删除表的时候,先删除子表,在删除父表。

使用外键【Alibaba规范不推荐使用】

create table t_class(

cno int ,

cname varchar(255) , #被引用：父表

primary key(cno)

);

create table t_student(

sno int ,

sname varchar(255) , #子表

classno int ,

foreign key(classno) reference t_class(cno)

);

#t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表

• reference是引用；

• 外键可以为NULL；

• 此时classno中的数据来自cno；

• 外键引用的字段不一定是主键，但是起码是被unique约束；

[](()五.存储引擎

---

表在数据库中的存储方式。

存储引擎只存在mysql中，（Oracle中有对应机制，但是不叫存储引擎）。

完整的建表语句:

CREATE TABLE mytable(

id INT(10) PRIMARY KEY,

username VARCHAR(30) NOT NULL,

PASSWORD VARCHAR(30)

)ENGINE = I 《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源 威信搜索公众号【编程进阶路】 nnoDB Default CAHRSET=UTF8;

注意：在MYSQL当中，凡是标识符可以使用飘号括起来，最好别用，不通用！

建表的时候可以指定存储引擎，也可以指定字符集。

mysql默认的存储引擎是InnoDB的方式！默认采用的字符集是UFT-8

mysql> show engines \G #查看当前，mysql版本支持的存储引擎命令! 当前MYSQL版本是5.7.33，支持9种存储引擎！

[](()1.常见的存储引擎：

• MyISAM 存储索引

• InnoDB 存储索引

• MEMORY 存储索引

[](()2.MyISAM 存储引擎:

MyISAM:这种存储引擎不支持事务。是最常用的但不是默认的

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO #不支持事务！

XA: NO

Savepoints: NO

它管理的表具有以下特征:

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

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

• 数据文件 — 存储表的数据(mytable.MYD)

• 索引文件 — 存储表中索引的文件（mytable.MYI）

优点：可被转换为压缩，来节省空间，并且可以转换为只读表，提高检索效率！

缺点：不支持事务！

[](()3.InnoDB 存储引擎:

支持事务，行级锁，外键等 ；这种存储引擎安全。

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

• 表的结构存储在xxx.frm文件当中

• 数据存储在tablespace这样的表空间当中（逻辑概念），无法被压缩，无法被转换为只读！

• 这种InnoDB存储引擎在MYSQL数据库崩溃之后提供自动恢复机制！【事务、安全、重量级】

• 支持级联删除和级联更新。

[](()4.MEMORY 存储引擎:

所有数据保存在内存中,断电即失，但是查询速度超级快！

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

• 在数据库目录当中，每个表均已.frm格式的文件表示

• 表数据以及索引被存储在内存当中

• 表级锁机制

• 不能包含TEXT（CLOB）或BLOB字段

• 查询速度快

[](()六.事务（Transaction）*

---

[](()1、什么是事务

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行转账，从A账户向B账务转账10000，需要执行两条update语句

update t_act set balance = balance - 10000 where actno = ‘act-001’ ;

update t_act set balance = balance + 10000 where actno = ‘act-002’ ;

以上两条DML语句必须同时成功，或同时失败，不允许一条成功，一条失败！

想要保证以上的两条DML语句（update、insert、delete）同时成功或者同时失败，那么就需要使用数据库的“事务机制” ！

[](()2、事务原理

假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete，这个事儿才算完成。

开启事务机制（开始)

• 执行insert语句–>insert.(这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存-条数据，不会真正的修改硬盘上的数据。)

• 执行update语句—> update…(这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据)

• 执行delete语句---->delete…(这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据)

提交事务或者回滚事务（结束）

怎么提交事务，怎么回滚事务？

提交事务：commit；语句

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

MySQL默认的事务行为是怎么样的？

MySQL默认情况下是支持自动提交事务的。（自动提交）：就是一条DML提交一次！

怎么样才能将MySQL的自动提交事务关闭呢？

先执行如下命令：start transaction ,开启事务，也是将自动提交事务关闭！

在次命令后的指定的DML语句并不会持久化到数据库，等待commit | rollback 操作！

[](()3、事务的特性：ACID

A：原子性（atomicity）：事务是最小的工作单元，不可再分。

C：一致性（consistency）：事务必须保证多条DML语句同时成功或同时失败。

I ：隔离性（isolation）：事务A与事务B之间具有隔离。

D：持久性（durability）：持久性说的时最终的数据必须持久化道硬盘文件中，事务才算成功的结束。

[](()4、事务的隔离性

将事务A与事务B分别比作教室A，B，然而它们之间的墙就是隔离性，墙越厚隔离级别越高！（这种墙分为四个级别）

• 第一级别：读未提交（read uncommitted）：我们当前事务可以读取对方未提交的事务，

存在问题：存在脏读（Dirty read）现象，表示读到了脏的数据。

• 第二级别：读已提交（read committed）：我们当前事务可读取对方已提交的事务，解决了脏读现象，存在问题：不可重复读。

• 第三级别：可重复读（repeatable read）：解决不可重复读，永远读取到的都是开启事务时的数据，==存在问题：读取到的数据是幻像（幻读）==例如：执行一条查询的sql需要从1点3点，那么这条sql读取的一直是1点的数据，即使13点之间存在其他操作，该sql的结果并不会受到影响，类似快照，一直读取的是快照中的数据。

• 第四级别：序列化读/串行化读（serializable）：解决了所有问题。 效率低，需要事务排队（类似于synchronized）。

Oracle数据库默认级别：第二级别；读已提交

Mysql数据库默认级别：第三级别，可重复读

[](()5、验证各种隔离级别

对以上各种隔离级别做一个验证

通过命令 select @@tx_isolation；查看当前事物的隔离级别 !

±----------------+

| @@tx_isolation |

±----------------+

| REPEATABLE-READ |

±----------------+

[](()读未提交（read uncommitted）：

#执行如下命令，设置全局的事务隔离级别为读未提交

set global transaction isolation level read uncommitted;

[](()读已提交（read committed）：

测试 读已提交（read committed）

#执行如下命令，设置全局的事务隔离级别为读已提交

set global transaction isolation level read committed;

[](()可重复读（repeatable read）：

测试：可重复读（repeatable read）

#执行如下命令，设置全局的事务隔离级别为可重复读

set global transaction isolation level read committed;

[](()序列化读/串行化读（serializable）：

测试 序列化读/串行化读（serializable）

#执行如下命令，设置全局的事务隔离级别为可重复读

set global transaction isolation level serializable;

[](()七.索引 *

---

[](()1、索引的概述

索引相当于一本书的目录，通过目录可以快速找到对应的资源。

作用：提高检索效率，缩小扫面范围。

注意：索引虽然提高效率但是依旧不能随意添加，因为索引也是数据库中的对象，也是需要数据库不断的维护的，有维护成本，表中数据经常修改就不适合，因为一旦修改索引需要重新排序，进行维护。

[](()2、怎么创建索引对象

语法格式：create index 索引名称 on 表名 （字段名）。

eg：create index emp_sal_index on emp(sal);

[](()3、怎么删除索引对象

语法格式：drop index 索引名称 on 表名；

[](()4、什么时候给字段添加索引

• 数据量庞大。（根据客户需求，根据线上的环境。）

• 该字段很少的DML操作。

• 该字段经常出现在where子句中。

[](()5、sql语句的执行计划

主键和具有unique约束的字段自动添加索引，根据主键查询效率较高，尽量根据主键检索。

==explain+sql语句可以解释该语句。（执行计划）==可以通过此命令判断sql是否走了索引！

[](()6、索引底层采用的数据结构是：b+tree

索引的实现原理

案例分析

[](()7、索引分类

• 单一索引：给单个字段添加索引

• 复合索引：给多个字段联合起来添加一个索引

• 主键索引：主键上会自动添加索引

• 唯一索引：有unique约束的字段上会自动添加索引

[](()8、索引什么时候失效 ?

1、模糊查询，这个时候索引失效,因为以%开始，无法使用索引！

select ename from emp where ename =‘%a%’ ;

2、使用or的时候会索引失效,因为如果or两端的字段存在一个字段不具备索引,具备索引的字段也会失效！

select ename from emp where ename =‘zhangsan’ or job = ‘Java’ ;

3、使用复合索引，没有使用左侧的列去查找，索引失效

creat index emp_job_sal_index on emp(job,sal) # 为job和sal两个字段添加复合索引

select * from emp where job = ‘java’ ; #会走索引，因为使用job列去查了

select * from emp where sal = 1800 ; #不会走索引，因为没有使用左侧的job列！

4、where当中的索引列参加了运算，索引失效。

select * from emp where sal + 1 = 1800 ;

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

select * from emp where lower(ename) = ‘smith’ ;

[](()八.视图（view）

---

[](()1.视图的概念

站在不同的角度看到数据。（同一张表的数据，通过不同的角度去看待。）

[](()2.创建/删除视图的sql

#创建视图

create view myview as select * from emp；

#删除视图

drop viem myview;

注意：只有DQL语句才能以视图对象的方式创建出来；

[](()3.对视图进行CRUD操作

对视图进行增删改查，会影响到原表数据。

create table emp_bak as select * from emp;

create view myview as select empno,ename,sal from emp_bak ;

update myview set name = ‘sqx’ ,sql = 1 where empno = ‘7369’ ; #通过视图修改原表数据

delete from myview where empno = 7369 ; #通过视图删除原表数据

注意：视图对象时保存在硬盘上的，而非保存在内存当中，所以不会消失！

[](()4.视图的作用

思考：假设有一条复杂的SQL语句，而且这条SQL语句需要在不同的地方反复使用，每一次使用都写一遍，很长很麻烦,怎么办？

答：可以把复杂的SQL以视图的形式展示出来，我们需要执行复杂的SQL时，只需查询该SQL所映射的视图即可！并且这种方式很容易后 期的 维护，我们只需改动原SQL，重新创建视图即可！

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对你提供相关的视图java程序员只能对视图进行CRUD。（并不会提高视图的检索效率）

[](()九.DBA命令（了解）

---

掌握以下即可，其他需要再查！

[](()1.将数据库导出

#在windows的DOS窗口命令中输入(不需要进入mysql)

mysqldump 数据库名 > 导出路径 -u：mysql账号 -p：mysql密码

[](()2.向数据库导入数据

create database 数据库名；

use数据库名；

source+sql脚本文件路径；

[](()十.数据库设计三范式（重点）

---

设计表的依据，按照这个三范式设计的表不会出现数据冗余。

[](()1、三范式都是那些

• 第一范式:要求任何一张表必须有主键，每一个字段原子性不可再分。【核心】

• 第二范式:建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

• 第三范式:建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

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

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

[](()2、必备口诀

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

一对多，两张表，多的表加外键。

一对一两种方案：主键共享，外键唯一。

提醒：实际的开发中，以满足客户需求为主，有时候会拿冗余换执行速度。

[](()三十四道练习题

---

1、取得每个部门最高薪水的人员名称；

SELECT e.ename,t.maxsal FROM emp e

JOIN (SELECT deptno,MAX(sal) maxsal FROM emp GROUP BY deptno) t

ON t.maxsal = e.sal AND t.deptno = e.deptno ; #需要满足两个条件！

2、哪些人的薪水在部门的平均薪水之上；

SELECT e.ename,e.sal,t.* FROM emp e

JOIN (SELECT deptno,AVG(sal)avgsal FROM emp GROUP BY deptno ) t

ON e.deptno = t.deptno AND e.sal > t.avgsal ; # 与第一题类似满足两个条件

3、取得部门中（所有人的）平均的薪水等级；

SELECT t.deptno,AVG(grade)

FROM (

SELECT e.sal,e.deptno,s.grade

FROM emp e JOIN salgrade s

ON e.SAL BETWEEN s.losal AND s.hisal ) t

GROUP BY deptno

4、不准用组函数（Max）取得最高薪水

SELECT sal maxsal FROM emp ORDER BY sal DESC LIMIT 1 ;

5、取得平均薪水最高的部门的部门编号

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

6、取得平均薪水最高的部门的部门名称

SELECT deptno,dname FROM dept

WHERE DEPTNO = (SELECT deptno FROM emp e GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1) ;

7、求平均薪水等级最低的部门的部门名称

SELECT deptno,dname FROM dept

WHERE DEPTNO = (SELECT deptno FROM emp e GROUP BY deptno ORDER BY AVG(sal) LIMIT 1) ;

8、取得比普通员工（员工代码没在mgr字段出现的）的最高薪水还要高的领导人姓名；

注意（比“普通员工最高薪水”还要高的一定是领导）

SELECT e.ename FROM emp e

WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE empno NOT IN

(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL))

#not in 使用的时候，后面的小括号记得排除null

9、取得薪水最高的前五名

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5 ;

10、取得薪水最高的第6名刀到第10名员工

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5,5 ;

11、最后入职的五名员工

SELECT ename,HIREDATE FROM emp ORDER BY HIREDATE DESC LIMIT 5

12、取得每个薪水等级有多少个员工

SELECT COUNT(*),grade

FROM (SELECT e.ename,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal ) t

GROUP BY grade

13.列出所有员工以及领导的名字

SELECT a.ename ‘员工’ ,b.ename ‘领导’ FROM emp a LEFT JOIN emp b ON a.mgr = b.empno

14、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

SELECT a.ename ‘员工’ ,a.hiredate,b.ename ‘领导’,b.hiredate,d.dname ‘部门名称’

FROM emp a

JOIN emp b ON a.mgr = b.empno

JOIN dept d ON d.deptno = a.deptno

WHERE a.hiredate < b.hiredate

15、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT d.dname ‘部门名称’,e.*

FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno ;

16、列出至少有五个员工的所有部门

SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(*) >= 5 ;

17、列出薪金比 SMITH多的所有员工信息

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘smith’)

18、列出所有CLERK(办事员)的姓名，部门名称，部门的人数。

注意：对两张临时表，进行连接查询！（目前最复杂）

SELECT t1.ename,t1.job,t1.dname,t2.deptcount

FROM

(SELECT e.ename,e.job,d.dname,d.deptno FROM emp e JOIN dept d ON d.deptno = e.deptno WHERE job = ‘clerk’) t1

JOIN

(SELECT deptno,COUNT(*)deptcount FROM emp GROUP BY deptno) t2