MySQL-1

前提环境

版本：MySQL 8.0

本机服务名称：MySQL8 ( mysql8 )

注意事项："服务名称"可能有所不同，具体请在 cmd 窗口中输入services.msc 查询自己的 MySQL 服务名称.

启动与停止

启动 MySQL 数据库的"服务端"

net start mysql8
1

停止 MySQL 数据库

net stop mysql8
1

连接到服务端

将 MySQL 提供的客户端，连接 MySQL 服务端

配置PATH环境变量，让任意路径的cmd窗口，都能检测并执行 mysql... 这些命令
1

下面命令中，最后一个 -p 的含义是密码

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

SQL通用语法

• SQL 语句可以单行或多行书写，以分号结尾
• SQL 语句可以使用空格/缩进来增加语句的可读性
• MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
• 注释：

单行注释：
	-- 注释内容
	# 注释内容（MySQL独有）
	
多行注释：
	/* 注释内容 */
1
2
3
4
5
6

SQL分类

数据类型

查阅文档：https://www.runoob.com/mysql/mysql-data-types.html

运用举例：

设计一张员工信息表，要求如下：
	1.编号（纯数字）
	2.员工工号（可变长字符串，长度不超过10）
	3.员工姓名（可变长字符串，长度不超过10）
	4.性别（定长字符串，存储一个汉字）
	5.年龄（无符号，微小整型）
	6.身份证号（18位）
	7.入职时间（取值年月日即可）
1
2
3
4
5
6
7
8

create table employee(
	id int,
	jobnum varchar(10),
	name varchar(10),
	gender char(1),
	age tinyint unsigned,
	idcard char(18),
	entrydate date
);
1
2
3
4
5
6
7
8
9

数据库操作

查询

查询所有数据库

show databases;
1

查询当前数据库

select database();
1

创建

create database [if not exists] 数据库名 [default charset utf8mb4] [collate 排序规则];
1

删除

drop database [if exists] 数据库名;
1

使用

use 数据库名;
1

表操作

查询表

查询当前数据库所有表

show tables;
1

查询表结构

desc 表名;
1

查询指定表的建表语句

show create table 表名;
1

创建表

create table 表名(
	字段1 类型[comment 字段注释],
	字段2 类型[comment 字段注释],
	字段3 类型[comment 字段注释],
	...
	字段n 类型[comment 字段注释]
)[comment 表注释]
1
2
3
4
5
6
7

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

改表名

alter table 旧表名 rename to 新表名;
1

删除表

删除表

drop table if exists 表名;
1

删除指定表，并重新创建该表

truncate table 表名;
1

注意：在删除表时，表中的全部数据也会被删除

添加字段

alter table 表名 add 字段名 类型 [comment 注释]
1

alter table tb_user add hobby varchar(20);
1

修改字段

修改数据类型

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

修改字段名和字段类型

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

删除字段

alter table 表名 drop 字段名;
1

数据操作

添加数据

给指定的字段添加数据

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

给全部的字段添加数据

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

批量添加数据

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

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

注意：字符串和日期型数据应该包含在引号中。

修改数据

update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];
1

update user set name = 'Peter' where id = 1;
1

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据

delete from 表名 [where 条件]
1

注意：

• delete 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• delete 语句不能删除某一个字段的值（可以使用 update ）

查询数据

基本查询

查询多个字段

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

select * from 表名;
1

设置别名

select 字段1 [as 别名1], 字段2 [as 别名2] ... from 表名;
1

select workaddress as '工作地址' from employee;
1

去除重复记录

select distinct 字段列表 from 表名;
1

select distinct workaddress as '工作地址' from employee;
1

条件查询

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

聚合函数

概念：将一列数据作为一个整体，进行纵向计算。

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

select count(id) from user;
1

注意：null 值是不参与聚合函数运算的

分组查询

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

where 与 having 区别：

• 执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
• 判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

/* 根据性别分组，统计男性员工和女性员工的数量 */
select gender, count(*) from employee group by gender;
1
2

/* 根据性别分组，统计男性员工和女性员工的平均年龄 */
select gender, avg(age) from employee group by gender;
1
2

/* 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址 */
select workaddress, count(*) from employee where age < 45 group by workaddress having count(*) >= 3;
1
2

排序查询

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

排序方式：

• ASC：升序（默认值）
• DESC：降序

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

分页查询

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

编写顺序

执行顺序

权限控制

管理用户

查询用户

use mysql;
select * from user;
1
2

创建用户

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

创建一个用户 peter，可以在任意主机访问该数据库，密码 123456：
create user 'peter'@'%' identified by '123456';
1
2

修改用户密码

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

alter user 'peter'@'%' identified with mysql_native_password by '654321';
1

删除用户

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

drop user 'peter'@'%'
1

分配权限

查询权限

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

授予权限

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

grant all on user.* to 'peter'@'localhost';
1

撤销权限

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

revoke all on user.* from 'peter'@'localhost';
1

函数

字符串函数

/* 字符串拼接 */
select concat('Hello', 'World');
1
2

/* 全部转为大写 */
select upper('hello');
1
2

/* 将名字右填充‘_’号至10位 */
update user set name = rpad(name, 10, '_');
1
2

数值函数

/* 保留两位小数 */
select round(3.14159, 2);
1
2

/* 随机小数 */
select rand()
1
2

日期函数

select now();
1

流程控制

select if(true, 'ok', 'error')
1

select
	name,
	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from user;
1
2
3
4

约束

常见的约束

约束演示

外键约束

添加外键

create table 表名(
	字段名 数据类型,
	...
	[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名);
);
1
2
3
4
5

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

删除与更新

/* 外键数据的同步更新与同步删除 */
alter table 表名 constraint 外键名称 foreign (外键字段) references 主表名 (主表字段名) on update cascade on delete cascade;
1
2

/* 更新与删除时，外键数据变为 null */
alter table 表名 constraint 外键名称 foreign (外键字段) references 主表名 (主表字段名) on update set null on delete set null;
1
2

多表查询

内连接

隐式内连接

select 字段列表 from 表1, 表2 where 条件...;
1

select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
1

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
1

select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;
1

外连接

左外连接（相当于查询表A的所有数据，包含表A和表B交集部分的数据）

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

右外连接（相当于查询表B的所有数据，包含表A和表B交集部分的数据）

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

自连接

select 字段列表 from 表A 别名A join 表B on 条件...;
1

select a.name, b.name from emp a, emp b where a.managerid = b.id;
1

联合查询

对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;
1
2
3

子查询

SQL 语句中嵌套 select 语句，称为嵌套查询，又称子查询

select * from t1 where column1 = (select column1 from t2);
1

子查询外部的语句可以是 insert / update / delete / select
1

标量子查询

查询"销售部"的所有员工信息
	a.查询销售部的部门ID
		select id from dept where name = '销售部';
		
	b.根据销售部的部门ID，查询员工信息
		select * from emp where dept_id = 4;

合并为一步：
	select * from emp where dept_id = (select id from dept where name = '销售部');
1
2
3
4
5
6
7
8
9

列子查询

查询"销售部"和"市场部"的所有员工信息
	a.查询销售部和市场部的部门ID
		select id from dept where name= '销售部' or name = '市场部';
	
	b.根据部门ID，查询员工信息
		select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
1
2
3
4
5
6

行子查询

查询与"小张"的薪资及直属领导相同的员工信息
	a.查询小张的薪资及直属领导
		select salary, managerid from emp where name = '小张';
		
	b.查询小张的薪资及直属领导相同的员工信息
	select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '小张');
1
2
3
4
5
6

表子查询

查询"小华"和"小明"的职位和薪资相同的员工信息
	a.查询小华、小明的职位和薪资
		select job, salary from emp where name = '小华' or name = '小明';
	
	b.查询"小华"和"小明"的职位和薪资相同的员工信息
		select * from emp where (job, salary) in (select job, salary from emp where name = '小华' or name = '小明');
1
2
3
4
5
6

事务

基本概念

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务操作

方式一：

• MySQL 默认每一句 SQL 命令都是自动提交事务，我们可以关闭事务的自动提交，全部手动提交。

/* 查看事务的提交方式 */
select @@autocommit;

/* 设置事务为手动提交 */
set @@autocommit = 0;

/* 设置事务为自动提交 */
set @@autocommit = 1;

/* 提交事务 */
commit;

/* 回滚事务 */
rollback;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

方式二：( 推荐使用 )

/* 开启事务 */
start transaction 或 begin;

/* 提交事务 */
commit;

/* 回滚事务 */
rollback;
1
2
3
4
5
6
7
8

并发事务问题

事务的隔离级别

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

下表中的隔离级别，由上到下，级别逐渐增加。

/* 查看事务的隔离级别 */
select @@transaction_isolation;
1
2

/* 设置事务隔离级别 */
set [ session | global ] transaction isolation level { read uncommitted | read committed | repeatable read | serializable };
1
2

set session transaction isolation level read uncommitted;
1