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，软件大家可以自行下载。

双击程序安装

一直下一步直到安装完成

默认安装位置：

C:\Program Files\MySQL\MySQL Server 5.7

2.2 安装 Navicat

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

官网:

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

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

此处含有隐藏内容，需要正确输入密码后可见！

[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 是否为 NULL6.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）*size2.每页数量大小

例如：每页数量为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, [email protected]);
INSERT INTO users (uname, email) VALUES (zhifoujun, [email protected]);

因为 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;

MySQL是怎样使用的，超全面的总结！