day34作业

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> desc userinfo;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)      | NO   |     | NULL    |                |
| age    | int(10) unsigned | NO   |     | 0       |                |
| sarlay | decimal(10,2)    | NO   |     | 0.00    |                |
| job    | char(10)         | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

表数据：

mysql> select * from userinfo;
+----+----------+-----+-----------+---------+
| id | name     | age | sarlay    | job     |
+----+----------+-----+-----------+---------+
|  1 | qinyj    |  18 |      0.00 | student |
|  2 | tank     |  18 |  10000.11 | teacher |
|  3 | jack     |  33 |  10000.33 | teacher |
|  4 | jing     |  20 |      1.33 | null    |
|  5 | xxx      |  20 | 100000.33 | teacher |
|  6 | xxx      |  20 |   1000.33 | teacher |
|  7 | xxx      |  20 | 100000.00 | teacher |
|  8 | xxx      |  20 |  10000.00 | teacher |
|  9 | jinggggg |  20 |  10000.00 | teacher |
+----+----------+-----+-----------+---------+
9 rows in set (0.00 sec)

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

   mysql> select name,age from userinfo where job="teacher";
   +------+-----+
   | name | age |
   +------+-----+
   | tank |  18 |
   | jack |  33 |
   | xxx  |  20 |
   | xxx  |  20 |
   +------+-----+
   4 rows in set (0.00 sec)
   
   # 查看符合条件的个数
   mysql> select count(*) from userinfo where job="teacher";
   +----------+
   | count(*) |
   +----------+
   |        7 |
   +----------+
   1 row in set (0.00 sec)
   

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

   mysql> select name,age from userinfo where job="teacher" and age > 30;
   +------+-----+
   | name | age |
   +------+-----+
   | jack |  33 |
   +------+-----+
   
   # 查看符合条件的个数
   mysql> select count(*) from userinfo where job="teacher" and age > 30;        
   +----------+
   | count(*) |
   +----------+
   |        1 |
   +----------+
   1 row in set (0.00 sec)
   

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

   mysql> select name,age,sarlay from userinfo where sarlay between 1000 and 9000;
   +------+-----+---------+
   | name | age | sarlay  |
   +------+-----+---------+
   | xxx  |  20 | 1000.33 |
   +------+-----+---------+
   1 row in set (0.00 sec)
   
   # 查看符合条件的个数
   mysql> select count(*) from userinfo where sarlay between 1000 and 9000;             
   +----------+
   | count(*) |
   +----------+
   |        1 |
   +----------+
   1 row in set (0.00 sec)
   

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

   mysql> select * from userinfo where job is not null;
   +----+-------+-----+-----------+---------+
   | id | name  | age | sarlay    | job     |
   +----+-------+-----+-----------+---------+
   |  1 | qinyj |  18 |      0.00 | student |
   |  2 | tank  |  18 |  10000.11 | teacher |
   |  3 | jack  |  33 |  10000.33 | teacher |
   |  4 | jing  |  20 |      1.33 | null    |
   |  5 | xxx   |  20 | 100000.33 | teacher |
   |  6 | xxx   |  20 |   1000.33 | teacher |
   +----+-------+-----+-----------+---------+
   6 rows in set (0.00 sec)
   
   # 查看符合条件的个数
   mysql> select count(*) from userinfo where job is not null; 
   +----------+
   | count(*) |
   +----------+
   |        9 |
   +----------+
   1 row in set (0.00 sec)
   

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

   mysql> select name,age,sarlay from userinfo where job="teacher" and sarlay in (9000,10000,30000);
   +------+-----+----------+
   | name | age | sarlay   |
   +------+-----+----------+
   | xxx  |  20 | 10000.00 |
   +------+-----+----------+
   1 row in set (0.00 sec)
   
   # 查看符合条件的个数
   mysql> select count(*) from userinfo where job="teacher" and sarlay in (9000,10000,30000);
   +----------+
   | count(*) |
   +----------+
   |        2 |
   +----------+
   1 row in set (0.00 sec)
   

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

   mysql> select name,age,sarlay from userinfo where job="teacher" and sarlay not in (10000,9000,30000);
   +------+-----+-----------+
   | name | age | sarlay    |
   +------+-----+-----------+
   | tank |  18 |  10000.11 |
   | jack |  33 |  10000.33 |
   | xxx  |  20 | 100000.33 |
   | xxx  |  20 |   1000.33 |
   | xxx  |  20 | 100000.00 |
   +------+-----+-----------+
   5 rows in set (0.00 sec)
   
   # 查看符合条件的个数
   mysql> select count(*) from userinfo where job="teacher" and sarlay not in (10000,9000,30000);
   +----------+
   | count(*) |
   +----------+
   |        5 |
   +----------+
   1 row in set (0.00 sec)
   
   

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

   mysql> select name,sarlay*12 as sarlay from userinfo where job="teacher" and name like "jin%";
   +----------+-----------+
   | name     | sarlay    |
   +----------+-----------+
   | jinggggg | 120000.00 |
   +----------+-----------+
   1 row in set (0.00 sec)