1 數(shù)據(jù)庫相關(guān)

-- --------------------表相關(guān)的----------
-- 查看字符集
show variables like '%character%';show databases;# 創(chuàng)建數(shù)據(jù)庫
create database test2;# 刪除數(shù)據(jù)庫
drop database test2;
show databases;#查看當(dāng)前使用的數(shù)據(jù)庫
select database();

2 用戶相關(guān)

-- --------------------用戶授權(quán)相關(guān)的----------
#切換數(shù)據(jù)庫
use test;select user();# 會報錯：[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'user12345'' at line
# mysql8.0版本為新版本，需要先創(chuàng)建用戶再分配權(quán)限，不能和舊版本一樣
# MySQL5.7版本可以使用
grant all on test.* to 'user1'@'%' identified by 'user12345';
FLUSH PRIVILEGES;# mysql8.0版本上需要使用：
# 1、創(chuàng)建新用戶
create user 'user1'@'%' identified by 'user12345';# 2、刷新權(quán)限
flush privileges;# 3、賦予權(quán)限，%標(biāo)識不限制主機(jī)
grant all on test.* to 'user1'@'%';# 4、刷新權(quán)限
flush privileges;# 5、查看用戶
select host,user,plugin,authentication_string from mysql.user;# 收回權(quán)限
revoke insert on test.* from 'user1'@'%';# 刪除用戶
drop user 'user1'@'%';# 查看權(quán)限，all包含的權(quán)限有：SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER
show grants for 'user1'@'%';

3 數(shù)據(jù)表相關(guān)

-- --------------------表相關(guān)的----------
CREATE TABLE business_logs (id          BIGINT PRIMARY KEY AUTO_INCREMENT,timestamp   DATETIME     NOT NULL,user_id     INT          NOT NULL,action      VARCHAR(255) NOT NULL,description TEXT,ip_address  VARCHAR(50),log_level   VARCHAR(20)
);-- 查看建表語句
show create table business_logs;-- 查看所有表
show tables;-- 查看表結(jié)構(gòu)
desc business_logs;select *
from business_logs;-- 插入數(shù)據(jù)到表中
insert into business_logs (timestamp, user_id, action, description) values (sysdate(), 1, 'cap', '查詢成功');
insert into business_logs (timestamp, user_id, action, description) values (sysdate(), 2, 'del', '刪除成功');
insert into business_logs (timestamp, user_id, action, description) values (sysdate(), 1, 'add', '添加成功');-- 更新表
update business_logs set action = 'delete', description = '刪除成功' where id = 1;-- 刪除表中的數(shù)據(jù)
delete from business_logs where id = 1;-- 查詢語句
/*
查詢關(guān)鍵字：
select字段列表
from表名
where查詢條件
group by分組字段
having分組后條件字段
order by排序字段
limit分頁參數(shù)
*/

3.1 聚合函數(shù)

聚合函數(shù)（count、sum、max、min、avg）使用

-- 刪除表
drop table if exists user;-- 創(chuàng)建表
create table user (id   int primary key auto_increment,name varchar(20) not null,age  int         not null,city varchar(20) default null
);-- 插入數(shù)據(jù)
insert into user (name, age, city) values ('張三', 20, '北京'), ('李四', 22, '上海'), ('王五', 20, '北京');select * from user;-- 查詢各個城市的人數(shù)
select city, count(*) as usr_num from user group by city;-- 查詢各個城市最小的年齡
select city, min(age) as age from user group by city;-- 查詢平均年齡
select avg(age) as age from user;-- 查詢年齡總和
select sum(age) as sum_age from user;

注意：所有的null值不參與聚合函數(shù)的運(yùn)算

3.2 分組查詢

基本語法

select 字段 from 表名 [where 條件] group by 分組字段 [having 分組后過濾條件]

where和having的區(qū)別

• 執(zhí)行時機(jī)不同：where是分組之前進(jìn)行過濾，不滿足where條件的數(shù)據(jù)不參與分組，而having是分組之后對結(jié)果進(jìn)行過濾
• 判斷條件不同：where不能對聚合函數(shù)進(jìn)行判斷，而having可以

insert into user (name, age, city) values ('張三2', 20, '北京'), ('李四2', 22, '上海'), ('王五2', 20, '北京');
insert into user (name, age, city) values ('夏明', 30, '北京'), ('李紅', 32, '北京'), ('張飛', 56, '西安');-- 查詢年齡小于等于30的人員
select * from user where age <= 30;-- 查詢各個城市中，年齡小于30的人數(shù)
select city, count(*) as user_num from user where age <= 30 group by city;-- 查詢年齡小于等于30的人員，按照城市進(jìn)行分組，獲取人員數(shù)量大于3的城市
select city, count(*) as user_num from user where age <= 30 group by city having user_num > 3;

注意：

• 執(zhí)行順序：where > 聚合函數(shù) > having
• 分組之后，查詢的字段一般為聚合函數(shù)和分組字段，其他字段不能查詢

3.3 排序查詢

語法

select 字段 from 表名 order by 字段1 排序1, 字段2 排序2;

• 支持多字段排序
• 排序方式

• • ASC：升序（默認(rèn)值）
  • DESC：降序

-- 根據(jù)年齡進(jìn)行排序
select * from user order by age;-- 根據(jù)年齡進(jìn)行排序，年齡相同時根據(jù)id倒序排序
select * from user order by age, id desc;

3.4 分頁查詢

使用 limit 關(guān)鍵字

語法

select 字段 from 表名 limit 起始索引, 查詢記錄數(shù)量;

-- 查詢第1頁，每頁顯示5條
select * from user limit 0,5;-- 查詢第2頁，每頁顯示5條
select * from user limit 5,5;

4 其他命令

4.1 查看是否忽略大小寫，1為忽略

show global variables like '%lower_case%';

4.2 查看主從狀態(tài)

查看主服務(wù)狀態(tài)，在主從配置時，查看主服務(wù)信息時使用

show master status;

查看從服務(wù)狀態(tài)，在主從配置時，查看從服務(wù)狀態(tài)時使用

show slave status;

使用示例：

mysql> show slave status \G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 10.0.24.10Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000007Read_Master_Log_Pos: 2050Relay_Log_File: 5d5ab079a223-relay-bin.000004Relay_Log_Pos: 546Relay_Master_Log_File: binlog.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 2050Relay_Log_Space: 1574Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 60a0cb5e-1a2f-11ee-8244-0242ac110003Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

4.3 查看字符集

-- 查看字符集
show variables like '%character%';

4.4 查看連接數(shù)

-- 獲取最大數(shù)據(jù)庫連接數(shù)
show variables like 'max_connections';-- 獲取當(dāng)前數(shù)據(jù)庫連接數(shù)
show status like 'threads_connected';-- 獲取最大數(shù)據(jù)庫連接數(shù)和當(dāng)前數(shù)據(jù)庫連接數(shù)
select @@max_connections as maxconnections, count(*) as currentconnections from information_schema.processlist;

4.5 查看當(dāng)前進(jìn)程

show processlist;# 找出所有執(zhí)行時間超過 5 分鐘的線程，拼湊出 kill 語句，方便后面查殺
select concat('kill ', id, ';') 
from information_schema.processlist 
where Command != 'Sleep' and Time > 300 order by Time desc;

關(guān)注我，我們一起學(xué)習(xí)。

?