1029 作业
1029 作业
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.创建老师数据库
create database teacher;
2.选择老师数据库准备创建
use teacher;
3.创建老师信息表
表头有id,岗位,姓名,年龄,薪资
create table teacher(
id int auto_increment primary key,
name char(32),
age int,
post char(32),
salary char(32)
)charset = utf8;
4.给信息表添加老师信息
insert into teacher (name,age,post,salary) values ('tank',50,'teacher',8000),('nick',17,'teacher',10000),('xiaoming',100,'student',3000),('dashan',60,'teacher',8000),('huage',25,'teacher',9000),('ming',23,'student',5000);
5.查看信息表内容
+----+----------+------+---------+--------+
| id | name | age | post | salary |
+----+----------+------+---------+--------+
| 1 | tank | 50 | teacher | 8000 |
| 2 | nick | 17 | teacher | 10000 |
| 3 | xiaoming | 100 | student | 3000 |
| 4 | dashan | 60 | teacher | 8000 |
| 5 | huage | 25 | teacher | 9000 |
| 6 | ming | 23 | student | 5000 |
+----+----------+------+---------+--------+
6 rows in set (0.00 sec)
6.查看岗位是teacher的员工姓名、年龄
select * from teacher where post='teacher';
/*
+----+--------+------+---------+--------+
| id | name | age | post | salary |
+----+--------+------+---------+--------+
| 1 | tank | 50 | teacher | 8000 |
| 2 | nick | 17 | teacher | 10000 |
| 4 | dashan | 60 | teacher | 8000 |
| 5 | huage | 25 | teacher | 9000 |
+----+--------+------+---------+--------+
4 rows in set (0.00 sec)
7.查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select * from teacher where post='teacher' and age>30;
/*
+----+--------+------+---------+--------+
| id | name | age | post | salary |
+----+--------+------+---------+--------+
| 1 | tank | 50 | teacher | 8000 |
| 4 | dashan | 60 | teacher | 8000 |
+----+--------+------+---------+--------+
2 rows in set (0.00 sec)
8.查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select * from teacher where post='teacher' and salary between 9000 and 10000 ;
/*
+----+-------+------+---------+--------+
| id | name | age | post | salary |
+----+-------+------+---------+--------+
| 2 | nick | 17 | teacher | 10000 |
| 5 | huage | 25 | teacher | 9000 |
+----+-------+------+---------+--------+
2 rows in set (0.00 sec)
9.查看岗位描述不为NULL的员工信息
select * from teacher where post is not null;
/*
+----+----------+------+---------+--------+
| id | name | age | post | salary |
+----+----------+------+---------+--------+
| 1 | tank | 50 | teacher | 8000 |
| 2 | nick | 17 | teacher | 10000 |
| 3 | xiaoming | 100 | student | 3000 |
| 4 | dashan | 60 | teacher | 8000 |
| 5 | huage | 25 | teacher | 9000 |
| 6 | ming | 23 | student | 5000 |
+----+----------+------+---------+--------+
6 rows in set (0.00 sec)
10.查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select * from teacher where post='teacher' and salary in(10000,3000,9000);
/*
+----+-------+------+---------+--------+
| id | name | age | post | salary |
+----+-------+------+---------+--------+
| 2 | nick | 17 | teacher | 10000 |
| 5 | huage | 25 | teacher | 9000 |
+----+-------+------+---------+--------+
2 rows in set (0.00 sec)
11.查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select * from teacher where post='teacher' and salary not in(10000,3000,9000);
/*
+----+--------+------+---------+--------+
| id | name | age | post | salary |
+----+--------+------+---------+--------+
| 1 | tank | 50 | teacher | 8000 |
| 4 | dashan | 60 | teacher | 8000 |
+----+--------+------+---------+--------+
2 rows in set (0.00 sec)
12.查看岗位是teacher且名字是jin开头的员工姓名、年薪
select * from teacher where post='teacher' and name like 'jin%';
//Empty set (0.00 sec) 并没有
增加一个金太阳
insert into teacher(name,age,post,salary) values('jintaiyang',5000,'teacher',100000000000);
// Query OK, 1 row affected (0.00 sec)
查询
select * from teacher where post='teacher' and name like 'jin%';
/*
+----+------------+------+---------+--------------+
| id | name | age | post | salary |
+----+------------+------+---------+--------------+
| 8 | jintaiyang | 5000 | teacher | 100000000000 |
+----+------------+------+---------+--------------+
1 row in set (0.00 sec)