Oracle之SQL基础命令

最新推荐文章于 2022-10-24 18:15:28 发布

java_shuai0570

最新推荐文章于 2022-10-24 18:15:28 发布

阅读量372

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/java_shuai0570/article/details/114162219

版权

#SQL语法
##sql简介
结构化查询语言（Structured Query Language）简称SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。

1、DML（数据库操作语言）：其语句包括动词INSERT,UPDATE,DELETE。它们分别用于添加，修改和删除表中的行，也称为动作查询语言。
2、DDL（数据库定义语言）：其语句包括动词CREATE和DROP。在数据库中创建新表或删除表;为表添加索引等；DDL包括许多与人数据库目录中获得数据有关的保留字，也是动作查询的一部分。
3、DCL（数据库控制语言）

查询语法:

//查询emp表中所有的数据
select * from emp;
select empno,ename,job from emp;
//设置别名Select 列名 别名，列名 别名，... from emp;
select empno as 员工编号,ename as 员工姓名 from emp;
select empno eno,ename en from emp; --as可以省略，较为常用
//除重复的数据 使用distinct 可以消除重复的行，如果查询多列的必须保证多列都重复才能去掉重复；Select distinct *|列名，... from emp;
select distinct job from emp;
//列合并(字符串连接使用“||”
select distinct ename,job,deptno from emp; --只有所有的列全相同才被认为是相同的行可以合并
//合并字符串
select '员工编号是'||empno||'姓名是'||ename||'的工作是'||job from emp;
//查询的数据中可以进行运算符运算，
select ename,sal*12 from emp;

二：限定查询

在这里插入代码片
Select * from emp where sal > 1500;
//非空和空的限制。只要字段中存在内容表示不为空，如果不存在内容就是null
Select * from emp where comm is not null;
Select * from emp where comm is null;
//查询工资大于1500并且有奖金领取的雇员（and为并且，or为或者）
Select * from emp where sal >1500 and comm is not null;
Select * from emp where sal > 1500 or comm is not null;
//查询工资不大于1500和没有奖金的人  （NOT（查询条件））
Select * from emp where sal <= 1500 and comm is null;
Select * from emp where not(sal >1500 or comm is not null);
//查询基本工资在1500到3000 元之间的员工（between...and...等于 sal >= 1500 and sal <=3000）。
Select * from emp where sal >= 1500 and sal <=3000;
Select * from emp where sal between to_date('1981-1-1',yyyy-MM-dd)and to_date('1981-1-1','yyyy-MM-dd');
//查询员工名字为'SMITH'的员工
Select * from emp where ename = 'SMITH'; --sql语句不区分大小写，但是表中的值是需要区分的。
//查询员工编号是7369，7499，7521的员工编号（用OR关键字，用IN关键字（其中的值不仅可以是数值也可以是字符串））
Select * from emp where empno=7369 or empno=7499 or empno=7521;
Select * from emp where empno in (7369,7499,7521);
Select * from emp where ename in('SMITH','ALLEM')
//模糊查询，使用like语句(“%”：可以匹配任意长度的内容
//“_”:可以匹配一个长度的内容)
Select * from emp where ename like '_M%';
Select * from emp where ename like '%%';
//不等号的用法可以有两种形式<>和！=
Select * from emp where empno != 7369;
Select * from emp where empno <> 7369;

三：对结果集排序

//SELECT *|列名 FROM 表名 ｛WEHRE 查询条件｝ORDER BY 列名1 ASC|DESC,列名2 ASC|DESC...(默认是asc升序）
//注意：ORDER BY 语句要放在sql的最后执行
Select * from emp order by sal asc;
Select * from emp order by sal desc;
Select * from emp order by sal desc,hiredate asc;

四：单行函数
1：字符函数

//把小写的字符转换成大写的字符（upper（‘smith’））
select upper('smith') from dual;
//把大写的字符变成小写的字符lowe（‘SMITH’）
select lower('SMITH') from dual;
//把首字母大写initcap（‘smith’）
select initcap('smith') from dual;
//字符串的连接可以使用concat或者使用“||”，这里建议使用“||”
select concat('hello', 'world') from dual;
select 'hello' || 'world' from dual;--建议使用
//字符串截取substr（‘***’，*，*），第一个参数是源字符串，第二个参数是开始索引，第三个参数结束的索引，
//开始的索引使用1和0效果相同
select substr('hello', 1, 3) from dual;--索引从1开始
select substr('hello', 0, 3) from dual;
//获取字符串的长度用length关键字
select length('hello') from dual;
//字符串替换用replace（‘***’，*，*），第一个参数是源字符串，第二个参数是被替换的字符串，第三个参数是替换的字符串
select replace('hello', 'l', 'x') from dual;

2、数值函数

//四舍五入函数round关键字（可以指定保留的位数）
select round(12.48) from dual;
select round(12.487, 2) from dual; 
//取整函数trunc，默认全部去掉小数，也可以指定保留的位数
select trunc(12.87) from dual;
select trunc(12.635, 2) from dual;
//取余用MOD关键字
select mod(10, 3) from dual;

3、日期函数

/**
*Oracle中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字(天)
*/
//查询员工进入公司的周数，用trunc函数（sysdate关键字是指当前日期，hiredate是指进入公司的日期）
select sysdate from dual;
select ename, trunc((sysdate - hiredate)/7) from emp;
//查询两个时间段中的月数（months_between）
select ename, trunc(months_between(sysdate, hiredate)) from emp;
//获得几个月后的日期：ADD_MONTH()
select add_months(sysdate, 6) from dual;
//4.指定给出下次某日期数NEXT_DATE(),例：求出下一个星期日是什么日期
select next_day(sysdate, '星期日') from dual;
5.求出一个日期的最后一天 ，例：求出本月的最后一天是几号
select last_day(sysdate) from dual;

4、转换函数

TO_CHAR:字符串转换函数
范例：查询所有的雇员将将年月日分开，此时可以使用TO_CHAR函数来拆分
拆分时需要使用通配符
年：y, 年是四位使用yyyy
月：m, 月是两位使用mm
日：d, 日是两位使用dd
时：HH是12进制，HH24进制
分：mi
秒：ss

//查询员工入职时的时间，用to_char函数
select ename,
       to_char(hiredate, 'yyyy') 年,
       to_char(hiredate, 'mm') 月,
       to_char(hiredate, 'dd') 日,
       to_char(hiredate, 'HH') 时,
       to_char(hiredate, 'mi') 分,
       to_char(hiredate, 'ss') 秒
  from emp;
select to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') from dual;
select to_char(sysdate, 'yyyy-mm-dd HH:mi:ss') from dual;
//查询入职时间的年月日（用fm放在前面表示去掉前导0）
select ename, to_char(hiredate, 'yyyy-mm-dd') from emp;
select ename, to_char(hiredate, 'fmyyyy-mm-dd') from emp;
//给数字做格式化（把员工的工资按三位用“，”分隔，在Oracle中“9”代表一位数字，如果在钱的前面用“￥”表示美元，用“L”表示本地钱的单位）
select ename , to_char(sal, '$99,999') from emp;
select ename , to_char(sal, 'l99,999') from emp;
//数值转换函数（to_number），可以把字符串转换成数值
select to_number('10') + to_number('10') from dual;
//日期转换函数（to_date）
select to_date('1985-04-22', 'yyyy-mm-dd') from dual;
select to_date('1985-04-22 12:30:20', 'yyyy-mm-dd HH24:mi:ss') from dual;
/**还有一个通用函数
空值处理nvl，查询所有的雇员的年薪时有时奖金数为null
*/
Select ename,nvl(comm,0),sal*12+nvl(comm,0) from emp;
//Decode函数，该函数有点像if...else if...esle
/**语法为：DECODE（col/expression,[search1,result1],[search2,result2],...[default]）
*Col/expression:列名或表达式
*Search1，search2...:用于比较的条件
*Result1, result2...:返回值
*如果col/expression和Searchi匹配就返回resulti,否则返回default的默认值
*/

5.多表查询
使用两张或两张以上的表就是多表查询。
语法：SELECT {DISTINCT} *|列名… FROM 表名别名，表名1 别名1，｛WHERE 限制条件 ORDER BY 排序字段 ASC|DESC…｝
笛卡尔积：当我们查询多张表时，查询到的标的记录数为其他表的记录数的乘积的现象
我们可以找一个共同的字段，使用这个字段做限制条件，两张表的关联字段，一般是其中一张表的主键，是另一张表的外键
多表查询我们可以为每一张表起一个别名，这样方便我们后面编写。

//多表查询  一般设置别名，不设置别名时用表名来查找
select * from emp e, dept d where e.deptno = d.deptno ;
select * from emp, dept  where emp.deptno = dept.deptno ;
select e.empno, e.ename, d.deptno, d.dname, d.loc
  from emp e, dept d
 where e.deptno = d.deptno;
 
 ---查询出每一个员工的上级领导
 select e.empno, e.ename, e1.empno, e1.ename
   from emp e, emp e1
  where e.mgr = e1.empno;
  
  --查询每一个员工的上级领导和部门名称
  select e.empno, e.ename, e1.empno, e1.ename, d.dname
    from emp e, emp e1, dept d
   where e.mgr = e1.empno
     and e.deptno = d.deptno;
--查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select e.empno,
       e.ename,
       d.dname,
       decode(s.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级',
              '无级') egrade,
       e1.ename,
       decode(s1.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级',
              '无级') e1grade
  from emp e, dept d, salgrade s, emp e1, salgrade s1
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal
   and e.mgr = e1.empno
   and e1.sal between s1.losal and s1.hisal

#外连接（左右连接）
1右连接
当我们在做基本连接查询的时候，查询出所有的部门下的员工，但是此时这个部门下没有员工，查询时要求把部门也展现出来，所有用到左右外连接。

---查询出每一个部门的员工
--两张表做连接查询时其中一张表要查询全量的数据（不会因为另一张的表的数据的关联而被筛选掉），
--在两张表关联的时候非全量表的关联字段后面加上（+）就可以做外连接查询
select * from dept d, emp e where d.deptno = e.deptno(+);

--做外连接查询的时候全量表在左端就是左连接
select * from dept d, emp e where d.deptno = e.deptno(+);
--做外连接查询的时候全量表在右端就是右连接
select * from emp e, dept d where e.deptno(+) = d.deptno;

--查询员工的编号和姓名和他的上级领导的编号和姓名
select e.empno, e.ename, e1.empno, e1.ename
  from emp e, emp e1
 where e.mgr = e1.empno(+);


//交叉连接  cross join（会产生笛卡尔积）
select * from emp e cross join dept d;
//自然连接 natural join（会自动分析管理，去掉笛卡尔积）
select * from emp natural join dept;
//USING子句，直接管理操作（了解）
select * from emp join dept using(deptno) where deptno = 20;

select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 20;
//ON字句，自己编写连接条件（ON相当于where）
select * from emp e join dept d on e.deptno = d.deptno ;
--以下的sql1999的外连接查询(重点)
select * from dept d left join emp e on d.deptno = e.deptno;

select * from dept d, emp e where d.deptno = e.deptno(+);

select * from emp e right join dept d on e.deptno = d.deptno;

select * from emp e, dept d where e.deptno(+) = d.deptno;
//左右连接的两种写法，一种是left join...on...  一种是在where判断条件后面添加加号
select e.empno, e.ename, e1.empno, e1.ename
  from emp e
  left join emp e1
    on e.mgr = e1.empno;

select e.empno, e.ename, e1.empno, e1.ename
  from emp e, emp e1
 where e.mgr = e1.empno(+);

6、分组函数
（1）统计记录数count（）
注意：不建议使用count（*），因为有可能会影响性能；
（2）最小值查询min()
（3）最大值查询 max（）
（4）平均值查询 avg（）
（5）求和函数sum（）

select * from emp;
//统计记录数 不建议使用count（*），接具体的列最好
select count(*)  from emp;
select count(empno) empnum from emp; --建议使用
//最小值查询min（）
select min(sal) minsal from emp; 
//最大值查询 max（）
select max(sal) maxsal from emp;
//平均值查询 avg（）
select avg(sal) avgsal from emp;
//求和函数sum（）
select sum(sal) sumsal20 from emp where deptno = 20;

7、分组统计
分组统计需要使用GROUP BY 来分组
语法：SELECT *|列名 FROM 表名｛WHERE 查询条件｝｛GROUP BY 分组字段1，分组字段2，…｝ORDER BY 列名1 ASC|DESC

//查询每个部门的人数
select * from emp order by deptno;
select count(empno), deptno from emp group by deptno;
//查询每个部门的平均工资
select avg(empno) avgsal, deptno from emp group by deptno;

select count(*), deptno from emp ;--会报错，因为没有group by分组所以统计函数统计的是整张表的 数据，
--我们却非要强加在部门编号上市不对的，所以想要查询出来部门下的统计信息就必须要分组
---    14, 30
--         20
--         10   

select count(*), d.deptno, d.dname, d.loc
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno,d.dname, d.loc;
 
 
 select count(*), d.deptno
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno;
 
 select count(*), deptno, job from emp group by deptno, job order by deptno;

/**
--部门人数大于5人的部门（这里加入了一个having，）
需要给count(ename)加条件，此时在本查询中不能使用where，可以使用HAVING在group by后面作用在分组函数上
*/
select count(*) empnum, d.deptno, d.dname, d.loc
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno, d.dname, d.loc
having count(*) > 5;


--范例：查询出部门平均工资大于2000的部门
select avg(sal), deptno
  from emp
 group by deptno
having avg(sal) > 2000

/*显示非销售人员工作名称以及从事同一工作的员工的月工资的总和，
并且要满足从事同一工作月工资总和大于5000，结果按月工资总和的升序排列。*/
//1：显示非销售人员工作名称
Select e.name from emp e where e.job <> 'SALESMAN';
//2：显示非销售人员工作名称以及从事同一工作的员工的月工资的总和
Select sum(e.sal),e.job from emp e where e.job <> 'ASLESMAN' group by e.job;
//3：再加上月工资大于5000（having sum(e.sal) > 5000）
//4：再加上按月工资总和的升序排列（order by sum(e.sal) asc）
select sum(e.sal), e.job
  from emp e
 where e.job <> 'SALESMAN'
 group by e.job
having sum(e.sal) > 5000
 order by sum(e.sal) asc

##子查询（和MySQL中嵌套查询差不多）
子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。
1：Sql的任何位置都可以加入子查询。
2：所有的子查询必须在“（）”中编写
3：子查询在操作中有三类：
单行单列列子查询：返回的结果是一列的一个内容
多行单列子查询：返回多个行一个列的内容，
多行子查询：返回多条记录

--查询出比雇员7654的工资高，同时从事和7788的工作一样的员工
 select *
   from emp t
  where t.sal > (select e.sal from emp e where e.empno = 7654)
    and t.job = (select e.job from emp e where e.empno = 7788);
--要求查询每个部门的最低工资和最低工资的雇员和部门名称
select t.*, d.dname
  from emp t,
       (select min(e.sal) minsal, e.deptno from emp e group by e.deptno) a,
       dept d
 where t.sal = a.minsal
   and t.deptno = a.deptno
   and t.deptno = d.deptno;
   
   --查询出来所有和每个部门最低工资的员工工资相等的人
  select *
    from emp t
   where t.sal in (select min(e.sal) from emp e group by e.deptno)

##Exists 和 not exists 关键字的用法
exists（sql 返回结果集为真）
not exists（sql 不返回结果集为真）

--查询出有员工的部门有哪些？
--in关键字尽量要少使用，因为性能比较低，可以使用exists来代替性能很高
select * from dept t where t.deptno in (select distinct deptno from emp);
--exists()  子查询可以查询出结果，则是true，查询不出数据就是false；
-- exists子查询（一般是要和外侧查询关联的）
Select * from dept d where exists (select * from emp e where e.deptno = d.deptno);
Select * from dept d where not exists (select * from emp e where e.deptno = d.deptno);

##Union和Union All 的用法
Union：对两个结果集进行并集操作，结果去掉了重复行。
Union All：对两个结果集进行并集操作，结果集中保留了重复行。

--union把两个集合做并集的时候会把重复的数据去掉留一份
select * from emp t where t.sal > 1000
union
select * from emp t where t.sal > 2000;

--union把两个集合做并集的时候会不去重
select * from emp t where t.sal > 1000
union all
select * from emp t where t.sal > 2000;
注意：并集时要合并的列类型和列数必须一致，列名可以不一致,。
select t.empno, t.ename, t.job from emp t where t.empno = 7499
union 
select t.empno, t.ename, t.job from emp t where t.empno = 7788;

#8、数据库变更（删改）
Create table 表名 as select * from 表名（原表）；
1.INSERT（增加）

//语法：
INSERT INTO 表名[(列名1，列名2，...)] VALUES(值1，值2，...);

注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null

2.update（修改）

//全部修改：
UPDATE 表名 SET 列名1=值1，列名2=值2，....
//局部修改：
UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；

3.delete（删除）

//语法 : 
DELETE FROM 表名	WHERE	删除条件;

#9.事务处理
事务处理：所谓的事务管理就是要保证数据的完整性，要么同时成功，要么同时失败。
是因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback

在oracle中会数据库变更会发生锁的情况（此处应用可以解决项目多线程并发带来的数据安全问题）
当两个数据库的连接同时来修改同一数据时，一定会有一连接先修改，另一个连接就会等待直到第一个连接修改完毕再修改

.表的管理
1.常用的数据类型

	数据类型	描述
1	Varchar	varchar2 表示一个字符串
2	NUMBER	NUMBER(n)表示一个整数，长度是n
		NUMBER(m,n):表示一个小数，总长度是m，小数是n，整数是m-n
3	DATA	表示日期类型
4	CLOB	大对象，表示大文本数据类型，可存4G
5	BLOB	大对象，表示二进制数据，可存4G

##2、建表
1.语法：
Create table 表名（
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
…
字段n 数据类型 [default 默认值]
）;

2.表删除
语法：DROP TABLE 表名

3.表的修改
在sql中使用alter可以修改表
添加语法：ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)
修改语法：ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)
注意修改时如果原有某列的数据长度为200，则不可以把该列修改成小于200的列
范例：在person表中增加列address
alter table person add(address varchar2(10));
范例：把person表的address列的长度修改成20长度
alter table person modify(address varchar2(20));

4.截断表（谨慎使用）
在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法
语法：TRUNCATE TABLE 表名
范例：截断person表
truncate table person;

11.约束
在数据库开发中，约束是必不可少，使用约束可以更好的保证数据的完整性。
1.主键约束(primary key)
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。
注意：主键不可重复， SCOTT.SYS_C0017981是系统自动分配的约束的名字，主键不可为空

创建一张表，把pid作为主键
create table person(
       pid      number(10) primary key,
       name     varchar2(10),
       ...

2.非空约束（not null）
使用非空约束，可以使指定的字段不可以为空。

create table person(
       pid      number(10) not null,
       ...

3.唯一约束（unique）
表中的一个字段的内容是唯一的

唯一约束的名字也可以自定义
constraint person_name_uk unique(name)

create table person(
       pid      number(10) ,
       name     varchar2(10),
       gender   number(1)  ,
       birthday date,
       constraint person_name_uk unique(name)
);

4.检查约束 (check(…要求…))
使用检查约束可以来约束字段值的合法范围。

//范例：创建一张表性别只能是1或2
create table person(
       pid      number(10) ,
       name     varchar2(10),
       gender   number(1)  check(gender in (1, 2)),
       birthday date
);
//检查约束也可以自定义
create table person(
       pid      number(10) ,
       name     varchar2(10),
       gender   number(1),
       birthday date,
       constraint person_gender_ck check(gender in (1,2))
);

5.外键约束
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。

create table orders(
       order_id      number(10) ,
       total_price   number(10,2),
       order_time date,
      constraint orders_order_id_pk primary key(order_id)
);

create table order_detail(
       detail_id      number(10) ,
       order_id   number(10),
       item_name  varchar2(10),
       quantity   number(10),
      constraint order_detail_detail_id_pk primary key(detail_id),
      constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id)
);

外键关联一定注意：
外键一定是主表的主键
删表时一定先删字表再删主表，如果直接删主表会出现由于约束存在无法删除的问题
在这里插入图片描述
但是可以强制删除drop table orders cascade constraint;(不建议)
删除主表的数据可以删除子表的关联数据，再删主表，也可以使用级联删除级,联删除在外键约束上要加上on delete cascade 如
constraint order_detail_order_id_fk foreign key(order_id)
references orders(order_id) on delete cascade
这样删除主表数据的时候会把字表的关联数据一同删除

12.rownum
ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
注意：rownum不支持大于号；

//范例：查询emp表带有rownum列
select rownum, t.* from emp t
//我们可以根据rownum来取结果集的前几行，比如前5行
select rownum, t.* from emp t where rownum < 6;
//-rownum 不支持大于号，所以下面的这种分页方式是错误的
select rownum, t.* from emp t where rownum > 5 and rownum < 11;

--分页步骤
--第一步查询全量的数据
select * from emp;

--第二步以第一步的结果集作为一张表，限定条件是rownum小于结束行号， 结果列把rownum作为结果集
select rownum rw, a.* from (select * from emp) a where rownum < 6;

--第三步以第二步的结果集作为一张表，限定条件是第二步的rownum列大于开始行号，结果列是*
select *
  from (select rownum rw, a.* from (
  select * from emp
  ) a where rownum < 16) b
 where b.rw > 10;
//分页查询就这三步走

/*
已知条件
pageNo:当前的页码
pageSize:每页记录数

未知条件
startNum：开始行号
endNum：结束行号
pageNo      pageSize       startNum      endNum
   1            5              0            6          
   2            5              5            11
   3            5              10           16
   所以：
   startNum = (pageNo - 1)*pageSize;
   endNum = pageNo*pageSize + 1;

*/

13.视图
视图就是封装了一条复杂查询的语句。
语法1.：CREATE VIEW 视图名称 AS 子查询
范例：建立一个视图，此视图包括了20部门的全部员工信息
create view empvd20 as select * from emp t where t.deptno = 20
视图创建完毕就可以使用视图来查询，查询出来的都是20部门的员工
在这里插入图片描述 语法2：CREATE OR REPLACE VIEW 视图名称 AS 子查询
如果视图已经存在我们可以使用语法2来创建视图，这样已有的视图会被覆盖。
create or replace view empvd20 as select * from emp t where t.deptno = 20
那么视图可以修改吗？
可以修改，我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。
我们可以设置视图为只读。
为scott设置管理员权限
qrant connect,resource,dba to scott;

select * from emp t where t.deptno = 20;
--创建部门是20的员工的视图
create view view_d20 as select * from emp t where t.deptno = 20;
--查询视图
select * from view_d20;
--创建视图的时候查询的sql不能有重复的列名
create view view_grade as select e.empno,
       e.ename,
       d.dname,
       decode(s.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级',
              '无级') egrade,
       e1.ename enamel,
       decode(s1.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级',
              '无级') e1grade
  from emp e, dept d, salgrade s, emp e1, salgrade s1
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal
   and e.mgr = e1.empno
   and e1.sal between s1.losal and s1.hisal;
--查询视图
select * from view_grade;

--创建视图或者覆盖视图
create or replace view view_d20 as select * from myemp t where t.deptno = 20;

select * from view_d20;
--修改视图, 修改视图其实是修改视图对于的表的数据，视图不建议修改
update view_d20 t set t.ENAME = '史密斯' where t.EMPNO = 7369;

select * from myemp;
--创建只读的视图
create or replace view view_d20 as select * from myemp t where t.deptno = 20 with read only;

语法3：CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY
create or replace view empvd20 as select * from emp t where t.deptno = 20 with read only

14.序列
在很多数据库中都存在一个自动增长的列,如果现在要想在oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
语法：CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];

注释：
[INCREMENT BY n] :每次增长多少
[START WITH n] ：从多少开始
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]：设置最大值和最小值
[{CYCLE|NOCYCLE}] ：循环序列还是不循环序列
[{CACHE n|NOCACHE}];：对缓存的使用

范例:创建一个seqpersonid的序列,验证自动增长的操作
查看序列：CREATE SEQUENCE seqpersonid;

在实际中每一张表会配一个序列，但是表和序列是没有必然联系的，一个序列被哪一张表使用都可以，但是一般都是一张表用一个序列，
序列的管理一般使用工具来管理

--序列虽然是给某个表使用，但是序列并没有绑定字某一张表，任何一张表使用这个序列都可以
create sequence seqpersonid;

--查询序列的下一个值(重点)
select seqpersonid.nextval from dual;
--查看当前值是多少
select seqpersonid.currval from dual;
//添加数据
insert into person
  (person_id, pname, gender, birthday)
values
  (seqpersonid.nextval, '张三', 1, sysdate);
  //查询数据
select * from person;

15索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:
为什么添加了索引之后，会加快查询速度呢？
图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询,按姓名查或者类别查,这样的话速度会快很多很多，
这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，
所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。
现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。
创建索引的语法：
创建索引：
1．单例索引
单例索引是基于单个列所建立的索引，比如:
CREATE index 索引名 on 表名(列名)
2．复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是
要求列的组合必须不同,比如：
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);
范例：给person表的name建立索引
create index pname_index on person(name);
范例：给person表创建一个name和gender的索引
create index pname_gender_index on person(name, gender);
索引的使用原则：
在大表上建立索引才有意义
在where子句后面或者是连接条件上建立索引
索引的层次不要超过4层

//创建单例索引
create index pname_index on person(pname);
//索引不需要主动使用，根据索引列查询时就自动的使用了索引
select * from person t where t.pname='张三'；

//复合索引，查询数据使用的时候是有索引顺序的（可以用两列以上创建索引）
create index pg_index on person(pname,gender);
select * from person t where t.gender = 1 and t.pname='张三'；

、//下面这sql就不会使用pg_index，因为顺序不一样了
select * from person t where  t.gender = 1 and t.pname = '张三';
create index gp_index on person(gender, pname);

#9.pl/sql
什么是PL/SQL?
PL/SQL（Procedure Language/SQL）
PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。
范例1：为职工长工资，每人长10％的工资。
Update emp set sal=sal*1.1
范例2：例2: 按职工的职称长工资,总裁长1000元,经理长800元，其他人员长400元。
这样的需求我们就无法使用一条SQL来实现，需要借助其他程序来帮助完成，也可以使用pl/sql。
1.pl/sql程序语法
1.程序语法：
declare
说明部分（变量说明，游标申明，例外说明〕
begin
语句序列（DML语句〕…
exception
例外处理语句
End;

select * from myemp;

update myemp t set t.sal = t.sal + 100 ;


--declare

begin
  dbms_output.put_line('hello world');
end;

--基本数据类型变量和常量
declare
  pname   varchar2(10);//（定义一个常量，每句话后面都要有一个分号结尾）**
  age    number(3) := 20;//（定义一个变量，这里的等于号变成了 “：=”）**
begin
  pname := 'zhangsan';
  dbms_output.put_line(pname);//**（进行输出）**
  dbms_output.put_line(age);
end;
--引用数据类型变量
declare
  pname myemp.ename%type;//**（定义一个引用变量，获取一个数据）**
begin
  select t.ename into pname from myemp t where t.empno = 7369;//**（把查到的t.ename用into赋值给pname）**
  dbms_output.put_line(pname);//（进行输出）
end;
--记录类型变量，可以对应java中的对象类型变量
declare
  prec myemp%rowtype;//（记录型变量）
begin
  select * into prec from myemp t where t.empno = 7369;//（把查到的所有数据都赋值给prec）
  dbms_output.put_line(prec.ename || '   '|| prec.sal);//（把赋值的数据按照所需要的打印出来）

3. if分支
语法1：
IF 条件 THEN 语句1;
语句2;
END IF;
语法2：
IF 条件 THEN 语句序列1；
ELSE 语句序列 2；
END IF；
语法3：
IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSE 语句;
END IF;

declare
  pno number(4) := &num;
  //&num表示手动输入
begin
  if pno < 5 then
    dbms_output.put_line('编号小于5');
  end if;

end;
---------------------------------------------

declare
  pno number(4) := &num;

begin
  if pno = 1 then
    dbms_output.put_line('我是1');
  else
    dbms_output.put_line('我不是1');
  end if;
end;
------------------------------------------------
declare
  pno number(4) := &num;

begin
  if pno = 1 then
    dbms_output.put_line('我是1');
  elsif pno = 2 then
    dbms_output.put_line('我是2');
  else
    dbms_output.put_line('其他');
  end if;
end;

4.LOOP循环语句
其中语法2比较常用
语法1：
WHILE total <= 25000 LOOP
… .
total : = total + salary;
END LOOP;

语法2：
Loop
EXIT [when 条件];
……
End loop

语法3：
FOR I IN 1 . . 3 LOOP
语句序列 ;
END LOOP ;

//使用语法输出1到100的数字
declare
  total number(4) := 0;
begin
  while total < 100 loop
    total := total + 1;
    dbms_output.put_line(total);
  end loop;
end;


------------------------------------
--最常用的循环方式

declare
  total number(4) := 0;

begin
  loop
    exit when total = 100;//判断条件为到100结束循环
    total := total + 1;
    dbms_output.put_line(total);
  end loop;
end;
----------------------------------
--适合于连续的数值的遍历
declare
  total number(4) := 0;
begin
  for total in 1 .. 100 loop
    dbms_output.put_line(total);
  end loop;
end;

5.游标Cursor（相当于Java中集合类变量，一般结合循环）
在写java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。
语法：
CURSOR 游标名 [ (参数名数据类型,参数名数据类型,…)] IS SELECT 语句;
例如：cursor c1 is select ename from emp;
游标的使用步骤：
打开游标： open c1; (打开游标执行查询)
取一行游标的值：fetch c1 into pjob; (取一行到变量中)
关闭游标： close c1;(关闭游标释放资源)
游标的结束方式 exit when c1%notfound（为true的话表示退出）
注意：上面的pjob必须与emp表中的job列类型一致：
定义：pjob emp.empjob%type;

//使用游标的方式输出emp表中的员工编号和姓名
declare
  cursor c1 is
    select * from emp; --定义游标
  prec emp%rowtype; --定义记录型的变量
begin
  open c1;--打开游标
  loop
    fetch c1
      into prec;--从游标中取值，取值后游标会自动向下移动一步
    exit when c1%notfound;
    dbms_output.put_line(prec.empno || '   '|| prec.ename);
  end loop;--结束循环
  close c1; --关闭游标
end;
	
//按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元。
select * from myemp;

declare
	prec myemp%rowtype;
	cursor c1 is 
		select * from myemp;
	addsal number(4);
begin
//打开游标
	open c1 into prec;
	exit when c1%notfond;
	if prex.job = '总裁' then
		addsal := 1000;
	elsif prec.job = '经理长' then
		addsal := 800;
	else
		addsal := 400;
		end if ;
		update myemp t set t.sal = t.sal + addsal where t.empno = prec.empno;
	end loop;
//关闭游标
close c1;
commit;
end;

//写一段PL/SQL程序，为部门编号为10的员工涨工资
select * from myemp t where t.deptno = 10;
declare
	cursor c1(dno myemp.deptno%type) is
		select * from myemp t where t.deptno =dno;
	prec myemp%rowtype;
begin
//打开带有参数的游标，除了此处其他的地方游标都不带参数
open c1(10);
loop
	fetch c1
	into prec;
exit when c1%notfound;
update myemp t set t.sal = t.sal + 1000 where t.empno = prec.empno;
end loop;
//关闭游标
close c1;
commit;
end;

6.例外(相当于Java中的异常处理)
例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。
系统定义例外
no_data_found (没有找到数据)
too_many_rows (select …into语句匹配多个行)
zero_divide ( 被零除)
value_error (算术或转换错误)
timeout_on_resource (在等待资源时发生超时)

   范例1：写出被0除的例外的plsql程序
declare
  pnum number;
begin
  pnum := 1 / 0;
exception
  when zero_divide then
    dbms_output.put_line('被0除');
  when value_error then
    dbms_output.put_line('数值转换错误');
  when others then
    dbms_output.put_line('其他错误');
end;

用户也可以自定义例外，在声明中来定义例外
DECLARE

My_job char(10);
v_sal emp.sal%type;
No_data exception;
cursor c1 is select distinct job from emp order by job;
如果遇到异常我们要抛出raise no_data;

---查询部门编号是50的员工
declare
  prec emp%rowtype;
  cursor c1 is
    select * from emp t where t.deptno = 50;
  no_data exception; --异常类型的定义
begin
  open c1;
  loop
    fetch c1
      into prec;
    if c1%notfound then
      raise no_data;  --抛出异常
    end if;
  end loop;
  close c1;
exception
  --处理异常
  when no_data then
    dbms_output.put_line('没有员工');
  when others then
    dbms_output.put_line('其他异常');
end;

**2.存储过程（相当于Java中的方法）
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。**存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

创建存储过程语法：需要编辑到数据库里面，才能被外界进行调用。
//输入输出参数可以有很多
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体；
End;
或者
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体；
End 过程名;

//创建一个输出helloword的存储过程
create or replace procedure helloworld as
begin
dbms_output.put_line('helloworld');
end helloworld;

调用存储过程
在plsql中调用存储过程
begin
helloworld;
end;

//范例2：给指定的员工涨100工资，并打印出涨前和涨后的工资
分析：我们需要使用带有参数的存储过程
cerate or replace procedure addSall (eno in number) is pemp myemp%rowtype;
begin
select * into pemp from myemp where empno = eno;
update myemp set sal = sal + 100 where empno = eno;
dbms_output.put_line('涨工资前'  || pemp.sal || '涨工资后' || (pemp.sal + 100));
end addSall;

调用
begin
addsall (eno => 7902);
commit;
end;