完成ENGINEERING NOTE

MySQL

整理 SQL 分类、DDL、DML、DQL、DCL 以及 MySQL 数据库、表和查询语法的基础笔记。

后端MySQL

SQL简介

SQL是一门操作型数据库的编程语言，定义操作所有关系型数据库的统一标准。

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格或缩进来增加可读性。
MySQL数据库的SQL语句不区分大小写。
注释：– 注释内容或/**/ SQL分为四类：

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库表中的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户，控制数据库的访问权限

## **SQL语法** ### **DDL：Data Definition Language** ### 增 - 创建数据库： ```plain text create database name; ``` - 删除数据库： ```plain text drop database name; ``` - 创建表基本形式： ```plain text create table tableName { 字段字段类型 [约束] [commont 字段注释] } comment 表注释 ``` 实例： ```plain text create table tb_user( id int primary key comment 'ID，唯一标识', username varchar(20) comment '用户名', name varchar(10) comment '姓名', age int comment '年龄', gender char(1) comment '性别' ) comment '用户信息'; ``` 约束共有五种：

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一，不重复的	unique
主键约束	主键是一行的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致和完整性	foreign key

可以使用auto_increment让主键自动增长。 ### 查 - 查表：`showtables` 查看当前数据库下的表 - 查看表元素：`desc table_name` 查看表中的元素信息 - 查看建表语句：`shwo create table table_name` ### 改 - 添加字段：`alter table table_name add colunm_name type(lenth) comment constraint` - 修改字段类型：`alter table table_name modify colunm_name new_type(lenth)` - 修改字段名和字段类型：`alter table table_name change old_colunm_name new_colunm_name new_type(lenth) comment constraint` - 删除字段：`alter table table_name drop colunm colunm_name` - 修改表名：`rename table table_name to new_table_name` ### **DML: Data Manipulation Language** DML语言时一种对数据库的数据记录进行增删改的操作。 ### Insert插入 - 插入指定字段的值： now()函数可以返回当前系统时间。 ```plain text insert into tb_emp(username, name, gender, create_time, update_time) values('KringKoter', '克林沃特', 1, now(), now()); ``` - 插入全部字段的值： ```plain text insert into tb_emp values('AfternoonTea2', null, '阿伏特罗', 123, 1, null, 1, null, now(), now()); ``` - 批量插入数据： ```plain text insert into tb_emp(username, name, create_time, update_time) values('Kring', '克林', now(), now()), ('Koter', '沃特', now(), now()); ``` ### Update更新 - 更新单个字段的值： ```plain text update tb_emp set name = '牛魔', update_time = now() where id = 1; ``` where后是更新条件，只有满足条件才会更新。 - 更新所有字段的值：不加where，通常会出警告。 ### Delete删除 - 删除单条数据： ```plain text delete from tb_emp where id = 1 ``` - 删除全部数据： ```plain text delete from tb_emp; ``` *delete不能删除单条字段的值，若需要此操作，可使用update将该字段设置为null。* ### **DQL：Data Query Language** 关键字select ### 基本查询 ```plain text #查找多个字段 select name, username from tb_emp;

#查找全部字段（不推荐 select * from tb_emp

#设置别名 select name as ‘姓名’, username as ‘用户名’ from tb_emp;

#去除重复记录 select distinct gender from tb_emp;

### 条件查询
```plain text
select colunm_list from table_name where condition_list

基本条件列表如下：

运算符	功能
\>, \>=, \<, \<=, =	大于，大于等于，小于，小于等于，等于
!=	不等于
between ... and ...	在某个范围内，闭区间
in(...)	在括号内的值，多选一
like _/%	模糊匹配，_匹配单个字符，%匹配任意字符
is null	是否为空
&&， \\|\\|	并/且
!	非

### 聚合函数聚合函数是将一列数据作为一个整体，进行纵向计算的函数。语法：`select 聚合函数(字段列表) from 表名`; 以下是聚合函数的类型：

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

### 分组查询语法： ```plain text select 字段列表 from 表名 [where 外筛选条件] group by 分组字段名 [having 分组后筛选条件] ``` 字段列表无法单独为表中的字段，需要搭配聚合函数使用，无法是单独的\* ### 排序查询语法： ```plain text select 字段列表 from 表名 [where 条件] group by 分组字段名 order by 字段1 排序方式1，字段2 排序方式2 ``` 升序：asc，降序：desc ### 分页查询语法： ```plain text select 字段列表 from 表名 limit 起始索引，查询记录数; ``` ## **多表关系与操作** 多表关系分为：一对一，一对多，多对多。 - 一对多：在多的一方添加外键。 - 多对多：创建一个中间表，中间表中有两方的外键。 ### **多表查询** ### 概述当需要从多张表中查询数据并合并时，需要用到多表查询。当查询两张表时，各元素会按照笛卡尔积进行排列。可以使用where来排序不需要的笛卡尔积。 ```plain text select * from tb_emp, tb_pst where tb_emp.job = tb_pst.id; ``` 多表查询分为内连接，外连接和子链接。内连接查询AB交集部分数据。外连接分为左外连接与右外连接，左外连接查询左表所有数据与两张表交集部分数据，右外连接查询油表有关数据与交集部分数据。 ### 内连接内连接无法查询两表没有关联的部分。内连接的查询方法有两种，分别为隐式内连接与显示内连接： - 隐式`select 字段列表 from 表1，表2 where 条件` 示例： ```plain text select tb_emp.name, tb_pst.pst_name from tb_emp, tb_pst where tb_emp.job = tb_pst.id; ``` - 显式`select 字段列表 from 表1 inner join 表2 on 条件` 示例： ```plain text select tb_emp.name, tb_pst.pst_name from tb_emp inner join tb_pst on tb_emp.job = tb_pst.id; ``` 可以给表起别名以简化代码： ```plain text select * from tb_emp e, tb_pst p where e.job = p.id; ``` ### 外连接外连接包含左外连接和右外连接。左外连接会完全包含左表的数据，即使数据与右表没有关系。右外连接同理。 - 左外连接：`select * from tb1 left outer join tb2 on conditions` 示例： ```plain text select * from tb_emp left outer join tb_pst on tb_emp.job = tb_pst.id; ``` - 右外连接：`select * from tb1 right outer join tb2 on conditions` 示例： ```plain text select * from tb_emp right join tb_pst tp on tp.id = tb_emp.job; ``` ### 子查询 - 标量查询：查询一个单行单列的值。 ```plain text # 例如，查询tb_emp表中，职位为管理员的员工 select tb_emp.name from tb_emp where job = (select id from tb_pst where tb_pst.pst_name = '管理员');

查询在‘子墨’之后入职的员工

select tb_emp.name from tb_emp where entrydate > (select entrydate from tb_emp where name = ‘子墨’);

- 列子查询：查询一列（可以多行）的值
```plain text
# 查询‘管理员’和'仓储人员'的所有人员信息
select * from tb_emp where job in (select id from tb_pst where pst_name = '管理员' or pst_name = '仓储人员');

需要注意必须使用表示范围的in操作符（而不是等于）

案例

# 需求：
# 1.查询价格低于200信用点的 菜品名称，价格 和 菜品的分类名称
# 显式内连接方法
select drinks.name, drinks.price, category.name
from drinks
         inner join category on drinks.category_id = category.id
where drinks.price < 200;

# 隐式内连接方法
select drinks.name, category.name, drinks.price
from drinks,
     category
where drinks.category_id = category.id
  and drinks.price < 200;

# 2.查询所有价格在 100信用点 - 300信用点 且 状态 起售 的菜品的名称 和 菜品的分类名称 （即使分类为空）
select drinks.name, drinks.price, category.name
from drinks
         left join category on drinks.category_id = category.id
where drinks.price between 100 and 300 and drinks.status = 1;

# 3.查询每个分类下最贵的菜品，展示出分类的名称，最贵的菜品价格
select category.name, max(drinks.price) as 'max price'
from drinks,
     category
where drinks.category_id = category.id
group by category.name;

# 4.查询各个分类下 菜品状态为 起售，且 该分类下菜品总数大于等于3的 分类名称
select category.name, count(*)
from category,
     drinks
where category.id = drinks.category_id
  and drinks.status = 1
group by category.name
having count(*) >= 3;

# 5.查询出 高级猫猫通行证套餐 中包含了哪些菜品，展示出 套餐名称，价格，包含菜品名称，价格，份数
select s.name, s.price, d.name, d.price, sd.copies
from drinks d,
     setmeal_drinks sd,
     setmeal s
where s.id = sd.meal_id
  and d.id = sd.drink_id
  and s.name = '高级猫猫通行证套餐';

# 6. 查询出低于菜品平均价格的菜品信息（展示出菜品名称，菜品价格）
select drinks.name, drinks.price
from drinks
where drinks.price < (select avg(price) from drinks);

事务

删除两个互相关联的表，通常需要两条SQL语句，如果其中一条出错或执行失败，会导致一个表数据被删除，另一个表没有变化。而如果使用事务来组合两条语句，如果其中一条出错，整个事务都将执行失败。语法：

# 开启事务
start transcation / begin;
# 提交事务
commit;
# 回滚事务
rollback;

事务的四大特性

原子性：事务是不可分割的最小单元，要不全部成功，要不全部失败。
一致性：事务完成时，必须保证所有数据保持一致状态。
隔离性：数据库系统提供的保护系统使得外部数据不受事务影响。
持久性：一旦提交或回滚，事务对数据库中的数据修改是永久的。

索引

索引是个b-二叉树，是一种数据结构，用来快速查找数据。

创建索引在某表某字段上创建字段

create index 索引名 on 表名(字段名);

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;