SQL语法基础之CREATE语句
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.查看帮助信息
1>.使用“？”来查看MySQL命令的帮助信息
mysql> ? CREATE #这里告诉我们CREATE命令需要和那些命令一起使用 Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX CREATE PROCEDURE CREATE RESOURCE GROUP CREATE ROLE CREATE SERVER CREATE SPATIAL REFERENCE SYSTEM CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE TABLE SHOW CREATE USER SPATIAL mysql>
2>.查看CREATE DATABASE命令的帮助信息
mysql> ? CREATE DATABASE Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. URL: http://dev.mysql.com/doc/refman/8.0/en/create-database.html #这里是官方给的帮助文档 mysql>
3>.查询帮助时关键点剖析
刚刚学习MySQL的小伙伴，可能知道使用问好（“？”）可以查询命令的使用方法，但是获取到帮助信息后，看不懂该怎么用。别着急，我们把帮助信息细细的揣摩一下就明白咋用了，也方便我在下面执行相应的SQL语句时，大家不会产生过多歧义。首先我们以上面的查看“CREATE DATABASE”命令的帮助信息为例，简要说明一下该如何查看帮助信息：
第一：没有使用括号包裹起来的字段是必须写的。
第二：使用大括号（“{ }”）包裹起来的字段是必须写的，只不过我们需要从大括号中用管道（“|”）分隔的各个字段中选取相应一个来使用，例如“{DATABASE | SCHEMA}” 就表示我们必须选一个字段，要么选择DATABASE，要么选择SCHEME，不可以不选哟！
第三：中括号的字段是可以不写的，比如“[IF NOT EXISTS] ”这个语句咱们就是可以不写，不过建议大家写上，可以避免出错，它是一个IF判断语句。
二.CREATE DATABASE
1>.CREATE DATABASE 语句是在MySQL实力上创建一个指定名称的数据库，CREATE SCHEMA语句的语意和CREATE DATABASE是一样的。
mysql> ? CREATE DATABASE Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. URL: http://dev.mysql.com/doc/refman/8.0/en/create-database.html mysql>
2>.当创建当数据本身存在没有写明“IF NOT EXISTS”子句是，创建数据库当语句会报错
3>.create_specification子句指明创建数据库的属性，并且存储在db.opt文件中
• Character set属性指明此数据库的默认字符集
• Collate属性指明此数据库的默认排序规则
4>.创建后的数据库在数据文件中所在目录会创建一个子句的文件目录，用来包含后续创建的表文件。
5>.创建数据库案例展示
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> mysql> CREATE DATABASE yinzhengjie CHARACTER SET = utf8; Query OK, 1 row affected, 1 warning (0.01 sec) mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yinzhengjie | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> SHOW CREATE DATABASE yinzhengjie; +-------------+----------------------------------------------------------------------+ | Database | Create Database | +-------------+----------------------------------------------------------------------+ | yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-------------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yinzhengjie | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> SHOW CREATE DATABASE yinzhengjie; +-------------+----------------------------------------------------------------------+ | Database | Create Database | +-------------+----------------------------------------------------------------------+ | yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-------------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> CREATE DATABASE yinzhengjie CHARACTER SET = gbk; ERROR 1007 (HY000): Can't create database 'yinzhengjie'; database exists mysql> mysql> CREATE DATABASE IF NOT EXISTS yinzhengjie CHARACTER SET = gbk; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> SHOW CREATE DATABASE yinzhengjie; +-------------+----------------------------------------------------------------------+ | Database | Create Database | +-------------+----------------------------------------------------------------------+ | yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-------------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
温馨提示：
在MySQL5.7版本咱们也可以直接通过mkdir的操作系统命令在数据目录创建文件夹，则MySQL会识别为一个数据库，并在执行show databases命令时可以看到。
但是，在MySQL8.0版本咱们再通过mkdir的操作系统命令在数据目录创建文件夹，就不会被识别了哟！
三.CREATE TABLE
1>.查看CREATE TABLE的帮助信息
mysql> ? CREATE TABLE Name: 'CREATE TABLE' Description: Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expression CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) } create_definition: col_name column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ... | {INDEX|KEY} [index_name] [index_type] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (key_part,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition | CHECK (expr) column_definition: data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [reference_definition] | data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] data_type: (see http://dev.mysql.com/doc/refman/8.0/en/data-types.html) key_part: {col_name [(length)] | (expr)} [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' | {VISIBLE | INVISIBLE} reference_definition: REFERENCES tbl_name (key_part,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT table_options: table_option [[,] table_option] ... table_option: AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'} | CONNECTION [=] 'connect_string' | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | ENCRYPTION [=] {'Y' | 'N'} | ENGINE [=] engine_name | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT|0|1} | STATS_PERSISTENT [=] {DEFAULT|0|1} | STATS_SAMPLE_PAGES [=] value | TABLESPACE tablespace_name | UNION [=] (tbl_name[,tbl_name]...) partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)] partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'string' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [(subpartition_definition [, subpartition_definition] ...)] subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'string' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] query_expression: SELECT ... (Some valid select or union statement) CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table. By default, tables are created in the default database, using the InnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist. For information about the physical representation of a table, see http://dev.mysql.com/doc/refman/8.0/en/create-table-files.html. URL: http://dev.mysql.com/doc/refman/8.0/en/create-table.html mysql>
2>.基本的建表语句
mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yinzhengjie | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> USE yinzhengjie; Database changed mysql> mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> CREATE TABLE student(stu_id int,stu_name varchar(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | +-----------------------+ 1 row in set (0.00 sec) mysql> mysql> DESC student; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(30) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
[root@node110 ~]# mysql -uroot -pyinzhengjie mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 8.0.14 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yinzhengjie | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> CREATE TABLE IF NOT EXISTS yinzhengjie.student2(stu_id int,stu_name varchar(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> mysql> USE yinzhengjie Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | +-----------------------+ 2 rows in set (0.00 sec) mysql> DESC student2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(30) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
3>.TEMPORARY
注意，TEMPORARY关键字表示创建的是临时表，临时表仅对本链接可见，另外的数据库链接不可见，当本链接断开时，临时表也被自动DROP掉。
mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | +-----------------------+ 2 rows in set (0.00 sec) mysql> mysql> CREATE TEMPORARY TABLE student_temp(stu_tem_id int,stu_tem_name varchar(30)); #这里我们使用TEMPORARY关键字创建了一张临时表 Query OK, 0 rows affected (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | +-----------------------+ 2 rows in set (0.00 sec) mysql> INSERT INTO student_temp VALUES(1,'jason'); #我们往临时表中插入一条数据 Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM student_temp; #很显然，数据往临时表中插入成功了，注意，如果此时另一个数据库链接在相同当数据库执行相同当查询语句是查不到数据当哟！能查询的仅限当前的数据库链接！ +------------+--------------+ | stu_tem_id | stu_tem_name | +------------+--------------+ | 1 | jason | +------------+--------------+ 1 row in set (0.00 sec) mysql> mysql>quit Bye [root@node110 ~]# mysql -uroot -pyinzhengjie mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 12 Server version: 8.0.14 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> USE yinzhengjie Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM student_temp; #很显然，当创建临时表当那个数据库链接端口后再链接，临时表已经不存在了！ ERROR 1146 (42S02): Table 'yinzhengjie.student_temp' doesn't exist mysql> mysql>
4>.LIKE
LIKE关键字表示基于另外一个表的定义复制一个新的空表，空表时尚的字段属性和索引都和原表相同。
mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | +-----------------------+ 2 rows in set (0.00 sec) mysql> mysql> CREATE TABLE student3 LIKE student2; #咱们这里使用了LIKE关键字 Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | +-----------------------+ 3 rows in set (0.00 sec) mysql> DESC student2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(30) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> mysql> DESC student3; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(30) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
mysql> SHOW CREATE TABLE student2; +----------+---------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------------+ | student2 | CREATE TABLE `student2` ( `stu_id` int(11) DEFAULT NULL, `stu_name` varchar(30) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+---------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW CREATE TABLE student3; +----------+---------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------------+ | student3 | CREATE TABLE `student3` ( `stu_id` int(11) DEFAULT NULL, `stu_name` varchar(30) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+---------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
5>.CREATE TABLE ... AS SELECT 语句
表示创建表的同时将SELECT的查询结果数据插入到表中，但索引和主外键信息都不会同步过来
mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | +-----------------------+ 3 rows in set (0.00 sec) mysql> mysql> SELECT * FROM student; +--------+-------------+ | stu_id | stu_name | +--------+-------------+ | 1 | jason | | 2 | danny | | 3 | jenny | | 4 | liming | | 5 | yinzhengjie | +--------+-------------+ 5 rows in set (0.00 sec) mysql> mysql> CREATE TABLE student4 AS SELECT * FROM student; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | +-----------------------+ 4 rows in set (0.00 sec) mysql> mysql> SELECT * FROM student4; +--------+-------------+ | stu_id | stu_name | +--------+-------------+ | 1 | jason | | 2 | danny | | 3 | jenny | | 4 | liming | | 5 | yinzhengjie | +--------+-------------+ 5 rows in set (0.00 sec) mysql>
6>.INNORE和REPLACE
表示在插入数据的过程中如果新表中碰到违反唯一约束的情况下怎么处理，IGNORE表示不插入，REPLACE表示替换已有的数据，默认两个关键字都不写则碰到违反的情况会报错。
7>.DATA_TYPE
表示定义字段类型
8>.NOT NULL/NULL
表示字段是否允许为空，默认NULL表示允许为空，NOT NULL表示需要对此字段明确数值，或者要有默认值，否则报错。
mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | +-----------------------+ 4 rows in set (0.00 sec) mysql> mysql> CREATE TABLE student5(stu_id INT NOT NULL,stu_name VARCHAR(50)); Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO student5(stu_name) values('jason'); #这里报错是正确的，因为我们要求stu_id字段不允许为空，我们又没有给他指定自增属性，因此需要手动给该字段赋值！ ERROR 1364 (HY000): Field 'stu_id' doesn't have a default value mysql> mysql> INSERT INTO student5(stu_id,stu_name) values(001,'jason'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | | student5 | +-----------------------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT * FROM student5; +--------+----------+ | stu_id | stu_name | +--------+----------+ | 1 | jason | +--------+----------+ 1 row in set (0.00 sec) mysql>
9>.DEFAULT
表示设置字段的默认值
mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | | student5 | +-----------------------+ 5 rows in set (0.00 sec) mysql> mysql> CREATE TABLE student6(stu_id INT,stu_name varchar(50),stu_gender ENUM('boy','girl') DEFAULT 'boy'); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO student6 VALUES(001,'yinzhengjie',DEFAULT); Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO student6 VALUES(002,'Jenny','girl'); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO student6(stu_id,stu_name) VALUES(003,'Danny'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | | student5 | | student6 | +-----------------------+ 6 rows in set (0.00 sec) mysql> SELECT * FROM student6; +--------+-------------+------------+ | stu_id | stu_name | stu_gender | +--------+-------------+------------+ | 1 | yinzhengjie | boy | | 2 | Jenny | girl | | 3 | Danny | boy | +--------+-------------+------------+ 3 rows in set (0.00 sec) mysql>
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属性，且插入复制会被当成很大的整数。
mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | | student5 | | student6 | +-----------------------+ 6 rows in set (0.00 sec) mysql> mysql> CREATE TABLE student7(stu_id INT PRIMARY KEY AUTO_INCREMENT,stu_name VARCHAR(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | | student5 | | student6 | | student7 | +-----------------------+ 7 rows in set (0.00 sec) mysql> mysql> INSERT INTO student7(stu_name) VALUES('yinzhengjie'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student7(stu_id,stu_name) VALUES(5,'jason'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO student7(stu_name) VALUES('jenny'); Query OK, 1 row affected (0.01 sec) mysql> mysql> SELECT * FROM student7; +--------+-------------+ | stu_id | stu_name | +--------+-------------+ | 1 | yinzhengjie | | 5 | jason | | 6 | jenny | +--------+-------------+ 3 rows in set (0.00 sec) mysql>
mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | student | | student2 | | student3 | | student4 | | student5 | | student6 | | student7 | +-----------------------+ 7 rows in set (0.01 sec) mysql> mysql> CREATE TABLE gender( -> gender_id INT(11) NOT NULL, -> name VARCHAR(30) DEFAULT NULL, -> PRIMARY KEY(gender_id) -> ); Query OK, 0 rows affected (0.01 sec) mysql>
mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | gender | | student | | student2 | | student3 | | student4 | | student5 | | student6 | | student7 | +-----------------------+ 8 rows in set (0.00 sec) mysql> 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) -> ); Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | gender | | student | | student2 | | student3 | | student4 | | student5 | | student6 | | student7 | | student8 | +-----------------------+ 9 rows in set (0.00 sec) mysql> mysql> DESC student8; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | stu_id | int(11) | NO | PRI | NULL | auto_increment | | stu_name | varchar(50) | YES | UNI | NULL | | | gender | int(11) | YES | MUL | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> mysql> DESC gender; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | gender_id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> mysql>
三.小试牛刀
设计一个学生选课数据库系统
• 创建一个名为course的数据库
• 在该数据库下创建以下几个表:
• Students表:
sid整型自增主键，sname字符串64位，gender字符串12位，dept_id整型并外键到Dept表的id字段
• Dept表:
id整型自增主键，dept_name字符串64位
• Course表:
id整型自增字段主键，course_name字符串64位，teacher_id整型外键到 Teacher表的id字段
• Teacher表:
id整型自增字段主键，name字符串64位，dept_id整型外键到Dept表的id 字段
• Students表和teacher表的dept_id为非空
首先，上面这道题很简单，但是我要劝心急的小伙伴先把真道题读完了在写SQL，读完题之后我们会发现Student表依赖于Dept表，Course表依赖于Teacher表，Tearcher表依赖于Dept表，而这些表都存放在course的数据库中。分析清楚题意后我们在写SQL就相对得心应手了，相应的SQL语句如下：
mysql> CREATE DATABASE course CHARACTER SET = utf8; Query OK, 1 row affected, 1 warning (0.00 sec) mysql>
mysql> USE course; Database changed mysql> mysql> CREATE TABLE Dept(id INT PRIMARY KEY AUTO_INCREMENT,demt_name VARCHAR(64)); Query OK, 0 rows affected (0.01 sec) mysql>
mysql> select database(); +------------+ | database() | +------------+ | course | +------------+ 1 row in set (0.00 sec) mysql> 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) -> ); Query OK, 0 rows affected (0.01 sec) mysql>
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | course | +------------+ 1 row in set (0.00 sec) mysql> 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) -> ); Query OK, 0 rows affected (0.01 sec) mysql>
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | course | +------------+ 1 row in set (0.00 sec) mysql> 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) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql>
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | course | +------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW TABLES; +------------------+ | Tables_in_course | +------------------+ | Dept | | Teacher | | course | | students | +------------------+ 4 rows in set (0.00 sec) mysql> mysql> DESC Dept; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | demt_name | varchar(64) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> mysql> DESC Teacher; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | | dept_id | int(11) | NO | MUL | NULL | | +---------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> DESC course; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | course_name | varchar(64) | YES | | NULL | | | teacher_id | int(11) | YES | MUL | NULL | | +-------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> mysql> DESC students; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | sid | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(64) | YES | | NULL | | | gender | varchar(12) | YES | | NULL | | | dept_id | int(11) | NO | MUL | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql>