2-MySQL的基本操作记录

1 数据库相关

-- --------------------表相关的----------
-- 查看字符集
show variables like '%character%';show databases;# 创建数据库
create database test2;# 删除数据库
drop database test2;
show databases;#查看当前使用的数据库
select database();

2 用户相关

-- --------------------用户授权相关的----------
#切换数据库
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版本为新版本，需要先创建用户再分配权限，不能和旧版本一样
# MySQL5.7版本可以使用
grant all on test.* to 'user1'@'%' identified by 'user12345';
FLUSH PRIVILEGES;# mysql8.0版本上需要使用：
# 1、创建新用户
create user 'user1'@'%' identified by 'user12345';# 2、刷新权限
flush privileges;# 3、赋予权限，%标识不限制主机
grant all on test.* to 'user1'@'%';# 4、刷新权限
flush privileges;# 5、查看用户
select host,user,plugin,authentication_string from mysql.user;# 收回权限
revoke insert on test.* from 'user1'@'%';# 删除用户
drop user 'user1'@'%';# 查看权限，all包含的权限有：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 数据表相关

-- --------------------表相关的----------
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;-- 查看表结构
desc business_logs;select *
from business_logs;-- 插入数据到表中
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;-- 删除表中的数据
delete from business_logs where id = 1;-- 查询语句
/*
查询关键字：
select字段列表
from表名
where查询条件
group by分组字段
having分组后条件字段
order by排序字段
limit分页参数
*/

3.1 聚合函数

聚合函数（count、sum、max、min、avg）使用

-- 删除表
drop table if exists user;-- 创建表
create table user (id   int primary key auto_increment,name varchar(20) not null,age  int         not null,city varchar(20) default null
);-- 插入数据
insert into user (name, age, city) values ('张三', 20, '北京'), ('李四', 22, '上海'), ('王五', 20, '北京');select * from user;-- 查询各个城市的人数
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值不参与聚合函数的运算

3.2 分组查询

基本语法

select 字段 from 表名 [where 条件] group by 分组字段 [having 分组后过滤条件]

where和having的区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件的数据不参与分组，而having是分组之后对结果进行过滤
• 判断条件不同：where不能对聚合函数进行判断，而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的人数
select city, count(*) as user_num from user where age <= 30 group by city;-- 查询年龄小于等于30的人员，按照城市进行分组，获取人员数量大于3的城市
select city, count(*) as user_num from user where age <= 30 group by city having user_num > 3;

注意：

• 执行顺序：where > 聚合函数 > having
• 分组之后，查询的字段一般为聚合函数和分组字段，其他字段不能查询

3.3 排序查询

语法

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

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

• • ASC：升序（默认值）
  • DESC：降序

-- 根据年龄进行排序
select * from user order by age;-- 根据年龄进行排序，年龄相同时根据id倒序排序
select * from user order by age, id desc;

3.4 分页查询

使用 limit 关键字

语法

select 字段 from 表名 limit 起始索引, 查询记录数量;

-- 查询第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 查看主从状态

查看主服务状态，在主从配置时，查看主服务信息时使用

show master status;

查看从服务状态，在主从配置时，查看从服务状态时使用

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 查看连接数

-- 获取最大数据库连接数
show variables like 'max_connections';-- 获取当前数据库连接数
show status like 'threads_connected';-- 获取最大数据库连接数和当前数据库连接数
select @@max_connections as maxconnections, count(*) as currentconnections from information_schema.processlist;

4.5 查看当前进程

show processlist;# 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
select concat('kill ', id, ';') 
from information_schema.processlist 
where Command != 'Sleep' and Time > 300 order by Time desc;

关注我，我们一起学习。