mysql练习5

数据准备
创建两张表:部门(dept)和员工(emp)，并插入数据，代码如下
create table dept(
dept id int primary key auto increment comment'部门编号',
dept_name char(20)comment'部门名称'
);
insert into dept (dept_name) values('销售部'),('财务部'),('生产部'),('人事部');

create table emp(
emp_id int primary key auto_increment comment'员工号'，
emp name char(20) not null default '' comment'员工姓名'， gender char(2) not null default '男'comment'性别'，
birth datetime not null default'1990-1-1'comment'出生日期'， salary decimal(10,2) not null default 0 comment'工资'，
address varchar(200) not null default '' comment'通讯地址'，
dept_id int comment'部门编号'
);

create index idx name on emp(emp name);
create index idx birth on emp(birth);
create index idx deptid name on emp(dept id,emp name);

insert into emp (emp_name,gender,birth,salary,address,dept_id)
values('张晓红'，'女','1980-1-23',5800，'河南省郑州市中原路10号',1)，
('张静静','女','1987-10-3',5400，'河南省新乡市平原路38号',1),
('王云飞'，男'，'1992-11-15',5600，'河南省新乡市人民路28号',1)，
('王鹏飞'，男'，'1987-10-1'，6800，'河南省新乡市东明大道12号',1)，

'王大鹏'，男，1989-2-11'，5900，'河南省郑州市东风路15号',1)，
('王萌萌',   女','1986-12-30',5000，'河南省开封市五一路14号',2)，
('王大光'，'男'，'1988-11-8'，6200，'河南省开封市八一路124号'2),
('王小明',男'，'1998-1-3',4800，'河南省驻马店市雪松路128号',2)，
('王娜娜'，   女   '1994-3-5',5200，'河南省驻马店市车站路2号',2),
('刘云飞'，男'，'1992-8-13',6800，'河南省南阳市民生路255号',3)，
('张陆军',男','1991-9-6'，6200，'河南省南阳市张仲景路14号',3);

1、创建视图v_emp_dept_id 1，查询销售部门的员工姓名和家庭住址

create v_emp_dept_id_1

as select emp_name,address

from emp inner join dept

on dept.dept_id = emp.dept_id

where emp.dept_id = (select dept_id from dept where dept_name = '销售部） ;

2、创建视图vempdept，查询销售部门员工姓名和家庭住址及部门名称。

create view v_emp_dept

as select emp_name,address,dept.dept_name

from emp inner join dept

on dept.dept_id = emp.dept_id

where dept_id = (select dept_id from dept where dept_name = '销售部'） ;

3、创建视图v_dept_emp_count(dept_name,emp_count,avg_salay)，统计每个部门人数并计算平均工资

create view v_dept_emp_count(dept_name,emp_count,avg_salay)

as select emp_name,count(1),avg(salary)

from emp inner join dept

on dept.dept_id = emp.dept_id

group by emp.dept_id;

4、修改视图v_emp_dept，查询销售部门员工姓名、家庭住址、工资和部门名称。

alter view v_emp_dept as

select emp.emp_name,emp.address,emp.salary,dept.dept_name

from emp inner join dept

on dept.dept_id = emp.dept_id;

5、查看视图名称: 查看视图结构; 查看创建视图语句;

6、删除以上三个视图。

存储过程作业:

1、创建一个提取emp表所有员工工资和的存储过程s1

\d @

create procedure s1()

begin

select sum(salary) from emp;

end @

\d ;

call s1();

2、调用存储过程s1

3、创建存储过程s2，实现输入员工姓名后返回员工的家庭住址。

\d @

create procedure s2(in sname char(20) ,out saddress char(200))

begin

select address into saddress from emp where emp.emp_name = sname;

end @

\d ;