mysql的快速复习 和一些思考记录

数据库设计

• 第一范式：有主键，具有原子性，字段不可分割

  数据库表中不能出现重复记录，每个字段是原子性的不能再分

  关于第一范式，每一行必须唯一，也就是每个表必须有主键。

  每一列不可再分！！ 就只有一个信息

• 第二范式：完全依赖，没有部分依赖

  第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖

• 第三范式：没有传递依赖

数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需 求。

多对多的关系数据库设计

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

一对多的关系数据库设计

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

一对一设计，有两种设计方案：

• 第一种设计方案：主键共享
• 第二种设计方案：外键唯一

基本命令行

创建

create database test_data;

create table table_test
(
    id int not NULL auto_increment,
    IP varchar(15) NULL,
    primary key (id)
) engine=InnoDB default charset= utf8;

drop table table_test;

/*添加列*/
alter table table_test add vend_phone CHAR(20);

/*删除列*/
alter table table_test  drop column vend_phone;

/*修改字段*/
alter table t_student modify student_name varchar(100) ;

/*例子*/
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d') / now(),3000, 500, 10);

/*创建新表 复制旧表*/
create table emp_bak as select empno,ename,sal fromemp;
/*查询的数据直接放到已经存在的表中*/
insert into emp_bak select * from emp where sal=3000;

约束

常见的约束

• a) 非空约束，not null
• b) 唯一约束，unique
• c) 主键约束，primary key
• d) 外键约束，foreign key
• e) 自定义检查约束，check（不建议使用）(在 mysql 中现在还不支持)
• 级联更新与级联删除 on update cascade;

drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3) not null,
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
);

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);

查询

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by …….
limit ...; (限制数据的个数)

以上语句的执行顺序

原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤的。

1. 首先执行 where 语句过滤原始数据
2. 执行 group by 进行分组
3. 执行 having 对分组数据进行操作
4. 执行 select 选出数据
5. 执行 order by 排序

select ephone,ename from person;
select ephone,ename from person where job = "IT";

where <> 500; /*不等于*/
where ename =null;
where ename='a' and ephone='118';
where esal > 50 and (ename='a' and ephone='118');
where jbob in ('IT','QQ');
where ename like 'M%'; /*模糊查询M开头的所有人   %N 结尾  %O% 有O  _A% 第二个字符有A*/ 


select * from person order by esal asc; /*从小到大 排序*/
select * from person order by esal desc; /*从小到大 排序*/
order by job desc, esal desc; /*多列 递减*/

select * from person where job = upper('it');
select lower(ename) from person;
select length(ename), ename from person where length(ename) > 5;

select * from person where HIREDATE='1981-02-20'; /*查询 1981-02-20 入职的员工*/
select empno, ename, date_format(date, '%Y-%m-%d %H:%i:%s') as date from emp;/*将入职日期格式化成 yyyy-mm-dd hh:mm:ss*/

select round(123.56); /*四舍五入*/
select rand(); /*随机数*/
select ifnull(comm,0) from emp; /*如果 comm 为 null 就替换为 0*/
select count(*) from emp; /*取得所有的员工数*/
select sum(esla) from emp; /*计算某一列的和*/  
avg(esal); /*平均数*/ max()  min()

select job, sum(sal) from emp group by job; /*通过job的个数进行分组，取得每个工作岗位的工资合计，要求显示岗位名称和工资合计*/

select job,deptno,sum(sal) from emp group by job,deptno; /*按照工作岗位和部门编码分组，取得的工资合计*/


select job, avg(sal) from emp group by job having avg(sal) >2000; /*如果想对分组数据再进行过滤需要使用 having 子句,,取得每个岗位的平均工资大于 2000 */

/*取得前 5 条数据*/
select * from emp limit 5;
/*从第二条开始取两条数据*/
select * from emp limit 1,2;
/*取得薪水最高的前 5 名*/
select * from emp e order by e.sal desc limit 5;

子查询

在 where 语句中使用子查询，也就是在 where 语句中加入 select 语句

/*查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名*/
/*首先取得管理者的编号，去除重复的  distinct 去除重复*/
select distinct mgr from emp where mgr is not null;
/*查询员工编号包含管理者编号的*/
select empno, ename from emp where empno in(select mgr from emp where mgr is not null);

/*查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水*/
select empno, ename, sal from emp where sal > (select avg(sal) from emp);

在 from 语句中使用子查询，可以将该子查询看做一张表

/*查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名*/
/*首先取得管理者的编号，去除重复的*/
select distinct mgr from emp where mgr is not null;
/*将以上查询作为一张表，放到 from 语句的后面*/
select e.empno, e.ename 
from emp e join (select distinct mgr from emp where mgr is not null) m 
on e.empno=m.mgr;

/*查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号*/
/*首先取得各个部门的平均薪水*/
select deptno, avg(sal) avg_sal from emp group by deptno;
/*将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级*/
select a.deptno,a.avg_sal,g.grade 
from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g 
on a.avg_sal between g.losal and g.hisal;

在 select 语句中使用子查询

/*查询员工信息，并显示出员工所属的部门名称*/
/*在 select 语句中再次嵌套 select 语句完成部分名称的查询*/
select e.ename, 
(select d.dname from dept d where e.deptno=d.deptno) as dname 
from emp e;

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

更新

update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where …….

/*将 job 为 manager 的员工的工资上涨 10%*/
update emp set sal=sal+sal*0.1 where job='MANAGER';

删除

可以删除数据，可以根据条件删除数据

/*语法格式：*/
Delete from 表名 where 。。。。。
/*删除津贴为 500 的员工*/
delete from emp where comm=500;
/*删除津贴为 null 的员工*/
delete from emp where comm is null;

创建视图

我们对视图进行增删改查，会影响到原始数据。相当是引用了原始数据

create view test_view as
select * from table_test;

drop view test_view;

存储过程

create procedure test()
begin
	select * from table_test;
end;

call test();

drop procedure test;

事务

start transaction read only;
delete * from table_test;
savepoint delete1; /*保存节点*/
drop table table_test;
rollback to delete1;
commit;

表与表格之间的连接

表与表之间常用的关联方式有两种：内连接、外连接，下面以MySQL为例来说明这两种连接方式。

内连接

内连接通过innner join来实现，它将返回两张表中满足连接条件的数据，不满足条件的数据不会查询出来。

select e.ename, e.sal, d.dname 
from emp e inner join dept d 
on e.deptno=d.deptno 
where e.sal>2000;

外连接

外连接通过outer join来实现，它会返回两张表中满足连接条件的数据，同时返回不满足连接条件的数据。外连接有两种形式：左外连接（left outer join）、右外连接（right outer join）。

• 左连接

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

• 右连接

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

全连接

full join是两张表的并集

表合并 union

/*查询 job 包含 MANAGER 和包含 SALESMAN 的员工*/
select * from emp where job in('MANAGER', 'SALESMAN');

select * from emp where job='MANAGER'
union
select * from emp where job='SALESMAN';

导入导出数据库

• 导出

  mysqldump -u用户名 -p 数据库名 > /xx/xx/数据库名.sql
  mysqldump -uroot -p abc >  /xx/xx/abc.sql
  

• 导入

  mysql -u用户名 -p 数据库名 < 数据库名.sql
  mysql -uroot -p123456 < abc.sql
  

常用的存储引擎

MyISAM 存储引擎

MyISAM 存储引擎是 MySQL 最常用的引擎。

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

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

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

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

• 索引文件 — 存储表上索引（mytable.MYI） —> 相当于一本的书的目录 用来缩小查找范围

  对于一个表只要是主键或者有unique约束的 key 都会自动添加索引；

• –灵活的 AUTO_INCREMENT 字段处理

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

InnoDB 存储引擎

**安全 ** 事务 InnoDB 存储引擎是 MySQL 的缺省引擎。

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

• 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
• InnoDB 表空间 tablespace 被用于存储表的内容 索引也存在 tablespace
• 提供一组用来记录事务性活动的日志文件
• 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
• 提供全 ACID 兼容
• 在 MySQL 服务器崩溃后提供自动恢复
• 多版本（MVCC）和行级锁定 – 支持外键及引用的完整性，包括级联删除和更新

MEMORY 存储引擎

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非 常快。

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

• 在数据库目录内，每个表均以.frm 格式的文件表示。
• 表数据及索引被存储在内存中。
• 表级锁机制。
• 不能包含 TEXT 或 BLOB 字段。

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

事务----InnoDB引擎

什么是数据库事务

事物就是一个完整的业务逻辑。 只有(update、delete、insert)会产生事务

例子：A 向 B 转账

• A的钱扣除
• B的钱增加

把上述的命令作为一个整体向系统提交，要么成功要么失败！！！ 不可再拆分！！

数据库的 事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。如果任意一个操作失败，那么整组操作即为失败，会回到操作前状态或者是上一个节点。

事务的四大特性ACID

事务具有四个特征 ACID

• a) 原子性(Atomicity)

  整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。

• b) 一致性(Consistency)

  在事务开始之前与结束之后，数据库都保持一致状态。 一个事务中的所有操作要么都是成功，要么都失败

• c) 隔离性(Isolation)

  一个事务不会影响其他事务的运行。

• d) **持久性(Durability) **

  在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

如何实现事务的 ACID 特性

MySQL事务的ACID，一致性是最终目的。
保证一致性的措施有：

• **A原子性：**靠undo log来保证（异常或执行失败后进行回滚）。
• **D持久性：**靠redo log来保证（保证当MySQL宕机或停电后，可以通过redo log最终将数据保存至磁盘中）。
• **I隔离性：**快照读（版本链和ReadView）和锁定读（利用三种锁）
• **C一致性：**事务的最终目的，即需要数据库层面保证，又需要应用层面进行保证，并且MySQL底层通过两阶段提交事务保证了事务持久化时的一致性。

事务怎么做的？

InnoDB存储引擎: 提供了一组用来记录事务性活动的日志文件。

• 事务开启了：

insert； update； delete； insert；。。。。。。

• 事务结束了！

这些都会被记录到事务性活动的日志文件中。

在事务执行的过程中，我们可以提交事务，也可以回滚事务。

• 提交事务

  清空事务性的日志文件、将数据全部持久化到数据库。 提交事务意味着成功的结束事务

• 回滚事务

  之前的DML(增、删、改)操作全部撤销，清空事务性活动的日志文件。回滚事务意味着失败的结束事务

事务中存在一些概念：

• a) 事务（Transaction）：一批操作（一组 DML）
• b) 开启事务（Start Transaction）
• c) 回滚事务（rollback） ---- 只能回滚到上一次的提交点.
• d) 提交事务（commit）
• e) SET AUTOCOMMIT：禁用或启用事务的自动提交模式

提交事务

• 默认提交

  在命令行中mysql默认自动提交事务，无法回滚。

• 关闭默认提交

  在每次执行MDL语句之前，执行 即可关闭. 然后

  start transaction；
  insert;
  delete;
  update;
  ......;
  
  rollback; /*回到 start transaction 之前的位置 */
  

事务的隔离

事务的隔离级别

• 读-未提交

读取数据不需要加 共享锁，这样就不会跟被修改的数据上的 排他锁 冲突；

• 读-已提交

各自事务操作，某个事务提交以后，另外的就可以查询

读操作需要加 共享锁，但是在语句执行完以后释放共享锁；

• 可重复-读

各自事务操作数据快照、结束事务之后才合并。一直查看的之前快照数据。

读操作需要加 共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；

• 串行化

是限制性最强的隔离级别，因为该级别 锁定整个范围的键，并一直持有锁，直到事务完成。 相当于同步锁。

索引

书的目录 缩小查找范围

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个 有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。MYISAM 和 INNODB 都是用 B+Tree 作为索引结构 （主键，unique 都会默认的添加索引）

优点

1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的查询速度，这也是创建索引的主要原因。
3. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点

1. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
2. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

实现原理

利用主键或者某一列的数据作为key，构建一个B+树，树中存储数据具体的物理位置，以供访问。

MySQL使用B+树作为主要的索引实现机制。B+树是一种平衡树数据结构，它优化了传统B树的性能，并且适用于磁盘存储。下面是MySQL索引的实现原理：

1. B+树结构：MySQL使用B+树作为索引的底层数据结构。B+树是一种多叉树，具有平衡的特性，可以高效地支持范围查询和按顺序遍历。

2. 索引类型：MySQL支持多种索引类型，包括聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index）。聚集索引决定了表中数据的物理排序顺序，而非聚集索引则是基于聚集索引的副本，它们分别适用于不同的查询需求。

3. 索引键选择：在创建索引时，可以选择一个或多个列作为索引键（Index Key）。索引键的选择对查询性能有重要影响，通常建议选择常用于查询条件和连接操作的列作为索引键。

4. 索引维护：当插入、更新或删除表中的数据时，索引也需要进行相应的维护，以保持数据的一致性和索引的有效性。这包括插入新的索引项、删除旧的索引项、更新索引键值等操作。

5. 索引选择器（Index Selector）：MySQL的查询优化器（Query Optimizer）根据查询条件和可用索引的统计信息选择最合适的索引来执行查询。优化器会考虑多个因素，如索引选择性、查询成本等。

6. 覆盖索引：当索引包含了查询所需的所有列时，称之为覆盖索引（Covering Index）。覆盖索引可以避免回表（Table Lookup）操作，提高查询性能。

创建索引

如经常根据 sal 进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对 sal 建立索引。

create unique index 索引名 on 表名(列名);
create unique index u_ename on emp(ename);

create index test_index on emp (sal);

删除索引

drop index u_ename on emp(ename);
alter table 表名 add unique index 索引名 (列名);

查看索引

show index from emp;

使用索引

explain select sal from emp where sal > 1500;

索引失效的情况

1. 使用函数操作：当在索引列上使用函数操作时，索引可能会失效。例如，考虑以下查询：

   explain SELECT * FROM table WHERE UPPER(column) = 'VALUE';
   

   在这个例子中，如果column是一个索引列，使用UPPER()函数会导致索引失效，因为MySQL无法利用索引的有序性来进行快速筛选。

2. 使用OR操作符连接条件：当使用OR操作符连接多个条件时，索引可能会失效。例如：

   explain SELECT * FROM table WHERE column1 = 'VALUE1' OR column2 = 'VALUE2';
   

   如果column1和column2都是索引列，MySQL可能无法同时使用这两个索引，而是选择全表扫描。

3. 使用NOT操作符：在某些情况下，使用NOT操作符可能导致索引失效。例如：

   explain SELECT * FROM table WHERE NOT column = 'VALUE';
   

   这会使MySQL无法使用索引进行快速筛选，而是进行全表扫描。

4. LIKE查询使用通配符前缀：当使用LIKE查询时，如果通配符（%）出现在查询的开头，索引可能无法生效。例如：

   explain SELECT * FROM table WHERE column LIKE '%VALUE';
   

   这会导致MySQL无法使用索引进行前缀匹配，而是进行全表扫描。

5. 使用函数索引：在某些情况下，将函数应用于索引列本身可能导致索引失效。例如，创建了一个函数索引：

   CREATE INDEX idx_func ON table (UPPER(column));
   

   在这种情况下，查询中直接使用column而不是UPPER(column)可能无法使用该函数索引。

视图

create view test_view as
select * from table_test;

drop view test_view;

MySQL视图在计算机中以文件存在的，可以把其表格一样看待。

1. 简化查询：视图可以隐藏复杂的查询逻辑和数据结构，提供一个简单的接口供用户查询。通过将多个表的关联查询、过滤条件和计算逻辑封装在一个视图中，用户可以使用简单的SELECT语句查询视图，而无需了解底层的复杂性。

2. 数据安全性：视图可以用于限制用户对数据的访问权限。通过在视图上设置适当的权限，可以控制用户只能访问视图中指定的列或行，而不是直接访问基础表。这提供了一种更细粒度的数据安全性控制机制。

3. 数据重用和模块化：视图可以被视为可重用的查询模块。一旦定义了视图，它可以在多个查询中使用，避免了在每个查询中重复编写相同的查询逻辑。这样可以提高开发效率，并且在需要修改查询逻辑时，只需修改视图定义而不影响使用该视图的查询。

4. 数据逻辑抽象：视图可以将底层数据结构进行逻辑抽象，使得数据的表现形式更符合业务需求。通过对基础表进行聚合、过滤、连接等操作，视图可以提供更易于理解和使用的数据模型。

5. 性能优化：在某些情况下，使用视图可以提高查询性能。通过将复杂的查询逻辑预先计算并缓存为视图，可以减少每次查询时的计算量，提高查询效率。