MySQL 資料定義語言（DDL）-有解無憂

參考資料：C語言中文網

SQL 包含以下 4 部分：
    1    資料定義語言（DDL）：DROP、CREATE、ALTER 等陳述句，
    2    資料操作語言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（洗掉）陳述句，
    3    資料查詢語言（DQL）：SELECT 陳述句，
    4    資料控制語言（DCL）: GRANT、REVOKE、COMMIT、ROLLBACK 等陳述句，

MySQL之DDL(Data Definition Language): 資料定義語言：CREATE,ALTER,DROP等

DDL：操作資料庫，表（CRUD）

1、操作資料庫（CRUD）

(1)C(Create):創建資料庫

CREATE DATABASE [IF NOT EXISTS] 資料庫名
[[DEFAULT] CHARACTER SET 字符集名]
[[DEFAULT] COLLATE 校對規則名];

[ ]中的內容是可選的，
語法說明如下：
資料庫名：創建資料庫的名稱，MySQL 的資料存盤區將以目錄方式表示 MySQL 資料庫，因此資料庫名稱必須符合作業系統的檔案夾命名規則，不能以數字開頭，盡量要有實際意義，注意在 MySQL 中不區分大小寫，
IF NOT EXISTS：在創建資料庫之前進行判斷，只有該資料庫目前尚不存在時才能執行操作，此選項可以用來避免資料庫已經存在而重復創建的錯誤，
[DEFAULT] CHARACTER SET：指定資料庫的字符集，指定字符集的目的是為了避免在資料庫中存盤的資料出現亂碼的情況，如果在創建資料庫時不指定字符集，那么就使用系統的默認字符集，
[DEFAULT] COLLATE：指定字符集的默認校對規則，

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 '資料庫名'];

語法說明如下：
LIKE 從句是可選項，用于匹配指定的資料庫名稱，LIKE 從句可以部分匹配，也可以完全匹配，
資料庫名由單引號' '包圍，

eg.查詢全部

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

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

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.查詢某個資料庫的創建語言

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 [資料庫名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校對規則名>}

語法說明如下：
ALTER DATABASE 用于更改資料庫的全域特性，
使用 ALTER DATABASE 需要獲得資料庫 ALTER 權限，
資料庫名稱可以忽略，此時陳述句對應于默認資料庫，
CHARACTER SET 子句用于更改默認的資料庫字符集，

eg.修改資料庫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 ] <資料庫名>

語法說明如下：
<資料庫名>：指定要洗掉的資料庫名，
IF EXISTS：用于防止當資料庫不存在時發生錯誤，
DROP DATABASE：洗掉資料庫中的所有表格并同時洗掉資料庫，使用此陳述句時要非常小心，以免錯誤洗掉，如果要使用 DROP DATABASE，需要獲得資料庫 DROP 權限，

注意：MySQL 安裝后，系統會自動創建名為 information_schema 和 mysql 的兩個系統資料庫，系統資料庫存放一些和資料庫相關的資訊，如果洗掉了這兩個資料庫，MySQL 將不能正常作業，

使用 DROP DATABASE 命令時要非常謹慎，在執行該命令后，MySQL 不會給出任何提示確認資訊，DROP DATABASE 洗掉資料庫后，資料庫中存盤的所有資料表和資料也將一同被洗掉，而且不能恢復，因此最好在洗掉資料庫之前先將資料庫進行備份，

eg.洗掉資料庫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、操作資料表（CRUD）

（1）C(Create)創建表

CREATE TABLE <表名> ([表定義選項])[表選項][磁區選項];

其中，[表定義選項]的格式為：
<列名1> <型別1> [,…] <列名n> <型別n>


CREATE TABLE 陳述句的主要語法及使用說明如下：
CREATE TABLE：用于創建給定名稱的表，必須擁有表CREATE的權限，
<表名>：指定要創建表的名稱，在 CREATE TABLE 之后給出，必須符合識別符號命名規則，表名稱被指定為 db_name.tbl_name，以便在特定的資料庫中創建表，無論是否有當前資料庫，都可以通過這種方式創建，在當前資料庫中創建表時，可以省略 db-name，如果使用加引號的識別名，則應對資料庫和表名稱分別加引號，例如，'mydb'.'mytbl' 是合法的，但 'mydb.mytbl' 不合法，
<表定義選項>：表創建定義，由列名（col_name）、列的定義（column_definition）以及可能的空值說明、完整性約束或表索引組成，
默認的情況是，表被創建到當前的資料庫中，若表已存在、沒有當前資料庫或者資料庫不存在，則會出現錯誤，

eg.創建一張測驗表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)

復制表結構：

 CREATE TABLE <資料表名> like <被復制的表名>;

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)

查看表結構：

DESCRIBE <表名>;

或簡寫成：
DESC <表名>;

DESCRIBE/DESC 陳述句可以查看表的欄位資訊，包括欄位名、欄位資料型別、是否為主鍵、是否有默認值等

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)

其中，各個欄位的含義如下：
Null：表示該列是否可以存盤 NULL 值，
Key：表示該列是否已編制索引，PRI 表示該列是表主鍵的一部分，UNI 表示該列是 UNIQUE 索引的一部分，MUL 表示在列中某個給定值允許出現多次，
Default：表示該列是否有默認值，如果有，值是多少，
Extra：表示可以獲取的與給定列有關的附加資訊，如 AUTO_INCREMENT 等，

SHOW CREATE TABLE陳述句可以用來顯示創建表時的CREATE TABLE陳述句:

SHOW CREATE TABLE <表名>\G；

提示：使用 SHOW CREATE TABLE 陳述句不僅可以查看創建表時的詳細陳述句，而且可以查看存盤引擎和字符編碼，如果不加“\G”引數，顯示的結果可能非常混亂，加上“\G”引數之后，可使顯示的結果更加直觀，易于查看，

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 <表名> [修改選項]


修改選項的語法格式如下：
{ 
ADD COLUMN <列名> <型別>
| CHANGE COLUMN <舊列名> <新列名> <新列型別>
| ALTER COLUMN <列名> { SET DEFAULT <默認值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <型別>
| DROP COLUMN <列名>
| RENAME TO <新表名> 
}

添加欄位:

ALTER TABLE <表名> ADD <新欄位名> <資料型別> [約束條件] [FIRST|AFTER 已存在的欄位名]；

新欄位名為需要添加的欄位的名稱；FIRST 為可選引數，其作用是將新添加的欄位設定為表的第一個欄位；AFTER 為可選引數，其作用是將新添加的欄位添加到指定的已存在的欄位名的后面，

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 <舊欄位名> <新欄位名> <新資料型別>；

其中，舊欄位名指修改前的欄位名；新欄位名指修改后的欄位名；新資料型別指修改后的資料型別，如果不需要修改欄位的資料型別，可以將新資料型別設定成與原來一樣，但資料型別不能為空，

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)

修改/洗掉欄位默認值：

ALTER TABLE <表名> ALTER COLUMN <列名> { SET DEFAULT <默認值> | DROP DEFAULT }

eg.將欄位sex的默認值改為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 <欄位名> <資料型別>

其中，表名指要修改資料型別的欄位所在表的名稱，欄位名指需要修改的欄位，資料型別指修改后欄位的新資料型別，

eg.修改name_cp的資料型別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 為可選引數，使用與否均不影響結果，

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 ...]


對語法格式的說明如下：
表名1, 表名2, 表名3 ...表示要被洗掉的資料表的名稱，DROP TABLE 可以同時洗掉多個表，只要將表名依次寫在后面，相互之間用逗號隔開即可，
IF EXISTS 用于在洗掉資料表之前判斷該表是否存在，如果不加 IF EXISTS，當資料表不存在時 MySQL 將提示錯誤，中斷 SQL 陳述句的執行；加上 IF EXISTS 后，當資料表不存在時 SQL 陳述句可以順利執行，但是會發出警告（warning），

兩點注意：
用戶必須擁有執行 DROP TABLE 命令的權限，否則資料表不會被洗掉，
表被洗掉時，用戶在該表上的權限不會自動洗掉，

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)

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66601.html

標籤：MySQL

上一篇：如果有一個特別大的訪問量到資料庫上，怎么做優化？主從復制、讀寫分離

下一篇：[MySQL] 查看explain中的key_len判斷究竟使用了哪個索引