Oracle

基本语法

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

SQL由下列类别的命令组成：

数据定义语言（DDL操作表结构）:
CREATE、ALTER、DROP

数据操纵语言（DML操作表数据）:
INSERT、DELETE、SELECT、UPDATE

DML语句

student 表 id name
insert into student(id,name) values(1,‘123’);
insert… into
delete from student where id=1;
delete… from
update student set name=‘123’ where id=1;
update… set
select s.* from student s where s.id=1;
select… from

oracle 数据类型

字符、数值、日期时间、RAW/LONG RAW、LOB

字符数据类型

char() （固定长度：1~2000字节）
varchar2() （可变长度：1~4000字节）
long （可变长度：最多2GB）

数值数据类型

可以存储整数、浮点数和实数
最高精度为 38 位

数值数据类型的声明语法：
NUMBER [( p[, s])]
P表示精度，S表示小数点的位数

日期时间

日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒

主要的日期时间类型有：
DATE - 存储日期和时间部分，精确到秒
TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位

伪列

常用的伪列有ROWID和ROWNUM

ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行

ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数。可用于分页查询

SQl操作符

算术操作符

算术操作符用于执行数值计算
+、-、*、/

比较操作符

比较操作符用于比较两个表达式的值
=、!=、<、>、<=、>=
between…and、in、not in、like（_、%占位符）、is null、is not null等

逻辑操作符

逻辑操作符用于组合多个比较运算的结果以生成一个或真或假的结果。
and、or、not

集合操作符

集合操作符将两个查询的结果组合成一个结果
union（合并两个查询结果，去除重复的记录）
uinion all（合并两个查询结果，不去除重复的记录）
intersect（两个查询结果共同的记录）
minus（从第一个结果集中去除第二个结果集中的记录）

连接操作符

连接操作符用于将多个字符串或数据值合并成一个字符串
||

操作符的优先级

SQL 操作符的优先级从高到低的顺序是：
算术操作符 --------最高优先级
连接操作符
比较操作符
NOT 逻辑操作符
AND 逻辑操作符
OR 逻辑操作符 --------最低优先级

SQl 函数

单行函数

单行函数对于从表中查询的每一行只返回一个值
可以出现在 SELECT 子句中和 WHERE 子句中
单行函数可以大致划分为以下几种：

日期函数

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果

ADD_MONTHS(d,n) --时间点d再加上n个月
MONTHS_BETWEEN(x,y) --用于计算x和y之间有几个月
LAST_DAY (d) --返回某个时间值的当月最后一天
ROUND(d) --返回某个时间值年月日(四舍五入)
NEXT_DAY(x,y) --用于计算x时间后第一个星期y的时间
TRUNC(d) --返回某个时间值年月日(直接截取)
EXTRACT(year from date) --用于从一个d或者interval类型中截取到特定的部分 ,如： EXTRACT(YEAR from sysdate)
特殊日期函数
–Y或YY或YYY 年的最后一位，两位，三位
select to_char(sysdate,‘Y’) from dual;
–Q 季度,1-3月为第一季度
select to_char(sysdate,‘Q’) from dual;
select to_char(sysdate,‘MM’) from dual; --MM 月份数
–RM 月份的罗马表示(I,II,III…)
select to_char(sysdate,‘RM’) from dual;
–ww 当年第几周
select to_char(sysdate,‘ww’) from dual;
–w 本月第几周
select to_char(sysdate,‘w’) from dual; 　
–DDD 当年第几天,一月一日为001 ,二月一日032
select to_char(sysdate,‘DDD’) from dual;
select to_char(sysdate,‘DD’) from dual; --DD 当月第几天
select to_char(sysdate,‘D’) from dual; --D 周内第几天 　　　　　　
select to_char(sysdate,‘DY’) from dual; --DY周内星期几　
–hh12 12小时制小时数,hh24 24小时制小时数　　　　　　
select to_char(sysdate,‘hh12’) from dual;
select to_char(sysdate,‘Mi’) from dual; --Mi 分钟数
select to_char(sysdate,‘ss’) from dual; --ss 秒数
–把日期转换为字符串
select to_char(sysdate,‘YYYY-MM-DD HH24:mi:ss’) from dual;

数字函数

数字函数接受数字输入并返回数值结果

– abs(n) 绝对值
select abs(-15) from dual;
– ceil(n) 取大于它的最小整数
select ceil(3.1415) from dual;
– floor(n) 取小于它的最大整数
select floor(3.1415) from dual;
– power(m,n) 计算m的n次方
select power(4,2) from dual;
– mod(m,n) 求余/取模
select mod(5,2) from dual;
– round(m,n) 四舍五入，保留n为小数
select round(3.1415,3) from dual;
– trunc(m,n) 直接截取，保留n位小数
select trunc(3.1415,2) from dual;
– sqrt(n) 开方根
select sqrt(9) from dual;

字符函数

字符函数接受字符输入并返回字符或数值

– initcap(char) 首字母大写
select initcap(‘hello’) from dual;
– Lower(cahr) 字母变小写
select lower(‘HELLO’) from dual;
– upper(char) 字母变大写
select upper(‘hello’) from dual;
– ltrim(char,set) 左删除
select ltrim(‘zxcabc’,‘zxc’) from dual;
– rtrim(char,set) 右删除
select rtrim(‘zxcdabc’,‘abc’) from dual;
– trim(char) 删除两头空格
select trim(’ zxcabc ') from dual;
– translate(char,from,to) 翻译(替换)（字符级别）
select translate(‘jack’,‘jc’,‘12’) from dual;
– replace(char,searchstring,[rep string])替换（字符串级别）
select replace(‘jack and jue’,‘j’,‘bl’) from dual;
– instr(char,m) 查找字符位置
select instr(‘abcdefg’,‘d’) from dual;
– substr(cahr,m,n) 获取字符串m开始位置，n获取几位
select substr(‘abcdefg’,1,3) from dual;
– concat(expr1,expr2) 字符串连接
select concat(‘abc’,‘def’) from dual;

以下是一些其它的字符函数：

CHR()和ASCII () chr(9)制表符, chr(10)换行符, chr(13)回车符
SELECT CHR(67) FROM dual; --字符C

LPAD (string,n,[pad_string])和RPAD (string,n,[pad_string])将左右边的字符串填充一些特定的字符数
SELECT LPAD(‘function’,15,’=’) FROM dual;

TRIM(string1/number1 from string/number)去除头尾包含的string1/number1
SELECT TRIM(9 from 9999876789999) FROM dual;

LENGTH(str) ‘’和 null 的长度为null
SELECT LENGTH(‘frances’) FROM dual;

DECODE(t,a,b,c,d….) --类似ifelse 当t=a,则返回b,当t=c,则返回d
以些类推

转换函数

转换函数将值从一种数据类型转换为另一种数据类型
常用的转换函数有：
TO_CHAR (9-代表整数,0-代表小数,C-国际货币符号, L(本地货币符号）
TO_DATE
TO_NUMBER

其他函数

以下是几个用来转换空值的函数：

NVL(expr1,expr2)->expr1为NULL返expr2；非NULL返expr1;
NVL2(expr1, expr2, expr3) ->expr1不为NULL返expr2；为NULL返expr3
NULLIF (expr1, expr2) ->相等返NULL(空),不等返回expr1; expr1和 expr2类型必须一致

分组函数

分组函数基于一组行来返回结果
为每一组行返回一个值
avg()
min()
max()
sum()
count()

分析函数

GROUP BY子句
用于将信息划分为更小的组
每一组行返回针对该组的单个结果

HAVING子句
用于指定 GROUP BY 子句检索行的条件(只能是分组的字段或者聚集函数)

ORDER BY子句
ASC 按升序排序
DESC按降序排序

多表查询

连接查询分为两大类，一是使用连接谓词进行连接；二是使用关键词JION进行连接。

连接谓词

使用连接谓词连接表的基本格式：
SELECT <输出字段列表>
FROM 表1，表2 [，…n]
WHERE <表1.字段名1> <连接谓词> <表2.字段名2>

连接字段：必须是可比较的
连接谓词包括：=、<、<=、>、>=、!=、<>

等值连接和不等值连接
当连接谓词是“=”时的连接，称为等值连接。当连接谓词是不等运算符时的连接，称为不等连接。

自然连接
自然连接是特殊的等值连接，要求比较的必须是相同的属性组，并且在结果中报重复的属性列去掉。
在针对多表进行查询时。如果所引用的列为被查询的表所共有，则引用列时必须用表名来指定列是来源于哪个表，指定方法为：表名.列名。反之，不一定需要表名来指定来源。

等值连接：
SELECT * FROM student a, score b
WHERE a.student_id = b.student_id
自然连接：
select * from tab_student a natural join tab_score b;

复合条件连接
在多表连接查询时含有多个连接条件称为复合条件连接。

自连接
一个表与自身进行连接称为自连接。

以JOIN关键字连接

JOIN关键字来连接表的方式，增强了表的连接能力和连接的灵活性。 使用JOIN关键字连接表的的基本格式为：

SELECT <输出字段列表>
FROM 表名1 <连接类型> 表名2 ON <连接条件>
[ <连接类型> 表名3 ON <连接条件>]…

参数说明：
1）表名1，表名2，表名3等用来指明需要连接的表。
2）连接类型有：[ INNER | { LEFT | RIGHE | FULL } OUTER ] JOIN。其中INNER JOIN表示内连接；OUTER JOIN表示外连接，外连接又分：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
3）ON：用来指明连接条件。

内连接 INNER JOIN
等值连接、不等连接和自然连接属于内连接。以JOIN关键字来实现内连接——按照ON所指定的连接条件合并两个表，返回满足条件的行。

SELECT a.* , b.*
FROM student a INNER JOIN score b
ON a.student_id = b.student_id

在内连接中，INNER可以省略，使用内连接后仍然可以使用WHERE子句对连接后的记录进行筛选。

外连接
外连接返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

左外连接
查询输出结果中显示符合条件的数据行及左边数据表中不符合条件的数据行。左外连接的连接格式为：
FROM 左表名 LEFT [OUTER] JOIN 右表名 ON 连接条件

【举例】查找所有学生的学号、姓名以及他们所学的课程编号和成绩，没有课程成绩的学生信息也要显示。
SELECT a.student_id, student_name, course_id, grade
FROM student a LEFT OUTER JOIN score b
ON a.student_id=b.student_id
Oracle简写：
SELECT a.student_id, student_name, course_id, grade
FROM student a,score b
where a.student_id=b.student_id(+)

右外连接
查询输出结果中显示符合条件的数据行及右边数据表中不符合条件的数据行。右外连接的连接格式为：
FROM 左表名 RIGHT [OUTER] JOIN 右表名 ON 连接条件

【举例】查询学生的学号、所在的班级编号和班级名称，班级表中没有连接成功的记录也要显示。
SELECT a.student_id, class.class_id, class_name
FROM student a RIGHT OUTER JOIN class b
ON a.class_id=b.class_id
Oracle简写：
SELECT a.student_id, class.class_id, class_name
FROM student a ,class b where
a.class_id(+)=b.class_id

全外连接
查询输出结果中显示符合条件的数据行右边及左边数据表中不符合条件的数据行。全外连接的格式为：
FROM 左表名 FULL [OUTER] JOIN 右表名 ON 连接条件

【举例】查询学生的学号、所学课程名称及成绩，两表中没有连接成功的记录均要显示（采用全外连接)。
SELECT student_id, course_name, grade
FROM score a FULL OUTER JOIN course b
ON a.course_id=b.course_id

交叉连接
交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。

【举例】查询学生的基本信息及其成绩信息（使用交叉连接）。
SELECT * FROM student CROSS JOIN score
SELECT * FROM student , score

子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的WHERE 或 HAVING 子句内，或嵌套在其他子查询中的SELECT查询。
子查询可以嵌套，它能将比较复杂的查询分解为几个简单的查询。一个SELECT—FROM—WHERE语句称为一个查询块。将一个查询块嵌套在另一个WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

子查询的使用
可以使用子查询的地方有：
●在使用 IN 或NOT IN的集合查询中。
●在UPDATE、DELETE和INSERT语句中。
●在使用比较运算符时
●使用 ANY 或 ALL时。
●使用 EXISTS或NOT EXISTS 引入的存在测试时。
●在有表达式的地方。

带有谓词IN或NOT IN的子查询
格式：<表达式> [NOT] IN （子查询）

带有比较运算符的子查询

数据库对象

oracle 数据库对象又称模式对象
数据库对象时逻辑结构的集合,最基本的数据库对象是表
其他数据库对象包括:
同义词,序列,视图,索引

同义词 SYNONYM

同义词是现有对象的一个别名
● 简化SQL语句
● 隐藏对象的名称和所有者
● 提供对对象的公共访问

同义词共有两种类型:
● 公有同义词被所有的数据库用户访问
● 私有同义词只能在其模式内访问,且不能于当前模式的对象同名

-- 创建一个私有同义词 ,hr.countries取一个别名countries，只能在本用户中访问
create or replace synonym countries for hr.countries;
-- 访问别名跟访问真实对象效果一样
select * from countries;
-- 创建一个公有同义词，hr.locations去一个别名locations，不同用户之间可以访问
create public synonym locations for hr.locations; 
select * from locations;
-- 删除私有同义词
drop synonym countries;
-- 删除公有同义词
drop public synonym locations;

序列 SEQUENCE

● 序列是用于生成唯一、连续序号的对象
● 序列可以是升序,也可以是降序
● 使用create sequence 语句创建序列

通过序列的伪列来访问序列的值
● NEXTVAL返回序列的下一个值
● CURRVAL　返回序列的当前值

-- 创建序列，最小值1 ，从1 开始，每次增长1
create sequence seq_test;
-- 使用序列中的两个伪列，获取和生成序列值
select seq_test.nextval from dual;--获取序列下一个值
select seq_test.currval from dual;--获取序列当 前值

--创建表test,id为主键，不可重复，演示序列
create table test(
    id number not null,
    name varchar2(20) not null
);
alter table test add constraint pk_test_id primary key (id);
select * from test;
insert into test(id,name) values(seq_test.nextval,'张三'||seq_test.currval);
commit;
-- 删除序列
drop sequence seq_test;

视图 VIEW

● 视图以经过定制的方式显示来自一个或多个表的数据
● 视图可以视为“虚拟表”或“存储的查询”
● 创建视图所依据的表称为“基表”
● 视图的优点有：
(1)提供了另外一种级别的表安全性
(2)隐藏的数据的复杂性
(3)简化的用户的SQL命令
(4)隔离基表结构的改变
(5)通过重命名列，从另一个角度提供数据
视图和使用表一样,但是不存放真实数据，真实数据还是存在表中，视图只是存放查询 sql，当查询视图时候，实际上执行的是视图保存的 sql

-- 创建视图 view_student
create or replace view view_student as
select * from student s;
-- 创建复制表
create table table_student as
select * from student s;
-- 查询视图 view_student，和使用表一样,但是不存放真实数据，真实数据还是存在表中，视图只是存放查询 sql，当查询视图时候，实际上执行的是视图保存的 sql
select * from view_student;

-- 创建视图 view_emp
create or replace view view_emp as
select 
       e.employee_id id,
       concat(e.last_name,e.first_name) name,
       e.salary+e.salary*nvl(e.commission_pct,0) salary,
       e.hire_date,
       d.department_name dept_name
from emp e,dept d
where e.department_id = d.department_id(+)
order by e.employee_id;

-- 使用视图
select * from view_emp;
-- 查询部门名称为空的员工
select * from view_emp e where e.dept_name is null;
-- 查询部门名称为it的员工
select * from view_emp e where e.dept_name='IT';

索引 INDEX

● 索引是与表相关的一个可选结构
● 用以提高 SQL 语句执行的性能
● 减少磁盘I/O
● 使用 CREATE INDEX 语句创建索引
● 在逻辑上和物理上都独立于表的数据
● Oracle 自动维护索引

-- 创建标准索引 ,作用在 student 表的 name 列上
create index idx_student_name on student(name);
-- 删除索引
drop index idx_student_username;
select s.name from student s where s.name = '张三';

索引有各种类型，除了标准索引外，还有一些特殊类型的索引：
唯一索引、位图索引、组合索引、基于函数的索引、反向键索引

唯一索引

● 唯一索引确保在定义索引的列中没有重复值
● Oracle 自动在表的主键列上创建唯一索引
● 使用CREATE UNIQUE INDEX语句创建唯一索引

- 创建唯一索引，作用在student表的username列上，一般用在唯一的列上，主键编号，用户账号，手机号码，身份证……
-- 一个列只可以创建一个索引
create unique index unidx_student_username on student(username);
select s.username from student s where s.username = 'zhangsan';

位图索引

● 位图索引适合创建在低基数列上
● 位图索引不直接存储ROWID，而是存储字节位到ROWID的映射
● 减少响应时间
● 节省空间占用

-- 创建位图索引，一般用在低基数列上，该列取值范围小，例如：性别，学历，职称，年龄……
create bitmap index idx_student_sex on student(sex);

组合索引

基于函数的索引

● 基于一个或多个列上的函数或表达式创建的索引
● 表达式中不能出现聚合函数
● 不能在LOB类型的列上创建
● 创建时必须具有 QUERY REWRITE 权限

-- 创建函数式索引
create index idx_emp_name on emp(concat(last_name,first_name));
create index idx_emp_salary on emp(salary+nvl(commission_pct,0));
-- 索引失效
-- 根据last_name和first_name查询，没有用上索引
select 
  e.last_name,
  e.first_name,
  e.salary 
from emp e 
where e.last_name = 'King'and e.first_name='Steven';
-- 根据salary查询，没有用上索引
select 
  e.last_name,
  e.first_name,
  e.salary 
from emp e 
where e.salary = 24000;
-- 有效索引
-- 根据函数式条件concat(last_name,first_name)查询，才用上索引
select 
  e.last_name,
  e.first_name,
  e.salary 
from emp e 
where concat(e.last_name,e.first_name) = 'KingSteven';
-- 根据函数式条件salary+nvl(commission_pct,0)查询，才用上索引
select 
  e.last_name,
  e.first_name,
  e.salary 
from emp e 
where e.salary+nvl(e.commission_pct,0) = 24000;

反向键索引

● 反向键索引反转索引列键值的每个字节
● 通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
● 创建索引时使用REVERSE关键字

-- 创建反向键索引，一般用作连续的值列上
create index idx_student_age on student(age) reverse;

总结

● 同义词是现有数据库对象的别名
● 序列用于生成唯一、连续的序号
● 视图是基于一个或多个表的虚拟表
● 索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能
● 索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引
● 索引组织表基于主键访问数据