四、多表查询
上面写的查询是DQL最基础的单表查询,这里说说多表查询。
多表关系介绍
各个表结构之间存在的联系分为三种:一对多、多对多、一对一。
一对多
例子:一个部门可能有多个员工,一个员工对应一个部门。
实现方法:在多的一方建立外键,指向一的一方的主键。
多对多
例子:一个学生可以选修多门课程,一门课程也可以被多个学生选择。
实现方法:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。
创建学生表与课程表:
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
name VARCHAR(10) COMMENT '姓名',
num VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';
INSERT INTO student VALUES (NULL, 'viper', '2024001'), (NULL, 'jett', '2024002'), (NULL, 'reyna', '2024003'), (NULL, 'clove', '2024004');
CREATE TABLE course(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
name VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';
INSERT INTO course VALUES (NULL, 'Python'), (NULL, 'Java'), (NULL, 'MySQL'), (NULL, 'HTML');
创建学生课程中间表:
CREATE TABLE student_course(
id INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
studentid INT NOT NULL COMMENT '学生ID',
courseid INT NOT NULL COMMENT '课程ID',
CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course(id),
CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student(id)
) COMMENT '学生课程中间表';
INSERT INTO student_course VALUES (NULL,1 ,1), (NULL,2 ,2), (NULL,1 ,2), (NULL, 1,3), (NULL, 2,3), (NULL,3 ,4);
学生表:
课程表:
学生课程中间表:
一对一
例子:用户与用户详情的关系
作用:多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率。
实现方法:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一(UNIQUE)
创建用户表、学历表:
CREATE TABLE user(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
name VARCHAR(15) COMMENT '姓名',
age INT COMMENT '年龄'
) COMMENT '用户基本信息表';
CREATE TABLE user_edu(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
degree VARCHAR(20) COMMENT '学历',
userid INT UNIQUE COMMENT '用户ID',
CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES user(id)
) COMMENT '用户教育信息表';
加入数据:
INSERT INTO user(id, name, age) VALUES
(NULL, '张三', 30), (NULL, '李四', 32), (NULL, '王五', 26);
INSERT INTO user_edu(id, degree,userid) VALUES
(NULL, '本科', 1), (NULL, '硕士', 2), (NULL, '专科', 3);
用户表:
学历表:
查询
user表中有3条数据,user_edu表中有2条数据,所以这样查会出来3*3 = 9条。
笛卡尔积:两个集合A集合和B集合的所有组合情况
从上面这张图也可以看出在多表查询时,需要消除无效的笛卡尔积。
SELECT * FROM user, user_edu WHERE user.id = user_edu.id;
(一)连接查询
1.内连接:查询A与B交集部分数据
2.外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
3.自连接:当前(只有一张)表与自身的连接查询,自连接必须使用表别名
(二)子查询
内连接
隐式内连接和显式内连接的区别在于连接方式和条件。
隐式内连接:
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接:
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
例子:
表结构:user, user_edu
连接条件:user.id = user_edu.id
隐式:
SELECT user.name, user_edu.degree FROM user, user_edu WHERE user.id = user_edu.id;
也可以简写为:
SELECT u.name, e.degree FROM user u, user_edu e WHERE u.id = e.id;
显式:
SELECT u.age, e.degree FROM user u INNER JOIN user_edu e ON u.id = e.id;
外连接
左外连接:查询左表的所有数据与左表右表交集部分数据。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
右外连接:查询右表的所有数据与左表右表交集部分数据。
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
例子:
SELECT u.*, e.degree FROM user u LEFT OUTER JOIN user_edu e ON u.id = e.id;
SELECT e.degree, u.* FROM user_edu e RIGHT OUTER JOIN user u ON u.id = e.id;
运行结果皆是下图
自连接查询
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
例子:
CREATE TABLE IF NOT EXISTS worker(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
name VARCHAR(16) COMMENT '姓名',
bossid INT COMMENT '上司ID'
) COMMENT '职工表';
INSERT INTO worker(id, name, bossid) VALUES (NULL, '张1', NULL), (NULL, '张2', 1), (NULL, '张3', 2), (NULL, '张4', 3),
(NULL, '张5', 3), (NULL, '张6', 4), (NULL, '张7', 4), (NULL, '张8', 6);
-- 查询员工及所属领导的名字(用自连接)
SELECT a.name, b.name FROM worker a, worker b WHERE a.id = b.bossid;
-- 查询员工及其所属领导名字,没有领导也要查询出来(用外连接)
SELECT a.name, b.name FROM worker a LEFT JOIN worker b ON a.bossid = b.id;
联合查询
概念:把多次查询的结果合并起来形成一个新的查询结果集。
优点:比OR效率更高。
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
UNION ALL会将全部数据直接合并,UNION会对合并之后的数据去重。
注意:第一行后面没有分号
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
例子:
年龄小于等于21的员工与位于广东分公司的员工
SELECT * FROM identity WHERE province = '广东'
UNION ALL
SELECT * FROM identity WHERE age <= 21;
-- 合并后去重
SELECT * FROM identity WHERE province = '广东'
UNION
SELECT * FROM identity WHERE age <= 21;
子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称为子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询外部语句可以是INSERT/UPDATE/DELETE/SELECT中的任何一个。
根据子查询结果不同,分为:
标量子查询 | 子查询结果为单个值 |
列子查询 | 子查询结果为一列 |
行子查询 | 子查询结果为一行 |
表子查询 | 子查询结果为多行多列 |
根据子查询位置,分为WHERE之后,FROM之后,SELECT之后。
常用的操作符有: = <> > >= < <=以及下面五个
IN | 在指定的集合范围内,多选一 |
NOT IN | 不在指定的集合范围内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值都必须满足 |
标量子查询
返回结果是单个值(数字、字符串、日期),最简单的形式,这种子查询称为标量子查询。
常用的操作 = <> > >= < <=
例1:找出开发部的人员信息
CREATE TABLE departments(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '卡片ID',
department VARCHAR(20) COMMENT '卡片所属部门'
)
INSERT INTO departments(id, department) VALUES (NULL, '销售部'), (NULL, '开发部'), (NULL, '财务部'), (NULL, '运维部');
正常来说要两步
SELECT id FROM departments WHERE department = '开发部';
SELECT * FROM identity WHERE cardid = 2;
但他们可以合并为
SELECT * FROM identity WHERE cardid = (SELECT id FROM departments WHERE department = '开发部');
例2:查询阿杰入职后入职的人员信息
SELECT * FROM identity WHERE dayandtime > (SELECT dayandtime FROM identity WHERE name = '阿杰');
列子查询
返回的结果是一列,可以有多行
常用操作符:IN、NOT IN、 ANY、SOME、ALL
例1:查询镇街单位和县级单位的所有员工信息
SELECT id FROM area WHERE classify = '县级单位' OR classify = '镇街单位';
SELECT * FROM identity WHERE cardid in (2, 3);
可以合并为
SELECT * FROM identity WHERE cardid in (SELECT id FROM area WHERE classify = '县级单位' OR classify = '镇街单位');
例2:查询比广东分公司所有人都早入职的员工信息
SELECT * FROM identity WHERE age > ALL (SELECT age FROM identity WHERE province = '广东');
例3:查询比县级单位所有人都早入职的员工信息
SELECT * FROM identity WHERE dayandtime < ALL (SELECT dayandtime FROM identity WHERE cardid = (SELECT id FROM area WHERE classify = '县级单位'));
例4:查询比县级单位任意一人早入职的员工信息
SELECT * FROM identity WHERE dayandtime < ANY (SELECT dayandtime FROM identity WHERE cardid = (SELECT id FROM area WHERE classify = '县级单位'));
行子查询
返回的结果是一行,可以有多列
常用操作符:=、<、>、IN、NOT IN
SELECT * FROM identity WHERE (province, gender) = ('广东', '男');
SELECT * FROM identity WHERE (province, gender) = (SELECT province, gender FROM identity WHERE name = '松神');
表子查询
返回的结果是多行多列
常用操作符:IN
例子:入职日期是2022-01-01之后的员工信息及所属单位性质
SELECT i.*, a.* FROM (SELECT * FROM identity WHERE dayandtime >'2022-01-01') i LEFT JOIN area a ON i.cardid = a.id;
五、事务
概念
事务是一组操作的集合,不可分割,会把所有的操作作为一个整体一起向系统提交或撤销操作请求。要么同时成功,要么同时失败。
举例:A给B转账1000元,A的账户扣除1000元和B的账户增加1000元这两件事必须都成功或都失败。
MySQL的事务是自动提交的。
操作
查看提交方式:0为手动提交,默认值1为自动提交
SELECT @@autocommit;
设置事务提交方式
SET @@autocommit = 0;
提交事务
COMMIT;
回滚事务
ROLLBACK;
例:
SELECT @@autocommit;
SET @@autocommit = 0;
-- 转账操作
-- 查询A的余额
SELECT * FROM account WHERE name = 'A';
-- A账户余额减少
UPDATE account SET money = money - 1000 WHERE name = 'A';
-- B账户余额增加
UPDATE account SET money = money + 1000 WHERE name = 'B';
COMMIT;
ROLLBACK;
开始事务
START TRANSACTION;
或
BEGIN;
例:
SET @@autocommit = 1;
START TRANSACTION;
SELECT * FROM account WHERE name = 'A';
UPDATE account SET money = money - 1000 WHERE name = 'A';
UPDATE account SET money = money + 1000 WHERE name = 'B';
COMMIT;
ROLLBACK;
事务四大特性ACID
原子性 | Atomicity | 事务时不可分割的最小操作单元,要么全部成功,要么全部失败。 |
一致性 | Consistency | 事务完成时,必须使所有的数据都保持一致状态。 |
隔离性 | Isolation | 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。 |
持久性 | Durability | 事务一旦提交或回滚,对数据库中数据的改变是永久的。 |
并发事务问题
脏读 | 一个事务读到另一个事务还没提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是再插入数据时,又发现这行数据已经存在 |
时间轴举例(从上到下)
脏读
1.A事务、B事务开始
2.A事务查询ID为6的记录
3.A事务修改ID为6的记录,此时A事务还未提交
4.B事务查询ID为6的记录
此时B没有读到A还未提交的数据,为脏读。
不可重复读
1.A事务、B事务开始
2.A事务查询ID为7的记录
3.B事务修改ID为7的记录
4.A事务查询ID为7的记录
此时A两次读到ID为7的记录不一致,为不可重复读。
幻读
1.A事务、B事务开始
2.A事务查询ID为8的数据,没有查询到
3.B事务插入ID为8的数据
4.A事务插入ID为8的数据
5.A事务查询ID为8的数据,仍然没有查询到
A事务和B事务都插入了ID为8的数据
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed(Oracle默认) | × | √ | √ |
Repeatable Read(MySQL默认) | × | × | √ |
Serializable | × | × | × |
Serializable 性能最低;Read uncommitted 性能最高。事务隔离级别越高,数据越安全,性能越低
-- 查看事务隔离级别:
SELECT @@TRANSACTION_ISOLATION;
-- 设置事务隔离级别:
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
SESSION 是会话级别,表示只针对当前会话有效,GLOBAL 表示对所有会话有效
六、整理常用的数据类型
整型
TINYINT | -128〜127 | 1个字节 |
SMALLINT | -32768〜32767 | 2个宇节 |
MEDIUMINT | -8388608〜8388607 | 3个字节 |
INT (INTEGHR) | -2147483648〜2147483647 | 4个字节 |
BIGINT | -9223372036854775808〜9223372036854775807 | 8个字节 |
如果要无符号的,那就在类型后面加UNSIGHED
CREATE TABLE good2(
id INT COMMENT 'id',
name VARCHAR(20) COMMENT '姓名'
) COMMENT '货物2';
INSERT INTO good2 VALUES(-2, 'ttt');
上面这个表的创建与插入负数值不报错。
CREATE TABLE good(
id INT UNSIGNED COMMENT 'id',
name VARCHAR(20) COMMENT '姓名'
) COMMENT '货物';
INSERT INTO good VALUES(-2, 'ttt');
上面这个表的创建不报错,插入负数值报错。
浮点型
FLOAT | 单精度浮点数 | 4 个字节 |
DOUBLE | 双精度浮点数 | 8 个字节 |
DECIMAL (M, D),DEC | 压缩的“严格”定点数 | M+2 个字节 |
日期时间类型
YEAR | YYYY | 1901 ~ 2155 | 1 个字节 |
TIME | HH:MM:SS | -838:59:59 ~ 838:59:59 | 3 个字节 |
DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 个字节 |
DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8 个字节 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC | 4 个字节 |
字符串
CHAR(M) | 固定长度非二进制字符串 | M 字节,1<=M<=255 |
VARCHAR(M) | 变长非二进制字符串 | L+1字节,在此,L< = M和 1<=M<=255 |
TINYTEXT | 非常小的非二进制字符串 | L+1字节,在此,L<2^8 |
TEXT | 小的非二进制字符串 | L+2字节,在此,L<2^16 |
MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节,在此,L<2^24 |
LONGTEXT | 大的非二进制字符串 | L+4字节,在此,L<2^32 |
ENUM | 枚举类型,只能有一个枚举字符串值 | 1或2个字节,取决于枚举值的数目 (最大值为65535) |
SET | 一个设置,字符串对象可以有零个或 多个SET成员 | 1、2、3、4或8个字节,取决于集合 成员的数量(最多64个成员) |
二进制
BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
BINARY(M) | 固定长度二进制字符串 | M 字节 |
VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
TINYBLOB (M) | 非常小的BLOB | L+1 字节,在此,L<2^8 |
BLOB (M) | 小 BLOB | L+2 字节,在此,L<2^16 |
MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节,在此,L<2^24 |
LONGBLOB (M) | 非常大的BLOB | L+4 字节,在此,L<2^32 |