您的位置:金沙游乐场85155 > 大数据库 > MySQL基础二

MySQL基础二

发布时间:2019-11-03 00:30编辑:大数据库浏览(138)

    事务

    1、概述

    MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务,但是一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。!

    • 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
    • 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
    • 事务用来管理insert,update,delete语句

    一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

    • 1、事务的原子性:一组事务,要么成功;要么撤回。
    • 2、稳定性 : 有非法数据(外键约束之类),事务撤回。
    • 3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
    • 4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

    2、事务操作

    • 开启事务 start transaction
    • 回滚事务 rollback
    • 提交事务 commit
    • 保留点    savepoint

      delimiter -- 此条可更改SQL语句结束符 create PROCEDURE p1(

      OUT p_return_code tinyint
      

      ) BEGIN DECLARE exit handler for sqlexception BEGIN

      -- ERROR 
      set p_return_code = 1; 
      rollback; 
      

      END;

      DECLARE exit handler for sqlwarning BEGIN

      -- WARNING 
      set p_return_code = 2; 
      rollback; 
      

      END;

      START TRANSACTION;

      DELETE from tb1;
      insert into tb2(name)values('seven');
      

      COMMIT;

      -- SUCCESS set p_return_code = 0;

      END delimiter ;

    其他 

    1、条件语句

    图片 1图片 2

    delimiter \
    CREATE PROCEDURE proc_if ()
    BEGIN
    
        declare i int default 0;
        if i = 1 THEN
            SELECT 1;
        ELSEIF i = 2 THEN
            SELECT 2;
        ELSE
            SELECT 7;
        END IF;
    
    END\
    delimiter ;
    

    if条件语句

    2、循环语句

    图片 3图片 4

    delimiter \
    CREATE PROCEDURE proc_while ()
    BEGIN
    
        DECLARE num INT ;
        SET num = 0 ;
        WHILE num < 10 DO
            SELECT
                num ;
            SET num = num + 1 ;
        END WHILE ;
    
    END\
    delimiter ;
    

    while循环

    图片 5图片 6

    delimiter \
    CREATE PROCEDURE proc_repeat ()
    BEGIN
    
        DECLARE i INT ;
        SET i = 0 ;
        repeat
            select i;
            set i = i + 1;
            until i >= 5
        end repeat;
    
    END\
    delimiter ;
    

    repeat循环

    图片 7图片 8

    delimiter \
    CREATE PROCEDURE proc_loop ()
    BEGIN
    
        declare i int default 0;
        loop_label: loop
            select i;
            set i=i+1;
            if i>=5 then
                leave loop_label;
                end if;
        end loop;
    
    END\
    delimiter ;
    

    loop

    3、动态执行SQL语句

    图片 9图片 10

    delimiter \
    DROP PROCEDURE IF EXISTS proc_sql \
    CREATE PROCEDURE proc_sql ()
    BEGIN
        declare p1 int;
        set p1 = 11;
        set @p1 = p1;
    
        PREPARE prod FROM 'select * from tb2 where nid > ?';
        EXECUTE prod USING @p1;
        DEALLOCATE prepare prod; 
    
    END\
    delimiter ;
    

    动态执行SQL

     

     

     

     

    其它

    1、条件语句

    图片 11图片 12

    delimiter \
    CREATE PROCEDURE proc_if ()
    BEGIN
    
        declare i int default 0;
        if i = 1 THEN
            SELECT 1;
        ELSEIF i = 2 THEN
            SELECT 2;
        ELSE
            SELECT 7;
        END IF;
    
    END\
    delimiter ;
    

    if条件语句

    2、循环语句

    图片 13图片 14

    delimiter \
    CREATE PROCEDURE proc_while ()
    BEGIN
    
        DECLARE num INT ;
        SET num = 0 ;
        WHILE num < 10 DO
            SELECT
                num ;
            SET num = num + 1 ;
        END WHILE ;
    
    END\
    delimiter ;
    

    while循环

    图片 15图片 16

    delimiter \
    CREATE PROCEDURE proc_repeat ()
    BEGIN
    
        DECLARE i INT ;
        SET i = 0 ;
        repeat
            select i;
            set i = i + 1;
            until i >= 5
        end repeat;
    
    END\
    delimiter ;
    

    repeat循环

    图片 17图片 18

    BEGIN
    
        declare i int default 0;
        loop_label: loop
    
            set i=i+1;
            if i<8 then
                iterate loop_label;
            end if;
            if i>=10 then
                leave loop_label;
            end if;
            select i;
        end loop loop_label;
    
    END
    

    loop

    3、动态执行SQL语句

    图片 19图片 20

    delimiter \
    DROP PROCEDURE IF EXISTS proc_sql \
    CREATE PROCEDURE proc_sql ()
    BEGIN
        declare p1 int;
        set p1 = 11;
        set @p1 = p1;
    
        PREPARE prod FROM 'select * from tb2 where nid > ?';
        EXECUTE prod USING @p1;
        DEALLOCATE prepare prod; 
    
    END\
    delimiter ;
    

    View Code

     

    存储过程

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

    1、创建存储过程

    图片 21图片 22

    -- 创建存储过程
    
    delimiter //
    create procedure p1()
    BEGIN
        select * from t1;
    END//
    delimiter ;
    
    
    
    -- 执行存储过程
    
    call p1()
    

    无参数存储过程

    对于存储过程,可以接收参数,其参数有三类:

    • in          仅用于传入参数用
    • out        仅用于返回值用
    • inout     既可以传入又可以当作返回值

    图片 23图片 24

    -- 创建存储过程
    delimiter \
    create procedure p1(
        in i1 int,
        in i2 int,
        inout i3 int,
        out r1 int
    )
    BEGIN
        DECLARE temp1 int;
        DECLARE temp2 int default 0;
    
        set temp1 = 1;
    
        set r1 = i1 + i2 + temp1 + temp2;
    
        set i3 = i3 + 100;
    
    end\
    delimiter ;
    
    -- 执行存储过程
    DECLARE @t1 INT default 3;
    DECLARE @t2 INT;
    CALL p1 (1, 2 ,@t1, @t2);
    SELECT @t1,@t2;
    

    有参数存储过程

    2、删除存储过程

    图片 25图片 26

    drop procedure proc_name;
    

    View Code

    3、执行存储过程

    图片 27图片 28

    -- 无参数
    call proc_name()
    
    -- 有参数,全in
    call proc_name(1,2)
    
    -- 有参数,有in,out,inout
    DECLARE @t1 INT;
    DECLARE @t2 INT default 3;
    call proc_name(1,2,@t1,@t2)
    

    View Code

    图片 29图片 30

    #!/usr/bin/env python
    # -*- coding:utf-8 -*-
    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 执行存储过程
    cursor.callproc('p1', args=(1, 22, 3, 4))
    # 获取执行完存储的参数
    cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
    result = cursor.fetchall()
    
    conn.commit()
    cursor.close()
    conn.close()
    
    
    print(result)
    

    pymysql执行存储过程

    视图

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

    图片 31图片 32

    SELECT
        *
    FROM
        (
            SELECT
                nid,
                NAME
            FROM
                tb1
            WHERE
                nid > 2
        ) AS A
    WHERE
        A. NAME > 'djb';
    

    临时表搜素

    1、创建视图

    图片 33图片 34

    --格式:CREATE VIEW 视图名称 AS  SQL语句
    CREATE VIEW v1 AS 
    SELET nid, 
        name
    FROM
        A
    WHERE
        nid > 4
    

    创建视图

    2、删除视图

    图片 35图片 36

    --格式:DROP VIEW 视图名称
    
    DROP VIEW v1
    

    删除视图

    3、修改视图

    图片 37图片 38

    -- 格式:ALTER VIEW 视图名称 AS SQL语句
    
    ALTER VIEW v1 AS
    SELET A.nid,
        B. NAME
    FROM
        A
    LEFT JOIN B ON A.id = B.nid
    LEFT JOIN C ON A.id = C.nid
    WHERE
        A.id > 2
    AND C.nid < 5
    

    修改视图

    4、使用视图

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

    图片 39图片 40

    select * from v1
    

    View Code

    触发器

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

    1、创建基本语法

    图片 41图片 42

    # 插入前
    CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
    BEGIN
        ...
    END
    
    # 插入后
    CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
    BEGIN
        ...
    END
    
    # 删除前
    CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END
    
    # 删除后
    CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END
    
    # 更新前
    CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END
    
    # 更新后
    CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
    BEGIN
        ...
    END
    

    View Code

    图片 43图片 44

    delimiter //
    CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
    BEGIN
    
    IF NEW. NAME == 'alex' THEN
        INSERT INTO tb2 (NAME)
    VALUES
        ('aa')
    END
    END//
    delimiter ;
    

    插入前触发器

    图片 45图片 46

    delimiter //
    CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
    BEGIN
        IF NEW. num = 666 THEN
            INSERT INTO tb2 (NAME)
            VALUES
                ('666'),
                ('666') ;
        ELSEIF NEW. num = 555 THEN
            INSERT INTO tb2 (NAME)
            VALUES
                ('555'),
                ('555') ;
        END IF;
    END//
    delimiter ;
    

    插入后触发器

    特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。

    2、删除触发器

    图片 47图片 48

    DROP TRIGGER tri_after_insert_tb1;
    

    View Code

    3、使用触发器

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

    图片 49图片 50

    insert into tb1(num) values(666)
    

    View Code

    索引

    1、概述

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

    建立索引会占用磁盘空间的索引文件。

    2、索引种类

    • 普通索引:仅加速查询
    • 唯一索引:加速查询 + 列值唯一(可以有null)
    • 主键索引:加速查询 + 列值唯一 + 表中只有一个(不可以有null)
    • 组合索引:多列值组成一个索引,
                    专门用于组合搜索,其效率大于索引合并
    • 全文索引:对文本的内容进行分词,进行搜索 

    索引合并:使用多个单列索引组合查询搜索
    覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖

    a、普通索引

    普通索引仅有一个功能:加速查询

    图片 51图片 52

    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        index ix_name (name)
    )
    

    创建表+索引

    图片 53图片 54

    create index index_name on table_name(column_name)
    

    创建索引

    图片 55图片 56

    drop index_name on table_name;
    

    删除索引

    图片 57图片 58

    show index from table_name;
    

    查看索引

    注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。

    create index ix_extra on in1(extra(32));
    

    b、唯一索引

    唯一索引有两个功能:加速查询 和 唯一约束(可含null)

    图片 59图片 60

    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        unique ix_name (name)
    )
    

    创建唯一索引+表

    图片 61图片 62

    create unique index 索引名 on 表名(列名)
    

    创建唯一索引

    图片 63图片 64

    drop unique index 索引名 on 表名
    

    删除唯一索引

    c、主键索引

    主键有两个功能:加速查询 和 唯一约束(不可含null)

    图片 65图片 66

    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        index ix_name (name)
    )
    
    OR
    
    create table in1(
        nid int not null auto_increment,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        primary key(ni1),
        index ix_name (name)
    )
    

    创建表+创建主键

    图片 67图片 68

    alter table 表名 add primary key(列名);
    

    创建主键

    图片 69图片 70

    alter table 表名 drop primary key;
    alter table 表名  modify  列名 int, drop primary key;
    

    删除主键

    d、组合索引

    组合索引是将n个列组合成一个索引

    其应用场景为:频繁的同时使用n列来进行查询,如:where n1 = 'djb' and n2 = 666

    图片 71图片 72

    create table in3(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text
    )
    

    创建表

    图片 73图片 74

    create index ix_name_email on in3(name,email);
    

    创建索引

    3、相关命令

    - 查看表结构
        desc 表名
    
    - 查看生成表的SQL
        show create table 表名
    
    - 查看索引
        show index from  表名
    
    - 查看执行时间
        set profiling = 1;
        SQL...
        show profiles;
    

    4、使用索引和不使用索引

    由于索引是专门用于加速搜索而生,所以加上索引之后,查询效率会快到飞起来。

    5、正确使用索引

    数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。
    即使建立索引,索引也不会生效:

    - like '%xx'
        select * from tb1 where name like '%cn';
    - 使用函数
        select * from tb1 where reverse(name) = 'wupeiqi';
    - or
        select * from tb1 where nid = 1 or email = 'seven@live.com';
        特别的:当or条件中有未建立索引的列才失效,以下会走索引
                select * from tb1 where nid = 1 or name = 'seven';
                select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
    - 类型不一致
        如果列是字符串类型,传入条件是必须用引号引起来,不然...
        select * from tb1 where name = 999;
    - !=
        select * from tb1 where name != 'alex'
        特别的:如果是主键,则还是会走索引
            select * from tb1 where nid != 123
    - >
        select * from tb1 where name > 'alex'
        特别的:如果是主键或索引是整数类型,则还是会走索引
            select * from tb1 where nid > 123
            select * from tb1 where num > 123
    - order by
        select email from tb1 order by name desc;
        当根据索引排序时候,选择的映射如果不是索引,则不走索引
        特别的:如果对主键排序,则还是走索引:
            select * from tb1 order by nid desc;
    
    - 组合索引最左前缀
        如果组合索引为:(name,email)
        name and email       -- 使用索引
        name                 -- 使用索引
        email                -- 不使用索引
    

    6、其他注意事项

    1 - 避免使用select *
    2 - count(1)或count(列) 代替 count(*)
    3 - 创建表时尽量时 char 代替 varchar
    4 - 表的字段顺序固定长度的字段优先
    5 - 组合索引代替多个单列索引(经常使用多个条件查询时)
    6 - 尽量使用短索引
    7 - 使用连接(JOIN)来代替子查询(Sub-Queries)
    8 - 连表时注意条件类型需一致
    9 - 索引散列值(重复少)不适合建索引,例:性别不适合
    

    7、limit分页

    方案:
    记录当前页最大或最小ID
    1. 页面只有上一页,下一页
    # max_id
    # min_id
    下一页:
    select * from userinfo3 where id > max_id limit 10;
    上一页:
    select * from userinfo3 where id < min_id order by id desc limit 10;
    2. 上一页 192 193  [196]  197  198  199 下一页
    
    select * from userinfo3 where id in (select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10)
    

    8、执行计划

    explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化

    mysql> explain select * from tb2;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    |  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    1 row in set (0.00 sec)
    

    9、慢日志查询

    a、配置MySQL自动记录慢日志

    slow_query_log = OFF                            是否开启慢日志记录
    long_query_time = 2                              时间限制,超过此时间,则记录
    slow_query_log_file = /usr/slow.log        日志文件
    log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录

    注:查看当前配置信息:
           show variables like '%query%'
         修改当前配置:
        set global 变量名 = 值

    b、查看MySQL慢日志

    mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

    图片 75图片 76

    """
    --verbose    版本
    --debug      调试
    --help       帮助
    
    -v           版本
    -d           调试模式
    -s ORDER     排序方式
                 what to sort by (al, at, ar, c, l, r, t), 'at' is default
                  al: average lock time
                  ar: average rows sent
                  at: average query time
                   c: count
                   l: lock time
                   r: rows sent
                   t: query time
    -r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
    -t NUM       显示前N条just show the top n queries
    -a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
    -n NUM       abstract numbers with at least n digits within names
    -g PATTERN   正则匹配;grep: only consider stmts that include this string
    -h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
                 default is '*', i.e. match all
    -i NAME      name of server instance (if using mysql.server startup script)
    -l           总时间中不减去锁定时间;don't subtract lock time from total time
    """
    

    View

    事务

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

    图片 77图片 78

    delimiter \
    create PROCEDURE p1(
        OUT p_return_code tinyint
    )
    BEGIN 
      DECLARE exit handler for sqlexception 
      BEGIN 
        -- ERROR 
        set p_return_code = 1; 
        rollback; 
      END; 
    
      DECLARE exit handler for sqlwarning 
      BEGIN 
        -- WARNING 
        set p_return_code = 2; 
        rollback; 
      END; 
    
      START TRANSACTION; 
        DELETE from tb1;
        insert into tb2(name)values('seven');
      COMMIT; 
    
      -- SUCCESS 
      set p_return_code = 0; 
    
      END\
    delimiter ;
    

    支持事务的存储过程

    图片 79图片 80

    DECLARE @i TINYINT;
    call p1(@i);
    select @i;
    

    执行存储过程

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:MySQL基础二

    关键词:

上一篇:MySQL---事务、函数

下一篇:没有了