Oracle小结1

Oracle常用命令

一、打开Oracle数据库方式

a)        Dos 下 sqlplus

快捷方式：Windows徽标键+ R 快速打开运行窗口，键入CMD

打开Dos 然后按照提示输入用户名密码

b)        图形化工具：开始-说有程序-oracle程序-应用程序开发-sqlplus

c)        浏览器方式打开：

     http://127.0.0.1:5560/isqlplus

        http://localhost:5560/isqlpus

     注：oracle11g开始就不支持isqlplus了

二、Oracle客户端

a)        PL/SQL

b)        Toad

三、SQL语句

1、更改用户

    alter user scott account unlock;

       解锁scott用户

 

2、显示数据库表信息

    1.desc 表名

3、select语句

1)        查询条件若在双引号中则区分大小写

若查询条件中含有“号 则可用\” 转译

2)        select 3*3 from dual，dual是系统自带的一张空表，计算数据时可以使用该表

3)        任何含有空值的算术表达式的计算结果是空值

4)        set linesize 200;--用于设定每行显示的宽度

5)        set pagesize 30;--设置显示的页数

 

4、distinct

    1.select distinct a from 表;

    2.select distinct a,b from 表;

       去除查询结果中相同内容

5、where

a)        select * from emp where empno = 10;

b)        select * from emp where empno <> 10;

c)        select * from emp where ename = 'hebe';

d)        select * from emp where sal (not) between 800 and 1500;

e)        select * from emp where comm is (not) null;

f)         select * from emp where ename (not) in ('smith','philip','jay');

g)        select * from emp where ename like '_A%';

h)        select * from emp where ename like '_\%a%';系统默认转义符是\,可以自己指定转义符

select * from emp where ename like '_$%a%' escape '$';

以上说明$为用户自定义的转义符

也可以用两个 ’ 代表’ 的转译

 

6、order by 语句

    1.select * from dept;

    2.select * from dept order by dept desc;(默认为asc)

            注意： desc为降序 asc 为升序

3.select ename, sal, deptno from emp where sal > 2000 order by deptno asc,ename   desc;

 

7、sql函数 

1.select ename from emp where ename not like '_A%' and sal > 1500

    order by sal desc;

    2.select lower(ename) from emp;

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

 4.select substr(ename,2,3) from emp;从第二字符截，一共截三个字符。

    5.select chr(65) from dual;结果为A  将ASCII码转成字符

    6.select ascii('a') from dual;结果为65 的到字符的ASCII码

    7.select round(35.572) from dual;结果为36

    8.select round(35.572,2) from dual;结果为35.57

    9.select round(35.572,-1) from dual;结果为40

9，10 两例 解析：以 . 为起点左为负右为正，得到取近似值的位数 然后进行四舍五入计算

    10.select to_char(sal,'$99,999.9999') from emp;

to_char函数主要用于对日期和数字进行格式化

11.select to_char(sal,'L99,999.9999') from emp;人民币符号，L代表本地符号。

    12.select birthdate from emp;

    显示为：BIRTHDATE

    ---------------------

    22-3月-87

    改为：select to_char(birthdate,'YYYY-MM-DD HH24:MI:SS') from emp;

    13.to_data函数

select ename,birthdate from emp where birthdate > to_date('1987-3-22 11:22:33','YYYY-MM-DD HH24:MI:SS');不能直接写birthdate>'1987-2-22 11:22:33'会出现格式不匹配，因为表中格式为DD-MM月－YY,

    14.select sal from emp where sal > to_number('$12,444.99','$99,999.99');

15.select ename, sal*12+nvl(comm,0) from 这样防止comm为空时，sal*12相加也为空的情况

                               如果你某个字段为空，但是你想让这个字段显示0

nvl(字段名,0)，就是当你选出来的时候，这个字段虽然为空，但是显示的是0，当然这个0也可以换成其他东西，如：1，2

8、Group function 组函数

    1.select max(sal) from emp;

    2.select min(sal) from emp;

    3.select to_char(avg(sal), '$999,999,999.99') from emp;

    4.select round(sum(sal),2) from emp;

    5.select count(*) from emp where sal > 1500;

    6.select count(comm) from emp;

    7.select count(distinct deptno) from emp;

 

9、Group by语句

    1.select avg(sal) from emp group by deptno;

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

    3.select deptno,job,max(sal) from emp group by deptno,job;

 4.求薪水值最高的人的名称select ename,max(sal) from emp;出错，因为max只能有一个值，但是ename的值可能有好几个，不能匹配。

    Group by 语句应注意，出现在select中的字段，如果没有出现在组函数中，必须出现在Group  by语句中。

 

10、Having对分组结果筛选

    1.where是对单条记录进行筛选,Having是对分组结果进行筛选

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

2.查询工资大于2000的雇员,按照部门编号进行分组,分组后平均薪水大于1500,按工资倒序排列 ，且显示平均工资

select deptno,avg(sal) from emp where sal > 2000 group by deptno having avg(sal) >  1500 order by avg(sal) desc;

 

11、子查询

    1.select 语句中嵌套select 语句,求哪些人工资在平均工资之上.

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

    2.查找每个部门挣钱最多的那个人的名字.

 select ename, 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) max_sal,deptno from emp group by         deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);