MySQL

前言

一个小白的学习历程，用自己的的亲身经历为各位还未步入职场的朋友们先探探路。qvq

---

提示：这是MySQL篇

MySQL

一、MySQL概述

1.1数据库介绍

数据库：DataBase（DB），是存储和管理数据的仓库，本质就是一个存放数据的文件系统

数据库会按照特定的格式对数据进行存储，用户可以对数据库中的数据进行增加，修改，删除及查询操作

层次结构：

数据库管理系统 (DataBase Management System，DBMS) 指一种操作和管理数据库的大型软件。

数据库管理系统的层次：软件–>数据库–>数据表–>数据记录

1.2SQL介绍

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

SQL语句可以单行或多行书写，以分号结尾。

SQL语句可以使用空格/缩进来增强语句的可读性。

MySQL数据库的SQL语句不区分大小写。

注释：

单行注释：-- 注释内容 或 # 注释内容(MySQL特有)

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

SQL语句通常被分为五大类：

二、数据库操作

2.1数据库的启动

1. mysql -h 主机名 -P 端口 -u 用户名 -p密码
2. 登录前，保证服务启动

常见报错：

错误1: Access denied for user ‘root’@‘localhost’ (using password: YES)
表示你的用户名和密码不对

错误2: Can’t connect to MySQL server on ‘localhost:3306’ (10061)
表示数据库服务停止了

2.2数据库的操作

创建数据库：

create  database [ if  not  exists ]   数据库名 ;

查询所有数据库：

show databases;

删除数据库：

drop  database [ if exists ]   数据库名 ;

查询当前数据库：

select database();

切换数据库：

use  数据库名 ;

上述语法中的database，也可以替换成 schema。如：create schema db01;

备份数据库：(注意:在DOS执行)命令行

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n >文件名.sql

恢复数据库：(注意:进入Mysql命令行再执行)

Source 文件名.sql

三、数据表操作（单表）

3.1创建数据表

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确性、有效性和完整性。

create table  表名(
	字段1  字段类型(字段长度)  [ comment  字段1注释 ] ,     -- 不是;号
	......
	字段n  字段类型(字段长度)  [ comment  字段n注释 ]       -- 最后一列后面没有,号
) [ comment  表注释 ] ;

数值类型：

字符串类型：

日期类型：

3.2查询数据表

查询当前数据库所有表：show tables;
查询表结构：desc  表名;
查询建表语句：show create table 表名;

3.3修改数据表

添加字段：alter table 表名  add  字段名  类型(长度)  [comment  注释]  [约束];
修改字段类型：alter table 表名 modify  字段名  新数据类型(长度);
修改字段名和字段类型：alter table 表名 change  旧字段名  新字段名  类型 (长度)  [comment 注释]  [约束];
删除字段：alter table 表名 drop  column 字段名;
修改表名： rename table 表名 to  新表名;

3.4删除数据表

删除表：drop table [ if exists ] 表名;

在删除表时，表中的全部数据也会被删除。

四、数据的操作

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

4.1增加数据

指定字段添加数据：insert into 表名 (字段名1, 字段名2)  values (值1, 值2);
全部字段添加数据：insert into 表名 values (值1, 值2, ...);
批量添加数据（指定字段）：insert into 表名 (字段名1, 字段名2)  values (值1, 值2), (值1, 值2);
批量添加数据（全部字段）：insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

注意事项：

1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
2. 字符串和日期型数据应该包含在引号中。
3. 插入的数据大小，应该在字段的规定范围内。

4.2修改数据

修改数据：update  表名  set  字段名1 = 值1 , 字段名2 = 值2 , .... [ where  条件 ] ;

注意事项：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

4.3删除数据

删除数据：delete  from  表名  [ where  条件 ];

注意事项：

1. DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
2. DELETE 语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将该字段的值置为NULL)。

4.4查询数据

DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。

查询数据（SELECT）

4.4.1基本查询

查询多个字段：select  字段1, 字段2, 字段3  from   表名;
查询所有字段（通配符）：select  *  from   表名;
设置别名：select  字段1  [ as  别名1 ] , 字段2  [ as  别名2 ]   from   表名;
去除重复记录：select  distinct  字段列表  from   表名;

注意事项：* 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

4.4.2条件查询

条件查询：select  字段列表  from   表名   where   条件列表 ;

4.4.3聚合函数

介绍：将一列数据作为一个整体，进行纵向计算。
语法：select  聚合函数(字段列表)  from   表名 ;

注意事项：

1. null值不参与所有聚合函数运算。
2. 统计数量可以使用：count() count(字段) count(常量)，推荐使用count()。

4.4.4分组过滤

分组查询：select  字段列表  from   表名  [ where   条件 ]  group   by  分组字段名  [ having  分组后过滤条件 ];

where与having区别:

1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
2. 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项：

1. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
2. 执行顺序: where > 聚合函数 > having 。

4.4.5排序查询

条件查询：select  字段列表  from   表名   [ where   条件列表 ] [ group by  分组字段 ] order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

排序方式：

ASC：升序（默认值）

DESC：降序

注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

4.4.6分页查询

分页查询：select  字段列表  from   表名  limit  起始索引, 查询记录数 ;

注意事项：

1. 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
3. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

五、数据表操作（多表）

5.1表间关系

项目开发中，因为业务之间相互关联，所以实体与实体之间存在联系，故而表跟表之间也存在着各种联系。

我们把这种能够表示表间关系的数据库称为关系型数据库（RDBMS）,在数据库设计上，表关系分为三种：

1. 一对多（多对一）
2. 多对多
3. 一对一

5.1.2一对多

在表设计时，我们把一的一方称为主表，多的一方称为从表。

在从表中添加一列指向主表的主键列，新加列名字一般是（主表名_主键名）。

将新建的这一列称为外键列，它的作用是为了建立当前表和其他表的关联关系，一张表中，允许有多个外键列。

外键约束：目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

-- 创建表时指定
create table 表名(
	字段名    数据类型,
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (字段名)	
);
-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key (外键字段名) references  主表(字段名);

物理外键：

概念：使用 foreign key 定义外键关联另外一张表。

缺点：

1. 影响增、删、改的效率（需要检查外键关系）。
2. 仅用于单节点数据库，不适用与分布式、集群场景。
3. 容易引发数据库的死锁问题，消耗性能。

逻辑外键：

概念：在业务层逻辑中，解决外键关联

通过逻辑外键，就可以很方便的解决上述问题

5.1.3多对多

新建一张中间表，作为两张主表的从表存在。

在中间表中创建两个外键，分别指向两张主表的主键。

多对多一个关系本质上是由两个一对多拼接而成。

-- 创建表时指定
create table 表名(
	字段名    数据类型,
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (字段名)	
);

5.1.4一对一

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)。

5.2查询

5.2.1介绍

多表查询：

在企业开发中，我们一个业务需要的数据往往是来自多张表的，所以这时候就需要多表联合查询。

所谓的多表联合查询就是使用一条SQL语句将多张表的数据一起查询展示出来。

数据准备：将资料中准备好的多表查询数据准备的SQL脚本导入数据库中。

5.2.2交叉连接（笛卡尔积）

解释: 使用左表中的每一条数据分别去连接右表中的每一条数据, 将所有的连接结果都展示出来。

--语法
select  字段列表  from 左表,右表

5.2.3内连接

解释: 使用左表中的每一条数据分别去连接右表中的每一条数据, 仅仅显示出匹配成功的那部分。

语法:

--隐式内连接
select  字段列表   from   左表 , 右表   where   条件 ...
--显示内连接
select  字段列表   from   左表  [ inner ]  join 右表  on  连接条件 ... 

区别：

1. 语法:隐式内连接使用where 条件进行连接,显示内连接使用inner join on 进行连接。
2. 执行顺序：隐式内连接会先做笛卡尔积,再过滤不匹配的行.显示内连接先匹配两表的连接条件,然后返回匹配的行。
3. 性能：数据量大的情况下,显示内连接的性能更好。

5.2.4外连接

解释: 首先要显示出左表的全部，然后使用连接条件匹配右表，能匹配中的就显示，匹配不中的显示为null。
解释: 首先要显示出左表的全部，然后使用连接条件匹配右表，能匹配中的就显示，匹配不中的显示为null。

--语法（左外连接）
select  字段列表   from   左表  left  [ outer ]  join 右表  on 连接条件
--语法（右外连接）
select  字段列表   from   左表  left  [ outer ]  join 右表  on 连接条件

5.2.5子查询

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

--形式
select  *  from   t1   where  column1 =  ( select  column1  from  t2  … );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

分类：

标量子查询：

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式。

常用的操作符：= > < 等。

select 字段列表 from 表 where 字段名 = (子查询)

列子查询：

子查询返回的结果是一列（可以是多行）。

常用的操作符：in 、not in等。

select 字段列表 from 表 where 字段名 in (子查询)

表子查询：

子查询返回的结果是多行多列，常作为临时表。

常用的操作符：as 临时表。

select 字段列表 from (子查询) as 临时表 join 表 on 条件

六、事务

6.1介绍

事务 是一组操作的集合，它是一个不可分割的工作单位。

事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作 要么同时成功，要么同时失败。

注意事项：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

操作：

开启事务：start transaction; / begin ;

提交事务：commit;

回滚事务：rollback;

6.2特性

四大特性：

原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败。

一致性：一个事务执行前后，数据库的状态是一致的。

隔离性：当多个事务同时执行的时候，互相是不会产生影响的。

持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

事务隔离性带来的问题：

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

不可重复读：一个事务读取到了另外一个事务修改的数据。

幻读：一个事务读取到了另外一个事务新增的数据。

事务隔离级别：