《ORACLE SQL》学习总结

作者：李坤凤
本次任务完成时间：2019年3月26日
开发工具与关键技术：开发工具：PLSQL关键技术：SQL语言

一、SQL查询语句
1、基本的查询语句，查询表中的所有字段；
2、算术运算符（+、-、*、/），操作符优先级，
乘除高于加减，同一优先级运算符从左向右执行，
括号内的运算先执行；
3、定义空值，空值是无效的，未指定的，未知的或不可预知的值，
空值不是空格或者0；
4、连接字符串‘||’与AS 使用别名 ，日期和字符只能在单引号中出现；、
5、删除重复行，把重复的数据删除；

二、过滤和排序数据
1.where 子句，通过条件过滤数据；
1）字符和日期要包含在单引号中，2）字符大小写敏感，日期格式敏感
2、=、>、>=、<、<=、<> 比较运算
3、其他比较运算
1）BETWEEN…AND…在两个值之间（包含边界）、2）IN(set) 等于值列表中的一个

3）LIKE 模糊查询，-%代表零个或多个字符（任意个字符）， -代表一个字符；
（1）ESCAPE 标识符选择‘%’和‘’符号， 回避特殊符号的：使用转义符。例如：将[%]转为[%]、 [_]转为[_]，然后再加上[ESCAPE ‘\’] 即可。
4）IS（NOT）NULL不为空 is null 空值（为空）
4、逻辑运算
AND逻辑并，要求并的关系为真； OR逻辑或，要求或关系为真；MOT逻辑否；
5、order by 升序，desc降序

三、单行函数
（1）字符
lower upper 大小写控制
（2）字符控制函数
concat 拼接字符、substr 切割字符、length 长度、
instr 索引 、lpad 左对齐、rpad 右对齐、
trim 查询第一或最后一个，或去空格、replace 替换字符
（3）数字函数
round 四舍五入、trunc 截断、mod 求余
（4）日期的数学运算
1）、在日期上加上或减去一个数字结果仍为日期；
2）、两个日期相减返回日期之间相差的天数；日期不允许做加法运算，无意义；
3）、可以用数字除24来向日期中加上或减去天数

（5）日期函数
sysdate 当前日期
months_between（’01-3月-95’, ’01-8月-98’） 两个日期相差的月数
add_months(’11-3月-95’,6) 向指定日期中加上若干月数
next_day(’01-3月-95’,星期几) 指定日期的下一个星期几对应的日期
last_day （’01-3月-95’） 本月的最后一天
round (sysdate,‘month’) 日期四舍五入、trunc (sysdate,‘month’) 截取日期
（6）转换函数
1）、to_char 转换字符类型，对日期转换；
2）、to_date 转换时间类型，对字符的转换
3)、to_number 函数对字符的转换
4)、9 数字，0 零，$ 美元符，L 本地货币符号，. 小数点， ，千位符；

5）、日期格式的元素
HH24:MI:SS:AM 15:45:32 PM 时间格式
DD“of”MONTH 12 of OCTOBER 使用双引号向日期中添加字符

（7）通用函数
1）这些函数适用于任何数据类型，同时也适用于空值：
nvl 函数、nvl2 函数、nullif函数

2）coalesce 函数，第一值为空，返回第二个值，第三值不返回；第二值为空，返回第一个值，第三值不返回；第一值为空，第二个值为空，返回第三值；

（8）case条件表达式
case when then else end

9)嵌套函数

四、多表查询
1）、：叉集和迪卡尔集相同（CROSS JOIN）：省略连接条件、连接条件无效、所有表中的所有行相互连接
2）、外链接语法
（1）等值连接、与另一个表中相同字段连接，多个连接条件与 AND 操作符 、连接多个表


（2）非等值连接，与另一个表中不相同的字段相连接

（3）外链接的符号是（+）、内连接、自连接;
内连接：相同的列进行内连接，不同的列进行连接则没有结果；

自连接，（自己与自己连接）

（4）SQL：1999语法连接
自然连接（NATURAL JOIN）、左外连接（left outer jion on）、右外连接（rigth outer join on）、满外连接（full outer join on）,使用外链接可以查询不满足连接条件的数据;

（5）使用 USING 子句创建连接、使用ON 子句创建连接（常用）：
可以使用 ON 子句指定额外的连接条件，ON 子句使语句具有更高的易读性；
using子句指定等值连接中需要用到的列、不要加上前缀与后缀；
join和 using 经常可以同时使用、join on多表连接；

五、分组函数
1、分组函数作用于一组数据，并对一组数据返回一个值；
2、组函数类型
avg 平均值
sum 求和
max 最大值
min 最小值

count 合计返回总数据，适用于任意数据类型；
（1）可以对数值型数据使用平均值和求和函数；
（2）可以对任意数据类型的数据使用最大值和最小值函数；
（3）平均值、最大值、最小值、求和与nvl函数同时使用使分组函数无法忽略空值；

3、分组数据
分组数据 group by 子句选择需要的字段进行筛选，group by 子句不包含在select列表中；
4、非法使用组函数
不能在 WHERE 子句中使用组函数；
可以在 HAVING 子句中使用组函数；
5、过滤分组（having子句）

六、子查询
1、子查询 (内查询) 在主查询之前一次执行完成，子查询的结果被主查询(外查询)使用；
2、子查询要包括在括号内，子查询放在比较条件的右侧；
3、单行子查询的操作符
操作符 含义
= Equal to

   Greater than

= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to 不等于

4、多行子查询操作符
IN 等于列表中任意一个
ANY 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较

七、创建和管理表
1、（1）varchar2(size) 可变长字符数据、char(size) 定长字符数据
（2）number（p,s）可变长数值数据、date 日期型数据
（3）long 可变长字符数据，最大可达到2G、clob 字符数据，最大可达到4G
（4）raw (long raw)原始的二进制数据、blob 二进制数据，最大可达到4G
（5）bfile 存储外部文件的二进制数据，最大可达到4G、rowid 行地址
2、create table 创建表

3、alter table 语句
表格的新增，修改，删除 （列）
add 新增一个列
modify 修改 可以修改列的数据类型，尺寸和默认值
default 默认值 只影响今后对表的修改
drop table 删除不需要的列，数据和结构都被删除，删除相关索引，不能回滚；
rename …To 重命名一个列
truncate table 删除所有的数据，释放表的存储空间；
truncate语句不能回滚；
可以使用 delete语句删除数据,可以回滚

八、数据处理
1、DML（数据操纵语言），向表中插入数据，修改现存数据，删除现存数据；
2、insert into 一次只能插入（一条数据）；
update set 修正现存数据，可以一次更新多条数据；r
delete table 删除现存、删除表格，可以回滚；
truncate table 清空表格，不可回滚；
commit 提交、savepoint 保存点 、rollback 回滚；

九、约束
1、如果不指定约束名 ，Oracle server 自动按照 SYS_Cn 的格式指定约束名
2、创建和修改约束:
（1）建表的同时、（2）建表之后
3、可以在表级或列级定义约束
1）、列级约束只能作用在一个列上
2）、表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）
NOT NULL 非空约束，只能定义在列上
UNIQUE 唯一约束，可以定义在表级或列级
PRIMARY KEY 主键约束、FROREIGN KEY 外键约束
REFERENCES: 标示在父表中的列

ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除；
ON DELETE SET NULL(级联置空): 子表中相应的列置空；
CHECK 范围约束，定义每一行必须满足条件
3）、添加约束的语法：
添加或删除约束，但是不能修改约束，有效化或无效化约束，
添加NOT NULL约束要用MODIFY语句；
add constraint 添加约束
drop constraint 删除约束
disable constraint 无效化约束
enable constraint 激活约束

十、视图
1、描述视图，创建和修改视图的定义，删除视图，从视图中查询数据，通过视图插入，修改和删除数据；
2、使用rownum 伪列、rowid ，对rownum只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据；
3、视图是一种虚表；
4、视图建立在已有表的基础上, 视图赖以建立的这些表称为基表；
5、向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句；
6、视图向用户提供基表数据的另一种表现形式；
7、控制数据访问，简化查询，避免重复访问相同的数据；
8、create view 创建视图 create or replace view 修改视图;

十一、其他数据库对象
1、 使用序列，使用索引提高查询效率，为数据对象定义同义词；
2、 序列提供有规律的数值，索引提高查询效率，同义词给对象起别名；
3、 可供多个用户用来产生唯一数值的数据库对象
（1）自动提供唯一的数值
（2）共享对象
（3）主要用于提供主键值
（3）将序列值装入内存可以提高访问效率
4、create sequence 语句：
Increment by 每次增长的数值、start with 从哪个值开始、maxvalue 最大值
minvalue 最小值、cycle nocycle 是否需要循环、cache nocache 是否缓存登录
5、nextval返回序列中下一个有效的值，任何用户都可以引用
6、currval 中存放序列的当前值
7、nextval应在currval之前指定 ，否则会报currval尚未在此会话中定义的错误；
8、序列在下列情况下出现裂缝:
（1）、回滚、（2）、系统异常、（3）、多个表同时使用同一序列
9、修改序列：必须是序列的拥有者或对序列有 alter权限，只有将来的序列值会被改变，改变序列的初始值只能通过删除序列之后重建序列的方法实现
10、删除序列：使用 drop sequence语句删除序列，删除之后，序列不能再次被引用
11、一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中；
12、 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
13、 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引
14、 用户不用在查询语句中指定使用哪个索引
15、 在删除一个表时,所有基于该表的索引会自动被删除
16、 通过指针加速 Oracle 服务器的查询速度，通过快速定位数据的方法，减少磁盘 I/O
17、创建索引:自动创建、手动创建
18、列中数据值分布范围很广，列经常在 WHERE 子句或连接条件中出现，
表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%，需要创建索引；
19、 表很小，列不经常作为连接条件或出现在WHERE子句中，查询的数据大于2%到4%，表经常更新，不需要创建索引；
20.同义词：方便访问其它用户的对象，缩短对象名字的长度

十二、控制用户权限
1、创建用户，创建角色，使用grant 赋予权限、revoke 回收权限，创建数据连接
2、系统权限：对于数据库的权限，创建新用户、删除用户、删除表、备份表
用户系统权限：
create session 创建会话
create table 创建表
create sequence 创建序列
create view 创建视图
create procedure 创建过程
DBA可以赋予用户特定的权限，可以创建用户和修改密码
3、对象权限：操作数据库对象的权限
1）、不同的对象具有不同的对象权限
2）、对象的拥有者拥有所有权限
3）、对象的拥有者可以向外分配权限

十三、SET运算符
UNION 并集，去重，重复值 UNION ALL 并集，两个结果重复，不去重
INTERSECT 交集 MINUS 差集

十四、总结
还没有开始学习orcale SQL 数据库的时候，有人说这个数据库是最难学的，现在我学完了orcale SQL中的一点皮毛，从中让我觉得，你不努力，不动脑，那些知识都不会成为是你的，以前我们学不会的时候，最爱说的一句话就是：‘书本上的知识认识我，我却不认识它，看它就像是在看天书’，这就是不努力学习的人，一个人努力学习，就是在难得知识点，也要把它记熟，在学习orcale SQL 中，里面的知识点需要思维力，逻辑力，orcale 的子查询，我看了几遍，但是一遇到难一点的题目，根本就不会做了，这就说明了，我的理解能力不够强，所以学习需要努力。