💂 个人网站: 【紫陌】【笔记分享网】
💅 想寻找共同学习交流、共同成长的伙伴， 请点击【前端学习交流群】
目录
1.mysql常用命令：
2.创建表和使用表
2.1介绍表
2.2创建表
2.3更新表
2.3.1添加列（字段）
2.3.2删除列（字段）
2.4查询表
2.4.1查询单个字段
2.4.2查询多个字段
2.4.3查询整个表
2.4.5查询排序数据【order by】
2.5.6过滤数据【where】
2.5.7数据处理函数
2.5.8分组查询
2.5.9使用子查询
2.5连接查询
2.5.1 内连接之非等值连接
2.5.2内连接之非等值连接
2.5.3内连接之自连接
2.5.4外连接（右外连接）
2.5.5外连接（左外连接）
2.5.6 union合并查询结果集
2.6 limit（非常重要）
2.7表的查询学完了（总结）
2.8插入数据insert
2.9修改update
2.10删除数据 delete
3视图(view)
4.约束(constraint)
1.约束的作用？
2.约束包括哪些？
1.非空约束 not null
2.唯一性约束：unique
3.unique和not null 可以联合
4.主键约束（primary key，简称PK）
5.外键约束 foreign key 简称 fk（重点）
5.存储引擎
1.存储引擎介绍
6.事务（重点）
6.1认识事务
6.2事务的实现
6.3怎么提交事务，怎么回滚事务？
6.4事务的4个特性
7.索引
7.1索引的实现原理
7.2创建和删除索引
7.3索引的失效
8.0 DBA常用命令
9.0 数据库三范式
1.mysql常用命令：
退出mysql
exit;查看mysql中有哪些数据库：
show databases;选择数据库
use 数据库名;创建数据库
create database 数据库名;查看数据库下有哪些表
show tables;注意：以上命令不区分大小写。
查看mysql数据库版本号：
select version();查看当前使用的是哪一个数据库
select database();注意：mysql是不见“;”不执行，“;”表示结束
\c可以用来终止一条命令的输入。
2.创建表和使用表
2.1介绍表
数据库最基本的单元是表：table
什么是表table？为什么用表来存储数据呢？
姓名 性别 年龄(列：字段)
---------------------------
张三 男 20 ------->行（记录）
李四 女 21 ------->行（记录）
王五 男 22 ------->行（记录）
数据库当中是以表格的形式表示数据的。
因为表比较直观。
任何一张表都有行和列：
行（row）：被称为数据/记录。
列（column）：被称为字段。
姓名字段、性别字段、年龄字段。
了解一下：
每一个字段都有：字段名、数据类型、约束等属性。
字段名可以理解，是一个普通的名字，见名知意就行。
数据类型：字符串，数字，日期等，后期讲。
约束：约束也有很多，其中一个叫做唯一性约束，
这种约束添加之后，该字段中的数据不能重复。2.2创建表
# 语法
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。
字段名：见名知意。
表名和字段名都属于标识符。
代码示例：
创建一个学生表？
学号、姓名、年龄、性别、邮箱地址
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
注意：
1、在同一张表中，字段名不能相同
2、宽度 和 约束条件为可选参数，字段名 和 字段名下记录的类型 是必须的
3、最后一个字段后不能加逗号
2.2.1 mysql中的数据类型
很多数据类型，我们只需要掌握一些常见的数据类型即可。
varchar(最长255)
可变长度的字符串
比较智能，节省空间。
会根据实际的数据长度动态分配空间。
优点：节省空间
缺点：需要动态分配空间，速度慢。
char(最长255)
定长字符串
不管实际的数据长度是多少。
分配固定长度的空间去存储数据。
使用不恰当的时候，可能会导致空间的浪费。
优点：不需要动态分配空间，速度快。
缺点：使用不当可能会导致空间的浪费。
varchar和char我们应该怎么选择？
性别字段你选什么？因为性别是固定长度的字符串，所以选择char。
姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。
int(最长11)
数字中的整数型。等同于java的int。
bigint
数字中的长整型。等同于java中的long。
float
单精度浮点型数据
double
双精度浮点型数据
date
短日期类型
datetime
长日期类型
clob
字符大对象
最多可以存储4G的字符串。
比如：存储一篇文章，存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
Character Large OBject:CLOB
blob
二进制大对象
Binary Large OBject
专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
你需要使用IO流才行。
2.3更新表
2.3.1添加列（字段）
语法：
alter table table_name add column 列名 字段类型
示例：
alter table 表名 add column 列名 varchar(30);注意：
在实际的开发中，需求一旦确定之后，表一旦设计好之后，很少的
进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。
修改表的结构，对应的java代码就需要进行大量的修改。成本是比较高的。
这个责任应该由设计人员来承担！
2.3.2删除列（字段）
语法：
alter table table_name
drop column 字段名
示例：
alter table 学生表
drop column 学号2.4查询表
2.4.1查询单个字段
语法：
select 字段名 from table_name
示例：
select 姓名 from 员工表2.4.2查询多个字段
语法：
select 字段名1,字段名2 from table_name
示例：
select 姓名,性别 from 员工表
2.4.3查询整个表
语法：
select * from table_name
示例：
select * from 员工表这种方式的缺点：
1、效率低
2、可读性差。
在实际开发中不建议，可以自己玩没问题。
你可以在DOS命令窗口中想快速的看一看全表数据可以采用这种方式。
2.4.4把查询结果去除重复记录【distinct】
语法：
select distinct 字段名 from table_name
示例：
select distinct 性别 from 员工表注意：原表数据不会被修改，只是查询结果去重。
2.4.5查询排序数据【order by】
按一个字段排序
语法：
select 字段名 from table_name order by 字段名
示例：
select 身高 from 学生表 order by 身高按多个字段排序
语法：
select 字段名1,字段名2 from table_name order by 字段名1,字段名2
示例：
select 字段名1,字段名2 from 学生表 order by 字段名1,字段名2指定排序方向
语法：
--升序排列
select 字段名 from table_name order by 字段名 asc
--降序排列
select 字段名 from table_name order by 字段名 desc
示例：
--升序排列
select 身高 from 学生表 order by 身高 asc
--降序排列
select 身高 from 学生表 order by 身高 desc2.5.6过滤数据【where】
使用where语句
语法：
select 字段名1,字段名2 from table_name where 限制条件
示例：
select *from 学生表 where 性别 = '男'不匹配检查
语法：
select 字段名1,字段名2 from 学生表 where 字段名 <> 某个值
示例：
列出不是团员的所有学生信息
select *from 学生表 where 党员 <> 1注意：！= 和 <> 通常可以互换，但是并不是所有的DBMS都支持这两种不等于操作符。
示例：
列出不是团员的所有学生信息
select *from 学生表 where 团员 <> 1范围值检查
在where子句中使用between…and…between匹配范围中所有的值，包括指定的开始值和结束值
示例：
查找身高在1.60~1.70之间的学生信息
select *from 学生表 where 身高 between 1.50 and 1.80空值检查
语法：
当一个列不包含值时，称其包含空值NULL
示例：
使用select语句的is null子句来检查空值
select *from 学生表 where 性别 is null组合where语句
（and操作符）
语法：
要通过不止一个列进行过滤，可以使用and操作符给where子句附加条件
示例：
select *from 学生表 where 性别 = '男' and 团员 = 1or操作符
语法：
or操作符与and操作符正好相反，它指示检索匹配任一条件的行。
示例：
select *from 学生表 where 性别 = '男' or 团员 = 1求值顺序
where子句可以包含任意数目的and和or操作符。允许两者结合以进行复杂、高级的过滤。
示例：
select *from 学生表 where (性别 = '女' or 团员 = 1) and 身高 > 1.70注意：SQL在处理or操作符前，优先处理and操作符，即and在求值过程中优先级更高。
in操作符
语法：
in操作符用来指定条件范围，范围中的每个条件都可以进行匹配
in取一组由逗号分隔、括在圆括号中的合法值。
示例：
select *from 学生表 where 身高 in ('1.60','1.55','1.75')not 操作符
语法：
where子句的not操作符有且只有一个功能，否定器后所跟的任何条件
not关键字可以用在要过滤的列前，而不是列后。
示例：
select *from 学生表 where not 团员 = 1like关键字
称为模糊查询，支持%或下划线匹配
%匹配任意多个字符
下划线：任意一个字符。
（%是一个特殊的符号，_ 也是一个特殊符号）
找出名字中含有A的
select 字段名 from 表名 where 字段名 like '%A%';
找出名字以T结尾的
select 字段名 from 表名 where 字段名 like '%T';
找出名字以G开始的
select 字段名 from 表名 where 字段名 like 'G%';
找出第二个字每是C的？
select 字段名 from 表名 where 字段名 like '_C%';
2.5.7数据处理函数
数据处理函数又被称为单行处理函数
单行处理函数的特点：一个输入对应一个输出。
和单行处理函数相对的是：多行处理函数。（多行处理函数特点：多个输入，对应1个输出！）
常用的文本处理函数：
| 函数 | 说明 |
| left() | 返回字符串左边的字符 |
| length() | 返回字符串长度 |
| lower() | 将字符串转换为小写 |
| ltrim() | 去掉字符串左边的空格 |
| right() | 返回字符串右边的字符 |
| rtrim() | 去掉字符串右边的空格 |
| soundex() | 返回字符串的soundex值 |
| substr() 或substring() | 提取字符串的组成部分 |
| upper() | 将字符串转换为大写 |
分组函数（多行处理函数）
多行处理函数的特点：输入多行，最终输出一行。
注意：
分组函数在使用的时候必须先进行分组，然后才能用。
如果你没有对数据进行分组，整张表默认为一组。
| 函数 | 说明 |
| avg() | 返回某列的平均值 |
| count() | 返回某列的行数 |
| max() | 范恢复某列的最大值 |
| min() | 返回某列的最小值 |
| sum() | 返回某列之和 |
AVG()函数
AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。
AVG()可以用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
示例：
求出成绩表中的平均成绩：
select AVG(成绩) as 平均成绩 from 成绩表COUNT()
COUNT()函数进行计数
示例：
求出学生表中的总人数
select COUNT(*) as 总人数 from 学生表注意：
1.使用COUNT(*)对表中的数据数目进行计数，不管表列中包含的是空值(NULL)还是非空值
2.使用COUNT(column)对特定列中具有值得行进行计数，忽略NULL值
组合分组函数
select语句可以根据需要包含多个分组函数
示例：
select COUNT(*) as 商品数目
MIN(价格) as 最低商品价格
MAX(价格) as 最高商品价格
AVG(价格) as 商品均价
from 商品表2.5.8分组查询
分组:
是使用select语句的group by子句建立的
求出每种商品的商品数量
select 商品名,COUNT(*) as 商品数量
from 商品表
group by 商品名重点结论：
在一条select语句当中，如果有group by语句的话，
select后面只能跟：参加分组的字段，以及分组函数。
其它的一律不能跟。
过滤分组（having）
使用having可以对分完组之后的数据进一步过滤。
having不能单独使用，having不能代替where，having必须和group by联合使用。
优化策略：
where和having，优先选择where，where实在完成不了了，再选择
having。
示例：
求出商品数量大于等于2的商品数量
select 商品名,COUNT(*) as 商品数量
from 商品表
group by 商品名
having COUNT(*)>=2where在数据分组前进行过滤，having在数据分组后进行过滤
2.5.9使用子查询
子查询：
select语句中嵌套select语句，被嵌套的select语句称为子查询。
利用子查询进行过滤
在select 中子查询总是从内向外进行处理，作为子查询的select语句只能查询单个列
示例：
select
..(select).
from
..(select).
where
..(select)
select * from 选课表 where 学号 in
(select 学号 from 学生表 where 性别 = '男')2.5连接查询
什么是连接查询？
从一张表中单独查询，称为单表查询。 emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。 这种跨表查询，多张表联合起来查询数据，被称为连接查询。
根据表连接的方式分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接
当两张表进行连接查询时，没有任何条件的限制会发生什么现象？
当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个数学现象。）
怎么避免笛卡尔积现象？
连接时加条件，满足这个条件的记录被筛选出来！
语法：
select 员工表的员工名字,部门表的部门名
from 员工表,部门表
where 员工表.名字=部门表.部门名
示例：
select
emp.ename,dept.dname
from
emp, dept
where
emp.deptno = dept.deptno;注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数
2.5.1 内连接之非等值连接
--inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
select
e.ename,d.dname
from
emp e -- as省略了，给字段起别名，效率更高
inner join
dept d
on
e.deptno = d.deptno; -- 条件是等量关系，所以被称为等值连接。2.5.2内连接之非等值连接
案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？
select
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。
2.5.3内连接之自连接
案例：查询员工的上级领导，要求显示员工名和对应的领导名？
emp a 员工表 emp b 领导表
select
a.ename as '员工名', b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno; --员工的领导编号 = 领导的员工编号以上就是内连接中的：自连接，技巧：一张表看做两张表
注意：
内连接：（A和B连接，AB两张表没有主次关系。平等的。）
2.5.4外连接（右外连接）
语法：
select 员工表.员工名,部门表.部门名
from 员工表 right join 部门表 on 员工表.编号=部门表.编号
语法
-- outer是可以省略的，带着可读性强。
select
e.ename,d.dname
from
emp e
right outer join
dept d
on
e.deptno = d.deptno;right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将 这张表的数据全部查询出来，捎带着关联查询左边的表。 在外连接当中，两张表连接，产生了主次关系。
2.5.5外连接（左外连接）
语法：
select 员工表.员工名,部门表.部门名
from 部门表 right join 员工表 on 员工表.编号=部门表.编号
-- outer是可以省略的，带着可读性强。
select
e.ename,d.dname
from
dept d
left outer join
emp e
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的连接条件2.5.6 union合并查询结果集
语法;
select 字段名1,字段名2,字段名3
from table_name
where 限制条件1
union
select 字段名1,字段名2,字段名3
from table_name
where 限制条件2
示例：
select 姓名,性别
from 学生表
where 学号 in ('01','02','03')
union
select 姓名,性别
from 学生表
where 学号 ='04'注意：union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。
2.6 limit（非常重要）
limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。 百度默认：一页显示10条记录。 分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。 可以一页一页翻页看。
完整用法：limit (startIndex, length)
startIndex是起始下标，length是长度 。起始下标从0开始。
按照薪资降序，取出排名在前5名的员工？
select
ename,sal
from
emp
order by
sal desc
limit 5; --取前5缺省用法：limit 5; 这是取前5.
注意：mysql当中limit在order by之后执行
取出工资排名在[3-5]名的员工？
select
ename,sal
from
emp
order by
sal desc
limit
2, 3;2.7表的查询学完了（总结）
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..
从某张表中查询数据，
先经过where条件筛选出有价值的数据。
对这些有价值的数据进行分组。
分组之后可以使用having继续筛选。
select查询出来。
最后排序输出！2.8插入数据insert
语法格式：
insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
insert语句中的“字段名”可以省略
注意：前面的字段名省略的话，等于都写上了！所以值也要都写上！
语法：
insert into table_name
values(值1, 值2,....)
示例：
insert into 学生表01
values('李明','男','1.70')2.9修改update
语法：
update table_name
set
字段名1='值',
字段名2='值'
where 限制条件
示例：
update 学生表
set
姓名='小红',
性别='女'
where 学号='01'注意：没有条件限制会导致所有数据全部更新。
2.10删除数据 delete
语法;
delete from table_name
where 要删除行的信息
示例：
delete from 学生表
where 学号='01'快速删除表中的数据？【truncate比较重要，必须掌握】
--删除dept_bak表中的数据
delete from dept_bak; --这种删除数据的方式比较慢。
用法：truncate table dept_bak; （这种操作属于DDL操作。）
删除表操作？
drop table 表名; -- 这不是删除表中的数据，这是把表删除。delete语句删除数据的原理？（delete属于DML语句！！！）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！！！
truncate语句删除数据的原理？
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。
大表非常大，上亿条记录？
删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！
truncate是删除表中的数据，表还在！
3视图(view)
什么是视图？
view:站在不同的角度去看待同一份数据。
数据库中的建立的表table叫物理表
view是查询结果的保存，它是虚拟表
怎么创建视图对象？怎么删除视图对象？
表复制：
create table dept2 as select * from dept;
dept2表中的数据：
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
创建视图对象：
create view dept2_view as select * from dept2;
删除视图对象：
drop view dept2_view;
注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;用视图做什么？
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）
--面向视图查询
select * from dept2_view;
-- 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');
-- 查询原表数据
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 60 | SALES | BEIJING |
+--------+------------+----------+
-- 面向视图删除
delete from dept2_view;
-- 查询原表数据
mysql> select * from dept2;
Empty set (0.00 sec)创建视图:
语法：
create view table_name as
select 创建视图的相应信息
示例：
create view
emp_dept_view
as
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;视图对象在实际开发中到底有什么用？
《方便，简化开发，利于维护》
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
可以把这条复杂的SQL语句以视图对象的形式新建。
在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。
并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要
修改视图对象所映射的SQL语句。
我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。
可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是
存储在硬盘上的，不会消失。
再提醒一下：
视图对应的语句只能是DQL语句。
但是视图对象创建完成之后，可以对视图进行增删改查等操作。
4.约束(constraint)
1.约束的作用？
在创建表时我们需要给表中的字段增加一些约束，来保证表数据的完整性，有效性
2.约束包括哪些？
- 非空约束 not null
- 唯一性约束 unique
- 主键约束 primary key(简称pk)
- 外键约束 foreign key (简称fk)
- 检查约束 check（MYSQL不支持，Orace支持）
1.非空约束 not null
非空约束所约束的字段不能为null。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null
);
mysql> insert into t_vip(id) values(1);
ERROR 1364 (HY000): Field ‘name’ doesn’t have a default value
2.唯一性约束：unique
唯一性约束unique约束的字段不能重复，但是可以为NULL。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);
insert into t_vip(id,name,email) values(1,'wangwu','wangwu@qq');
如果再插入一条wangwu，就会报错
mysql> insert into t_vip(id,name,email) values(1,‘wangwu’,‘lisu@qq’);
ERROR 1062 (23000): Duplicate entry ‘wangwu’ for key ‘name’
新需求：name和email两个字段联合起来具有唯一性
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255) unique //约束添加在列的后面，被称为列级约束
);
insert into t_vip(id,name,email) values(1,'wangwu','wangwu@qq');
insert into t_vip(id,name,email) values(2,'wangwu','wangwu@sina');
这样达不到新的需求，插入数据会报错，因为wangwu和wangwu重复。这样创建表示，name和email各自唯一。
两个字段联合唯一
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) //这样约束被称为表级约束
);
两个字段同时一样，才插入失败。
3.unique和not null 可以联合
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null unique
);
在mysql中，如果一个字段同时被 not null 和 unique 约束的话，该字段自动变成主键字段。（但Oracle不一样）
4.主键约束（primary key，简称PK）
主键约束的相关术语：主键约束、主键字段、主键值。
主键值是每一行记录的唯一标识。
注意：任何一张表都应该有主键，没有主键，表无效！
主键的特征：not null + unique（不能为空，同时不能重复）
添加主键约束：
DROP TABLE t_vip;
CREATE TABLE t_vip(
id INT PRIMARY KEY,
NAME VARCHAR(255)
);
INSERT INTO t_vip(id,NAME) VALUES(1,'zhangsan');
INSERT INTO t_vip(id,NAME) VALUES(2,'lisi');
INSERT INTO t_vip(id,NAME) VALUES(2,'baiye');错误信息：Duplicate entry '2' for key 'PRIMARY'
也可以这样写表示表级约束，前面是列级约束
CREATE TABLE t_vip(
id INT,
NAME VARCHAR(255),
PRIMARY KEY(id)
);表级约束主要给多个字段起来添加约束
在mysql中有一种机制可以帮助我们自动维护一个主键值
CREATE TABLE t_vip(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255)
);
INSERT INTO t_vip(NAME) VALUES('zhangsan');
INSERT INTO t_vip(NAME) VALUES('lisi');
INSERT INTO t_vip(NAME) VALUES('baiye');
INSERT INTO t_vip(NAME) VALUES('baiye');AUTO_INCREMENT表示自增，从1开始
5.外键约束 foreign key 简称 fk（重点）
当两张表有父子关系时，子表上与父表关联的字段为保证数据有效需要加外键约束，被引用的字段可以不是主键可以为null但是必须具有唯一性
设计数据库表，描述“班级和学生”信息：
班级一张表、学生一张表
t_class 班级表
classno(pk) classname
100 北京市大兴区亦庄镇第二中学高三1班
101 北京市大兴区亦庄镇第二中学高三2班
t_student 学生表
no(pk) name cno(fk引用t_class这张表的classno)
1 jack 100
2 luck 100
3 lilei 100
4 zhaf 101
5 wan 101
6 zhaoliu 101
当cno字段没有任何约束的时候，可能会导致数据无效。
所以为了保证cno字段中的值都是t_class表中的classno，需要给cno添加一个外键约束。
注意：
添加了外键约束，表与表之间产生了父子关系。
t_class是父表、t_student是子表。
删除表的顺序：先删子，再删父。
创建表的顺序：先创建父，再创建子。
删除数据的顺序：先删子，再删父。
插入数据的顺序：先插入父，再插入子。
drop table if exists t_student; //先删子
drop table if exists t_class;
create table t_class( //先创建父
classno int primary key,
classname varchar(255),
);
create table t_student(
no int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) references t_class(classno)
);
注意：
子表中的外键引用的附表中的某个字段，被引用的这个字段不一定是主键。但至少具有unique唯一性。
外键可以为null。
5.存储引擎
1.存储引擎介绍
存储引擎是MySQL中特有的一个术语，其他数据库中没有。
实际上存储引擎是一个表存储/组织数据的方式，不同的存储引擎，表存储数据的方式不同。
mysql默认的存储引擎是：InnoDB
mysql默认的字符编码方式是：utf8
可以在建表的时候给表指定存储引擎，在小括号")"的右边使用：ENGINE来指定存储引擎；CHARSET来指定这张表的字符编码方式。
create table t_product(
id int primary key,
name varchar(255)
) engine=InnoDB default charset=gbk;
查看mysql支持哪些存储引擎
命令：show engines \G
总共有9种存储引擎，不同版本支持的情况不一样。
6.事务（重点）
6.1认识事务
一个事务其实就是一个完整的业务逻辑，是一个最小的工作单元。要么同时成功，要么同时失败，不可再分。
假设转账，从A账户向B账户转账10000
A账户的钱减去10000（update语句）
B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑
这两个update语句要求必须同时成功或者同时失败，才能保证钱是正确的。
只有DML语句才会有事务这一说，其他语句和事务无关。
insert、delete、update。
一旦你的操作涉及到增、删、改，那么就一定要考虑安全问题。
6.2事务的实现
事务：就是批量的DML语句同时成功，或者同时失败。
事务是怎么做到多条DML语句同时成功和同时失败的？
InnoDB存储引擎：提供一组用来记录事务性活动的日志文件。
事务开启：
insert
delete
update
…
事务结束
在事务执行过程中。每一条DML的操作都会记录到“事务性活日志文”中
在事务执行过程中我们可以提交事务也可以回滚事务
提交事务:清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中,标志着事物成功结束
回滚事务：清空事务性活动的日志文件,将所有的DML操作全部撤销,标志着事务失败结束
6.3怎么提交事务，怎么回滚事务？
提交事务：commit
回滚事务：rollback
mysql默认情况下自动提交事务,那怎么自己提交事务呢？
START TRANSACTION;
SELECT * FROM dept;
START TRANSACTION;
INSERT INTO dept VALUE(50,'jjj','yyyy');
SELECT * FROM dept;
ROLLBACK;
SELECT * FROM dept;事务一旦提交回滚就没有作用
SELECT * FROM dept;
START TRANSACTION;
INSERT INTO dept VALUE(50,'jjj','yyyy');
SELECT * FROM dept;
COMMIT;
ROLLBACK;
SELECT * FROM dept;6.4事务的4个特性
ACID
- 原子性（A）：说明事务是最小的工作单元，不可再分。
- 一致性（C）：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
- 隔离性（I）：A事务和B事务之间具有一定的隔离。相当于多线程并发访问的每个线程。
- 持久性（D）：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！
事务与事务之间有4个隔离级别
- 读未提交：read uncommitted（最低隔离级别）
事务A可以读取到事务B未提交的数据。（没有提交就读取到了）
这种隔离级别存在的问题就是：脏读现象（Dirty Read），我们称堵到了脏数据。
这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二挡起步。
- 读已提交：read commited
事务A只能读取到事务B提交之后的数据。解决了脏读现象。
这种隔离级别存在的问题是：不可重复读取数据。
这种隔离级别是比较真实的数据，每次读到的数据是绝对真实的。
Oracle数据库默认的隔离级别是：read commited
- 序列化/串行化：serializable（最高的隔离级别）
这是最高隔离级别，效率最低。解决了所有的问题。
这种隔离级别表示事务排队，不能并发！
synchronized，线程同步（事务同步）
每次读取到的数据都是最真实的，但是效率是最低的。
7.索引
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
根据SQL语句扫描：select * from t_user where name = 'jack'，扫描name查找。
如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率比较低。
MySQL在查询方面主要就是两种方式：
第一种方式：全表扫描。
第二种方式：根据索引检索。
在mysql数据库当中，索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同。TreeSet底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。
遵循左小右大原则存放，采用中序遍历方式遍历取数据。
7.1索引的实现原理
什么情况下，我们会考虑给字段添加索引呢？
1.数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）。
2.该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
3.该字段很少的DML操作，因为DML之后，索引需要重新排序。
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。建议通过主键查询，建议通过unique约束的字段进行查询，效率会比较高。
7.2创建和删除索引
创建索引：
create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引，起别名：emp_ename_index
删除索引：
drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除。
在mysql当中，查看一个SQL语句是否使用了索引进行检索：
explain select * from emp where ename = 'KING';7.3索引的失效
索引也有失效的情况，如下：
"%"开头的模糊查询
select * from emp where ename like '%T';
尽量避免模糊查询的时候以"%"开头，这是一种优化的策略。
可以用SQL语句查看：explain select * from emp where ename like '%T';
使用or
如果使用or，那么要求or两边的条件字段都要有索引，索引才生效。如果其中一个字段没有索引，那么另一个字段上的索引也会失效。所以不建议使用or，可以使用union来代替。
使用复合索引
使用复合索引的时候，要使用左侧的列查找，索引才生效。使用右侧，则失效。
复合索引：两个字段，或者更多的字段联合起来添加一个索引。
create index emp_job_sal_index on emp(job,sal);
使用左侧：explain select * from emp where job = 'MANAGER'; 生效
使用右侧：explain select * from emp where sal= 800; 失效
索引列参与运算
在where当中索引列参加了运算，索引失效。
explain select * from emp where sal+1 = 800;
索引列使用了函数
在where当中索引列使用了函数。
explain select * from emp where lower(ename) = 'smith';
还有其他情况，先了解这些…
索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类：
- 单一索引：一个字段上添加索引。
- 复合索引：两个字段或者更多的字段上添加索引。
- 主键索引：主键上添加索引。
- 唯一性索引：具有unique约束的字段上添加索引。（注意：唯一性比较弱的字段上添加索引的用处不大。）
例：需要在examination_info表创建以下索引，规则如下：
在duration列创建普通索引 idx_duration、在exam_id列创建唯一性索引 uniq_idx_exam_id、在tag列创建全文索引 full_idx_tag。
create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(tag);
8.0 DBA常用命令
- 新建用户
- 授权
- 回收权限
- 数据的备份（数据的导入和导出）
-
数据的导出：
注意：在windows的dos命令窗口中
mysqldump 数据库>D:\数据库.sql -uroot -p密码
也可以导出指定的表：
mysqldump bjpowernode emp>D:\bjpowernode .sql -uroot -p密码 -
数据导入：
注意：需要先登录到mysql数据库服务器上。
然后创建数据库：create database bjpowernode;
使用数据库：use bjpowernode;
然后初始化数据库：source D:\bjpowernode.sql
9.0 数据库三范式
数据库设计范式：数据库表的设计依据，教你怎么进行数据库表的设计。
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
第三范式：建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
三范式是面试官经常问的，一定要熟记于心！！！
设计数据表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。
总结数据库表的设计：
一对一，第二张表外键唯一（fk+unique）
一对多，两张表，多的表加外键！
多对多，三张表，关系表两个外键！
嘱咐：
数据库设计三范式是理论上的，实践和理论有的时候有偏差。
最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。
因为在SQL中，表和表之间连接次数越多，效率越低。（笛卡尔积）
有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低。
更多推荐
MySQL的学习笔记（超详细超完整）
发布评论