用户与授权
sqlplus
system
password
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 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' ;
查询
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" ;
SELECT * FROM "code" WHERE "ts_code" IN ( '000001.SZ' , '000002.SZ' ) ;
SELECT * FROM "code" WHERE "f1" BETWEEN 1 AND 20 ;
'%'
'_'
SELECT * FROM "code" WHERE "ts_code" LIKE '00000%' ;
INTERSECT
UNION ALL
UNION
MINUS
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]
[ MINVALUE num| NOMINVALUE]
[ 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
表分区
范围分区: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)
)
列表分区: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 )
)
散列(哈希)分区:HASH 组合分区
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'