Oracle学习笔记

Oracle新手上路

用户基本信息

• 新安装的oracle软件中有三个用户：sys，system，scott；
• sys和system用户是默认可以使用的，而scott需要解锁后才能使用；

• 用户登录

  • 登录用户system，运行SQL Plus程序，再用户名部位输入system再密码部位输入你创建数据库是使用的密码即可；
  • 登录用户sys是，需要注意，直接输入密码会提示错误，必须在设置的密码后面加上“ as sysdba”或者“ as sysoper”字符串货才能使用；
• scott解锁：
  
  • 登录用户sys或者system；
  • 再命令窗口输入：“alter user scott account unlock;”注（‘；’是必须要添加的， 如果不添加会显示‘2’）;
  • 输入完有提示密码工期或者失效，在输入：“conn scott/tiger”;
  • 会出现让你修改密码，自己修改自己喜欢的密码就OK了；

oracle入门

1.select语句:

• select是SQL中使用的查询语句，

• 一般使用select语句都是：

  • select （列命） from （表命）；
  • 这是最简单的查询语句；

• select子句 表示所需查询的数据列；

• select子句和面的列名可以有多个，每个列名之间用“，”分隔；
• from子句 表示查询的数据来自于那个表。

2.运算符

• 可以在select语句中直接使用运算符，改变输出的结果；
• 运算符的优先等级：
  
  • 乘除优于加减；
  • 相同优先权的表达式按照从左至有的顺序依次计算；
  • 括号可以提高优先权，并且是表达式变得清晰；

运算符 描述 + 加 - 减 * 乘 / 除

3.null空值

• 空值是指一种无效的、未赋值、未知的或不可用的值；
• 空值不同于零或者空格；
• 任何包含空值的运算表达式运算后结果都为空值NULL;

4.列别名

• 列别名
  
  • 用来重新命名列的显示标题；
  • 如果SELECT语句中包含计算列，通常使用列别名来重新定义列标题；
• 使用列别名的方法
  
  • 方式一： 列命 列别名；
  • 方式二： 列命 AS 列别名；
• 以下三种情况列别名两侧需要添加双引号
  
  • 列别名中含有空格；
  • 列别名要区分大小写；
  • 列别名中有特殊符号；

5.连接操作符

• 链接操作符
  
  • 用于链接列与列、列和字符；
  • 用于创建字符表达式的结果列；
  • 操作符有：“||”和“concat”；
• “concat”和“||”的区别：
  
  • concat只能连接两个数据，需要连接第三个数据是不能使用concat，只能使用||，应为||连接操作不限制使用个数；
    

    ---

6.消除重复行

• 再select语句中，再列的前面加上distinct 关键字就可以消除重复行了；

7.限制条件语句

• 再表命的后面加上where条件语句，查询语句变成了现实条件查询语句了；

8.比较操作符

操作符 含义 = 等于 > 大于 .>= 大于等于 < 小于 <= 小于等于 <> 不等于 between…and… 判断要比较的值师傅在摸个范围内； in(集合列表) 判断要比较的值是否和集合列表中的任何一个值相等； like 判断要比较的是是否满足部分要求； is NULL 判断要比较的值是否为空值。

- like运算符也叫模糊查询，其中有两个通配符：
- 1：% 代表零个或任意多个的字符；
- 2：_ 代表一个字符；- 可以使用escape表示服实现对‘%’和‘_’的查找；
- 但出现特例情况时，可以采用如下方法：

- 或者采用“【】”来转换成该特殊符号为字符后就可以使用了；

9.逻辑运算符

运算符 含义 AND 逻辑与，用来连接多个条件表达式，所有表达式都为TRUE，整个结果才为TRUE OR 逻辑或，用来连接多个条件表达式，只要有1个表达式为TRUE，则整个结果为TRUE NOT 逻辑非，用来对条件表达式取反值，true就为false，false变为true；

10.运算符优先级

优先级 运算分类 运算符举例 1 算数运算符 +、-、*、/ 2 连接运算符 concat以及双竖线 3 比价运算符 =,<>,<,>,<=,>= 4 特殊比较运算符 BETWEEN … AND … , IN , LIKE , IS NULL 5 逻辑非 NOT 6 逻辑与 AND 7 逻辑或 OR

ORDER BY 子句

• 使用该子句能对查询结果排序
• 其中
  
  • 可以按找列名、表达式、别列名结果集的序列号排序
  • ASD：升序，默认值 DESC: 降序；
  • ORDER BY子句必须写在SELECT语句的最后

函数

1.单行函数

• 对单行处理，并返回一个结果的函数
• 有可能返回值与原参数数据类型不一致
• 单行函数可以写在SELECT、WHERE、ORDER BY 子句中，可以嵌套

2.单行函数的分类

• 字符函数
  
  • LOWER（列命|表达式） 所有字母小写函数
  • UPPER（列命|表达式） 所有字母大写函数
  • INITCAP（列命|表达式） 首字母大写的函数
  • CONCAT（s1，s2） 注解：见连接操作符
  • SUBSTR （s1，n1，n2） 返回s1从n1位置起，n2长度的子字符串
  • LEIGHT（s）返回字符串的长度
  • INSTR（s1，s2，n1，n2）返回‘s1’中‘s2’从n1开始，第n2次出现的位置；
  • LAPD（s1，n1，s2）返回s1被s2从左面填充到n1长度后的字符串
  • RPAD（s1，n1，s2）返回s1被s2从右面填充到n1长度后的字符串
  • TRIM（‘s1’from‘s2’）返回s2去掉首尾的s1后的字符串
  • REPLACE（s1,s2,s3）把s1,中的s2用s3替换
• 数值函数
  
  • ROUND（double，int）返回double从小数点后第int位开始四舍五入后的数值
  • TRUNC（double,int）返回double从小数点后的第int位以前的数值
  • MOD（int1，int2）返回int1除以int2的余数

• 日期函数

  • SYSDATE：返回当前的系统时间
  • MONTHS_BETWEEN（date1，date2）months 返回date1到date2经历了多少个月份
  • ADD_MONTHS（date1，int2）再‘date1’的基础上加上‘int1’个月
  • NEXT_DAY（date，星期几）返回再‘date’之后的下一个‘星期几’是哪一天
  • LAST_DAY（date）返回‘date’所在月的最后一天
  • EXTRACT（ll from date）返回‘date’中的‘ll’是什么（ll：可以使年、月、日）

• 转换函数

  • TO_CHAR（值，类型）将值转换成char中的什么类型
  • TO_DATE（值，类型）将值转换成date中的什么类型
  • TO_NUMBER （值，类型）将值转换成number中的什么类型

• 通用函数

  • NVL （expr1，expr2）如果expr1不为空返回expr1，否则返回expr2
  • NVL2（expr1，expr2，expr3）如果expr1不为空放回expr2，否则返回expr3
  • NULLIF（expr1，expr2）比价两个值，相等返回null，否则返回第一个值
  • COALESCE（expr1，expr2，expr3，…….）返回比一个不为空的expr
  • CASE和DECODE
    
    

3.分组函数

• 对一组列数据进行操作，每组返回只返回一个结果

4.分组函数的分类

• 通常分为下面五类：
  
  • MIN（列名） 最大值【可以用于时间类型】
  • MIX（列名）最小值【可以用于时间类型】
  • SUM（列名）求和
  • AVG（列名）求平均数
  • COUNT （列名）返回满足条件的数据个数

5.GROUP BY 子句

• 通过GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组

6.HAVING 子句

• HAVING子句也限制条件语句；

HAVING 子句与 WHERE 子句的区别

having 子句是写在 broup by 语句后的限制语句，而where是写在 broup by 语句前的限制语句，而且where语句的执行顺序也在having子句前面；

Oracle 语句执行顺序和书写书写顺序

• 书写顺序
  
  • select语句 –>from语句 –> where语句 –>group by 语句 –> having 语句 –> order by 结束
• 执行顺序
  
  • from 语句–>where语句–> group by 语句–>having 语句 –> select 语句–> order by 结束

多表连接

• 怎么实现多表连接
  
  • from后面连接表的不为书写对于一个表单，并使用空格分离
• 之后我们会发现第一个表中的所有行和第二个表中的所有行都发生
  连接这种现象我么成为笛卡尔积现象
• FROM N个表单 WHERE 后最少接N-1个连接条件，否则会产生笛卡尔积现象
• 表连接一个共分为五中链接方式：
  
  • 分别为内连接、左外链接、右外链接、全外链接和交叉链接；
  • 内连接：除了再from后面写表单，where里加限制条件我们还可以用join…on… 的方法创建连接，【inner】join后面接表单，on后接连接条件；
  • 左外连接：在join前加上‘left 【outer】’字符创就可以了，他的功能是在这连接前面的表单所有的数据全部显示；
  • 右外连接：在join前加上‘right 【outer】’字符就可以了，他的功能是在这链接后面的表单所有的数据全部显示；
  • 全外连接：将左外里的left更改为full就OK了，是连接的左右表单内容全部显示 ；
  • 交叉链接：在限制条件里使用不等于号链接条件，所产生的链接结果就为交叉链接；

子查询

• 利用查询的数据供另外一个查询使用的查询被称为子查询
• 子查询可以书写在： WHERE 、HAVING、FROM子句中
• 子查询可以返回一个主句，也可也返回多个数据
  
  • 当返回值为多个时：使用IN（不解释，看比较操作符去）、ANY（效果等同于IN，但是可以判断大于或者小于，小于最大值，大于最小值）、ALL来限制查询条件（可以判断大于或者小于，小于最小值，大于最大值）

分页查询

• 首先我们先要明确分页用来做什么，为什么要使用分页
  
  • 用户信息数据返回到操作界面上时并不会全部都显示，只会显示一部分，而每一部分又独立成为一个页面，所以我们要见当前页面的数据从数据苦衷跳出来，所以就用到了分页查询；
• 接下啦我们来看一看分页的细部技术
  
  • ROWNUM ：一个伪列，实际不存在表中的特殊列，只是在表最前面加一列序号，供分页使用
  • TOP-N查询主要是实现表中按照某个列排序，输出最大或最小的N条记录功能，也是实现分页的手段
• 最后，我们来看一看分页
  
  • 因为ROWNUM不能书写大于的条件，而且一个SELECT语句中只能有一个ROWNUM，所以我们需要将ROWNUM列实例化，并起一个列别名然后套在另一个SELECT语句中；
  • 最后使用TOP-N的方法，取获分页数据；

相关子查询

• 相关子查询执行的过程

  • 取得父查询的候选行；
  • 用候选行被子查询引用列的值执行子查询；
  • 用来自查询的值确认或者取消候选行；
  • 重复步骤1、2、3，直到父查询中无剩余的候选行；

• EXISTS和NOT EXISTS操作符

  • EXISTS判断是否存在，存在就返回true，否则返回false；
  • NOT EXISTS则相反；

数据操作和事务控制

操作语言

• 增加行数据：使用INSERT语句实现；
• 修改行数据：使用UPDATE语句实现；
• 删除行数据：使用DELETE语句实现；
• 合并行数据：按照指定的田间合并两个标的数据，使用MERGE语句实现；

插入数据
- INSERT INTO 表名 [列名，列名 … ] VALUES[对应列名的数据，数据 … ]（单行数据插入）;
- INSERT INTO 表名 [列名，列名] 子查询（多行数据插入）；

修改数据

• UPDATE 表名 SET 列元素限制 [WHERE 行元素限制]（如果没有行元素限制，则整列全部修改）

删除数据

• DELETE 表名 [WHERE 限制行元素条件]（如果没有限制条件则删除表中全部的内容，注意：并不是删除当前表）；

事务

• 事务，也称工作单元，由一个或多个SQL语句组成的操作序列
• 事务的组成：由一组相关的DML或SELECT语句加上一个TPL语句或DDL语句或DCL语句组成
• 事物的特性：
  
  • 原子性（Atomicity）：不可分割，要么全成功要么全失败；
  • 一致性（Consistency）：整个系统数据一致；
  • 隔离性（Isolation）：事务于事物之间不存在必然的联系，个忙个的；
  • 持久性（Durability）：再数据没有再次修改之前，一致保持数据不发生改变；