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>
<#if param('单据结束时间')!=''>
and voucher_Date <= '${param('单据结束时间')}'
</#if>
<#if param('供应商id')!=''>
and supplierId in (${param('供应商id')})
</#if>
<#if param('采购部门id')!=''>
and departmentId in (${param('采购部门id')})
</#if>
<#if param('采购员id')!=''>
and purchaserId in (${param('采购员id')})
</#if>
<#if param('物料id')!=''>
and productId in (${param('物料id')})
</#if>
<#if param('物料分类id')!=''>
and productClassId in (${param('物料分类id')})
</#if>
<#if param('采购组织id')!=''>
and purchaseOrg in (${param('采购组织id')})
</#if>
<#if param('需求组织id')!=''>
and requestOrg in (${param('需求组织id')})
</#if>
<#if (param('canDeliverStatus')!='' && param('canDeliverStatus') = '0')>
and mainId is null
</#if>) base
where
1=1
<#if param('havDeliverStatus')!='' >
<#if param('havDeliverStatus') = '1'>
and havDeliver = 1
</#if>
<#if param('havDeliverStatus') = '0'>
and havDeliver = 0
</#if>
</#if>
<#if param('allDeliverStatus')!='' >
<#if param('allDeliverStatus') = '1'>
and allDeliver = 1
</#if>
<#if param('allDeliverStatus') = '0'>
and allDeliver = 0
</#if>
</#if>
<#if param('delayAllDeliverStatus')!='' >
<#if param('delayAllDeliverStatus') = '1'>
and delayAllDeliver = 1
</#if>
<#if param('delayAllDeliverStatus') = '0'>
and delayAllDeliver = 0
</#if>
</#if>
<#if param('timelyAllDeliverStatus')!='' >
<#if param('timelyAllDeliverStatus') = '1'>
and timelyAllDeliver = 1
</#if>
<#if param('timelyAllDeliverStatus') = '0'>
and timelyAllDeliver = 0
</#if>
</#if>