MySQL

任校彤

已于 2024-08-09 08:55:20 修改

阅读量309

点赞数 4

文章标签： mysql 数据库

于 2024-08-03 09:14:25 首次发布

本文链接：https://blog.csdn.net/weixin_65738216/article/details/140884991

版权

一、SQL通用语法

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

单行注释：-注释内容或#注释内容(MySQL特有)
多行注释：/注释内容

二、SQL分类

分类	全程	说明
DDL	数据定义语言	用来定义数据库对象（数据库、表、字段）
DML	数据操作语言	用来对数据库表中的数据进行增删改查
DQL	数据查询语言	用来查询数据库中表的记录
DCL	数据控制语言	用来创建数据库用户、控制数据库的访问权限

三、DQL

（一）DQL-介绍

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

（二）DQL-语法

DQL 查询语句，语法结构如下：

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

1.基本查询

查询多个字段

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

设置别名

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

去除重复记录

select distinct 字段列表 from 表名;

2.条件查询

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

条件

常用的比较运算符如下:

在这里插入图片描述

常用的逻辑运算符如下:

在这里插入图片描述

一般使用 and , or , not 。

3.聚合函数

将一列数据作为一个整体，进行纵向计算。
函数功能count统计数量max最大值min最小值avg平均值sum求和

SELECT 聚合函数(字段列表) FROM 表名;

注意：null不参与任何聚合函数的计算

常见的聚合函数
在这里插入图片描述

4.分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]；

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

注意：执行顺序：where>聚合函数>having。

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

5.排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：
ASC：升序
DESC：降序

6.分页查询

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

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

（三）DQL-执行顺序

DQL语句在执行时是有执行顺序的，也就是先执行那一部分，后执行那一部分：
在这里插入图片描述

（四）多表查询

1.内连接

语法：

# 隐式内连接
select 字段列表 from 表1,表2 where 条件;
# 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;

2.外连接

语法：

# 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
# 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

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

3.子连接

语法：

select 字段列表 from 表a 别名a join 表a 别名b on 条件;

自链接查询可以是内连接查询也可以是外连接查询

4.联合查询union、union all

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

select 字段列表 from 表a
union [all]
select 字段列表 from 表b

# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union all
select * from emp where age>50;

# 没有all重复满足条件的只出现一次
# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union
select * from emp where age>50;

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

5.标量子查询

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

常用符号：=、<>、>、>=、<、<=

# 根据销售部门的id查询员工信息
# 先分开查询
# 查询销售部门的id
select id from dept where name='销售部'; #id为4
# 查询销售部门中员工的信息
select * from emp where dept_id=4;
# 合并为一个查询
select * from emp where dept_id=(select dept.id from dept where dept.name='销售部' );

6.列子查询

子查询的结果为一列(可以是多行)的，这种子查询为列子查询

常用操作符：

# 列子查询
# 查询销售部和市场部的所有员工信息
# 查询销售部和市场部的id
select id from dept where name='销售部' or name='市场部'; #id为2 4
# 查询两个部门的所有员工
select * from emp where dept_id in (2,4);
# 合并
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

7.行子查询

子查询返回的结果是一行(可以是多列)，这种子查询为行子查询

常用操作符：=、<>、in、not in

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

8.表子查询

子查询的结果是多行多列这种查询为表子查询

常用操作符：in

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

四、事务

（一）事务的四个特性ACID

ACID，是指在可靠数据库管理系统（DBMS）中，事务（transaction）应该具有的四个特性：

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

2.1 原子性

事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
事务是一个完整的操作，事务的各元素是不可分的。事务中的所有元素必须作为一个整体提交或回滚。
如果事务中的任何元素失败，则整个事务将失败。

案例：
A给B转帐100元钱的时候只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么都执行，要么就都不执行。

2.2 一致性

指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
当事务完成时，数据必须处于一致状态。
在事务开始前，数据库中存储的数据处于一致状态。在正在进行的事务中，数据可能处于不一致的状态。
当事务成功完成时，数据必须再次回到已知的一致状态。

案例：
对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

2.3 隔离性

指在并发环境中，当不同的事务同时操纵相同的数据时，每个羽务都有各自的完整数据空间。
对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
也就是说并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

2.4 持久性

在事务完成以后，该事务所对数据库所作的更改使持久的保存在数据库之中，并不会被回滚。指不管系统是否发生故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会被永久地保留在数据库中。

（二）事务控制语句

BEGIN 或 START TRANSACTION:显式地开启一个事务.
COMMIT 或 COMMIT WORK:提交事务,并使已对数据库进行的所有修改变为永久性的.
ROLLBACK 或 ROLLBACKWORK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改.
SAVEPOINT S1:使用SAVEPOINT允许在事务中创建一个回滚点,一个事务中可以有多个SAVEPOINT;"S1"代表回滚点名称.
ROLLBACK TO[SAVEPOINT] S1:把事务回滚到标记点.

（三）事务的并发问题

脏读(读取未提交数据)

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读

读已提交、不可重复读(前后多次读取，数据内容不一致)

一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

可重复读、幻读(前后多次读取，数据总量不一致)

一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。

幻读：

原因：因为mysql数据库读取数据时，是将数据放入缓存中，当事务B对数据库进行操作：例如删除所有数据且提交时，事务A同样能访问到数据，这就产生了幻读。

问题：解决了可重复读，但是会产生一种问题，错误的读取数据，对于其他事务添加的数据也将访问不到

（四）事务的隔离级别

MySQL 中事务的隔离级别一共分为四种，分别如下

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。