MySQL中创建表的三种方法汇总
作者:不剪发的Tony老师 发布时间:2024-01-21 09:40:35
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 是可选的字段约束;多个字段使用逗号进行分隔。
table_constraint 是可选的表级约束。
其中,常见的约束包括主键、外键、唯一、非空、检查约束以及默认值。
举例来说,以下语句用于创建一个新表 department:
CREATE TABLE department
( dept_id INTEGER NOT NULL PRIMARY KEY
, 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 NULL
, 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,
`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,
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
1 row 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 LIMIT 0;
以上语句会基于 orig_tbl 创建一个新的空表 new_tbl,具体参考下一节内容。
CREATE TABLE … LIKE 使用原始表的所有 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值创建目标表。
另外,LIKE 只能基于表进行复制,而不能复制视图。
CREATE TABLE … SELECT
在 MySQL 中复制表结构的另一种方法就是利用查询语句的结果定义字段和复制的数据:
CREATE TABLE table_name
[AS] SELECT ...;
其中的 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 |
+----+------+------+
2 rows 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 属性不会被应用,除非为目标表明确指定这些选项。
来源:https://blog.csdn.net/horses/article/details/119720521
猜你喜欢
- 本文实例讲述了Python3实现计算两个数组的交集算法。分享给大家供大家参考,具体如下:问题:给定两个数组,写一个方法来计算它们的交集。方案
- 数据库数据导出为excel表格,也可以说是一个很常用的功能了。毕竟不是任何人都懂数据库操作语句的。下面先来看看完成的效果吧。数据源导出结果依
- 1.作用域在python中,作用域分为两种:全局作用域和局部作用域。全局作用域是定义在文件级别的变量,函数名。而局部作用域,则是定义函数内部
- 读取十万多条文本写入SQLite类型数据库,由于文本中存在中文字符,插入到数据库没错,取出时一直是UnicodeDecodeError,导致
- 针对与mssql2005的安全,应当针对于2个方面来做,针对于主要的权限及端口。(要是有人说删除不安全的系统存储过程,先说好这个方式只能针对
- windows版本下需要先安装ffmpeg工具:1:先下载指定(https://ffmpeg.zeranoe.com/builds/) 有S
- 一. 代码使用Python+Splinter开发,Splinter是一个使用Python开发的开源Web应用测试工具,它可以帮你实
- 分享一个 * 真网页拾色器(调色板),颜色丰富216色,使用方便。运行截图:<html id="container"
- 如何在线修改表?具体代码如下:<%Set conn1 = Server.CreateObject(&qu
- 一、DatasetDataset 类提供一种方式去获取数据及其标签主要有两个目的:获取每一个数据及其标签获取数据的总量大小1. 在控制台进行
- 实现思路是先获取到当前最上面活动的窗口信息,然后提取该窗口的名称信息。之后获取窗口的坐标信息,即左上角的开始坐标及右下角的结束坐标。最后直接
- 一. MovingAverage权值滑动平均更新1.1 示例代码:def create_target_q_network(self,stat
- 本文实例为大家分享了python实现飞机大战的具体代码,供大家参考,具体内容如下游戏的实现本质是多个图片的快速切换,类似动画一样,达到动态的
- 定义临时变量:{% with i=1 %}{{i}}{% endwith %}定义对临时变量操作的tag在templatetags中创建se
- 前言:如何将一个JSON文档映射为Python对象主要包括一下三个部分:考点:loads函数的用法。面试题:如何将一个JSON文档映射为Py
- 前几天有一个需求,透视表中的年级这一列要按照一年级,二年级这样的序列进行排序,但是用过透视表的人都知道,透视表对中文的排序不是太理想,放弃p
- Inotify地址:访问# -*- coding:utf-8 -*-import osimport pyinotifyfrom functi
- 传参时传递可变对象,实际上传的是指向内存地址的指针/引用这个标题是我的结论,也是我在做项目过程查到的。学过C的都知道,函数传参可以传值,也可
- 一、ZeroMQ概述 ZeroMQ(又名ØMQ,MQ,或zmq)像一个可嵌入的网络库,但其作用就像一个并发框
- <?php/** * HOST: www.icbase.com *///set_time_limit(0);//