目录
某公司的mis数据库中有部⻔表(dept)和员工表(employee)表结构及其数据如下:
表1:dept表结构
dept_id | dept_name |
1 | 开发部门 |
2 | 测试部门 |
表2:dept表数据
列名称 | 类型 | 说明 | 约束 |
dept_id | int | 部⻔编号 | 主键,自增⻓ |
dept_name | varchar(20) | 部⻔名称 | 不允许为空 |
create table dept(
dept_id int primary key Auto_increment comment '部门编号',
dept_name varchar(20) not null comment '部门名称');
insert into dept values(1,'开发部门'),(2,'测试部门');
表3:employee表结构
列名称 | 类型 | 说明 | 约束 |
emp_id | int | 员工编号 | 主键,自增长 |
emp_name | varchar(20) | 员工姓名 | |
birthday | date | 出生日期 | |
gender | varchar(10) | 员工性别 | |
salary | double | 员工工资 | |
dept_id | int | 部门编号 |
表4: employee表数据
emp_id | emp_name | birthday | gender | salary | dept_id |
1 | 林冲 | 1981-10-10 | 男 | 2800 | 1 |
2 | 宋江 | 1992-6-21 | 男 | 3100 | 2 |
3 | 扈三娘 | 1984-3-8 | 女 | 3000 | 1 |
4 | 孙二娘 | 1985-6-7 | 女 | 2950 | 2 |
create table employee(
emp_id int primary key auto_increment comment '员工编号',
emp_name varchar(20) comment '员工姓名',
birthday date comment '出生日期',
gender varchar(10) comment '员工性别',
salary double comment '员工工资',
dept_id int comment '部门编号'
);
insert into employee values
(1,'林冲','1981-10-10', '男',2800,1),
(2,'宋江','1992-6-21', '男',3100,2),
(3,'扈三娘','1984-3-8', '女',3000,1),
(4,'孙二娘','1985-6-7', '女',2950,2);
练习
-- 任务1:编写脚本,创建mis数据库,创建dept表,创建employee表。 eg。。。。 |
-- 任务2:添加约束 :dept表的 dept_name 列的值是唯一的 ; alter table dept add unique (dept_name); employee表的 |
dept_id 列引用dept表的 dept_id 列 ,给employee表的gender字段添加默认约 |
束,默认为'男' alter table employee add foreign key (dept_id) references dept(dept_id) on delete cascade ; alter table employee modify gender varchar(10) default '男' |
-- 任务3:根据表2和表4,向dept表和employee表中添加测试数据 eg。。。。。。 |
-- 任务4:查询工资大于2900元的员工信息 select * from employee where salary>2900; |
-- 任务5:将孙二娘的出生日期更改为1985-6-8 update employee set birthday ='1985-6-8'where emp_name ='孙二娘'; |
-- 任务6:删除员工扈三娘 delete from employee where emp_name = '扈三娘'; |
-- 任务7:查找工资最低的两个员工 select emp_name,salary from employee order by salary limit 0,2; |
-- 任务8:按照工资降序排序员工信息 select * from employee order by salary desc; |
-- 任务9:删除测试部门及其员工 delete from dept where dept_id = 2; |
-- 任务10: 统计男员工和女员工的人数 select gender, count(gender) As 人数 from employee group by gender; |
-- 任务11: 计算最高工资和最低工资的差额 select max(salary)-min(salary) as 差额 from employee; |
-- 拓展任务:统计部门平均工资,及部门名称 select dept_name,avg(salary)from dept,employee group by dept_name; |
-- 拓展任务:查询员工信息,显示员工姓名,部门名称,薪水 select emp_name, dept_name,salary from dept d1,employee e1 where d1.dept_no=e1.dept_no ; |