【无标题】

mysql> create table dept(
-> dept_id int primary key auto_increment comment ‘部门编号’,
-> dept_name char(20) comment ‘部门名称’
-> );
mysql> 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 ‘部门编号’
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> create index idx_name on emp(emp_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index idx_birth on emp(birth);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index idx_deptid_name on emp(dept_id,emp_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> 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);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
111111111111111111111111
mysql> create view v_emp_dept_id_1 as
-> select emp_name,address from emp e
-> inner join dept d
-> on e.dept_id=d.dept_id
-> where dept_name=‘销售部’;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_emp_dept_id_1;
±---------±-------------------------+
| emp_name | address |
±---------±-------------------------+
| 张晓红 | 河南省郑州市中原路10号 |
| 张静静 | 河南省新乡市平原路38号 |
| 王云飞 | 河南省新乡市人民路28号 |
| 王大鹏 | 河南省郑州市东风路15号 |
| 王鹏飞 | 河南省新乡市东明大道12号 |
±---------±-------------------------+
5 rows in set (0.00 sec)
222222222222222222
mysql> create view v_emp_dept as
-> select emp_name,address,dept_name from emp e
-> inner join dept d
-> on e.dept_id=d.dept_id
-> where dept_name=‘销售部’;
mysql> select * from v_emp_dept;
±---------±-------------------------±----------+
| emp_name | address | dept_name |
±---------±-------------------------±----------+
| 张晓红 | 河南省郑州市中原路10号 | 销售部 |
| 张静静 | 河南省新乡市平原路38号 | 销售部 |
| 王云飞 | 河南省新乡市人民路28号 | 销售部 |
| 王大鹏 | 河南省郑州市东风路15号 | 销售部 |
| 王鹏飞 | 河南省新乡市东明大道12号 | 销售部 |
±---------±-------------------------±----------+
5 rows in set (0.00 sec)
333333333333333333333
mysql> create view v_dept_emp_count(dept_name,emp_count,avg_salay) as
-> select dept_name,count(1),avg(salary)
-> from emp e inner join dept d
-> on e.dept_id=d.dept_id
-> group by dept_name;
mysql> select * from v_dept_emp_count;
±----------±----------±------------+
| dept_name | emp_count | avg_salay |
±----------±----------±------------+
| 销售部 | 5 | 5900.000000 |
| 财务部 | 4 | 5300.000000 |
| 生产部 | 2 | 6500.000000 |
±----------±----------±------------+
3 rows in set (0.00 sec)
444444444444444444444444444
mysql> alter view v_emp_dept as
-> select emp_name,address,salary,dept_name from emp e
-> inner join dept d
-> on e.dept_id=d.dept_id
-> where dept_name=‘销售部’;
mysql> select * from v_emp_dept;
±---------±-------------------------±--------±----------+
| emp_name | address | salary | dept_name |
±---------±-------------------------±--------±----------+
| 张晓红 | 河南省郑州市中原路10号 | 5800.00 | 销售部 |
| 张静静 | 河南省新乡市平原路38号 | 5400.00 | 销售部 |
| 王云飞 | 河南省新乡市人民路28号 | 5600.00 | 销售部 |
| 王大鹏 | 河南省郑州市东风路15号 | 5900.00 | 销售部 |
| 王鹏飞 | 河南省新乡市东明大道12号 | 6800.00 | 销售部 |
±---------±-------------------------±--------±----------+
5 rows in set (0.00 sec)
55555555555555555555
mysql> show full tables;
±--------------------±-----------+
| Tables_in_db_system | Table_type |
±--------------------±-----------+
| bia | BASE TABLE |
| biao | BASE TABLE |
| dept | BASE TABLE |
| emp | BASE TABLE |
| grade | BASE TABLE |
| student | BASE TABLE |
| studet | BASE TABLE |
| user | BASE TABLE |
| useralary | BASE TABLE |
| v_dept_emp_count | VIEW |
| v_emp_dept | VIEW |
| v_emp_dept_id_1 | VIEW |
±--------------------±-----------+
12 rows in set (0.00 sec)

mysql> desc v_dept_emp_count;
±----------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------±--------------±-----±----±--------±------+
| dept_name | char(20) | YES | | NULL | |
| emp_count | bigint | NO | | 0 | |
| avg_salay | decimal(14,6) | YES | | NULL | |
±----------±--------------±-----±----±--------±------+
3 rows in set (0.00 sec)
mysql> show create view v_dept_emp_count \g
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------±---------------------+
| View | Create View | character_set_client | collation_connection |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------±---------------------+
| v_dept_emp_count | CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW v_dept_emp_count (dept_name,emp_count,avg_salay) AS select d.dept_name AS dept_name,count(1) AS count(1),avg(e.salary) AS avg(salary) from (emp e join dept d on((e.dept_id = d.dept_id))) group by d.dept_name | gbk | gbk_chinese_ci |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------±---------------------+
1 row in set (0.00 sec)
666666666666666666666666
mysql> drop view v_dept_emp_count,v_emp_dept,v_emp_dept_id_1;
Query OK, 0 rows affected (0.01 sec)

mysql> show full tables;
±--------------------±-----------+
| Tables_in_db_system | Table_type |
±--------------------±-----------+
| bia | BASE TABLE |
| biao | BASE TABLE |
| dept | BASE TABLE |
| emp | BASE TABLE |
| grade | BASE TABLE |
| student | BASE TABLE |
| studet | BASE TABLE |
| user | BASE TABLE |
| useralary | BASE TABLE |
±--------------------±-----------+
9 rows in set (0.00 sec)
111111111111111111111111111
mysql> delimiter $$mysql>createprocedures1()->begin->selectsum(salary)fromemp;->end$$
222222222222222222
mysql> call s1();
±------------+
| sum(salary) |
±------------+
| 63700.00 |
±------------+
1 row in set (0.00 sec)
3333333333333333
mysql> delimiter //
mysql> create procedure s2(in name_address char(20))
-> begin
-> select address from emp where emp_name = name_address;
-> end //
Query OK, 0 rows affected (0.00 sec)
4444444444444444
mysql> call s2(‘王云飞’);
±-----------------------+
| address |
±-----------------------+
| 河南省新乡市人民路28号 |
±-----------------------+
1 row in set (0.00 sec
666666666666666
mysql> call avg_sai(1,‘男’,@avg_salary);
±------------+
| avg_salary |
±------------+
| 6100.000000 |
±------------+
1 row in set (0.00 sec)
666666666666666666
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure s1;
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure s2;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure avg_sai;
Query OK, 0 rows affected (0.00 sec)