细说MySQL创建表的三种⽅法
⽂章⽬录
⼤家好！我是只谈技术不剪发的 Tony ⽼师。
SQL 标准使⽤ CREATE TABLE 语句创建数据表；MySQL 则实现了三种创建表的⽅法，⽀持⾃定义表结构或者通过复制已有的表结构来创建新表，本⽂给⼤家分别介绍⼀下这些⽅法的使⽤和注意事项。
如果你觉得⽂章有⽤，欢迎评论 、点赞 、推荐
CREATE TABLE
CREATE TABLE 语句的基本语法如下：
CREATE TABLE[IF NOT EXISTS] table_name
(
column1 data_type column_constraint,
column2 data_type,
...,
table_constraint
);
使⽤该语句时，我们需要⼿动定义表的结构。以上包含的内容如下：
IF NOT EXISTS 表⽰当该表不存在时创建表，当表已经存在时不执⾏该语句。
table_name 指定了表的名称。
括号内是字段的定义；columnN 是字段的名称，data_type 是它的数据类型；column_constraint 是可选的字段约束；多个字段使⽤逗号进⾏分隔。
sql2000安装教程64位
table_constraint 是可选的表级约束。
其中，常见的约束包括主键、外键、唯⼀、⾮空、以及默认值。
举例来说，以下语句⽤于创建⼀个新表 department：
CREATE TABLE department
( dept_id    INTEGER NOT NULL PRIMARY KEY
mysql语句多表查询
, dept_name  VARCHAR(50)NOT NULL
);
部门表 department 包含两个字段，部门编号（dept_id）是⼀个整数类型（INTEGER），不可以为空（NOT NULL），同时它还是这个表的主键（PRIMARY KEY）。部门名称（dept_name）是⼀个可变长度的字符串，最长 50 个字符，不允许为空。
如果我们想要创建⼀个⾃定义名称的主键约束，可以使⽤表级约束：
CREATE TABLE department
( dept_id    INTEGER NOT NULL
, dept_name  VARCHAR(50)NOT NULL
,CONSTRAINT pk_department PRIMARY KEY(dept_id)
);
表级约束在所有字段之后定义，其中 pk_dept 是⾃定义的主键名称。
对于数字类型的主键字段，我们可以通过⾃增长列（auto increment）⾃动⽣成⼀个唯⼀的数字。例如：
CREATE TABLE department
( dept_id    INTEGER AUTO_INCREMENT PRIMARY KEY
, dept_name  VARCHAR(50)NOT NULL
);
此时，我们在插⼊数据时不再需要为 dept_id 字段提供数据，MySQL 默认会产⽣⼀个从 1 开始，每次递增 1 的数字序列。
然后我们再创建两个表：
CREATE TABLE job
( job_id        INTEGER NOT NULL PRIMARY KEY
, job_title      VARCHAR(50)NOT NULL
);
CREATE TABLE employee
( emp_id    INTEGER NOT NULL PRIMARY KEY
, emp_name  VARCHAR(50)NOT NULL
, sex      VARCHAR(10)NOT NULL
, dept_id  INTEGER NOT NULL
, manager  INTEGER
, hire_date DATE NOT NULL
, job_id    INTEGER NOT NULL
, salary    NUMERIC(8,2)NOT NULL
,
bonus    NUMERIC(8,2)
, email    VARCHAR(100)NOT NULLonlyfans视频下载教程
instron公司哪个国家,CONSTRAINT ck_emp_sex CHECK(sex IN('男','⼥'))
,CONSTRAINT ck_emp_salary CHECK(salary >0)
,CONSTRAINT uk_emp_email UNIQUE(email)
,CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id)REFERENCES department(dept_id)
,CONSTRAINT fk_emp_job FOREIGN KEY(job_id)REFERENCES job(job_id)
,CONSTRAINT fk_emp_manager FOREIGN KEY(manager)REFERENCES employee(emp_id)
);
job 表⽤于存储职位信息，和部门表相似，⽐较简单。
employee 表⽤于存储员⼯信息，包含的字段和约束如下：
员⼯编号（emp_id）是⼀个整数类型（INTEGER），不可以为空（NOT NULL），同时它还是这个表的主键（PRIMARY KEY）。
员⼯姓名（emp_name）是⼀个可变长度的字符串，最长 50 个字符，不允许为空。
性别（sex）是⼀个可变长度的字符串，最长 10 个字符，不允许为空；另外，我们通过表级约束 ck_emp_sex 限制了性别的取值只能为“男”或者“⼥”。
部门编号（dept_id）代表了员⼯所在的部门，因此通过外键约束 fk_emp_dept 引⽤了部门表的主键字段。
经理编号（manager）代表了员⼯的直接上级，可能为空。外键约束 fk_emp_manager 表⽰经理也属于员⼯。
⼊职⽇期（hire_date）是⼀个 DATE 类型的字段，不能为空。
职位编号（job_id）代表了员⼯的职位，因此通过外键 fk_emp_job 引⽤了职位表的主键字段。
⽉薪（salary）是⼀个⽀持两位⼩数的数字，不能为空。检查约束 ck_emp_salary 要求⽉薪必须⼤于零。
奖⾦（bonus）是⼀个可选的数字字段。
电⼦邮箱（email）是⼀个可变长度的字符串，最长100 个字符，不允许为空。检查约束 uk_emp_email 要求每个员⼯的电⼦邮箱都不相同。
CREATE TABLE … LIKE
除了⼿动定义表的结构之外，MySQL 还提供了复制已有表结构的⽅法：
CREATE TABLE[IF NOT EXISTS] table_name
{ LIKE old_tbl_name |(LIKE old_tbl_name) }
MySQL 的 LIKE 语法只复制表结构，包括字段的属性和索引，但是不复制数据。例如：
CREATE TABLE emp_copy
LIKE employee;
以上语句基于 employee 表的结构复制⽣成⼀个新的表 emp_copy。
mysql>show create table emp_copy \G
***************************1.row***************************
Table: emp_copy
Create Table: CREATE TABLE`emp_copy`(
`emp_id`int NOT NULL,
security spp是什么意思
`emp_name`varchar(50)NOT NULL,
`sex`varchar(10)NOT NULL,
`dept_id`int NOT NULL,
`manager`int DEFAULT NULL,
`hire_date`date NOT NULL,
`job_id`int NOT NULL,
`salary`decimal(8,2)NOT NULL,
`bonus`decimal(8,2)DEFAULT NULL,
`email`varchar(100)NOT NULL,strlen函数在哪个头文件c
PRIMARY KEY(`emp_id`),
UNIQUE KEY`uk_emp_email`(`email`),
KEY`fk_emp_dept`(`dept_id`),
KEY`fk_emp_job`(`job_id`),
KEY`fk_emp_manager`(`manager`),
CONSTRAINT`emp_copy_chk_1`CHECK((`salary`>0)),
CONSTRAINT`emp_copy_chk_2`CHECK((`sex`in(_utf8mb4'男',_utf8mb4'⼥')))
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1row in set(0.01 sec)
对于 CREATE TABLE … LIKE 命令，⽬标表会保留原始表中的主键、唯⼀约束、⾮空约束、表达式默认值、检查约束（⾃动⽣成约束名），同时还会保留原始表中的计算列定义。
CREATE TABLE … LIKE 命令不会保留外键约束（但是会保留外键索引），以及任何 DATA DIRECTORY 或者 INDEX DIRECTORY 表属性选项。
如果原始表是⼀个 TEMPORARY 表，CREATE TABLE … LIKE 不会保留 TEMPORARY 关键字。如果想要创建⼀个临时表，可以使⽤CREATE TEMPORARY TABLE … LIKE。
使⽤ mysql 表空间、InnoDB 系统表空间（innodb_system）或者通⽤表空间创建的表包含⼀个 TABLESPACE 属性，表⽰该表所在的表空间。⽬前，⽆论 innodb_file_per_table 设置为什么参数，CREATE TABLE … LIKE 都会保留 TABLESPACE 属性。为了避免复制新表时使⽤原始表的 TABLESPACE 属性，可以使⽤下⾯介绍的第三种⽅法。例如：
CREATE TABLE new_tbl SELECT*FROM orig_tbl LIMIT0;
以上语句会基于 orig_tbl 创建⼀个新的空表 new_tbl，具体参考下⼀节内容。
CREATE TABLE … LIKE 使⽤原始表的所有 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值创建⽬标表。
另外，LIKE 只能基于表进⾏复制，⽽不能复制视图。
CREATE TABLE … SELECT
在 MySQL 中复制表结构的另⼀种⽅法就是利⽤查询语句的结果定义字段和复制的数据：
CREATE TABLE table_name
[;
其中的 SELECT 语句定义了新表的结构和数据。以下⽰例基于查询的结果创建了⼀个新表：emp_devp，表中包含了研发部的所有员⼯。
CREATE TABLE emp_devp
AS
SELECT e.*
FROM employee e
JOIN department d
ON(d.dept_id = e.dept_id AND d.dept_name ='研发部');
对于这种语法，MySQL 实际上是在已有⽬标表中增加新的字段。例如：
CREATE TABLE t1(col1 INTEGER, col2 INTEGER);
INSERT INTO t1(col1, col2)VALUES(1,1),(2,4);
CREATE TABLE t2(id INTEGER AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB
AS SELECT col1, col2 FROM t1;
我们⾸先为 t2 指定了⼀个⾃增 id，然后将 t1 的查询结果添加到该字段的后⾯。其中，ENGINE 选项属于 CREATE TABLE 语句，因此需要位于 SELECT 语句之前。
查询 t2 可以看到以上语句不仅复制了表结构，同时还复制了 t1 中的数据：
TABLE t2;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|1|1|1|
|2|2|4|
+----+------+------+
2rows in set(0.00 sec)
如果只想复制结构，不需要复制数据，可以在查询语句中增加 LIMIT 0 或者 WHERE 1=0 条件。
如果在 SELECT 语句前⾯增加 IGNORE 或者 REPLACE 关键字，可以处理复制数据时导致唯⼀键冲突的数据⾏。对于 IGNORE，源数据中和⽬标表重复的数据⾏就会被丢弃；对于 REPLACE，使⽤新数据⾏替换⽬标表中的已有数据⾏。如果没有指定任何选项，唯⼀键冲突将会返回错误。
CREATE TABLE … SELECT 命令不会⾃动创建任何索引，这样可以使得该语句尽量灵活。如果想要创建索引，可以在 SELECT 语句之前指定。例如：
CREATE TABLE t3(id INTEGER PRIMARY KEY)
AS SELECT col1 as id, col2 FROM t1;
CREATE TABLE … SELECT 命令不会保留计算列的定义，也不会保留默认值定义。同时某些数据类型可能产⽣转换。例
如，AUTO_INCREMENT 属性不会被保留，VARCHAR 类型被转换为 CHAR 类型。保留的属性包括 NULL（NOT NULL）以及CHARACTER SET、COLLATION、COMMENT 和 DEFAULT ⼦句。
使⽤ CREATE TABLE … SELECT 命令创建表时，需要为查询语句中的函数和表达式指定⼀个别名，否则该命令可能失败或者创建意料之外的字段名：
CREATE TABLE artists_and_works
SELECT artist.name,COUNT(work.artist_id)AS number_of_works
FROM artist LEFT JOIN work ON artist.id =work.artist_id
GROUP BY artist.id;
对于 CREATE TABLE … SELECT 命令，如果我们指定了 IF NOT EXISTS 并且⽬标表已经存在，不会将数据复制到⽬标表，同时该语句不会写⼊⽇志⽂件。
CREATE TABLE … SELECT 命令不⽀持 FOR UPDATE 选项。
CREATE TABLE … SELECT 命令只会应⽤字段的 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 属性。表和索引的ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 属性不会被应⽤，除⾮为⽬标表明确指定这些选项。
总结
本⽂通过⼀些案例介绍了 MySQL 中三种创建表的⽅法和注意事项。
如果你想要了解更多 MySQL ⼊门知识，可以参考。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。