MySQL是怎样使用的，超全面的总结！-51CTO.COM

1. 认识 MySQL

1.1 认识 MySQL

MySQL 是一个关系型数据库管理系统，主要用来存储、管理数据。

官网：

https://www.mysql.com/

图片

MySQL 是一个服务器-客户端系统。

服务器是负责所有数据访问和处理的一个软件，也就是 DBMS。

客户端是与 MySQL 进行数据库通信的应用程序。客户端发送 SQL 命令到 MySQL 服务器，然后接收服务器的响应。市面上有很多常用的客户端软件：SQLyog、Navicat 等。

我们说的安装 MySQL，其实就是安装 DBMS 服务器。

1.2 MySQL 结构

MySQL服务器 -> 数据库 -> 表

我们安装 MySQL 服务器之后，可以在它上面创建很多数据库，然后在每一个数据库上创建很多表

图片

2. 安装 MySQL

2.1 安装服务端

这里我们在 Windows 系统下安装 MySQL5.7，软件已经放在百度云盘里，大家可以自行下载。

链接: https://pan.baidu.com/s/1bpyLRNVBV0ycO4tj43XyTg?pwd=zhif 
提取码: zhif

双击程序安装：

图片

一直下一步直到安装完成。

默认安装位置：

C:\Program Files\MySQL\MySQL Server 5.7

2.2 安装 Navicat

Navicat 是一个连接 MySQL 的客户端。

官网:

https://www.navicat.com.cn/

图片

这里我也给大家下载到百度云盘里了，大家自行安装即可。支持正版，你懂得！

链接: https://pan.baidu.com/s/1L-2BQJTG9f3v4kivzK3n2g?pwd=1234 
提取码: 1234

Navicat 连接 MySQL 数据库。

图片

3. SQL 语句

3.1 DDL：数据定义语句，创建数据库、表

3.1.1 数据库相关

1.创建数据库db1

CREATE DATABASE IF NOT EXISTS db1;

2.创建一个使用utf8字符集的db2数据库

CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET utf8;

3.创建一个使用utf8字符集和排序规则的数据库

CREATE DATABASE IF NOT EXISTS db3 CHARACTER SET utf8 COLLATE utf8_bin;

4.查看所有的数据库

SHOW DATABASES;

5.删除数据库

DROP DATABASE db1

6.备份数据库

mysqldump -u [username] -p[password] [database_name] > [backupfile.sql]

例如：

mysqldump -u root -p123456 mydatabase > E:\\mydatabase.sql

7.恢复数据库

进入到 mysql 的可执行命令下执行

source  E:\\mydatabase.sql

3.1.2 表相关

1.创建表

create table table_name

创建表时指定字符集和排序规则，如果不指定，默认按照设置数据库时的字符集和排序规则。

CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `status` tinyint(1) DEFAULT '0' COMMENT '状态（1：正常 0：停用）',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

2.删除表

drop table table_name;

3.修改表名

rename table old_name to new_name;

4.查看表结构

desc table_name;

5.添加列

alter table table_name add col_name;
ALTER TABLE users ADD type datatype AFTER name;
ALTER TABLE users ADD type datatype BEFORE name;

6.修改列

#修改列的数据类型
alter table table_name modify column col_name new_datatype;
#修改列名和数据类型
alter table table_name change old_col_name new_col_name new_datatype;

7.删除列

alter table table_name drop column col_name;

3.1.3 Navicat 创建数据库和表

其实在日常工作中，大部分人都是使用 MySQL 客户端创建数据库和表

1.新建数据库

图片

注：字符集我们常用的就是 utf8，排序规则常用的有 utf8_bin 和 utf8_general_ci。其中 utf8_general_ci 是不区分大小写的，utf8_bin 是区分大小写的。

2.新建表

3.备份和恢复数据库

图片

3.2 DML：数据操作语句，增删改语句

3.2.1 新增语句

插入的数据的类型应该和列名的类型一致
插入的数据位置应该和列的位置一致

insert into table_name(column1,column2) values(1,"知否君");
#同时插入多条记录
insert into table_name(column1,column2) values(1,"知否君"),(2,"李白");

如果要插入所有列的数据，可以不用写列名，例如：

INSERT INTO `users` VALUES(10, '知否君');

表复制：将表中的数据再复制一遍

INSERT INTO `tableName01` 
select * from `tableName01`

3.2.2 修改语句

update table_name set colname1 = 值,colname2 = 值 where ...

3.2.3 删除语句

注：如果没有where 条件，会删除表中所有数据!

delete from table_name where ...

3.3 DQL：数据查询语句

3.3.1 基本查询语句

select * from tableName where

注：select * 表示查询所有列

3.3.2 distinct 去重

distinct: 要查询的字段值全部相同，才会去重

SELECT DISTINCT name,age FROM users;

3.3.3 别名：as

可以使用 as 表示别名

select name as thisName FROM `order`

3.3.4 where 常用运算符

1.>、<、>=、<=、= 等比较运算符
2.BETWEEN ...AND 区间
3.IN 范围内
4.LIKE 模糊查询
5.IS NULL、IS NOT NULL 是否为 NULL
6.AND 和运算符
7.OR 或运算符
8.NOT 非

SELECT * FROM users WHERE `name`='知否君';
SELECT * FROM users WHERE `age`>=18;;
SELECT * FROM users WHERE age >18 AND id >101;
SELECT * FROM users WHERE age BETWEEN 18 AND 35;
SELECT * FROM users WHERE `name` like '%知否君';

3.3.5 order by 排序

默认是升序，我们可以指定升序还是降序

1.ASC：升序
2.DESC: 降序

SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC,status ASC

3.3.6 group by 分组

按照字段分组

SELECT * FROM users group by name;

having：分组查询加入限制条件

SELECT * FROM users group by name having age>18

注：在查询数据时限定条件按照 w(where)、g(group)、h(having)、o(order)顺序。

3.3.7 limit 分页

limit 后面的两个参数

1.初始值：（current-1）*size

2.每页数量大小

例如：每页数量为5，查询第2页的数据

#(2-1)*5，5
SELECT * FROM users limit 5,5

3.3.8 多表查询

根据相关列查询

select from emp.name,dep.name
from emp,dmp
where emp.dep_id = dep.id

3.3.9 子查询

子查询又叫做嵌套查询，从查询的结果里面再次查询

select * from emp
where emp_no=(
  select emp_no from emp
  where name="知否君”
) and age>18

3.3.10 合并查询 union

union: 就是将两个查询结果合并在一起，并去重

select employee.name,employee.age
from employee 
union 
select employee.name,employee.age from employee

3.3.11 外连接

左外连接: 左侧的数据完全显示

select employee.name,employee.age,dep.name  depName
from employee
left join dep
on employee.dep_id=dep.id

图片

右外连接: 右侧的数据完全显示

select employee.name,employee.age,dep.name  depName
from employee
right join dep
on employee.dep_id=dep.id

图片

4. 常用数据类型

MySQL 中常用的数据类型主要是数值、字符串、日期。

4.1 数值型

数值型就是用来表示数值大小的类型，例如表示年龄，表示金额等等。

数值型表示的范围分为无符号和有符号。有符号指既可以存正数又可以存负数，无符号只能存大于等于 0 的数。

4.1.1 tinyint

占用 1个字节，带符号的数值范围是 -128 到 127。无符号的数值范围是 0 到 255。

我们在设置年龄、状态等数值在 100 以下的属性都可以设置成 tinyint 的。

4.1.2 smallint

占用 2个字节，带符号的数值范围是 -2^15 到 2^15-1，无符号的数值范围是 0 到 2^16 -1。

4.1.3 mediumint

占用 3个字节，带符号的数值范围是 -2^23 到 2^23-1 ,无符号的数值范围是 0 到 2^24 -1。

4.1.4 int

占用 4个字节，带符号的数值范围是 -2^31 到 2^31-1，无符号的数值范围是 0 到2^32-1。

4.1.5 bigint

占用 8个字节，带符号的数值范围是 -2^63 到 2^63-1，无符号的数值范围是 0 到 2^64 -1。

4.1.6 float

占用 4个字节，单精度小数。

4.1.7 double

占用 8个字节，双精度小数。

4.1.8 decimal（M，D）

可以设置更加精确的小数位，其中 M 是数值的总位数，最大 65 位。D 是小数点后面的数字位数，最大 30 位。

如果想要设置精度更高的数值，可以设置成 decimal 类型的。

4.1.9 注意

在 MySQL中，int(1)和int(10)的主要区别在于显示宽度，而不是存储范围。无论是指定为int(1)还是 int(10)，它们所能存储的整数值范围都是一样的，都是-2^31 到 2^31-1。

当设置填充0时，两者的区别更明显：

图片

4.2 字符串

4.2.1 char(size)

固定长度字符串，最大可以存储 255 个字符。

4.2.2 varchar(size)

可变长度字符串，最大存储 65535 字节，其中 3 个字节用来记录存储空间大小。

如果表的编码是 utf8，则最多可以存储 (65535-3) / 3 = 21844 个字符。

如果表的编码是 gbk ，则最多可以存储 (65535-3) / 2 = 32766 个字符。

4.2.3 text

最多可以存储 2^16 个字符。

4.2.4 longtext

最多可以存储 2^32 个字符。

4.3 日期型

日期型主要用来存储和时间、日期相关的数值。

4.3.1 time

主要用来存储时分秒格式的数据：17:53:32

4.3.2 date

主要用来存储 yy-MM-dd 格式的数据

4.3.3 datetime

主要用来存储 yy-MM-dd HH:mm:ss 格式的数据

4.3.4 timestamp

时间戳，其实格式也是 yy-MM-dd HH:mm:ss，新增或者修改数据时可以自动设置该值，一般默认值需要设置为 CURRENT_TIMESTAMP。

图片

5. 函数

5.1 聚合函数

5.1.1 统计

count: 返回统计的行数，count(*) 返回总记录数，count(列名) 返回某列不为 null 的总记录数

语法：

select count(*)/count(列名) from tableName where 条件

例1：返回订单总数

select COUNT(*) from `order`

例2：返回用户名不为 NULL 的总订单数

select COUNT(name) from `order`

5.1.2 合计

SUM：计算某列的总和

select SUM(amount) from `order`

5.1.3 平均值

AVG：计算某列的平均值

select AVG(amount) from `order`

5.1.4 最大值

MAX：计算某列的最大值

select MAX(amount) from `order`

5.1.5 最小值

MIN：计算某列的最小值

select MIN(amount) from `order`

5.2 字符串

5.2.1 字符串长度

LENGTH：返回字符串的长度

select LENGTH("知否技术");
select LENGTH(name) from `order`;

5.2.2 转大写

UPPER：英文小写转大写

select UPPER("zhifoujishu");
select UPPER(name) from `order`;

5.2.3 转小写

LOWER: 英文大写转小写

select LOWER("ZHIFOUJISHU");
select LOWER(name) from `order`;

5.2.4 拼接

CONCAT: 拼接字符串

SELECT CONCAT('知否', '&', '技术');

5.2.5 替换

REPLACE：替换字符串

SELECT REPLACE('知否', '否', '否技术');

5.2.6 截取

SUBSTRING：截取字符串。

第1个参数：要截取的字符串
第2个参数：起始位，默认从 1 开始
第3个参数：截取后字符串的长度

SELECT SUBSTRING('公众号知否技术', 2, 5);

图片

5.3 时间

5.3.1 获取当前日期时间

select CURRENT_DATE(); #yyyy-MM-dd
select CURRENT_TIME();#HH:mm:ss
select NOW();#yyyy-MM-dd HH:mm:ss

5.3.2 从日期中选择出年份

select year("2024-08-18");

5.3.3 从日期中选择出月份

select MONTH("2024-08-18");

5.3.4 从日期中选择出天数

select DAY("2024-08-18");

5.3.5 日期差：天数

select DATEDIFF("2024-09-18","2024-08-18")

5.3.6 日期加减

select DATE_ADD("2024-09-18",INTERVAL 1 DAY);#日期相加-天数
select DATE_SUB("2024-09-18",INTERVAL 1 MONTH);#日期相减-月份

6.表约束

6.1 主键

关键词：primary key

解答：一个表中只能有一个主键，主键不能重复不能为 null，可以是单列主键或者是多列复合主键。

CREATE TABLE order_detail (  
        order_id INT PRIMARY KEY, 
    );

如果一个表中同时设置多个列是主键，那么这几个列联合起来叫做复合主键。

例：这里设置 id 和 type 列是复合主键。那么 id 和 type 联合起来不能重复

图片

案例中，id 和 type 不能同时一样。

图片

6.2 唯一

关键词： unique

解答：不能重复

6.3 外键

关键字：FOREIGN KEY

外键用来定义主表和从表之间的关系。首先主表的某个字段必须要有唯一约束，然后外键约束主要定义在从表上。

当定义外键约束之后，主表列必须存在或者为 null。

6.3.1 外键案例

新建部门表：id 是主键

CREATE TABLE `demp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新建员工表：dep_id 是外键

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`dep_id`) REFERENCES `demp` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

employee 中 dep_id 是 demp 表中已存在的 id，如果不存在可以设置成 null。

图片

6.3.2 navicat 设置外键

图片

用 Navicat 设置外键的时候，删除时和更新时有四个值可以选择：CASCADE、NO ACTION、RESTRICT、SET NULL

他们的区别如下：

CASCADE：主表 delete、update 的时候，子表会 delete、update 掉关联记录;
RESTRICT：如果想要删除主表的记录，而子表中有关联主表的列，则不允许删除父表中的记录;
NO ACTION：同 RESTRICT;
SET NULL：主表 delete、update 的时候，子表会将外键字段所在列设为 null，所以注意在设计子表时外键不能设为 not null;

6.4 自增长

解答：我们一般在设置数值的时候会设置自增长，例如设置 id 自增长: AUTO_INCREMENT。自增长默认从 1 始。

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

我们也可以修改自增长的初始值从某个数字开始：

alter table table_name auto_increment = 1;

7.索引

我们在参加面试的时候，面试官经常会问：如何提高数据库查询速度？

大部分人可能只知道一个答案：加索引。

索引就像字典的目录一样，可以提高查询速度。

在 MySQL 中，索引的本质其实就是一个文件，它的结构是二叉树：

图片

没有索引之前，MySQL 需要对整个表全部扫描一遍才能查到数据，所以速度非常慢。

有了索引之后，MySQL 直接去索引文件查数据，先从根节点查找，如果要查的数据比根节点小，那就进入左边，如果比根节点大，那就进入右边，以此类推，就像我们翻词典目录一样，所以速度非常快。

MySQL 中常用的索引类型主要有以下几种：

7.1 主键索引

任何加了主键约束的列默认都设置了主键索引。

我们在设计表的时候一般都会给 id 设置主键，所以跟据 id 查询数据的时候会很快。这就是因为 id 默认设置了主键索引。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
);

7.2 唯一索引

设置唯一索引之后，该列必须是唯一的。

例如：我们给 uname 属性设置唯一索引

CREATE UNIQUE INDEX uname_index ON users (uname);
#或者
ALTER TABLE users ADD UNIQUE INDEX uname_index (uname);

然后插入两条记录

INSERT INTO users (uname, email) VALUES ('zhifoujun', '124@qq.com');
INSERT INTO users (uname, email) VALUES ('zhifoujun', '456@qq.com');

因为 uname 设置了唯一索引，所以插入失败。

7.3 普通索引

普通索引：就是给任意列设置索引

CREATE  INDEX uname_index ON users (uname);
#或者
ALTER TABLE users ADD  INDEX uname_index (uname);

7.4 组合索引

组合索引：一个索引包含多个列

CREATE INDEX indexName ON table_name (column_name1,column_name2,...);

组合索引遵循最左前缀原则：就是如果你的 SQL 语句中用到了组合索引中的最左边的索引，那么这个索引就有效。

举个例子：我们创建一个用户表：

CREATE TABLE `z_user`(
 `id` INT,
 `name` VARCHAR(50),
 `age` INT
);

然后创建索引，后面使用abc代表这三个索引字段

CREATE UNIQUE INDEX index_name ON z_user(id,name,age);

索引有效的情况：abc、ab、ac、a

SELECT * FROM t_user WHERE id = 1 AND NAME = 'zhifoujun' AND age = 21;
SELECT * FROM t_user WHERE id = 1 AND NAME = 'zhifoujun';
SELECT * FROM t_user WHERE id = 1 AND age = 21;
SELECT * FROM t_user WHERE id = 1;

索引失效情况：b、c、bc

SELECT * FROM t_user WHERE NAME = 'zhifoujun';
SELECT * FROM t_user WHERE age = 21;
SELECT * FROM t_user WHERE NAME = 'zhifoujun' AND age = 21;

总结：前面我们给 abc 三个字段设置了组合索引，发现只要查询条件中包含 a 字段，索引就有效，这就是“最左前缀”原则。

7.5 Navicat 设置索引

使用 Navicat 设置索引非常简单，索引类型选择 NORMAL，索引方法选择 BTREE。

如果是组合索引，那就选择多个字段。

图片

8.用户管理

8.1 创建用户

CREATE USER 'zhifoujun'@'localhost' IDENTIFIED BY '123456';

8.2 修改密码

root 用户修改其他用户密码

SET PASSWORD FOR 'zhifoujun'@'localhost' = PASSWORD('123456')

自己修改自己的密码

SET PASSWORD = PASSWORD('123456')

8.3 删除用户

如果用户 host 不是 % ,需要根据设置的 ip 删除

DROP USER 'zhifoujun'@'localhost';

如果用户 host 是 % ,直接根据名字删除

DROP USER zhifou

9.权限管理

9.1 给用户授权

图片

grant 权限列表 on 数据库.表 to '用户名’@’登录位置' 密码

给用户 zhifou 设置查询、新增权限

GRANT SELECT, INSERT
 ON testdb.users
 TO 'zhifou'@'localhost';

9.2 收回用户权限

REVOKE SELECT, INSERT ON testdb.users FROM 'zhifou'@'localhost';