MySQL的数据表操作
数据表操作
每一张数据表都相当于一个文件,在数据表中又分为表结构与表记录。表结构:包括存储引擎,字段,主外键类型,约束性条件,字符编码等
表记录:数据表中的每一行数据(不包含字段行)idnamegenderage1Alicefemale18
2Bobmale17
3Carolmale16
创建数据表
创建数据表其实大有讲究,它包括表名称,表字段,存储引擎,主外键类型,约束性条件,字符编码等。
如果InnoDB数据表没有创建主键,那么MySQL会自动创建一个以行号为准的隐藏主键。#语法: []为可选
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
) [chrset="字符编码"];
#注意:
1. 在同一张表中,字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
4. 表中最后一个字段不要加逗号
以下示例将演示在school数据库中创建student数据表。mysql> use school;
Database changed
mysql> create table student(
-> name varchar(32),
-> gender enum("male", "female"),
-> age smallint
-> );
Query OK, 0 rows affected (0.18 sec)
也可以不进入数据库在外部或另外的库中进行创建,那么创建时就应该指定数据库。create table 数据库名.新建的数据表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件]
);
查看数据表
在某一数据库中使用show tables;可查看该库下的所有数据表。
使用show create table 表名;可查看该表的创建信息。
使用desc 表名;可查看该表的表结构,包括字段,类型,约束条件等信息。mysql> show tables; # 查看当前库下所有表名
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.01 sec)mysql> show create table student; # 查看student表的创建信息
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`name` varchar(32) DEFAULT NULL,
`gender` enum('male','female') DEFAULT NULL,
`age` smallint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
mysql> show create table student \G; # 使用\G可将其转换为一行显示
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`name` varchar(32) DEFAULT NULL,
`gender` enum('male','female') DEFAULT NULL,
`age` smallint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specifiedmysql> desc student; # 查看表结构
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改表名字
使用alter table 旧表名 rename 新表名;可修改表名
以下示例将展示将studnet表名修改为students。mysql> alter table student rename students;
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| students |
+------------------+
1 row in set (0.00 sec)
清空数据表
使用truncate 表名可将表中所有记录清空,并将部分结构进行重置(如自增字段会恢复至初始值)。
以下示例将演示创建出一张temp表并在其中插入一些数据后进行清空操作。mysql> create table temp(id smallint); # 新建temp表
Query OK, 0 rows affected (0.04 sec)
mysql> select * from temp;
Empty set (0.00 sec)
mysql> insert into temp values (1),(2); # 插入数据
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> truncate temp; # 清空操作
Query OK, 0 rows affected (0.11 sec)
mysql> select * from temp;
Empty set (0.01 sec)
删除数据表
使用drop table 表名;可删除某一数据表,也可使用drop tables 表名1,表名2,表名n进行批量删除的操作。
以下示例将演示创建出一个temp表再将其进行删除的操作。mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| students |
| temp |
+------------------+
2 rows in set (0.00 sec)
mysql> drop table temp; # 删除temp表
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| students |
+------------------+
1 row in set (0.00 sec)
复制表操作
结构复制mysql> create table temp like students; # 结构复制
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| students |
| temp |
+------------------+
2 rows in set (0.00 sec)
mysql> desc temp;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
全部复制
又要复制表结构,又要复制表记录,则使用以下语句(不会复制主键,外键,索引)。mysql> create table temp select * from students;
Query OK, 0 rows affected (0.04 sec)
选择复制
选择某一字段及其记录进行复制,可使用以下语句。mysql> create table temp select host,user from mysql.user;
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
5 rows in set (0.00 sec)
表字段操作
表字段是属于表结构的一部分,可以将他作为文档的标题。
其标题下的一行均属于当前字段下的数据。
新增字段# ==== 增加多个字段 ====
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
# ==== 增加单个字段,排在最前面 ====
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
# ==== 增加单个字段,排在某一字段后面 ====
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
以下示例将展示为students表新增一个名为id的非空字段,该字段放在最前面,并且在age字段后新增class字段。mysql> alter table students
-> add id mediumint not null first,
-> add class varchar(12) not null after age
-> ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | mediumint | NO | | NULL | |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint | YES | | NULL | |
| class | varchar(12) | NO | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段
修改字段分为修改字段名或者修改其数据类型。# ==== MODIFY只能修改数据类型及其完整性约束条件 ====
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
# ==== CHANGE能修改字段名、数据类型及其完整性约束条件 ====
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
以下示例将展示修改id字段为自增主键,并将其名字修改为stu_id。mysql> alter table students
-> change id stu_id mediumint not null primary key auto_increment first
-> ;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| stu_id | mediumint | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint | YES | | NULL | |
| class | varchar(12) | NO | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
如果不修改名字只修改其原本的类型或完整性约束条件,可使用modify进行操作。
删除字段
使用以下命令可删除某一字段。ALTER TABLE 表名
DROP 字段名;
以下示例将展示删除class字段。mysql> alter table students drop class;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| stu_id | mediumint | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
其他操作
存储引擎
以下示例将展示如何将数据表students存储引擎修改为memory。mysql> alter table students engine="memory";
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table students;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`stu_id` mediumint NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`gender` enum('male','female') DEFAULT NULL,
`age` smallint DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
字符编码
以下示例将展示如何将数据表students字符编码修改为gbk。mysql> alter table students charset="gbk";
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`stu_id` mediumint NOT NULL AUTO_INCREMENT,
`name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`gender` enum('male','female') CHARACTER SET utf8 DEFAULT NULL,
`age` smallint DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=MEMORY DEFAULT CHARSET=gbk |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)