create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('alex','male',78,'','teacher',1000000.31,401,1),
('yuanhao','male',73,'','teacher',3500,401,1),
('liwenzhou','male',28,'','teacher',2100,401,1),
('jingliyang','female',18,'','teacher',9000,401,1),
('jinxin','male',18,'','teacher',30000,401,1),
('成龙','male',48,'','teacher',10000,401,1),('歪歪','female',48,'','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'','sale',2000.35,402,2),
('丁丁','female',18,'','sale',1000.37,402,2),
('星星','female',18,'','sale',3000.29,402,2),
('格格','female',28,'','sale',4000.33,402,2),('张野','male',28,'','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'','operation',20000,403,3),
('程咬银','female',18,'','operation',19000,403,3),
('程咬铜','male',18,'','operation',18000,403,3),
('程咬铁','female',18,'','operation',17000,403,3)
;
1. 查看岗位是teach
er的员工姓名、年龄
mysql> select name,age,post from employee where post='teacher';
+------------+-----+---------+
| name | age | post |
+------------+-----+---------+
| alex | 78 | teacher |
| yuanhao | 73 | teacher |
| liwenzhou | 28 | teacher |
| jingliyang | 18 | teacher |
| jinxin | 18 | teacher |
| 成龙 | 48 | teacher |
+------------+-----+---------+
6 rows in set (0.00 sec)
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
mysql> select name,age,post from employee where post='teacher' and age>30;
+---------+-----+---------+
| name | age | post |
+---------+-----+---------+
| alex | 78 | teacher |
| yuanhao | 73 | teacher |
| 成龙 | 48 | teacher |
+---------+-----+---------+
3 rows in set (0.00 sec)
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
mysql> select name,age,post,salary from employee where post='teacher' and salary between 9000 and 10000;
+------------+-----+---------+----------+
| name | age | post | salary |
+------------+-----+---------+----------+
| jingliyang | 18 | teacher | 9000.00 |
| 成龙 | 48 | teacher | 10000.00 |
+------------+-----+---------+----------+
2 rows in set (0.00 sec)
4. 查看岗位描述不为NULL的员工信息
mysql> select * from employee where post_comment is not null;
Empty set (0.00 sec)
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from employee where post='teacher' and salary in(10000,9000,30000);
+------------+-----+----------+
| name | age | salary |
+------------+-----+----------+
| jingliyang | 18 | 9000.00 |
| jinxin | 18 | 30000.00 |
| 成龙 | 48 | 10000.00 |
+------------+-----+----------+
3 rows in set (0.00 sec)
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from employee where post='teacher' and salary not in(10000,9000,30000);
+-----------+-----+------------+
| name | age | salary |
+-----------+-----+------------+
| alex | 78 | 1000000.31 |
| yuanhao | 73 | 3500.00 |
| liwenzhou | 28 | 2100.00 |
+-----------+-----+------------+
3 rows in set (0.00 sec)
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
mysql> select name,salary*12 from employee where post='teacher' and name like 'jin%';
+------------+-----------+
| name | salary*12 |
+------------+-----------+
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
+------------+-----------+
2 rows in set (0.00 sec)
