关于mysql:MySQL的数据表操作

34次阅读

共计 9504 个字符，预计需要花费 24 分钟才能阅读完成。

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 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 specified
mysql> 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)

正文完
mysql
发表至： mysql
2020-09-22
 0
关于mysql:记docker中mysql导出数据库和表到本地
关于mysql:Linux环境监控工具汇总
关于mysql:GreatSQL项目捉虫活动第一期重磅来袭
关于mysql:MySQL基础