MySQL 数据表的基本操作

创建数据表

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

创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。

在MySQL中,可以使用 CREATE TABLE 语句创建表。其语法格式为：

 CREATE TABLE IF NOT EXISTS <表名> (
  字段1 数据类型[字段属性|约束][索引][注释],
  字段2 数据类型[字段属性|约束][索引][注释],
     ...
  字段n 数据类型[字段属性|约束][索引][注释]
   #多字段联合主键
   PRIMARY KEY(字段1,字段2)  
 )[表选项][CHARSET='表字符集'][COMMENT='注释'];

注意:

1. 在MYSQL中,如果使用的数据库名、表名、字段名等与保留字冲突，需使用''括起来。在MYSQL自动生成的代码中表名或字段名等全部使用''括起来

2. 使用CREATE TABLE语句创建表时，多字段之间使用,分隔，最后一个字段后无需逗号

3. 常用的注释方式

   • 单行注释：#注释内容

   • 多行注释：/* 多行注释 */

4. 表中的字段也成为列

字段的约束及属性

数据完整性是指数据的准确性和一致性。MYSQL提供了约束机制以保证完整性。

常用的字段属性约束：

示例1: 在指定数据库中创建表

数据表属于数据库，在创建数据表之前，应使用语句“USE<数据库>”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出 No database selected 的错误。

选择创建表的数据库 test_db，创建学生表 student_db，输入的 SQL 语句和运行结果如下所示。

 mysql>CREATE TABLE IF NOT EXISTS student_tb(
 stuNo INT(4) NOT NULL COMMENT'学号' PRIMARY KEY, #非空,主键
 loginpwd VARCHAR(20) NOT NULL COMMENT'密码',
 studentName VARCHAR(50) NOT NULL COMMENT'学生姓名',
 sex CHAR(2) DEFAULT'男' NOT NULL COMMENT'性别', #非空,默认男
 gradeId INT(4) UNSIGNED COMMENT'年级编号',  #无符号数 
 phone VARCHAR(50) COMMENT'联系电话',
 address VARCHAR(255) DEFAULT'地址不详' COMMENT'地址', #默认值"地址不详"
 birthday DATETIME COMMENT'出生日期',
 email VARCHAR(50) COMMENT'邮件账号',
 identityCard VARCHAR(18) UNIQUE KEY COMMENT'身份证号' #唯一
 )COMMENT="学生表"
 ​
 #查看表结构
 mysql> desc student_tb;
 +--------------+-----------------+------+-----+----------+-------+
 | Field        | Type            | Null | Key | Default  | Extra |
 +--------------+-----------------+------+-----+----------+-------+
 | stuNo        | int(4)          | NO   | PRI | NULL     |       |
 | loginpwd     | varchar(20)     | NO   |     | NULL     |       |
 | studentName  | varchar(50)     | NO   |     | NULL     |       |
 | sex          | char(2)         | NO   |     | 男       |       |
 | gradeId      | int(4) unsigned | YES  |     | NULL     |       |
 | phone        | varchar(50)     | YES  |     | NULL     |       |
 | address      | varchar(255)    | YES  |     | 地址不详 |       |
 | birthday     | datetime        | YES  |     | NULL     |       |
 | email        | varchar(50)     | YES  |     | NULL     |       |
 | identityCard | varchar(18)     | YES  | UNI | NULL     |       |
 +--------------+-----------------+------+-----+----------+-------+
 10 rows in set (0.00 sec)

修改数据表

修改数据表的前提是数据库中已经存在该表。修改表指的是修改数据库中已经存在的数据表的结构。

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。 其语法格式如下：

 ALTER TABLE <表名> [修改选项]
 ​
 #修改表名  
 ALTER TABLE <旧表名>  RENAME <新表名>
 #修改字符集  
 ALTER TABLE 表名  CHARACTER SET <字符集名>  COLLATE <校对规则名>;

添加字段

MySQL数据表是由行和列构成的，通常把表的“列”称为字段（Field），把表的“行”称为记录（Record）。随着业务的变化，可能需要在已有的表中添加新的字段。

1. 在末尾添加字段

     ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件]; 

   
   

2. 在开头添加字段

   MySQL 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字，语法格式如下： FIRST 一般放在语句末尾

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

   
   

3. 在中间添加字段

   MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字： 只能在某个已有字段的后面添加新字段，不能在它的前面添加新字段。

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

修改字段

1. 修改字段名称

    ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型><字段约束>；

   • 旧字段名：指修改前的字段名；

   • 新字段名：指修改后的字段名；

   • 新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

2. 修改字段数据类型

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

   • 表名：指要修改数据类型的字段所在表的名称；

   • 字段名：指需要修改的字段；

   • 数据类型：指修改后字段的新数据类型。

删除字段

删除字段是将数据表中的某个字段从表中移除，语法格式如下：

ALTER TABLE <表名> DROP <字段名>；

删除数据表

在MySQL数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。

在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。 语法如下:

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

• 表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。

• IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

• 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。

• 表被删除时，用户在该表上的权限不会自动删除。

  
  

查看表结构

以表格形式展示表结构

DESCRIBE/DESC 语句会以表格的形式来展示表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法格式如下：

DESCRIBE <表名>;
#或简写成
DESC <表名>;

示例1: 分别使用 DESCRIBE 和 DESC 查看表 tb_emp1 的表结构，SQL 语句和运行结果如下：

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

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

可以看出两种方式没有区别,各个字段含义如下:

• Null：表示该列是否可以存储 NULL 值。

• Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。

• Default：表示该列是否有默认值，如果有，值是多少。

• Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

以SQL语句展示表结构

SHOW CREATE TABLE 命令会以 SQL 语句的形式来展示表信息，可以查看表的存储引擎和字符编码；可以通过\g或者\G参数来控制展示格式(添加\g或\G参数结尾不能写;,虽然可以查询出来结果,但是也会抛出错误ERROR:No query specified)。 语法如下：

SHOW CREATE TABLE <表名> [\g \G]

示例1 使用 SHOW CREATE TABLE 语句查看表 tb_emp1 的详细信息

 mysql> SHOW CREATE TABLE tb_emp1;
 +---------+------------------------------------------------+
 | Table   | Create Table                                   |
 +---------+------------------------------------------------+
 | tb_emp1 | CREATE TABLE `tb_emp1` (
   `id` int(11) DEFAULT NULL,
   `name` varchar(25) DEFAULT NULL,
   `salary` float DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
 +---------+------------------------------------------------+
 1 row in set (0.01 sec)

示例2 SHOW CREATE TABLE 使用\G结尾的 SQL 语句

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

示例3 SHOW CREATE TABLE 使用\g结尾的 SQL 语句

 mysql> SHOW CREATE TABLE tb_emp1 \g;
 +---------+------------------------------------------------+
 | Table   | Create Table                                   |
 +---------+------------------------------------------------+
 | tb_emp1 | CREATE TABLE `tb_emp1` (
   `id` int(11) DEFAULT NULL,
   `name` varchar(25) DEFAULT NULL,
   `salary` float DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
 +---------+------------------------------------------------+
 1 row in set (0.00 sec)