大部分引用:https://github.com/TIGERB/easy-tips/blob/master/mysql/sql.md ## 常用sql語句整理：mysql 1. 增 - 增加一張表 ``` CREATE TABLE `table_name`( ... )ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` - 增加記錄 ``` INSERT INTO `your_table_name`(`column_name`) VALUES ('your_value_one'), ('your_value_two'); ``` - 增加字段 ``` ALTER TABLE `your_table_name` ADD `your_column_name` ... AFTER `column_name`; ``` - 增加索引 + 主鍵 ``` ALTER TABLE `your_table_name` ADD PRIMARY KEY your_index_name(your_column_name); ``` + 唯一索引 ``` ALTER TABLE `your_table_name` ADD UNIQUE your_index_name(your_column_name); ``` + 普通索引 ``` ALTER TABLE `your_table_name` ADD INDEX your_index_name(your_column_name); ``` + 全文索引 ``` ALTER TABLE `your_table_name` ADD FULLTEXT your_index_name(your_column_name); ``` 2. 刪 - 逐行刪除 ``` DELETE FORM `table_name` WHERE ...; ``` - 清空整張表 ``` TRUNCATE TABLE `your_table_name`; ``` - 刪除表 ``` DROP TABLE `your_table_name`; ``` - 刪除字段 ``` ALTER TABLE `your_table_name` DROP `column_name`; ``` - 刪除索引 ``` ALTER TABLE `your_table_name` DROP INDEX your_index_name(your_column_name); ``` 3. 改 - 變更數據 ``` UPDATE `table_name` SET column_name=your_value WHERE ...; ``` - 變更字段 ``` ALTER TABLE `your_table_name` CHANGE `your_column_name` `your_column_name` ...(變更); ``` - 變更字段值為另一張表的某個值 ``` UPDATE `your_table_name` AS a JOIN `your_anther_table_name` AS b SET a.column = b.anther_column WHERE a.id = b.a_id...; ``` 4. 查 - 普通查詢 ``` SELECT `column_name_one`, `column_name_two` FROM `table_name`; ``` - 關聯查詢 ``` SELECT * FROM `your_table_name` AS a JOIN `your_anther_table_name` AS b WHERE a.column_name = b.column_name...; ``` - 合計函數條件查詢：WHERE 關鍵字無法與合計函數一起使用 ``` SELECT aggregate_function(column_name) FROM your_table_name GROUP BY column_name HAVING aggregate_function(column_name)...; ``` - 同一個實例下跨庫查詢 ``` SELECT * FROM database_name.your_table_name AS a JOIN another_database_name.your_another_table_name AS b WHERE a.column_name = b.column_name...; ``` 5. 復制一張表結構 ``` CREATE TABLE `your_table_name` LIKE `destination_table_name`; ``` 6. 完全復制一張表：表結構+全部數據 ``` CREATE TABLE `your_table_name` LIKE `destination_table_name`; INSERT INTO `your_table_name` SELECT * FROM `destination_table_name`; ``` --- ### 附錄：mysql常用命令 - 登陸： mysql -h host -u username -p - 列出數據庫：SHOW DATABESES; - 列出表:SHOW TABLES; - 列出表結構:DESC table_name - 使用一個數據庫：USE database_name; - 導入：source 'file'; - 導出：mysqldump -h 127.0.0.1 -u root -p "database_name" "table_name" --where="condition" > file_name.sql; - 查看慢日志：mysqldumpslow -s [c:按記錄次數排序/t:時間/l:鎖定時間/r:返回的記錄數] -t [n:前n條數據] -g "正則"　/path - 新增用戶： insert into `user`(`Host`, `User`, `authentication_string`) value('localhost', 'username', password('pwd')) ### mysql 5.7 新增用戶 ``` // 插入新用戶 insert into mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject value('localhost', 'username', password('password'), '', '', ''); // 數據庫授權 grant all privileges on dbname.name.* to username@localhost identified by 'password'; // 刷新權限信息 FLUSH PRIVILEGES; ```