黑帽联盟

 找回密码
 会员注册
查看: 1798|回复: 0
打印 上一主题 下一主题

[oracle] oracle查询语句总结(可用于复习)

[复制链接]
yun 黑帽联盟官方人员 

920

主题

37

听众

1364

积分

超级版主

Rank: 8Rank: 8

  • TA的每日心情
    奋斗
    2019-10-18 11:20
  • 签到天数: 678 天

    [LV.9]以坛为家II

    Oracle创建表
    语法:create table 表名(字段名 数据类型,...);
    使用alter table 来修改表当中字段的属性或者增加字段。
    Alter table 表名 add(字段 数据类型)   为表增加字段
    Alter table 表名 modify (字段 修改后的数据类型)  为表修改字段属性
    oracle语法有点不同,而且这里没有change,不能改变字段名称。
    Alter table 表名 drop column 字段   删除表当中相应的字段

    Oracle表的管理
    Crud 即(createretriveupdatedelete)增删改查
    Insert语句     增加数据
    Delete语句    删除数据
    Update语句   修改语句
    Select语句     查询语句
    Insert语句(略)
    Update语句
    语法:update 表名 set 字段=修改的数据 where 条件;
    Delete语句
    语法:delete from 表名 where 条件进行约束;(不加条件就是删除表中所有记录,但不删除结构)
    Drop table 表名;(删除记录和结构)
    Truncate table 表名;(删除记录,结构存在,但数据通过回滚也找不回了)

    Oracle保存点和回滚事务
    前提设置提交方式是手动提交  set autocommit off
    Savepoint:(设置保存点)
    语法:savepoint 保存点名称
    Rollback:(回滚事务)
    语法:rollback to 保存点名称;
    直接使用rollback,回滚所有。即之前的一切操作都没有生效。

    Oracle查询语句
    Oracle里面要求大小写的,仅仅针对的是字段值,对字段名和表名没有要求。
    Mysql没有大小写要求。
    对表中字段值为空时,进行表达式计算,最终结果还是空值。所以需要函数来处理
    Select sal*12+nvl(comm0) 年薪 from emp
    Nvl(comm.,0) 如果commnull,给它赋值0,如果不等于0,按原值计算。

    用户锁定
    要求:如果某个用户不用了,不想让这个用户查看数据,但允许管理员可以。
    Alter user 用户 account lock/unlock

    Oracle查询操作符
    Like关键词(模糊查询)
    要求:查看名字开头是S的人的工资
    Select ename,sal from emp where like ‘S%’;
    %表示0个或者任意多个字符。
    _表示任意单个字符。

    in操作符
    要求:查询empno123345800的员工情况
    Select * from emp where enpno=123 or empno=345 or empno=800
    或者简便点
    Select * from emp where empno in123345800);

    Is null操作符
    要求:查询么有上司的员工,即董事长
    Select ename from emp where mgr is null;

    Order by操作符
    oracle语法一样
    Select * from emp order by deptnohiredate desc
    按照部门编号升序的同时,在对雇员入职时间进行降序排列(有多个排序,即可在后面添加即可)

    Oracle复杂查询
    聚合函数:maxminsumcountavg
    Group byhaving字句
    Group by用于对结果进行分组统计
    Having用于限制结果分组显示
    Having后面条件表达式不能用别名。如:having 平均工资>2000。这种事错误的。
    Group byhavingorder by同时出现的话,顺序依次是group byhavingorder by

    多表查询
    在此处就涉及到了外键这个概念,通过外键让多表进行关联。
    Select enamesalgrade from emp,salgrade where sal between losal and hisalorder by grade
    含义:查询员工的工资,以及工资所在的级别。
    注:order by 后面的字段如果是一个表当中的外键的话,在这里就要明确标出是哪个表的字段,如deptemp表里都有deptno,所在在这里应该这样写:order by dept . deptno

    自连接
    定义:自己连自己,一张表被当作两张表以及多张表来用。
    Select worker.enameboss.ename from emp workeremp boss where worker.mgr=boss.deptno
    查询所有员工姓名以及他们对应的老板姓名,此结果不是最完美的。应该最高级别的员工没有老板,也就没有显示出来。
    解决办法:使用外连接
    Select worker.enameboss.ename from emp workeremp boss where worker.mgr=boss.deptno(+);

    子查询
    定义:嵌套在其他sql语句中的select字句。
    分为:单行子查询和多行子查询
    单行子查询:子查询语句值返回一行记录的语句
    多行子查询:子查询语句返回多行记录的语句

    多行子查询:
    使用in操作符
    查询与10号部门当中职位相同的雇员名称、职位、部门编号
    Select enamejobdeptno from emp where job inselect distinct job from emp);

    使用all操作符
    查询大于30号部门所有员工工资的姓名、工资、岗位、部门编号。
    Select enamejobsaldeptno from emp where sal>allselect sal from emp wheredeptno=30);
    第二种方法:
    Select enamejobsaldeptno from emp where sal>select maxsal from emp where deptno=30);

    使用any操作符
    查询大于30号部门任意一个员工工资的姓名、工资、岗位、部门编号。
    Select enamejobsaldeptno from emp where sal>anyselect sal from emp wheredeptno=30);
    第二种:
    Select enamejobsaldeptno from emp where sal>select minsal from emp where deptno=30);

    多列子查询
    定义:针对返回结果是多列的数据
    查询与姓名是SMITH员工的部门编号和岗位完全的相同的员工的姓名
    Select ename from emp where deptnojob inselect deptnojob from emp where
    ename=’SMITH’);

    from字句中使用子查询(子查询语句相当于一个临时表表)
    查询高于自己部门平均工资的员工信息
    Select t1.*t2.myavg from emp t1,(select deptnoavg(sal) myavg from emp group by deptnot2 where t1.deptno=t2.deptno andt1.sal>t2.myavg

    分页查询
    Oracle表中都有这么一个默认字段rownum
    Select *rownum from emp
    案例:查询emp表中第四条到第六条记录
    Select * from Select t1.*rownum rn from emp t1where rownum<=6t2 where rn>=4
    也可以这样写
    Select * from Select t1.*rownum rn from emp t1t2 where rn between 4 and 6;但效率太低,

    快速创建表
    Create table 表名 as select 字段,字段,… from 存在的表;
    快速复制(插入内容)
    Insert into mytest select * from mytest;多执行几次(主要用于测试分页查询)


    联合查询
    Union操作符(去除重复行)
    Union all操作符(不去除重复行)
    Intersect 操作符(交集)
    Minus 操作符(差集)
    不经常用



    表连接
    分为内连接和外连接

    内连接
    定义:利用where字句对两张表进行笛卡尔集进行筛选。
    语法:select 字段,… from 表名1 inner join 表名2 on 条件表达式;
    案例:显示员工的姓名以及所在的部门名称
    Select ename,dname from emp,dept whereemp.deptno=dept.deptno;
    相当于
    Select ename,dname from emp (inner) joindept on emp.deptno=dept.deptno;


    外连接
    分为左外连接,右外连接,完全外连接

    左外连接:如果左侧的表完全显示我们就说是左外连接
    右外连接:如果右侧的表完全显示我们就说是右外连接
    完全外连接:完全显示两个表,没有匹配的记录置为空

    左外连接和右外连接可以相互转换

    Insert into stu values(1,’jack’);
    Insert into stu values(2,’ton’);
    Insert into stu values(3,’kity’);
    Insert into stu values(4,’nono’);

    Insert into exam values(1,88);
    Insert into exam values(2,99);
    Insert into exam values(11,66);


    案例:查询部门里所有员工的信息,以及部门没有员工的也显示出来
    select dname,emp.* from emp right join depton emp.deptno=dept.deptno;
    select dname,emp.* from emp right join depton emp.deptno(+)=dept.deptno;
    select dname,emp.* from dept left join empon emp.deptno(+)=dept.deptno;
    select dname,emp.* from dept left join empon emp.deptno=dept.deptno;


    帖子永久地址: 

    黑帽联盟 - 论坛版权1、本主题所有言论和图片纯属会员个人意见,与本论坛立场无关
    2、本站所有主题由该帖子作者发表,该帖子作者与黑帽联盟享有帖子相关版权
    3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和黑帽联盟的同意
    4、帖子作者须承担一切因本文发表而直接或间接导致的民事或刑事法律责任
    5、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责
    6、如本帖侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意
    7、黑帽联盟管理员和版主有权不事先通知发贴者而删除本文

    您需要登录后才可以回帖 登录 | 会员注册

    发布主题 !fastreply! 收藏帖子 返回列表 搜索
    回顶部