练习四答案

构建数据库

数据库

数据表

answer开头表为对应题号答案形成的数据表

表结构

表数据

答案：

1、编写脚本，创建mis数据库，创建dept，创建employee表

SQL语句

CREATE TABLE `dept` (                                      
  `dept_id` int PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号', 
  `dept_name` varchar(20) NOT NULL COMMENT '部门名称'          
);     
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 '部门编号'                              
);   
​

结果：

2、添加约束：dept表的 dept_name 列的值是唯一的 ;employee表的dept_id 列引用dept表的 dept_id 列 ,给employee表的gender字段添加默认约束，默认为'男'

SQL语句

 ALTER TABLE answer2_dept ADD UNIQUE (dept_name);
 ALTER TABLE answer2_employee ADD CONSTRAINT FOREIGN KEY(dept_id) REFERENCES answer2_dept(dept_id);
 alter table answer2_employee modify gender varchar(10) default '男';

结果

3、根据表2和表4，向dept表和employee表中添加测试数据

SQL语句

 insert into dept(dept_name) values('开发部门'),('测试部门');
 insert into employee(emp_name,birthday,gender,salary,dept_id) values
 ('林冲','1981-10-10','男',2800,1),
 ('宋江','1992-6-21','男',3100,2),
 ('扈三娘','1984-3-8','女',3100,1),
 ('孙二娘','1992-6-7','女',2950,2);

结果

4、查询工资大于2900元的员工信息-

SQL语句

select * from employee where salary > 2900;

结果

5、将孙二娘的出生日期更改为1985-6-8

SQL语句

UPDATE employee SET birthday = '1985-6-8' WHERE emp_name = '孙二娘';

结果

6、删除员工扈三娘

SQL语句

delete from answer6 where emp_name = '扈三娘';

结果

7、查找工资最低的两个员工

SQL语句

select * from employee order by salary desc limit 2;

结果

8、按照工资降序排序员工信息

SQL语句

select * from employee order by salary desc;

结果

9、删除测试部门及其员工删除测试部门及其员工

SQL语句

ALTER TABLE employee ADD CONSTRAINT FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ON DELETE CASCADE;
DELETE FROM dept WHERE dept_id = 4;

结果

10、统计男员工和女员工的人数

SQL语句

select gender,count(1) from employee group by gender;

结果

11、计算最高工资和最低工资的差额

SQL语句

SELECT MAX(salary)-MIN(salary) FROM employee;

结果

12、统计部门平均工资，及部门名称

SQL语句

select avg(e.salary),d.dept_name from employee e,dept d where d.dept_id = e.dept_id group by d.dept_name;

结果

13、查询员工信息，显示员工姓名，部门名称，薪水

SQL语句

select e.emp_name,d.dept_name,e.salary from employee e,dept d where d.dept_id = e.dept_id ;

结果