mysql的视图、索引、触发器、存储过程

USE school;
SELECT * FROM sc;
SELECT * FROM course;
SELECT * FROM student;
SELECT * FROM teacher;
-- 创建所有学语文学生的视图
/*

视图的好处：安全、简化操作；
视图的来源：一张或多张基表通过查询得到的新表，也做虚表。
视图可以做的操作:视图的操作和表的操作一样，可以做增删改查。
视图的注意事项：视图不能更改由基表通过聚合函数得到的表,对视图的数据所做的操作会影响到基表(数据的同步)。
*/
CREATE VIEW v1
AS
SELECT * FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno=(SELECT cno FROM course WHERE cname='语文'));
-- 对视图中的数据进行处理
UPDATE  v1 SET sname='王华' WHERE sno=1;
SELECT * FROM v1
DELETE FROM v1 WHERE sno=1
INSERT INTO v1 VALUES(1,'王华',18,'男')
SELECT ssex,COUNT(ssex) FROM v1 GROUP BY ssex
UPDATE v1 SET ssex='女' WHERE sno=1;
SELECT * FROM v1 ORDER BY sno ASC
SELECT * FROM v1 WHERE ssex='女' ORDER BY sage DESC,sno
SELECT ssex,COUNT(ssex) AS num FROM v1 GROUP BY ssex ORDER BY num DESC

DROP VIEW v1 --删除视图
DESC v1--查看视图结构
SHOW CREATE VIEW v1



INSERT INTO student SELECT * FROM student;
SELECT DISTINCT * FROM student;

/*
什么是索引:是一种有效组合数据的方法。
索引的分类:普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。
索引的好处:可以提高查找速度，缩短查询时间。
索引的缺点：太多的索引会占用磁盘空间，而且索引的创建和维护会占用时间。因此，在创建索引时要权衡利弊。
适合创建索引的情况：
.经常被查询的字段，即在WHERE子句中出现的字段。
.在分组的字段，即在GROUP BY子句中出现的字段。
.存在依赖关系的子表和父表之间的联合查询，即主键或外键字段。
.设置唯一完整性约束的字段。

*/
-- 为表中字段添加索引
-- 1.创建普通索引：5

-- 在创建表的时候创建索引：
-- 直接在表的创建语句中创建索引。
-- 为已经存在的表创建索引有两种方式：
-- 第一种
ALTER TABLE student ADD INDEX ix(sno)
-- 第二种                       
CREATE INDEX ix ON student(sno)
-- 删除索引
DROP INDEX ix ON student
-- 检验索引是否被使用
EXPLAIN SELECT * FROM student WHERE sno=1
-- 显示表结构
SHOW CREATE TABLE student t
-- 删除表中重复的数据
-- 1.用distinct关键字筛选目标数据(不重复的数据),然后创建一个临时表来保留目标数据
CREATE TABLE tm SELECT DISTINCT * FROM student;
-- 2.删除重复的表
DROP TABLE student;
-- 3.把临时表的名字改为原表的名字
ALTER TABLE tm RENAME TO student;




-- 存储过程
-- 好处：减少网络流量
DROP PROCEDURE IF EXISTS fn;
DELIMITER $$
CREATE PROCEDURE fn ()
BEGIN
   DECLARE i INT;
   DECLARE sun INT;
   SET i=0;
   SET sun=0;
   WHILE i<101 DO
      SET sun=sun+i;
      SET i=i+1;
   END WHILE;
  SELECT sun;
END$$
DELIMITER ;
CALL fn();


DELIMITER $$
CREATE PROCEDURE fn2()
BEGIN
  DECLARE i INT;
  SET i=0;
  IF i=0 THEN
  SELECT '你欠费了';
  ELSE
  SELECT '你现在资金充裕';
  END IF;
END$$
DELIMITER ;
CALL fn2();

DELIMITER $$
CREATE PROCEDURE fg(IN id INT)
BEGIN
SELECT * FROM student WHERE sno=id;
END $$
DELIMITER ;

CALL fg(1);

DELIMITER $$
CREATE PROCEDURE fgout(OUT ascore INT)
BEGIN
SELECT AVG(score) INTO ascore FROM sc;
END$$
DELIMITER ;

CALL fgout(@avg);
SELECT @avg;
SET @sno='sno';
SELECT @sno

-- 函数 (不能返回表，要想返回表，可以用存储过程)
-- 创建一个函数，用来根据性别查询人数
DELIMITER $$
CREATE FUNCTION fn(m VARCHAR(20))
RETURNS INT 
BEGIN
SELECT COUNT(*) INTO @num FROM student WHERE ssex=m;
RETURN @num;
END$$
DELIMITER ;
-- 删除函数名为fn的函数
DROP FUNCTION fn;
-- 调用函数
SELECT fn('女');

-- 创建一个函数，用来统计指定学生的平均成绩

DELIMITER $$
CREATE FUNCTION fn1(sn INT)
RETURNS DOUBLE
BEGIN
  SELECT AVG(score) INTO @avg FROM sc WHERE sno=sn;
  RETURN @avg;
END$$
DELIMITER ;

-- 触发器
-- 为测试触发器创建两个表 bank 、info
CREATE TABLE bank(
id VARCHAR(10) PRIMARY KEY,
NAME VARCHAR(10),
money INT
);

CREATE TABLE info(
cardid VARCHAR(10),
operatDate DATETIME,
operattype VARCHAR(6),
operatMoey INT
);
-- 插入测试数据
INSERT INTO bank VALUES('1001','王华',1000);
INSERT INTO bank VALUES(1002,'张三',300);
-- 创建一个触发器
DELIMITER $$
CREATE TRIGGER triBank
AFTER UPDATE ON bank
FOR EACH ROW
BEGIN
  IF(old.money-new.money>0) THEN
  
    INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),'支取',old.money-new.money);
    
  ELSE
  
    INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),'存款',new.money-old.money);
    
    END IF;
    
END$$
DELIMITER ;

-- 触发触发器
UPDATE bank SET money=money+200 WHERE id='1002';
-- 利用左外连接查询所需所有信息
SELECT bank.*,info.* FROM bank LEFT JOIN info ON bank.id=info.cardid;

DELIMITER $$
  CREATE TRIGGER tribank1
  BEFORE UPDATE ON bank
  FOR EACH ROW
  BEGIN
      IF(old.money-new.money>500) THEN
        SET new.money=old.money-500;
        ELSE
        INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),'存款',new.money-old.money);
        END IF;
        
  END$$
DELIMITER ;

DROP TRIGGER tribank1;
UPDATE bank SET money=money-600 WHERE id='1001';

-- case when 
/*

Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。 
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 
--比如说，下面这段SQL，你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
WHEN col_1 IN ('a')       THEN '第二类'
ELSE'其他' END
*/
SELECT sno AS '学号',cno,
CASE 
  WHEN score>80 THEN '优秀'
  WHEN score <60 THEN '不及格'
  ELSE '良好' END AS '成绩评级'
  FROM sc;
  
  SELECT sno AS '学号',sname AS '姓名',ssex AS '性别',
  CASE 
   WHEN sage>18 THEN '青年'
   WHEN sage<1 THEN '儿童' 
   ELSE '少年' END AS '年龄划分'
   FROM student;
    
   -- case when在聚合函数中应用
   创建一个测试表
   CREATE TABLE studentInfo(
    id INT PRIMARY KEY,
     sex VARCHAR(10),
     province VARCHAR(10)  
   );
  
  -- 插入测试数据
  -- 统计各个省份的男女比
 INSERT INTO studentInfo VALUES(1,'男','江西省');
 INSERT INTO studentInfo VALUES(2,'男','广东省');
 INSERT INTO studentInfo VALUES(3,'男','浙江省');
 INSERT INTO studentInfo VALUES(4,'女','江西省');
 INSERT INTO studentInfo VALUES(5,'男','浙江省');
 INSERT INTO studentInfo VALUES(6,'女','浙江省');
 -- 查询studentinfo中所有的信息
 SELECT * FROM studentinfo;
 
 -- 统计浙江省的男女比
 SELECT sex,COUNT('浙江省') AS '浙江省' FROM studentinfo GROUP BY sex;
 
 -- 统计总的男女比
 SELECT sex,COUNT(*) FROM studentinfo GROUP BY sex;
 
 -- 统计各个省的男女比
  SELECT sex,COUNT((CASE province WHEN '浙江省' THEN '浙江省' END )) AS '浙江省',
 COUNT((CASE province WHEN '江西省' THEN '江西省' END )) AS '江西省',
 COUNT((CASE province WHEN '广东省' THEN '广东省' END )) AS '广东省'
 FROM studentinfo GROUP BY sex;
 
 -- 创建一个测试表
 CREATE TABLE wwwpopution(
      country VARCHAR(10),
      sex VARCHAR(10),
      population INT 
 );
 
 -- 插入测试数据
 -- 按照国家和性别进行分组
 INSERT INTO wwwpopution(country,sex,population) VALUES('中国','1',340), 
  ('中国','2',260), ('美国','1',45), 
 ('美国','2',55), ('加拿大','1',51),
  ('加拿大','2',49),('英国','1',40), 
  ('英国','2',60);
  
  SELECT * FROM wwwpopution;
 -- 利用case when
 SELECT country AS '国家',SUM(CASE WHEN sex = '1' THEN
population ELSE 0 END) AS '男',SUM(CASE WHEN sex = '2' THEN
population ELSE 0 END)  AS '女' FROM wwwpopution GROUP BY country
SELECT country,SUM(CASE popution WHEN '1' THEN '1' END) FROM wwwpopution GROUP BY country

-- 事务
-- 创建一个测试表
CREATE TABLE depart(
 did CHAR(1) PRIMARY KEY NOT NULL,
 dname VARCHAR(20) NULL,
 dmaster CHAR(3) NULL,
 droom CHAR(10) NULL
);
-- 在存储过程中事务的回滚Demo
-- 无参存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS  test_sp1 
 CREATE PROCEDURE test_sp1( )  
    BEGIN  
    DECLARE t_error INTEGER DEFAULT 0;  -- 设置一个事务标识位
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 声明一个sql异常,当触发这个异常时，把标识位置为1
  
        START TRANSACTION;  
            INSERT INTO depart VALUES('5', '科研部','002','4201');     
            INSERT INTO depart VALUES('6', '宣传部','004','4202');
            INSERT INTO depart VALUES('7', '工会','006','4203');
 
        IF t_error = 1 THEN  -- 标识位为1,事务回滚
            ROLLBACK;  
        ELSE    -- 为0提交事务
            COMMIT;  
        END IF;  
    SELECT test_sp1; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
 END$$
DELIMITER ;

CALL test_sp1();

-- 有参数的存储过程

DELIMITER $$
#DROP PROCEDURE IF EXISTS  test_sp2
 CREATE PROCEDURE test_sp2(IN id CHAR(1),IN dn VARCHAR(20),IN dm CHAR(3),IN dr CHAR(10))  
    BEGIN  
    DECLARE t_error INTEGER DEFAULT 0;  -- 设置一个事务标识位
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 声明一个sql异常,当触发这个异常时，把标识位置为1
  
        START TRANSACTION;  
            INSERT INTO depart VALUES(id,dn,dm,dr);
        IF t_error = 1 THEN  -- 标识位为1,事务回滚
            ROLLBACK;  
        ELSE    -- 为0提交事务
            COMMIT;  
        END IF;  
    SELECT t_error; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
 END$$
DELIMITER ;

CALL test_sp2('5','科研部','002','4201');
CALL test_sp2('1','财务部','003','2201');
CALL test_sp2('2','人事处','005','2209');
CALL test_sp2('3','市场部','009','3201');
CALL test_sp2('4','开发部','001','3206');

-- 带输出参数的存储过程
DELIMITER $$
#DROP PROCEDURE IF EXISTS  test_sp3
 CREATE PROCEDURE test_sp3(IN id CHAR(1),IN dn VARCHAR(20),IN dm CHAR(3),IN dr CHAR(10),OUT err INT)  
    BEGIN  
    DECLARE t_error INTEGER DEFAULT 0;  -- 设置一个事务标识位
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 声明一个sql异常,当触发这个异常时，把标识位置为1
  
        START TRANSACTION;  
            INSERT INTO depart VALUES(id,dn,dm,dr);
        IF t_error = 1 THEN  -- 标识位为1,事务回滚
            ROLLBACK;  
        ELSE    -- 为0提交事务
            COMMIT;  
        END IF;  
    SET err=t_error; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
 END$$
DELIMITER ;

CALL test_sp3('4','开发部','001','3206',@m);
SELECT @m;