SQL常用命令
Recently, having learning MySQL, I try to make a conclusion:
I. 创建一个表Table
1 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 2 [(create_definition,...)] 3 [partition_options] 4 [table_options] [select_statement] 5 6 Or: 7 8 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 9 [(] LIKE old_tbl_name [)]; 10 11 create_definition: 12 column_definition 13 | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) 14 | KEY [index_name] [index_type] (index_col_name,...) 15 | INDEX [index_name] [index_type] (index_col_name,...) 16 | [CONSTRAINT [symbol]] UNIQUE [INDEX] 17 [index_name] [index_type] (index_col_name,...) 18 | FULLTEXT [INDEX] [index_name] (index_col_name,...) 19 [WITH PARSER parser_name] 20 | SPATIAL [INDEX] [index_name] (index_col_name,...) 21 | [CONSTRAINT [symbol]] FOREIGN KEY 22 [index_name] (index_col_name,...) [reference_definition] 23 | CHECK (expr) 24 25 column_definition: 26 col_name type [NOT NULL | NULL] [DEFAULT default_value] 27 [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] 28 [COMMENT 'string'] [reference_definition] 29 30 type: 31 TINYINT[(length)] [UNSIGNED] [ZEROFILL] 32 | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] 33 | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] 34 | INT[(length)] [UNSIGNED] [ZEROFILL] 35 | INTEGER[(length)] [UNSIGNED] [ZEROFILL] 36 | BIGINT[(length)] [UNSIGNED] [ZEROFILL] 37 | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] 38 | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] 39 | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] 40 | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] 41 | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] 42 | DATE 43 | TIME 44 | TIMESTAMP 45 | DATETIME 46 | YEAR 47 | CHAR(length) [BINARY | ASCII | UNICODE] 48 | VARCHAR(length) [BINARY] 49 | BINARY(length) 50 | VARBINARY(length) 51 | TINYBLOB 52 | BLOB 53 | MEDIUMBLOB 54 | LONGBLOB 55 | TINYTEXT [BINARY] 56 | TEXT [BINARY] 57 | MEDIUMTEXT [BINARY] 58 | LONGTEXT [BINARY] 59 | ENUM(value1,value2,value3,...) 60 | SET(value1,value2,value3,...) 61 | spatial_type 62 63 index_col_name: 64 col_name [(length)] [ASC | DESC] 65 66 reference_definition: 67 REFERENCES tbl_name [(index_col_name,...)] 68 [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] 69 [ON DELETE reference_option] 70 [ON UPDATE reference_option] 71 72 reference_option: 73 RESTRICT | CASCADE | SET NULL | NO ACTION 74 75 table_options: table_option [table_option] ... 76 77 table_option: 78 [TABLESPACE tablespace_name STORAGE DISK] 79 ENGINE [=] engine_name 80 | AUTO_INCREMENT [=] value 81 | AVG_ROW_LENGTH [=] value 82 | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] 83 | CHECKSUM [=] {0 | 1} 84 | COMMENT [=] 'string' 85 | CONNECTION [=] 'connect_string' 86 | MAX_ROWS [=] value 87 | MIN_ROWS [=] value 88 | PACK_KEYS [=] {0 | 1 | DEFAULT} 89 | PASSWORD [=] 'string' 90 | DELAY_KEY_WRITE [=] {0 | 1} 91 | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 92 | UNION [=] (tbl_name[,tbl_name]...) 93 | INSERT_METHOD [=] { NO | FIRST | LAST } 94 | DATA DIRECTORY [=] 'absolute path to directory' 95 | INDEX DIRECTORY [=] 'absolute path to directory' 96 97 partition_options: 98 PARTITION BY 99 [LINEAR] HASH(expr) 100 | [LINEAR] KEY(column_list) 101 | RANGE(expr) 102 | LIST(expr) 103 [PARTITIONS num] 104 [ SUBPARTITION BY 105 [LINEAR] HASH(expr) 106 | [LINEAR] KEY(column_list) 107 [SUBPARTITIONS num] 108 ] 109 [(partition_definition) [, (partition_definition)] ...] 110 111 partition_definition: 112 PARTITION partition_name 113 [VALUES {LESS THAN (expr) | MAXVALUE | IN (value_list)}] 114 [[STORAGE] ENGINE [=] engine-name] 115 [COMMENT [=] 'comment_text' ] 116 [DATA DIRECTORY [=] 'data_dir'] 117 [INDEX DIRECTORY [=] 'index_dir'] 118 [MAX_ROWS [=] max_number_of_rows] 119 [MIN_ROWS [=] min_number_of_rows] 120 [TABLESPACE [=] (tablespace_name)] 121 [NODEGROUP [=] node_group_id] 122 [(subpartition_definition) [, (subpartition_definition)] ...] 123 124 subpartition_definition: 125 SUBPARTITION logical_name 126 [[STORAGE] ENGINE [=] engine-name] 127 [COMMENT [=] 'comment_text' ] 128 [DATA DIRECTORY [=] 'data_dir'] 129 [INDEX DIRECTORY [=] 'index_dir'] 130 [MAX_ROWS [=] max_number_of_rows] 131 [MIN_ROWS [=] min_number_of_rows] 132 [TABLESPACE [=] (tablespace_name)] 133 [NODEGROUP [=] node_group_id] 134 135 select_statement: 136 [IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
比如：
1 CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), 2 -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
One of my Table :
II. 修改
1 ALTER [IGNORE] TABLE tbl_name 2 alter_specification [, alter_specification] ... 3 4 alter_specification: 5 ADD [COLUMN] column_definition [FIRST | AFTER col_name ] 6 | ADD [COLUMN] (column_definition,...) 7 | ADD INDEX [index_name] [index_type] (index_col_name,...) 8 | ADD [CONSTRAINT [symbol]] 9 PRIMARY KEY [index_type] (index_col_name,...) 10 | ADD [CONSTRAINT [symbol]] 11 UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) 12 | ADD FULLTEXT [INDEX] [index_name] (index_col_name,...) 13 [WITH PARSER parser_name] 14 | ADD SPATIAL [INDEX] [index_name] (index_col_name,...) 15 | ADD [CONSTRAINT [symbol]] 16 FOREIGN KEY [index_name] (index_col_name,...) 17 [reference_definition] 18 | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 19 | CHANGE [COLUMN] old_col_name column_definition 20 [FIRST|AFTER col_name] 21 | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] 22 | DROP [COLUMN] col_name 23 | DROP PRIMARY KEY 24 | DROP INDEX index_name 25 | DROP FOREIGN KEY fk_symbol 26 | DISABLE KEYS 27 | ENABLE KEYS 28 | RENAME [TO] new_tbl_name 29 | ORDER BY col_name 30 | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] 31 | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] 32 | DISCARD TABLESPACE 33 | IMPORT TABLESPACE 34 | table_options 35 | partition_options 36 | ADD PARTITION (partition_definition) 37 | DROP PARTITION partition_names 38 | COALESCE PARTITION number 39 | REORGANIZE PARTITION partition_names INTO (partition_definitions) 40 | ANALYZE PARTITION partition_names 41 | CHECK PARTITION partition_names 42 | OPTIMIZE PARTITION partition_names 43 | REBUILD PARTITION partition_names 44 | REPAIR PARTITION partition_names 45 | REMOVE PARTITIONING
1. 添加一行的
比如添加一个AUTO_INCREMENT integer 行c
1 ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, 2 ADD PRIMARY KEY (c);
2. 修改一行
比如，将行a从INTEGER改为TINYINT NOT NULL（行名不变），同时将行b从CHAR(10)改为CHAR(20)，并将其改名为c：
1 ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
3. 修改名称
ALTER TABLE t1 RENAME t2;
4. 删除一行
ALTER TABLE t2 DROP COLUMN c;
