Oracle总结

Oracle总结

一、  Oracle安装及基本命令

1、   配置服务

OracleOra10g_home1TNSListener，表示监听服务，如果客户端想连接到数据库，此服务必须打开，此服务必须打开。

OracleService数据库名，表示数据库的主服务，此服务必须启动。

2、   进入sqlplusw，主机字符串处输入数据库名

3、   设置行缓冲长度

set linesize 长度;

4、   修改每页显示记录的长度

set pagesize 行数;

5、   ed 文件名

可以以相应的工具编辑sql文件

然后通过@文件名  就可以执行文件中的命令



通过@路径 可以访问磁盘上的文件，如@d:/demo.txt  /可以省略

如果文件的扩展名是.sql，则扩展名可以省略。

6、   更换用户

conn 用户名/密码 [AS SYSDBA| SYSOPER]

如conn scott/manager

如果现在连接的是超级管理员(sys)，则在连接的最后必须写上AS SYSDBA，表示以管理员的身份进行连接。

如： conn sys/change_on_install

                                     conn system/manager

                   访问不同用户下的表，需要加上相应的用户名，如select  * from scott.emp

7、   显示当前连接的是哪个用户

show user;

8、   得到数据库中的所有表

select * from tab;

9、    得到表的详细描述

          desc emp;

10、 

二、  SQL的分类

1、   DML（Data Manipulation Language, 数据查询语言）：用于检索或者修改数据

2、   DDL（Data Definition Language, 数据定义语言）：用于定义数据的结构，如创建、修改或者删除数据库对象。

3、   DCL（Data Control Language, 数据控制语言）：用于定义数据库用户的权限。

三、  简单查询语句

1、  为显示列起别名

select empno 编号, ename 姓名, job 工作 from emp;

2、  DISTINCT的使用

select distinct job from emp;

3、  使用||作连接操作

select ‘编号是：’||empno||’的雇员，姓名是’||enmae||’工作是：’||job from emp;

4、  支持四则运算功能

select ename, sal*12 income from emp;

5、  

四、  限定查询

1、  null（如果字段中不存在内容，则为null）

select * from emp where comm. is not null;（查询含有薪水的雇员儿）

2、  支持逻辑运算符and or

3、  not

select * from emp where not(sal>1500 and comm is not null)

4、  between ……and……（包括两端数据）

select * from emp where sal between 1500 and 3000

                   支持日期的区间取值

                            select * from emp where hiredate between ‘1-1月 -81’ and ’31-12月 -81’

5、  Oracle对字符串的大小写敏感

6、  [not] in(支持所有类型)

select * from emp where empno in (7369, 7499, 7521);

7、  模糊查询

“%”：可以匹配任意长度的内容

“_”：可以匹配一个长度的内容

如：查询出所有孤雁姓名中第二个字母是M的信息

           select * from emp where name like ‘_M%’

           查询出在1981年雇佣的雇员信息

           select * from emp where hiredate like ‘%81%’

           查询工资中包含6的雇员信息

           select * from emp where sal like ‘%6%’

如果使用like时，没有查询关键字，则表示查询全部

8、  支持条件运算符，其中不等号有两种表示形式: <>  !=

9、  distinct

select {distinct} job from emp

10、 order by 子语中，asc 表示升序， desc表示降序

五、  单行函数

1、   字符函数

a)      upper(str) 将小写字母变成大写字母

select upper(‘smith’) from dual

b)      lower(str) 将一个字符串变成小写字母表示

c)      initcap(str) 将单词的第一个字母大写

select initcap(‘HELLOW word’) from dual;

d)      contact(str1, str2) 字符串连接操作

e)      substr(str, startPos, length) 截取字符串，第二个参数为开始位置，第三个位置为长度，当第一个参数为0时，oracle会当做1处理，第三个参数省略时，表示截取到字符串尾部。第二个参数为负时，表示从后往前数的位置。



f)       length(str) 返回字符串长度

g)      replace(str, replacedStr, replaceStr ) 替换字符串，第二个参数为被替换的字符串，第三个参数为替换字符串

select substr(‘hello’, 1, 3), length(‘hello’), replace(‘hello’, ‘l’, ‘x’) from dual

显示所有雇员的姓名及姓名后的三个字母

         select ename, substr(ename, length(ename)-2) from emp 或者

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

2、   数值函数

a)      round(number) 四舍五入，默认截取所有小数，round(number, length),第二个参数为保留的小数点位数，当为负时表示舍去的整数位数。

select round(789.536) from dual     output:790

select round(789.536, -2) from dual     output:800

b)      trunk(number) 截取小数位，用法和round相同，只是不进行四舍五入，而仅是进行舍去操作。

select trunk(789.536, -2)     output:700

c)      mod(number1, number1) 取余，即number1 mod number2

3、   日期函数

a)      日期+（-）日期=日期

日期-日期=数字（天数）

b)      显示当前日期：

select sysdate from dual;

显示雇员进入公司的星期数

         select empno, ename, round(sysdate-hiredate)/7 from emp;

c)      months_between(date1, date2): 求出给定日期范围的月数

select empno, ename, months_between(sysdate, hiredate) from emp;

d)      add_months(date, number) 在指定的日期上加上指定的月数

select add_months(sysdate, 4);

e)      next_day()：下一个日期是哪一天

select next_day(sysdate, ‘星期一’) from dual;

f)       last_date(date): 求出给定日期的每月最后一天的日期

找出每月倒数第三天受雇的所有员工

select * from emp where last_day(hiredate)-2=hiredate

4、   转换函数

a)      to_char()：转换成字符串

select empno, ename, to_char(hiredate, ‘yyyy’) year,

     to_char(hiredate,’mm’) months,

     to_char(hiredate, ‘dd’) day

from emp;

用作日期的转换

         to_char(hiredate, ‘yyyy-mm-dd’)

在日期格式前加上fm，则会去除前导0

         to_char(hiredate, ‘fmyyyy-mm-dd’)

可以用于格式化数字，格式字符串中可以使用$（表示美元）L（Local的缩写，以本地的语言进行金额的显示）

         to_char(sal, ’L99.999’)

b)      to_number(str): 转换成数字

c)      to_date():转换成日期

select to_date(‘2009-02-16’, ‘yyyy-mm-dd’) from dual;

5、   通用函数

a)      nvl(col, value) 如果col的值为null，则表达式的值为value，否则为col

select nvl(comm, 0)

b)      decode(col/expression, value1, result1, value2, result2, … , [default]) 相当于if… else if … else if… else… 语句

select ename 雇员姓名, decode(job, ‘CLERK’, ‘业务员’, ‘SALESMAN’, ‘销售人员’, ‘MANAGER’, ‘经理’ ) 职位 from emp;

6、   

六、  查询语句中应该注意的问题

1、  左右连接

选择一个雇员的姓名和其领导的姓名

select e.ename, m.ename from emp from emp e, emp m where e.empno=m.empno(+)

此时使用做链接，雇员信息将全部显示出来，即使没有领导信息。

+在左边表示右连接，+在右边表示左连接。左连接表示左边数据全部显示，有链接表示右表数据全部显示。

2、  SQL1999语法对SQL的支持

a)      交叉连接（corss join用于产生笛卡尔积）

select * from emp cross join dept;

b)      自然连接（nature join）

select * from emp nature join dept;

c)      using子句：用于指定连接的字段

select * from emp join dept using(deptno) where deptno=30;

d)      on子句，用于指定连接条件

select * from emp e join dept d on(e.deptno=d.deptno) where e.deptno=30;

e)       左（外）连接，右（外）连接

select e.enme, e.ename from emp e RIGHT OUTER JOIN dept d

ON(e.deptno=d.deptno);  即d中的所有行全部显示

    左外连接同理。

七、  组函数和分组统计

1、          组函数

COUNT([DISTINCT|ALL] 列名|*)

MAX([DISTINCT|ALL] 列名|*)

MIN([DISTINCT|ALL] 列名|*)

AVG([DISTINCT|ALL] 列名|*)

SUM([DISTINCT|ALL] 列名|*)

2、          如果程序中出现了分组函数，则有两种使用的情况

a)      程序中存在了group by，并指定了分组条件，这样可以将分组条件一起查询出来。

b)      如果不适用分组，则只能单独使用分组函数

3、          分组函数不允许在where语句中出现，分组条件只能通过having指定

4、          组查询语法

select … where …[ group by …  having … ] [order by … ]

5、          显示非销售人员工作名称及从事同一工作雇员的月工资的总和，并且满足同一工作的月工资合计大于$5000，输出结果按月工资的合计排序

select job, sum(sal) su

from emp

where job<>’SALSMAN’

group by job having sum(sa)>5000

order by su

6、          组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句。

select max(avg(sal)) from emp group by deptno

八、  子查询

1、  子查询在操作中分为以下三类

a)      单列子查询：返回结果是一列的内容

b)      多行子查询：返回多个列，有可能是一条完整的记录

c)      多行子查询：返回多条记录

2、  要求查询出：部门名称、部门的员工数、部门的平均工资，部门最低收入雇员的姓名。

select d.name, ed.ct, ed.av

from dept d,

( select deptno count(empno) ct, avg(sal) av, min(sal) min

from emp

group by depno

) ed, emp e

where d.deptno = emp.deptno and d.deptno=emp.deptno and e.sal=ed.min

3、  在子查询中，存在以下三种操作符号

a)      IN

求出每个部门的最低工资的员工信息

select * from emp where emp.sal in

(select min(sal) from emp group by deptno)

b)      ANY

                          i.          =ANY 与IN操作完全相同

select * from emp

where sal=any(select min(sal) from emp group by deptno)

                         ii.          >ANY 比里面最小的值要大

select * from emp

where sal>any(select min(sal) from emp group by deptno)

                        iii.          <ANY 比里面最大的值要小

select * from emp

where sal>any(select min(sal) from emp group by deptno)

c)      ALL

                          i.          >ALL 比最大的值要大

                         ii.          <ALL比最小的值要小

九、  事务处理

1、  事务处理命令

a)      事务提交：commit

b)      事务回滚：rollback

2、  Oracle中可能发生死锁

一个session如果更新了数据库中的记录，其他session是无法立刻更新的，要等待对方提交之后才允许更新。

十、  数据库的更新操作

1、  复制表

create table 表名称 as （子查询），此语法只在oracle中起作用

如果子查询中的where条件是一个非真条件，则只复制表结构，不复制表数据。

2、  固定格式日期的插入，需要使用日期函数to_date();