文章目录
- 多表查询
- 内连接
- 隐式内连接
- 显示内连接
- 外连接
- 左外连接
- 右外连接
- 子查询
- 事务
- 事务隔离级别
多表查询
有时我们不仅需要一个表的数据,数据可能关联到俩个表或者三个表,这时我们就要进行夺标查询了。
- 数据准备:
创建一个部门表并且插入数据:
create table dept(id int primary key auto_increment,`name` varchar(20)
)
insert into dept (name) values ('开发部'),('市场部'),('财务部');
创建一个员工表并且插入数据:
create table emp (id int primary key auto_increment,name varchar(10),gender char(1), -- 性别salary double, -- 工资join_date date, -- 入职日期dept_id int,foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键)
)
- 查看数据信息。
select * from emp;
select * from dept;
- 查询员工以及其部门的全部信息
select * from emp,dept where emp.dept_id = dept.id;
这里我们可以看到我们查询出emp表以及dept表俩个表中的数据了,但是其中存在冗余数据,因此我们要查询出我们所需要的具体数据来提高我们的查询数据。
- 查询员工的姓名及其部门名称
select emp.name 员工姓名,dept.name 部门名称 from emp,dept where emp.dept_id = dept.id;
- 通过起别名的方式来使sql代码简单
select e.name 员工姓名,d.name 部门名称 from emp e , dept d where e.dept_id = d.id;
内连接
用左边表的记录去匹配右边表的记录
隐式内连接
看不到 JOIN 关键字,条件使用 WHERE 指定
语法:
SELECT 字段名 FROM 左表, 右表 WHERE 条件
示例:
select e.name 员工姓名,d.name 部门名称 from emp e , dept d where e.dept_id = d.id;
显示内连接
使用 INNER JOIN ... ON 语句, 可以省略 INNER
语法:
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
示例:
- 查询员工的全部信息
select * from emp inner join dept on emp.dept_id = dept.id;
- 查询员工姓名为猪八戒的全部信息,只要员工姓名和部门名称信息
select e.name as 员工名称,d.name as 部门名称 from emp e inner join dept d on e.dept_id = d.id and e.name = '猪八戒'
外连接
左外连接
用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL
语法:
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
- 数据准备
在部门表中增加一个销售部
insert into dept (name) values ('销售部');
- 使用左外连接查询(保证左表的数据全部显示)
select * from dept left outer join emp on dept.id = emp.dept_id;
这里我们可以看到部门表的信息全部显示了,但是右表中没有匹配的数据则全部为null。
右外连接
用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示 NULL
语法:
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
- 数据准备
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
- 使用右外连接查询(在内连接的基础上,保证右表的数据全部显示)
select * from dept right outer join emp on dept.id = emp.dept_id;
子查询
- 一个查询的结果做为另一个查询的条件
- 有查询的嵌套,内部的查询称为子查询
- 子查询要使用括号
子查询结果:
- 单行单列:
子查询结果只要是单行单列,肯定在 WHERE 后面作为条件
- 多行单列:
结果集类似于一个数组,父查询使用 IN 运算符
- 多行多列:
子查询结果只要是多列,肯定在 FROM 后面作为表
示例:
**单行单列:**查询最低工资的员工信息
select * from emp where salary = (select min(salary) from emp)
**多行单列:**查询员工工资低于6000的部门名称
select name from dept where id in (select id from emp where salary < 6000)
**多行多列:**查询出 2011 年以后入职的员工信息,包括部门名称
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;
事务
要么成功都成功,要么失败都失败
功能 | SQL 语句 |
---|---|
开启事务 | start transaction; |
提交事务 | commit; |
回滚事务 | rollback; |
- 数据准备
CREATE TABLE account (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),balance DOUBLE
);INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);
- 手动事务提交
过程图解:
示例(成功):
start transaction;-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';commit;
当我们开启事务之后,对张三和李四账户进行金额修改的时候,我们可以
看到金额都为1000,并没有发生改变,但是当我们进行提交的时候,账户的金额发生了改变。
示例(失败): 恢复数据张三和李四分别拥有1000元
-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';rollback;
当我们开启事务之后,对张三和李四账户进行金额修改,我们可以看到金额都为1000,并没有发生改变,我们使用事务的回滚,账户金额依然没有变。
- 自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕自动提交事务
事务原理(图解):
事务在开启之后,所有操作都会被保存到日志文件当中,事务日志只有在得到commit命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)
事务隔离级别
事务特性 | 含义 |
---|---|
原子性(Atomicity) | 每个事务都是一个整体,不可再拆分,事务中所有的 SQL 语句要么都执行成功,要么都失败。 |
一致性(Consistency) | 事务在执行前数据库的状态与执行后数据库的状态保持一致。 |
隔离性(Isolation) | 事务与事务之间不应该相互影响,执行时保持隔离的状态。 |
持久性(Durability) | 一旦事务执行成功,对数据库的修改是持久的。就算关机,也是保存下来的。 |
- MySQL 数据库有四种隔离级别
级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
---|---|---|---|---|---|---|
1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle 和 SQL Server |
3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL |
4 | 串行化 | serializable | 否 | 否 | 否 |
欢迎java热爱者了解文章,作者将会持续更新中,期待各位友友的关注和收藏。。。