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 event
                  Master_Host: 10.0.24.10
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 2050
               Relay_Log_File: 5d5ab079a223-relay-bin.000004
                Relay_Log_Pos: 546
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2050
              Relay_Log_Space: 1574
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_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: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 60a0cb5e-1a2f-11ee-8244-0242ac110003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_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;

关注我，我们一起学习。