MySQL中庫的基本操作指南(推薦!)

2023-02-22 06:01:00

檢視資料庫

語法格式：

SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]

#檢視全部資料庫

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

show databases [like '庫名'| where 表示式];

例：檢視有my的資料庫

show databases like 'my%'

建立資料庫

MySQL安裝好之後，⾸先需要建立資料庫，這是使⽤MySQL各種功能的前提。本章將詳細介紹資料的基本操作，主要內容包括：建立資料庫、刪除資料庫、不同型別的資料儲存引擎和儲存引擎的選擇。

MySQL安裝完成之後，將會在其data⽬錄下⾃動建立⼏個必需的資料庫，可以使⽤SHOW DATABASES；語句來檢視當前所有存在的資料庫，如下。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

可以看到，資料庫列表中包含了4個資料庫，mysql是必需的，它描述⽤戶存取許可權，⽤戶經常利⽤test資料庫做測試的⼯作，其他資料庫將在後⾯的章節中介紹。

建立資料庫是在系統磁碟上劃分⼀塊區域⽤於資料的儲存和管理，如果管理員在設定許可權的時候為⽤戶建立了資料庫，則可以直接使⽤，否則，需要⾃⼰建立資料庫。MySQL中建立資料庫的基本SQL語句格式為：

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

<資料庫名>：建立資料庫的名稱。MySQL 的資料儲存區將以⽬錄⽅式表示 MySQL資料庫，因此資料庫名稱必須符合作業系統的⽂件夾命名規則，注意在 MySQL 中不區分⼤⼩寫。

IF NOT EXISTS：在建立資料庫之前進⾏判斷，只有該資料庫⽬前尚不存在時才能執⾏操作。此選項可以⽤來避免資料庫已經存在⽽重複建立的錯誤。

[DEFAULT] CHARACTER SET：指定資料庫的預設字元集。

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

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

刪除資料庫

刪除資料庫是將已經存在的資料庫從磁碟空間上清除，清除之後，資料庫中的所有資料也將⼀起被刪除。刪除資料庫語句和建立資料庫的命令相似，MySQL中刪除資料庫的基本語法格式如下。

DROP {DATABASE | SCHEMA} [IF EXISTS] 庫名

注：IF EXISTS表示如果存在，則刪除庫。

如果指定的資料庫不存在，則刪除出錯。

mysql> drop database test_db;
Query OK, 0 rows affected (0.02 sec)

字元集/字元校驗

#檢視字元校驗

show collation;

#檢視字元集

show character set;

#檢視字元集

SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]show character set [like '字元集名' | where 表示式];

#檢視字元校驗

SHOW COLLATION [LIKE 'pattern' | WHERE expr] show collation [like '字元校驗名' | where 表示式];

修改資料庫

ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...
 
alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
}

#修改庫字元集

alter database 庫名 default character set = 字元集名;

#修改字元校驗

alter database test collate 字元校驗名;

幫助命令

#幫助

help

#幫助分類

help contents

#檢視定義語言

help data definition

#檢視操作語言

help data manipulation

#檢視建立庫格式

help create database

資料庫儲存引擎

資料庫儲存引擎是資料庫底層軟體元件，資料庫管理系統（DBMS）使⽤資料引擎進⾏建立、查詢、更新和刪除資料操作。不同的儲存引擎提供不同的儲存機制、索引技巧、鎖定⽔平等功能，使⽤不同的儲存引擎，還可以獲得特定的功能。現在許多不同的資料庫管理系統都⽀持多種不同的資料引擎。MySQL的核⼼就是儲存引擎。

2.3.1 MySQL儲存引擎簡介MySQL提供了多個不同的儲存引擎，包括處理事務安全表的引擎和處理⾮事務安全表的引擎。在MySQL中，不需要在整個伺服器中使⽤⼀種引擎，針對具體要求可以對每⼀個表使⽤不同的儲存引擎。MySQL5.5⽀持的儲存引擎有：InnoDB、MyISAM、Memory等。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

檢視預設儲存引擎

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.06 sec)

儲存引擎簡介

儲存引擎說⽩了就是資料儲存的格式，不同的儲存引擎功能不同，佔⽤的空間⼤⼩不同，讀取效能也不同。
資料庫儲存引擎是資料庫底層軟體元件，不同的儲存引擎提供不同的儲存機制。
在 MySQL 中，不需要在整個伺服器中使⽤同⼀種儲存引擎，可以對每⼀個表使⽤不同的儲存引擎。
MySQL ⽀持多種儲存引擎，如 InnoDB 、MyISAM 、Memory 、Merge 、Archive 、CSV 、Federated 等等。

MyISAM 儲存引擎特點

MySQL 5.5 之前使⽤ MyISAM 引擎，MySQL 5.5 之後使⽤ InnoDB 引擎。
MyISAM 引擎讀取速度較快，佔⽤資源相對較少，不⽀持事務，不⽀持外來鍵約束，但⽀持全⽂索引。
讀寫互相阻塞，也就是說讀資料的時候你就不能寫資料，寫資料的時候你就不能讀資料。
MyISAM 引擎只能快取索引，⽽不能快取資料。

MyISAM 適⽤場景

不需要事務⽀持的業務，例如轉賬就不⾏。
適⽤於讀資料⽐較多的業務，不適⽤於讀寫頻繁的業務。
並行相對較低、資料修改相對較少的業務。
硬體資源⽐較差的機器可以考慮使⽤ MyISAM 引擎。

InnoDB 儲存引擎特點

事務型資料庫的⾸選引擎，⽀持事務安全表，⽀持⾏鎖定和外來鍵，MySQL5.5.5 版本之後，InnoDB 作為預設儲存引擎。
具有提交、回滾和崩潰恢復能⼒的事務安全儲存引擎，能處理巨⼤資料量，效能及效率⾼，完全⽀持外來鍵完整性約束。
具有⾮常⾼效的快取特性，能快取索引也能快取資料，對硬體要求⽐較⾼。
使⽤ InnoDB 時，將在 MySQL 資料⽬錄下建立⼀個名為 ibdata1 的 10MB ⼤⼩的⾃動擴充套件資料⽂件，以及兩個名為 ib_logfile0 和 ib_logfile1 的 5MB ⼤⼩的⽇志⽂件。

InnoDB 適⽤場景

需要事務⽀持的業務、⾼並行的業務。
資料更新較為頻繁的場景，⽐如 BBS、SNS、微博等。
資料⼀致性要求較⾼的業務，⽐如充值轉賬、銀⾏卡轉賬。

Memory 儲存引擎特點

Memory 儲存引擎將表中的資料儲存到記憶體中，為查詢和引⽤其他表資料提供快速存取。
Memory 儲存引擎執⾏ HASH 和 BTREE 索引，不⽀持 BLOB 和 TEXT 列，⽀持 AUTO_INCREMENT 列和對可包含 NULL 值得列的索引。
當不再需要 Memory 表的內容時，要釋放被 Memory 表使⽤的記憶體，應該執⾏DELETE FROM 或 TRUNCATE TABLE ，或者刪除整個表。

儲存引擎的選擇

如果要提供提交、回滾和崩潰恢復能⼒的事務安全能⼒，並要求實現並行控制，InnoDB 是個很好的選擇。
如果資料表主要⽤來插⼊和查詢記錄，則 MyISAM 引擎能提供較⾼的處理效率。
如果只是臨時存放資料，資料量不⼤，並且不需要較⾼的安全性，可以選擇將資料儲存在記憶體中的 Memory 引擎，MySQL 使⽤該引擎作為臨時表，存放查詢的中間結果。
如果只有 INSERT 和 SELECT 操作，可以選擇 Archive 引擎，⽀持⾼並行的插⼊操作，如記錄⽇志資訊可以使⽤ Archive 引擎。

功能	MyISAM	Memory	InnoDB
儲存限制	256TB	RAM	64TB
⽀持事務	no	no	yes
支援全文索引	yes	no	no
支援數索引	yes	yes	yes
支援雜湊快取	no	yes	no
支援資料快取	no	N/A	yes
支援外來鍵	no	no	yes