mysql第四次作业

最新推荐文章于 2024-07-07 12:29:28 发布

Bula..

最新推荐文章于 2024-07-07 12:29:28 发布

阅读量42

点赞数

文章标签： mysql adb android

本文链接：https://blog.csdn.net/m0_68403978/article/details/131648714

版权

一、单表查询

1、查看创建好的表
mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   | PRI | NULL    |       |
| ename    | varchar(255) | YES  |     | NULL    |       |
| job      | varchar(255) | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | NO   |     | NULL    |       |
| sai      | int(255)     | NO   |     | NULL    |       |
| comm     | int(255)     | YES  |     | NULL    |       |
| deptno   | int(2)       | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)


mysql> select * from emp;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1001 | 甘宁      | 文员      | 1013 | 2000-12-17 |  8000 |  NULL |     20 |
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 |  3000 |     30 |
|  1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500 |  5000 |     30 |
|  1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750 |  NULL |     20 |
|  1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500 | 14000 |     30 |
|  1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500 |  NULL |     30 |
|  1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500 |  NULL |     10 |
|  1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000 |  NULL |     20 |
|  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1011 | 周泰      | 文员      | 1006 | 2007-05-23 | 11000 |  NULL |     20 |
|  1012 | 程普      | 文员      | 1006 | 2001-12-03 |  9500 |  NULL |     30 |
|  1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000 |  NULL |     20 |
|  1014 | 黄盖      | 文员      | 1007 | 2002-01-23 | 13000 |  NULL |     10 |
|  1015 | 张三      | 保洁员    | 1001 | 2013-05-01 | 80000 | 50000 |     50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.00 sec)

2、查找部门编号为30的员工
mysql> select * from emp where deptno=30;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 |  3000 |     30 |
|  1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500 |  5000 |     30 |
|  1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500 | 14000 |     30 |
|  1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500 |  NULL |     30 |
|  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1012 | 程普      | 文员      | 1006 | 2001-12-03 |  9500 |  NULL |     30 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
6 rows in set (0.00 sec)

3、查找职位为销售员的员工的姓名，员工编号，部门编号
mysql> select ename,empno,deptno from emp where job='销售员';
+-----------+-------+--------+
| ename     | empno | deptno |
+-----------+-------+--------+
| 黛绮丝    |  1002 |     30 |
| 殷天正    |  1003 |     30 |
| 谢逊      |  1005 |     30 |
| 韦一笑    |  1010 |     30 |
+-----------+-------+--------+
4 rows in set (0.00 sec)

4、查找奖金大于工资的员工
mysql> select ename from emp where comm>sai;
+--------+
| ename  |
+--------+
| 谢逊   |
+--------+
1 row in set (0.00 sec)


5、查找奖金大于工资的百分之60的员工
mysql> select ename from emp where comm>sai*0.6;
+--------+
| ename  |
+--------+
| 谢逊   |
| 张三   |
+--------+
2 rows in set (0.00 sec)


6、查找部门编号为10 ，工作为经理和部门编号为20，工作为销售员
mysql> select * from emp where deptno=10 or job='经理' and deptno=20 or job='销售员';
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 |  3000 |     30 |
|  1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500 |  5000 |     30 |
|  1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750 |  NULL |     20 |
|  1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500 | 14000 |     30 |
|  1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500 |  NULL |     10 |
|  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1014 | 黄盖      | 文员      | 1007 | 2002-01-23 | 13000 |  NULL |     10 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
8 rows in set (0.00 sec)


7、查找部门编号是10且职位是经理的或部门编号是20职位是销售员的
mysql> select * from emp where deptno=10 and job='经理' or deptno=20 and job='销售员';
+-------+--------+--------+------+------------+-------+------+--------+
| empno | ename  | job    | mgr  | hiredate   | sai   | comm | deptno |
+-------+--------+--------+------+------------+-------+------+--------+
|  1007 | 张飞   | 经理   | 1009 | 2001-09-01 | 24500 | NULL |     10 |
+-------+--------+--------+------+------------+-------+------+--------+
1 row in set (0.00 sec)

8、查找部门编号为10 ，工作为经理和部门编号为20，工作为销售员或工作不是经理也不是销售员并且工资大于等于20000
mysql> select * from emp where deptno=10 and job='经理' or deptno=20 and job='销售员' or not (job='经理'and job='销售员') and sai >=20000;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750 |  NULL |     20 |
|  1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500 |  NULL |     30 |
|  1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500 |  NULL |     10 |
|  1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000 |  NULL |     20 |
|  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000 |  NULL |     20 |
|  1015 | 张三      | 保洁员    | 1001 | 2013-05-01 | 80000 | 50000 |     50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
7 rows in set (0.00 sec)


9、查找奖金为空或小于1000的
mysql> select ename,comm  from emp where comm is null or comm<1000;
+-----------+------+
| ename     | comm |
+-----------+------+
| 甘宁      | NULL |
| 刘备      | NULL |
| 关羽      | NULL |
| 张飞      | NULL |
| 诸葛亮    | NULL |
| 曾阿牛    | NULL |
| 韦一笑    |    0 |
| 周泰      | NULL |
| 程普      | NULL |
| 庞统      | NULL |
| 黄盖      | NULL |
+-----------+------+
11 rows in set (0.00 sec)


10、查找名字是三个字的
mysql> select ename  from emp where length(ename)=9;
+-----------+
| ename     |
+-----------+
| 黛绮丝    |
| 殷天正    |
| 诸葛亮    |
| 曾阿牛    |
| 韦一笑    |
+-----------+
5 rows in set (0.00 sec)


11、查找入职在2000年的
mysql> select ename  from emp where year(hiredate)=2000;
+--------+
| ename  |
+--------+
| 甘宁   |
+--------+
1 row in set (0.00 sec)

12、通过员工编号升序排序
mysql> select * from emp  order by empno;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1001 | 甘宁      | 文员      | 1013 | 2000-12-17 |  8000 |  NULL |     20 |
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 |  3000 |     30 |
|  1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500 |  5000 |     30 |
|  1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750 |  NULL |     20 |
|  1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500 | 14000 |     30 |
|  1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500 |  NULL |     30 |
|  1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500 |  NULL |     10 |
|  1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000 |  NULL |     20 |
|  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1011 | 周泰      | 文员      | 1006 | 2007-05-23 | 11000 |  NULL |     20 |
|  1012 | 程普      | 文员      | 1006 | 2001-12-03 |  9500 |  NULL |     30 |
|  1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000 |  NULL |     20 |
|  1014 | 黄盖      | 文员      | 1007 | 2002-01-23 | 13000 |  NULL |     10 |
|  1015 | 张三      | 保洁员    | 1001 | 2013-05-01 | 80000 | 50000 |     50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.00 sec)

13、按工资降序排列，相同工资按入职时间升序排序
mysql> select * from emp  order by sai desc ,hiredate;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1015 | 张三      | 保洁员    | 1001 | 2013-05-01 | 80000 | 50000 |     50 |
|  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000 |  NULL |     20 |
|  1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000 |  NULL |     20 |
|  1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750 |  NULL |     20 |
|  1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500 |  NULL |     30 |
|  1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500 |  NULL |     10 |
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 |  3000 |     30 |
|  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1014 | 黄盖      | 文员      | 1007 | 2002-01-23 | 13000 |  NULL |     10 |
|  1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500 |  5000 |     30 |
|  1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500 | 14000 |     30 |
|  1011 | 周泰      | 文员      | 1006 | 2007-05-23 | 11000 |  NULL |     20 |
|  1012 | 程普      | 文员      | 1006 | 2001-12-03 |  9500 |  NULL |     30 |
|  1001 | 甘宁      | 文员      | 1013 | 2000-12-17 |  8000 |  NULL |     20 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.01 sec)

14、按部门分组，统计总工资
mysql> select deptno,avg(sai) from emp group by deptno ;
+--------+------------+
| deptno | avg(sai)   |
+--------+------------+
|     10 | 29166.6667 |
|     20 | 21750.0000 |
|     30 | 15666.6667 |
|     50 | 80000.0000 |
+--------+------------+
4 rows in set (0.00 sec)

15、统计部门人数
mysql> select deptno, count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
|     50 |        1 |
+--------+----------+
4 rows in set (0.00 sec)
16、按职位分组，查找最高工资和最低工资
mysql> select max(sai),min(sai),count(*) from emp group by job;
+----------+----------+----------+
| max(sai) | min(sai) | count(*) |
+----------+----------+----------+
|    80000 |    80000 |        1 |
|    30000 |    30000 |        2 |
|    13000 |     8000 |        4 |
|    29750 |    24500 |        3 |
|    50000 |    50000 |        1 |
|    16000 |    12500 |        4 |
+----------+----------+----------+
6 rows in set (0.00 sec)

二、多表查询

1、
mysql> select * from dept3 d,emp3 e where d.deptno=e.dept_id ;
+--------+-----------+-----+--------------+------+---------+
| deptno | name      | eid | ename        | age  | dept_id |
+--------+-----------+-----+--------------+------+---------+
| 1001   | 研发部    | 1   | 乔峰         |   20 | 1001    |
| 1001   | 研发部    | 2   | 段誉         |   21 | 1001    |
| 1001   | 研发部    | 3   | 虚竹         |   23 | 1001    |
| 1001   | 研发部    | 4   | 阿紫         |   18 | 1001    |
| 1002   | 销售部    | 5   | 扫地僧       |   85 | 1002    |
| 1002   | 销售部    | 6   | 李秋水       |   33 | 1002    |
| 1002   | 销售部    | 7   | 鸠摩智       |   50 | 1002    |
| 1003   | 财务部    | 8   | 天山童姥     |   60 | 1003    |
| 1003   | 财务部    | 9   | 慕容博       |   58 | 1003    |
+--------+-----------+-----+--------------+------+---------+
9 rows in set (0.00 sec)

2、
mysql> select * from dept3 d,emp3 e where d.deptno=e.dept_id and name='研发部';
+--------+-----------+-----+--------+------+---------+
| deptno | name      | eid | ename  | age  | dept_id |
+--------+-----------+-----+--------+------+---------+
| 1001   | 研发部    | 1   | 乔峰   |   20 | 1001    |
| 1001   | 研发部    | 2   | 段誉   |   21 | 1001    |
| 1001   | 研发部    | 3   | 虚竹   |   23 | 1001    |
| 1001   | 研发部    | 4   | 阿紫   |   18 | 1001    |
+--------+-----------+-----+--------+------+---------+
4 rows in set (0.00 sec)

3、
mysql> select * from dept3 d,emp3 e where d.deptno=e.dept_id and name in('研发
部','销售部');
+--------+-----------+-----+-----------+------+---------+
| deptno | name      | eid | ename     | age  | dept_id |
+--------+-----------+-----+-----------+------+---------+
| 1001   | 研发部    | 1   | 乔峰      |   20 | 1001    |
| 1001   | 研发部    | 2   | 段誉      |   21 | 1001    |
| 1001   | 研发部    | 3   | 虚竹      |   23 | 1001    |
| 1001   | 研发部    | 4   | 阿紫      |   18 | 1001    |
| 1002   | 销售部    | 5   | 扫地僧    |   85 | 1002    |
| 1002   | 销售部    | 6   | 李秋水    |   33 | 1002    |
| 1002   | 销售部    | 7   | 鸠摩智    |   50 | 1002    |
+--------+-----------+-----+-----------+------+---------+
7 rows in set (0.00 sec)

4、
mysql> select count(*),name  from dept3 d,emp3 e where d.deptno=e.dept_id groupp by name order by count(*);
+----------+-----------+
| count(*) | name      |
+----------+-----------+
|        2 | 财务部    |
|        3 | 销售部    |
|        4 | 研发部    |
+----------+-----------+
3 rows in set (0.00 sec)


5、
mysql> select count(*),name  from dept3 d,emp3 e where d.deptno=e.dept_id group by name having count(*)>=3 order by count(*);
+----------+-----------+
| count(*) | name      |
+----------+-----------+
|        3 | 销售部    |
|        4 | 研发部    |
+----------+-----------+
2 rows in set (0.00 sec)