Oracle数据库

数据库

用来持久化保存数据

数据库的种类

1. 关系型数据库 MySQL，Oracle，SQL Server
2. 层次性数据库 IMS
3. 网状型数据库 DBTG
4. 分布式数据库 DDB，HequoiaDB
   关系型数据库：包含一系列的数据和对象，以及对关系的操作，增删改查，包含数据的完整性约束。

完整性约束

主键约束：值不可重复，不能为空
外键约束：一定是另一张表的主键列值
自定义约束：给每一个列都给一个限制

sql,sqlplus,plsql

sql：有结构的查询 语言，命令需要分号结尾
sqlplus：工具 用于执行sql语句 把执行以后的效果返回。使用命令不需要分号结尾
plsql：增加了逻辑控制功能

Oracle中的数据类型

1. NUMBER(p,s) 数值类型
   p 代表有效位数 精度(总长度)
   s 代表小数点后的位数 刻度
2. 字符类型
   2.1 CHAR(8) 定长字符类型：占用空间固定
   2.2 VARCHAR(8) 定长字符类型 占用空间 跟着输入有关，输入多长就是多长
   2.3 VARCHAR2(8) 变长字符类型 占用空间 跟着输入有关。
   一般推荐使用VARCHAR2
3. 日期类型 DATE
   3.1 默认类型：日-月-年
   3.2 修改当前语言环境
   alter session set nls_date_language=english
   3.3 修改日期显示格式
   alter session set nls_date_format=‘yyyy-mm-dd’;
4. CLOB：字符型大对象 如 存一本小说
5. BOLB：字节数组 用于保存文件的二进制字节数组

四种对象

1. table 表格 行和列
2. view 视图 一张表或者多张表的部分或者完整的映射
3. sequence 序列
4. index 索引，提高数据库的访问效率

Oracle主要操作语言

1. select 语句
2. 数据操纵语言(DML) 有事务概念
   insert delete update
3. 数据定义语言(DDL) 会提交事务
   create alter drop rename truncate
4. 数据控制语句(TCL)
   commit rollback savepoint
5. 数据控制语言(DCL)
   grant revole

事务的特点

原子性：同时成功，或者同时失败
一致性：事务执行的结果必须是使数据库从一个一致性状态到另一种一致性状态。
隔离性：事务操作应该相互独立
持久性：事务所作的影响，在事务结束之后用改是持久的

事务的隔离级别

read-uncommitted 不提交也能读
read-committed 提交之后才能读 解决了脏读
repeatable-read 解决了脏读和不可重复读
serializable 强制的进行排序，在每个读数据行上添加共享锁，会导致大量超时现象，和锁竞争，解决了三个问题。

级别越高解决的问题越多但是效率越低

事务中产生的问题

1.脏读 主要针对update操作。 一个事务A读到另一个事务B中修改过但是还没有提交的数据

2.不可重复读 主要针对update操作。 一个事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改并提交了,所以就出现了事务A里面读一个数据俩次,但是读到的结果是不同的。

3.幻读 主要针对的是insert/delete操作。事务A第一次用where条件筛选出了10条数据,事务A第二次用通样的where条件筛选出的却是11条数据,因为事务B在事务A的第一次和第二次查询之间进行了插入操作,并且插入的这个数据满足事务A的where筛选条件.

三种删除操作

1. delete 删除表中的一条或者多条记录，该操作需要提交事务
2. truncate 清空表格，该操作不需要提交事务
3. drop 删除整张表，该操作不需要提交事务

sqlplus 命令

1. 以管理员方式登录Oracle
   sqlplus system/密码
   show user 显示当前登录的用户名字
2. 创建用户XXX,密码xxx
   create user XXX identified by xxx
3. 给新用户授权
   grant connect,resource to XXX
4. 激活用户
   alter user XXX account unlok
5. 设置当前会话的日期格式
   alter session set nls_date_language=english
6. 执行sql文件
   6.1 start 路径+文件名 执行sql语句
   6.2 @路径+文件名 执行sql语句
   6.3 get+文件名 不执行sql语句
7. 查询当前用户下有哪些表
   select table_name from user_tables

select语句

1. 查询所有
   select * from table_name
2. select语句可以对指定的列的所有值进行算术运算。
   select 列名 运算符 数字 from 表名
3. 起别名：给某一个列 起别名
   3.1 select 旧列名 as 新列名 from 表名
   3.2 select 旧列名 新列名
4. 连接符||：可以将多个字符串或者多个字符串列的值进行拼接
   作用：可以将多个列的值或者字符串进行拼接
   注意：oracle中字符串使用的是单引号
   select 列||‘你好’ from table_name;
5. nvl(参数一，参数二)：空值函数
   参数一：可能为空的列
   参数二：如果参数一的列为空 就使用参数二的值代替
6. 命令 col [column] [option] : 修改列在sqlplus中的显示格式(样式)

排序和限制查询

1. 排序
   1.1 使用order by子句，执行权最低，最后执行。
   1.2 排序关键字
   asc 升序(默认为升序排序)
   desc 降序
   1.3 如果有多个排序，必须在之前的排序排好之后再进行排序，有重复的值
2. 限制查询
   使用where 子句，条件可以多个。使用逻辑操作符和()进行条件的逻辑整合。where子句的优先级别最高。比较操作表达式由操作符和值组成。
   2.1 常见的操作：
   逻辑比较操作符
   = > < >= <= !=
   不等于:三个都表示不等于的意思(经常用的是!=)
   != <> ^=
   2.2 在某一个区间[1,2]
   where 列名 between 1 and 2
   2.3 列名(not) in(可选值1，可选值2)
   某一列的值只能是括号里的数据
   where 列名 in (41,44);
   2.4 and 且
   需要连接多个条件
   where 条件一 and 条件二
   当条件一和条件二都满足的数据才会被查询出来
   2.5 or 或
   需要多个连接条件
   where 条件一 or 条件二 or 条件三
   满足其中的一个条件就可以查询出来
   2.6 (not)like 模糊匹配
   _: 占位符 占一个符号位
   %: 占位符 占0-多个位子
   escape 定义转义符号，将特殊含义的字符串转义为普通字符串
   where 列名 like ‘/_name’ escape ‘/’;
   以N开头 ‘N%’，包含N ‘%N%’，
   2.7 is (not) null :判断那一列是null
   where 列名 is null

单值函数

单值函数分类

1. 字符函数

   • lower 把字符转为小写
     select lower(‘HELLO’) from dual;
   • upper 把字符转换为大写
     select upper(‘world’) from dual;
   • initcap 把字符串首字母转换为大写
     select initcap(‘hELLO’) from dual;
   • concat 把两个字符串连接在一起
     select concat(‘hello’,‘world’) from dual;
   • substr 截取字符串
     substr(参数一，参数二，参数三)
     参数一：截取的字符串
     参数二：从第几个字符开始(包含)，正数从前往后取，负数从后往前取
     参数三：截取后面连续的几个字符
     select substr(‘hello’,2,3) from dual; ell
   • length 获得字符串长度
     select length(‘world’) from dual;
   • nvl 替换列中为null的值
     select nvl(列名,0) from 表名

2. 日期函数

   • sysdate 表示系统的当前时间
     select sysdate from dual;
     sysdate 进行加减操作的时候，单位是天
   • months_between 俩个日期之间相差多少个月(单位是月)
     select months_between(sysdate+30,sysdate) from dual;
   • add_months 返回一个日期数据:表示一个时间点,往后推x月的日期
     select add_months(sysdate,2) from dual;
     18-1月 -20
   • next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
     select next_day(sysdate,‘星期一’) from dual;
     25-11月-19
     如果要使用’Friday’，那么需要把当前会话的语言环境修改为英文
   • last_day 返回一个日期数据：表示一个日期所在的月份的最后一天
     select last_day(sysdate) from dual;
     30-11月-19
   • round 对日期进四舍五入,返回操作后的日期数据
     把当前日期四舍五入到月
     select round(sysdate,‘MONTH’) from dual;
   • trunc 对日期进行截取 和round类似,但是只舍弃不进位

3. 转换函数

   • to_char(参数一，参数二)：将数字或者日期类型的数据转换为字符串类型，返回值是字符串
     参数一：要转换的数字或者日期
     参数二：要转换成的类型
     select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
     select to_char(123,’$999.99’) from dual;
   • to_number(参数一):将字符串数据转换为数字类型
     参数一：全数字字符串
     select to_number(‘1000’) from dual;
   • to_date(参数一，[参数二])：将字符串转化为日期类型
     参数一：字符串，和系统日期格式一致
     select to_date(‘18-12月-2019’) from dual;
     参数一：随便字符串
     参数二：指定字符串的格式
     select to_date(‘18-12-2019’,‘dd-mm-yyyy’) from dual;

4. 数字函数

   • round 四舍五入
     round(arg1,arg2)
     第一个参数表示要进行四舍五入操作的数字
     第二个参数表示保留到哪一位
     select round(45.923,0) from dual;
     46
   • trunc 截取到某一位
     trunc(arg1,arg2)
     和round的用法一样,但是trunc只舍去不进位
     select trunc(45.929,2) from dual; 45.92
   • mod 取余
     mod(arg1,arg2)
     第一个参数表示要进行取余操作的数字
     第二个参数表示参数1和谁取余
     select mod(10,3) from dual; 1

多表查询

多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，数据通过特定的连接进行联合显示。在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积。为了在多表查询中避免笛卡尔积的产生,我们可以使用连接查询来解决这个问题。

连接查询

1. 等值连接
   利用一张表中某列的值和另一张表中某列的值相等的关系,把俩张表连接起来。

	select last_name,dept_id,name
	from s_emp,s_dept
	where s_emp.dept_id=s_dept.id;

2. 不等值连接
   有另一张表：s_gender,表中的数据类似于下面内容:
   id name minsal maxsal
   1 初级程序员 2000 4000
   2 中级程序员 4000 6000

	select se.last_name,se.title,sg.name
	from s_emp se,s_gender sg
	where se.salary between sg.minsal and sg.maxsal;

3. 外连接
   3.1 左外连接

		//查询所有员工 以及对应的部门的名字,没有部门的员工也要显示出来
		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id=s_dept.id(+);
		或者用 left outer join

3.2 右外连接

		//查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来
		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id(+)=s_dept.id;
		//或者用 right outer join

3.3 全连接

		select last_name,dept_id,name
		from s_emp full outer join s_dept 
		on s_emp.dept_id=s_dept.id;

4. 自链接
   一张表，自己和自己连接

		//查询每个员工的名字以及员工对应的管理者的名字
		select s1.last_name,s2.last_name manager_name
		from s_emp s1,s_emp s2
		where s1.manager_id = s2.id;

集合操作符

如果有俩条sql语句,每一条sql都可以查询出一个结果,这个被称之为结果集。那么我们可以使用下面的关键字对俩个结果集进行操作。
注意:前提条件 俩个结果集中查询的列要完全一致

• union 取俩个结果集的并集

		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id=s_dept.id(+)
		union
		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id(+)=s_dept.id;

• union all 把俩个结果集合在一起显示出来

		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id=s_dept.id(+)
		union all
		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id(+)=s_dept.id;

• minus 第一个结果集除去第二个结果集和它相同的部分

		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id=s_dept.id(+)
		minus
		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id(+)=s_dept.id;

• intersect 求俩个结果集的交集

		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id=s_dept.id(+)
		intersect
		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id(+)=s_dept.id;

组函数

组函数(分组函数),是指将数据按照某列的值进行分组后，然后使用组函数分别对每个分好的小组中的数据进行处理。所以组函数一般要结合着分组关键字group来使用
group by 在查询表中数据的时候进行分组的关键字
having 分组之后的进一步数据筛选的关键字
组函数:
    avg 求平均值
    count 计算有多少条数据
    max 最大值
    min 最小值
    sum 求和
    stddev 标准差
    variance 方差
组函数出现的位置：
    select后面
    havaing后面
    order by 后面
    where 后面一定不能出现组函数
where和having对比:
     1.where和having都是做条件筛选的
     2.where执行的时间比having要早
     3.where后面不能出现组函数
     4.having后面可以出现组函数
     5.where语句要紧跟from后面
     6.having语句要紧跟group by后面

使用组函数：不解和group分组使用

	select avg(salary)
		from s_emp;

使用组函数:结合group分组使用

	select dept_id,avg(salary)
	from s_emp
	group by dept_id;

使用组函数:结合group分组以及having筛选使用

	select dept_id,avg(salary)
	from s_emp
	group by dept_id
	having avg(salary)>=1400;

子查询(嵌套查询)

一条sql语句嵌套一个或者多个sql语句

1. 把查询一的结果集(多行多列)当做表 进行查询
2. 把查询一的结果集(多行一列)当做当前表的限定条件
3. 把查询一的结果集(一行一列)当做当前表的限定条件