【MySQL】2.数据表的操作
创建数据表
在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。
创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。
在MySQL中,可以使用 CREATE TABLE 语句创建表。其语法格式为：
CREATE TABLE IF NOT EXISTS ( 字段1 数据类型[字段属性|约束][索引][注释], 字段2 数据类型[字段属性|约束][索引][注释], ... 字段n 数据类型[字段属性|约束][索引][注释] #多字段联合主键 PRIMARY KEY(字段1,字段2) )[表选项][CHARSET='表字符集'][COMMENT='注释'];注意:
-
在MYSQL中,如果使用的数据库名、表名、字段名等与保留字冲突，需使用’‘括起来。在MYSQL自动生成的代码中表名或字段名等全部使用’'括起来
-
使用CREATE TABLE语句创建表时，多字段之间使用,分隔，最后一个字段后无需逗号
-
常用的注释方式
- 单行注释：#注释内容
- 多行注释：/* 多行注释 */
-
表中的字段也成为列
字段的约束及属性
数据完整性是指数据的准确性和一致性。MYSQL提供了约束机制以保证完整性。
常用的字段属性约束：
| 字段属性约束名 | 关键字 |
|---|---|
| NOT NULL | 数据列中不允许包含空值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMAENT | 自动递增 |
| UNIQUE KEY | 唯一约束 |
| FOREIGN KEY | 外键约束 |
| UNSIGNED | 无符号数 |
示例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'密码',studentNameVARCHAR(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）。随着业务的变化，可能需要在已有的表中添加新的字段。
-
在末尾添加字段
ALTER TABLE ADD [约束条件]; -
在开头添加字段
MySQL 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字，语法格式如下： FIRST 一般放在语句末尾
ALTER TABLE ADD [约束条件] FIRST; -
在中间添加字段
MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字： 只能在某个已有字段的后面添加新字段，不能在它的前面添加新字段。
ALTER TABLE ADD [约束条件] AFTER ;
修改字段
-
修改字段名称
ALTER TABLE CHANGE ；- 旧字段名：指修改前的字段名；
- 新字段名：指修改后的字段名；
- 新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
-
修改字段数据类型
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_emp1Create 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=gb23121 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)
