Oracle

第二章 select,null,distinct

第三章 where子句和order by子句

第四章 单行函数

1.字符函数---upper,lower,initcap    concat,substr,length,instr,lpad,rpad,replace,trim

2.数值函数-- round(exp,n)四舍五入 trunc(exp,n)截取  mod(m,n)取余

3.日期函数--sysdate,months_between,add_months,next_day,last_day,round,trunc,extract

4.转换函数--to_char(),to_number(),to_date()

5.通用函数--nvl,nvl2,nullif,coalesce  条件处理函数：case表达式，decode

 

 

Oracle整理文档：

第二章：编写简单的查询语句

 

结构化查询语言简介：

结构化查询语言（Structured Query Language） 简称SQL

 

SQL的发展：

1.1974年首次提出，SEQUEL

2.1980年改名为SQL

3.1986年ANSI定义关系数据库语言的标准，并公布了标准SQL

4.1992年------SQL-92

5.1999年，发布标准SQL99标准~~~用的比较多

6.2003年，发布SQL2003标准

 

 

SQL的分类：

1. 数据查询语言（DQL：Data Query Language）：语句主要是 SELECT

2. 数据操作语言（DML：Data Manipulation Language）：

语句主要包括 INSERT,UPDATE和DELETE，用于添加，修改和删除表中的行数据

3. 事务处理语言（TPL：Transaction Process Language）：

    语句主要包括COMMIT和ROLLBACK，用于提交和回滚

4. 数据控制语言（DCL：Data Control Language）：

    语句主要包括GRANT和REVOKE，用于进行授权和收回权限

5. 数据定义语言（DDL：Data Definition Language）：

语句主要包括CREATE、DROP、ALTER，用于定义、销毁、修改数据库对象

 

SQL书写规则：

1. 不区分大小写

2. 可以单行书写，也可以多行书写，建议多行书写，增强代码可读性

3. 关键字不要缩写，分开和跨行

4. 关键字最好大写，其他如表名、列名等其他语法元素小写

5. 缩进和换行的使用提高可读性

 

例子：

两种查询部门表的方式

1.查询部门表信息

select * from dept;  

--这种查询方式执行效率低于第二种

select deptno,dname,loc from dept;

--因为这种方法指定了列名，所以数据库不需要从头找，直接找到列名就好

 

 

 

空值NULL

  空值是指一种无效的、未赋值、未知的或不可用的值

  空值不同于零或空格

（任何包含空值的算术表达式运算后的结果都为空值NULL）

 

例子：

--查询员工表中的工资和一年的工资

select sal 月薪,sal*12+comm 年薪 from emp

----这里的年薪是错误的，因为奖金comm如果为空值的时候，年薪计算出来也会为空值

 

那么如何解决这种问题呢？

 

我们使用NVL(express1,express2)这种函数

如果express1不为空值，那么返回它自己，如果它为空值，返回express2

 

SELECT sal 月薪,sal*12+NVL(comm,0) 年薪 FROM emp

 

 

 

列别名

语法：1， 列名 列别名

      2， 列名 AS 列别名

 

其中以下情况需要在列别名的两侧加上双引号

 

--列别名中包含有空格

SELECT sal "MAN SAL" FROM emp

 

--列别名中要求区分大小写

SELECT sal "ManSal" FROM emp

 

--列别名中包含有特殊字符

SELECT sal "MAN&*" FROM emp

 

 

连接操作符

--用于连接列于列，列和字符

--形式上是两个||

--用于创建字符表达式的结果列

 

SELECT sal||ename 工资和员工名字,empno||'MONDAY' FROM emp

 

 

原义字符串

--原义字符串是包含在SELECT列表中的一个字符，一个数字或一个日期

--日期和字符字面值必须用单引号引起来

--每个原义字符串都会在每个数据航输出中出现

 

SELECT ename||'is a'||job FROM emp

 

 

消除重复行（distinct）

 

查询员工表中的部门编号，不要重复显示

SELECT DISTINCT deptno FROM emp

 

 

 

显示表的结构

在command window中输入

 (desc tablename)

如：

  DESC emp

 

 

 

 

 

 

 

第三章：限制数据和对数据排序

1. where子句紧跟在from 子句中

语法格式：列名  比较操作符  要比较的值

      如：  WHERE sal>1000;

             WHERE ename='SCOTT'

WHERE hiredate>to_date('1982-02-01','yyyy-mm-dd')

  比较操作符： 等于 = 、大于 >、大于或等于 >=、小于 <、小于或等于 <=、不等于 <>

比较数值型数据时，直接比较，自交字符型数值型是，要用单引号引起来，比较日期型时要注意比较的格式

 

 

2. 特殊比较运算符

   BETWEEN....AND....  判断比较的值是否在某个范围内

   WHERE sal BETWEEN 1000 AND 2000;

 

   IN   判断要比较的值是否在集合列表中的任何一个值相等

   WHERE sal IN (1000,2000,3000)

 

   LIKE 判断要比较的值是否满足部门匹配

   WHERE ename LIKE '%S%'

 

   IS NULL    判断要比较的值是否为空值NULL

WHERE sal is null

 

 

3.逻辑运算符

  AND 逻辑与，用来连接多个表达式，如果每个都返回真，整个表达式的结果才是真

  WHERE sal>=100 AND jon='CLERK'

 

  OR  逻辑或，用来连接多个条件表达式，只要有一个条件的结果是真，整个都是真

WHERE sal>=100 OR  job='CLERK'

 

  NOT 逻辑非，用来对条件表达式取反

WHERE job NOT IN('CLERK','MANAGER')

 

 

4. 运算符的优先级

  1.算术运算符>2.连接运算符>3.比较运算符>4.特殊运算符>5.NOT>6.AND>7.OR

  使用括号可以强制改变优先级

 

 

5. 排序表达式

语法：ORDER BY 列名、表达式、列别名、结果集的列序号  排序

ASC:升序，默认值   DESC：降序

 排序规则：

  1.数字按照从小到大排列

  2.日期按照较早的在前

  3.字符按照A~Z

  4.空值在升序排列中排在最后，在降序排列中排在最开始

 

 

 

第四章：单行函数

单行函数输入单行，输出单行

多行函数输入多行，输出单行

 

字符函数：

1. 大小写转换： LOWER :将大写或大小写混合的字符全部转换成小写

                UPPER：将小写或大小写混合的字符全部转换成大写

                INITCAP：将每个单词的第一个字母转换成大写，其余的字母都转换成小写

2. 字符处理：   

CONCAT(exp1,exp2)：连接两个值，相当于||

SELECT CONCAT(empno,ename) FROM emp

 

SUBSTR(exp,n1[,n2])：返回第一个参数中，从第n1位开始，长度为n2的子串

SELECT SUBSTR(ename,1,2) FROM emp

 

LENGTH(exp1):取字符长度

 

INSTR(s1,s2[,n1][,n2]):返回s1中，子串s2从n1开始，第n2次出现的位置，默认1

SELECT INSTR('ENAME','A',1,1) FROM DUAL    --输出3

 

LPAD：左填充

LPAD(sal,10,'*')    --******5000

 

RPAD：右填充

RPAD(sal,10,'*')    --5000******

 

 

TRIM：截断

TRIM('S' FROM 'SSMITH')    --MITH

 

REPLACE：替换

REPLACE('abc','b','d')     --adc

 

 

 

数值函数

ROUND(exp,n):四舍五入

ROUND(45.926,2)      --45.93

 

TRUNC(exp,n)：截取

TRUNC(45.926,2)      --45.92

 

MOD(m,n)：取m除以n后得到的余数

MOD(1600,300)        --100

 

 

 

日期函数

SYSDATE:返回当前系统日期

MONTHS_BETWEEN:返回两个日期类型数据之间间隔的自然月数

MONTHS_BETWEEN(SYSDATE,hiredate)

 

ADD_MONTHS:返回指定日期加上相应的月数后的日期

ADD_MONTHS(hiredate,3)

 

NEXT_DAY:返回某一日期的下一个指定日期

SELECT NEXT_DAY('09-2月-06','星期一') FROM dual

 

LAST_DAY:返回指定日期当月最后一天的日期

SELECT LAST_DAY('09-2月-06') FROM dual

 

ROUND:将日期四舍五入为最近的一天

 

TRUNC:截取为最近的一天

 

EXTRACT:返回日期类型数据中的年份、月份或者日

EXTRACT(month FROM hiredate) MONTH

 

 

 

 

转换函数

TO_CHAR(DATE)---->CHARACTER

to_date(hiredate,’MM/YY’)

YYYY--完整地年份

YEAR--年份的英文表示

MM--用两位数字来表示月份

MONTH--月份的全名

DAY--星期几

DY--用3个英文字符缩写来表示星期几

 

TO_CHAR(CHARACTER)---->NUMBER

to_char(sal,’$99,999’)

9表示一位数字，0显示前导零，$显示美元符号，L显示本地货币符号，.显示小数点，

 

TO_NUMBER(NUMBER)--->CHARACTER

TO_DATE(CHARACTER)--->DATE

 

 

通用函数

NVL（exp1,exp2）

如果1不是null，返回exp1,否则返回exp2

 

NVL2(exp1,exp2,exp3)

如果exp1不是null,返回exp2，否则返回exp3

 

NULLIF(exp1,exp2)

比较两个表达式，如果相等，返回null，否则，返回第一个表达式

 

COALESCE（exp1,exp2,……exp n）

该函数的功能是返回第一个不为空的参数，参数个数不受限制

 

CASE和DECODE 函数

这两个函数都用在select语句中

CASE语法：

  

CASE   deptno

    WHEN 10 THEN '销售部'

    [WHEN 20 THEN '技术部'

    ELSE '无' ]

END 

 

DECODE函数

DECODE(deptno,10,'销售部',

                         20,'技术部',

                         '无')

第五章：多表连接


  N表连接，需要N-1个条件


按连接条件：等值连接，非等值连接
按其他连接方法分：外部连接，内部连接


笛卡尔积：如果不在where子句中添加连接条件，就会产生笛卡尔积


等值连接：
非等值连接：
外部连接：
自身连接：




SQL99标准的连接语法：
交叉连接：CROSS JOIN


自然连接：tableName1 NATURAL JOIN tableName2


USING子句: emp e JOIN dept d USING(deptno)




ON子句：emp e JOIN dept d ON e.deptno=d.deptno


左外连接：
emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno


右外连接
emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno


全外连接：
emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno






第六章：分组函数
五个常用分组函数：MIN() MAX() SUM() AVG() COUNT()
分组关键字：GROUP BY 


GROUP BY所指定的列并不是必须出现在SELECT 列表中
SELECT AVG(sal) FROM emp GROUP BY deptno;




如果要给组函数添加条件，要放在HAVING 语句中，不可以放在WHERE 子句中




SELECT 语句执行过程：
 FROM-->WHERE--->GROUP BY--->HAVING-->SELECT -->ORDER BY


组函数只能嵌套两层




第七章：子查询


1.子查询要用括号括起来，括号内的先查询，得到的结果被主查询使用
2.单行运算符：>、=、>=、<、<>、<=
  多行运算符： IN 、 ANY 、 ALL


3.子查询出现位置：WHERE  HAVING  FROM 
4.使用子查询规则：
   子查询要用括号括起来；
   将子查询放在比较运算符的右边
   对于单行子查询要使用单行运算符
   对于多行子查询要使用多行运算符
5.子查询只返回一行一列






6.ROENUM是一个伪列。实际上不存在而且没有实际存储意义的特殊列
  但是可以记录行号


 6.1对于ROWNUM只能执行<,<=运算，不能执行>，>=或一个区间运算BETWEEN AND 等，因为如果是>3，第一次，大于3的会被删除，然后伪列重新排序，又删掉<=3的，然后伪列重新排序，……最后全部删掉为止


 6.2 当ROWNUM和ORDER BY 一起使用时。需要注意ROWNUM 实际上是已经被排了序的ROWNUM








第八章：集合运算


联合运算：（UNION）
联合运算就是两者全部都显示出来，但是两者重叠部分只会显示一次


完全联合运算：（UNION ALL）
完全联合就是两者全部都显示出来，就像两个不重叠一样，如果有重叠部分，那么重叠部分显示两次


相交（INTERSECT）:
相减(MINUS)：






第十章：层次查询
语法：
  SELECT [LEVEL,] column,exp
  FROM table 
  [WHERE condition(s)]
  [START WITH condition(s)]
  [CONNECT BY PRIOR condition(s)];


LEVEL:节点的层次，伪列，由查询的起点开始算起为1，依此类推
FROM table:指定表、视图或包含列的快照，只能从单独一个表中选择
WHERE:限制返回的行
Condition:是一个比较式
START WITH：指定层次的根（起点），这个子句对于一个正确的分级查询是必须的
CONNECT BY PRIOR：指定存在父与子行的关系列，对于分级查询来说该子句也是必须的




用LEVEL和LPAD生成分级报告
1.创建一个报告显示公司的管理层，从最高级别开始，缩进下面跟随的级别


例子：
SELECT LPAD(ename,Length(ename)+(LEVEL*2)-2,'*')
       AS org_chart
FROM emp
START WITH ename='KING'
CONNECT BY PRIOR empno=mgr


修剪分支：
在WHERE 子句中使用限制条件，只可以修剪一个节点，
在CONNECT BY子句中使用限制条件，可以去除一个分支






第十一章：数据操作与事务控制
1.数据操作语言：
Data Manipulation Language ,简称DML
增:INSERT
修改：UPDATE
删除：DELETE