标题: 【成都校区】数据库多表查询 [打印本页] 作者: 江山美人 时间: 2019-4-17 13:55 标题: 【成都校区】数据库多表查询 # 创建部门表
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) -- 外键，关联部门表(部门表的主键)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
-- 查询每个部门的人数和部门名称
SELECT
COUNT(t1.`id`),t2.`NAME`
FROM
emp t1,dept t2
WHERE
t1.`dept_id`=t2.`id`
GROUP BY
dept_id;
-- 查询部门是财务部的员工信息
SELECT
*
FROM
emp t1, dept t2
WHERE
t1.`dept_id`=t2.`id` AND t2.`NAME`="财务部";
-- 查询工资最高的员工信息
SELECT
*
FROM
emp t1
WHERE
t1.`salary` =( SELECT MAX(salary) FROM emp) ;
-- 查询'财务部'和'市场部'的员工信息
SELECT
*
FROM emp t1,dept t2
WHERE t1.`dept_id`=t2.`id` AND (t2.`NAME`="财务部" OR t2.`NAME`="市场部");
-- 查询'市场部'工资信息大于’财务部‘所有员工的工资
SELECT
*
FROM
emp ,dept
WHERE emp.`dept_id`=dept.`id` AND dept.`NAME`="市场部" AND
emp.`salary`> ALL(SELECT salary FROM emp,dept WHERE emp.`dept_id`=dept.`id` AND dept.`NAME`="财务部");
-- 查询'市场部'工资信息大于’财务部‘最高员工工资的员工信息
SELECT
*
FROM
emp ,dept
WHERE emp.`dept_id`=dept.`id` AND dept.`NAME`="市场部" AND
emp.`salary`> (SELECT MAX(salary) FROM emp,dept WHERE emp.`dept_id`=dept.`id` AND dept.`NAME`="财务部");
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT
m1.*,m2.`NAME`
FROM
emp m1,dept m2
WHERE
m1.`join_date`>"2011_11-11" AND m1.`dept_id`=m2.`id`;
-- 查询部门人数大于1个人的员工信息
SELECT m1.*,m3.NAME
FROM
emp m1,(SELECT COUNT(dept_id),dept_id FROM emp GROUP BY emp.`dept_id` HAVING COUNT(dept_id)>1) m2 ,dept m3
WHERE
m1.`dept_id`=m2.dept_id AND m1.`dept_id`=m3.`id`;