MYSQL查询表的主键

SELECT

t.TABLE_NAME,

t.CONSTRAINT_TYPE,

c.COLUMN_NAME,

c.ORDINAL_POSITION

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c

WHERE

t.TABLE_NAME = c.TABLE_NAME

AND t.TABLE_SCHEMA = 'test'

AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

测试执行结果如下:

mysql>SELECT

-> t.TABLE_NAME,

-> t.CONSTRAINT_TYPE,

-> c.COLUMN_NAME,

-> c.ORDINAL_POSITION

->FROM

-> INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,

-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c

->WHERE

-> t.TABLE_NAME = c.TABLE_NAME

-> AND t.TABLE_SCHEMA = 'test'

-> AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

->LIMIT 3

+------------+-----------------+-------------+------------------+

| TABLE_NAME | CONSTRAINT_TYPE | COLUMN_NAME | ORDINAL_POSITION |

+------------+-----------------+-------------+------------------+

| mr_dept| PRIMARY KEY | dept_id |1 |

| order | PRIMARY KEY | id |1 |

| tab| PRIMARY KEY | id |1 |

+------------+-----------------+-------------+------------------+

3 rows in set (0.06 sec)

第一种 exists与not exists

select * from table1 t1 where exists(select columnName from table2 t2 where t1.columnName2 = t2.columnName2)

第二种，in，not in的用法

select * from table1 where columnName in(select columnName from table2 where columnNane = 'condition')

第三种,any，all与比较运算的用法

-- 与任意一个比较返回真

select * from table1 where columnName >any(select columnName from table2 where columnNane = 'condition')

-- 与所有的值比较返回真

select * from table1 where columnName >all(select columnName from table2 where columnNane = 'condition')

select a.name, max(b.log_time) from 表1 as a, 表2 as b where a.id = b.user_id group by a.name 根据您最新的需求，SQL修改如下，保证查询效率没的说： select a.id, a.name, max(b.log_time), ( select c.content from 表2 as c where c.us..

---