一、场景表
部门表--》dept 职务表--》job 员工表--》emp 工资等级表--》salarygrade
USE demotest;
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);
二、多表关联查询
-- 需求
-- 1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/* 分析：1. 员工编号，员工姓名，工资 信息在emp 员工表中
2. 职务名称，职务描述 信息在 job 职务表中
3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
*/
-- 隐式内连接
-- SELECT
-- e.id ,
-- e.ename ,
-- e.salary,
-- j.jname,
-- j.description
-- FROM
-- emp e,job j
-- WHERE
-- e.job_id = j.id;
-- -- 当表名写了简称之后，就不能在这样写了-->emp.job_id = job.id
-- 显式内连接
-- SELECT
-- e.id ,
-- e.ename ,
-- e.salary,
-- j.jname,
-- j.description
-- FROM
-- emp e
-- INNER JOIN job j ON e.job_id = j.id;
-- 2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/* 分析：1. 员工编号，员工姓名，工资 信息在emp 员工表中 2. 职务名称，职务描述 信息在 job 职务表中 3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id 4. 部门名称，部门位置 来自于 部门表 dept 5. dept 和 emp 一对多关系 dept.id = emp.dept_id */
-- 方式一 ：隐式内连接
-- SELECT
-- emp.id,
-- emp.ename,
-- emp.salary,
-- job.jname,
-- job.description,
-- dept.dname,
-- dept.loc
-- FROM
-- emp,job,dept
-- WHERE
-- emp.job_id = job.id
-- AND
-- emp.dept_id = dept.did;
-- 方式二 ：显式内连接
-- SELECT
-- emp.id,
-- emp.ename,
-- emp.salary,
-- job.jname,
-- job.description,
-- dept.dname,
-- dept.loc
-- FROM
-- emp
-- INNER JOIN job ON emp.job_id = job.id
-- INNER JOIN dept ON emp.dept_id = dept.did;
-- 3. 查询员工姓名，工资，工资等级
-- SELECT
-- emp.ename,
-- emp.salary,
-- t2.grade
-- FROM
-- emp ,salarygrade t2
-- WHERE
-- emp.salary >= t2.losalary
-- AND
-- emp.salary <= t2.hisalary ;
USE demotest;
-- -- 4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
-- -- 分析：
-- -- job表-->emp表 一对多 外键关联； dept表-->emp表 一对多 外键关联
-- -- 1.姓名、工资--> emp表
-- -- 2.职务名称、职务描述 --> job表
-- -- 3.部门名称、部门位置 -->dept表
-- -- 4.工资等级 --> salarygrade表
-- SELECT
-- emp.ename,
-- emp.salary,
-- job.jname,
-- job.description,
-- dept.dname,
-- dept.loc,
-- salarygrade.grade
-- FROM
-- emp,job,dept,salarygrade
-- WHERE
-- emp.job_id = job.id
-- AND
-- emp.dept_id = dept.did
-- AND
-- emp.salary >= salarygrade.losalary
-- AND
-- emp.salary <= salarygrade.hisalary;
-- \显式内连接
-- SELECT
-- emp.ename,
-- emp.salary,
-- job.jname,
-- job.description,
-- dept.dname,
-- dept.loc,
-- t2.grade
-- FROM
-- emp
-- INNER JOIN job ON emp.job_id = job.id
-- INNER JOIN dept ON emp.dept_id = dept.did
-- INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary AND t2.hisalary;
-- 5. 查询出部门编号、部门名称、部门位置、部门人数
-- 分析：
-- 部门编号、部门名称、部门位置 属于 dept 表
-- 部门人数 属于 emp 表
-- dept 和 emp 是一对多关系
/* 分析：1. 部门编号、部门名称、部门位置 来自于部门 dept 表
2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
3. 使用子查询，让部门表和分组后的表进行内连接 */
-- 首先根据部门id分组查询各部门人数
-- SELECT dept_id , COUNT(*) count_nums FROM emp GROUP BY dept_id;
-- 将此作为临时表，进行一对多查询
-- SELECT
-- dept.did,
-- dept.dname,
-- dept.loc,
-- t2.count_nums
-- FROM
-- dept,(SELECT dept_id , COUNT(*) count_nums FROM emp GROUP BY dept_id) t2
-- WHERE
-- dept.did = t2.dept_id;
三、mysql事物
-- 4.1 概述
-- 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。
-- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。
-- 事务是一个不可分割的工作逻辑单元。
-- 这些概念不好理解，接下来举例说明，如下图有一张表
-- 张三和李四账户中各有100块钱，现李四需要转换500块钱给张三
-- 开启事务
-- START TRANSACTION;
-- 或者BEGIN;
-- 提交事务
-- commit;
-- 回滚事务
-- rollback;
-- 环境准备
-- DROP TABLE IF EXISTS account;
-- -- 创建账户表
-- CREATE TABLE account(
-- id int PRIMARY KEY auto_increment,
-- name varchar(10), money double(10,2)
-- );
-- -- 添加数据
-- INSERT INTO account(name,money) values('张三',1000),('李四',1000);
-- 不加事务演示问题
-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 李四账户 -500
-- UPDATE account set money = money - 500 where name = '李四';
-- -- 出现异常了... -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行 --
-- -- 3. 张三账户 +500
-- UPDATE account set money = money + 500 where name = '张三';
-- 添加事务sql如下：
-- 开启事务
-- BEGIN;
-- -- 转账操作
-- -- 1. 查询李四账户金额是否大于500
-- -- 2. 李四账户 -500
-- UPDATE account set money = money - 500 where name = '李四';
-- -- 出现异常了... -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- -- 3. 张三账户 +500
-- UPDATE account set money = money + 500 where name = '张三';
-- -- 提交事务
-- COMMIT;
-- -- 回滚事务
-- ROLLBACK;
-- 上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。
-- 以后我们肯定不可能这样操作，而是在
-- java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。
-- 4.4 事务的四大特征
-- 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
-- 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
-- 隔离性（Isolation） :多个事务之间，操作的可见性
-- 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
-- 说明：
-- mysql中事务是自动提交的。
-- 也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。
-- 可以通过下面语句查询默认提交方式：
-- SELECT @@autocommit;
-- 查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式
-- set @@autocommit = 0;
--
//查询每个员工每年的总工资
USE demotest;
SELECT t1.e_name AS 姓名,DATE_FORMAT(t2.d_year,'%Y') AS a_year,SUM(t2.salary) AS 总工资
FROM employee t1 LEFT JOIN deptemp t2
ON t1.e_name = t2.d_name
GROUP BY t1.e_name,a_year;
目录
-- 将deptemp年份int类型换为时间类型date 年-月-日 datetime 年-月-日 时分秒
-- ALTER TABLE deptemp MODIFY d_year date;
-- 添加内容
-- -- -- INSERT INTO deptemp(d_year)
-- -- -- VALUES ('2000-01-01');
-- -- -- apply_id 为空：
-- -- -- update product_report_temp SET state = 0 WHERE apply_id is NULL;
-- -- -- apply_id 不为空：
-- -- -- update product_report_temp SET state = 1 WHERE apply_id <> "" or apply_id is not null;
-- UPDATE deptemp SET d_year = '2020-01-01' WHERE d_year IS NULL;
514
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
