SQL语法基础之CREATE语句
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.查看帮助信息
1>.使用“？”来查看MySQL命令的帮助信息
1mysql> ? CREATE #这里告诉我们CREATE命令需要和那些命令一起使用 2Many help items for your request exist. 3To make a more specific request, please type 'help <item>', 4where <item> is one of the following 5topics: 6 CREATE DATABASE 7 CREATE EVENT 8 CREATE FUNCTION 9 CREATE FUNCTION UDF 10 CREATE INDEX 11 CREATE PROCEDURE 12 CREATE RESOURCE GROUP 13 CREATE ROLE 14 CREATE SERVER 15 CREATE SPATIAL REFERENCE SYSTEM 16 CREATE TABLE 17 CREATE TABLESPACE 18 CREATE TRIGGER 19 CREATE USER 20 CREATE VIEW 21 SHOW 22 SHOW CREATE DATABASE 23 SHOW CREATE EVENT 24 SHOW CREATE FUNCTION 25 SHOW CREATE PROCEDURE 26 SHOW CREATE TABLE 27 SHOW CREATE USER 28 SPATIAL 29 30mysql>
2>.查看CREATE DATABASE命令的帮助信息
1mysql> ? CREATE DATABASE 2Name: 'CREATE DATABASE' 3Description: 4Syntax: 5CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 6 [create_specification] ... 7 8create_specification: 9 [DEFAULT] CHARACTER SET [=] charset_name 10 | [DEFAULT] COLLATE [=] collation_name 11 12CREATE DATABASE creates a database with the given name. To use this 13statement, you need the CREATE privilege for the database. CREATE 14SCHEMA is a synonym for CREATE DATABASE. 15 16URL: http://dev.mysql.com/doc/refman/8.0/en/create-database.html #这里是官方给的帮助文档 17 18 19mysql>
3>.查询帮助时关键点剖析
刚刚学习MySQL的小伙伴，可能知道使用问好（“？”）可以查询命令的使用方法，但是获取到帮助信息后，看不懂该怎么用。别着急，我们把帮助信息细细的揣摩一下就明白咋用了，也方便我在下面执行相应的SQL语句时，大家不会产生过多歧义。首先我们以上面的查看“CREATE DATABASE”命令的帮助信息为例，简要说明一下该如何查看帮助信息：
第一：没有使用括号包裹起来的字段是必须写的。
第二：使用大括号（“{ }”）包裹起来的字段是必须写的，只不过我们需要从大括号中用管道（“|”）分隔的各个字段中选取相应一个来使用，例如“{DATABASE | SCHEMA}” 就表示我们必须选一个字段，要么选择DATABASE，要么选择SCHEME，不可以不选哟！
第三：中括号的字段是可以不写的，比如“[IF NOT EXISTS] ”这个语句咱们就是可以不写，不过建议大家写上，可以避免出错，它是一个IF判断语句。
二.CREATE DATABASE
1>.CREATE DATABASE 语句是在MySQL实力上创建一个指定名称的数据库，CREATE SCHEMA语句的语意和CREATE DATABASE是一样的。
1mysql> ? CREATE DATABASE 2Name: 'CREATE DATABASE' 3Description: 4Syntax: 5CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 6 [create_specification] ... 7 8create_specification: 9 [DEFAULT] CHARACTER SET [=] charset_name 10 | [DEFAULT] COLLATE [=] collation_name 11 12CREATE DATABASE creates a database with the given name. To use this 13statement, you need the CREATE privilege for the database. CREATE 14SCHEMA is a synonym for CREATE DATABASE. 15 16URL: http://dev.mysql.com/doc/refman/8.0/en/create-database.html 17 18 19mysql>
2>.当创建当数据本身存在没有写明“IF NOT EXISTS”子句是，创建数据库当语句会报错
3>.create_specification子句指明创建数据库的属性，并且存储在db.opt文件中
1• Character set属性指明此数据库的默认字符集 2 • Collate属性指明此数据库的默认排序规则
4>.创建后的数据库在数据文件中所在目录会创建一个子句的文件目录，用来包含后续创建的表文件。
5>.创建数据库案例展示
1mysql> SHOW DATABASES; 2+--------------------+ 3| Database | 4+--------------------+ 5| information_schema | 6| mysql | 7| performance_schema | 8| sys | 9+--------------------+ 104 rows in set (0.00 sec) 11 12mysql> 13mysql> CREATE DATABASE yinzhengjie CHARACTER SET = utf8; 14Query OK, 1 row affected, 1 warning (0.01 sec) 15 16mysql> 17mysql> SHOW DATABASES; 18+--------------------+ 19| Database | 20+--------------------+ 21| information_schema | 22| mysql | 23| performance_schema | 24| sys | 25| yinzhengjie | 26+--------------------+ 275 rows in set (0.00 sec) 28 29mysql> 30mysql> SHOW CREATE DATABASE yinzhengjie; 31+-------------+----------------------------------------------------------------------+ 32| Database | Create Database | 33+-------------+----------------------------------------------------------------------+ 34| yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8 */ | 35+-------------+----------------------------------------------------------------------+ 361 row in set (0.00 sec) 37 38mysql>
创建一个默认字符集为utf8的数据库（mysql> CREATE DATABASE yinzhengjie CHARACTER SET = utf8;）
1mysql> SHOW DATABASES; 2+--------------------+ 3| Database | 4+--------------------+ 5| information_schema | 6| mysql | 7| performance_schema | 8| sys | 9| yinzhengjie | 10+--------------------+ 115 rows in set (0.00 sec) 12 13mysql> 14mysql> SHOW CREATE DATABASE yinzhengjie; 15+-------------+----------------------------------------------------------------------+ 16| Database | Create Database | 17+-------------+----------------------------------------------------------------------+ 18| yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8 */ | 19+-------------+----------------------------------------------------------------------+ 201 row in set (0.00 sec) 21 22mysql> 23mysql> CREATE DATABASE yinzhengjie CHARACTER SET = gbk; 24ERROR 1007 (HY000): Can't create database 'yinzhengjie'; database exists 25mysql> 26mysql> CREATE DATABASE IF NOT EXISTS yinzhengjie CHARACTER SET = gbk; 27Query OK, 1 row affected, 1 warning (0.00 sec) 28 29mysql> 30mysql> SHOW CREATE DATABASE yinzhengjie; 31+-------------+----------------------------------------------------------------------+ 32| Database | Create Database | 33+-------------+----------------------------------------------------------------------+ 34| yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8 */ | 35+-------------+----------------------------------------------------------------------+ 361 row in set (0.00 sec) 37 38mysql>
创建数据库时，如果数据库存在就不创建，若不存在我们在创建，可避免MySQL的交互终端报错的情况！（mysql> CREATE DATABASE IF NOT EXISTS yinzhengjie CHARACTER SET = gbk;）
1温馨提示： 2 在MySQL5.7版本咱们也可以直接通过mkdir的操作系统命令在数据目录创建文件夹，则MySQL会识别为一个数据库，并在执行show databases命令时可以看到。 3 但是，在MySQL8.0版本咱们再通过mkdir的操作系统命令在数据目录创建文件夹，就不会被识别了哟！
三.CREATE TABLE
1>.查看CREATE TABLE的帮助信息
1mysql> ? CREATE TABLE 2Name: 'CREATE TABLE' 3Description: 4Syntax: 5CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 6 (create_definition,...) 7 [table_options] 8 [partition_options] 9 10CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 11 [(create_definition,...)] 12 [table_options] 13 [partition_options] 14 [IGNORE | REPLACE] 15 [AS] query_expression 16 17CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 18 { LIKE old_tbl_name | (LIKE old_tbl_name) } 19 20create_definition: 21 col_name column_definition 22 | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) 23 [index_option] ... 24 | {INDEX|KEY} [index_name] [index_type] (key_part,...) 25 [index_option] ... 26 | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] 27 [index_name] [index_type] (key_part,...) 28 [index_option] ... 29 | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...) 30 [index_option] ... 31 | [CONSTRAINT [symbol]] FOREIGN KEY 32 [index_name] (col_name,...) reference_definition 33 | CHECK (expr) 34 35column_definition: 36 data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ] 37 [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] 38 [COMMENT 'string'] 39 [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] 40 [reference_definition] 41 | data_type [GENERATED ALWAYS] AS (expression) 42 [VIRTUAL | STORED] [NOT NULL | NULL] 43 [UNIQUE [KEY]] [[PRIMARY] KEY] 44 [COMMENT 'string'] 45 46data_type: 47 (see http://dev.mysql.com/doc/refman/8.0/en/data-types.html) 48 49key_part: {col_name [(length)] | (expr)} [ASC | DESC] 50 51index_type: 52 USING {BTREE | HASH} 53 54index_option: 55 KEY_BLOCK_SIZE [=] value 56 | index_type 57 | WITH PARSER parser_name 58 | COMMENT 'string' 59 | {VISIBLE | INVISIBLE} 60 61reference_definition: 62 REFERENCES tbl_name (key_part,...) 63 [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] 64 [ON DELETE reference_option] 65 [ON UPDATE reference_option] 66 67reference_option: 68 RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT 69 70table_options: 71 table_option [[,] table_option] ... 72 73table_option: 74 AUTO_INCREMENT [=] value 75 | AVG_ROW_LENGTH [=] value 76 | [DEFAULT] CHARACTER SET [=] charset_name 77 | CHECKSUM [=] {0 | 1} 78 | [DEFAULT] COLLATE [=] collation_name 79 | COMMENT [=] 'string' 80 | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'} 81 | CONNECTION [=] 'connect_string' 82 | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory' 83 | DELAY_KEY_WRITE [=] {0 | 1} 84 | ENCRYPTION [=] {'Y' | 'N'} 85 | ENGINE [=] engine_name 86 | INSERT_METHOD [=] { NO | FIRST | LAST } 87 | KEY_BLOCK_SIZE [=] value 88 | MAX_ROWS [=] value 89 | MIN_ROWS [=] value 90 | PACK_KEYS [=] {0 | 1 | DEFAULT} 91 | PASSWORD [=] 'string' 92 | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 93 | STATS_AUTO_RECALC [=] {DEFAULT|0|1} 94 | STATS_PERSISTENT [=] {DEFAULT|0|1} 95 | STATS_SAMPLE_PAGES [=] value 96 | TABLESPACE tablespace_name 97 | UNION [=] (tbl_name[,tbl_name]...) 98 99partition_options: 100 PARTITION BY 101 { [LINEAR] HASH(expr) 102 | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) 103 | RANGE{(expr) | COLUMNS(column_list)} 104 | LIST{(expr) | COLUMNS(column_list)} } 105 [PARTITIONS num] 106 [SUBPARTITION BY 107 { [LINEAR] HASH(expr) 108 | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) } 109 [SUBPARTITIONS num] 110 ] 111 [(partition_definition [, partition_definition] ...)] 112 113partition_definition: 114 PARTITION partition_name 115 [VALUES 116 {LESS THAN {(expr | value_list) | MAXVALUE} 117 | 118 IN (value_list)}] 119 [[STORAGE] ENGINE [=] engine_name] 120 [COMMENT [=] 'string' ] 121 [DATA DIRECTORY [=] 'data_dir'] 122 [INDEX DIRECTORY [=] 'index_dir'] 123 [MAX_ROWS [=] max_number_of_rows] 124 [MIN_ROWS [=] min_number_of_rows] 125 [TABLESPACE [=] tablespace_name] 126 [(subpartition_definition [, subpartition_definition] ...)] 127 128subpartition_definition: 129 SUBPARTITION logical_name 130 [[STORAGE] ENGINE [=] engine_name] 131 [COMMENT [=] 'string' ] 132 [DATA DIRECTORY [=] 'data_dir'] 133 [INDEX DIRECTORY [=] 'index_dir'] 134 [MAX_ROWS [=] max_number_of_rows] 135 [MIN_ROWS [=] min_number_of_rows] 136 [TABLESPACE [=] tablespace_name] 137 138query_expression: 139 SELECT ... (Some valid select or union statement) 140 141CREATE TABLE creates a table with the given name. You must have the 142CREATE privilege for the table. 143 144By default, tables are created in the default database, using the 145InnoDB storage engine. An error occurs if the table exists, if there is 146no default database, or if the database does not exist. 147 148For information about the physical representation of a table, see 149http://dev.mysql.com/doc/refman/8.0/en/create-table-files.html. 150 151URL: http://dev.mysql.com/doc/refman/8.0/en/create-table.html 152 153 154mysql>
2>.基本的建表语句
1mysql> 2mysql> SHOW DATABASES; 3+--------------------+ 4| Database | 5+--------------------+ 6| information_schema | 7| mysql | 8| performance_schema | 9| sys | 10| yinzhengjie | 11+--------------------+ 125 rows in set (0.00 sec) 13 14mysql> 15mysql> USE yinzhengjie; 16Database changed 17mysql> 18mysql> SELECT DATABASE(); 19+-------------+ 20| DATABASE() | 21+-------------+ 22| yinzhengjie | 23+-------------+ 241 row in set (0.00 sec) 25 26mysql> 27mysql> CREATE TABLE student(stu_id int,stu_name varchar(30)); 28Query OK, 0 rows affected (0.01 sec) 29 30mysql> 31mysql> SHOW TABLES; 32+-----------------------+ 33| Tables_in_yinzhengjie | 34+-----------------------+ 35| student | 36+-----------------------+ 371 row in set (0.00 sec) 38 39mysql> 40mysql> DESC student; 41+----------+-------------+------+-----+---------+-------+ 42| Field | Type | Null | Key | Default | Extra | 43+----------+-------------+------+-----+---------+-------+ 44| stu_id | int(11) | YES | | NULL | | 45| stu_name | varchar(30) | YES | | NULL | | 46+----------+-------------+------+-----+---------+-------+ 472 rows in set (0.00 sec) 48 49mysql>
在当前库中创建一个表（mysql> CREATE TABLE student(stu_id int,stu_name varchar(30));）
1[root@node110 ~]# mysql -uroot -pyinzhengjie 2mysql: [Warning] Using a password on the command line interface can be insecure. 3Welcome to the MySQL monitor. Commands end with ; or \g. 4Your MySQL connection id is 11 5Server version: 8.0.14 MySQL Community Server - GPL 6 7Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 8 9Oracle is a registered trademark of Oracle Corporation and/or its 10affiliates. Other names may be trademarks of their respective 11owners. 12 13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15mysql> 16mysql> SHOW DATABASES; 17+--------------------+ 18| Database | 19+--------------------+ 20| information_schema | 21| mysql | 22| performance_schema | 23| sys | 24| yinzhengjie | 25+--------------------+ 265 rows in set (0.00 sec) 27 28mysql> 29mysql> CREATE TABLE IF NOT EXISTS yinzhengjie.student2(stu_id int,stu_name varchar(30)); 30Query OK, 0 rows affected (0.01 sec) 31 32mysql> 33mysql> SELECT DATABASE(); 34+------------+ 35| DATABASE() | 36+------------+ 37| NULL | 38+------------+ 391 row in set (0.00 sec) 40 41mysql> 42mysql> USE yinzhengjie 43Reading table information for completion of table and column names 44You can turn off this feature to get a quicker startup with -A 45 46Database changed 47mysql> 48mysql> SELECT DATABASE(); 49+-------------+ 50| DATABASE() | 51+-------------+ 52| yinzhengjie | 53+-------------+ 541 row in set (0.00 sec) 55 56mysql> 57mysql> SHOW TABLES; 58+-----------------------+ 59| Tables_in_yinzhengjie | 60+-----------------------+ 61| student | 62| student2 | 63+-----------------------+ 642 rows in set (0.00 sec) 65 66mysql> DESC student2; 67+----------+-------------+------+-----+---------+-------+ 68| Field | Type | Null | Key | Default | Extra | 69+----------+-------------+------+-----+---------+-------+ 70| stu_id | int(11) | YES | | NULL | | 71| stu_name | varchar(30) | YES | | NULL | | 72+----------+-------------+------+-----+---------+-------+ 732 rows in set (0.00 sec) 74 75mysql>
在指定的数据库中创建对应的表（mysql> CREATE TABLE IF NOT EXISTS yinzhengjie.student2(stu_id int,stu_name varchar(30)); ）
3>.TEMPORARY
注意，TEMPORARY关键字表示创建的是临时表，临时表仅对本链接可见，另外的数据库链接不可见，当本链接断开时，临时表也被自动DROP掉。
1mysql> SELECT DATABASE(); 2+-------------+ 3| DATABASE() | 4+-------------+ 5| yinzhengjie | 6+-------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> SHOW TABLES; 11+-----------------------+ 12| Tables_in_yinzhengjie | 13+-----------------------+ 14| student | 15| student2 | 16+-----------------------+ 172 rows in set (0.00 sec) 18 19mysql> 20mysql> CREATE TEMPORARY TABLE student_temp(stu_tem_id int,stu_tem_name varchar(30)); #这里我们使用TEMPORARY关键字创建了一张临时表 21Query OK, 0 rows affected (0.00 sec) 22 23mysql> 24mysql> SHOW TABLES; 25+-----------------------+ 26| Tables_in_yinzhengjie | 27+-----------------------+ 28| student | 29| student2 | 30+-----------------------+ 312 rows in set (0.00 sec) 32 33mysql> INSERT INTO student_temp VALUES(1,'jason'); #我们往临时表中插入一条数据 34Query OK, 1 row affected (0.00 sec) 35 36mysql> 37mysql> SELECT * FROM student_temp; #很显然，数据往临时表中插入成功了，注意，如果此时另一个数据库链接在相同当数据库执行相同当查询语句是查不到数据当哟！能查询的仅限当前的数据库链接！ 38+------------+--------------+ 39| stu_tem_id | stu_tem_name | 40+------------+--------------+ 41| 1 | jason | 42+------------+--------------+ 431 row in set (0.00 sec) 44 45mysql> 46mysql>quit 47Bye 48[root@node110 ~]# mysql -uroot -pyinzhengjie 49mysql: [Warning] Using a password on the command line interface can be insecure. 50Welcome to the MySQL monitor. Commands end with ; or \g. 51Your MySQL connection id is 12 52Server version: 8.0.14 MySQL Community Server - GPL 53 54Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 55 56Oracle is a registered trademark of Oracle Corporation and/or its 57affiliates. Other names may be trademarks of their respective 58owners. 59 60Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 61 62mysql> USE yinzhengjie 63Reading table information for completion of table and column names 64You can turn off this feature to get a quicker startup with -A 65 66Database changed 67mysql> 68mysql> SELECT DATABASE(); 69+-------------+ 70| DATABASE() | 71+-------------+ 72| yinzhengjie | 73+-------------+ 741 row in set (0.00 sec) 75 76mysql> SELECT * FROM student_temp; #很显然，当创建临时表当那个数据库链接端口后再链接，临时表已经不存在了！ 77ERROR 1146 (42S02): Table 'yinzhengjie.student_temp' doesn't exist 78mysql> 79mysql>
mysql> CREATE TEMPORARY TABLE student_temp(stu_tem_id int,stu_tem_name varchar(30)); #这里我们使用TEMPORARY关键字创建了一张临时表
4>.LIKE
LIKE关键字表示基于另外一个表的定义复制一个新的空表，空表时尚的字段属性和索引都和原表相同。
1mysql> SELECT DATABASE(); 2+-------------+ 3| DATABASE() | 4+-------------+ 5| yinzhengjie | 6+-------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> SHOW TABLES; 11+-----------------------+ 12| Tables_in_yinzhengjie | 13+-----------------------+ 14| student | 15| student2 | 16+-----------------------+ 172 rows in set (0.00 sec) 18 19mysql> 20mysql> CREATE TABLE student3 LIKE student2; #咱们这里使用了LIKE关键字 21Query OK, 0 rows affected (0.01 sec) 22 23mysql> SHOW TABLES; 24+-----------------------+ 25| Tables_in_yinzhengjie | 26+-----------------------+ 27| student | 28| student2 | 29| student3 | 30+-----------------------+ 313 rows in set (0.00 sec) 32 33mysql> DESC student2; 34+----------+-------------+------+-----+---------+-------+ 35| Field | Type | Null | Key | Default | Extra | 36+----------+-------------+------+-----+---------+-------+ 37| stu_id | int(11) | YES | | NULL | | 38| stu_name | varchar(30) | YES | | NULL | | 39+----------+-------------+------+-----+---------+-------+ 402 rows in set (0.00 sec) 41 42mysql> 43mysql> DESC student3; 44+----------+-------------+------+-----+---------+-------+ 45| Field | Type | Null | Key | Default | Extra | 46+----------+-------------+------+-----+---------+-------+ 47| stu_id | int(11) | YES | | NULL | | 48| stu_name | varchar(30) | YES | | NULL | | 49+----------+-------------+------+-----+---------+-------+ 502 rows in set (0.00 sec) 51 52mysql>
mysql> CREATE TABLE student3 LIKE student2; #咱们这里使用了LIKE关键字
1mysql> SHOW CREATE TABLE student2; 2+----------+---------------------------------------------------------------------------------------------------------------------------------------+ 3| Table | Create Table | 4+----------+---------------------------------------------------------------------------------------------------------------------------------------+ 5| student2 | CREATE TABLE `student2` ( 6 `stu_id` int(11) DEFAULT NULL, 7 `stu_name` varchar(30) DEFAULT NULL 8) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 9+----------+---------------------------------------------------------------------------------------------------------------------------------------+ 101 row in set (0.00 sec) 11 12mysql> 13mysql> SHOW CREATE TABLE student3; 14+----------+---------------------------------------------------------------------------------------------------------------------------------------+ 15| Table | Create Table | 16+----------+---------------------------------------------------------------------------------------------------------------------------------------+ 17| student3 | CREATE TABLE `student3` ( 18 `stu_id` int(11) DEFAULT NULL, 19 `stu_name` varchar(30) DEFAULT NULL 20) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 21+----------+---------------------------------------------------------------------------------------------------------------------------------------+ 221 row in set (0.00 sec) 23 24mysql>
两张表的建表语句都相同哟！（mysql> SHOW CREATE TABLE student3;）
5>.CREATE TABLE ... AS SELECT 语句
表示创建表的同时将SELECT的查询结果数据插入到表中，但索引和主外键信息都不会同步过来
1mysql> SHOW TABLES; 2+-----------------------+ 3| Tables_in_yinzhengjie | 4+-----------------------+ 5| student | 6| student2 | 7| student3 | 8+-----------------------+ 93 rows in set (0.00 sec) 10 11mysql> 12mysql> SELECT * FROM student; 13+--------+-------------+ 14| stu_id | stu_name | 15+--------+-------------+ 16| 1 | jason | 17| 2 | danny | 18| 3 | jenny | 19| 4 | liming | 20| 5 | yinzhengjie | 21+--------+-------------+ 225 rows in set (0.00 sec) 23 24mysql> 25mysql> CREATE TABLE student4 AS SELECT * FROM student; 26Query OK, 5 rows affected (0.01 sec) 27Records: 5 Duplicates: 0 Warnings: 0 28 29mysql> 30mysql> SHOW TABLES; 31+-----------------------+ 32| Tables_in_yinzhengjie | 33+-----------------------+ 34| student | 35| student2 | 36| student3 | 37| student4 | 38+-----------------------+ 394 rows in set (0.00 sec) 40 41mysql> 42mysql> SELECT * FROM student4; 43+--------+-------------+ 44| stu_id | stu_name | 45+--------+-------------+ 46| 1 | jason | 47| 2 | danny | 48| 3 | jenny | 49| 4 | liming | 50| 5 | yinzhengjie | 51+--------+-------------+ 525 rows in set (0.00 sec) 53 54mysql>
mysql> CREATE TABLE student4 AS SELECT * FROM student;
6>.INNORE和REPLACE
表示在插入数据的过程中如果新表中碰到违反唯一约束的情况下怎么处理，IGNORE表示不插入，REPLACE表示替换已有的数据，默认两个关键字都不写则碰到违反的情况会报错。
7>.DATA_TYPE
表示定义字段类型
8>.NOT NULL/NULL
表示字段是否允许为空，默认NULL表示允许为空，NOT NULL表示需要对此字段明确数值，或者要有默认值，否则报错。
1mysql> SELECT DATABASE(); 2+-------------+ 3| DATABASE() | 4+-------------+ 5| yinzhengjie | 6+-------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> SHOW TABLES; 11+-----------------------+ 12| Tables_in_yinzhengjie | 13+-----------------------+ 14| student | 15| student2 | 16| student3 | 17| student4 | 18+-----------------------+ 194 rows in set (0.00 sec) 20 21mysql> 22mysql> CREATE TABLE student5(stu_id INT NOT NULL,stu_name VARCHAR(50)); 23Query OK, 0 rows affected (0.02 sec) 24 25mysql> 26mysql> INSERT INTO student5(stu_name) values('jason'); #这里报错是正确的，因为我们要求stu_id字段不允许为空，我们又没有给他指定自增属性，因此需要手动给该字段赋值！ 27ERROR 1364 (HY000): Field 'stu_id' doesn't have a default value 28mysql> 29mysql> INSERT INTO student5(stu_id,stu_name) values(001,'jason'); 30Query OK, 1 row affected (0.00 sec) 31 32mysql> 33mysql> SHOW TABLES; 34+-----------------------+ 35| Tables_in_yinzhengjie | 36+-----------------------+ 37| student | 38| student2 | 39| student3 | 40| student4 | 41| student5 | 42+-----------------------+ 435 rows in set (0.00 sec) 44 45mysql> 46mysql> SELECT * FROM student5; 47+--------+----------+ 48| stu_id | stu_name | 49+--------+----------+ 50| 1 | jason | 51+--------+----------+ 521 row in set (0.00 sec) 53 54mysql>
mysql> CREATE TABLE student5(stu_id INT NOT NULL,stu_name VARCHAR(50));
9>.DEFAULT
表示设置字段的默认值
1mysql> SHOW TABLES; 2+-----------------------+ 3| Tables_in_yinzhengjie | 4+-----------------------+ 5| student | 6| student2 | 7| student3 | 8| student4 | 9| student5 | 10+-----------------------+ 115 rows in set (0.00 sec) 12 13mysql> 14mysql> CREATE TABLE student6(stu_id INT,stu_name varchar(50),stu_gender ENUM('boy','girl') DEFAULT 'boy'); 15Query OK, 0 rows affected (0.01 sec) 16 17mysql> 18mysql> INSERT INTO student6 VALUES(001,'yinzhengjie',DEFAULT); 19Query OK, 1 row affected (0.01 sec) 20 21mysql> 22mysql> INSERT INTO student6 VALUES(002,'Jenny','girl'); 23Query OK, 1 row affected (0.00 sec) 24 25mysql> 26mysql> INSERT INTO student6(stu_id,stu_name) VALUES(003,'Danny'); 27Query OK, 1 row affected (0.00 sec) 28 29mysql> 30mysql> SHOW TABLES; 31+-----------------------+ 32| Tables_in_yinzhengjie | 33+-----------------------+ 34| student | 35| student2 | 36| student3 | 37| student4 | 38| student5 | 39| student6 | 40+-----------------------+ 416 rows in set (0.00 sec) 42 43mysql> SELECT * FROM student6; 44+--------+-------------+------------+ 45| stu_id | stu_name | stu_gender | 46+--------+-------------+------------+ 47| 1 | yinzhengjie | boy | 48| 2 | Jenny | girl | 49| 3 | Danny | boy | 50+--------+-------------+------------+ 513 rows in set (0.00 sec) 52 53mysql>
mysql> CREATE TABLE student6(stu_id INT,stu_name varchar(50),stu_gender ENUM('boy','girl') DEFAULT 'boy');
10>.COLUMN_FORMAT
目前仅在ndb存储引擎的表上有用，表示该字段的存储类型是FIXED，DYNAMIC或者DEFAULT。
11>.STORAGE
目前也仅在ndb存储引擎的表上有用。
12>.CONSTRAINT
表示为主键，唯一键，外键等约束条件命名，如果没有命名则MySQL会默认给一个。
13>.PRIMARY KEY
表示该字段为主键，主键字段必须唯一，必须非空，一个表中只能有一个主键，主键可以包含一个或者多个字段。
14>.KEY/INDEX
表示索引字段。
15>.UNIQUE
表示该字段为唯一属性字段，且允许包含多个NULL值。
16>.FOREIGN KEY
表示该字段为外键字段。一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
17>.AUTO_INCREMENT
表示字段为整数或者浮点数类型的value+1递增数值，value为当前表中该字段最大的值，默认是从1开始递增；一个表中只允许有一个自增字段，且该字段必须有key属性，不能还有DEFAULT属性，且插入复制会被当成很大的整数。
1mysql> SELECT DATABASE(); 2+-------------+ 3| DATABASE() | 4+-------------+ 5| yinzhengjie | 6+-------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> 11mysql> SHOW TABLES; 12+-----------------------+ 13| Tables_in_yinzhengjie | 14+-----------------------+ 15| student | 16| student2 | 17| student3 | 18| student4 | 19| student5 | 20| student6 | 21+-----------------------+ 226 rows in set (0.00 sec) 23 24mysql> 25mysql> CREATE TABLE student7(stu_id INT PRIMARY KEY AUTO_INCREMENT,stu_name VARCHAR(30)); 26Query OK, 0 rows affected (0.01 sec) 27 28mysql> 29mysql> SHOW TABLES; 30+-----------------------+ 31| Tables_in_yinzhengjie | 32+-----------------------+ 33| student | 34| student2 | 35| student3 | 36| student4 | 37| student5 | 38| student6 | 39| student7 | 40+-----------------------+ 417 rows in set (0.00 sec) 42 43mysql> 44mysql> INSERT INTO student7(stu_name) VALUES('yinzhengjie'); 45Query OK, 1 row affected (0.00 sec) 46 47mysql> INSERT INTO student7(stu_id,stu_name) VALUES(5,'jason'); 48Query OK, 1 row affected (0.01 sec) 49 50mysql> INSERT INTO student7(stu_name) VALUES('jenny'); 51Query OK, 1 row affected (0.01 sec) 52 53mysql> 54mysql> SELECT * FROM student7; 55+--------+-------------+ 56| stu_id | stu_name | 57+--------+-------------+ 58| 1 | yinzhengjie | 59| 5 | jason | 60| 6 | jenny | 61+--------+-------------+ 623 rows in set (0.00 sec) 63 64mysql>
mysql> CREATE TABLE student7(stu_id INT PRIMARY KEY AUTO_INCREMENT,stu_name VARCHAR(30));
1mysql> SELECT DATABASE(); 2+-------------+ 3| DATABASE() | 4+-------------+ 5| yinzhengjie | 6+-------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> SHOW TABLES; 11+-----------------------+ 12| Tables_in_yinzhengjie | 13+-----------------------+ 14| student | 15| student2 | 16| student3 | 17| student4 | 18| student5 | 19| student6 | 20| student7 | 21+-----------------------+ 227 rows in set (0.01 sec) 23 24mysql> 25mysql> CREATE TABLE gender( 26 -> gender_id INT(11) NOT NULL, 27 -> name VARCHAR(30) DEFAULT NULL, 28 -> PRIMARY KEY(gender_id) 29 -> ); 30Query OK, 0 rows affected (0.01 sec) 31 32mysql>
创建gender表（mysql> CREATE TABLE gender( gender_id INT(11) NOT NULL, name VARCHAR(30) DEFAULT NULL, PRIMARY KEY(gender_id) );）
1mysql> SHOW TABLES; 2+-----------------------+ 3| Tables_in_yinzhengjie | 4+-----------------------+ 5| gender | 6| student | 7| student2 | 8| student3 | 9| student4 | 10| student5 | 11| student6 | 12| student7 | 13+-----------------------+ 148 rows in set (0.00 sec) 15 16mysql> 17mysql> CREATE TABLE student8( 18 -> stu_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 19 -> stu_name VARCHAR(50) UNIQUE, 20 -> gender INT, 21 -> CONSTRAINT waijian_01 FOREIGN KEY(gender) REFERENCES gender(gender_id) 22 -> ); 23Query OK, 0 rows affected (0.01 sec) 24 25mysql> SHOW TABLES; 26+-----------------------+ 27| Tables_in_yinzhengjie | 28+-----------------------+ 29| gender | 30| student | 31| student2 | 32| student3 | 33| student4 | 34| student5 | 35| student6 | 36| student7 | 37| student8 | 38+-----------------------+ 399 rows in set (0.00 sec) 40 41mysql> 42 mysql> DESC student8; 43+----------+-------------+------+-----+---------+----------------+ 44| Field | Type | Null | Key | Default | Extra | 45+----------+-------------+------+-----+---------+----------------+ 46| stu_id | int(11) | NO | PRI | NULL | auto_increment | 47| stu_name | varchar(50) | YES | UNI | NULL | | 48| gender | int(11) | YES | MUL | NULL | | 49+----------+-------------+------+-----+---------+----------------+ 503 rows in set (0.00 sec) 51 52mysql> 53mysql> DESC gender; 54+-----------+-------------+------+-----+---------+-------+ 55| Field | Type | Null | Key | Default | Extra | 56+-----------+-------------+------+-----+---------+-------+ 57| gender_id | int(11) | NO | PRI | NULL | | 58| name | varchar(30) | YES | | NULL | | 59+-----------+-------------+------+-----+---------+-------+ 602 rows in set (0.00 sec) 61 62mysql> 63mysql>
为student8创建外间约束（mysql> CREATE TABLE student8( stu_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, stu_name VARCHAR(50) UNIQUE, gender INT, CONSTRAINT waijian_01 FOREIGN KEY(gender) REFERENCES gender(gender_id) );）
三.小试牛刀
1设计一个学生选课数据库系统 2 • 创建一个名为course的数据库 3 • 在该数据库下创建以下几个表: 4 • Students表: sid整型自增主键，sname字符串64位，gender字符串12位，dept_id整型并外键到Dept表的id字段 5 • Dept表: id整型自增主键，dept_name字符串64位 6 • Course表: id整型自增字段主键，course_name字符串64位，teacher_id整型外键到 Teacher表的id字段 7 • Teacher表: id整型自增字段主键，name字符串64位，dept_id整型外键到Dept表的id 字段 8 • Students表和teacher表的dept_id为非空
首先，上面这道题很简单，但是我要劝心急的小伙伴先把真道题读完了在写SQL，读完题之后我们会发现Student表依赖于Dept表，Course表依赖于Teacher表，Tearcher表依赖于Dept表，而这些表都存放在course的数据库中。分析清楚题意后我们在写SQL就相对得心应手了，相应的SQL语句如下：
1mysql> CREATE DATABASE course CHARACTER SET = utf8; 2Query OK, 1 row affected, 1 warning (0.00 sec) 3 4mysql>
创建course数据库（mysql> CREATE DATABASE course CHARACTER SET = utf8;）
1mysql> USE course; 2Database changed 3mysql> 4mysql> CREATE TABLE Dept(id INT PRIMARY KEY AUTO_INCREMENT,demt_name VARCHAR(64)); 5Query OK, 0 rows affected (0.01 sec) 6 7mysql>
创建Dept表（mysql> CREATE TABLE Dept(id INT PRIMARY KEY AUTO_INCREMENT,demt_name VARCHAR(64));）
1mysql> select database(); 2+------------+ 3| database() | 4+------------+ 5| course | 6+------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> CREATE TABLE students( 11 -> sid INT PRIMARY KEY AUTO_INCREMENT, 12 -> sname VARCHAR(64), 13 -> gender VARCHAR(12), 14 -> dept_id INT NOT NULL, 15 -> CONSTRAINT student_dept FOREIGN KEY(dept_id) REFERENCES Dept(id) 16 -> ); 17Query OK, 0 rows affected (0.01 sec) 18 19mysql>
创建Student表（mysql> CREATE TABLE students( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(64), gender VARCHAR(12), dept_id INT NOT NULL, CONSTRAINT student_dept FOREIGN KEY(dept_id) REFERENCES Dept(id) );）
1mysql> SELECT DATABASE(); 2+------------+ 3| DATABASE() | 4+------------+ 5| course | 6+------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> CREATE TABLE Teacher( 11 -> id INT PRIMARY KEY AUTO_INCREMENT, 12 -> name VARCHAR(64), 13 -> dept_id INT NOT NULL, 14 -> CONSTRAINT teacher_dept FOREIGN KEY(dept_id) REFERENCES Dept(id) 15 -> ); 16Query OK, 0 rows affected (0.01 sec) 17 18mysql>
创建Teacher表（ mysql> CREATE TABLE Teacher( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64), dept_id INT NOT NULL, CONSTRAINT teacher_dept FOREIGN KEY(dept_id) REFERENCES Dept(id) );）
1mysql> SELECT DATABASE(); 2+------------+ 3| DATABASE() | 4+------------+ 5| course | 6+------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> CREATE TABLE course( 11 -> id INT PRIMARY KEY AUTO_INCREMENT, 12 -> course_name VARCHAR(64), 13 -> teacher_id INT, 14 -> CONSTRAINT course_teacher FOREIGN KEY(teacher_id) REFERENCES Teacher(id) 15 -> ); 16Query OK, 0 rows affected (0.01 sec) 17 18mysql> 19mysql>
创建course表（mysql> CREATE TABLE course( id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(64), teacher_id INT, CONSTRAINT course_teacher FOREIGN KEY(teacher_id) REFERENCES Teacher(id) );）
1mysql> SELECT DATABASE(); 2+------------+ 3| DATABASE() | 4+------------+ 5| course | 6+------------+ 71 row in set (0.00 sec) 8 9mysql> 10mysql> SHOW TABLES; 11+------------------+ 12| Tables_in_course | 13+------------------+ 14| Dept | 15| Teacher | 16| course | 17| students | 18+------------------+ 194 rows in set (0.00 sec) 20 21mysql> 22mysql> DESC Dept; 23+-----------+-------------+------+-----+---------+----------------+ 24| Field | Type | Null | Key | Default | Extra | 25+-----------+-------------+------+-----+---------+----------------+ 26| id | int(11) | NO | PRI | NULL | auto_increment | 27| demt_name | varchar(64) | YES | | NULL | | 28+-----------+-------------+------+-----+---------+----------------+ 292 rows in set (0.00 sec) 30 31mysql> 32mysql> DESC Teacher; 33+---------+-------------+------+-----+---------+----------------+ 34| Field | Type | Null | Key | Default | Extra | 35+---------+-------------+------+-----+---------+----------------+ 36| id | int(11) | NO | PRI | NULL | auto_increment | 37| name | varchar(64) | YES | | NULL | | 38| dept_id | int(11) | NO | MUL | NULL | | 39+---------+-------------+------+-----+---------+----------------+ 403 rows in set (0.00 sec) 41 42mysql> DESC course; 43+-------------+-------------+------+-----+---------+----------------+ 44| Field | Type | Null | Key | Default | Extra | 45+-------------+-------------+------+-----+---------+----------------+ 46| id | int(11) | NO | PRI | NULL | auto_increment | 47| course_name | varchar(64) | YES | | NULL | | 48| teacher_id | int(11) | YES | MUL | NULL | | 49+-------------+-------------+------+-----+---------+----------------+ 503 rows in set (0.01 sec) 51 52mysql> 53mysql> DESC students; 54+---------+-------------+------+-----+---------+----------------+ 55| Field | Type | Null | Key | Default | Extra | 56+---------+-------------+------+-----+---------+----------------+ 57| sid | int(11) | NO | PRI | NULL | auto_increment | 58| sname | varchar(64) | YES | | NULL | | 59| gender | varchar(12) | YES | | NULL | | 60| dept_id | int(11) | NO | MUL | NULL | | 61+---------+-------------+------+-----+---------+----------------+ 624 rows in set (0.00 sec) 63 64mysql>