MySQL基础(二)

一、视图

视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。

创建视图

1 --格式:CREATE VIEW 视图名称 AS  SQL语句
2 
3 CREATE VIEW person_info_view AS SELECT * FROM person_info;
4 CREATE VIEW person_info_view AS SELECT person_id,name,country FROM person_info;
5 CREATE VIEW person_info_view (Vperso_id,Vname,Vcountry,Vsalary) AS SELECT person_id,name,country,salary FROM person_info;

删除视图

1 --格式:DROP VIEW 视图名称
2 
3 DROP VIEW person_info_view;

修改视图

1 -- 格式:ALTER VIEW 视图名称 AS SQL语句
2 
3 ALTER VIEW person_info_view AS SELECT person_id,name,country FROM person_info;

查看视图

1 -- 格式:SHOW CREATE VIEW 视图名称
2 
3 SHOW CREATE VIEW person_info_view;

使用视图

使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。

1 SELECT * FROM person_info_view;

二、存储过程

存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。

创建存储过程

 1 -- 创建存储过程
 2 delimiter $$
 3 CREATE PROCEDURE p1()
 4 BEGIN
 5     SELECT * FROM person_info;
 6 END $$
 7 delimiter ; 
 8 
 9 -- 执行存储过程
10 call p1()
无参数的存储过程

存储过程的参数有三类,如下:

  • in:     仅用于传入参数用
  • out:   仅用于返回值用
  • inout:既可以传入又可以当作返回值
 1 -- 创建存储过程
 2 delimiter $$
 3 CREATE PROCEDURE p2(
 4 in i1 int,
 5 in i2 varchar(20),
 6 inout i3 int,
 7 out r1 int
 8 )
 9 BEGIN
10     DECLARE tmp1 int;
11     DECLARE tmp2 int DEFAULT 0;
12     SET tmp1 = 2;
13     SET i3 = i3 + 10;
14     SET r1 = i1 + i2 - tmp1 + tmp2;
15     SELECT * FROM person_info;
16 END $$
17 delimiter ; 
18 
19 
20 -- 执行存储过程
21 SET @c1 = 5;
22 SET @c2 = 10;
23 
24 call p2(1,2,@c1,@c2);  //执行存储过程返回查询结果
25 
26 SELECT @c1,@c2;        //执行SELECT返回类型为out和inout的参数值
带参数的存储过程
 1 -- 创建存储过程
 2 delimiter $$
 3 DROP PROCEDURE IF EXISTS p3;
 4 CREATE PROCEDURE p3(
 5 in nid INT
 6 )
 7 BEGIN
 8     SET @nid = nid;
 9     PREPARE prod FROM 'select * from person_info where person_id > ?';
10     EXECUTE prod USING @nid;
11     DEALLOCATE PREPARE prod;
12 
13 END $$
14 delimiter ; 
15 
16 -- 执行存储过程
17 call p3(1);
18 call p3(2);
19 call p3(3);
动态执行SQL语句
 1 -- 创建存储过程
 2 delimiter $$
 3 DROP PROCEDURE IF EXISTS p4;
 4 CREATE PROCEDURE p4(
 5 in id INT
 6 )
 7 BEGIN
 8     SET @id = id;
 9     IF id = 0 THEN
10         PREPARE prod FROM 'select * from person_info where person_id > ?';
11     ELSEIF id = 1 THEN
12         PREPARE prod FROM 'update person_info set salary = 1.12 where person_id > ?';
13     ELSE
14         PREPARE prod FROM 'delete from person_info where person_id > ?';
15     END IF;
16 
17 EXECUTE prod USING @id;
18 DEALLOCATE PREPARE prod;
19 
20 END $$
21 delimiter ; 
22 
23 -- 执行存储过程
24 call p4(0);
25 call p4(1);
26 call p4(2);
IF条件语句
 1 -- 创建存储过程
 2 delimiter $$
 3 DROP PROCEDURE IF EXISTS p5;
 4 CREATE PROCEDURE p5(
 5 in id INT
 6 )
 7 BEGIN
 8     WHILE id <= 5 DO
 9         SET @id = id;
10         PREPARE prod FROM 'select * from person_info where person_id > ?';
11         EXECUTE prod USING @id;
12         DEALLOCATE PREPARE prod;
13         SET id = id + 1;
14     END WHILE;
15 
16 END $$
17 delimiter ; 
18 
19 -- 执行存储过程
20 call p5(3);
While循环
 1 -- 创建存储过程
 2 delimiter $$
 3 DROP PROCEDURE IF EXISTS p6;
 4 CREATE PROCEDURE p6(
 5 in id INT
 6 )
 7 BEGIN
 8     REPEAT
 9         SET @id = id;
10         PREPARE prod FROM 'select * from person_info where person_id > ?';
11         EXECUTE prod USING @id;
12         DEALLOCATE PREPARE prod;
13         SET id = id + 1;
14         UNTIL id >5
15     END REPEAT;
16 
17 END $$
18 delimiter ; 
19 
20 -- 执行存储过程
21 call p6(3);
REPEAT循环
 1 -- 创建存储过程
 2 delimiter $$
 3 DROP PROCEDURE IF EXISTS p7 $$
 4 CREATE PROCEDURE p7(in id INT)
 5 BEGIN
 6     Loop_tab:LOOP
 7     SET id = id + 1;
 8         IF id < 3 THEN
 9              ITERATE Loop_tab;   -- 相当于其他语言当中的continue
10         END IF;
11         IF id >= 7 THEN
12             LEAVE Loop_tab;      -- 相当于其他语言当中的break            
13         END IF;
14     SET @id = id;
15     PREPARE prod FROM 'select * from person_info where person_id > ?';
16     EXECUTE prod USING @id;
17     DEALLOCATE PREPARE prod;
18     END LOOP Loop_tab;
19 
20 END $$
21 delimiter ; 
22 
23 -- 执行存储过程
24 call p7(4);
LOOP循环

删除存储过程

1 DROP PROCEDURE p1;

执行存储过程

 1 -- 无参数
 2 call proc_name()
 3 
 4 -- 有参数,全in
 5 call proc_name(1,2)
 6 
 7 -- 有参数,有in,out,inout
 8 set @t1=0;
 9 set @t2=3;
10 call proc_name(1,2,@t1,@t2)

pymysql执行存储过程

 1 import pymysql
 2 
 3 conn = pymysql.connect(host='192.168.182.130', port=3306, user='root', passwd='123456', db='test')
 4 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 5 # 执行存储过程,获取存储过程的结果集,将存储过程的返回值赋给了@_p2_0,@_p2_1,@_p2_2,@_p2_3
 6 cursor.callproc('p2', args=(1,22,3,4))
 7 # call p2(1,22,3,4)
 8 # set @_p2_0 = 1
 9 # set @_p2_1 = 22
10 # set @_p2_2 = 3
11 # set @_p2_3 = 4
12 
13 # 获取存储过程中的select语句的结果集
14 result1 = cursor.fetchall()
15 print(result1)
16 
17 # 获取执行完存储的参数返回值
18 cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
19 result2 = cursor.fetchall()
20 print(result2)
21 
22 conn.commit()
23 cursor.close()
24 conn.close()

三、触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。

创建触发器

 1 # 插入前:在往person_info表插入数据之前执行触发器中的sql语句
 2 CREATE TRIGGER tri_before_insert_person_info BEFORE INSERT ON person_info FOR EACH ROW
 3 BEGIN
 4         INSERT INTO person(name) VALUES('Jack');
 5     ...
 6 END
 7 
 8 # 插入后:在往person_info表插入数据之后执行触发器中的sql语句
 9 CREATE TRIGGER tri_after_insert_person_info AFTER INSERT ON person_info FOR EACH ROW
10 BEGIN
11     ...
12 END
13 
14 # 删除前:在删除person_info表中的数据之前执行触发器中的sql语句
15 CREATE TRIGGER tri_before_delete_person_info BEFORE DELETE ON person_info FOR EACH ROW
16 BEGIN
17     ...
18 END
19 
20 # 删除后:在删除person_info表中的数据之后执行触发器中的sql语句
21 CREATE TRIGGER tri_after_delete_person_info AFTER DELETE ON person_info FOR EACH ROW
22 BEGIN
23     ...
24 END
25 
26 # 更新前:在更新person_info表中的数据之前执行触发器中的sql语句
27 CREATE TRIGGER tri_before_update_person_info BEFORE UPDATE ON person_info FOR EACH ROW
28 BEGIN
29     ...
30 END
31 
32 # 更新后:在更新person_info表中的数据之后执行触发器中的sql语句
33 CREATE TRIGGER tri_after_update_person_info AFTER UPDATE ON person_info FOR EACH ROW
34 BEGIN
35     ...
36 END
基本语法
 1 -- 创建触发器
 2 delimiter $$
 3 DROP TRIGGER IF EXISTS befor_insert_person_info;
 4 CREATE TRIGGER befor_insert_person_info BEFORE INSERT ON person_info FOR EACH ROW
 5 
 6 BEGIN
 7 -- 根据person_info表新插入数据的name字段来判断
 8     IF NEW.name = 'Jack' THEN
 9         INSERT INTO person(name) VALUES(NEW.name);
10     END IF;
11 
12 END $$
13 
14 delimiter ;
15 
16 
17 -- 触发触发器执行
18 INSERT INTO person_info (person_id,name,country,salary) VALUES (11,'Jack','France',11.25);
插入前触发
 1 -- 创建触发器
 2 delimiter $$
 3 DROP TRIGGER IF EXISTS after_insert_person_info;
 4 CREATE TRIGGER after_insert_person_info AFTER INSERT ON person_info FOR EACH ROW
 5 
 6 BEGIN
 7 -- 根据person_info表新插入数据的name字段来判断
 8     IF NEW.name = 'Jack' THEN
 9         INSERT INTO person(name) VALUES(NEW.name);
10     END IF;
11 
12 END $$
13 
14 delimiter ;
15 
16 
17 -- 触发触发器执行
18 INSERT INTO person_info (person_id,name,country,salary) VALUES (13,'Jack','France',11.25);
插入后触发

NEW表示即将插入的数据行

 1 -- 创建触发器
 2 delimiter $$
 3 DROP TRIGGER IF EXISTS before_delete_person_info;
 4 CREATE TRIGGER before_delete_person_info BEFORE DELETE ON person_info FOR EACH ROW
 5 
 6 BEGIN
 7 
 8     IF OLD.name = 'Jack' THEN
 9         INSERT INTO person(name) VALUES(OLD.name);
10     END IF;
11 
12 END $$
13 
14 delimiter ;
15 
16 
17 -- 触发触发器执行
18 DELETE FROM person_info WHERE person_id = 1;
删除前触发
 1 -- 创建触发器
 2 delimiter $$
 3 DROP TRIGGER IF EXISTS after_delete_person_info;
 4 CREATE TRIGGER after_delete_person_info AFTER DELETE ON person_info FOR EACH ROW
 5 
 6 BEGIN
 7 
 8     IF OLD.name = 'Luo' THEN
 9         INSERT INTO person(name) VALUES(OLD.name);
10     END IF;
11 
12 END $$
13 
14 delimiter ;
15 
16 
17 -- 触发触发器执行
18 DELETE FROM person_info WHERE person_id = 5;
删除后触发

OLD表示即将删除的数据行

 1 -- 创建触发器
 2 delimiter $$
 3 DROP TRIGGER IF EXISTS before_update_person_info;
 4 CREATE TRIGGER before_update_person_info BEFORE UPDATE ON person_info FOR EACH ROW
 5 
 6 BEGIN
 7   -- 这里使用OLD就是判断person_info表中更新前的name,如果是NEW就是更新后的name
 8     IF OLD.name = 'Luo' THEN
 9         INSERT INTO person(name) VALUES(OLD.name);
10     END IF;
11 
12 END $$
13 
14 delimiter ;
15 
16 
17 -- 触发触发器执行
18 UPDATE person_info SET name = 'Jack' WHERE person_id = 6;
更新前触发
 1 -- 创建触发器
 2 delimiter $$
 3 DROP TRIGGER IF EXISTS after_update_person_info;
 4 CREATE TRIGGER after_update_person_info AFTER UPDATE ON person_info FOR EACH ROW
 5 
 6 BEGIN
 7   -- 这里使用NEW就是判断person_info表中更新后的name,如果是OLD就是更新前的name
 8     IF NEW.name = 'Cherry' THEN
 9         INSERT INTO person(name) VALUES(NEW.name);
10     END IF;
11 
12 END $$
13 
14 delimiter ;
15 
16 
17 -- 触发触发器执行
18 UPDATE person_info SET name = 'Cherry' WHERE person_id = 6;
更新后触发

NEW表示更新后的数据行,OLD表示更新前的数据行

创建触发器语句中的 For Each ROW 的意思是【插入/删除/更新】几条数据,触发器中的sql就执行几次,相当于个循环执行触发器。

删除触发器

1 DROP TRIGGER before_insert_person_info;

调用触发器

触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的

1 -- 触发触发器执行
2 INSERT INTO person_info (person_id,name,country,salary) VALUES (13,'Jack','France',11.25);
3 
4 DELETE FROM person_info WHERE person_id = 1;
5 
6 UPDATE person_info SET name = 'Cherry' WHERE person_id = 6;

四、函数

内置函数

MySQL中提供了许多内置函数,例如:

CHAR_LENGTH(str)
        返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ,则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接(自定义连接符)
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。

    CONV(N,from_base,to_base)
        进制转换
        例如:
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点,或不含小数部分。
        例如:
            SELECT FORMAT(12332.1,4); 结果为: '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos:要替换位置其实位置
            len:替换的长度
            newstr:新字符串
        特别的:
            如果pos超过原字符串长度,则返回原字符串
            如果len超过原字符串长度,则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

    LOWER(str)
        变小写

    UPPER(str)
        变大写

    LTRIM(str)
        返回字符串 str ,其引导空格字符被删除。
    RTRIM(str)
        返回字符串 str ,结尾空格字符被删去。
    SUBSTRING(str,pos,len)
        获取字符串子序列

    LOCATE(substr,str,pos)
        获取子序列索引位置

    REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 countNULL,则返回 NULLREPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ,顺序和字符顺序相反。
    RIGHT(str,len)
        从字符串str 开始,返回从后边开始len个字符组成的子序列

    SPACE(N)
        返回一个由N空格组成的字符串。

    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。

        mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'
部分内置函数

自定义函数

 1 -- 创建函数
 2 delimiter $$
 3 
 4 DROP FUNCTION IF EXISTS f1;
 5 CREATE FUNCTION f1(
 6 i1 INT,
 7 i2 INT
 8 )
 9 RETURNS INT
10 BEGIN
11     DECLARE num INT;
12     SET num = i1 + i2;
13     RETURN num;
14 
15 END $$
16 
17 delimiter ;
18 
19 
20 -- 调用函数
21 SELECT f1(1,person_id),name,country FROM person_info WHERE person_id = 6;

删除函数

1 drop function func_name;

调用函数

1 -- 调用函数,在查询中使用
2 SELECT f1(1,person_id),name,country FROM person_info WHERE person_id = 6;

五、事物

事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。

 1 -- 创建存储过程
 2 delimiter \\
 3 DROP PROCEDURE IF EXISTS p10;
 4 CREATE PROCEDURE p10(
 5     OUT p_return_code tinyint
 6 )
 7 BEGIN
 8     -- 事物中的sql执行报错时执行以下代码块
 9   DECLARE exit handler for sqlexception 
10   BEGIN 
11     -- ERROR
12     set p_return_code = 1; 
13     rollback; 
14   END; 
15  
16     -- 事物中的sql执行警告时执行以下代码块
17   DECLARE exit handler for sqlwarning 
18   BEGIN 
19     -- WARNING 
20     set p_return_code = 2; 
21     rollback; 
22   END; 
23  
24     -- 调用存储过程后先执行事物中的代码块,如果报错或警告则执行以上两段代码块
25   START TRANSACTION; 
26     DELETE from person;
27     insert into person_info(person_id,name,country,salary) values('abc','Jack','England',9.58);
28   COMMIT; 
29  
30   -- SUCCESS 
31   set p_return_code = 0; 
32  
33   END\\
34 delimiter ;
35 
36 -- 执行存储过程
37 set @i =0;
38 call p10(@i);
39 select @i;
支持事物的存储过程

posted on 2019-03-06 16:49  破解孤独  阅读(340)  评论(0编辑  收藏  举报

导航