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