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

先创建表格slt：

mysql> create table slt(
	-> num int auto_increment primary key,
	-> name char(10),
	-> job char(10),
	-> age int,
	-> salary int,
	-> descrip char(128)not null default \'\'
	-> )charset=utf8;

查看列表

mysql> desc slt;
+---------+-----------+------+-----+---------+----------------+
| Field   | Type      | Null | Key | Default | Extra          |
+---------+-----------+------+-----+---------+----------------+
| num     | int(11)   | NO   | PRI | NULL    | auto_increment |
| name    | char(10)  | YES  |     | NULL    |                |
| job     | char(10)  | YES  |     | NULL    |                |
| age     | int(11)   | YES  |     | NULL    |                |
| salary  | int(11)   | YES  |     | NULL    |                |
| descrip | char(128) | NO   |     |    |                |
+---------+-----------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

此时表为空

mysql> select * from slt;
Empty set (0.00 sec)

插入数据：

mysql> insert into tlt(name, job, age, salary,descrip) values
    -> (\'Tom\',   \'teacher\',    30,  	20000,	level2	),
    -> (\'frank\',	\'teacher\'， 31, 	21000, 	level2	),
    -> (\'jack\',	 \'teacher\', 	32,	 22000, 	level2	),
    -> (\'jhon\', 	\'asistant\',	23,	8000,	 \'\'	),
    -> (\'hugo\', 	\'manager\', 	45, 	30000, 	level4	),
    -> (\'jinhisan\', 	\'teacher\', 	26, 	9000, 	level1	)
    -> ;
Query OK, 6 row affected (0.01 sec)

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

mysql> select distinct name, age from slt where job=\'teacher\';

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

mysql> select distinct name, age from slt where job=\'teacher\' and age>30;

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

mysql> select distinct name, age, salary from slt where job=\'teacher\' and (salary>1000 and salary<9000);

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

mysql> select * from slt where descrip!=NULL ;

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

mysql> select distinct name, age, salary from slt where job=\'teacher\' and (salary=10000 or salary=9000 or salary=30000);

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

mysql> select distinct name, age, salary from slt where job=\'teacher\' and (salary not in (10000,9000,30000));

7、查看岗位是teacher且名字是\’jin\’开始的员工姓名、年薪

mysql> select * from slt where job=\'teacher\' and name like \'jin%\' ;

版权声明：本文为allenchen168原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/allenchen168/p/11762030.html