全量查询数据库表名字段名字段类型等杂SQL 完了整理

最新推荐文章于 2024-03-28 14:05:35 发布

xiaomao329

最新推荐文章于 2024-03-28 14:05:35 发布

阅读量547

点赞数 2

分类专栏：收集工具文章标签： sql 数据库整体

本文链接：https://blog.csdn.net/xiaomao329/article/details/97011598

版权

收集工具专栏收录该内容

5 篇文章 0 订阅

订阅专栏

select * from information_schema.columns where table_name='user_award_experience';

select column_name,DATA_TYPE,COLUMN_TYPE,IS_NULLABLE,COLUMN_COMMENT from information_schema.columns where COLUMN_TYPE='VARCHAR' and

(SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA='krplus2' and DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH >=8);

SELECT TABLE_SCHEMA,CONCAT(COLUMN_NAME,'.',DATA_TYPE),TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA='krplus2' and DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH >=8 ORDER BY TABLE_NAME;

SELECT TABLE_NAME AS c from information_schema.columns WHERE TABLE_SCHEMA='krplus2' and DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH >=8 GROUP BY TABLE_NAME;

SELECT a.* from (SELECT TABLE_NAME from information_schema.columns WHERE TABLE_SCHEMA='krplus2' and DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH >=8) a ;

查询某库某些表有某某字段

SELECT DISTINCT
t.table_name,
c.COLUMN_NAME
FROM
information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON c.TABLE_NAME = t.TABLE_NAME
where t.TABLE_TYPE = 'base table'
and c.COLUMN_NAME = 'uid'
and t.TABLE_SCHEMA = 'krplus2'
ORDER BY t.TABLE_TYPE

数据库位运算

select uid from user_type where (((industry_investor_enterpriser >> 9) & 0x01 = 1)
or ((industry_investor_enterpriser >> 27) & 0x01 = 1)) and uid = '618168445';

SELECT u.id,u.phone,ut.industry_investor_enterpriser FROM user_type ut
INNER JOIN `user` u ON u.id=ut.uid WHERE ut.auth_enterpriser =1 AND u.phone <>''
AND (ut.industry_investor_enterpriser&134217728=134217728 OR ut.industry_investor_enterpriser&67108864=67108864)

时间：
SELECT DISTINCT(mark.uid) from market_daily_life mark INNER JOIN (select uid from user_type where uid not in(1) and ( ( ( industry_investor_enterpriser >> 7 ) & 0x01 = 1 ) OR ( ( industry_investor_enterpriser >> 8 ) & 0x01 = 1 ) OR ( ( industry_investor_enterpriser >> 11 ) & 0x01 = 1 ) ) ) users on mark.uid=users.uid and mark.create_time BETWEEN (SELECT DATE_ADD(now(),INTERVAL -6 MONTH)) and now();

快如去掉 and ustype.uid >11111 order by ustype.uid asc limit 5 下面的快这是为何

select ustype.uid from user_type ustype where ustype.uid not in(1) and ( ( ( ustype.industry_investor_enterpriser >> 7 ) & 0x01 = 1 ) OR ( ( ustype.industry_investor_enterpriser >> 8 ) & 0x01 = 1 ) OR ( ( ustype.industry_investor_enterpriser >> 11 ) & 0x01 = 1 ) ) and EXISTS (SELECT DISTINCT
(uid) from market_daily_life mark WHERE mark.uid=ustype.uid) and ustype.uid >11111 order by ustype.uid asc limit 5;

慢

SELECT DISTINCT(mark.uid) from market_daily_life mark INNER JOIN (select uid from user_type where uid not in(1) and ( ( ( industry_investor_enterpriser >> 7 ) & 0x01 = 1 ) OR ( ( industry_investor_enterpriser >> 8 ) & 0x01 = 1 ) OR ( ( industry_investor_enterpriser >> 11 ) & 0x01 = 1 ) ) ) users on mark.uid=users.uid and mark.uid >11111 order by mark.uid asc limit 5;

查询数据中字段最长值

SELECT
   `title`,
   length(`title`)
FROM
   technology_achievement_tranfrom
WHERE
   length(`title`) = (
       SELECT
           max(length(`title`))
       FROM
           technology_achievement_tranfrom
   )