VIPJAVA/02_JavaWeb/day03_mysql/day03.sql

# 创建部门表
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);



# 链接查询
# 内连接  获取交集

select * from emp,dept;  # 笛卡尔积

# 隐式查询 99标准
select * from emp e,dept d where e.dep_id = d.did;

# 显示查询 92
# 用 join连表  用 on 添加条件
select * from emp e join dept d on e.dep_id = d.did;

# 单独获取数据
# 获取人员的部门名称
select e.*,d.dname from emp e join dept d on e.dep_id = d.did;


-- 获取人员名称和对应部门名称
select e.NAME ,dname from dept inner join emp e on dept.did = e.dep_id;
-- 获取 20150101之后入职的人员名称和对应部门名称
select e.NAME ,dname from dept inner join emp e on dept.did = e.dep_id where e.join_date > '2012-12-01';

-- 获取 薪资大于5000的员工人员名称和对应部门名称
select
    e.NAME ,dname
from
    dept inner join emp e
on
    dept.did = e.dep_id
where
    e.salary > 5000;

# 外连接
    # 左右外链接

# 查询emp表所有数据和对应的部门信息（左外连接）
select e.*,d.dname from emp e left join dept d on d.did = e.dep_id;

# 查询dept表所有数据和对应的员工信息（右外连接）
select d.*,e.* from emp e right join dept d on d.did = e.dep_id;


# 子查询
# 需求: 查询工资大于孙悟空的人员.
#
# - 孙悟空的工资
select salary from  emp where NAME = '孙悟空';
# - 大于  孙悟空工资的人
select * from  emp
where salary > (
    select salary from  emp where NAME = '孙悟空'
);


#子查询练习
# 查询 '财务部' 和 '市场部' 所有的员工信息
# 市场和财务的id
select did from dept where dname = '财务部' or dname = '市场部';
# 查询员工 = 上面条件
select * from emp where emp.id in(
    select did from dept where dname = '财务部' or dname = '市场部'
    );

# 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息
# 用外连接  子查询虚拟表
select *from emp e where e.join_date > '2011-11-11';
#

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


# 插入数据
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
      did INT PRIMARY KEY PRIMARY KEY, -- 部门id
      dname VARCHAR(50), -- 部门名称
      loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
     id INT PRIMARY KEY,
     jname VARCHAR(20),
     description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp (
     id INT PRIMARY KEY, -- 员工id
     ename VARCHAR(50), -- 员工姓名
     job_id INT, -- 职务id
     mgr INT , -- 上级领导
     joindate DATE, -- 入职日期
     salary DECIMAL(7,2), -- 工资
     bonus DECIMAL(7,2), -- 奖金
     dept_id INT, -- 所在部门编号
     CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
     CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (did)
);
-- 工资等级表
CREATE TABLE salarygrade (
     grade INT PRIMARY KEY,   -- 级别
     losalary INT,  -- 最低工资
     hisalary INT -- 最高工资
);

-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES
                (10,'教研部','北京'),
                (20,'学工部','上海'),
                (30,'销售部','广州'),
                (40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
                 (1, '董事长', '管理整个公司，接单'),
                 (2, '经理', '管理部门员工'),
                 (3, '销售员', '向客人推销产品'),
                 (4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
           (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
           (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
           (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
           (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
           (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
           (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
           (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
           (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
           (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
           (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
           (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
           (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
           (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
           (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);


-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
         (1,7000,12000),
         (2,12010,14000),
         (3,14010,20000),
         (4,20010,30000),
         (5,30010,99990);

-- 练习
# 查询员工编号，员工姓名，工资，职务名称，职务描述
select
    e.id,e.ename , e.salary , j.jname ,j.description
from emp e inner join job j  on e.job_id = j.id;

# 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

select
    e.id,e.ename , e.salary , j.jname ,j.description , d.dname,d.loc
from
    emp e inner join
    job j join dept d
on e.dept_id = d.did and e.job_id = j.id;

# 查询员工姓名，工资，工资等级
select e.ename ,e.salary , s.grade
from  emp e join salarygrade s on e.salary between s.losalary and s.hisalary;

# 查询最高工资和最低工资的员工信息
select MAX(salary) from emp;
select MIN(salary) from emp;

select * from emp where salary in ((select MAX(salary) from emp),(select MIN(salary) from emp));

select * from emp where salary in (
                                   (select salary from emp order by salary desc limit 1),
                                   (select MIN(salary) from emp));


# 查询出部门编号、部门名称、部门位置、部门人数

select  d.did,d.dname,d.loc,count(*) from dept d inner join emp e on d.did = e.dept_id group by d.did;

select  d.did,d.dname,d.loc,count(e.dept_id) from dept d left join emp e on d.did = e.dept_id group by d.did


# 查看事务
select @@autocommit;

# set 设置  0  手动提交事务

set @@autocommit = 0;


# 修改数据
# 修改person内容
update person set name = 'ls' where id = 1;

commit ;


# 后面  jdbc  mybatis 事务   spring 事务