MySQL 之 DML-3
1.根据下图和提供的sql，写出对应语句：
(1) 建表：
CREATE TABLE `tbl_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`locAdd` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
(2) 代码：
1 A、B两表共有：select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
2 A、B两表共有+A的独有：select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
3 A、B两表共有+B的独有： select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
4 A的独有：select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
5 B的独有：select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
6 AB全有：
#MySQL Full Join的实现 因为 MySQL不支持 FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id
UNION ALL
SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id
7 A的独有+B的独有
SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;
2.查询与141号或174号员工的manager_id 和department_id 相同的其他员工的employee_id，manager_id,department_id。
SELECT employee_id,manager_id,department_id FROM employees
WHERE manager_id IN (
SELECT manager_id FROM employees WHERE employee_id IN (141,174)
)AND department_id IN (
SELECT department_id FROM
employees WHERE employee_id IN (141, 174)
)AND employee_id NOT IN (141, 174);
--或者
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN (
SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174)
) AND employee_id NOT IN (141,174);
3.返回比本部平均工资高的员工last_name,department_id, salary及平均工资
SELECT last_name, e1.department_id, salary, e2.avg_sal
FROM employees e1, (
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) e2
WHERE e1.department_id = e2.department_id
AND e1.salary > e2.avg_sal;
4.判断公司所有员工，若在10部门，打印工资的1.1倍，20部门，打印工资的1.2倍，30部门，打印工资的1.3部门，其他的打印工资的1.4倍
-case - when - then 表达式(mysql,oracle数据库都可以使用)
select employee_id, last_name, salary, department_id, case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
when 30 then salary * 1.3
end "new_salary"
from employees
where department_id in (10, 20, 30);
-decode 函数(只适用于oracle数据库)
select employee_id, last_name, salary, department_id, decode(department_id, 10, salary * 1.1
, 20, salary * 1.2
, 30, salary * 1.3
,salary * 1.4) " new_salary"
from employees;
5.显示员工的employee_id, last_name和location。其中若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。
SELECT employee_id,last_name,CASE department_id WHEN(
SELECT department_id
FROM departments
WHERE location_id = 1800
)THEN 'Canada' ELSE 'USA' END "location"
FROM employees;
6.查询员工的employee_id, last_name，要求按照员工的department_name排序
SELECT employee_id,last_name
FROM employees e1
ORDER BY (
SELECT department_name
FROM departments d
WHERE d.department_id = e1.department_id
)
7.查询department表中，不存在employees表中的部门的department_id和department_name
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 'x'
FROM employees e
WHERE d.department_id = e.department_id
)
8.更改108员工的信息：使其工资为所在部门中的最高工资，job变为公司中平均工资最低的job
UPDATE employees e1
SET salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
),job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)=(
SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id
)
)
WHERE employee_id = 108;
9.删除108号员工所在部门中工资最低的那个员工
DELETE FROM employees
WHERE employee_id IN(
SELECT employee_id
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE employee_id = 108
)
)
)
说明：如果需要以上操作sql的创建语句和数据，请：http://download.****.net/download/luomingkui1109/10228754