【MySQL篇】 MySQL基础学习

前言

在做项目的过程中，深深感悟到一个扎实的MySQL基础是多么重要，这篇文章就用来记录一下我对于MySQL的复习，下面开始介绍了。

MySQL 是一款开源免费的数据库。后端程序员的工作离不开与数据库打交道，MySQL因其简单易懂、使用方便的特性深受Java程序员喜爱，这篇文章就让我们一起来了解MySQL。

此外，在这里我非常推荐大家可以去看看鱼皮大佬的MySQL练习网站SQL之母 - SQL自学网站 (yupi.icu)，大家可以在上面检验一下自己编写sql语句的水平。

基础

数据类型

数值类型：

字符串类型：

日期类型：

DDL数据库操作

查询数据库

查询所有数据库

show databases;
1

查询当前数据库

select database();
1

创建数据库

create database 数据库名;
1

如果不存在该数据库则创建，存在则创建失败：

create database if not exists 数据库名;
1

删除数据库

drop database 数据库名;
1

如果存在该数据库则删除，不存在则删除失败：

drop database if exists 数据库名;
1

使用数据库

对某个数据库操作（进入该数据库来操作数据库中的表）

use 数据库名;
1

DDL表操作

记住一定要先进入该数据库，才能对该数据库的表进行操作。

创建表

create table 表名(
    data1 数据类型 可接comment '注释'表示对data注释,
    data2 数据类型,
    data3 数据类型
) comment '注释';
1
2
3
4
5

如下表：表示创建一个包含姓名、年龄、爱好这三个字段的用户表

create table user(
	name varchar(20) comment '姓名',
    age int comment '年龄',
    gender varchar(100) comment '爱好'
)comment '用户表';
1
2
3
4
5

查询表

1.查询当前库的所有表

show tables;
1

2.查询表结构

desc 表名;
1

3. 查询指定表的建表结构

show create table 表名;
1

修改表

1.添加字段

alter table 表名 add 字段名 数据类型;
1

2.修改字段

• 修改数据类型

alter table 表名 modify 字段名 新数据类型;
1

• 修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型;
1

3.修改表名

alter table 表名 rename to 新表名;
1

删除

1.删除字段

alter table 表名 drop 字段名;
1

2.删除表

drop table 表名;
1

3.删除并重新创建该表

truncate table 表名;
1

DML-增删改

添加数据

1.给指定字段添加数据

insert into 表名 (字段1、字段2，...) values (值1，值2，...);
1

2.给全部字段添加数据

insert into 表名 values (值1，值2，...);
1

3.批量添加数据

insert into 表名(字段1、字段2，...) values (值1，值2，...),(值1，值2，...),...;
1

insert into 表名 values (值1，值2，...),(值1，值2，...);
1

更改数据

这里大致列出基本语法，更新时主要是根据where条件来更新（后面会讲）

1.更新所有数据

update 表名 set 字段1=要更改成的值;
1

2.更新部分数据

update 表名 set 字段1=要更改成的值 where 字段2=某值;
1

删除数据

1.删除所有数据

delete from 表名;
1

2.删除部分数据

delete from 表名 where 字段1=某值;
1

DQL-查询

基础查询

*表示所有字段

1.查询多个字段

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

select * from 表名;
1

2.设置别名（在我们的sql语句比较复杂时设置别名可以让我们的sql语句更加简洁清晰）

select 字段1 as '别名' from 表名;
1

3.去除重复记录

select distinct 字段 from 表名;
1

条件查询

select 字段列表 from 表名 where 条件;
1

%表示任意长度的任意字符序列。_表示单个字符。

例如我们要查询一张表中名字中不带李的其他所有人的信息：

select * from 表名 where name not like '%李%';
1

聚合函数

语法：

select 聚合函数(字段) from 表名;
1

例子如下：

• 统计user表中未成年的数量（每条数据都有一个id字段用来确定唯一性）

select count(id) from user where age<18;
1

• 查询user表中年龄最大的人

select max(age) from user;
1

• 查询user表的平均年龄

select avg(age) from user;
1

• 统计所有来自南昌的员工的年龄之和

select sum(age) from employee where home='南昌';
1

注意：null不参与计算。

分组查询

group by是分组查询的关键字，它可以将表中的数据进行分组，再进行查询等操作。换言之，可通俗地理解为：通过group by将原来的表拆分成了几张小表。例如我们可以根据性别将一张大表分成男性表和女性表两个小表。

select 字段 from 表名 where 条件 group by 分组字段名 having 分组后的过滤条件;
1

可以结合以下例子进行理解：

• 在emp表中，根据性别分组，统计男性员工和女性员工的数量

select gender,count(*) from emp group by gender;
1

• 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址。

select workplace,count(*) from emp where age<45 group by workplace having count(*)>=3;
1

where与having的区别：

• where在分组前过滤，having在分组后过滤

• where不能对聚合函数进行判断，having可以

总结：

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

排序查询

select 字段 from 表名 order by 字段1 排序方式，字段2 排序方式;
1

asc升序（默认） desc（降序）

• 例如：根据创建时间（create_time）降序查询

select * from user order by create_time desc;
1

注意：如果是多字段排序，第一个字段相同时，才会继续根据第二个字段排序。

分页查询

select 字段 from 表名 limit 起始索引,查询记录数;
1

注意：*起始索引从0开始，起始索引=（查询页码-1）每页显示记录数。

• 查询第1页数据，每页10条数据

select * from 表名 limit 0,10;
1

• 查询第2页数据，每页10条数据

select * from 表名 limit 10,10;
1

编写顺序

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数;
1

DML-用户及权限

用户管理

1.查询用户

use mysql;
select * from user;
1
2

2.创建用户

create user '用户名'@'主机名' identified by '密码';
1

3.修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
1

4.删除用户

drop user '用户名'@'主机名';
1

权限控制

常见权限：

1.查询权限

show grants for '用户名'@'主机名';
1

2.授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
1

3.撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
1

注意：1.多个权限中用逗号分隔。2.数据库名和表名都可以用*表示，代表所有。

函数

不知道你是否还记得上面我们讲到过聚合函数，这里是其他的函数，具体用法就先不一一介绍了，混个眼熟后续再细说。

字符串函数

数值函数

日期函数

流程函数

约束

概述

约束是作用于表中字段上的规则，用于限制存储在表中的数据。其可以保证数据库中数据的正确性、有效性和完整性。

注意：我们可以在创建表的时候添加约束，也可以在修改表的时候添加约束。

实例：

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check (age>0&&age<=120) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
)comment '用户表';
1
2
3
4
5
6
7

外键约束

概述：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

如上图所示，按理来说，当我们删除部门表中的某一个部门时，因为其关联了员工表中的数据，所以不能直接删除。但由于这两张表并未建立外键关联，所以删除部门表数据时，数据就直接被删了。

添加外键的方法有两种：

• 创建表的时候添加

• 表结构创建好了再额外添加

alter table 表名 add constraint 外键名称 foreign key(要关联的字段) references 主表(关联的字段);
1

例如以下表示：把emp表的emp_id与boss表的boss_id添加外键，并取外键名称为guanlian

alter table emp add constraint guanlian foreign key (emp_id) references boss (boss_id);
1

2.删除外键

alter table 表名 drop foreign key 外键名称;
1

例如将上面创建的外键删除：

alter table emp drop foreign key guanlian;
1

外键更新、删除时的操作

给外键被更新或删除时设置操作：

alter table 表名 add constraint 外键名称 foreign key(要关联的字段) references 主表(关联的字段) on update 行为 on delete 行为;
1

多表查询

概述

在项目开发过程中，我们需要设计许多张表，这些表与表之间存在着许多联系，具体分为一对一（例如用户基本信息与用户详情信息）、一对多（例如部门与员工）、多对多（例如学生与课程）

连接查询

内连接

内连接查询的是两张表交集的部分。

• 隐式内连接

select 字段列表 from 表一,表二 where 条件;
1

• 显式内连接

select 字段列表 from 表一 inner join 表二 on 连接条件; -- inner可省略
1

外连接

• 左外连接

select 字段列表 from 表1 left outer join 表2 on 条件;
1

查询表1（左表）的所有数据，包含表1表2的交集部分数据。

• 右外连接

select 字段列表 from 表1 right outer join 表2 on 条件;
1

查询表2（右表）的所有数据，包含表一表二的交集部分数据。

内连接无法查询为null的数据，外连接可以。

自连接

当前表与自身的连接查询，子连接必须使用表的别名

select 字段列表 from 表1 别名1 join 表1 别名2 on 条件;
1

自连接查询，可以是内连接查询，也可以是外连接查询。

例如我们要查询一张员工表，查询员工对应的上级，因为员工和他的上级都在这张员工表中，这时候我们就需要自连接查询了。

联合查询

将多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表一 条件
union
select 字段列表 from 表二 条件; -- 去重
1
2
3

select 字段列表 from 表一 条件
union all
select 字段列表 from 表二 条件; -- 不去重
1
2
3

例如：我们要查询出来emp表中年龄（age）大于50岁的员工数据和工资（salary）小于2000的员工数据，不去重

select * from emp where age>50
union all
select * from emp where salary<2000;
1
2
3

注意：union all表示直接合并，union表示去重合并；联合查询的字段列表的列数及字段类型必须保持一致。

子查询

SQl语句中嵌套select语句，也可称为嵌套查询。

select * from 表名 字段1= (select 字段2 from 表名 where 条件); -- 后面的这个select语句就是子查询语句
1

外部语句可以是insert/update/delete/select

标量子查询

子查询返回的结果是单个值，这种子查询称为标量子查询

例如：查询销售部所有员工的信息，该过程分为两步，1.查询销售部部门的id，2.根据部门id查询员工信息

select * from emp where dept_id=(select id from dept where name='销售部');
1

该过程中查询销售部部门id的操作就是子查询

列子查询

子查询返回的结果是一列多行

例如：查询销售部和市场部所有员工的信息，该过程分为两步：1.查询销售部和市场部的id，2.根据id查询员工

select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部')
1

行子查询

子查询返回的结果是一行多列

例如：查询张三的薪资以及与张三的薪资相同的员工的信息，该过程分为两步：1.查询张三的薪资，2.查询与张三薪资相同的员工的信息

select * from emp where salary = (select salary from emp where name='张三');
1

表子查询

子查询返回的结果是多行多列

例如：查询张三和李四的薪资以及与张三和李四的薪资相同的员工的信息，该过程分为两步：1.查询张三和李四的薪资，2.查询与张三和李四薪资相同的员工的信息

select * from emp where salary in (select salary from emp where name='张三' or name='李四')
1

事务

基础

概述：事务是一组操作的集合，把几个操作作为一个整体，要么所有操作同时成功，要么同时失败。

常见案例如银行转账：该过程分为两个操作，a转账，b收账，这两个操作要么都成功，要么都失败，不存在a转了钱b却没收到钱。

要把一组操作放在一个事务里面，那我们就需要手动开启事务和提交事务，开启事务和提交事务这中间就是我们的事务，如果这中间抛出了异常，那么事务就要手动回滚。值得注意的是，MySQL的事务默认是提交的。

操作

• 查看/设置事务提交方式

select @@autocommit; -- 查询出来的值是1，表示事务是自动提交的
set @@autocommit=0; -- 系统的事务是自动提交的，这样我们可以将其改为手动提交
1
2

0表示自动、1表示手动，默认是自动提交的，我们可以手动开启事务

上述两条语句是通过设置@@autocommit的值来设置事务是自动提交还是手动提交，我们也可以直接通过语句显式地开启事务

• 开启事务

start transaction 或 begin ;
1

2.提交事务

commit;
1

3.回滚事务

rollback;
1

注意：如果事务正常执行，那我们需要提交事务，提交事务之后才能看到数据的变化；如果事务执行过程出现异常，我们就需要手动回滚事务。

四大特性

• 原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
• 一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态（数据前后的总量不会发生变化）
• 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题

并发事务：几个事务同时操作数据库时的现象。下面三点表示在并发事务中可能出现的问题。

• 脏读：一个事务读到另一个事务还没提交的数据（如下图所示，事务B读到了事务A还没有提交的数据）

• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同（同一条数据先被事务A查询，再被事务B修改或删除，再被事务A查询）

• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在（一条数据被事务A查询时，查不到；接着这条数据在事务B中被插入，事务A再来插入的时候，发现插入不了）

针对以上问题，我们可以通过设置事务的隔离级别来解决：

这四个级别从上到下越往下数据越安全，但同时效率也会更低。

下面介绍一些关于事务的隔离级别的SQL语句：

• 查看事务隔离级别

select @@transaction_isolation
1

• 设置事务的隔离级别

set session transaction isolation level 隔离级别; 
1