作业_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开头的员工姓名、年薪

实现：

创建表

show databases;
use test;

create table staff_info(
id int auto_increment primary,
name varchar(11),
gender enum('male','female'),
age int,
salary int,
job varchar(11));

desc staff_info;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)           | YES  |     | NULL    |                |
| gender | enum('male','female') | YES  |     | NULL    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| salary | int(11)               | YES  |     | NULL    |                |
| job    | varchar(10)           | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

录入数据

insert into staff_info(name,gender,age,salary,job) values
('xiao_bai_long','male',18,3.3,'zuo_ji'),
('xiao_hei_long','male',19,2.8,'zuo_ji'),
('xiao_hong_long','female',20,3.1,'qqu_jing_ren');

insert into staff_info(name,age,salary,job) values
('jin_long',35,3.8,'teacher'),
('tan_zhang_lao',38,9000,'teacher');

select * from staff_info;
+----+------------+--------+------+--------+------------+
| id | name       | gender | age  | salary | job        |
+----+------------+--------+------+--------+------------+
|  1 | xiao_bai_l | male   |   18 |    3.3 | zuo_ji     |
|  2 | xiao_hei_l | male   |   19 |    2.8 | zuo_ji     |
|  3 | xiao_hong_ | female |   20 |    3.1 | qqu_jing_r |
|  5 | jin_long   | NULL   |   35 |    3.8 | teacher    |
|  6 | tan_zhang_ | NULL   |   38 |    9.9 | teacher     |
+----+------------+--------+------+--------+------------+

实现需求

1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
# 1
select name,age from staff_info where job='teacher';
# 2
select name,age from staff_info where job='teacher' and age>30;
# 3
select name,age,salary from staff_info where job='teacher' and salary between 9000 and 10000;
# 4
select * from staff_info where job is not NULL;
# 5
select name,age,salary from staff_info where job='teacher' and salary in (9000,10000,30000);
# 6
select name,age,salary from staff_info where job='teacher' and salary not in (9000,10000,30000);
# 7
select name,salary*12 from staff_info where job='teacher' and name like 'jin%';