1.CREATE DATABASE句法
CREATE DATABASE db_name 用给定的名字创建一个数据库
2.DROP DATABASE句法
DROP DATABASE [IF EXISTS] db_name
3.CREATE TABLE句法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...)
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
TYPE = {ISAM | MYISAM | HEAP}
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 | 1}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 | 1}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 | 1}
or ROW_FORMAT= { default | dynamic | static | compressed }
select_statement:
[IGNORE | REPLACE] SELECT ... (Some legal select statement)
4.ALTER TABLE句法
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or RENAME [AS] new_tbl_name
or table_options
5.OPTIMIZE TABLE句法
OPTIMIZE TABLE tbl_name
6.DROP TABLE句法
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
7.DELETE句法
DELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition] [LIMIT rows]
DELETE从tbl_name表中删除满足由where_definition给出的条件的行,并且返回删除记录的个数。
8.SELECT句法
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
9.JOIN句法
MySQL支持下列用于SELECT语句的JOIN句法:
table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
10.INSERT和REPLACE句法
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
或 INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
或 INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
REPLACE句法
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
或 REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
或 REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...
REPLACE功能与INSERT完全一样,除了如果在表中的一个老记录具有在一个唯一索引上的新记录有相同的值,在新记录被插入之前,老记录被删除
11.LOAD DATA INFILE句法
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中
12.UPDATE句法
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...
[WHERE where_definition] [LIMIT #]
UPDATE用新值更新现存表中行的列,SET子句指出哪个列要修改和他们应该被给定的值,WHERE子句,如果给出,指定哪个行应该被更新,否则所有行被更新。
13.USE句法
USE db_name
USE db_name语句告诉MySQL使用db_name数据库作为随后的查询的缺省数据库
14.FLUSH句法(清除缓存)
FLUSH flush_option [,flush_option]
如果你想要清除一些MySQL使用内部缓存,你应该使用FLUSH命令。为了执行FLUSH,你必须有reload权限。
flush_option可以是下列任何东西:
HOSTS 清空主机缓存表。如果你的某些主机改变IP数字,或如果你得到错误消息Host ... is blocked,你应该清空主机表。当在连接MySQL服务器时,对一台给定的主机有多于max_connect_errors个错误连续不断地发生,MySQL认定某些东西错了并且阻止主机进一步的连接请求。清空主机表允许主机再尝试连接。见18.2.3 Host '...' is blocked错误)。你可用-O max_connection_errors=999999999启动mysqld来避免这条错误消息。
LOGS 关闭并且再打开标准和更新记录文件。如果你指定了一个没有扩展名的更新记录文件,新的更新记录文件的扩展数字将相对先前的文件加1。
PRIVILEGES 从mysql数据库授权表中重新装载权限。
TABLES 关闭所有打开的表。
STATUS 重置大多数状态变量到0。
15.KILL句法
KILL thread_id
每个对mysqld的连接以一个单独的线程运行。你可以用看SHOW PROCESSLIST命令察看哪个线程正在运行,并且用KILL thread_id命令杀死一个线程。
如果你有process权限,你能看到并且杀死所有线程。否则,你只能看到并且杀死你自己的线程。
你也可以使用mysqladmin processlist和mysqladmin kill命令检查并杀死线程。
16.SHOW句法 (得到表,列等的信息)
SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]
or SHOW [FULL] PROCESSLIST
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW GRANTS FOR user
SHOW提供关于数据库、表、列或服务器的信息。如果使用LIKE wild部分,wild字符串可以是一个使用SQL的“%”和“_”通配符的字符串。
17.EXPLAIN句法(得到关于SELECT的信息)
EXPLAIN tbl_name
or EXPLAIN SELECT select_options
EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。
当你在一条SELECT语句前放上关键词EXPLAIN,MySQL解释它将如何处理SELECT,提供有关表如何联结和以什么次序联结的信息。
18.DESCRIBE句法 (得到列的信息)
{DESCRIBE | DESC} tbl_name {col_name | wild}
DESCRIBE提供关于一张表的列的信息
19.LOCK TABLES/UNLOCK TABLES句法
LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE}
[, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
LOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时,或当服务器的连接被关闭时,当前线程锁定的所有表自动被解锁。
20.SET OPTION句法
SET [OPTION] SQL_VALUE_OPTION= value, ...
SET OPTION设置影响服务器或你的客户操作的各种选项。你设置的任何选择保持有效直到当前会话结束,或直到你设置选项为不同的值。
21.GRANT和REVOKE句法
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
GRANT在MySQL 3.22.11或以后版本中实现。对于
22.CREATE INDEX句法
CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)],... )
23.DROP INDEX句法
DROP INDEX index_name ON tbl_name
DROP INDEX从tbl_name表抛弃名为index_name的索
24.注释句法
MySQL服务器支持# to end of line、-- to end of line和/* in-line or multiple-line */注释风格
25.CREATE FUNCTION/DROP FUNCTION句法
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
SONAME shared_library_name
DROP FUNCTION function_name