mysql学习笔记-基础篇-蒲公英云

一、数据类型

二、DQL

DQL语句的执行顺序为： from … where … group by … having … select … order by … limit …

三、函数

3.1、字符串函数：

3.2、数值函数：

3.3、日期函数：

3.4、流程函数：

四、约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的：保证数据库中数据的正确、有效性和完整性。

4.1、外键约束

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

altertable emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

ALTERTABLE 表名 DROP FOREIGN KEY 外键名称;

altertable emp drop foreign key fk_emp_dept_id;

ALTERTABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名)ONUPDATE CASCADE ONDELETE CASCADE;

altertable emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id)onupdate cascade ondelete cascade ;

altertable emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id)onupdatesetnullondeletesetnull;

五、多表查询

5.1、多表关系

5.2、多表查询概述

5.3、内连接

5.4、外连接

5.5、自连接

注意事项:在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

5.6、联合查询

select*from emp where salary <5000

union 【all】

select*from emp where age >50;

5.7、子查询

5.7.1、标量子查询

5.7.2、列子查询

1.查询比财务部所有人工资都高的员工信息

select*from emp where salary > all (select salary from emp where dept_id =

(select id from dept where name =’财务部’));

2.查询比研发部其中任意一人工资高的员工信息

select*from emp where salary > any (select salary from emp where dept_id =

(select id from dept where name =’研发部’))

5.7.3、行子查询

1.查询与 “张无忌” 的薪资及直属领导相同的员工信息

select*from emp where(salary,managerid)=(select salary, managerid from emp where name =’张无忌’);

5.7.4、表子查询

1.查询与 “鹿杖客”,”宋远桥” 的职位和薪资相同的员工信息

select*from emp where(job,salary)in(select job, salary from emp where name =’鹿杖客’or name =’宋远桥’);

2.查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

select e.*, d.*from(select*from emp where entrydate >’2006-01-01’) e left join dept d on e.dept_id= d.id;

总结

六、事务

6.1、事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

6.2、事务操作

6.2.1、控制事务一

6.2.2、控制事务二

6.3、事务四大特性（ACID）

6.4、并发事务问题

6.5、事务隔离级别

1). 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

2). 设置事务隔离级别

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

6.5.1、Read uncommitted（读未提交）

提供了事务建最小限度的隔离。顾名思义，就是一个事务可以读取另一个未提交事务的数据。

示例：小明去商店买衣服，付款的时候，小明正常付款，钱已经打到商店老板账户，但是小明发起的事务还没有提交。就在这时，商店老板查看自己账户，发现钱已到账，于是小明正常离开。小明在走出商店后，马上回滚差点提交的事务，撤销了本次交易曹邹。

结果：小明未付钱买到了衣服，商店老板实际未收到小明的付款。

分析：商店老板查看自己的资金账户，这个时候看到的是小明还没有提交事务的付款。这就是脏读。

注意：处于该隔离级别的事务A与B，如果事务A使用事务B不提交的变化作为计算的基础，然后哪些未提交的变化被事务A撤销，这就导致了大量的数据错误变化。

6.5.2、Read committed （读已提交）

处于Read committed （读已提交）级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的同一sql在其他事务执行前后返回的是不同的结果。一个事务要等另一个事务提交后才能读取数据。

示例：小明卡里有1000元，准备与几个朋友聚餐消费，消费1000元，当他买单时（事务开启），收费系统检测到他卡里有1000元。就在检测完毕的时候，小明女朋友发现小明有私房钱，全部转走并提交。当收费系统准备扣款时，再检查小明卡里的金额，发现已经没钱了，付款不成功。小明此时就会很纳闷，明明有钱的呀，钱呢？

分析：该示例中同一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。该隔离级别可以解决脏读问题。

6.5.3、Repeatable read （可重复读）

在开始读取数据（事务开启）时，不再允许修改操作。

示例：还是小明有1000元，准备跟朋友聚餐消费这个场景，当他买单（事务开启）时，收费系统检测到他卡里有1000元，这个时候，他的女朋友不能转出金额。接下来，收费系统就可以扣款成功了，小明醉醺醺的回家，准备跪脱衣板。

分析：重复读可以解决不可重复读的问题，这句话有些别扭，大家可以仔细品一下。

写到这里，大家可能会产生疑问，什么情况下产生幻读呢？

示例来了：

小明在公司上班，女朋友告诉他，拿着他的卡去逛街消费。花了一千元，然后小明去查看他银行卡的消费记录（事务开启），看到确实是花了一千元。就在这个时候，小明女朋友又花三千元买了一些化妆品和衣服，即新增了一些消费记录。当小明打印自己银行卡消费记录单的时候（女朋友事务提交），发现花了四千元，似乎出现了幻觉，小明很心疼。这就是幻读

扩展：当我们开启一个事务以后，有如下的程序操作

第一步：更新A表id=1的记录

第二步：查询A表id=1的记录

第三步：使用第二步的查询结果作为依据继续业务逻辑

第四步：提交事务

问题来了：同一个事务中，事务未提交前，第二步的查询结果是第一步执行前的结果还是第一步执行后的结果呢？

答案：事务隔离级别是针对不通事务的，同一事务中的未提交的更新，在后续是可以查询到的。

6.5.4、Serializable （序列化）

数据库事务的最高隔离级别。在此级别下，事务串行执行。可以避免脏读、不可重复读、幻读等读现象。但是效率低下，耗费数据库性能，不推荐使用。

注意：

①事务隔离级别越高，数据越安全，但是性能越低；

②mysql默认REPEATABLE READ，可以避免脏读，不可重复读，不可避免幻读；oracl默认READ COMMITTED。