韩顺平oracle视频笔记三

vftemhwncv

一、准备数据
创建账号huangbiao，并使用这个账号创建表
 
    conn system/admin as system;
    create user huangbiao identified by huangbiao;
 
    grant connect to huangbiao;
 

create table hb_user(
id int primary key,
name varchar2(20),
password varchar2(20),
deptid int
);
insert into hb_user (id,name,password,deptid)values(1,'zhangsan','zs',1000);
insert into hb_user (id,name,password,deptid)values(2,'lisi','ls',2000);
insert into hb_user (id,name,password,deptid)values(3,'wangwu','ww',3000);
insert into hb_user (id,name,password,deptid)values(4,'zhaoliu','zl',4000);
insert into hb_user (id,name,password,deptid)values(5,'tianqi','tq',5000);
insert into hb_user (id,name,password,deptid)values(6,'liuba','lb',6000);
create table dept_info(
dept_id int,
dept_name varchar2(20),
dept_phone int
);
insert into dept_info (dept_id,dept_name,dept_phone) values(1000,'软件开发',110);
insert into dept_info (dept_id,dept_name,dept_phone) values(2000,'软件测试',120);
insert into dept_info (dept_id,dept_name,dept_phone) values(3000,'软件设计',130);
insert into dept_info (dept_id,dept_name,dept_phone) values(4000,'软件管理',140);
insert into dept_info (dept_id,dept_name,dept_phone) values(5000,'软件架构',150);
insert into dept_info (dept_id,dept_name,dept_phone) values(6000,'项目经理',160);
 
查看一个表的结构
desc scott.dept;
 
打开显示操作的开关，即相关操作显示多长时间
set timing on;
 
二、快速准备数据办法之一
 

create tables users(
userid varchar2(20),
username varchar2(20),
password varchar2(20)
);

insert into users(userid,username,password)select * from users; 
 
三、查询例子
1、SQL语句的where区分大小写
SQL> select deptno,job,sal from emp where ename='smith';
SQL> select deptno,job,sal from emp where ename='SMITH';
 
2、但是SQL语句的关键字——列名、表名、关键字都不区分大小写
select sal*13 as "年工资",ename from scott.emp;
注意：上面的重命名使用的是“双引号”
 
3、设置默认值nvl()函数
select sal*13+comm as "年工资",ename from scott.emp;
注意：上面的查询结果可以发现有些人的工资是没有的,因为oracle查询运算发现如果有一个值为空则值的结果就位空
 
select sal*13+nvl(comm,0) as "年工资",ename from scott.emp;
备注：nvl(comm,0),如果comm的值为空就变为0，如果不为空就是comm
 
4、如何根据时间条件查找？查找大于1982年入职的同时信息
select * from emp where emp.hiredate>'1-1月-1982';
 
5、查询工资大于2000小于2500的员工信息
select * from emp where sal between 2000 and 2500;
 
%表示0到多个字符
_表示任意单个字符
 
6、如何显示首字母为S的员工的姓名？
SQL> select * from emp where ename like 'S%';
 
7、如何显示第三个字符为大写O的所有员工的信息？
select * from emp where ename like '__O%';
 
8、尽量使用in这种方式，效率比or快多了
SELECT * FROM EMP WHERE EMPNO IN(7369,223,4,12,32,4,324);
 
9、显示没有“上级”领导的信息
select * from emp where mgr is null;
 
10查询工资大于500或者是职位为经理并且名字是已J开头的
select * from emp where (sal>500 or job='MANAGER')AND ENAME LIKE 'J%';
 
11、排序
select * from emp order by desc;//降序
select * from emp order by asc;//升序，默认的
 
12查询部门号降序，工资降序排列——先排列deptno降序，然后再在同deptno情况下对sal再次降序排列
select * from emp order by deptno desc ,sal desc;
select  ename,(sal*13+nvl(comm,0)) "年薪" from emp order by "年薪" desc;
 
四、查询统计
1、显示员工的最高和最低工资
select max(sal) as "最高工资",min(sal) as "最低工资" from emp;
 
2、显示最高薪水员工的信息
select * from emp where sal in(select max(sal) as "最高工资" from emp);
select * from emp where sal=(select max(sal) as "最高工资" from emp);
 
3、显示所有员工的平均工资和工资总和
SQL> select avg(sal) from emp ;
 
4、查询大于平均工资的员工信息
select * from emp where sal < (select avg(sal) from emp );
 
5、计算共有多少员工
select count(*)from emp;
 
五、分组统计
group by对查询结果分组统计
 
1、显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from emp group by deptno; 
 
2、显示每个部门的每种岗位的平均工资和最低工资
SQL> select deptno,job,avg(sal),max(sal) from emp group by deptno,job;
 
3、显示平均工资低于2000的部门号和他的平均工资
select deptno,avg(sal)from emp group by deptno having avg(sal)>2000;
 
4、having子句用于限制分组显示结果
如果一个查询语句如果同时包含下面三个限制条件，顺序如下
group by --> having --> order by
 
六、 查询语句练习
　　1、自连接查询
　　查询某个员工的上级领导的姓名，比如是FORD
　　select boss.* from emp worker,emp boss where worker.ename='FORD' and worker.mgr=boss.empno;
　　
　　2、嵌套查询
　　显示与SMITH同一部门的所有员工
　　select * from emp where deptno = (select deptno from emp where ename='SMITH');
　　select * from emp where deptno in (select deptno from emp where ename='SMITH');
　　备注：数据库的执行时“从左到右”
　　
　　3、查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
　　SQL> select * from emp where emp.job in  (select job from emp where emp.deptno=10);
　　关于多列嵌套查询
　　
　　4、查询与SMITH部门和工作相同的人员信息；
　　select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
　　
　　6、查询各个部门的平均工资和部门编号
　　select deptno,avg(sal) from emp group by deptno;
　　
　　7、把上面的查询看做是一张子表
　　select a1.*,a2.mysal from emp a1,(select deptno,avg(sal) as mysal from emp group by deptno) a2 where a1.sal > a2.mysal and a1.deptno = a2.deptno;
　　备注：(在这里针对ORACL)在给表取别名的时候不要用as，否则会报错，但是可以给列取别名用as
　　
　　8、希望SCOTT的岗位、工资、补助和SMITH一样
　　update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
　　
　　当from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫作内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名