学习大数据DAY06 SQL基础语法6

目录

on和where使用区别

集合查询

作业

作业2

定义操作控制等语句

数据库表定义

数据库设计

满足三大范式的ER模型图

三大范式

数据存储方式

表和表空间的关系

创建表空间

创建临时表空间：

创建用户

用户权限赋予

常见问题-system登录报错：

作业3

---

SQL基础语法（六）

on和where使用区别

内连接无差别，外连接用on效率高

select * from emp

inner join dept on emp.deptno=dept.deptno

where emp.deptno>10

select * from emp

inner join dept on emp.deptno=dept.deptno and emp.deptno>10

select * from emp

right outer join dept on emp.deptno=dept.deptno

where dept.deptno>10 --前面都执行完再筛选

select * from emp

right outer join dept on emp.deptno=dept.deptno and dept.deptno>10 --先筛选再连表

集合查询

1.union/union all,intersect 没有上下关系，minus有（永远是上减下）

2.集合做运算的时候，可以不仅是一列，多列做运算时，要求列数量相等，并且列的数

据类型相同

3.union、union all、intersect、minus运算可以进行混合运算、它们之间优先级相同。

4.可以使用圆括号控制集合运算的优先级，它具有最高的优先级。

--并集

select deptno from emp

union

select deptno from dept

select deptno from emp

union all

select deptno from dept

--union all 并集全部显示（列名可以不一样，类型一样就可以了）

select deptno from emp

union all

select sal from emp

--交集

select deptno from emp

intersect

select deptno from dept

--差集

select deptno from dept

minus

select deptno from emp

(select deptno from emp

union all

select deptno from dept)

minus

select deptno from emp

--灵活应用

select empno,ename from emp

union

select 333,'test' from dual

order by empno desc;

作业

--1.10号部门的部门名称和7369的员工姓名进行行拼显示，使用union all实现

(select dname from dept

where deptno=10)

union all

(

select ename from emp

where empno=7369

);



--2.查看不是领导的员工编号和员工姓名，使用minus和子查询实现

select empno,ename from emp

where empno in

(

select empno from emp

minus

select mgr from emp

);

作业2

--1.查询工资比20号部门所有人工资都高的员工信息

select * from emp

where sal>all

(

select sal from emp

where deptno=20

);



--2.查询各个部门的部门编号和员工人数，要求部门的平均工资大于30号部门的平均工资

select deptno,count(1) from emp

group by deptno

having avg(sal)>(

select avg(sal) from emp

where deptno=30

);



--3.显示部门内最低工资比20部门最低工资要高的部门的部门编号及部门内的最低工资

select deptno,min(sal) from emp

group by deptno

having min(sal)>(

select min(sal) from emp

where deptno=20);



--4.查询比三个部门平均工资都高的员工编号，员工姓名，工种和收入

select empno,ename,job,sal+nvl(comm,0)

from emp

where sal>

(

select avg(sal) from emp

where deptno in(10,20,30)

);



--5.显示部门名称为RESEARCH的员工姓名，工资

select ename,sal

from emp left outer join dept on emp.deptno=dept.deptno

where dname='RESEARCH';



--6.如果部门名称中含有字母T，则查询该部门所有员工信息，两种方法实现

--方法一

select * from emp left outer join dept on emp.deptno=dept.deptno

where dname like '%T%';



--方法二

select * from emp left outer join dept on emp.deptno=dept.deptno

where instr(dname,'T',1,1)>0 ;





--7.如果有平均工资不小于1500的部门信息则查询相应的部门信息

select * from dept

right join 

(

select deptno,avg(sal) from emp

group by deptno

having avg(sal)>=1500 

) D on dept.deptno=D.deptno;



--8.查询出Emp表中比所有销售员(“SALESMAN”)工资低的员工姓名、工作、工资

select ename,job,sal from emp

where sal<all

(

select sal from emp

where job='SALESMAN'

);



--9.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间

select ename,hiredate from emp

where hiredate>

(

select hiredate from emp

where sal=

      (

      select max(sal) from emp

      )

);



--10.查询emp表中有2个以上直接下属的领导人员信息

select * from emp

where empno in

(

select L.empno from 

emp L inner join emp E on L.empno=E.mgr

group by L.empno

having count(E.empno)>2 

);



--11.使用开窗函数求出emp表中每一个部门工资第一高的人员姓名ename和人员工资sal，以及他所在部门的最高工资

select ename,sal,max(sal)over(partition by deptno)

from emp

where sal in

(

select max(sal)over(partition by deptno) from emp

);



--12.emp表中每个部门员工入职日期最早的员工信息，多条件子查询

select * from emp

where hiredate in

(

select min(hiredate)over(partition by deptno) from emp

);



--13.实现下表功能

select E.deptno as 部门,dname as 部门名称,job as 工作,小计,合计

from

(

select deptno,job,count(1) as 小计,null as 合计 from emp group by deptno,job

union all

select deptno,null,null,count(1) from emp group by deptno

) E left join dept on E.deptno=dept.deptno

order by E.deptno,dname,合计;

13题的表：

定义操作控制等语句

数据库表定义

数据库设计

满足三大范式的ER模型图

三大范式

第一范式：原子性的，不可分。

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不

能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。

第二范式：要求实体的属性完全依赖于主关键字。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和

主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的

关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。

第三范式 ：属性不依赖于其它非主属性。

第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

满足第三范式（3NF）必须先满足第二范式（2NF）。 

数据存储方式

oracle数据库的存储结构可以分为逻辑存储结构和物理存储结构,对于这两种存储结构，

oracle是分别进行管理的。

逻辑存储结构：oracle内部的组织和管理数据的方式。

物理存储结构：oracle外部（操作系统）组织和管理数据的方式。

1.逻辑存储结构：

oracle在逻辑上将保存的数据划分为一个个小单元来进行存储和维护，更高一级的逻辑存

储结构都是由这些基本的小单元组成的。

逻辑结构类型按照尺寸从小到大分可分为：块(block)-->区(extent)-->段(segment)-->表

空间(tablespace)

我们可以用下面的比喻来描述逻辑结构关系：

块 ：一张张白纸 ---> 区：白纸组成的本子---> 段：多个本子放到一个文件袋中---> 表

空间：文件柜（存放多个文件袋） 

2.oracle物理存储结构

Oracle数据库逻辑上由一个或多个表空间组成，每个表空间在物理上由一个或多个数据

文件组成，而每个数据文件是有数据块构成的。所以，逻辑上数据存放在表空间中，而

物理上存储在表空间所对应的数据文件中。

构成数据库物的物理文件主要有三种：

数据文件：存放数据库数据。

控制文件：存放数据库的基本信息，告诉数据库到哪里找到数据文件和重做日志文件

等。对数据库的成功启动和正常运行是很重要的。

重做日志文件：存放对数据的改变。至少两组，Oracle以循环方式来使用它们。 

表和表空间的关系

表空间：表空间是由若干个数据段组成

存放表，视图，索引等

一个表只能属于一个表空间，

一个表空间可以放任意多个表

一个表空间至少有一个存储文件.dbf文件，可以有多个，而且这多个数据文件可以在不同

位置

一个用户有一个默认的表空间，一个用户可以在默认表空间外的其它表空间建表

创建表空间

create tablespace 表空间名 datafile '数据文件路径' size 初始大小（2G） autoextend on

next 每次扩展的大小(100M) maxsize 最大容量（unlimited）;

create tablespace ora

datafile 'c:\test\tab.dbf'

size 2G autoextend on next 100M maxsize unlimited;

创建临时表空间：

create temporary tablespace 临时表空间名 tempfile '数据文件路径' size 初始大小

（2G） autoextend on next 每次扩展的大小(100M) maxsize 最大容量（unlimited）;

create temporary tablespace tabtemp

tempfile 'c:\test\tabtemp.dbf'

size 2G autoextend on next 100M maxsize unlimited;

创建用户

create user 用户名 identified by 密码 default tablespace 表空间名 temporary tablespace

临时表空间名;

default后面的语句是为用户指定默认表空间和临时表空间，如果不指定，默认的表空间

是users表空间，临时表空间是temp

create user orastudy

identified by oracle default tablespace ora temporary tablespace tabtemp; 

用户权限赋予

grant 权限 to 用户名;

grant resource,connect to ora; --连接权限和资源权限

grant create any table to ora; --建表权限

grant create any tablespace to ora; --建表空间权限

grant select any table to ora; --只读权限

grant create any view to bw; --创建视图权限

grant select any table to ora; --给ora用户预编译表的权限

--查看角色权限信息

select * from role_sys_privs;

grant dba to ora; --管理员权限

用户权限对比表：

https://blog.csdn.net/weixin_36470210/article/details/116318174

--操作流程

create tablespace TEST_20220511 --表空间名

datafile 'C:\app\orastudy\oradata\orcl\TEST_20220511.DBF' --数据文件路径

size 10M --初始大小

autoextend on next 5M --自增长大小

maxsize unlimited; --最大大小（不限制）

create temporary tablespace tabtemp_20220511

tempfile 'C:\app\orastudy\oradata\orcl\tabtemp_20220511.dbf'

size 10M autoextend on next 10M maxsize unlimited;

create user test0511 identified by oracle

default tablespace TEST_20220511

temporary tablespace tabtemp_20220511;

grant dba to test0511;--简单粗暴

常见问题-system登录报错：

ORA-01017（：用户名/口令无效; 登录被拒绝）

1.打开cmd命令窗，输入sqlplus / as sysdba

1）修改密码

alter user 用户名 identified by 密码;

2）用户被锁定，解锁

alter user 用户名 account unlock;

2.再次登录验证，成功

作业3

--1.以管理员的身份创建表空间“自己名字全拼+今天日期”和临时表空间“自己名字全拼Temp+今天日期”

create tablespace shihaihong20240701

datafile 'C:\test\tabshh.dbf'

size 100M autoextend on next 100M maxsize unlimited;



create temporary tablespace shhtabtemp

tempfile 'c:\test\shhtabtemp.dbf'

size 100M autoextend on next 100M maxsize unlimited;



--2.创建用户 自己名字全拼

create user shihaihong

identified by oracle;



--3.给用户赋权限 dba

grant resource,connect to shihaihong; --连接权限和资源权限

grant create any table to shihaihong; --建表权限

grant create any tablespace to shihaihong; --建表空间权限

grant select any table to shihaihong; --只读权限

grant create any view to shihaihong; --创建视图权限

grant select any table to shihaihong; --给ora用户预编译表的权限

grant dba to shihaihong; --管理员权限

--查看角色权限信息

select * from role_sys_privs;



--4.用新的用户登录测试查看scott.emp和scott.dept

select * from scott.emp;

select * from scott.dept;