day 34 作业

day 34 作业

'''
作业：
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
'''

# 先创建一个数据库如下：
'''
mysql> select * from staff;
+----+----------+------+-----------+----------+
| id | name     | age  | post      | salary   |
+----+----------+------+-----------+----------+
|  1 | nick     |   18 | teacher   | 20000.00 |
|  2 | tank     |   20 | teacher   | 20000.00 |
|  3 | tom      |   32 | professor | 30000.00 |
|  4 | jack     |   28 | teacher   |  9000.00 |
|  5 | jane     |   25 | teacher   | 10000.00 |
|  6 | jinx     |   18 | teacher   | 12000.00 |
|  7 | tool_man |   22 | NULL      | 22345.00 |
|  8 | lihua    |   45 | teacher   | 40000.00 |
+----+----------+------+-----------+----------+
8 rows in set (0.00 sec)

'''
# 1. 查看岗位是teacher的员工姓名、年龄
mysql> select name,age from staff where post="teacher";
+-------+------+
| name  | age  |
+-------+------+
| nick  |   18 |
| tank  |   20 |
| jack  |   28 |
| jane  |   25 |
| jinx  |   18 |
| lihua |   45 |
+-------+------+
6 rows in set (0.00 sec)

# 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
mysql> select name,age from staff where post="teacher" and age>30;
+-------+------+
| name  | age  |
+-------+------+
| lihua |   45 |
+-------+------+
1 row in set (0.00 sec)

# 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
mysql> select name,age,salary from staff where post="teacher" and salary between 1000 and 9000;
+------+------+---------+
| name | age  | salary  |
+------+------+---------+
| jack |   28 | 9000.00 |
+------+------+---------+
1 row in set (0.00 sec)

# 4. 查看岗位描述不为NULL的员工信息
mysql> select * from staff where post != "NULL";
+----+-------+------+-----------+----------+
| id | name  | age  | post      | salary   |
+----+-------+------+-----------+----------+
|  1 | nick  |   18 | teacher   | 20000.00 |
|  2 | tank  |   20 | teacher   | 20000.00 |
|  3 | tom   |   32 | professor | 30000.00 |
|  4 | jack  |   28 | teacher   |  9000.00 |
|  5 | jane  |   25 | teacher   | 10000.00 |
|  6 | jinx  |   18 | teacher   | 12000.00 |
|  8 | lihua |   45 | teacher   | 40000.00 |
+----+-------+------+-----------+----------+
7 rows in set (0.00 sec)

# 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from staff where post="teacher" and salary in (9000,10000,30000);
+------+------+----------+
| name | age  | salary   |
+------+------+----------+
| jack |   28 |  9000.00 |
| jane |   25 | 10000.00 |
+------+------+----------+
2 rows in set (0.00 sec)

# 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from staff where post="teacher" and salary not in (9000,10000,30000);
+-------+------+----------+
| name  | age  | salary   |
+-------+------+----------+
| nick  |   18 | 20000.00 |
| tank  |   20 | 20000.00 |
| jinx  |   18 | 12000.00 |
| lihua |   45 | 40000.00 |
+-------+------+----------+
4 rows in set (0.00 sec)

# 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
mysql> select name,salary from staff where post="teacher" and name like "jin%";
+------+----------+
| name | salary   |
+------+----------+
| jinx | 12000.00 |
+------+----------+
1 row in set (0.00 sec)