天天看點
傳回

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主從配置及主從切換 .

繼續閱讀