Mysql手册—SQLStatementSyntax

2022-06-10 17:14:29

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

14.7.1.1 ALTER USER Syntax
14.7.1.2 CREATE USER Syntax
14.7.1.3 DROP USER Syntax
14.7.1.4 GRANT Syntax
14.7.1.5 RENAME USER Syntax
14.7.1.6 REVOKE Syntax
14.7.1.7 SET PASSWORD Syntax

码农公寓

14.1.1 ALTER DATABASE Syntax,可用于修改数据库字符集和校验规则

14.1.2 ALTER EVENT Syntax 事件修改语法

14.1.3 ALTER LOGFILE GROUP Syntax

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 对表本身的相关操作，如增加列，删除列，改名，改存储引擎，改分区之类的。

14.1.8.1 ALTER TABLE Partition Operations

14.1.8.2 ALTER TABLE Online Operations in MySQL Cluster

14.1.8.4 ALTER TABLE Examples

14.1.9 ALTER TABLESPACE Syntax

14.1.10 ALTER VIEW Syntax

14.1.11 CREATE DATABASE Syntax

14.1.12 CREATE EVENT Syntax

14.1.13 CREATE FUNCTION Syntax

14.1.14 CREATE INDEX Syntax

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

14.2.10.1 The Subquery as Scalar Operand

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

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

14.7.5 SHOW Syntax

相关文章

也可参考：http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

14.1.18 CREATE TABLE 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.