SQL_oracle串讲

Oracle串讲

一、查询语句
 1.SELECT
 语法：
 SELECT column, group_function
 FROM table
 [WHERE condition]
 [GROUP BY group_by_expression]
 [HAVING group_condition]
 [ORDER BY column];

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

  SELECT * FROM cat;#查出当前数据库中所有用户表
 
 理解列别名与表别名
 eg:
  SELECT sid, sname, age AS a FROM stu0902 WHERE a > 20;
  #ORA-00904: "A": invalid identifier
  SELECT sid, sname, age AS a FROM stu0902 WHERE stu0902.a>20;
  #ORA-00904: "STU0902"."A": invalid identifier
  SELECT sid, sname, age AS a FROM stu0902 s WHERE s.a>20;
  #ORA-00904: "S"."A": invalid identifier

  SELECT sid, sname, a FROM (SELECT sid, sname, age AS a FROM stu0902 WHERE age > 20);
  结论：表别名可以在当前层级使用，而列别名是提供给上一层次使用的
 
2.WHERE
 用于进行选择操作，
 
 逻辑比较运算符：< > = >= <= != <>
 SELECT * FROM stu0902 WHERE age>=24;
 SELECT sid, sname FROM stu0902 WHERE age>=24;#并不是age没在SELECT后出现就不能使用

 SELECT * FROM stu0902 WHERE sname='illu';
 
 逻辑运算符：
 SELECT * FROM stu0902 WHERE scid=1 AND sname='illu';
 SELECT * FROM stu0902 WHERE scid=1 OR (scid=2 AND age>15);

 BETWEEN...AND...
 SELECT * FROM stu0902 WHERE age BETWEEN 25 AND 55;# age>=25 AND age<=55
 SELECT * FROM stu0902 WHERE age>25 AND age<55; 
 
 IN
 不是一个区间概念，而是一个枚举概念
 SELECT * FROM stu0902 WHERE sid IN(1,5,3);
 SELECT * FROM stu0902 WHERE sid=1 OR sid=5 OR sid=3;

 LIKE&ESCAPE
 用于进行模糊查找
 _代表有且只有一个字符
 %代表0个到无限多个字符
 字符是大小写敏感的
 ESCAPE 当数据中存在有%或_的数据时，若要使用LIKE需要使用\%或\_进行转义
 
 SELECT * FROM stu0902 WHERE sname LIKE '%l%';
 SELECT * FROM stu0902 WHERE sname LIKE 'ill_';
 
 若ename中存在 n%cy t%m j%ck这样的数据，现在查找ename中含有%的所有数据
 SELECT * FROM stu0902 WHERE sname LIKE '%\%%' ESCAPE '\';
 第一个%代表字符%前的任意数据
 第二个%代表字符%
 第三个%代表字符%后的任意数据
 
 IS NULL & IS NOT NULL
 用于查找为NULL的数据
 SELECT * FROM stu0902 WHERE scid IS NULL;
 SELECT * FROM stu0902 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 stu0902 WHERE age IS NOT NULL GROUP BY age HAVING count(*)>0 ORDER BY age desc;
  SELECT * FROM stu0902 GROUP BY age;#Error
  SELECT age FROM stu0902, clazz0902 GROUP BY age HAVING scid=cid;#Error
  
  列出班级名称和该班级学生平均年龄

  SELECT c.cname, sub.avgValue FROM
  (
  SELECT scid, avg(age) avgValue FROM stu0902
  WHERE scid IS NOT NULL GROUP BY scid
  ) sub , clazz0902 c
  WHERE c.cid=sub.scid;

  
 4.ORDER BY
 用于排序, 默认为ASC升序，可设置为DESC降序
 SELECT * FROM stu0902 ORDER BY sid, sname DESC;#等同于
 SELECT * FROM stu0902 ORDER BY sid ASC, sname DESC;

 NULL排序在ASC的最后，而在DESC的最前
 SELECT * FROM stu0902 ORDER BY scid;(or scid DESC);
 
二、常用关键字及方法
1.连接符 ||
 eg:
 SELECT 'name is '||sname||' and age is '||age FROM stu0902;
2.NVL
 判断是否为空，为空则显示需要显示的内容
 eg:
 SELECT sid, sname, age ,NVL(cname,'no class') FROM stu0902, clazz0902 WHERE cid(+)=scid;

3.DISTINCT
 SELECT DISTINCT * FROM stu0902;
 SELECT DISTINCT age FROM stu0902;

4.转换大小写方法
 LOWER UPPER INITCAP
 SELECT LOWER('illU') FROM dual;
 SELECT UPPER('illU') FROM dual;
 SELECT INITCAP('illU') FROM dual;

5.字符操纵方法
 CONCAT SUBSTR LENGTH REPLACE
 CONCAT将字符串进行连接
 SELECT CONCAT('GOOD','MORNING') FROM dual;
 SELECT CONCAT(sname,' concated') FROM stu0902;
 
 SUBSTR 截取字符串
 
 SELECT sid, SUBSTR(sname,1,3) FROM stu0902;

 LENGTH 得到字符串长度
 
 SELECT sid, LENGTH (sname) FROM STU0902;

 REPLACE 替换字符串

 SELECT sid, REPLACE(sname,'a','A') FROM stu0902;#将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 stu0902, clazz0902 WHERE scid=cid;
  SELECT sid,sname,age,cname FROM stu0902 join clazz0902 on scid=cid;

 2.外连接
  使用(+)
  SELECT sid,sname,age,cname FROM stu0902, clazz0902 WHERE cid(+)=scid;
  SELECT sid,sname,age,cname FROM stu0902, clazz0902 WHERE scid(+)=cid;

  SELECT sid,sname,age,cname FROM clazz0902, stu0902 WHERE cid(+)=scid;
  结论：
  使用(+)进行外连接时，与FROM后表名顺序无关，而(+)所在的另一边代表的表一条记录都不会少

  使用left join 或者 right join
  SELECT sid,sname,age,cname FROM stu0902 LEFT JOIN clazz0902 ON scid=cid;
  SELECT sid,sname,age,cname FROM stu0902 LEFT JOIN clazz0902 ON cid=scid;

  SELECT sid,sname,age,cname FROM clazz0902 LEFT JOIN stu0902 ON scid=cid;
  SELECT sid,sname,age,cname FROM clazz0902 RIGHT JOIN stu0902 ON scid=cid;
  结论：
  使用JOIN进行外连接时，LEFT JOIN则JOIN左边的表一条记录都不会少，而RIGHT JOIN则是JOIN右边的表一条记录都不会少

四、oracle分页查询
 rownum:伪字段
 代表结果集中的记录序列数
 为什么使用rownum>n结果集为空？
 假设使用表stu0902 里面有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 stu0902
 WHERE ROWNUM<=20)
 WHERE r>=1;
 解决了分页的问题，但是不能按排序后的顺序进行分页

 oracle分页查询版本二：
 SELECT sid, sname, age FROM
 (SELECT ROWNUM r, sid, sname, age FROM
 (SELECT * FROM stu0902 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 stu0902 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 stu0902 (sid, sname, age, scid) VALUES (1,'illu',10,1);
  INSERT INTO stu0902 VALUES(1,'illu',10,1);
  INSERT INTO stu0902 (sid, sname, scid) VALUES (1,'illu',1);

 2.修改
  UPDATE stu0902 SET age=21 WHERE sid=2;
  UPDATE stu0902 SET age=21, scid=3 WHERE sid=2;

 3.删除
  DELETE FROM stu0902;
  DELETE FROM stu0902 WHERE sid=1;
  DELETE FROM stu0902 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'
 )