ORACLE
20220107惨痛教训：除了个人学习的测试数据库表，公司企业严禁没有十足把握的update、delete操作（where行过滤条件一定要写全）
Cmd →systeminfo 查看何时安装操作系统
数据库三层结构：①、client客户端（专门用于访问数据库）
Oracle DBMS：database management system
②、Db实例：
Master 高级 professional 个人
安装时创建一个默认数据库oracl
每创建一个数据库实例的时候会自动生成三个默认用户
Sys超级管理员、system管理员、Scott普通用户
Create user xiaoming identified by 123456;\\创建用户
Grant connect to xiaoming;\\授予登录权限
Grant resource to xiaoming;\\授予使用数据库资源的权限
Connect xiaoming/123456;\\切换
Passward xiaoming;\\修改密码
Grant select[delete[insert][update][all]];\\授予权限
Create table users<id number>；
Insert into users value(10);
Select * from users;
\\
Connect system/sever2011;
Create users tea identified by 123456;
Grant connect to tea;
Grant resource tea;
Create users stu identified by 123456;
Grant connect to stu;
Grant resource stu;
Disconnect system/sever2011;
Connect scott/123456;
Grant select on emp to tea;
Connect tea/123456;
Select * from scott.emp;
Connect scott/123456;
Grant all on emp to stu;
Connect tea/123456
Update scott.emp set teacherid=’17112840139’ set teacherid=’17112840138’
Select from scott.emp where teacherphone=’15769915238’
Insert scott.emp into values(001,’ford’’teacher’’男’‘1995-11-24’‘26’)
Connect scott/123456
Revoke select on scott.emp from tea;
Revoke all on scott.emp from tea;
***权限转移：with grant option\\得到权限的用户可以将权限转移给他人；
With admin option \\若果是系统权限，则带with admin option；
With grant option \\若果是用户权限，则带with grant option；
Connect scott/123456;
Grant select on emp to tea with grant option;
Connect tea/123456;
Grant select on scott.emp to stu;
revoke select on scott.emp from stu;
Connect scott/123456;
Revoke all on emp from tea;
行：row 列：column
类ß->表
实例ßà表的一条数据记录；
Oracle数据类型：
- char 字符串最大2000字符，定长
- varchar2 字符串4000字符，变长
- 如果数据长度是固定的，比如发票号，则就用char；若数据长度不固定，则用varchar2；
- Nchar 编码方式为 Unicode；这种编码方式一个汉字只占一个字符空间；nvarchar2同理；
- Clob 字符型大对象，字符串长度为8tb，变长；
- Blob 变长，8tb，存放声音，图像；一般都是存放url统一资源标识符；
- 数值类型：number：变长、可以存整数，也可以存放小数；1~22 bytes;
- Number（p,s）:p表示整数，s表示小数；
- Number(5,-2) 看小数点前两位，123.345-à100,小数点前第二位是2四舍五入为0，输出则为100；
- Date 日期类型，默认格式‘dd-mm-yy’‘11-11月-21’
- Modify ：create table test11 modify（age number）；修改表的字段类型；
- alter table test11 drop column age;删除表的某个字段；//add
- update set where （where字句指定更新那些行，没有where表示更新所有行）；
- delete from where删除某一行的数据记录，要删除某列运用update；
- truncate table 速度快，但不能回滚rollback；
- select column 代表列，distinct 过滤数据记录；可选，指显示结果时，是否剔除重复数据；dept：科室；advice：建议医嘱；detail：详细的；default：默认的；
- select [distinct]*|(列1，列2…) from 表名 [where 条件]
- 重复行：返回的数据完全一样才算重复行；
- Oracle 的sql不区分大小写，但是内容（查询where条件）区分大小写；
- select ename,sal*13+nvl(comm,0)*13 as "年薪" from emp;nvl函数：用于数据null的问题，if comm is null→返回0，if comm is not nll→值本身；//别名 as“年薪”；
- || 双竖杆，用来连接字符串；select ename ||'是一个'|| job from emp;连接两列作为一列返回SMITH是一个CLERK；\\如果希望把多列拼接起来作为一列，则就用||’xxx’||连接字符串连接。
- 锁定用户：alter user scott account lock；scott账户不能登录数据库，但是sys仍然可以使用它的数据对象；
- 解锁用户：alter user scott account unlock；
- To_char:select * from emp where to_char(hiredate,'yyyy-mm-dd') >'1982-1-1'; select * from emp where to_char(hiredate,'yyyy') ='1980';\\to_char转换成自己想要的格式；
- 模糊查询：%表示0到多个字符，_任意一个字符；select ename,sal from emp where ename like 'S%'; select ename,sal from emp where ename like '__O%';
- In 简化作用：where条件中使用in：select * from emp where enmpno in(123,345,800);等价于select * from emp where empno=123 or empno=345 or empno=800;
- Is null/is not null；select * from emp where mgr is null/is not null;
- Oracle 逻辑运算符号：or 或；and 并且；select * from emp where (sal>500 or job='manager') and ( ename like 'J%');
- Order by 子句使用：对查询结果进行排序显示 asc升序，desc降序；默认升序排列；select * from emp order by deptno asc,hiredate desc;在deptno升序的基础上以hiredate降序；
- 使用列的别名排序：Oracle支持用别名排序：select ename,sal*13+nvl(comm,0)*13 as 年薪 from emp order by 年薪 desc;\\年薪就是别名；
- 表的复杂查询：sum,max,min,avg(只计算有值的，null不计入计算)，count（count（*）统计该表的总行数，不管是否为空//count（sal）统计sal字段时只统计非空），因此要计算为空的行也参与avg，即可select sum（sal）/count（*）…：绝对平均；
- select ename,job from emp where sal=(select max(sal) from emp);第一个子查询；select执行顺序：①、写sql语句从左到右写；②sql默认从右到左执行；③、不管select有多少，Oracle都有执行顺序；
- group by 对查询的结果进行分组统计；having （条件筛选，作用和where一样，但只能跟在group by后面）用于（过滤）限制分组显示结果；select deptno,job,avg(sal),min(sal) from emp group by deptno,job;（显示每个部门不同岗位的平均工资和最低工资）；group by 把每个deptno的每个岗位区分开来，然后分别计算avg、max；group分几组都可以；select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;（显示部门平均工资低于2000的部门号和该部门的平均工资）\\先查询每个部门的平均工资的查询结果，再通过having来筛选出平均工资低于2000的结果；
- ***分组函数avg（**）只能出现在选择列表、having、orderby 子句中，不能出现在from后面
- ***若select同时包含group by、having、order by则顺序为group by→having→order by
- ***在选择列中如果有列、表达式、分组函数、那么这些列和表达式必须有一个出现在group by子句中，否则就会出错；
- 多表查询：select * from emp,dept,salgrade; 已选择280行。=5*4*14=280rows→多表查询不加任何条件时，Oracle先从最后一个表选取第一条直至最后一条数据和第二个表的每一条数据进行匹配，然后再从第二个表中选取第一条直至最后一条与第一个表的每一条数据进行匹配，以此类推。→笛卡尔集现象
- 避免笛卡尔集→规定：多表联合查询时，查询条件必须>=表的个数-1
- 插：测试端口是否开通：telnet ip 端口号
- 在多表查询时，需不需要加表名：如果两个表的列有同名字段，就需要加表名区分；若表之间没有同名字段，则不需要加，但是建议加表名，清晰严谨。
- 技巧：在进行多表查询的时候，使用别名，简化方便。select t1.empno,t1.ename,t1.sal,t2.grade from emp t1,salgrade t2 where t1.sal between t2.losal and t2.hisal;
- Order by 排序：order by后可跟多个排序条件以逗号隔开，order by默认以第一个值排序后，第一个值相同的条件下，再以第二个值排序，同理类推；
- 复杂查询（自连接）：同一张表的连接查询（把一张表看成两张表进行逻辑分析）；select woker.ename 员工姓名,boss.ename "员工上级=老板" from emp woker,emp boss where woker.mgr=boss.empno(+);（把emp表看成worker表和BOSS表，再以worker的上级=boss 的编号进行自连接）
- 复杂查询（子查询=嵌套查询）：嵌入在其他sql语句中的select语句；
一、单行子查询：只返回一行数据的子查询
-
- 、先查出smith所在的部门select deptno from emp where ename=’SMITH’
- 、再查出该部门的所有员工信息：
select * from emp where deptno=(select deptno from emp where ename='SMITH');
二、多行子查询：返回多行数据的子查询(= 表示符合一个条件的记录deptno里只有一个10号部门；in 表示符合条件的多条记录十号部门里有三个工作岗位)
查询和部门10工作岗位相同的员工姓名、岗位、工资、部门号：
- 先查询10号部门有哪些工作岗位
Select distinct job from emp where deptno='10';
- 找出与10号部门相同的员工…
Select ename,sal,deptno from emp where job=( Select distinct job from emp where deptno='10');
Select ename,sal,deptno from emp where job in( Select distinct job from emp where deptno='10');
- All操作符：
（查询工资比三十号部门所有员工的工资都高的所有员工信息）
select * from emp where sal>all(select sal from emp where deptno=’30’);等价于select * from emp where sal>(select max(sal) from emp where deptno=’30’);
- Any操作符：
（查询工资比三十号部门所有员工的工资都高的任意员工姓名、工资、部门号）
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno='30') order by deptno,sal desc;等价于select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=’30’);
三、多列子查询：
查询和smith的部门和岗位完全相同的员工信息
- 先查询smith所在的部门和岗位
select deptno,job from emp where ename='SMITH';
- 再查询和该部门岗位相同的员工信息
select * from emp where deptno=(select deptno from emp where ename='SMITH') and job=(select job from emp where ename='SMITH');简化→
select * from emp where (deptno,job)=(sElect deptno,job from emp where ename='SMITH');(标红字段名顺序要一致)
- From语句中使用子查询***把第一次查询出的结果当做一个临时表***
查询高于自己部门平均工资的员工信息。
-
- 查询各个部门的平均工资
Select deptno,avg(sal) from emp group by deptno order by deptno desc;
-
- 把①步的查询结果当做一个临时表对待；
- 查询高于自己部门平均工资的员工信息
Select t.deptno,emp.ename,emp.sal,t.pjgz from emp,(select deptno,avg(sal) pjgz from emp group by deptno) t where emp.deptno=t.deptno and emp.sal>t.pjgz;
*** 这个知识点必须掌握：把第一次子查询的结果当做一个临时表参与sql
- 45的例子：select t.deptno,emp.*,t.maxsal from emp,(select deptno,max(sal) as maxsal from emp group by deptno) t where emp.deptno=t.deptno and emp.sal=t.maxsal order by t.deptno desc;先把蓝色的当成一个临时表；
（+）表示外连接符号，这个地方是左外连接，把没有员工的部门显示出来；
- ***分页查询***，学习任何一个数据库，都必须掌握的点
Mysql：select * from 表名 where limit条件 从第几条取，取几条
***rownum 行数，是oracle默认自带的字段
select emp.*,rownum from emp;显示出该表的行号
********oracle 分页查询=逐级查询（逐层分割查询结果，如果要针对不同的情况进行分页，则在最内层处理，包括多表）
第一层：先查询出带行号且满足条件的整个结果
select rownum as r1,t1.* from emp t1;
第二层：（把第一层的结果当做一个临时表，筛选小于目标数的）
select rownum as r2,t2.* from ( select rownum as r1,t1.* from emp t1) t2 where r1<=6;(在第一层的r1中筛选<=6的结果作为r2：r2<=6)
第三层：（把第二层的查询结果当做一个临时表，筛选大于目标数的）
select rownum as r3,t3.* from (select rownum as r2,t2.* from ( select rownum as r1,t1.* from emp t1) t2 where r1<=6) t3 where r2>=4; (在第二层的r2中筛选<=6的结果作为r3：4=<r3<=6)
- ***表的自我复制***：
- 、从某表中选取某些字段创建新表：
Create table mytest as select empno,ename,sal,comm,deptno from emp;→得到 表mytest
-
- 、让表自我复制（蠕虫复制）：
Insert into mytest (empno,ename,sal,comm,deptno) select empno,ename,sal,comm,deptno from mytest;
- 执行execute=F8 终止break=shift+esc
- 合并查询：
Union\union all\intersect\minus\
- ***内连接，外连接
- 内连接，用的最多的就是内连接；特点：只有两张表同时匹配，才被选择。
基本语法：select 列名..from 表1 inner join 表2 on 条件
内连接实际上就是利用where子句对两张表形成的笛卡尔集进行筛选，我们前面学习的查询都是内连接，也是在开发过程中用的最多的连接查询；
举例：查询所有员工的姓名以及部门号
select emp.ename,emp.sal,dept.deptno from emp,dept where emp.deptno=dept.deptno;
等价于
select emp.ename,emp.sal,dept.deptno from emp,dept where emp.deptno=dept.deptno;
- 外连接，分三种：①左外连、②右外连、③完全外连接
- 、左外连接left join on
特点：如果左侧的表完全显示，则称左外连接；若stu的记录没有和exam的记录匹配上，也要将stu的记录显示出来；
基本语法1：select 列名..from 表1 left join 表2 on 条件
（在left join左边的的表就是左表）
基本语法2：select 列名..from 表1，表2 where 条件（+）；
（（+）在条件后面则是左外连接）
-
- 、右外连接right join on
特点：如果右侧的表完全显示，则称右外连接；若exam的记录没有和stu的记录匹配上，也要将exam的记录显示出来；
基本语法1：select 列名..from 表1 right join 表2 on 条件
（在right join右边的表就是右表）
基本语法2：select 列名..from 表1，表2 where （+）条件；
（（+）在条件左边则是右外连接）
小结：实际上，左右外连可以通过调换表名或者（+）实现转换，即左右外连都有多种写法。
-
- 、完全外连接full outer join on
特点：查询两个表，不管有没有数据匹配结果，都显示出来；不管stu和exam的记录有没有相互匹配上，匹配上的显示匹配结果，没有匹配上的显示各自的记录，总之要全部显示查询结果；
基本语法1：select 列名..from 表1 full outer join 表2 on 条件
内、外、完全外连接练习：
create table stu(id number,name varchar2(32));
insert into student values('1','JACK');
insert into student values('2','TOM');
insert into student values('3','KITY');
insert into student values('4','NONO');
select * from student;
create table exam (id number,grade varchar2(32));
insert into exam values('1','56');
insert into exam values('2','76');
insert into exam values('11','8');
select distinct exam.* from exam;
update exam set grade='80' where grade='8';
select distinct exam.* from exam;
select stu.id as 学号,stu.name as 姓名,nvl(exam.grade,0) as 成绩 from stu inner join exam on stu.id=exam.id;
\\内连接
select stu.id as 学号,stu.name as 姓名,nvl(exam.grade,0) as 成绩 from stu left join exam on stu.id=exam.id;
\\左外连接1
select stu.id as 学号,stu.name as 姓名,nvl(exam.grade,0) as 成绩 from stu,exam where stu.id=exam.id(+);
\\左外连接2
select stu.id as 学号,nvl(stu.name,null) as 姓名,exam.grade as 成绩 from stu right join exam on stu.id=exam.id;
\\右外连接1
select stu.id as 学号,nvl(stu.name,null) as 姓名,exam.grade as 成绩 from stu,exam where stu.id(+)=exam.id;
\\右外连接2
select stu.id as 学号,nvl(stu.name,null) as 姓名,nvl(exam.grade,0) as 成绩 from stu full outer join exam on stu.id=exam.id;
\\完全外连接（不能通过条件两端加（+）来实现）
//在emp和dept上练习：
select dept.dname,emp.ename,emp.job,emp.sal from emp right join dept on emp.deptno=dept.deptno ;
select dept.dname,emp.ename,emp.job,emp.sal from dept left join emp on emp.deptno=dept.deptno ;
select dept.dname,emp.ename,emp.job,emp.sal from emp , dept where emp.deptno(+)=dept.deptno ;
select dept.dname,emp.ename,emp.job,emp.sal from emp , dept where dept.deptno=emp.deptno(+) ;
- ***Oracle默认时间年月日格式：’xx-xx月-xx’转换格式：to_char(字段名,’yyyy-mm-dd’)
select ename,job,hiredate from emp where hiredate between '01-2月-81' and '01-5月-81' order by hiredate;
select ename,job,hiredate from emp where to_char(hiredate,'yyyy-mm-dd') between '1981-02-01' and '1981-05-01' order by hiredate;
- ***cube的作用：cube生成的结果集显示了所选列中值的所有组合的聚合
\\算出每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资
select deptno,job,avg(sal) from emp group by deptno,job;
select deptno,avg(sal) from emp group by deptno;
select job,avg(sal) from emp group by job;
select avg(sal),deptno,job from emp group by cube(deptno,job);（cube实行多行聚合，方便快捷）
- To_date 日期转换函数；to_date 表示date；to_char表示字符串
我们在插入date日期格式时，必须以Oracle默认的时间格式（日-月-年→11-11月-88年）插入，否则就会报错,因此可以用to_date转换。
To_date
- ①在Oracle里，列的别名可以使用as或者直接空格，而表的别名直接空格不能使用as，否则报错。
②在Oracle里，对表使用别名后，凡是用到表名的地方都只能使用别名，否则报错。
2805
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
