SQL

展开顺序号标签值

SQL

1 示例

1.1 查看数据库元数据

1.1.1 用户表名

SELECT TABLENAME FROM SYS.SYSTABLES WHERE TABLETYPE='T'

1.1.2 用户表信息

SELECT * FROM SYS.SYSTABLES WHERE TABLETYPE='T'

1.1.3 系统表名

SELECT TABLENAME FROM SYS.SYSTABLES WHERE TABLETYPE='S'

1.1.4 系统表信息

SELECT * FROM SYS.SYSTABLES WHERE TABLETYPE='S'

1.1.5 视图名

SELECT TABLENAME FROM SYS.SYSTABLES WHERE TABLETYPE='V'

1.1.6 视图信息

SELECT * FROM SYS.SYSTABLES WHERE TABLETYPE='V'

1.1.7 表的列名和类型

SELECT columnname, columndatatype FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS c where t.TABLEID=c.REFERENCEID AND tablename='TREE' order by columnnumber

1.1.8 表的列信息

SELECT c.* FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS c where t.TABLEID=c.REFERENCEID AND tablename='TREE' order by columnnumber

1.1.9 表的索引名

SELECT CONGLOMERATENAME FROM SYS.SYSTABLES t, SYS.SYSCONGLOMERATES c where t.TABLEID=c.TABLEID AND tablename='TREE'

1.1.10 表的索引信息

SELECT c.* FROM SYS.SYSTABLES t, SYS.SYSCONGLOMERATES c where t.TABLEID=c.TABLEID AND tablename='TREE'

1.2 查询数据库表

1.2.1 行范围

SELECT * FROM visit_history OFFSET 2 ROWS FETCH NEXT 50 ROWS ONLY

1.2.2 最多行数

SELECT * FROM visit_history FETCH FIRST 300 ROWS ONLY

1.2.3 首行

SELECT * FROM visit_history FETCH FIRST ROW ONLY

1.2.4 查询条件

SELECT * FROM visit_history WHERE file_type > 1 OR data_more like '%fxml%'

1.2.5 计数

SELECT count(file_type) as count FROM visit_history WHERE file_type > 0 AND last_visit_time BETWEEN '2022-03-11 00:00:00.000' AND '2022-03-18 13:09:41.000'

1.2.6 众数

SELECT file_type, count(file_type) AS mode FROM visit_history GROUP BY file_type ORDER BY mode DESC FETCH FIRST ROW ONLY

* 网页可编辑时，链接和控件可能不响应。