DMSQL

–数据库修改语句

ALTER DATABASE MOUNT/OPEN;

–用户定义语句

CREATE USER TEST IDENTIFIED BY "123123123";

–修改用户,会话空闲期无限制，最大连接数为10

ALTER USER TEST LIMIT SESSION_PER_USER 10,CONNECT_IDLE_TIME UNLIMITED;

–删除用户，使用CASCADE除数据库中该用户及其创建的所有对象被删除外，
–如果其他用户创建的表引用了该用户表上的住关键字或唯一
关键字，或者在该表上创建了试图，DM将自动删除相应的引用完整性约束及试图依赖关系

DROP USER TEST;

–模式定义,模式SCHEMA属于SYSDBA

CREATE SCHEMA SCHEMA1 AUTHORIZATION SYSDBA;

–设置当前模式语句，SYSDBA用户将当前的模式从SYSDBA切换到SALES

SET SCHEMA SALES;

–删除模式

DROP SCHEMA SCHEMA1 CASCADE;

–表空间定义语句

CREATE TABLESPACE TS1 DATAFILE 'D:\TS1.DBF' SIZE 128;

–修改表空间语句

ALTER TABLESPACE TS1 RENAME TO TS2;--将表空间TS1修改为TS2

ALTER TABLESPACE TS1 ADD DATAFILE 'D:\TS1.DBF' SIZE 128;--增加数据文件

ALTER TABLESPACE TS1 RESIZE DATAFILE 'D:\TS1.DBF' TO 200;--修改表空间数据文件大小

–修改表空间数据文件路径

ALTER TABLESPACE TS1 OFFLINE;
ALTER TABLESPACE TS1 RENAME DATAFILE 'D:\TS1.DBF' TO 'E:\TS1.DBF';
ALTER TABLESPACE TS1 ONLINE;

–修改表空间TS1的数据文件’D:\TS1.DBF’自动扩展属性为每次扩展10M，最大文件大小为1G

ALTER TABLESPACE TS1 DATAFILE 'D:\TS1.DBF' AUTOEXTEND ON NEXT 10 MAXSIZE 1024;

–修改表空间TS1缓冲池名为KEEP

ALTER TABLESPACE TS1 CACHE="KEEP";

–修改表空间为CORRUPT状态，注意只有表空间处于OFFLINE状态或表空间损坏的情况才允许使用

ALTER TABLESPACE TS1 CORRUPT;

–删除表空间,SYSDBA用户登录删除

DROP TABLESPACE TS1;

–表空间文件失效检查,该过程只在LINUX下有效

SP_FILE_SYS_CHECK();

–表空间失效文件恢复准备,在LINUX下有效

SP_TABLESPACE_PREPARE_RECOVER('TS1');

–表空间失效文件恢复，LINUX下有效，在SP_TABLESPACE_PREPARE_RECOVER及在OS系统内完成了数据文件的复制后调用

SP_TABLESPACE_RECOVER('TS1');

–创建HTS表空间

CREATE HUGE TABLESPACE HTS_NAME PATH 'E:\HTSSPACE';

–修改HTS表空间

ALTER HUGE TABLESPACE HTS_NAME ADD PATH 'E:\HTSPATH';

–删除HTS空间

DROP HUGE TABLESPACE HTS_NAME;

–索引定义语句

create index s1 on  address(ADDRESSID);
create UNIQUE index s2 on address(ADDRESSID,city);
create index index_t1 on T1(C1-C2);
drop index s1;
drop index s2;
drop index index_t1;

–使用空间索引
–创建含空间类型的表

--create table testgo (id int,name varchar(20),geo ST_polygon);

–创建空间索引

--create spatial index sipdx on testgo(name);

–删除空间索引

--drop index spidx;

–修改索引
–重命名索引

alter index index_t1 rename to index_t2;

–当索引为visible时

create table testindex (c1 int,c2 int);
create index index_c1 on testindex(c1);

–修改索引为invisible

alter index index_c1 invisible;
explain select c1 from testindex(c1);
drop table testindex;

–使用unusable将索引置为无效状态

create table testindex (c1 int,c2 int);
create index index_c1 on testindex(c1);
alter index index_c1 unusable;
create unique index u_index on testindex(c2);
insert into testindex values(1,1);
select * from testindex;

–使用rebuild将索引置为生效状态

alter index index_c1 rebuild;

–索引删除

drop index index_c1;

–位图连接索引定义
–创建位图连接索引,用于连接的列必须是维度表中的主键或存在唯一约束；如果是复合主键，则必须使用复合主键中的所有列；

create bitmap index bitindex on T1(T1.ID) from T1,T2 where T1.ID=T2.ID;
explain select * from T1,T2 where T1.ID=T2.ID and T1.name='houhou';

–删除位图索引

drop index bitindex;

–全文索引定义语句,全文索引必须在一般用户表上定义，字符类型才能建立全文索引

create context index index0001 on T1(name) lexer chinese_lexer;

–全文索引修改语句，T1表列上完全填充全文索引

alter context index index0001 on T1 rebuild;

–删除全文索引

drop CONTEXT index index0001 on T1;  

–数组索引定义语句, CREATE ARRAY INDEX <索引名> ON [<模式名>.] <表名> (<索引列定义>)

create array index index_array on t1(ID);

–数组索引引用，CONTAINS(<索引列名>,<val{,val}>)或者CONTAINS(<索引列名>,arr_var_exp)

create type arr_num1 is varray(1024) of number;--varray数组
create type arr_num2 is table of number;--嵌套表
create type arr_num3 is array number[];--动态
create type arr_num4 is array number[3];--静态
create class clsl as V arr_num1;end;
create table test (c1 clsl);
insert into test values(clsl (arr_num1(1,2,3)));
insert into test values(clsl(arr_num1(1,2)));
insert into test values(clsl(arr_num1(2,1)));
insert into test values(clsl(arr_num1(1,5)));
insert into test values(clsl(arr_num1(2,4)));
insert into test values(clsl(arr_num1(4,5,6)));
create array index idx on test(c1);--创建数组索引
select * from test where contains(c1,1,2,3);--使用数组索引

查询；
–嵌套表

declare
x arr_num2;
begin
x:=arr_num2();
x.extend(3);
x(1):=1;
x(2):=2;
x(3):=3;
select * from test where contains(c1,x);
end;

–动态数组

declare
x arr_num3;
begin
x:=new number[3];
x[1]:=1;
x[2]:=2;
x[3]:=3;
select * from test where contains(c1,x);
end;

–静态数组

declare
x arr_num4;
begin
x[1]:=1;
x[2]:=2;
x[3]:=3;
select * from test where contains(c1,x);
end;
drop table test;

–定义序列
–创建序列,间隔为10

create sequence seq_test increment by 10; 

–将序列的第一个值插入表test中

create table test (t1 int,t2 int,t3 int);
select * from test;
insert into test values (1,1,seq_test.nextval);
select * from test;

–修改序列
–创建完序列后直接修改序列的步长

create sequence seq1 increment by 1000 start with 5 nomaxvalue NOMINVALUE cache 10;
alter sequence seq1 increment by 1;
select seq1.nextval from dual;

–创建序列后使用nextval访问序列，然后修改步长

create sequence seq2 INCREMENT by 1000 start with 5 NOMAXVALUE NOMINVALUE nocache;
select seq2.nextval from dual;
alter sequence seq2 INCREMENT by 1;
select seq2.nextval from dual;

–修改序列的最小值

create sequence seq3 INCREMENT by 1 start with 100 MINVALUE 3;
alter sequence seq3 MINVALUE 2;

–删除序列

drop sequence seq3;

–域（domain），一个可允许值得集合，域在模式中定义，并由<域名>标识，域是用来约束由各种操作存储于基表中某列的有效值集
–创建域

create domain da int check (value<100);

–使用domain,使用某个域的用户必须具有该域的usage domain或usage any domain权限

create table T(ID DA);

–删除域，restrict表示仅当domain未被表列使用时才可以被删除，cascade表示级联删除

drop domain DA cascade;

–上下文（context），提供一组设置以及访问服务器运行时应用数据接口，通过这些接口可以有效控制应用数据的修改和访问
–创建上下文
–create [or replace] context using {<模式名.>};
–删除上下文
–drop context ;
–如何使用上下文
–第一步 创建package;

create or replace
package test_pk
as
procedure set_context
        (
                ts_name varchar,
                key     varchar,
                value   varchar);
procedure set_context
        (
                ts_name varchar,
                key     varchar,
                value varcahr,
                username varchar,
                client_id varcahr);
procedure get_context
        (
                ts_name varchar,
                key     varchar);
procedure clear_context
        (
                ts_name varchar,
                key     varchar,
                value   varchar);
end test_pk;
create or replace
package body test_pk
as
procedure set_context
        (
                ts_name varchar,
                key     varchar,
                value   varchar)
as
begin
        dbms_session.set_context(ts_name, key, value);
end;
procedure set_user_context
        (
                ts_name   varchar,
                key       varchar,
                value     varchar,
                username  varchar,
                client_id varchar)
as
begin
        dbms_session.set_context(ts_name, key, value, username, client_id);
end;
procedure clear_context
        (
                ts_name varchar,
                key     varchar,
                value   varchar)
as
BEGIN
        dbms_session.clear_context(ts_name, key, value);
end;
end test_pk;
select * from V$CONTEXT;

–创建context

create or replace context c_user01 using test_pk;

–设置namespace的属性

call test_pk.set_context('c_user01', 'u_k2', 'u_v2');

–查询该属性值

call test_pk.get_context('c_user01', 'u_k2');

–管理目录,目录是操作系统文件在数据库中的一个映射
–创建目录
–create [or replace] directory <目录名>as ‘DIR_PATH’;供具有create any directory权限的用户创建一个目录对象
–删除目录
–drop directory <目录名>;供具有create any directory权限的用户删除一个目录对象
–时区信息
–设置当前会话时区信息

set time zone '+9:00';

–设置当前会话时区为服务器所在地时区

set time zone local;

–日期串语言
–设置当前会话日期串为english

alter session set NLS_DATE_LANGUAGE=english;

–日期串格式
–设置当前会话的日期串格式为YYYY-MM-DD

alter session set NLS_DATE_FORMAT='YYYY-MM-DD';

–注释语句
–为表test创建注释信息

comment on table test is 'test表的注释';

–为表test的列name创建注释信息

comment on column test.name is 'test的name列';

–设置ini参数，INI参数分为手动、静态和动态三种类型，分别对应V$PARAMETER试图中type列的read only、in file
–SYS/SESSION。服务器运行过程中，手动（READ ONLY）参数不能被修改。静态和动态参数可以修改。
–静态（IN FILE）参数只能通过修改dm.ini文件进行修改，修改后重启服务器才能生效，为系统级参数，生效后会影响所有会话。
–动态（SYS和SESSION）参数可在dm.ini文件和内存同时修改，修改后及时生效。其中，SYS为系统级参数，修改后会影响所有会话；
–session为会话级参数，服务器运行过程中被修改时，之前创建的会话不受影响，只有新创建的会话使用新的参数值
–设置当前系统动态、会话级参数sort_buf_size参数值为200，要求延迟生效，对当前的session不生效，对后面创建的会话才生效，并且只修改内存

alter system set 'SORT_BUF_SIZE'=200 DEFERRED memory;

–设置静态参数MTAB_MEM_SIZE参数值为1200

alter system set 'MTAB_BUF_SIZE'=1200 SPFILE;

–设置只对当前会话起作用
–设置当前会话的HAGR_HASH_SIZE参数值为2000000

alter SESSION set 'HAGR_HASH_SIZE'=2000000;

–修改系统语句
–alter system <修改系统语句>;
–<修改系统语句>::=switch logfile | archive log current archive log current 和switch logfile功能一样，都是把新生成的，
–还未归档的联机日志进行归档。
–设置列、索引生成统计信息
–对SYSOBJECTS表上ID列生成统计信息，采样率的百分比为30%

stat 30 on SYS.SYSOBJECTS(ID);

–对purchasing模式下的索引s1生成统计信息，采样率为50%

stat 50 on index purchasing.s1;

–对SYSOBJECTS表上PID,NAME列生成统计信息，采样率的百分比为30%

stat 30 on sys.sysobjects(PID,NAME);

–设置表生成统计信息
–stat on [<模式名>.]<表名>[global];global用于MPP环境下各个节点数据收集统一生成统计信息。
–为SYSOBJECTS表生成统计信息

stat on SYS.SYSOBJECTS;
stat on TEST."COMMENT";

–用dbms_stats包查看统计信息

SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_STATS');
BEGIN
	dbms_stats.TABLE_STATS_SHOW('SYS','SYSOBJECTS');
end;
BEGIN
	dbms_stats.TABLE_STATS_SHOW('TEST','COMMENT');
end;

–查看登录信息

select * from sys.SYSACCHISTORIES;

–数据查询时数据库的核心操作

--create student
CREATE
        TABLE TEST.STUDENT
        (
                SNO   INT NOT NULL        ,
                SNAME VARCHAR(50) NOT NULL,
                SEX   CHAR(10) DEFAULT '男',
                DEPT  VARCHAR(50)         ,
                BIRTH DATE                ,
                AGE INT                   ,
                CLUSTER PRIMARY KEY (SON) ,
                CHECK(SEX  IN('男', '女')),
                CHECK(AGE   > 0
            AND AGE         < 100)                                                ,
                CHECK(DEPT IN('信息系', '计算机科学系', '数学系', '管理系', '中文系', '外语系', '法学系'))
        );
--create course
CREATE
        TABLE TEST.COURSE
        (
                CNO   INT NOT NULL        ,
                CNAME VARCHAR(50) NOT NULL,
                PRIMARY KEY(CNO)
        );
--create cs
CREATE
        TABLE TEST.CS
        (
                SNO INT NOT NULL                             ,
                CNO INT NOT NULL                             ,
                CJ  INT                                      ,
                FOREIGN KEY(SNO) REFERENCES TEST.STUDENT(SNO),
                FOREIGN KEY(CNO) REFERENCES TEST.COURSE(CNO) ,
                CHECK(CJ >= 0
            AND CJ       <= 100)
        );
INSERT INTO TEST.STUDENT VALUES(1,'张三','男','信息系','1998-09-09',23);
INSERT INTO TEST.STUDENT VALUES(2,'李四','男','计算机科学系','1997-09-09',24);
INSERT INTO TEST.STUDENT VALUES(3,'王五','女','数学系','1998-01-01',23);
INSERT INTO TEST.STUDENT VALUES(4,'陆六','女','管理系','1999-09-09',22);
INSERT INTO TEST.STUDENT VALUES(5,'顾七','女','中文系','1997-08-09',24);
INSERT INTO TEST.STUDENT VALUES(6,'小八','男','外语系','2000-09-09',21);
INSERT INTO TEST.STUDENT VALUES(7,'小九','男','法学系','1996-09-09',25);
INSERT INTO TEST.COURSE VALUES(1,'计算机');
INSERT INTO TEST.COURSE VALUES(2,'信息安全');
INSERT INTO TEST.COURSE VALUES(3,'操作系统');
INSERT INTO TEST.COURSE VALUES(4,'数据库');
INSERT INTO TEST.COURSE VALUES(5,'JAVA');
INSERT INTO TEST.COURSE VALUES(6,'WEB');
INSERT INTO TEST.COURSE VALUES(7,'高数');
INSERT INTO TEST.CS VALUES(1,1,99);
INSERT INTO TEST.CS VALUES(1,2,98);
INSERT INTO TEST.CS VALUES(2,3,99);
INSERT INTO TEST.CS VALUES(2,4,98);
INSERT INTO TEST.CS VALUES(3,5,99);
INSERT INTO TEST.CS VALUES(3,6,98);
INSERT INTO TEST.CS VALUES(4,7,99);
INSERT INTO TEST.CS VALUES(5,1,98);
INSERT INTO TEST.CS VALUES(6,3,99);
INSERT INTO TEST.CS VALUES(6,5,98);
INSERT INTO TEST.CS VALUES(7,7,99);
INSERT INTO TEST.CS VALUES(7,4,98);

–查询全体学生的学号与姓名

select sno,sname from TEST.STUDENT;

–查询全体学生的姓名、学号、所在系，并用别名显示出来

select sname 姓名,sno 学号,dept 所在系 from TEST.student;

–查询全体学生的详细信息

select * from TEST.student;

–查询选修了课程的学生学号

select DISTINCT(sno) 学号 from TEST.CS;

–查询年龄在20-23之间的学生的姓名、系别和年龄

select sname 姓名,dept 系别,age 年龄 from TEST.STUDENT where age >= 20 and age <= 23;

–查询信息系、数学系和计算机科学系生的姓名和性别

select sname 姓名, sex 性别 from TEST.student where dept in ('信息系','数学系','计算机科学系');

–查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别

select sname 姓名,sex 性别 from TEST.student where dept not in('信息系','数学系','计算机科学系');

–查询所有姓刘学生的姓名、学号和性别

select sname 姓名,sno 学号,sex 性别 from TEST.StUDENT where sname like '刘%';

–查询所有不姓刘的学生姓名

select sname 姓名 from TEST.STUDENT where sname not like '刘%';

–查询信息系、数学系和计算机科学系学生的姓名和性别

select sname,sex from TEST.STUDENT where dept in('信息系','数学系','计算机科学系');

–查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

select sno,cj from TEST.CS where cno=3 order by cj desc;

–查询全体学生情况，查询结果按所在系升序排列，同一系中的学生按年龄降序排列。

select * from TEST.STUDENT order by dept ASC , age desc;

–查询学生总人数。

select count(*) from TEST.STUDENT;

–查询选修了课程的学生人数。

select count(distinct(sno)) from TEST.cs ;

–计算1号课程的学生平均成绩。

select avg(cj) from TEST.CS where cno=1;

–查询选修1号课程的学生最高分数。

select max(cj) from TEST.CS where cno=1;

–求各个课程号及相应的选课人数。

select count(*),cno  from TEST.CS group by cno;

–查询选修了3门以上课程的学生学号。

select sno from TEST.CS group by sno HAVING count(*)>=3;

–查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。

select count(cno) 课程数,sno from TEST.cs where cj >= 90 group by sno having count(sno)>=3;

–查询每个学生选修课程的总分。

select sum(cj) 总成绩,sno from TEST.cs group by sno;

–查询所有选修了1号课程的学生姓名。

select distinct(s.sname) 姓名 from TEST.student s,TEST.cs,TEST.course c
where s.sno=cs.sno and cs.cno=c.cno and TEST.cs.cno=1

–create TT

CREATE TABLE TT(A INT);
INSERT INTO TT VALUES(5);
INSERT INTO TT VALUES(6);
INSERT INTO TT VALUES(7);

–create KK

CREATE TABLE KK(A INT);
INSERT INTO KK VALUES(5);
INSERT INTO KK VALUES(5);
INSERT INTO KK VALUES(6);
INSERT INTO KK VALUES(8);

–查询tt表中有的KK表中没有的

select * from TT except select * from KK;
select * from TT minus select * from KK;

–查询TT表和KK表中都由有的数据

select * from TT intersect select * from KK;

–带条件的查询
–学生年龄在21-23的姓名、系别

select sname,dept,age from TEST.STUDENT where age >= 21 and age <= 23;
select sname,dept,age from TEST.STUDENT where age between 21 and 23;

–查询信息系，中文系的学生信息

select * from TEST.STUDENT where dept in ('信息系','中文系');
select * from TEST.STUDENT where dept not in ('信息系','中文系');

–like '_‘匹配一个字符，’%'匹配多个字符

select * from TEST.STUDENT where sname like '张%';

–转移字符 escape ‘*’

select * from TEST.STUDENT where sname like '%李/_%' escape '/';

–使用.row进行like谓词的查询,表STUDENT中所有带系得列都被查询出来，相当于列查询用or连接

select * from TEST.STUDENT where TEST.STUDENT.row LIKE '%系';

–使用null谓词查询

select * from TEST.STUDENT where sname is null;
select * from TEST.STUDENT where sname is not null;

–组合逻辑
–用各种谓词组合成较复杂得条件查询
–集函数
–函数分类
–1.count(*);
–2.相异函数 avg|max|min|sum|count(distinct<列名>);
–3.完全集函数avg|max|min|count|sum([all]<表达式>);
–4.完全集函数VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、STDDEV;
–5.协方差函数COVAR_POP、COVAR_SAMP、CORR;
–6.首行函数FIRST_VALUE;
–7.求区间范围内最大值集函数AREA_MAX;
–8.FIRST/LAST集函数 AVG|MAX|MIN| COUNT|SUM([ALL]<表达式>) KEEP (DENSE_PANK FIRST|LAST ORDER BY子句);
–9.字符串集函数LISTAGG/LISTAGG2
–10.求中位数MEDIAN;
–求年龄最小

 select min(age) from TEST.STUDENT;

–求年龄最大

 select max(age) from TEST.STUDENT;

–求平均年龄

 select avg(age) from TEST.STUDENT;

–求学生总数

 select count(*) from TEST.STUDENT;

–求方差集函数variance、标准差函数STDDEV和样本标准差函数STDDEV_SAMP

select VARIANCE(age) from TEST.STUDENT;
 select STDDEV(age) from TEST.STUDENT;
 select STDDEV_SAMP(age) from TEST.STUDENT;

–求总体协方差集函数COVAR_POP、样本协方差COVAR_SAMP和相关系数CORR

 select COVAR_POP(age,sno) from TEST.STUDENT;
 select COVAR_SAMP(age,sno) from TEST.STUDENT;
 select CORR(age,sno) from TEST.STUDENT;

–首行函数first_value

select FIRST_VALUE(sname) from TEST.STUDENT;

–求区间范围内得最大函数area_max

select area_max(age,21,23) from TEST.STUDENT;

–求first/last集函数
–求LISTAGG/LISTAGG2集函数
–将sname列全部查询出来用’,'连接

select LISTAGG(sname,',') within group (order by sname) listagg from TEST.STUDENT;
 select LISTAGG2(sname,',') within group (order by sname) listagg from TEST.STUDENT;

–求MEDIAN集函数
–求按照dept分组后，各组内的age的中位数

select median(age) from TEST.STUDENT group by(dept);

–分析函数
–1． COUNT(*)；
–2． 完全分析函数 AVG|MAX|MIN| COUNT|SUM([ALL]<值表达式>)，这 5 个分析函数的参数和作为集函数时的参数一致；
–3． 方差函数 VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、STDDEV；
–4． 协方差函数 COVAR_POP、COVAR_SAMP、CORR；
–5． 首尾函数 FIRST_VALUE、LAST_VALUE；
–6． 相邻函数 LAG 和 LEAD；
–7． 分组函数 NTILE；
–8． 排序函数 RANK、DENSE_RANK、ROW_NUMBER；
–9． 百分比函数 PERCENT_RANK、CUME_DIST、RATIO_TO_REPORT、PERCENTILE_CONT、NTH_VALUE；
–10． 字符串函数 LISTAGG；
–11． 指定行函数 NTH_VALUE。
–查看版本

select *,id_code from SYS."V$VERSION"