MySQL数据表的基本操作一：创建表与表约束

MySQL数据表的基本操作，字段的添加、删除、重命名、类型更改、表的各种约束

mrdavidtian

14849人浏览 · 2014-09-23 01:14:55

mrdavidtian · 2014-09-23 01:14:55 发布

1 创建数据表

在数据库创建好以后，接下来的工作就是创建数据表。所谓创建数据表，指的是在已经创建好的数据库中建立新表。

创建数据表之前，应当使用语句 "USE <数据库名>“指定操作是在哪个数据库中进行。

1 .1 创建表的语法

CREATE TABLE <表名>

(

字段名１　数据类型[列级别约束条件][默认值],

字段名2　数据类型[列级别约束条件][默认值],

...
[表级别约束条件]

);

1.2 说明

要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字。
数据表中每个列的名称和数据类型，如果需要创建多个列，需要以逗号隔开。

1.3 示例

１）要创建的employees表结构说明

employees表结构
字段名称	数据类型	备注
employee_id	INT(11)	员工编号
employee_name	VARCHAR(25)	员工名称
department_id	INT(11)	所在部门编号
salary	FLOAT	工资

２）步骤

----选择／新建要创建表的数据库

mysql> create database basics;
Query OK, 1 row affected (0.02 sec)

mysql> use basics;
Database changed

----创建表的语句

mysql> create table employees
    -> (
    -> employee_id int(11),
    -> employee_name varchar(25),
    -> department_id int(11),
    -> salary float
    -> );
Query OK, 0 rows affected (0.16 sec)

----验证表是否创建成功

mysql> show tables;
+------------------+
| Tables_in_basics |
+------------------+
| employees        |
+------------------+
1 row in set (0.00 sec)

2 使用主键约束(Pirmary Key Constraint)

主键，又称为主码，是表中一列或多列组成。主键约束要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条记录。主键分为两种类型：单列主键和复合主键。

2.1 单列主键(可以在创建表时指定，也可以在表级别约束处指定)

2.1.1 创建表时指定

2.1.1.1 语法：

 字段名　数据类型　PRIMARY KEY [默认值]

2.1.1.2 示例：

mysql> CREATE TABLE department
    -> (
    -> department_id int(11) PRIMARY KEY,
    -> department_name varchar(50),
    -> location varchar(200)
    -> );
Query OK, 0 rows affected (0.09 sec)

2.1.2 表级别约束处指定

2.1.2.1 语法：

 [CONSTRAINT <约束名> PRIMARY KEY [字段名]

2.1.2.2 示例：

mysql> create table location
    -> (
    -> location_id int(11),
    -> location_name varchar(200),
    -> PRIMARY KEY(location_id)
    -> );
Query OK, 0 rows affected (0.08 sec)

2.2 复合主键(主键由多个字段组成)

2.2.1 语法

PRIMARY KEY [字段1, 字段2,...字段n]

2.2.2 示例

mysql> create table sales
    -> (
    -> product_id int(11),
    -> region_code varchar(10),
    -> quantity int(11),
    -> price float,
    -> PRIMARY KEY(product_id,region_code)
    -> );
Query OK, 0 rows affected (0.09 sec)

3 使用外键约束

3.1 外键说明

外键用来在两个表的数据之间建立连接，它可以是一个列或者多个列。
一个表可以有一个或者多个外键。
外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
定义外键后，不允许删除在另一个表中具有关联关系的行。
主表(父表): 对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
从表(子表): 对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

3.2 创建外建的语法

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1[,字段名2,...]  REFERENCES <主表名>  主键列1[,主键列2,...]

3.3 示例--需求：创建部门表dept和员工表emp,并在员工表上创建外键

3.3.1 创建主表

mysql> CREATE TABLE dept
    -> (
    -> id int(11) PRIMARY KEY,
    -> name varchar(22) NOT NULL,
    -> location varchar(50),
    -> description varchar(200)
    -> );
Query OK, 0 rows affected (0.07 sec)

3.3.2 创建子表并定义外键约束

mysql> create table emp
    -> (
    -> id int(11) PRIMARY KEY,
    -> name varchar(25),
    -> dept_id int(11),
    -> salary float,
    -> CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
    -> );
Query OK, 0 rows affected (0.11 sec)

3.3.3 重要提示

关联指的是在关系数据库中，相关表之间的联系。它是通过相同或者相容的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配。

4 使用非空约束(Not NULL Constraint)

4.1 说明

非空约束指字段的值不能为空。

4.2 语法

字段名　数据类型　NOT NULL

4.3 示例--定义数据表emp1,指定员工的名称不能为空

mysql> create table emp1
    -> (
    -> id int(11) primary key,
    -> name varchar(25) NOT NULL,
    -> deptid int(11)
    -> );
Query OK, 0 rows affected (0.07 sec)

5 使用唯一性约束(Unique Constraint)

5.1 说明

唯一性约束要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

5.2 列级别定义唯一性约束

语法：

字段名　数据类型　UNIQUE

示例：

mysql> create database dl_table;
Query OK, 1 row affected (0.00 sec)

mysql> use dl_table;
Database changed
mysql> create table department
    -> (
    -> id int(11) primary key,
    -> name varchar(22) UNIQUE,
    -> location varchar(50)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into department(id,name,location) values(1,'david','wx');
Query OK, 1 row affected (0.03 sec)

mysql> insert into department(id,name,location) values(2,'david','sz');
ERROR 1062 (23000): Duplicate entry 'david' for key 'name'

5.3 表级别定义唯一性约束

语法：

[CONSTRAINT <约束名>] UNIQUE(<字段名>)

示例：

mysql> create table bm
    -> (
    -> id int(11) primary key,
    -> name varchar(22),
    -> location varchar(20),
    -> CONSTRAINT STH UNIQUE(name)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into bm values(1,'xxx','x');
Query OK, 1 row affected (0.02 sec)

mysql> insert into bm values(2,'xxx','y');
ERROR 1062 (23000): Duplicate entry 'xxx' for key 'STH'

5.4 唯一约束与主键的区别

一个表中可以有多个字段声明为UNIQUE,但是只能有一个主键；
声明为主键的列不允许有空值，但是声明为唯一约束的字段允许空值存在；

6 使用默认约束(Default Constraint)

6.1 说明

默认约束指定某弄的默认值。

6.2 语法规则

字段名　数据类型　DEFAULT 默认值

6.3 示例

mysql> create table employees
    -> (
    -> id int(11) primary key,
    -> name varchar(50) not null,
    -> department_id int(11) default 2000,
    -> salary float,
    -> description varchar(300)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into employees(id,name) values(1,'david');
Query OK, 1 row affected (0.04 sec)

mysql> insert into employees(id,name) values(2,'louis');
Query OK, 1 row affected (0.04 sec)

mysql> insert into employees(id,name) values(3,'tian');
Query OK, 1 row affected (0.04 sec)

mysql> select * from employees;
+----+-------+---------------+--------+-------------+
| id | name  | department_id | salary | description |
+----+-------+---------------+--------+-------------+
|  1 | david |          2000 |   NULL | NULL        |
|  2 | louis |          2000 |   NULL | NULL        |
|  3 | tian  |          2000 |   NULL | NULL        |
+----+-------+---------------+--------+-------------+
3 rows in set (0.00 sec)

7 设置表的属性值自动增加

7.1 说明

在数据库中，经常希望在每次插入记录时，系统自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现;
在MySQL中，AUTO_INCREMENT的初始值是1,每新增加一条记录，字段值自动加1;
一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分;
AUTO_INCREMENT约束的字段可是任何整数类型(TINYINT, SMALLINT, INT, BIGINT);

7.2 语法

字段名　数据类型　AUTO_INCREMENT

7.3 示例

mysql> create table product
    -> (
    -> product_id int(11) primary key AUTO_INCREMENT,
    -> product_name varchar(50) not null,
    -> description varchar(200)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into product(product_name, description) values('Mouse','PC Mouse');
Query OK, 1 row affected (0.04 sec)

mysql> insert into product(product_name, description) values('Pen','e-Pen');
Query OK, 1 row affected (0.04 sec)

mysql> insert into product(product_name, description) values('iPhone6 Plus','Apple');
Query OK, 1 row affected (0.03 sec)

mysql> select * from product;
+------------+--------------+-------------+
| product_id | product_name | description |
+------------+--------------+-------------+
|          1 | Mouse        | PC Mouse    |
|          2 | Pen          | e-Pen       |
|          3 | iPhone6 Plus | Apple       |
+------------+--------------+-------------+
3 rows in set (0.00 sec)

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！

一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

编译时权限控制

前言权限控制，对于 MLSQL 而言的重要程度可以说是生命线。 MLSQL 需要面对各式各样的资源访问，比如 MySQL, Oracle,HDFS，Hive，Kafka，Sorl，ElasticSearch，Redis，API，Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。传统模式是，每个用户都需要有个 proxy user，然后到每个数据源里面给这个 proxy user

Byzer 白泽

Byzer 支持 JDBC 聚合下推

聚合下推PR链接聚合下推我们知道 Byzer 支持多数据源和联邦查询，可以方便分析师在一个平台上快速的分析来自多种数据源的数据，从而进行灵活的探索式分析。使用场景和优势当前 Byzer 中加载数据的方式会拉取明细数据到 spark 中进行聚合计算，对于小数据量或者在分布式文件系统上的数据源来说是常规操作。但是对于 JDBC 数据源或者有分析能力的 OLAP 系统来说拉取明细数据可能就不是最

Byzer 白泽

Byzer 术语表

Byzer-lang Byzer ，又称为 Byzer-lang，一门面向 Data 和 AI 的低代码、云原生的开源编程语言。 Byzer 是一门结合了声明式编程和命令式编程的混合编程语言，其低代码且类 SQL 的编程逻辑配合内置算法及插件的加持，能帮助数据工作者们高效打通数据链路，完成数据的清洗转换，并快速地进行机器学习相关的训练及预测。 Byzer 希望能够提供一套语言、一个引擎，就能覆盖整