mysql 命令整理詳情 - mysql,MySQL,數據庫小熊跳舞博客

連接數據庫

使用客户端連接數據庫服務器

登錄MySQL：

mysql [-h 主機名或IP地址] [-P 端口號] -u 用户名 -p

參數説明：

-h : 指定客户端所要登錄的 MySQL 主機名, 登錄本機(localhost 或 127.0.0.1)該參數可以省略。

-P : 默認端口號3306。

退出登錄：

exit 或 quit

數據庫

創建數據庫

CREATE DATABASE [IF NOT EXISTS] database_name  
  [[DEFAULT] CHARACTER SET charset_name]
  [[DEFAULT] COLLATE collation_name];

示例：

CREATE DATABASE test_db;

CREATE DATABASE IF NOT EXISTS test_db_char DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_chinese_ci;

查看數據庫

查看所有數據庫

SHOW DATABASES;

查看數據庫創建命令

SHOW CREATE DATABASE database_name;

修改數據庫

ALTER DATABASE database_name CHARACTER SET charset_name;

使用數據庫

USE database_name;

刪除數據庫

DROP DATABASE [IF EXISTS] database_name;

數據表

創建數據表

CREATE TABLE table_name
(
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
)[ENGINE=InnoDB] [DEFAULT CHARSET=utf8] [COLLATE=utf8_general_ci] [AUTO_INCREMENT=n];

數據類型：

數值類型：BIT、TINYINT、SMALLINT、MEDIUMINT、INT或INTEGER、BIGINT、FLOAT、DOUBLE、DECIMAL

字符串類型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB、ENUM、SET

日期和時間類型：DATE、TIME、YEAR、DATETIME、TIMESTAMP

空間數據類型：GEOMETRY、POINT、LINESTRING、POLYGON、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

約束：

NOT NULL

DEFAULT

UNIQUE

AUTO_INCREMENT

PRIMARY KEY

FOREIGN KEY

創建索引

普通索引

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

唯一索引

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

創建視圖

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

查看數據表

查看當前數據庫的所有表

SHOW TABLES;

查看數據表創建命令

SHOW CREATE TABLE table_name;

查看數據表的結構

SHOW COLUMNS FROM 數據表:

DESCRIBE 數據表；

DESC 數據表；

EXPLAIN 數據表；

查看數據表的詳細索引信息

SHOW INDEX FROM 數據表:

查看錶信息

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] \G:

説明： \G 表示查詢結果按列打印

修改數據表

修改表名

ALTER TABLE old_table_name
RENAME [TO] new_table_name;

修改表存儲引擎

ALTER TABLE table_name
ENGINE = 存儲引擎名稱;

添加列

ALTER TABLE table_name
ADD [COLUMN] new_column_name datatype [FIRST | AFTER column_name];

修改列的數據類型

ALTER TABLE TABLE_NAME
MODIFY [COLUMN] column_name new_datatype [FIRST | AFTER column_name];

修改列名

ALTER TABLE table_name
CHANGE [COLUMN] old_column_name new_column_name datatype [FIRST | AFTER column_name];

修改列默認值

ALTER TABLE table_name
ALTER [COLUMN] column_name SET DEFAULT 默認值;

刪除列默認值

ALTER TABLE table_name
ALTER [COLUMN] column_name DROP DEFAULT;

刪除列

ALTER TABLE table_name
DROP [COLUMN] column_name;

添加主鍵

ALTER TABLE table_name
ADD PRIMARY KEY (column_name, ...);

刪除主鍵

ALTER TABLE table_name
DROP PRIMARY KEY;

添加外鍵

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);

刪除外鍵

ALTER TABLE table_name
DROP FOREIGN KEY fk_name;

添加索引

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

添加唯一索引

ALTER table table_name  
ADD CONSTRAINT unique_constraint_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...);

ALTER TABLE table_name

ADD UNIQUE [INDEX] index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

刪除索引

ALTER TABLE table_name
DROP INDEX index_name;

刪除視圖

DROP VIEW [IF EXISTS] view_name;

刪除索引

DROP INDEX index_name ON table_name;

刪除數據表

DROP TABLE [IF EXISTS] table_name;

數據

插入數據

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    (value1, value2, value3, ...);

INSERT IGNORE INTO：如果當前插入數據的主鍵（或唯一列）在數據表中已存在，則不插入當前數據；不存在，則插入當前數據。

INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    (value1, value2, value3, ...);

REPLACE INTO：如果表中存在 primary key 或 unique 相同的記錄，則先刪除掉，再插入新記錄；如果不存在相同的記錄，直接插入新記錄。

REPLACE INTO table_name (column1, column2, column3, ...)
VALUES
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    (value1, value2, value3, ...);

查詢數據

通用查詢：

SELECT [DISTINCT] column_name1, column_name2, ...
FROM table_name
[ WHERE condition ]
[ ORDER BY column_name1 [ASC | DESC] [, column_name2 [ASC | DESC]] ]
[ LIMIT [start,] length]

分組查詢：

SELECT column_name1, aggregate_function(column_name2)
FROM table_name
[ WHERE condition ]
GROUP BY column_name1 [,column_name2]
[ HAVING condition ];

分組查詢説明：

在 SQL 中增加 HAVING 子句原因是，WHERE 關鍵字無法與聚合函數一起使用。

HAVING 子句可以使用聚合函數篩選分組後的各組數據。

連接兩個以上的 SELECT 語句的結果組合到一個結果集合：

SELECT column1, column2, ...
FROM table1
WHERE condition1

UNION [ALL]

SELECT column1, column2, ...
FROM table2
WHERE condition2

[ORDER BY column1, column2, ...];

説明：

UNION 操作符在合併結果集時會去除重複行，而 UNION ALL 不會去除重複行。

ORDER BY 子句是一個可選的子句，用於指定合併後的結果集的排序順序。

多表查詢-內連接：

SELECT column_name1, column_name2, ...
  FROM table1
  INNER JOIN table2 ON table1.column_name = table2.column_name
[ INNER JOIN table3 ON table1.column_name = table3.column_name ];

多表查詢-左外連接：

SELECT column_name1, column_name2, ...
  FROM table1
  LEFT [OUTER] JOIN table2 ON table1.column_name = table2.column_name
[ LEFT [OUTER] JOIN table3 ON table1.column_name = table3.column_name ];

多表查詢-右外連接：

SELECT column_name1, column_name2, ...
  FROM table1
  RIGHT [OUTER] JOIN table2 ON table1.column_name = table2.column_name
[ RIGHT [OUTER] JOIN table3 ON table1.column_name = table3.column_name ];

多表查詢-笛卡爾積：

SELECT column_name1, column_name2, ...  
  FROM table1  
  CROSS JOIN table2;

直接查詢多表，也會產生笛卡爾積

SELECT column_name1, column_name2, ...  
FROM table1, table2;

更新數據

基礎更新：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[ WHERE condition ];

根據一張表或多張表的數據來更新另一張表的數據：

UPDATE table1 INNER JOIN table2 ON table1.column1 = table2.column2  
SET table1.column2 = table2.column2
[ WHERE condition ];

刪除數據

刪除數據：

DELETE FROM table_name
[ WHERE condition ];

清空表中的所有數據，但不會刪除表本身

TRUNCATE TABLE table_name;

事務

開始一個事務：

BEGIN;  或者 START TRANSACTION;

提交事務：

COMMIT;

回滾事務：

ROLLBACK;

導出、導入數據

導出、導入數據（只包含表數據，不包含表結構）

導出方法：

SELECT column1, column2, ...
FROM table_name
[ WHERE conditions ]
INTO OUTFILE 'file_path'
[ FIELDS TERMINATED BY ','  [OPTIONALLY] ENCLOSED BY '"' ]  
[ LINES TERMINATED BY '\n' ];

導出方法-示例1：

SELECT *  
FROM users  
INTO OUTFILE 'd:/users.txt';

導出方法-示例2：

SELECT *  
FROM users  
INTO OUTFILE 'd:/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

導入方法：

LOAD DATA [LOCAL] INFILE 'file_path' INTO TABLE table_name
[ FIELDS TERMINATED BY ','  [OPTIONALLY] ENCLOSED BY '"' ]  
[ LINES TERMINATED BY '\n' ];

導入説明：

如果指定LOCAL關鍵詞，則表明從客户主機上按路徑讀取文件。如果沒有指定，則在服務器上按路徑讀取文件。

導入方法-示例1：

LOAD DATA LOCAL INFILE 'd:/users.txt' INTO TABLE users;

導出、導入數據（可包含表數據和表結構）

導出方法：

mysqldump -u username -p password -h hostname database_name > output_file.sql

導出方法-示例（導出整個數據庫）：

mysqldump -u root -p mydatabase > mydatabase_backup.sql

mysqldump -u root -p mydatabase > D:/mydatabase_backup.txt

導出方法-示例（導出特定表）：

mysqldump -u root -p mydatabase mytable > mytable_backup.sql

mysqldump -u root -p mydatabase mytable > mytable_backup.txt

導出方法-示例（只導出結構，不包括數據）：

mysqldump -u root -p --no-data mydatabase > output_file.sql

mysqldump -u root -p --no-data mydatabase > output_file.txt

導入方法1（直接在cmd命令行執行）：

mysql -u root -p database_name < output_file.sql

導入方法2（通過"mysql -u username -p "進入mysql命令行後執行）：

source output_file.sql

用户

修改用户密碼

mysqladmin -u用户名 -p舊密碼 password 新密碼

注意：“-u”和“用户名”直接沒有空格。

mysqladmin可直接在命令行執行，無需使用“mysql -u 用户名 -p ”連接到數據庫服務器後再執行。

用户設置

創建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

説明：

host：指定用户可以從哪些主機連接。例如，localhost 僅允許本地連接，% 允許從任何主機連接。

授權權限

GRANT privileges ON database_name.* TO 'username'@'host';

説明：

privileges：所需的權限，如 ALL PRIVILEGES、SELECT、INSERT、UPDATE、DELETE 等。

刷新權限

FLUSH PRIVILEGES;

查看用户權限

SHOW GRANTS FOR 'username'@'host';

撤銷權限

REVOKE privileges ON database_name.* FROM 'username'@'host';

刪除用户

DROP USER 'username'@'host';

修改用户密碼

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

修改用户主機

可以先刪除用户，再重新創建一個新的用户。

觸發器

創建觸發器

DELIMITER &&

CREATE TRIGGER trigger_name

  [BEFORE|AFTER] [INSERT|UPDATE|DELETE]

  ON table_name FOR EACH ROW

BEGIN

  -- 觸發器主體邏輯

  -- 可使用 NEW 和 OLD 訪問新舊數據  
END&&

DELIMITER ;

查看觸發器

查看當前數據庫的所有觸發器：

SHOW TRIGGERS;

查看特定觸發器定義：

SHOW CREATE TRIGGER trigger_name\G;

刪除觸發器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

説明：schema_name指數據庫名。

存儲過程

創建存儲過程

DELIMITER //  -- 臨時修改分隔符（命令行工具中需使用）

CREATE PROCEDURE procedure_name(

    [IN | OUT | INOUT] parameter1 datatype,  -- 輸入/輸出參數，默認值為 IN

    ...

)

BEGIN  
    -- SQL 邏輯（可包含變量聲明、條件判斷、循環等）

    DECLARE local_var INT;  -- 聲明局部變量  
    SET local_var = 10;     -- 變量賦值

    SELECT * FROM table WHERE column = local_var;

END //

DELIMITER ;  -- 恢復默認分隔符

查看存儲過程

查看所有存儲過程：

SHOW PROCEDURE STATUS [LIKE 'pattern'];

查詢指定的存儲過程在創建時的SQL語句：

SHOW CREATE PROCEDURE 存儲過程名稱;

調用存儲過程

CALL 存儲過程名稱([參數]);

刪除存儲過程

DROP PROCEDURE [IF EXISTS] 存儲過程名稱;

博客 / 詳情