MySQL基础

发表于 分类于

MySQL

什么是MySQL？

MySQL它是一个关系型数据库管理系统，Oracle旗下产品；是最流行的关系型数据库管理系统之一，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，以此增加了速度并提高了灵活性；其次它使用SQL语言用于操作数据库的标准语言，以其体积小、速度快、成本低广受个人及企业的喜爱；

SQL语句分类

**DDL：**数据定义语言，用来定义数据库、表、列等

**DML：**数据操作语言，用来对数据库表中的数据进行增删改查

**DQL：**数据查询语言，用来查询数据库表中的数据

**DCL：**数据控制语言，用来定义数据库的访问权限和安全级别，以及创建用户

MySQL常用数据类型

数值

1
2
3
4
5
int：标准的整数，4个字节
bigint：较大整数数据，8个字节

float：浮点数，4个字节
double：浮点数，8个字节

字符串

1
2
3
char：固定字符串
varchar：可变字符串
text：文本

时间日期

1
2
3
date：年月日，YYYY-MM-DD
time：时分秒，HH：mm:ss
datetime：年月日时分秒，YYYY-MM-DD HH:mm:ss

安装

1、配置MySQL软件仓库，具体配置获取；清华大学软件源官网：https://mirrors.tuna.tsinghua.edu.cn/help/mysql/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#创建mysql.repo
vim /etc/yum.repos.d/mysql.repo

#从清华大学软件源官网复制MySQL软件源配置，并填入，不要直接复制下面的！去官网复制
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-connectors-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

[mysql-tools-community]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

[mysql-8.0-community]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

2、清理缓存、更新数据源

1
2
3
dnf clean all

dnf makecache

3、安装、启动、开机自启MySQL

1
2
3
4
5
dnf -y install mysql-community-server

systemctl start mysqld

systemctl enable mysqld

4、修改密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#获取临时密码
grep 'temporary password' /var/log/mysqld.log

#使用临时密码登陆
mysql -uroot -p
输入临时密码

#修改密码，新密码要包含大写字母、小写字母、特殊符号，例如：Yang@963214
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Yang@963214';

#退出MySQL
exit

#测试使用新密码登入

5、运行安全配置，交互模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql_secure_installation

#输入密码
Enter current password for root (enter for none):新密码

#是否修改密码，按 n ，刚刚已经修改了
Set root password? [Y/n] n

#是否删除匿名用户，按 y 删除
Remove anonymous users? [Y/n] y

#是否禁止root用户远程登陆，按 y 禁止，如果是个人用的话可以不用禁止
Disallow root login remotely? [Y/n] y

#是否删除test数据库，按 y 删除
Remove test database and access to it? [Y/n] y

#是否重新加载权限表，按 y 重新加载
Reload privilege tables now? [Y/n] y

6、查看

1
2
3
4
5
6
7
8
9
10
11
#运行状态
systemctl status mysqld

#进程情况
ps -ef | grep mysql

#查看端口号
ss -tulnp | grep mysql

#连接情况
ss -a | grep mysql

7、新建用户提供远程登陆，root已被禁止远程登陆

1
2
3
4
5
6
7
8
#登入mysql，新建用户
CREATE USER 'viper'@'%' IDENTIFIED BY 'Yang@963214';

#赋予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'viper'@'%' WITH GRANT OPTION;

#刷新权限
FLUSH PRIVILEGES;

8、配置防火墙

1
2
3
4
5
6
7
8
#放行MySQL默认端口
firewall-cmd --permanent --add-port=3306/tcp

#重新加载防火墙
firewall-cmd --reload

#查看是否放行
firewall-cmd --permanent --query-port=3306/tcp

9、使用Navicat测试

1
2
用户名：viper
密码：Yang@963214

DDL

数据定义语言，用来定义数据库、表、列等操作；

操作数据库

1、创建数据库

1
2
#可选项 if not exists 如果数据库不存在才创建
create database [if not exists] 数据库名;

2、使用数据库

1
user 数据库名;

3、查看当前使用的数据

1
select database();

4、查看所有数据库

1
show database;

5、删除数据库

1
2
#可选项 if not exists 如果数据库存在才删除
drop database [if exists] 数据库名;

操作表

====== 创建表 ======

1
2
3
4
5
6
7
8
#可选项 if not exists 检查该表不存在才会创建
#注意：最后一行不用加 , 号，不然会报错
create table [if not exists] 表名(
	字段名1 数据类型1,
    字段名2 数据类型2,
    ...
    字段名n 数据类型n
);

====== 查询表 ======

1
2
3
4
5
#查看当前数据库下所有表名称
show tables;

#查看指定表的结构
desc 表名;

====== 修改表 ======

1、修改表名

1
alter table 表名 rename to 新表名;

2、添加一列

1
alter table 表名 add 列名 数据类型;

3、修改数据类型

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

4、修改列名和数据类型

1
alter table 表名 change 新列名 新数据类型;

5、删除列

1
alter table 表名 drop 列名;

====== 删除表 ======

1
2
#可选项 if not exists 检查表是否存在才会删除
drop [if not exists] 表名;

DML

数据操作语言，用来对数据库表中的数据进行增删改查；

添加数据

1
2
3
4
5
6
7
8
9
10
#给指定列添加数据
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

#给全部列添加数据
insert into 表名 values(值1，值2......);

#批量添加数据
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n),values(值1,值2,...值n)....;

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

修改数据

1
2
#注意：不加添加条件则会删除所有数据！！！
update 表名 set 列名1=值1,列名2=值2... where 条件;

删除数据

1
2
#注意：不加where条件将删除所有数据
delete from 表名 where 条件;

DQL

数据查询语言，用来查询数据库表中的数据；

基础查询

1
2
3
4
5
6
7
8
9
10
11
#查询表中所有数据
select * from 表名;

#查询多个字段数据
select 字段列表 from 表名;

#去重
select distinct 字段列表 from 表名;

#取别名 as，as可以省略不写
select 字段名 as 别名 from 表名;

条件查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select 字段列表 from 表名 where 条件列表;

#条件符号
>：大于
<：小于
>=：大于等于
<=：小于等于
=：等于
!=：不等于
between...and...：在某个范围之内
in(...)：多选一
is null：是null
is not null：不是null
and：并且
or：或者
not：非，不是

模糊查询

通配符：_代表任意单个字符，%代表任意多个字符

1
select 字段列表 from 表名 where 字段名 like '匹配项';

排序查询

排序方式：asc代表升序排序（默认），desc代表降序排序

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

聚合函数

将一列数据作为一个整体，纵向计算，null值不参加所有聚合函数运算；

====== 分类 ======

函数名 功能
count(列名) 统计数量
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值

====== 聚合函数语法格式 ======

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

分组查询

1
select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];

where和having的区别

1、执行时机不同，where是分组前限定，不符合的不参与分组，having则是分组后对结果进行过滤，不符合的不会显示结果；

2、可判断的条件不一样，where条件不能对聚合函数进行判断，having可以。

分页查询

起始索引计算公式：当前页码-1 * 每页显示的条目

1
select 字段列表 from 表名 limit 起始索引 , 查询条目数;

DCL

数据控制语言，用来定义数据库的访问权限和安全级别，以及创建用户；

用户管理

创建用户

1
2
3
4
5
# %允许所有主机访问，localhost表示仅本地访问
create user '用户名'@'允许访问的主机' identified by '密码';

create user 'viper'@'%' identified by '000000';
FLUSH PRIVILEGES;

修改用户名

1
rename user '旧用户名'@'host' to '新用户名'@'host';

修改密码

1
alter user '用户名'@'host' identified by '新密码';

删除用户

1
drop user '用户名'@'host';

权限管理

常用权限

关键字 含义
select 允许查询数据
insert 允许插入数据
update 允许修改数据
delete 允许删除数据
create 允许创建库/表
alter 允许修改表结构
drop 允许删除库/表
index 允许创建/删除索引
execute 允许执行存储过程
all 所有权限

授于权限

1
2
3
4
grant 权限类型 on 数据库名.表名 to '用户名'@'host';

grant all on ry to 'viper'@'host';
FLUSH PRIVILEGES;

查看权限

1
2
show grant for '用户名'@'host';
show grant for 'viper'@'host';

撤销权限

1
2
revoke 权限类型 on 数据库名.表名 to '用户名'@'host';
FLUSH PRIVILEGES;

角色管理（8.0及以上版本才支持）

除了直接赋予用户权限外，还可以通过角色的形式赋予用户权限。

创建角色

1
create role '角色名称';

授于角色权限

1
grant 权限类型 on 数据库名.表名 to '角色名称';

将角色授予用户

1
grant '角色名称' to '用户名'@'host';

激活角色

1
set default role all to '用户名'@'host';

约束

约束作用于表中列上的规则，用于限制加入表的数据，保证了数据的正确性、有效性和完整性。

约束分类

名称 关键字 功能
非空约束 not null 保证列中所有数据不能有null值
唯一约束 unique 保证列中所有数据各不相同
主键约束 primary key 一行数据唯一标识符，要求非空且唯一
默认约束 default 保存数据时，未指定值则采用默认值
外键约束 foreign key 用来让两个表的数据之间建立连接，保证数据的一致性和完整性

外键约束

1、建表时添加

1
constraint 外键名称 foreign key(外键列名) references 主表名(主表列名);

2、建完表后添加

1
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名称(主表列名);

3、删除外键

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

多表查询

多表查询分类

内连接：查询两表交集部分的数据

左外连接：查询左边表和交集所有的数据

右外连接：查询右边表和交集所有的数据

子查询：查询中嵌套查询

连接示意图

image-20250513115127934

内连接

语法格式

1
2
3
4
5
#隐式内连接
select 字段列表 from 表1 , 表2... where 条件;

#显式内连接,inner可以省略不写
select 字段列表 from  表1 inner join 表2 on 条件;

左外连接

语法格式

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

右外连接

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

子查询

语法格式

1
2
3
4
5
6
7
8
#单行单列，作为条件值
select 字段列表 from 表名  where 字段名 = (子查询);

#多行单列，作为条件值，使用in关键字进程判断
select 字段列表 from 表名 字段名 in (子查询);

#多行多列，作为虚拟表
select 字段列表 from (子查询) where 条件;

事务

事务是一种机制、一个操作序列，包含了一组数据库操作命令，事务会将这些操作命令作为一个整体一起向系统提交或撤销操作请求，这时这一组命令要们全部成功，要们全部失败。

语法格式

1
2
3
4
5
6
7
8
#开启事务
begin;

#提交事务
commit;

#回滚事务
rollback;

事务四大特征（ACID）

原子性：要们同时成功，要们同时失败

一致性：事务完成时，必须保证所有数据都保持一致

隔离性：多个事务之间相互隔离，当一个事务在操作时不应被另一个事务干扰

持久性：事务一旦提交或回滚，它的数据的修改就是永久的

事务的隔离级别

===== 读未提交 ======

特点：事务可以读取到其它事务未提交的数据

问题：会出现脏读，事务A读取了事务B未提交的修改，如果B回滚了事务，那么A事务读取到的数据就是无效的。

===== 读已提交 ======

特点：事务只能读取到其他事务已提交的数据

问题：会出现不可重复读，事务A两次读取同一数据，中间事务B修改并提交了事务A读取的数据，导致A事务两次读取到的数据不一致。

===== 可重复读======

特点：事务执行期间，多次读取同一数据结果一致，是MySQL默认隔离级别

问题：会出现幻读，事务A读取某个范围的数据，事务B插入了新数据并提交，事务A再次查询时发现凭空出现的数据。

===== 序列化======

特点：最高隔离级别，所有事务串行执行（类似单线程），完全避免并发问题

问题：性能极低，可能会导致大量锁等待或死锁

索引

什么是索引？

索引时数据库中用于快速查找数据的数据结构，类似书籍的目录，使用索引可以快速定位到需要查找的数据，减少数据库需要扫描的数据量。

索引分类

普通索引：最基本的索引，无特殊限制，关键字 inde/key

唯一索引：索引列值必须唯一，允许null值，关键字 unique key

主键索引：特殊的唯一索引，不允许null值，关键字 primary key

全文索引：用于全文搜索 FullText

创建索引

1
2
3
4
5
#创建表时
create 索引类型 索引名称 on 表名(列名);

#创建表后
alter table 表名 add 索引类型 索引名称(列名);

查看索引

1
show index from 表名;

删除索引

1
drop index 索引名称 on 表名;

索引使用场景

====== 适合作为索引的字段 ======

1、主键

2、频繁作为查询条件的字段

3、查询中排序的字段

4、查询中统计或分组的字段

====== 不合适作为索引的字段 ======

1、频繁更新的字段

2、数据量小的表

3、数据量重复且分布均与的字段

备份与导入

备份

1
mysqldump -h主机 -u用户名 -p 数据库 表 > 存储路径/备份文件名.sql

导入

1
2
#登陆后
source sql文件所在路径