Oracle 基础语句备忘

--查询数据库实例信息
select * from v$instance;
--查询数据库ID,NAME等信息
select * from v$database;
--查询当前时间
select sysdate from dual;
--查询系统所有用户
select * from all_users;
--查询当前用户的所有对象及列名
select * from tab;
--查询所有的表空间名
select tablespace_name from dba_tablespaces;
--select * from dba_tablespaces;
select * from dba_tablespaces;
--查看所有表空间大小
select tablespace_name as 表空间名,(sum(bytes)/1024/1024) as 表空间大小 from dba_data_files group by tablespace_name; 
--查看未使用的表空间大小
select tablespace_name as 表空间名,(sum(bytes)/1024/1024) as 剩余表空间大小  from dba_free_space group by tablespace_name; 
--新建表空间
create tablespace news_tablespace datafile 'F:\app\FUHUI\oradata\JRSCLJT\news_data.dbf' size 200M;
--删除表空间（可能需要手动删除物理文件）
DROP TABLESPACE news_tablespace INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS;
--查询所有用户
select * from all_users;
--查询所有表空间及表
select *  from dba_tables;
select *  from dba_tables where owner = 'LJT_ADMIN';
--查询表空间下的所有表
select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'SYSTEM';
select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'LJT';
select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'LJT_SEMIS';
--查询用户的表空间及表名
select owner,table_name,tablespace_name  from dba_tables where owner = 'LJT_ADMIN';
--查询所有的表空间
select tablespace_name from dba_tablespaces;
select * from dba_tablespaces;
--创建表空间
create tablespace LJT 
       datafile 'F:\app\FUHUI\oradata\JRSCLJT\ljt.dbf' 
       size 200M;
create tablespace LJT_SEMIS 
       datafile 'F:\app\FUHUI\oradata\JRSCLJT\ljt_semis.dbf' 
       size 200M;
--删除表空间（可能需要手动删除物理文件）
--DROP TABLESPACE LJT INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS;
--创建用户，给定表空间
create user LJT_ADMIN identified by LJT_ADMIN default tablespace LJT;
--赋予LJT_ADMIN DBA权限
grant dba to LJT_ADMIN;
--查看用户表空间权限
select * from user_ts_quotas;
--创建的表的OWNER为当前登录的用户，创建表所在的表空间为用户默认的表空间，如果一个用户拥有多个表空间，在不指定创建表的表空间的情况下默认创建在默认表空间下，
--也可以指定表的表空间
create table T_STU
(
       STU_ID CHAR(5) NOT NULL,
       STU_NAME VARCHAR2(8) NOT NULL,
       CONSTRAINT PK_T_STUT PRIMARY KEY (STU_ID)
);
create table T_STU_TEST
(
       STU_ID CHAR(5) NOT NULL,
       STU_NAME VARCHAR2(8) NOT NULL,
       CONSTRAINT PK_T_STU_TEST PRIMARY KEY (STU_ID)
);
create table T_STU_SEMIS
(
       STU_ID CHAR(5) NOT NULL,
       STU_NAME VARCHAR2(8) NOT NULL,
       CONSTRAINT T_STU_SEMIS PRIMARY KEY (STU_ID)
) tablespace LJT_SEMIS;

create table T_SCORE
(
       EXAM_SCORE NUMBER(5,2),
       EXAM_DATE DATE,
       AUTOID NUMBER(10) NOT NULL,
       STU_ID CHAR(5) NOT NULL,
       SUB_ID CHAR(3) NOT NULL,
       CONSTRAINT PK_T_SCORE PRIMARY KEY (AUTOID),
       CONSTRAINT FK_T_SCORE FOREIGN KEY (STU_ID) REFERENCES T_STU(STU_ID)  
);

INSERT INTO T_STU VALUES('1','XuLei1');
INSERT INTO T_STU VALUES('2','XuLei2');
INSERT INTO T_STU VALUES('3','XuLei3');
INSERT INTO T_STU VALUES('4','XuLei4');
INSERT INTO T_STU VALUES('5','XuLei5');
INSERT INTO T_STU VALUES('6','XuLei6');
INSERT INTO T_STU VALUES('7','XuLei7');

select * from T_STU;