MySQL:数据库练习题-1

表一：employees

USE yggl
SELECT * FROM employees
INSERT into employees VALUES
('102201','刘明','本科',19721018,1,3,'虎踞路100-2','83606608',5),
('102208','朱骏','硕士',19650928,1,2,'牌楼巷5-3-106','84708817',5),
('108991','钟敏','硕士',19790810,0,4,'中山路10-3-105','83346722',3),
('111006','张石兵','本科',19741001,1,1,'解放路34-1-203','84563418',5),
('210678','林涛','大专',19770402,1,2,'中山北路24-35','83467336',3),
('302566','李玉珉','本科',19680920,1,3,'热河路209-3','58765991',4),
('308759','叶凡','本科',19781118,1,2,'北京西路3-7-52','83308901',4),
('504209','陈林琳','大专',19690903,0,5,'汉中路120-4-12','84468158',4)

表二：salary

SELECT * FROM salary
INSERT into salary VALUES
('000001',2100.8,123.09),
('010008',1582.62,88.03),
('020010',2860,198),
('020018',2347.68,180),
('102201',2569.88,185.65),
('102208',1980,100),
('108991',3259.98,281.52),
('111006',1987.01,79.58),
('210678',2240,121),
('302566',2980.7,210.2),
('308759',2531.98,199.08),
('504209',2066.15,108)

表三：departments

SELECT * FROM departments
INSERT into departments VALUES
(1,"财务部",NULL),
(2,"人力资源部",NULL),
(3,"经理办公室",NULL),
(4,"研发部",NULL),
(5,"市场部",NULL)

练习题及答案：

---1、查询empioyees表的员工部门号和性别，要求消除重复行

SELECT DISTINCT 性别,员工部门号 FROM employees

---2、计算每个雇员的实际收入（实际收入=收入-支出）

SELECT 员工编号,收入-支出 as 实际收入 from salary

---3、查询employees表中的姓名和性别，要求性别值为1时显示为“男”，为0时显示为“女”

SELECT 姓名,性别 from employees WHERE 性别=1 as '男' 

---4、查询每个雇员的地址和电话号码，显示的列标题为adress,telephone

SELECT 地址 AS "adress",电话号码 AS "telephone" from employees 

---5、计算salary表中员工月收入的平均数

SELECT avg(收入) as 员工月收入的平均数 from salary

---6、计算所有员工的总支出

SELECT sum(支出) as 所有员工的总支出 from salary

---7、显示女雇员的地址和电话号码

SELECT 地址,电话号码 from employees where 性别=0

---8、计算员工总数

SELECT count(*) as 员工总数 from employees

---9、显示最高收入和最低收入的员工的员工编号

SELECT max(收入) as 最高收入,min(收入) as 最低收入 FROM salary

二、条件查询

---1、显示月收入高于2000元的员工编号

SELECT 员工编号 from salary where 收入>2000

---2、查询1970年以后出生的员工的姓名和地址

SELECT 姓名,地址 from employees where 出生日期>19700000

---3、显示工作年限3年以上（含3年），学历在本科以上（含本科）的男性员工的信息

SELECT * from employees where 工作年限>=3 and 学历 in("本科","硕士") and 性别=1

---4、查找员工编号中倒数第2个数字为0的姓名，地址和学历

SELECT 姓名,地址,学历 from employees where 员工编号 like "%0_"

---5、查询月收入在2000远~3000远的员工编号

SELECT 员工编号 from salary where 收入 BETWEEN 2000 and 3000

三.多表查询

---1、查询“王林”的基本情况和所工作的部门名称

SELECT employees.*,departments.部门名称 FROM employees
JOIN departments ON employees.员工部门号=departments.部门编号
WHERE employees.姓名='王林'

---2、查询财务部、研发部、市场部的员工信息。

SELECT employees.*,departments.部门名称 FROM employees
JOIN departments ON employees.员工部门号=departments.部门编号
WHERE departments.部门名称 IN ('财务部','研发部','市场部')

---3、查询每个雇员的基本情况和薪水情况。

SELECT employees.*,salary.* FROM employees
JOIN salary ON employees.员工编号=salary.员工编号

---4、查询研发部在1970年以前出生的员工姓名和薪水情况。

SELECT e.姓名,s.* FROM employees e,salary s,departments d
WHERE e.员工编号=s.员工编号 AND e.员工部门号=d.部门编号
AND e.出生日期 < '1970' AND 部门名称='研发部'

---5、查询员工的姓名、住址和收入水平，要求2 000元以下显示为“低收入”，
2000元～3000元显示为“中等收入”，3000元以上时显示为“高收入”。

SELECT e.姓名,e.地址,
CASE
       WHEN 收入 < '2000' THEN '低收入'
       WHEN 收入 BETWEEN '2000' AND '3000' THEN '中等收入'
       WHEN 收入 > '3000' THEN '高收入'
END AS '收入等级'
FROM employees e,salary s
WHERE e.员工编号=s.员工编号

四、分类汇总与排序

---1、按部门列出该部门工作的员工人数。

SELECT count(*),d.部门编号 FROM employees e JOIN departments d 
ON e.员工部门号=d.部门编号 GROUP BY 员工部门号

---2、分别统计男性员工和女性员工人数。

SELECT count(*),
CASE
       WHEN 性别='1' THEN '男'
       WHEN 性别='0' THEN '女'
END AS '性别'
FROM employees e GROUP BY 性别

---3、查找雇员数超过2人的部门名称和员工数量。

SELECT COUNT(*),d.部门名称 FROM employees e JOIN departments d ON e.员工部门号=d.部门编号
GROUP BY 员工部门号 HAVING COUNT(e.员工部门号) > 2

---4、按员工学历分组统计各种学历人数。

SELECT COUNT(*),e.学历 FROM employees e GROUP BY 学历

---5、将员工信息按出生日期从大到小排序。

SELECT * FROM employees ORDER BY 出生日期 DESC

---6、将员工薪水按收入多少从小到大排序。

SELECT * FROM salary ORDER BY 收入

---7、按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序。

SELECT COUNT(*),employees.工作年限 FROM employees GROUP BY 工作年限
ORDER BY COUNT(工作年限)