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)