数据库常用查询

记录一些工作中常用到的查询，方便以后查找，后续慢慢更新。

Oracle

     1.获取建表语句
        select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
        TABLE_NAME为表名必须大写
     2.获取表中数据插入的时间
        select to_char(scn_to_timestamp(ORA_ROWSCN),'yyyy-mm-dd hh24:mi:ss:ff8') insert_time  from 表名;
     3.查看相关用户的授权信息
        select * from user_tab_privs;
     4.授权管理
        Oracle有三种类型授权：1.Object(对象) 2.System(系统) 3.Rols(角色)
     权限分类分为两种：1.系统权限(用户对数据库的操作使用权限) 2.实体权限(对于其他用户表、视图的权限)
        系统权限分类：
     DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
     RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
     CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
     对于普通用户：授予connect, resource权限。
     对于DBA管理用户：授予connect，resource, dba权限。
        至于实体权限都比较好理解增删改查就不多说了。
     5.授权语句
        系统权限
           grant connect,resource,dba to user1...;
           我们可以通过以下sql查询所有的系统权限
           select distinct a.privilege from user_sys_privs a order by a.privilege asc；
        实体权限
           grant 权限名称  on schema. 对象名 to 用户名
           我们可以通过以下sql查询所有的实体权限
           select distinct t.privilege from user_tab_privs t;
     6.查询用户默认表空间
       SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='GAOL'
     7.创建表空间
       create tablespace GAOL_SPACE DATAFILE 'D:\softwore\Oracle\oradata\orcl\GAOL_SAPCE.DBF' size 10M autoextend on;
     8.查看临时表空间大小
       select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name;
     9.查看所有表空间
       select tablespace_name from sys.dba_tablespaces;
     10.查看Schema对应的表空间
       select username,default_tablespace from dba_users  where username='GAOL';
     11.查看表空间文件,表空间,表空间大小,已用大小,剩余大小
       select A.FILE_NAME "表空间文件",
            A.TABLESPACE_NAME "表空间",
            A.Bytes / POWER(2, 20) "表空间大小（M）",
            A.Bytes / POWER(2, 20) - B.freeSize "已用表空间大小（M）",
            B.freeSize "空闲表空间大小（M）"
         from dba_data_files A,
   (SELECT TABLESPACE_NAME,
           ROUND(SUM(BYTES) / (1024 * 1024), 2) freeSize
      FROM SYS.DBA_FREE_SPACE
     GROUP BY TABLESPACE_NAME) B
where a.tablespace_name = b.TABLESPACE_NAME;
在dba_data_files中有个字段USER_BYTES，该字段表示的是文件中实际有用的字节数，而表空间中还包含一些元数据信息，所以需要用总的大小-剩余大小
     12.查看表注释，字段注释
         select  *   from user_tab_comments   where Table_Name='scott'  ;--表注释
         select *   from user_col_comments   where Table_Name='scott' ;--字段注释
         select *   from user_tab_columns   where Table_Name='用户表' ;--获取表字段
    13.查看用户下的分区表信息
        select * from user_tab_partitions;
    14.查看表的所在的分区
        select * from dba_tables where table_name='RPT_STORE_SO_DEL';
        select table_name 表名 ,tablespace_name 所使用表空间 from user_tables;

Teradata

     1.获取当前时间
     select current_date/date FROM  XXX;--格式为yyyy/mm/dd
     结果：2020/8/21
     1.获取前一天的时间
     SELECT DATE-1 FROM XXX;
     结果：2020/8/20
     2.两个时间的差值(默认是天)
     select date-cast('2020/08/19' as date) from XXX;
     结果：2
     select (date - date '2020-02-01') month--月份差
     结果：6
     select (date - date '2019-02-01') year--年份差
     结果：1
     3.获取时间的年或月或日
     select EXTRACT(YEAR from date);
     结果： 2020
     select EXTRACT(MONTH from date);
     结果： 8
     select EXTRACT(DAY from date);
     结果：21
     4.关于timestamp
     select CURRENT_TIMESTAMP
     结果：2020/8/21 10:57:47.490000
     select CURRENT_TIMESTAMP(0)
     结果：2020/8/21 10:58:37
     5.在建表的时候如果是VARCHAR类型需要加上CHARACTER SET UNICODE CASESPECIFIC
     CREATE TABLE XXX(
       system_name VARCHAR(100) CHARACTER SET UNICODE CASESPECIFIC)