1、索引
2、触发器
3、存储过程
4、函数
5、事务
6、视图
1、索引
索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
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个条件时，组合索引的性能好于多个单一索引合并。
索引补充：
http://www.cnblogs.com/wupeiqi/articles/5716963.html
2、触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
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
插入前调用触发器：
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 ;
插入前触发器 插入后调用触发器
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、删除触发器
DROP TRIGGER tri_after_insert_tb1;
3、使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
insert into tb1(num) values(666)
3、存储过程
存储过程是一个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; 有参数的存储过程
1. 结果集
delimiter //
create procedure p1()
begin
select * from v1;
end //
delimiter ;
------------------------------------
2. 结果集+out值
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 ;
------------------------------------------
3. 事务
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 ;
-----------------------------------------------
4. 游标
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 ;
-------------------------------------------
5. 动态执行SQL
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)
----------------------------------------------------------
pymysql执行存储过程
#!/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)
4、函数
略
5、事务
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
6、视图（临时表）
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
也可以理解为，对于需要多次使用的临时表，我们给其定义一个名称，以及对应关系，便于查询。
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
