MySQL精简知识

MySQL

是一个DBMS，数据库管理系统。

关系型数据库：数据按照不同的类型分表存储，数据表之间可以有一些关联关系。

环境配置

mysql的默认端口号为3306.

mysql登录命令： mysql -u用户名 -p密码。-p后不写密码直接回车，进入密码输入框。

数据库相关概念

数据库

存储和管理数据的仓库。

在MySQL中，可以创建多个数据库。数据库本质就是一个目录。

数据表

在一个数据库中，可以创建多张表。数据表本质就是文件。

数据记录

在表中，一行代表一个记录。记录就是文件中的数据。

实际开发中，一个实体类一般对应一张表。

一个实体类的实例对象就是表中的一行记录。

一个数据库一般对应一个web项目。但是从技术角度，一个web项目也可以对应多个数据库。

如果我们想要操作这些数据，必须通过MySQL的dbms系统进行操作。使用的语言就是SQL。

SQL

结构化的查询语言，用于操作数据库。

学习的要点：多写多练，不断提升熟练度。

分类

DDL：数据定义语言。定义数据库的结构、数据表的结构。

DML：数据操作语言。操作数据的增删改。

DQL：数据查询语言。用于查询数据。

DCL：数据控制语言。一般是DBA（数据库管理员）使用的。

通用语法

1. 不区分大小写，推荐使用小写。

2. 所有的sql语句必须以分号结尾。

3. 注释：

   ​ 通用的：–空格 注释内容

   ​ 多行注释：/*注释内容*/

   ​ MYSQL的方言：# 注释内容

方言

在sql中，有标准语法。使用的比较多的是sql99标准。

在每个数据库中，都会在sql99标准的基础上进行语法的扩充。这些扩充的语法并不通用，仅在某个数据库可以使用，我们称之为方言。

注意事项

mysql中的字符集在使用时最好使用utf8mb4.

表的创建

表名尽量不要使用关键字，如果要使用，可以用反引号引起来。``

create table 表名(
	列名 列的类型(列的长度),
    ...
);

列的类型：

int

double：可以写成double(10,3) 。这种写法代表浮点数一共长度为10，其中小数位置是3位。

date：日期，年月日

time：时间，时分秒

datetime：年月日时分秒都有的时间，可以为null。

timestamp：时间戳，年月日时分秒都有的时间。时间戳必须有值，不能为null。如果不给它设置值或者赋值为null，它会自动把当前系统时间填充进去。

字符串类型必须指定长度

char：固定长度的字符串。指定为多长就是多长，不足的部分自动补空格。

varchar：可变长度的字符串。指定的长度是最大长度，实际长度为存值的长度。

数据新增

insert into 表名 列的列表 values 值的列表…

数据修改

update 表名 set 列名=值，列2=值2… where 条件列表

数据删除

delete from 表名 where 条件列表

truncate关键字也能完成表数据的删除。和delete from 表名不加条件效果看上去差不多。

区别：delete会把符合条件的数据一条一条的逐行删除，效率比较低。是一个DML语句。

truncate是把表直接删除，然后重新建一张一模一样的表。相当于drop table再create table。效率比较高。是一个DDL语句。

数据的查询

select 列的列表 from 表名

在列的列表之前加上DISTINCT关键字，可以取出结果中的重复值。

在sql中，跟null做任何运算，得到的值都是null。

ifnull函数，作用为当指定的列值为null时，函数的返回值替换成替代值。否则返回值为原值。

有两个参数：

​ 第一个参数代表列名

​ 第二个参数代表替代值。

别名：每个列都可以通过as关键字起别名，as关键字可以省略。

条件查询

不等于： != <>

等于：=

in 从指定的列表中多选一

null值判断：null值不能使用 = 或者!=判断的。必须使用is null或者is not null。

模糊查询：关键字like。指定的值中的通配符：_代表一个任意字符，不能没有。%代表0个或多个任意字符。

如果直接写 like “小米”，相当于写 = “小米”。

聚合函数

聚合函数进行计算时，会自动忽略所有的null值。

count()：可以写的形式：count(*) count(id) count(0)，推荐使用count(*),统计所有列，只要有一列不为null，就会增加一。

max()：最大值

min()：最小值

sum()：求和

avg()：求平均值

注意：聚合函数会把多个列的值通过某种运算得到一个值。所以属于多列聚合为一列的操作。那么在查询时，得出的结果是一个整体。不能出现个体的信息。

使用子查询（sql嵌套）完成需求，查询最大价格的产品

select name,price from product where price = (select max(price) from product);

排序查询

关键字：order by 列名 排序方式

排序方式：升序asc，默认值。 降序desc。

如果涉及到多个列的排序，优先按照前面的列排序；如果列的值相同，则再按照后面的列排序。

分组查询

一般配合聚合函数一起使用。

关键字：group by 分组依据，可以指定多个列。

SELECT brand,name,SUM(price) FROM product GROUP BY brand,name;

where和having的区别？

1. where是分组之前添加过滤条件，having是分组之后添加过滤条件。
2. where不支持聚合函数，having支持聚合函数。

分页查询

关键字：limit 此关键字是mysql的方言。

使用方式：limit 参数1 ， 参数2

参数1：开始的索引值，从0开始。

参数2：要查询的数据的条数。

limit 0,m 可以省略0,简写为limit m

实际分页查询时，经常根据页码数计算开始索引。

页码数===> 开始索引？

1 0-9

2 10-19

3 20-29

开始索引 = （页码数-1）* 页面中数据的条数,即等差数列

总结单表查询关键字顺序：select distinct … from … where … group by … having… order by …limit…

MySQL

约束

单表约束

1. 主键约束：非空的，唯一的。一般用于作为数据的唯一标识。通常和业务分离。

   ​ 主键在一张表中最多只能有一个，通常情况下表最好加入主键。

   ​ 主键可以由多列组成，它还是一个主键。这种主键叫联合主键（复合主键）。

   关键字：primary key

   复合主键：

   create table  student(
   		sid int ,
   		cid int,
   		score  double,
   		primary key	(sid,cid) -- 复合主键的指定方式
   );
   

   自动增长：配合数字类型的主键使用。如果自增的列没有指定值，也就是赋值为null，则自动进行增长赋值。值不会使用已经用过的值，自增也是基于已使用的所有值的最大值自增。

   关键字：auto_increment

2. 唯一约束

   ​ 当前列不能重复，一张表中可以加多个唯一约束。

   ​ 唯一约束底层实现使用的唯一索引。

   ​ 唯一约束并不是非空，所以可以指定null值，而且null值可以重复。

   关键字：unique。

3. 非空约束

   ​ 当前列不能为null。

   关键字：not null

多表约束

​ 外键约束

​ 外键：从我们的从表建立外键指向主表的主键，保证他们这两列的对应关系。

​ 关键字：

constraint [约束名称] foreign key (从表中外键列的名称)  references  主表的表名(主表的主键名)

alter table 表名 add constraint [约束名称] foreign key (从表中外键列的名称)  references  主表的表名(主表的主键名)

外键列可以为null

多表关系

一对一

建表原则：

1. 唯一外键

   ​ 在任意一张表中添加外键指向另一张表的主键，然后把外键添加上唯一约束。

2. 主键对应

   ​ 人为使用逻辑控制，两张表对应数据的主键设置为相同的值。

3. 合并表，一般一对一的表关系，可以把两张表合并成一张。

一对多

建表原则：一的一方是主表，多的一方是从表。在多的一方建立外键指向一的一方的主键。

多对多

建表原则：需要新建一张中间表，至少要有两个列，分别作为原来两张表的外键指向原来两张表的主键。

注：

1. 中间表不是一定只有两个列，根据实际需求可以添加其他列。
2. 中间表一般不单独建立主键，而是使用两个外键创建联合主键。

多表查询

连接查询

交叉连接

select * from user cross join category;
select * from user, category;

cross join：查询的结果为笛卡尔积。这种结果是所有组合关系的全排列，一般没有实际业务意义。

如果需要无中生有查询数据，可能会涉及到笛卡尔积。

大多数情况下，如果结果中有笛卡尔积的出现，意味着sql语句条件不完整，导致查询结果有错误。

内连接

查询的是多张表的交集部分。

语法：

1. 显式内连接

   明确的指定内连接的关键字编写sql

SELECT 列名列表 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件 [INNER] JOIN 表名3  ON  关联条件... ;

2. 隐式内连接

   把内连接的关键字省略掉编写sql

select 列名列表 from 表名1，表名2，...  where 关联条件1   and  关联条件2....

在关联查询时，如果多张表有明确的主外键关系，那么当关联查询时，首先要指定的就是主外键条件。否则会出现笛卡尔积。

外连接

查询的是多张表的交集部分及某一侧的全部数据。

左外连接、右外连接

此处的左和右是说的查询语句中外连接关键字两侧写的表名，写在左边的是左表，写在右边的是右表。左外连接的左表的数据是全的，右外连接的右表的数据是全的。左外连接和右外连接是可以互相转化的。

子查询（sql嵌套）

在sql语句中嵌套sql语句使用的编写方式。

sql是非过程性语言，每一句都是独立的。如果要使用其他sql执行的结果，只能使用sql嵌套。

sql嵌套本身也只是写了一句sql语句。

按照结果类型分类：

单行单列：把结果当成一个值使用即可。

多行单列：把结果当成一个单列集合使用，通常和in关键字一起使用。

多行多列：把结果当成一张表使用。

按照使用的位置分类：

where子句子查询：子句在where处，当成条件使用。

from子句子查询：把子句的结果当表使用。

select子句子查询：在select后的列表中嵌套sql，把查询的结果当条件使用。

-- select子句子查询的案例，以下两种写法效果相同
select id,number,(select name from user where id = uid) name from orderlist;
select o.id,o.number,u.name  from orderlist o left join user u on o.uid = u.id;

自关联

一张表中，通过某些字段自己跟自己表中的其他数据有关联关系。这种关联关系叫做自关联。

查询时，要把一张表看成多张表对待。一定要分清楚主从关系。

多表查询的套路：

1. 先明确所需要的数据来自于哪些表—确定from子句
2. 梳理这些表中是否包含主外键关系，如果有，先指定主外键。—where子句的主外键确定
3. 查看需求，分析是否有额外条件。— 确定where子句的其他条件
4. 筛选最终结果中我们关心的列—确定select子句的列表

视图

定义：在数据库中的一张虚拟表，一般使用比较复杂的sql查询语句生成。视图中仅存储了查询的sql语句，并不会存储真实数据。

作用：

1. 能够把一些非常常用的复杂sql缓存起来，供后期使用，简化开发流程。
2. 能够屏蔽一些不想公开的数据，仅提供想暴露的数据出去。
3. 一般会把视图做成只读视图，防止某些数据被修改。

语法

create view 视图名称 as  查询语句;

存储过程（了解）

sql本身是非过程性的语言，存储过程的语法就弥补了这项缺点。添加了过程性的语法元素。

存储过程没有返回值，但是可以通过输出参数模拟返回数据的效果。

输入参数

要求调用存储过程时，从外界传入到内部使用的参数。

输出参数

在调用存储过程时，指定该参数，参数可以在存储过程内进行赋值，然后携带到存储过程外。

输出参数指定时，一般使用会话变量，也就是开头时一个@符号的变量，这种变量在当前会话有效。

存储函数和存储过程的区别？

存储函数有返回值，而存储过程没有返回值。

存储函数可以在select子句中使用，类似于聚合函数。

触发器（了解）

定义：当关心的数据表中发生了增删改操作时，会自动触发触发器逻辑的执行。触发器可以在增删改之前或之后触发。

作用：数据完整性保证、日志记录、数据校验。

    CREATE TRIGGER 触发器名称
	BEFORE|AFTER INSERT|UPDATE|DELETE
	ON 表名
	FOR EACH ROW
	BEGIN
		触发器要执行的功能;
	END$

BEFORE|AFTER：触发器为前置触发还是后置触发。前置触发意味着操作还没有生效之前先执行触发器，后置触发是操作完成之后再执行触发器。

INSERT|UPDATE|DELETE：哪个操作会触发当前触发器。

FOR EACH ROW：是否行级触发。如果此参数指定，则代表是行级触发器。如果一条语句会影响多条记录，则触发器会给每一行记录触发一次。

在执行的代码块中，使用new和old关键字可以获取新数据和旧数据。new和old代表的就是一行记录，可以使用.属性的方式获取其中的值。

事务

要求：掌握事务的定义及意义，和事务中隔离级别导致的一些问题。

定义：在一个业务中，多条sql语句组合成一个整体，这个整体要同时成功或同时失败，此时我们需要使用事务对这一组sql操作进行管理。

手动管理事务

命令

start transaction; -- 开启事务.开启事务之后，在事务终止之前，所做的所有操作都是临时性的。
-- 执行一组sql操作
rollback;-- 回滚。把数据库的状态恢复到开启事务的时候的状态，就是还原。
commit;-- 提交。把在事务中操作的所有内容真正写入数据库文件持久化保存。
-- 如果在事务没有提交或者回滚之前，会话中止了，事务会自动中止，事务中的操作自动回滚。

一个事务只能被终止一次。

事务提交方式

手动提交

执行commit指令

自动提交

MySQL数据库默认就是自动提交，Oracle数据库默认是手动提交。

事务开启的时机

手动开启

start transaction;

自动开启

如果当前我们没有处在一个事务中，当我们执行任意一条sql语句时，MySQL会自动开启一个事务。

在默认的自动提交状态下，每执行一条sql语句，都会先开启一个事务，执行完毕后自动提交此事务。

如果要管理一组sql，让它们同时成功和失败，必须把它们放到同一个事务中。

事务的四大特征

原子性

强调事务的不可分割，是一个整体。

一致性

事务操作前后，数据库要保持一致性不变。

隔离性

在事务并发的时候，事务之间理论上不应该互相影响。

实际操作中，考虑到效率问题，我们一般会设置不同的隔离级别。控制事务并发之间的影响。

持久性

当一个事务结束时，它造成的影响就会持久化存储到数据库文件中。

隔离级别

指的是事务之间隔离的程度，隔离的级别越高，并发时事务之间的影响就越低，但是执行效率也会随之降低。

read uncommitted：未提交读。可能引发脏读、不可重复读、幻读/虚读。

read committed：已提交读。可能引发不可重复读、幻读/虚读。Oracle的默认隔离级别

repeatable read ：可重复读。可能引发幻读/虚读。MySQL的默认隔离级别

serializable：串行化。可以解决所有并发问题。

脏读：一个事务读取到了另一个事务未提交的数据。

不可重复读：一个事务读取到了另一个事务已经提交的修改。

幻读/虚读：一个事务读取到了另一个事务已经提交的新增或删除操作。

MySQL的引擎（了解）

mysql处理数据，包括存储、管理等各种技术的合体称之为引擎。

InnoDB：支持事务、支持外键、有表锁和行锁。

​ 占据的存储空间大。默认的引擎。

MyISAM：不支持事务，不支持外键，有表锁。

​ 效率高，业务简单的时候适用。

MEMORY：不支持事务，不支持外键，有表锁。数据存储在内存中，数据是临时的，断电丢失。一般用作缓存。

存储引擎是设置在表层级上的。

索引（重点）

使用特定的数据结构，把数据库中的数据进行重新排列存储。使用特定算法，可以在较短时间内稳定的获取我们预期的数据。

索引是建立在列上的。

索引的作用：提升查询数据的效率。

语法

create index 索引名称 on 表名(列的列表);

数据结构

B-Tree

每个磁盘块中保存了两个数据节点和三个指针。根据数据的区间，决定三个指针指向的后续磁盘块，分别为小于最小数据的，在两个数据之间的和大于最大数据的。根据算法可以减少2/3磁盘IO的次数。

缺点：每次读取磁盘块会额外读取出所有的数据，即使是用不上的数据。

B+Tree

非叶子节点中不存储数据，仅保持索引的key值及指针。通过算法最终找到叶子节点，才能获取到数据。这样就节省了遍历过程中获取数据的次数，从而提升效率。

叶子节点是按照链表的结构进行了排列，我们可以非常迅速的根据头和尾节点进行范围查询。

最左匹配原则

联合索引，匹配规则，是从最左侧的列进行匹配，如果有部分能匹配也会命中；但是如果最左侧列不能匹配，则不能命中索引。

mysql建索引的原则（mysql优化）

1. 查询多，增删改少的大表建索引。
2. 索引的列中的值越离散越好。
3. 索引一定要建在常作为查询条件的列上，而不要滥用。

锁（理解）

作用：在并发的情况下解决共享资源抢占冲突的问题。

InooDB的共享锁：

语法：select语句 lock in share mode;

特性

1. 共享锁加上之后，其他事务数据将只能读取，不能修改
2. 共享锁可以加多个，多个锁都释放掉之后才能修改
3. 默认情况下，索引列加锁时是行锁，非索引列加锁时是表锁。
4. 加锁操作在事务中进行，当事务结束后，自动释放当前事务中的共享锁。

InnoDB的排他锁：

语法：select语句 for update;

特性

1. 排他锁加上之后，其他事务数据将不能加锁进行查询，也不能修改。普通查询（不加锁的查询是可以的）

2. 排他锁加上之后，其他事务不能添加任意的锁。

3. 默认情况下，索引列加锁时是行锁，非索引列加锁时是表锁。

4. 加锁操作在事务中进行，当事务结束后，自动释放当前事务中的排他锁。

MyISAM的读锁（了解）

语法：lock table 表名 read;

特性：

MyISAM的锁是表级锁。

加上锁之后，所有连接都只能读取数据。

解锁：unlock tables；

MyISAM的写锁（了解）

语法：lock table 表名 write;

特性：

MyISAM的锁是表级锁。

加上锁之后，其他连接都不能进行读写操作。

解锁：unlock tables；

乐观锁及悲观锁

悲观锁是使用技术手段加锁的机制保证操作并发时的安全。

乐观锁不会加锁，而是使用逻辑手段尝试操作数据，如果真的在操作过程中因为其他线程操作导致数据发送改变，则进行重试直到成功为止。

如果增删改非常少，查询非常多，此时加锁冲突的概率不大，可以使用乐观锁减少系统资源的占用以提升性能。