MySQL Basics Tutorial 14 - SQL Syntax Data Definition Statement DDL
黄舟
Release： 2017-02-24 11:57:43
Original
1260 people have browsed it
1. ALTER DATABASE syntax
ALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ...alter_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
Copy after login
ALTER DATABASE is used to change the global characteristics of the database. These properties are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need ALTER permission on the database.
CHARACTER SET clause is used to change the default database character set. The COLLATE clause is used to change the default database ordering.
The database name can be ignored. At this time, the statement corresponds to the default database. ALTER SCHEMA can also be used.
2. ALTER TABLE syntax
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
| partition_options
| ADD PARTITION partition_definition
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_names
Copy after login
ALTER TABLE is used to change the structure of the original table. For example, you can add or delete columns, create or delete indexes, change the type of existing columns, or rename columns or tables. You can also change the table's comments and the table's type.
Among the changes allowed, the syntax of many clauses is similar to the syntax of the clauses in CREATE TABLE. These include table_options modifications, options include ENGINE, AUTO_INCREMENT and AVG_ROW_LENGTH, etc.
The storage engine does not support some operations. If these operations are performed, a warning will appear. These warnings can be displayed using SHOW WARNINGS.
If you use ALTER TABLE to change the column specification, but DESCRIBE tbl_name prompts you that the column specification has not changed, it may be because MySQL ignored the changes you made. For example, if you try to change a VARCHAR column to a CHAR column, MySQL will still use VARCHAR if the table contains other variable-length columns.
When ALTER TABLE is running, it will temporarily copy the original table, make changes on the copy, delete the original table, and then rename the new table. When ALTER TABLE is executed, other users can read the original table, but updates and modifications to the table will be delayed until the new table is generated. After the new table is generated, these updates and modifications will be automatically transferred to the new table.
Note that if you use options other than RENAME when executing ALTER TABLE, MySQL will create a temporary table. MySQL does this even if the data does not need to be copied (for example, when you change the name of a column). For MyISAM tables, you can speed up index re-creation (the slowest part of the change process) by setting the myisam_sort_buffer_size system variable to a higher value.
· To use ALTER TABLE, you need to obtain ALTER, INSERT and CREATE permissions on the table.
· IGNORE is an extension of MySQL relative to standard SQL. If there are duplicate keywords in the new table, or a warning occurs when STRICT mode is started, use IGNORE to control the operation of ALTER TABLE. If IGNORE is not specified, when a duplicate keyword error occurs, the copy operation is abandoned and returns to the previous step. If IGNORE is specified, for rows with duplicate keywords, only the first row is used, and other conflicting rows are deleted. Furthermore, the error value is corrected to make it as close to the correct value as possible.
· You can write multiple ADD, ALTER, DROP and CHANGE clauses in one ALTER TABLE statement, separated by commas. This is an extension of MySQL relative to standard SQL. In standard SQL, each clause is allowed only once in each ALTER TABLE statement. For example, to cancel multiple columns in one statement:
mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
Copy after login
· CHANGE col_name, DROP col_name and DROP INDEX are extensions of MySQL relative to standard SQL.
· MODIFY is Oracle’s extension to ALTER TABLE.
· COLUMN is only an optional item and can be ignored.
· If you use ALTER TABLE tbl_name RENAME TO new_tbl_name and there are no other options, MySQL will only match the file corresponding to tabletbl_name Rename. There is no need to create a temporary table. (You can also use the RENAME TABLE statement to rename the table.)
· column_definition clause uses the same syntax as the ADD and CHANGE clauses in CREATE TABLE. Note that this syntax includes column names, not just column types.
· You can use the CHANGE old_col_namecolumn_definition clause to rename a column. When renaming, you need to give the old and new column names and the current type of the column. For example: to change the name of an INTEGER column from a to b, you need to do the following:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
Copy after login
If you want to change the type of the column but not the name, the CHANGE syntax still requires the old and new column names , even though the old and new column names are the same. For example:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
Copy after login
You can also use MODIFY to change the column type. There is no need to rename it at this time:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
Copy after login
· If you use CHANGE or MODITY to shorten the column length, there are index, and the shortened column length is less than the index length, MySQL will automatically shorten the index length.
· When you use CHANGE or MODIFY to change the type of a column, MySQL will try its best to convert the original column value into the new type.
· You can use FIRST or AFTER col_name to add a column at a specific position in a table row. Columns are added last by default. You can also use FIRST and AFTER in a CHANGE or MODIFY statement.
· AFTER COLUMN用于指定列的新默认值，或删除旧的默认值。如果旧的默认值被删除同时列值为NULL，则新的默认值为NULL。如果列值不能为NULL，MySQL会指定一个默认值。
CREATE TABLE t1 (
id INT,
year_col INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999)
);
Copy after login
您可以在表中增加一个新的分区p3，该分区用于储存小于2002的值。添加方法如下：
ALTER TABLE t1 ADD PARTITION p3 VALUES LESS THAN (2002);
Copy after login
注释：您不能使用ALTER TABLE向一个没有进行分区的表添加分区。
DROP PARTITION用于取消一个或多个RANGE或LIST分区。此命令不能用于HASH或KEY 分区；用于这两个分区时，应使用COALESCE PARTITION（见后）。如果被取消的分区其名称列于partition_names清单中，则储存在此分区中的数据也被取消。例如，如果以前已定义的表t1，您可以采用如下方法取消名称为p0和p1的分区：
如果表已存在，则使用关键词IF NOT EXISTS可以防止发生错误。注意，原有表的结构与CREATE TABLE语句中表示的表的结构是否相同，这一点没有验证。注释：如果您在CREATE TABLE...SELECT语句中使用IF NOT EXISTS，则不论表是否已存在，由SELECT部分选择的记录都会被插入。
Archive storage engine. See "ARCHIVE storage engine".
BDB
Transaction-safe table with page locking. Also known as BerkeleyDB. See "BDB (BerkeleyDB) Storage Engine".
CSV
Table with values separated by commas. See "CSV Storage Engine.
EXAMPLE
Example Engine. See "EXAMPLE Storage Engine".
FEDERATED
Storage engines that can access remote tables. See "FEDERATED Storage Engine"
#HEAP
See "MEMORY (HEAP) Storage Engine". .
##(
OBSOLETE
)ISAMThis engine is not available in MySQL 5.1. If you are upgrading to MySQL 5.1 from a previous version. Original ISAM tables should be converted to MyISAM tables before upgrading. See
Storage Engines and Table Types
#InnoDB. Transaction-safe table with row locking and foreign keys. See "InnoDB Storage Engine"
##MEMORY
Data of this table type is only stored in memory. Known as HEAP in earlier MySQL versions.)
##MERGE
A collection of MyISAM tables, also known as MRG_MyISAM. ".
MyISAM
Binary lightweight storage engine, this engine is the default storage engine used by MySQL. See "MyISAM Storage Engine".
##NDBCLUSTER
Clustered table, fault-tolerant table, also called NDB. See
MySQL Cluster
.
If the specified storage engine cannot be used, MySQL uses MyISAM instead. For example, if a table definition includes the ENGINE=BDB option, but the MySQL server does not support BDB tables, the table is created as a MyISAM table. This way, you can set up replication if you have transactional tables on the master, but create non-interactive tables on the slave (for speed). In MySQL 5.1, a warning will appear if the storage engine convention is not followed.
Other table options are used to optimize the properties of the table. In most cases, you do not need to specify table options. These options apply to all storage engines unless otherwise noted:
· AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. In MySQL 5.1, this option only applies to MyISAM and MEMORY tables. InnoDB also supports this option. If the engine does not support the AUTO_INCREMENT table option, then to set the engine's first auto-increment value, a "false" row needs to be inserted. The value of the row is one less than the value after creating the table, and then the fake row is deleted.
For engines that support the AUTO_INCREMENT table option in the CREATE TABLE statement, you can also use ALTER TABLE tbl_nameAUTO_INCREMENT = n to reset the AUTO_INCREMENT value.
·AVG_ROW_LENGTH
Approximate average row length in the table. This setting is only required for large tables with variable-sized records.
When creating a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to determine how large the resulting table is. If one option is not specified, the maximum table size is 65,536TB of data. (If the operating system does not support such large files, the table size is capped at the operating system's limits.) If you want to reduce the pointer size so that the index is smaller and faster, and you don't need large files, then you The default pointer size can be reduced by setting the myisam_data_pointer_size system variable. If you want all tables to grow beyond the default limits, and would like the tables to be slightly slower and slightly larger, you can increase the default pointer size by setting this variable.
·[DEFAULT] CHARACTER SET
is used to specify a default character set for the table. CHARSET is a synonym for CHARACTER SET.
For CHARACTER SET.
· COLLATE
is used to specify a default order for the table.
·CHECKSUM
If you want MySQL to perform real-time checksums on all rows at any time (that is, after the table changes, MySQL automatically updates the checksums), you should set this to 1. Doing so will make table updates slightly slower, but it will be easier to find damaged tables. The CHECKSUM TABLE statement reports checksums (MyISAM only).
·COMMENT
The comment of the table, up to 60 characters.
· CONNECTION
The connection string of the FEDERATED table. (Note: Older versions of MySQL use the COMMENT option for connection strings.
·MAX_ROWS
The maximum number of rows you intend to store in the table. This is not a hard limit , more like an instruction statement indicating that the table must be able to store at least this many rows
·MIN_ROWS
The minimum number of rows you intend to store in the table. ##·PACK_KEYS
If you want the index to be smaller, set this option to 1. This usually makes updates slower, while setting the option to 0 suppresses all keywords. Compression. When this option is set to DEFAULT, the storage engine only compresses long CHAR or VARCHAR columns (MyISAM only)
If you do not use PACK_KEYS, the default operation is to compress only strings, but not compress them. Numbers. If you use PACK_KEYS=1, numbers are also compressed.
When compressing binary numeric keywords, MySQL uses prefix compression:
o Each keyword is required. Extra bytes to indicate how many bytes in the previous keyword are the same as the next keyword
o Pointers to rows are stored after the keyword in big-endian order for improvements. Compression effect.
This means that if there are many identical keywords in two consecutive lines, subsequent "identical" keywords usually only occupy two bytes (including the pointer to the line). Compare this to the normal case where subsequent keys occupy storage_size_for_key + pointer_size (the pointer size is usually 4). However, prefix compression only benefits if many numbers are identical. And keywords cannot contain NULL values, so each keyword uses one more byte (in this case, the byte to store the length of the compressed keyword and the byte used to mark whether the keyword is NULL. are the same bytes.)
·PASSWORD
Use password to encrypt the .frm file. In the standard MySQL version, this option has no effect.
## If you want to delay updating the keyword until the table is closed, set this to 1 (MyISAM only) ·ROW_FORMAT Define how each row should be stored. Currently, this option only applies to MyISAM tables. For static rows or variable-length rows, this option value can be FIXED or DYNAMIC # to set the type. ##
o HASH（expr）：用于混编一个或多个列，创建一个关键字，用于放置行，并确定行的位置。expr是一个表达式，使用一个或多个表中的列。该表达式可以是任何能够生成单一整值的合法的MySQL表达式（包括MySQL函数）。例如，这些都是有效的CREATE TABLE语句，语句中使用了PARTITION BY HASH：
CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR KEY(col3)
PARTITIONS 5;
Copy after login
VALUES LESS THAN或VALUES IN子句不能和PARTITION BY KEY一起使用。
o RANGE：在此情况下，expr使用一套VALUES LESS THAN操作符显示了某一范围内的值。当使用范围分区时，您必须使用VALUES LESS THAN定义至少一个分区。VALUES IN不能和范围分区一起使用。
VALUES LESS THAN可以与一个文字值同时使用，或者与一个可以求算单一值的表达式同时使用。
举例说明，假设您有一个表，您希望采用以下方法对包含年份值的一列进行分区：
分区编号：
年份范围：
0
1990以前
1
1991 - 1994
2
1995 - 1998
3
1999 - 2002
4
2003 - 2005
5
2006年以后
采用这种分区方法的表可以通过如下CREATE TABLE语句实现：
CREATE TABLE t1 (
year_col INT,
some_data INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999),
PARTITION p3 VALUES LESS THAN (2002),
PARTITION p4 VALUES LESS THAN (2006),
PARTITION p5 VALUES LESS THAN MAXVALUE
);
Copy after login
PARTITION ... VALUES LESS THAN ...语句按顺序执行。VALUES LESS THAN MAXVALUE的作用是指定大于最大值的“其余”的值。
注意，VALUES LESS THAN子句按顺序执行，执行方式类似于switch ... case语段的一部分（许多编程语言，如C, Java和PHP也如此）。也就是说，子句必须按照这样一种方法排列，每一个后续的VALUES LESS THAN中指定的上限值大于前一个VALUES LESS THAN中指定的上限值，并在清单的最后加一个参照性的MAXVALUE。
VALUES IN与一系列的值同时使用。举例说明，您可以创建如下的分区方法：
CREATE TABLE client_firms (
id INT,
name VARCHAR(35)
)
PARTITION BY RANGE (id) (
PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);
Copy after login
当前，与VALUES IN...同时使用的值必须只包含整数值。
（因为此表只使用VALUES IN表达式进行分区，您也可以用PARTITION BY LIST代替，而不是使用PARTITION BY RANGE。请参见下一条。）
在使用VALUES LESS THAN或VALUES IN情况下，每个分区使用PARTITION name定义，此处name是分区的标识名，后面接VALUES...子句。
o LIST(expr)：当根据含有一系列限定性值（例如州代码或国家代码）的列进行分区时使用。在这种情况下，所有与特定的州或国家有关的记录都被分配到一个单一分区中，或者可以预留出一个分区，用于一系列特定的州或国家。LIST(expr)与RANGE类似，除了一点以外，即只有VALUES IN可以被用于为每个分区指定值。
当使用清单分区时，您必须使用VALUES IN定义至少一个分区。VALUES LESS THAN不能与PARTITION BY LIST一起使用。
o 分区数目可以使用PARTITION num子句，自选进行指定，此处，num是分区的数目。如果本子句和其它PARTITION子句同时使用，则num必须与使用PARTITION子句说明的分区的总数相等。
o VALUE子句：对于范围分区，每个分区必须包括一个VALUES LESS THAN子句；对于清单分区，您必须为每个分区指定一个VALUES IN子句。本子句用于确定哪些行将被存储到此分区中。
o 自选的COMMENT子句可以用于描述分区。注释必须加单引号。举例说明：
o COMMENT = 'Data for the years previous to 1999'
Copy after login
o DATA DIRECTORY和INDEX DIRECTORY可以被用于指示本分区的数据和索引各自的存储位置的目录。data_dir和index_dir都必须是绝对系统路径。例如：
CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
PARTITION p1999 VALUES IN (1995, 1999, 2003) DATA DIRECTORY = '/var/appdata/95/data' INDEX DIRECTORY = '/var/appdata/95/idx',
PARTITION p2000 VALUES IN (1996, 2000, 2004) DATA DIRECTORY = '/var/appdata/96/data' INDEX DIRECTORY = '/var/appdata/96/idx',
PARTITION p2001 VALUES IN (1997, 2001, 2005) DATA DIRECTORY = '/var/appdata/97/data' INDEX DIRECTORY = '/var/appdata/97/idx',
PARTITION p2000 VALUES IN (1998, 2002, 2006) DATA DIRECTORY = '/var/appdata/98/data' INDEX DIRECTORY = '/var/appdata/98/idx'
);
Copy after login
DATA DIRECTORY和INDEX DIRECTORY的操作方法与CREATE TABLE语句中的table_option子句的操作方法一样。此table_option子句用于位于MyISAM表管理程序下的各表。
o MAX_ROWS和MIN_ROWS分别用于将被存储在分区中的行数目最大值和行数目最小值。max_number_of_rows和min_number_of_rows的值必须为正整数。和具有同样名称的桌面选项一样，max_number_of_rows和min_number_of_rows只作为对服务器的“建议”值，并不是硬性限值。
o 自选的TABLESPACE子句可以用于为分区指定一个桌面空间。仅用于MySQL Cluster。
o 自选的[STORAGE] ENGINE子句可以把本分区中表的类型改为指定的类型。表的类型可以是本MySQL服务器支持的所有类型。STORAGE关键字和等号(=)均为自选项。如果没有使用此选项设置分区存储引擎，则适用于整个表的引擎可以用于此分区。
CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;
Copy after login
您也可以明确地为一个已生成的列指定类型：
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
