七, 表查询 一

发布于 2018-03-21  0 次阅读



表查询
Oracle安装后都是附带有一些基本表的(scott.emp),对于初学者可以利用这些表进行学习

查询emp表所有记录,以下都是登录scott用户进行操作

SQL> show user;
USER is "SCOTT"

SQL>Select * from emp       

把查询结果写入表中

create table emp1 as select * from emp where 1=2;
insert into emp1(empno) values((select empno from emp where ename = 'SMITH' ));  

说明:当where条件不成立,仅复制表结构不复制数据。

统计

select count (*) from emp;  

取消重复行distinct

select distinct deptno, job from emp;       

使用别名

select ename "姓名",empno "编号" from student;   

查看当前的所有数据库(DBA用户查询)

select * from v$database;
select name from v$database;

查看数据库结构字段

desc v$databases; 

查看哪些用户拥有SYSDBA、SYSOPER权限:

select * from V_$PWFILE_USERS;

查看当前数据库连接用户

Show user;

进入test数据库

database test;

查看所有的数据库实例

select * from v$instance;

查看当前库的所有数据表

select TABLE_NAME from all_tables;
select * from all_tables;
select table_name from all_tables where table_name like ‘u’;

查看表结构

desc all_tables;

nvl函数
格式为:nvl(string1, replace_with)   
功能:如果string1为null,则nvl函数返回replace_with的值,否则返回string1的值。  
注意事项:string1和replace_with必须为同一数据类型,除非显示的使用to_char函数。  

select sal/2+nvl(id,0)/2 "mp",ename,sal from emp;

%:表示0到多个字符
_:表示任意单个字符

select ename,sal from emp where ename like 's%'; 
查询以s结尾的
select ename,sal from emp where ename like '__o%';      
查询第三个字符为大写O的

IN, ALL, ANY, NOT NULL用法

select * from emp where empno  in(7844, 7839, 123, 456); 
查询条件在()之中
select classid,name,match from student where match > all(select max(match) from student where classid = 2);
条件全部
select classid,name,match from student where match > any(select avg(match) from student where classid = 2);
任意
select * from emp where mgr is null;                    
值为空

聚合函数用法:max,min,avg,sum,count

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

**注意:**select ename, max(sal) from emp;这语句执行的时候会报错,说ora-00937:非单组分组函数。因为max是分组函数,而ename不是分组函数…….
但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数,就是说:如果列里面有一个分组函数,其它的都必须是分组函数,否则就出错。这是语法规定的

order by
默认为asc倒序

select * from emp order by sal; 
select * from emp order by deptno, sal desc; 

group by 和 having
用于对查询的结果分组统计
having 子句用于限制分组显示结果。

显示每个部门的每种岗位的平均工资和最低工资

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

注意:如果要分组查询的话,分组的字段deptno一定要出现在查询的列表里面,否则会报错。因为分组的字段都不出现的话,就没办法分组了

显示平均工资低于2000的部门号和它的平均工资

select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)< 2000;

注意:
1 分组函数只能出现在选择列表、having、order by子句中不能出现在where中
2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by
3 在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则就会出错。
如select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal) < 2000;这里deptno就一定要出现在group by中


富强 民主 文明 和谐 自由 平等 公正 法治 爱国 敬业 诚信 友善