MySQL复习自用

MySQL基础

一、数据库相关概念

1.数据库好处

1. 可以持久化数据到本地
2. 结构化查询

2.数据库的常见概念

DB:数据库

DBMS:数据库管理系统(管理DB中的数据)

又称数据库软件或数据库产品
常见数据库软件:MySQL Oracle DB2 SqlServer

DBMS分为两类:

1. 基于共享文件系统的DBMS
2. 基于客户端-服务端的DBMS(MySQL Oracle SqlServer)

SQL:结构化查询语言(专门用来和数据库通信的语言)

(在卸载MySQL时C盘的ProgramData中还有MySQL残留)

SQL作用:

1. 是一种所有关系型数据库的查询规范，不同的数据库都支持
2. 通用的数据库操作语言，可以用在不同的数据库中
3. 不同的数据库 SQL 语句有一些区别

sql语句分类 :DDL DML DQL DCL

3.数据库存放数据的特点:

1. 数据存放在表中,表放在库中
2. 一个库中多张表,每个表具有唯一的表名标识自己
3. 表中有一个或多个列,列为"字段"
4. 表中每一行数据,相当于java中的对象

二、MySQL的常见命令

注:查看表的结构的写法:desc

三、DQL查询语言

1.基础查询

语法

select:查询列表
from:表名

特点

查询的结果是一个虚拟的表

示例

查询单个字段 多个字段 所有字段 常数
字符型和日期型必须用单引号引起来,数值型不需要
查询函数:select 函数名(实参列表)
查询表达式:select 100/1234
起别名:使用 as 或 空格
去重复值:SELECT DISTINCT 字段名 FROM 表名;
SELECT DISTINCT id FROM employees;
" + " 号:在SQL中仅仅只有一个作用–做加法运算
注: 参与计算的必须是数值型,不然要先进行数值转换
concat实现字符拼接:SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees
ifnull函数:判断某字段表达式是否为null,如果是返回指定的值,否则返回原值
select ifnull(commission, 0) from employees;
isnull函数:判断某字段是否为null,是返回1,否则返回0
select isnull(commission) from employees;

2.条件查询

语法

select 查询列表
from 表名
where 筛选条件

筛选条件分类

1.按条件表达式查询:> 、< 、<= 、>= 、= 、<> 、<=>安全等于
2.按逻辑表达式查询:and 或 &&、or或|| 、not或!
3.按模糊查询:【not】between and、like、in、is null、is not null
between and (包含临界值[ ],两个临界值不能调换顺序)
like:一般与通配符搭配使用
% 包含任意多个字符,包含0个字符
_ 包含任意单个字符

LIKE  表示模糊查询
SELECT * FROM  表名 WHERE  字段名 LIKE ' 通配符(%)字符串';

ESCAPE关键字的作用进行说明

### 查询名字带有“%”字符的用户
> SELECT * FROM user WHERE name LIKE CONCAT("%", "$%", "%") ESCAPE "$"
### 查询名字带有“_”字符的用户
> SELECT * FROM user WHERE name LIKE CONCAT("%", "a_", "%") ESCAPE "a"

IN

IN 
SELECT * FROM user WHERE name IN('bi', 'wang', 'yu');

is null
is null:仅仅可以判断NULL值,可读性较高,建议使用
<=>:既可以判断NULL值,又能判断普通的数值,可读性较低

3.排序查询

select 查询列表
from 表
【where 筛选条件】
order by 排序列表【asc | desc】

asc:升序（默认） desc：降序

order by 一般放在最后面，limit除外

按单个字段、多个字段（先按谁排，谁就排在前面）、表达式、函数、别名排序

length（字段） 求某字段字节数

4.常见函数

将一组逻辑语句封装在方法体中

select 函数名（实参列表）【from 表】

1.单行函数
concat length isnull
2.多行函数（聚合函数、组函数等）

单行函数

字符函数

1）length 长度

select length(str)
select length('杉杉今天学习了吗hahaha')   # 30

show variables like %char%'    # 查看客户端的字符集

2）concat 拼接字符串

3）upper lower 大小写

4）substr substring 截取一段字符

sql语言中索引从1开始

select substr('李莫愁爱上了陆展元',7) out_put   # 陆展元
select substr('李莫愁爱上了陆展元',1,3) out_put   # 李莫愁 3：指定字符长度

5）instr：返回字串第一次出现的起始索引，找不到返回0

select instr('李莫愁爱上了陆展元','李莫愁') out_put   # 1

6）trim

去掉前后空格

select length(trim('       杉杉     ')) output    #  4
select length('a' form trim('aaaaa杉aaa杉aaaaaa')) output    #  杉杉aaa杉

7）lpad rpad 用指定的字符，实现左/右填充指定长度

select LPAD('杉杉',10,'*') as out_put

8）replace 替换

数学函数

1）round 四舍五入

select round(-1.45) # -2
select round(-1.457) # -1.46

2）ceil 向上取整 floor 向下取整

select ceil(-1.45)   # -1
select floor(-1.45)    # -2

3）truncate 截断

select truncate(1.45,1)  # 1.4

4）mod 取余

select mod(a,b)  # a-a/b*b
select mod(10,3)
select 10%3

日期函数

1）now 返回当前系统日期+时间

select now();

2）curdata 当前系统日期 curtime 当前系统时间

3）获取指定年、月、日、时间

select year(now());  # 年
select year('1998-01-01')  # 年

select month(now())  # 月
select monthname(now())  # 月

4）str_to_data 将字符转换为相应的日期

select str_to_data('1998-01-02','%Y-%c-%d') as output

5）date_format 将日期转换为字符

date_format(字段,'%m月-%d日-%y年')   

其他函数

select version()
select database()
select user()

流程控制函数 if case

case 要判断的表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
else 要显示的值n或语句n
end

case
when 条件1 then 要显示值1
when 条件2 then 要显示值2
else 要显示的值n或语句n
end

分组函数

sum avg max min count
注意：
1.sum、avg一般用于处理数值型；max、min、count可以处理任意类型数据
2.以上分组函数都忽略了null值
3.可以与distinct搭配实现去重

select sum(distinct salary) from employees;

4.count函数介绍：一般用count(*)统计函数

select count(*) from employees;   # 统计行数
select count(常数) from employees;    # 统计行数

5.和分组函数一同查询的字段要求是group by后的字段

5.分组查询

语法：

select 分组函数，列（要求出现在group by后面的要分组的列表名）
from 表
【where 筛选条件】（如果根据原始表就能够进行筛选，则放在from之后）
group by 分组的列表
having 分组结果进一步筛选
【order by 子句】

注意： 查询列表必须特殊，要求是分组函数和group by后出现的字段

案例：查询哪个部门的员工数>2
1）：查询每个部门的员工数
2）：根据1）的结果进行筛选，查询哪个部门的员工个数>2
select count(*), department_id
from employees 
group by department_id
having count(*)>2   # having：实现分组后的筛选

小结：
1.分组查询中的筛选分为两类：

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的数据集	group by 子句的后面	having

能用分组前筛选的，优先使用分组前筛选。
2.group by语句支持单个字段分组，也支持多给字段分组，多个字段之间用逗号分开，也支持表达式或函数
3.也可以添加排序，排序放在整个分组查询的最后

6.连接查询（多表连接）

涉及到的字段涉及多个表，避免笛卡尔乘积现象的产生

按功能分类：

连接类型	分类
内连接	等值连接 非等值连接 自连接
外连接	右外连接 左外连接 全外连接
交叉连接	（笛卡尔乘积）

等值连接：
1）多表等值连接的结果为多表的交集部分
2）n表连接至少需要n-1个连接条件
3）一般需要为表起别名
4）可以搭配排序、分组、筛选
5）多表顺序没有要求

sql99语法：

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by排序列表】

连接类型：

• 内连接：inner
• 外连接：
  • 左外：left 【outer】
  • 右外：right 【outer】
  • 全外：full【outer】
• 交叉连接：cross

内连接 —— 非等值连接：
# 查询工资级别的个数>20的个数，并且按照工资级别降序
select count(*),grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having cout(*)>20
order by grade_level desc;

外连接：

1. 外连接的查询结果为主表的所有记录
   1）如果从表中有和他匹配的，则显示匹配值
   2）如果没有和他匹配的，则显示null
   3）外连接的查询结果 = 内连接结果 + 主表中有而从表中没有的记录
2. 左外连接，left join 左边是主表；右外连接，right join 右边是主表
3. 左外与右外交换两个表的顺序，可以实现同样的结果

左外连接：
# 查询男朋友不在表里的女生名字
select * from beauty;
select * from boys;

select b.name
from beauty b
left join boys bo
on b.boyfriend_id = bo.id
where bo.id is null;

7.子查询（内查询）

• 含义：出现在其他语句内部的select语句。
• 分类
  • 按子查询出现的位置：
    • select 后面
      • 仅仅支持标量子查询
    • from 后面
      • 支持表子查询
    • where 或 having 后面
      • • 标量子查询
      • • 列子查询
      • 行子查询
    • exists 后面（相关子查询）
      • 支持表子查询
  • 按结果集的行列数不同：
    • 标量子查询（结果集只有一行一列）
    • 列子查询（结果集一列多行）
    • 行子查询（结果集一行多列）
    • 表子查询（结果集一般为多行多列）

一、放where和having后面
特点：

1. 子查询放在小括号里面
2. 子查询一般放在条件右侧
3. 标量子查询，一般搭配单行操作符使用 > < = <= >= <>
   列子查询，一般搭配多行操作符使用 in/not in、any/some、all
4. 子查询的执行优于主查询执行，主查询的条件都用到了子查询的结果

行子查询（用的不多）
select * 
from employees
where (employees_id,salary) = (
		select min(employees_id),max(salary)
		from employees
)

二、放select后面，仅仅支持标量子查询

三、放from后面
将子查询结果充当一张表，要求必须起别名

# 查询每个部门平均工资的工资等级
select ag_dep.*, g.grade_level
from (
		select avg(salary) ag,department_id
		from employees
		group by department_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;

四、exists查询（相关子查询）

语法：
	exists（完整的查询语句）
结果：
	1或0

8.分页查询（重点）

应用场景：要显示的数据一页显示不全，需分页提交sql请求

语法：

select 查询列表
from 表1
【连接类型 join 表2
on 连接条件 
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset,size； # offset要显示条目的起始索引（从0开始索引）

特点：

1. limit 语句放在查询语句最后
2. 要显示的页数page，每页的条目数size，满足下面这个公式：

select 查询列表
from 表
limit （page - 1）*size，size

练习题：

# 查找所有学员的邮箱名（@之前的名称）
select  substr(email,1,instr(email,'@')-1) 用户名
from stuinfo；

9.union联合查询

union 联合、合并：将多条查询语句的结果合并成一个结果

引入案例：查询部门编号>90或邮箱编号包含a的员工信息

select * from employees where email like '%a%' or department_id>90;

select * from employees where email like '%a%'
union
select * from employees where department_id>90;

语法：
	查询语句1
	union 【all】
	查询语句2
	union 【all】
	...

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致

特点：
1.多条查询语句的查询列数一致
2.多条查询语句查询的每一列的类型和顺序最好一致
3.使用union关键字是去重的，如果使用union all 可以包含重复项

四、DML操作语言

1.插入语言 insrt

语法： 表名  列名  新插入的值
	INSERT INTO 表名(列名,...)  VALUES(值1,...);

1）方式一：经典的插入

• 插入的值的类型要与列的类型一致或兼容
• 不可以为null的列必须插入值，而为null的列可以插入null值、或插入时忽略

insrt into beauty(age,name,sex,borndate,phone)
values (18,'张飞','男',null,'119')；

• 列的顺序可以调换
• 列数和值的个数必须一致
• 可以省略列名，默认所有列，但注意表中列的顺序要一致

insrt into beauty
values (18,'张飞','男',null,'119',null,null)；

• 支持多行插入，支持子查询

insert into employees
select 1,'dewf','gsdg','fsdgs',902 union
select 2,'sf','fasf','fasf',341 union
select 3,'da','ada','faf',21 

2）方式二：

语法：
	INSERT INTO 表名
	SET 列名=值,列名=值,...;

2.修改语言 update

1）修改单表记录

语法：
	update 表名 SET 列名=值,列名=值,...
	where 筛选条件;

# 如： 
	update employees set last_name='sef' where id = 3;

2）修改多表记录

语法：
	sql92语法：
	update 表1 别名，表2 别名
	SET 列名=值,列名=值,...
	where 连接条件
	and 筛选条件;
	
	sql99语法：
	update 表1 别名
	inner | left |right join 表2 别名
	on 连接条件
	SET 列名=值,列名=值,...
	where 筛选条件;

3.删除语言 delete truncate

方式一：delete

语法：
	delete from 表 # 删除整个表！！！

单表的删除：
	delete from 表 where 筛选条件
	
多表的删除：
	sql92语法：
	delete  表1的别名，表2的别名
	from 表1 别名,表2 别名
	where 连接条件
	and 筛选条件;
	
	sql99语法：
	delete  表1的别名，表2的别名
	from 表1 别名
	inner | left |right join 表2 别名
	on 连接条件
	where 筛选条件;

方式二、truncate

语法：
truncate tble  表 # 删除整个表！！！

truncate 不能允许加 where 条件，即truncate用于与清空表数据

truncate与delete区别：面试题

1. delete可以加where，truncate不能加
2. truncate删除效率高一点点
3. 假如有自增长的列，用delete删除再插入数据，自增长列的值从断点开始；
   用truncate删除再插入数据，自增长列的值从1开始
4. truncate删除没有返回值 ，delete有返回值（知道删除了几行数据）
5. truncate删除不能回滚，delete可以回滚

五、DDL 数据定义语言

1.库与表的管理

创建：crate
修改：alter
删除：delete

1）库的管理

库的创建

语法：
	 create database 【if not exists】 库名；

库的修改

更改名字从文件夹中修改

更改库的字符集
语法：
	alter database books character set bgk；

库的删除

语法：
	drop database if exists books；

2）表的管理

表的创建

语法：
	create table if not exists 表名(
		列名 列的类型【(长度) 约束】,
		列名 列的类型【(长度) 约束】,
		列名 列的类型【(长度) 约束】,
	    ......
		列名 列的类型【(长度) 约束】
	)	

表的修改

1. 修改列名

alter table 表名 change column 旧列名 新列名 类型；

2. 修改列的类型或约束

alter table 表名 modify column 列名 新类型；

3. 添加新列

alter table 表名 add column 新列 新列的类型；

4. 删除列

alter table 表名 drop column 列名；

5. 修改表名

alter table 表名 rename to 新表名；

表的删除

drop table if not exists 表名；

表的复制

复制表的结构：
	create table 新表 like 旧表；
复制表的结构 + 数据：
	create table 新表
	select * from 旧表；
只复制部分数据：
	create table 新表
	select 列名,列名,...
	from 旧表
	where 筛选条件；
复制部分表的结构：
	create table 新表
	select 列名,列名,...
	from 旧表
	where 0【或1=2】；

2.常见数据类型介绍

• 数值型：
  • 整形：
    • tinyint、smallint、mediumint、int / interger、bigint，字节数分别为1、2、3、4、8
  • 小数：
    • 定点数：dec(M,D)，decimal(M,D)
    • 浮点数：float(M,D)、double(M,D)，字节数分别为4、8
      • M代表整数部位 + 小数部位，D代表小数部位，如果超过将会插入临界值
      • M和D都可省略
        • 如果是decimal，则M会默认为10，D默认为0
        • 如果是float或double，则会根据插入的数值来调整精度
      • 定点型的精确度较高
• 字符型
  • 较短的文本：char、varchar
  • 较长的文本：text、blob（较长的二进制数据）
  • 一些常用的：
    • binary、varbinary 用于保存较短的二进制
    • enum 用于保存枚举
    • set 用于保存集合
• 日期型
  • date、datetime、timestamp、time、year，字节数分别为4、8、4、3、1

char与varchar的对比

写法	M的含义	特点	空间的耗费	效率
char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar(M)	最大的字符数	可变长度的字符	比较节省	低

datetime与timestamp的对比

写法	字节	范围	时区等的影响
datetime	8	1000 - 9999	不受
timestamp	4	1970 - 2038	受

3.常见约束

含义：一种限制，用于限制表中的数据，保证数据准确与可靠性。

六大约束：

• not null：保证字段不为空
• default：保证字段具有默认值
• primary key：主键，保证字段唯一，并且非空
• unique：保证字段唯一，可以为空
• check：检查约束
• foreign key：外键，限制两个表的关系
  • 主键所在的表是主表，外键所在的表是从表
  • 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
  • 主表的关联列必须是一个key（一般是主键或唯一）
  • 插入数据时，先插入主表，再插入从表
  • 删除数据时，先删除从表，再删除主表

添加约束的时机：

• 创建表时
• 修改表时

约束的添加分类：

• 列级约束
  • 六大约束语法上都支持，但外键约束没有效果
• 表级约束
  • 除了非空、默认，其他的都支持
  • 【constraint 约束名】 约束类型 (字段名)
  • 【constraint 约束名】 约束类型 (字段名) references 外键表(字段名) # 外键

create table 表名（
		字段名 字段类型 列级约束，
		字段名 字段类型
		表级约束
）

4.标识列（自增长列）

auto_increment

特点：

• 标识列必须与主键、唯一、外键搭配，必须有一个key
• 一个表中只能有一个标识列
• 标识列类型只能是数值型
• 标识列可以通过set auto_increment_increment = 3，设置步长
  可以通过手动插入，设置起始值

六、TCL：Transaction Control Language 事务控制语言

引用文本

H₂O is是液体。

2¹⁰ 运算结果是 1024.

一个具有注脚的文本。¹

Markdown将文本转换为 HTML。

KaTeX数学公式

您可以使用渲染LaTeX数学表达式 KaTeX:

Gamma公式展示 $\Gamma (n)=(n-1)!\forall n\in \mathbb{N}$ 是通过欧拉积分

$$\Gamma (z)={\int }_0^{\infty }{t}^{z-1}{e}^{-t}dt.$$

你可以找到更多关于的信息 LaTeX 数学表达式here.

新的甘特图功能，丰富你的文章

• 关于 甘特图 语法，参考 这儿,

UML 图表

可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图：

这将产生一个流程图。:

• 关于 Mermaid 语法，参考 这儿,

FLowchart流程图

我们依旧会支持flowchart的流程图：

• 关于 Flowchart流程图 语法，参考 这儿.

##导出与导入

##导出
如果你想尝试使用此编辑器, 你可以在此篇文章任意编辑。当你完成了一篇文章的写作, 在上方工具栏找到 文章导出 ，生成一个.md文件或者.html文件进行本地保存。

导入
如果你想加载一篇你写过的.md文件，在上方工具栏可以选择导入功能进行对应扩展名的文件导入，
继续你的创作。

---

1. 注脚的解释 ↩︎