14.1.1 ALTER DATABASE Syntax,可用于修改数据库字符集和校验规则
查看校验规则可如下:
由于utf8的校验规则都是ci(case insensitive),所以是不区分大小写,
如
但可以这样,加上binary
14.1.2 ALTER EVENT Syntax 事件修改语法
可修改其执行计划,可禁用与启用该事件执行,可重名该事件【ALTER EVENT ... RENAME TO ...】
14.1.3 ALTER LOGFILE GROUP Syntax
ALTER LOGFILE GROUP logfile_group
ADD UNDOFILE 'file_name'
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name
All MySQL Cluster Disk Data objects share the same namespace. 所以 tablespace and an undo log file 以及 data file 都不能同名。可以在my.cnf中配置undo file的初始大小【可以M,G为单位】,ENGINE 参数决定在log file group 中使用哪个存储引擎,只能是【NDBCLUSTER/NDB】。
在每个Mysql的集群数据节点都会创建一个 undo file文件。
14.1.4 ALTER FUNCTION Syntax
14.1.5 ALTER INSTANCE Syntax
14.1.6 ALTER PROCEDURE Syntax
也可参考 :http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html
14.1.8 ALTER TABLE Syntax 对表本身的相关操作,如增加列,删除列,改名,改存储引擎,改分区之类的。
关于表存储storage,性能[performance],并行[concurrency]的考虑
当对表的结构进行修改时,其实是对原始表作一个Copy.在进行修改时,先将更改的内容放进副本中,然后删除原表,最后重命名为新表。当在修改表时仍可读【会报异常,在修改中也会阻止读ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads (and writes).】,但对表的更新、写、删操作会阻塞直到新表准备完毕,原表的临时备份是在新表的数据库目录里创建的。
一旦出现临时表拷贝,效率就会比较低。下面这些操作不会出现临时表:
对表重命名,仅对.frm 文件修改【即数据表元数据信息,而非数据,如重命名列,改变列的默认值,对ENUMor SET 增加新元素】,对表分区的修改,重命名索引,新增和删除索引(仅对INNODB和NDB)。
通过设置old_alter_table=ON或ALGORITHM=COPY,强制不使用临时表拷贝。
14.1.8.1 ALTER TABLE Partition Operations
与修改表相关的分区操作,如重分区,新增,删除,导入,合并和拆分;使用partition 选项,总是以PARTITION BY 开头,接着是对CREATE TABLE语句其他rules。也可对未分区表进行分区操作。
查找分区表信息,
14.1.8.2 ALTER TABLE Online Operations in MySQL Cluster
14.1.8.4 ALTER TABLE Examples
14.1.9 ALTER TABLESPACE Syntax
ALTER TABLESPACE is useful only with Disk Data storage for MySQL Cluster.
14.1.10 ALTER VIEW Syntax
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
14.1.11 CREATE DATABASE Syntax
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
14.1.12 CREATE EVENT Syntax
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
14.1.13 CREATE FUNCTION Syntax
14.1.14 CREATE INDEX Syntax
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
14.1.15 CREATE LOGFILE GROUP Syntax
14.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax
14.1.17 CREATE SERVER Syntax
14.1.18 CREATE TABLE Syntax
14.1.18.1 CREATE TABLE ... LIKE Syntax
14.1.19 CREATE TABLESPACE Syntax
14.2.1 CALL Syntax
14.2.2 DELETE Syntax
14.2.3 DO Syntax
14.2.4 HANDLER Syntax
14.2.5 INSERT Syntax
14.2.7 LOAD XML Syntax
14.2.9 SELECT Syntax
14.2.9.2 JOIN Syntax
In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other).
Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.
14.2.9.3 UNION Syntax
14.2.10 Subquery Syntax
s A subquery is a SELECT statement within another statement.
14.2.10.1 The Subquery as Scalar Operand
note:For the subquery just shown, if t1 were empty, the result would be NULL even though s2 is NOT NULL.
14.2.10.2 Comparisons Using Subqueries
14.2.11 UPDATE Syntax
14.3 MySQL Transactional and Locking Statements
14.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax
14.3.5 LOCK TABLES and UNLOCK TABLES Syntax
14.3.6 SET TRANSACTION Syntax
SET [GLOBAL | SESSION] TRANSACTION
transaction_characteristic [, transaction_characteristic] ...
transaction_characteristic:
ISOLATION LEVEL level
| READ WRITE
| READ ONLY
level:
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
14.3.7 XA Transactions
14.4 Replication Statements
14.6.3 DECLARE Syntax
14.6.5 Flow Control Statements
14.6.7.3 GET DIAGNOSTICS Syntax
14.7 Database Administration Statements
14.7.1 Account Management Statements