建设展示类网站的意义,网站建设五站合一,邢台做网站哪里便宜,做译员的网站文章目录 知识点梳理#xff1a;1. mysql 中 in 和 exists 区别2. varchar 与 char 的区别 查看表结构#xff1a;获取当前时间#xff1a;查看建表语句#xff1a;修改用户密码#xff1a;查看所有用户#xff1a;grant命令#xff1a;判断当前数据库有多少连接数… 文章目录 知识点梳理1. mysql 中 in 和 exists 区别2. varchar 与 char 的区别 查看表结构获取当前时间查看建表语句修改用户密码查看所有用户grant命令判断当前数据库有多少连接数分组之前进行排序查询媒体名称为小强日报的数据量内连接去重Cast字符串转数字分割函数1. MySQL8 split2. substring_index group by 1, 2replace intoON DUPLICATE key updateDATEDIFF() 函数返回两个日期之间的时间四舍五入1. 函数ROUND(x)2. 函数ROUND(x,y)3. 函数TRUNCATE(x,y) 简单的增删改beauty_article_view视图创建库和表create、insert 和 select 相结合查询数据库中所有表名或者表的所有字段名索引日期和字符相互转换\g 和 \G开窗函数if函数经纬度转换需要注意的点查询数据库中的存储过程和函数INTERVAL关键字和INTERVAL()函数1. INTERVAL关键字2. INTERVAL() 函数 创建日期维度表从库binlog报错json 知识点梳理
来自mysql delete exists用法_2020年最新版MySQL面试题四
1. mysql 中 in 和 exists 区别 mysql 中的 in 语句是把外表和内表作 hash 连接而 exists 语句是对外表作 loop 循环每次 loop 循环再对内表进行查询。一直大家都认为 exists 比 in 语句的效率要高这种说法其实是不准确的。这个是要区分环境的。
如果查询的两个表大小相当那么用 in 和 exists 差别不大。如果两个表中一个较小一个是大表则子查询表大的用 exists子查询表小的用 in。not in 和 not exists如果查询语句使用了 not in那么内外表都进行全表扫描没有用到索引而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大用 not exists 都比 not in 要快。
2. varchar 与 char 的区别
char 的特点
char 表示定长字符串长度是固定的如果插入数据的长度小于 char 的固定长度时则用空格填充因为长度固定所以存取速度要比 varchar 快很多甚至能快50%但正因为其长度固定所以会占据多余的空间是空间换时间的做法对于 char 来说最多能存放的字符个数为255和编码无关
varchar的特点
varchar 表示可变长字符串长度是可变的插入的数据是多长就按照多长来存储varchar 在存取方面与 char 相反它存取慢因为长度不固定但正因如此不占据多余的空间是时间换空间的做法对于 varchar 来说最多能存放的字符个数为 65532 总之结合性能角度(char更快)和节省磁盘空间角度(varchar更小)具体情况还需具体来设计数据库才是妥当的做法。
查看表结构
desc tabl_name;获取当前时间
SELECT CURRENT_TIMESTAMP;
# 输出结果为2021-06-11 16:14:45SELECT CURRENT_TIME;
# 输出结果为16:14:45SELECT CURRENT_DATE;
# 输出结果为2021-06-11SELECT NOW();
# 输出结果为2021-06-11 16:14:45SELECT CURDATE();
# 输出结果为2021-06-11SELECT CURTIME();
# 输出结果为16:14:45来自mysql如何获取当前时间
查看建表语句
mysql show create table t1;
-------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-------------------------------------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE t1 (a int(11) DEFAULT NULL,b varchar(50) DEFAULT NULL,last_update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 该条信息的更新时间
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 |
-------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)注意当 update 的值和表里现有的值一样时 last_update_time 字段是不更新的。
修改用户密码
-- 创建用户及密码
create user huiq% identified by huiq%123;-- 修改用户密码
mysqlset password for huiq% password(huiq%123456);-- 删除用户
mysqldelete from mysql.user where userhuiq and host%;
-- 删除账户及权限想删除用户的话一般用这种方式
mysqldrop user 用户名%;mysqlflush privileges;注删除mysql的users_MySQL两种删除用户语句的区别(delete user和drop user)
查看所有用户
mysql select user,host from mysql.user;
--------------------------------------
| user | host |
--------------------------------------
| TBPM | % |
| root | 192.168.1.12 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
--------------------------------------
5 rows in set (0.01 sec)grant命令
# 例1增加一个test1用户密码为123456可以在任何主机上登录并对所有数据库有查询增加修改和删除的功能。需要在mysql的root用户下进行
mysqlgrant select,insert,update,delete on *.* to test1% identified by 123456;
mysqlflush privileges;# 例2增加一个test2用户密码为123456只能在192.168.2.12上登录并对数据库student有查询增加修改和删除的功能。需要在mysql的root用户下进行
mysqlgrant select,insert,update,delete on student.* to test2192.168.2.12 identified by 123456;
mysqlflush privileges;# 例3授权用户test3拥有数据库student的所有权限
mysqlgrant all privileges on student.* to test3localhost identified by 123456;
mysqlflush privileges;
# 注如果已经有相应用户identified by可不加其中关键字 “privileges” 可以省略。# 查看当前用户自己权限
show grants;# 查看其他 MySQL 用户权限
show grants for root192.168.10.1;grant all on *.* to root192.168.10.1;
# show grants for root192.168.10.1;显示GRANT ALL PRIVILEGES ON *.* TO root192.168.10.1# 撤销已经赋予给 MySQL 用户权限的权限。revoke 跟 grant 的语法差不多只需要把关键字 “to” 换成 “from” 即可
revoke all on *.* from root192.168.10.1;
# show grants for root192.168.10.1;显示GRANT USAGE ON *.* TO root192.168.10.1
# 注usage 特殊的“无权限”权限# 如果想在mysql.user表中不想存在该账户还得进行“删除账户及权限”
drop user root192.168.10.1;参考mysql 创建用户指定数据库表的读写权限常用命令
判断当前数据库有多少连接数
show full processlist;问题com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: Too many connections
原因数据库超过最大连接数了查询当前最大连接数。后来排查代码根本原因是创建的mysql连接并没有关闭。。。
select VARIABLE_VALUE from performance_schema.global_variables where VARIABLE_NAMEMAX_CONNECTIONS;
----------------
| VARIABLE_VALUE |
----------------
| 800 |
----------------
1 row in set (0.00 sec)解决方案
set global max_connections 3600;
或者要彻底解决问题还是要修改my.cnf配置文件在“[mysqld]”下面添加“max_connections1000”执行service mysql restart 重新启动MySQL服务分组之前进行排序
SELECT * FROM(SELECT * FROM biz_messageboard ORDER BY CREATETIME DESC) a GROUP BY a.USERID;select * from goonie_article_view where id%21 and id 3094578 ORDER BY id DESC limit 50查询媒体名称为小强日报的数据量
SELECT COUNT(*) FROM goonie_article_view WHERE SUBSTRING_INDEX(goonie_article_view.mediaNameZh,-,1) 小强日报;SELECT * FROM beauty_article_view WHERE SUBSTRING_INDEX(beauty_article_view.mediaNameZh,-,-1) 旅游;SELECT * FROM goonie_article_view WHERE author LIKE 张%;SELECT COUNT(*) FROM goonie_article_view WHERE pubdate 2018-05-09 18:00:00;SELECT * FROM beauty_article_view WHERE codename礼仪 AND id BETWEEN 1216 AND 1318;
SELECT * FROM beauty_article_view WHERE codename语录 OR codename礼仪 AND id 24658 ORDER BY id DESC LIMIT 50;内连接
SELECT COUNT(*) FROM goonie_article_view w INNER JOIN gooniewechat_key t ON w.creatort.weixin_name WHERE w.gather_time2019-01-29;去重
SELECT COUNT(DISTINCT(codename)) FROM beauty_article_view;
SELECT DISTINCT(codename) FROM beauty_article_view;Cast字符串转数字 一开始以为这样用就Ok结果报错 后来折腾了半天这样写才好使select cast(12 as SIGNED);
参考MySQL CAST函数
分割函数
1. MySQL8 split
参考mysql8 split函数 在 MySQL8 中新增了一个非常实用的函数 split它可以帮助我们将字符串按照指定的分隔符拆分成多个部分。语法如下
split(str, delimiter, position)str要拆分的字符串。delimiter分隔符。position可选参数表示要返回的部分的索引默认为1。
2. substring_index 但我的 Mysql 版本没有那么高不支持 split 函数只能选择另一种方式了。
select version();
-- 5.7.36-log参考mysql 函数 substring_index 的用法 用法规则
substring_index(“待截取有用部分的字符串”“截取数据依据的字符”截取字符的位置 N)实例
SELECT SUBSTRING_INDEX (15,151,152,16,,,1);得到结果为 15SELECT SUBSTRING_INDEX (15,151,152,16,,,-1);得到结果为 16SELECT SUBSTRING_INDEX (SUBSTRING_INDEX (15,151,152,16,,,2),,,-1);得到结果为 151SELECT SUBSTRING_INDEX (SUBSTRING_INDEX (15,151,152,16,,,-2),,,1);得到结果为 152group by 1, 2
select name,age,gender from person_inf where amout 10000 group by 1,2
# 等于
select name,age,gender from person_inf where amout 10000 group by name,agegroup by 1,2的意思是根据select参数的第1、第2列进行分组
replace into replace into 主要作用类似 insert 插入操作。主要的区别是 replace 会根据主键或者唯一索引检查数据是否存在如果存在就先删除再更新不存在则直接新增记录。三种形式
1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_namevalue, ...mysql 中常用的三种插入数据的语句
insert into 表示插入数据数据库会检查主键如果出现重复会报错replace into 表示插入替换数据需求表中有 PrimaryKey或者 unique 索引如果数据库已经存在数据 则用新数据替换如果没有数据效果则和 insert into 一样insert ignore 表示如果中已经存在相同的记录则忽略当前新数据
来自mysql中replace与replace into的用法 注意1unique 索引一定要设置合理在工作中使用 replace into 就遇到了本来想根据三个字段去插入数据结果 unique 索引只设置了两个字段这就导致本来想插入两个字段值一样另一个字段值不一样的五条数据但不一样的那个字段没设置为 unique 索引这就导致只插入了一条循环后的最后一条数据。 注意2尽量使用下面的 ON DUPLICATE key update工作中发现 replace into 会覆盖不指定字段的值比如我更新字段1和2之前字段3的值为5字段3设置的默认值为0则 replace into 字段1, 字段2 后字段3会变为0下面的 ON DUPLICATE key update 就没有这个问题。
ON DUPLICATE key update 有时候由于业务需求可能需要先去根据某一字段值查询数据库中是否有记录有且值不一样则更新没有则插入。这个时候就可以用到 ON DUPLICATE key update 这个 sql 语句了。
insert into user (username,userpwd,num) values (testName2,testPwd3,10)
ON DUPLICATE KEY UPDATE
username VALUES(username),
userpwd VALUES(userpwd)参考ON DUPLICATE key update的介绍与使用 扩展MYSQL on duplicate key update和replace into 详解
DATEDIFF() 函数返回两个日期之间的时间
SELECT DATEDIFF(2008-12-30,2008-12-28) AS DiffDate;
结果2SELECT TIMESTAMPDIFF(DAY, 2022-01-05, 2022-01-01) AS date_diff;
结果-4# 使用DATEDIFF和CASE语句计算两个日期的差值可以灵活处理日期顺序
SELECT CASEWHEN 2022-01-01 2022-01-05 THEN DATEDIFF(2022-01-01, 2022-01-05)ELSE DATEDIFF(2022-01-05, 2022-01-01)END AS date_diff;# 使用TIMESTAMPDIFF和ABS函数计算两个日期的差值无论日期的先后顺序
SELECT ABS(TIMESTAMPDIFF(DAY, 2022-01-01, 2022-01-05)) AS date_diff;四舍五入
参考MySQL四舍五入
1. 函数ROUND(x) ROUND(x) 函数返回最接近于参数 x 的整数对 x 值进行四舍五入。
SELECT ROUND(-2.34),ROUND(-4.56),ROUND(2.34),ROUND(4.56);
结果-2 -5 2 52. 函数ROUND(x,y) ROUND(x,y) 函数返回最接近于参数 x 的数其值保留到小数点后面 y 位若 y 为负值则将保留 x 值到小数点左边 y 位。
SELECT ROUND(3.45,1),ROUND(3.45,0),ROUND(123.45,-1),ROUND(167.8,-2);
结果3.5 3 120 200实例
update table set c_total_hour round(hour,2) where length(hour) 8;3. 函数TRUNCATE(x,y) TRUNCATE(x,y) 函数返回被舍去至小数点后 y 位的数字 x。若y的值为0则结果不带有小数点或不带有小数部分。若 y 设为负数则截去归零x 小数点左起第 y 位开始后面所有低位的值。
SELECT TRUNCATE(2.34,1),TRUNCATE(4.56,1),TRUNCATE(4.56,0),TRUNCATE(56.78,-1);
结果2.3 4.5 4 50简单的增删改
UPDATE afacebooktoken SET tokenEAACEdEose0 where id1;
DELETE FROM afacebooktoken WHERE id1;
INSERT INTO afacebooktoken VALUES(1,EAAL7AMi5Z);beauty_article_view视图
CREATE ALGORITHMUNDEFINED DEFINERwoman% SQL SECURITY DEFINER VIEW beauty1_article_view AS
selecta.id AS id,a.creator AS author,a.publish_time AS pubdate,a.site_code AS media_level,a.source AS trans_from_m,a.title AS titleZh,a.depth AS depth,a.location AS page_place_src,a.url AS url,a.content_finger AS finger,a.url_hash AS url_hash,d.content AS textZh,d.summary AS abstractZh,d.keywords AS keywordsZh,e.description AS codename,a.detriment AS detriment,#c.name AS mediaNameZh,a.gather_time AS gather_time
from ((gooniearticle ajoin gooniearticledetailed d)join goonienewssort e)
where (a.id d.pid)and (d.infoid e.id)
order by a.id创建库和表
-- 创建库
CREATE DATABASE active_modle DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;-- 删除库
drop database active_modle;-- 创建表
CREATE TABLE biz_reply (id BIGINT(64) NOT NULL AUTO_INCREMENT,userid VARCHAR(255) DEFAULT NULL COMMENT 用户id,shopid VARCHAR(200) DEFAULT COMMENT 商家id,recontent VARCHAR(1000) DEFAULT COMMENT 留言内容,createId VARCHAR(32) DEFAULT NULL COMMENT 创建人,createTime DATETIME DEFAULT NULL COMMENT 创建时间,PRIMARY KEY (id)
) ENGINEINNODB DEFAULT CHARSETutf8;
-- CHARSETutf8mb4能存储Emoji表情-- 增加字段
ALTER table mysql_into_hbase add execute_sql text COMMENT ods层到dwd层的执行语句;
-- 删除字段
alter table mysql_into_hbase drop execute_sql;-- 表名称的修改
alter table mysql_into_hbase rename oracle_into_hbase;-- 删除表
drop table biz_reply;增删字段可参考入门必看mysql数据表添加字段的方法
create、insert 和 select 相结合
create table heheda_tmp as select * from heheda limit 10;
insert into heheda_tmp(message_id,project,file_date,create_time) select message_id,project,file_date,create_time from heheda limit 10;查询数据库中所有表名或者表的所有字段名
-- 查询数据库中所有表名
select table_name from information_schema.tables where table_schemadata_warehouse and table_typebase table;-- 查询指定数据库中指定表的所有字段名column_name
select column_name from information_schema.columns where table_schemadata_warehouse and table_namedw_4029_manage;索引
-- 创建普通索引
CREATE INDEX t_grhx_grbq_index ON t_grhx_grbq(sfzhm);-- 查看索引
show index from t_grhx_grbq;日期和字符相互转换 date_format(date,%Y-%m-%d) --------------oracle中的to_char();str_to_date(date,%Y-%m-%d) --------------oracle中的to_date();%Y代表4位的年份%y代表2为的年份%m代表月, 格式为(01……12)%c代表月, 格式为(1……12)%d代表月份中的天数,格式为(00……31)%e代表月份中的天数, 格式为(0……31)%H代表小时,格式为(00……23)%k代表 小时,格式为(0……23)%h 代表小时,格式为(01……12)%I 代表小时,格式为(01……12)%l 代表小时,格式为(1……12)%i 代表分钟, 格式为(00……59)%r代表 时间,格式为12 小时(hh:mm:ss [AP]M)%T代表 时间,格式为24 小时(hh:mm:ss)%S代表 秒,格式为(00……59)%s代表 秒,格式为(00……59)SELECT DATE_FORMAT(20130111191640,%Y-%m-%d %H:%i:%s)DATE_FORMAT(20130111191640,%Y-%m-%d %H:%i:%s)\g 和 \G \g等价于;如下实例
mysql select * from t3\g
------------------------------------------------------------------
| id | username | password | money | birthday | cztime |
------------------------------------------------------------------
| 1 | 用户1 | 12346 | 500 | 1995-05-20 | 2018-07-30 09:40:30 |
------------------------------------------------------------------
1 rows in set (0.00 sec)mysql select * from t3;
------------------------------------------------------------------
| id | username | password | money | birthday | cztime |
------------------------------------------------------------------
| 1 | 用户1 | 12346 | 500 | 1995-05-20 | 2018-07-30 09:40:30 |
------------------------------------------------------------------
1 rows in set (0.00 sec)\G将查询到的横向表格纵向输出方便阅读
如下实例
mysql show create table t4;
——-————————————————————————————————————————————————————-
| Table | Create Table |
——-————————————————————————————————————————————————————-
| t4 | CREATE TABLE t4 (
id int(11) DEFAULT NULL,
username varchar(20) DEFAULT NULL,
money int(11) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8 |
——-————————————————————————————————————————————————————-
1 row in set (0.00 sec)mysql show create table t4\G
***************** 1. row *****************
Table: t4
Create Table: CREATE TABLE t4 (
id int(11) DEFAULT NULL,
username varchar(20) DEFAULT NULL,
money int(11) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec)ERROR:
No query specified开窗函数
说明开窗函数与聚合函数一样也是对行集组进行聚合计算但是它不像普通聚合函数那样每组只返回一个值开窗函数可以为每组返回多个值因为开窗函数所执行聚合计算的行集组是窗口。 语法 主要是over( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) …. ) 常用函数 参考https://www.bbsmax.com/A/q4zVkPLxJK/ 1、row_number() over(partition by … order by …) 2、rank() over(partition by … order by …) 3、dense_rank() over(partition by … order by …) rank(): 跳跃排序如果有两个第一级时接下来就是第三级。 dense_rank(): 连续排序如果有两个第一级时接下来仍然是第二级。 4、count() over(partition by … order by …) 5、max() over(partition by … order by …) 6、min() over(partition by … order by …) 7、sum() over(partition by … order by …) 8、avg() over(partition by … order by …) 9、first_value() over(partition by … order by …) 10、last_value() over(partition by … order by …) 11、lag() over(partition by … order by …) 12、lead() over(partition by … order by …) lag 和lead 可以 获取结果集中按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联 lag lead 分别是向前向后 lag 和lead 有三个参数第一个参数是列名第二个参数是偏移的offset第三个参数是 超出记录窗口时的默认值 但是mysql 8.0版本以下并不支持只能通过变通的方法实现相同的功能 1实现自增
SELECTnum : num1 score_ranking,id
FROMuser, (SELECT num : 0) t1
ORDER BYid DESC;2实现rank() 参考https://blog.csdn.net/justry_deng/article/details/80597916 38.0版本以上实现rank()rank()、row_number()、dense_rank() 参考https://blog.csdn.net/sqsltr/article/details/94408487
create table students(id int(4) auto_increment primary key,name varchar(50) not null, score int(4) not null);insert into students(name,score) values(curry, 100),(klay, 99),(KD, 100), (green, 90), (James, 99), (AD, 96);select id, name, rank() over(order by score desc) as r from students;
select id, name, DENSE_RANK() OVER(order by score desc) as dense_r from students;
select id, name, row_number() OVER(order by score desc) as row_r from students;if函数
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
说明: 当条件testCondition为TRUE时返回valueTrue否则返回valueFalseOrNullmysql和hive都适用
实例1
select if(hehe小强,1,2) as flag from t_xiaoqiang;实例2
select if(haha is not null,hehe,123) from t_xiaoqiang;经纬度转换
度分秒转经纬度
度分秒120°13’52整数部分120直接读取小数部分13/600.2166666666666666752/36000.014444440.2166660.01440.231111结果120.231111
经纬度转度分秒
度数113.211度113直接读取分0.211*6012.66读取12秒0.66*6039.6结果113°12’39.6
相关sql
SELECT 22FORMAT(35/6034.62/3600,6) JD;
# 结果为22.59295注意在 Doris 中 FORMAT 函数不存在所以只能用 round 函数结果和上面相同不会显示末尾的0但是 mysql 使用 round 函数末尾会显示0。
-- mysql
SELECT 22ROUND(35/6034.62/3600,6) JD;
# 结果为22.592950-- doris
SELECT 22ROUND(35/6034.62/3600,6) JD;
# 结果为22.59295需要注意的点
1.在做多表查询或者查询的时候产生新的表的时候会出现这个错误Every derived table must have its own alias每一个派生出来的表都必须有一个自己的别名同样适用于SqlServer。 2.同级生成的别名字段不可以使用只能嵌套使用这样感觉增加了语句量在Gauss是可以做到的 mysql
SELECTa.hehe heheda,heheda*12
FROMhaha a;-- 报错Unknown column heheda in field listGauss
查询数据库中的存储过程和函数
select name from mysql.proc where db test_schema1 and type PROCEDURE; //存储过程
select name from mysql.proc where db test_schema1 and type FUNCTION; //函数INTERVAL关键字和INTERVAL()函数
来自一文速学-玩转MySQL中INTERVAL关键字和INTERVAL()函数用法讲解
1. INTERVAL关键字 语法
interval [/-]数值 时间单位# 例如
interval 3 dayselect (create_time-INTERVAL 3 day) as time from value_test;
# 等于
select (create_timeINTERVAL -3 day) as time from value_test;时间单位表为
时间单位描述year年quarter季度month月week周day天hour小时minute分钟second秒microsecond微秒second_microsecond秒_微秒minute_microsecond分_微秒minute_second分_秒hour_microsecond时_微秒hour_second时_秒hour_minute时_分day_microsecond天_微秒day_second天_秒day_minute天_分钟day_hour天_小时year_month年_月 如果我们想要在原基础上加上1小时10分钟10秒
select (create_timeINTERVAL 1:10:10 hour_second) as time from value_test;# 这里要注意一下指定的时间单位向下兼容性当我们选择的是hour_second小时到秒时输入的数值仅为两个数值会自动识别为10分10秒例如
select (create_timeINTERVAL 10:10 hour_second) as time from value_test;
# 其他的连续时间段表示单位也都一样识别成了10小时10分10秒例如
select (create_timeINTERVAL 10:10:10 day_second) as time from value_test;interval 一般使用场景都在场景筛选中使用比如 where 的条件判断或者是筛选、去一段指定的时间片段。如取近七日的时间段的数据
and time between cast(date_sub(date_format(curdate(),%Y%m%d)
, interval 7 day) as DECIMAL)
and cast(date_sub(date_format(curdate(),%Y%m%d) , interval 1 day)
as DECIMAL)# 或者file_date为date格式如2023-12-08
where bb.file_date between DATE_SUB(aa.max_file_date, INTERVAL 7 DAY) and aa.max_file_date2. INTERVAL() 函数 在 MySQL 输入 interval() 函数就会显示相应的语法格式 INTERVAL() 函数为分区函数其中N是要判断的数值N1,N2,N3,... 是分段的间隔。 这个函数的返回值是段的位置
如果NN1则返回0如果N1NN2则返回1如果N2NN3则返回2。区间是左闭右开的。 我们想要把 time 列的时间归类为 20220601-20220610 为一个区域20220611-20220620 为一个区域20220621-20220630 为一个区域每隔10天为一个分区。
select INTERVAL(time,20220611,20220621,20220631) as time from value_test分完区之后一般进行统计就可以得到对应区间的个数了
select INTERVAL(time,20220611,20220621,20220631) as times ,count(*) as count
from value_test
GROUP BY times创建日期维度表
参考hive数仓里建立日期维表、MySQL 如何生成日期表
先创建表
CREATE TABLE dim_date (date_id bigint(8) DEFAULT NULL,date date DEFAULT NULL,date_cn varchar(20) DEFAULT NULL,WEEK_CN varchar(20) DEFAULT NULL,WEEK_OF_YEAR_CN varchar(20) DEFAULT NULL,MONTH varchar(20) DEFAULT NULL,MONTH_CN varchar(20) DEFAULT NULL,QUARTER varchar(20) DEFAULT NULL,QUARTER_CN varchar(20) DEFAULT NULL,YEAR_CN varchar(20) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;创建存储过程并执行
delimiter $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
create procedure p_dim_date(in start_date VARCHAR(20),in date_count int)
begindeclare i int;set i0;DELETE from dim_date;while idate_count doINSERT into dim_date(date_id,date,date_cn,WEEK_CN,WEEK_OF_YEAR_CN,MONTH,MONTH_CN,QUARTER,QUARTER_CN,YEAR_CN)SELECT REPLACE(start_date,-,) date_id,DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y-%m-%d) date,DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y年%m月%d日) date_cn,case dayofweek(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s)) when 1 then 星期日 when 2 then 星期一 when 3 then 星期二 when 4 then 星期三 when 5 then 星期四 when 6 then 星期五 when 7 then 星期六 end WEEK_CN,DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y年第%u周) WEEK_OF_YEAR_CN,DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y-%m) MONTH,DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y年第%m月) MONTH_CN,CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y),Q,quarter(STR_TO_DATE( start_date,%Y-%m-%d %H:%i:%s))) QUARTER,CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y),年第,quarter(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s)),季度) QUARTER_CN,DATE_FORMAT(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),%Y年) YEAR_CNfrom dual;set ii1;set start_date DATE_FORMAT(date_add(STR_TO_DATE(start_date,%Y-%m-%d %H:%i:%s),interval 1 day),%Y-%m-%d);end while;
end$$call p_dim_date(2018-01-01,365)注执行存储过程的时候得加delimiter $$参考MySQL 存储过程否则报错
从库binlog报错
问题我在程序中先truncate t2再insert into t2 select * from t1主库的一张表后报错Duplicate entry 1 for key PRIMARY, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the events master log mysql-bin.000059, end_log_pos 183416832 解决把程序里的truncate table换成了delete from table就可以了。相应的表结构t1和t2表结构一样
CREATE TABLE t2 (id bigint(18) NOT NULL,ch_name varchar(64) NOT NULL,remark varchar(255) DEFAULT NULL COMMENT 备注,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;json
参考https://blog.csdn.net/lianghecai52171314/article/details/121742377 MySQL 里的 json 分为 json array 和 json object。
分类函数描述创建jsonjson_array创建json数组json_object创建json对象json quote将json转成json字符串类型查询jsonjson_contains判断是否包含某个json值json_contains_path判断某个路径下是否包含json值json_extract提取json值column-pathjson_extract的简洁写法Mysql 5.7.9开始支持column-pathjson_unquote(column - path)的简洁写法json_keys提取json中的键值为json数组json_search按给定字符串关键字搜索json返回匹配的路径
示例
SELECT json_extract({id:1,nickName:小强},$.nickName);
# 返回结果小强SELECT json_object(name,xiaoyu,old,12,height,165.1);
# 返回结果{old: 12, name: xiaoyu, height: 165.1}创建测试表和数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS 0;-- ----------------------------
-- Table structure for tb_emp
-- ----------------------------
DROP TABLE IF EXISTS tb_emp;
CREATE TABLE tb_emp (id int(0) NOT NULL AUTO_INCREMENT COMMENT 员工编号,ename varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 员工姓名,info json NULL COMMENT 员工信息,PRIMARY KEY (id) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Dynamic;-- ----------------------------
-- Records of tb_emp
-- ----------------------------
INSERT INTO tb_emp VALUES (1, SMITH, {\job\: \CLERK\, \mgr\: 7902, \sal\: 800.0, \comm\: null, \empno\: 7369, \ename\: \SMITH\, \deptno\: 20, \hiredate\: 345830400000});
INSERT INTO tb_emp VALUES (2, ALLEN, {\job\: \SALESMAN\, \mgr\: 7698, \sal\: 1600.0, \comm\: 300.0, \empno\: 7499, \ename\: \ALLEN\, \deptno\: 30, \hiredate\: 351446400000});
INSERT INTO tb_emp VALUES (3, WARD, {\job\: \SALESMAN\, \mgr\: 7698, \sal\: 1250.0, \comm\: 500.0, \empno\: 7521, \ename\: \WARD\, \deptno\: 30, \hiredate\: 351619200000});
INSERT INTO tb_emp VALUES (4, JONES, {\job\: \MANAGER\, \mgr\: 7839, \sal\: 2975.0, \comm\: null, \empno\: 7566, \ename\: \JONES\, \deptno\: 20, \hiredate\: 354988800000});
INSERT INTO tb_emp VALUES (5, MARTIN, {\job\: \SALESMAN\, \mgr\: 7698, \sal\: 1250.0, \comm\: 1400.0, \empno\: 7654, \ename\: \MARTIN\, \deptno\: 30, \hiredate\: 370454400000});
INSERT INTO tb_emp VALUES (6, BLAKE, {\job\: \MANAGER\, \mgr\: 7839, \sal\: 2850.0, \comm\: null, \empno\: 7698, \ename\: \BLAKE\, \deptno\: 30, \hiredate\: 357494400000});
INSERT INTO tb_emp VALUES (7, CLARK, {\job\: \MANAGER\, \mgr\: 7839, \sal\: 2450.0, \comm\: null, \empno\: 7782, \ename\: \CLARK\, \deptno\: 10, \hiredate\: 360864000000});
INSERT INTO tb_emp VALUES (8, SCOTT, {\job\: \ANALYST\, \mgr\: 7566, \sal\: 3000.0, \comm\: null, \empno\: 7788, \ename\: \SCOTT\, \deptno\: 20, \hiredate\: 545756400000});
INSERT INTO tb_emp VALUES (9, KING, {\job\: \PRESIDENT\, \mgr\: null, \sal\: 5000.0, \comm\: null, \empno\: 7839, \ename\: \KING\, \deptno\: 10, \hiredate\: 374774400000});
INSERT INTO tb_emp VALUES (10, TURNER, {\job\: \SALESMAN\, \mgr\: 7698, \sal\: 1500.0, \comm\: 0.0, \empno\: 7844, \ename\: \TURNER\, \deptno\: 30, \hiredate\: 368726400000});
INSERT INTO tb_emp VALUES (11, ADAMS, {\job\: \CLERK\, \mgr\: 7788, \sal\: 1100.0, \comm\: null, \empno\: 7876, \ename\: \ADAMS\, \deptno\: 20, \hiredate\: 548694000000});
INSERT INTO tb_emp VALUES (12, JAMES, {\job\: \CLERK\, \mgr\: 7698, \sal\: 950.0, \comm\: null, \empno\: 7900, \ename\: \JAMES\, \deptno\: 30, \hiredate\: 376156800000});
INSERT INTO tb_emp VALUES (13, FORD, {\job\: \ANALYST\, \mgr\: 7566, \sal\: 3000.0, \comm\: null, \empno\: 7902, \ename\: \FORD\, \deptno\: 20, \hiredate\: 376156800000});
INSERT INTO tb_emp VALUES (14, MILLER, {\job\: \CLERK\, \mgr\: 7782, \sal\: 1300.0, \comm\: null, \empno\: 7934, \ename\: \MILLER\, \deptno\: 10, \hiredate\: 380563200000});SET FOREIGN_KEY_CHECKS 1;# 查看所有员工的编号、名称、工作、薪水
SELECT id,ename,info-$.job,info-$.sal from tb_emp;
SELECT id,ename,JSON_EXTRACT(info,$.job),JSON_EXTRACT(info,$.sal) from tb_emp;
SELECT id,ename,JSON_EXTRACT(info,$.job,$.sal) from tb_emp;# 查询id值为7788的员工的名称是否为SCOTT
SELECT JSON_CONTAINS(info,{ename:SCOTT})
from tb_emp
WHERE id 8# 查询指定字段中是否包含ename和dno中的任何一个
SELECT JSON_CONTAINS_PATH(info, one,$.ename,$.dno)
from tb_emp
WHERE id 8# 查询指定字段中是否同时包含ename和dno
SELECT JSON_CONTAINS_PATH(info, all,$.ename,$.dno)
from tb_emp
WHERE id 8# 查询部门编号为10的员工的详细信息
select * from tb_emp where info-$.deptno10;# 查询部门编号为10且mgr为null的员工的详细信息
select * from tb_emp where info-$.deptno10 and info-$.enameCLARK;# 查询部门编号为10且id为7的员工的详细信息
select * from tb_emp where info-$.deptno10 and id 7;# 查询薪水大于3000的员工的信息
select * from tb_emp where JSON_EXTRACT(info, $.sal)3000;# 查询薪水等于5000的员工的信息
select * from tb_emp where JSON_CONTAINS(info, JSON_OBJECT(sal,5000));# 查询JSON字段中所有的键
select JSON_KEYS(info) FROM tb_emp;注在 Mysql 8.0Mysql 5.7.22 增加了两个聚合函数json_arrayagg() 和 json_objectagg()
