Oracle SQL基础

Oracle


一、查询语句
1.SELECT
语法：
SELECT column, 


group_function
FROM table
[WHERE condition]
[GROUP BY 


group_by_expression]
[HAVING group_condition]
[ORDER BY 


column];


用于查询数据
SELECT * FROM stu0809;#查询表中所


有数据
SELECT sid, sname, age FROM stu0809;#查询表中特定数据
SELECT sid, sname, age+1 FROM stu0809;#查询并进行运算


SELECT * 
 FROM cat;#查出当前数据库中所有用户表


理解列别名与表别名




eg: SELECT sid, sname, age AS a FROM stu0809 WHERE a > 20;




#ORA-00904: "A": invalid identifier
SELECT sid, sname, age AS 


a FROM stu0809 WHERE stu0809.a>20;
#ORA-00904: 


"STU0809"."A": invalid identifier
SELECT sid, sname, age AS 


a FROM stu0809 s WHERE s.a>20;
#ORA-00904: "S"."A": invalid 


identifier


SELECT sid, sname, a FROM (SELECT sid, sname, age 
AS a FROM stu0809 WHERE age > 20);
结论：表别名可以在当前层
级使用，而列别名是提供给上一层次使用的


2.WHERE
用于进行选择操作
，逻辑比较运算符：< > = >= <= != <>
SELECT * FROM 
stu0809 WHERE age>=24;
SELECT sid, sname FROM stu0809 WHERE age>=24;
并
不是age没在SELECT后出现就不能使用


SELECT * FROM stu0809 WHERE 


sname='illu';


逻辑运算符：
SELECT * FROM stu0809 WHERE 


scid=1 AND sname='illu';
SELECT * FROM stu0809 WHERE scid=1 OR 


(scid=2 AND age>15);


BETWEEN...AND...
SELECT * FROM stu0809 


WHERE age BETWEEN 25 AND 55;# age>=25 AND age<=55
SELECT * FROM 


stu0809 WHERE age>25 AND age<55;


IN
不是一个
区间概念，而是一个枚举概念
SELECT * FROM stu0809 WHERE sid IN
(1,5,3);
SELECT * FROM stu0809 WHERE sid=1 OR sid=5 OR sid=3;






LIKE&ESCAPE
用于进行模糊查找_代表有且只有一个字符
%代表0个
到无限多个字符
字符是大小写敏感的
ESCAPE 当数据中存在有%或_的数据时


，若要使用LIKE需要使用\%或\_进行转义


SELECT * FROM stu0809 


WHERE sname LIKE '%l%';
SELECT * FROM stu0809 WHERE sname LIKE 


'ill_';


若ename中存在 n%cy t%m j%ck这样的数据，现在查找


ename中含有%的所有数据
SELECT * FROM stu0809 WHERE sname LIKE '%\%%' 


ESCAPE '\';
第一个%代表字符%前的任意数据
第二个%代表字符%




第三个%代表字符%后的任意数据


IS NULL & IS NOT NULL
用于查找


为NULL的数据
SELECT * FROM stu0809 WHERE scid IS NULL;
SELECT * 


FROM stu0809 WHERE age IS NOT NULL;


3.GROUP BY & HAVING




GROUP BY
用于对信息进行分组
使用GROUP BY语句


中的SELECT后只能跟GROUP BY的列和分组函数






HAVING
用于对GROUP BY后的数据进行选择操作
HAVING中


可以使用分组函数，而WHERE不能使用
不能使用HAVING进行表的连


接操作，WHERE是对GROUP BY前的数据进行选择，而HAVING是之后






SELECT age,COUNT(*) FROM stu0809 WHERE age IS NOT NULL GROUP BY age 


HAVING count(*)>0 ORDER BY age desc;
SELECT * FROM stu0809 


GROUP BY age;#Error
SELECT age FROM stu0809, clazz0809 GROUP 


BY age HAVING scid=cid;#Error


列出班级名称和该


班级学生平均年龄


SELECT c.cname, sub.avgValue FROM




(
SELECT scid, avg(age) avgValue FROM stu0809 




WHERE scid IS NOT NULL GROUP BY scid
) sub , clazz0809 c




WHERE c.cid=sub.scid;






4.ORDER BY
用于排序, 


默认为ASC升序，可设置为DESC降序
SELECT * FROM stu0809 ORDER BY 


sid, sname DESC;#等同于
SELECT * FROM stu0809 ORDER BY sid ASC, 


sname DESC;


NULL排序在ASC的最后，而在DESC的最前
SELECT * FROM 


stu0809 ORDER BY scid;(or scid DESC);


二、常用关键字及方法
.连接符 


||
eg:
SELECT 'name is '||sname||' and age is '||age FROM 


stu0809;
2.NVL
判断是否为空，为空则显示需要显示的内容
eg:
SELECT 


sid, sname, age ,NVL(cname,'no class') FROM stu0809, clazz0809 WHERE cid


(+)=scid;


3.DISTINCT
SELECT DISTINCT * FROM stu0809;
SELECT 


DISTINCT age FROM stu0809;


4.转换大小写方法
LOWERUPPERINITCAP




SELECT LOWER('illU') FROM dual;
SELECT UPPER('illU') FROM dual;




SELECT INITCAP('illU') FROM dual;


5.字符操纵方法
CONCATSUBSTR


LENGTHREPLACE
CONCAT将字符串进行连接
SELECT CONCAT


('GOOD','MORNING') FROM dual;
SELECT CONCAT(sname,' concated') FROM 


stu0809;


SUBSTR 截取字符串


SELECT sid, 


SUBSTR(sname,1,3) FROM stu0809;


LENGTH 得到字符串长度






SELECT sid, LENGTH (sname) FROM STU0809;


REPLACE 替换字符串






SELECT sid, REPLACE(sname,'a','A') FROM stu0809;#将sname中的a改成A


6.ROUND&TRUNC
ROUND 四舍五入
TRUNC 截取


SELECT ROUND


(123.456)FROM dual;--123
SELECT TRUNC(123.456) FROM dual;


--123
SELECT ROUND(123.456,2) FROM dual;--123.46
SELECT 


TRUNC(123.456,2) FROM dual;--123.45
SELECT ROUND(153.456,-2) 


FROM dual;--200
SELECT TRUNC(123.456,-2) FROM dual;--100




关于第二个参数：如没有，则代表0，即截取到个位；如果为正数，向右截取；如果


为负数，向左截取


7.TO_CHAR
将数字转换成字符，并且可以进行格式化




SELECT TO_CHAR(123.4,'000,000,000.000') FROM dual;
--


000,000,123.400
SELECT TO_CHAR(123.4,'fm000,000,000.000') FROM 


dual;
--000,000,123.400
SELECT TO_CHAR(123.4,'999,999,999.999') 


FROM dual;
--123.400
SELECT TO_CHAR(123.4,'fm999,999,999.999') 


FROM dual;
--123.4


8.TO_NUMBER
将字符串转换成数字
SELECT 


TO_NUMBER('123456.78') FROM dual
SELECT TO_NUMBER('AAA') FROM 


dual#Error


三、连接查询
1.等值连接
SELECT 


sid,sname,age,cname FROM stu0809, clazz0809 WHERE scid=cid;




SELECT sid,sname,age,cname FROM stu0809 join clazz0809 on scid=cid;






2.外连接
使用(+)
SELECT 


sid,sname,age,cname FROM stu0809, clazz0809 WHERE cid(+)=scid;




SELECT sid,sname,age,cname FROM stu0809, clazz0809 WHERE scid(+)=cid;






SELECT sid,sname,age,cname FROM clazz0809, stu0809 WHERE cid(+)=scid;




结论：
使用(+)进行外连接时，与FROM后表名顺序无关，而(+)所在的另


一边代表的表一条记录都不会少


使用left join 或者 right join




SELECT sid,sname,age,cname FROM stu0809 LEFT JOIN clazz0809 ON scid=cid;




SELECT sid,sname,age,cname FROM stu0809 LEFT JOIN clazz0809 ON 


cid=scid;


SELECT sid,sname,age,cname FROM clazz0809 LEFT 


JOIN stu0809 ON scid=cid;
SELECT sid,sname,age,cname FROM 


clazz0809 RIGHT JOIN stu0809 ON scid=cid;
结论：




使用JOIN进行外连接时，LEFT JOIN则JOIN左边的表一条记录都不会少，而RIGHT 


JOIN则是JOIN右边的表一条记录都不会少




四、oracle分页查询
rownum:伪


字段
代表结果集中的记录序列数
为什么使用rownum>n结果集为空？




假设使用表stu0809 里面有6条记录
现在用rownum < 3的条件来过滤




第一行，rownum初始分配为1，满足rownum <3的条件，然后rownum加1
第二行，


rownum现在为2,满足 < 3的条件,然后rownum加1
……


然后用 rownum > 2


的条件来过滤
现在看第一行，rownum初始分配为1， 不满足rownum>2的条


件,rownum不会加1
第二行，rownum还是1，同样不满足>2的条件




所以，最后的查询结果，一条数据也没有


oracle分页查询版本一：
SELECT 


sid, sname, age FROM 
(SELECT ROWNUM r, sid, sname, age FROM stu0809




WHERE ROWNUM<=20)
WHERE r>=1;
解决了分页的问题，但是不能按排序


后的顺序进行分页


oracle分页查询版本二：
SELECT sid, sname, age 


FROM 
(SELECT ROWNUM r, sid, sname, age FROM 
(SELECT * FROM 


stu0809 ORDER BY age)
WHERE ROWNUM<=20)
WHERE r>=1;






、数据类


型
varchar2(size)  变化    oracle特有的数据类型
varchar(size)  变


化  标准的数据类型
char(size)定长   标准的数据类型
number


整数，浮点数


date日期
系统时间 sysdate




select sysdate from dual;
create table my_table (hiredate date);




新增时间 TO_DATE
insert into my_table (hiredate) values (to_date


('2008-8-8','YYYY-MM-DD'));
insert into my_table (hiredate) values 


(to_date('2008-8-8 20:30:45','YYYY-MM-DD HH24:MI:SS'));


操作时间




DATE - DATE
SELECT sysdate - hiredate from my_table;






MONTHS_BETWEEN(DATE,DATE)
SELECT MONTHS_BETWEEN(sysdate, hiredate) 


FROM my_table;


TO_NUMBER(TO_CHAR(DATE,'YYYY'))-TO_NUMBER


(TO_CHAR(DATE,'YYYY'))


显示时间 TO_CHAR
SELECT TO_CHAR


(hiredate,'MON DAY YYYY') FROM my_table WHERE TO_CHAR(hiredate,'MM')=8;




六、数据定义语言 DDL
TABLE 表
1.创建 CREATE
CREATE 


TABLE my_table(...);


CREATE TABLE my_table AS 


...;


2.修改 ALTER


3.删除 DROP




DROP TABLE my_table;


VIEW 视图
CREATE OR REPLACE VIEW 


my_view AS ...;
DROP VIEW my_view;
来源于一
张表的视图能进行DML操作，而来源于多张表的视图不能进行DML操作
create 
view view_test_illu as select * from stu0809 order by sid;


SEQUENCE 


序列
CREATE SEQUENCE my_seq;
SELECT 
my_seq.NEXTVAL FROM dual;
SELECT my_seq.CURRVAL FROM dual;




DROP SEQUENCE my_seq;






CONSTRAINT 约束
PRIMARY 


KEY主键
FOREIGN KEY外键
NOT NULL非空
UNIQUE


唯一
CHECK检查




PRIMARY KEY
保证记录唯一
主键约束


，唯一且非空
并且每一个表中只能有一个主键


联合主键：将两个字段组合


在一起唯一标示记录
CREATE TABLE test_primary_illu(




id NUMBER(11) PRIMARY KEY,
...
);


CREATE TABLE 


test_primary_illu(
id NUMBER(11),
...




CONSTRAINT primary_pk_illu PRIMARY KEY (id)
);


FOREIGN KEY




表示了两个关系之间的联系
CREATE TABLE test_foreign_illu(




...
tcid NUMBER(11) CONSTRAINT foreign_fk_illu REFERENCES 


test_primary_illu(id)
);


CREATE TABLE test_foreign_illu(




...
tcid NUMBER(11) ,
CONSTRAINT 


foreign_fk_illu FOREIGN KEY(tcid) REFERENCES test_primary_illu(id)




);


NOT NULL
非空约束
CREATE TABLE test_notnull_illu(




sname VARCHAR2(20) NOT NULL,
cname VARCHAR2(20) 


CONSTRAINT not_null_cname NOT NULL 
);


UNIQUE
唯一约束




CREATE TABLE test_unique_illu(
id NUMBER(11) UNIQUE
);






CREATE TABLE test_unique_illu(
id NUMBER(11) CONSTRAINT 


unique_test_illu UNIQUE
);


联合唯一约束
CREATE 


TABLE test_unique_illu(
sid NUMBER(11),




cid NUMBER(11),
CONSTRAINT unique_test_illu UNIQUE(sid, 


cid)
);


INSERT INTO test_unique_illu VALUES(1,1);
INSERT 


INTO test_unique_illu VALUES(1,2);
INSERT INTO test_unique_illu 


VALUES(2,1);
INSERT INTO test_unique_illu VALUES(1,1);--Error




七、数据操作语言 DML
1.新增
INSERT INTO stu0809 (sid, sname, 


age, scid) VALUES (1,'illu',10,1);
INSERT INTO stu0809 


VALUES(1,'illu',10,1);
INSERT INTO stu0809 (sid, sname, scid) 


VALUES (1,'illu',1);


2.修改
UPDATE stu0809 SET age=21 WHERE 


sid=2;
UPDATE stu0809 SET age=21, scid=3 WHERE sid=2;






3.删除
DELETE FROM stu0809;
DELETE FROM stu0809 WHERE 


sid=1;
DELETE FROM stu0809 WHERE sname like 'Zhang%';


、一对


多案例
CREATE TABLE clazz(
cid NUMBER(11),




cname VARCHAR2(30) NOT NULL,
CONSTRAINT pk_clazz_illu PRIMARY 


KEY(cid)
);


CREATE TABLE STUDENT(
sid NUMBER(11),




sname VARCHAR2(20) NOT NULL,
age NUMBER(3),




scid NUMBER(11),
CONSTRAINT pk_student_illu PRIMARY KEY


(sid),
CONSTRAINT fk_student_clazz_illu FOREIGN KEY(scid) 


REFERENCES clazz(cid)
);


创建表
先创建clazz表，再创建student表






删除表
先删除student表，再删除clazz表


新增记录
INSERT 


INTO clazz VALUES(1,'clazz1');
INSERT INTO student VALUES


(1,'illu',10,1);
INSERT INTO student VALUES(2,'satan',20,1);




INSERT INTO student VALUES(3,'jojo',30,1);


删除记录
DELETE 


FROM student WHERE scid=1;
DELETE FROM clazz WHERE cid=1;


九、扩展




UNION
UNION指令的目的是将两个SQL语句的结果合并起来。
前后SQL语句中的显


示列数必须一致，而且类型也要一直
UNION会过滤重复记录，而UNION ALL
不会
UNION ALL效率更高，建议使用
SELECT 1 FROM dual
UNION 


SELECT 2 FROM dual
UNION SELECT 4 FROM dual
UNION SELECT 4 


FROM dual;


SELECT 1 FROM dual
UNION ALL SELECT 2 FROM dual




UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 4 FROM dual;


树状查询




oracle用表的形式组织数据，某些数据还呈现树状结构，oracle提供了对树状结构


数据的组织、查询等功能。


select
connect by {prior col1 = col2 || 


col1 = prior col2 }
[start with ]
connect by子句说明数据按照层次顺


序检索，并将数据连入树形结构关系中
prior运算符必须放在连接关系的两列


中某一列的前面，从而确定查找顺序是自上而下还是自下而上，连接关系中，可使


用列名、列表达式
start with 子句为可选项，用来标识查找结构的根节点


。若省略，则表示所有满足条件的行作为根节点


使用level 
节点所处


位置不同，每行记录都有一个层号，层号有节点与根节点的距离决定。无论从哪个


节点开始，该其实根节点的层号始终为1，依次类推




CREATE TABLE 


tree_illu (
nid NUMBER(11) PRIMARY KEY,
name 


VARCHAR2(20) NOT NULL,
pid NUMBER(11)
);


INSERT INTO 


tree_illu VALUES(1,'root node',0);
INSERT INTO tree_illu VALUES


(2,'java',1);
INSERT INTO tree_illu VALUES(3,'.net',1);
INSERT 


INTO tree_illu VALUES(4,'java EE',2);
INSERT INTO tree_illu VALUES


(5,'java SE',2);
INSERT INTO tree_illu VALUES(6,'core java',5);






从根结点遍历树状表
SELECT nid, name, level FROM tree_illu
START 


WITH nid=1
CONNECT BY prior nid = pid
ORDER BY level;






查找java结点的子结点
SELECT nid, name FROM tree_illu
WHERE 


pid=(
SELECT nid FROM tree_illu
WHERE name='java'
)