常用sql语句_sql 语句 from as-优快云博客

本文链接：https://blog.youkuaiyun.com/wangwe2000/article/details/117508872

本文介绍了几种常用的SQL查询技巧，包括统计各类调查表记录数的方法、如何进行三表连接查询、使用EXCEPT操作符去除重复记录以及如何查询数据库结构信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

常用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;