一、作业
-
原表:
courseid coursename score 1 Java 70 2 Oracle 90 3 xml 40 4 jsp 30 5 servlet 80 查询此表后的结果显示如下(几个分数为60):
courseid coursename score mark 1 Java 70 pass 2 Oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass 写出此查询语句
-- 1.创建t_score表
CREATE TABLE t_score(ID NUMBER PRIMARY KEY,course CHAR(10) NOT NULL,score NUMBER(3));
-- 2.插入数据
CREATE SEQUENCE seq MINVALUE 0 START WITH 0;
INSERT INTO t_score VALUES(seq.nextval,'Java',70);
INSERT INTO t_score VALUES(seq.nextval,'oracle',90);
INSERT INTO t_score VALUES(seq.nextval,'xml',40);
INSERT INTO t_score VALUES(seq.nextval,'jsp',30);
INSERT INTO t_score VALUES(seq.nextval,'servlet',80);
-- 3.查询此表
SELECT ID,course,score,CASE
WHEN score>=60 THEN 'pass'
WHEN score<60 THEN 'fail'
END mark
FROM t_score; -
选课表(id,student,course),找出选课5门的学生
-- 1.创建选课表
CREATE TABLE t_course(ID NUMBER PRIMARY KEY,student VARCHAR(8) NOT NULL,course CHAR(8) NOT NULL);
-- 2.插入数据
CREATE SEQUENCE seq MINVALUE 0 START WITH 0;
INSERT INTO t_course VALUES(seq.nextval,'谭艺','Java');
INSERT INTO t_course VALUES(seq.nextval,'谭艺','oracle');
INSERT INTO t_course VALUES(seq.nextval,'谭艺','c');
INSERT INTO t_course VALUES(seq.nextval,'谭艺','c#');
INSERT INTO t_course VALUES(seq.nextval,'谭艺','c++');
INSERT INTO t_course VALUES(seq.nextval,'小明','python');
-- 3.找出选课5门的学生
SELECT student FROM t_course GROUP BY student HAVING COUNT(*)=5; -
经人举报JC抓住了一群CX人员,CX头目赵一有下线钱二,孙三,钱二下面有李四,周五,孙三下面有吴六,吴六下面有郑七,周五下面有冯八。JC请程序员小程来统计一下每个人的下线都是谁,通过指定姓名来得到此人的下线,小程该怎么写SQL。
-- 1.创建一个级别表
CREATE TABLE t_level(ID NUMBER PRIMARY KEY ,NAME VARCHAR(8),pre NUMBER);
-- 2.插入数据
INSERT INTO t_level VALUES(1,'赵一',0);
INSERT INTO t_level VALUES(2,'钱二',1);
INSERT INTO t_level VALUES(3,'孙三',1);
INSERT INTO t_level VALUES(4,'李四',2);
INSERT INTO t_level VALUES(5,'周五',2);
INSERT INTO t_level VALUES(6,'吴六',3);
INSERT INTO t_level VALUES(7,'郑七',6);
INSERT INTO t_level VALUES(8,'冯八',5);
-- 3.根据姓名来查找下线
SELECT NAME FROM t_level WHERE pre=(SELECT ID FROM t_level WHERE NAME='赵一');
二、PGA
-
程序缓存区,为了特定用户进程服务,是私有的
-
功能
-
排序区
-
会话区
-
堆栈区
-
游标区
-
三、SGA
-
系统全局区,所有用户都能使用,是共享的,一个oracle实例,一个SGA区
-
组成
-
固定区:通过这个区查找其他区的地址
-
重做缓冲区
-
块缓冲区
-
共享池
-
大池
-
Java池
-
流池
-
四、存储空间
从小到大依次为:
数据块,分区,段,表空间
-
数据块设置的大小应合理
-
分区是一系列连续的数据块的集合
-
数据段是分区的上层组织单位
-
表空间中有多个段,段和文件相对应
五、分区表
-
将一张表的数据存储到不同的表空间
-
优点
-
增强可用性,一个分区坏了,其他分区还可以用
-
均衡I/O,可以把表的不同分区分配到不同的磁盘I/O来平衡I/O改善性能
-
提高性能,对大表的查询,增加,修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快
-
分区对用户透明,用户感觉是同一张表
-
-
分类:按范围分区,hash分区,复杂分区
-
建立分区
-- 1.建立3个表空间
CREATE TABLESPACE tb1 DATAFILE 'tb1.dbf' SIZE 1m;
CREATE TABLESPACE tb2 DATAFILE 'tb2.dbf' SIZE 1m;
CREATE TABLESPACE tb3 DATAFILE 'tb3.dbf' SIZE 1m;
-- 2.建立分区表,
-- 根据范围分区
CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
PARTITION BY RANGE(money)
(
PARTITION p1 VALUES LESS THAN (1500) TABLESPACE tb1,
PARTITION p2 VALUES LESS THAN (3000) TABLESPACE tb2,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tb3
);
-- hash分区
CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
PARTITION BY HASH(money)
(
PARTITION p1 TABLESPACE tb1,
PARTITION p2 TABLESPACE tb2,
PARTITION p3 TABLESPACE tb3
);
-- 3.插入数据
INSERT INTO t_area VALUES(sq.nextval,1000);-- 将插入到分区p1
INSERT INTO t_area VALUES(sq.nextval,2000);-- 将插入到分区p2
INSERT INTO t_area VALUES(sq.nextval,5000);-- 将插入到分区p3
-- 4.查询数据,在相应分区查询相应的money
SELECT * FROM t_area PARTITION(p1);-- 查询p1分区
SELECT * FROM t_area PARTITION(p2);-- 查询p2分区
SELECT * FROM t_area PARTITION(p3);-- 查询p3分区
-- 查询6000工资员工的信息,在p3分区查找
select * from t_area partition(p3) where money=6000;
-- 内容,分区都删除
ALTER TABLE yourTable DROP PARTITION partionName1;
-- 只清除数据
ALTER TABLE yourTable TRUNCATE PARTITION partionName1; -
为分区建立索引
-
全局索引
create index 索引名称 on 表名(字段) global
partition by range (字段)
(
partition index_part1 values less than (60),
partition index_part2 values less than (80),
partition index_partmax values less than (maxvalue)
); -
局部索引
create index 索引名 on 表名 (字段1, 字段2) local;
-
六、union和union all
-
union和union all的要求
-
每条sql的列数相同
-
每条sql的列的类型要相互匹配,char对char,number对number
-
-
区别
union结果没有重复的,union all结果重复
七、导入导出
-
导出
-
pl/sql导出:tools->export
-
命令行导出:
exp scott/scott@orcl file="d:/1.sql"
-
-
导入
-
pl/sql导入:tools->import
-
命令行导入:
imp scott/scott@orcl file="d:/1.sql" full=y
-- 成功终止导入,说明导入成功
-
八、SQL优化
-
写出具体的列名,不写*,减少SQL的分析时间,不用再去数据字典中找列的信息。
-
创建索引
-
对索引列的过滤时,不用函数,不要隐匿转换数据类型,不用模糊匹配开头查询
-