MYSQL数据库基础篇

已于 2024-07-07 22:32:49 修改
阅读量315 收藏 4
点赞数 10
分类专栏： MySQL 文章标签： mysql
于 2024-06-13 21:37:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45044818/article/details/139664737
版权

MYSQL数据库基础篇

文章目录

1、关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点：

  • 使用表存储数据，格式统一，便于维护。
  • 使用SQL语言操作，标准统一，使用方便。

2、MYSQL注释

  • 单行注释： – 注释内容 或 # 注释内容（MYSQL特有）
  • 多行注释： /* 注释内容 */

3、SQL分类

分类全称说明
DDLData Definition Language数据定义语言，用来定义数据库对象（数据库、表、字段）
DMLData Manipulation Language数据操作语言，用来对数据库的数据进行增删改
DDLData Query Language数据查询语言，用来查询数据库中表的记录
DDLData Control Language数据控制语言，用来创建数据库用户、控制数据库的访问权限

3.1 DDL

3.1.1 DDL-数据库操作

  1. 查询

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();
  1. 创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
  1. 删除
DROP DATABASE [IF EXISTS] 数据库名;
  1. 使用
USE 数据库名;

字符集一般用utf8mb4

3.1.2 DDL-表操作-查询

  1. 查询当前数据库所有表
SHOW TABLES;
  1. 查询表结构
 DESC 表名;
  1. 查询指定表的建表语句
 SHOW CREATE TABLE 表名;

3.1.3 DDL-表操作-创建、修改

  1. 表的创建
CREATE TABLE 表名 (
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	字段3 字段3类型[COMMENT 字段3注释],
	......
	字段6 字段6类型[COMMENT 字段6注释]  # 最后没逗号
) [COMMENT 表注释];

字段类型有很多：

  • unsigned代表无符号，不能为负数
  • char和varchar 一个是定长，一个是变长
  1. 表的修改

添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束];

修改（修改字段类型）

ALTER TABLE 表名 MODIFY 字段名 类型(长度)[约束];

修改（修改字段名和类型）

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[约束];
  1. 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;

3.1.4 DDL-表操作-删除

删除表

DROP TABLE IF EXISTS 表名;

删除指定表，并重新创建该表

TRUNCATE TABLE 表名;

注意：在创建表时，表中的全部数据也会被删除。

3.2 DML

DML英文全称是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。
添加数据（INSERT）
修改数据（UPDATE）
删除数据（DELETE）

3.2.1 DML-添加数据

  • 给指定字段添加数据
INSERT INTO 表名(字段名1, 字段名2, ...) VALUES(值1, 值2, ...);
  • 给全部字段添加字段
INSERT INTO 表名 VALUES(值1, 值2, ...);
  • 批量添加数据
INSERT INTO 表名(字段名1, 字段名2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

注意：

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

3.2.2 DML-修改数据

UPDATE 表明 SET 字段名1 = 值1, 字段名2 = 值2 [WHERE 条件];

注意：修改语句的条件可以有，也可以没有。如果没有条件，则会修改整张表的所有数据。
例子：

create table emp(
    id int primary key auto_increment comment 'ID',
    name varchar(20) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '工作',
    salary int comment '薪水',
    entrydate date comment '入职日期',
    managerid int comment '领导ID',
    dept_id int comment '部门ID'
);

INSERT INTO emp VALUES (null, '黎明', 66 , '领导', 10000, '2020-09-01', null, 1),
                       (null, '王二', 66 , '程序员', 8000, '2021-09-01', 1, 1),
                       (null, '三毛', 66 , '程序员', 11000, '2022-09-01', 1, 1),
                       (null, '何洁', 66 , '程序员', 12000, '2023-09-01', 1, 1),
                       (null, '好的', 66 , '程序员', 13000, '2025-09-01', 1, 1),
                       (null, '胡都', 66 , '程序员', 18000, '2026-09-01', 1, 1);

# 修改id为1的数据，将name改为黎明1
UPDATE emp SET name = '黎明1' WHERE id = 1;
# 修改id为2的数据，将name改为黎明2，薪水改为50000
UPDATE emp SET name = '黎明2', salary = 50000 WHERE id = 2;
# 将所有员工的入职日期修改为2008-01-01
UPDATE emp SET entrydate = '2008-01-01';

3.2.3 DML-删除数据

DELETE FROM 表名 [WHERE 条件];

注意：DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。 DELETE语句不能删除某一个字段的值（可以使用UPDATE）。
例子：

# 修改id为6的数据
DELETE FROM emp WHERE id = 6;

3.3 DQL

DQL英文全称是Data Query Language（数据查询语言），用来查询数据库中表的数据。
DQL语法

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY 
    分组字段列表
HAVING 
    分组后条件列表
ORDER BY 
    排序字段列表
LIMIT 
    分页参数

3.3.1 DQL-基本查询

  1. 查询多个字段
 SELECT 字段1, 字段2... FROM 表名;
 SELECT * FROM 表名;
  1. 设置别名
SELECT 字段1 [AS 别名1], 字段2  [AS 别名2]... FROM 表名;
  1. 去除重复记录DISTINCT
SELECT DISTINCT 字段列表 FROM 表名;

例子：

#基本查询
#1. 查询员工表中的id，name，salary返回
SELECT id, name, salary from emp;
#2. 查询所有字段返回
SELECT id, name, id, name, age, job, salary, entrydate, managerid, dept_id from emp;

SELECT * from emp;

#2. 查询员工的所有职业（不要重复）
SELECT DISTINCT job FROM emp;

3.3.2 DQL-条件查询

  1. 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
  1. 条件
比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<>或!=不等于
BETWEEN…AND…在某个范围之内（含最小、最大值）
IN(…)在in之后的列表中的值，多选1
LIKE占位符模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL是NULL
逻辑运算符功能
AND或&&并且（多个条件同时成立）
OR或者（多个条件任意一个成立）
NOT 或！非，不是

例子：

# 条件查询
# 1. 查询年龄大于50的员工
SELECT * FROM emp WHERE age > 50;
# 2. 查询没有领导的员工
SELECT * FROM emp WHERE managerid IS NULL;
# 3. 查询年龄不等于66夫人员工
SELECT * FROM emp WHERE age != 66;
SELECT * FROM emp WHERE age <> 66;
# 4.查询年龄在20 到 100之间的员工
SELECT * FROM emp WHERE age >= 20 AND age <= 100;
SELECT * FROM emp WHERE age >= 20 && age <= 100;

SELECT * FROM emp WHERE age BETWEEN 20 AND 100;
#5. 查询姓名为两个字的员工信息
SELECT * FROM emp WHERE name LIKE '__';
#6. 查询姓名以洁结尾的员工信息
SELECT * FROM emp WHERE name LIKE '%洁';

3.3.3 DQL-聚合函数

  1. 介绍
    将一列作为一个整体，进行纵向计算。
  2. 常见聚合函数
函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和
  1. 语法
SELECT 聚合函数(字段列表) FROM 表名;

例子：

# 聚合函数
# 1. 统计该企业的员工数量
SELECT COUNT(*) FROM emp;
SELECT COUNT(id) FROM emp;
# 2. 统计该企业员工的平均年龄
SELECT AVG(age) FROM emp;

注意： null值不参与所有聚合函数的运算。

3.3.4 DQL-分组查询

  1. 语法
SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

2. where和having的区别

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

例子：

# 分组函数
# 1. 根据工作进行分组，统计不同工作的员工数量
SELECT job, COUNT(*) FROM emp GROUP BY job;
# 2. 根据工作进行分组，统计不同工作的平均薪资
SELECT job, AVG(salary) FROM emp GROUP BY job;
# 3. 查询员工年龄小于100的，根据工作进行分组，获取平均工作薪资大于10000的
SELECT job, AVG(emp.salary) FROM emp WHERE age < 100 group by job HAVING AVG(salary) > 10000;

注意：

  • 执行顺序： where > 聚合函数 > having
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

3.3.5 DQL-排序查询

  1. 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
  1. 排序方式
  • ASC: 升序（默认值）
  • DESC: 降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

例子：

# 排序
# 1. 根据年龄对公司员工进行升序排序
SELECT * FROM emp ORDER BY age ASC;
SELECT * FROM emp ORDER BY age;
# 2. 根据入职时间对公司员工进行降序排序
SELECT * FROM emp ORDER BY entrydate DESC ;
# 3. 根据年龄对员工进行升序排序，如果年龄相同，根据薪资做降序排序
SELECT * FROM emp ORDER BY age ASC , salary DESC;

3.3.6 DQL-分页查询

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

注意：

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

例子：

# 分页
# 1. 查询第一页的员工数据，每页展示3条记录
SELECT * FROM emp LIMIT 0, 3;
SELECT * FROM emp LIMIT 3;
# 1. 查询第二页的员工数据，每页展示3条记录
SELECT * FROM emp LIMIT 3, 3;

3.3.7 DQL-执行顺序

SELECT  4
    字段列表  
FROM  1
    表名列表   
WHERE  2
    条件列表  
GROUP BY  3
    分组字段列表  
HAVING 
    分组后条件列表 
ORDER BY  5
    排序字段列表  
LIMIT  6
    分页参数

3.3.8 DQL-综合场景测试

# 综合
# 1. 查询id为1， 3， 5且为程序员的员工信息
SELECT * FROM emp WHERE id IN (1, 3, 5) AND job = '程序员';
# 2. 统计员工中，年龄小于80的，薪资为在20000以内的不同职业的员工数量
SELECT job, COUNT(*) FROM emp WHERE salary < 20000 GROUP BY job;
# 3. 查询职业为程序员，且年龄在50-80之间以内的前3个员工信息，年龄相同按入职时间升序排序
SELECT * FROM emp WHERE job = '程序员' AND age BETWEEN 50 AND 80 ORDER BY AGE, entrydate;

3.4 DCL

介绍：
DCL英文全称是Data Control Language（数据控制语言），用来管理数据库用户，控制数据库的访问权限。

3.4.1 DCL-管理用户

  1. 查询用户
USE mysql;
SELECT * FROM user;
  1. 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  1. 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
  1. 删除用户
DROP USER '用户名'@'主机名';

例子：

# 用户管理
# 1. 查询全部的用户
USE mysql;
SELECT * FROM user;
# 2. 创建用户TEST01，只能在当前主机localhost访问，密码为123456
CREATE USER 'TEST01'@'localhost' IDENTIFIED BY '123456';
# 3. 创建用户TEST02，可以在任意主机上访问，密码为123456
CREATE USER 'TEST02'@'*' IDENTIFIED BY '123456';
# 4. 修改'TEST01'@'localhost'密码为1234
ALTER USER 'TEST01'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';
# 5. 删除用户'TEST01'@'localhost'
DROP USER 'TEST01'@'localhost';

注意：

  • 主机名可以使用%作为通配。
  • 这类SQL开发人员操作较少，主要是DBA（Database Administrator 数据库管理员）在用。

3.4.2 DCL-权限控制

MySQL中定义了很多权限，但是常用的就这几种:

权限说明
ALL, ALL PRIVILEGES所有权限
SELECT查询权限
UPDATE修改数据
INSERT插入数据
DELETE删除数据
ALTER修改表
DROP删除数据库、表、视图
CRETAE创建数据库、表
  1. 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
  1. 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  1. 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

例子：

# 权限
# 1. 查询权限
SHOW GRANTS FOR 'TEST01'@'localhost';
# 2. 授予权限
GRANT SELECT, DROP ON test_0630.* TO 'TEST01'@'localhost';
# 3. 撤销权限
REVOKE SELECT, DROP ON test_0630.* FROM 'TEST01'@'localhost';

注意：

  • 多个权限之间，使用逗号分隔。
  • 授权时，数据库名和表名可以使用*进行通配，代表所有。

4、函数

4.1 字符串函数

MySQL内置了很多字符串函数，常用的几个如下：

函数功能
CONCAT(S1, S2,… Sn)字符串拼接，将S1,S2,…Sn拼接成一个字符串
LOWER(str)将字符串str全部转为小写
UPPER(str)将字符串str全部转为大写
LPAD(str, n, pad)左填充，用字符串pas对str的左边进行填充，达到n个字符串长度
RPAD(str, n, pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)去除字符串头部和尾部的空格
SUBSTRING(str, start, len)返回字符串str从start位置起的len个长度的字符串，start起始为1

例子：

# 字符串
SELECT CONCAT('HELLO', ' ' , 'WORLD');
SELECT LOWER('HElLO');
SELECT UPPER('hello');
SELECT LPAD('HELLO', 10, '-');
SELECT RPAD('HELLO', 10, 'me'); #HELLOmemem
SELECT SUBSTRING('HELLO', 2, 3); # ELL
SELECT TRIM('  HELLO WORLD '); # HELLO WORLD

4.2 数值函数

函数功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x, y)返回x/y的模
RAND()返回0-1内的随机数
ROUND(x, y)求参数x的四舍五入的值，保留y位小数

例子：

# 数值
SELECT CEIL(10.1); # 11
SELECT FLOOR(10.9); # 10
SELECT ROUND(16.88386, 2); # 16.88
SELECT MOD(5, 2); # 1
SELECT RAND();
# 案例： 通过数据的函数，生成一个六位数的随机验证码
SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0');

4.3 日期函数

常见的日期函数如下：

函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取指定date的年份
MONTH(date)获取指定date的月份
DAY(date)获取指定date的日期
DATE_ADD(date， INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1， date2)返回起始时间date1和结束时间date2之间的天数

例子：

# 日期
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT YEAR('2022-09-01');
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATEDIFF('2022-09-01', NOW());

# 查询所有员工入职到现在多少天了
SELECT name, DATEDIFF(NOW(), entrydate) AS 'entrydays' FROM emp ORDER BY entrydays DESC;

4.4 流程函数

流程函数也是很常见的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率

函数功能
IF(value， t， f)如果value为true，则返回t，否则返回f
IFNULL(value1， value2)如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END如果val1为true，返回res1，…否则返回default默认值
CASE [expr] WHEN [val] THEN [res1] … ELSE [default] END如果expr的值为val1，返回res1，…否则返回default默认值

例子：

# 流程
SELECT IF(FALSE, 1, 2);
SELECT IFNULL(1, 2);
SELECT IFNULL('', 2); # ''
# 10000以上为高薪资，否则为低薪资
SELECT
    name,
    salary,
    CASE WHEN salary >= 10000 THEN '高薪资' ELSE '低薪资' END
FROM emp;

5、 约束

  1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  2. 目的： 保证数据库中数据的正确、有效性和完整性。
  3. 分类：
约束描述
非空约束限制该字段的数据不能为null
唯一约束保证该字段的所有数据都是唯一、不重复的
主键约束主键是一行数据的唯一标识，要求非空且唯一
默认约束保存数据时，如果未指定该字段的值，则采用默认值
检查约束（8.0.16版本之后）保证字段值满足某一个条件
外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性

注意：

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

例子：

CREATE TABLE user(
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '名字',
    age INT CHECK ( age > 0 && age <= 120) COMMENT '年龄',
    status CHAR(1) DEFAULT '1' COMMENT '状态',
    gender CHAR(1) COMMENT '性别'
)

5.1 外键约束

5.1.1 外键约束 - 添加/删除外键

外键是用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
在这里插入图片描述
应该添加外键约束进行关联

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

例子：

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

# ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;

5.1.2 外键约束 - 删除/更新行为

行为说明
NO ACTION当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与REATRICT一致）
REATRICT当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致）
CASCADE当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新子表中的记录
SET NULL当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）
SET DEFAULT父表有变更时，子表将外键设置成一个默认的值（Innodb不支持）

例如：

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

6、 多表关系

项目开发中，在进行数据表结构设计时，会根据业务需求及业务模块之间的关系。分析并设计表结构，由于业务之间相互关联，所以各个表之间也存在着各种联系，基本上分为三种：

  • 一对多（多对一）
  • 多对多
  • 一对一

6.1 一对多（多对一）

案例： 部门与员工的关系
关系： 一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
在这里插入图片描述

6.2 多对多

案例： 学生与课程的关系
关系： 一个学生可以选修多门课程，一个课程也可以供多个学生选择
实现： 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
在这里插入图片描述

6.3 一对一

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

7、 多表查询

概述： 指从多表中查询数据。
笛卡尔积： 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）
在这里插入图片描述

7. 1 多表查询-分类

  • 连接查询
  1. 内连接： 相当于查询A、B交集部分数据
  2. 外连接：
    左外连接： 查询左表所有数据，以及两张表交集部分数据
    右外连接： 查询右表所有数据，以及两张表交集部分数据
  3. 自连接： 当前表与自身的连接查询，自连接必须使用表别名。
    在这里插入图片描述
  • 子查询

7. 2 连接查询-内连接

内连接查询语法：

  • 隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
  • 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 on 连接条件...;

内连接查询的是两张表的交集部分

例子：

# 内连接
# 1. 查询员工的姓名，以及部门的名称
# -- 表结构：emp，dept
# -- 连接条件： emp.dept_id = dept.id
# 这个没起别名哦
# 如果emp表中没有部门id或者id不在dept表中的部分没有查出来
SELECT emp.name, dept.name FROM emp, dept WHERE emp.dept_id = dept.id;
SELECT emp.name, dept.name FROM emp JOIN dept ON emp.dept_id = dept.id;

7. 3 连接查询-外连接

外连接查询语法：

  • 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据

  • 右外连接：
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据

例子：

# 外连接
SELECT emp.name, dept.name FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;

在这里插入图片描述

7. 4 连接查询-外连接

自连接查询语法：

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

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

情景： 例如：
在这里插入图片描述

# 自查询
# 1. 查询员工及其领导的名字
SELECT a.name, b.name FROM emp a, emp b WHERE a.managerid = b.id;

# 2. 查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
SELECT a.name, b.name FROM emp a LEFT JOIN emp b ON a.managerid = b.id;

7. 5 联合查询-union、union all

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

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

测试：

# 联合查询
# 查询薪资大于10000和年龄大于60的员工全部查询出来
SELECT * FROM emp WHERE salary > 10000
UNION ALL
SELECT * FROM emp WHERE age > 60;

# 查询薪资低于10000和年龄大于60的员工全部查询出来(去重)
SELECT * FROM emp WHERE salary > 10000
UNION
SELECT * FROM emp WHERE age > 60;

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

8. 子查询

  • 概念： SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

  • 根据子查询结果不同，分为：
  1. 标量子查询（子查询结果为单个值）
  2. 列子查询（子查询结果为一列）
  3. 行子查询（子查询结果为一行）
  4. 表子查询（子查询结果为多行多列）
  • 根据子查询位置，分为： WHERE之后，FROM之后，SELECT之后

8.1 子查询-标量子查询

子查询的结果为单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常见的操作符： = <> < <= > >=
例子：

# 标量子查询
# 1. 查询研发部的所有员工信息
# a. 查询研发部的部门id
SELECT id FROM dept WHERE name = '研发部';
# b. 根据部门id，查询员工信息
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name='研发部');

# 2. 查询在黎明1入职之后的员工信息
# a. 查询黎明1的入职时间
SELECT entrydate FROM emp WHERE name = '黎明1';
# b. 查询指定入职日期之后入职的员工信息
SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE name = '黎明1');

8.2 子查询-列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符： IN 、 NOT IN、 ANY、 SOME 、ALL

操作符描述
IN在指定的集合范围之内，多选1
NOT IN不在的指定的集合范围之内
ANY子查询返回列表中，有任意一个满足即可
SOME与ANY等同，使用SOME的地方都可以使用ANY
ALL子查询返回列表的所有值必须满足
例子：
# 1. 查询研发部和财务部的所有员工信息
# a. 查询研发部和财务部的部门id
SELECT id FROM dept WHERE name = '研发部' OR name = '财务部';
# b. 根据部门id，查询员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = '研发部' OR name = '财务部');

# 2. 查询比财务部所有人工资都高的员工信息
# a. 查询所有财务部人员工资
SELECT id FROM dept WHERE name = '财务部';
SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部');
# b. 比财务部所有人工资都高的员工信息
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));

# 3. 查询比财务部任意一人工资都高的员工信息
# a. 查询所有财务部人员工资
SELECT id FROM dept WHERE name = '财务部';
SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部');
# b. 查询比财务部任意一人工资都高的员工信息
SELECT * FROM emp WHERE salary > SOME (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));

8.3 子查询-行子查询

行子查询的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符： = 、<> 、IN 、 NOT IN
例子：

# 1. 查询与黎明1的薪资和领导相同的员工信息
# a. 查询黎明1的薪资和领导
SELECT salary, managerid FROM emp WHERE name = '黎明1';
# b. 查询与黎明1的薪资和领导相同的员工信息
SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = '黎明1');

8.4 子查询-表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符： IN
例子：

# 1. 查询与黎明1和三毛的薪资和领导相同的员工信息
# a. 查询黎明1和三毛的薪资和领导
SELECT salary, managerid FROM emp WHERE name = '黎明1' OR name = '三毛';
# b. 查询与黎明1和三毛的薪资和领导相同的员工信息
SELECT * FROM emp WHERE (salary, managerid) IN (SELECT salary, managerid FROM emp WHERE name = '黎明1' OR name = '三毛');

9、事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，既这种操作要么同时成功，要么同时失败。
数据库是单条语句就提交事务了

9.1 事务操作(改为手动提交事务)

查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit = 0;

提交事务

COMMIT;

回滚事务

ROLLBACK;

操作

# 事务
CREATE TABLE account(
    id int auto_increment primary key comment 'ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';
INSERT INTO account VALUE (1, '张三',  2000), (2, '李四', 2000);

# 恢复
UPDATE account SET money = 2000 WHERE name = '张三' OR name = '李四';


# 先设置事务为手动提交（针对当前窗口）
SELECT @@autocommit;
SET @@autocommit = 0;

#执行事务操作
# 转账操作（张三给李四转1000）
# 1. 查询张三的账户余额是否够1000
SELECT money FROM account WHERE name = '张三';
# 2. 张三金额少1000
UPDATE account SET money = money - 1000 WHERE name = '张三';
程序出异常...
# 3. 李四金额多1000
UPDATE account SET money = money + 1000 WHERE name = '李四';

# 如果没有异常就
COMMIT;
# 有异常
ROLLBACK;

9.2 事务操作(开启事务)

开启事务

START TRANSACTION 或 BEGIN;

提交事务

COMMIT;

回滚事务

ROLLBACK;

例子：

START TRANSACTION;
# 转账操作（张三给李四转1000）
# 1. 查询张三的账户余额是否够1000
SELECT money FROM account WHERE name = '张三';
# 2. 张三金额少1000
UPDATE account SET money = money - 1000 WHERE name = '张三';
程序出异常...
# 3. 李四金额多1000
UPDATE account SET money = money + 1000 WHERE name = '李四';


# 如果没有异常就
COMMIT;
# 有异常
ROLLBACK;

9.3 事务四大特性

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

9.4 并发事务问题

问题描述
脏读一个事务读到另一个事务还没有提交的数据。
不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

9.5 事务隔离级别

隔离级别脏读不可重复读幻读
Read uncommitted√√√
Read committed×√√
Repeatable Read××√
Serializable×××

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE ];

9.5.1 事务隔离级别 - 例子（脏读）：

Read uncommitted
窗口1：

# 验证隔离级别解决并发事务问题
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 1 

START TRANSACTION ; 2

SELECT name, money FROM account WHERE name = '张三';  3

SELECT name, money FROM account WHERE name = '张三';  6

窗口2

START TRANSACTION ; 4

UPDATE account SET money = money - 1000 WHERE name = '张三'; 5

第五步读到的结果是张三的账户为1000，窗口2没commit，窗口1居然读到了，脏读。

事务A开启事务先 查询张三的余额，事务B 开启事务，把张三的余额减少1000（不提交），事务A再读取张三的余额居然发现少了1000。

9.5.2 事务隔离级别 - 例子（不可重复读）：

窗口1：

# 验证隔离级别解决并发事务问题
SET SESSION TRANSACTION ISOLATION LEVEL [READ COMMITTED | READ UNCOMMITTED] ;  1

START TRANSACTION;  2

SELECT name, money FROM account WHERE name = '张三';  3

SELECT name, money FROM account WHERE name = '张三';  7

窗口2：

START TRANSACTION ;  4

UPDATE account SET money = money - 1000 WHERE name = '张三';  5

COMMIT;  6

初始张三的余额为2000
执行3的时候张三的余额为2000
执行7的时候张三的余额为1000
在同一个事务中，两次相同的查询结果不同，就是不可重复读。

事务A开启事务先 查询张三的余额，事务B 开启事务，把张三的余额减少1000（提交），事务A再读取张三的余额居然发现少了1000。

9.5.3 事务隔离级别 - 例子（幻读）：

窗口1：

SET SESSION TRANSACTION ISOLATION LEVEL [READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ] ;  1

START TRANSACTION; 2 

SELECT * FROM account WHERE id = 3; 3 

INSERT INTO account VALUE (3, '王五', 2000); 7

SELECT * FROM account WHERE id = 3; 8

窗口2：

START TRANSACTION ; 4
INSERT INTO account VALUE (3, '王五', 2000); 5
COMMIT ; 6

执行3 的时候查出来的结果为空
但是执行7 的时候数据库报错ERROR 1062 (23000): Duplicate entry '3' for key 'account.PRIMARY'
执行8 的时候查出来的结果还为空

事务A查询id为3的数据为空
事务B 插入id为3的数据提交，提交
事务A再插入id为3的数据报错，但是查询为3的数据还为空
这就是幻读。

总结

此文章为数据库基础知识，高阶知识再写文章。

weixin_45044818
关注
  • 10
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库基础篇之入门基础命令小结
09-08
主要介绍了MySQL数据库入门基础命令,总结分析了mysql数据库基本创建、删除、查看、权限等相关操作技巧与注意事项,需要的朋友可以参考下
【MySQL系列】MySQL数据库基础
哎呀是小张啊 的博客
07-14 1834
MySQL数据库的基础部分内容~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
【MySQL基础篇】MySQL数据库安装教程
hacker707的博客
09-12 5234
MySQL基础篇之MySQL数据库安装教程以及配置环境变量，连接数据库。持续更新MySQL从入门到精通系列专栏
Mysql数据库基础知识总复习
qq_48584557的博客
05-02 3413
sql常用语法总结,Mysql基础知识，数据，数据库，数据库管理系统，数据库系统，数据库的发展阶段，数据库系统的结构，数据模型，设计模式，数据库的创建修改，删除，表的怎删改查，，索引，事务
mysql数据库学习（基础篇）
qianbao55的博客
03-23 3254
mysql数据库学习（基础） 题记：将分为入门（基础、操作）、进阶（底层实现原理学习、优化）进行学习笔记总结。本文为第一阶段，基础部分。 文章目录mysql数据库学习（基础）SQL（重点）SQL通用语法DDL数据库操作：DMLDQLDCL（简略）函数（略）：约束：外键约束：多表查询事务并发事务问题 定义： 数据库概念（DataBase，DB） 存储数据的仓库，数据是有组织的进行存储。 数据库管理系统（DBMS） 操纵和管理数据库的大型软件 linux 命令行打开数据库： service mysql star
MySQL数据库基础教程
04-15
总的来说，"MySQL数据库基础教程"涵盖了数据库管理、SQL语言、存储过程、触发器、视图、事务、游标等核心概念。通过学习这个教程，初学者将能够熟练地安装、配置MySQL，并进行基本的数据操作和数据库设计，为进一步...
mysql 数据库基础笔记
09-11
这篇笔记主要涵盖了MySQL数据库的基础操作，对于初学者来说非常有帮助。以下是一些关键知识点的详细解释： 1. **安装与目录导航**： 在Windows环境下，MySQL的可执行文件通常位于安装目录的`bin`子目录下。你可以...
数据库01MySQL数据库开发基础篇-mysql数据库编程开发实训(基础篇)
06-28
学习 MySQL 开发需要掌握一定的基础知识和技能，以下是一个系统的学习路线：首先，需要了解 MySQL 的基本概念，包括数据库、表、字段、索引、视图、存储过程等。同时，需要了解 MySQL 的数据类型，包括整数、字符串...
MySQL 进阶（三）【SQL 优化】
最新发布
梦想是动物管理员
07-13 863
MySQL SQL 优化
mysql笔记1
m0_62975692的博客
07-11 344
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
如何快速将Excel定义的表结构转换为MySQL的建表语句
这家伙很懒，什么都没有留下
07-09 1163
在数据管理和数据库设计中，经常需要从Excel文件中获取表结构并快速转换为MySQL数据库的建表语句。这个过程不仅可以节省大量手动输入的时间，还能减少因人为错误导致的数据库设计问题。本文将详细介绍如何高效地完成这一过程，包括使用不同的方法和工具，以及提供实际案例和代码示例，旨在帮助初学者和技术人员快速掌握这一技能。
mysql bit 对gorm使用何种类型？
gdutlhh的专栏
07-11 273
目前我们在查询mysql后拿到的src 其实是[]uint8类型（实际上就是 []byte），接着就进入了case []byte 分支，然后这里直接把原数据通过string(s) 强制转换成字符串，导致原来的值 0x01 变成了字符面值为 SOH （通过查看ASCII表）代表标题开始的字符。只要你的类型实现了该接口，那么就可以操作bit类型了，于是我自定义了类型来接收bit类型。可见，在底层解析过程中，并不能把mysql的bit类型转化成go的bool。其实sql包提供了，scan接口：如下。
MySQL空间索引
你的指尖有改变世界的力量
07-10 264
空间类型是建立在空间类型字段上的。
mysql快速精通（一）DQL数据查询语言
不起眼小菜菜的博客
07-10 1099
文章建立在已知基础用法的前提下进行深入了解……
Mysql中存储过程、存储函数、自定义函数、变量、流程控制语句、光标/游标、定义条件和处理程序的使用示例
BADAO_LIUMANG_QIZHI的博客
07-11 978
MySQL 中使用 DECLARE 关键字来定义变量，定义变量的基本语法格式如下DECLARE 关键字用来声明变量。var name 数是变量的名称，可以同时定义多个变量。type参数用来指定变量的类型。DEFAULT value 子句为变量提供一个默认值。默认值可以是一个常数，也可以是一个表达式。如果没有给变量指定默认值，初始值为NULLDECLARE studentid char(10) DEFAULT '一年级';
MySQL Innodb存储引擎中，当页默认的大小是16K时，页中最多存放多少行的记录？
zxrhhm的博客
07-09 1075
MySQL Innodb存储引擎中，当页默认的大小是16K时，页中最多存放7992行数据记录
MySQL9.0的新特性
ZTBztb123456的博客
07-10 370
虽然这是 MySQL 8.3 版本的特性，但值得一提的是，它引入了支持标签的全局事务标识符（GTID），使得事务集的分组和识别更加直观。的旧客户端程序的认证请求，进一步加强了 MySQL 的安全性，并鼓励用户采用更安全的认证方法。命令，以及一些复制和服务器的相关选项，引导管理员采用更有效、更现代的操作方法。两张表，记录了系统变量的类型、作用域和允许的值，对于性能微调和维护至关重要。：新版本扩展了预处理语句的功能，支持数据定义语言（DDL）命令，如。：MySQL 8.3 版本引入了一个新的。
基于Python协同过滤的旅游景点推荐系统，采用Django框架，MySQL数据存储，Bootstrap前端，echarts可视化实现
weixin_49081159的博客
07-09 1172
随着旅游业的迅速发展，个性化旅游推荐系统成为提升用户体验和促进旅游市场增长的重要工具。本研究旨在设计并实现一种基于Python协同过滤的旅游景点推荐系统，结合Django框架、MySQL数据库存储、Bootstrap前端框架以及echarts数据可视化技术，为用户提供精准且个性化的旅游推荐服务。
关于MySQL mvcc
qq_40914968的博客
07-09 439
innodb mvcc 原理性解释

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值