关系数据库之MySQL（二）

视图

---

 

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

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

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

--格式：DROP VIEW 视图名称

DROP VIEW v1

-- 格式：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

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

select * from v1

索引

---

 

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

1 2 3 4 5 6 7

30           10                        40      5         15            35             66   1   6     11    19      21      39     55     100

　　

MySQL中常见索引有：

• 普通索引
• 唯一索引
• 主键索引
• 组合索引

1、普通索引

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

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)
)

create index index_name on table_name(column_name)

drop index_name on table_name;

show index from table_name;

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

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

2、唯一索引

唯一索引有两个功能：加速查询 和 唯一约束（可含null）

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)
)

创建表 + 唯一索引

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

drop unique index 索引名 on 表名

3、主键索引

主键有两个功能：加速查询 和 唯一约束（不可含null）

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)
)

创建表 + 创建主键索引

增删改

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

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

4、组合索引

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

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

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

create index ix_name_email on in3(name,email);

如上创建组合索引之后，查询：

• name and email  -- 使用索引
• name                 -- 使用索引
• email                 -- 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

 

事务

---

 

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

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 ;

set @i =0;
call p1(@i);
select @i;

存储过程

---

 

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

1、创建存储过程

-- 创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;



-- 执行存储过程

call p1()

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

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

-- 创建存储过程
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 ;

-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

delimiter //
                    create procedure p1()
                    begin
                        select * from v1;
                    end //
                    delimiter ;

delimiter //
                    create procedure p2(
                        in n1 int,
                        inout n3 int,
                        out n2 int,
                    )
                    begin
                        declare temp1 int ;
                        declare temp2 int default 0;

                        select * from v1;
                        set n2 = n1 + 100;
                        set n3 = n3 + n1 + 100;
                    end //
                    delimiter ;

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 ;

delimiter //
                    create procedure p3()
                    begin 
                        declare ssid int; -- 自定义变量1  
                        declare ssname varchar(50); -- 自定义变量2  
                        DECLARE done INT DEFAULT FALSE;


                        DECLARE my_cursor CURSOR FOR select sid,sname from student;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
                        
                        open my_cursor;
                            xxoo: LOOP
                                fetch my_cursor into ssid,ssname;
                                if done then 
                                    leave xxoo;
                                END IF;
                                insert into teacher(tname) values(ssname);
                            end loop xxoo;
                        close my_cursor;
                    end  //
                    delimter ;

delimiter \\
                    CREATE PROCEDURE p4 (
                        in nid int
                    )
                    BEGIN
                        PREPARE prod FROM 'select * from student where sid > ?';
                        EXECUTE prod USING @nid;
                        DEALLOCATE prepare prod; 
                    END\\
                    delimiter ;

2、删除存储过程

drop procedure proc_name;

3、执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

#!/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)