slt mysql_数据库操作 练习一

查看岗位是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%' ;