Oracle数据库学习

Oracle数据库学习按照李兴华视频进行的学习，视频共有26讲。

第一讲：Oracle数据库安装这里没什么可讲的，安装的oracle有普通用户scott/scott,超级用户sys/sys，斜杠后面的是密码。

第二讲：Oracle的常用命令（大小写不敏感）首先Oracle的控制台是SQLplus输入用户名和密码即可登录转换用户：conn 新用户名/密码Conn 超级用户/密码as sysdba显示当前用户：show user;显示所有的表：select * from tab;显示某个具体表结构：desc 表名称（注：desc是describe的缩写）查看表的所有内容：select * from 表名称Oracle可以直接执行文件中的sql语句。

方法：@路径，例如：SQL> @D:\select.txt第三讲：基本的SQL语句3.1基本的查询Select * from table;Select a,b,c from table; abc是列名可以对abc进行限定，distinct关键字限定列。

例子：对列进行重命名显示对列进行简单运算3.2限定查询（where）限定条件有> < =, is not null is null, 不等于：<>或者!=各类条件可以使用and or not等连接词连接，and和or放中间，not放最前面的指定范围的查询过滤语句：between …and…（闭区间）例如：等价于：Between and除了支持数字查询也支持日期查询，比较><=同样支持日期查询Oracle是大小写敏感的指定范围的另一种方法：in(a,b,c) not in(a,b,c)这是针对具体的枚举情况可以看出依然是大小写敏感的。

模糊匹配：LIKE %匹配任意长度_匹配一个长度例如查询第二个字母包含M的雇员的信息：3.3对查询结果进行排序Order by （ASC升序，DESC降序默认是升序）按降序排列：可以有多个排序条件，但有优先级，sal优先，如果sal相等，那么按hire日期升序排列3.4单行函数对于不同的DBMS，其实SQL都相同，主要是函数不同。

3.4.1字符函数小写边大写UPPER(‘XXX’)大写转小写是lower(‘XXX’)3.4.2数值函数四舍五入：round()截断小数位：trunc()取模：mod()3.4.3日期函数Months_between(sysdate,hiredate)/12表示hiredate距离现在的年数3.4.4转换函数略3.4.5通用函数略第四讲：SQL进一步学习SQL(structure query language),功能强大的数据库语言。

ANSI声称SQL是标准关系型数据库管理系统的标准语言。

●DML：数据操作语言，用于查询和修改数据●DDL：数据定义语言，用于定义数据的结构，如创建，修改或删除数据库对象●DCL：数据控制语言，用于定义数据库用户的权限。

第五讲：复杂查询1.多表查询2.分组统计及统计函数的使用3.子查询，并结合多表查询和分组统计做复杂查询4.数据库更新5.事务处理及死锁5.1多表查询例如select * from emp,dept;产生了56条记录，但是emp一共14条，dept4条，即56=14*4；所以使用多表查询会产生笛卡尔积。

是一种组合情况想要减少记录，则需要使用字段进行关联的操作。

Select * from emp,dept where emp.deptno=dept.deptno.(表明deptno的归属) 表的自身关联的例子：查询雇员姓名，工作以及该雇员领导姓名。

来自同一张表，要声明两个对象分别用e和m两个对象来表示这种单表复合查询的复杂情况。

现在要增加复杂情况，将dept表也加进来。

即查询部门的名称。

5.2左右连接由于emp中没有部门号是40的雇员，因此没有显示出dept中的40这条记录，怎样才能显示呢？在e.deptno后面加上(+)就显示出了40，属于emp列是没有结果的，这是右连接。

右连接保证了右边表的完整性，即dept表数据的完整性。

左右连接在开发中十分常用！左右连接都是外部连接，它不满足条件的行并不像内部连接一样将数据完全过滤掉，而是保留一部分数据。

行数不会减少。

左右连接查询实际上是指定以哪个表的数据为准，而默认情况下是以两个表都存在的数据为准的。

因此为了防止数据丢失要选用左右连接。

之前一个例子：显示雇员姓名，job，老板姓名。

之前的写法如下图：一共是13行数据，而实际上emp表有14行数据，缺少的是没有mgr的那行，因为它是最高领导上面没有领导了。

为了保证e的数据完整性,修改如下这是左连接，即保证了e表的完整性。

5.3SQL：1999语法对SQL的支持语法比较多，但是实质的内容我们上面都已经涉及了，只是写法不同而已，了解下Select table1.column,table2.columnFrom table[cross join table2] | ===相当于直接写from emp,dept[natural join table2] | ====相当于from emp,dept where emp.deptno=dept.deptno [join table2 using(column name)] |=======同上[join table2 on(table1.column_name=table2.column_name)] |====同上[left|right|full outer join table2 on(table1.column_name=talbe2.column_name)]左连接写法同样能得到14行记录。

5.4组函数、分组统计5.4.1组函数常用的组函数：Min()Count()Max()Avg()Sum()通过where语句求出一个分组，然后通过组函数求出相应的结果。

例子：求emp行数求emp中工资最低的注意它不是查询语句，不能这么写：求出20部门的总工资；求出所有员工的平均工资：5.4.2 分组统计要使用关键字group by例子：求出每个部门的雇员数量看出使用group by以后就可以将组函数用于查询了。

查询结果按组进行显示。

注意group by 什么就显示什么，这里group by的内容是deptno，那么我如果这样写是不行的。

分组统计对于多表查询同样适用（查询部门名称，部门人数）显示平均工资大于2000的部门编号和平均工资出现错误：原因是分组函数只能在分组中使用，不允许在where语句中出现，那么如果现在假设要指定分组的条件，则只能通过第二种条件的指令，having，having位置如图显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列。

分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句。

例如：求平均工资最高的部门的工资这个例子中，如果有deptno查询就无法正常，去掉就可以正常执行了。

5.5子查询在一个查询的内部还有另外一个查询子查询存在的位置：可以是from中，可以是where中例子：要求查询比7654工资高的全部雇员的信息Select * from emp where sal>(select sal from emp where empno=7654)所有子查询必需在（）中进行。

分三种情况单列子查询：返回结果是一列的一个内容单行子查询：返回多个列，有可能是一条完整的记录多行子查询：返回多行开发中单列子查询用的是最多的。

例子：要求查询工资比7654高，同时与7788从事相同工作的雇员信息。

Select ename,job,sal from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788)例子：要求查询出工资最低的雇员姓名，工作和工资。

Select ename,job,sal from emp where sal=(select min(sal) from emp);例子：查询部门名称，部门员工数，部门平均工资，部门最低收入雇员的姓名和最高收入雇员的姓名先按部门分组获得一张表：Select deptno,count(empno) cnt,avg(sal) avg ,min(sal) min,max(sal) maxFrom empGroup by deptno该表已经含有部门编号，部门员工数，部门平均工资，部门最低工资和最高工资。

现在还差部门名称和员工名称。

需要关联dept和emp表。

Select d.dname,t,ed.avg,ed.min,ed.max,e1.ename 高收入,e2.ename 低收入From dept d,(上面的内容) ed,emp eWhere d.deptno=ed.deptno ;上面的没有给出姓名，下面是给出姓名的情况在子查询中存在三种查询的操作符号：In：指定一个查询范围Any：All范例：求出每个部门的最低工资的雇员信息。

例子可以看出in的使用场景，当子查询结果是多行时，用=号就没用了，要用in Any：=any与in功能一样，如图>any比里面最小的值要大13条记录，唯独少了smith，因为smith最小。

<any:比最大值要小所有人的工资都小于1300，如上图All:>all:比最大的还大<all比最小的还小5.6数据库更新数据库的注意操作分为两种：数据库查询操作：select数据库更新操作：insert，delete，update为了保证进行更新后数据不被破坏要先对表进行备份。

Create table copy_emp as select * from emp;表的结构和数据完整复制添加数据：insert into 表名(列名…)values(…)对于日期类型的数据，可以使用to_date()函数，将数据插入todate(‘1995-09-08’,’yyyy-mm-dd’);修改操作：update 表名称set 字段=新值，字段=新值…where …删除操作：delete from 表名称where…5.7事务处理事物处理包括commit和rollback两个命令。

当没有提交时，可以通过rollback回滚到操作以前。

所谓事务处理指的是事件要么成功要么失败。

来举个例子：先基于emp建立一张表：create table emp10 as select * from emp where deptno=10;现在打开两个sqlplus窗口在窗口1中，删除7782的记录，在窗口2中查询依然存在。