天天看点
返回

Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

时间逝去如飞，转眼间已经进入这个行业快五年了，在这期间可谓尝尽了酸甜苦辣。在不断的学习过程中也碰过不少壁，从之前的Python开发再到现在的Java开发真的是踩过了无尽的坑，很多东西学习了又忘记了，然后又不断的取再次学习再次忘记，就是在这样不断的重复中磨练了自己的意志力，同事也锻炼了自己的学习总结能力。后来真的发现，其实写博客可以不断的锻炼自己的耐心和能力，好了不多哔哔，现在开始言归正传。

Mysql好不夸张的说是当今开发世界里面使用频率最高的一门结构化查询语言了，这也很大程度是取决于其免费开源的主要原因吧。不管你是做运维的还是后端开发的，学会使用Mysql都能让你不至于在竞争激烈的乱世中饿肚子(哈哈)。

这个专栏主要是为了从头到尾的对Mysql做一个系统性的归纳总结。该专栏主要包含三个部分的内容：初级，高级(包含中级)以及扩展部分。每个部分都分别讲解一些常用的功能和使用方式，这样一来方便自己的查阅，毕竟，温故而知新嘛，假如有兴趣朋友也可以关注了解一下，假如有说的不对的地方也欢迎指正，这是一个互相学习的过程。加油……。以下是这个系列的文章，假如不出什么大的变动的话基本会按照如下的思维导图进行文章日更。欢迎大家留言互动。

Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

1、前言:

在我们的熟知的数据库中，不管是Mysql还是其他的数据库(Oracle,SQL Server等)基本都会针对表数据进行CRUD(增删改查)。但是，关于Mysql的表结构同样会对Mysql的表结构进行增删改查(这里的增删改查主要是针对Mysql的表结构)。话不多说，以下就分别谈谈咱们Mysql数据库表结构是如何进行增删改查的。

2、如何创建Mysql表

(1)、通常建立数据库表结构的方式一:

Mysql数据库表主要包含以下基本属性: 表名，字段名，字段所属的基本数据类型，某个字段是否为主键，是不是包含某个表的外键，这个表属于什么存储引擎(InnoDB,MyIsam等，以后会专门开一个专题讨论Mysql的存储引擎)等。

建表语法:

CREATE TABLE table_name (column_name column_type);

这种方式是最简单的，其中只有表名，字段名和字段类型，其它信息都是默认的，比如该表所属的存储引擎等。

案列分析:

-- 方式1
CREATE TABLE IF NOT EXISTS test_table_create(  -- 这种方式是没有则新建
   id INT UNSIGNED AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   date DATE,
   PRIMARY KEY (id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 方式2
DROP TABLE IF EXISTS test_table_create; -- 这种方式是删了重建
CREATE TABLE IF NOT EXISTS test_table_create(
   id INT UNSIGNED AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   date DATE,
   PRIMARY KEY (id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

以上两种基本建表方式按照个人需求可以酌情选择。这里我们以第一种方式去进行建表测试，在运行第一个建表方式之后再检索该表是否确实创建成功。

执行建表语句:

[SQL]CREATE TABLE IF NOT EXISTS test_table_create(  -- 这种方式是没有则新建
   id INT UNSIGNED AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   date DATE,
   PRIMARY KEY (id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
受影响的行: 0
时间: 0.061s

检索创建的表: 

Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

如上图所示，表已经创建成功，只是暂时还未向里面添加数据。为了方便测试，这里需要向里面添加一些测试数据。

添加测试数据：

INSERT INTO test_table_create(title,author,date) VALUES('《C语言》','马云',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《C#语言》','马化腾',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《Java》','李彦宏',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《Mysql》','刘强东',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《演员的自我修养》','周星驰',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《神雕侠侣》','金庸',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《武林外传》','阿猫',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《某海堡垒站》','阿狗',NOW());
INSERT INTO test_table_create(title,author,date) VALUES('《二逼练习手册》','VV',NOW());

以上数据插入的时候为什么没有添加ID这个字段？ 可能细心的小伙伴已经看出端倪了，因为在以上船舰表结构的时候，我们使用了一个Mysql关键字"AUTO_INCREMENT",并把ID设为该表的主键，因此，一个默认自增主键就油然而生啦。

Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

(2)、CREATE TABLE ... LIKE语法

用于

CREATE TABLE ... LIKE

根据另一个表的定义（包括原始表中定义的所有列属性和索引）创建一个空表：

CREATE TABLE new_tbl LIKE orig_tbl;

该副本是使用与原始表格相同版本的表格存储格式创建的。该 

SELECT

权限需要对原始表。

LIKE

 仅适用于基表，不适用于视图。

你不能执行CREATE TABLE或 CREATE TABLE ... LIKE同时 LOCK TABLES声明生效。CREATE TABLE ... LIKE进行相同的检查， CREATE TABLE而不仅仅是复制.frm文件。这意味着如果当前SQL模式与原始表创建时有效的模式不同，那么对于新模式，表定义可能被认为是无效的，并且语句将失败。

因为CREATE TABLE ... LIKE目标表保留了原始表中生成的列信息。CREATE TABLE ... LIKE不保留为原始表指定的任何 DATA DIRECTORY或INDEX DIRECTORY表选项或任何外键定义。如果原始表是一张TEMPORARY表， CREATE TABLE ... LIKE则不保留 TEMPORARY。要创建 TEMPORARY目标表，请使用 CREATE TEMPORARY TABLE ... LIKE。

测试CREATE TABLE ... LIKE创建表：

-- 测试CREATE TABLE ... LIKE创建表：
CREATE TABLE test_table_create_copy LIKE test_table_create;

-- 查看表数据(数据为空)
SELECT * FROM test_table_create_copy;


-- 查看被创建出来的表结构字段信息
show create table test_table_create_copy;

CREATE TABLE `test_table_create_copy` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author` varchar(40) NOT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意: 使用CREATE TABLE ... LIKE创建的表，仅仅是复制并新建了一张表而已，数据是没有复制的。

(3)、CREATE TABLE ... SELECT语法

语法格式: 

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

使用以上的语句同样可以创建一张新的表，只是这张新表的列是可控的，是由我们自主选择的。

演示：

-- 使用CREATE TABLE ... SELECT语法

create TABLE test_create SELECT * FROM test_table_create;


-- 查看表数据(数据为空)
SELECT * FROM test_create;


-- 查看被创建出来的表结构字段信息
show create table test_create;


CREATE TABLE `test_create` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(100) NOT NULL,
  `author` varchar(40) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意: 

这种方式创建的表相对于第二种(CREATE TABLE ... LIKE)是有区别的，第二种仅仅只是创建了表结构而已，但是使用CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; 不仅复制了表结构还复制了表数据。在开发的过程中具体怎么选择，需要我们视具体情况而定。

验证我们选择表字段新建:

-- 使用CREATE TABLE ... SELECT语法（选择表字段创建）
create TABLE test_create_1 SELECT id,title FROM test_table_create;


-- 查看表数据(数据为空)
SELECT * FROM test_create_1;


-- 查看被创建出来的表结构字段信息
show create table test_create_1;


CREATE TABLE `test_create_1` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

PS： 在建表的时候也会有一些需要注意的点，比如我们的原始表test_table_create使用了AUTO_INCREMENT添加自增属性，但是在使用 测试CREATE TABLE ... LIKE和  使用CREATE TABLE ... SELECT新建表之后，所建立的表就没有AUTO_INCREMENT字段了，取而代之的是: unsigned NOT NULL DEFAULT '0',。这是需要注意的。

(4)、关于Mysql表结构创建的总结: 

关于Mysql数据库表结构的建立暂且先讨论以上三种方式，在以后的学习使用过程中假如发现了新的方式再进行更新分享，假如有道友有好的想法和意见可以留言讨论。

2、如何删除Mysql表结构

谈到删除，肯定会想到删除表结构和删除表数据等情况。在删除表数据的时候我们会联想到Delete table,truncat table等方式，但是我们要删除表结构的话基本上就是使用Drop table关键字操作。

drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;

truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;

delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;

delete from table_name where xxx : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;

delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;

delete from 表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

(1)、 删除表结构语法: DROP TABLE

语法格式:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

演示:

验证表test_create_1存在且里面有数据。

Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

使用DROP执行删除:

DROP TABLE test_create_1;
Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

再次查看:

Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

3、如何修改Mysql数据库表结构

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

方便本次演示，以下新建一个表开始：

CREATE TABLE t1 (a INTEGER, b CHAR(10));

(1)、将表格重命名

t1

 为 

t2

 ： 

ALTER TABLE t1 RENAME t2;

(2)、若要更改列

a

 从 

INTEGER

 给

TINYINT NOT NULL

 （名字一样），并更改列 

b

 从

CHAR(10)

 到 

CHAR(20)

 以及来自重命名 

b

 到

c

 ： 

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

(3)、要添加

TIMESTAMP

 名为的新列

d

 

ALTER TABLE t2 ADD d TIMESTAMP;

(4)、在列上添加索引

d

 并 

UNIQUE

 在列上添加索引

a

 ： 

ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);

(5)、删除列

c

 ： 

ALTER TABLE t2 DROP COLUMN c;

(6)、要添加一个

AUTO_INCREMENT

 名为的新整数列

c

 ： 

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (c);

我们索引

c

（作为

PRIMARY KEY

），因为

AUTO_INCREMENT

列必须被索引，我们宣布

c

为 

NOT NULL

是因为主键列不能

NULL

。对于

NDB

表格，也可以更改用于表格或列的存储类型。例如，考虑

NDB

创建一个如下所示的表格：

mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.27 sec)

a、要将此表转换为基于磁盘的存储，可以使用以下

ALTER TABLE

 语句： 

mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (2.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */
ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

b、表格最初创建时不需要引用表空间; 但是，表空间必须被引用

ALTER TABLE

 ： 

mysql> CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.00 sec)

mysql> ALTER TABLE t2 STORAGE DISK;
ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)
mysql> ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (3.42 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */
ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

要更改单个列的存储类型，可以使用 

ALTER TABLE ... MODIFY [COLUMN]

。例如，假设您使用以下

CREATE TABLE

 语句创建两列的NDB集群磁盘数据表：

mysql> CREATE TABLE t3 (c1 INT, c2 INT)
    ->     TABLESPACE ts_1 STORAGE DISK ENGINE NDB;
Query OK, 0 rows affected (1.34 sec)

要将列

c2

从基于磁盘的内存更改为内存中，请在ALTER TABLE语句使用的列定义中包含STORAGE MEMORY子句，如下所示：

mysql> ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;
Query OK, 0 rows affected (3.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

您可以使用

STORAGE DISK

类似的方式将内存列写入基于磁盘的列中。列

c1

使用基于磁盘的存储，因为这是表的默认值（由

STORAGE DISK

语句中的表级子句 确定 

CREATE TABLE

）。但是，列

c2

使用内存存储，在这里可以看到在SHOW的输出

CREATE TABLE

：

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

添加

AUTO_INCREMENT

列时，列值将自动填入序列号。对于 

MyISAM

表格，您可以通过在执行之前 或通过使用 表格选项来设置第一个序列号。 

SET INSERT_ID=

value

ALTER TABLE

AUTO_INCREMENT=

value

使用

MyISAM

表格，如果不更改 

AUTO_INCREMENT

列，则序号不受影响。如果删除一

AUTO_INCREMENT

 列然后再添加一

AUTO_INCREMENT

 列，则数字将从1开始重新排序。

使用复制时，向

AUTO_INCREMENT

表中添加一 列可能不会产生从属和主控上的行的相同顺序。发生这种情况是因为行编号的顺序取决于用于表的特定存储引擎以及行插入的顺序。如果在主站和从站上具有相同的顺序非常重要，那么在分配

AUTO_INCREMENT

数字之前，必须对行进行排序 。假设您想将添加

AUTO_INCREMENT

列的表 

t1

，下面的语句产生新表

t2

相同，

t1

 但有一个

AUTO_INCREMENT

列：

CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)
SELECT * FROM t1 ORDER BY col1, col2;

这假定表中

t1

有列 

col1

和

col2

。这组语句也将产生一个新的表 

t2

相同

t1

，加上一个的

AUTO_INCREMENT

列：

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

为保证主站和从站的顺序相同 ，必须在子句中引用所有的列。 

t1 

ORDER BY

不管用于创建和填充具有该

AUTO_INCREMENT

列的副本的方法，最后一步是删除原始表，然后重新命名该副本：

DROP TABLE t1;
ALTER TABLE t2 RENAME t1;

PS: 关于表的ALTER以及扩展学习参考☞(Mysql Alter);感兴趣的朋友可以学习下。

4、Mysql表结构的查看

关于Mysql表结构的查看大致可以分为两种方式: 分别是使用DESCRIBE，以及SHOW CREATE TABLE命令可以达到相同的目的。

(1)、DESCRIBE

比如在Navicat中使用DESCRIBE的反回的直接是Mysql的字段信息。就相当于是一个描述一般的清晰。

DESCRIBE test_create;
Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

(2)、SHOW CREATE TABLE

在使用SHOW CREATE TABLE T_JC_SHOP;展示的是创建Mysql表的语句:

-- 查询语句
 SHOW CREATE TABLE test_create;

-- 查看到的表结构信息
CREATE TABLE `test_create` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(100) NOT NULL,
  `author` varchar(40) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Mysql表结构的增删改查(CRUD)——Mysql初级(一)1、前言:2、如何创建Mysql表2、如何删除Mysql表结构3、如何修改Mysql数据库表结构4、Mysql表结构的查看

在使用的过程中根据具体需求可以使用具体的方式。

5、总结:

这个系列的第一篇文章终于告一段落了，此文后期还会继续更新和完善。主要讨论了Mysql表结构的增删改查的使用，使用不同的命令，得到的结果自然也是大相径庭。假如有什么不足或者错误的地方希望大家提出宝贵的意见和建议，小生在此谢过!

PS： 可能文章结束的有点匆忙，还望大家见谅!

Mysql深入浅出 Mysql表结构 增删改查 drop
上一篇: 卸载后以前拍的视频会删除吗_95后不倒翁小姐姐：5天涨粉100万，带火一座长安城！...
下一篇: redis主从切换会堵塞进程吗_redis主从配置及主从切换 .

继续阅读