七， 表查询 一

表查询
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中