先下载安装包:
mysql-5.0.27-win32.zip
mysql-noinstall-6.0.0-alpha-win32.zip
下载了2个版本:一个5.0.27安装版;一个6.0.0非安装版。本人测试用的为5.0.27安装版,安装过程不再赘述。各个操作系统平台的安装可以看MYSQL联机文档。
本章的主要目的是让我们对MYSQL的基础框架有个大概的了解。
1 连接与断开MYSQL服务器
安装完毕登陆MYSQL(有过一些其他数据库基础的人都应该很容易使用这几步):
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 22 to server version: 5.0.27-community-nt
| Database |
+——————–+
| information_schema |
| mysql |
| root |
+——————–+
3 rows in set (0.08 sec)
+————+
| database() |
+————+
| NULL |
+————+
1 row in set (0.78 sec)
Bye
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 7 to server version: 5.0.27-community-nt
+————+
| database() |
+————+
| mysql |
+————+
1 row in set (0.00 sec)
元数据是关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和
“系统目录”。INFORMATION_SCHEMA是信息数据库,其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在INFORMATION_SCHEMA
中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。
Database changed
+——————–+
| database() |
+——————–+
| information_schema |
+——————–+
1 row in set (0.00 sec)
+———————+————–+————+———————+——-
———+
| VERSION() | CURRENT_DATE | CURDATE() | NOW() | USER()
|
+———————+————–+————+———————+——-
———+
| 5.0.27-community-nt | 2007-05-24 | 2007-05-24 | 2007-05-24 17:01:16 | root@l
ocalhost |
+———————+————–+————+———————+——-
———+
1 row in set (0.01 sec)
+—————+———————+
| Variable_name | Value |
+—————+———————+
| version | 5.0.27-community-nt |
+—————+———————+
1 row in set (0.13 sec)
3 MYSQL的SHOW命令
一些SHOW语句允许使用FROM、WHERE子句,这样,在指定需要显示的行时,可更为灵活。下边给出部分例子:
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
17 rows in set (0.00 sec)
+—————————————+
| Tables_in_information_schema |
+—————————————+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+—————————————+
16 rows in set (0.00 sec)
+—————————————+————-+——–+
| table_name | table_type | engine |
+—————————————+————-+——–+
| CHARACTER_SETS | SYSTEM VIEW | MEMORY |
| COLLATIONS | SYSTEM VIEW | MEMORY |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY |
| COLUMNS | SYSTEM VIEW | MyISAM |
| COLUMN_PRIVILEGES | SYSTEM VIEW | MEMORY |
| KEY_COLUMN_USAGE | SYSTEM VIEW | MEMORY |
| ROUTINES | SYSTEM VIEW | MyISAM |
| SCHEMATA | SYSTEM VIEW | MEMORY |
| SCHEMA_PRIVILEGES | SYSTEM VIEW | MEMORY |
| STATISTICS | SYSTEM VIEW | MEMORY |
| TABLES | SYSTEM VIEW | MEMORY |
| TABLE_CONSTRAINTS | SYSTEM VIEW | MEMORY |
| TABLE_PRIVILEGES | SYSTEM VIEW | MEMORY |
| TRIGGERS | SYSTEM VIEW | MyISAM |
| USER_PRIVILEGES | SYSTEM VIEW | MEMORY |
| VIEWS | SYSTEM VIEW | MyISAM |
| columns_priv | BASE TABLE | MyISAM |
| db | BASE TABLE | MyISAM |
| func | BASE TABLE | MyISAM |
| help_category | BASE TABLE | MyISAM |
| help_keyword | BASE TABLE | MyISAM |
| help_relation | BASE TABLE | MyISAM |
| help_topic | BASE TABLE | MyISAM |
| host | BASE TABLE | MyISAM |
| proc | BASE TABLE | MyISAM |
| procs_priv | BASE TABLE | MyISAM |
| tables_priv | BASE TABLE | MyISAM |
| time_zone | BASE TABLE | MyISAM |
| time_zone_leap_second | BASE TABLE | MyISAM |
| time_zone_name | BASE TABLE | MyISAM |
| time_zone_transition | BASE TABLE | MyISAM |
| time_zone_transition_type | BASE TABLE | MyISAM |
| user | BASE TABLE | MyISAM |
+—————————————+————-+——–+
33 rows in set (0.03 sec)
+—————–+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) | YES | | NULL | |
| DATA_LENGTH | bigint(21) | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) | YES | | NULL | |
| INDEX_LENGTH | bigint(21) | YES | | NULL | |
| DATA_FREE | bigint(21) | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(64) | YES | | NULL | |
| CHECKSUM | bigint(21) | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(80) | NO | | | |
+—————–+————–+——+—–+———+——-+
21 rows in set (0.06 sec)
+———-+—————————–+———————+——–+
| Charset | Description | Default collation | Maxlen |
+———-+—————————–+———————+——–+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+———-+—————————–+———————+——–+
36 rows in set (0.00 sec)
+———+————————–+——————-+——–+
| Charset | Description | Default collation | Maxlen |
+———+————————–+——————-+——–+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
+———+————————–+——————-+——–+
1 row in set (0.00 sec)
+—————–+——————–+—-+————+————-+——–
-+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN
|
+—————–+——————–+—-+————+————-+——–
-+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1
|
| big5_bin | big5 | 84 | | Yes | 1
|
+—————–+——————–+—-+————+————-+——–
-+
2 rows in set (0.00 sec)
+——————————————————————————-
———————————————————+
| Grants for root@localhost
|
+——————————————————————————-
———————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@'localhost’ IDENTIFIED BY PASSWORD ‘*DA2
8842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB’ WITH GRANT OPTION |
+——————————————————————————-
———————————————————+
1 row in set (0.00 sec)
+——————————————————————————-
——————————————————————————–
——————————————————————————–
——————————————————————————–
——-+
| Grants for root@%
+——————————————————————————-
——————————————————————————–
——————————————————————————–
——————————————————————————–
——-+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘root’@'%
‘ IDENTIFIED BY PASSWORD ‘*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB’ WITH GRANT
OPTION |
+——————————————————————————-
——————————————————————————–
——————————————————————————–
——————————————————————————–
——-+
1 row in set (0.00 sec)
ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION
ALTER Enables use of ALTER TABLE
ALTER ROUTINE Enables stored routines to be altered or dropped
CREATE Enables use of CREATE TABLE
CREATE ROUTINE Enables creation of stored routines
CREATE TEMPORARY TABLES Enables use of CREATE TEMPORARY TABLE
CREATE USER Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW Enables use of CREATE VIEW
DELETE Enables use of DELETE
DROP Enables use of DROP TABLE
EXECUTE Enables the user to run stored routines
FILE Enables use of SELECT … INTO OUTFILE and LOAD DATA INFILE
INDEX Enables use of CREATE INDEX and DROP INDEX
INSERT Enables use of INSERT
LOCK TABLES Enables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS Enables use of SHOW FULL PROCESSLIST
REFERENCES Not implemented
RELOAD Enables use of FLUSH
REPLICATION CLIENT Enables the user to ask where slave or master servers are
REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)
SELECT Enables use of SELECT
SHOW DATABASES SHOW DATABASES shows all databases
SHOW VIEW Enables use of SHOW CREATE VIEW
SHUTDOWN Enables use of mysqladmin shutdown
SUPER Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE Enables use of UPDATE
USAGE Synonym for “no privileges”
GRANT OPTION Enables privileges to be granted
Name: ‘SHOW’
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE STATUS [LIKE 'pattern']
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]
replication master and slave servers and are described in [HELP PURGE
MASTER LOGS]:
SHOW BINLOG EVENTS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
part, ‘pattern’ is a string that can contain the SQL `%’ and `_’
wildcard characters. The pattern is useful for restricting statement
output to matching values.
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.0/en/extended-show.html.
Query OK, 1 row affected (0.00 sec)
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| mytest |
| root |
+——————–+
4 rows in set (0.01 sec)
Database changed
mysql> SELECT DATABASE();
+————+
| DATABASE() |
+————+
| mytest |
+————+
1 row in set (0.00 sec)
Empty set (0.01 sec)
可以用两种方式创建MySQL账户:
mysql> create user test_cr identified by ‘test_cr’;
Query OK, 0 rows affected (0.23 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
+————-+
| USER |
+————-+
| TEST |
| TEST_NORMAL |
| root |
| TEST |
| TEST_NORMAL |
| root |
+————-+
6 rows in set (0.00 sec)
(‘TEST’@'localhost’)只用于从本机连接时。另一个账户(‘TEST’@'%’)可用于从其它主机连接。请注意TEST的两个账户必须能
从任何主机以TEST连接。没有localhost账户,当TEST从本机连接时,mysql_install_db创建的localhost的匿名用户账户将占先。
结果是,TEST将被视为匿名用户。原因是匿名用户账户的Host列值比’TEST’@'%’账户更具体,这样在user表排序顺序中排在前面。
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 14 to server version: 5.0.27-community-nt
+————+
| DATABASE() |
+————+
| mytest |
+————+
1 row in set (0.00 sec)
+—————-+
| USER() |
+—————-+
| TEST@localhost |
+—————-+
1 row in set (0.00 sec)
1 创建表
-> MC VARCHAR(60),DT DATE DEFAULT NOW());
ERROR 1067 (42000): Invalid default value for ‘DT’
+——————+
| LAST_INSERT_ID() |
+——————+
| 3 |
+——————+
1 row in set (0.06 sec)
A、列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。
B、列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。(注意一个UPDATE设置一个列为它已经有的值,这将不引起TIMESTAMP列被更新,因为如果你设置一个列为它当前的值,MySQL为了效率而忽略更改。)
C、明确地设定TIMESTAMP列为NULL或NOW()。
MySQL可以以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD格式来显示TIMESTAMP值,这主要取决于M值,它们分别为14(缺省值)/12/8/6。
-> MC VARCHAR(60),DT TIMESTAMP);
Query OK, 0 rows affected (1.08 sec)
+——-+————-+——+—–+——————-+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+——————-+—————-+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | CURRENT_TIMESTAMP | |
+——-+————-+——+—–+——————-+—————-+
3 rows in set (0.17 sec)
Query OK, 1 row affected (0.13 sec)
+—-+———-+———————+
| ID | MC | DT |
+—-+———-+———————+
| 1 | ZhangSan | 2007-05-25 09:54:59 |
+—-+———-+———————+
1 row in set (0.06 sec)
Query OK, 1 row affected (0.70 sec)
Records: 1 Duplicates: 0 Warnings: 0
+——-+————-+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———————+—————-+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | CURRENT_TIMESTAMP | |
| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |
+——-+————-+——+—–+———————+—————-+
4 rows in set (0.19 sec)
Query OK, 1 row affected (0.05 sec)
+—-+———-+———————+———————+
| 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 |
+—-+———-+———————+———————+
2 rows in set (0.01 sec)
Query OK, 1 row affected (0.11 sec)
+—-+———-+———————+———————+
| 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 | LiSi | 2007-05-25 10:03:29 | 2007-05-25 10:03:29 |
+—-+———-+———————+———————+
3 rows in set (0.00 sec)
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
+—-+———-+———————+———————+
| 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 |
+—-+———-+———————+———————+
1 rows in set (0.00 sec)
修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
方式创建:
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表
的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER
TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,如要了解
更多信息,请参见MySQL documentation。
-> 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)
-> //
Query OK, 1 row affected (34.48 sec)
+——-+————-+——+—–+———————+——-+
| 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)
+—–+——+———————+———————+
| 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)
Query OK, 1000 rows affected (0.81 sec)
Records: 1000 Duplicates: 0 Warnings: 0
+——-+————-+——+—–+———————+——-+
| 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)
+—–+——+———————+———————+
| 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)
Query OK, 3 rows affected (1.20 sec)
Records: 3 Duplicates: 0 Warnings: 0
(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> CREATE PROCEDURE P()SELECT * FROM TEST; //
Query OK, 0 rows affected (0.31 sec)
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)
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)
Query OK, 0 rows affected (0.01 sec)
+——+
| @SUM |
+——+
| 30 |
+——+
1 row in set (0.00 sec)
+—————————————————————————+
| body |
+—————————————————————————+
| BEGIN
DECLARE PARA INT;
SET PARA = 20;
SET PARA_OUT = PARA_IN + PARA;
END |
+—————————————————————————+
1 row in set (0.01 sec)
IF … THEN
…
ELSE
…
END IF;
WHEN … THEN
WHEN … THEN
END CASE;
WHILE …
…
END WHILE;
…
ITERATE LOOP_LABEL;
…
LEAVE LOOP_LABEL;
END LOOP;
…
UNTIL …
END REPEAT;
…
GOTO LABEL_NAME;
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> select fn_test(10);
+————-+
| fn_test(10) |
+————-+
| 3628800 |
+————-+
1 row in set (0.20 sec)
+————+
| fn_test(3) |
+————+
| 6 |
+————+
1 row in set (0.00 sec)