一,课程大纲
1,oracle函数学习
1.1单行函数
1.2多行函数
1.3转换函数
1.4其他函数
2,分组查询&分组筛选
3,增,删,改&数据备份
4,多表查询
5,子查询
6,用户管理
7,二维表创建
8,序列
9,索引
10,视图
11,二维表的约束
二,函数的学习
1,单行函数(字符函数 数值函数 日期函数)(不作为重点,日后可以上百度查)
1.1字符函数
1.1.1 select 字段名,函数名(字段名) from 表名
--查询所有的员工信息,员工姓名小写显示。
select empno,ename,lower(ename),job,mgr,sal,lower('HH') from emp
1.2数值函数
1.1.2 select 函数名(数值) from dual(虚表)
select abs(-1),ceil(2.2),floor(3.3),power(2,3),mod(5,2),round(4.55),trunc(10/3,2) from dual
1.3日期函数
1.1.3 select 函数名(转换的日期格式)
select months_between('01-1月-2018','24-6月-2017') from dual --返回两个日期间的月份数
select add_months('01-4月-2018',-4) from dual --返回指定月数后的日期
select next_day('16-4月-2018','星期二') from dual--查询最近的星期的日期
select last_day('16-4月-2018') from dual--返回当月的最后一天的日期
select round(to_date('19-4月-2018'),'DAY') from dual--按照星期进行四舍入
1.2特点:
1.2.1:不改变真实数据
1.2.2:可以和字段混用
2,多行函数(重点学习)
2.1种类:
max(),min(),avg(),sum(),count()
2.2介绍:
max():返回该字段的最大值
min():返回该字段最小的值
avg():求该字段的平均值
sum():返回该字段的和
count()
count(*):返回表的记录数
count(字段名):返回该字段非空的记录数
count(distinct 字段名):返回该字段非空去重的记录数
2.3注意:
2.3.1多行函数不能和字段混用,除非分组,因为多行函数返回的是多行的结果集,而字段的话返回的是单行的结果集
对应不上
3,转换函数
3.1种类
to_number(),to_char(),to_date()
3.2介绍
3.2.1 to_number:将字符类型转换成数值类型,可以不写,存在隐士转换
--select to_number('2222') from dual
3.2.2 to_char:将数值类型转换成字符类型
--select to_char(22222) from dual
3.2.3 to_date:
3.2.3.1 一般的使用方法:新增数据使用to_date,查询数据使用to_char
3.2.3.2 日期的格式:yyyy-mm-dd
yyyy/mm/dd
3.2.3.3 使用方法:将字符转换成日期 to_date("1999-01-01","yyyy-mm-dd")
--查询员工入职日期在82年后的信息
select * from emp where hiredate >to_date('1982-01-01','yyyy-mm-dd')
将日期转换成字符 to_char(日期,"日期格式")
select to_char(hiredate,'yyyy-mm-dd') from emp--使用指定格式 yyyy-mm-dd
4,其他函数
4.1种类
nvl(),nvl2(),decode()
4.2介绍:
nvl(字段名,新值):如果字段名不为空,就显示字段本来的之,如果为空显示新值
nvl2(字段名,处理1,处理2):如果字段不为空执行处理1,如果为空执行处理2
decode(字段名,值1,处理1,值2,处理2.............):如果字段的值和值1相等就执行处理1
如:select ename,job,decode(job,'MANAGER','经理','PRESIDENT','董事长','SALESMAN','销售','普通员工') from emp
三,分组查询&分组筛选
1,分组查询
1.1关键字:group by 字段1,字段2...............
1.2注意:
1.2.1:使用分组查询,select语句只能出现分组字段和多行函数
--查询不同部门的最高工资
select deptno,max(sal) from emp group by deptno
1.2.2:如果是多字段分组,先按第一个字段进行分组,然后在按第二个字段进行分组
--查询不同部门的不同工作岗位的人数
select deptno ,lower(job),count(*) from emp group by deptno,job order by deptno
1.2.3:where字句中不允许出现多行函数如select * from emp where count(*)>10
2,分组筛选
2.1关键字:having针对分组进行分组后的数据筛选,允许使用多行函数。
2.2注意:
2.2.1:having关键必须和分组结合使用。不允许单独使用。
2.3where和having的比较:
2.3.1 --where子句不允许出现多行函数,having允许出现多行函数
2.3.2 --where子句和having都可以使用普通字段直接进行筛选,但是where的效率高于having
2.3.3 --where执行顺序: from--->where--->group by-->select-->order by
2.3.4 --having执行顺序:from--->group by-->select--->having--->order by
2.4--结论:在分组语句中,使用where进行字段级别的筛选,使用having进行多行函数的筛选。
四,增,删,改&数据备份
1,增加数据
inster into 表名(字段名) values(值)
1.1注意:
--注意1:主键必须给值,允许为空的字段可以不给值。
--注意2:插入语句表名后跟的字段名为要赋值的字段,值和字段数量和顺序必须是一一对应的。
--注意3:如果是全字段插入,可以省略字段名部分 insert into 表名 values(值1,值2,.....)
2,删除数据
delete from 表名 --清空表
delete from 表名 where 条件 --删除指定条件的数据
trancate table 表名 --清空表 速度要比deldte快
3,修改数据
--update 表名 set 字段名=新的值,字段名=新的值...(会将字段的值全部改为新的值)
--update 表名 set 字段名=新的值,字段名=新的值... where 条件(将符合条件的数据的字段改为新的值)
4,数据备份:
--注意:只会备份表结构和表的数据,约束不会备份。
--表级别备份
--全部备份:create table 新的表名 as select * from 备份表名
--部分备份: create table 新的表名 as select 字段名,字段名,...from 备份表名
--数据整体插入
--insert into 插入表名 select * from 表名
--注意:查询语句结果的字段数据必须和插入表名的字段数量一致,类型要一致。
create table deptBak as select * from dept--全部备份
create table deptBak2 as select deptno,dname from dept-- 部分备份
select * from deptBak2
insert into deptBak2 select deptno,dname from dept
4.2:总结:
--注意:增加删除修改的数据SQL语句执行完毕后,不会立马进行数据的写入。
--还需要手动对数据进行提交,如果数据有问题还可以回滚
五,多表查询
5.1 需求:当获取的数据在多张表的时候
5.2 方式:92方式
99方式
5.3 对比:
5.3.1 92方式:
5.3.1.1 笛卡儿积:将多个表的数据进行一一对应,所得到结果为多表的笛卡尔积。
select * from emp,dept where emp.deptno=dept.deptno
5.3.1.2 等值连接
select ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno--等值连接筛选
5.3.1.3 不等值连接
--查询员工姓名,工作,工资,工资等级
select * from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal
5.3.1.4 自然连接
5.3.1.5 外连接
5.3.1.5.1 左外连接:加在右边,显示左边对应字段没有值的数据
--查询员工姓名,工作,薪资,部门名称及没有部门的员工信息
select * from emp e,dept d where e.deptno=d.deptno(+)
5.3.1.5.2 右外连接:加在做边,显示右边边对应字段没有值的数据
--查询员工姓名,工作,薪资,部门名称及没有员工信息的部门
select * from emp e,dept d where e.deptno(+)=d.deptno
5.3.2 99方式
5.3.2.1 笛卡儿积:使用corss join关键字,如:selcet * from emp corss join dept
5.3.2.2 自然连接:
a:使用inner join 关键字如:select * from emp inner join dept using(deptno)
b:使用inner join,on关键字如:select * from emp e inner join dept d on e.deptno=d.deptno
5.3.2.3 外连接
5.3.2.3.1 左外连接:select 内容 from 表名 left outer join 表名 on 连接条件
--查询员工姓名,工作,薪资,部门名称及没有部门的员工信息
select * from emp e left outer join dept d on e.deptno=d.deptno
5.3.2.3.2 右外连接:select 内容 from 表名 right outer join 表名 on 连接条件
--查询员工姓名,工作,薪资,部门名称及没有员工的部门信息
select * from emp e right outer join dept d on e.deptno=d.deptno
5.3.2.3.3 全连接:select 内容 from 表名 full outer join 表名 on 连接条件
select * from emp e full outer join dept d on e.deptno=d.deptno
5.3.2.3.4 自连接:--查询员工及其上级领导姓名
select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno
5.4,三表联合查询
5.4.1 : --SQL92实现:查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
select e.*,d.dname,c.cname
from emp e,dept d,city c
where (e.deptno=d.deptno and d.loc=c.cid and sal>200)
or (e.deptno=d.deptno and d.loc=c.cid and sal>2000)
order by e.sal
5.4.2 : 用法:
--select 内容 (别名,连接符,去除重复,oracle函数,逻辑运算)
--from 表名1,表名2,表名3...
--where 条件(连接条件,普通筛选条件,where子句关键字)
--group by 分组字段
--having 多行函数筛选
--order by 排序字段
5.4.3 :SQL99实现:查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
select e.*,d.dname,c.cname
from emp e
inner join dept d
on e.deptno=d.deptno
inner join city c
on d.loc=c.cid
gorup by e.sal
5.4.4 : 使用:
--select 内容 from 表名1
-- inner join 表名2
-- on 连接条件
--inner join 表名3
--on 连接条件
--where 普通筛选条件
--group by 分组
--having 多行函数筛选
--order by 排序
六,子查询(挺重要)
1.1 使用场景:当给定的查询条件比较模糊时,可以考虑使用子查询
1.2 分类:单行子查询
多行子查询
1.3 介绍:
1.3.1 单行子查询:筛选条件不明确需要执行一次查询,并且查询结果一个字段并值只有一个
--查询所有比雇员“CLARK”工资高的员工信息
select * from emp where sal>(select sal from emp where ename=‘CLARK’ )
--查询工资高于平均工资的员工的名字和工资
select ename,sal from emp where sal>(select avg(sal) from emp)
1.3.2 多行子查询:子查询的结果只有一个字段但是字段有n个值,考虑使用多行子查询,其实就是使用关键字
1.3.2.1:--关键字1:any 任意
--select 内容 from 表名 where 字段名 比较运算符 any 子查询语句
--关键字2:all 所有
--select 内容 from 表名 where 字段名 比较运算符 all 子查询语句
--关键字3:in 表示任意存在,相当于 = any
--select 内容 from 表名 where 字段名 in 子查询语句
--select 内容 from 表名 where 字段名 not in 子查询语句
1.3.2.2:--查询工资高于任意一个CLERK的所有员工信息
select * from emp where sal> any (select sal from emp where job='CLERK')
1.3.2.3:--查询工资高于所有SALESMAN的员工信息
select * from emp where sal> all (select sal from emp where job='SALESMAN')
七,用户管理
1.1 创建用户
1.1.1:c reate user ljx identified by ljx;
1.2 赋予权限
1.2.1:grant connect to bjsxt;--给用户赋予登录权限
grant resource to bjsxt;--给用户资源操作权限
grant dba to bjsxt;--给用户赋予dba权限
select * from scott.emp--查看其它用户的表 使用用户名.表名
1.3 删除权限
1.3.1: revoke dba from bjsxt;
1.4 删除用户
1.4.1:drop user bjsxt;
八,序列
8.1 特点:
8.1.1 --特点1:默认开始是没有值的,也就是指针指在了没有值的位置。
8.1.2 --特点2:序列名.nextval每次执行都会自增一次,默认步长为1
8.1.3 --特点3:序列名.currval查看当前序列的值。开始是没有的。
8.2 作用:
8.2.1 --作为主键使用,动态的获取之间的值,这样新增数据的时候极大的避免了主键冲突
8.3 举例:
8.3.1 -- create sequence cc;--创建序列cc
-- insert into teacher values(cc.nextval,'张三');
8.4 删除序列
--drop sequence 序列名
drop sequence cc
九,索引
9.1 特点:
9.1.1 --显示的创建,隐式的执行
9.2 作用:
9.2.1 --提升查询的效率
9.3 创建索引
9.3.1 -- create index 索引名 on 表名(字段名)
-- create index index_teacher_tname on teacher(tname)--创建索引
9.4 删除索引
9.4.1 --drop index 索引名
十,视图
10.1 特点:
10.1.1 --特点1:保护真实表,隐藏重要字段的数据。保护数据。
10.1.2 --特点2:在视图中的操作会映射执行到真实表中
10.1.3 --特点3:可以手动开启只读模式 使用关键字 with read only
10.2 注意:
10.2.1 --视图的创建必须拥有dba权限
10.3 创建视图:
10.3.1 --create view 视图名 as select 对外提供的内容 from 真实表名
--create view stu as select sno,sname,sage from student
10.4 删除视图:
10.4.1 drop view 视图名
十一,二维表的约束
11.1 分类:
主键约束 primary key
非空约束 not all
检查约束 cheak(age>10)
唯一性约束 unique
外键约束 references clazz(cno)
11.2 主键约束
11.2.1 特点:
非空唯一
11.2.2 使用:
--直接在创建表的字段后使用 primary key
--在创建表的语句的最后面使用 constraints pk_表名_字段名 primary key(字段名)
--在创建表后使用 alter table 表名 add constraints pk_表名_字段名 primary key(字段名);
--删除主键 alter table student drop constraints 主键的约束名;
11.3 非空约束
11.3.1 使用:
--直接在创建表的字段后使用 not null 关键字
--在创建表的语句的最后面使用 constraints ck_表名_字段名 check(字段名 is not null)
--在创建表后使用 alter table 表名 add constraints ck_表名_字段名 check(字段名 is not null);
--删除非空约束 alter table student drop constraints 非空约束名;
11.4 检查约束
11.4.1 使用:
--直接在创建表的字段后使用 check(条件) 例如 sage number(3) check(sage<150 and sage>0),
--在创建表的语句的最后面使用 constraints ck_表名_字段名 check(条件)
--在创建表后使用 alter table 表名 add constraints ck_表名_字段名 check(条件);
--删除检查约束 alter table student drop constraints 检查约束名;
11.5 唯一性约束
11.5.1 使用:
--直接在创建表的字段后使用 unique
--在创建表的语句后面使用 constraints un_表名_字段名 unique(字段名);
--在创建表后使用 alter table 表名 add constraints un_表名_字段名 unique(字段名);
--删除约束:alter table 表名 drop constraints 唯一约束名;
11.6 外键约束
11.6.1 作用:
当在子表中插入的数据在父表中不存在,则会自动报错
11.6.2 使用:
--在子表中的字段后直接使用 references 父表名(字段) 例如: cid number(10) references clazz(cno)
--在创建表语句的最后面使用 constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
--在创建表后使用:alter table 表名 add constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
--删除外键:alter table 表名 drop constraints 外键约束名
11.6.3 注意:
在使用外键时,无法删除父表的数据,除非级联
11.7 举例(三种添加约束的方式)
11.7.1
create table student(
sno number(10) ,--primary key
sname varchar2(100) ,--not null
sage number(3), --check(sage<150 and sage>0)
ssex char(4) ,--check(ssex='男' or ssex='女')
sfav varchar2(500),
sbirth date,
sqq varchar2(30) --unique
--constraints pk_student_sno primary key(sno)--添加主键约束
--constraints ck_student_sname check(sname is not null)--非空约束
--constraints ck_student_sage check(sage<150 and sage>0)--检查约束
--constraints ck_student_ssex check(ssex='男' or ssex='女')--检查约束
--constraints un_student_sqq unique(sqq)--唯一约束
)
11.7.2
--添加主键约束
alter table student add constraints pk_student_sno primary key(sno);
alter table student drop constraints pk_student_sno;
--添加非空约束
alter table student add constraints ck_student_sname check(sname is not null);
alter table student drop constraints ck_student_sname;
--添加检查约束
alter table student add constraints ck_student_sage check(sage<150 and sage>0)
alter table student drop constraints ck_student_sage;
--添加检查约束校验性别
alter table student add constraints ck_student_ssex check(ssex='男' or ssex='女')
alter table student drop constraints ck_student_ssex;
--添加唯一约束
alter table student add constraints un_student_sqq unique(sqq)
select * from student
11.7.3 添加外键:
create table student(
sno number(10) primary key,
sname varchar2(100) not null,
sage number(3) check(sage>0 and sage<150),
ssex char(4) check(ssex='男' or ssex='女'),
sfav varchar2(500),
sqq varchar2(30) unique,
cid number(10) --references clazz(cno)
--constraints fk_student_cid foreign key(cid) references clazz(cno)--外键
)