MySQL 數(shù)據(jù)定義語言（DDL）

路人甲Java 2022-04-05

展開全文

SQL 包含以下 4 部分：
    1    數(shù)據(jù)定義語言（DDL）：DROP、CREATE、ALTER 等語句。
    2    數(shù)據(jù)操作語言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（刪除）語句。
    3    數(shù)據(jù)查詢語言（DQL）：SELECT 語句。
    4    數(shù)據(jù)控制語言（DCL）: GRANT、REVOKE、COMMIT、ROLLBACK 等語句。

MySQL之DDL(Data Definition Language): 數(shù)據(jù)定義語言：CREATE,ALTER,DROP等

DDL：操作數(shù)據(jù)庫，表（CRUD）

1、操作數(shù)據(jù)庫（CRUD）

(1)C(Create):創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE [IF NOT EXISTS] 數(shù)據(jù)庫名
[[DEFAULT] CHARACTER SET 字符集名]
[[DEFAULT] COLLATE 校對(duì)規(guī)則名];

[ ]中的內(nèi)容是可選的。
語法說明如下：
數(shù)據(jù)庫名：創(chuàng)建數(shù)據(jù)庫的名稱。MySQL 的數(shù)據(jù)存儲(chǔ)區(qū)將以目錄方式表示 MySQL 數(shù)據(jù)庫，因此數(shù)據(jù)庫名稱必須符合操作系統(tǒng)的文件夾命名規(guī)則，不能以數(shù)字開頭，盡量要有實(shí)際意義。注意在 MySQL 中不區(qū)分大小寫。
IF NOT EXISTS：在創(chuàng)建數(shù)據(jù)庫之前進(jìn)行判斷，只有該數(shù)據(jù)庫目前尚不存在時(shí)才能執(zhí)行操作。此選項(xiàng)可以用來避免數(shù)據(jù)庫已經(jīng)存在而重復(fù)創(chuàng)建的錯(cuò)誤。
[DEFAULT] CHARACTER SET：指定數(shù)據(jù)庫的字符集。指定字符集的目的是為了避免在數(shù)據(jù)庫中存儲(chǔ)的數(shù)據(jù)出現(xiàn)亂碼的情況。如果在創(chuàng)建數(shù)據(jù)庫時(shí)不指定字符集，那么就使用系統(tǒng)的默認(rèn)字符集。
[DEFAULT] COLLATE：指定字符集的默認(rèn)校對(duì)規(guī)則。

eg.

mysql> CREATE DATABASE IF NOT EXISTS test
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.01 sec)

(2)R(Retrieve):查詢

SHOW DATABASES [LIKE '數(shù)據(jù)庫名'];

語法說明如下：
LIKE 從句是可選項(xiàng)，用于匹配指定的數(shù)據(jù)庫名稱。LIKE 從句可以部分匹配，也可以完全匹配。
數(shù)據(jù)庫名由單引號(hào)' '包圍。

eg.查詢?nèi)?/p>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |         |
| mqcms              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| sys                |
| test               |
| test_aa            |
| ultrax             |
+--------------------+
9 rows in set (0.00 sec)

eg.查詢某一個(gè)(我有test和test_aa兩個(gè)庫)

mysql> show databases like 'test';
+-----------------+
| Database (test) |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> show databases like '%test%';
+-------------------+
| Database (%test%) |
+-------------------+
| dedetest          |
| test              |
| test_aa           |
+-------------------+
3 rows in set (0.00 sec)

這里的like跟模糊查詢一樣%aa%;%aa;aa%;

eg.查詢某個(gè)數(shù)據(jù)庫的創(chuàng)建語言

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

(3)U(Update):修改

ALTER DATABASE [數(shù)據(jù)庫名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校對(duì)規(guī)則名>}

語法說明如下：
ALTER DATABASE 用于更改數(shù)據(jù)庫的全局特性。
使用 ALTER DATABASE 需要獲得數(shù)據(jù)庫 ALTER 權(quán)限。
數(shù)據(jù)庫名稱可以忽略，此時(shí)語句對(duì)應(yīng)于默認(rèn)數(shù)據(jù)庫。
CHARACTER SET 子句用于更改默認(rèn)的數(shù)據(jù)庫字符集。

eg.修改數(shù)據(jù)庫test的字符集

mysql> ALTER DATABASE test
    -> DEFAULT CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.02 sec)


mysql> show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gb2312 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

(4)D(Delete):刪除

DROP DATABASE [ IF EXISTS ] <數(shù)據(jù)庫名>

語法說明如下：
<數(shù)據(jù)庫名>：指定要?jiǎng)h除的數(shù)據(jù)庫名。
IF EXISTS：用于防止當(dāng)數(shù)據(jù)庫不存在時(shí)發(fā)生錯(cuò)誤。
DROP DATABASE：刪除數(shù)據(jù)庫中的所有表格并同時(shí)刪除數(shù)據(jù)庫。使用此語句時(shí)要非常小心，以免錯(cuò)誤刪除。如果要使用 DROP DATABASE，需要獲得數(shù)據(jù)庫 DROP 權(quán)限。

注意：MySQL 安裝后，系統(tǒng)會(huì)自動(dòng)創(chuàng)建名為 information_schema 和 mysql 的兩個(gè)系統(tǒng)數(shù)據(jù)庫，系統(tǒng)數(shù)據(jù)庫存放一些和數(shù)據(jù)庫相關(guān)的信息，如果刪除了這兩個(gè)數(shù)據(jù)庫，MySQL 將不能正常工作。

使用 DROP DATABASE 命令時(shí)要非常謹(jǐn)慎，在執(zhí)行該命令后，MySQL 不會(huì)給出任何提示確認(rèn)信息。DROP DATABASE 刪除數(shù)據(jù)庫后，數(shù)據(jù)庫中存儲(chǔ)的所有數(shù)據(jù)表和數(shù)據(jù)也將一同被刪除，而且不能恢復(fù)。因此最好在刪除數(shù)據(jù)庫之前先將數(shù)據(jù)庫進(jìn)行備份。

eg.刪除數(shù)據(jù)庫test_aa;

mysql> DROP DATABASE IF EXISTS test_aa;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases like 'test%';
+------------------+
| Database (test%) |
+------------------+
| test             |
+------------------+
1 row in set (0.00 sec)

2、操作數(shù)據(jù)表（CRUD）

（1）C(Create)創(chuàng)建表

CREATE TABLE <表名> ([表定義選項(xiàng)])[表選項(xiàng)][分區(qū)選項(xiàng)];

其中，[表定義選項(xiàng)]的格式為：
<列名1> <類型1> [,…] <列名n> <類型n>


CREATE TABLE 語句的主要語法及使用說明如下：
CREATE TABLE：用于創(chuàng)建給定名稱的表，必須擁有表CREATE的權(quán)限。
<表名>：指定要?jiǎng)?chuàng)建表的名稱，在 CREATE TABLE 之后給出，必須符合標(biāo)識(shí)符命名規(guī)則。表名稱被指定為 db_name.tbl_name，以便在特定的數(shù)據(jù)庫中創(chuàng)建表。無論是否有當(dāng)前數(shù)據(jù)庫，都可以通過這種方式創(chuàng)建。在當(dāng)前數(shù)據(jù)庫中創(chuàng)建表時(shí)，可以省略 db-name。如果使用加引號(hào)的識(shí)別名，則應(yīng)對(duì)數(shù)據(jù)庫和表名稱分別加引號(hào)。例如，'mydb'.'mytbl' 是合法的，但 'mydb.mytbl' 不合法。
<表定義選項(xiàng)>：表創(chuàng)建定義，由列名（col_name）、列的定義（column_definition）以及可能的空值說明、完整性約束或表索引組成。
默認(rèn)的情況是，表被創(chuàng)建到當(dāng)前的數(shù)據(jù)庫中。若表已存在、沒有當(dāng)前數(shù)據(jù)庫或者數(shù)據(jù)庫不存在，則會(huì)出現(xiàn)錯(cuò)誤。

eg.創(chuàng)建一張測(cè)試表test_tbl;

字段名稱	字段類型	字段備注
id	int(11)	ID
name	varchar(20)	名稱
age	int(11)	年齡
sex	tinyint(4)	性別0 男 1女 2 未知

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
+----------------+
2 rows in set (0.01 sec)


mysql> CREATE TABLE test_tbl
    -> (
    -> id INT(11),
    -> name VARCHAR(20),
    -> age INT(11),
    -> sex TINYINT(4)
    -> );
Query OK, 0 rows affected (0.03 sec)

復(fù)制表結(jié)構(gòu)：

 CREATE TABLE <數(shù)據(jù)表名> like <被復(fù)制的表名>;

eg.

mysql> CREATE TABLE test_tbl_cp like test_tbl;
Query OK, 0 rows affected (0.01 sec)

(2)R(Retrieve)查看表

查看所有表：

SHOW TABLES;

eg.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
| test_tbl       |
| test_tbl_cp    |
+----------------+
4 rows in set (0.00 sec)

查看表結(jié)構(gòu)：

DESCRIBE <表名>;

或簡(jiǎn)寫成：
DESC <表名>;

DESCRIBE/DESC 語句可以查看表的字段信息，包括字段名、字段數(shù)據(jù)類型、是否為主鍵、是否有默認(rèn)值等

eg.

mysql> DESC test_tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

其中，各個(gè)字段的含義如下：
Null：表示該列是否可以存儲(chǔ) NULL 值。
Key：表示該列是否已編制索引。PRI 表示該列是表主鍵的一部分，UNI 表示該列是 UNIQUE 索引的一部分，MUL 表示在列中某個(gè)給定值允許出現(xiàn)多次。
Default：表示該列是否有默認(rèn)值，如果有，值是多少。
Extra：表示可以獲取的與給定列有關(guān)的附加信息，如 AUTO_INCREMENT 等。

SHOW CREATE TABLE語句可以用來顯示創(chuàng)建表時(shí)的CREATE TABLE語句:

SHOW CREATE TABLE <表名>\G；

提示：使用 SHOW CREATE TABLE 語句不僅可以查看創(chuàng)建表時(shí)的詳細(xì)語句，而且可以查看存儲(chǔ)引擎和字符編碼。如果不加“\G”參數(shù)，顯示的結(jié)果可能非常混亂，加上“\G”參數(shù)之后，可使顯示的結(jié)果更加直觀，易于查看。

eg.

mysql> show create table test_tbl\G;
*************************** 1. row ***************************
       Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

(3)U(Update)修改

ALTER TABLE <表名> [修改選項(xiàng)]


修改選項(xiàng)的語法格式如下：
{ 
ADD COLUMN <列名> <類型>
| CHANGE COLUMN <舊列名> <新列名> <新列類型>
| ALTER COLUMN <列名> { SET DEFAULT <默認(rèn)值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <類型>
| DROP COLUMN <列名>
| RENAME TO <新表名> 
}

添加字段:

ALTER TABLE <表名> ADD <新字段名> <數(shù)據(jù)類型> [約束條件] [FIRST|AFTER 已存在的字段名]；

新字段名為需要添加的字段的名稱；FIRST 為可選參數(shù)，其作用是將新添加的字段設(shè)置為表的第一個(gè)字段；AFTER 為可選參數(shù)，其作用是將新添加的字段添加到指定的已存在的字段名的后面。

eg.在表test_tbl的name后面添加新字段name_cp：

mysql> ALTER TABLE test_tbl ADD name_cp VARCHAR(20) AFTER name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(20) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段名稱：

ALTER TABLE <表名> CHANGE <舊字段名> <新字段名> <新數(shù)據(jù)類型>；

其中，舊字段名指修改前的字段名；新字段名指修改后的字段名；新數(shù)據(jù)類型指修改后的數(shù)據(jù)類型，如果不需要修改字段的數(shù)據(jù)類型，可以將新數(shù)據(jù)類型設(shè)置成與原來一樣，但數(shù)據(jù)類型不能為空。

eg.修改name的名稱改為names

mysql> ALTER TABLE test_tbl
    -> CHANGE name names VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| names   | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(20) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改/刪除字段默認(rèn)值：

ALTER TABLE <表名> ALTER COLUMN <列名> { SET DEFAULT <默認(rèn)值> | DROP DEFAULT }

eg.將字段sex的默認(rèn)值改為2

mysql> ALTER TABLE test_tbl ALTER sex SET DEFAULT 2;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| names   | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(50) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | 2       |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段類型：

ALTER TABLE <表名> MODIFY <字段名> <數(shù)據(jù)類型>

其中，表名指要修改數(shù)據(jù)類型的字段所在表的名稱，字段名指需要修改的字段，數(shù)據(jù)類型指修改后字段的新數(shù)據(jù)類型。

eg.修改name_cp的數(shù)據(jù)類型varchar(50)

mysql> ALTER TABLE test_tbl 
    -> MODIFY name_cp varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(50) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

刪除字段：

ALTER TABLE <表名> DROP <字段名>；

其中，字段名指需要從表中刪除的字段的名稱。

eg.刪除sex字段

mysql> ALTER TABLE test_tbl
    -> DROP sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| names   | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(50) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表名：

ALTER TABLE <舊表名> RENAME [TO] <新表名>；

其中，TO 為可選參數(shù)，使用與否均不影響結(jié)果。

eg.修改表名test_tbl改為test_tb;

mysql> ALTER TABLE test_tbl
    -> RENAME TO test_tb;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
| test_tb        |
| test_tbl_cp    |
+----------------+
4 rows in set (0.00 sec)

(4)D(Delete)刪除表

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]


對(duì)語法格式的說明如下：
表名1, 表名2, 表名3 ...表示要被刪除的數(shù)據(jù)表的名稱。DROP TABLE 可以同時(shí)刪除多個(gè)表，只要將表名依次寫在后面，相互之間用逗號(hào)隔開即可。
IF EXISTS 用于在刪除數(shù)據(jù)表之前判斷該表是否存在。如果不加 IF EXISTS，當(dāng)數(shù)據(jù)表不存在時(shí) MySQL 將提示錯(cuò)誤，中斷 SQL 語句的執(zhí)行；加上 IF EXISTS 后，當(dāng)數(shù)據(jù)表不存在時(shí) SQL 語句可以順利執(zhí)行，但是會(huì)發(fā)出警告（warning）。

兩點(diǎn)注意：
用戶必須擁有執(zhí)行 DROP TABLE 命令的權(quán)限，否則數(shù)據(jù)表不會(huì)被刪除。
表被刪除時(shí)，用戶在該表上的權(quán)限不會(huì)自動(dòng)刪除。

eg.刪除表test_tbl_cp

mysql> DROP TABLE test_tbl_cp;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
| test_tb        |
+----------------+
3 rows in set (0.00 sec)

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：路人甲Java > 《待分類》

舉報(bào)/認(rèn)領(lǐng)