oracle杂记_基础

用户与授权

• 首次登陆1

sqlplus
system
password

• 首次登陆2

sqlplus /nolog
connect / as sysdba

-- 创建用户
create user username identified by password;
-- 更改密码
alter user user1 identified by 234556;
-- 删除用户
drop user user1;
-- 授权
grant connect, resource,dba to user1;
-- 撤销授权
revoke connect, resource,dba from user;

表

创建表

CREATE TABLE "student"(
	"sno" VARCHAR2(10) PRIMARY KEY,
	"name" VARCHAR2(10) NOT NULL,
	"sex" VARCHAR2(2) CHECK("sex"='男' OR "sex"='女'),
	"age" NUMBER(3),
	"score" NUMBER(3,2),
	"birthday" DATE
);

临时表

-- 会话级
CREATE GLOBAL TEMPORARY TABLE STUDENT
(STU_ID NUMBER(5),
CLASS_ID  NUMBER(5),
STU_NAME VARCHAR2(8),
STU_MEMO VARCHAR2(200)) ON COMMIT PRESERVE ROWS ;


-- 事务级
CREATE GLOBAL TEMPORARY TABLE CLASSES
(CLASS_ID NUMBER(5),
CLASS_NAME VARCHAR2(8),
CLASS_MEMO VARCHAR2(200)) ON COMMIT DELETE ROWS ;

约束表

CREATE TABLE "student2"(
    "sno" VARCHAR2(10),
    "name" VARCHAR2(10),
    "sex" VARCHAR2(2),
    "age" NUMBER(3),
    "score" NUMBER(3,2),
    "birthday" DATE
);

-- 主键约束
ALTER TABLE "student" ADD CONSTRAINT "PK_NO" PRIMARY KEY ("no"));
ALTER TABLE "student" DROP CONSTRAINT "PK_NO");

-- 外检约束
ALTER TABLE "student" ADD CONSTRSINT "FK_NO" FOREIGN KEY("no") references "table2"("fno"));
ALTER TABLE "student" DROP CONSTRAINT "FK_NO");

-- 检查约束
ALTER TABLE "student" ADD CONSTRAINT "CK_SEX" CHECK("sex"='男' or "sex"='女');
ALTER TABLE "student" DROP CONSTRAINT "CK_SEX");

-- 唯一值约束
ALTER TABLE "student" ADD CONSTRAINT "UN_NAME" UNIQUE("nama"));
ALTER TABLE "student" DROP CONSTRAINT "UN_NAME");

-- 空值约束
ALTER TABLE "student" MODIFY ("age" NOT NULL);
ALTER TABLE "student" MODIFY ("age" NULL);

-- 查看约束名
select * from user_constraints where "OWNER" = 'SCOTT' AND "TABLE_NAME"='student';

修改表

-- 复制表 
CREATE "cop" AS SELECT * FROM "student";

-- 删除表
DROP TABLE "cop";

-- 删除表数据
TRUNCATE TABLE  "student"; #速度比delete快
DELETE FROM "student";

-- 修改表名
ALTER TABLE "student" RENAME TO "student1";

列

-- 加列
ALTER TABLE "student" ADD "sex" VARCHAR2(2);

-- 删列
ALTER TABLE "student" DROP COLUMN "sex";

-- 修改列名
ALTER TABLE "student" RENAME COLUMN "sex" to "new_sex";

-- 修改列数据类型(有数据时不能改，添加临时列操作)
ALTER TABLE "student" MODIFY ("sex" VARCHAR2(2) NOT NULL);

ALTER TABLE "student" MODIFY ("sex" VARCHAR2(4 BYTE));

数据

主要数据类型

• CHAR(length)
• VARCHAR2(length)
• BUMBER(p,s)
• DATE

# 增删改

-- 主要数据类型
CHAR(length)
VARCHAR2(length)
NUMBER(p,s)
DATE

-- 插入
INSERT INTO "student" VALUES('s001','zack','男',18,100,TO_DATE(19950820,'YYYYMMDD');
INSERT INTO "student"("sno","name") VALUES('s002','kevin');
INSERT INTO "student" SELECT * FROM "student2";

-- 更新
UPDATE "student" SET "name" = 'zack_k' WHERE "name"='zack';
UPDATE "student" SET "tmp_col" = "name";

-- 修改值
UPDATE "code" SET "close"=90,"low"='10' where "f1"=1;

-- 删除数据
DELETE FROM "code" WHERE "f1"='0';

查询

-- 别名，列可以选择用AS ，表别名不可以用AS
select c."ts_code" as "code" from "code" c

with A AS 
    (select "ts_code" from "code")
SELECT A."ts_code" from A;


-- 基本查询
SELECT "code"."ts_code",COUNT(1) as "ctn" FROM "code" GROUP BY "ts_code" HAVING COUNT(1)>3000 ORDER BY "ctn" DESC ;


-- 去重查询
SELECT DISTINCT "f1" FROM "code"; #比groupby更快


-- 包含查询
SELECT * FROM "code" WHERE "ts_code" IN('000001.SZ','000002.SZ');


-- 区间查询
SELECT * FROM "code" WHERE "f1" BETWEEN 1 AND 20; #包含


-- 模糊查询
'%' # 0个或多个字符
'_' # 任意一个字符
SELECT * FROM "code" WHERE "ts_code" LIKE '00000%';


-- 集合查询
INTERSECT #交集
UNION ALL #所有并集
UNION #并集
MINUS #补集，1有2没有

SELECT B."ts_code" FROM "basic" B 
INTERSECT 
SELECT C."ts_code" FROM "code" C; 


-- 链接表
inner join;left join;right join;outer join;

SELECT
	* 
FROM
	"basic"
	LEFT JOIN "code" ON "basic"."ts_code" = "code"."ts_code" 
ORDER BY "symbol" DESC;


-- 链接字符
SELECT
	("ts_code" || ' pct is ' || "pct_chg") AS "text"
FROM
	"code" 
WHERE
	"ts_code" = '000001.SZ';


-- 计算
SELECT
	"trade_date",ROUND(("high"-"low")/"low"*100, 2) AS "dif"
FROM
	"code" 
WHERE
	"ts_code" = '000001.SZ';


-- 子查询
SELECT
    b."ts_code" 
FROM
    "basic" b 
WHERE
    "ts_code" IN ( SELECT c."ts_code" FROM "code" c );


-- 伪列
ROWID #每行内存位置
ROWNUM #行号

-- 筛选行数
select * from "code" order by "f1" where ROWNUM<5;

分析函数

-- 排序
SELECT
    C."trade_date",
    C."close",
    C."ts_code",
    SUM( C."close" ) OVER ( PARTITION BY C."ts_code" ORDER BY C."trade_date" DESC ) "test" 
FROM
    "code" C;


-- 窗口
UNBOUNDED PRECEDING #前面所有行
CURRENT ROW #当前行
UNBOUNDED FOLLOWING #后面所有行

SELECT
    C."trade_date",
    C."close",
    C."ts_code",
    LAST_VALUE ( C."close" ) OVER (
        PARTITION BY C."ts_code" 
        ORDER BY C."trade_date" DESC 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
        ) "test" 

FROM
    "code" C;


-- 去前一个值
SELECT 
c.*,
LAG(c."ts_code",1) OVER ( ORDER BY c."list_date" DESC ) "pre" ,
LEAD(c."ts_code",1) OVER ( ORDER BY c."list_date" DESC ) "next" 
FROM "basic" c


-- 行列转换
SELECT S.Y,
       SUM(DECODE(S.Q, 1, AMT, NULL)) Q1,
       SUM(DECODE(S.Q, 2, AMT, NULL)) Q2,
       SUM(DECODE(S.Q, 3, AMT, NULL)) Q3,
       SUM(DECODE(S.Q, 4, AMT, NULL)) Q4
FROM S
GROUP BY S.Y

表空间

-- 同义词
CREATE [OR REPLACE] [PUBLIC] SYNONYM 
[schema.]synonym_name 
FOR 
[schema.]object_name;

DROP [PUBLIC] SYNONYM [schema.]sysnonym_name;


-- 序列
[START WITH num] --开始数字
[INCREMENT BY increment] --增长数
[MAXVALUE num|NOMAXVALUE] --最大值，10的27次方
[MINVALUE num|NOMINVALUE] --最小值，-10的26次方
[CYCLE|NOCYCLE] --到达最大重新开始
[CACHE num|NOCACHE] --预生成序列值个数

CREATE SEQUENCE "myseq"
MINVALUE 1
START WITH 1
NOMAXVALUE
INCREMENT BY 1
NOCYCLE
CACHE 30;

SELECT "myseq".NEXTVAL FROM DUAL; --访问下一个值
SELECT "myseq".CURRVAL FROM DUAL; --访问当前值

-- 修改序列
ALTER SEQUENCE "myseq"
MAXVALUE 10000
MINVALUE -300

-- 删除序列
DROP SEQUENCE "myseq"; 


-- 视图
[OR REPLACE] -- 替换
[FORCE|NOFORCE] --无基础表强制建立
[WITH READ ONLY] --不能增删改

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW "view_name"
AS
SELECT...
[WITH READ ONLY] 
SELECT * FROM "view_name"; 

DROP VIEW "view_name";


-- 索引
[UNIQUE] --制定索引列的值必须唯一

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])

DROP INDEX index_name

表分区

1. 范围分区：RANGE

CREATE TABLE MYEMP
(
    EMPNO  NUMBER(4)  PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2), 
    DEPTNO NUMBER(7,2) ,
    CONSTRAINT EMP2_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)
PARTITION  BY  RANGE (HIREDATE)
(
     PARTITION  part1 VALUES  LESS  THAN (TO_DATE('1981-1-1','YYYY/MM/DD')), --①
     PARTITION  part2 VALUES  LESS  THAN (TO_DATE('1982-1-1','YYYY/MM/DD')),
     PARTITION  part3 VALUES  LESS  THAN (TO_DATE('1983-1-1','YYYY/MM/DD')), 
     PARTITION  part4 VALUES  LESS  THAN (TO_DATE('1988-1-1','YYYY/MM/DD')), 
     PARTITION  part5 VALUES  LESS  THAN (MAXVALUE)
)

2. 列表分区：LIST

CREATE TABLE MYEMP2
(
    EMPNO  NUMBER(4)  PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(7,2),
    CONSTRAINT EMP1_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)
PARTITION BY LIST (DEPTNO)
(
    PARTITION MYEMP_DEPTNO_10  VALUES (10),
    PARTITION MYEMP_DEPTNO_20  VALUES (20),
    PARTITION MYEMP_DEPTNO_30  VALUES (30), 
    PARTITION MYEMP_DEPTNO_40  VALUES (40) 
)

4. 散列（哈希）分区：HASH
5. 组合分区

CREATE TABLE MYEMP3
(
    EMPNO  NUMBER(4)  PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2), 
    DEPTNO NUMBER(7,2) ,
    CONSTRAINT EMP3_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)
PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST (DEPTNO)
(
   PARTITION P1 VALUES LESS THAN(TO_DATE('1981-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P1A VALUES (10) ,
              SUBPARTITION P1B VALUES (20),
              SUBPARTITION P1C VALUES (30),
              SUBPARTITION P1D VALUES (40)
          ),
   PARTITION P2 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P2A VALUES (10) ,
              SUBPARTITION P2B VALUES (20),
              SUBPARTITION P2C VALUES (30),
              SUBPARTITION P2D VALUES (40)
          ),
   PARTITION P3 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P3A VALUES (10) ,
              SUBPARTITION P3B VALUES (20),
              SUBPARTITION P3C VALUES (30),
              SUBPARTITION P3D VALUES (40)
          ),
   PARTITION P4 VALUES LESS THAN (TO_DATE('1988-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P4A VALUES (10) ,
              SUBPARTITION P4B VALUES (20),
              SUBPARTITION P4C VALUES (30),
              SUBPARTITION P4D VALUES (40)
          )
)

• 维护

-- 查询分区的内容
select * from LDAY PARTITION(PART3);


-- 所有分区信息 
select * from DBA_PART_TABLES where owner=upper('test') AND table_name=upper('lday');


-- 表的分区信息
select * from user_tab_partitions where table_name = 'LDAY'


-- 添加分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');


-- 删除分区
ALTER TABLE SALES DROP PARTITION P3;
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

-- 截断分区
ALTER TABLE SALES TRUNCATE PARTITION P2;
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; 


-- 合并分区
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;


-- 拆分分区
ALTER TABLE SALES SPLIT PARTITION P20 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P2,PARTITION P22);


-- 接合分区(将散列分区中的数据接合到其它分区中)
ALTER TABLE SALES COALESCA PARTITION;


-- 重命名表分区
ALTER TABLE SALES RENAME PARTITION P21 TO P2; 

常用函数

• 聚合函数
  AVG(col):平均
  SUM(col):合计
  COUNT(col):计量
  MIN(col):最小
  MAX(col):最大
• 分析函数
  ROW_NUMBER():没有相同排名
  DENSE_RANK():有相同排名
  RANK():相同排名腾出空间
  LAG(col,offset[,noffset]):当前行的前n行的值
  LEAD(col,offset[,noffset]):当前行的后n行的值
• 字符函数
  ASCII(x):返回ascii
  CONCAT(x,y):链接连个字符
  INSTR(x, str [,start] [,n]):查找str出现的位置
  LENGTH(x):长度
  LOWER(x):小写
  UPPER(x):大写
  TRIM([trim_str FROM] x):截边
  REPLACE(x,old,new):查找替换
  SUBSTR(x,start[,length]):返回截断的字符串
• 数字函数
  ABS(x):绝对值
  MOD(x,y):模
  POWER(x,y):次方
  ROUND(x[,y]):四舍五入
  TRUNC(x[,y]):截断
• 日期函数
  SYSDATE:当前时间
  ADD_MONTHS(d,n):增加月份数
  LAST_DAY(d):返回当月最后一天
  ROUND(d[,fmt]):fmt with[‘DAY’,‘MONTH’,‘YEAR’]
  TRUNC(d[,fmt]):截断
• 类型转换
  TO_CHAR(d|n[,fmt]):转换为字符串
  TO_DATE(x [,fmt]):转换为日期
  TO_NUMBER(x[,fmt]):转换为数值
• 其他
  NVL(col, nval):替换带null返回
  NVL2(col, val1, val2):替换带null返回
  DECODE(col|val, con1, val1, con2, val2, nval):字典匹配
  CASE col WHEN con1 THEN val1 WHEN con2 THEN val2 ELSE nval END: 判断

select round(356.46,-2) from dual;

系统表

# 模式表信息
select * from user_tables where table_name = 'tbname'

# 模式列信息
select COLUMN_NAME FROM user_col_comments WHERE TABLE_NAME = 'tbname'