本文深入解析数据库系统架构，涵盖数据库管理系统、应用程序、管理员及最终用户的角色。详解Oracle数据库安装与测试，聚焦PL/SQL Developer工具使用。通过实例演示SQL语言操作，包括查询、排序、筛选、模糊查询等，以及数据库对象如用户、表、约束、索引、视图的管理。
数据库
- 认识数据库
1.数据库系统
数据库管理系统 ： 管理n个数据库，（关系型数据库 Oracle Mysql Sql Server DB2 Access）
数据库应用程序 ： 人事管理系统 学生成绩管理系统。。。。
数据库管理员： 操作数据库中的数据（有权限的划分）（DBA与数据库工程师的区别）
最终用户： 学生 老师 游客
数据库系统： 数据+ 数据库+数据库管理系统+数据库应用程序+数据库管理员+最终用户
2.安装Oracle并进行测试
1.记住Oracle的按装注意事项（花点时间）
2.测试：
1.dos：sqlplus 对应的sqlplus.exe
2.SQL PLUS
3.SQL Developer
3.总结：今后几乎不用
4.重点：plsql developer工具的使用
3.plsql developer工具的使用
重点：plsql developer工具的使用
4.卸载（了解）
注意事项关注PPT即可
5.认识一下Oracle目录
1. jdbc
2. jdk
3. oradata
4.NETWORK:有两个重要的配置文件
5.deinstall -----> deinstall.bat
6.认识Oracle的系统用户和测试用户
system dba
Sys
scott:tiger 用户下有测试数据----》可以进行学习
7.Oracle的发展史及版本
老板的事迹
oracle 8i 9i 10g 11g 12c
2.配置监听器&客户端
两个重要的配置文件：监听配置文件listenr.ora 本地网络服务配置文件 tnsnames.ora
1.监听配置文件listenr.ora (只需要一个监听即可 ---添加 修改 删除)
1.手动配置
2.图形化界面 Net configration assistant
3.图形化界面 net manager
2.本地网络服务配置文件 tnsnames.ora n个(添加 修改 删除)
1.手动配置
2.图形化界面 Net configration assistant
3.图形化界面 net manager
3.注意四个参数
TCP
IP
PORT
SID===数据库实例名==数据库的名字
4.DBCA ：创建一个数据库实例（如果需要数据库中初始化的数据时可以创建一个数据库实例）
额外的问题说明：
开发环境不一：win7 win8 win10
操作的系统的防护机制越来越强-----》对用户比较好
对开发者不太：没有权限对文件进行修改：
解决：对文件的的权限进行更改
开发实际使用情况的说明
1.服务器：监听配置文件 配置本机的ip 和端口号即可
2.客户端：本地网络服务配置文件 4个参数（参考服务器端的4个参数）----》用户名和密码
小组开发已经够用。
3.单表查询
- 引入
1.查询&*&部分信息&大小写&别名&字符拼接||
--查询员工表的所有员工信息 *
select*from emp ;
--查询表中的部分列信息：会根据要就查询出关键信息
--比如：查询所有员工的姓名、上岗日期、月薪
select ename,hiredate,sal from emp;
--sql
--关键字、表名、表字段名不区分大小写
--表中的数据内容不区分大小写
SELECT Ename,HIREdate,SAL from EMP;
--别名：--->目的是增强可读性
--字段别名 表别名
--查询所有员工的姓名、上岗日期、月薪、年薪
select ename,hiredate,sal,sal*12 年薪 from emp;
select ename,hiredate,sal,sal*12 as "年薪" from emp; --as通常可以省掉不写
select ename,hiredate,sal,sal*12 "年 薪" from emp; --对于特殊的字符、可以加上双引号、代表原样输出
--数据拼接：用||
--查询所有员工的姓名、上岗日期、月薪、年薪
--要求显示如下：“姓名：XXX、上岗日期：YYY、月薪：ZZZ、年薪：NNN”
--select "姓名:"+ename+"上岗日期："+hiredate+"月薪："+sal+"年薪"+sal*12 from emp;
select '姓名:'||ename||'上岗日期：'||hiredate||'月薪：'||sal||'年薪'||sal*12 from emp;
select*from emp;
- 常见用法
单表select语句：去重distinct&排序order by asc desc&指定查询条件进行筛选where&模糊查询 like % _ escape
1.去重 distinct
--查询员工的所有的岗位信息
--1列
select distinct job from emp;
--2列
--查询每个部门所拥有的岗位信息
select distinct job,deptno from emp;
2.排序 order by 升序asc 降序 desc
--数值排序
--查询所有员工的姓名、工资，并且按照工资的升序进行排序
select ename,sal from emp order by sal asc;
--查询所有员工的工资，并且按照工资的降序进行排序
select ename,sal from emp order by sal desc;
--默认是按照升序进行排序===缺省时
select ename,sal from emp order by sal;
--字母排序
--查询所有员工的姓名，按照升序排序
select ename from emp order by ename asc;
--时间排序
--查询所有员工的信息，按照入职日期晚的进行排序
select *from emp order by hiredate desc;
3.指定查询条件 筛选行记录 where
--查询10部门的员工信息
select *from emp where deptno=10;
--按照日期进行筛选查询
--查询入职日期为1981/11/17的员工信息
--默认的日期格式字符串的格式为“DD-M月-YY”
select *from emp where hiredate='9-6月-81';
select *from emp where hiredate='9-6月-1981';
4.模糊查询 like % _ escape
--%:代表0个字符或者多个字符
--“_”代表任意一个字符
--escape:可以指定一个转义字符
--模糊匹配
--查询员工中姓名包含字母M的员工信息
select * from emp where ename like '%M%';
--首匹配
--查询员工中姓名的首字母为K的员工信息
select *from emp where ename like 'K%';
--尾匹配
--查询员工中姓名的尾字母为K的员工信息
select * from emp where ename like '%K';
--查询员工中姓名的第三字母为R的员工信息
select * from emp where ename like '__R%';
--查询员工中姓名的包含“_”的员工信息
select * from emp where ename like '%\_%' escape '\';
select * from emp where ename like '%A_%' escape 'A';
select * from emp for update;：
比较运算符< > = >= <= != <>&逻辑运算符 and or & between and &集合(in)&null与0的区别&虚表 dual &运算符的优先级别&作业练习
5.比较运算符 < > = >= <= != <>
--查询工资在2000-3000内(起始和末尾都包含)的员工信息
select * from emp where sal>=2000 and sal<=3000;
--查询工资不等于800的员工信息
select * from emp where sal!=800;
select * from emp where sal<>800;
6.逻辑运算 and or
--and:当指定的查询条件达到两种以上的时候 ，同时满足
--查询部门 编号为10的 且岗位为MANAGER的员工信息
select * from emp where deptno=10 and job='MANAGER';
--or:当指定的条件中满足一种即可
--查询姓名为KING或者为SMITH的员工信息
select * from emp where ename='KING' or ename='SMITH';
--3.区别 闭区间 between and 闭区间[2000,3000] 开区间(2000,3000)
--查询工资在2000-3000内(起始和末尾都包含)的员工信息
select * from emp where sal between 2000 and 3000;
7.in（集合） 对比学习 =全值匹配，只能匹配一个，而in能匹配多个
--查询部门编号为10的员工信息
select * from emp where deptno=10;
select * from emp where deptno in(10);
--查询部门编号除了10部门以外的员工信息
select * from emp where deptno not in(10);
select * from emp where not deptno in(10);
select * from emp where deptno in(20,30);
select * from emp where deptno not in(20,30);
8.null与0的区别
--查询没有提成的员工信息
--select * from emp where comm=0;--提成为0的员工信息
select * from emp where comm is null;
--查询有提成的员工信息
select * from emp where comm is not null;
select * from emp where not comm is null;
- 虚表 dual ==虚拟的表，不存在的表---利用虚表做测试
select 1,2,3 from dual;
select 1+2,2+3,6 from dual;
select sysdate from dual;--获取系统的当前时间
10运算符的优先级别
--运用()进行限制
11作业练习
select * from emp;
--【1】查询出每个月倒数第三天受雇的所有员工
select * from emp where hiredate=last_day(hiredate)-2;
--【2】找出早于30年前受雇的员工
select *from emp where (sysdate - hiredate)/365>30;
--【3】以首字母大写的方式显示所有员工的姓名
select initcap(e.ename),e.* from emp e;
--【4】显示不带有'R'的员工的姓名
select * from emp where not ename like '%R%';
--【5】显示正好为5个字符的员工的姓名
select * from emp where ename like '_____';
--【6】显示所有员工姓名的前三个字符
select substr(e.ename,1,3),e.* from emp e;
--【7】显示所有员工的姓名，用'a'替换所有的'A'
select replace(e.ename,'A','a'),e.* from emp e;
--【8】显示满30年的服务年限的员工的姓名和受雇日期
select ename,hiredate from emp where(sysdate-hiredate)/365>30;
--【9】显示员工的详细资料，按姓名排序
select * from emp order by ename asc;
--【10】显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
select e.ename,e.hiredate from emp e order by hiredate asc;
--【11】显示所有员工的姓名，加入公司的年份和月份，按受雇日期月排序，若月份相同则将最早年份的员工排在最前面
select ename,hiredate from emp order by to_char(hiredate,'mm') asc,to_char(hiredate,'yyyy');
--【12】显示所有员工的姓名，工作，薪金，按工作的降序排列，若工作相同则按薪金升序排序
select e.ename,e.job,e.sal from emp e order by job desc,sal asc;
--【13】找出在（任何年份的）2月受聘的所有员工
select * from emp where to_char(hiredate,'mm')=2;
--【14】对于每个员工，显示其加入公司的天数
select ename,hiredate,floor(to_char(sysdate-hiredate)) from emp;
--【15】显示姓名字段的任何位置包含‘A’的所有员工的姓名
select ename from emp where ename like '%A%';
4.单行函数
- 字符串函数常用方法：
--1.lower()upper(),initcap()
--2.字符串替换replace(源字符串，子串，要将子串进行替换的串);
--3.instr(源字符串，子串):查找子串的位置，返回的是子串的索引值，索引从1开始
--4.substr(源字符串，起始位置的索引值，子串的长度):截取字符串，返回子串
--5.concat(字符串1，字符串2):连接字符串
1.lower()upper(),initcap()
select lower('ASDF'),upper('asdf'),initcap('string') from dual;
--查询员工信息，并将员工姓名的第一个字母变为大写
select initcap(e.ename),e.* from emp e;
2.字符串替换replace(源字符串，子串，要将子串进行替换的串);
select replace('play bastetball is my favritor sport','bastet','foot') from dual;
--查询员工的信息，并将员工姓名中K字母替换为Q
select replace(e.ename,'K','Q'),e.* from emp e;
3.instr(源字符串，子串):查找子串的位置，返回的是子串的索引值，索引从1开始
select instr('play bastetball is my favritor sport','bask') from dual;
select instr('play basketball is my favritor sport','momo') from dual;--找不到子串时返回0
4.substr(源字符串，起始位置的索引值，子串的长度):截取字符串，返回子串
--传两个参数时，代表从指定位置开始截取到源字符串的末尾
--第二参数为负数，截取时，从右往左数，第三个参数是截取的长度个数
select substr('play basketball is my favritor sport',10) from dual;
select substr('play basketball is my favritor sport',-10) from dual;
select substr('play basketball is my favritor sport',10,6) from dual;
5.concat(字符串1，字符串2):连接字符串
select concat('Yang','ZhiWei') from dual;
- 数值函数常用方法：
--1.取绝对值
--2.向上取整
--3.向下取整
--4.四舍五入
--5截断
--6.取余
--7.幂方
1.取绝对值
select abs(-12.3) from dual;
2.向上取整
select ceil(3.001) from dual;
select ceil(-2.01) from dual;
3.向下取整
select floor(3.001) from dual;
select floor(-2.01) from dual;
4.四舍五入
--第二个参数代表四舍五入后的小数位数
select round(3.14159265358979) from dual;
select round(3.14159265358979,3) from dual;
5截断
--没有第二个参数的时候只截取整数部分
--第二个参数代表小数的位数
select trunc(3.14199265358979) from dual;
select trunc(3.14199265358979,3) from dual;
6.取余
select mod(10,3) from dual;
7.幂方
select power(2,3) from dual;
3.--日期函数
--1.months_between(d1,d2);返回两个时间相隔的月份数
--2.add_months(d1,num):在指定日期上进行月份的加减，返回加减后的新日期
--3.next_day(d1,星期几); 返回日期（下一个星期几所对应的日期）
--4.last_day(d1)：返回日期d1所在月份最后一天的日期 (可以知道每个月总的天数)
1.months_between(d1,d2);返回两个时间相隔的月份数
select sysdate,months_between(sysdate,'09-11月-17') from dual;
select hiredate,months_between(sysdate,hiredate) from emp;
select hiredate,floor(months_between(sysdate,hiredate)) from emp;
2.add_months(d1,num):在指定日期上进行月份的加减，返回加减后的新日期
select sysdate,add_months(sysdate,3) from dual;
select sysdate,add_months(sysdate,-3) from dual;
3.next_day(d1,星期几); 返回日期（下一个星期几所对应的日期）
select sysdate,next_day(sysdate,'星期一') from dual;
select sysdate,next_day(sysdate,'星期五') from dual;
4.last_day(d1)：返回日期d1所在月份最后一天的日期 (可以知道每个月总的天数)
select last_day('1-2月-1994') from dual;
- -转换函数
1.自动类型转换 强制类型转换
2.to_number() to_date() to_char()
--1.字符串型向数值类型转换 to_number()
--2.字符串型向日期类型转换 to_date()
--3.数值类型向字符串型转换
--to_char(数值,字符串格式类型):
--9:代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示,用0显 示
--$:代表美元
--L：代表本地货币符号
--0:代表一位数字，如果该位没有数字则强制显示0
--4.日期类型向字符串型转换
--自动类型转换
--查询10部分以外的员工信息
select * from emp where deptno not in(10);
select * from emp where deptno in(20,30);
--自动类型转换 字符型---》数值型
select * from emp where deptno in('20','30');
--13.14 自动类型转换 字符型---》数值型
select '13.14'+10 from dual;
--两个日期相减：返回的是两个日期相隔的天数
select sysdate,hiredate,sysdate-hiredate from emp;
select sysdate-'9-7月-2017' from dual;
--此时无法进行自动类型转换，需要引入转换函数完成转换
select sysdate-to_date('9-7月-2017') from dual;
1.字符串型向数值类型转换 to_number()
select to_number('184135186132315') from dual;
2.字符串型向日期类型转换 to_date()
--to_date(默认的日期格式字符串)1个参数时，传的是默认的日期格式字符串
select to_date('9-8月-2017') from dual;
--to_date(字符串，日期格式)
select to_date('2017-8-9','YYYY/MM/DD') from dual;
select to_date('2017-8-9 12:39:56','YYYY/MM/DD HH24/MI/SS') from dual;
select to_date('2017-08-09 11:10:55','YYYY/MM/DD HH24:MI:SS') from dual;
select to_date('2017-08-09 11:10:55','YYYY-MM-DD HH24:MI:SS') from dual;
select to_date('2017/08/09 11:10:55','YYYY/MM/DD HH12:MI:SS') from dual;
select to_date('2017/08/09 11:10:55','yyyy/mm/dd hh12:mi:ss') from dual;
3.数值类型向字符串型转换
--to_char(sal):变成字符串后原样输出
select to_char(sal) from emp;
--to_char(数值,字符串格式类型):
--9:代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示,用0显示
--$:代表美元 L：代表本地货币符号
--0:代表一位数字，如果该位没有数字则强制显示0
select sal,to_char(sal,'$99,999,999.999') from emp;
select sal,to_char(sal,'L00,000,000.000') from emp;
4.日期类型向字符串型转换
--to_char(sysdate):变为默认的日期格式字符串
select sysdate,to_char(sysdate) from dual;
--to_char(sysdate，格式)
select sysdate,to_char(sysdate,'yyyy') from dual;
select sysdate,to_char(sysdate,'mm') from dual;
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"MI"分"SS"秒"') from dual;
- --其他函数
- nvl()==单分支
- nvl2()==双分支
- decode()==多分支
4.--sum() avg() 仅适用于数值类型
5.--count() max() min() 适用于所有的数据类型
--1.查询每个员工 月收入 月工资 sal+提成comm
select * from emp;
select sal,sal+comm from emp;
--问题：null不参与运算
--引入其他函数nvl()对null进行处理，解决问题
1.NVL (expr1, expr2),若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
select sal,nvl(comm,0)+sal from emp;
2.nvl2(e1,e2,e3);e1不为NULL，返回e2；e1为NULL，返回e3。
select sal,comm,nvl2(comm,500,0)+sal from emp;
3.decode()==多分支
select distinct job from emp;
select distinct job,decode(job,
'CLERK','普通职工',
'SALESMAN','销售员',
'流弊岗位'
) from emp;
5.多行函数
1. sum() avg() count() max() min()
--求公司每月发的总工资，最高工资、最低工资
select * from emp;
select sum(sal),max(sal),min(sal) from emp;
--sum() avg() 仅适用于数值类型
select sum(ename) from emp;
select avg(hiredate) from emp;
--count() max() min() 适用于所有的数据类型
2.count()
--引出问题：当没有引入分组时，select子句中字段或者单行函数不能与多行函数同时出现，除非引入分组
--分组 group by
--过滤分组 having
3.where与having的区别
--执行顺序：from--where -- group by– having– select-- order by
--求公司的总人数
.分组练习作业
--count()
select count(*) from emp;
--注意1：null值不参与统计
select count(comm) from emp;
--注意2：count()不会返回null，当表中没有记录数的时候，返回0
select count(*) from bonus;
select max(hiredate) from emp;
select min(ename) from emp;
--查询10部门的总人数
select count(*) from emp where deptno=10;
--引出问题：当没有引入分组时，select子句中字段或者单行函数不能与多行函数同时出现，除非引入分组
select deptno,count(*) from emp where deptno=10;
select deptno,count(*) from emp where deptno=10 group by deptno;
select ename,deptno,count(*) from emp where deptno=10 group by deptno,ename;
--分组 group by
--过滤分组 having
--当没有引入分组时，select子句中字段或者单行函数不能与多行函数同时出现
--引入分组后，只有出现在group by子句中字段或者单行函数才能出现select子句中，除非该字段位于多行函数
--1.查询公司各部门的人数
select deptno,count(*) from emp group by deptno;
--2.查询公司除了10部门以外各部门的总人数，最高工资，最低工资，平均工资
--分组前过滤掉10部门
select deptno,count(*),max(sal),min(sal),avg(sal)
from emp
where deptno!=10--过滤行记录
group by deptno;
--分组后过滤掉10部门
select deptno,count(*),max(sal),min(sal),avg(sal)
from emp
group by deptno
having deptno<>10;--过滤分组记录
--3.where与having的区别
--查询公司除了10部门以外各部门的总人数，最高工资，最低工资，平均工资,排除平均工资低于2000的部门
--where子句的后边不能跟多行函数
--经典错误
select deptno,count(*),max(sal),min(sal),avg(sal)
from emp
where deptno!=10
and avg(sal)>=2000--此时逻辑不通
group by deptno;
--查询公司除了10部门以外各部门的总人数，最高工资，最低工资，平均工资,排除平均工资低于2000的部门
--having子句的后边能跟多行函数
select deptno,count(*),max(sal),min(sal),avg(sal)
from emp
group by deptno
having deptno<>10
and avg(sal)>=2000;
--关键字： select from where group by having order by
--执行顺序：from--where -- group by– having– select-- order by
4分组习题
select * from emp;
--1.在emp表中，列出工资最小值小于2000的职位。
select job,min(sal)
from emp
group by job
having min(sal)<2000;
--2.列出平均工资大于1200元的部门和工作搭配组合
select deptno,job,avg(sal)
from emp
group by deptno,job
having avg(sal)>1200;
--3.统计人数小于4的部门的平均工资。
select deptno,avg(sal),count(deptno)
from emp
group by deptno
having count(deptno)<4;
--4.统计各部门的最高工资，排除最高工资小于3000的部门。
select deptno,max(sal)
from emp
group by deptno
having max(sal)>=3000;
6.DML/增删改/
--DML 数据操作语言 Data manipulation language
--insert update delete
--添加数据 insert into 表 values()
--修改 update 表 set 成为什么 where
--删除 delete from 表 where
--复制一张表格（包含表格中的数据）
create table temp as (select * from emp);
select * from temp;
--复制一张表结构
create table temp2 as(select * from temp where 1=2);
select * from temp2;
添加数据 insert into 表 values()
方式1：
使用时有前提条件，必须保证添加数据的顺序与表结构一致
insert into temp2 values(1111,'yangzhiwei','Teacher','7296',sysdate,3000,200,40);
--事务： 提交：真正将数据存入 回滚：撤销数据存入
方式2：
往表中指定的列添加数据----》更加灵活
insert into temp2(ename,empno,job) values('lulu',2222,'Student');
insert into temp2(empno,ename,job,sal,comm) values(5555,'lulu','teacher',4000,100);
--添加数据成功的前提条件：要满足该表的约束条件才行
insert into temp2(empno,ename,job) values(6666,'tt','killer');
方式3：
来个狠一点的
insert into temp2(select * from emp);
注意添加时间
往Oracle数据库中插入日期型数据（to_date的用法）
今天碰到了需要往Oracle数据库中插入日期型数据，要使用TO_DATE函数，在网上发现一篇写的不错的文章，摘来一起分享。
INSERT INTO FLOOR VALUES ( to_date ( '2007-12-20 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' ) ) ;
查询显示：2007-12-20 18:31:34.0
-------------------
INSERT INTO FLOOR VALUES ( to_date ( '2007-12-14 14:10' , 'YYYY-MM-DD HH24:MI' ) );
查询显示：2007-12-14 14:10:00.0
-------------------
INSERT INTO FLOOR VALUES ( to_date ( '2007-12-14 14' , 'YYYY-MM-DD HH24' ) );
查询显示：2007-12-14 14:00:00.0
-------------------
INSERT INTO FLOOR VALUES ( to_date ( '2007-11-15' , 'YYYY-MM-DD' ) );
查询显示：2007-11-15 00:00:00.0
-------------------
INSERT INTO FLOOR VALUES ( to_date ( '2007-09' , 'YYYY-MM' ) );
查询显示：2007-09-01 00:00:00.0
-------------------
INSERT INTO FLOOR VALUES ( to_date ( '2007' , 'YYYY' ) );
查询显示：2007-05-01 00:00:00.0
修改 update 表 set 成为什么 where
方式1
--修改一条数据中的一列信息
update temp2 set job='Student' where empno=5555;
方式2
--修改一条数据中的多列信息
update temp2 set job='Student',sal=sal*1.5 where ename='yangzhiwei';
方式3
--修改多条数据中的1列信息
update temp2 set job='Student';
select * from temp2;
删除 delete from 表 where
方式1
-- 删除一条数据
delete from temp2 where empno=6666;
方式2
--删除多条数据
delete from temp2 where ename='lulu';
方式3
--删除表中的所有数据
--方式1：
删除是遍历的
delete from temp2;
--方式2：删除效率高
truncate table temp2;
select * from temp2;
7.多表链接查询
一.92多表连接查询
--1.笛卡尔积:无用
--2.等值连接 ：必须掌握
--3.非等值连接 一般
--4.外连接 ：左外连接 右外连接 ：
--5.自连接 了解 一张表 ：一表两用
1.笛卡尔积:无用
select * from emp;
select * from dept;
select * from emp,dept;
2.等值连接 ：必须掌握
select * from emp,dept where emp.deptno=dept.deptno;
--使用表的别名：方便
select * from emp e,dept d where e.deptno=d.deptno;
--查询每个员工的姓名、部门编号、部门名称
--两表连接查询，当显示的字段是两表的中都有的字段的时候，需要指定字段的归属表
select ename,e.deptno,dname
from emp e,dept d
where e.deptno=d.deptno;
3.非等值连接 一般
--查询员工姓名、工资，工资的等级 emp salgrade
select * from emp;
select * from salgrade;
select ename,sal,grade
from emp e,salgrade s
where e.sal>=s.losal and e.sal<=s.hisal;-- 连接类型：非等值连接
4.外连接 ：左外连接 右外连接 ：
--外连接概念：两个表连接查询中，除了显示两方匹配的数据外，还需显示某一方不匹配的数据
--外连接运算符：(+)
--主表：是from关键字的第一个表
--从表：是from关键字的第二个表
--外连接：会将运算符放在从表匹配的列上
--查询所有的部门的信息【所有】，并将该部门对应的员工信息查询出来
select * from dept d, emp e where d.deptno=e.deptno(+);
select * from dept d, emp e where e.deptno(+)=d.deptno;
--主表：是from关键字的第二个表
--从表：是from关键字的第一个表
--外连接：会将运算符放在从表匹配的列上
--查询所有的员工信息【所有】，并将该员工对应的部门信息查询出来
select * from emp e, dept d where e.deptno=d.deptno(+);
select * from emp e, dept d where d.deptno(+)=e.deptno;
5.自连接 了解 一张表 ：一表两用
--查询员工编号、员工姓名、领导编号、领导的姓名
select e.empno,e.ename,mgr.empno,mgr.ename
from emp e,emp mgr--员工表--领导表
where e.mgr=mgr.empno;--关联条件
6.n个表关联查询
--n个表关联查询， 至少需要(n-1)个关联条件
--查询员工姓名、部门编号、部门名称、工资等级 emp dept salgrade
select e.ename,e.deptno,d.dname,s.grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and--一个关联条件
e.sal>=s.losal and e.sal<=s.hisal;--另一个关联条件
7.外连接的使用点
--查询每个部门【所有的部门】的总人数
--左连接
select * from emp;
select * from dept;
select d.deptno,count(empno)
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno;
--复杂
select d.*,count(empno)
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc
--92多表连接查询的缺点：会造成概念模糊，表的关联条件与过滤行记录条件分不清，出现相模糊的情况
--查询除了10部门以外部门【所有的部门】的总人数
select d.deptno,count(empno)
from dept d,emp e
where d.deptno=e.deptno(+)--关联条件
and d.deptno!=10--过滤行记录条件
group by d.deptno;
二.99式多表连接查询解决。
--引入99式多表连接查询解决。
--SQL99的多表连接查询
1.交叉连接 无用 类似于笛卡尔积
select * from emp;
select * from dept;
select * from emp,dept;
select * from emp cross join dept;
2.自然连接 natural join
--显示：两个表同名的列只显示一列，
--连接：会自动将两张表【所有】同名的列进行自动连接
select * from emp natural join dept;
select * from emp natural inner join dept;--inner可以省略不写
--两个表同名的列，显示的时候不需指定同名列的归属表，否则会报错
select deptno from emp natural join dept;
select emp.deptno from emp natural join dept;--报错
3.Using连接 可以指定两表中同名的列进行连接
--使用前提：两表中必须要有同名的列
--显示：两个表同名的列只显示一列， 其他未指定进行连接的列照常显示
--两个表同名的列，显示的时候不需指定同名列的归属表，否则会报错
select * from emp join dept using( deptno);
--同名的列不能加表别名.列名表示
select * from emp join dept using(emp.deptno=dept.deptno);--报错
select emp.deptno from emp join dept using (deptno);--报错
4.on连接 重点：可以灵活的指定两表连接的列（并不必须要求列名一样）
select * from emp join dept on (emp.deptno=dept.deptno);
--语法没问题
--显示：两表同名的列，需指定列的归属表才行，否则会报错，与92式sql一样
select emp.deptno from emp join dept on (emp.deptno=dept.deptno);--灵活的指定两表连接的列
--查询员工姓名、部门编号、部门名称
select ename,emp.deptno,dname
from emp join dept
on(emp.deptno=dept.deptno);--连接类型：等值连接
--查询员工姓名、工资、工资等级
select ename,sal,grade
from emp join salgrade
on(sal between salgrade.losal and salgrade.hisal);--连接类型：非等值连接
--5.99式sql的好处:让表关联条件与过滤条件分开，解决了92式多表连接查询的关联条件与过滤条件概念不清晰问题。
--查询除了10部门以外部门【所有的部门】的总人数
select * from emp;
select emp.deptno,count(empno)
from emp join dept on(emp.deptno=dept.deptno)--表关联条件
where emp.deptno!=10--过滤条件
group by emp.deptno;
6.null是否参与连接？ null不参与连接
select * from emp e1 join emp e2 using(comm);
7.外连接 概念：除了显示两表匹配的数据以外，还需显示某一方【某一张表】不匹配的数据，对于99，有满外连接的概念，但是92没有
--左外连接
--查询所有部门信息【显示所有】，以及对应的员工信息
select * from dept left outer join emp on(emp.deptno=dept.deptno);
--右外连接
--查询所有部门信息，以及对应的员工信息【显示所有】
select * from dept right join emp on(emp.deptno=dept.deptno);--outer可以省略不写
--满外连接
--查询所有部门信息【显示所有】，以及对应的员工信息【显示所有】
select * from dept full join emp on(emp.deptno=dept.deptno);
三.92与99总结
--n个表关联，至少需要【n-1】个关联条件
--3个表 emp dept salgared
--查询员工姓名、工资、部门名称、工资等级
92式
select ename,sal,dname,grade
from emp,dept,salgrade
where emp.deptno=dept.deptno
and emp.sal between salgrade.losal and salgrade.hisal;
99式
select ename,sal,dname,grade
from emp join dept on(emp.deptno=dept.deptno)
join salgrade on(sal between salgrade.losal and salgrade.hisal);
--今后的sql写法：92 99
--查询工资大于1000的各部门编号、名称、总人数 ，排除10部门，按照部门编号降序排序
--执行顺序：from where group by having select order by
92式
select * from emp;
select emp.deptno,dname,count(empno)
from emp,dept
where emp.deptno=dept.deptno--关联条件
and sal>1000 and emp.deptno!=10--过滤条件
group by emp.deptno,dname--分组
order by deptno desc;--排序
99式
select emp.deptno,dname,count(empno)
from emp join dept
on(emp.deptno=dept.deptno)-- 关联条件
where sal>1000 --过滤条件
group by emp.deptno,dname--分组
having emp.deptno!=10--过滤分组
order by deptno desc;--排序
四.练习题
-- 1、列出所有员工的年工资，按年薪从低到高排序。
select * from emp;
select nvl(comm,0)+sal from emp;
select ename,nvl(comm,0)+sal from emp order by nvl(comm,0)+sal asc;
-- 2、列出薪金比“SMITH”多的所有员工。
select sal from emp where ename='SMITH';
select ename,sal from emp where sal>(select sal from emp where ename='SMITH');
-- 3、列出所有员工的姓名及其直接上级的姓名。
select mgr from emp where mgr is not null;
select ename,mgr from emp
where empno in (select mgr from emp where mgr is not null);
select e1.ename,e2.ename from emp e1 join emp e2
on(e1.mgr=e2.empno);
-- 4、列出受雇日期早于其直接上级的所有员工。
select e1.*,e2.* from emp e1 join emp e2
on(e1.mgr=e2.empno)
where (e2.hiredate)<(e1.hiredate);
-- 5、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
select dname,emp.* from dept,emp
where dept.deptno=emp.deptno;
select dname,emp.* from dept full join emp
on(dept.deptno=emp.deptno)
-- 6、列出所有job为“CLERK”（办事员）的姓名及其部门名称。
select ename,dname from emp join dept
on(emp.deptno=dept.deptno)
where job='CLERK';
-- 7、列出最低薪金大于1500的各种工作。
select * from emp;
select distinct job from emp
where sal>1500;
-- 8、列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select * from emp;
select * from dept;
select ename from emp join dept
on(emp.deptno=dept.deptno)
where dname='SALES';
-- 9、列出薪金高于公司平均薪金的所有员工。
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
-- 10、列出与“SCOTT”从事相同工作的所有员工。
select job from emp where ename='SCOTT';
select * from emp where job=(select job from emp where ename='SCOTT');
-- 11、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select sal from emp where deptno=30;
select ename,sal from emp where sal>all(select sal from emp where deptno=30);
-- 12、列出在每个部门工作的员工数量、平均工资和平均服务期限。
select * from emp;
select deptno,count(empno),avg(sal),avg(sysdate-hiredate) from emp
group by deptno
-- 13、列出所有员工的姓名、部门名称和工资。
select ename,dname,sal from emp join dept on(emp.deptno=dept.deptno);
-- 14、列出从事同一种工作但属于不同部门的员工的一种组合。
select * from emp e1 join emp e2 on (e1.job=e2.job)
where e1.deptno!=e2.deptno;
-- 15、列出所有部门的详细信息和部门人数。
select emp.deptno,count(empno),dept.dname
from emp join dept on(emp.deptno=dept.deptno)
group by emp.deptno,dept.dname
-- 16、列出各种工作的最低工资。
select job,min(sal)
from emp
group by job
-- 17、列出各个部门的MANAGER（经理）的最低薪金。
select * from emp;
select deptno,min(sal)
from emp
where job='MANAGER'
group by deptno
-- 18、列出至少有一个员工的所有部门。
select deptno,count(empno) from emp
group by deptno
select deptno
from emp
where 0 < any (select count(empno) from emp group by deptno)
group by deptno;
五.子查询
1.单行子查询
--执行顺序：子查询在主查询前执行一次
--特点：主查询使用子查询的结果
--1.1如何查得所有比“CLARK”工资高的员工的信息
select sal from emp where ename='CLARK';
select * from emp where sal>(select sal from emp where ename='CLARK');--推荐这种写法
select * from emp where (select sal from emp where ename='CLARK')<sal;--不推荐
--1.2查询工资高于平均工资的雇员名字和工资。
select avg(sal) from emp;
select ename,sal from emp where sal>(select avg(sal) from emp);
--1.3查询和SCOTT同一部门且比他工资低的雇员名字和工资。
select sal from emp where ename='SCOTT';
select deptno from emp where ename='SCOTT';
select ename,sal
from emp
where deptno=(select deptno from emp where ename='SCOTT')
and sal<(select sal from emp where ename='SCOTT');
--1.4查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
select job from emp where ename='SCOTT';
select hiredate from emp where ename='SCOTT';
select * from emp
where job=(select job from emp where ename='SCOTT')
and hiredate<(select hiredate from emp where ename='SCOTT');
--1.5查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字
select sal from emp where ename='SCOTT';
select hiredate from emp where ename='SCOTT';
select * from emp
where sal>(select sal from emp where ename='SCOTT')
or hiredate<(select hiredate from emp where ename='SCOTT');
2.多行子查询
--多行子查询返回多行记录
--ALL 和子查询返回的所有值比较
--ANY 和子查询返回的任意一个值比较
--IN 等于列表中的任何一个
--2.1查询工资低于任意一个“CLERK”的工资的雇员信息。
select sal from emp where job='CLERK';
select * from emp where sal<any(select sal from emp where job='CLERK');
--2.2查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
select sal from emp where job='SALESMAN';
select * from emp where sal>all(select sal from emp where job='SALESMAN');
--2.3查询部门20中职务同部门10的雇员一样的雇员信息。
select job from emp where deptno=20;
select job from emp where deptno=10;
select * from emp
where job in (select job from emp where deptno=20)
and deptno=10;
--2.4查询在雇员中有哪些人是领导
select mgr from emp where mgr is not null;
select * from emp
where empno in( select mgr from emp where mgr is not null);
--2.5找出部门编号为20的所有员工中收入最高的职员
select max(sal) from emp where deptno=20;
select * from emp
where sal>=all(select max(sal) from emp where deptno=20)
and deptno=20;
--2.6查询每个部门平均薪水的等级
--步骤1：将每个部门平均薪水结果当做一个表
select deptno,avg(sal) avg_sal
from emp
group by deptno;
--步骤2：
select deptno,t.avg_sal,grade
from salgrade,(select deptno,avg(sal) avg_sal
from emp
group by deptno) t
where t.avg_sal between salgrade.losal and salgrade.hisal;
- 数据库对象
1.用户 创建、授权、删除
1.创建一个用户,
需要拥有dba权限的用户才能创建用户
create user yzw identified by yzw;
2.给用户授予权限,
需要拥有dba权限的用户才能操作
grant connect,resource to yzw;
grant dba to yzw;--授予较高的权限
3.回收用户的权限
需要拥有dba权限的用户才能操作
revoke connect,resource from yzw;
4.给用户修改密码
alter user yzw identified by yzw123;
5.删除用户
drop user yzw;
--数据库表对象
2.表 创建、修改、删除
1.创建一个表
--建立一张用来存储学生信息的表
--字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
create table student(
sno number(11),
sname varchar2(10),
sex char(5),
enterdate date,
clazz varchar2(10),
email varchar2(20)
);
2.修改表
--2.1 添加表字段
alter table student add(phone varchar2(11));
--2.2 修改表字段（字段名、字段的数据类型）
alter table student modify(sex char(2));
--2.3 重命表名字段名
alter table student rename column phone to phonenum;
--2.4 删除表字段
alter table student drop column phonenum;
3.重命名表
rename student to stu;
4.删除表
drop table stu;
5.删除拓展
--1.查看回收站
select * from recyclebin;
--2.从回收站中将表还原
flashback table stu to before drop;
--3.删除时不经过回收站（物理删除）
drop table stu purge;
3.表约束 对添加的数据进行约束
--字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
--学号是主键
--姓名不能为空AAa
--性别默认值是男
--年龄范围18---30岁
--Email唯一
--1.主键约束：一列
create table student(
sno number(5),
sname varchar2(10),
sex char(5),
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20)
);
select * from student;
drop table student;
insert into student(sno,sname,sex,age,enterdate,clazz,email)
values(110,'momo','空',888,sysdate,'javaEE508','434344as@qq.com');
--1.主键约束：一列
create table student(
--sno number(5) primary key,--2.添加列级约束，学号是主键，系统会自动给一个名字
sno number(5) constraints pk_student primary key,--3.添加列级约束，学号是主键，自己给一个名字
sname varchar2(10),
sex char(5),
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20)
-- constraints pk_student primary key(sno) --1.添加表级约束，学号是主键
);
--2.主键约束：联合主键 可以有多列构成主键
create table student(
sno number(5),
sname varchar2(10),
sex char(5),
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20),
constraints pk_student primary key(sno,sname) --1.添加表级约束，学号是主键
);
--验证
--3.非空约束 ：只能定义列级约束
--姓名不能为空
create table student(
sno number(5),
sname varchar2(10) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex char(5),
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20),
constraints pk_student primary key(sno) --1.添加表级约束，学号是主键
);
--验证
insert into student(sno,sname,sex,age,enterdate,clazz,email)
values(111,'','空',888,sysdate,'javaEE508','434344as@qq.com');
--4.添加默认约束
--性别默认值是男
create table student(
sno number(5),
sname varchar2(10) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex char(5) default '男',--3.添加默认约束 ,性别默认值是男
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20),
constraints pk_student primary key(sno) --1.添加表级约束，学号是主键
);
--验证：要让默认值生效，不是对该列添加‘’，而是不对该列添加
insert into student(sno,sname,sex,age,enterdate,clazz,email)
values(111,'mom','',888,sysdate,'javaEE508','434344as@qq.com');
insert into student(sno,sname,age,enterdate,clazz,email)
values(112,'mom',888,sysdate,'javaEE508','434344as@qq.com');
--5.添加检查约束 ：
--年龄范围18---30岁
create table student(
sno number(5),
sname varchar2(10) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex char(5) default '男',--3.添加默认约束 ,性别默认值是男
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20),
constraints pk_student primary key(sno), --1.添加表级约束，学号是主键
constraints ck_student_age check(age>=18 and age<=30)
);
--验证：
insert into student(sno,sname,sex,age,enterdate,clazz,email)
values(111,'mom','男',20,sysdate,'javaEE508','434344as@qq.com');
--6.添加唯一约束
--Email唯一
create table student(
sno number(5),
sname varchar2(10) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex char(5) default '男',--3.添加默认约束 ,性别默认值是男
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20),
constraints pk_student primary key(sno), --1.添加表级约束，学号是主键
constraints ck_student_age check(age>=18 and age<=30),--4.添加检查约束,年龄范围18---30岁
constraints uk_student_email unique(email)--5.添加唯一约束 Email唯一
);
--验证：
insert into student(sno,sname,sex,age,enterdate,clazz,email)
values(112,'mom','男',20,sysdate,'javaEE508','434344as@qq.com');
1.表约束总结
--列级约束
create table student(
sno number(5) constraints pk_student primary key,
sname varchar2(10) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex char(5) default '男',--3.添加默认约束 ,性别默认值是男
age number(3) constraints ck_student_age check(age between 18 and 30),
enterdate date,
clazz varchar2(10),
email varchar2(20) constraints uk_student_email unique
);
--表级约束
create table student(
sno number(5),
sname varchar2(10) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex char(5) default '男',--3.添加默认约束 ,性别默认值是男
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20),
constraints pk_student primary key(sno), --1.添加表级约束，学号是主键
constraints ck_student_age check(age>=18 and age<=30),--4.添加检查约束,年龄范围18---30岁
constraints uk_student_email unique(email)--5.添加唯一约束 Email唯一
)
4.外键约束 ：主表 依赖表||从表||子表
--班级表（主表 ） 学生表（子表）
--字段 班级编号 班级名称 班级地点
1.主表
create table clazz(
classno number(5) primary key,--主键
cname varchar2(15),
cloc varchar2(50)
)
--添加数据
insert into clazz values(506,'506JAVA班','京南');
insert into clazz values(507,'507前端班','京南');
insert into clazz values(508,'508VIPjava班','京南');
2.子表
--字段包含学号、姓名、性别，年龄、入学日期、班级，email,班级编号(外键) 等信息
--列级约束定义外键约束
create table student(
sno number(5) constraints pk_student primary key,
sname varchar2(8) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex varchar2(2) default '男',--3.添加默认约束 ,性别默认值是男
age number(3) constraints ck_student_age check(age between 18 and 30),
enterdate date,
clazz varchar2(10),
email varchar2(20) constraints uk_student_email unique,
classno number(5) references clazz(classno) --列级约束定义外键约束--默认是RESTRICT方式
)
--表级约束指定外键约束
create table student(
sno number(5) constraints pk_student primary key,
sname varchar2(8) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex varchar2(2) default '男' check (sex in('男','女')),--3.添加默认约束 ,性别默认值是男
age number(3) constraints ck_student_age check(age between 18 and 30),
enterdate date,
clazz varchar2(10),
email varchar2(20) constraints uk_student_email unique,
classno number(5),
constraints fk_student_classno foreign key(classno) references clazz(classno)--表级约束指定外键约束--默认是RESTRICT方式
)
--添加数据
insert into student(sno,sname,sex,age,enterdate,clazz,email,classno)
values(110,'momo','男',22,sysdate,'javaEE508','434344as1@qq.com',508);
insert into student(sno,sname,sex,age,enterdate,clazz,email,classno)
values(111,'lulu','女',22,sysdate,'javaEE508','434344as2@qq.com',508);
insert into student(sno,sname,sex,age,enterdate,clazz,email,classno)
values(112,'nana','女',22,sysdate,'javaEE508','434344as3@qq.com',508);
3.删除主表数据：对于主表的删除和修改主键值的操作，会对依赖关系产生影响，
--解决方法有3种
--第一种：RESTRICT方式
--要删除主表的数据，要先删除子表对应的记录才行
delete from clazz where classno=508;
delete from clazz where classno=507;
delete from student where classno=508;
delete from clazz where classno=508;
--第二种：CASCADE方式 霸道 ：删除主表数据的同时也会直接将子表对应的数据一起删除
create table student(
sno number(5) constraints pk_student primary key,
sname varchar2(10) constraints nn_student_sname not null,
sex char(5) default '男' check (sex in('男','女')),
age number(3) constraints ck_student_age check(age between 18 and 30),
enterdate date,
clazz varchar2(10),
email varchar2(20) constraints uk_student_email unique,
classno number(5),
--CASCADE方式
constraints fk_student_classno foreign key(classno) references clazz(classno) on delete cascade
);
--删除主表数据的同时也会直接将子表对应的数据一起删除
delete from clazz where classno=508;
--第三种：SET NULL方式 ：删除主表数据的同时，会将子表对应的外键值置为空，但是子表记录还在。
--SET NULL方式
create table student(
sno number(5) constraints pk_student primary key,
sname varchar2(10) constraints nn_student_sname not null,
sex char(5) default '男' check (sex in('男','女')),
age number(3) constraints ck_student_age check(age between 18 and 30),
enterdate date,
clazz varchar2(10),
email varchar2(20) constraints uk_student_email unique,
classno number(5),
--SET NULL方式
constraints fk_student_classno foreign key(classno) references clazz(classno) on delete set null
);
--删除
delete from clazz where classno=508;
4.添加约束的时机 ：
1.创建表时定义约束 2.修改表时定义约束
--修改表时定义约束
create table student(
sno number(5),
sname varchar2(10) constraints nn_student_sname not null,--2.添加列级非空约束，姓名不能为空
sex char(5) default '男',--3.添加默认约束 ,性别默认值是男
age number(3),
enterdate date,
clazz varchar2(10),
email varchar2(20),
classno number(5)
);
--修改表时添加约束
alter table student add constraints pk_student primary key(sno); --1.添加表级约束，学号是主键
alter table student add constraints ck_student_age check(age>=18 and age <= 30);--4.添加检查约束,年龄范围18---30岁
alter table student add constraints uk_student_email unique(email);--5.添加唯一约束 Email唯一
alter table student add constraints fk_student_classno foreign key(classno) references clazz(classno) on delete set null;--修改表时删除指定约束
----------------------------------------------------------------------------------------------
5.序列化
--数据库对象 序列对象 sequence ,是oracle独有的对象，mysql数据没有序列对象
1.创建序列对象
--最简单的一个序列
create sequence seq_student_sno;--默认开始值是1，且默认递增步长是1
--根据自己需求创建序列对象 可以指定开始值，也可以指定增长步长
create sequence seq_student_sno start with 1000 increment by 2;
create sequence seq_student_sno start with 1000 increment by 5 minvalue 1000 maxvalue 1020 cycle cache 2;
2.如何查看序列对象
--注意：要先调用nextval一次之后，才能调用currval
--获取序列对象的下一个值
select seq_student_sno.nextval from dual;
--获取序列对象的当前值
select seq_student_sno.currval from dual;
3.序列的作用
--一般会将序列对象用于主键值递增，或者用于标识值的唯一性
--以学生为例
insert into student(sno,sname,sex,age,enterdate,clazz,email,classno)
values(seq_student_sno.nextval,'momo','女',22,sysdate,'javaEE508',seq_student_sno.currval||'andy@qq.com',508);
4.如何删除序列
drop sequence seq_student_sno;
-----------------------------------------------------------------------------
6.索引：
index 作用：能够大大的提高查询速度
1.创建索引
create index index_emp_sal on emp(sal);
create index index_emp_ename on emp(ename);
--索引作用到多个字段的时候用逗号隔开即可。
create index index_emp_sal_ename on emp(sal,ename);
2.如何查看索引
让索引起作用,按F5调出解释计划窗口
--没有索引时所耗时间 : 3
select sal from emp where sal>1000;--3
select sal from emp where ename='scott';--3
select * from emp where ename='scott';--3
--有索引的时候所耗时间:1
select sal from emp where sal>1000;--1
select ename from emp where ename='scott';--1
select sal from emp where ename='scott';--2
select * from emp where ename='scott';--2
3.删除索引
drop index index_emp_sal;
drop index index_emp_ename;
drop index index_emp_sal_ename;
----------------------------------------------------
7.视图
--视图：是一个虚表，基于基本表，可以看作是一个动态的sql查询语句
select * from emp;
--创建视图需要什么角色==权限（权利）的组合 connect resource dba
--授权dba
grant dba to scott;
1.创建一个普通的视图
可以对其进行DML操作
create view view_emp(select * from emp);
create or replace view view_emp as(select * from emp);
2.操作视图
--2.1 查询视图
select * from view_emp;
--2.2用途1：可以将基本表的表结构隐藏起来
create or replace view view_emp as(select empno,ename,job,deptno from emp);
--2.3可以对普通的视图进行DML操作,实际上是对底层的基本表进行DML操作
insert into view_emp(empno,ename,job,deptno) values (2222,'张三',3000,10);
select * from emp;
--2.4用途2：可以将复杂sql语句定义为视图，今后进行直接面向视图操作，让问题变得简单
--当需要将复杂的sql语句定义为视图时，对于多行函数的查询结果，需要使用别名指定
--查询每个部门的总人数，及各部门的平均工资
select d.deptno,count(empno) cnt,avg(nvl(sal,0))
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno
create or replace view view_emp as(
select d.deptno,count(empno) cnt,avg(nvl(sal,0)) avg_sal
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno
)
select * from view_emp
--2.5,基于上述的视图，可否直接操作各个部门的平均工资----不可以
update view_emp set avg_sal=avg_sal*1.5 where deptno=10;--操作非法
--2.6此时可以将2.4的视图定义为只读视图，说明只可以进行查询的操作，不能进行DML操作
create or replace view view_emp as(
select d.deptno,count(empno) cnt,avg(nvl(sal,0)) avg_sal
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno
) with read only;
select * from view_emp;
3.创建一个只读的视图
不可以对其进行DML操作
create or replace view view_emp as(select empno,ename,job,deptno from emp) with read only;
--只读视图不能填加数据
insert into view_emp(empno,ename,job,deptno) values (2222,'张三',3000,10);
4.视图的定义还可以基于一个视图
create or replace view view_emp2 as (select * from view_emp);
select * from view_emp2;
5.删除视图
drop view view_emp;
drop view view_emp2;
--------------------------------------------------------
8.事务
--事务:（Transaction）是一个DML操作的序列 ，要么都OK，要么都不OK。
1.转账业务
--设计一个账户表
create table uaccount(
uno number(5) primary key,--账户号
uname varchar2(10), --账户名
money number(10)--余额
);
--添加两个账户 A B
insert into uaccount(uno,uname,money) values(111,'A',5000);
insert into uaccount(uno,uname,money) values(112,'B',5000);
--转账操作 A----》B：1000
update uaccount set money=money-1000 where uname='A';
update uaccount set money=money+1000 where uname='B';
select * from uaccount;
--事务操作
2.提交
commit;
3.回滚
rollback;
-------------------------------------------------
9.伪列
--伪列：rowid rownum,需要特别指定才会显示
1.rowid
--rowid在记录创建时生成，而且是不变，直接指向硬件上的存储位置
select emp.*,rowid from emp;
--rowid的优点：查询快，删除也快
select emp.*,rowid from emp where rowid='AAAR3sAAEAAAACVAAA';
2.rownum
:需要特别指定才会显示
--rownum:运算符只能是 < <=
select emp.*,rownum from emp;
3.用rownum实现分页
--查询员工信息，按照工资升序排序，要前5条数据即可。TOP-N,每页有5条数据，第二页：6-10
--步骤1：查询员工信息，按照工资升序排序
select * from emp order by sal asc;
--步骤2：去尾操作，用rownum( 将步骤1的结果当作一张表)
select rownum,t1.* from(select emp.*,rownum from emp order by sal asc) t1;
select rownum,t1.* from(select emp.*,rownum from emp order by sal asc) t1
where rownum<=5;
--问题：目前只能显示第一页，要显示第2页？第n页？
--步骤3：砍头操作，用rownum的别名实现（将步骤2的结果当作一张表）
--第二页
select rownum,t1.* from(select emp.*,rownum from emp order by sal asc) t1
where rownum<=5;
select rownum, t2.*
from (select rownum rn, t1.*
from (select emp.*, rownum from emp order by sal asc) t1
where rownum <= 10) t2
where rn >= 6
--第三页
select rownum, t2.*
from (select rownum rn, t1.*
from (select emp.*, rownum from emp order by sal asc) t1
where rownum <= 15) t2
where rn >= 11
----------------------------------------------------------------
- 导包
1.dmp
使用PL/SQL Developer导入/出数据
Oracle Export/ Oracle Import
扩展名dmp
使用的就是exp/imp命令
二进制文件，无法查看
可以跨平台，效率高、使用最广
2.sql
SQL Inserts
扩展名sql
可使用记事本等查看，效率不如第一种
适合小数据量导入导出
不能导出blob、clob等字段
3.pde
PL/SQL Developer
扩展名pde
PL/SQL Developer的自有文件格式，只能使用该软件来导入导出
很少使用
11.三大范式
1.参考App
9.数据库建表
1.学习概念模型（了解）
新建--》概念模型(Conceptual Date Model)or物理模型(physical date model)--》
2.学习物理模型（必须会）
注意：指定数据库physical date model-----》DBMS
大概步骤见上图
注意：建立物理模型时应当制定数据库
3.正向工程：将数据库模型应用到关系数据库上（比如Oracle）
1.生成sql脚本（必须会）
Datebase----》 Generate Datebase.....
将sql文件拉人数据库软件
4.逆向工程：将关系数据上的数据库变为数据库模型（看数据库设计图纸）
File--->reverse Engineer---->Datebase
5.将数据库模型生成图片
Edit----》export image------》
- 将数据库模型生成帮助文档
Report-----》Report wizard......
数据库补充：
- 修改表字段大小
Alter table 表 modify (字段 varchar2(200));
alter table LKProDayConDetails modify (riskname varchar2(200));
- 表添加行
Alter table 表 add (字段名 字段类型);
Alter table bi_trade_info add (LEAGUER_TYPE varchar2(100));
- 表删除行
Alter table 表 drop column 字段名
Alter table bi_trade_info drop column LEAGUER_TYPE;
4.视图与表的区别
区别：1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表
4.存储过程
个人理解
写好的sql
使用 exec 调用 并且传参；
exec insertTask('个险-省分报表生成批处理', 'ProvincesFileGeneratedBL', '01', '01', '0');
相当于java中的方法；不用每次都写，写好之后，用的时候调用就ok了
官方解说
5.触发器
比如一个人退学了，他的餐厅信息，图书信息等
只需要删除student表信息 触发删除个人餐厅信息，个人图书信息
6.三大范式
第一范式 1NF:
列不可再分：比如电话字段要分为，家庭电话和手机电话两个字段
第二范式2NF:
主键，每一条数据代表一个信息，全部依赖主键
第三范式3NF:
非主键不能相互依赖，学号，课程，学院，老师
课程依赖学院
扫一扫
787
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
