注：本篇博客大部分是黑马pdf中的内容，根据我自己学习的实际情况进行修改和补充。

简介

数据库(database 简称DB)是存储数据的仓库，数据是有组织的进行存储.而数据库管理系统(database management system)是操作和管理数据库的大型软件(常用的有oracle、mysql等).SQL是操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

准备工作

  1. 建议读者具有一定的Linux基础再来学习本篇博客
  2. 本博客所使用的是Mysql8.0的版本
  3. 所有代码中的注释均使用--或者/**/以便适配不同的sql语言

下载

Windows下载MySQL8.0版本

官网：MySQL :: Download MySQL Installer

如图所示进行下载.后面一路默认安装，在输入mysql中root用户密码的时候建议稍复杂一点，牢记即可。
001_mysql安装_01.png

然后再配置环境变量：在“开始”中搜索”编辑系统环境变量“-找到PATH点击去，将MySQL server的安装目录下的bin目录添加到环境变量，如图

001_mysql安装_02.png

Linux中下载MySQL8.0版本

同样在上面的官网下载，选择合适的版本(当然用Linux联网下载也是可以的)。如果速度慢可以考虑此链接Index of /mysql/ | 清华大学开源软件镜像站

001_mysql安装_03.png

  1. 用Xftp连接到Linux，然后将其上传到根目录下
  2. 创建目录并解压.注意这里的文件名是你自己下的文件的名字。
1
2
mkdir mysql
tar -xvf mysql-8.0.40-1.e17.x86_64.rpm-bundle.tar -C mysql
  1. 安装MySQL的安装包(注意严格按照顺序执行命令，否则会出现依赖错误)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
cd mysql

rpm -ivh mysql-community-common-8.0.40-1.el7.x86_64.rpm 

rpm -ivh mysql-community-client-plugins-8.0.40-1.el7.x86_64.rpm 

rpm -ivh mysql-community-libs-8.0.40-1.el7.x86_64.rpm 

rpm -ivh mysql-community-libs-compat-8.0.40-1.el7.x86_64.rpm

yum install openssl-devel

rpm -ivh  mysql-community-devel-8.0.40-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.40-1.el7.x86_64.rpm

rpm -ivh  mysql-community-server-8.0.40-1.el7.x86_64.rpm
  1. 启动MySQL服务
1
systemctl start mysqld

设置开机自启动,以后只要打开Linux就会启动mysql

1
systemctl enable mysqld
  1. 登录MySQL并设置root密码

查看自动生成的密码：grep 'temporary password' /var/log/mysqld.log并将其复制

命令行输入mysql -u root -p 并粘贴刚才的密码(shift+insert)

注意 以下的命令输入是在登录MySQL后的

登录到MySQL之后，需要将自动生成的不便记忆的密码修改了，修改成自己熟悉的便于记忆的密码。

1
ALTER  USER  'root'@'localhost'  IDENTIFIED BY '1234(你自己设置密码)';

执行上述的SQL会报错，原因是因为设置的密码太简单，密码复杂度不够。我们可以设置密码的复杂度为简单类型(自己学习用可以设置简单密码，但是在生产中不建议使用简单密码)。

1
set global validate_password.policy = 0;

降低密码的校验规则之后，再次执行上述修改密码的指令。

  1. 创建远程登录用户

默认的root用户只能当前节点localhost访问，是无法远程访问的，我们还需要创建一个root账户，用户远程访问

1
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '自己设置密码';

分配权限

1
grant all on *.* to 'root'@'%';

后面通过其他软件进行远程登录的时候就可以使用此用户进行登录了。

示例：使用IDEA进行远程登录

点击“+”-”数据源“-“MySQL”，然后按照图填写相关配置即可。

001_mysql安装_04.png

Windows启动MySQL

打开方式有如下三种

通过命令行的方式打开(Windows11)

  1. 按win+r键，在弹出的窗口内输入cmd
  2. 再按ctrl+shift+enter以管理员的方式运行
  3. 输入net start MySQL服务名(例如本机是mysql80)
  4. 停止Mysql命令：net stop mMySQL服务名

001_mysql安装_05.png

  1. 按win+r键，在弹出的窗口内输入cmd
  2. 输入services.msc打开服务，找到对应的mysql，右键启动即可

按win+x键，选择“任务管理器”，在左侧栏点击“服务”，搜索mysql，然后鼠标右键点击“打开服务”(或者按照系统控制的方法打开服务也行)，然后找到mysql右键-“属性”，点开“启动类型”并选择自动，点击应用并确定即可实现开机自启动。

MySQL连接方式

方式 效果 难度 连接操作
MySQL 8.0 Command Line Client 和命令行一样，可视性不是特别强 低，安装MySQL自带的 开始界面搜索MySQL 8.0 Command Line Client，然后输入密码即可
Windows命令行 命令行，无需多言 中，需要在系统属性里的PATH里面配置环境变量(就是你的MySQL路径\bin) 打开命令行之后输入mysql -u用户名 -p密码 或者mysql -u root -p 加密输入密码防止泄露

Windows命令行打开示意图(和Linux一样)

001_mysql安装_06.png

命令行工具指令讲解

1
mysql[-h 127.0.0.1][-P 3306] -u root -p

其中-h:MySQL服务所在的主机IP；-P:MySOL服务端口号，默认3306；-u:MySQL数据库用户名-p:MySQL数据库用户名对应的密码

MySQL基础篇

概念解析

关系型数据库

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。二维表就是由行和列组成的表，就是Excel中的那种表

SQL语言

Structured Query Language 操作关系型数据库的编程语言

无论是MySQL，还是oracle等其他的数据库，都遵守一定的SQL通用语法，特点如下

  1. 不区分大小写，但建议关键字大写
  2. 注释：#或者是--单行注释，/**/是多行注释
  3. 可以单行或者多行书写，以分号结尾

DDL

Data Definition Languages 即数据定义语言

数据库操作

以下我使用图形化工具IDEA展示效果，读者可以使用datagrip、Tomcat等图形化工具进行操作学习

由于hexo的原因，以下所有代码块中的语言都改为SQL，除特别指出，以下SQL均代表MySQL

  1. 查询所有数据库
1
show databases;

002_DDL_01.png

因为我之间创建过一些数据库，所以会有读者没有的数据库，读者无需在意。

  1. 查询当前数据库
1
select database();
  1. 创建数据库

语法：create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;

语法讲解

  1. []内的内容表示非必写，或者是限定条件
  2. 对于某些版本的MySQL(比如5.7版本)可能需要设置字符集为utf-8
1
2
3
4
-- 由于mysql中不能创建两个名称相同的数据库，可以使用if语句事先判断是否存在同名的数据库
create database if not exists test1;
-- 创建一个数据库并指定字符集
create database if not exists Mydatabase01 default charset utf8mb4;
  1. 删除数据库

语法：drop database [ if exists ] 数据库名 ; 

1
drop database test3;
  1. 切换数据库

语法：use 数据库名

1
use test1;

表操作

  1. 展示当前数据库中所有的表
1
show tables;
  1. 查看表结构

语法：desc 表名.可以查看表的字段、数据类型、主键等

1
desc emps;

002_DDL_02.png

查看表中所有的数据

1
2
3
describe test1.emps;
-- 等效于以下语句
show columns from emps;
  1. 查询指定表的建表语句

语法：show create table 表名。就是查看创建表所使用的代码。

1
show create table emps;

002_DDL_03.png

  1. 创建表结构

语法如下：注意[…]为可选函数，最后一个字段后面没有逗号

1
2
3
4
5
6
7
CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释 ],
	字段2 字段2类型 [COMMENT 字段2注释 ],
	字段3 字段3类型 [COMMENT 字段3注释 ],
	......
	字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

示例

1
2
3
4
5
6
create table tb_user(
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表';

查看错误与警告

1
2
3
-- 展示错误与警告
show errors ;
show warnings ;

数据类型

常见的数据类型基本上和其他高级编程语言大致相同，主要分为三类，数值类型、字符串类型、日期时间类型。根据字段要存储的数据选择合适的数据类型(比如年龄适合用tinyint unsigned)

类型 大小 有符号(SIGNED)范围 无符号(UNSIGNED)范围 描述
TINYINT 1byte (-128，127) (0，255) 小整 数值
SMALLINT 2bytes (-32768，32767) (0，65535) 大整 数值
MEDIUMINT 3bytes (-8388608，8388607) (0，16777215) 大整 数值
INT/INTEGER 4bytes (-2147483648， 2147483647) (0，4294967295) 大整 数值
BIGINT 8bytes (-2^63，2^63-1) (0，2^64-1) 极大 整数 值
FLOAT 4bytes (-3.402823466 E+38， 3.402823466351 E+38) 0 和 (1.175494351 E- 38，3.402823466 E+38) 单精 度浮 点数 值
DOUBLE 8bytes (-1.7976931348623157 E+308， 1.7976931348623157 E+308) 0 和 (2.2250738585072014 E-308， 1.7976931348623157 E+308) 双精 度浮 点数 值
DECIMAL 依赖于M(精度)和D(标度)的值(精度就是数字长度，标度就是小数点后几位) 依赖于M(精度)和D(标度)的值 小数值(精确确定点数)
类型 大小 描述
CHAR 0-255 bytes 定长字符串(需要指定长度)
VARCHAR 0-65535 bytes 变长字符串(需要指定最大长度)
TINYBLOB 0-255 bytes 不超过255个字符的二进制数据
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据
类型 大 小 范围 格式 描述
DATE 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
TIME 3 -838:59:59 至 838:59:59 HH:MM:SS 时间值或持续 时间
YEAR 1 1901 至 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时 间值
TIMESTAMP 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时 间值，时间戳 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 案例-设置员工表
-- 1.编号（纯数字）
-- 2.员工工号(字符串类型，长度不超过10位)
-- 3.员工姓名(字符串类型，长度不超过10位)
-- 4.性别(男/女，存储一个汉字)
-- 5.年龄(正常人年龄，不可能存储负数)
-- 6.身份证号(二代身份证号均为18位，身份证中有x这样的字符)
-- 7.入职时间（取值年月日即可）
create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    entrydate date comment '入职时间'
)comment '员工表';

增删查改

  1. 添加字段

语法：ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; 

1
alter table emp add nickname varchar(20) comment '昵称';
  1. 修改数据类型

语法：ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度); 

1
alter table emp change nickname username varchar(30) comment '用户名';
  1. 修改字段和字段类型

语法：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

1
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
  1. 删除字段

语法：ALTER TABLE 表名 DROP 字段名; 

1
alter table emp drop username;
  1. 修改表名

语法：ALTER TABLE 表名 RENAME TO 新表名 

1
alter table emp rename to employee;
  1. 删除表

语法：DROP TABLE [ IF EXISTS ] 表名; ;

1
2
-- 只有表名存在的时候才会删除该表，不存在的时候就不会删除
drop table if exists tb_user;
  1. 删除并重新创建表

语法：TRUNCATE TABLE 表名; 

1
2
-- 删除并创建该表,这样就实现了数据清空但保留表结构的效果
truncate table employee;

注意：以上操作虽然可以通过图形化界面工具直接实现，但数据库常常在Linux上操作，建议读者使用命令行的方式实现有关需求。

方法：右键数据库-“新建”-“查询控制台”，然后就可以在界面中编写sql并执行。

DML

Data Manipulation Languages 数据操作语言

增删查改数据

  1. 给指定字段添加数据

语法：INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); 

1
2
-- 示例
insert into employee(id, workno, name, gender, age, idcard, entrydate) VALUES(1,'1','itcast','男',10,'123456789012345678','2000-11-01');

数据查询如下

003_DML_01.png

如果是要给全部字段添加数据，就无需写字段名

  1. 批量添加

语法：INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ; 如果是全部字段添加数据则无需写明字段。

1
2
3
insert into employee values
	(3,'3','韦一笑','男',38,'123456789012345670','2005-01-01'),
	(4,'4','赵敏','女',18,'123456789012345670','2005-01-01');

注意

  1. 插入数据时指定的字段顺序需要与值的顺序是一一对应的
  2. 字符串和日期类型要在引号中
  3. 数据应在规定范围内，否则会报错
  1. 修改数据

语法：UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ; 

1
2
-- 将id为1的员工的员工名字改为itheima，性别改为女
update employee set name = 'itheima',gender = '女' where id = 1;

003_DML_02.png

1
2
-- 将所有员工的入职时期设置为同一个时期
update employee set entrydate  = '2003-01-01';

003_DML_03.png

如果修改数据没有where语句，默认修改所有数据

  1. 删除数据

语法：DELETE FROM 表名 [ WHERE 条件 ] ;

1
2
3
4
-- 删除所有员工性别为女的数据
delete from employee where gender = '女';
-- 相当于删除所有数据
delete from employee;

delete语句不能删除某一个字段的值，需要使用update将该字段设置为null

DQL

Data Query Language 数据查询语言，用来查询数据库中表的记录

在实际开发和业务中，查询也是最常用的

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

这一语法非常重要，涉及到很多的知识。

基础查询

  1. 查询多个字段

语法：SELECT 字段1, 字段2, 字段3 ... FROM 表名 ; 

1
2
3
4
-- 从emps表中查询employee_id、email和hire_date字段的数据
select employee_id,email,hire_date from emps;
-- 如果要查询所有字段，可以使用select * from 表明，但是在开发规范中一般会规定不使用* 而用字段提高效率。
select * from emps;

004_DQL_01.png

  1. 字段设置别名

语法：SELECT 字段1 [ [AS] 别名1 ] , 字段2 [ [AS] 别名2 ] ... FROM 表名; 

1
2
-- 给字段取别名 建议带上as
select employee_id as '职工号' from emps;

004_DQL_02.png

  1. 去除重复数据

语法：SELECT DISTINCT 字段列表 FROM 表名; 

1
2
-- 查询字段数据且不重复
select distinct job_id '工作岗位' from emps;

004_DQL_03.png

条件查询

语法：SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
常见的比较运算符和逻辑运算符如下

比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<> 或 != 不等于
BETWEEN … AND … 在某个范围之内(含最小、最大值)
IN(…) 在in之后的列表中的值，多选一
LIKE 占位符 模糊匹配(_匹配单个字符, %匹配任意个字符)
IS NULL 是NULL
逻辑运算符 功能
AND 或 && 并且 (多个条件同时成立)
OR 或 || 或者 (多个条件任意一个成立)
NOT 或 ! 非 , 不是 
1
2
3
-- 条件查询 
-- 从emps表中查询员工id为100的员工所有信息
select * from emps where employee_id = 100;

004_DQL_04.png

1
2
3
-- 多个字段的限制
-- 从emps表中查询工资等于2500且部门id为50的员工所有信息
select * from emps where salary = 2500 and department_id = 50;

004_DQL_05.png

字符串的查询：模糊匹配

1
2
-- 查询姓最后一个字符是a的数据
select * from emps where emps.first_name like '%a';

004_DQL_06.png

聚合函数

概念：将一列数据作为整体进行数学运算，就像是Excel的函数库
常见的聚合函数

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

语法:SELECT 聚合函数(字段列表) FROM 表名 ;

1
2
3
4
-- 统计有commission_pct字段数据的个数 注意null是不参与聚合函数的运算的
select count(commission_pct) from emps;
-- 统计总记录数 用count(1)同理
select count(*) from emps;

004_DQL_07.png

分组查询

语法：SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
where与having区别

  1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组
    之后对结果进行过滤。
  2. 判断条件不同：where不能对聚合函数进行判断，而having可以
  1. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
  2. 执行顺序: where > 聚合函数 > having 。
  3. 支持多字段分组, 具体语法为 : group by columnA,columnB
1
2
3
-- 统计不同工资对应的人员数量
-- 先对工资分组，然后再统计每一组工资员工的数量
select salary,count(*) from emps group by salary;

004_DQL_08.png

1
2
-- 根据部门分组，统计各部门的平均工资
select department_id,avg(salary) from emps group by department_id;

004_DQL_09.png

1
2
-- 查询工资小于等于4000的员工，并根据部门分组，获取员工数量大于等于2的部门id
select department_id,count(*) from emps where salary <=4000 group by department_id having count(*) >=2;

004_DQL_10.png

排序查询

语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ; asc为升序(默认)，desc为降序

1
2
-- 根据入职时间升序排序
select * from emps order by hire_date;

004_DQL_11.png

如果是多字段排序，当第一个字段相同的时候才会按照第二个字段进行排序

1
2
-- 先按照工资排序，年龄相同就按入职时间降序排序
select * from emps order by salary asc,hire_date desc;

004_DQL_12.png

分页查询

就是在浏览网页的时候经常会有的分页条

语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

1
2
3
-- 分页查询-尤其在电商网站上使用较多
-- 查询第一页员工数据，每一页显示10条数据,注意经典索引从0开始
select * from emps limit 0,10;

004_DQL_13.png

启示：用英语“从句”的思维理解mysql语句执行的先后顺序

举例：查询性别为男，年龄在20-40岁之间(含)的前5个员工信息，对查询的结果按年龄升序排列，如果年龄相同就按照入职时间排序。

这里的关键在于找到“主句”-查询信息，谁的信息呢？，前面的修饰语就是“男”、“年龄”和“前五个”，后面的对……句都是状语，一般放在后面的，所以先写出select * from emp where gender = '男' and age between 20 and 40的语句，但是好像没有“前五个”，聪明的你应该发现前五个就是分页查询，按每页5个不就是能找到前五个了吗。那就是limit 5，由于mysql的特性，limit要放在句子的最后面。我们再考虑“状语”，关键词是“排序”，那就在前面语句的基础上再筛选 即order by age asc,entrydate asc limit 5;组合起来就是完整的语句了。执行顺序是 from->where->group by->select->having->order by->limit.

1
select * from emp where gender = '男' and age between 20 and 40 order by age asc ,endrydate asc limit 5

DCL

Data Control Languages 数据控制语言

概念：用来管理数据库用户、控制数据库的访问权限

管理用户

  1. 查询用户
1
2
3
-- 查询用户
USE MYSQL;
select * from mysql.user;

005_DCL_01.png

其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的(用于远程访问的一般是Linux上的MySQL)。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户

  1. 创建用户

语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 

1
2
-- 创建用户，只能够在当前主机上访问(localhost)
create user 'jerry'@'localhost' identified by '123456';
1
2
-- 创建用户，可以在任意主机上访问,主要是在虚拟机上使用的较多
create user 'tom'@'%' identified by '123456';
  1. 修改用户密码

语法：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ; 

1
2
-- 修改用户密码
alter user 'jerry'@'localhost' identified with mysql_native_password by '1234';
  1. 删除用户
1
2
3
-- 删除用户
drop user 'jerry'@'localhost';
drop user 'tom'@'%';
  1. 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户
  2. 主机名可以使用 % 通配

权限控制

分类

权限 说明
ALL, ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表

具体的权限描述和其他的请查看官方网站https://dev.mysql.com；不会就找中文版的文档吧(ㆆᴗㆆ)

  1. 查询权限

语法：SHOW GRANTS FOR '用户名'@'主机名' ; 

1
2
-- 查询权限
show grants for 'jerry'@'localhost';

005_DCL_02.png

  1. 授予权限

很重要，远程登录的时候无法增删查改很大程度上都是因为没有授予相应的权限。

语法：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; 

1
2
-- 授予权限 将test1数据库的所有表对jerry用户开放 *代表所有
grant all on test1.* to 'jerry'@'localhost';

005_DCL_03.png

  1. 撤销权限

语法：REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'; 

1
2
-- 撤销权限
revoke all on  test1.* from 'jerry'@'localhost';
  1. 多个权限之间用逗号连接
  2. 授权时数据库名和表名可以使用 * 进行通配，代表所有。

函数

字符串函数

常见的字符串函数如下

函数 功能
CONCAT(S1,S2,…Sn) 字符串拼接，将S1，S2，… Sn拼接成一个字符串
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符 串长度
RPAD(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符 串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串 
1
2
-- 字符串拼接
select concat('hello','mysql');

006_函数_01.png

1
2
3
-- 大小写转换
select lower('HELLO');
select upper('hello');
1
2
3
-- 左填充与右填充 常用于日期、编号等的数据
select lpad('01',5,'-');
select rpad('01',5,'-');

006_函数_02.png

1
2
3
4
-- 去除空格
select trim(' Hello MySQL ');
-- 截取字符串 注意索引从1开始
select substring('hello mysql',1,5);

006_函数_03.png

数值函数

函数 功能
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回x/y的模
RAND() 返回0~1内的随机数
ROUND(x,y) 求参数x的四舍五入的值，保留y位小数 
1
2
3
4
5
6
7
8
9
10
11
-- 数值函数 就是编程语言中常见的函数
-- 向上取整，
select ceil(1.1);-- 结果为2
-- 向下取整-即保留整数位
select floor(1.9);
-- 取模
select mod(3,2);
-- 随机数 不写默认是0-1的数
select rand();
-- 四舍五入+保留小数位数
select round(2.35,2);

生成一个六位数的随机验证码

1
2
-- rand函数生成0-1的随机小数
select lpad(round(rand()*1000000,0),6,'0');

006_函数_04.png

日期函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的 时间值
DATEDIFF(date1,date2) 返回起始时间date1 和 结束时间date2之间的天 数

示例

1
2
3
4
5
6
7
8
9
10
11
-- 英语中date表示日期，time表示具体的时间
-- 当前日期
select curdate();
-- 当前时间
select curtime();
-- 当前日期+时间
select now();
-- 年月日函数
select day(now());
-- “增加”时间
select date_add(now(),interval 70 day); # 这里的意思是在当前时间+70天

两时间之差：第一个参数-第二个参数

1
2
-- 求2021-07-21与1921-07-21间隔的天数
select datediff('2021-07-21','1921-07-21');
1
2
-- 查询所有员工的入职天数，并根据入职天数倒序排序
select first_name,datediff(curdate(),hire_date) as 'entrydays' from emps order by entrydays desc;

006_函数_05.png

流程函数

就是高级语言中的控制函数

函数 功能
IF(value , t , f) 如果value为true，则返回t，否则返回 f
IFNULL(value1 , value2) 如果value1不为空，返回value1，否则 返回value2
CASE WHEN [ val1 ] THEN [res1] … ELSE [ default ] END 如果val1为true，返回res1，… 否 则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END 如果expr的值等于val1，返回 res1，… 否则返回default默认值 
1
2
3
4
5
6
7
8
9
-- 流程控制函数 就是编程语言中的控制函数
select if(true,'OK','error');
-- 判断是否为null
select ifnull(null,'default');
-- 多选择分支
select
    name,
    (case note when 'novel' then '小说' when 'drama' then '戏剧' else '其他类型'end) as '图书表'
from books;

006_函数_06.png

1
2
3
4
5
-- 案例 按照不同的工资范围给予对应员工评价
select
    first_name,
    (case when salary >=10000 then '优秀' when salary >=6000 then '还行' else '加把劲' end) '工资表'
from emps;

006_函数_07.png

约束(constraint)

一般约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT
检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接，保证数据的一致 性和完整性 FOREIGN KEY

在创建表过程或者已经创建均可以添加约束

1
2
3
4
5
6
7
8
9
10
11
12
-- 案例
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 '用户表';
-- 插入数据 主键自增不需要添加数据
-- 同理可以在图形化界面添加对应的约束
insert into user(name,age,status,gender) value ('Tom1','19','1','男'),('Tom2','25','0','男');
insert into user(name,age,status,gender) value (null,'19','1','男');

第12行代码异常执行，不符合约束的数据不会添加

007_约束_01.png

外键约束

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

  1. 添加外键

语法

1
2
3
4
5
6
-- 在创建表的时候添加
CREATE TABLE 表名(
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
)
1
2
-- 在修改表的时候添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 让两张表的数据之间建立联系，从而保证数据一致
-- 具有外键的表成为子表，而关联的表称为父表
-- 准备数据
create table dept(
                     id   int auto_increment comment 'ID' primary key,
                     name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');


create table emp(
                    id  int auto_increment comment 'ID' primary key,
                    name varchar(50) not null comment '姓名',
                    age  int comment '年龄',
                    job varchar(20) comment '职位',
                    salary int comment '薪资',
                    entrydate date comment '入职时间',
                    managerid int comment '直属领导ID',
                    dept_id int comment '部门ID'
)comment '员工表';

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
                                                                               (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
                                                                               (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
                                                                               (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);                                                                               
1
2
3
-- 添加外键
-- 为emp表中的dept_id字段添加外键约束，关联dept表中的主键id，并将外键命名为fk_emp_dept_id
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

007_约束_02.png

  1. 删除外键

语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; 

1
2
-- 删除外键
alter table emp drop foreign key fk_emp_dept_id;
  1. 删除/更新行为

概念：添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为
分类

行为 说明
NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为
RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则 也删除/更新外键在子表中的记录。
SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表 中该外键值为null（这就要求该外键允许取null）。
SET DEFAULT 父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)

语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE; 

1
2
3
4
-- 外键更新 父表更新时子表也会更新
-- on update 后面跟cascade 表明在更新的时候首先检查该记录是否有对应外键，如果有，则更新外键在子表中的记录。
-- on delete 后面跟cascade 表明在删除的时候首先检查该记录是否有对应外键，如果有，则删除外键在子表中的记录。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

在一般的业务系统中，一般不会 修改一张表的主键值

多表查询

概念:从多张表中查询数据

多表关系

分类

  • 一对多（多对一）
  • 多对多
  • 一对一

示例：多对多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 表之间的联系，一对一，一对多(多对一),多对多
-- 一对多(一个部门对应多个员工，一个员工对应一个部门)
-- 多对多(比如学生与课程)
-- 一对一常用于单表拆分

-- 多对多演示
create table student(
                        id int auto_increment primary key comment '主键ID',
                        name varchar(10) comment '姓名',
                        no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');


create table course(
                       id int auto_increment primary key comment '主键ID',
                       name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');


create table student_course(
                               id int auto_increment comment '主键' primary key,
                               studentid int not null comment '学生ID',
                               courseid  int not null comment '课程ID',
                               constraint fk_courseid foreign key (courseid) references course (id),
                               constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';

008_多表查询_01.png

右键表-‘图表’-‘显示可视化’

008_多表查询_02.png

多表查询

1
2
-- 多表查询
select * from test1.emp,dept;-- 这样写出来的多表查询是笛卡尔积，考过计算机二级的同学应该学过

简单来说，就是集合A与集合B的所有组合情况,显然这不是我们想要呈现的数据，消除笛卡尔积的方法就是让多个表以某种关系式连接起来(比如id相等)

笛卡尔积部分演示

008_多表查询_03.png

数学图演示

008_多表查询_04.png

而我们不需要所有的组合情况，只需要按照一定的条件将多余的组合情况排除即可。

1
2
-- 消除笛卡尔积
select * from test1.emp,dept where emp.dept_id = dept.id;

消除笛卡尔积之后

008_多表查询_05.png

连接分类

内连接

补图：用chartjs以韦恩图的形式展现连接

概念：就是求两张表的交集

  1. 隐式内连接

语法：SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ; 

1
2
3
4
5
6
-- 内、外、自连接(和集合中的概念相似)，子查询
-- 内连接-查找两个表之间的交集
-- 隐式内连接(没有直接写inner join) 从两张表中找到id相同的并展示其在不同表的名字
select emp.name,dept.name from test1.emp,dept where emp.dept_id = dept.id;
-- 可以取别名缩短语句长度
select e.name,d.name from emp e,dept d where e.dept_id = d.id;

008_多表查询_06.png

  1. 显式内连接

语法：SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ; 

1
2
-- 显示内连接,就是通过inner join实现
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

表的别名语法

  1. tablea as 别名1 , tableb as 别名2;
  2. tablea 别名1 , tableb 别名2;

注意：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接

就是两表的交集+其中一个表,比如

  1. 左外连接 A+AUB

语法：SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ; 就是关键字left join

1
2
-- 左外连接-相当于表1+表1与表2的交集，右连接同理
select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;

008_多表查询_07.png

  1. 右外连接 AUB+B

语法：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ; 就是关键字right join

1
2
-- 右外连接
select d.*,e.* from emp e right outer join dept d on e.dept_id = d.id;

008_多表查询_08.png

左右外连接理论上是可以相互替换和相互转换的，不过一般左外连接用的多。

自连接

将表自身看做是另一张表，然后将两表连接
语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

自连接可以是内连接也可以是外连接

示意图

008_多表查询_09.png

1
2
3
-- 其实就是把自己看做是两张表进行连接 不过需要取别名否则会产生歧义
select a.name,b.name from emp a,emp b where a.managerid = b.id;
select a.name,b.name from emp a left join emp b on a.managerid = b.id;

008_多表查询_10.png

008_多表查询_11.png

在自连接查询时必须取别名，否则会有歧义导致无法运行

联合查询

概念：就是把多次查询的结果合并起来，形成一个新的查询结果集

语法：

1
2
3
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
  1. 联合的表列数和字段类型必须保持一致
  2. 用all会将所有的数据合在一起，而不用all就会去重
1
2
3
4
5
6
7
-- 联合查询
-- 相当于将查询得到的两张表上下拼起来
select * from emp where salary >6000
union all
select * from emp where age < 50;
-- 如果要去重，就不写all
-- 注意：查询的多张表列数要一致，字段类型要保持一致

008_多表查询_12.png

这里的联合查询就是将两张表上下拼接的

子查询

概念：就是sql语句中嵌套select语句，也就是将select查询出的结果作为另一个sql语句的参数

示例：SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

  1. 标量子查询（子查询结果为单个值）

常见的操作符：= <> > >= < <= 

1
2
3
4
5
6
-- 子查询-又称嵌套查询
-- 把表看做是矩阵，就容易理解查询的结果
-- 标量子查询
-- select id from dept where name = '研发部'查询结果只有一个，称为标量子查询
select * from emp where dept_id = (select id from dept where name = '总经办');
select * from emp where entrydate > (select entrydate from emp where name = '杨逍');

008_多表查询_13.png

  1. 列子查询(子查询结果为一列)

操作符

操作符 描述
IN 在指定的集合范围之内，多选一
NOT IN 不在指定的集合范围之内
<> 不等于
ANY 子查询返回列表中，有任意一个满足即可
SOME 与ANY等同，使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足 
1
2
3
4
5
6
7
-- 列查询
select id from dept where name  = '销售部' or name = '研发部';
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- 比研发部所有人工资还要高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '研发部'));
-- 比研发部任意一个人工资高的员工信息,用any和some都行
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

008_多表查询_14.png

008_多表查询_15.png

  1. 行子查询(子查询结果为一行)
1
2
-- 其实就是查询出来的多个字段作为另一个查询语句的条件
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '韦一笑');

008_多表查询_16.png

  1. 表子查询(子查询结果为多行多列)

关键字：in

1
2
-- 将查询出来的表作为范围继续查找
select * from emp where (job,salary) in (select job,salary from emp where name = '张无忌' or name = '韦一笑');

008_多表查询_17.png

事务

概念：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。 常用于银行转账、贸易等。

注意：默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。 用git来比喻的话就是当你执行一段DML语句之后，会将数据“add”，而由于MySQL默认提交事务，“add“的同时又”commit“了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 一组操作的集合，“同患难共享福”，
-- 数据准备
-- 数据准备
create table account(
                        id int auto_increment primary key comment '主键ID',
                        name varchar(10) comment '姓名',
                        money int comment '余额'
) comment '账户表';
insert into account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);
-- 转账事务
-- 设置事务为自动提交 在MySQL中事务是自动提交的
select @@autocommit;
-- 设置事务为手动提交
set @@autocommit = 0;
-- 转账操作 (张三给李四转账1000)
-- 1. 查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- 提交事务
-- 也就是将事务放在set和commit语句之间(事务为手动提交时);
commit;
-- 回滚事务 
rollback;
-- 恢复数据
update account set money = 2000 where name = '张三' or name = '李四';

事务正常运行的情况

009_事务_01.png

009_事务_02.png

事务异常执行的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 第二种创建事务的方法
-- 转账操作 (张三给李四转账1000)
start transaction ;
-- 1. 查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

程序执行报错 ...

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- 提交事务
commit;
-- 回滚事务
rollback;

009_事务_03.png

009_事务_04.png

控制事务

  1. 开启事务

语法：START TRANSACTION 或 BEGIN ;

  1. 查看/设置事务的提交方式

语法：SELECT @@autocommit ; 自动提交，或者是SET @@autocommit = 0 ;手动提交

  1. 提交事务

语法：COMIT 如果是自动提交就不需要写,如果更改为手动提交，那么后面的sql语句要想修改数据必须运行此代码。

  1. 回滚事务

语法：ROLLBACK;

事务的特性

四大特性

  1. 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

  2. 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

  3. 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

  4. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性，简称ACID。

并发事务问题

补充示意图

  1. 脏读：一个事务读到另外一个事务还没有提交的数据
  2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
  3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

事务隔离级别

隔离级别 脏读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable Read(默认) × × √
Serializable × × ×
  1. 查看事务隔离级别

语法：SELECT @@TRANSACTION_ISOLATION;

009_事务_05.png

  1. 设置事务隔离级别

语法：SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE } 

1
2
-- 设置事务隔离级别
set session transaction isolation level read uncommitted;

009_事务_06.png

事务隔离级别越高，数据越安全，但是性能越低。

至此基础篇完结

学习完之后可以参考此进阶篇学习

  1. MySQL学习笔记(一)基础篇
  2. MySQL学习笔记(二)进阶篇

参考

视频教程

  1. 黑马程序员
  2. 尚硅谷

笔记参考

  1. 在浏览器上搜索“黑马程序员”进入官网，然后点击免费教程-“搜索mysql”，下载对应文件
  2. MySQL学习笔记 | 智云知识