mysql学习之路三（转）
创建MYSQL索引
mysql> create procedure p_test()
-> begin
-> declare counter int;
-> set counter = 1000;
-> while counter >= 1 do
-> insert into test(id,mc) values(counter,'test');
-> set counter = counter - 1;
-> end while;
-> end;//
Query OK, 0 rows affected (0.98 sec)
mysql> call p_test();
-> //
Query OK, 1 row affected (34.48 sec)
mysql> show columns from test;
+-------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------------------+-------+
| ID | int(11) | NO | | 0 | |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | 0000-00-00 00:00:00 | |
| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-------------+------+-----+---------------------+-------+
4 rows in set (0.08 sec)
mysql> select * from test where id=500;
+-----+------+---------------------+---------------------+
| ID | MC | DT | RQ |
+-----+------+---------------------+---------------------+
| 500 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> create index idx_test on test(id);
Query OK, 1000 rows affected (0.81 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> show columns from test;
+-------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------------------+-------+
| ID | int(11) | NO | MUL | 0 | |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | 0000-00-00 00:00:00 | |
| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-------------+------+-----+---------------------+-------+
2 rows in set (0.00 sec)
mysql> select * from test where id=800;
+-----+------+---------------------+---------------------+
| ID | MC | DT | RQ |
+-----+------+---------------------+---------------------+
| 800 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------+---------------------+---------------------+
1 row in set (0.00 sec)
我们可以看出上边的例子创建索引前后SQL执行时间的变化(红色字体部分)。
删除索引
mysql> drop index idx_test on test;
Query OK, 3 rows affected (1.20 sec)
Records: 3 Duplicates: 0 Warnings: 0
3 创建存储过程
MYSQL存储过程大致格式如下：
CREATE PROCEDURE procedure1 /* name存储过程名*/
(IN parameter1 INTEGER) /* parameters参数*/
BEGIN /* start of block语句块头*/
DECLARE variable1 CHAR(10); /* variables变量声明*/
IF parameter1 = 17 THEN /* start of IF IF条件开始*/
SET variable1 = 'birds'; /* assignment赋值*/
ELSE
SET variable1 = 'beasts'; /* assignment赋值*/
END IF; /* end of IF IF结束*/
INSERT INTO table1 VALUES (variable1); /* statement SQL语句*/
END /* end of block语句块结束*/
首先说明一点，在MYSQL的控制台执行创建过程的脚本时，要选择一个分隔符 DELIMITER，
给出一个最简单的MYSQL存储过程示例：
mysql> DELIMITER //
mysql> CREATE PROCEDURE P()SELECT * FROM TEST; //
Query OK, 0 rows affected (0.31 sec)
mysql> DELIMITER ;
mysql> CALL P();
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
| 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 |
+----+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
给出一个带有输入输出参数的存储过程示例：
mysql> DELIMITER //
mysql> CREATE PROCEDURE P_WITH_PARA(IN PARA_IN INT,OUT PARA_OUT INT)
-> BEGIN
-> DECLARE PARA INT;
-> SET PARA = 20;
-> SET PARA_OUT = PARA_IN + PARA;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL P_WITH_PARA(10,@SUM);//
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @SUM//
+------+
| @SUM |
+------+
| 30 |
+------+
1 row in set (0.00 sec)
存储过程内容的查看：
mysql> select body from proc where name='P_WITH_PARA';
+---------------------------------------------------------------------------+
| body |
+---------------------------------------------------------------------------+
| BEGIN
DECLARE PARA INT;
SET PARA = 20;
SET PARA_OUT = PARA_IN + PARA;
END |
+---------------------------------------------------------------------------+
1 row in set (0.01 sec)
MYSQL存储过程中的一些常用控制结构：
选择结构：
IF ... THEN
...
ELSE
...
END IF;
CASE ...
WHEN ... THEN
WHEN ... THEN
END CASE;
循环结构：
WHILE ...
...
END WHILE;
LOOP_LABEL:LOOP
...
ITERATE LOOP_LABEL;
...
LEAVE LOOP_LABEL;
END LOOP;
REPEAT
...
UNTIL ...
END REPEAT;
LABEL LABEL_NAME;
...
GOTO LABEL_NAME;
4 创建函数
各个数据库包括各种开发工具都为用户提供了创建函数的功能，这里关于函数的语法我们不再做更多解释了，给出一个例子：
mysql> delimiter //
mysql> CREATE FUNCTION fn_test (n DECIMAL(3,0))
-> RETURNS DECIMAL(20,0)
-> DETERMINISTIC
-> BEGIN
-> DECLARE v_tmp DECIMAL(20,0) DEFAULT 1;
-> DECLARE counter DECIMAL(3,0);
-> SET counter = n;
-> factorial_loop: REPEAT
-> SET v_tmp = v_tmp * counter;
-> SET counter = counter - 1;
-> UNTIL counter = 1
-> END REPEAT;
-> RETURN v_tmp;
-> END
-> //
Query OK, 0 rows affected (0.23 sec)
mysql> delimiter ;
mysql> select fn_test(10);
+-------------+
| fn_test(10) |
+-------------+
| 3628800 |
+-------------+
1 row in set (0.20 sec)
mysql> select fn_test(3);
+------------+
| fn_test(3) |
+------------+
| 6 |
+------------+
1 row in set (0.00 sec)
显示一个定义好的函数的内容：
mysql> show create function fn_test;
5 创建视图
给出一个创建视图的例子：
注意：下边例子红色字体部分为MYSQL数据库取前n条记录的方法，不同于其他数据库。
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2001 |
+----------+
1 row in set (0.01 sec)
mysql> create view v_test as select * from test limit 20;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from v_test;
+------+------+---------------------+---------------------+
| ID | MC | DT | RQ |
+------+------+---------------------+---------------------+
| 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 999 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 998 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 997 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 996 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 995 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 994 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 993 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 992 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 991 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 990 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 989 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 988 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 987 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 986 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 985 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 984 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 983 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 982 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 981 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+------+------+---------------------+---------------------+
20 rows in set (0.06 sec)
和显示函数内容类似，我们也可以按照如下方法获得创建视图的语法内容：
mysql> show create view v_test;
删除视图
mysql> drop view v_test;
Query OK, 0 rows affected (0.02 sec)
6 创建触发器
给出一个简单的创建触发器的例子：
mysql> delimiter //
mysql> create trigger tr_test before insert on test for each row
-> begin
-> insert into test1(id,mc) values(new.id,new.mc);
-> end
-> //
Query OK, 0 rows affected (0.17 sec)
mysql> delimiter ;
mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> insert into test (id,mc) values(50000,'trigger_to_test1');
Query OK, 1 row affected (0.19 sec)
mysql> select id,mc from test1;
+-------+------------------+
| id | mc |
+-------+------------------+
| 50000 | trigger_to_test1 |
+-------+------------------+
1 row in set (0.00 sec)
3 MYSQL存储引擎和表类型
MYSQL支持数个存储引擎作为对不同表的类型的处理器。
mysql> SHOW ENGINES;
+------------+---------+--------------------------------------------------------
--------+
| Engine | Support | Comment
|
+------------+---------+--------------------------------------------------------
--------+
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance
|
| MEMORY | YES | Hash based, stored in memory, useful for temporary tabl
es |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign k
eys |
| BerkeleyDB | NO | Supports transactions and page-level locking
|
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disa
ppears) |
| EXAMPLE | NO | Example storage engine
|
| ARCHIVE | YES | Archive storage engine
|
| CSV | NO | CSV storage engine
|
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables
|
| FEDERATED | NO | Federated MySQL storage engine
|
| MRG_MYISAM | YES | Collection of identical MyISAM tables
|
| ISAM | NO | Obsolete storage engine
|
+------------+---------+--------------------------------------------------------
--------+
mysql> create procedure p_test()
-> begin
-> declare counter int;
-> set counter = 1000;
-> while counter >= 1 do
-> insert into test(id,mc) values(counter,'test');
-> set counter = counter - 1;
-> end while;
-> end;//
Query OK, 0 rows affected (0.98 sec)
mysql> call p_test();
-> //
Query OK, 1 row affected (34.48 sec)
mysql> show columns from test;
+-------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------------------+-------+
| ID | int(11) | NO | | 0 | |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | 0000-00-00 00:00:00 | |
| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-------------+------+-----+---------------------+-------+
4 rows in set (0.08 sec)
mysql> select * from test where id=500;
+-----+------+---------------------+---------------------+
| ID | MC | DT | RQ |
+-----+------+---------------------+---------------------+
| 500 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> create index idx_test on test(id);
Query OK, 1000 rows affected (0.81 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> show columns from test;
+-------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------------------+-------+
| ID | int(11) | NO | MUL | 0 | |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | 0000-00-00 00:00:00 | |
| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-------------+------+-----+---------------------+-------+
2 rows in set (0.00 sec)
mysql> select * from test where id=800;
+-----+------+---------------------+---------------------+
| ID | MC | DT | RQ |
+-----+------+---------------------+---------------------+
| 800 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------+---------------------+---------------------+
1 row in set (0.00 sec)
我们可以看出上边的例子创建索引前后SQL执行时间的变化(红色字体部分)。
删除索引
mysql> drop index idx_test on test;
Query OK, 3 rows affected (1.20 sec)
Records: 3 Duplicates: 0 Warnings: 0
3 创建存储过程
MYSQL存储过程大致格式如下：
CREATE PROCEDURE procedure1 /* name存储过程名*/
(IN parameter1 INTEGER) /* parameters参数*/
BEGIN /* start of block语句块头*/
DECLARE variable1 CHAR(10); /* variables变量声明*/
IF parameter1 = 17 THEN /* start of IF IF条件开始*/
SET variable1 = 'birds'; /* assignment赋值*/
ELSE
SET variable1 = 'beasts'; /* assignment赋值*/
END IF; /* end of IF IF结束*/
INSERT INTO table1 VALUES (variable1); /* statement SQL语句*/
END /* end of block语句块结束*/
首先说明一点，在MYSQL的控制台执行创建过程的脚本时，要选择一个分隔符 DELIMITER，
给出一个最简单的MYSQL存储过程示例：
mysql> DELIMITER //
mysql> CREATE PROCEDURE P()SELECT * FROM TEST; //
Query OK, 0 rows affected (0.31 sec)
mysql> DELIMITER ;
mysql> CALL P();
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
| 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 |
+----+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
给出一个带有输入输出参数的存储过程示例：
mysql> DELIMITER //
mysql> CREATE PROCEDURE P_WITH_PARA(IN PARA_IN INT,OUT PARA_OUT INT)
-> BEGIN
-> DECLARE PARA INT;
-> SET PARA = 20;
-> SET PARA_OUT = PARA_IN + PARA;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL P_WITH_PARA(10,@SUM);//
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @SUM//
+------+
| @SUM |
+------+
| 30 |
+------+
1 row in set (0.00 sec)
存储过程内容的查看：
mysql> select body from proc where name='P_WITH_PARA';
+---------------------------------------------------------------------------+
| body |
+---------------------------------------------------------------------------+
| BEGIN
DECLARE PARA INT;
SET PARA = 20;
SET PARA_OUT = PARA_IN + PARA;
END |
+---------------------------------------------------------------------------+
1 row in set (0.01 sec)
MYSQL存储过程中的一些常用控制结构：
选择结构：
IF ... THEN
...
ELSE
...
END IF;
CASE ...
WHEN ... THEN
WHEN ... THEN
END CASE;
循环结构：
WHILE ...
...
END WHILE;
LOOP_LABEL:LOOP
...
ITERATE LOOP_LABEL;
...
LEAVE LOOP_LABEL;
END LOOP;
REPEAT
...
UNTIL ...
END REPEAT;
LABEL LABEL_NAME;
...
GOTO LABEL_NAME;
4 创建函数
各个数据库包括各种开发工具都为用户提供了创建函数的功能，这里关于函数的语法我们不再做更多解释了，给出一个例子：
mysql> delimiter //
mysql> CREATE FUNCTION fn_test (n DECIMAL(3,0))
-> RETURNS DECIMAL(20,0)
-> DETERMINISTIC
-> BEGIN
-> DECLARE v_tmp DECIMAL(20,0) DEFAULT 1;
-> DECLARE counter DECIMAL(3,0);
-> SET counter = n;
-> factorial_loop: REPEAT
-> SET v_tmp = v_tmp * counter;
-> SET counter = counter - 1;
-> UNTIL counter = 1
-> END REPEAT;
-> RETURN v_tmp;
-> END
-> //
Query OK, 0 rows affected (0.23 sec)
mysql> delimiter ;
mysql> select fn_test(10);
+-------------+
| fn_test(10) |
+-------------+
| 3628800 |
+-------------+
1 row in set (0.20 sec)
mysql> select fn_test(3);
+------------+
| fn_test(3) |
+------------+
| 6 |
+------------+
1 row in set (0.00 sec)
显示一个定义好的函数的内容：
mysql> show create function fn_test;
5 创建视图
给出一个创建视图的例子：
注意：下边例子红色字体部分为MYSQL数据库取前n条记录的方法，不同于其他数据库。
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2001 |
+----------+
1 row in set (0.01 sec)
mysql> create view v_test as select * from test limit 20;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from v_test;
+------+------+---------------------+---------------------+
| ID | MC | DT | RQ |
+------+------+---------------------+---------------------+
| 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 999 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 998 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 997 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 996 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 995 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 994 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 993 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 992 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 991 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 990 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 989 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 988 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 987 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 986 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 985 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 984 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 983 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 982 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 981 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+------+------+---------------------+---------------------+
20 rows in set (0.06 sec)
和显示函数内容类似，我们也可以按照如下方法获得创建视图的语法内容：
mysql> show create view v_test;
删除视图
mysql> drop view v_test;
Query OK, 0 rows affected (0.02 sec)
6 创建触发器
给出一个简单的创建触发器的例子：
mysql> delimiter //
mysql> create trigger tr_test before insert on test for each row
-> begin
-> insert into test1(id,mc) values(new.id,new.mc);
-> end
-> //
Query OK, 0 rows affected (0.17 sec)
mysql> delimiter ;
mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> insert into test (id,mc) values(50000,'trigger_to_test1');
Query OK, 1 row affected (0.19 sec)
mysql> select id,mc from test1;
+-------+------------------+
| id | mc |
+-------+------------------+
| 50000 | trigger_to_test1 |
+-------+------------------+
1 row in set (0.00 sec)
3 MYSQL存储引擎和表类型
MYSQL支持数个存储引擎作为对不同表的类型的处理器。
mysql> SHOW ENGINES;
+------------+---------+--------------------------------------------------------
--------+
| Engine | Support | Comment
|
+------------+---------+--------------------------------------------------------
--------+
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance
|
| MEMORY | YES | Hash based, stored in memory, useful for temporary tabl
es |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign k
eys |
| BerkeleyDB | NO | Supports transactions and page-level locking
|
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disa
ppears) |
| EXAMPLE | NO | Example storage engine
|
| ARCHIVE | YES | Archive storage engine
|
| CSV | NO | CSV storage engine
|
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables
|
| FEDERATED | NO | Federated MySQL storage engine
|
| MRG_MYISAM | YES | Collection of identical MyISAM tables
|
| ISAM | NO | Obsolete storage engine
|
+------------+---------+--------------------------------------------------------
--------+
