MySQL（二）

 转自：http://www.cnblogs.com/wupeiqi/articles/5713323.html

---

视图

　　不推荐使用，因为改视图可能需要DBA去修改。直接在代码里写子查询就行。

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

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

1、创建视图

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

2、删除视图

--格式：DROP VIEW 视图名称

DROP VIEW v1

3、修改视图

-- 格式：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、使用视图

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

select * from v1

触发器

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

1、创建基本语法

# 插入前
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
。。。
没有 查询select

插入前触发器

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    INSERT INTO teacher(tname) values('sasasda');
END//
delimiter ;

插入后触发器

delimiter修改SQL语句结束符号，下面这句话表示//结束而不是；结束。之所以要这样做，是因为 begin 里面的语句有；。如果不改，就直接结束了
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表示即将删除的数据行。插入有新数据，删除有老数据。update更新的时候新数据、老数据都有。

delimiter //
create trigger t1 before insert on student for each row
begin
    insert into teacher(tname) values(NEW.sname);       #这里就会在teacher的tname里面插入 NEW.name的值，也就是student里新增的一列的sname的值
end//
delimiter ;

执行语句，触发器启动。insert into student(gender,class_id,sname) values ("女",1,"名字1"),("女",3,"名字2"),("女",2,"名字3");

2、删除触发器

DROP TRIGGER tri_after_insert_tb1;

3、使用触发器

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

insert into tb1(num) values(666)

 函数

MySQL中提供了许多内置函数，例如：

mysql字符串函数：

concat(String2 [,...]) 　　　　　　　　//连接字串
lcase(string2) 　　　　　　　　　　//转换成小写
ucase(string2)　　　　　　　　　　 //转换成大写
length(string) 　　　　　　　　　　//string长度
ltrim(string2) 　　　　　　　　　　//去除前端空格
rtrim(string2)　　　　　　　　　　 //去除后端空格
repeat(string2,count) 　　　　　　//重复count次
replace(str,search_str,replace_str) 　　//在str中用replace_str替换search_str
substring(str,position [,length]) 　　//position开始，取length个字符,注意：position偏移量是从1开始的
space(count) 　　　　　　　　　　//生成count个空格

 


mysql数学函数：

bin(decimal_number) //十进制数字转换成二进制
ceiling(number2)    //向上取整
floor(number2)    //向下取整
max(列名)    //最大值
min(列名)    //最小值
sqrt(number)    //平方根
rand()    //随机数(0-1)，可以进行随机排序：select * from user order by rand();

 


mysql日期函数：

curdate()    // 返回当前日期
curtime()    //返回当前时间
now()    //返回当前的日期时间
unix_timestamp(date)    //返回当前date的unix时间戳
from_unixtime(时间戳)    //返回UNIX时间戳的日期值
week(date)    //返回日期date为一年中的第几周
year(date)    //返回日期date的年份
datediff(expr,expr2)    //返回起始时间expr和结束时间expr2间的天数
DATE_FORMAT()
　　select DATA_FORMAT(ctime,"%Y-%m"),count(1) from blog group by DATE_FORMAT(ctime,"%Y-%m")

更多函数：官方猛击这里

1、自定义函数

delimiter \
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;          # declare num int default 0;
    set num = i1 + i2;
    return(num);
END \
delimiter ;

2、删除函数

drop function func_name;

3、执行函数

# 获取返回值
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;


# 在查询中使用
select f1(11,nid) ,name from tb2;

存储过程

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

保存在MySQL上，用于代替程序员写SQL语句。（MySQL存储过程用的比较少）

方式一：

　　MySQL：储存过程

　　程序：调用储存过程

方式二：

　　MySQL：什么都不干

　　程序：SQL语句

方式三：

　　MySQL：什么都不干

　　程序：类和对象（SQL语句）

mysql查看已经创建的存储过程

SELECT * FROM information_schema.Routines;

1、创建存储过程

无参数的存储过程

-- 创建存储过程

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



-- 执行存储过程

call p1()

cursor.callproc('p1')

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

• in          仅用于传入参数用
• out        仅用于返回值用，存储过程中不能使用（查看）
• inout     既可以传入又可以当作返回值

有参数的存储过程(in)

delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN
    
    select * from student where sid > n1;
END //
delimiter ;

call p2(12,2)
cursor.callproc('p2',(12,2))

有参数的存储过程(out)，用以标识存储过程的执行结果

delimiter //
create procedure p3(
    in n1 int,
    out n2 int           
)
BEGIN
    set n2 = 123123;
    select * from student where sid > n1;
END //
delimiter ;

set @v1 = 10;        #创建了一个session级别的变量v1，因为存储过程没有返回值，所以需要传入一个变量，更改这个变量后再次获取变量。通过这种方式来实际伪造一个返回值；
call p2(12,@v1)
select @v1;

set @_p3_0 = 12
set @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3


cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1）
cursor.execute('select @_p3_0,@_p3_1')         #固定写法，p3为存储过程，取n1，用@_p3_0，由于是in，所以等于传入的12；取n2，用@_p3_1，由于是out，所以存储过程中设置了什么，取到什么
r2 = cursor.fetchall()
print(r2)


这里pymysql执行 cursor.callproc('p3',(12,2))
相当于帮忙执行了
set @_p3_0=12
set @_p3_1=2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1

(inout)相当于in + out 的功能

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

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

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；

• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

• ROLLBACK TO identifier 把事务回滚到标记点；

• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

事务测试

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

　　事务用于将某些操作的多个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 ;

游标（性能低，dba比较多用），对于每一行数据要分门别类计算才用游标

delimiter //
create procedure p6()
begin 
    declare row_id int; -- 自定义变量1  
    declare row_num int; -- 自定义变量2 
    declare done INT DEFAULT FALSE;
    declare temp int;
    
    declare my_cursor CURSOR FOR select id,num from A;
    declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    
    
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into row_id,row_num;
            if done then 
                leave xxoo;
            END IF;
            set temp = row_id + row_num;
            insert into B(number) values(temp);
        end loop xxoo;
    close my_cursor;
    
    
end  //
delimter ;

动态执行SQL（防SQL注入）

-

delimiter //
create procedure p7(
    in tpl varchar(255),
    in arg int
)
begin 
    1. 预检测某个东西 SQL语句合法性
    2. SQL =格式化 tpl + arg 
    3. 执行SQL语句
    
    set @xo = arg;
    PREPARE xxx FROM 'select * from student where sid > ?';
    EXECUTE xxx USING @xo;
    DEALLOCATE prepare prod; 
end  //
delimter ;

call p7("select * from tb where id > ?",9)
===>

 -

delimiter \
CREATE PROCEDURE p8 (
    in tpl varchar(255),
    in arg int
)
BEGIN
    set @nid = arg;
        set @tpl =tpl;
    PREPARE prod FROM @tpl;
    EXECUTE prod USING @nid;
    DEALLOCATE prepare prod;
END\
delimiter ;

-------------------------------------------
call p8('select * from class WHERE cid>?',2);

索引

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

索引种类（某种格式存储）：

　　hash索引：根据相应的数据做hash，然后创建一张索引表，关联这个hash值和数据存储地址；

　　　　但是有个问题，hash值得顺序跟数据表里面的顺序不一样。单值的情况下会非常快，但是范围会慢，甚至比原来的数据库查找还慢；

　　btree索引：二叉树的形式。（innodb用的是btree索引）

1 2 3 4 5 6 7

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

MySQL中常见索引有：
　　- 主键索引：加速查找 + 不能为空 + 不能重复
　　- 普通索引：加速查找
　　- 唯一索引：加速查找 + 不能重复
　　- 联合索引（多列）：
　　　　- 联合主键索引
　　　　- 联合唯一索引
　　　　- 联合普通索引

建立索引：

　　1、额外的文件保存特殊的数据结构

　　2、查询快，插入更新删除慢

　　3、需要命中索引

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个条件时，组合索引的性能好于多个单一索引合并。

其他 

1、条件语句

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 ;

2、循环语句

while 循环

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 ;

repeat 循环

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 ;

loop

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

3、动态执行SQL语句

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 ;