常用数据库语句

常用数据库语句

统计分析:analyze table table_name compute statistics;
ORACLE查询当前数据库的默认日期格式:SELECT SYSDATE FROM dual;
ORACLE修改当前数据库的默认日期格式(使用dba权限的用户):
ALTER SYSTEM SET nls_date_format='YYYY-MM-DD hh24:mi:ss' scope=spfile;
SHUTDOWN IMMEDIATE;
startup;
SELECT SYSDATE FROM dual;
SHOW PARAMETERS NLS;
查询当前用户下,有哪些表:SELECT * FROM user_tables; 
查询当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]:SELECT * FROM all_tables; 
查询当前数据库所有的表， 需要你有 DBA 的权限:SELECT * FROM dba_tables; 
查询当前用户下的所有对象:SELECT * FROM tab;
创建用户和密码:CREATE USER user_name IDENTIFIED BY pass_word;
授予连接,资源和dba权限:GRANT CONNECT,RESOURCE,DBA TO user_name;
用户需要查询数据库中所有数据：GRANT SELECT ANY TABLE TO user_name;
该用户需要读取数据字典、使用OEM工具等：GRANT SELECT ANY DICTIONARY TO huyingzhao;
给表或存储过程赋权限:(with grant option权限会被回收,with admin option会永久保留)
GRANT CREATE TABLESPACE TO a;
GRANT SELECT ON tabelname TO a;
GRANT UPDATE ON tablename TO a;
GRANT EXECUTE ON procedurename TO a;
授权存储过程:grant update on tablename to A with grant option;
给所有用户向表 tablename 插入记录的权限：GRANT ALL ON tablename TO PUBLIC;
赋予权限SQL语句（给普通用户user赋所有权限）：GRANT ALL TO user_name;
如果只要赋予部分权限，则：GRANT CREATE SESSION, SELECT ANY TABLE, DBA TO user_name;
清空数据:TRUNCATE TABLE table_name;
查看表:select * from user_tables;
查看视图:SELECT view_name FROM user_views;
查询包体:
SELECT s.name, s.text,s.line
  FROM all_source s
 WHERE TYPE = 'PACKAGE BODY'
   AND owner = 'user_name'
 GROUP BY s.name, s.text,s.line;
删除用户:DROP USER user_name CASCADE;
创建用户表空间:
create tablespace ods datafile'D:\app\huyingzhao\oradata\huyingzhao\ods.dbf' size 100m autoextend on next 100m maxsize 1024m extent
management local autoallocate;
无需用户修改sys密码:
sqlplus/nolog;
CONNECT SYS AS SYSDBA ALTER USER SYS IDENTIFIED BY password;
查询某字段是否有重复的数据:
SELECT s.comment, COUNT(*)
  FROM table_name s
 GROUP BY s.comment
HAVING COUNT(*) > 1;
查询指定数据库的表创建语句(但是不能超过100条, rownum <= 100条件不能去除):
SELECT dbms_metadata.get_ddl('TABLE', table_name, USER)
  FROM user_tables s
 WHERE s.tablespace_name = 'tablespace_name'
   AND rownum <= 100
最高效率分页查询:
SELECT * FROM  
(  SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
WHERE ROWNUM <= end ) 
WHERE RN >= star
查看密码到期时间:SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'
去除密码到期:ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED 
鹏华基金获得当年实际天数:SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') days FROM DUAL
创建新用户需要的语句:
CREATE TABLESPACE YSSUCO DATAFILE 'D:\app\huyingzhao\oradata\huyingzhao\YSSUCO01.dbf' SIZE 1024M 
AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER TABLESPACE YSSUCO ADD DATAFILE 'D:\app\huyingzhao\oradata\huyingzhao\YSSUCO02.dbf' SIZE 1024M;
ALTER DATABASE DATAFILE 'D:\app\huyingzhao\oradata\huyingzhao\YSSUCO02.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
CREATE USER phfund IDENTIFIED BY 1 DEFAULT TABLESPACE YSSUCO  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO phfund;
GRANT RESOURCE TO phfund;
GRANT DBA TO phfund;
GRANT UNLIMITED TABLESPACE TO phfund;
GRANT create any table TO phfund;
--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
--4、查看控制文件
SELECT NAME FROM v$controlfile;
--5、查看日志文件
SELECT MEMBER FROM v$logfile;
--6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--8、查看数据库的版本　
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
--9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;

SQL2:
代码如下:
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
--查看主键外键,索引等
SELECT t.table_name  AS 表名,
       t.column_name AS 表字段,
       co.comments   AS 表备注,
       t.data_type   AS 字段类型,
       t.data_length AS 字段长度,
       c.comments    AS 字段备注
  FROM user_tab_columns t
  JOIN user_col_comments c
    ON t.table_name = c.table_name
  JOIN user_tab_comments co
    ON co.table_name = t.table_name;
SELECT au.table_name AS 表名,
       cu.column_name AS 字段名,
       au.owner AS 用户,
       cu.constraint_name AS 约束名称,
       decode(au.constraint_type,
              'C',
              '普通字段',
              'P',
              '主键',
              'U',
              '唯一约束',
              'R',
              '外键') AS 约束类型
  FROM user_constraints au
  JOIN user_cons_columns cu
    ON au.table_name = cu.table_name;
--综合查询
SELECT 用户,
       索引名称,
       字段名称,
       字段类型,
       字段长度,
       字段备注,
       约束名称,
       约束对应的字段名,
       约束类型,
       表名,
       表备注,
       所属表空间
  FROM (
        --所有非约束表
        SELECT NULL               AS 用户,
                NULL               AS 索引名称,
                t.column_name      AS 字段名称,
                t.data_type        AS 字段类型,
                t.data_length      AS 字段长度,
                c.comments         AS 字段备注,
                NULL               AS 约束名称,
                NULL               AS 约束对应的字段名,
                NULL               AS 约束类型,
                t.table_name       AS 表名,
                co.comments        AS 表备注,
                ut.tablespace_name AS 所属表空间
          FROM user_tab_columns t
          JOIN user_col_comments c
            ON t.table_name = c.table_name
           AND t.column_name = c.column_name
          JOIN user_tab_comments co
            ON co.table_name = t.table_name
          JOIN user_tables ut
            ON ut.table_name = t.table_name
        UNION
        --所有约束表
        SELECT au.owner AS 用户,
                au.index_name AS 索引名称,
                t.column_name AS 字段名称,
                t.data_type AS 字段类型,
                t.data_length AS 字段长度,
                c.comments AS 字段备注,
                au.constraint_name AS 约束名称,
                cu.column_name AS 约束对应的字段名,
                decode(au.constraint_type,
                       'C',
                       '普通字段',
                       'P',
                       '主键',
                       'U',
                       '唯一约束',
                       'R',
                       '外键') AS 约束类型,
                t.table_name AS 表名,
                co.comments AS 表备注,
                ut.tablespace_name AS 所属表空间
          FROM user_tab_columns t
          JOIN user_col_comments c
            ON t.table_name = c.table_name
           AND t.column_name = c.column_name
          JOIN user_tab_comments co
            ON co.table_name = t.table_name
          JOIN user_constraints au
            ON au.table_name = t.table_name
          JOIN user_cons_columns cu
            ON cu.constraint_name = au.constraint_name
          JOIN user_tables ut
            ON ut.table_name = t.table_name) k;
创建序列:
CREATE SEQUENCE seq_huyingzhao
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE

让程序猿能共同成长起来

让程序猿传播和共享技术

让程序猿养成做笔记习惯

让程序猿持续的增长知识

让程序猿写代码更加规范

让程序猿职场上学会低调