常用Mysql或者PostGresql或者Greenplum的语句总结。
常用Mysql或者PostGresql或者Greenplum的语句总结。
别先生
发布于 2018-11-09 17:07:31
发布于 2018-11-09 17:07:31
1、使用mysql的union all可以同时查询出所有自己想要查询数据表的数据量。
1 select 'user' as tablename, count(*) from user
2 union all select 'teacher' as tablename, count(*) from teacher
3 union all select 'person' as tablename, count(*) from person
4 union all select 'student' as tablename, count(*) from student
5 order by tablename2、使用mysql的union all可以同时查询出所有自己想要查询数据表的数据量。添加上限制条件进行查询。
1 select 'user' as tablename, count(*) from user where update_time>'2018-10-09'
2 union all select 'teacher' as tablename, count(*) from teacher where update_time>'2018-10-09'
3 union all select 'person' as tablename, count(*) from person where update_time>'2018-10-09'
4 union all select 'student' as tablename, count(*) from student where update_time>'2018-10-09'
5 order by tablename 3、使用Postgresql或者Greenplum的union all可以同时查询出所有自己想要查询数据表的数据量。添加上限制条件进行查询。
1 select 'user' as tablename, count(*) from user where update_time>to_date('2018-10-09 01', 'yyyy-mm-dd hh24')
2 union all select 'teacher' as tablename, count(*) from teacher where update_time>to_date('2018-10-09 01', 'yyyy-mm-dd hh24')
3 union all select 'person' as tablename, count(*) from person where update_time>to_date('2018-10-09 01', 'yyyy-mm-dd hh24')
4 union all select 'student' as tablename, count(*) from student where update_time>to_date('2018-10-09 01', 'yyyy-mm-dd hh24')
5 order by tablename 4、在Mysql数据库中，如果某个字段是换行的，如何去掉换行的字段，然后正常查询出来。 注意：char(10)换行键、char(13)回车键。 4.1、查询出多个数据表某条记录可能含有换行符的记录。 CONCAT()函数用于将多个字符串连接成一个字符串。
1 select * from user where name like CONCAT("%",char(13),"%")
2 union all select * from teacher where name like CONCAT("%",char(13),"%")
3 union all select * from person where name like CONCAT("%",char(13),"%")
4 union all select * from student where name like CONCAT("%",char(13),"%");4.1、然后将换行和回车进行替换，将换行和回车换成''。这样做就将回车和换行替换完成。 replace(object,search,replace)，把object中出现search的全部替换为replace。
1 select REPLACE(REPLACE(name, char(10), ''), char(13), '') as name from user where name like CONCAT("%",char(13),"%")
2 union all select REPLACE(REPLACE(name, char(10), ''), char(13), '') as name from student where name like CONCAT("%",char(13),"%")
3 union all select REPLACE(REPLACE(name, char(10), ''), char(13), '') as name from person where name like CONCAT("%",char(13),"%")
4 union all select REPLACE(REPLACE(name, char(10), ''), char(13), '') as name from student where name like CONCAT("%",char(13),"%");4.3、可以将回车符和换行符转换为特殊的字符。 -- 将char(10)换行键，char(13)回车键换成@#r;和@#n;
1 select REPLACE(REPLACE(name, char(10), '@#r;'), char(13), '@#n;') as name from user where name like CONCAT("%",char(13),"%")如果需要有需要，可以将特殊的字符再转换为回车符和换行符。 -- 将@#r;和@#换成nchar(10)换行键，char(13)回车键;
1 select REPLACE(REPLACE(name, '@#r;', char(10)), '@#n;', char(13)) as name from user where name like CONCAT("%",char(13),"%")待续......
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2018-10-23 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读