# MySQL 知识点

SQL 分类

分类全称说明
DDLData Definition Language数据定义语言,例如建数据库、建表等,都属于数据定义语言
DMLData Manipulation Language数据操纵语言,最常用的增删改查就属于 DML,操作对象是数据表中的记录
DQLData Query Language数据查询语言,用来查询数据库中表的记录
DCLData Control Language数据控制语言,如 Grant、Rollback 等等,常见于数据库安全管理,多数人一般很少用

# SQL 基本操作

# DDL 数据定义语言

操作数据库

查询:

​ 查询所有数据库:show databses;

​ 查询当前数据库:show databse();

创建:create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

删除:drop database [if exists] 数据库名;

使用:use 数据库名;


操作数据表

查询:

​ 查询当前数据库的所有表:show tables;

​ 查询表结构:desc 表名;

​ 查询指定表的创建表语句:show create table 表名;

创建:

create table{
	字段1 字段1类型[comment 字段1 注释],
	字段2 字段2类型[comment 字段2 注释],
	字段3 字段3类型[comment 字段3 注释],
	...
}[comment 表注释]

修改:

​ 添加字段:alter table 表名 add 字段名 类型 (长度) [comment 注释] [约束];

​ 删除字段:alter table 表名 drop 字段名

​ 修改数据类型:alter table 表名 modify 字段名 新数据类型 (长度);

​ 修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 类型 (长度) [comment 注释] [约束];

​ 修改表名:alter table 表名 rename 新表名;

删除:

​ 删除表:drop table [if exists] 表名;

​ 删除指定表并重新创建该表:truncate table 表名;


# DML 数据操纵语言

插入 (添加) 数据

  • 给指定字段添加:Insert into 表名 (字段 1, 字段 2, 字段 3....) values (值 1, 值 2...);
  • 给全部数据添加:insert into 表名 values (值 1, 值 2...);
  • 批量添加:
    • Insert into 表名 (字段 1, 字段 2, 字段 3....) values (值 1, 值 2...),(值 1, 值 2...),.....;
    • insert into 表名 values (值 1, 值 2...),(值 1, 值 2...),(值 1, 值 2...)...;

注意:

  • 插入数据时,指定的字段顺序与值的顺序要一一对应
  • 字符串和日期类型的数据要包含在引号中
  • 插入的数据大小,应该再字段的规定范围中

修改数据

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

删除数据

deletefrom 表名 [where 条件];

注意:

  • delete 语句的条件可以有也可以没有,如果没有条件,则会删除整张表的所有数据;
  • delete 语句不能删除某一个字段的值(要删除某一个字段的值可以用 update 修改成空)

# DQL 数据查询语言

查询数据:(语法结构)

select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件查询列表
order by 排序字段列表
limit 分页参数
基本查询
查询多个字段:
select1,2,3.... from 表名;
select * from 表名; # 全部返回
设置别名:
select1 [as 别名] from 表名;
去重:
select distinct 字段列表 from 表名;

条件查询
select 字段列表 from 表名 where 条件列表;

条件:

比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<> 或!=不等于
BETWEEN ... AND ...在某个范围之内 (含最小、最大值)
IN(...)在 in 之后的列表中的值,多选一
LIKE占位符 模糊匹配 (_匹配单个字符,% 匹配任意个字符) IS NULL 是 NULL

常用的逻辑运算符如下:

逻辑运算符功能
AND 或 &&并且 (多个条件同时成立)
OR 或 ||或者 (多个条件任意一个成立)
NOT 或!非,不是

聚合函数
SELECT 聚合函数(字段列表) FROM 表名 ;#将一列数据作为一个整体,进行纵向计算。

常见的聚合函数

函数功能示例
count统计数量select count (*) from 表名;
max最大值select max (age) from 表名;
min最小值select min (age) from 表名;
avg平均值select avg(age) from emp;
sum求和select sum (age) from emp where workaddress = ' 西安 ';

分组查询

分组查询有两种:where 和 having

  • 执行时机不同:where 是分组之前进行过滤,不满足 where 条件,不参与分组;而 having 是分组之后对结果进行过滤

  • 判断条件不同:where 不能对聚合函数进行判断,而 having 可以

注意事项:

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

排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
#ASC : 升序 (默认值)
#DESC: 降序
#如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序;
---------------------------------------------------------------
#根据年龄对员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from emp order by age asc , entrydate desc;

分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
#注意事项:
# 起始索引从 0 开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
# 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL 中是 LIMIT。
# 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。

DML 执行的顺序:

执行顺序
from 表名列表
where 条件列表
group by 分组列表
having 分组后条件列表
select 字段列表
order by 排序列表
limit 分页参数

# SQL 函数

函数 是指一段可以直接被另一段程序调用的程序或代码。

MySQL 中的函数主要分为以下四类: 字符串函数、数值函数、日期函数、流程函数

字符串函数
函数功能
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 个长度的字符串

数值函数
函数功能
ceil(x)向上取整
floor(x)向下取整
mod(x,y)返回 x/y 的模
rand()返回 0~1 内的随机数
round(x,y)求参数 x 的四舍五入的值,保留 y 位小数

日期函数
函数功能
curdate()返回当前日期
curtime()返回当前时间
now()返回当前日期和时间
year(date)获取指定 date 的年份
month(date)获取指定 date 的月份
day(date)获取指定 date 的日期
date_add(date, INTERVAL exprtype)返回一个日期 / 时间值加上一个时间间隔 expr 后的时间值
datediff(date1,date2)返回起始时间 date1 和 结束时间 date2 之间的天数

流程函数
函数功能
if(value , t , f)如果 value 为 true,则返回 t,否则返回 f
ifnull(value1 , value2)如果 value1 不为空,返回 value1,否则返回 value2
case when [ v1 ] then [r1] ...else[ default ] end如果 v1 为 true,返回 r1,... 否则返回 default 默认值
case[ expr ] when [ v1 ] then [r1] ... else[ default ] end如果 expr 的值等于 v1,返回 r1,... 否则返回 default 默认值

# SQL 约束

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

目的:保证数据库中数据的正确、有效性和完整性。

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

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

示例

根据需求,完成表结构的创建。需求如下:

字段名含义类型约束条件关键字
idID 唯一标识int主键,并且自动增长PRIMARY KEY AUTO_INCREMENT
name姓名varchar(10)不为空,并且唯一NOT NULL , UNIQUE
age年龄int大于 0,并且小于等于 120CHECK
status状态char(1) 如果没有指定该值,默认为 1DEFAULT
gender性别char(1)

对应的建表语句为:

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 '性别'
);

# 多表查询

# 连接查询

# 内连接

image

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

隐式内连接
select 字段列表 from1 ,2 where 条件 ... ;

显式内连接
select 字段列表 from1 [ inner ] join2 on 连接条件 ... ;
示例

查询每一个员工的姓名,及关联的部门的名称 (隐式内连接实现) 表结构: emp , dept

连接条件: emp.dept_id = dept.id

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化 SQL 编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

# 外连接

外连接分为两种,分别是:左外连接 和 右外连接

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

select 字段列表 from1 left [ outer ] join2 ON 条件 ... ;

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

select 字段列表 from1 right [ outer ] join2 on 条件 ... ;
示例

查询 emp 表的所有数据,和对应的部门信息

由于需求中提到,要查询 emp 的所有数据,所以是不能内连接查询的,需要考虑使用外连接查询。

表结构: emp, dept

连接条件: emp.dept_id = dept.id

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
#或者
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

注意事项:

左外连接和右外连接是可以相互替换的,只需要调整在连接查询时 SQL 中,表结构的先后顺 序就可以了。

而在日常开发使用时,更偏向于左外连接。

# 自连接

自连接查询,就是自己连接自己,也就是把一张表连接查询多次。

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

对于自连接查询,可以是内连接查询,也可以是外连接查询。

示例

查询员工 及其 所属领导的名字

表结构: emp

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