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;
评论区