mysql基础

交集：公共部分
并集：相加部分

select * from where order by limit ;
#设置密码
set password for username @localhost = password(newpwd);
#更新密码
update user set authentication_string=password(‘12345678’) where user=‘root’;
#刷新
flush privileges;
#设置远程可访问
GRANT ALL ON . TO root@‘%’ IDENTIFIED BY ‘12345678’ WITH GRANT OPTION;

常用命令

服务器命令链接mysql:mysql -h ip -u用户 -p密码
连接数据库
mysql -h 192.168.100.1 -uroot -p password
查看库
show databases;
选择库
use database_name;
查看ddl表结构
show create table table_names;
设置编码
set names ‘utf8’;
查看表
show tables;
查看表结构
describe table_name;
查看进程列表
show processlist;
杀进程
kill -9 id;
查看事务情况
select * from information_schema.innodb_trx\G
删除表
delete fron table_name;
截断表（清空表一般不要用DELETE)
truncate table table_name;
查看所有表数据量
select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = ‘database_name’ order by table_rows desc;
导出数据
mysqldump -h 172.31.60.215 -uroot -p database_name > charts_pre.sql
写入数据
mysql -h 172.31.60.215 -uroot -p database_name < charts_pre.sql

权限

创建用户名和密码
CREATE USER ‘hue’@‘%’ IDENTIFIED BY ‘password#’;
创建库
CREATE DATABASE hue DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
授权对应库表给对应用户ip和密码
grant all privileges on hue.* to hue@‘%’ identified by “password” ;
GRANT ALL ON hue.* TO ‘hue’@‘%’ IDENTIFIED BY password;
刷新生效
FLUSH PRIVILEGES;
创建表：
CREATE TABLE dim_common_type (
id int(11) NOT NULL AUTO_INCREMENT,
type_level_name varchar(255) DEFAULT NULL COMMENT ‘维度类型名称’,
type_name varchar(255) DEFAULT NULL COMMENT ‘维度名称’,
type_code varchar(128) DEFAULT NULL COMMENT ‘维度编码’,
dt varchar(64) DEFAULT NULL COMMENT ‘分区’,
data_version varchar(255) DEFAULT NULL COMMENT ‘版本号’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘名族维度码值表’;
create database db1 default character set utf8mb4;

增删改查

插入数据：
truncate table table_name
INSERT INTO table_name(type_level_code,type_level_name,type_code,type_name,data_version) VALUES (‘SX000084SBWHCD’,‘省标文化程度’,‘4’,‘高中’,‘202203’);
删除数据：delete from table_name where target_name=‘B’;
更新数据
UPDATE table_name set idcard = REPLACE(idcard,‘\t’,‘’) where idcard like ‘%\t%’;
查询数据(表名,表注释,表容量)
select
table_schema as ‘数据库’,
table_name as ‘表名’,
TABLE_COMMENT as ‘表注释’,
table_rows as ‘记录数’,
truncate(data_length/1024/1024, 2) as ‘数据容量(MB)’,
truncate(index_length/1024/1024, 2) as ‘索引容量(MB)’,
CREATE_TIME as ‘创建时间’,
UPDATE_TIME as ‘数据最后同步时间’
from information_schema.tables WHERE TABLE_SCHEMA =‘database_name’
order by table_name desc;

常用函数

#替换函数
REPLACE(idcard,‘\t’,‘’)
#长度函数
length()
#json 中array获取字段内容
JSON_EXTRACT(json_data, ‘KaTeX parse error: Expected 'EOF', got '#' at position 14: [0].idcard') #̲表示获取array中第一个js…[1].*’) # 表示获取array中第二个json
最小和最大：least()和greatest()
if判断函数：if(certLevel4=0,5,certLevel4)
类型转换：CONVERT(‘1’ ,unsigned int) cast(string as unsigned INT)
json数据解析：JSON_EXTRACT(JSON_EXTRACT(json_data , ‘$.data{s}^{\prime }){,}^{\prime }$[0].columns’)
向上取整：ceil（1.2） = 2
向下取整：floor（1.2） = 1
四舍五入：round（）
取绝对值：abs()
返回第一个非空值：coalesce(tb_b.idcard,tb_a.idcard2),
截取：TRUNCATE((POW(1.012,50))*10000,4)
性别赛选：CAST(SUBSTR(idcard,17,1) as unsigned INT)%2 =0：1
取余获取取商：商DIV;余数 %或者MOD
DATE_FORMAT(now(),‘%Y%m%d’)

ifNULL(ostap.e_qty,0)
计算月份：TIMESTAMPDIFF(MONTH,a.end_time,a.start_time)

计算年龄：case when LENGTH(card_id) = 15 then TIMESTAMPDIFF(YEAR,CONCAT(‘19’,MID(card_id,7,6)),CURRENT_DATE)
when LENGTH(card_id) = 18 then TIMESTAMPDIFF(YEAR,MID(card_id,7,8),CURRENT_DATE) end age
计算性别：IF(CAST(SUBSTR(card_id,17,1) as unsigned INT)%2=0,‘女’,‘男’) as gender
计算日期天数：DATEDIFF(due_date,now())

拼接json串：concat_ws(‘’,‘{“actual”:"’,tb_ap.act_qty, ‘“,“houses”:”’,1, ‘“,“vacant”:”’,tb_ap.kz_qty,’ “,“areaName”:”‘,ahb.building_name,’“,“grid”:”‘,ahb.area_code,’“,“community”:”‘,sa2.full_name,’ “,“household”:”‘,tb_ap.hj_qty,’“,“microGrid”:”‘,ahb.user_name,’“,“microGridTelephone”:”‘,ahb.phone,’"}')

排名字段：@rank1:=@rank1+1 as shipaiming
SELECT
sid, name, age,
@curRank := @curRank + 1 AS rank
FROM student s,
(SELECT @curRank := 0) r
ORDER BY age
例如：select warehouse_name,qty,@rank1:=@rank1+1 as shipaiming from (select warehouse_name,count(*) as qty,(SELECT @rank1 := 0) r from table_name group by warehouse_name)tb_all order by qty desc
行转列合并：
select std_tab_desc as ‘字典名称’,GROUP_CONCAT(std_cde_cd,‘-’,std_cde_desc) as ‘字典值’ from table_name where std_tab_desc is not null GROUP BY std_tab_desc ;

存储过程和定时器

查看定时器是否开启
show VARIABLES like ‘event_scheduler’
开启mysql定时器
SET GLOBAL event_scheduler = 1;

一：开启定时器
SET GLOBAL event_scheduler = 1;
二：创建存储过程
create procedure crontab_table_nam()
begin
truncate table table_nameo;
insert into table_nameo(id,id_card,real_name,create_time,sync_time)
select id,id_card,real_name,create_time,now() from ods_test_tb_info;
end;
三：定时器创建
参考如下
create event test_event --test_event定时任务的名称
on schedule every 10 second --每10秒执行一次
on completion preserve disable --创建后并不立即生效
do call test_proce(); --调用存储过程（存储过程的名称）
create event event_table_name
on schedule every 60*60 second
on completion preserve disable
do call crontab_table_nameo();
四：开启事件
alter event event_name on completion preserve enable;

alter event user_event on completion preserve enable; – 开启定时任务
alter event user_event on completion preserve disable; – 关闭定时任务
五:查看所有定时器调度任务
select * from information_schema.EVENTS;

定时器语法结构
DROP EVENT IF EXISTS 定时器名称 ;
CREATE EVENT user_event
ON SCHEDULE xx – 定时器从什么开始执行的 (必须)
ENDS CURRENT_TIMESTAMP xx – 定时器开始后在什么时候结束 (非必选)
ON COMPLETION xx – 定时器清除策略 (默认就行)
DO sql语句 – 定时器执行的内容,只允许一条sql语句以分号结束 (必须)
每隔一天执行一次
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON SCHEDULE EVERY 9 DAY STARTS NOW()
DO call user_procedure();
每天凌晨一点执行一次
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 1 hour)
DO call user_procedure();
每个月一号凌晨1点执行
DROP EVENT IF EXISTS 定时器名称 ;
CREATE EVENT user_event
ON schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour)
DO call user_procedure();
每天凌晨一点执行一次 2022-08-27 1:00:00
on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 1 hour)
每天凌晨0点15分钟执行一次 2022-08-27 00:15:00

常见问题

查询报错：The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
参考：set sql_big_selects=on;