hooyantsing's Blog

第41次课程

字数统计: 1.5k阅读时长: 7 min
2019/07/19

源辰74班

第41次课程

07.19.2019

**内容    **

Oracle_基础查询和函数[蒋斌]

–复习日期函数

select * from emp;

–修改日期 to_date 创建新的日期

update emp set hiredate=to_date(‘2017/02/27’,’yyyy/mm/dd’) where empno = 7369;

–显示员工的入职年份和月份 to_char 以字符串的形式转化格式

select ename,to_char(hiredate,‘yyyy@mm@dd’) where emp;

–查找出入职10年的员工 add_months(hiredate,12*10) 从入职的时间添加120个月,向后推了十年

select * from emp where add_months(hiredate,12*10)<sysdate;

–求每个员工入职的天数 trunc

select ename,trunc(sysdate-hiredate) from emp;

create table classInfo(

       cid int primary key,

       cname varchar2(50)

);

create table student(

sid int primary key,

       sname varchar2(50),

       age int

constraint CK_student_age check(age between 16 and 28),

       sex varchar2(2) default ‘男’

constraint CK_student_sex check(sex=’男’ or sex=’女’),

       cid int

constraint FK_cid references classInfo(cid),

       addr varchar2(100)

);

create sequence seq_cid start with 1001 increment by 1;

create sequence seq_sid start with 1001 increment by 1;

insert into classInfo values(seq_cid.nextval,’YC001班’);

insert into classInfo values(seq_cid.nextval,’YC002班’);

insert into classInfo values(seq_cid.nextval,’YC003班’);

insert into classInfo values(seq_cid.nextval,’YC004班’);

insert into student(sid,sname,age,sex,cid,addr) values(seq_sid.nextval,’小倩’,20,default,1003,’衡阳’);

insert into student(sid,sname,age,sex,cid,addr) values(seq_sid.nextval,’小红’,19,default,1004,’湖北省武汉’);

insert into student(sid,sname,age,sex,cid,addr) values(seq_sid.nextval,’小强’,19,default,1003,’湖南省长沙’);

insert into student(sid,sname,age,sex,cid,addr) values(seq_sid.nextval,’小白’,21,default,1003,’黑龙江省哈尔滨’);

insert into student(sid,sname,age,sex,cid,addr) values(seq_sid.nextval,’唐京’,22,default,1003,’内蒙古省’);

insert into student(sid,sname,age,sex,cid,addr) values(seq_sid.nextval,’张开花’,28,default,1004,’青海省’);

insert into student(sid,sname,age,sex,cid,addr) values(seq_sid.nextval,’宁采臣’,20,default,1001,’’);

select * from classInfo for update;

select * from student;

commit;

delete from student where sid=1007;

–查询开始emp表

select empno,sal+500 from emp;

–查询学生信息 查询一年后学生的年龄小于21

select * from student where age+1<21;

–查询学生为1002的学生

select * from student where cid=1002;

–查询不是1002的学生

select * from student where cid!=1002;

select * from student where cid<>1002;

–查询年龄在20-22之间的学生 包括20和22

select * from student where age between 20 and 22;

–查询1001班和1003班学生

select * from student where cid in(1001,1003);

–查询不是1001班和1003班学生

select * from student where cid!=1001 and cid!=1003;

select * from student where cid not in(1001,1003);

–查询姓张的学生

select * from student where sname like ‘张%’;

–查询张某的学生 两个字的名字

select * from student where sname like ‘唐_’;

–查询地址为空的学生

select * from student where addr is null;

–逻辑操作 and or not

–查询湖南省长沙和湖北省武汉

select * from student where addr=’湖南省长沙’ or addr=’湖北省武汉’;

select * from student where addr in(‘湖南省长沙’,’湖北省武汉’);

–查询所有家庭地址不为空的学生

select * from student where addr is not null;

–连续操作符 将多条sql语句连成一条语句执行

–union 两个表同时显示,并去除重复数据

select sid,sname from student

union

select empno,ename from emp;

–union all 两个表同时显示,不去重

select sid,sname from student

union all

select empno,ename from emp;

–intersect 获取两个表相同数据

select sid,sname from student

intersect

select empno,ename from emp;

–多行函数聚合函数:对多行的数据进行函数运算

–查出员工最高,最低,平均,总和 的底薪

select max(sal),min(sal),avg(sal),sum(sal) from emp;

–按照部门统计员工的最高,最低,平均 总和 底薪

select deptno,max(sal),min(sal),avg(sal),sum(sal) from emp group by deptno;

select * from dept;

select * from emp;

–统计学生来自那些地区 去重函数 distinct 单表去重

select distinct addr from student;

–统计有多少学生

select count(*) from student;

select count(sid) from student;

–统计学生来自多少个地区

select count(distinct addr) from student;

–其他函数

–地址为空的学生,则显示“地址不详” nvl(addr,’地址不详’)

select sname,nvl(addr,’地址不详’) from student;

–查询学生的性别 如果是男 M 如果是女 F    类似于:sex=’男’?’M’:’F’;  decode(sex,’男’,’M’,’F’)

–写法一

select sname,decode(sex,’男’,’M’,’F’) 性别 from student;

–写法二 不推荐 when then end

select sname,case sex when ‘男’ then ‘M’ when ‘女’ then ‘F’ end 性别 from student;

–分析函数:排序

–根据学生的学号排序 order by 字段[desc/asc]

–默认是升序

select * from student order by sid;

–asc升序

select * from student order by sid asc;

–desc降序

select * from student order by sid desc;

–row_number() 排序 over()开窗函数

select empno,ename,sal,row_number() over(order by sal desc) from emp; –1,2,3,4,5

–rank

select empno,ename,sal,rank() over(order by sal desc) from emp;  –1,2,3,3,5

–dense_rank

select empno,ename,sal,dense_rank() over(order by sal desc) from emp; –1,2,3,3,4

–需求:查询员工表工资前三名的员工,练习

select empno,ename,sal,dense_rank() over(order by sal desc) from emp  ;

–分页查询

–rownum 伪列:是一序列,他是隐藏的一列,这个序列在查询的时候生成的

select ename,sal,rownum from emp;

–通过rownum查出第一条数据

select ename,sal,rownum from emp where rownum = 1;

–通过rownum查出前几条数据

select ename,sal,rownum from emp where rownum < 3;

–分页查询 从开始至 到 结束值 这个区间的数据查出来 5 - 10

select no,empno,ename from(select rownum no,empno,ename from emp where rownum<=10 )where no>=5;

–效率高的分页

select * from emp;

–第一次筛选

select a.* from(select * from emp) a where rownum <=10;

–第二次筛选

select * from (select a.*,rownum rn from(select * from emp) a where rownum <=10)where rn>=5;

–笛卡尔乘积查询

select * from classInfo,student;

–笛卡尔乘积查询 去重

select * from classInfo c,student s where c.cid=s.cid;

–内联查询 基于两张表 inner join 表 on 表达式

select * from student s inner join classInfo c on c.cid = s.cid;

–左连接 以左表为基准 左表的数据是要全部查出来的,如果右表没有值也会被当作空值查出来

select * from student s left join classInfo c on c.cid=s.cid;

–右连接 以右表为基准 右表的数据是要全部查出来的,如果左表没有值也会被当作空值查出来

select * from student s right join classInfo c on c.cid=s.cid;

CATALOG