学习总结

一、索引:索引用于快速找出在某个列有一特定值的行,如果没有索引,MySQL必须从第1条记录开始读完整个表,直到找出相关的行。表越大,查询数据所花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达某个位置去搜寻数据文件,而不必查看所有数据。

    索引是在存储引擎中实现的、每种存储引擎都不一定相同。

    索引是对数据表中一列或多列的值进行排序的一种结构,使用索引可提高数据库中特定数据的查询速度。

CREATE TABLE 中指定索引列,使用ALTER  TABLE 语句在存在的表上创建索引,使用 CREATE  INDEX语句在已存在的表上添加索引。

1.1创建表的时候创建索引:

CREATE  TABLE  book

            (

                bookid   INT NOT NULL,

                bookname   VARCHAR(255) NOT NULL

                year_publication   YEAR  NOT NULL,

[UNIQUE | FULLTEXT | SPATIAL]  INDEX(year_publication  [ASC | DESC])

            );

    UNIQUE:唯一索引            FULLTEXT:全文索引        SPATIAL: 空间索引    ;

创建唯一索引:主要原因减少查询索引列操作的执行时间,尤其日对比较庞大的数据表。,不同的是索引列的值必须唯一,但允许有空值。

创建单列索引:

CREATE  TABLE  t2

            (

            id   INT  NOT  NULL,

            name  CHAR(50)  NULL,

            INDEX  SingleIdx(name(20))

            ) ;

           1.2在已经存在的表上创建索引:使用ALTER   TABLE  或者  CREATE  INDEX创建索引;

                   CREATE  [ UNIQUE | FULLTEXT | SPATIAL]    INDEX  index_name

                   ON  table_name  (col_name [length] ,....)  [ASC | DESC]

例如:CREATE  [ UNIQUE ] INDEX  BkNameIdx   ON   book(bookname) ;

 BkNameIdx:索引名

                    book:表名

                    bookname:字段名

2、删除索引:使用ALTER  TABLE  或者  DROP  INDEX 语句,两者可以实现相同的功能,DROP  INDEX  语句在内部被映射到一个ALTER TABLE 语句中。

       2.1    ALTER  TABLE  table_name   DROP   INDEX  index_name ;

        2.2    DROP  INDEX  index_name   ON  table_name  ;

二、存储过程和函数

    存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用语句分别是:    CREATE  PROCEDURE  和

CREATE  FUNCTION ;使用CALL语句来调用存储过程,只能用输出变量返回值 ;函数可以从语句外调用(即通过函数名);

1、创建存储过程

                                        [characteristice  ...]  routi ne_body ;

CREATE  PROCEDURE  为用来创建存储函数的关键字 ; sp_name 为存储过程的名称;proc_parameter为指定存储过程的参数列表,列表形式如下:

,IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出 ; param_name 表示参数名称;type 表示参数的类型,该类型可以是MySQL数据库中的任意类型。

routine_boyd是SQL代码的内容,可以用BEGIN ......END来表示SQL代码的开始和结束  ;

将MySQL的结束符设置为 '//' ,避免与存储过程中SQL语句结束符相冲突;

MySQL->CREATE  PROCEDURE  Proc()

                      ->BEGIN

                     ->SELECT*FROM fruits ;

                     ->END //

2、创建存储函数

            CREATE     FUNCTION  func_name  ( [func_parameter] )

                    RETURNS  type

                    [ characteristic ...]   routine_body

CREATE  FUNCTION  为用来创建存储函数的关键字 ;func_name表示存储函数的名称 ; func_parameter为存储过程的参数列表,参数列表如下:

            [ IN | OUT | INOUT ] param_name  type

IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name 表示参数名称;type 表示参数的类型,该类型可以是MySQL数据库中的任意类型。

    RETURNS  type语句表示函数返回数据的类型;characteristic 指定存储函数的特性,取值与存储过程时相同。

将MySQL的结束符设置为 '//' ,避免与存储过程中SQL语句结束符相冲突;

MySQL->CREATE  FUNCTION  NameByZip ()

                      ->RETURNS    CHAR(50)

                     ->RETURN  (SELECT s_name  FROM  suppliers  WHERE  s_call='48075') ;

                     -> //

调用存储过程和函数

    1、调用存储过程:CALL  sp_name ( [parameter[....] )

  mysql->DELIMITER //

                    ->CREATE  PROCEDURE  CountProc1( IN sid  INT , OUT num  INT )

                    ->BEGIN

                    ->    SELECT  COUNT(*)   INTO  num  FROM  fruits   WHERE  s_id  =  sid ;

                    ->END //

    调用存储过程:mysql>CALL  CountProc1 (101,@num) ;

    2、调用存储函数:

            mysql->DELIMITER  //

                      ->CREATE  FUNCTION   CountProc2 (sid  INT)

                      ->RETURNS  INT

                      ->RETURN (SET  COUNT(*)  FROM  fruits  WHERE  s_id = sid) ;

                      ->END //

调用存储函数:   SELECT  CountProc2 (101) ;

查看存储过程和函数:SHOW  PROCEDURE  STATUS  LIKE 'C%' \G

                                  SHOW  CREATE   FUNCTION  test.CountProc  \G

3、光标的使用:查询语句可能返回多条记录,如果数据量非常大,需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。

SELECT语句的内容) ; 

                2、打开光标:OPEN  cursor_name(光标名称) ;

                3、使用光标:FETCH  cursor_name  INTO  var_name  [, var_name] ......{参数名称}

其中,cursor_name参数表示光标的名称 ; var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中,var_name必须在声明光标之前就定义好 ;

                4、关闭光标:CLOSE  cursor_name{光标名称}

4、流程控制的使用:IF     CASE    LOOP    LEAVE    ITERATE    REPEAT    WHILE ;

                1、IF语句

IF  expr_condition  THEN

ELSEIF expr_condition  THEN

ELSE

                          END  IF

                2、CASE语句:分支语句

CASE 

WHEN   when_value  THEN

WHEN  when_value   THEN

ELSE

                            END  CASE

                3、LOOP语句:用来重复执行某些语句,LOOP只是创建一个循环过程,并不进行条件判断 。LOOP内的语句一起重复执行直到循环被退出,跑出循环过程,使用LEAVE子句退出。

例如:使用LOOP语句进行循环操作,id值小于等于10之前,将重复执行循环过程:

                            DECLARE  id  INT   DEFAULT  0 ;

add_loop

                            SET  id = id+1 ;

THEN  LEAVE 

                                END  IF ;

END  LOOP  add_loop

                4、LEAVE语句:用来退出任何被标的流程控制构造

                5、ITERATE语句:将执行顺序转到语句段开头处 ;  只可以出现在LOOP、REPEAT和while语句内 ;

例如:

    CREATE   PROCEDURE  doiterate()                                            DECLARE  id   INT   DEFAULT  0 ;

BEGIN 

DECLARE  p1  INT  DEFAULT  0 ;                                                         SET  id = id+1 ;

my_loop : LOOP                                                                                   UNTIL  id >= 10

    SET  P1=P1+1 ;                                                                                END  REPEAT ;

  IF  p1< 10  THEN  ITERATE

THEN  LEAVE 

END  IF ;

SELECT  'P1  is   between  10  and  20' ;

END  LOOP  my_loop ;

END

                6、WHILE语句:创建一个带条件判断的循环过程,与REPEAT不同,WHILE在执行语句时,先对指定的表达式进行判断,如果为真,则执行循环内的语句,否则退出循环。

DECLARE   i  INT   DEFAULT  0  ;

WHILE  i < 10  DO

SET  i = i+1;

END  WHILE ;