mysqlSenior

一、   index

(一)           introduction

索引是一个单独的、物理的数据库结构，由数据库表中的一列或者多列组合而成。

优点：大大加快数据的检索速度；创建唯一性索引，保证数据库表中每一行数据的唯一性；加速表和表之间的连接；在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：索引需要占物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。（来自网络）

分类：普通索引，最基本的索引，没有任何限制。唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。全文索引和空间索引，mysql都不支持。

根据列数，又分为单列索引和多列索引（组合索引）。

(二)           操作

1.      创建表时创建索引

CREATE table 表名(

      属性名 类型[约束],

      属性名 类型[约束],

      ...

      属性名 类型[约束],

       [索引的类型] index [别名](属性名,属性名)

);

约束条件也是一种索引类型。

1)       创建不带约束条件的索引

CREATE TABLEusers(

      id INT(20) PRIMARY KEY AUTO_INCREMENT,

      username VARCHAR(20),

      PASSWORD VARCHAR(20),

      INDEX (username)

);

2)       创建带约束有别名的索引

CREATE TABLEusers2(

      id INT(20) PRIMARY KEY AUTO_INCREMENT,

      username VARCHAR(20),

      PASSWORD VARCHAR(20),

      UNIQUE INDEX bieming (username)

)

3)       创建有别名的多列索引

CREATE TABLEusers3(

      id INT(20) PRIMARY KEY AUTO_INCREMENT,

      username VARCHAR(20),

      PASSWORD VARCHAR(20),

      INDEX bieming (username,PASSWORD)

       )

2.      在已创建表中创建索引

Create index 索引名 on 表(属性名);//索引名不可以省略

CREATE INDEX suo ONt_book(bookName);

3.      修改表时创建索引

Alter table 表名index(或者key) [索引名] (属性名);//[]表示sql语句中可以省略的部分

ALTER TABLE t_book ADD INDEX suo2(price);

4.       查看索引

Show index|keys from 表

SHOW INDEX FROM t_book;

5.      删除索引

DROP INDEX suo ON t_book;

二、   View

(一)           Introduction

视图是一种虚拟的表，从数据库中一个或多个表中导出来的表。视图表中并没有存放数据，这些数据存放在原来的表中。使用视图查询数据，实际上是从原来的表中取得相应的数据。

作用：1，简化了查询数据操作；2，增加了数据的安全性；3，提高了表的安全性。

(二)           操作

1.      创建视图

Create view 视图名称 as select…;

1)       在单表创建视图

CREATE VIEW vv2 AS SELECT * FROM t_book;

2)       在多表创建视图

CREATE VIEW vv1 AS SELECTtb.*,tbp.`t_bookType` FROM t_book tb INNER JOIN t_bookType tbp ONtb.bookTypeId=tbp.id;

3)       使用视图

Select  * from vv1;

2.      查看视图

1)       查看视图基本信息

Describe

Desc vv1;

2)       查看视图基本状态信息

SHOW TABLE STATUS LIKE 'vv1';

3)       查看视图编码的详细信息

SHOW CREATE VIEW vv1;

3.      修改视图

1)       Createor replace view 有就修改，没有就直接创建。

CREATE OR REPLACE VIEW vv1 ASSELECT * FROM t_book;

2)       Alter 语句修改视图

ALTER VIEW vv1 AS SELECT * FROMt_bookType;

4.      更新视图

更新视图的时候，本身的表也会变化

1)       插入数据

INSERT INTO vv1 VALUES (NULL,'哈哈');

注意：1.多表视图不能插入数据；2.单表视图中拥有基表中所有不能为空或者没有默认值的字段才能加入新的数据。

2)       更新数据

UPDATE vv1 SET t_bookTypeName='嘿嘿'WHERE id=1;

理论上可以更细数据，在添加视图，可以添加条件限制，满足某种条件可以更新视图。

3)       删除数据

Delete from vv1 where id=4;

注意：多表视图不能删除数据。

5.      删除视图

DROP VIEW  IF EXISTS vv1;                   

三、   Trigger

(一)           Introduction

触发器是一种特殊的存储过程，它由事件激发，比如对一个表执行操作时就会激活其执行。

(二)           操作

1.      创建触发器

Create trigger 触发器名字 after|before insert|update|delete

       On表名for each row

              触发的sql；

CREATE TRIGGER tri1 AFTER INSERT

ON t_book2 FOR EACH ROW

      UPDATEt_total SET total=total+1;

INSERT INTO t_book2 VALUES (NULL,'哈哈');

2.      New 关键字

获得刚插入的数据的信息

CREATE TRIGGER tri1 AFTER INSERT

ON t_book2 FOR EACH ROW

      UPDATEt_total SET total=new.id;

INSERT INTO t_book2 VALUES (NULL,'哈哈');

3.      触发程序激活多个sql

Old 取得删除时的数据信息

DELIMITER $

CREATE TRIGGERtri3 AFTER DELETE

      ON t_book2 FOR EACH ROW

      BEGIN

             UPDATEt_total SET total=total+1 WHERE id=1;

             INSERTINTO t_total VALUES (NULL,old.id);

      END

$

DELIMITER ;

DELETE FROM t_book2 WHERE id=2;  

4.      查看触发器

SHOW TRIGGERS;

通过mysql的information_schema数据库查看trigger表。

5.      删除触发器

DROP TRIGGER tri1;

四、   使用delimiter字时注意

结尾的dilimiter和其后面的;之间插入空格，可避免出现1064报错。

五、   Stored Procedure and Function

(一)           Introduction

为了完成特定功能的sql语句集。存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。好处：1，可重复使用，减少工作人员工作量，提高效率；2，在mysql服务器中存储和执行，减少客户端和服务端的传输，3，创建时编译一次，提高数据库执行速度。

(二)           操作

1.      创建存储过程

DELIMITER //

      CREATEPROCEDURE pr_book (IN b INT)

      BEGIN

      SELECT *FROM t_book WHERE id=1;

      END

      //

DELIMITER ;

调用存储过程

CALL pr_book(1);

其中create procedure pr1(in|out|inout)

In：输入参数；out：输出参数；inout：调用时指定，可被改变和返回。

2.      创建函数

如果使用mysql 函数，需要开启函数制造器。SHOW VARIABLES LIKE '%func%';可以查看开启状态。

set global log_bin_trust_function_creators=1;开启函数权限。

DELIMITER //

      CREATEFUNCTION fun1(b INT)

      RETURNSVARCHAR(20)

      BEGIN

      RETURN(SELECT bookName FROM t_book where id=b);

      END

      //

DELIMITER ;

3.      存储过程中变量的使用

1)       使用declare a varchar(20) 声明变量

DELIMITER //

      CREATE PROCEDURE pr2 ()

      BEGIN

      DECLARE a VARCHAR(20);

      INSERT INTO t_book2 VALUES (NULL,'回来');

      END

      //

DELIMITER ;

CALL pr2();

2)       给变量赋值

a)       直接赋值

DELIMITER //

   CREATE PROCEDURE pr4 ()

   BEGIN

   DECLARE a VARCHAR(20);

   SET a='往往';

   INSERT INTO t_book2 VALUES (NULL,a);

   END

   //

DELIMITER ;

CALL pr4();

b)       使用sql语句赋值

利用这个特征，将存储过程中声明变量作为中介，将前一个表中数据，插入到另一个表中。

DELIMITER //

     CREATEPROCEDURE pr5 ()

     BEGIN

     DECLAREa,b VARCHAR(20);

     SELECTUsername,PASSWORD INTO a,b FROM users WHERE id=1;

     INSERTINTO users2 VALUES (NULL,a,b);

     END

     //

DELIMITER ;

CALL pr5();

4.      Cursor        游标的使用

使用游标可以查出来多条记录。在存储过程或者是函数中使用游标。可以逐条读取结果集中的数据。使用步骤：声明游标，打开游标，使用游标，关闭游标。游标的使用必须在处理程序之前，在声明的变量和条件之后。

DELIMITER //

      CREATEPROCEDURE pr3()

      BEGIN

      DECLARE a,bVARCHAR (20);

      DECLAREcurlName CURSOR FOR SELECT username,PASSWORD FROM users;

      OPENcurlName;

      FETCHcurlName INTO a,b;

      INSERT INTOusers2 VALUES (NULL,a,b);

      END

      //

DELIMITER ;

CALL pr3();

六、   流程控制

使用流程控制执行sql语句。选择结构：if，case；循环结构：loop，iterate，repeat，while，leave。

声明全局变量，需要使用@。

(一)           If

DELIMITER //

      CREATEPROCEDURE prif()

      BEGIN

      SELECTCOUNT(*) INTO @sum FROM users;

      IF@sum>1 THEN UPDATE user2 SET username='ifif' WHERE id=1;

      ELSE INSERTINTO users2 VALUES (NULL,'ifnotif','kkk');

      END IF;

      END

      //

DELIMITER ;

CALL prif();

(二)           Case

DELIMITER //

      CREATEPROCEDURE prcase()

      BEGIN

      SELECTCOUNT(*) INTO @sum FROM users;

      CASE @sum

      WHEN 1 THENUPDATE users2 SET username='case' WHERE id=1;

      WHEN 2 THENINSERT INTO users2 VALUES (NULL,'casenot','ss');

      ELSE INSERTINTO users2 VALUES (NULL,'case333','sss');

      END CASE;

      END

      //

DELIMITER ;

CALL prcase;

(三)           Loop

Leave用于退出loop循环。

DELIMITER *

       CREATEPROCEDURE pro5(IN num INT)

       BEGIN

       loop1:LOOP

              SET num=num-1;

              IF num<=0 THEN LEAVE loop1;

              ELSE INSERT INTO t_user VALUES (NULL,'哈哈'+num,num);

              END IF;

       END LOOP;

       END

*

DELIMITER ;

CALL pro8(3);

 

(四)           Repeat

配合until退出循环

DELIMITER //

       CREATEPROCEDURE pr1 (IN num INT)

       BEGIN

       REPEAT

       SETnum=num-1;

       INSERTINTO t_user VALUES (NULL,num,num);

       UNTILnum=1

       END REPEAT;

       END

       //

DELIMITER ;

CALL pr1(3);

(五)           While

While 后面跟执行条件 do后面要执行的语句

DELIMITER //

       CREATEPROCEDURE pr2 (IN num INT)

       BEGIN

       WHILEnum>0 DO

       INSERTINTO t_user VALUES (NULL,num,num);

       SETnum=num-1;

       END WHILE;

       END

       //

DELIMITER ;

CALL pr2(3);

(六)           Iterate

Iterate 相当于java中的continue，表示退出当次循环。Leave的作用是退出整个循环。

DELIMITER //

       CREATEPROCEDURE pr5(IN num INT)

       BEGIN

       loop1:LOOP

       SET num=num-1;

       IF num=0 THEN LEAVE loop1;

       END IF;

       IF num=3 THEN ITERATE loop1;

       ELSE INSERT INTO t_user VALUES (NULL,'loop1',num);

       END IF;

       END LOOP loop1;

       END

       //

DELIMITER ;

CALL pr5(5);

(七)           查看存储过程和函数

1.      查看存储过程的状态

SHOWPROCEDURE STATUS LIKE 'pr5';

2.      查看函数的状态

SHOWFUNCTION STATUS LIKE 'fun2';

3.      查看存储过程的定义

SHOW CREATE PROCEDURE pr5;

4.      查看函数的状态

SHOW CREATE FUNCTION fun2;

5.      也可以使用mysql系统中information_schema库中的routines表查看这些信息。

(八)           修改存储过程和函数

因为修改存储过程和函数比较复杂，所以一般直接删除，然后新建。删除时，要在创建存储过程或者函数的库下面，操作，否则，如果没有同名者，会报错。

 

删除存储过程：drop procedure 名称。如：drop procedure pro_1;

注意：做操作时，要在包含操作对象的库下面操作，否则报错。