sqlplus 有三种

1. 命令行的
   

2. 浏览器http://127.0.0.1:5560/isqlplus

3. 图形界面管理工具

sqlplus sys/bjsxt as sysdba

alter user scott account unlock(把用户解锁开)

sql语言 两个标准sql1992和sql1999

sql语言是第四代语言。

第一代语言机器语言、第二代语言汇编语言、

第三代语言c/c++/java、第四代语言是sql语言（只用告诉干什么，没有条件、分支、循环语句）

sql语言有四类

1. 查询语句 （select语句）
   

2. dml语句 数据操作语句
   

3. ddl语句 数据定义语句

4. 数据库语句

四种语句中最重要的就是select语句

从表里面把数据选出来

desc emp(描述一下emp表)

desc dual 这个表可以用来计算纯数学表达式

select 2*3 from dual;

select sysdata from dual;

select ename, sal*12 * comm from emp;(任何含有空值的表达式，结果都为空值)

字符串连接符

select ename||sal from emp;

select ename || 'abcddfs' from emp;

select ename || 'sdfsaf''gsdgsg' from emp(如果有单引号，就用两个单引号代替一个单引号)

select distinct deptno from emp;(把空的重复的都排除掉了)

select distinct deptno, job from emp;（两个字段都相同则排除）

select ename, sal from emp where sal between 800 and 1500;

select ename, sal from where sal >=800 and sal <=1500;同上

对于空值的处理

select ename, sal, comm from emp where comm is null;

select ename, sal, comm from emp where sal in (800,1500,2000);

日期的处理

select ename, sql, hiredate from emp where hiredate > ‘20-2月-81’

模糊查询

select ename from emp where ename like ''

通配符， .代表一个字符，*代表零个或者多个，？代表零个或者一个，+代表1个或者多个

select ename from emp where ename like '_A%';(第二个字母为A,_代表一个字母)

名字里面含有%号的怎么取

select ename from emp where ename like '%\%%';

select ename from emp where ename like "%$%%" escape '$';

数据排序

select ename, sal, deptno from emp order by deptno asc, ename desc;

先按deptno排序，相同的再按ename排序

select ename, sal*12 annual_sal from emp where ename not like '_A%' and sal > 800 order by sal desc;

常用的sql函数，就相当于java里面的方法

    分为单行函数、和多行行数

select lower(ename) from emp;(把ename都变成小写)

select ename from emp where lower(ename) like '_a%';

select ename from emp where ename like '_a%' or ename like '_A%';

输入是一行所以是单行函数

select substr(ename, 2, 3) from emp;

select chr(65) from dual;(把数字转换为Ascii码)

select ascii('A') from dual;

select round(23.652) from dual;

select round(23.652, 2) from dual;

select to_char(sal,'$99,999.999') from emp;

select to_char(sal,'L99,999.999') from emp;

select to_char(sal,'L0000.0000') from emp;

select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;

select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');

select sal from emp where sal > to_number('$1,250.00', '$9,999.99');

专门处理空值的函数

select ename, sal*12+nvl(comm,0) from emp;

多行函数(组函数，它会把好多好多条记录综合在一起作为输入，最后给出一个输出)

最高的薪水值是多少，平均的薪水值是多少，最低的薪水值是多少

select to_char(avg(sal), '999999.99') from emp;

select round(avg(sal), '999999.99') from emp;

组函数有5个min、max、sum、avg、count

select count(distinct deptno) from emp;一共多少不重复的部门编号

group by语句（求每个部门的平均薪水）

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

求薪水值最高的人的名字

select ename from emp where sal=(select max(sal) from emp);

求每个部门薪水最高的人的名字

出现在select列表中的字段，如果没有出现在组函数里面，必须出现在group by 语句里面

子查询

select ename, sal from emp where sal=(select max(sal) from emp);

select语句可以出现在from语句也可以出现在where语句.

求出来那些人的工资在所有人的平均工资之上

select ename from emp where sal>(select avg(sal) from emp);

求出来按照部门分组之后，每个部门挣钱最多的人的他的名字、他的部门

select ename, sal, deptno from emp where sal in(select max(sal) from emp group by deptno);这种写法不正确

select max(sal), deptno from emp group by deptno;(这个查询结果相当于一个表)

select ename, sal from emp join (select max(sal), deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno = t.deptno);

理解子查询的关键是，把它当作一张表，然后再做连接。

运用select语句求出自己的名字还有他的经理人的名字

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr=e2.empno;(自连接，为同一张表取两个别名)

sql-1992标准把表连接条件直接写到where语句里面

表的连接条件和数据过滤条件放在一块（不太好读和理解）

select ename, dname, grade from emp e, dept d, salgrade s where e.deptno = d.deptno and e.sal betwwen s.losal and s.hisal and job <> 'CLERK'

sql-1999解决了这个问题

select ename, dname from emp e, demt d;(这时笛卡尔乘积)

select ename, dname from emp cross join dept;(同上)交叉连接

1992：

select ename, dname from emp, dept where emp.deptno = dept.deptno;

1999:

select ename, dname from emp join dept on (emp.deptno = dept.deptno);

select ename, dname from emp join dept using(deptno)(同上)

select ename, grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

select ename, dname, grade from emp e join dept d on(e.deptno = d.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';

select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);

left join 和left outer join(是一个意思)

select ename, dname from emp e right join dept d on(e.deptno = d.deptno);

1992的缺陷是不支持全外连接

select ename, dname from emp e full join dept d on (e.deptno = d.deptno);

求部门中哪些人的薪水最高

select ename, sal from emp join(select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal adn emp.deptno = t.deptno);

求每一个部门平均薪水，这个平均薪水的薪水等级。（练习表连接）

select
deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp
group by deptno) t join salgrade s on (t.avg_sal between s.losal and
s.hisal);

求部门平均的薪水等级

select deptno, arg(grade) from (select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t gruop by deptno;

雇员中有哪些人是经理

select ename from emp where empno in (select distinct mgr from emp);

不准用组函数，求薪水的最高值（面试题）

select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal)); 

求平均薪水最高的部门的部门编号

(1.先求出各部门的平均薪水，2.求出平均薪水的最大值，3找到等于这个最大值的部门编号和平均薪水)

select deptno, avg_sal

from

(select avg(sal) avg_sal, deptno from emp group by deptno)

where

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

select deptno, avg_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = (select max(avg(sal)) from emp group by deptno)

求平均薪水最高的部门的部门名称

select dname from dept where deptno =

(

select deptno

from

(select avg(sal) avg_sal, deptno from emp group by deptno)

where

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

)

求平均薪水的等级最低的部门的部门名称

（首先第一步求什么，把结果当作一张表。第二步求什么。。一层一层，从里到外）

注：组函数只能嵌套两层

1. 先求出平均薪水

   select avg(sal) from emp group by deptno;

2. 求平均薪水的等级

   select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal betwwen s.losal and s.hisal)

3. 求平均薪水的等级的最低

   select min(grade) from

   (select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from
   emp group by deptno) t join salgrade s on (t.avg_sal betwwen s.losal
   and s.hisal))

4. 最后结果

   select dname,t1.deptno, grade, avg_sal from

   (select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from
   emp group by deptno) t join salgrade s on (t.avg_sal betwwen s.losal
   and s.hisal)) join dept no t1.deptno = dept.deptno

   where

   t1.grade = (select min(grade) from (select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from
   emp group by deptno) t join salgrade s on (t.avg_sal betwwen s.losal
   and s.hisal)))

   视图就是一张表，就是一个子查询(是虚表)

   create view v$_dept_avg_sal_info as

   select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from
   emp group by deptno) t join salgrade s on (t.avg_sal betwwen s.losal
   and s.hisal)

    conn sys/bjsxt as sysdba;

    grant create table, create view to scott;

    创建视图后就可以简化了

    select dname, t1.deptno, grade, avg_sal from     v$_dept_avg_sal_info t1 join dept on (t1.deptno =     dept.deptno)    where t1.grade =

    (select min(grade) from v$_dept_avg_sal_info);

求部门经理人中平均薪水最低的部门名称

求比普通员工的最高薪水还要高的经理人名称

select ename from emp

where

empno in (select distinct mgr from emp where mgr is not null)

and

sa >

(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));

面试题:比较效率

select * from emp where deptno = 10 and ename like '%A%';

select * from emp where like '%A%' and deptno = 10;

上面的高，短路，而且比较数字比比较字母效率高

理论是这样，这还得看数据库优化

转载请注明：学时网 » oracle学习总结