MySQL的题2答案

CREATE  DATABASE homework;
USE homework;
-- 创建Employee表(中间表)
CREATE TABLE Employee(
num INT,
NAME VARCHAR(10),
addr VARCHAR(20),
zip VARCHAR(20),
tel VARCHAR(20),
email VARCHAR(40),
birth DATE,
sex CHAR(2),
depno INT
);
DESC Employee;

-- 插入数据
INSERT INTO Employee(num,NAME,addr,zip,tel,email,birth,sex,depno)
 VALUES
(004,'王小燕','武汉交大','430071','85743261','lili@sina.com','1950-7-30','女',1),
(006,'李明','华中师大', '430075','85362143','zhujun@sina.com','1955-09-28','男',5),
(007,'田丽','中南财大', '430076','85693265','zgming@sohu.com','1968-08-10','女',3),
(008,'吴天','武汉电力', '430077','36985612','zjamg@china.com','1964-10-01','男',5),
(010,'张飞','武汉钢铁', '430079','69865632','liyu@tom.com','1958-09-20','男',4);

INSERT INTO Employee(num,NAME,addr,zip,tel,birth,sex,depno)
 VALUES
(001,'王林','武汉大学','430074','87598405','1985-2-1','男',2),
(002,'王芳','华中科大', '430073','62534231','1966-3-28','男',1),
(003,'张晓','武汉理工大','430072','87596985','1972-12-9 ','男',1),
(005,'李华','华中农大', '430070','87569865','1962-10-18','男',5),
(009,'刘备','武汉邮科院','430078','69865231','1967-04-02','男',3),
(011,'赵云','学府家园', '430071','68592312','1968-11-18','男',4),
(012,'貂禅]','湖北工大','430074','65987654','1959-09-03','女',4);
SELECT * FROM Employee;

-- 创建Department表
CREATE TABLE Department(
depno INT,
depname VARCHAR(10),
remark VARCHAR(40)
)
DESC Department;

-- 插入数据
INSERT INTO Department(depno,depname)
VALUES
(1,'财务部'),
(2,'人力资源部'),
(3,'经理办公室'),
(4,'研发部'),
(5,'市场部');

-- 创建Salary表
CREATE TABLE Salary(
num INT,
income FLOAT,
outcome FLOAT
)
DESC Salary;

-- 插入数据
INSERT INTO Salary
VALUES
(001,2100.8,123.09),
(002,1582.62,88.03),
(003,2569.88,185.65),
(004,1987.01,79.58),
(005,2066.15,108.0),
(006,2980.7,210.2),
(007,3259.98,281.52),
(008,2860.0,198),
(009,2347.68,180),
(010,2531.98,199.08),
(011,2240.0,121.0),
(012,1980.0,100.0); 
SELECT * FROM Salary;

 

-- 练习1：SELECT语句的基本使用
-- 1.查询每个雇员的所有记录；
SELECT * FROM Employee;
-- 2.查询前5个雇员的所有记录；
SELECT * FROM Employee LIMIT 5;
-- 3.查询每个雇员的地址和电话；
SELECT addr,tel FROM Employee;
-- 4.查询num为001的雇员地址和电话；
SELECT addr,tel FROM Employee WHERE num='001'
-- 5.查询表Employee表中女雇员的地址和电话，
-- 使用AS子句将结果列中各列的标题分别指定为地址、电话；
SELECT addr AS 地址,tel 电话 FROM Employee WHERE sex='女'
-- 6.计算每个雇员的实际收入
SELECT *,(income-outcome) FROM Salary
-- 7.找出所有姓王的雇员的部门号（部门号不能重复显示）
SELECT DISTINCT depno FROM Employee WHERE NAME LIKE '王%'
SELECT DISTINCT depno,NAME FROM Employee WHERE NAME LIKE '王%'
-- 8.找出所有收入在2000－3000元之间的雇员编号
SELECT num FROM Salary WHERE income BETWEEN 2000 AND 3000
SELECT * FROM Salary WHERE income BETWEEN 2000 AND 3000

 

-- 练习2：子查询的使用（答案可以不唯一）

-- 1.查找在财务部工作的雇员情况；
-- 分析：
-- SELECT * FROM Employee WHERE depno=（财务部的部门编号）

-- 查财务部的部门编号
SELECT  depno FROM Department WHERE depname='财务部'
-- 最终结果
SELECT * FROM Employee 
WHERE depno=(SELECT  depno FROM Department
WHERE depname='财务部'
)

-- 2.查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名；
-- 分析：
-- SELECT NAME FROM Employee WHERE depno=(财务部的depno) and  birth<(研发部最小生日)

-- 查找财务部年龄比研发部最大年龄还大的员工

-- 比最小出生日期还要小的日期
-- 查找研发部最小的生日年份
SELECT MIN(birth) FROM Employee
WHERE  depno=
(SELECT depno FROM Department WHERE depname='研发部')

-- 最终结果
SELECT NAME FROM Employee WHERE depno=      -- name换为*
(SELECT depno FROM Department WHERE depname='财务部')
AND
birth<
(
SELECT MIN(birth) FROM Employee
    WHERE  depno=
   (SELECT depno FROM Department WHERE depname='研发部')
)

-- 3.查找比所有财务部雇员收入都高的雇员的姓名；
-- SELECT NAME FROM Employee WHERE num IN（比财务部最大收入还大的员工编号）
-- A.查询财务部最高收入
     -- 分析：部门名称在Department
          -- 收入在Salary
          -- 关系表是Employee
   -- 先查询财务部的所有员工编号
   SELECT num FROM Employee WHERE depno=
   (SELECT depno FROM Department WHERE depname='财务部')

   -- 再查上面员工编号中对应的收入的最大收入
   SELECT MAX(income) FROM Salary WHERE num IN
   (
     SELECT num FROM Employee WHERE depno=
     (SELECT depno FROM Department WHERE depname='财务部')
   )
   
 -- B.查询比财务部最大收入还大的员工编号
 SELECT num FROM Salary WHERE income>
  (
   SELECT MAX(income) FROM Salary WHERE num IN
   (
     SELECT num FROM Employee WHERE depno=
     (SELECT depno FROM Department WHERE depname='财务部')
   )

  )
 
  -- 最终结果
 SELECT NAME FROM Employee WHERE num IN (
  SELECT num FROM Salary WHERE income>
  (
   SELECT MAX(income) FROM Salary WHERE num IN
   (
     SELECT num FROM Employee WHERE depno=
     (SELECT depno FROM Department WHERE depname='财务部')
   )

  )
)

 

练习3：连接查询的使用

-- (1)查找每个雇员的情况及薪水情况
SELECT * FROM Employee;
SELECT * FROM Department;
SELECT * FROM Salary;

SELECT e.*,d.depname,d.remark,s.income,s.outcome
FROM Employee e
LEFT JOIN Department d ON e.depno=d.depno
LEFT JOIN Salary s ON s.num=e.num
-- (2)查找财务部收入在2200元以上的雇员姓名及其薪水详细情况；
SELECT e.*,d.depname,d.remark,s.income,s.outcome
FROM Employee e
LEFT JOIN Department d ON e.depno=d.depno
LEFT JOIN Salary s ON s.num=e.num
WHERE income>2200 AND depname='财务部'

-- 练习5：GROUP  BY
-- 1.求各部门的雇员数（要求显示部门号、部门名称和部门雇员数）
-- 显示部门号、部门雇员数
SELECT COUNT(*) FROM Employee GROUP BY depno
SELECT depno,COUNT(*) FROM Employee GROUP BY depno
-- 最终结果
  SELECT * FROM
(SELECT depno,COUNT(*) FROM Employee GROUP BY depno) n
 LEFT JOIN Department d ON d.depno=n.depno
 
 
-- 方法2
 SELECT *,
(SELECT depname FROM Department WHERE depno=n.depno)
 FROM
(SELECT depno,COUNT(*)c FROM Employee GROUP BY depno) n