MySQL的数据表操作
数据表操作
每一张数据表都相当于一个文件，在数据表中又分为表构造与表记录。
表构造：包含存储引擎，字段，主外键类型，约束性条件，字符编码等表记录：数据表中的每一行数据（不蕴含字段行）
| id | name | gender | age |
|---|---|---|---|
| 1 | Alice | female | 18 |
| 2 | Bob | male | 17 |
| 3 | Carol | male | 16 |
创立数据表
创立数据表其实大有考究，它包含表名称，表字段，存储引擎，主外键类型，约束性条件，字符编码等。
如果InnoDB数据表没有创立主键，那么MySQL会主动创立一个以行号为准的暗藏主键。
#语法: []为可选create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]) [chrset="字符编码"];#留神:1. 在同一张表中，字段名是不能雷同2. 宽度和约束条件可选3. 字段名和类型是必须的4. 表中最初一个字段不要加逗号以下示例将演示在school数据库中创立student数据表。
mysql> use school;Database changedmysql> 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: studentCreate Table: CREATE TABLE `student` ( `name` varchar(32) DEFAULT NULL, `gender` enum('male','female') DEFAULT NULL, `age` smallint DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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)