mysql命令大全(5)

+------+------+------+-------+ | dept | id | sex | name |

+------+------+------+-------+ | 1 | 1 | 0 | wang | | 2 | 2 | 1 | zhang | | 3 | 3 | 0 | li |

+------+------+------+-------+ 3 rows in set (0.00 sec)

mysql> select dept,id,if(sex=0,'女','男') sex,name from test; +------+------+-----+-------+ | dept | id | sex | name | +------+------+-----+-------+ | 1 | 1 | 女 | wang | | 2 | 2 | 男 | zhang | | 3 | 3 | 女 | li |

+------+------+-----+-------+ 3 rows in set (0.00 sec)

2.9.6. Select中使用CASE Statement

mysql> select dept,id,(case sex when '0' then '女' else '男' end) as sex,name from test;

+------+------+------+-------+ | dept | id | sex | name |

+------+------+------+-------+

| 1 | 1 | 女 | wang | | 2 | 2 | 男 | zhang | | 3 | 3 | 女 | li |

+------+------+------+-------+

mysql> select (case dept when '1' then 'no1' when '2' then 'no2' else 'other' end) as dept from test; +-------+ | dept | +-------+ | no1 | | no2 | | other | +-------+

3 rows in set (0.00 sec)

注意: 相当于Oracle 中的decode 和case when 在统计报表中很有用处

2.10. 存储过程和函数

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

func_parameter: param_name type

type:

Any valid MySQL data type

characteristic: LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

routine_body:

Valid SQL procedure statement or statements

例子：创建一个过程hello，显示问候语 mysql> delimiter ;

mysql> drop PROCEDURE if exists hello; Query OK, 0 rows affected (0.00 sec)

mysql> delimiter // mysql>

mysql> CREATE PROCEDURE hello (IN s varchar(20)) -> BEGIN

-> SELECT CONCAT('Hello,',s,'!') as hello; -> END; -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call hello('wangyl'); +---------------+ | hello |

+---------------+ | Hello,wangyl! | +---------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

例子2: 查询t表的记录总数 mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN

-> SELECT COUNT(*) INTO param1 FROM t; -> END -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+

1 row in set (0.00 sec)

例子：创建一个函数 mysql> delimiter ;

mysql> drop FUNCTION if exists dateFunction; Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE FUNCTION dateFunction (iCase int) -> RETURNS varchar(50) -> begin

-> DECLARE iType int;

-> DECLARE sReturn varchar(50); -> set iType =icase +1; -> case iType

-> when 1 then select DATE_FORMAT(NOW(),'%Y-%m-%d') into sReturn; -> when 2 then select DATE_FORMAT(NOW(),'%W %M %Y') into sReturn; -> else

-> select NOW() into sReturn; -> end case;

-> return sReturn; -> end; -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select dateFunction(0); +-----------------+ | dateFunction(0) | +-----------------+ | 2005-11-14 |

+-----------------+

1 row in set (0.00 sec)

mysql> select dateFunction(1); +----------------------+ | dateFunction(1) |

+----------------------+ | Monday November 2005 | +----------------------+ 1 row in set (0.00 sec)

mysql> select dateFunction(2); +---------------------+ | dateFunction(2) |

+---------------------+ | 2005-11-14 15:05:43 | +---------------------+ 1 row in set (0.00 sec)

2.11. 补充：trigger

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4(

a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 );

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1

FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1;

UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END|

DELIMITER ;

INSERT INTO test3 (a3) VALUES

(NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES

(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

If you insert the following values into table test1 as shown here:

mysql> INSERT INTO test1 VALUES

-> (1), (3), (1), (7), (1), (8), (4), (4); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0

Then the data in the four tables will be as follows:

mysql> SELECT * FROM test1; +------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+

8 rows in set (0.00 sec)

mysql> SELECT * FROM test2; +------+ | a2 | +------+ | 1 |

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库mysql命令大全(5)在线全文阅读。