常用sql语句

1、统计各类调查表记录数

select schema_name(t.schema_id) as [Schema], t.name as TableName,i.rows as [RowCount] 
from sys.tables as t, sysindexes as i 
where t.object_id = i.id and i.indid <=1 ORDER BY i.rows DESC

2、三表连接查询

SELECT prj.PRJID,prj.XMMC,user.user_id,user.username
FROM project prj LEFT JOIN prj_user_relation relation
ON prj.GUID = relation.GUID_P
LEFT JOIN sys_user as user
ON relation.user_id = user.user_id

3、EXCEPT

SELECT dataIndex.wildId from
DATAINDEX dataIndex
WHERE dataIndex.projectid ='DD20201125' and dataIndex.type = '井孔调查表'
EXCEPT
SELECT dataIndex.wildId from
[井孔调查表] JK, DATAINDEX dataIndex
WHERE JK.GUID = dataIndex.GUID and dataIndex.projectid ='DD20201125'  

4、数据库结构

1） 查询数据表信息

//查询表信息
SELECT *
FROM information_schema.`TABLES` WHERE table_schema='xiamendbase' and table_name='dataindex';

2） 查询数据表字段信息

//查询表字段信息
SELECT column_name,column_comment,data_type,column_type,NUMERIC_PRECISION,NUMERIC_SCALE,is_nullable,column_default
FROM information_schema.Columns WHERE table_schema='数据库名称' and table_name='表名';

SELECT *
FROM information_schema.Columns WHERE table_schema='数据库名称' and table_name='表名';

show columns from dataindex;