数据表的简单操作
1. 实验介绍
1.1 实验内容
在本节内容中，我们将带领大家学习数据表的创建等操作，以及对数据的增删改等内容。
1.2 实验知识点
- 约束
- 创建表
- 修改表的定义
- 查看表
- 删除表
- 数据的增删改操作
2. SHOW TABLES 和 DESCRIBE TABLE
- SHOW TABLES
在指定某个数据库之后，我们可以查看数据库中的表的列表，使用如下语法：
SHOW [FULL] TABLES
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
在未指定 db_name 即数据库时，使用默认的数据库。可选的 [FULL] 修饰符会显示 Table_type 列。而 [LIKE 'pattern' | WHERE expr] 等用法将在下一节的内容中介绍到。
例如，我们查看 mysql 数据库中的表
mysql> SHOW FULL TABLES IN mysql;
+-----------------+------------+
| Tables_in_mysql | Table_type |
+-----------------+------------+
| columns_priv | BASE TABLE |
| db | BASE TABLE |
| event | BASE TABLE |
| func | BASE TABLE |
| general_log | BASE TABLE |
| help_category | BASE TABLE |
...
mysql>
- DESCRIBE TABLE
DESCRIBE 一般用来列出指定表或视图中的所有列，缩写为 DESC。
例如，我们查看 mysql 数据库中的 db 表：
mysql> DESC mysql.db;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
...
# 使用 \G 查看
mysql> desc mysql.db \G
********* 1. row *********
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
********* 2. row *********
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
3. 创建表
创建一个表的语法大致如下：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(
col_name column_definition,
col_name column_definition,
col_name column_definition,
...
)
[table_options];
可选项 [TEMPORARY] 代表临时的意思，即创建一个临时的表，在与 MySQL 服务器断开连接后表就会被删除。即只在当前会话中使用。
tbl_name 为表名
col_name column_definition 分别为字段名和字段的定义，即列。在列的定义中，我们可以使用 [AUTO_INCREMENT] 定义自增（即为整数时，自动加 1），或者定义约束，以及给出注释语句。
3.1 约束
在上一节的内容中我们有讲到数据类型的一些基础知识。在这里我们引入一个 约束的概念。
约束的定义顾名思义就是限制，除了可以在定义列的时候进行约束，还可以针对整个表进行约束定义。前一种只能针对列。
对于前面提到的 column_definition 字段定义，一般由两种组成，一个为数据类型，另一个即为约束。
同样对于数据库来说，要保证数据库的完整性（数据的正确性和相容性），我们可以通过一些约束条件来确保它的完整性。
首先，我们了解一下关于 MySQL 中的几种约束：
| 类型 | 关键字主键 |
|---|---|
| 主键 | PRIMARY KEY |
| 外键 | FOREIGN KEY |
| 默认值 | DEFAULT |
| 唯一 | UNIQUE |
| 非空 | NOT NULL |
主键 主键 (PRIMARY KEY) 用来表示一列中的每个值都是唯一的，或者多个列（可以使用多个列一起作为主键）。
外键 外键 (FOREIGN KEY) 既能确保数据完整性，也能表现表之间的关系。
一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的列或者一组列，因此被称为参照完整性约束。被外键约束的列，取值必须在它参考的列中有对应值。并且定义外键时所引用的表中必须有主键，或者唯一约束中的一项。
非空 非空约束 (NOT NULL)，听名字就能理解，被非空约束的列，在插入值时必须非空。对应的还有 NULL，即可以为空，属于默认项。
主键等默认有非空约束
默认 默认值 (DEFAULT)，一般与非空约束搭配使用，即插入数据时，未提供该列的数据时，使用默认值，未指定时，默认值为 NULL
唯一 唯一约束 (UNIQUE) 比较简单，它规定一张表中指定的一列的值不能有重复值，即这一列每个值都是唯一的。但是可以使用多个 NULL 值。
检查 检查约束(CHECK)，一旦表中某列设置了检查约束，则在向表中添加数据时，会使用这个约束对输入的数据按照设置的逻辑进行检查。对于检查约束而言，对于目前的 MySQL 数据库来说，会被直接忽略掉，也就是说，即使你定义了该约束，它也是无效的
例如，我们创建一张学生表（student）。有三个字段，并且分别设置主键约束，默认约束，以及唯一。
mysql> CREATE TABLE student(id INT PRIMARY KEY, name VARCHAR(10) DEFAULT "shiyanlou", address VARCHAR(30) UNIQUE);
Query OK, 0 rows affected (0.16 sec)
mysql> DESC student\G
********* 1. row *********
Field: id
Type: int(11)
Null: NO
Key: PRI
Default: NULL
Extra:
********* 2. row *********
Field: name
Type: varchar(10)
Null: YES
Key:
Default: shiyanlou
Extra:
********* 3. row *********
Field: address
Type: varchar(30)
Null: YES
Key: UNI
Default: NULL
Extra:
3 rows in set (0.00 sec)
我们通过 DESC 语句，查看显示信息中的 Key 可以看到 PRI 和 UNI 即主键和唯一值约束，而默认值 Default 以及 NULL 等信息，显示默认值和非空约束。如上对于主键的描述，我们可以看到主键 id 我们并未设定非空约束，而是默认不可以为空，而后面的默认值 Default 中的 NULL 是无效的，因为主键不能为 NULL。
关于更多有关约束的内容我们将在课程 SQL的高级特性 一节中讲到
3.2 查看创建表的语句
跟数据库一样，我们可以查看创建数据表，使用如下语法格式：
SHOW CREATE TABLE tbl_name
例如，我们查看 student 表的创建语句。如下所示：
mysql> SHOW CREATE TABLE student\G
********* 1. row *********
Table: student
Create Table: CREATE TABLE student (
id int(11) NOT NULL,
name varchar(10) DEFAULT 'shiyanlou',
address varchar(30) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY address (address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MySQL 的解析和优化
这里我们列举出一段的 SQL 语句，以及 MySQL 解析后进行优化后的语句。
# 之前我们创建 student 表语句如下
CREATE TABLE student(
id int PRIMARY KEY,
name varchar(10) DEFAULT "shiyanlou",
address varchar(30) UNIQUE
);
# 经过 MySQL 解析优化后的语句如下
CREATE TABLE student (
id int(11) NOT NULL,
name varchar(10) DEFAULT 'shiyanlou',
address varchar(30) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY address (address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
如上所示，除了反引号的使用之外。我们还可以看出定义约束的不同，例如，默认值和非空约束，适合在列级定义，而主键，唯一等适合在表级结构进行约束定义。并且，还可以设定默认的字符集，以及数据库引擎（ENGINE）等信息，即语法格式中的 [table_options]。
3.3 修改表的定义
对于上述的表来说，我们可以修改表的结构。例如添加或删除列，更改现有列的类型或者重命名列和表等操作。简单语法的使用如下：
ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
对于 alter_specification 的解释太过复杂，下面让我们通过示例来学习相关的内容。这里需要注意的是，修改表的定义语句时，表内是否有数据等会造成一定影响，需要根据实际情况进行考虑
修改表名 对于 student 表来说，我想将其名称修改为 students 即，加一个 s 。重命名表的语法格式如下
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
[TO|AS] 是可选项。使不使用没有什么区别，下面我们修改 student 表的表名
mysql> ALTER TABLE student RENAME students;
Query OK, 0 rows affected (0.29 sec)
mysql> SHOW TABLES;
+------------------------+
| Tables_in_shiyanlou001 |
+------------------------+
| students |
+------------------------+
1 row in set (0.00 sec)
我们还可以添加列，使用如下语法：
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)
例如，我们给 students 表添加年龄（age）和成绩（grade) 字段:
mysql> ALTER TABLE students ADD COLUMN (age INT,grade INT);
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看 students 表的列，
mysql> DESC students;
+---------+-------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-----------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | shiyanlou | |
| address | varchar(30) | YES | UNI | NULL | |
| age | int(11) | YES | | NULL | |
| grade | int(11) | YES | | NULL | |
+---------+-------------+------+-----+-----------+-------+
5 rows in set (0.00 sec)
我们可以看到此时 students 表中已经有 age 列和 grade 列了。
除此之外，我们还可以删除列，语法如下：
ALTER TABLE tbl_name DROP [COLUMN] col_name
例如，删除我们刚刚创建的 grade 列
mysql> ALTER TABLE students DROP grade;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+---------+-------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-----------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | shiyanlou | |
| address | varchar(30) | YES | UNI | NULL | |
| age | int(11) | YES | | NULL | |
+---------+-------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)
修改列的数据类型
- 设置或取消设置默认值
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
例如设置年龄 age 的默认值为 18
mysql> ALTER TABLE students ALTER age SET DEFAULT 18;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+---------+-------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-----------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | shiyanlou | |
| address | varchar(30) | YES | UNI | NULL | |
| age | int(11) | YES | | 18 | |
+---------+-------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)
- 修改列的定义
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition
例如，修改 age 的数据类型为 TINYINT，并且不能为空，默认值为 20
mysql> ALTER TABLE students change age age tinyint not null DEFAULT 20;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+---------+-------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-----------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | shiyanlou | |
| address | varchar(30) | YES | UNI | NULL | |
| age | tinyint(4) | NO | | 20 | |
+---------+-------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)
3.4 删除表
删除表的语法如下：
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
这里，我们删除我们创建的学生表，
mysql> DROP TABLE students;
Query OK, 0 rows affected (0.34 sec)
mysql> SHOW TABLES;
Empty set (0.00 sec)
数据库表的基本操作视频：
4. 数据的增删改
4.1 插入数据
对于插入数据的语法大致为
INSERT [INTO]
tbl_name [(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)]
对于插入数据时，我们可以根据指定字段的顺序进行插入，也可以使用默认的字段顺序。并且可以一次插入多条记录。
这里，我们再次创建学生表，并插入数据：
mysql> CREATE TABLE student(id INT PRIMARY KEY, name VARCHAR(10) not null, address VARCHAR(30) UNIQUE);
Query OK, 0 rows affected (0.05 sec)
# 默认值的顺序，即 id,name,address
mysql> INSERT INTO student VALUE(1,"xiaoming","chengdu city");
Query OK, 1 row affected (0.02 sec)
# 手动指定
mysql> INSERT INTO student(name,id,address) VALUE("xiaohong",2,"shanghai");
Query OK, 1 row affected (0.03 sec)
# 插入多条记录
mysql> INSERT INTO student VALUE (3,"zhangsan","beijing"),(4,"lisi","shenzhen");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
最后，我们可以查看表中的记录，关于查询的详细内容，将会在下一节内容中学习。
mysql> SELECT * FROM student;
+----+----------+--------------+
| id | name | address |
+----+----------+--------------+
| 1 | xiaoming | chengdu city |
| 2 | xiaohong | shanghai |
| 3 | zhangsan | beijing |
| 4 | lisi | shenzhen |
+----+----------+--------------+
4 rows in set (0.00 sec)
4.2 更新
使用 UPDATE 更新表中的一行或多行内容，使用语法如下：
UPDATE tbl_name
SET col_name = VALUE [,col_name = VALUE]
[WHERE where_condition]
通过 SET 指定修改行中某个字段的数据，WHERE 表达式可以限定某一行或者该条件下的多行，否则对所有行进行修改。如下示例
# 修改所有行 name 为 shiyanlou
mysql> UPDATE student SET name="shiyanlou";
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM student;
+----+-----------+--------------+
| id | name | address |
+----+-----------+--------------+
| 1 | shiyanlou | chengdu city |
| 2 | shiyanlou | shanghai |
| 3 | shiyanlou | beijing |
| 4 | shiyanlou | shenzhen |
+----+-----------+--------------+
4 rows in set (0.00 sec)
# 指定修改 id=1 的行
mysql> UPDATE student SET name="xiaohong",address="qingdao" WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM student;
+----+-----------+----------+
| id | name | address |
+----+-----------+----------+
| 1 | xiaohong | qingdao |
| 2 | shiyanlou | shanghai |
| 3 | shiyanlou | beijing |
| 4 | shiyanlou | shenzhen |
+----+-----------+----------+
4 rows in set (0.00 sec)
mysql>
4.3 删除
删除数据的语法跟更新类似，如下所示：
DELETE FROM tbl_name [WHERE where_condition]
如下示例：
# 删除 id=4 的行
mysql> DELETE FROM student WHERE id=4;
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM student;
+----+-----------+----------+
| id | name | address |
+----+-----------+----------+
| 1 | xiaohong | qingdao |
| 2 | shiyanlou | shanghai |
| 3 | shiyanlou | beijing |
+----+-----------+----------+
3 rows in set (0.00 sec)
# 删除所有行
mysql> DELETE FROM student;
Query OK, 3 rows affected (0.01 sec)
mysql> SELECT * FROM student;
Empty set (0.01 sec)
数据的增删改操作视频：
5. 总结
在本节内容我们介绍了关于数据表的一些操作。