（二）MySQL学习笔记

1.视图

视图是一系列select语句返回的可视化结果集，是一张虚拟表。更多介绍请查看http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh

视图操作

CREATE VIEW vw(v_title,v_url) AS SELECT title,url FROM info WHERE types='生活';  #创建一个info表中types为'生活',有v_title,v_url两个字段的视图

SELECT * FROM vw WHERE v_title='VID' LIMIT 5;  #在视图中查询title为'VID'的前5条结果

DROP VIEW vw;  #删除视图

SHOW CREATE VIEW vm;  #查看视图的完整定义

2.索引

MySQL 索引可以分为单列索引、复合索引、唯一索引、主键索引等。适当的建立索引能有效的提高性能

创建单列索引（多列索引并不能取得复合索引的效果）

1) CREATE INDEX index_title ON info(title);  #为info表的title列创建单列索引index_title

2) ALTER TABLE info ADD INDEX index_title ON (title);  

3) CREATE TABLE info (title VARCHAR(20), INDEX(title(20))); 

创建复合索引

1) CREATE INDEX index_name ON info(title,author);  #为info表的title,author列创建复合索引index_name

2) ALTER TABLE info ADD INDEX index_name ON (title,author);  

3) CREATE TABLE info (title VARCHAR(20), 
　　　　　　　　　　     author VARCHAR(10),
　　　　               aid INT NOT NULL,
　　　　               PRIMARY KEY ('aid'),
　　　　　　　　　　　　　INDEX index_name(title,author)); 

查看索引

SHOW INDEX FROM info；  #查看info表的索引

删除索引

DROP INDEX index_name ON info;  #删除info表的索引index_name

复合查询

查询条件中使用了复合索引的第一个字段（这里指title），索引才会被使用，跟索引列的顺序相关联。

SELECT * FROM info WHERE title='崩坏';
 
SELECT * FROM info WHERE title='崩坏' AND aid=2;
 
SELECT * FROM info WHERE title='崩坏' AND (title='博客' OR aid=2);
 
SELECT * FROM info WHERE title='崩坏' AND aid>=3 AND aid < 6;

#以下查询是不可行的

SELECT * FROM info WHERE aid=2;

SELECT * FROM test WHERE aid=2 OR title='崩坏;

创建唯一索引

1） CREATE UNIQUE INDEX index_title ON info(title);  #为info表创建title列的索引index_title

2） ALTER TABLE info ADD UNIQUE INDEX index_title ON (title);

3） CREATE TABLE info (title VARCHAR(20),
　　　　　　　　         aid int(11) NOT NULL AUTO_INCREMENT,
                      author varchar(10),
                      PRIMARY KEY (aid),
                      UNIQUE index_title (title(20)));

创建主键索引

1） ALTER TABLE info ADD PRIMARY KEY(title);

2） CREATE TABLE info (title VARCHAR(20),
                       aid int(10) NOT NULL AUTO_INCREMENT ,
                       PRIMARY KEY (aid)); 

3.存储过程

存储在服务器中的一套SQL语句就是存储过程，可反复执行。

创建存储过程（名称为simpleproc）

DELIMITER //  #首先改变分隔符

#IN,OUT,INOUT表示输入类型，输出类型，输入又输出类型

mysql> CREATE PROCEDURE simpleproc1 (IN bram INT,OUT param INT)
    -> BEGIN
    ->   SELECT MAX(coin) INTO param FROM info WHERE views>bram;
    -> END
    -> //

mysql> CREATE PROCEDURE simpleproc2 (IN name VARCHAR(20))
    -> BEGIN
    -> IF name IS NULL OR name='' then
    -> 　　SELECT * FROM info;
    -> ELSE
    -> 　　SELECT * FROM info WHERE title like name;
    -> END IF-> END
    -> //

DELIMITER ；  #还原分隔符

执行存储过程

CALL simpleproc1(1000,@a);
SELECT @a;

CALL simpleproc2(%崩坏%);

修改存储过程

ALTER FUNCTION simpleproc1 READS SQL DATA COMMENT 'find max num';  #将simpleproc1读写权限改为'READS SQL DATA',并添加注释'find max num'

删除存储过程

DROP PROCEDURE simpleproc1;

DROP PROCEDURE simpleproc2;

查看存储过程

SHOW PROCEDURE STATUS;

4.存储函数

存储函数，就是封装一段sql代码，完成某种特定的功能，并返回结果，且函数只能指定输入类型，而不能指定输出类型，输入输出类型，类似于COUNT(),MAX()等。

创建存储函数（名称为simplefunc）

DELIMITER //  #首先改变分隔符
mysql> CREATE FUNCTION simplefunc1(name CHAR(20)) RETURNS CHAR(50)
    -> BEGIN
    -> RETURN CONCAT('Hello',name,'!');
    -> END
    -> //

mysql> CREATE FUNCTION simplefunc2(num INT) RETURNS INT
    -> BEGIN
    -> DECLARE result INT DEFULT 0;
    -> WHILE num>0 DO
    ->　　SET result = result + 10;
    ->　　SET num = num - 1;
    -> END WHILE
    -> END
    -> //
DELIMITER ;  #还原分隔符

执行存储函数

SELECT simplefunc1('Delav');

SELECT simplefunc2(5),simplefunc2(10);

修改存储函数

ALTER ROCEDURE simplefunc1 READS SQL DATA COMMENT 'find max num';  #将simplefunc1读写权限改为'READS SQL DATA',并添加注释'find max num'

删除存储函数

DROP FUNCTION simplefunc1;

DROP FUNCTION simplefunc2;

查看存储函数

SHOW CREATE FUNCTION STATUS;

5.触发器

触发器是与表有关的命名数据库对象，当表出现特定事件时，将激活该触发器。

创建触发器

DELIMITER //  #修改分隔符

#当在info表执行插入操作前，将tdcp表中coin为100的title更改为'MySQL'
mysql> CREATE TRIGGER simpletger1 BEFOR INSERT ON info FOR EACH ROW
    -> BEGIN
    -> UPDATE tdcp SET title='MySQL' WHERE coin=100;
    -> END
    -> //

#当在info表执行删除操作后，将删除记录插入到history_info表中
#NEW/OLD关键字可获取变更前后的记录，OLD用于AFTER，NEW用于BEFORE
mysql> CREATE TRIGGER simpletger2 AFTER DELETE ON info FOR EACH ROW
    -> BEGIN
    -> INSERT INTO history_info(title,danmaku,coin,url) VALUES(OLD.title,OLD.danmaku,OLD.coin,OLD.url)  
    -> END
    -> //

DELIMITER ;  #还原分隔符

激活触发器

INSER INTO info (title,danmaku,coin,url) VALUES('博客园','1000','90','http://cnblogs')  #向info表插入数据，会更新tdcp表coin为100的title

DELETE FROM info WHERE title='崩坏3';  #info表删除title为'崩坏3'的记录，会自动执行触发器，将删除的这条记录插入到history_info表中

删除触发器

DROP TRIGGER simpletger1;

DROP TRIGGER simpletger2;