1、创建数据表

1.1、创建表的语法形式

创建数据表的语句为： CREATE TABLE。

  CREATE  TABLE <表名>
      (
  列名1，数据类型 [列级别约束条件] [默认值],
  列名2，数据类型 [列级别约束条件] [默认值],
  ……
  [表级别约束条件]
       );

创建数据表的举例：(语句在cmd环境中运行)

创建数据库

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.01 sec)

使用数据库

mysql> USE test_db;
Database changed

创建数据表：员工表tb_emp1。

mysql> CREATE TABLE tb_emp1
    -> (
    -> id      INT(11),
    -> name   VARCHAR(25),
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

1.2、使用主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key constraint）要求主键列的数据唯一，并且不允许为空。

1、单字段主键

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

（1）、在定义列的同时制定主键。

定义数据表tb_emp 2，其主键为id，SQL语句如下：

mysql> CREATE TABLE tb_emp2
    -> (
    -> id    INT(11) PRIMARY KEY,
    -> name  VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

（2）、在定义完所有的列后定义制定的主键。

定义数据表tb_emp 3，其主键为id，SQL语句如下：

mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

2、多字段联合主键

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

主键由多个主键联合而成。

定义数据表tb_emp4，假设表中间没有主键员工的id，为了唯一确定一个员工，可以把员工的name、deptId联合起来做为主键，SQL语句如下：

mysql> CREATE TABLE tb_emp4
    ->  (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

1.3、使用外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。

一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

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

外键要对应其他表的主键，使两个表之间相互关联起来。外键主要启动保证数据的完整性，它不可以删除在另一个表中具有关联关系的行，保证了数据的完整性、一致性。

定义数据表tb_emp5，让它的键deptId作为外键关联到tb_dept1的主键id：

1、  创建一个部门表tb_dept1，SQL语句如下
mysql> CREATE TABLE tb_dept1
    -> (
    -> id       INT(11) PRIMARY KEY,
    -> name    VARCHAR(22)  NOT NULL,
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

2、创建数据表tb_emp5SQL语句为

mysql> CREATE TABLE tb_emp5
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name   VARCHAR(25),
    -> deptId  INT(11),
    -> salary   FLOAT,
    -> CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

1.4、使用非空约束

非空约束（Not Null constraint）指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据时，没有指定值，数据库系统会报错。

字段名 数据类型 not null

定义数据表tb_emp6，指定员工的名称不能为空，SQL语句如下：

mysql> CREATE TABLE tb_emp6
    -> (
    -> id     INT(11) PRIMARY KEY,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

1.5、使用唯一性约束

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

1、在定义完列之后，直接制定唯一约束

字段名 数据类型 UNIQUE

定义数据表tb_dept2，指定部门的名称唯一，SQL语句如下：

mysql> CREATE TABLE tb_dept2
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name    VARCHAR(22) UNIQUE,
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

2、在定义完所有的列之后，制定唯一约束

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

定义数据表tb_dept3，指定部门的名称唯一，SQL语句如下：

mysql> CREATE TABLE tb_dept3
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name    VARCHAR(22),
    -> location  VARCHAR(50),
    -> CONSTRAINT STH UNIQUE(name)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

1.6、使用默认约束

默认约束（Default Constraint）指定某列的默认值。

字段名 数据类型 DEFAULT 默认值

定义数据表tb_emp7，指定员工的部门编号默认为1111，SQL语句如下：

mysql> CREATE TABLE tb_emp7
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11) DEFAULT 1111,
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

唯一UNIQUE约束和主键PRIMARY KEY约束的区别：
一个表中可以有多个字段声明为UNIQUE，但是只能有一个PRIMARY KEY  声明。
声明为PRIMAY KEY的列不可以有空值，但是声明为UNIQUE的字段可以有空值存在。

1.7、设置表的属性值自动增加

在数据库应用中，经常希望在每次插入新记录时，系统就会自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现。

一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。

字段名 数据类型 AUTO_INCREMENT

定义数据表tb_emp8，指定员工的编号自动递增，SQL语句如下：

mysql> CREATE TABLE tb_emp8
    -> (
    -> id      INT(11) PRIMARY KEY AUTO_INCREMENT,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

2、查看数据表结构

2.1、查看表基本结构语句DESCRIBE/DESC

该语句可以查看表的字段信息，其中包括：字段名、字段数据类型、是否为主键、是否有默认值等。

DESCRIBE <表名>/DESC <表名>

1、DESCRIBE

使用DESCRIBE查看表tb_dept1的表结构，SQL语句如下：

mysql> DESCRIBE tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、DESC

使用DESC查看表tb_emp1的表结构，SQL语句如下：

mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int         | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2.2、查看数据表详细的结构语句SHOW CREATE TABLE

SHOW CREATE TABLE语句可以用来显示数据表的创建语句。

SHOW CREATE TABLE <表名\G>

使用SHOW CREATE TABLE查看表tb_emp1的详细信息，SQL语句如下：

使用参数’\G’之后的结果如下

mysql> SHOW CREATE TABLE tb_emp1\G;
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

从查询创建数据表详细的查询语句来看：使用了‘\G’可以使显示更加的清晰。

3、修改数据表

3.1、修改数据表名称

MySQL是通过ALTER TABLE语句来实现表名的修改的。

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

将数据表tb_dept3改名为tb_deptment3。

mysql> ALTER TABLE tb_dept3 RENAME tb_deptment3;
Query OK, 0 rows affected (0.01 sec)

3.2、修改字段的数据类型

修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。

ALTER TABLE <表名> MODIFY <字段名>  <数据类型>

将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。

1、执行修改表名操作之前，使用DESC查看tb_dept表结构，结果如下：

mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、执行修改表名操作

mysql> ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、执行修改表名操作之后，使用DESC查看tb_dept表结构，结果如下：

mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

可以看到数据表tb_dept1的name字段的数据类型由VARCHAR(22)修改成了VARCHAR(30)。

3.3、修改字段名称

MySQL中修改表字段名的语法规则如下：

ALTER TABLE <表名> 
CHANGE <旧字段名> <新字段名> <新数据类型>;

将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，SQL语句如下：

mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，SQL语句如下：

mysql> ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(60) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.4、添加字段

在已经存在的表添加新的字段。

ALTER TABLE <表名> 
ADD <新字段名> <数据类型> 
[约束条件] [FIRST | AFTER 已存在字段名];

1、添加无完整性约束条件的字段

在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId，SQL语句如下：

mysql> ALTER TABLE tb_dept1 ADD managerId INT(10);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2、添加有完整性约束条件的字段

在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：

mysql> ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3、在数据表的第一列添加一个字段

在数据表tb_dept1中添加一个INT类型的字段column2，SQL语句如下：

mysql>  ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql>  DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2   | int         | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

4、在数据表的制定的列之后添加一个字段

在数据表tb_dept1中name列后添加一个INT类型的字段column3，SQL语句如下：

mysql> ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| collumn2  | int         | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

MySQL数据表的基本操作和基于 MySQL数据表的基本操作的综合实例项目（上）