Mysql高级查询整理

INFORMATION_SCHEMA

INFORMATION_SCHEMA 是 MySQL 数据库系统中的一个特殊数据库，它包含了关于数据库对象的元数据信息。通过查询 INFORMATION_SCHEMA，可以获取有关数据库、表、列、索引、外键、用户权限等方面的信息。

以下是 INFORMATION_SCHEMA 中包含的一些常见信息：

1. SCHEMATA：
   SCHEMATA 表包含了数据库实例中所有可用的数据库（schema）的信息，如数据库名称、字符集、默认排序规则等。

2. TABLES：
   TABLES 表包含了数据库中的所有表的信息，如表名、所属数据库、创建时间、表类型（临时表、视图等）等。

3. COLUMNS：
   COLUMNS 表包含了数据库中所有表的列信息，如列名、所属表名、所属数据库、数据类型、是否为主键等。

4. STATISTICS：
   STATISTICS 表包含了数据库中表和索引的统计信息，如索引名、索引类型、索引的列名和基数（distinct key count）等。

5. ROUTINES：
   ROUTINES 表包含了数据库中的存储过程和函数的信息，如名称、定义的数据库、创建时间、参数信息等。

6. USER_PRIVILEGES：
   USER_PRIVILEGES 表包含了数据库用户及其权限的信息，如用户名称、权限级别、权限类型等。

以上只是 INFORMATION_SCHEMA 中的一部分表，还有其他诸如 KEY_COLUMN_USAGE、REFERENTIAL_CONSTRAINTS、VIEWS、TRIGGERS 等表，用于存储关于键、外键、视图和触发器的信息等。

通过查询 INFORMATION_SCHEMA，可以了解 MySQL 数据库的结构、元数据信息和权限等方面的内容，这些信息对于数据库管理和查询优化等工作非常有用。

希望以上信息对你有所帮助，如果还有其他问题，请随时提问。

根据表名查找数据库名

SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'index_info';

查询数据库中所有的表：

SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database';

查询特定表的列信息：

SELECT column_name, data_type, column_type, is_nullable FROM information_schema.columns WHERE table_schema = 'your_database' AND table_name = 'your_table';
 

 查询特定表的索引信息：

SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'your_database' AND table_name = 'your_table';
 

查询所有存储过程和函数：

SELECT routine_name, routine_type FROM information_schema.routines WHERE routine_schema = 'your_database';

查询数据库用户及其权限：

SELECT grantee, privilege_type FROM information_schema.user_privileges WHERE table_schema = 'your_database';
 

 查询视图信息：

SELECT table_name, view_definition FROM information_schema.views WHERE table_schema = 'your_database';
 

在上述查询语句中，需要替换 'your_database' 为你实际的数据库名，'your_table' 为表名或对象名等。

可以根据具体需求，使用不同的查询条件和需要的字段来获取 INFORMATION_SCHEMA 中的信息。通过查询 INFORMATION_SCHEMA，你可以获得数据库、表、列、索引、存储过程、用户权限等方面的详细信息。

其他的表

除了 INFORMATION_SCHEMA，MySQL 还有一些其他特殊的表，用于存储和管理特定的信息。以下是一些常见的特殊表：

1. mysql.user：
   mysql.user 表存储了 MySQL 数据库的用户信息，包括用户名、密码、权限等。

2. mysql.db：
   mysql.db 表存储了数据库级别的权限信息，包括用户对各个数据库的权限授予情况。

3. mysql.tables_priv：
   mysql.tables_priv 表存储了表级别的权限信息，包括用户对各个表的权限授予情况。

4. mysql.columns_priv：
   mysql.columns_priv 表存储了列级别的权限信息，包括用户对各个列的权限授予情况。

这些特殊的表位于 MySQL 的系统数据库中，这些数据库名为 mysql。这些表用于存储和管理用户、权限和安全相关的信息。

需要注意的是，对于这些特殊的表，一般来说，不推荐直接向这些表进行插入、更新和删除等操作。建议使用 MySQL 提供的 GRANT 和 REVOKE 命令来管理用户和权限，以确保数据的完整性和一致性。

MySQL中查询锁表的SQL

MySQL 是一个开源关系型数据库管理系统，十分常用。在 MySQL 中，查询锁表是一种常见的操作，用来查看哪些表被锁住，以及锁住这些表的 SQL 语句是什么。下面介绍一下查询锁表的 SQL 语句。

SHOW OPEN TABLES WHERE In_use >0;

这个 SQL 语句是用来查询当前正在被占用（即被锁住）的表的信息。其中，“In_use” 表示正在使用的连接数。如果大于 0，就说明该表被锁住了。

SHOW OPEN TABLES WHERE In_use >0 AND Name LIKE '%表名%';

这个 SQL 语句是用来查询指定表是否被锁住的。其中，“Name” 表示表的名称，用 % 表示通配符。只有符合条件的表才会被查询。

SHOW FULL PROCESSLIST;

这个 SQL 语句是查看当前正在执行的 SQL 语句的详细信息，其中包括锁信息。如果需要查询某个特定的连接，可以使用以下 SQL 语句：

SELECT * FROM information_schema.processlist WHERE id = 连接ID;

以上就是 MySQL 查询锁表的 SQL 语句。通过这些语句，可以方便地查看哪些表被锁住，进而处理卡顿、防止死锁等问题。

综合去开

select b.id,a.*,a.trx_state,b.db,b.* from information_schema.innodb_trx a left join information_schema.processlist b on a.trx_mysql_thread_id=b.id  where db='scct' order by trx_started;

其中

`information_schema.innodb_trx` 表是 MySQL 中的一个信息模式表，它提供了关于当前正在运行的 InnoDB 事务的信息。该表包含了以下列：

- `trx_id`：事务的唯一标识符。
- `trx_state`：事务的状态，如运行中、等待锁定等。
- `trx_started`：事务开始的时间。
- `trx_requested_lock_id`：事务所请求的锁的标识符。
- `trx_wait_started`：等待锁定开始的时间。
- `trx_weight`：事务的权重，用于决定事务在等待锁定时的优先级。
- `trx_mysql_thread_id`：事务所属的 MySQL 线程 ID。

`information_schema.processlist` 表也是一个信息模式表，它提供了当前正在运行的连接和查询的信息。该表包含了以下列：

- `id`：连接的唯一标识符。
- `db`：连接所使用的数据库。
- `command`：连接当前执行的命令类型，如查询、锁定等。
- `time`：连接已经执行的时间。
- `state`：连接的当前状态。
- `info`：连接正在执行的查询语句信息。

综合来说，`information_schema.innodb_trx` 表提供了有关 InnoDB 事务的信息，而 `information_schema.processlist` 表提供了当前正在运行的连接和查询的信息。在查询中，通过将这两个表联接起来，您可以获得关于指定数据库的事务信息以及相关连接和查询的信息。

其他

如果用查询出来的字段做筛选的话，可以包一层，

如下面的 canDeliver,havDeliver,allDeliver,delayAllDeliver,timelyAllDeliver,

select mainId,canDeliver,havDeliver,allDeliver,delayAllDeliver,timelyAllDeliver,

qty,

fTotalInQty,

fTotalRecieveQty, 

planShipDate, 

 purchaseArriveVoucherDate, 

planArriveDate , 

voucher_Date,

requestOrg, 

purchaseOrg, 

productId,

productClassId, 

supplierId, 

purchaserId, 

departmentId, 

`status`,

 closeStatus,

year,

season,

month,

week,

day,

rowclose

from (SELECT

mainId,

CASE 

    WHEN `status` = 1 and `close` = 0 and `rowClose` = 0 THEN

        1

    ELSE

        0

END  as canDeliver,

CASE 

    WHEN `status` = 1 and `close` = 0 and `rowClose` = 0 

    and ((`fTotalRecieveQty` > 0 ) or (fTotalInQty > 0 )) THEN

        1

    ELSE

        0

END as havDeliver,

CASE  

    WHEN `status` = 1 and `close` = 0 and `rowClose` = 0 

    and ((`fTotalRecieveQty` >= qty) or (fTotalInQty >= qty )) THEN

        1

    ELSE

        0

END as allDeliver,

CASE  

    WHEN `status` = 1 and `close` = 0 and `rowClose` = 0 

    and ((`fTotalRecieveQty` >= qty ) or (fTotalInQty >= qty))

    and min_xiayou_date >planArriveDate

    THEN

        1

    ELSE

        0

END as delayAllDeliver,

CASE  

    WHEN   `status` = 1 and `close` = 0 and `rowClose` = 0 

    and ((`fTotalRecieveQty` >= qty) or (fTotalInQty >= qty))

    and max_xiayou_date <=planArriveDate 

    THEN

        1

    ELSE

        0

END as timelyAllDeliver,

qty,-- 主数量

fTotalInQty, -- 累计入库数量

fTotalRecieveQty, -- 累计到货数量

planShipDate,  --  计划发货日期

 max_xiayou_date as 'purchaseArriveVoucherDate', --  最大采购到货单据日期

planArriveDate , --  计划到货日期

voucher_Date, -- 单据时间

requestOrg, -- 需求组织

purchaseOrg, -- 采购组织

productId,-- 物料

productClassId, -- 物料分类

supplierId, -- 供应商

purchaserId, -- 采购员

departmentId,  -- 部门id

`status` as `status`,

CASE 

    WHEN `close` = 0 and `rowClose` = 0 THEN

        1

    ELSE

        0

END as closeStatus,

rowClose,

--  时间维度

year,

season,

month,

week,

day

FROM

yonbip_scm_dw.performance

 where

 ytenant_id = '${var('租户id')}' and 

status = 1 and close = 0 and rowclose = 0  and businessCode='st_purchaseorder_lyl'

<#if param( '单据起始时间' ) !=''>

  and voucher_Date >= '${param('单据起始时间')}'

  

  <#if param('单据结束时间')!=''>

    and voucher_Date <= '${param('单据结束时间')}'

<#if param('供应商id')!=''>

    and supplierId  in  (${param('供应商id')})

<#if param('采购部门id')!=''>

    and departmentId  in  (${param('采购部门id')})

<#if param('采购员id')!=''>

    and purchaserId  in  (${param('采购员id')})

<#if param('物料id')!=''>

    and productId in (${param('物料id')})

<#if param('物料分类id')!=''>

    and productClassId in (${param('物料分类id')})

<#if param('采购组织id')!=''>

    and purchaseOrg  in  (${param('采购组织id')})

<#if param('需求组织id')!=''>

    and requestOrg  in  (${param('需求组织id')})

<#if (param('canDeliverStatus')!='' &&  param('canDeliverStatus') = '0')>

  and mainId is null

) base

where 

1=1

<#if param('havDeliverStatus')!='' >

   <#if  param('havDeliverStatus') = '1'>

    and havDeliver = 1

   

    <#if  param('havDeliverStatus') = '0'>

    and havDeliver = 0

   

<#if param('allDeliverStatus')!='' >

   <#if  param('allDeliverStatus') = '1'>

    and allDeliver = 1

   

    <#if  param('allDeliverStatus') = '0'>

    and allDeliver = 0

   

<#if param('delayAllDeliverStatus')!='' >

   <#if  param('delayAllDeliverStatus') = '1'>

    and delayAllDeliver = 1

   

    <#if  param('delayAllDeliverStatus') = '0'>

    and delayAllDeliver = 0

   

<#if param('timelyAllDeliverStatus')!='' >

   <#if  param('timelyAllDeliverStatus') = '1'>

    and timelyAllDeliver = 1

   

    <#if  param('timelyAllDeliverStatus') = '0'>

    and timelyAllDeliver = 0