四、Oracle表查询

最新推荐文章于 2025-09-08 20:55:49 发布

weixin_34228662

最新推荐文章于 2025-09-08 20:55:49 发布

阅读量91

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1757476/blog/493240

2019独角兽企业重金招聘Python工程师标准>>>

基本查询
1. 查看表结构 SQL> desc tableName;
2. 查询所有列 SQL> select * from tableName; 6.5W条记录，执行查询时间 1199s
3. 查询指定列 SQL> select columnName, columnName, columnName from tableName; 执行时间
  上面两种查询的不同之处：oracle在执行select * 语句的时候会将 * 转换成表名.字段名，而后者就不必了，所以后者效率会高一些。
4. 取消查询结果的重复行： SQL> SQL> select distinct columnName, columnName from tableName;
  注：oracle对查询条件的大小写是区分的，比如：select * deptno, job from emp where ename = 'smith';
5. 使用算数表达式和列的别名：select ename "姓名", sal * 12 as "年收入" from emp;
  列的别名：可以直接在列后面接别名，或者加 as "别名"
6. 处理null值： nvl(sal, 0) 如果sal 值是null，则用0代替
7. 连接字符串：SQL> select columnName || 'xxxx' || columnName from tableName;
8. 使用where子句： SQL> select columnName, columnName from tableName where xxx = xxx and *** = ***;
  在where 条件中使用 in：SQL> select * from tableName where columnName in (xxx, xxx, xxx);
9. 使用 like 操作符：% 表示0到多个字符， _ 表示任意单个字符。
10. is null 操作符：SQL> select * from tableName where columnName is null;
11. order by 子句：SQL> select * from emp order by deptno desc, sal desc;; 降序DESC、默认升序 ASC
12. 使用列的别名排序：
  SQL> select ename, (sal+nvl(comm, 0))*12 as "YearSal" from emp order by "YearSal" desc;
复杂查询：
1. 分组函数 max, min, avg, sum, count
  分组函数和非分组函数不能同时作为查询的列：SQL> select ename, max(sal), min(sal) from emp; 错误的，但是可以出现在group by子句的查询列中使用：SQL> select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
  SQL> select ename, sal from emp where sal = (select max(sal) from emp);
2. group by 和 having 子句： group by 用于对查询结果的分组统计，having子句用于限制分组显示结果。
  对数据分组的总结：
  # 分组函数只能出现在选择列、having、order by子句中
  # 如果在select语句中同时包含有group by，having， order by，它们的顺序是group by，having，order by
  # 在选择列种如果有列名、表达式、分组函数，那么这些列和表达式必须出现在group by子句中
  select deptno, job, avg(sal), max(sal) from emp group by job having avg(sal) > 2000
  ORA-00979: 不是group by表达式
多表查询：给予两个和两个以上的表或是视图的查询
1. 笛卡儿积
  如果两张表联合查询，匹配原则表A的每一条数据去匹配表B的每条数据形成新的结果记录。所以如果不加限制条件，那么两张表查询的结果记录条数= 表A记录条数 x 表B的记录条数。以此类推。。
2. 自连接：在同一张表的连接查询
  如何显示员工FORD的上级领导姓名：（同一张表可以当作两个表来用）
  SQL> select boss.empno, boss.ename from emp worker, emp boss where worker.mgr = boss.empno and worker.ename = 'FORD';
子查询：指潜入在其他sql语句中的select语句，也叫嵌套查询。
1. 单行子查询：只返回一行数据的子查询
  查询与SMITH同一部门的所有员工：SQL> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
2. 多行子查询：返回多行（单列）数据的子查询，操作符： in、all、any
  查询和部门10的工作相同的雇员的名字、岗位、工资、部门号：SQL> select ename, job, sal, deptno from emp where job in (select job from emp where deptno = 10);
  查询工资比30部门的所有员工工资都高的员工的姓名、工资和部门号：
  SQL> select ename, sal, deptno from emp where sal > all (select sal from emp where deptno = 30);
  SQL> select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30);
3. 多列子查询：返回多列的子查询，
  查询与SMITH的部门和岗位完全相同的员工：SQL> select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');
Oracle的分页查询：三种分页查询
三种查询从效率上来说，rowid > rownum > row_number() over 函数；以7W条记录的表来说，rowid 0.03s，rownum 0.1s，row_number() over 1.01s
1. 按rowid来分
  步骤一、查询出rowid 作为内嵌视图，可以按照表内的某个字段排序
  SQL> select rowid rid, sal from emp order by sal;
  步骤二、利用上面的内嵌视图查询出rownum和rid，并用rownum选出N条记录，将结果作为内嵌视图
  SQL> select rownum rn, rid from (select rowid rid, sal from emp order by sal) where rownum <= 10;
  步骤三、再利用上面的内嵌视图查询rowid，通过rownum筛选出从第M条开始的记录，再作为内嵌视图
  select rid from (select rownum rn, rid from (select rowid rid, sal from emp order by sal) where rownum <= 10) where rn >= 5;
  步骤四、根据上面符合条件的rowid，查询出符合条件的记录的列
  SQL> select * from emp where rowid in (select rid from (select rownum rn, rid from (select rowid rid, sal from emp order by sal) where rownum <= 10) where rn >= 5);
2. 按rownum来分
  步骤一、查询出需要的记录（可以按字段排序），作为内嵌视图