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

  1 USE school;
  2 SELECT * FROM sc;
  3 SELECT * FROM course;
  4 SELECT * FROM student;
  5 SELECT * FROM teacher;
  6 -- 创建所有学语文学生的视图
  7 /*
  8 
  9 视图的好处：安全、简化操作；
 10 视图的来源：一张或多张基表通过查询得到的新表，也做虚表。
 11 视图可以做的操作:视图的操作和表的操作一样，可以做增删改查。
 12 视图的注意事项：视图不能更改由基表通过聚合函数得到的表,对视图的数据所做的操作会影响到基表(数据的同步)。
 13 */
 14 CREATE VIEW v1
 15 AS
 16 SELECT * FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno=(SELECT cno FROM course WHERE cname=‘语文‘));
 17 -- 对视图中的数据进行处理
 18 UPDATE  v1 SET sname=‘王华‘ WHERE sno=1;
 19 SELECT * FROM v1
 20 DELETE FROM v1 WHERE sno=1
 21 INSERT INTO v1 VALUES(1,‘王华‘,18,‘男‘)
 22 SELECT ssex,COUNT(ssex) FROM v1 GROUP BY ssex
 23 UPDATE v1 SET ssex=‘女‘ WHERE sno=1;
 24 SELECT * FROM v1 ORDER BY sno ASC
 25 SELECT * FROM v1 WHERE ssex=‘女‘ ORDER BY sage DESC,sno
 26 SELECT ssex,COUNT(ssex) AS num FROM v1 GROUP BY ssex ORDER BY num DESC
 27 
 28 DROP VIEW v1 --删除视图
 29 DESC v1--查看视图结构
 30 SHOW CREATE VIEW v1
 31 
 32 
 33 
 34 INSERT INTO student SELECT * FROM student;
 35 SELECT DISTINCT * FROM student;
 36 
 37 /*
 38 什么是索引:是一种有效组合数据的方法。
 39 索引的分类:普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。
 40 索引的好处:可以提高查找速度，缩短查询时间。
 41 索引的缺点：太多的索引会占用磁盘空间，而且索引的创建和维护会占用时间。因此，在创建索引时要权衡利弊。
 42 适合创建索引的情况：
 43 .经常被查询的字段，即在WHERE子句中出现的字段。
 44 .在分组的字段，即在GROUP BY子句中出现的字段。
 45 .存在依赖关系的子表和父表之间的联合查询，即主键或外键字段。
 46 .设置唯一完整性约束的字段。
 47 
 48 */
 49 -- 为表中字段添加索引
 50 -- 1.创建普通索引：5
 51 
 52 -- 在创建表的时候创建索引：
 53 -- 直接在表的创建语句中创建索引。
 54 -- 为已经存在的表创建索引有两种方式：
 55 -- 第一种
 56 ALTER TABLE student ADD INDEX ix(sno)
 57 -- 第二种                       
 58 CREATE INDEX ix ON student(sno)
 59 -- 删除索引
 60 DROP INDEX ix ON student
 61 -- 检验索引是否被使用
 62 EXPLAIN SELECT * FROM student WHERE sno=1
 63 -- 显示表结构
 64 SHOW CREATE TABLE student t
 65 -- 删除表中重复的数据
 66 -- 1.用distinct关键字筛选目标数据(不重复的数据),然后创建一个临时表来保留目标数据
 67 CREATE TABLE tm SELECT DISTINCT * FROM student;
 68 -- 2.删除重复的表
 69 DROP TABLE student;
 70 -- 3.把临时表的名字改为原表的名字
 71 ALTER TABLE tm RENAME TO student;
 72 
 73 
 74 
 75 
 76 -- 存储过程
 77 -- 好处：减少网络流量
 78 DROP PROCEDURE IF EXISTS fn;
 79 DELIMITER $$
 80 CREATE PROCEDURE fn ()
 81 BEGIN
 82    DECLARE i INT;
 83    DECLARE sun INT;
 84    SET i=0;
 85    SET sun=0;
 86    WHILE i<101 DO
 87       SET sun=sun+i;
 88       SET i=i+1;
 89    END WHILE;
 90   SELECT sun;
 91 END$$
 92 DELIMITER ;
 93 CALL fn();
 94 
 95 
 96 DELIMITER $$
 97 CREATE PROCEDURE fn2()
 98 BEGIN
 99   DECLARE i INT;
100   SET i=0;
101   IF i=0 THEN
102   SELECT ‘你欠费了‘;
103   ELSE
104   SELECT ‘你现在资金充裕‘;
105   END IF;
106 END$$
107 DELIMITER ;
108 CALL fn2();
109 
110 DELIMITER $$
111 CREATE PROCEDURE fg(IN id INT)
112 BEGIN
113 SELECT * FROM student WHERE sno=id;
114 END $$
115 DELIMITER ;
116 
117 CALL fg(1);
118 
119 DELIMITER $$
120 CREATE PROCEDURE fgout(OUT ascore INT)
121 BEGIN
122 SELECT AVG(score) INTO ascore FROM sc;
123 END$$
124 DELIMITER ;
125 
126 CALL fgout(@avg);
127 SELECT @avg;
128 SET @sno=‘sno‘;
129 SELECT @sno
130 
131 -- 函数 (不能返回表，要想返回表，可以用存储过程)
132 -- 创建一个函数，用来根据性别查询人数
133 DELIMITER $$
134 CREATE FUNCTION fn(m VARCHAR(20))
135 RETURNS INT 
136 BEGIN
137 SELECT COUNT(*) INTO @num FROM student WHERE ssex=m;
138 RETURN @num;
139 END$$
140 DELIMITER ;
141 -- 删除函数名为fn的函数
142 DROP FUNCTION fn;
143 -- 调用函数
144 SELECT fn(‘女‘);
145 
146 -- 创建一个函数，用来统计指定学生的平均成绩
147 
148 DELIMITER $$
149 CREATE FUNCTION fn1(sn INT)
150 RETURNS DOUBLE
151 BEGIN
152   SELECT AVG(score) INTO @avg FROM sc WHERE sno=sn;
153   RETURN @avg;
154 END$$
155 DELIMITER ;
156 
157 -- 触发器
158 -- 为测试触发器创建两个表 bank 、info
159 CREATE TABLE bank(
160 id VARCHAR(10) PRIMARY KEY,
161 NAME VARCHAR(10),
162 money INT
163 );
164 
165 CREATE TABLE info(
166 cardid VARCHAR(10),
167 operatDate DATETIME,
168 operattype VARCHAR(6),
169 operatMoey INT
170 );
171 -- 插入测试数据
172 INSERT INTO bank VALUES(‘1001‘,‘王华‘,1000);
173 INSERT INTO bank VALUES(1002,‘张三‘,300);
174 -- 创建一个触发器
175 DELIMITER $$
176 CREATE TRIGGER triBank
177 AFTER UPDATE ON bank
178 FOR EACH ROW
179 BEGIN
180   IF(old.money-new.money>0) THEN
181   
182     INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),‘支取‘,old.money-new.money);
183     
184   ELSE
185   
186     INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),‘存款‘,new.money-old.money);
187     
188     END IF;
189     
190 END$$
191 DELIMITER ;
192 
193 -- 触发触发器
194 UPDATE bank SET money=money+200 WHERE id=‘1002‘;
195 -- 利用左外连接查询所需所有信息
196 SELECT bank.*,info.* FROM bank LEFT JOIN info ON bank.id=info.cardid;
197 
198 DELIMITER $$
199   CREATE TRIGGER tribank1
200   BEFORE UPDATE ON bank
201   FOR EACH ROW
202   BEGIN
203       IF(old.money-new.money>500) THEN
204         SET new.money=old.money-500;
205         ELSE
206         INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),‘存款‘,new.money-old.money);
207         END IF;
208         
209   END$$
210 DELIMITER ;
211 
212 DROP TRIGGER tribank1;
213 UPDATE bank SET money=money-600 WHERE id=‘1001‘;
214 
215 -- case when 
216 /*
217 
218 Case具有两种格式。简单Case函数和Case搜索函数。
219 --简单Case函数
220 CASE sex
221 WHEN ‘1‘ THEN ‘男‘
222 WHEN ‘2‘ THEN ‘女‘
223 ELSE ‘其他‘ END
224 --Case搜索函数
225 CASE WHEN sex = ‘1‘ THEN ‘男‘
226 WHEN sex = ‘2‘ THEN ‘女‘
227 ELSE ‘其他‘ END
228 
229 这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。 
230 还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 
231 --比如说，下面这段SQL，你永远无法得到“第二类”这个结果
232 CASE WHEN col_1 IN ( ‘a‘, ‘b‘) THEN ‘第一类‘
233 WHEN col_1 IN (‘a‘)       THEN ‘第二类‘
234 ELSE‘其他‘ END
235 */
236 SELECT sno AS ‘学号‘,cno,
237 CASE 
238   WHEN score>80 THEN ‘优秀‘
239   WHEN score <60 THEN ‘不及格‘
240   ELSE ‘良好‘ END AS ‘成绩评级‘
241   FROM sc;
242   
243   SELECT sno AS ‘学号‘,sname AS ‘姓名‘,ssex AS ‘性别‘,
244   CASE 
245    WHEN sage>18 THEN ‘青年‘
246    WHEN sage<1 THEN ‘儿童‘ 
247    ELSE ‘少年‘ END AS ‘年龄划分‘
248    FROM student;
249     
250    -- case when在聚合函数中应用
251    创建一个测试表
252    CREATE TABLE studentInfo(
253     id INT PRIMARY KEY,
254      sex VARCHAR(10),
255      province VARCHAR(10)  
256    );
257   
258   -- 插入测试数据
259   -- 统计各个省份的男女比
260  INSERT INTO studentInfo VALUES(1,‘男‘,‘江西省‘);
261  INSERT INTO studentInfo VALUES(2,‘男‘,‘广东省‘);
262  INSERT INTO studentInfo VALUES(3,‘男‘,‘浙江省‘);
263  INSERT INTO studentInfo VALUES(4,‘女‘,‘江西省‘);
264  INSERT INTO studentInfo VALUES(5,‘男‘,‘浙江省‘);
265  INSERT INTO studentInfo VALUES(6,‘女‘,‘浙江省‘);
266  -- 查询studentinfo中所有的信息
267  SELECT * FROM studentinfo;
268  
269  -- 统计浙江省的男女比
270  SELECT sex,COUNT(‘浙江省‘) AS ‘浙江省‘ FROM studentinfo GROUP BY sex;
271  
272  -- 统计总的男女比
273  SELECT sex,COUNT(*) FROM studentinfo GROUP BY sex;
274  
275  -- 统计各个省的男女比
276   SELECT sex,COUNT((CASE province WHEN ‘浙江省‘ THEN ‘浙江省‘ END )) AS ‘浙江省‘,
277  COUNT((CASE province WHEN ‘江西省‘ THEN ‘江西省‘ END )) AS ‘江西省‘,
278  COUNT((CASE province WHEN ‘广东省‘ THEN ‘广东省‘ END )) AS ‘广东省‘
279  FROM studentinfo GROUP BY sex;
280  
281  -- 创建一个测试表
282  CREATE TABLE wwwpopution(
283       country VARCHAR(10),
284       sex VARCHAR(10),
285       population INT 
286  );
287  
288  -- 插入测试数据
289  -- 按照国家和性别进行分组
290  INSERT INTO wwwpopution(country,sex,population) VALUES(‘中国‘,‘1‘,340), 
291   (‘中国‘,‘2‘,260), (‘美国‘,‘1‘,45), 
292  (‘美国‘,‘2‘,55), (‘加拿大‘,‘1‘,51),
293   (‘加拿大‘,‘2‘,49),(‘英国‘,‘1‘,40), 
294   (‘英国‘,‘2‘,60);
295   
296   SELECT * FROM wwwpopution;
297  -- 利用case when
298  SELECT country AS ‘国家‘,SUM(CASE WHEN sex = ‘1‘ THEN
299 population ELSE 0 END) AS ‘男‘,SUM(CASE WHEN sex = ‘2‘ THEN
300 population ELSE 0 END)  AS ‘女‘ FROM wwwpopution GROUP BY country
301 SELECT country,SUM(CASE popution WHEN ‘1‘ THEN ‘1‘ END) FROM wwwpopution GROUP BY country
302 
303 -- 事务
304 -- 创建一个测试表
305 CREATE TABLE depart(
306  did CHAR(1) PRIMARY KEY NOT NULL,
307  dname VARCHAR(20) NULL,
308  dmaster CHAR(3) NULL,
309  droom CHAR(10) NULL
310 );
311 -- 在存储过程中事务的回滚Demo
312 -- 无参存储过程
313 DELIMITER $$
314 DROP PROCEDURE IF EXISTS  test_sp1 
315  CREATE PROCEDURE test_sp1( )  
316     BEGIN  
317     DECLARE t_error INTEGER DEFAULT 0;  -- 设置一个事务标识位
318     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 声明一个sql异常,当触发这个异常时，把标识位置为1
319   
320         START TRANSACTION;  
321             INSERT INTO depart VALUES(‘5‘, ‘科研部‘,‘002‘,‘4201‘);     
322             INSERT INTO depart VALUES(‘6‘, ‘宣传部‘,‘004‘,‘4202‘);
323             INSERT INTO depart VALUES(‘7‘, ‘工会‘,‘006‘,‘4203‘);
324  
325         IF t_error = 1 THEN  -- 标识位为1,事务回滚
326             ROLLBACK;  
327         ELSE    -- 为0提交事务
328             COMMIT;  
329         END IF;  
330     SELECT test_sp1; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
331  END$$
332 DELIMITER ;
333 
334 CALL test_sp1();
335 
336 -- 有参数的存储过程
337 
338 DELIMITER $$
339 #DROP PROCEDURE IF EXISTS  test_sp2
340  CREATE PROCEDURE test_sp2(IN id CHAR(1),IN dn VARCHAR(20),IN dm CHAR(3),IN dr CHAR(10))  
341     BEGIN  
342     DECLARE t_error INTEGER DEFAULT 0;  -- 设置一个事务标识位
343     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 声明一个sql异常,当触发这个异常时，把标识位置为1
344   
345         START TRANSACTION;  
346             INSERT INTO depart VALUES(id,dn,dm,dr);
347         IF t_error = 1 THEN  -- 标识位为1,事务回滚
348             ROLLBACK;  
349         ELSE    -- 为0提交事务
350             COMMIT;  
351         END IF;  
352     SELECT t_error; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
353  END$$
354 DELIMITER ;
355 
356 CALL test_sp2(‘5‘,‘科研部‘,‘002‘,‘4201‘);
357 CALL test_sp2(‘1‘,‘财务部‘,‘003‘,‘2201‘);
358 CALL test_sp2(‘2‘,‘人事处‘,‘005‘,‘2209‘);
359 CALL test_sp2(‘3‘,‘市场部‘,‘009‘,‘3201‘);
360 CALL test_sp2(‘4‘,‘开发部‘,‘001‘,‘3206‘);
361 
362 -- 带输出参数的存储过程
363 DELIMITER $$
364 #DROP PROCEDURE IF EXISTS  test_sp3
365  CREATE PROCEDURE test_sp3(IN id CHAR(1),IN dn VARCHAR(20),IN dm CHAR(3),IN dr CHAR(10),OUT err INT)  
366     BEGIN  
367     DECLARE t_error INTEGER DEFAULT 0;  -- 设置一个事务标识位
368     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 声明一个sql异常,当触发这个异常时，把标识位置为1
369   
370         START TRANSACTION;  
371             INSERT INTO depart VALUES(id,dn,dm,dr);
372         IF t_error = 1 THEN  -- 标识位为1,事务回滚
373             ROLLBACK;  
374         ELSE    -- 为0提交事务
375             COMMIT;  
376         END IF;  
377     SET err=t_error; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
378  END$$
379 DELIMITER ;
380 
381 CALL test_sp3(‘4‘,‘开发部‘,‘001‘,‘3206‘,@m);
382 SELECT @m;