Page tree
Skip to end of metadata
Go to start of metadata


PG

MySQL

Oracle

查看所有表

\dt


SELECT *  FROM
    pg_catalog.pg_tables
WHERE
    schemaname != 'pg_catalog'
    AND schemaname != 'information_schema';



show tables;


select * from dba_tables;
select * from user_tables;
select * from all_table;

查看表结构

\d t1

\d+ t1



desc t1

desc t1

查看表上的索引

select * from pg_indexes
    where tablename='student';



SELECT * FROM mysql.`innodb_index_stats` a
    WHERE a.`database_name` = '数据库名'
    and a.table_name like '%表名%';


SELECT * FROM dba_indexes
    where table_name = 'TB_NAME';


连接数据库

psql -h host -d database -U user -W

mysql -u root -pxxx

sqlplus user/pwd@tns


切换数据库

\c dbname username ;


use database ;


alter session set container=PDB1 ;


显示可用数据库

\l
psql -l

select * from pg_database;


show databases;


show pdbs ;


查看表大小

select pg_relation_size('t1');
select pg_size_pretty(pg_relation_size('t1')) ;
SELECT pg_size_pretty (pg_total_relation_size ('t1'));


SELECT
    TABLE_NAME AS `Table`,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`
FROM information_schema.TABLES
    WHERE  TABLE_NAME = 't1'


select bytes from user_segments
    where segment_name = 'T1' ;



查看索引大小

SELECT
    pg_size_pretty (pg_indexes_size('ind1'));



select bytes from user_segments
    where segment_name = 'IND1' ;


 

查看所有表空间

select oid , * from pg_tablespace ;  

PG 表空间


select * from dba_tablespaces;

select * from v$tablespace ;

查看表空间大小

SELECT
    pg_size_pretty (pg_tablespace_size ('pg_default'));



select bytes from user_segments
    where tablespace_name = 'TBS1' ;


 

查看库大小

SELECT
    pg_size_pretty (
        pg_database_size ('dvdrental')
    );


SELECT
    table_schema "DB Name",
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;


--Check the database size physical consume on disk.
select sum(bytes)/1024/1024 size_in_mb from dba_data_files;
--Check the PDB Size of database
select con_id, name, open_mode, total_size from v$pdbs;
--Check the CDB Size of database
select sum(size)/1024/1024/1024 from cdb_data_files;


切换日志

select pg_switch_wal() ;


flush logs;

alter system switch logfile ;


查看数据库参数

show work_mem;
SELECT
    name,setting
FROM
    pg_settings | pg_file_settings
WHERE
    name LIKE 'work%';


Show variables like ‘%%’;


Show parameter xxx ;


查看数据目录

SHOW data_directory;


show variables like ‘%data%’


select * from dba_data_files;


日期转换及计算

date_format(date,'%Y-%m-%d')
str_to_date(date,'%Y-%m-%d')

  %Y:代表4位的年份
  %y:代表2为的年份
  %m:代表月, 格式为(01……12)
  %c:代表月, 格式为(1……12)
  %d:代表月份中的天数,格式为(00……31)
  %e:代表月份中的天数, 格式为(0……31)
  %H:代表小时,格式为(00……23)
  %k:代表 小时,格式为(0……23)
  %h:代表小时,格式为(01……12)
  %I:代表小时,格式为(01……12)
  %l:代表小时,格式为(1……12)
  %i:代表分钟, 格式为(00……59)
  %r:代表 时间,格式为12 小时(hh:mm:ss [AP]M)
  %T:代表 时间,格式为24 小时(hh:mm:ss)
  %S:代表 秒,格式为(00……59)
  %s:代表 秒,格式为(00……59)


to_char() to_date()


根据查询创建表

create table as select * from xxx ;


  • No labels