day 34作业

作业：

表:

mysql> select * from employee;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

1. 查看岗位是teacher的员工姓名、年龄

   mysql> select name,age from employee where post='teacher';
   +------------+-----+
   | name       | age |
   +------------+-----+
   | alex       |  78 |
   | wupeiqi    |  81 |
   | yuanhao    |  73 |
   | liwenzhou  |  28 |
   | jingliyang |  18 |
   | jinxin     |  18 |
   | 成龙       |  48 |
   +------------+-----+
   7 rows in set (0.00 sec)
   

2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄

   mysql> select name,age from employee where post='teacher' and age>30;
   +---------+-----+
   | name    | age |
   +---------+-----+
   | alex    |  78 |
   | wupeiqi |  81 |
   | yuanhao |  73 |
   | 成龙    |  48 |
   +---------+-----+
   4 rows in set (0.00 sec)
   

3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资

   mysql> select name,age,salary from employee where salary between 1000 and 9000;
   +------------+-----+---------+
   | name       | age | salary  |
   +------------+-----+---------+
   | egon       |  18 | 7300.33 |
   | wupeiqi    |  81 | 8300.00 |
   | yuanhao    |  73 | 3500.00 |
   | liwenzhou  |  28 | 2100.00 |
   | jingliyang |  18 | 9000.00 |
   | 歪歪       |  48 | 3000.13 |
   | 丫丫       |  38 | 2000.35 |
   | 丁丁       |  18 | 1000.37 |
   | 星星       |  18 | 3000.29 |
   | 格格       |  28 | 4000.33 |
   +------------+-----+---------+
   10 rows in set (0.00 sec)
   

4. 查看岗位描述不为NULL的员工信息

   mysql> select * from employee where post_comment is not null;
   Empty set (0.00 sec)
   

5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

   mysql> select name,age,salary from employee where post='teacher' and (salary=10000 or salary=9000 or salary=30000);
   +------------+-----+----------+
   | name       | age | salary   |
   +------------+-----+----------+
   | jingliyang |  18 |  9000.00 |
   | jinxin     |  18 | 30000.00 |
   | 成龙       |  48 | 10000.00 |
   +------------+-----+----------+
   3 rows in set (0.00 sec)
   

6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

   mysql> select name,age,salary from employee where post='teacher' and salary!=10000 and salary!=9000 and salary!=30000;
   +-----------+-----+------------+
   | name      | age | salary     |
   +-----------+-----+------------+
   | alex      |  78 | 1000000.31 |
   | wupeiqi   |  81 |    8300.00 |
   | yuanhao   |  73 |    3500.00 |
   | liwenzhou |  28 |    2100.00 |
   +-----------+-----+------------+
   4 rows in set (0.00 sec)
   

7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

   mysql> select name,salary from employee where name like 'jin%';
   +------------+----------+
   | name       | salary   |
   +------------+----------+
   | jingliyang |  9000.00 |
   | jinxin     | 30000.00 |
   +------------+----------+
   2 rows in set (0.00 sec)