mysql学习之路三（转）

2011-10-13 12:04 myjava2 阅读(114) 评论(0) 编辑收藏举报

创建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
      |
+------------+---------+--------------------------------------------------------
--------+

会员力量，点亮园子希望

刷新页面返回顶部

myjava2

mysql学习之路三（转）

About