mysql-1

开启binglog、慢查询和常规查询日志配置：

general-log=1
general_log-file="changgui.log"
slow-query-log=0
slow_query_log_file="manchaxun.log"
long_query_time=10
log-bin="binlog-bin"

查询错误日志路径

SHOW VARIABLES LIKE 'log_error';
SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'log_error';

查询数据存储位置和日志文件路径

SHOW VARIABLES LIKE 'datadir';
SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'general_log_file';

查询存储引擎

SHOW ENGINES;
SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test20240519' GROUP BY ENGINE;
SHOW TABLE STATUS FROM test20240519 WHERE Name = 't_myisam';

查询建库、建表语句

SHOW CREATE DATABASE test20240519;
SHOW CREATE TABLE test20240519.t_myisam;

查询库表创建、修改和更新时间，需确保建库、建表语句添加了相应字段

-- 建表时添加
create_time timestamp not null default CURRENT_TIMESTAMP COMMENT '创建时间',
update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间');
-- 修改字段
ALTER TABLE test MODIFY create_time timestamp not null default CURRENT_TIMESTAMP COMMENT '创建时间';
ALTER TABLE test MODIFY update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间';
-- 增加字段
ALTER TABLE test ADD create_time timestamp not null default CURRENT_TIMESTAMP COMMENT '创建时间';
ALTER TABLE test ADD update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间';

-- 查询表更新和创建时间
select update_time from test20240519.t_myisam order by update_time desc;
select create_time from test20240519.t_myisam order by create_time desc;
select update_time from test20240519.t_myisam order by update_time desc limit 1;
select create_time from test20240519.t_myisam order by create_time desc limit 1;

show语句

-- 查询服务状态
show status;
SHOW STATUS LIKE 'Connection%';

-- 查询配置变量和值
SHOW VARIABLES;

-- 查询客户端连接信息
SHOW PROCESSLIST;

-- 查询INNODB存储引擎状态信息
SHOW ENGINE INNODB STATUS;

-- 查询主从复制状态
SHOW MASTER STATUS;
SHOW SLAVE STATUS;

-- 查询库和表
SHOW DATABASES;
SHOW TABLES;
SHOW TABLES from test20240519;

-- 查询指定表的列信息，和desc类似
SHOW COLUMNS FROM t_myisam;

-- 查询指定表索引信息
SHOW INDEXES FROM t_myisam;

-- 查询指定用户权限信息
SHOW GRANTS FOR 'root'@'localhost';

-- 查询上一个语句产生的告警和错误信息
SHOW WARNINGS;
SHOW ERRORS;

-- 查询指定库中的事件信息
SHOW EVENTS FROM test20240519;

-- 查询指定库中的触发信息
SHOW TRIGGERS FROM test20240519;

select一些常用查询语句

-- 查询所有列
SELECT * FROM t_myisam;

-- 指定列查询
SELECT create_time, update_time FROM t_myisam;

-- 列设置别名查询
SELECT create_time AS ca_time, update_time AS up_time FROM t_myisam AS a;

-- 条件过滤查询
SELECT * FROM t_myisam WHERE id = 1;

-- 查询结果排序
SELECT * FROM t_myisam ORDER BY create_time ASC, update_time DESC;

-- 查询结果分组
SELECT name, COUNT(*) FROM t_myisam GROUP BY name;

-- 聚合函数，对分组数据进行计算
SELECT name, COUNT(*), SUM(create_time), AVG(create_time), MIN(create_time), MAX(create_time) FROM t_myisam GROUP BY name;

-- 对分组过滤结果
SELECT name, COUNT(*) FROM t_myisam GROUP BY name HAVING COUNT(*) < 10;

-- 限制返回结果
SELECT * FROM t_myisam LIMIT 1;

-- 设置结果起始位置
SELECT * FROM t_myisam LIMIT 1 OFFSET 10;

-- 连接多个表查询
SELECT * FROM world.country AS t1 JOIN world.city AS t2 ON t1.name = t2.name;

-- 嵌套查询,需确保字符集和排序规则一致
SELECT name FROM t_myisam WHERE name IN (SELECT name COLLATE utf8mb4_general_ci  FROM world.city WHERE name like 'Kabul');
SELECT name FROM t_myisam WHERE name COLLATE utf8mb4_general_ci IN (
    SELECT name COLLATE utf8mb4_general_ci FROM world.city WHERE name LIKE 'Kabul'
) LIMIT 0, 1000;

-- 合并查询
SELECT ID, Name FROM world.city WHERE id = 1
UNION
SELECT Code, Continent FROM world.country where code = 'AGO';

创建库表

-- 创建支持中文的库
CREATE DATABASE your_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

-- 创建表
CREATE TABLE your_database.your_table (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键，自动递增',
    name VARCHAR(255) NOT NULL COMMENT '非空的字符串类型列',
    age INT COMMENT '整数类型列',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间，默认值为当前时间戳',
    modifie_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间，默认值为当前时间戳，每次更新记录时自动更新为当前时间戳',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间，默认值为当前时间戳，每次更新记录时自动更新为当前时间戳'
);

Share