MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一:https://developer.aliyun.com/article/1531168
字符串函数 数值函数 日期函数 流程函数
作用类似于C语言的三目操作符
约束
- 概念:约束是作用于表中字段上的规则,用于限制存储在表中数据。
- 目的:保证数据库中数据的正确、有效性和完整性。
- 分类: 一般的约束我们看一下之前的例题就可以明白其用法:
- 根据需求,完成表格的创建:
create table user( id int primary key auto_increment comment '主键', name varchar(10) not null unique comment '姓名', age int check(age > 0 and age <= 120) comment '年龄', status char(1) default '1' comment '状态', gender char(1) comment '性别' ) comment '用户表';
重点看一下外键的用法:
添加外键
CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主列表名) ); -- 或 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCE 主表 (主列表名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
外键删除/更新行为
1. ALTER TABLE 表名 2. ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCE 主表 (主列表名) 3. ON UPDATE CASCADE ON DELETE CASCADE;
多表查询
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
笛卡尔积
笛卡尔积是指在数学中,两个集合A和集合B的所有的组合情况。
例如A、B 和1、2、3、4组合,不同的组合情况就有八种:
- A 1
- A 2
- A 3
- A 4
- B 1
- B 2
- B 3
- B 4
所以,在多表查询时,需要消除无效的笛卡尔积。
内连接
隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
相当于查询表1(左表)的所有数据,包含表1和表2交集部分的数据
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
相当于查询表2(右表)的所有数据,包含表1和表2交集部分的数据
自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
自连接查询,可以是内连接查询,也可以是外连接查询。
联合查询
查询语法
SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ...;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT 的任何一个。
根据子查询的结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:WHERE之后、FROM之后、SELECT之后。
事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
1.事务操作
1. START TRANSACTION; -- 开启事务 2. COMMIT / ROLLBACK; -- 提交/回滚事务
2.事务四大特性
原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
原子性(Atomicity)
- 事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
当一个事务在执行时,它会将一组SQL语句放在同一批次内去执行。如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行。
一致性(Consistency)
- 事务完成时,必须使所有的数据保持一致状态。
例如,银行转账可以分解为两个动作:从A账户中扣除资金额X,然后将资金额X加到B账户中。如果在这个过程中发生了错误,那么整个操作都应该被回滚到初始状态。保证A和B加起来的账户余额要和初始状态一致。
隔离性(Isolation)
数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
例如,一个事务正在修改某个数据项,此时另一个事务也想要修改这个数据项,但是由于隔离性的存在,第二个事务会等待第一个事务完成修改后再进行修改 。
持久性(Durability)
- 事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
事务一旦提交,它对数据库的改变就应该是永久性的(将数据提交保存在了硬盘中),接下来的其他操作或故障不应该对本次事务的修改有任何影响 。
3.并发事务问题
(1)脏读
一个事务读到另外一个事务还没有提交的数据。
(2)不可重复读
一个事务先后读到同一条记录,但两次读取的数据不同,称之为不可重复读。
(3)幻读
一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。
4.事务隔离级别
READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE(串行化)
END