表记录查询小练习
- 查看岗位是teacher的员工姓名、年龄
- 查看岗位是teacher且年龄大于26岁的员工姓名、年龄
- 查看岗位是teacher且薪资在12000-16000范围内的员工姓名、年龄、薪资
- 查看岗位描述不为NULL的人员信息
- 查看岗位是teacher且薪资是10000或14000员工姓名、年龄、薪资
- 查看岗位是teacher且薪资不是10000或14000的员工姓名、年龄、薪资
- 查看岗位是teacher且名字是b开头的员工姓名、薪资
mysql> select * from t1;
+----+---------+-----+---------+--------+----------+
| id | name | age | job | salary | job_desc |
+----+---------+-----+---------+--------+----------+
| 1 | alpha | 18 | student | 0 | NULL |
| 2 | bravo | 25 | teacher | 10000 | python |
| 3 | charlie | 26 | teacher | 12000 | NULL |
| 4 | delta | 27 | teacher | 14000 | golang |
| 5 | echo | 28 | teacher | 16000 | NULL |
+----+---------+-----+---------+--------+----------+
mysql> select name,age from t1 where job='teacher';
+---------+-----+
| name | age |
+---------+-----+
| bravo | 25 |
| charlie | 26 |
| delta | 27 |
| echo | 28 |
+---------+-----+
4 rows in set (0.00 sec)
mysql> select name,age from t1 where job='teacher'and age>26;
+-------+-----+
| name | age |
+-------+-----+
| delta | 27 |
| echo | 28 |
+-------+-----+
2 rows in set (0.00 sec)
mysql> select name, age, salary from t1 where job='teacher'and salary between 12
000 and 16000;
+---------+-----+--------+
| name | age | salary |
+---------+-----+--------+
| charlie | 26 | 12000 |
| delta | 27 | 14000 |
| echo | 28 | 16000 |
+---------+-----+--------+
3 rows in set (0.00 sec)
mysql> select * from t1 where job_desc is not NULL;
+----+-------+-----+---------+--------+----------+
| id | name | age | job | salary | job_desc |
+----+-------+-----+---------+--------+----------+
| 2 | bravo | 25 | teacher | 10000 | python |
| 4 | delta | 27 | teacher | 14000 | golang |
+----+-------+-----+---------+--------+----------+
2 rows in set (0.00 sec)
mysql> select name, age, salary from t1 where job='teacher'and salary in (10000,
14000);
+-------+-----+--------+
| name | age | salary |
+-------+-----+--------+
| bravo | 25 | 10000 |
| delta | 27 | 14000 |
+-------+-----+--------+
2 rows in set (0.00 sec)
mysql> select name, age, salary from t1 where job='teacher'and salary not in (10
000, 14000);
+---------+-----+--------+
| name | age | salary |
+---------+-----+--------+
| charlie | 26 | 12000 |
| echo | 28 | 16000 |
+---------+-----+--------+
2 rows in set (0.00 sec)
mysql> select name, salary from t1 where name like 'b%';
+-------+--------+
| name | salary |
+-------+--------+
| bravo | 10000 |
+-------+--------+
1 row in set (0.00 sec)