本文羅列工作中常用的MySQL命令，方便工作速查描滔，并會不但更新完善。
配置系列
DDL
- 創(chuàng)建數(shù)據(jù)庫
create database db_name;
create database if not exists db_name;
create database db_name default character set utf8mb4;
- 查看數(shù)據(jù)庫
-- 查看所有的數(shù)據(jù)庫
show databases;
-- 查看庫定義信息
show create database db_name;
- 修改數(shù)據(jù)庫
alter database db_name character set utf8;
- 刪除數(shù)據(jù)庫
drop database db_name;
- 使用數(shù)據(jù)庫
-- 查看正在使用的數(shù)據(jù)庫
select database();
-- 改變數(shù)據(jù)庫
use db_name;
- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE `table_name` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4;
-- 快速創(chuàng)建表
create table table_name2 like table_name2;
- 查看數(shù)據(jù)表
-- 查看所有表
show tables;
-- 查看表結(jié)構(gòu)
desc table_name;
-- 查看表創(chuàng)建語句
show create table table_name;
- 修改表結(jié)構(gòu)
alter table table_name add remark varchar(20);
alter table table_name modify remark varchar(30);
ALTER TABLE table_name DROP COLUMN remark;
alter table table_name change remark remark2 varchar(40);
rename table table_name1 to table_name2;
alter table table_name character set utf8mb4;
- 刪除數(shù)據(jù)表
drop table table_name;
drop table if exists table_name;
- 創(chuàng)建視圖
CREATE VIEW v_staffs AS SELECT * FROM staffs;
- 更新視圖
-- 更新視圖，會同步更新基本表數(shù)據(jù)
UPDATE v_staffs SET salary = salary + 1000 WHERE uid = 1;
- 刪除視圖
DROP VIEW v_staffs;
- 創(chuàng)建索引
-- 創(chuàng)建普通索引
CREATE INDEX 索引名稱 ON 表名(字段名);
-- 創(chuàng)建唯一索引
CREATE UNIQUE INDEX 索引名稱 ON 表名(字段名);
-- 創(chuàng)建主鍵索引
ALTER TABLE 表名 ADD PRIMARY KEY 索引名稱(字段名);
- 查看索引
SHOW INDEX FROM 表名;
- 刪除索引
DROP INDEX 索引名稱 ON 表名;
DML
- 插入記錄
insert into table_name (id, name) values ("1", "barret");
- 復(fù)制記錄
create table table_name1 like table_name2;
insert into table_name1 select * from table_name2;
insert into table_name1 (name, age) select name, age from table_name2;
- 更新記錄
update table_name set name='barret' where id=1;
- 刪除記錄
delete from table_name;
delete from table_name where id = 1;
DQL
- 查詢數(shù)據(jù)
select * from table_name;
select id, name from table_name;
select id as 編號, name as 名稱 from table_name;
select tb.id as 編號, tb.name as 名稱 from table_name as tb;
select distinct name from table_name;
- 查詢結(jié)果參與運(yùn)算
select id+5, name from table_name;
- 條件查詢
3.1 比較運(yùn)算- >羡洛、<鹤竭、>=踊餐、<=、=臀稚、<>/!=
- BETWEEN ... AND...
- IN(...)
- LIKE 'some%'
- IS NULL
-- like某個確定的值 等效于 where name = 'yyx'
select * from stu where name like 'yyx';
-- %代表任意個任意字符吝岭，查詢name字段中包含x的
select * from stu where name like '%x%';
-- 查詢name字段中最后一個字符 為 u的
select * from stu where name like '%u';
-- 查詢name字段中第一個字符 為 q 的
select * from stu where name like 'q%';
-- 使用 _ 表示一個任意字符，查詢表中 name 字段為兩個字符的數(shù)據(jù)
select * from users where name like '__';
-- 查詢 name 字段最后為j吧寺，的兩個字符的數(shù)據(jù)
select * from users where name like '_j';
3.2 邏輯運(yùn)算
- AND OR NOT
3.3 排序
...ORDER BY 字段名 [ASC|DESC];
3.4 聚合函數(shù)
- MAX(列名)
- MIN(列名)
- AVG(列名)
- COUNT(列名)
- SUM(列名)
3.5 分組
...GROUP BY 分組字段 [HAVING 條件];- WHERE:分組之前過濾數(shù)據(jù)窜管，條件中不可以使用聚合函數(shù)
- HAVING:分組之后過濾數(shù)據(jù)，條件中可以使用聚合函數(shù)
3.6 limit語句
... LIMIT length;... LIMIT offset, length;... LIMIT length OFFSET offset;
3.7 約束
- 主鍵約束：PRIMARY KEY稚机，同時滿足 非空 和 唯一
- 默認(rèn)約束：DEFAULT
- 非空約束：NOT NULL
- 唯一約束：UNIQUE
- 外鍵約束：FOREIGN KEY
CREATE TABLE `student_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序號',
`name` varchar(64) NOT NULL COMMENT '姓名',
`class_id` int COMMENT '班級序號',
`insert_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '創(chuàng)建時間',
`update_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '更新時間',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`),
FOREIGN KEY (class_id) REFERENCES class_table(id)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4;
3.8 多表查詢
- 內(nèi)連接:
inner join - 外連接:
left join和right join - 聯(lián)合:
union
3.9 執(zhí)行順序
(8)SELECT (9)DISTINCT (11)TOP n <select_item_list>
(1)FROM <left_table> (3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <cube|rollup>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_condition>;
DCL
針對用戶的操作本質(zhì)上是對mysql.user數(shù)據(jù)表的修改
- 創(chuàng)建用戶
create user '用戶名'@'主機(jī)名' identified by '密碼';
- 關(guān)于主機(jī)名：本地用戶使用localhost幕帆；任意遠(yuǎn)程用戶可使用通配符%
- 用戶授權(quán)
-- 查看用戶授權(quán)
show grants for '用戶名'@'主機(jī)名';
-- 給用戶授權(quán)
grant 權(quán)限1, 權(quán)限2，... on 數(shù)據(jù)庫名.表名 to '用戶名'@'主機(jī)名';
-- 給用戶授權(quán)所有數(shù)據(jù)庫和表
grant all privileges on *.* to '用戶名'@'主機(jī)名';
-- 撤銷用戶權(quán)限
revoke 權(quán)限1, 權(quán)限2赖条，... on 數(shù)據(jù)庫名.表名 from '用戶名'@'主機(jī)名';
-- 撤銷用戶所有授權(quán)
revoke all privileges on *.* from '用戶名'@'主機(jī)名';
- 刪除用戶
drop user '用戶名'@'主機(jī)名';
- 修改密碼
# 修改管理員密碼
mysqladmin -uroot -p舊密碼 新密碼
-- 修改當(dāng)前用戶密碼
set password = password("some_pwd");
-- 修改指定用戶密碼
set password for '用戶名'@'主機(jī)名' = password("some_pwd");
備份與恢復(fù)
- 備份表數(shù)據(jù)
mysqldump -uroot -p db_name table_name1 table_name2 > some_file.sql // 備份db_name 下面的table_name1 和 table_name2
- 備份庫數(shù)據(jù)
mysqldump -uroot -p db_name > some_file.sql //備份db_name數(shù)據(jù)庫
mysqldump -uroot -p --databases db_name1 db_name2 > some_file.sql //備份多個數(shù)據(jù)庫
mysqldump -uroot -p --all-databases > some_file.sql //備份所有數(shù)據(jù)庫
- 物理備份
- 停止MySQL失乾，然后復(fù)制MySQL中data目錄
- 還原表數(shù)據(jù)
mysql -uroot -p some_db < some_file.sql //還原some_db的表文件
mysql> use some_db; //還原some_db的表文件
mysql> source some_file.sql;
- 還原庫數(shù)據(jù)
mysql -uroot -p < some_file.sql //還原數(shù)據(jù)庫
mysql> source some_file.sql //還原數(shù)據(jù)庫
事務(wù)
BEGIN; ... ROLLBACK; ... COMMIT;SET AUTOCOMMIT=0; ... SET AUTOCOMMIT=1;
忘記密碼
- 第一步：停止MySQL
- 第二步：
mysqld --skip-grant-tables跳過驗(yàn)證啟動MySQL - 第三步：打開MySQL客戶端修改密碼
USE mysql;
UPDATE user SET password=password('123456') WHERE user='root';
清理binlog
- 查看binlog：
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000076 | 524735314 |
| mysql-bin.000077 | 524735314 |
| mysql-bin.000078 | 524735314 |
| mysql-bin.000079 | 524735314 |
| mysql-bin.000080 | 524735314 |
| mysql-bin.000081 | 524735314 |
| mysql-bin.000082 | 205076231 |
+------------------+-----------+
7 rows in set (0.00 sec)
- 清理binlog:
mysql> help purge
.....
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010'; # 清理掉mysql-bin.010之前的binlog
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; #清理掉時間點(diǎn)之前的binlog
- 手動清理binlog：
- 第一步：刪除
master-log-bin.index里面的binlog對應(yīng)項(xiàng) - 第二步：刪除相應(yīng)的binlog文件
- 第一步：刪除
- 設(shè)置過期自動清理：
mysql> show variables like 'expire_logs_days'; #查看過期清理時間
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 7 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> set global expire_logs_days = 3; #設(shè)置過期清理時間
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs; #觸發(fā)日志清理
問題排查
- 查看先當(dāng)前庫的進(jìn)程情況
show full processlist;
- 殺死進(jìn)程
kill 進(jìn)程號;
