- content
一、基本信息及意义
MySQL 常用语句积累
1. 表信息查询
1.1 查询表名称信息 SQL 语句
SELECT
table_name,
TABLE_COMMENT
FROM
information_schema. TABLES
WHERE
table_schema = 'xxx'
AND LENGTH(TABLE_COMMENT) > 0;
-- and table_type='base table';
1.2 统计表的数据量
SELECT
TABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'XXXX';
1.3 查询数据库表列及列字段备注信息
SELECT
t.TABLE_NAME,
t.TABLE_COMMENT,
c.COLUMN_NAME,
c.COLUMN_TYPE,
c.COLUMN_COMMENT
FROM
information_schema. TABLES t,
INFORMATION_SCHEMA. COLUMNS c
WHERE
c.TABLE_NAME = t.TABLE_NAME
AND t.`TABLE_SCHEMA` = 'xxx'
1.4 数据库表操作
-- <1. 修改表名
alter table oldTableName rename newTableName;
rename table oldTableName to newTableName;
-- <2. 添加表列
alter table tableName add column columnName varchar(20);
-- <3. 删除表列
alter table tableName drop column columnName;
-- <4. 修改表列类型
alter table tableName modify columnName varchar(20)
alter table tableName change address address varchar(20)
-- <5. 修改表列名(**)
alter table tableName change column oldColumnName newColumnName varchar(20)
-- <5.1 同时修改多列
alter table
open_api_req_config
MODIFY
column `param_name` varchar(1000) DEFAULT NULL COMMENT '参数名',
MODIFY
column `bind_field` varchar(1000) DEFAULT NULL COMMENT '绑定字段';
-- <6. 添加索引
create index j_wfirst_date_index on ads_wlh_project_plan_3m(j_wfirst_date) comment '经营周索引';
-- <7. 删除索引
DROP INDEX <索引名> ON <表名>
1.5 查看进程、杀掉进程
-- 查询进程
SHOW PROCESSLIST;
-- 杀掉进程
kill pid;
-- 查询连接数
show variables like '%max_connection%';
show status like 'Threads%';
-- 查询、过滤DB连接信息
-- 查看/开启 mysql定时任务
show variables like '%sche%';
-- 开启
set global event_scheduler=1;
1.6 权限
-- <1. 授权
-- 添加存储过程执行权限
grant execute on PROCEDURE ipCreateTempRiskTable to 'crud'@'%';
-- 添加创建临时表权限
grant create temporary tables on xx_iron_isc.* to 'crud'@'%';
1.7 优化
-- 了解数据库基本信息
-- Connections 试图连接 Mysql 服务器的次数
-- Uptime 服务器工作时间
-- Slow_queries 慢查询的次数
-- 查询执行效率
explain select xxx;
-- 查询索引使用情况
show status like 'Handler_read%';
2. 函数
2.1 系统函数
-- <1.四舍五入
select ROUND(1.015,2) -- 保留两位小数 1.02
-- 类型转换convert 、cast
select convert(cnt,signed) as cnt from t_info;
-- 4 - 5 范围内的数据
-- 若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。
-- <2.非空 IFNULL(expr1,expr2)函数
select IFNULL(fieldName,0) from tableName;
-- <3. 时间函数 NOW()、DATE_FORMART(date,formart) ...
select DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%s")
-- 日期操作（查询凌晨）CAST(CAST(SYSDATE()AS DATE)AS DATETIME)
-- 加一天 day:为单位 week、hour、minute、second ...
-- select date_sub(CAST(CAST(SYSDATE()AS DATE)AS DATETIME),interval -1 day)
select date_add(now(),interval 1 day);
-- 减一天
-- select date_sub(CAST(CAST(SYSDATE()AS DATE)AS DATETIME),interval 1 day)
select date_add(now(),interval -1 day);
select date_sub(now(),interval 1 day);
-- 日期与时间戳相互转换
select UNIX_TIMESTAMP('2021-07-22 12:25:00');
select FROM_UNIXTIME(1597536937); -- 秒级时间戳
-- <4. 一列多行数据转为一列一行数据 1,2,3
select GROUP_CONCAT(fieldName) from tableName;
-- 行转列 module_flag： 列名 ，bank_union_no 行内容 2021.05.20
select
date,
max( case module_flag when 'bank_union_no' then module_call_num else 0 end) as bank_union_no,
max( case module_flag when 'city' then module_call_num else 0 end) as city,
max( case module_flag when 'company_basic' then module_call_num else 0 end) as company_basic
from
module_invoke_date_statistics_info
group by date
-- <5. 查询是否包含敏感(包含关系) instr 返回值： 第一出现位置
select (select instr( address,sensitive_words) from wlh_sensitive_words where instr( address,sensitive_words) >0 LIMIT 1)>0
-- <6. 校验绿化率 (长度、% 绝对值)
select greening_rate is not null and (instr(greening_rate,'%')=0 or REPLACE(greening_rate,'%','') REGEXP '[^0-9.]' or abs(REPLACE(greening_rate,'%',''))<0 or abs(REPLACE(greening_rate,'%',''))>100)
-- <7. 字符串截取 拆分经纬度
select SUBSTRING_INDEX(lng,',',1)
select SUBSTRING_INDEX(lng,',',-1)
2.2 自定义函数(使用)
-- ----------------------------
-- Function structure for getConversionRate
-- ----------------------------
DROP FUNCTION IF EXISTS `getConversionRate`;
DELIMITER ;;
CREATE FUNCTION `getConversionRate`(`reportCustomers` integer,`dealedCustomers` integer) RETURNS varchar(20) CHARSET utf8
BEGIN
#转化率
IF reportCustomers = 0 || dealedCustomers =0 THEN
RETURN '';
ELSE
RETURN concat(ROUND((dealedCustomers*100)/reportCustomers,1),'%');
END IF;
END
;;
DELIMITER ;
-- Function2 (定义函数时，需要指定变量长度)
delimiter //
CREATE PROCEDURE test ( myid VARCHAR ( 50 ) ) BEGIN
DECLARE
v BIGINT;
SELECT STATUS INTO
v
FROM
book
WHERE
id = myid;
CASE
WHEN v = 0 THEN
UPDATE book
SET `status` = 1
WHERE
id = myid;
WHEN v = 1 THEN
UPDATE book
SET `status` = 0
WHERE
id = myid;
ELSE UPDATE book
SET `status` = - 1
WHERE
id = myid;
END CASE;
END;
// delimiter;
-- 自定义存储过程，游标使用
2.3 MySQL 与 Oracle 函数差异
注意 MySQL 版本号问题 select showversion() ，比如 5.7.3 版本有些不支持临时表 With as、isnull 函数等
| MySQL | Oracle | 说明 |
|---|---|---|
| isnull | nvl | 是否为空 |
| 请用 IF 或 CASE 语句代替. IF 语句格式:(expr1,expr2,expr3) |
DECODE(iv_sr_status, g_sr_status_com, ld_sys_date, NULL) |
IF ELSE 判断 |
3. 数据备份
3.1 通过查询语句备份
create table newTableName select * from oldTableName; -- 主键、唯一键等不会备份
3.2 插入数据
-- 复制表结构（包括索引等）
create table newTableName like oldTableName ;
insert into newTableName(field1,field2) select field1,field2 from otherTabelName; -- insert into select
3.3 插入或更新 INSERT INTO table ON DUPLICATE KEY UPDATE
-- 2020.05.14
INSERT INTO dc_sale_test ( upload_id, create_time, update_time, create_user, update_user, org_name, statistics_date, customer_visit, sign_amount )
VALUES
( 60, '2020-05-14 16:33:56.284', '2020-05-14 16:33:56.284', 'XXX1', 'XXX1', '重庆', '2020-05-14', '20', '50' ),
( 60, '2020-05-14 16:33:56.284', '2020-05-14 16:33:56.284', 'XXX1', 'XXX1', '重庆', '2020-05-14', '21', '51' )
ON DUPLICATE KEY UPDATE
customer_visit = VALUES( customer_visit ),
sign_amount =VALUES( sign_amount ),
upload_id =VALUES( upload_id ),
update_time =VALUES( update_time ),
update_user =VALUES( update_user )
# 分析： 如果存在主键或唯一键则根据其新增或更新；如果主键或唯一键都不存在，则插入
3.4 时间差(最大、最小时间)
- 一天执行一次，计算最大、最小时间
select * from
(
SELECT
h.JobName as jobName,
end as jobLayer,
min(j.JobTime) as startTime,
max(j.JobTime) as endTime,
TIMESTAMPDIFF(MINUTE,min(j.JobTime),max(j.JobTime)) as duration
FROM
jobstatus_his h
LEFT JOIN jobloghis j ON h.JobName = j.JobName and h.DataDate = j.DataDate
WHERE
h.DataDate='2020-06-17'
group by h.JobName order by JobName asc
)as t where duration >=10
- 一天执行多次，计算最新一次的最大、最小时间
select * from
(
SELECT
h.JobName as jobName,
end as jobLayer,
max( case when j.JobStatus='Pending' OR j.JobStatus='Ready' then j.JobTime else 0 end) AS startTime,
max( case when j.JobStatus='Done' OR j.JobStatus='Failed' then j.JobTime else 0 end) AS endTime,
TIMESTAMPDIFF( MINUTE, max( case when j.JobStatus='Pending' OR j.JobStatus='Ready' then j.JobTime else 0 end),
max( case when j.JobStatus='Done' OR j.JobStatus='Failed' then j.JobTime else 0 end)) AS duration
FROM
jobstatus_his h
LEFT JOIN jobloghis j ON h.JobName = j.JobName and h.DataDate = j.DataDate
WHERE
h.DataDate='2020-06-17'
group by h.JobName order by JobName asc
)as t where duration >=10
- 查询近 7 天的数据
-- 查询近7天的所有数据
SELECT * FROM table_name where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(time_field);
[2020-12-22]统计汇总（字段拆分、汇总） case when then else end
select DISTINCT r1.date,r1.obj_id,r1.device_type,dimension,xj_rate,deviceNumKs,deviceNumKsZy,deviceNumKsFc from
(
select
t1.`data_date` as date,
t1.city_id as obj_id,
-- t1.point_property,
case when point_type = 0 then 1 when point_type = 1 then 2 end as device_type,
1 as dimension,
(sum(t1.actual_order_duration)/min(t1.city_point_use_duration)) as xj_rate,
sum(t1.point_Number) as deviceNumKs,
sum(case when t1.point_property = 0 then t1.point_number else 0 end) as deviceNumKsZy,
sum(case when t1.point_property = 1 then t1.point_number else 0 end) as deviceNumKsFc
from ads_point_pro_point_number_snt t1
where LENGTH(t1.city_id) >0
group by t1.data_date,t1.city_id,t1.point_type
) as r1
-- 分析： 经营周：上周六 ~ 本周五
-- (1) 如果包含 point_property, 该字段会出现数据扩散： 分组中不包含该字段
-- (2) MySQL中如果是字符串类型，(sum(t1.actual_order_duration)/min(t1.city_point_use_duration)) 会出现异常！！（将字符串解析成数字）
-- 知识小点：
mysql 命令行下 \G ，数据格式化
参考：
索引设计原则、索引失效场景、Limit 、Order By、Group By 等常见场景优化
"努力就有进步，坚持就能成功！"