VIP31/mysql/day03.sql

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


# 创建部门表
CREATE TABLE dept(
    did INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(20)
);

# 创建员工表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    gender CHAR(1), -- 性别
    salary DOUBLE, -- 工资
    join_date DATE, -- 入职日期
    dep_id INT,
    FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);


# 92 标准  笛卡尔乘积  重复数据
select * from dept d , emp e;

# 加条件 连表条件
select * from dept d , emp e where  d.did = e.dep_id ;



# 92 标准 内连接 = 92标准
# inner join  连表 条件 on
select * from dept d inner join emp e on d.did = e.dep_id;

# 省略 inner
select * from dept d join emp e on d.did = e.dep_id;

# 99标准 外连接
# 左外连接 left join  on     以左表为主 主表的内容全部保留。
select * from emp e left join dept d on d.did = e.dep_id;

# 右外连接 right join  on    以右表为主
select * from dept d right join emp e on d.did = e.dep_id;

select * from dept d left join emp e on d.did = e.dep_id;



# 查询工资高于猪八戒的员工信息
# 1.猪八戒工资
# 2. 大于  1 结果。员工信息。

select e.salary from emp e where e.NAME="猪八戒";

select * from emp e where e.salary > (
    select e.salary from emp e where e.NAME="猪八戒"
);

#子查询根据查询结果不同，作用不同
/*
    * 子查询语句结果是单行单列，子查询语句作为条件值，使用 =  !=  >  <  等进行条件判断
    * 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
    * 子查询语句结果是多行多列，子查询语句作为虚拟表  返回是表
 */


# 查询 '财务部' 和 '市场部' 所有的员工信息

# 子查询
select did from dept d where d.dname in ( '财务部','市场部');

select * from emp e where e.dep_id in (
    select did from dept d where d.dname in ( '财务部','市场部')
)

# 不用子查询
select e.* from emp e join dept d on d.did = e.dep_id where d.dname in ( '财务部','市场部');


# 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息

select  * from emp e where e.join_date > '2011-11-11';

select d.dname,e.* from dept d join  (
    select  e.NAME, e.dep_id  from emp e where e.join_date > '2011-11-11'
) e on d.did = e.dep_id;

# 注意 明确字段