MySQL学习笔记

目录

一、数据库介绍

二、sql语句

1、DDL语句

1.1 数据库层面

1.2 表层面

2、DML语句

2.1 添加数据（插入数据）

2.2 删除数据

2.3 修改数据

 3、DQL语句

3.1 条件查询

3.2 模糊查询

3.3 聚合函数

3.4 分组查询

3.5 分页查询

三、多表的设计和查询

1、外键约束

2、 多表设计

2.1 为什么要设计多表？

2.2 多表设计的三种形式

3、多表查询

3.1 多表查询简介

3.2 连接查询 

3.3 子查询

四、事务

1、什么是事务？

2、事务的基本使用

3、事务的四大特性

五、索引

1、什么是索引？

2、有关索引的三种基本操作

2.1 创建索引

2.2 显示索引

2.3 删除索引

2.4 有关创建索引的注意点

3、索引的优缺点

4、结构

---

一、数据库介绍

数据库是一个用于存储和管理数据的仓库。数据按照特定的格式存储，可以对数据库中的数据进行增加、修改、删除和查询操作。数据库的本质是一个文件系统，按照一定的逻辑结构组织数据，以方便高效地访问和维护。而MySQL是一种较为常见的数据库，较为常见的数据库还有Oracle、DB2、SQLServer、SQLite等。

数据库管理系统（DataBase Management System，DBMS）是一个操作和管理数据库的软件。它用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

二、sql语句

学习sql语句之前，我们先了解一下约束。

 约束就是对数据的一种限制条件，目的是保证表中的记录完整和有效。主要有以下几种：

• 非空约束(not null)：数据值不能为null
• 唯一性约束(unique)：数据值只能是唯一存在的，不能有相同的数据
• 主键约束(primary key) PK：一张表必须有一个主键约束，当前行的数据是非空且唯一
• 外键约束(foreign key) FK：关联其他表的主键，用于多表设计

1、DDL语句

DDL是数据定义语言，用来定义数据库对象：库、表、列等，主要是对表结构的增删改操作，我们可以将其大致分为两个层面，一个是在数据库中进行操作，一个是在表层面进行操作。

1.1 数据库层面

DDL语句在数据库中主要参与数据库的创建和删除操作。

• 创建数据库：

语法：

create database 数据库名；

代码：

-- 创建一个名为db的数据库
create database db;

• 删除数据库：

语法：

 drop database 数据库名；

代码：

-- 删除一个名为db的数据库
drop database db;

常用的数据库层面的DDL语句就这两种，但目前我们创建数据库几乎都是在图形化界面上进行创建，很少有自己手写创建数据库语句了。

下面看idea中数据库的快捷创建方式：

1.2 表层面

DDL语句对表的创建也就增删改查四种操作，其中改的操作比较多。

创建表结构

语法：

create table 表名
(
字段1名 数据类型 [约束] [comment '字段注释'],
.........
) [comment '表注释'];

 代码：

create table tb_dept
(
    id          int unsigned primary key auto_increment comment '主键ID',
    name        varchar(10) not null unique comment '部门名称',
    create_time datetime    not null comment '创建时间',
    update_time datetime    not null comment '修改时间'
) comment '部门表';

 跟创建数据库一样，表的创建在图形化界面上同样也有快捷创建方式。

删除表

语法：

drop table 表名;

 代码：

-- 删除book表
drop table book;

查看表

语法：

desc 表名;

 代码：

desc student;

 修改表

• 添加字段：

语法：

alter table 表名 add (字段名 字段类型);

代码：

-- 添加字段
alter table student add (sex char(1));

•  修改字段的数据类型

语法： 

alter table 表名 modify 要修改的字段名 修改之后的数据类型;

代码：

-- 修改字段类型
alter table student modify sex int;

•  修改字段名

语法：

alter table 表名 change 修改之前的字段名 修改之后的字段名 修改之后的数据类型;

 代码：

-- 将sex改成gender
alter table student change sex gender char(1);

•  删除字段

语法：

alter table 表名 drop 需要删除的字段名;

 代码：

alter table 表名 change 修改之前的字段名 修改之后的字段名 修改之后的数据类型;

•  修改表名

语法：

alter table 原表名 rename to 新表名;

 代码：

-- 修改表名称
alter table student rename to student1;

2、DML语句

DML语句是数据操作语句，主要负责对数据的增删改操作，下面对这三种数据操作进行介绍

2.1 添加数据（插入数据）

语法：
插入单条指定字段数据：

insert into 表名(name,id，字段1，字段2,......) values ('张三丰',6,.......);

插入单条所有字段数据：

insert into 表名 values (所有字段数据);

插入多条数据：

insert into 表名 values (字段数据1)，（字段数据2），（字段数据3）......;

需要插入多条数据的话直接在values关键字后用逗号继续添加括号即可。

代码：

-- 插入单条指定字段数据
insert into student (name,id) values ('张三丰',6);
-- 插入单挑所有字段数据
insert into student values ('lisi',7,19,90,'男');
-- 插入多条所有字段数据
insert into student values ('lisi',7,19,90,'男'),('wangwu',8,20,91,'女');

2.2 删除数据

语法：

delete from 表名 where 条件;

代码：

-- 删除数据
delete from student where id = 1;

这里是删除符合where里面条件的数据。

2.3 修改数据

修改数据可也可以理解为更新某一条数据，常与where条件进行配合使用

语法：

update 表名 set 字段1 = 数据1,字段2 = 数据2 where 条件;

 代码：

-- 更新数据
update student
set name = '灭绝师太',sex = '男'
where id = 2;

 3、DQL语句

DQL语句是数据查询语句，在数据库的操作当中，数据的查询占极大部分，故DQL语句的重要程度也是最高的。

3.1 条件查询

这部分比较简单，就是简单的查询符合where条件的数据。

语法：

select 查询后显示的字段 from 表名 where 条件;

 代码：

-- 查询id=6的所有字段信息
select *
from student
where id = 6;

这里的代码用了*号来代替查询的字段，*号代表的是所有字段，这里的运行结果是显示所有的字段信息。

3.2 模糊查询

在认识模糊查询之前，我们需要先了解两个字符。

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符。

_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字 符长度语句。

模糊查询就是利用like关键字搭配这两个字符进行操作。

代码：

-- 模糊查询 查询姓张的并且名字是三个字的学生信息
select *
from student
where name like '张__';

-- 查询姓名中含有张字的学生信息
select *
from student
where name like '%张%'

3.3 聚合函数

要学习剩下的几种查询，要先了解一下聚合函数，聚合函数是用来做纵向运算的函数，主要有一下几种聚合函数：

• COUNT()：统计指定列不为 NULL 的记录行数；
• MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0；
• AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0；

 代码：

-- 查询表中的数据条数
select COUNT(*)
from student;

-- 查询表中的最高分
select max(score)
from student;

-- 查询表中最低分
select min(score)
from student;

-- 查询平均分
select avg(score)
from student;

-- 查询年龄之和
select sum(age)
from student;

3.4 分组查询

分组查询是通过group by语句对表中数据进行分组，然后对分组之后的数据进行查询，常常配合着聚合函数进行查询。

语法：

select 字段1,字段2....
from 表名
group by 字段1;

代码：

-- 分组查询 查询每个部门中入职日期最早的日期
select dept_id,min(entrydate)
from tb_emp
group by dept_id;

 在分组查询当中，我们会需要在分组之前和分组之后对数据进行过滤（也就是判断是否符合条件），这时就需要使用having关键字了，having关键字是对分组之后的数据进行过滤，而where是对分组之前的数据进行过滤，如果某条数据不符合where中的数据，则不会参与分组，两者除了功能是一样以外，还是存在着很多的不同，另外，having可以配合着聚合函数使用，而where不能。

-- 查询每个部门男性入职日期晚于2001-01-01的员工
select dept_id
from tb_emp
where gender = 1
group by dept_id
having dept_id is not null;

3.5 分页查询

分页查询用到的关键字是limit，对查询的数据进行分页展示。

语法：

select *
from tb_emp
limit 起始索引,每页展示记录数;

代码：

-- 每页展示五条语句
select *
from tb_emp
limit 0,5;

 当起记录数量多的时候，我们很难用肉眼观察到每页需要展示的记录数，这里提供了一个计算公式用于计算起始索引：

起始索引 = （查询页码 - 1）*每页展示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，如果查询的是第一页的数据，起始索引可以省略，直接简写成limit 10。

三、多表的设计和查询

1、外键约束

在学习多表查询的之间，我们需要先了解一下外键约束，外键约束就是在一张表当中的某个字段上设置一个约束，这个约束需要关联另外一张表中的主键，使这两个表之间建立联系。建立了外键约束之后，当主键存在的表中的某一条数据被删除了，与之关联的外键的那些数据也会跟随删除，举个例子，部门表中的id字段和员工表中的emp_id字段有主外键关联，当部门表中id为1的那条数据被删除了，员工表中emp_id为1的数据也会随之删除。这就使数据的在物理层面建立起了一种联系。

下面介绍如何给字段添加外键约束：

 

 

 

 外键分为物理外键和逻辑外键，下面对这两种外键进行一下分析说明

物理外键：

物理外键是指通过对表进行物理层面上面的一系列设置，使两张表建立起联系，上方介绍的就是一种物理外键，物理外键具有以下缺点：

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

逻辑外键：

由于物理外键存在的一系列问题，我们引出了逻辑外键， 逻辑外键很好理解，就是在业务逻辑层面给字段添加一个逻辑外键，不需要进行上述的一系列操作，这样可以很好的解决物理外键存在的问题，目前很多大厂已经明文规定不允许使用物理外键。

2、 多表设计

2.1 为什么要设计多表？

多表设计是为了消除数据库中的冗余数据，当多个表中同时出现具有相同意义的字段，那这个字段就是冗余字段，这样会消耗掉宝贵的资源，于是多表设计便产生了，我们会将一个表中的字段拆分成多个表，或者将多个表中重复的字段提取到一个表当中，这就是多表设计。

2.2 多表设计的三种形式

多表设计有三种情形，一对一，一对多，多对多，下面详细介绍这三种情形。

一对一：

一对一很好理解，在某些表当中，会存在很多个字段，而有一些字段信息是较为少用的，我们便可以将这两张表拆分成两张表，通过设置某个字段使两表建立起联系。举个最简单的例子，用户信息表和身份证信息表，在这个表结构当中，身份证信息会在某些场景中会较为少用，于是我们便可以将这两张表拆分成两张表，一个用户信息表，一个身份证信息表。

 上方三张图片便是将一张表拆分成两张表结构的示例图，我们将第一张用户表表拆分成两张表，一张是用户基本信息表，一张是用户身份信息表，以提高操作效率，在这两个表当中，我们需要将用户基本信息表中的id设为主键，在身份信息表中的id字段中设置为用户基本信息表中的id字段的外键，建立起关联关系。

一对多：

还是举个例子，部门表和员工表，部门表中的一个部门对应着员工表中的多个员工，我们可以在员工表中设计一个部门id字段与部门表中的id字段产生一个关联。使两表直接产生一种联系，这就是一对多的多表设计。

 

 

 上方给出的两张表便是我们所说的部门表和员工信息表（第一张为员工信息表，第二张为部门表），在员工信息表中我们设置了一个dept_id字段，它代表着这个员工所归属部门id，我们在部门表中有一个id字段，这个字段设置为主键，员工表中的emp_id字段与部门表的id相对应，设置成关联外键，根据员工表中的emp_id我们可以在部门表中找到相对于的部门，同样，我们在部门表中通过一个id可以在员工表中找到属于这个部门的所有员工信息。需要注意：一对多指的是一张表中的一个字段在另一张表中对应着多个字段，是字段与字段之间的对应关系。

多对多：

 多对多的表结构是较为复杂的一种多表设计，多对多是指在两张表当中，双方的某一个字段与其中另外一个表中的多个字段有联系，这样讲比较生硬，我们还是通过例子来解释。比如说学生表和课程表，一个学生可能有多个课程与之对应，一个课程又有多个学生与之对应，这我们就需要设计一个多对多的表结构。我们可以在两个表中间设计一个中间表来实现两表之间的对应关系。下面看设计图。

 在这个表设计中，我们借助了一个学生课程关系表来建立学生表和课程表之间的关系，在这个中间表中我们设置了两个外键来分别关联两张表的主键，studentid关联着学生表的id，courseid关联着课程表的id，通过这样的一张中间表，我们就可以将两张表之间的联系安排的清清楚楚，这就是多对多的多表设计。 

3、多表查询

3.1 多表查询简介

多表查询就是在多张表中查询数据，代码示例如下：

/*多表查询*/
select * from tb_emp,tb_dept;

 这是不加条件下的多表查询，它所查询的结果是两张表数据的笛卡尔积，数据很混乱，我们一般会在后面用where加入某个条件，例如主键与外键的关系使其展现我们想要的结果。

/*多表查询*/
select * from tb_emp,tb_dept where dept_id = tb_dept.id;

  

3.2 连接查询 

连接查询又分为内连接查询和外连接查询，而外连接查询又分为左外连接查询和右外连接查询。下面看示例图，根据示例图分别进行讲解。

 内连接查询：

查询的范围是A与B之间的交集部分，就是只显示符合where里面的条件的内容。

语法：

select 字段1... from 表1 inner join 表2 on 条件语句;

/*内连接查询
  隐式内连接*/
select 员工姓名.name,部门.name 
from tb_emp 员工姓名,tb_dept 部门 
where dept_id = 部门.id;
/*显示内连接*/
select tb_emp.name,tb_dept.name 
from tb_emp inner join tb_dept 
on dept_id = tb_emp.id;

 内连接有显式内连接和隐式内连接两张写法，上方代码都有介绍。

外连接查询：

外连接查询就是只查询A或B，包括A与B的交集部分，也就是说查询的结果为符合where中条件的数据和A或者B中的不符合where中的数据。

语法：

select 字段1... from 表1 left/right join 表2 on 条件语句;

/*外连接查询
  左右外连接查询可以通过更换两张表的位置来实现相同的查询效果
  */
/*左外连接查询*/
select tb_emp.name,tb_dept.name 
from tb_emp left join tb_dept 
on tb_emp.dept_id = tb_dept.id;

/*右外连接查询*/
select tb_emp.name,tb_dept.name 
from tb_emp right join tb_dept 
on tb_emp.dept_id = tb_dept.id;

左右外连接查询语法都一样，这里就不在分别讲解了，这种外查询可以理解成将某一张表的所有数据都查询出来，假如在另外一张表当中有符合条件的那就显示对应的数据，没有符合条件的则显示为null。

3.3 子查询

子查询可以理解成查询套娃，就是查询里面套查询。子查询有标量子查询、行子查询、列子查询、表子查询，每一种子查询都有各种的用法，下面对这几种查询进行一一介绍。

标量子查询：

标量子查询就是先通过一条查询语句将某个值查询出来，然后将这个值作为另一个查询语句的条件值再次进行查询。下面通过一个案例进行解释。

假设我要查询学工部的所有员工信息，按照往常的方法，我要分为两步，先查询出学工部的部门id，在根据这个id作为查询条件在员工表中进行查询。我们可以利用子查询，将这两条查询语句合并成一条查询语句。代码如下：

/*查询某个部门的所有员工信息*/
-- 先把部门id查询出来 --
select id from tb_dept where name = '学工部';
-- 再根据这个id将所有员工信息查询出来 --
select name from tb_emp where dept_id = 1;
-- 利用子查询 --
select name 
from tb_emp 
where dept_id = (select id from tb_dept where name = '学工部');

 子查询返回的结果是一个单个值，是最简单的一种查询方式，一般和 < > = 等比较运算符搭配使用。

列子查询：

列子查询可以理解成标量子查询中返回多个值，这多个值都是同一字段，是一列数据，这多个值参与另一条查询语句的where条件。下面看案例。

假设我们要查询学工部和教研部两个部门的所有员工姓名，我们要先查出这两个部门的部门编号，然后在员工表中利用in关键字进行判断员工是否属于这两个部门。代码如下：

/*查询某两个部门的所有员工姓名*/
-- 先查询部门的id --
select id from tb_dept where name = '学工部' or name = '教研部';
-- 再根据返回的id查询员工姓名，返回的是两个id，是两行数据 --
select name,dept_id from tb_emp where dept_id in(1,2);
-- 使用列子查询 --
select name,dept_id 
from tb_emp 
where dept_id in(select id from tb_dept where name = '学工部' or name = '教研部');

列子查询一般返回的是多个数据，是一列数据，一般常与in，not in搭配使用。

行子查询：

行子查询可以根据列子查询和标量子查询进行理解，行子查询像是列子查询和标量子查询的合并（仅代表个人理解，感觉理解有误可以直接忽略），行子查询是通过查询多个字段的值，利用这些值参与其他的查询语句。下面看案例。

假如我要查询和韦一笑入职日期和工作一样的员工信息，我要先查询姓名为韦一笑的工作和入职日期，再去查询与这个结果相同的员工信息。代码如下：

  /*查询和韦一笑入职日期和工作一样的员工信息*/
-- 先查询韦一笑的入职日期和岗位
select entrydate,job from tb_emp where name = '韦一笑';
-- 再查询符合此查询结果的所有员工
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
-- 使用行子查询
select *
from tb_emp
where entrydate = (select entrydate from tb_emp where name = '韦一笑')
and job = (select job from tb_emp where name = '韦一笑');
-- 优化一下代码
select *
from tb_emp
where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');

行子查询更像是多个标量子查询参与，将多个查询的结果参与到新的查询语句当中，代码可以优化一下，（字段1，字段2） = （子查询1，子查询2）。子查询返回的是一行，可以是多列，一般和 = < > in，not in 搭配使用

表子查询：

表子查询很好理解，就是先根据某些条件查询出一张表，再将这张表作为临时表参与下一张表的查询。直接看案例。

假如我要查询入职日期在2008-01-01之后的员工的部门名称，我可以先查询出入职日期在2008-01-01之后的员工信息，然后将这张员工信息表作为临时表再次查询部门名称。代码如下：

/*查询入职日期在2008-01-01之后的员工的部门名称*/
-- 先查询入职日期在2008-01-01之后的员工信息
select * from tb_emp where entrydate > '2008-01-01';
-- 利用表子查询将这张表作为临时表查询员工的部门信息
select ta.*, tb_dept.name 
from (select * from tb_emp where entrydate > '2008-01-01') ta,tb_dept 
where ta.dept_id = tb_dept.id;

以上便是子查询的所有内容，这几种子查询其实都可以看成是查询语句的嵌套。就是无限的套娃。

四、事务

1、什么是事务？

一句话，要么都成功，要么都失败。

事务是多条sql语句所组成的一个最小单元，在这个最小的单元里面，所有的sql语句都是相互依赖的，里面的所有语句只能全都执行成功，或者全部都不执行，当这个单元里面有一条语句不能够执行，那么所有的语句都不能够执行，都将回滚到最初未执行的状态。一般运用在互相关联的多张表的操作当中，例如部门表和员工表，加入某个部门要解散，则部门表中的这个部门就要删除，员工表中属于这个部门的员工信息也要删除，这两个操作就是必须同时发生或者都不发生的操作。

2、事务的基本使用

基本语法：

start transaction/begin;

sql语句1；

sql语句2；

sql语句3；

......

commit;

rollback;

一个事务先通过start transaction或者begin开启事务，然后执行里面所包含的sql语句，当所有的sql语句都没有问题之后，将通过commit关键字提交事务，如果其中有任何一条sql语句执行失败，将通过rollback回滚到最初执行的状态（没有执行的不再执行，已经执行的撤销执行）

案例：

/*事务
  多个操作的集合，这个集合是一个单元，里面的操作要么全都发生，要么全都失败*/
-- start transaction;
begin;
delete
from tb_dept
where id = 3;

delete
from tb_emp
where dept_id = 3;

-- 当所有操作都能正确执行时，才会提交事务
commit;

-- 当其中出现某条操作不能执行时，会回滚事务，其它所有操作都不会再继续执行
rollback;

3、事务的四大特性

• 原子性：

意思就是，一个事务好比一个原子，已经是最小的一个单元了，不可以对这个单位再进行拆分，每一个原子都只有发生和不发生两种操作。

• 一致性：

数据库在经过事务之后，前后的状态必须保持一致，举个例子：有甲乙丙三个人，这三个人的总余额为100元，甲给乙转账20元，乙给丙转账30元，他们的总余额必须还是100，转账的前后总余额必须保持不变，换一种说法，我们只能看到转账前的余额状态或者转账后的余额状态，而不能看到甲给乙转，甲的余额少了但乙的余额没加的状态。

• 隔离性：

每一个事务的执行都是独立的、隔离的，当一个事务正在执行的时候里面的数据是不能被并发的其他事务干扰到，并发执行的事务互不干扰。

• 持久性：

一个事务一旦提交成功，它对数据的处理应该是永久的，不会被其他的操作或故障影响。

五、索引

1、什么是索引？

索引是数据库当中用于提高查询效率的一种数据结构，给一个表中的一个字段添加一个索引实质上是给这个字段添加一个查询效率高的数据结构。

就好比一本书，索引就相当于这本书当中的目录页码，假如我们没有这个目录页码，我们需要在这本书中找到我们想要的地方将会花费较长的时候，当我们有了目录页码，我们就可以根据这个目录页码查询我们所要找的位置，这将大大的提高我们的查找效率，索引亦是如此。

2、有关索引的三种基本操作

2.1 创建索引

语法：

create index 索引名 on 表名(字段名);

案例：在tb_emp这张表中给name字段创建名为idx_emp_name的索引

-- 创建索引
create index idx_emp_name on tb_emp(name);

2.2 显示索引

语法：

show index from 表名;

案例：查询tb_emp表中的所有索引

-- 查询索引 当创建字段的时候如果添加了唯一约束和主键约束的话，会自动的创建唯一索引和主键索引
show index from tb_emp;

2.3 删除索引

语法：

drop index 索引名 on 表名;

案例：

-- 删除索引
drop index idx_emp_name on tb_emp;

2.4 有关创建索引的注意点

当我们在创建一张表格的时候，如果某个字段添加了unique关键字使用了唯一约束，那在创建表格的时候会自动的给这个字段添加一个唯一索引，当某个字段使用主键约束那在创建表格的时候会自动创建一个主键索引，这两种索引类型都是比较常见的索引类型。

3、索引的优缺点

优点：

（1）可以大大缩短数据的检索速度，提高数据库的IO成本

（2）可以提高根据添加索引的字段的排序效率，添加索引时，被添加的字段会进行自动排序，当使用order by 进行排序的时候可以提高效率

缺点：

（1）添加索引是占用一部分内存的，造成内存消耗

（2）添加索引虽然提高了查找的效率，但是同时也降低了更新、插入、删除这三种种操作的效率，但是对于数据库而言，大多数的操作都是查询操作，总的来说还是利大于弊。

4、结构

索引的结构一般是B+树结构，这里就不多介绍，详细可以参考下方链接： 

     

索引详细介绍