作業中經常用會遇到這種情況，可以訪問mysql所在的服務器，但是服務器埠不對外暴露（通常因為安全原因），這時，操作資料庫只能通過命令列和mysql client視窗來實作，我對這些操作進行一些匯總，方便以后隨時查閱，
一、登錄登出mysql client
登錄mysql client比較簡單，執行下面的命令回車，輸出root用戶密碼，即可進入，
mysql -u root -p
登出只需執行 退出命令 exit 或使用快捷鍵CTRL + C，
二、操作資料
登錄至mysql client后，可以操作常見的DDL、DML、DQL和DCL陳述句，
2.1 資料庫（database）相關操作
2.1.1 資料庫創建
創建資料庫，執行以下陳述句，
CREATE DATABASE your_db_name DEFAULT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
上述陳述句創建了名為your_db_name的資料庫，且設定了該資料庫默認的字符集為uft8mb4，這里注意mysql資料庫的命名不支持短中杠（-），多個單詞的拼接用下劃線（_）即可，
2.1.2 資料庫洗掉
洗掉資料庫，只需執行DROP DATABASE命令，
DROP DATABASE your_db_name;
2.1.3 查詢和展示資料庫
展示當前用戶下，全量的資料庫串列，
SHOW DATABASES;
如果要查詢特定的資料，可以使用LIKE 進行過濾查詢，
SHOW DATABASE LIKE 'your_db%';
2.1.4 進入指定的資料庫
要進入剛才創建的資料庫 your_db_name,執行下面的陳述句，
USE your_db_name;
2.2 表格相關操作
2.2.1 創建表
我們嘗試創建一個表your_table_name，假如該表不存在，則創建；如果存在，則不執行，
CREATE TABLE IF NOT EXISTS `your_table_name` (
`id` BIGINT NOT NULL COMMENT '主鍵' AUTO_INCREMENT,
`your_field_name1` VARCAHR(10) NOT NULL DEFAULT '' COMMENT '你創建的欄位1',
`your_field_name2` CHAR(1) NOT NULL DEFAULT '0' COMMENT '你創建的欄位2',
PRIMARY KEY (`id`) USING BTREE
)
ENGINE = InnoDB
COLLATE = 'utf8mb4_unicode_ci'
COMMENT = '你的表名'
;
2.2.2 查看建表陳述句
如果想看某張表的建表陳述句，執行下面的陳述句：
SHOW CREATE TABLE your_table_name;
2.2.3 洗掉表
通過drop table陳述句洗掉表，
DROP TABLE `your_table_name`;
2.2.4 重命名表
如果想快速備份當前的表(your_table_name)，可以使用rename table命令，把當前表重命名為其他表（例如your_table_name_bak），
RENAME TABLE your_table_name TO your_table_name_bak;
注意：
重命名的表名最好不要帶上
.符號，比如your_table_name.bak，這是因為，sql 決議器有可能誤認為.符號前面的your_table_name是資料庫名，它會報下面的錯誤：
ERROR 1049 (42000): Unknown database 'your_table_name'
2.2.5 快速復制表
已有一張表\(A\)(your_table_name)，如果想快速復制一張表\(A_2\)(your_table_name2)，表\(A_2\)與表\(A\)完全相同，但是不含\(A\)表中的資料，我們可以這么執行：
CREATE TABLE your_table_name2 SELECT * FROM your_table_name LIMIT 0;
如果只是想快速復制表\(A_3\)(your_table_name3)，\(A_3\)表只包含\(A\)表的部分欄位，且同\(A_2\)一樣不包含\(A\)表中的資料，可以這么執行：
CREATE TABLE your_table_name3 SELECT id FROM your_table_name LIMIT 0;
如果想快速復制表\(A_4\)(your_table_name4)，\(A_4\)表包含\(A\)表的所有欄位，并且包含\(A\)表的所有資料，可以這么執行：
CREATE TABLE your_table_name4 SELECT * FROM your_table_name;
如果想快速復制表\(A_5\)(your_table_name5)，\(A_5\)表包含\(A\)表的所有欄位，并且包含A表的若干條資料，比如1條，可以這么執行：
CREATE TABLE your_table_name5 SELECT * FROM your_table_name LIMIT 1;
但是注意，上述執行方式創建的復制表，都只保留原表的欄位相關資訊，但是會丟棄掉原表的主鍵、索引等資訊，如果要復制一張完全相同表結構的表，可以使用LIKE來執行，
CREATE TABLE your_table_same_strut_name LIKE your_table_name;
2.2.5 快速同步表資料
假設有兩張表，表\(A\)（your_table_name）和表\(B\)（another_table_name），兩者欄位完全相同，或部分相同，或欄位含義接近，表\(A\)有全量資料，把表\(A\)的資料一次性同步到表\(B\)，可以通過下面的步驟實作，
初始條件，
# 已知表A （`your_table_name`）
CREATE TABLE IF NOT EXISTS `your_table_name` (
`id` BIGINT NOT NULL COMMENT '主鍵' AUTO_INCREMENT,
`your_field_name1` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '你創建的欄位1',
`your_field_name2` CHAR(1) NOT NULL DEFAULT '0' COMMENT '你創建的欄位2',
PRIMARY KEY (`id`) USING BTREE
)
ENGINE = InnoDB
COLLATE = 'utf8mb4_unicode_ci'
COMMENT = '你的表名'
;
# 表B（another_table_name）
CREATE TABLE `another_table_name` (
`id` BIGINT(20) NOT NULL COMMENT '主鍵' AUTO_INCREMENT,
`field_name1` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '欄位1' COLLATE 'utf8mb4_unicode_ci',
`field_name2` CHAR(1) NOT NULL DEFAULT '0' COMMENT '欄位2' COLLATE 'utf8mb4_unicode_ci',
`field_name3` INT(11) NULL DEFAULT NULL COMMENT '欄位3',
PRIMARY KEY (`id`) USING BTREE
)
COMMENT='另外一張表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
又已知表\(A\)（your_table_name）和表\(B\)（another_table_name）前3個欄位含義相同，那么同步表\(A\)的資料至表\(B\)，可以按以下方式執行：
INSERT INTO `another_table_name` (`id`, `field_name1`, `field_name2`)
SELECT `id`, `your_field_name1`, `your_field_name2` FROM `your_table_name`;
同樣，同步表\(A\)（your_table_name）的部分資料至表\(B\)（another_table_name），比如只同步前2條資料，執行陳述句如下：
INSERT INTO `another_table_name` (`id`, `field_name1`, `field_name2`)
SELECT `id`, `your_field_name1`, `your_field_name2` FROM `your_table_name` LIMIT 2;
2.2.6 清空表資料
如果表中資料沒有用了，表又比較大，那么執行下面的陳述句可以快速清空并重置當前表，為什么說該操作有重置的作用，假設你的主鍵是自增的（AUTO INCREMENT），全量洗掉（DELETE *）資料后，再添加資料，主鍵不會從1開始遞增，而是接繼之前的主鍵值繼續遞增；但是執行TRUNCATE命令后，主鍵值會重新從1開始遞增，
TRUNCATE TABLE `your_table_name`;
注意：
生產環境慎用，
2.2.7 列出庫中的表
SHOW TABLES;
如果要列出特定的表，則可以用 LIKE進行過濾查詢，
SHOW TABLES LIKE '%your_table_name%';
2.2.8 查詢表狀態
SHOW TABLE STATUS WHERE `NAME`= 'your_table_name';
這是可以查詢到表當前的相關資訊，比如表名、表引擎、版本、資料行格式、資料行數、表創建時間、表更新時間、字符集、表注釋等等，
2.3 欄位相關操作
2.3.1 添加欄位
除了創建表時添加欄位外，還可以通過ALTER ADD COLUMN陳述句添加欄位，
-- 添加一個欄位
ALTER TABLE `your_table_name` ADD COLUMN `your_field_name3` DECIMAL(8,2) NOT NULL DEFAULT 0 COMMENT '欄位3' AFTER `your_field_name2`;
-- 添加多個欄位 以半角逗號分割
ALTER TABLE `your_table_name`
ADD COLUMN `your_field_name4` BIT NOT NULL DEFAULT FALSE COMMENT '欄位4' AFTER `your_field_name3`,
ADD COLUMN `your_field_name5` TIMESTAMP NULL COMMENT '欄位5' AFTER `your_field_name4`;
如果不指定欄位在特定欄位后面，則該欄位默認在表的末尾處添加，
2.3.2 修改欄位
變更表欄位，使用 ALTER CHANGE COLUMN陳述句，
-- 變更一個欄位
ALTER TABLE `your_table_name` CHANGE COLUMN `your_field_name3` `your_field_new_name3` DECIMAL(8,3) NOT NULL DEFAULT 0 COMMENT '新欄位3';
-- 變更多個欄位 以半角逗號分割
ALTER TABLE `your_table_name`
CHANGE COLUMN `your_field_name4` `your_field_new_name4` TINYINT NOT NULL DEFAULT 0 COMMENT '新欄位4',
CHANGE COLUMN `your_field_name5` `your_field_new_name5` DATETIME NULL COMMENT '新欄位5';
2.3.3 洗掉欄位
洗掉欄位，使用 ALTER DROP COLUMN陳述句，
-- 洗掉一個欄位
ALTER TABLE `your_table_name` DROP COLUMN `your_field_new_name3`;
-- 洗掉多個欄位 以半角逗號分割
ALTER TABLE `your_table_name`
DROP COLUMN `your_field_new_name4`,
DROP COLUMN `your_field_new_name5`;
2.3.4 列出表中欄位資訊
有時要查看表中有哪些欄位，可以通過SHOW COLUMNS陳述句實作，
SHOW COLUMNS FROM `your_table_name`;
查詢后的效果如下所示，我們可以得到欄位名、欄位型別、是否為Null，Key值，默認值和額外資訊，
mysql> SHOW COLUMNS FROM `your_table_name`;
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| your_field_name1 | varchar(10) | NO | | | |
| your_field_name2 | char(1) | NO | | 0 | |
+------------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
上述陳述句同下面的陳述句等價，
SHOW FIELDS FROM `your_table_name`;
如果要展示欄位的更多資訊，可以使用下面的陳述句，相較于上面的陳述句，增加了字符集資訊，權限資訊和注釋資訊，
SHOW FULL FIELDS FROM `your_table_name`;
2.4 索引相關操作
2.4.1 添加索引
使用ADD INDEX 執行添加索引操作：
ALTER TABLE `your_table_name` ADD INDEX `your_index_name` (`your_field_name1`);
還可以添加聯合索引：
ALTER TABLE `your_table_name` ADD INDEX `your_composite_index_name` (`your_field_name1`, `your_field_name2`);
如果要添加唯一索引，則可以增加UNIQUE（唯一性）關鍵字執行：
ALTER TABLE `your_table_name` ADD UNIQUE INDEX `your_index_name` (`your_field_name1`);
如果要添加主鍵，那么可以執行：
ALTER TABLE `your_table_name` ADD PRIMARY KEY (`id`);
2.4.2 洗掉索引
使用DROP INDEX 執行索引的丟棄操作，
ALTER TABLE `your_table_name` DROP INDEX `your_index_name`;
洗掉主鍵，執行：
ALTER TABLE `your_table_name` DROP PRIMARY KEY;
2.4.3 查看已有索引
SHOW INDEX FROM命令可以用來查看某張表下所有索引資訊，
SHOW INDEX FROM `your_table_name`;
三、備份和還原資料
備份和還原資料，通常使用mysqldump命令和source命令（在mysql client內執行），
3.1 備份操作
備份操作特別適合于資料的全量匯出的情況，特別是資料存在二進制（binary或blob）的情況，
3.1.1 備份單庫和庫內資料
linux環境下，可以執行：
mysqldump -u root -p your_db_name > /home/your_db_name.sql
windows環境下，需要指定具體盤符：
mysqldump -u root -p your_db_name > E:/your_db_name.sql
3.1.2 備份單庫庫表結構
查看mysqldump操作幫助（mysqldump --help），可知，添加-d運算子，可以匯出不包含資料的庫表資訊，
-d, --no-data No row information.
于是，備份單庫但不要庫內資料的執行陳述句如下：
mysqldump -u root -p -d your_db_name > /home/your_db_name_without_data.sql
3.1.3 備份多庫及庫內資料
使用-B運算子，可以指定多個庫，
mysqldump -u root -p -B your_db_name your_db_name_bak > /home/your_db_names.sql
3.1.4 備份單庫內若干表結構及表內資料
使用--tables運算子，可以指定多個表，
mysqldump -u root -p your_db_name --tables your_table_name your_table_name2 > /home/your_table_names.sql
3.1.5 備份單庫內若干表結構
mysqldump -u root -p -d your_db_name --tables your_table_name your_table_name2 > /home/your_table_names_without_data.sql
3.1.6 備份單庫并忽略特定的表
使用--ignore-table=name運算子，可以在匯出庫的同時，忽略特定的表，
mysqldump -u root -p your_db_name --ignore-table=your_db_name.another_table_name --ignore-table=your_db_name.your_table_name_bak > /home/your_db_name_ignore_tables.sql
注意：
要忽略的表名一定要書寫正確，否則會出現執行了明明忽略操作，但是對應的表仍會匯出的錯覺，
3.2 還原操作
3.2.1 使用SOURCE還原資料
該操作需要在mysql client中執行，
假設要還原某個庫里面的資料，可以通過下面簡單的兩步實作還原庫中資料的操作，
首先，切換到要還原的庫，
USE your_db_name;
接著，在庫內執行SOURCE命令，SOURCE后跟的是匯出的或者你已準備好的資料的實際路徑，
SOURCE /home/your_db_name.sql
3.2.2 使用mysqldump還原資料
mysqldump同樣支持把備份的資料還原回去，只需執行的時候，把指向箭頭掉個方向，
mysqldump -u root -p your_db_name < /home/your_db_name.sql
注意：
通常不推薦使用這種方式，因為這種方式會機械的執行庫表的還原，如果庫名不對應，執行的預期不是我們想要的，
四、授權和識訓
4.1 授權
使用GRANT TO命令進行庫表的授權，
我們先嘗試創建一個本地用戶custom_user, 并為其設定密碼customPassword，
CREATE USER 'custom_user'@'localhost' IDENTIFIED BY 'customPassword';
4.1.1 授權庫
授權本地用戶custom_user擁有your_db_name的所有權限，
GRANT ALL PRIVILEGES ON your_db_name.* TO 'custom_user'@'localhost';
FLUSH PRIVILEGES;
4.1.2 授權特定表
授權本地用戶custom_user擁有your_db_name的特定表(your_table_name、your_table_name2)的所有權限，
GRANT ALL PRIVILEGES ON your_db_name.your_table_name TO 'custom_user'@'localhost';
GRANT ALL PRIVILEGES ON your_db_name.your_table_name2 TO 'custom_user'@'localhost';
FLUSH PRIVILEGES;
4.1.3 授權只讀查詢
可以授權本地用戶custom_user只擁有your_db_name的特定表(your_table_name、your_table_name2)的查詢權限，
GRANT SELECT ON your_db_name.your_table_name TO 'custom_user'@'localhost';
GRANT SELECT ON your_db_name.your_table_name2 TO 'custom_user'@'localhost';
FLUSH PRIVILEGES;
注意：
這里授權查詢操作需保證，對應庫表存在，
4.2 權限識訓
識訓權限使用REVOKE FROM命令，
4.2.1 識訓所有權限
識訓所有之前賦給本地用戶custom_user對于特定庫your_db_name的權限，
REVOKE ALL PRIVILEGES ON your_db_name.* FROM 'custom_user'@'localhost';
FLUSH PRIVILEGES;
注意：
授權是可以累計的，因此，權限識訓要與授權保持一一對應，否則，會出現權限識訓了，但仍能看到被授權相關資訊的錯覺，
4.2.2 識訓只讀查詢權限
REVOKE SELECT ON your_db_name.your_table_name FROM 'custom_user'@'localhost';
REVOKE SELECT ON your_db_name.your_table_name2 FROM 'custom_user'@'localhost';
FLUSH PRIVILEGES;
五、其他
5.1 查看資料磁盤空間占用
查看磁盤空間占用的目的，是方便在資料匯出等操作時做出合理選擇，比如，避免匯出不重要但很大的資料，
5.1.1 查看資料庫資料磁盤空間占用
SELECT
table_schema as '資料庫',
SUM(table_rows) as '記錄數',
SUM(TRUNCATE(data_length/1024/1024, 2)) as '資料容量(MB)',
SUM(TRUNCATE(index_length/1024/1024, 2)) as '索引容量(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;
5.1.2 查看庫中各表資料磁盤空間占用
SELECT
table_schema as '資料庫',
table_name as '表名',
table_rows as '記錄數',
TRUNCATE(data_length/1024/1024, 2) as '資料容量(MB)',
TRUNCATE(index_length/1024/1024, 2) as '索引容量(MB)'
FROM information_schema.tables
WHERE table_schema='your_db_name'
ORDER BY data_length DESC, index_length DESC;
5.2 釋放磁盤空間
通常在執行一些大表資料的洗掉之后，資料庫并不會立即釋放磁盤空間，這時，執行OPTIMIZE命令可以用來釋放相應的磁盤空間，
OPTIMIZE TABLE your_table_name;
出處：https://www.cnblogs.com/lunyu/p/16630611.html
著作權：本文著作權歸作者和博客園共有
轉載：歡迎轉載，但未經作者同意，必須保留此段宣告；必須在文章中給出原文連接；否則必究法律責任
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/502953.html
標籤：其他
下一篇：Redis 集群模式