MySQL（五）之建立删除修改庫表

一、資料庫的常用操作

1、建立資料庫

文法：CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

說明：

DATABASE|SCHEMA：都表示為資料庫
IF NOT EXISTS ：判斷資料庫是否存在
CHARACTER SET=''：指定預設字元集
COLLATE=''：指定排序規則
DEFAULT：将建立資料庫的選項指定為預設

建立資料庫時若庫名重複就會報錯，而使用IF NOT EXISTS 選項就不會報錯，但會有一個警告資訊：

MariaDB [test]> CREATE DATABASE  test;
ERROR 1007 (HY000): Can't create database 'test'; database exists

MariaDB [test]> CREATE DATABASE  IF NOT EXISTS test;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1007 | Can't create database 'test'; database exists |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)

2、删除資料庫

文法：DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

3、修改資料庫屬性

文法：ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

二、表的日常操作

1、表的建立第一種方式

文法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

TEMPORARY ：建立臨時表，建立在記憶體中，占用記憶體空間。

create_definition,... 可定義字段：字段名、類型和類型修飾符。也可定義鍵、限制或索引：PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK 或者索引：{INDEX|KEY}

舉例：

注意PRIMARY KEY(Name,Age)定義了兩個主鍵，而主鍵即可放在屬性的後面，也可專門定義。

MariaDB [test]> CREATE TABLE t2 (Name VARCHAR(30) NOT NULL,Age TINYINT UNSIGNED NOT NULL, PRIMARY KEY(Name,Age));
Query OK, 0 rows affected (0.06 sec)
MariaDB [test]> DESC t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name  | varchar(30)         | NO   | PRI | NULL    |       |
| Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

[table_options]：表的選項，下面列舉了一些常用選項。

ENGINE [=] engine_name：指定表的存儲引擎，在使用此選項前需要先檢視目前系統能支援的存儲引擎，查詢語句為SHOW ENGINES;通過檢視DEFAULT所在位置可以看到系統預設引擎，而我們在建立表時不想使用系統預設引擎則在建立表的最後加上此選項就行了。而建立後我們還可以通過SHOW TABLE STATUS LIKE 'table_name'\G;語句來檢視該表所使用的引擎。

AUTO_INCREMENT [=] value ：指定AUTO_INCREMENT的起始值。
[DEFAULT] CHARACTER SET [=] charset_name ：設定字元集。
[DEFAULT] COLLATE [=] collation_name ：指定排序規則。
COMMENT [=] 'string' ：增加表的注釋資訊。
DELAY_KEY_WRITE [=] {0 | 1} ：延遲鍵寫入。
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} ：表格式。
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] ：指定表空間。

2、表建立第二種方式（複制表資料，但會失去原表的屬性）

[(create_definition,...)]

[table_options]

select_statement

MariaDB [test]> DESC t1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | char(3)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
+----+------+
| ID | Name |
+----+------+
|  1 | bo   |
|  2 | lon  |
|  3 | lon  |
+----+------+
3 rows in set (0.00 sec)

MariaDB [test]> CREATE TABLE t4 SELECT * FROM t1;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM t4;
+----+------+
| ID | Name |
+----+------+
|  1 | bo   |
|  2 | lon  |
|  3 | lon  |
+----+------+
3 rows in set (0.00 sec)

MariaDB [test]> DESC t4;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| ID    | int(10) unsigned | NO   |     | 0       |       |
| Name  | char(3)          | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3、表建立：第三種方式（複制表結構，但會沒有原表的資料）

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

MariaDB [test]> CREATE TABLE t5 LIKE t1;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> DESC t5;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | char(3)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM t5;
Empty set (0.00 sec)

上面兩種方法建立表都有其優缺點，然而實際使用中還是一第三種，即先複制原表的結構然後在使用INSERT語句進行插入資料。

4、表删除

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]：若表中有依賴則使用CASCADE解除依賴。

5、表屬性修改

ALTER TABLE tbl_name

[alter_specification [, alter_specification] ...]

插入新字段：ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]

MariaDB [test]> DESC t5;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | char(3)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MariaDB [test]> ALTER TABLE t5 ADD Age TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC t5;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Name  | char(3)             | YES  |     | NULL    |                |
| Age   | tinyint(3) unsigned | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

放在指定位置後面：

MariaDB [test]> ALTER TABLE t5 ADD Gender ENUM('M','F')NOT NULL DEFAULT 'M' AFTER Name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC t5;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| ID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Name   | char(3)             | YES  |     | NULL    |                |
| Gender | enum('M','F')       | NO   |     | M       |                |
| Age    | tinyint(3) unsigned | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

删除字段：DROP [COLUMN] col_name

MariaDB [test]> ALTER TABLE t5 DROP Gender;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段名稱

文法：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

MariaDB [test]> ALTER TABLE t5 CHANGE Name StuName CHAR(20) NOT NULL;
Query OK, 0 rows affected (0.14 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC t5;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| ID      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Age     | tinyint(3) unsigned | YES  |     | NULL    |                |
| StuName | char(20)            | NO   |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

修改字段類型及屬性等

文法：MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

MariaDB [test]> ALTER TABLE t5 MODIFY Age TINYINT UNSIGNED AFTER ID;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC t5;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Age   | tinyint(3) unsigned | YES  |     | NULL    |                |
| Name  | char(3)             | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

修改限制、鍵或索引

文法：ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option]

MariaDB [test]> ALTER TABLE t5 ADD INDEX (StuName);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [test]> SHOW INDEXES FROM t5;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t5    |          0 | PRIMARY  |            1 | ID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t5    |          1 | StuName  |            1 | StuName     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MariaDB [test]> ALTER TABLE t5 DROP INDEX StuName;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SHOW INDEXES FROM t5;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t5    |          0 | PRIMARY  |            1 | ID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

6、表改名

文法：RENAME [TO|AS] new_tbl_name

mysql> RENAME TABLE old_name TO new_name;

7、修改表的存儲引擎

MariaDB [test]> SHOW TABLE STATUS LIKE 't5'\G;
*************************** 1. row ***************************
           Name: t5
         Engine: InnoDB
        Version: 10
     Row_format: Compact
**************************************************************
MariaDB [test]> ALTER TABLE t5 ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)              
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [test]> SHOW TABLE STATUS LIKE 't5'\G;
*************************** 1. row ***************************
           Name: t5
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
**************************************************************

注意：此操作很危險，一般不建議在生産環境中進行此操作。

8、指定排序标準的字段

文法：ORDER BY col_name [, col_name] ...

9、轉換字元集及排序規則

文法：CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

在修改表選項時，其所可以使用的選項與建立時的選項通用。

三、MyISAM和InnoDB表的存儲結構

1、MyISAM表，每表有三個檔案，都位于資料庫目錄中

tb_name.frm：表結構定義

tb_name.MYD：資料檔案

tb_name.MYI：索引檔案

2、InnoDB表，有兩種存儲方式

預設：每表有一個獨立檔案和一個多表共享的檔案；

tb_name.frm：表結構的定義，位于資料庫目錄中；

ibdata#：共享的表空間檔案，預設位于資料目錄(datadir指向的目錄)中。

獨立的表空間：

tb_name.frm：每表有一個表結構檔案；

tb_name.ibd：一個獨有的表空間檔案。

在InnoDB的表存儲中，第一種方法由于ibdata#是多個表共享的檔案，是以資料庫使用時間一長就極容易造成該檔案過大，進而導緻不容易管理資料檔案，而第二種方法即使用獨立的表空間就不會出現此類狀況，是以在系統初始化時還是使用第二種方法好，友善人們管理，下面就是介紹如何在InnoDB引擎中設定其存儲方法為獨立表空間。

首先産看資料庫中關于innodb設定的選項：

MariaDB [test]> SHOW GLOBAL VARIABLES LIKE 'innodb%';

從輸出的結果來看mariadb-10.0已經預設将其打開了。

| innodb_file_per_table                     | ON

MariaDB [test]> SET GLOBAL innodb_file_per_table=ON;

[[email protected] ~]# vim /etc/my.cnf 
[mysqld]
innodb_file_per_table = ON

MySQL（五）之建立删除修改庫表

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案