(如有错误，欢迎指正！)
1.select语句查询
(1)查询employees表员工部门号和性别，要求消除重复行。
mysql>select distinct 员工部门号,性别from employees;
+—————–+——–+
| 员工部门号|性别|
+—————–+——–+
| 2 | 1 |
| 1 | 1 |
| 1 | 0 |
| 5 | 1 |
| 3 | 0 |
| 3 | 1 |
| 4 | 1 |
| 4 | 0 |
+—————–+——–+
8 rows in set (0.03 sec)
(2)计算salary表每个雇员的实际收入(实际收入=收入-支出)。
mysql> select (收入-支出) as实际收入from salary;
+——————–+
| 实际收入|
+——————–+
| 1977.7100524902344 |
| 1494.5899963378906 |
| 2662 |
| 2167.679931640625 |
| 2384.2298889160156 |
| 1880 |
| 2978.459991455078 |
| 1907.4300079345703 |
| 2119 |
| 2770.499954223633 |
| 2332.8999786376953 |
| 1958.14990234375 |
+——————–+
12 rows in set (0.00 sec)
(3)查询employees表中员工的姓名和性别，要求sex值为1时，显示为‘男’，为0时显示为‘女’。
mysql>Select 姓名,
case
when 性别=1 then "男"
when 性别=0 then "女"
end as 性别
from employees;
+———–+——–+
| 姓名|性别|
+———–+——–+
| 王林|男|
| 伍容华|男|
| 王向容|男|
| 李丽|女|
| 刘明|男|
| 朱俊|男|
| 钟敏|女|
| 张石兵|男|
| 林涛|男|
| 李玉珉|男|
| 叶凡|男|
| 陈林琳|女|
+———–+——–+
12 rows in set (0.00 sec)
(4)查询employees表每个雇员的地址和电话，列标题要求显示为 address 和 telephone。
mysql> select 地址asadress, 电话号码 as telephone from employees;
+——————–+———–+
| adress | telephone |
+——————–+———–+
| 中山路32-1-508 | 83355668 |
| 北京东路100-2 | 83321321 |
| 四牌楼10-0-108 | 83792361 |
| 中山东路102-2 | 83413301 |
| 虎距路100-2 | 83606608 |
| 牌楼巷5-3-106 | 84708817 |
| 中山路10-3-105 | 83346722 |
| 解放路34-1-203 | 84563418 |
| 中山北路24-35 | 83467336 |
| 热和路209-3 | 58765991 |
| 北京西路3-7-52 | 83308901 |
| 汉中路120-4-12 | 84468158 |
+——————–+———–+
12 rows in set (0.00 sec)
(5)计算salary表中员工月收入的平均数。
mysql> select avg(收入)as月平均收入 from salary;
+——————-+
| 月平均收入 |
+——————-+
| 2375.566640218099 |
+——————-+
1 row in set (0.00 sec)
(6)计算所有员工的总支出。
mysql>select sum(支出) as 总支出 from salary;
+——————–+
| 总支出 |
+——————–+
| 1874.1499786376953 |
+——————–+
1 row in set (0.00 sec)
(7)显示女雇主的地址和电话。
mysql> select 姓名,地址,电话号码from employees where性别=0;
+———–+——————-+————–+
| 姓名|地址|电话号码|
+———–+——————-+————–+
| 李丽|中山东路102-2 | 83413301 |
| 钟敏|中山路10-3-105 | 83346722 |
| 陈林琳|汉中路120-4-12 | 84468158 |
+———–+——————-+————–+
3 rows in set (0.00 sec)
(8)计算员工总数。
mysql> select count(*) from employees;
+———-+
| count(*) |
+———-+
| 12 |
+———-+
1 row in set (0.00 sec)
(9)显示员工的最高收入和最低收入。
mysql> Select max(收入),min(收入) from salary;
+——————+——————–+
| max(收入) | min(收入) |
+——————+——————–+
| 3259.97998046875 | 1582.6199951171875 |
+——————+——————–+
1 row in set (0.00 sec)
2.条件查询
(1)显示月收入高于2000元员工的员工号。
mysql> select 员工编号from salary where收入>2000;
+————–+
| 员工编号|
+————–+
| 000001 |
| 020010 |
| 020018 |
| 102201 |
| 108991 |
| 210678 |
| 302566 |
| 308759 |
| 504209 |
+————–+
9 rows in set (0.00 sec)
(2)查询1970年以后的员工的姓名和地址。
mysql>select 姓名,地址from employees where出生日期>"1970-1-1";
+———–+——————–+
| 姓名|地址|
+———–+——————–+
| 伍容华|北京东路100-2 |
| 王向容|四牌楼10-0-108 |
| 刘明|虎距路100-2 |
| 钟敏|中山路10-3-105 |
| 张石兵|解放路34-1-203 |
| 林涛|中山北路24-35 |
| 叶凡|北京西路3-7-52 |
+———–+——————–+
7 rows in set (0.00 sec)
(3)显示工作年限三年以上(包含3年)、学历在本科以上(包含本科)的男性员工的信息。
mysql> select * from employeeswhere 工作年限>=3 and (学历="本科" or学历="硕士") and性别= 1;
+————–+———–+——–+————–+——–+————–+——————-+————–+—————–+
| 员工编号|姓名|学历|出生日期|性别|工作年限|地址|电话号码|员工部门号|
+————–+———–+——–+————–+——–+————–+——————-+————–+—————–+
| 010008 | 伍容华|本科| 1976-03-28 | 1 | 3 |北京东路100-2 | 83321321 | 1 |
| 102201 | 刘明|本科| 1972-10-18 | 1 | 3 |虎距路100-2 | 83606608 | 5 |
| 302566 | 李玉珉|本科| 1968-09-20 | 1 | 3 |热和路209-3 | 58765991 | 4 |
+————–+———–+——–+————–+——–+————–+——————-+————–+—————–+
3 rows in set (0.00 sec)
(4)查找员工中倒数第二个数字为0的员工的姓名、地址和学历。
mysql>select 姓名,地址,学历from employees where员工编号like "%0_";
+———–+——————-+——–+
| 姓名|地址|学历|
+———–+——————-+——–+
| 王林|中山路32-1-508 |大专|
| 伍容华|北京东路100-2 |本科|
| 刘明|虎距路100-2 |本科|
| 朱俊|牌楼巷5-3-106 |硕士|
| 张石兵|解放路34-1-203 |本科|
| 陈林琳|汉中路120-4-12 |大专|
+———–+——————-+——–+
6 rows in set (0.00 sec)
(5)查询月收入在2000-3000元的员工。
mysql> select * from salary where 收入>=2000 and收入<=3000;
+————–+———+——–+
| 员工编号|收入|支出|
+————–+———+——–+
| 000001 | 2100.8 | 123.09 |
| 020010 | 2860 | 198 |
| 020018 | 2347.68 | 180 |
| 102201 | 2569.88 | 185.65 |
| 210678 | 2240 | 121 |
| 302566 | 2980.7 | 210.2 |
| 308759 | 2531.98 | 199.08 |
| 504209 | 2066.15 | 108 |
+————–+———+——–+
8 rows in set (0.00 sec)
3.多表查询
(1)查询王林的基本情况和所工作的部门名称。
mysql> select * from employees e,departments d where e.员工部门号=d.部门编号and姓名="王林";
+————–+——–+——–+————–+——–+————–+——————-+————–+—————–+————–+—————–+——–+
| 员工编号|姓名|学历|出生日期|性别|工作年限|地址|电话号码|员工部门号|部门编号|部门名称|备注|
+————–+——–+——–+————–+——–+————–+——————-+————–+—————–+————–+—————–+——–+
| 000001 | 王林|大专| 1966-01-23 | 1 | 8 |中山路32-1-508 | 83355668 | 2 | 2 |人力资源部| NULL |
+————–+——–+——–+————–+——–+————–+——————-+————–+—————–+————–+—————–+——–+
1 row in set (0.00 sec)
(2)查询财务部、研发部、市场部的员工信息。(预习和学习子查询相关知识)
mysql>select * from employees e,departments d where e.员工部门号=d.部门编号and (部门名称="财务部" or部门名称="市场部" or部门名称="研发部");
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+————–+
| 员工编号|姓名|学历|出生日期|性别|工作年限|地址|电话号码|员工部门号|部门名称|
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+————–+
| 010008 | 伍容华|本科| 1976-03-28 | 1 | 3 |北京东路100-2 | 83321321 | 1 |财务部|
| 020010 | 王向容|硕士| 1982-12-09 | 1 | 2 |四牌楼10-0-108 | 83792361 | 1 |财务部|
| 020018 | 李丽|大专| 1960-07-30 | 0 | 6 |中山东路102-2 | 83413301 | 1 |财务部|
| 302566 | 李玉珉|本科| 1968-09-20 | 1 | 3 |热和路209-3 | 58765991 | 4 |研发部|
| 308759 | 叶凡|本科| 1978-11-18 | 1 | 2 |北京西路3-7-52 | 83308901 | 4 |研发部|
| 504209 | 陈林琳|大专| 1969-09-03 | 0 | 5 |汉中路120-4-12 | 84468158 | 4 |研发部|
| 102201 | 刘明|本科| 1972-10-18 | 1 | 3 |虎距路100-2 | 83606608 | 5 |市场部|
| 102208 | 朱俊|硕士| 1965-09-28 | 1 | 2 |牌楼巷5-3-106 | 84708817 | 5 |市场部|
| 111006 | 张石兵|本科| 1974-10-01 | 1 | 1 |解放路34-1-203 | 84563418 | 5 |市场部|
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+————–+
9 rows in set (0.00 sec)
(3)查询每个雇员的基本情况和薪水情况。
(找到两种方法)
mysql> Select * from employees join salary using(员工编号);
mysql> Select * from employees e,salary s where e.员工编号=s.员工编号;
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+———+——–+
| 员工编号|姓名|学历|出生日期|性别|工作年限|地址|电话号码|员工部门号|收入|支出|
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+———+——–+
| 000001 | 王林|大专| 1966-01-23 | 1 | 8 |中山路32-1-508 | 83355668 | 2 | 2100.8 | 123.09 |
| 010008 | 伍容华|本科| 1976-03-28 | 1 | 3 |北京东路100-2 | 83321321 | 1 | 1582.62 | 88.03 |
| 020010 | 王向容|硕士| 1982-12-09 | 1 | 2 |四牌楼10-0-108 | 83792361 | 1 | 2860 | 198 |
| 020018 | 李丽|大专| 1960-07-30 | 0 | 6 |中山东路102-2 | 83413301 | 1 | 2347.68 | 180 |
| 102201 | 刘明|本科| 1972-10-18 | 1 | 3 |虎距路100-2 | 83606608 | 5 | 2569.88 | 185.65 |
| 102208 | 朱俊|硕士| 1965-09-28 | 1 | 2 |牌楼巷5-3-106 | 84708817 | 5 | 1980 | 100 |
| 108991 | 钟敏|硕士| 1979-08-10 | 0 | 4 |中山路10-3-105 | 83346722 | 3 | 3259.98 | 281.52 |
| 111006 | 张石兵|本科| 1974-10-01 | 1 | 1 |解放路34-1-203 | 84563418 | 5 | 1987.01 | 79.58 |
| 210678 | 林涛|大专| 1977-04-02 | 1 | 2 |中山北路24-35 | 83467336 | 3 | 2240 | 121 |
| 302566 | 李玉珉|本科| 1968-09-20 | 1 | 3 |热和路209-3 | 58765991 | 4 | 2980.7 | 210.2 |
| 308759 | 叶凡|本科| 1978-11-18 | 1 | 2 |北京西路3-7-52 | 83308901 | 4 | 2531.98 | 199.08 |
| 504209 | 陈林琳|大专| 1969-09-03 | 0 | 5 |汉中路120-4-12 | 84468158 | 4 | 2066.15 | 108 |
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+———+——–+
12 rows in set (0.01 sec)
(4)查询研发部在1970年以前的员工姓名和薪水情况。
mysql> select e.姓名,s.收入,s.支出
from employees e,salary s,departments d
where e.员工部门号=d.部门编号
and e.员工编号=s.员工编号
and d.部门名称="研发部"
and e.出生日期
+———–+———+——–+
| 姓名|收入|支出|
+———–+———+——–+
| 李玉珉| 2980.7 | 210.2 |
| 陈林琳| 2066.15 | 108 |
+———–+———+——–+
2 rows in set, 1 warning (0.00 sec)
(5)查询employees表中员工的姓名、住址和收入水平，要求2000元以下显示为‘低收入’，2000-3000显示为‘中等收入’，3000元以上显示为‘高收入’。
mysql> select 姓名,地址,
case
when 收入<2000 then "低收入"
when 收入>3000 then "高收入"
when 收入>2000 and收入<3000 then "中等收入"
end as "收入等级"
from employees,salary
where employees.员工编号=salary.员工编号;
+———–+——————–+————–+
| 姓名|地址|收入等级|
+———–+——————–+————–+
| 王林|中山路32-1-508 |中等收入|
| 伍容华|北京东路100-2 |低收入|
| 王向容|四牌楼10-0-108 |中等收入|
| 李丽|中山东路102-2 |中等收入|
| 刘明|虎距路100-2 |中等收入|
| 朱俊|牌楼巷5-3-106 |低收入|
| 钟敏|中山路10-3-105 |高收入|
| 张石兵|解放路34-1-203 |低收入|
| 林涛|中山北路24-35 |中等收入|
| 李玉珉|热和路209-3 |中等收入|
| 叶凡|北京西路3-7-52 |中等收入|
| 陈林琳|汉中路120-4-12 |中等收入|
+———–+——————–+————–+
12 rows in set (0.00 sec)
4.分类汇总与排序
(1)按部门列出该部门的员工人数。
mysql>select d.部门名称,count(e.姓名) as "员工总人数"
from employees e,departments d
where e.员工部门号=d.部门编号group by d.部门名称;
+—————–+—————–+
| 部门名称|员工总人数|
+—————–+—————–+
| 人力资源部| 1 |
| 市场部| 3 |
| 研发部| 3 |
| 经理办公室| 2 |
| 财务部| 3 |
+—————–+—————–+
5 rows in set (0.00 sec)
(2)分别统计男性员工和女性员工人数。
mysql> select count(*) as "人数",
case
when 性别=1 then "男"
when 性别=0 then "女"
end as 性别
from employees group by 性别;
+——–+——–+
| 人数|性别|
+——–+——–+
| 3 | 女|
| 9 | 男|
+——–+——–+
2 rows in set, 1 warning (0.00 sec)
(3)查找雇员数超过2人的部门名称和员工数量。
mysql> selectd.部门名称, count(e.姓名)as "员工总人数"
from employees e,departments d
where e.员工部门号=d.部门编号
group by d.部门名称
having count(*)>2 ;
+————–+—————–+
| 部门名称|员工总人数|
+————–+—————–+
| 市场部| 3 |
| 研发部| 3 |
| 财务部| 3 |
+————–+—————–+
3 rows in set (0.00 sec)
(4)按员工学历分组统计各种学历人数。
mysql> select 学历,count(*) as人数
from employees
group by 学历;
+——–+——–+
| 学历|人数|
+——–+——–+
| 大专| 4 |
| 本科| 5 |
| 硕士| 3 |
+——–+——–+
3 rows in set (0.00 sec)
(5)将员工信息按出生日期从大到小排序。
mysql> select * from employees order by 出生日期;
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+
| 员工编号|姓名|学历|出生日期|性别|工作年限|地址|电话号码|员工部门号|
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+
| 020018 | 李丽|大专| 1960-07-30 | 0 | 6 |中山东路102-2 | 83413301 | 1 |
| 102208 | 朱俊|硕士| 1965-09-28 | 1 | 2 |牌楼巷5-3-106 | 84708817 | 5 |
| 000001 | 王林|大专| 1966-01-23 | 1 | 8 |中山路32-1-508 | 83355668 | 2 |
| 302566 | 李玉珉|本科| 1968-09-20 | 1 | 3 |热和路209-3 | 58765991 | 4 |
| 504209 | 陈林琳|大专| 1969-09-03 | 0 | 5 |汉中路120-4-12 | 84468158 | 4 |
| 102201 | 刘明|本科| 1972-10-18 | 1 | 3 |虎距路100-2 | 83606608 | 5 |
| 111006 | 张石兵|本科| 1974-10-01 | 1 | 1 |解放路34-1-203 | 84563418 | 5 |
| 010008 | 伍容华|本科| 1976-03-28 | 1 | 3 |北京东路100-2 | 83321321 | 1 |
| 210678 | 林涛|大专| 1977-04-02 | 1 | 2 |中山北路24-35 | 83467336 | 3 |
| 308759 | 叶凡|本科| 1978-11-18 | 1 | 2 |北京西路3-7-52 | 83308901 | 4 |
| 108991 | 钟敏|硕士| 1979-08-10 | 0 | 4 |中山路10-3-105 | 83346722 | 3 |
| 020010 | 王向容|硕士| 1982-12-09 | 1 | 2 |四牌楼10-0-108 | 83792361 | 1 |
+————–+———–+——–+————–+——–+————–+——————–+————–+—————–+
12 rows in set (0.01 sec)