--数据更新:insert、update、delete
--insert数据插入
/*insert into 表名(字段1,···,字段n) values(,···,);
*/
insert into grade values(1,'一班');
insert into student(sid,sname) values(6,6);
/*
to_date();把字符串转换成Date类型进行存储
(‘符合日期格式的字符串’,‘yyyy-mm-dd hh:mi:ss’)
*/
insert into student(sid,sname,sdate)
values(7,7,to_date('2017-01-01 08:08:08','yyyy-mm-dd hh:mi:ss'));
insert into student(sid,sname,sdate)
values(7,7,to_date('2017-01-01 14:08:08','yyyy-mm-dd hh24:mi:ss'));
/*
to_timestamp(符合日期格式的字符串’,‘yyyy-mm-dd hh24:mi:ss.ff3’)
*/
insert into student(sid,sname,sborn)
values(8,8,to_timestamp('2017-01-01 14:08:08.233','yyyy-mm-dd hh24:mi:ss.ff3'));
/*
数据修改
update 表名 set 字段1=新值,字段n=新值 where 条件*/
--修改学生姓名,年龄,入学日期
update student set sname=18,sage=30,
sdate=to_date('2017-01-15 18:08:08','yyyy-mm-dd hh24:mi:ss')where sid=6;
/*
数据删除
应该做备份delete from 表名 where 条件
*/
delete from student where sid=7;
/*
查询所有学生信息
*/select * from student;
select sage from student where sid = 6;
--distinct 去除重复的行
select distinct sname from student;
select * from student where sage>15 and sdate>to_date('2017-02-15 12:00:08','yyyy-mm-dd hh24:mi:ss');
/*
asc:默认值,升序
desc:降序
*/select * from student order by sid desc;
select * from student order by sdate desc;
--利用学生表创建学生备份表
create table student_back as select * from student;select * from student_back;
--使用列别名
select sborn as 出生日期 from student;--如何使用多列的内容在一个单元格中显示
--使用连接符||select sid||sname||sdate from student;
--取值范围:between
--查询年龄在15-20之间的学生select * from student where sage>=15 and sage<=20;
--等价于
select * from student where sage between 15 and 20;
--集合成员测试:in
--查询学号为6或9的学生信息select * from student where sid=6 or sid=9;
--等价于
select * from student where sid in(6,7,8,9);
/*
模糊查询 :like
%:匹配任意长度,任意字符组成的字符串
*/select * from grade where gname like '%班'; --班%
--查询名字中含有‘四’的班级信息
select * from grade where gname like '%四%';
--查询名字中含有%的名字信息lpp
/*
escape:用来定义转义符,转义符通常使用‘\’或者其他字符来表示,
其次还需要将定义的转义符置于被转义符的前面,此时,被转义符被当作普通字符串来执行
*/select * from grade where gname like '%\%%' escape '\';
--空值判断:is null
select * from student where sname is null;/*
oracle 中的函数主要划分为
1、自定义函数
2、内置函数:
a、单行函数(每次针对一行来操作)
b、聚合函数
单行函数可以划分为:
日期函数:to_date('日期格式字符串',‘格式’);
to_timestamp('日期格式字符串',‘格式’);
add_months(日期,月份);给指定的日期添加月份
last_day(日期字段):获取每月最后一天
*/
--插入6的学生信息,在入学日期中获取系统日期并之后两个月
insert into student (sid,sdate) values(7,add_months(sysdate,2));
--查询每月最后一天入学的学生
select last_day(sysdate) from dual; --当前月份的最后一天
select * from student where sdate=last_day(sdate);
--查询每个月第一天入学的学生
select * from student where sdate=(last_day(add_months(sdate,-1))+1);
select * from student where sdate=(last_day(sdate-1)+1);
select * from student where substr(to_char(sdate,'yyyy-mm-dd'),9,2) = '01';
select last_day(add_months(sysdate,-1))+1 from dual; --当前月的第一天
/*
数学函数
abc(字段):求绝对值
round(字段):四舍五入
mod(字段,除数):取模函数
to_number();将符合字符串格式的数字转换为数字类型
*/
--查询年龄取模操作
select mod(sage,5) from student;
insert into student(sid) values(to_number('12'));
/*
字符串函数
lower():转换小写
upper():转大写
substr(字段,1,长度);截取字符串
*/
select lower(sname) from student;
--查询所有学生的“姓”;
select substr(sname,1,1) from student;
/*
聚合函数: 针对一组行返回单个结果
avg(字段):求平均max(字段):
min(字段):
sum(字段):
count(字段):统计字段值不为空的记录数
*/
--查询学生表的平均年龄(对不为空的字段使用)
select avg(sage) from student;
--查询学生表中年龄最大的学生信息
select max(sage) from student;
select * from student where sage = ( --嵌套子查询
select max(sage) from student
);
--给学生表添加成绩字段,然后统计学生表中的成绩总和;
select sum(sal) from student;
--统计学生表中的总记录数(5)
select count(sid) from student;
select count(*) from student;
/*
分组查询:按照属性(字段)将表中的数据划分为不同的数据单元
语法:
select 字段, 聚合函数(字段) from 表名
[where 条件] group by 分组依据 [having 条件]
先按照where筛选再分组筛选,最后用having筛选
语法:select 字段, 聚合函数(字段) from 表名
[where 条件] group by 分组依据 [having 条件]
先按照where筛选再分组筛选,最后用having筛选
*/
--查询员工表中的每个部门的工资总额
select deptno, sum(sal) from emp group by deptno;
--查询员工表中每个部门的工资最高的工资
select deptno,max(sal) from emp group by deptno;
--查询员工表中入职日期大于1981-2-20 每个部门的工资总额
select deptno, sum(sal)
from emp
where hiredate > to_date('1981-2-20','yyyy-mm-dd') group by deptno;
--查询员工表中每个部门的工资总额>9000的工资总额
select sum(sal) from emp group by deptno having sum(sal)>9000;
/*
子查询:嵌入在其他查询语句中的select语句
子查询划分为:嵌套子查询和相关子查询
嵌套子查询(不相关子查询):不依赖于父查询
执行过程:
(1)先执行子查询语句,其结果不会被显示,而是把查询结果集作为外部查询(父查询)的条件使用
(2)父查询在根据子查询的结果进行查询,并显示查询结果集
子查询划分为:嵌套子查询和相关子查询嵌套子查询(不相关子查询):不依赖于父查询
执行过程:
(1)先执行子查询语句,其结果不会被显示,而是把查询结果集作为外部查询(父查询)的条件使用
(2)父查询在根据子查询的结果进行查询,并显示查询结果集
*/
--emp表中,查询与“SCOTT”在同一部门的雇员信息
select * from emp where deptno=(
select deptno from emp where ename='SCOTT');
--查询比“SCOTT”工资高的雇员信息
select * from emp where sal >(select sal from emp where ename='SCOTT');
--查询员工表中每个部门的工资最高的工资的员工信息
select * from emp where (sal,deptno) in(
select max(sal),deptno from emp group by deptno);
s
/*
相关子查询:通常情况下是指子查询的条件中引用了父查询的列值
执行过程:
(1)外部查询取出第一条记录(元组),将元组的列值给了内层查询
(2)内层查询根据该列值进行条件查询,然后得到结果集
(3)外层查询再将取出的第一条记录根据内层查询的结果进行匹配,匹配成功则显示该记录,不成功则不显示数据
(4)外层查询再取出第二条记录重复123步骤直到元组全部被提取完毕
执行过程:(1)外部查询取出第一条记录(元组),将元组的列值给了内层查询
(2)内层查询根据该列值进行条件查询,然后得到结果集
(3)外层查询再将取出的第一条记录根据内层查询的结果进行匹配,匹配成功则显示该记录,不成功则不显示数据
(4)外层查询再取出第二条记录重复123步骤直到元组全部被提取完毕
*/
--EMP表中,找出每个雇员超过他所在部门平均工资的雇员编号、雇员名称、薪水、部门编号
select empno,ename,sal,deptno
from emp e1
where sal > (select avg(sal)
from emp e2
where e1.deptno=e2.deptno );
--查询员工表中每个部门的工资最高的工资
select *
from emp e1
where sal = (
select max(sal)
from emp e2
where e1.deptno=e2.deptno );
--每次取出一条员工信息,查询他的工资是否是部门工资最高,是显示,否则不显示
select empno,ename,sal,deptno from emp e1 where sal >(
select avg(sal) from emp e2 where e1.deptno = e2.deptno
);
--eg:
select * from emp
where deptno in ( select distinct deptno from emp order by deptno desc);
select count(distinct deptno) from emp;
--查询其他部门中比30号部门某一雇员薪水少的雇员信息
select * from emp where sal <(
select max(sal) from emp where deptno=30)
and deptno <> 30;
select * from emp e1 where sal <(
select max(sal) from emp e2 where deptno=30 group by deptno having e1.deptno<>e2.deptno);
/*
在数据中<any 等价于 <max()
*/select * from emp e1 where sal <any(
select max(sal) from emp where deptno=30)
and deptno <> 30;
--查询雇员薪水大于3000的部门名称
select dname from dept where deptno in (
select deptno from emp where sal >3000);
/*
exists(子查询): 用户检测子查询是否查询记录,如果有则返回值TRUE,没有返回false
执行过程:父查询取出第一条记录,然后将部门编号传递给子查询,子查询根据deptno和sal>3000的
条件进行查询,只要查询出至少一条记录,则exist返回TRUE,此时父查询将根据where后的
TRUE和false来显示该记录,为false则不显示,然后父查询再取出下一条记录重复上述过程,直到完、
*/
select dname from dept d where exists(
select * from emp e where sal>3000 and e.deptno = d.deptno);
--查询比员工编号7654工资高,同时与7788从事相同工作的全部员工信息
select * from emp where sal > (select sal from emp where empno=7654)
and job=(select job from emp where empno=7788);
--查询和姓名中含有字母‘s’的员工在相同部门的员工工号和姓名
select * from emp where deptno in (select deptno from emp where ename like '%S%');
--查询至少含有一个员工的部门信息
select * from dept where deptno in(
select deptno from emp where NOT(empno is null)); --条件表达式
select * from dept where deptno in(
select deptno from emp group by deptno having count(deptno)>=1);
/*
子查询语句可放在from后面
*/--找出最高的部门平均工资
select max(avgsal)
from (select deptno, avg(sal) as avgsal from emp group by deptno) ;
select max(avg(sal)) from emp group by deptno; --嵌套
--找出平均工资最高的部门名称
select dname from dept where deptno=
(select deptno from (select deptno, avg(sal) as avgsal from emp group by deptno)
where avgsal = (select max(avgsal)
from (select deptno, avg(sal) as avgsal from emp group by deptno) ));
--select dname from dept d
-- where exists(select max(avg(sal)) from emp e group by deptno having e.deptno=d.deptno);
/*
连接查询:内连接、自然连接、外连接
内连接:等值连接和不等值连接
语法:
select * from R1 inner join S s on r.属性=s.属性 (等值连接的语法)
select * from R1 inner join S s on r.属性<>s.属性 (不等值连接的语法)
select * from R,S where R.属性=S.属性
内连接:等值连接和不等值连接
语法:select * from R1 inner join S s on r.属性=s.属性 (等值连接的语法)
select * from R1 inner join S s on r.属性<>s.属性 (不等值连接的语法)
select * from R,S where R.属性=S.属性
*/
--查询员工姓名及其对应部门信息(内连接与生成笛卡尔积有关,效率不够高)
select ename,dname,e.deptno
from emp e inner join dept d
on e.deptno=d.deptno;
等价于===>
select ename,dname,emp.deptno
from emp,dept
where emp.deptno=dept.deptno;
/*
自然连接:属于特殊的等值连接,要求两张表或多张表具有相同的列名
语法:
select * from R natural join S
语法:select * from R natural join S
*/
select * from emp e natural join dept; --自动去重复
select ename,dname,deptno from emp e natural join dept;
/*
外连接:左(外)连接和右(外)连接以及全(外)连接
语法:
左(外)连接:使用left join 关键字,查询内容为:两张表的数据加上基表的全部数据,在附表中找不到记录显示为空s
右(外)连接:使用right join 关键字,查询内容为:两张表的数据加上基表的全部数据,在附表中找不到记录显示为空
语法:左(外)连接:使用left join 关键字,查询内容为:两张表的数据加上基表的全部数据,在附表中找不到记录显示为空s
右(外)连接:使用right join 关键字,查询内容为:两张表的数据加上基表的全部数据,在附表中找不到记录显示为空
*/
--使用外连接:查询部门信息及其对应的员工信息,同时显示没有员工的部门信息
select d.deptno,d.dname,e.ename from dept d left join emp e on d.deptno = e.deptno;
--使用外连接:查询部门信息及其对应的员工信息,同时显示没有部门的员工信息
select d.deptno,d.dname,e.ename from dept d right join emp e on d.deptno = e.deptno;
--查询部门信息及其对应的员工信息,并且要求员工入职日期在1981-2-1以后
select d.deptno,d.dname,e.empno,e.ename
from dept d inner join emp e on d.deptno = e.deptno
where e.hiredate>to_date('1981-2-1','yyyy-mm=dd');
--等价于
select d.deptno,d.dname,e.empno,e.ename
from dept d,emp e
where d.deptno = e.deptno and e.hiredate>to_date('1981-2-1','yyyy-mm=dd');
--查询员工表并显示第一条记录
select * from emp where rownum = 1;
/*
rownum:伪列,常用来限制结果集的查询
其原理如下:
当使用select从表中检索数据时,每次从表中提取出一条记录后,
oracle自动使用rownum对该条元组添加序列,此时rownum=1,如果
在where语句后使用rownum进行比较,结果为TRUE,则将提取的记录
显示在结果集,然后提取下一条记录,此时,rownum为2,然后再次
比较,结果为假,则不显示到结果集。
其原理如下:当使用select从表中检索数据时,每次从表中提取出一条记录后,
oracle自动使用rownum对该条元组添加序列,此时rownum=1,如果
在where语句后使用rownum进行比较,结果为TRUE,则将提取的记录
显示在结果集,然后提取下一条记录,此时,rownum为2,然后再次
比较,结果为假,则不显示到结果集。
*/
select * from(select rownum as rn, e.* from emp e) where rn >= 2 and rn <=5; --可以做分页
--给出页数,和每页显示的条数,实现oracle的分页语句
select * from (select rownum as rn, e.* from emp e)
where rn>(页数-1)*条数 and rn <= 页数*条数 ; --真分页
--SELECT * FROM think_message LIMIT (页数-1)*条数,每页显示的条数; --MySQL