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)

Create

比如：

1 CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
2     -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

View Code

 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

Alter

1. 添加一行的

比如添加一个AUTO_INCREMENT integer 行c

1 ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
2   ADD PRIMARY KEY (c);

View Code

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);

View Code

3. 修改名称

ALTER TABLE t1 RENAME t2;

4. 删除一行

ALTER TABLE t2 DROP COLUMN c;