oracle 笔记

报错：ORA-28001: 口令已经失效

https://blog.csdn.net/qq_41419769/article/details/103890928

数据库启动关闭

find

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r5l4bBbi-1630125174945)(C:/Users/Administrator.DESKTOP-103F3OH/AppData/Roaming/Typora/typora-user-images/image-20210731080425540.png)]

设置plsql

SQL> set linesize 140
SQL> set pagesize 100
SQL> select * from emp;

配置文件

vi ./sqlplus/admin/glogin.sql

find 命令
https://www.cnblogs.com/xudong-bupt/archive/2013/03/23/2976793.html

soctt方案：表的介绍

修改上一条语句

SQL> select * feo emp;

SQL> c/feo/from/
  1* select * from emp
SQL> /

alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

select语句

常用

/*
MDL 操作语言--针对数据
DDL 针对表结构
DCL 询问控制 COMMIT ROLLBACK
DML CRUD
*/

select * from tab; -- 查看用户下所有表
select * from user_tables;
select * from emp;

desc emp; -- 使用非常多 查看字段描述

-- 数据字典(做项目生成数据字典)
--为表添加注释
comment on table emp is '雇员表';
--为列添加注释
comment on column emp.Empno is '雇员工号';

– 正式开始学习 sql 语句学习
selest -from where
SELECT [DISTINCT] {*.column alias,…}
column 表示列
distinct 表示去重复
alias 表示列的别名

查询开始(最重要–必考)

/* ================查询开始==========================*/
select * from tab;          /*查看用户下所有表*/ 
select * from user_tables;  /*详细查询用户下的表格*/
select * from emp; -- 可能显示不完整
-- 查看表结构
desc emp;  

-- 查询表中所有字段，不要用
select * from emp;
-- 查询雇佣表中的部门表示是10的员工
select empno,ename,job from emp where deptno = 10;
select job from emp where deptno = 10;
-- 去重distinct 可以针对多个字段deptno,sal，多个字段值只要又一个不匹配就算是不同的记录
select distinct deptno,sal from emp;

/* =====================查询结束=====================*/

别名alins

-- 查询中添加别名 alins  建议使用 多个表使用
-- 包含空格 添加“”
select e.empno as "雇员 编号"，e.ename as "雇员 名称" from emp e where e.deptno = 10;
select e.empno as 雇员编号，e.ename as 雇员名称 from emp e where e.deptno = 10;

总结：

添加别名 中间有空格使用“ ”

任何含有空值的表达式都是空值

字符串连接||

**distinct必须放在开头 **多个字段值只要又一个不匹配就算是不同的记录

where 过滤条件 过滤相关条件

关系运算符

/*sql语句学习
关系运算符
＝，!＝,<>，<,>,<=,>=,any,some,all
is null,is not null
between x and y
in（list），not in（list）
exists（sub－query）
like  _ ,%,escape ‘\‘   _\% escape ‘\’

*/
/*===========第一组==========*/
-- =
select * from emp where deptno = 20;
select * from emp where deptno = 10;
-- != 不等于
select * from emp where deptno != 20;
-- <> 不等于 ：= 赋值语句
select distinct * from emp where deptno <> 20;
-- <
select sal from emp where sal < 1500;
-- >
select sal from emp where sal > 1500;
-- >= 
select sal from emp where sal >= 1500;

-- any 大于其某一个值就成立
select sal from emp where sal > any(1000,1500,3000);
--  some 跟any 一样
-- all 大于所有值才能成立
select sal from emp where sal > all(1000,1500,3000);

/*===========第二组==========*/

-- is null ,is not null判断是是否为空 ，不能用=判断只能用is null ,is not null
select * from emp where comm is null;
select * from emp where comm is not null;
select * from emp where sal is null;
-- between x and y  包含x y 的值
select * from emp where sal between 1500 and 3000;
select * from emp where sal >= 1500 and sal <= 3000;

/*===========第三组==========*/
-- in（list），not in（list）
-- 需要进行某些值的等值判断的时候可以使用in 和 not in
select * from emp where deptno in(10,20);
select * from emp where deptno=10 or deptno=20;
-- and or 优先级 ,and 高于or 加（）提高优先级
select * from emp where deptno not in(10,20);
select * from emp where deptno != 10 and deptno != 20;

/*===========第四组==========*/
-- exists (sub -query 子查询) 
-- 使用子查询查询部门标号为10和20的员工，要求使用exists实现
select * from emp where deptno = 10 or deptno =20;
-- 通过外层循环来规范内层循环
select *
  from emp e
 where exists (select deptno
          from dept d
         where (d.deptno = 10 or d.deptno = 20)
           and e.deptno = d.deptno)


----------面试会考----平常并不用---
/*exists（sub－query）,当exists中的子查询语句能查到对应结果的时候，
意味着条件满足
相当于双层for循环
--现在要查询部门编号为10和20的员工，要求使用exists实现
*/
select * from emp where deptno = 10 or deptno = 20;
--通过外层循环来规范内层循环
-- 优先级
select *
  from emp e
 where exists (select deptno
          from dept d
         where (d.deptno = 10 or d.deptno = 20)
           and e.deptno = d.deptno)

模糊查询、转义字符escape

/*===========第五组==========*//*模糊查询：like  _ ,%,escape ‘\‘   _\% escape ‘\’在like的语句中，需要使用占位符或者通配符_,某个字符或者数字仅出现一次%，任意字符出现任意次数escape,使用转义字符,可以自己规定转义字符使用like的时候要慎重，因为like的效率比较低使用like可以参考使用索引，但是要求不能以%开头涉及到大文本的检索的时候，可以使用某些框架 luence，solr，elastic search*/--查询名字以S开头的用户select * from emp where ename like('S%');--查询名字以S开头且倒数第二个字符为T的用户select * from emp where ename like('S%T_');--查询名字中带%的用户, \% 转义字符 查找%select * from emp where ename like('%A%%') escape('A');select * from emp where enme like('%\%%') escape('\');/*===========第 ppt 十道题组==========*/select * from emp where deptno = 10;select * from emp where sal is null;

NULL

--null是比较特殊的存在，null做任何运算都还是为null，因此要将空进行转换--引入函数nvl，nvl(arg1,arg2),如果arg1是空，那么返回arg2，如果不是空，则返回原来的值select ename,(e.sal+nvl(e.comm,0))*12  from emp e;

-- is null ,is not null判断是是否为空 ，不能用=判断只能用is null ,is not nullselect * from emp where comm is null;select * from emp where comm is not null;select * from emp where sal is null;-- between x and y  包含x y 的值select * from emp where sal between 1500 and 3000;select * from emp where sal >= 1500 and sal <= 3000;

排序

/*===========排序 ==========*//*order by进行排序操作默认情况下完成的是升序的操作，asc:是默认的排序方式，表示升序desc：降序的排序方式排序是按照自然顺序进行排序的如果是数值，那么按照从大到小如果是字符串，那么按照字典序排序在进行排序的时候可以指定多个字段，而且多个字段可以使用不同的排序方式每次在执行order by的时候相当于是做了全排序，思考全排序的效率会比较耗费系统的资源，因此选择在业务不太繁忙的时候进行*/select * from emp order by sal;select * from emp order by sal desc;select * from emp order by ename;select * from emp order by sal desc,ename asc;------------使用计算字段----------字符串连接符 || concatselect 'mu name is '|| ename name from emp;select concat('my name is ',ename) from emp;--计算所有员工的年薪select ename,(e.sal+e.comm)*12 from emp e; -- 执行这个有问题--null是比较特殊的存在，null做任何运算都还是为null，因此要将空进行转换--引入函数nvl，nvl(arg1,arg2),如果arg1是空，那么返回arg2，如果不是空，则返回原来的值select ename,(e.sal+nvl(e.comm,0))*12  from emp e;--dual是oracle数据库中的一张虚拟表，没有实际的数据，可以用来做测试select 100+null from dual;

交集、并集、全集、差集

----------- 交集、并集、全集、差集--------------Aselect * from emp where deptno =30;--Bselect * from emp where sal >1000; --并集，将两个集合中的所有数据都进行显示，但是不包含重复的数据select * from emp where deptno =30 unionselect * from emp where sal >1000;--全集，将两个集合的数据全部显示，不会完成去重的操作select * from emp where deptno =30 union allselect * from emp where sal >1000;--交集，两个集合中交叉的数据集，只显示一次select * from emp where deptno =30 intersect select * from emp where sal >1000;--差集,包含在A集合而不包含在B集合中的数据，跟A和B的集合顺序相关select * from emp where deptno =30 minus select * from emp where sal >1000;

练习

1、查询部门编号为10的员工信息2、查询年薪大亍3万的人员的姓名不部门编号3、查询佣金为null的人员姓名不工资4、查询工资大亍1500 且 and 含有佣金的人员姓名5、查询工资大亍1500 戒 or含有佣金的人员姓名6、查询姓名里面含有 S 员工信息 工资、名称7、求姓名以J开头第二个字符O的员工姓名的不工资8、求包含%的雇员姓名9、使用in查询部门名称为 SALES 和 RESEARCH 的雇员姓名、工资、部门编号10、使用exists查询部门名称为SALES和RESEARCH 的雇员姓名、工资、部门编号。1、使用基本查询诧句. (1)查询DEPT表显示所有部门名称. (2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补劣),处理NULL行,幵指定列别名为"年收入" (3)查询显示丌存在雇员的所有部门号。2、限制查询数据 (1)查询EMP表显示工资超过2850的雇员姓名和工资。 (2)查询EMP表显示工资丌在1500～2850乊间的所有雇员及工资。 (3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。 (4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。 (5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。 (6)查询EMP表显示补劣非空的所有雇员名及其补劣。3、排序数据 (1)查询EMP表显示所有雇员名、工资、雇佣日期，幵以雇员名的升序进行排序。 (2)查询EMP表显示在1981年2月1日到1981年5月1日乊间雇佣的雇员名、岗位及雇佣日期，幵以雇佣日期进行排序。 (3)查询EMP表显示获得补劣的所有雇员名、工资及补劣，幵以工资升序和补劣降序排序。

单行函数

单行函数

多行函数

--函数的测试/*取出数据进行处理，不会改变数据库中的值分组函数又称为聚合函数  输入多个值，最终只会返回一个值  组函数仅可用于选择列表或查询的having子句  where 用表中规定好的字段单行函数  输入一个值，输出一个值*/--查询所有员工的薪水总和select sum(sal) from emp;--查看表中有多少条记录select deptno,count(*) from emp group by deptno where count(*) >3;select deptno,count(*) from emp group by deptno having count(*) >3;

字符函数

/*====================字符函数=====================*/--concat：表示字符串的连接  等同于||select concat('my name is ', ename) from emp;--将字符串的首字母大写select initcap(ename) from emp;--将字符串全部转换为大写select upper(ename) from emp;--将字符串全部转换为小写select lower(ename) from emp;--填充字符串select lpad(ename,10,'*') from emp;select rpad(ename,10,'*') from emp;--去除空格select trim(ename) from emp;select ltrim(ename) from emp;select rtrim(ename) from emp;--查找指定字符串的位置select instr('ABABCDEF','A') from emp;--查看字符串的长度select length(ename) from emp;--截取字符串的操作select substr(ename,0,2) from emp;--替换操作select replace('ababefg','ab','hehe') from emp;

数值函数

/*==============数值函数============*/--给小数进行四舍五入操作，可以指定小数部分的位数select round(123.123,2) from dual;select round(123.128,2) from dual;select round(-123.128,2) from dual;--截断数据,按照位数去进行截取，但是不会进行四舍五入的操作select trunc(123.128,2) from dual;--取模操作select mod(10,4) from dual;select mod(-10,4) from dual;--向上取整select ceil(12.12) from dual;--向下取整select floor(13.99) from dual;--取绝对值select abs(-100) from dual;--获取正负值select sign(-100) from dual; -- -1select sign(100) from dual;  -- +1--x的y次幂select power(2,3) from dual;

日期函数

/*==============日期函数============*/--日期函数-- mysql 和oralce查看时间不一样/* mysql : current_time */-- oralce 查询日期select sysdate from dual;  select current_date from dual;--add_months,添加指定的月份-- hiredateselect add_months(hiredate,2),hiredate from emp;select add_months(hiredate,2),hiredate from emp;--返回输入日期所在月份的最后一天select last_day(sysdate) from dual;--两个日期相间隔的月份-- sysdate最近  hiredateselect months_between(sysdate,hiredate) from emp;--返回四舍五入的第一天select sysdate 当时日期,round(sysdate) 最近0点日期,round(sysdate,'day') 最近星期日,round(sysdate,'month') 最近月初,round(sysdate,'q') 最近季初日期, round(sysdate,'year') 最近年初日期 from dual;--返回下周的星期几select next_day(sysdate,'星期一') from dual;--提取日期中的时间select extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年 from dual;--返回日期的时间戳select localtimestamp from dual;select current_date from dual;select current_timestamp from dual;--给指定的时间单位增加数值selecttrunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)trunc(sysdate)+(interval '1' hour), --加1小时(1/24)trunc(sysdate)+(INTERVAL '1' DAY),  --加1天(1)trunc(sysdate)+(INTERVAL '1' MONTH), --加1月trunc(sysdate)+(INTERVAL '1' YEAR), --加1年trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟from dual;

转换函数—重要

/*=================转换函数================*//*转换函数     在oracle中存在数值的隐式转换和显式转换     隐式转换指的是字符串可以转换为数值或者日期显式转换：    to_char: 当由数值或者日期转成字符串的时候，必须要规定格式    to_date    to_number    number：date 之间不能转换*/select '999'+10 from dual; -- 隐式转换select to_char(999+10) from dual;/*	YYYY 年	MM 月	DD日	DY 星期	HH24 24小时	MI 分钟	SS 秒*/--date ：to_char--sysdate 当前系统时间select to_char(sysdate,'YYYY-MI-SS HH24:MI:SS') from dual;select to_char(sysdate,'YYYY-MM-SS') from dual;-- number : to_charselect to_char(123.456789,'9999') from dual; -- 前有四个位置 一个空格select to_char(123.456789,'0000.00') from dual;select to_char(123.456789,'$0000.00') from dual; -- 显示$select to_char(123.456789,'L0000.00') from dual; -- 显示￥select to_char(123456789,'999,999,999,999') from dual;--to_date:转换之后都是固定的格式select to_date('2019/10/10 10:10:10','YYYY-MM-DD HH24:MI:SS') from dual;--to_number:转成数字select to_number('123,456,789','999,999,999') from dual;--显示没有上级管理的公司首脑select ename,nvl(to_char(mgr),'boss') from emp where mgr is null;--显示员工雇佣期满6个月后下一个星期五的日期select hiredate,next_day(add_months(hiredate,6),'星期五') from emp;

通用函数 nvl

条件函数

/*===================条件函数=================*/--条件函数-- 相当于if else 但是oracle中使用的关键字不同--decode,case when   -- decode (字段，值？，操作)-- decode 、case when --给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30%-- m1select ename,sal,deptno,decode(deptno,10,sal*1.1,										20,sal*1.2,										30,sal*1.3) from emp;--M2select ename,       sal,       deptno,       case deptno         when 10 then          sal * 1.1         when 20 then          sal * 1.2         when 30 then          sal * 1.3       end  from emp;------------------------------create table test(   id number(10) primary key,   type number(10) ,   t_id number(10),   value varchar2(5));insert into test values(100,1,1,'张三');insert into test values(200,2,1,'男');insert into test values(300,3,1,'50');insert into test values(101,1,2,'刘二');insert into test values(201,2,2,'男');insert into test values(301,3,2,'30');insert into test values(102,1,3,'刘三');insert into test values(202,2,3,'女');insert into test values(302,3,3,'10');select * from test;/*需求将表的显示转换为姓名      性别     年龄--------- -------- ----张三       男        50*/-- 起名称select decode(type, 1, value) 姓名,       decode(type, 2, value) 性别,       decode(type, 3, value) 年龄  from test;  select min(decode(type, 1, value)) 姓名,       min(decode(type, 2, value)) 性别,       min(decode(type, 3, value)) 年龄  from test group by t_id; 

分组函数-- 重点

多行输入一行输出

/*组函数,一般情况下，组函数都要和group by组合使用组函数一般用于选择列表或者having条件判断常用的组函数有5个avg()  平均值,只用于数值类型的数据min()  最小值，适用于任何类型max()  最大值，适用于任何类型count() 记录数,处理的时候会跳过空值而处理非空值    count一般用来获取表中的记录条数，获取条数的时候可以使用*或者某一个具体的列       甚至可以使用纯数字来代替，但是从运行效率的角度考虑，建议使用数字或者某一个具体的列       而不要使用*       sum()   求和，只适合数值类型的数据*/select avg(sal) from emp;select min(sal) from emp;select max(sal) from emp;select count(sal) from emp;select sum(sal) from emp;

group by – 必须掌握–重点重点

看一下为什么报错？

select ename, max(cout) from emp; -- 不是单组分组函数

答案：

select ename from emp where sal = (select max(sal) from emp);

再看；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fAm09m1e-1630125175011)(C:\Users\Administrator.DESKTOP-103F3OH\AppData\Roaming\Typora\typora-user-images\image-20210426223218174.png)]

select deptno ,max(sal) from emp group by deptno;

--group by,按照某些相同的值去进行分组操作--group进行分组操作的时候，可以指定一个列或者多个列，但是当使用了groupby 之后，--选择列表中只能包含组函数的值或者group by 的普通字段--求每个部门的平均薪水select avg(sal) from emp group by deptno;--求平均新书大于2000的部门select avg(sal),deptno from emp where sal is not null group by deptno having avg(sal) >2000 order by avg(sal);select count(10000) from emp;--部门下雇员的工资>2000 人数select deptno,count(1) from emp where sal>2000 group by deptno--部门薪水最高select deptno,max(sal) from emp group by deptno;--部门里面 工龄最小和最大的人找出来,知道姓名select deptno,min(hiredate),max(hiredate) from emp group by deptno;select ename, deptno  from emp e where hiredate in (select min(hiredate) from emp group by deptno)    or hiredate in (select max(hiredate) from emp group by deptno)select * from empselect mm2.deptno, e1.ename, e1.hiredate  from emp e1,       (select min(e.hiredate) mind, max(e.hiredate) maxd, e.deptno          from emp e         group by e.deptno) mm2 where (e1.hiredate = mm2.mind    or e1.hiredate = mm2.maxd)    and e1.deptno = mm2.deptno;select 子句顺序sel语句执行过程：       1.读取from子句的基本表       2.选取满足where子句中给出的条件表达式的元组       3.按group子句中指定列的值分组       4       5

having

group by

–group by,按照某些相同的值去进行分组操作
–group进行分组操作的时候，可以指定一个列或者多个列，但是当使用了groupby 之后，
–选择列表中只能包含组函数的值或者group by 的普通字段

having 对分组进行限制，对分组进行限制。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0p4u6FD6-1630125175024)(C:\Users\Administrator.DESKTOP-103F3OH\AppData\Roaming\Typora\typora-user-images\image-20210427175103660.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9JPKZNpq-1630125175027)(C:\Users\Administrator.DESKTOP-103F3OH\AppData\Roaming\Typora\typora-user-images\image-20210427175309039.png)]

多表查询

笛卡尔积

等值连接

就是加个等于号

select ename sal dname from emp e,dept d where d.deptno = e.deptno;

不等值连接

外连接

自连接

表自己跟自己做笛卡尔全集再过滤一下。

用到字符函数。

select e.ename || 'boss is' || b.ename from emp e, emp b where e.mgr = b.empno; -- 缺少king

加入外连接就好了。

子查询

在结果集中查询。嵌套。

按照部分分组之后，每个部门挣钱最多的人，的名字，的编号。

主、子查询 在不同表间进行

select * from emp where deptno = (select deptno from dept where dname = ‘SALES’)

单行子查询

只返回一条数据。

单行子查询注意：

多行子查询

IN

select * from emp where emp.deptno in (select deptno from dept where dname = ‘ACCOUNTING’ or dname = ‘SALES’);

ANY

select * from emp where sal >any (select sal from emp where deptno = 30);

ALL

子查询的空值

关联查询 – 重要重要重要

--关联查询/*select t1.c1,t2.c2 from t1,t2 where t1.c3 = t2.c4在进行连接的时候，可以使用等值连接，可以使用非等值连接*/--查询雇员的名称和部门的名称select ename,dname from emp,dept where emp.deptno = dept.deptno;--查询雇员名称以及自己的薪水等级select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;--等值连接，两个表中包含相同的列名--非等值连接，两个表中没有相同的列名，但是某一个列在另一张表的列的范围之中--外连接select * from emp;select * from dept;--需要将雇员表中的所有数据都进行显示,利用等值连接的话只会把关联到的数据显示，--没有关联到的数据不会显示，此时需要外连接--分类：左外连接（把左表的全部数据显示）和右外连接（把右表的全部数据显示）select * from emp e,dept d where e.deptno = d.deptno；--等值连接select * from emp e,dept d where e.deptno = d.deptno(+);--左外连接select * from emp e,dept d where e.deptno(+) = d.deptno;--右外连接--自连接,将一张表当成不同的表来看待，自己关联自己 ？？？--将雇员和他经理的名称查出来select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;--笛卡尔积,当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积，--关联后的总记录条数为M*n，一般不要使用select * from emp e,dept d;--92的表连接语法有什么问题？？？？--在92语法中，多张表的连接条件会方法where子句中，同时where需要对表进行条件过滤--因此，相当于将过滤条件和连接条件揉到一起，太乱了，因此出现了99语法

99语法-- 熟练

--99语法/*CROSS　JOINNATURAL JOINUSING子句ON子句LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOINInner join*/--cross join 等同于92语法中的笛卡儿积select * from emp cross join dept;--natural join  相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接--当两张表中不具有相同的列名的时候，会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系select * from emp e natural join dept d ;select * from emp e natural join salgrade sg;--on子句，可以添加任意的连接条件，--添加连接条件 相当于92语法中的等值连接 join onselect * from emp e join dept d on e.deptno = d.deptno;--相当于92语法中的非等值连接，select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;--left outer join ,会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可select * from emp e left outer join dept d on e.deptno = d.deptno;select * from emp e,dept d where e.deptno = d.deptno(+);--right outer join ,会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可select * from emp e right outer join dept  d on e.deptno = d.deptno;select * from emp e,dept d where e.deptno(+) = d.deptno;--full outer join ,相当于左外连接和右外连接的合集select * from emp e full outer join dept d on e.deptno = d.deptno;--inner outer join，两张表的连接查询，只会查询出有匹配记录的数据select * from emp e inner join dept d on e.deptno = d.deptno;select * from emp e join dept d on e.deptno = d.deptno;--using,除了可以使用on表示连接条件之外，也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表select deptno from emp e join dept d using(deptno);select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;--总结:两种语法的SQL语句没有任何限制，再公司中可以随意使用，但是建议使用99语法，不要使用92语法，SQL显得清楚明了--检索雇员名字、所在单位、薪水等级select e.ename, d.loc, sg.grade  from emp e  join dept d    on e.deptno = d.deptno  join salgrade sg    on e.sal between sg.losal and sg.hisal;

子查询

/*
子查询：
    嵌套再其他sql语句中的完整sql语句，可以称之为子查询
分类：
    单行子查询
    多行子查询

*/
--有哪些人的薪水是在整个雇员的平均薪水之上的
--1、先求平均薪水
select avg(e.sal) from emp e;
--2、把所有人的薪水与平均薪水比较
select * from emp e where e.sal > (select avg(e.sal) from emp e); 
--我们要查在雇员中有哪些人是经理人
--1、查询所有的经理人编号
select distinct e.mgr from emp e;
--2、再雇员表中过滤这些编号即可
select * from emp e where e.empno in (select distinct e.mgr from emp e);


--每个部门平均薪水的等级
--1、先求出部门的平均薪水
select e.deptno,avg(e.sal) from emp e group by e.deptno; 
--2、跟薪水登记表做关联，求出平均薪水的等级
select t.deptno, sg.grade
  from salgrade sg
  join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
    on t.vsal between sg.losal and sg.hisal;


--1、求平均薪水最高的部门的部门编号
--求部门的平均薪水
select e.deptno,avg(e.sal) from emp e group by e.deptno;
--求平均薪水最高的部门
select max(t.vsal) from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno) t
--求部门编号
select t.deptno
  from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
 where t.vsal =
       (select max(t.vsal)
          from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t);
--2、求部门平均薪水的等级
--3、求部门平均的薪水等级
--求部门每个人的薪水等级
select e.deptno, sg.grade
  from emp e
  join salgrade sg
    on e.sal between sg.losal and sg.hisal;
--按照部门求平均等级
select t.deptno, avg(t.grade)
  from (select e.deptno, sg.grade
          from emp e
          join salgrade sg
            on e.sal between sg.losal and sg.hisal) t
 group by t.deptno;
--限制输出，limit，mysql中用来做限制输出的，但是oracle中不是
--再oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，
--但是rownum不能直接使用，需要嵌套使用
--4、求薪水最高的前5名雇员
select *
  from (select * from emp e order by e.sal desc) t1
 where rownum <= 5
  
 select * from emp e  where rownum <=5 order by e.sal desc
--5、求薪水最高的第6到10名雇员
select t1.*,rownum
  from (select * from emp e order by e.sal desc) t1
 where rownum <= 10
--使用rownum的时候必须要再外层添加嵌套，此时才能将rownum作为其中的一个列，然后再进行限制输出
 select *
   from (select t1.*, rownum rn
           from (select * from emp e order by e.sal desc) t1
          where rownum <= 10) t
  where t.rn >  5
    and t.rn <= 10;


select *
  from (select t1.*, rownum rn
          from (select * from emp e order by e.sal desc) t1) t
 where t.rn > 5
   and t.rn <= 10;
        

行专列 – 工作使用非常多

create table tmp(rq varchar2(10),shengfu varchar2(5));insert into tmp values('2005-05-09','胜');insert into tmp values('2005-05-09','胜');insert into tmp values('2005-05-09','负');insert into tmp values('2005-05-09','负');insert into tmp values('2005-05-10','胜');insert into tmp values('2005-05-10','负');insert into tmp values('2005-05-10','负');/*          胜 负2005-05-09 2 22005-05-10 1 2*/select rq,decode(shengfu,'胜',1),decode(shengfu,'负',2) from tmp;select rq,       count(decode(shengfu, '胜', 1)) 胜,       count(decode(shengfu, '负', 2)) 负  from tmp group by rq;create table STUDENT_SCORE(  name    VARCHAR2(20),  subject VARCHAR2(20),  score   NUMBER(4,1));insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);/*姓名   语文  数学  英语王五    89    56    89*/--至少使用4中方式下写出--decodeselect ss.name,       max(decode(ss.subject, '语文', ss.score)) 语文,       max(decode(ss.subject, '数学', ss.score)) 数学,       max(decode(ss.subject, '英语', ss.score)) 英语  from student_score ss group by ss.name--case whenselect ss.name,       max(case ss.subject             when '语文' then              ss.score           end) 语文,       max(case ss.subject             when '数学' then              ss.score           end) 数学,       max(case ss.subject             when '英语' then              ss.score           end) 英语  from student_score ss group by ss.name;--joinselect ss.name,ss.score from student_score ss where ss.subject='语文';select ss.name,ss.score from student_score ss where ss.subject='数学';select ss.name,ss.score from student_score ss where ss.subject='英语';select ss01.name, ss01.score 语文, ss02.score 数学, ss03.score 英语  from (select ss.name, ss.score          from student_score ss         where ss.subject = '语文') ss01  join (select ss.name, ss.score          from student_score ss         where ss.subject = '数学') ss02    on ss01.name = ss02.name  join (select ss.name, ss.score          from student_score ss         where ss.subject = '英语') ss03    on ss01.name = ss03.name;--union allselect t.name,sum(t.语文),sum(t.数学),sum(t.英语) from (select ss01.name,ss01.score 语文,0 数学,0 英语 from student_score ss01 where ss01.subject='语文' union allselect ss02.name,0 语文,ss02.score 数学,0 英语 from student_score ss02 where ss02.subject='数学' union allselect ss03.name,0 语文,0 数学,ss03.score 英语 from student_score ss03 where ss03.subject='英语') t group by t.name

视图

/*
CREATE [OR REPLACE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH READ ONLY];

*/
--如果普通用户第一次创建视图，提示没有权限，要使用管理员去修改权限
grant create view to scott;

--创建视图
create view v_emp as select * from emp where deptno = 30;
--视图的使用
select * from v_emp;
--向视图中添加数据,执行成功之后，需要提交事务，绿色表示提交事务，让数据生效，红色表示回滚事务，让数据恢复原状态
insert into v_emp(empno,ename) values(1111,'zhangsan');
select * from emp;
--如果定义的视图是非只读视图的话，可以通过视图向表中插入数据，如果是只读视图，则不可以插入数据
create view v_emp2 as select * from emp with read only;
select * from v_emp2;
--只读视图只提供查询的需求，无法进行增删改操作
insert into v_emp2(empno,ename) values(1234,'lisi');
--删除视图
drop view v_emp2;
--当删除视图中的数据的时候，如果数据来源于多个基表，则此时不能全部进行删除，只能删除一个表中的数据

--我们要求平均薪水的等级最低的部门，它的部门名称是什么，我们完全使用子查询
--1、求平均薪水
select e.deptno,avg(e.sal) from emp e group by e.deptno;
--2、求平均薪水的等级
select t.deptno,sg.grade gd
  from salgrade sg
  join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
    on t.vsal between sg.losal and sg.hisal;
--3、求平均薪水的等级最低的部门
select min(t.gd) from (select t.deptno,sg.grade gd
  from salgrade sg
  join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
    on t.vsal between sg.losal and sg.hisal) t
--4、求平均薪水的等级最低的部门的部门名称

select d.dname, d.deptno
  from dept d
  join (select t.deptno, sg.grade gd
          from salgrade sg
          join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
            on t.vsal between sg.losal and sg.hisal) t
    on t.deptno = d.deptno
 where t.gd =
       (select min(t.gd)
          from (select t.deptno, sg.grade gd
                  from salgrade sg
                  join (select e.deptno, avg(e.sal) vsal
                         from emp e
                        group by e.deptno) t
                    on t.vsal between sg.losal and sg.hisal) t);
--查看sql语句能够发现，sql中有很多的重复的sql子查询，可以通过视图将重复的语句给抽象出来
--创建视图
create view v_deptno_grade as select t.deptno, sg.grade gd
          from salgrade sg
          join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
            on t.vsal between sg.losal and sg.hisal;
--使用视图替换

select d.dname, d.deptno
  from dept d
  join v_deptno_grade t
    on t.deptno = d.deptno
 where t.gd =
       (select min(t.gd)
          from v_deptno_grade t);

用户管理

ppt  04 数据库设计 17页

序列

--在oracle中如果需要完成一个列的自增操作，必须要使用序列
/*
create sequence seq_name
  increment by n  每次增长几
  start with n    从哪个值开始增长
  maxvalue n|nomaxvalue 10^27 or -1  最大值
  minvalue n|no minvalue  最小值
	cycle|nocycle           是否有循环
	cache n|nocache          是否有缓存

*/
create sequence my_sequence
increment by 2
start with 1

--如何使用？
--注意，如果创建好序列之后，没有经过任何的使用，那么不能获取当前的值，必须要先执行nextval之后才能获取当前值
--dual是oracle中提供的一张虚拟表，不表示任何意义，在测试的时候可以随意使用
--查看当前序列的值
select my_sequence.currval from dual;
--获取序列的下一个值
select my_sequence.nextval from dual;

insert into emp(empno,ename) values(my_sequence.nextval,'hehe');
select * from emp;

数据更新DML

--DML：数据库操作语言
--增
--删
--改

--在实际项目中，使用最多的是读取操作，但是插入数据和删除数据同等重要，而修改操作相对较少

/*
增删改是数据库的常用操作，在进行操作的时候都需要《事务》的保证， 也就是说每次在pl/sql中执行sql语句之后都需要完成commit的操作
事务变得非常关键：
    最主要的目的是为了数据一致性
    如果同一份数据，在同一个时刻只能有一个人访问，就不会出现数据错乱的问题，但是在现在的项目中，更多的是并发访问
    并发访问的同时带来的就是数据的不安全，也就是不一致
    如果要保证数据的安全，最主要的方式就是加锁的方式，MVCC
    
    事务的延申：
        最基本的数据库事务
        声明式事务
        分布式事务
    为了提高效率，有可能多个操作会在同一个事务中执行，那么就有可能部分成功，部门失败，基于这样的情况就需要事务的控制。
    select * from emp where id = 7902 for update
    select * from emp where id = 7902 lock in share mode.
    
    如果不保证事务的话，会造成脏读，不可重复读，幻读。
*/

插入

/*
插入操作：
  元组值的插入
  查询结果的插入

*/
--最基本的插入方式
--insert into tablename values(val1,val2,....) 如果表名之后没有列，那么只能将所有的列都插入
--insert into tablename(col1,col2,...) values(val1,val2,...) 可以指定向哪些列中插入数据

insert into emp values(2222,'haha','clerk',7902,to_date('2019-11-2','YYYY-MM-dd'),1000,500,10);
select * from emp;
--向部分列插入数据的时候，不是想向哪个列插入就插入的，要遵循创建表的时候定义的规范
insert into emp(empno,ename) values(3333,'wangwu')

创建

--创建表的其他方式
--复制表同时复制表数据，不会复制约束
create table emp2 as select * from emp;
--复制表结构但是不复制表数据，不会复制约束
create table emp3 as select * from emp where 1=2;
--如果有一个集合的数据，把集合中的所有数据都挨条插入的话，效率如何？一般在实际的操作中，很少一条条插入，更多的是批量插入

删除

/*
删除操作：
 delete from tablename where condition

*/
--删除满足条件的数据
delete from emp2 where deptno = 10;
--把整张表的数据全部清空
delete from emp2;
--truncate ,跟delete有所不同，delete在进行删除的时候经过事务，而truncate不经过事务，一旦删除就是永久删除，不具备回滚的操作
--效率比较高，但是容易发生误操作，所以不建议使用
truncate table emp2;

修改

/*
修改操作：
   update tablename set col = val1,col2 = val2 where condition;
   可以更新或者修改满足条件的一个列或者多个列
*/
--更新单列
update emp set ename = 'heihei' where ename = 'hehe';
--更新多个列的值
update emp set job='teacher',mgr=7902 where empno = 15;

事务

--事务：表示操作集合，不可分割，要么全部成功，要么全部失败

--事务的开始取决于一个DML语句
/*
事务的结束
  1、正常的commit（使数据修改生效）或者rollback（将数据恢复到上一个状态）
  2、自动提交，但是一般情况下要将自动提交进行关闭，效率太低
  3、用户关闭会话之后，会自动提交事务
  4、系统崩溃或者断电的时候回回滚事务，也就是将数据恢复到上一个状态
*/
insert into emp(empno,ename) values(2222,'zhangsan');
--commit;
--rollback;
select * from emp;

--savepoint  保存点
--当一个操作集合中包含多条SQL语句，但是只想让其中某部分成功，某部分失败，此时可以使用保存点
--此时如果需要回滚到某一个状态的话使用 rollback to sp1;
delete from emp where empno = 1111;
delete from emp where empno = 2222;
savepoint sp1;
delete from emp where empno = 1234;
rollback to sp1;
commit;
/*
事务的四个特性：ACID
  原子性：表示不可分割，一个操作集合要么全部成功，要么全部失败，不可以从中间做切分
  一致性：最终是为了保证数据的一致性，当经过N多个操作之后，数据的状态不会改变（转账）
          从一个一致性状态到另一个一致性状态，也就是数据不可以发生错乱
  隔离性：各个事务之间相关不会产生影响，（隔离级别）
          严格的隔离性会导致效率降低，在某些情况下为了提高程序的执行效率，需要降低隔离的级别
          隔离级别：
            读未提交
            读已提交
            可重复读
            序列化
          数据不一致的问题：
            脏读
            不可重复读
            幻读
  持久性：所有数据的修改都必须要持久化到存储介质中，不会因为应用程序的关闭而导致数据丢失

  四个特性中，哪个是最关键的？
     所有的特性中都是为了保证数据的一致性，所以一致性是最终的追求
     事务中的一致性是通过原子性、隔离性、持久性来保证的

     锁的机制：
     为了解决在并发访问的时候，数据不一致的问题，需要给数据加锁
     加锁的同时需要考虑《粒度》的问题：
         操作的对象
            数据库
            表
            行
     一般情况下，锁的粒度越小，效率越高，粒度越大，效率越低 
            在实际的工作环境中，大部分的操作都是行级锁  

*/

索引

--索引：加快数据的检索
--创建索引
create index i_ename on emp(ename);
--删除索引
drop index i_ename;
select * from emp where ename = 'SMITH';

建表操作

/*

CREATE TABLE [schema.]table
  (column datatype [DEFAULT expr] , …
	);

*/

--设计要求：建立一张用来存储学生信息的表，表中的字段包含了学生的学号、姓名、年龄、入学日期、年级、班级、email等信息，
--并且为grade指定了默认值为1，如果在插入数据时不指定grade得值，就代表是一年级的学生

create table student
(
stu_id number(10),
name varchar2(20),
age number(3),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50)
);
insert into student values(20191109,'zhangsan',22,to_date('2019-11-09','YYYY-MM-DD'),'2','1','123@qq.com');
insert into student(stu_id,name,age,hiredate,classes,email) values(20191109,'zhangsan',22,to_date('2019-11-09','YYYY-MM-DD'),'1','123@qq.com');

select * from student;
--正规的表结构设计需要使用第三方工具 powerdesigner
--再添加表的列的时候，不能允许设置成not null
alter table student add address varchar2(100);
alter table student drop column address;
alter table student modify(email varchar2(100));
--重新命名表
rename student to stu;
--删除表
/*
在删除表的时候，经常会遇到多个表关联的情况，多个表关联的时候不能随意删除，需要使用级联删除
cascade:如果A,B,A中的某一个字段跟B表中的某一个字段做关联，那么再删除表A的时候，需要先将表B删除
set null:再删除的时候，把表的关联字段设置成空
*/
 drop table stu;
 

 

约束

 --创建表的时候可以给表中的数据添加数据校验规则，这些规则称之为约束
/*
 约束分为五大类
 not null: 非空约束，插入数据的时候某些列不允许为空
 unique key:唯一键约束，可以限定某一个列的值是唯一的，唯一键的列一般被用作索引列。
 primary key:主键：非空且唯一，任何一张表一般情况下最好有主键，用来唯一的标识一行记录，
 foreign key:外键，当多个表之间有关联关系（一个表的某个列的值依赖与另一张表的某个值）的时候，需要使用外键
 check约束:可以根据用户自己的需求去限定某些列的值
 */
 --个人建议：再创建表的时候直接将各个表的约束条件添加好，如果包含外键约束的话，最好先把外键关联表的数据优先插入
 
 insert into emp(empno,ename,deptno) values(9999,'hehe',50);
 
 create table student
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age>0 and age<126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2)
);
insert into student(stu_id,name,age,hiredate,classes,email,deptno) values(20191109,'zhansgan',111,to_date('2019-11-09','YYYY-MM-DD'),'1','12443@qq.com',10);

alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno);