多表查询

CREATE DATABASE date3;
use date3;
– 创建部门表
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,dept ;

– 如何清除笛卡尔积现象的影响 设置过滤条件
– 查询员工和部门的名字
SELECT *FROM emp,dept WHERE emp.dept_id=dept.id;

– 隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定
SELECT *FROM emp,dept WHERE emp.dept_id=dept.id;

– 显式内连接 有 inner join关键字 条件用on 可以省略 INNER
SELECT emp.name,dept.name from emp join dept on emp.dept_id = dept.id where emp.name=‘唐僧’;

/*总结内连接查询步骤：

1. 确定查询哪些表
2. 确定表连接的条件
3. 确定查询的条件
4. 确定查询的字段*/

– 左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略
SELECT E.name,E.id,D.name FROM emp E LEFT JOIN dept D ON E.dept_id = D.id;

– 在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

– 右外连接：使用 RIGHT OUTER JOIN … ON，OUTER 可以省略
SELECT E.name,E.id,D.name FROM dept D RIGHT JOIN emp E ON E.dept_id = D.id;
– 在内连接的基础上保证右表的数据全部显示(左表是部门，右表员工)

– 子查询的概念：
/1) 一个查询的结果做为另一个查询的条件
2) 有查询的嵌套，内部的查询称为子查询
3) 子查询要使用括号/

/子查询的结果是一个值的时候
 子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=
等/
– 查询最高工资是多少
SELECT max(salary) from emp;
– 根据最高工资到员工表查询到对应的员工信息
SELECT emp.name FROM emp WHERE salary= (select max(salary) from emp);

– 查询工资小于平均工资的员工有哪些
select avg(salary)from emp;

– 根据平均工资到员工表查询低于平均工资的人
select emp.name FROM emp where salary<(select avg(salary)from emp);

/*子查询结果是多行单列的时候
 子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符/

– 查询工资大于 5000 的员工，来自于哪些部门的名字
select emp.dept_id from emp where salary>5000;

select dept.name from dept where dept.id in (select dept_id from emp where salary>5000) ;

– 4.4.2 查询开发部与财务部所有的员工信息

SELECT *from emp where dept_id in (SELECT id FROM dept where name in(‘开发部’,‘财务部’));

– 子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

– -- 查询出 2011 年以后入职的员工信息，包括部门名称
– z在员工表中查询日期 大于2011年加入的人,生成虚拟表
SELECT *FROM emp WHERE join_date >2011;
– 给表起别名,筛选条件
SELECT * FROM dept d ,(SELECT *FROM emp WHERE join_date >2011) e WHERE d.id=e.dept_id;

– 第二种方式,表与表连接
select * from emp inner join dept on emp.dept_id = dept.id where
join_date >=‘2011-1-1’;

/子查询结果只要是单列，则在 WHERE 后面作为条件
 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询/

– 事务
– 事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的
– SQL 语句都要回滚，整个业务执行失败。

CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
INSERT INTO account (NAME, balance) VALUES (‘张三’, 1000), (‘李四’, 1000);

– 张三账号-500
UPDATE account SET balance = balance - 500 WHERE NAME=‘张三’;

– 李四账号+500
UPDATE account SET balance =balance +500 WHERE NAME= ‘李四’;

START TRANSACTION ;
UPDATE account SET balance = balance - 500 WHERE NAME=‘张三’;
ROLLBACK ;
UPDATE account SET balance =balance +500 WHERE NAME= ‘李四’;
COMMIT ;
UPDATE account SET balance = balance - 500 WHERE NAME=‘张三’;
commit ;
– 查看是否自动提交事务 1 是手动 0是自动
SELECT @@autocommit;
– 设置提交事务为自动或者手动
SET @@autocommit=1;

– 查看级别
select @@tx_isolation;
– 设置级别
set global transaction isolation level repeatable read;