你最需要的mysql-（二）

---书接上回！！！
-----------------------------------------------------------------------
第六章：事务控制语句（TCL语句）
-----------------------------------------------------------------------
-- COMMIT 提交，ROLLBACK 回滚，SAVEPOINT  回滚点

-- 什么是事务？
-- 事务是包含SQL语句的一个执行整体，对DML语句生效
-- mysql版本的存储引擎决定当前是否支持事务
SHOW ENGINES;-- 查看当前数据库支持的存储引擎 InnoDB
-- mysql InnoDB默认自动提交事务
-----------------------------------------------
-- 关闭自动提交事务功能 
-- SET autocommit=1;-- 自动提交事务
SET autocommit=0;-- 关闭自动提交事务

-- 为emp表增加一个实习生
INSERT INTO emp
VALUES(1111,'XIAOMIN','CLERK',7369,CURDATE(),200,NULL,20);

SELECT * FROM emp
SAVEPOINT  A;  -- 设置回滚点A

-- 为emp表再增加一个实习生
INSERT INTO emp
VALUES(1112,'XIAOHUA','CLERK',7369,CURDATE(),200,NULL,20);

ROLLBACK TO SAVEPOINT A; -- 回滚到指定的回滚点A处
ROLLBACK ; -- 回滚到最初事务开始的位置
-----------------------------------
SET autocommit=0;  -- 关闭自动提交事务（手动提交事务，Oracle默认就是手动提交）
-- 向bonus表中插入一行数据
INSERT INTO bonus
VALUES('XIAOLI','BOSS','9999',NULL);

SELECT * FROM bonus;

COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务不生效（事务一旦提交，就直接对数据库服务器生效，无法回滚）
-- 注意：提交事务之前，可以回滚，提交之后，则不能回滚

-- 企业应用场景
-- ATM机转账
-- A卡向B卡转账，在A卡完成转账操作的同时，B卡需要入账操作，如果B卡入账失败，则A卡的操作会被回滚
-- 电商业务
-- 订单操作： 确认购买——扣减库存——创建订单号，若订单创建失败，则库存会被回滚到扣减之前的状态

-----------------------------------------------------------------------
第七章：常用的数据库对象（视图、索引）
-----------------------------------------------------------------------
【第1节】视图
-- 视图可以理解为数据的快捷方式，视图本身不存储数据，存储的只是一个查询语句
/*
CREATE VIEW 视图名称
AS
SELECT  -- 完整的查询语句
*/
1:新建视图
SET autocommit=0;-- 关闭自动提交事务
【用法一】针对不同的用户或权限，提供不同的视图（权限控制）
-- HR、BOSS想要查询员工的薪水
SELECT empno,ename,sal FROM emp;
-- 20号部门的经理想要查询他所在部门的员工薪水
SELECT empno,ename,sal FROM emp WHERE deptno=20;

-- 创建一个视图，保存20号部门的数据
CREATE VIEW v_dept20
AS
SELECT * FROM emp WHERE deptno=20;

-- 查询v_dept20视图（视图的用法和表基本相同）
SELECT empno,ename,sal FROM v_dept20;
-- dept20号部门的经理，要给SMITH加薪 200
UPDATE v_dept20
SET sal=sal+200
WHERE ename='SMITH';


-- Q:请问现在emp表中SMITH的薪水是多少？  
SELECT * FROM emp WHERE deptno=20;
-- 注意：视图本身不存储数据，对视图的修改就是对原表的修改
ROLLBACK ;  -- 回滚事务(只有DML语句才能被回滚）

【用法二】简化复杂的查询语句
-- 通过部门名称查询该部门的平均工资(例如：查询SALES部门）
SELECT AVG(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.dname= 'SALES';
-- 创建一个视图，用于emp表和dept表的关联（目的是将多表查询变为单表查询）
CREATE VIEW v_emp_dept
AS
SELECT e.*,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno;

-- 用视图查询某部门的平均工资(例如：查询SALES部门）
SELECT AVG(sal) FROM v_emp_dept
WHERE dname='SALES'

2.删除视图
-- 删除视图，等同于删除一个查询语句，不会对原表有任何影响
DROP VIEW 视图名称;
DROP VIEW v_dept20;
DROP VIEW v_emp_dept;


常见数据库对象【练习题】
1.创建一个视图，目的是查询CLERK职位的薪水
CREATE VIEW v_clerk
AS
SELECT * FROM emp  WHERE job='CLERK';
2.创建一个视图，目的是查询出员工的薪资等级和部门名称 （例如指定某人，查询对应的等级和部门名称）
CREATE VIEW v_empall
AS
SELECT e.* ,d.dname,d.loc,s.*
FROM emp e,dept d,salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;

SELECT ename,grade,dname FROM v_empall WHERE ename='KING';

3.删除创建的两个视图
DROP VIEW v_clerk;
DROP VIEW v_empall;
------------------------------------------
【第2节】索引 INDEX
Q:什么是索引？
-- 索引（INDEX）类似书的目录，是供服务器在表中快速查找和操作行的数据库结构
-- 索引是以列为单位建立的，经常作为查询条件的列，可以创建索引来提高查询效率

Q：索引设计原则
-- 经常出现在where，order by，group by后面的列可以创建索引
-- 有大量空值的列，数据频繁改动的列，不经常作为查询条件的列不要创建索引
-- 当某个数据具有唯一性特征时，可以指定唯一索引，以确保数据的完整性，提高查询效率

Q:优点
-- 可以大大提高数据的查询速度，这是建索引的主要原因
-- 索引的内部结构由系统自动创建，每次对数据的修改索引也会自动更新
-- 在创建主键约束和唯一约束时，会自动创建主键索引和唯一索引，保证每行数据的唯一性

Q:不足
-- 索引虽然提高了查询速度，同时却会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
-- 建立索引会占用磁盘空间的索引文件，每个表至少支持16个索引（依据存储引擎的不同而有所区别）

-- 索引的分类：唯一索引、普通索引
1：普通索引
-- 允许在定义的索引列中插入重复值/空值
-- 创建索引
CREATE INDEX 索引名称 ON 表名(列名);   -- 列名如果是BLOB,TEXT类型，则需要指定length长度
-- 为ename创建一个索引
CREATE INDEX ind_name ON emp(ename); 

-- 查看表的索引
SHOW INDEX FROM emp;

-- 创建新表时，主键列会自动创建一个唯一索引；外键列会自动创建一个普通索引

-- 用ename作为查询条件，对比创建索引前和索引后的效果
SELECT * FROM emp WHERE ename='ALLEN';   


2:唯一索引
-- 创建表时，主键自动创建唯一索引
CREATE TABLE mytable
(
id INT(5) PRIMARY KEY,  -- 主键自动创建唯一索引
username VARCHAR(20) NOT NULL,
phone BIGINT(11)
);
-- 查看mytable表的索引
SHOW INDEX FROM mytable;

-- 建表后创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
CREATE UNIQUE INDEX ind_phone ON mytable(phone);

3:组合索引
-- 当经常在一起作为查询条件的列，可以创建一个组合索引
-- 例如经常把job和deptno一起作为查询条件，则可以创建一个组合索引
CREATE INDEX ind_job_deptno ON emp(job,deptno);

-- 多列组合查询左边生效
-- 如果查询条件只有job，则ind_job_deptno 索引生效
-- 如果查询条件有job和deptno，则ind_job_deptno 索引生效
-- 如果查询条件只有deptno，则ind_job_deptno 不生效

-- 查看emp表的索引
SHOW INDEX FROM emp;


4：删除索引
DROP INDEX 索引名称 ON 表名;
DROP INDEX  ind_name ON emp;
DROP INDEX  ind_job_deptno ON emp;

-----------------------------------------------------------------------
第八章：存储过程基础 SP(Stored Procedure）
-----------------------------------------------------------------------
1：存储过程基础
Q：什么是存储过程?
-- 简单来讲，存储过程就是一条或者N条SQL语句的集合
-- 存储过程能够实现复杂的数据处理（例如：业务逻辑处理（不推荐）；准备测试数据）
-- MYSQL的编程主要体现在存储过程中
/*
DELIMITER //
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
程序主体;
END //
DELIMITER ;
*/
-- DELIMITER // 是告诉系统不要将分号作为分隔符，而是将//当做分隔符
-- DELIMITER ;  是告诉系统请将分号作为分隔符
-- 参数列表
IN 参数名称 参数类型   -- 输入参数（常用）
OUT 参数名称 参数类型   -- 输出参数
INOUT 参数名称 参数类型   -- 即可输入又可输出的参数（不推荐）

CALL 存储过程名(参数列表); -- 调用某个存储过程
------------------------------------------
-- 编写一个不带参数的存储过程，输出 hello world
DELIMITER //
CREATE PROCEDURE pro_hello()
BEGIN
SELECT 'hello world' FROM  DUAL;  -- 程序主体
END //
DELIMITER ;

-- 调用存储过程
CALL pro_hello(); 
------------------------------------------
-- 编写一个带参数的存储过程
-- 输入一个员工编号，查询出该员工的薪水
DELIMITER //
CREATE PROCEDURE pro_empsal(IN paramA INT)
BEGIN
SELECT sal FROM emp WHERE empno=paramA; -- 程序主体
END //
DELIMITER ;

-- 调用存储过程
CALL pro_empsal(7499); 

------------------------------------------
SET autocommit=0;  -- 关闭自动提交事务
-- 编写一个带参数的存储过程（既有输入又有输出）
-- 输入一个员工编号，返回该员工加薪500后的薪水
-- SELECT...INTO... 给参数或者变量赋值
DELIMITER //
CREATE PROCEDURE pro_addsal(IN paramA INT,OUT paramB FLOAT)
BEGIN
-- 程序主体可以有多个，每个用分号隔开
UPDATE emp
SET sal=sal+500 WHERE empno=paramA;

SELECT sal INTO paramB FROM emp WHERE empno=paramA;   -- SELECT 语句自带输出效果，不是非要用OUT输出的
END //
DELIMITER ;

-- 调用存储过程
-- OUT类型可以输出存储过程中的参数结果，但是不能直接被外部调用
-- 在存储过程外部如果要调用参数，则必须使用变量来处理
-- @+变量名  声明一个会话变量，作用域是整个会话
CALL pro_addsal(7499,@sal);   -- 将paramB输出的结果存储在名为@sal的变量中
SELECT @sal;-- 输出变量中的数据


SELECT sal FROM emp WHERE empno=7499

ROLLBACK;  -- 回滚事务
------------------------------------------
删除存储过程
DROP PROCEDURE 存储过程名;
DROP PROCEDURE pro_hello;
DROP PROCEDURE pro_empsal;
DROP PROCEDURE pro_addsal;
-------------------------------------------
-- 输入一个员工编号，查询出该员工的薪水
DELIMITER //
CREATE PROCEDURE pro_empsal(IN paramA INT)
BEGIN
SELECT sal FROM emp WHERE empno=paramA; -- 程序主体
END //
DELIMITER ;

-- 调用存储过程
CALL pro_empsal(7499); 
存储过程基础 【练习题】  
1）编写一个存储过程，要求能够对emp表中的所有员工平均工资进行统计，并打印出来。
DELIMITER //
CREATE PROCEDURE pro_test1()
BEGIN
SELECT AVG(sal)FROM emp;  -- 程序主体
END //
DELIMITER ;

CALL pro_test1();

2）编写一个存储过程，要求输入部门编号，对emp表中每个部门的员工人数进行统计，并打印出来。
DELIMITER //
CREATE PROCEDURE pro_test2(IN p_deptno INT)
BEGIN
SELECT deptno,COUNT(empno) FROM emp
WHERE deptno=p_deptno;
END //
DELIMITER ;

CALL pro_test2(30);

3）编写一个存储过程，要求输入一个员工的编号，
过程能把该员工的所属部门名称和他的直接领导manager的姓名打印出来。
DELIMITER //
CREATE PROCEDURE pro_test3(IN p_empno INT)
BEGIN
SELECT w.ename,m.ename,d.dname
FROM emp w,emp m, dept d
WHERE w.mgr=m.empno
AND w.deptno=d.deptno
AND w.empno=p_empno;

END //
DELIMITER ;

CALL pro_test3(7369);

-- 如果要展示出没有员工的领导，则需要用到外链接
DELIMITER //
CREATE PROCEDURE pro_test4(IN p_empno INT)
BEGIN
SELECT a.worker,a.manager,d.dname
FROM dept d,
(SELECT w.empno,w.deptno,w.ename AS worker,m.ename AS manager
FROM emp w LEFT JOIN emp m
ON w.mgr=m.empno) a
WHERE d.deptno=a.deptno
AND a.empno=p_empno;

END //
DELIMITER ;

CALL pro_test4(7839);

4）删除创建的所有存储过程
DROP PROCEDURE pro_test1;
DROP PROCEDURE pro_test2;
DROP PROCEDURE pro_test3;
DROP PROCEDURE pro_test4;


【补充】
（补充）INOUT用法简介
---------------------------------------------
-- 既可输入也可输出(不推荐)
-- 复制一条雇员记录信息，并返回新的empno
DELIMITER //
CREATE PROCEDURE proc_NewempNumber(INOUT empNumber INT)
BEGIN
SET @newEmployeeNumber =  empNumber + 100000;
INSERT INTO emp 
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
SELECT @newEmployeeNumber , 
    ename, 
    job, 
    mgr, 
    hiredate, 
    sal, 
    comm, 
    deptno
FROM emp WHERE empno = empNumber;
SET empNumber = @newEmployeeNumber; -- 把新的雇员编号赋值给输出参数empNumber
END //
DELIMITER ;

SET @empno = 7788; -- 定义变量empno并设置要复制的employee记录编号
CALL proc_NewempNumber(@empno); -- 调用存储过程，传入上一步定义的变量
-- 存储过程执行后，应该会将新复制的employee记录编号更新到这个empno变量
SELECT @empno; -- 输出empno变量中保存的新记录编号
 
SELECT * FROM  emp  WHERE empno = @empno;
-----------------------------------------------------------------------
第九章：存储过程的控制结构
-----------------------------------------------------------------------
SET autocommit=0;  -- 关闭自动提交事务
1：IF 条件控制
/*
IF 条件判断 THEN 执行语句1;  -- THEN后面可以加多个执行语句，每个用分号隔开
ELSEIF 条件判断 THEN 执行语句2;
... 
条件判断 THEN 执行语句N;
ELSE 执行语句N+1;
END IF;
*/
-- 输入一个部门编号，计算出该部门的平均工资
-- 如果平均工资低于1800，则给该部门员工涨薪500
-- SELECT...INTO... 给参数或者变量赋值
-- DECLARE 变量名称 数据类型[DEFAULT 默认值]; 
-- 用DECLARE关键字声明一个变量，该变量只能在存储过程内部使用，称为存储过程变量
DELIMITER //
CREATE PROCEDURE pro_addsal2(IN p_deptno INT)
BEGIN
DECLARE avgsal FLOAT(7,2);  -- 声明一个存储过程变量，用于存储平均工资
SELECT AVG(sal) INTO avgsal FROM emp WHERE deptno=p_deptno;  -- 将AVG(SAL)的值传给变量avgsal

IF avgsal < 1800  THEN 
UPDATE emp SET sal=sal+500 WHERE deptno=p_deptno ;
END IF;

SELECT AVG(sal) FROM emp WHERE deptno=p_deptno;
END //
DELIMITER ;

CALL pro_addsal2(30);
ROLLBACK;
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno

-- 输入一个部门编号，计算出该部门的平均工资
-- 如果平均工资低于1800，则给该部门员工涨薪500
-- 如果平均工资在1800~2800之间，则给该部门涨薪100
-- 如果平均工资大于2800，则提示“工资太高啦！”

DELIMITER //
CREATE PROCEDURE pro_addsal3(IN p_deptno INT)
BEGIN
DECLARE avgsal FLOAT(7,2);  -- 声明一个存储过程变量，用于存储平均工资
SELECT AVG(sal) INTO avgsal FROM emp WHERE deptno=p_deptno;  -- 将AVG(SAL)的值传给变量avgsal

IF avgsal < 1800  THEN 
UPDATE emp SET sal=sal+500 WHERE deptno=p_deptno ;

ELSEIF avgsal <2800 THEN
UPDATE emp SET sal=sal+100 WHERE deptno=p_deptno ;

ELSE SELECT '工资太高啦！';
END IF;

END //
DELIMITER ;

CALL pro_addsal3(10);
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

2. WHILE 循环语句
/*
WHILE 进入循环体的条件 DO
循环体;
END WHILE;
*/
-- 创建一张reg表，用来存储用户的注册信息
-- 包含2个列，uname 存储用户名，作为主键，upass存储密码，不能为空
CREATE TABLE reg 
(
uname VARCHAR(20) PRIMARY KEY,
upass VARCHAR(20) NOT NULL
);

SELECT * FROM reg;
-- 要求向reg表中存入100条数据
uname     upass
test1     pwd1
test2     pwd2
... 
test100   pwd100
-----------------------------------
DELIMITER //
CREATE PROCEDURE pro_reg(IN p_name VARCHAR(20),IN p_pwd VARCHAR(20),IN n INT)
BEGIN
DECLARE varA INT DEFAULT 1; -- 声明一个存储过程变量，初始值为1

WHILE varA<=n DO
INSERT INTO reg 
VALUES(CONCAT(p_name,varA),CONCAT(p_pwd,varA));
SET varA=varA+1;  -- 使用SET手动给变量赋值
END WHILE;

END //
DELIMITER ;


CALL pro_reg('test','pwd',100);  -- 插入100条指定数据
SELECT * FROM reg;
-- 删除存储过程
DROP PROCEDURE pro_reg
-------------------------------------------------------
控制结构 【练习题】
练习1：
输入一个部门编号，对该部门员工的薪资进行统计，要求:
用部门最高薪水－最低薪水的之差进行判断，
如果差距大于等于2000,输出标记‘H’
如果差距小于2000，大于等于1000，输出标记‘M’，
如果差距小于1000，输出标记‘L’
-- 提示：要求用IF实现


练习2：
输入一个员工编号，并对员工的工资进行判断,要求：
如果员工的工资等级为5，则输出wealthy
如果工资等级为4，输出rich
如果工资等级为3，输出average
如果工资等级为2，输出poor
如果工资等级为1，输出misery
-- 提示：用CASE实现
/*
CASE 表达式 
WHEN  value1 THEN 执行语句1;
WHEN  value2 THEN 执行语句2;
... 
WHEN  valueN THEN 执行语句N;
ELSE 执行语句N+1;
END CASE;
*/

练习3
自定义一张表test1，字段（id(主键)，NAME（不能重复），phno）用循环向这张表中插入100行记录。
-- 提示：用while实现