MySQL基础笔记

1. SQL

1.1 SQL-DDL语句

数据定义语言

1.1.1 数据库操作

# 连接 mysql
mysql -u username -p

# 查看数据库
show dtatbases;

# 创建数据库
create database if not exists db_name;

# 选择数据库
use db_name;

# 查看当前所属数据库
select database();

# 删除数据库
drop databse if exists db_name;

1.1.2 表操作

# 查看当前数据库所有的表
show tables;

# 创建表
create table tb_name (字段 字段类型, 字段 字段类型);

# 查看当前表的字段
desc tb_name

# 查看此表的建表语句
show create table tb_name;

# 添加字段/修改字段类型/修改字段名称及类型/删除字段/修改表名
alter table tb_name add/modify/change/drop/rename to ...;

# 删除表
drop table tb_name;

1.2 MySQL-DML语句

• 数据操作语言

# 添加数据
insert into tb_name (column1, column2,...) values (value1, value2,...); # 指定字段
insert into tb_name values (value1, value2,...); # 所有字段

# 修改数据
update tb_name set column1 = value1, column2 = value2, ... where condition;

# 删除参数
delete from tb_name where condition;

1.3 MySQL-DQL语句

– 数据查询语言

1.3.1 基本查询

# 查询多个字段
select 字段1,字段2,... frmo tb_name;
select * from tb_name;

# 设置别名
select 字段1 as zd1, 字段2 as zd2 from tb_name; # as可以省略

# 去除重复记录
select distinct 字段列表 from tb_name;

1.3.2 条件查询

• 语法

select 字段列表 from tb_name where 条件列表;

• 条件部分
  

1.3.3 聚合函数

将一列数据作为一个整体, 进行纵向计算

• 语法

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

• 函数
  

null值不参与聚合函数计算

1.3.4 分组查询

• 语法

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

where与having的区别:

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

# 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select address, count(*) add_num from emp 
where age < 45 group by address 
having add_num >= 3

注意事项:
? 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
? 执行顺序: where > 聚合函数 > having 。
? 支持多字段分组, 具体语法为 : group by columnA,columnB

1.3.5 排序查询

• 语法

# 多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
select 字段列表 from 表名 order by 字段1 排序方式1 , 字段2 排序方式2 ;

• 排序方式
  • asc 升序(默认值)
  • desc 降序

1.3.6 分页查询

• 语法

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

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

1.4 MySQL-DCL语句

数据控制语言: 管理数据库用户、控制数据库的访问权限

1.4.1 管理用户

# 查询用户
use mysql;
select *from user;

# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

# 修改密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

# 删除用户
DROP USER '用户名'@'主机名';

注意事项:
? 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
? 主机名可以使用 % 通配。
? 这类SQL开发人员操作的比较少，主要是DBA使用

1.4.2 权限控制

# 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;

# 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

# 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

2. 函数

一段可以直接被另一段程序调用的程序和代码

2.1 字符串函数

• 示例

# 由于业务需求变更，企业员工的工号，统一为5位数
# 目前不足5位数的全部在前面补0
update emp set workno = lpad(workno, 5, '0');

2.2 数值函数

2.3 日期函数

• 示例

# 查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as 'entrydays' 
from emp 
order by entrydays desc;

2.4 流程函数

# case when then else end
# 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select
	name,
	(case workaddress when '北京' then '一线城市' 
	when '上海' then '一线城市' 
	else'二线城市' end) 
	as '工作地址'
from emp;

# 查询成绩返回优秀 及格 不及格
select
id,
name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )
'数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'
end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'
end ) '语文'
from score;

3. 约束

3.1 概述

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2. 目的：保证数据库中数据的正确、有效性和完整性。
3. 分类：
   

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

3.2 约束演示

# 对应的sql语句
CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
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 '性别'
);

3.3 外键约束

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

如图，只存在逻辑外键，删除部门后，对应部门的员工还在。

• 语法

# 1. 创建表时直接添加
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

# 2. 表结构创建好 额外增加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名); # 关联的主表


# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

# 新增外键 尝试删除部门时 如有员工 则不能删除（默认行为 no action）
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);

# 删除外键
alter table emp drop foreign key fk_emp_dept_id

• 删除/更新行为

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

4. 多表查询

4.1 多表关系

• 一对多
  • 案例: 部门 与 员工的关系
  • 关系: 一个部门对应多个员工，一个员工对应一个部门
  • 实现: 在多的一方建立外键，指向一的一方的主键

---

• 多对多
  • 案例: 学生 与 课程的关系
  • 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

---

• 一对一
  • 案例: 用户 与 用户详情的关系
  • 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
  • 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

4.2 多表查询

• 概述: 从多张表中查询数据
• 笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况

多表查询就是在剔除无效的笛卡尔积

• 分类:
  • 连接查询
    • 内连接：相当于查询A、B交集部分数据
    • 外连接
      • 左外连接：查询左表所有数据，以及两张表交集部分数据
      • 右外连接：查询右表所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表别名
  • 子查询

4.3 内连接

查询A、B交集部分数据

内连接的语法分为两种: 隐式内连接、显式内连接

# 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ... ;

# 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

# 查询每一个员工的姓名 , 及关联的部门的名称 
# 隐式内连接实现
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
select e.name,d.name from emp e , dept d where e.dept_id = d.id; # 别名简化

# 显式内连接实现
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name, d.name from emp e join dept d on e.dept_id = d.id; # inner省略 别名简化

4.4 外连接

• 左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据

• 右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据

注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

4.5 自连接

4.5.1 自连接查询

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

注意事项：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

# 查询员工 及其 所属领导的名字(内连接)
select a.name , b.name from emp a , emp b where a.managerid = b.id;

# 查询所有员工emp及其领导的名字emp, 如果员工没有领导, 也需要查询出来(外连接)
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =
b.id;

4.5.2 联合查询

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

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

• 对于联合查询返回的多张表的列数必须保持一致，字段类型也需要保持一致
• union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

4.6 子查询

4.6.1 概述

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

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

根据子查询结果不同, 分为:

• 标量 子查询(子查询结果为单个值)
• 列 子查询(子查询结果为一列)
• 行 子查询(子查询结果为一行)
• 表 子查询(子查询结果为多行多列)

根据子查询位置不同, 分为:

• where之后
• from之后
• select之后

4.6.2 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询

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

# 查询 "销售部" 的所有员工信息
select id from dept where name = '销售部'; # 返回结果是单个值

select * from emp where dept_id = (select id from dept where name = '销售部');

4.6.3 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

# 查询 "销售部" 和 "市场部" 的所有员工信息
select id from dept where name = '销售部' or name = '市场部';

select * from emp where dept_id in 
(select id from dept where name = '销售部' or name = '市场部');

4.6.4 行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

常用的操作符：= 、<> 、IN 、NOT IN

# 查询与 "张无忌" 的薪资及直属领导相同的员工信息
select salary, managerid from emp where name = '张无忌';

select * from emp where (salary, managerid) = (select salary, managerid from emp
where name = '张无忌');

4.6.5 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用的操作符：IN

# 查询与 "鹿杖客", "宋远桥"的职位和薪资相同的员工信息
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

select * from emp where (job,salary) in 
( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

5. 事务

5.1 事务简介

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

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务

5.2 事务操作

5.2.1 控制事务一

# 查看/设置事务提交方式
SELECT @@autocommit;

SET @@autocommit = 0;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交

5.2.2 控制事务二

# 开启事务
START TRANSACTION 或 BEGIN;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

5.3 事务特性

是事务的四大特性，简称=ACID

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

5.4 并发事务问题

(1) 赃读：一个事务读到另外一个事务还没有提交的数据

比如B读取到了A未提交的数据

(2) 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

(3) 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”

5.5 事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：读–未提交、读–提交、可重复读、串行化

# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

# 设置事务隔离级别
# 会话/全局级别
SET [ SESSION | GLOBAL ] 
TRANSACTION ISOLATION LEVEL 
{ 
READ UNCOMMITTED | 
READ COMMITTED | 
REPEATABLE READ |
SERIALIZABLE 
} # 四选一

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