MySQL对数据表的基本操作
基本语法
在 MySQL 中,可以使用 CREATE TABLE 语句创建表。其语法格式为:
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
其中,[表定义选项]的格式为:
<列名1> <类型1> [,…] <列名n> <类型n>
CREATE TABLE 命令语法比较多,其主要是由表创建定义(create-definition)、表选项(table-options)和分区选项(partition-options)所组成的。
这里首先描述一个简单的新建表的例子,然后重点介绍 CREATE TABLE 命令中的一些主要的语法知识点。
CREATE TABLE 语句的主要语法及使用说明如下:
- CREATE TABLE:用于创建给定名称的表,必须拥有表CREATE的权限。
- <表名>:指定要创建表的名称,在 CREATE TABLE 之后给出,必须符合标识符命名规则。表名称被指定为 db_name.tbl_name,以便在特定的数据库中创建表。无论是否有当前数据库,都可以通过这种方式创建。在当前数据库中创建表时,可以省略 db-name。如果使用加引号的识别名,则应对数据库和表名称分别加引号。例如,‘mydb’.‘mytbl’ 是合法的,但 ‘mydb.mytbl’ 不合法。
- <表定义选项>:表创建定义,由列名(col_name)、列的定义(column_definition)以及可能的空值说明、完整性约束或表索引组成。
- 默认的情况是,表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在,则会出现错误。
注意:
使用 CREATE TABLE 创建表时,必须指定以下信息:
- 要创建的表的名称不区分大小写,不能使用SQL语言中的关键字,如DROP、ALTER、INSERT等。
- 数据表中每个列(字段)的名称和数据类型,如果创建多个列,要用逗号隔开。
在指定的数据库中创建表
数据表属于数据库,在创建数据表之前,应使用语句“USE<数据库>”指定操作在哪个数据库中进行,如果没有选择数据库,就会抛出 No database selected 的错误。
【实例 1】创建员工表 tb_emp1,结构如下表所示。
字段名称 | 数据类型 | 备注 |
---|---|---|
id | INT(ll) | 员工编号 |
name | VARCHAR(25) | 员工名称 |
deptld | INT(ll) | 所在部门编号 |
salary | FLOAT | 工资 |
选择创建表的数据库 test_db,创建 tb_emp1 数据表,输入的 SQL 语句和运行结果如下所示。
mysql> USE test_db;
Database changed
mysql> CREATE TABLE tb_emp1
-> (
-> id INT(11),
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT
-> );
Query OK, 0 rows affected (0.37 sec)
注意:
使用 SHOW CREATE TABLE 语句不仅可以查看创建表时的详细语句,而且可以查看存储引擎和字符编码。如果不加“\G”参数,显示的结果可能非常混乱,加上“\G”参数之后,可使显示的结果更加直观,易于查看。
修改数据表
语法格式如下:
ALTER TABLE <表名> [修改选项]
修改选项的语法格式如下:
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }
修改表名
MySQL 通过 ALTER TABLE 语句来实现表名的修改,语法规则如下:
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
例 1
使用 ALTER TABLE 将数据表 student 改名为 tb_students_info,SQL 语句和运行结果如下所示。
mysql> ALTER TABLE student RENAME TO tb_students_info;
注意:
修改表名并不修改表的结构,因此修改名称后的表和修改名称前的表的结构是相同的。用户可以使用 DESC 命令查看修改后的表结构,
修改表字符集
MySQL 通过 ALTER TABLE 语句来实现表字符集的修改,语法规则如下:
ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;
其中,DEFAULT 为可选参数,使用与否均不影响结果。
例 2
使用 ALTER TABLE 将数据表 tb_students_info 的字符集修改为 gb2312,校对规则修改为 gb2312_chinese_ci。SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_students_info CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;
修改/删除字段
修改字段名称
MySQL 中修改表字段名的语法规则如下:
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
其中:
- 旧字段名:指修改前的字段名;
- 新字段名:指修改后的字段名;
- 新数据类型:指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样,但数据类型不能为空。
例 1
使用 ALTER TABLE 修改表 tb_emp1 的结构,将 col1 字段名称改为 col3,同时将数据类型变为 CHAR(30),SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1
-> CHANGE col1 col3 CHAR(30);
提示:
由于不同类型的数据在机器中的存储方式及长度并不相同,修改数据类型可能会影响数据表中已有的数据记录,因此,当数据表中已经有数据时,不要轻易修改数据类型。
修改字段数据类型
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下:
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中:
- 表名:指要修改数据类型的字段所在表的名称;
- 字段名:指需要修改的字段;
- 数据类型:指修改后字段的新数据类型。
例 2
使用 ALTER TABLE 修改表 tb_emp1 的结构,将 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30),SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1
-> MODIFY name VARCHAR(30);
删除字段
删除字段是将数据表中的某个字段从表中移除,语法格式如下:
ALTER TABLE <表名> DROP <字段名>;
其中,“字段名”指需要从表中删除的字段的名称。
例 3
使用 ALTER TABLE 修改表 tb_emp1 的结构,删除 col2 字段,SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1
-> DROP col2;
删除数据表(DORP TABLE语句)
基本语法
使用 DROP TABLE 语句可以删除一个或多个数据表,语法格式如下:
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
对语法格式的说明如下:
- 表名1, 表名2, 表名3 …表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表,只要将表名依次写在后面,相互之间用逗号隔开即可。
- IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS,当数据表不存在时 MySQL 将提示错误,中断 SQL 语句的执行;加上 IF EXISTS 后,当数据表不存在时 SQL 语句可以顺利执行,但是会发出警告(warning)。
两点注意:
- 用户必须拥有执行 DROP TABLE 命令的权限,否则数据表不会被删除。
- 表被删除时,用户在该表上的权限不会自动删除。
删除表的实例
选择数据库 test_db,创建 tb_emp3 数据表,输入的 SQL 语句和运行结果如下所示。
mysql> USE test_db;
Database changed
mysql> CREATE TABLE tb_emp3
-> (
-> id INT(11),
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT
-> );
删除被其它表关联的主表
删除父表有以下两种方法:
- 先删除与它关联的子表,再删除父表;但是这样会同时删除两个表中的数据。
- 将关联表的外键约束取消,再删除父表;适用于需要保留子表的数据,只删除父表的情况。
下面介绍了如何取消关联表的外键约束并删除主表,也就是上面所说的删除父表的第二种方法。
在数据库中创建两个关联表。创建表 tb_emp4 的 SQL 语句如下:
CREATE TABLE tb_emp4
(
#创建主键
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR (50)
);
接下来创建表 tb_emp5,SQL 语句如下:
CREATE TABLE tb_emp5
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
#创建外键的语法
CONSTRAINT fk_emp4_emp5 FOREIGN KEY (deptId) REFERENCES tb_emp4(id)
);
在DataGrip上使用命令创建外键的命令如下:
语法如下:
alter table (需要创建外键的表名) add constraint (需要创建外键的表的外键字段名)
foreign key (需要创建外键表的字段名
) references 需要连接外键表的表名(需要连接外键表的字段名
);
alter table product add constraint `ser_id` foreign key (`series_id`) references series(`ser_id`);