1 Oracle基础

一、随记

以管理员身份登录：sqlplus sys/1234 as sysdba;

解锁用户：alter user scott account unlock;

内部用户之间的跳转：conn scott/1234;

DOS的sqlplus模式中，Oracle命令后面不加分号，SQL命令后面必须加分号结束才可执行；

DOS的sqlplus模式中，edit命令打开缓冲区可以继续编辑上一条命令；

DOS的sqlplus模式中，针对折行设置行宽：set linesize 100;

oracle表中数据区分大小写，列名不区分大小写。

查看表结构：desc tbname;

 

二、SQL

结构化查询语言，是关系型数据库系统的标准语言,分为DML(select、insert、update、delete)数据操作语言\DDL数据定义语言\DCL数据控制语言。

（一）DML SELECT 基础查询

别名：select empno as(可不写) 员工编号 from emp;

排重：select distinct job from emp; ---distinct后面所有字段都排重

条件：where

分组：group by，select 字段中有的，没有进行分组函数计算，则必须跟在group by 后面

分组后筛选：having（跟分组函数）

 排序：order by 默认升序asc,降序是desc。select distinct deptno,job from emp order by deptno,job desc

 

比较运算符：>< !=  <> (between and) 

 

【in (not in)和exist】

 例如:

（1）SELECT NAME FROM emp WHERE empno IN(SELECT distinct empno FROM job_history);

     先执行子查询，将结果集缓存，然后与外部查询的值进行比较；适用于子查询返回的结果集较小且确定的情况。

（2）SELECT ename FROM emp WHERE EXISTS
(SELECT empno FROM job_history WHERE emp.empno=job_history.empno); 

EXISTS 用于检查子查询是否返回任何行，不关心具体数据，只关心是否存在；

• 对于 emp 表中的每一行，检查 job_history 表中是否存在与该员工 empno 匹配的记录。

• 如果存在匹配记录，则将该员工的 ename 包含在最终结果中。

适用于子查询可能返回大量数据或需要关联外部查询的情况。

 

模糊查询： like[ _%]

字符串拼接：||

例：如果模糊查询名字中有“F_”的，需要使用 escape使其不被解释：

select * from emp where ename like '%F\_%' escape '\';其中\也可以是|等其它字符；

逻辑运算符：not  and or

集合运算符： union  / union all

is null /is not null 

 

函数：字符串函数、数值函数、日期函数、转换函数、聚合函数

（数学运算）数字+空=空，需要做空值校验，将空值转换为其他任意值，sum(sal+nvl(comm,0)),如comm为空则置为0.或者聚合函数对null自动排除处理：sum(sal)+sum(comm)

条件判断decode(目标值，比较值1，结果1，比较值2，结果2。。。)

 

基础查询案例：

1、显示非办事人员（CLERK）工作名称，以及从事同一工作雇员的月工资的总和，并且要满足雇员的月工资合计大于$5000,输出结果按月工资合计升序排列。

2、查询20,30部门的平均工资，并将平均工资大于2000的输出，输出结果按平均工资排序。

（二）DML SELECT连接查询

1、连接分类方式一

（1）等值连接，同内连接  where emp.deptno=dept.deptno

（2）非等值连接   例如：

from emp e, salgrade s 

where e.sal>=s.losal and e.sal<=hisal 

或 where e.sal between s.losal and s.hisal

2、连接分类方式二

（1）内连接 同等值连接

（2）外连接，又分为左（外）连接、右（外）连接、全（外）连接

oracle左外连接写法：

select e.empno,e.ename,d.deptno,d.dname

from emp e,dept d

where e.deptno=d.deptno(+)

(+)在右侧，表示左外连接，会列出在左表中出现但是没有在右表出现的行；同理，右外连接是将（+）放到左侧，表示保留右表中所有行；

表连接的写法：from emp e, dept d where  关联条件 and 筛选条件，是Oracle中的特有写法；

通用写法是：

内连接：from emp e inner join dept d on 关联条件 where 筛选条件

左外连接： from emp e left (outer) join  dept d on关联条件 

右外连接： from emp e right (outer) join  dept d on关联条件 

全外连接： from emp e full (outer) join  dept d on关联条件 

3、自连接：

select e.empno,e.ename,e.mgr,m.ename

from emp e,emp m

where e.mgr=e.empno;

4、自然连接：必须对应主外键相同

5、笛卡尔积的形式，称为交叉连接 cross join

（三）DML SELECT 子查询

1、子查询是个值，放在where后面，例：查询和MILLER同一部门的员工编号和姓名

select empno,ename from emp where deptno=(select deptno from emp where ename='MILLER');

2、子查询本质是个表，可放在from后面，例：查询员工编号，员工姓名，所在部门编号，所在部门人数

select e.empno,e.ename,e.deptno,d.cnt

from emp e,(select deptno,count(*) cnt from emp group by deptno) d

where e.deptno=d.deptno;

其中，该子查询作为一个连接用的表，因规范表字段名中不能有*特殊字符，count(*)必须起别名

3、子查询是个值，还可放在select后面，例：查询员工编号，员工姓名，员工总人数

select empno,ename,(select count(*) from emp)  from emp;

该例也可以写在from后，通过笛卡尔积（交叉连接方式）实现：

select empno,ename,c.cnt from emp,(select count(*)  cnt from emp) c;

（四）DML 插入更新删除

1、插入

（1）insert into 表名 (列1，列2,...) values();

（2）insert into 表名 values（）；

（3）insert into 表名 (列1，列2,...)  select  (列1，列2,...) ； 

（4）insert into 表名 select 语句块；

2、更新

update 表名 set 要修改的字段名1=新值1，

                         要修改的字段名2=新值2

where 条件；

 

拓展-关联更新：

update emp a set deptno=

(select b.deptno from dept b where b.deptno=a.deptno)

where exists(select 1 from dept b where b.deptno=a.deptno)

3、删除

delete from 表名 where..; 可rollback找回；速度慢，

而truncate table temp_bak;截断表，直接全部删除，无法rollback，速度快，truncate实际是属于DDL语句。

二、表的创建和管理

表中字段名只能是字母、数字、下划线的组合。

数据类型：字符串 varchar  数字 number  日期 date datetimestamp  CLOB 长文本   BLOB二进制图片视频等

查看表结构的数据库命令：desc tablename

1、创建表: create table tbname(字段1 类型1，字段2 类型2，。。。);

2、修改表结构

（1）增加列： alter table 表名 add(列名 数据类型)；

（2）删除列：alter table 表名 drop column 列名；

（3）修改列：alter table 表名 modify (列名 数据类型)；

注意：修改列如果跨数据类型修改，那么要保证数据为空，否则修改失败；

如果同数据类型，那么要保证改长不改短，否则可能修改失败。

3、复制表

create table 表名 AS (子查询)；

说明：当子查询不成立时，比如条件为1=2，则只复制表的结构，不复制表的数据；

4、删除表

删除表数据：delete  truncate

删除表连同结构：drop table 表名；

修改表名：rename 旧表名 to 新表名；