常用sql

1.查看数据库中有多少表

select count(*)  from user_tables;

2.查看数据库所有表的名字

select table_name from user_tables;

3.查看数据库表的创建时间和最后一次的修改时间

select object_name,created,last_ddl_time from user_objects;

4.查看导出的dmp文件的路径

select * from dba_directories;

5.oracle查看表中小于当前日期的上一个工作日

select max(d_date) from 表名 where  type=1 and to_date(d_date,'yyyyMMdd')<date'2020-11-25'

6.hive中执行大于时间点的数据

select * from where add_time>'2020-12-31 09:00:00'

7.查询oracle数据库一张表有哪些字段有索引

SELECT 

b.uniqueness, a.index_name, a.table_name, a.column_name 

FROM all_ind_columns a, all_indexes b

WHERE a.index_name=b.index_name 

AND a.table_name = upper('表名')

ORDER BY a.table_name, a.index_name, a.column_position

命令行登录oracle 

sqlplus as sysdba

 

 

mysql 

列转行

    变为  

 

 

 sql语句

SELECT a.name,
    substring_index( substring_index(a.value,    ',',    b.help_topic_id + 1),',' ,- 1) AS shareholder
FROM
    test_counter a
JOIN mysql.help_topic b ON b.help_topic_id < (
    length(a.value) - length(REPLACE (a.value, ',', '')) + 1)

行转列

变为

 

 sql语句

SELECT name,GROUP_CONCAT(shareholder) as value from yyf_test GROUP BY name

扩展

变为

 

 

 sql语句

SELECT name,

 max(CASE subject WHEN'语文' THEN score ELSE 0 END) 语文,

 max(CASE subject WHEN'数学' THEN score ELSE 0 END) 数学,

 max(CASE subject WHEN'外语' THEN score ELSE 0 END) 外语

FROM test

GROUP BY name