mysql8学习笔记5--SQL语法基础 create select insert
Create database语句是在MySQL实例上创建一个指定名称的数据
库，create schema语句的语义和create database是一样的
当创建的数据库本身存在而且没有写明if not exists子句时，则创
建数据库的语句会报错
mysql> create database test_20200702; Query OK, 1 row affected (0.06 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | company | | information_schema | | mysql | | performance_schema | | sys | | test_20200702 | +--------------------+ 6 rows in set (0.00 sec) mysql> create database test_20200702; ERROR 1007 (HY000): Can't create database 'test_20200702'; database exists mysql> create database if not exists test_20200702; Query OK, 1 row affected, 1 warning (0.00 sec)
create_specification子句指明创建的数据库的属性，并存储在db.opt文件中
• Character set属性指明此数据库的默认字符集
• Collate属性指明此数据库的默认排序规则
• 创建后的数据库在数据文件所在目录会创建一个自己的文件目录，用来包含后续创建的表文件
[root@localhost ~]# ll -d /mysql8/mysql_data/test_20200702 drwxr-x---. 2 mysql mysql 6 Jul 1 10:36 /mysql8/mysql_data/test_20200702 [root@localhost ~]#
尝试指定字符集和排序规则：
mysql> create database if not exists test2 character set gb2312 collate gb2312_bin; Query OK, 1 row affected (0.06 sec) mysql> create database if not exists test2 character set gb2312 collate gb2312_bin; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | company | | information_schema | | mysql | | performance_schema | | school | | sys | | test2 | | test_20201121 | +--------------------+ 8 rows in set (0.00 sec) mysql> show create database test2; +----------+-------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------------------------+ | test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET gb2312 COLLATE gb2312_bin */ | +----------+-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
Create table语句是在数据库中创建表
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 | {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option] ... | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition | check_constraint_definition } column_definition: { data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}] [STORAGE {DISK | MEMORY}] [reference_definition] [check_constraint_definition] | data_type [COLLATE collation_name] [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] [check_constraint_definition] } data_type: (see Chapter 11, Data Types) 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} } check_constraint_definition: [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED] 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 [STORAGE {DISK | MEMORY}] | 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)
mysql> create database if not exists test_20200702_01; Query OK, 1 row affected (0.10 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | company | | information_schema | | mysql | | performance_schema | | sys | | test_20200702 | | test_20200702_01 | +--------------------+ 7 rows in set (0.00 sec) mysql> create database if not exists test_20200702_01; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> create database if not exists test_20200702_01; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> create database if not exists test_20200702_01; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> create database if not exists test_20200702_01; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use test_20200702; Database changed mysql> create table app_acct(id int,name varchar(20),sno int); Query OK, 0 rows affected (0.15 sec) mysql> show tables; +-------------------------+ | Tables_in_test_20200702 | +-------------------------+ | app_acct | +-------------------------+ 1 row in set (0.00 sec) mysql> desc app_acct; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sno | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> create table app_acct();#创建的表至少定义一个字段 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 mysql> create table app_acct(id int); ERROR 1050 (42S01): Table 'app_acct' already exists mysql> create table app_acct2(id int); Query OK, 0 rows affected (0.10 sec) mysql> create table test_20200702_01.app_acct(id int); Query OK, 0 rows affected (0.16 sec) mysql> use test_20200701_01; ERROR 1049 (42000): Unknown database 'test_20200701_01' mysql> use test_20200702_01; 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> show tables; +----------------------------+ | Tables_in_test_20200702_01 | +----------------------------+ | app_acct | +----------------------------+ 1 row in set (0.00 sec) mysql>
创建临时表：就是在table前面加个关键字temporary，创建的临时表在show tables里查不出来，但做增删改查操作。
mysql> create temporary table if not exists app_acct_tmp(id int,name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------------------+ | Tables_in_test_20200702_01 | +----------------------------+ | app_acct | +----------------------------+ 1 row in set (0.00 sec) mysql> select * from app_acct_tmp; Empty set (0.00 sec) mysql> insert into app_acct_tmp values(1,'名字'); Query OK, 1 row affected (0.00 sec) mysql> select * from app_acct_tmp; +------+--------+ | id | name | +------+--------+ | 1 | 名字 | +------+--------+ 1 row in set (0.00 sec) mysql>
新建一个mysql连接，会发现新的连接查不到这个临时表
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 58 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 test_20200702_01; 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> show tables; +----------------------------+ | Tables_in_test_20200702_01 | +----------------------------+ | app_acct | +----------------------------+ 1 row in set (0.00 sec) mysql> select * from app_acct_tmp; ERROR 1146 (42S02): Table 'test_20200702_01.app_acct_tmp' doesn't exist mysql>
连接断开重连后，发现临时表消失了，说明临时表只在当前连接生效
mysql> select * from app_acct_tmp; +------+--------+ | id | name | +------+--------+ | 1 | 名字 | +------+--------+ 1 row in set (0.00 sec) mysql> mysql> exit Bye [root@localhost ~]# mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 60 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 test_20200702_01; 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> select * from app_acct_tmp; ERROR 1146 (42S02): Table 'test_20200702_01.app_acct_tmp' doesn't exist mysql>
Like关键词表示基于另外一个表的定义复制一个新的空表，空表上的字段属性和索引都和原表相同
mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> create table customers3 like customers; Query OK, 0 rows affected (0.48 sec) mysql> desc customers3; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) mysql>
mysql> show create table customers; +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers | CREATE TABLE `customers` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) NOT NULL, `cust_address` char(50) DEFAULT NULL, `cust_city` char(50) DEFAULT NULL, `cust_state` char(5) DEFAULT NULL, `cust_zip` char(10) DEFAULT NULL, `cust_country` char(50) DEFAULT NULL, `cust_contact` char(50) DEFAULT NULL, `cust_email` char(255) DEFAULT NULL, PRIMARY KEY (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8 | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create index index_one on customers(cust_zip); Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table customers; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers | CREATE TABLE `customers` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) NOT NULL, `cust_address` char(50) DEFAULT NULL, `cust_city` char(50) DEFAULT NULL, `cust_state` char(5) DEFAULT NULL, `cust_zip` char(10) DEFAULT NULL, `cust_country` char(50) DEFAULT NULL, `cust_contact` char(50) DEFAULT NULL, `cust_email` char(255) DEFAULT NULL, PRIMARY KEY (`cust_id`), KEY `index_one` (`cust_zip`) ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> drop table customers3; Query OK, 0 rows affected (0.25 sec) mysql> create table customers3 like customers; Query OK, 0 rows affected (0.27 sec) mysql> show create table customers3; +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers3 | CREATE TABLE `customers3` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) NOT NULL, `cust_address` char(50) DEFAULT NULL, `cust_city` char(50) DEFAULT NULL, `cust_state` char(5) DEFAULT NULL, `cust_zip` char(10) DEFAULT NULL, `cust_country` char(50) DEFAULT NULL, `cust_contact` char(50) DEFAULT NULL, `cust_email` char(255) DEFAULT NULL, PRIMARY KEY (`cust_id`), KEY `index_one` (`cust_zip`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
Create table … as select语句表示创建表的同时将select的查询结果数据插入到表中，但索引和主外键信息都不会同步过来
mysql> create table customers4 as select * from customers where cust_id in ('10001','10002'); Query OK, 2 rows affected (0.33 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from customers4; +---------+-------------+------------------+-----------+------------+----------+--------------+--------------+-----------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-------------+------------------+-----------+------------+----------+--------------+--------------+-----------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | +---------+-------------+------------------+-----------+------------+----------+--------------+--------------+-----------------+ 2 rows in set (0.00 sec) mysql> show create table customers; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers | CREATE TABLE `customers` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) NOT NULL, `cust_address` char(50) DEFAULT NULL, `cust_city` char(50) DEFAULT NULL, `cust_state` char(5) DEFAULT NULL, `cust_zip` char(10) DEFAULT NULL, `cust_country` char(50) DEFAULT NULL, `cust_contact` char(50) DEFAULT NULL, `cust_email` char(255) DEFAULT NULL, PRIMARY KEY (`cust_id`), KEY `index_one` (`cust_zip`) ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table customers4; +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers4 | CREATE TABLE `customers4` ( `cust_id` int(11) NOT NULL DEFAULT '0', `cust_name` char(50) CHARACTER SET utf8 NOT NULL, `cust_address` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_city` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_state` char(5) CHARACTER SET utf8 DEFAULT NULL, `cust_zip` char(10) CHARACTER SET utf8 DEFAULT NULL, `cust_country` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_contact` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_email` char(255) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
add primary key：
mysql> alter table customers5 add primary key(cust_id); Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table customers5; +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers5 | CREATE TABLE `customers5` ( `cust_id` int(11) NOT NULL DEFAULT '0', `cust_name` char(50) CHARACTER SET utf8 NOT NULL, `cust_address` char(50) CHARACTER SET utf8 NOT NULL, `cust_city` char(50) CHARACTER SET utf8 DEFAULT '深圳', `cust_state` char(5) CHARACTER SET utf8 DEFAULT '南山区', `cust_zip` char(10) CHARACTER SET utf8 DEFAULT NULL, `cust_country` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_contact` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_email` char(255) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`cust_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
insert into table() values();
如果字段有值不能为Null，则必须插入值：
mysql> desc customers; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | MUL | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 9 rows in set (0.04 sec) mysql> insert into customers(cust_id,cust_name) values('123',''); Query OK, 1 row affected (0.22 sec) mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 123 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 6 rows in set (0.00 sec) mysql> insert into customers(cust_id) values('1234'); ERROR 1364 (HY000): Field 'cust_name' doesn't have a default value mysql>
把column改成 not null
Not null/null表示字段是否允许为空，默认为null表示允许为空，not null表示需要对此字段明确数值，或者要有默认值，否则报错
mysql> desc customers; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | MUL | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 9 rows in set (0.01 sec) mysql> alter table customers modify cust_address char(50) not null; ERROR 1138 (22004): Invalid use of NULL value #这个提示是这一列有字段值为null mysql> update customers set cust_address='中国深圳南山区'; Query OK, 6 rows affected (0.15 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> alter table customers modify cust_address char(50) not null; Query OK, 0 rows affected (0.77 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc customers; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | NO | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | MUL | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) mysql>
• Default表示设置字段的默认值
mysql> alter table customers alter column cust_city set default '深圳'; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc customers; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | NO | | NULL | | | cust_city | char(50) | YES | | 深圳 | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | MUL | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) mysql> alter table customers alter cust_state set default '南山区'; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc customers; +--------------+-----------+------+-----+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+-----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | NO | | NULL | | | cust_city | char(50) | YES | | 深圳 | | | cust_state | char(5) | YES | | 南山区 | | | cust_zip | char(10) | YES | MUL | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+-----------+----------------+ 9 rows in set (0.00 sec) mysql>
mysql> insert into customers(cust_id,cust_name,cust_address) values(110,'小明','中国广东省');
Query OK, 1 row affected (0.01 sec)
mysql> select * from customers where cust_id=110;
+---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+
| 110 | 小明 | 中国广东省 | 深圳 | 南山区 | NULL | NULL | NULL | NULL |
+---------+-----------+-----------------+-----------+------------+----------+--------------+--------------+------------+
1 row in set (0.00 sec)
Auto_increment表示字段为整数或者浮点数类型的value+1递增数值，value为当前表中该字段最大的值，默认是从1开始递增；一个表中只容许有一个自增字段，且该字段必须有key属性，不能含有default属性，且插入负值会被当成很大的正数。
mysql> create table customers5(id int auto_increment,cust_name varchar(20)); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> create table customers5(id int primary key auto_increment,cust_name varchar(20)); Query OK, 0 rows affected (0.50 sec) mysql> desc customers5 -> ; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql>
测试下auto_increment效果：
mysql> insert into customers5 values('张三'); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into customers5(cust_name) values('张三'); Query OK, 1 row affected (0.09 sec) mysql> insert into customers5(cust_name) values('李四'); Query OK, 1 row affected (0.05 sec) mysql> insert into customers5(cust_name) values('王五'); Query OK, 1 row affected (0.08 sec) mysql> select * from customers5; +----+-----------+ | id | cust_name | +----+-----------+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | +----+-----------+ 3 rows in set (0.00 sec) mysql>
如果自增字段中间插了个不是连续的最大值，下一条插入记录的自增字段会在最大值基础上+1
mysql> insert into customers5(id,cust_name) values(1002,'赵六'); Query OK, 1 row affected (0.07 sec) mysql> insert into customers5(cust_name) values('徐七'); Query OK, 1 row affected (0.01 sec) mysql> select * from customers5; +------+-----------+ | id | cust_name | +------+-----------+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 1002 | 赵六 | | 1003 | 徐七 | +------+-----------+ 5 rows in set (0.00 sec) mysql>
Column_format目前仅在ndb存储引擎的表上有用，表示该字段的存储类型是fixed, dynamic或者是default
• Storage目前也仅在ndb存储引擎的表上有用
• Constraint表示为主键、唯一键、外键等约束条件命名，如果没有命名则MySQL会默认给一个
• Primary key表示该字段为主键，主键字段必须唯一，必须非空，一个表中只能有一个主键，主键可以包含一个或多个字段
• Key/index表示索引字段
• Unique表示该字段为唯一属性字段，且允许包含多个null值
• Foreign key表示该字段为外键字段
创建unique index （唯一索引）
mysql> create unique index index_name on customers5(cust_name); Query OK, 0 rows affected (0.59 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table customers5; +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers5 | CREATE TABLE `customers5` ( `cust_id` int(11) NOT NULL DEFAULT '0', `cust_name` char(50) CHARACTER SET utf8 NOT NULL, `cust_address` char(50) CHARACTER SET utf8 NOT NULL, `cust_city` char(50) CHARACTER SET utf8 DEFAULT '深圳', `cust_state` char(5) CHARACTER SET utf8 DEFAULT '南山区', `cust_zip` char(10) CHARACTER SET utf8 DEFAULT NULL, `cust_country` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_contact` char(50) CHARACTER SET utf8 DEFAULT NULL, `cust_email` char(255) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`cust_id`), UNIQUE KEY `index_name` (`cust_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Unique表示该字段为唯一属性字段，且允许包含多个null值
mysql> desc customers5; +--------------+-------------+------+-----+-----------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-----------+-------+ | cust_id | int(11) | NO | PRI | 0 | | | cust_name | varchar(20) | YES | UNI | NULL | | | cust_address | char(50) | NO | | NULL | | | cust_city | char(50) | YES | | 深圳 | | | cust_state | char(5) | YES | | 南山区 | | | cust_zip | char(10) | YES | | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-------------+------+-----+-----------+-------+ 9 rows in set (0.00 sec) mysql> insert into customers5(cust_id,cust_address) values(120,'中国'); mysql> insert into customers5(cust_id,cust_address) values(130,'中国'); mysql> select * from customers5 where cust_id in (120,130); +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ | 120 | NULL | 中国 | 深圳 | 南山区 | NULL | NULL | NULL | NULL | | 130 | NULL | 中国 | 深圳 | 南山区 | NULL | NULL | NULL | NULL | +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ 2 rows in set (0.01 sec)
Foreign key表示该字段为外键字段
mysql> alter table customers6 add FOREIGN KEY(cust_id) references customers(cust_id); Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc customers6; +--------------+-----------+------+-----+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+-----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | NO | | NULL | | | cust_city | char(50) | YES | | 深圳 | | | cust_state | char(5) | YES | | 南山区 | | | cust_zip | char(10) | YES | | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +--------------+-----------+------+-----+-----------+----------------+ 9 rows in set (0.00 sec) mysql> show create table customers6; +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers6 | CREATE TABLE `customers6` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) COLLATE utf8_unicode_ci NOT NULL, `cust_address` char(50) COLLATE utf8_unicode_ci NOT NULL, `cust_city` char(50) COLLATE utf8_unicode_ci DEFAULT '深圳', `cust_state` char(5) COLLATE utf8_unicode_ci DEFAULT '南山区', `cust_zip` char(10) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_country` char(50) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_contact` char(50) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_email` char(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`cust_id`), CONSTRAINT `customers6_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table customers6 drop foreign key(cust_id); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(cust_id)' at line 1 mysql> alter table customers6 drop foreign key cust_id; ERROR 1091 (42000): Can't DROP 'cust_id'; check that column/key exists mysql> alter table customers6 drop foreign key customers6_ibfk_1;#创建外键时，可以定义名称，否则mysql会自动生成一个。 Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table customers6; +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers6 | CREATE TABLE `customers6` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) COLLATE utf8_unicode_ci NOT NULL, `cust_address` char(50) COLLATE utf8_unicode_ci NOT NULL, `cust_city` char(50) COLLATE utf8_unicode_ci DEFAULT '深圳', `cust_state` char(5) COLLATE utf8_unicode_ci DEFAULT '南山区', `cust_zip` char(10) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_country` char(50) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_contact` char(50) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_email` char(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`cust_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> alter table customers6 add foreign key cust_id_foernkey (cust_id) references customers(cust_id); Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table customers6; +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | customers6 | CREATE TABLE `customers6` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) COLLATE utf8_unicode_ci NOT NULL, `cust_address` char(50) COLLATE utf8_unicode_ci NOT NULL, `cust_city` char(50) COLLATE utf8_unicode_ci DEFAULT '深圳', `cust_state` char(5) COLLATE utf8_unicode_ci DEFAULT '南山区', `cust_zip` char(10) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_country` char(50) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_contact` char(50) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_email` char(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`cust_id`), CONSTRAINT `cust_id_foernkey` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
给子表插入数据和给主表删除数据时有约束
mysql> insert into customers6(cust_id,cust_name,cust_address) values(111,'Tom','China'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`customers6`, CONSTRAINT `cust_id_foernkey` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)) mysql> insert into customers6(cust_id,cust_name,cust_address) values(110,'Tom','China'); Query OK, 1 row affected (0.13 sec) mysql> select * from customers6; +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ | 110 | Tom | China | 深圳 | 南山区 | NULL | NULL | NULL | NULL | +---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+ 1 row in set (0.00 sec) mysql> delete from customers where cust_id=110; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`customers6`, CONSTRAINT `cust_id_foernkey` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)) mysql> mysql> mysql> delete from customers6 where cust_id=110; Query OK, 1 row affected (0.15 sec) mysql> delete from customers where cust_id=110; Query OK, 1 row affected (0.10 sec)
设计一个学生选课数据库系统
• 创建一个名为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为非空
mysql> show tables; Empty set (0.00 sec) mysql> create table dept(id int auto_increment,dept_name varchar(64)); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> create table dept(id int primary key auto_increment,dept_name varchar(64)); Query OK, 0 rows affected (0.13 sec) mysql> mysql> create table students(sid int primary key auto_increment,sname varchar(64),gender varchar(12),dept_id int,foreign key(dept_id) references dept(id)); Query OK, 0 rows affected (0.45 sec)
mysql> create table teacher(id int primary key auto_increment,name varchar(64),dept_id int,foreign key(dept_id) references dept(id)); Query OK, 0 rows affected (0.16 sec) mysql> create table Course(id int primary key auto_increment,course_name varchar(64),teacher_id int,foreign key (teacher_id) references teacher(id)); Query OK, 0 rows affected (0.16 sec) mysql> show create table students; +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | students | CREATE TABLE `students` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `sname` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `gender` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`sid`), KEY `dept_id` (`dept_id`), CONSTRAINT `students_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table dept; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | dept | CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dept_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show tables; +------------------+ | Tables_in_course | +------------------+ | Course | | dept | | students | | teacher | +------------------+ 4 rows in set (0.01 sec) mysql> show create table Course; +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Course | CREATE TABLE `Course` ( `id` int(11) NOT NULL AUTO_INCREMENT, `course_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `teacher_id` (`teacher_id`), CONSTRAINT `Course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table teacher; +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | teacher | CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `dept_id` (`dept_id`), CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
