Oracle 学习笔记

2020年3月27日13:19:20

数据类型

NUMBER类型

NUMBER[(P[,M])];
++P表示精度，M表示小数点的位数++

CREATE TABLE t2 (a NUMBER(2,1));
Insert INTO t2 VALUES(1.1);
Insert INTO t2 VALUES(1.333); --四舍五入 保存为1.3
Insert INTO t2 VALUES(1.999); --四舍五入 保存为2.0
SELECT * FROM t2;

DATE类型

SELECT to_char(SYSDATE,'yyyymmdd hh24:mi:ss') FROM dual;--精确到秒
SELECT to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss:ssxff6') FROM dual; --精确到小数点后6位

LOB类型

CLOB => 字符型大对象
BLOB => 图形、视频、声音
BFILE => 二进制文件

ROWID和ROWNUM伪列

SELECT t.a,ROWNUM,t.rowid FROM t2 t 

ALTER操作

create table STUDENT
( 
  sno      NUMBER(6),
  sname    VARCHAR2(10),
  birthday DATE,
  tele     VARCHAR2(20)
);
ALTER TABLE student ADD tele VARCHAR2(11);          --给student增加tele新列
ALTER TABLE student MODIFY tele VARCHAR2(20);       --修改student.tele列大小
ALTER TABLE student dorp column tele VARCHAR2(20);  --删除student.tele列

>DESC student;  --显示student表的结构

INSERT INTO student values('1','张三',to_date('19981220','yyyymmdd'),'18717705936');
INSERT INTO student values('1','李四',to_date('19980213','yyyymmdd'),'18717705936');
INSERT INTO student values('1','万五',to_date('19980607','yyyymmdd'),'18717705936');
INSERT INTO student values('1','',to_date('19980914','yyyymmdd'),'18717705936');

student更新到student2

INSERT INTO student2 SELECT * FROM student;

is null

SELECT * FROM student t WHERE t.sname IS NULL;

DELETE、TRUNCATE和DROP区别

DELETE --> 会保存到缓存空间，可以恢复。大数据
DELETE FROM table_name;
TRUNCATE --> 直接删除，无法恢复。删除速度快
TRUNCATE TABLE table_name;
DROP --> 连带表结构都删除掉，无法恢复
DROP TABLE table_name;

创建成绩表

CREATE TABLE grade(
sno NUMBER(20) NOT NULL,
Subjects VARCHAR2(30),
grade NUMBER(3,1)
);

给grade插入测试数据

INSERT INTO grade VALUES (0,'语文',80.5);
INSERT INTO grade VALUES (0,'数学',60.5);
INSERT INTO grade VALUES (0,'英语',59);
INSERT INTO grade VALUES (1,'语文',88.5);
INSERT INTO grade VALUES (1,'数学',46.5);
INSERT INTO grade VALUES (1,'英语',63);
INSERT INTO grade VALUES (2,'语文',82.5);
INSERT INTO grade VALUES (2,'数学',66.5);
INSERT INTO grade VALUES (2,'英语',70);
INSERT INTO grade VALUES (3,'语文',82.5);
INSERT INTO grade VALUES (3,'数学',66.5);
INSERT INTO grade VALUES (3,'英语',70);
INSERT INTO grade VALUES (4,'语文',99.5);
INSERT INTO grade VALUES (4,'数学',88.5);
INSERT INTO grade VALUES (4,'英语',90);

提交
COMMIT;

查询数据
SELECT * FROM STUDENT;
SELECT * FROM GRADE;

ORDER BY 排序

SELECT * FROM GRADE t ORDER BY t.sno DESC;

UNION、UNION all 、MINUS 、INTERSECT

SELECT 1 FROM dual UNION SELECT 1 FROM dual; –去重
SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual; –合并
SELECT * FROM student MINUS SELECT * FROM student t WHERE t.sno = ‘0’ –相减
SELECT * FROM student INTERSECT SELECT * FROM student t WHERE t.sno = ‘0’ –取交集

TABLE、COLUMN重命名

RENAME student TO newstudent; –>table 重命名
ALTER TABLE newstudent RENAME COLUMN sno TO newsno; –>column 重命名

UPDATE操作

MySQL update语法

UPDATE user SET PASSWORD = PASSWORD(‘root’) WHERE User = ‘root’;

Oracle update语法

UPDATE USER set PASSWORD =(‘root’) WHERE User = ‘root’;
UPDATE STUDENT t SET t.sno = (‘0’) WHERE t.sno = ‘1’
SELECT t.*,t.rowid from STUDENT t –查询

创建用户及用户表空间、授权

----创建ETL_DATA临时表空间
create temporary tablespace ETL_TEMP tempfile 'ETL_TEMP.dbf' size 1G extent management local;
----创建ETL_DATA表空间
create TABLESPACE etl_data logging datafile 'etl_data.dbf' size 1G autoextend on next 50M maxsize unlimited extent management local;
----创建ETL用户并指定ETL用户的表空间
create USER test identified by test
default tablespace etl_data
temporary TABLESPACE temp
profile DEFAULT;
----授权
grant connect,dba to test;
grant unlimited tablespace to test;