JZTXT

mysql学习笔记（动力节点）

发布时间 2023-05-04 12:02:58作者: hangsingplus

1.连接数据库

1.1命令行连接

mysql -uroot -p123456 -- 连接
flush privileges; -- 刷新权限
-- 所有的命令都采用分号结尾，切换不需要
show databases; -- 查看所有的数据库
mysql> use school -- 切换数据库：use+数据库名
Database changed

show tables; -- 查看数据库所有的表
describe students; -- 查看表的信息

create database newdatabase; -- 创建一个数据库

exit; -- 退出连接 //注意分号结束一个语句

-- 单行注释
/*
多行注释
*/
-- 修改登录密码，登陆之后输入：
-- set password for username @localhost = password(newpwd);
-- 其中 username 为要修改密码的用户名，newpwd 为要修改的新密码
-- 再推出即可登入
set password for root @localhost = password('root')；  -- 新密码为root

1.2数据库 xxx 语言

DDL 对表结构增删改

DML 对表中数据增删改

DQL 查询

DCL 数据控制

TCL 事物控制

写命令行的地方：

点击【工具】菜单，选择【命令列界面】

2.操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中表的数据

mysql关键字不区分大小写

2.1操作数据库

2.1.1创建数据库

-- 为mysql语句
create database [if not exists] west

2.1.2删除数据库

drop database [if exists] west

2.1.3使用数据库

use school -- 如果表名或字段名是一个特殊字符或者系统带的变量名字，需要在使用的名字左右加上Tab见上方的符号
-- e.g.
select `user` from student;

2.1.4查看数据库

--查看当前使用的是哪个
show databases;
-- 或：
select database;
-- 查看mysql的版本号
select version();

2.1.5查看其它库中的表

show tables from databases;

2.1.6查看表的结构/创建语句

decs table name;
show create table <table name>;

2.1.8 退出数据库

exit;

2.2 条件限制查询

select 字段 from 表名 where 条件;
    -- 若字段部分为*，证明查询所有内容，但速度较慢，JDBC中一般不用

2.2.1 and / or

and 优先级高于or，若一起使用记不清优先级时，可使用括号

2.2.2 between … and …

等同于 >= and <=

可用于字符串和数字，都是闭区间

2.2.3 in

in 等同于多个 on

select ename,job from emp where sal in (800,5000); -- 注意in后边不是区间，而是具体的值

not in : 不是800和5000的

2.2.4 模糊查询 like

需掌握两个符号：

% : 多个字符 _ : 一个字符

比如找出名字里含O的：

select ename from emp where ename like '%O%';

找出名字里第二个字母为A的

select ename from emp where ename like '_A%';

找出名字里第三个字母为A的

select enmae from emp where ename like '__A';

找出名字里有下划线的

select enmae from emp where ename like '%_%';

因为‘_’表示任意一个字符，这样写会查询到所有名字，应该用转义字符：

select enmae from emp where ename like '%/_%';

找出名字中最后一个字是T的：

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

2.3 排序（升序、降序）

2.3.1 单个字段

按照工资升序，找出员工名字和工资

select 
	ename,sal
from
	emp
order -- 排序
	by -- 通过
 		sal; -- 工资

注意，默认为升序

若需指定：升序 asc 降序 desc

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

2.3.2 多个字段

越靠前的字段起的主导作用越大,只有当前面的字段无法完成排序时，才会启用后面的字段。

按照工资降序，若工资相等，按照名字升序排列。找出员工名字和工资：

select enmae,sal from emp order by sal desc,eame asc;

找出工作岗位是salesman的员工,并且按工资降序排列

select 
	ename,job,sal
from
	emp
where
	job='salesman'
order by
	sal 
		desc;

2.4 分组函数 (group function)

2.4.1 一共五个函数

属于多行处理函数 : 输入多行，输出一行

count 计数

sum 求和

avg 平均值

max 最大值

min 最小值

对于某一组数据（字段）操作

e.g 找出工资总和

select sum(sal) from emp;

2.4.2 单行处理函数

输入n行，输出n行，以ifnull()函数示例

前提：所有数据库中只要有null参与的运算，其最后结果一定是null，但所有分组函数自动忽略null.

引入：ifnull（）空处理函数

ifnull（可能为空的数据，被当作什么处理）

e.g

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

2.4.3 分组函数使用的语法规则

分组函数不可直接使用在where子句当中（下边解释）

count(*)和count(具体的某个字段)的区别：

​ count(*)：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）

​ count(comm)：表示统计comm字段中不为NULL的数据的总数量。

2.5 分组查询group by

group by：按照某个字段或某些字段进行分组

having：对分组之后的数据进行再次过滤

​ 两个搭配使用

案例：找出每个工作岗位的最高薪资

select max(sal) from emp group by job;
-- 先分组再找最大值

注意：分组函数一般都会和group by联合使用，且分组函数都是在group by结束之后才执行的，而group by在where

之后执行，所以分组函数不能在where中使用（相当于还未分组，不能使用分组函数）

当一句sql语句没有group by语句时，整张表的数据自成一组

当一句sql语句有group by语句时，select后边跟着的字段只能是分组函数和参与分组的字段，否则报错或结果不正确（Oracle比mysql语法规则严谨）

2.6分组之后再次过滤

用 having 还是 where ?

-- 找出薪资大于2900的部门
select deptno,max(sal) from emp group by deptno having max(sal) > 2900;
-- 这种方式效率低，分组后又舍弃数据，此时应使用where先过滤掉小于2900的再进行分组（基于where比group by先执行
select deptno,max(sal) from emp where sal > 2900 group by deptno; 
-- 效率较高，能使用where过滤的尽量使用where，涉及到求平均等分组函数的情况则只能用having

3.DQL语句

总结：以上为单表查询，接下来是多表查询

3.1执行顺序：

select

​ ..

from

​ ..

where

​ ..

having

​ ..

order by

​ ..

3.2 查询结果集的去重

-- 在字段前加distinct(截然不同的，完全分开的；清晰的)
select distinct job from emp;

注意：distinct：只能出现在所有字段的前面，且对后边的所有字段联合去重

3.3 连接查询

定义：多张表共同查询出最终的结果

3.3.1 连接查询的分类

根据表的连接方式：

​ 内连接：

​ 等值连接

​ 非等值连接

​ 自连接

​ 外连接：

​ 左外连接（左连接）

​ 右外连接（右连接）

​ 全连接

笛卡尔乘积现象：若未对连接查询的多张表的字段进行条件限制，最后的查询结果为多张表字段数量的乘积。

但是，加条件过滤避免了笛卡尔积现象也不能减少底层记录的匹配次数，只是显示出来的减少了。

3.3.2 给表起别名

select e.ename,d.dname from emp e,dept d;-- as或者空格都可以

起别名的好处：效率高（防止多张表有字段相同，需得访问多遍）、可读性好

3.3.3 内连接

等值连接：查询条件是等量关系

e.g查询每个员工的部门名称 (join on语句)

-- sql99语法结构更清晰，将查询条件与过滤条件where分离
select
	e.ename,d.dname
from 
	emp
inner join -- inner可以省略，带着可读性更好些（表示内连接）
	dmp
on 
	e.dptno = d.dptno;
where...

非等值连接：查询条件不是等量关系

select
	e.ename,e.salary,s.grade
from 
	emp
inner join -- inner可以省略，带着可读性更好些（表示内连接）
	dmp
on 
	e.salary btween s.lsal and s.higsal; -- 非等量关系
where...

自连接：一张表看作两张表，自己连自己。

e.g找出每个员工的上级领导，要求显示员工名和对应的领导名

将两张表视作员工表和领导表，员工的领导编号=领导的员工编号时即找出对应关系

select 
	a.enamem as '员工名',b.ename as '领导名'
from
	emp a
inner join
	emp b
on -- 条件
	a.mgr = b.empno;

3.3.4 外连接（使用的多）

3.3.4.1 与内连接区别

内连接：A和B表进行连接，将两个表能够匹配上的记录查询出来。A、B表没有主副之分，是平等的。

外连接：AB两表中一主表一副表，主要查询主表中的数据，捎带查询附表，若副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配。特点：主表的数据无条件的全部查询出来。

比如查员工领导，大boss也是员工，虽然没有领导但也不能丢失数据，要查出来的。

3.3.4.2 分类

左外连接（左连接）：表示左边的表使主表。

右外连接（右连接）：表示右边的表使主表。

两种连接写法不同。

-- 左外连接，左表占主导地位
select 
	a.enamem as '员工名',b.ename as '领导名'
from
	emp a
left outer join -- outer可省略
	emp b
on -- 条件
	a.mgr = b.empno;
-- 右外连接，右表占主导地位
select 
	a.enamem as '员工名',b.ename as '领导名'
from
	emp b -- 两表交换位置，等效
right join
	emp a
on -- 条件
	a.mgr = b.empno;

3.3.5 三张表以上的连接

找出每一个员工的部门名称、工资等级和上级领导。

员工和部门名称、工资等级内连接，和上级领导左连接，嵌套查询

select e.ename as '员工',d.name,s.grade,e1.ename '领导' 
from
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno
join 
	salgrade s
on
	e.sal between s.losal and s.hisal 
left join 
	emp e1
on 
	e.mgr = e1.empno;

3.4 子查询

3.4.1 where子句中使用子查询

找出高于平均薪资的员工信息

-- 第一步：找出平均薪资
select avg(sal) from emp;
-- 第二步：where过滤
select * from emp where sal > 2073.21;
-- 第一二步合并：
select * from emp where sal > (select avg(sal) from emp);

3.4.2 from子句中使用子查询

找出每个部门平均薪水的薪资等级

-- 一：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
-- 二：将以上查询结果当作临时表t,让t和salgrade表连接查询，条件是t.avgsal 在其薪资范围之间
select 
	t.*,s.grade
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
join 
	salgrade s
on 
	t.avgsal between s.losal and s.higsal;

找出每个部门平均的薪资等级

-- 一：找出每个员工的薪水等级
select e.name,e.sal,e.deptno,s.grade from emp join salgrade on e.sal between s.loasal and s.higsal;
-- 二：基于以上结果继续按照部门分组，求薪水等级的平均值
select 
	e.deptno,avg(s.grade) -- 基于以上查询出的等级结果求每个组的平均值
from 
	emp 
join 
	salgrade 
on 
	e.sal between s.loasal and s.higsal
group by -- 没有必要作临时表，继续往下写效率更高
	e.deptno;

3.4.3 select后边嵌套子查询

找出每个员工所在的部门名称，要求显示员工姓名和部门名

-- 之前学的：
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
-- 直接在select后边嵌套子查询
select e.ename,e.deptno,(select d.dname from dept d where d.deptno = e.deptno) as dname from emp e;

3.5 union（可以将查询结果相加）

找出工作岗位是a和b的员工

-- 一：
select e.ename,job from emp e where job = 'a' or job = 'b';
-- 二：
select e.ename,job from emp e where job in('a','b'); -- in相当于多个on，是值不是区间
-- 三：union
select e.ename,job from emp e where job = 'a'
union
select e.ename,job from emp e where job = 'b';

union可以将不同表中的数据拼接到一起，但两个字段的列数需一致

3.6 limit（分页查询用）

mysql特有

作用：取结果集中的部分数据

3.6.1 语法机制

limit startIndex,length; -- startIndex起始位置，length取几个长度，注意start是开区间，不包括起始位置

-- 比如，取工资前五名的员工
select ename,sal from emp order by sal desc limit 0,5;

3.6.2 Limit是sql语句最后执行的一个环节

4.DML语句 只操作表中的数据

4.1 表的创建

4.1.1 表的基本数据类型

varchar：可变长字符串

data：日期类型

BLOB：二进制大对象（存储图片、视频等流媒体信息）

CLOB：字符大对象（存储较大文本，比如，可以存储较大的字符串）

e.g 创建一个学生表

create table t_student(
-- 先写变量名再加数据类型
	no bigint,
	name varchar(266),
	sex char(1) default 1, -- 默认性别的值为1
	classno varchar(),
	birth char(10)，
);

4.1.2 insert 语句插入数据

第一种方式

语法格式：

insert into 表明（字段名1，2，3，4) values(值1，2，3，4);
注意：字段的数量和数据类型与值的数量和数据类型一一对应

insert into t(no,name,sex) values(1,'Marry','0');
-- 注意数据库用的utf-8,dos命令窗口采用的GBK，输入中文会出现乱码

需要注意的地方：

​ 当一条inser语句执行成功之后，表中必然会多一行记录，而新加的记录即使某些字段是NULL（未输入，系统自动指配），后面也没法用insert语句插入数据了，只能用update更新

第二种方式

insert into t_student values(值。。。)

这种方法输入的值必须和列数匹配

第三种方式 -- 一次插入多行数据

插入多行顺序时不用写字段名字，但要确保值的顺序与表中的列顺序相同

insert into t_student values(值。。。),（值。。。）

4.2 表的复制以及批量插入

表的复制：

create table emp2 as select * from emp;
-- 将查询语句作为表创建出来

将查询结果插入到一张表中：

insert into dept1 select * from dept;

4.3 修改表中的数据 ---- update

语法格式：

​ update 表名 set 字段名1=值1，字段名2=值2.。。。where 条件;

没有条件，整张表的数据全部更新

4.4 删除数据

语法格式：

​ delete from 表名 where 条件;

没有条件，整张表的数据全部删除

删除一行：

delete from dept where deptno=10;

删除大表（重点）：

delete :时间很长，效率慢，原因：没有释放真实的存储空间，可以找回delete删掉的数据

表截断 truncate：风险较高的不能后悔的方法

5 DDL语句

5.1关于表结构的修改

很少发生，用工具，出现在java中的sql：insert delete update select (对表中的数据操作)

增删改查术语：CRUD（各取首字母）Create（增） Retrieve(检索) Update(修改) Delete(删除)

drop: alter table 表名 drop 字段

​ drop table

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

5.2 查看表结构的信息

1.desc查看表结构的详细信息

desc table_name；
-- PS：此处desc是describe的缩写，用法： desc 表名/查询语句

2.desc降序排列数据

select ename,sal from emp order by sal desc; 
手动指定按照薪水由大到小排序（降序关键字desc） 
select ename,sal from emp order by sal asc; 
手动指定按照薪水由小到大排序（升序关键字 asc）
PS：此处desc是descend的缩写， 用法：select * from 表 order by 字段 desc

6 表的约束语句

定义：约束用于限制加入表的数据的类型。保证数据的合法性、完整性、有效性。 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

比如，加唯一性约束（unique）避免昵称重复，

非空约束（not null），

主键约束（primary key）:不能为空也不能重复，简称PK

外键约束（foreign），简称PK

检查约束（check），注意Oracle数据库有检查约束，mysql不支持

6.1 非空约束 not null

只有列级约束，没有表级约束

drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255) not null,
    psd varchar(255),
);
insert into t_user(id,username,psd) values(1,123);
-- 报错，必须一一匹配

6.2 唯一性约束 (unique)

修饰的字段具有唯一性，不能重复，但可以为NULL

6.2.1 给一个字段添加unique属性

drop table if exists t_user;
create table t_user(id int,username varchar(255) unique);
insert into t_user(id,username) values(2,'zhang');
insert into t_user(id,username) values(3,'zhang');
-- 报错：  Duplicate entry 'zhang' for key 'username'，
-- duplicate重复 entry进入
insert into t_user(id) values(3);
-- 未报错，查询后多一行数据

6.2.1 给多个字段添加unique属性

情况一：表级约束

drop table if exists t_user;
-- 这种方法对两个字段一起约束。只要两个不完全相同就行
create table t_user(id int,username varchar(255),unique(id,username) );
insert into t_user values(1,'li'),(2,'li'),(1,'zhang');
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
|    1 | li       |
|    1 | zhang    |
|    2 | li       |
+------+----------+
-- 结果正确

情况二：列级约束

drop table if exists t_user;
-- 这种方法分别对两个字段约束。一个字段都不能重复，每个字段都具有唯一性
create table t_user(id int unique,username varchar(255) unique);
insert into t_user values(5,'li'),(5,'zhang');
-- ERROR 1062 (23000): Duplicate entry '5' for key 'id'

6.3 主键约束 （primary key）

一张表的主键约束只能有一个

6.3.1 列级约束

drop table if exists t_user;
create table t_user(id int primary key,-- 列级约束
                    username varchar(255),email varchar(255));
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;
+----+----------+------------+
| id | username | email      |
+----+----------+------------+
|  1 | zs       | zs@123.com |
|  2 | ls       | ls@123.com |
|  3 | ww       | ww@123.com |
+----+----------+------------+
insert into t_user(id,username,email) values(1,'la','la@123.com');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
-- 除主键之外的字段可以为空
insert into t_user(id,email) values(7,'ee');
+----+----------+------------+
| id | username | email      |
+----+----------+------------+
|  1 | zs       | zs@123.com |
|  2 | ls       | ls@123.com |
|  3 | ww       | ww@123.com |
|  5 | gg       | NULL       |
|  7 | NULL     | ee         |
+----+----------+------------+
-- 主键不能为空
insert into t_user(email) values('xx');
-- ERROR 1364 (HY000): Field 'id' doesn't have a default value
-- 主键不能重复
insert into t_user(id,email) values(7,'ee');
-- ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMA'
-- 由上可知，id为主键，因为添加了主键约束，所以==主键字段不能重复也不能为NULL==

6.3.2主键的作用

表的设计三范式中第一范式就要求任何一张表都应该有主键，其作用：

主键值是这行记录在这张表的唯一标识（类似身份证号码

6.3.3 主键的分类

根据主键字段的数量划分：

​ 单一主键 ：（常用）

​ 复合主键：多个字段联合起来添加一个主键（不建议使用，违背三范式）

根据主键性质划分：

​ 自然主键：主键值最好是一个和业务没有关系的自然数（推荐这种方式）

​ 业务主键：主键值和系统的业务挂钩，比如，拿着身份证号做主键（不建议使用）修改不方便

最好不要拿和业务挂钩的字段作为主键，因为以后的业务一旦发生大概，主键值可能也需要随之变化，但主键变化可能会导致键值重复

6.2.3 使用表级约束方式定义主键

drop table if exists t_user;
create table t_user(id int,username varchar(255),primary key(id));
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
|  3 | ws       |
+----+----------+
insert into t_user(id,username) values(3,'oo');
-- ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

主键约束的列级约束和表级约束的区别和唯一性约束的差不多，不同之处是写在每个字段之后还是写在最后边

复合主键： 不建议使用！！！

​ 主键约束的复合主键就是将多个字段联合起来作为主键，联合起来不能重复也不能为空

6.2.4 主键值不可以为NULL

简单记忆为每个人不能没有身份证号

insert into t_student(sname) values('zz');
-- ERROR 1364 (HY000): Field 'sno' doesn't have a default value

6.4 mysql提供主键值自增 (重要) auto_increment

drop table if exists t_user;
-- 设置主键后加auto_increment,主键实现自增，id字段自动维护一个自增的数字，从1递增
create table t_user(id int primary key auto_increment,username varchar(255));
-- 只传入value的值
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('v');
insert into t_user(username) values('d');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | v        |
|  4 | d        |
+----+----------+

6.5 外键约束 (foreign key)

e,g,设计数据库表，维护学生和班级的信息

外键定义在子表引用父表数据的字段

先创建父表再创建子表，删除数据时先删除子表再删除父表，类似于继承的构造和析构，子表引用父表的数据

先插父再插子

-- 因为之前有这两个表，所以删表需要先删子表再删父表，否则会报错
drop table if exists t_student;
drop table if exists t_class;
-- 先创父再创子
create table t_class( cno int primary key,cname varchar(255) );
create table t_student( sno int,sname varchar(255),classno int,primary key(sno),foreign key(classno) references t_class(cno) ); -- reference：引用
insert into t_class values(101,'xxxxxx');
insert into t_class values(102,'yyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',101);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
select * from t_student;
select * from t_class;

insert into t_student values(7,'tt',103);-- 对应班级不存在，a child row：子记录
-- 报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

6.5.1 sql脚本的执行

运行sql脚本文件，连接数据库时直接 source+脚本路径即可，路径不可以加引号

source E:\Ty md\mysql\school.sql
Query OK, 0 rows affected (0.01 sec)

……..很多OK,按语句执行

	+------+-------+---------+
	| sno  | sname | classno |
	+------+-------+---------+
	|    1 | zs1   |     101 |
	|    2 | zs2   |     101 |
	|    3 | zs3   |     101 |
	|    4 | zs4   |     102 |
	|    5 | zs5   |     102 |
	|    6 | zs6   |     102 |
	+------+-------+---------+
6 rows in set (0.00 sec)

	+-----+--------+
	| cno | cname  |
	+-----+--------+
	| 101 | xxxxxx |
	| 102 | yyyyyy |
	+-----+--------+
2 rows in set (0.00 sec)

6.5.2 外键值可以为NULL

insert into t_student(sno,sname) values(15,'pp');
select * from t_student;
+------+-------+---------+
| sno  | sname | classno |
+------+-------+---------+
|    1 | zs1   |     101 |
|    2 | zs2   |     101 |
|    3 | zs3   |     101 |
|    4 | zs4   |     102 |
|    5 | zs5   |     102 |
|    6 | zs6   |     102 |
| NULL | pp    |     101 |
|   15 | pp    |    NULL |
+------+-------+---------+

6.5.3 外键不一定是主键

外键引用的别的表某个字段不一定必须要主键，但该字段必须要有唯一性，有unique约束

7. 存储引擎（了解）

mysql特有，为表的存储方式( ORACLE中有对应的机制，名字就叫“表的存储方式” ）

7.1 完整的建表语句

create table emp( id int,name varchar(255) );
-- 查看建表语句：
show create table emp;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT COLLATE=utf8mb4_bin |
-- 可以看出默认存储引擎为InnoDB，默认字符集是utf8mb4_bin
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 完整的建表语句
create table `emp` ( `id` int(11) DEFAULT NULL,`name` varchar(255)  COLLATE utf8mb4_bin DEFAULT NULL)ENGINE=InnoDB DEFAULT COLLATE=utf8mb4_bin;

7.2 查看不同版本支持的存储引擎

每个存储引擎都有其不同的优缺点，应合理选择

-- 显示本版本mysql 5.7.41 支持的存储引擎方式
show engines \G;
-- 共九个
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL

7.3 常见的存储引擎

7.3.1 MyISAM

Transaction事务，MyISAM不支持事务，是mysql最常用的存储引擎，但不是默认的

使用三个文件表示一张表：分别用来存储表结构（.frm)、表数据（.MYD)、表的索引（.MYI)

      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
  Transactions: NO
          XA: NO
  Savepoints: NO

 Transaction事务，MyISAM不支持事务，是mysql最常用的存储引擎，但不是默认的
使用三个文件表示一张表：分别用来存储表结构（.frm)、表数据（.MYD)、表的索引（.MYI)
优点：可被压缩，可以转换为只读表，提高检索效率
缺点：不支持事务

7.3.2 InnoDB

      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
  
  优点：支持事务、行级锁、外键等。这种存储引擎使数据的安全得到保障。
  表的结构存储在.frm文件中
  数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩和转换成只读
  数据库崩溃之后提供自动恢复机制
  支持即联删除和级联更新

7.3.3 MEMORY

	 Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
  
缺点：不支持事务。所有数据和索引存储在内存中，容易丢失
优点：查询索引速度最快
以前叫做HEPA引擎

8 事务 （Transaction） -- 很重要

8.1 事务的概述

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

比如，银行账户转账，从a账户转账10000到b账户，需要执行两次upadate语句

update emp set balance = balance - 10000 where name = 'a';
update emp set balance = balance + 10000 where name = 'b';
-- 以上两条语句必须同时成功或同时失败，不然金额错误
-- 因此需要使用数据库的事务机制

8.2 和事务相关的语句：DML

insert delete update

因为事务的存在是为了保障数据的完整性和安全性，而DML语句是操作表中的数据的

8.3 事务的原理

执行顺序写入缓存，留下操作历史记录，在提交或事务回滚后被清除

事务提交（commit）：对应文件随之改变

事务回滚（rollback）：将该事务已经完成对数据库的更新操作撤销

两个标志一出来都表示事务结束了

8.4 事务的四大特性（ACID）

1.原子性（atomicity）

一个事务必须被视为一个不可分割的最小的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

2.一致性（consistency）

要么同时成功，要么同时失败

数据库总是从一个一致性的状态转换到另一个一致性的状态（在前面的例子中，在没有执行取款前，余额总数为5000，在执行完事务后，取出来的钱再加上银行余额的总数跟原来的余额保持一致性原则，否则要么事务中的语句没有执行完，要么执行过程中出现异常）

3.隔离性（isolation）

事务A与事务B之间有距离

通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

4.持久性（durability）

数据必须持久化到硬盘文件里，事务才算成功地结束

一旦事务提交，则其所做的修改会永久保存到数据库（此时即使系统崩溃，修改的数据也不会丢失。）

8.5 事务的隔离性

事务隔离性存在隔离级别，理论上隔离级别有4个：

​ 第一级别：读未提交（read uncommitted)

​ 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

​ 读未提交存在脏读（Dirty commited)现象：表示读到了脏的数据

第二级别：读已提交（read committed）

​ 对方事务提交之后的数据我方可以提取到

​ 这种隔离级别解决了脏读现象

​ 存在的问题：不可重复读，可能每次读到的数据不一样

第三级别：可重复读（read committed）

​ 只要事务没结束，数据一直在，重复读取的数据相同

​ 这种隔离级别解决了不可重复读的问题

​ 存在读取到的数据是幻象的问题，读到的是备份数据

第四级别：序列化读/串行化读

​ 解决了所有问题

​ 效率低，事务需要进行排队

Oracle数据库默认的隔离级别是：读已提交

Mysql数据库默认的隔离级别是：可重复读

8.6 演示事务

mysql事务默认情况下自动提交（自动提交：只要执行任意一条DML语句则提交一次）

关闭自动提交：start transaction

演示回滚：

-- 创表
drop table if exists t_user;
create table t_user ( id int primary key auto_increment,username varchar(255) );
-- 演示回滚失败
insert t_user(username) values('zs');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
mysql> rollback;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
-- 写一句DML语句后自动提交（committ),回滚后仍是一条记录

-- 演示：使用start transaction;
start transaction;
insert t_user(username) values('ls');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
+----+----------+
mysql> rollback;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
-- 回滚成功

演示提交：

start transaction;
insert t_user(username) values('ww');
insert t_user(username) values('jack');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ww       |
|  4 | jack     |
+----+----------+
commit;rollback;
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ww       |
|  4 | jack     |
+----+----------+
-- commit提交后数据就写入硬盘了，rollback只能回滚到上一次的提交点

8.7 使用两个事务演示隔离级别

需要开两个cmd窗口表示两个事务

-- 设置事务的隔离级别，global为全局的意思
set global transaction isolation level read uncommitted;
-- 查看事务的全局隔离级别
select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
exit;

-- 修改完隔离级别后，所有数据库用户必须退出重新登录，才能使新的隔离级别生效
-- 开两个cmd窗口，都开启事务
-- 1.读未提交：这边写一条数据，还未提交或回滚（即写入硬盘），另一边再查询数据就多出新写的记录
-- 2.读已提交：这边插入数据并提交后，另一边才能查询到记录
-- 3.可重复读：即使这边删除表并提交，那边也可以查询到数据（读的是备份数据）
-- 4.序列化（serializable）/串行化：事务按顺序进行，这边的事务未提交，那边的DML语句一直处于等待状态，一旦这边的事务commit，那边瞬间出结果

9 索引(index)

9.1 定义

索引是给单个字段添加，底层用的B_tree的数据结构

索引相当于目录，在数据库中，查询一张表有两种方式：

​ 第一种：全表扫描

​ 第二种：根据索引检索（缩小扫描范围，提高检索效率）

9.2 添加索引的条件

不能随意添加索引，属于数据库中的对象，数据更改时索引也需要维护，数据经常修改的不适合添加索引；

添加索引：数据量庞大（根据需求和环境）、该字段的DML操作很少、该字段经常出现在where子句中

注意：主键和具有unique约束 的字段会自动添加索引

所以，根据主键检索效率较高，尽量使用主句。

9.3 添加 / 删除索引的方式

用explain查看sql语句的执行计划：

explain select username from t_user where username = 'za';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+------
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filt
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+------
-- type ：ALL表示全表扫描，rows表示扫描的记录数量

给username添加索引：

create index 索引名称 on 表名（字段名）；

create index t_username_index on t_user(username);
explain select username from t_user where username = 'zs';
 -- type ：ref，key变成了索引名，rows=1：只查询了一条记录

给username删除索引：

drop index 索引名 on 表名；

drop index t_username_index on t_user;

9.4 索引底层的实现原理

底层索引进行了排序，分区，表的索引会携带着数据在表中的物理地址，作为b+树的叶子节点存储，通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位到表中的数据，效率最高。

9.5 索引的分类

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

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

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

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

9.6 索引的失效

模糊查询时第一个通配符时‘%’，索引无法定位分区，只能使用全局扫描，索引失效

10 视图（了解）

10.1 定义

视图：站在不同的角度看同一张表的数据。（看作另一张表）

创建：create view myview as select empno,ename from emp;

只有select（DQL）语句可以创建视图对象

删除：drop view myview;

create table emp(empno int,ename varchar(255) );

注意：对视图进行CRUD会影响到原表数据

10.2 面向视图操作

mysql> select * from myview;
+-------+-------+
| empno | ename |
+-------+-------+
|     1 | zs    |
|     2 | ls    |
|     3 | ww    |
+-------+-------+

10.3 视图的作用

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD

11 DBA命令（了解）

11.1 将数据库中的数据导出

在windows的dos命令窗口中实行（不需要登录数据库）：

-- 1.导出整个库
mysqldump 数据库表名>D:\文件名.sql -uroot -proot
-- 将左边的表导入到（>是导入的意思）右边的文件中，要有数据库的密码才行

-- 2.导出指定数据库的指定表
mysqldump 数据库 表名>D:\文件名.sql -uroot -proot

11.2 导入数据

source(之前用过)

12 数据库设计的三大范式

12.1 定义

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

12.2 三范式

第一范式：任何一张表都应该有主键，每个字段的原子性不可再分

第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖（不完全依赖）

​ 多对多关系：用三张表，关系表里有两个外键（两张表表示信息，第三张表只用关系表示）

第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖

​ 一对多关系：两张表，多的表加外键

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

12.3 一对多的设计

两种方案：主键共享、外键唯一