oracle笔记

 

 1.       连接方式：

1)    窗口环境

Oracle-OraDb_10g_home1 è 应用程序开发 è SQL Plus

2)    命令行

sqlplus [ 用户名[/密码 ] ][@网络服务名  ] [ as sysdba | as sysoper ]hh

普通用户登录本机数据库服务器： sqlplus 用户名/密码

 2.       操作sql脚本文件

v        l[ ist ] 列出缓冲区的内容

v        r[un]/执行缓冲区的内容

v        cl[ ear ]  buff[ er ] 清除缓冲区的所有内容

v               conn[ ect ]  切换用户

v        disco[ nnect ] 断开连接

v        exit/quit  退出

v        possw[ ord ] 修改密码

v        sav[e] 文件名 [ replace | append ]  sava d:\q.sql (将当前sql缓冲区的内容保存到sql 脚本文件)，使用相对路径时，默认保存在c:\Decuments and Setting\Administrator 下

v        get文件名 get d:\a.sql

v        sta[ rt ] 或 @文件名  @d:\a.sql

v        ed[ it ]  [ 文件名 ]   ed d:\a.sql

v        spo[ ol ] 文件名 (将sql*Plus中的输出结果复制到指定文件中)

v        spo[ ol ] off  ( 停止sql*Plus中的输出结果复制，并关闭文件)

v        help index  列出所有SQL*Plus命令

v        help 命名名字 查看某个命令的详细帮助信息

 

 3.       sql  

select * fromtab;(查看当前用户的所有的表)

desc tablename; 查看表结构

                       i.             oracle常用数据类型：char , varchar2 , number ,date , timestamp , clob , blob

伪列：rowid （每条记录的唯一标识，数据的物理地址）

Rownum： 行号，查到结果后加上去的一个列

                     ii.             运算符：

1.        算术运算符： +  -   *   /

2.        连接运算符： ||

3.        比较运算符：

a)        =  != ( <> )  > <   <=   >= 

b)       any( 值1，值2，值3…… )   与列表中的任意一个值进行比较

c)        all (值1，值2，值3…… )   与列表中的所有值进行比较

4.        逻辑运算符： and or not

Sql 运算符的优先级è 高————底

算术è连接è比较è逻辑

                   iii.             操作符

1.        in( 值1，值2，值3…… ) 匹配值列表

2.        like ‘模式字符串’：匹配指定的字符串模式（_  一个字符， % 人一个字符）

3.        between 值1 and  值2：

4.        is null ： 匹配空值

5.        IS　NAN：

 

 4.       单行函数：

                       i.             字符函数

 

                     ii.             数字函数

                   iii.             日期函数

sysdate 返回数据库所在操作系统的当前日期时间。

    systimestamp 返回数据库所在操作系统的当前时间戳。

    last_day(d) 返回指定日期当月的最后一天的日期时间

     trunc(d[,unit]) 截断日期时间，unit: 'year', 'month', 'day'

                    iv.             转换函数

                      v.             正则表达式函数

 

 

高级查询

 5.       聚合函数

                       i.             count( x )  返回包含x的行数

                     ii.             sum( x )  x的和

                   iii.             avg( x )  平均数

                    iv.             max( x )  最大值

                      v.             min( x ) 最小值

组函数可以忽略控制，count（*）:使用会降低效率，尽量不适用/ count( rowid )

 6.       分组统计

select [distinct ] * | [ 列名 别名，……]

                     from表名

                     [where  条件]

                     [group by 分组条件  [ having 组过滤条件 ] ]

                     [order by 列名 [sac] |[desc] ,……]；  

       注意事项：如果查询中包含一个聚合函数，则其他列不再聚合函数中，则必须出现在group by 自己中，且聚合函数不能出现在 where 自己中。

 

 7.       连接

                       i.             内连接

select[distinct] * | [列名，别名，…… ]

from 主表名 别名

[ inner ] join 从表名 别名 on 连接条件

[ where 条件 ];

 

                     ii.             外连接

select[distinct] * | [列名，别名，…… ]

from 主表名 别名

[ left | right |full ] [ outer ] join 从表名 别名 on 连接条件

[ where 条件 ];

 

left  [ outer ] join  列出从表中的符合条件的数据行，主表中的所有的数据

right  [ outer ] join列出主表中的符合条件的数据行，从表中的所有的数据

                   iii.             交叉连接

select …from  join_table cross join join_table2;

返回连接表中的所有数据行的笛卡尔积

86: select *from 主表，从表

Sql 86 语法

                     select [ distinct ] * | [ 列名 别名，…… ]

                     from 主表名 别名，从表名 别名 [ , 从表名 别名]

                     where 连接条件 and where 过滤条件

连接

              select [ distinct ] * | [ 列名 别名，…… ]

              from 主表名 别名，从表名 别名 [ , 从表名 别名]

              where 主表名.列名[ + :you ]= 从表名.列名[(+:zuo)] andwhere 过滤条件

 

 8.       集合操作符：

union all:  合并结果，有重复行

union :  并集

intersect:  交集

minus:  差集

 

更新操作及事务处理

 9.       对数据的操作

                       i.             创建表：

Createtable  tabname as select * from empwhere  条件  ；

                     ii.             insert into 表名 [ (列名1，列名2，……) ] values ( 值1 ，值2 )；

insert into 表名 values( 值1，值2，…… )；

 

insert into c_emp(empno,ename,job,mgr,hiredate,sal,comm.,deptno)

values( 9000,’dfg’,’fgh’,8876,’12-11月-88’,7000,800,40 );

 

字符串用  ’  ’ 引起了，日期使用to_date() 函数转换to_date( ‘2009-06-06’,’yyyy-mm-dd ’ )

数据库事物：一组相关的数据改变SQL语句，这组SQL语句对数据的改变要么不成功，要么全部成功

      

                   iii.             update 表名 set 列名=值[ ,列名2=值2 ，……] [ where 修改条件 ]

当没有条件时，将修改表中的所有记录

                    iv.             delete from 表名 [ where 删除条件 ];

不指定条件，将删除所有的行

       事物ACID属性：

u      原子性

u      一致性

u      隔离性

u      持久性

 

用户管理（sqlplus sys as sysdba）

10.       用户管理

创建用户： create user 用户名 identified by 密码；

修改用户密码：alter user 用户名 identified by 新密码；

锁定用户：alter user 用户名 account lock；

解锁用户：alter 用户名 accountunlock；

删除用户：drop user 用户名；

11.       权限管理

向用户授予系统权限：grant 权限名 [ ,权限名2,… ]  to 用户名

limited  tablespace 对表空间的操作权限

              查看系统权限： user_sys_privs表

撤销用户系统权限：revoke权限名 [ , 权限名2,… ]  from 用户名；

向用户授予对象权限：grant 权限名 [ ,权限名2,…] on 方案名.对象名 to 用户名；

撤销用户对象权限：revoke权限名 [ ,权限名2,… ]  on 方案名.对象名 from 用户名；

12.       角色管理

Connect：连接到oracle的权限

              Select privilege fromdba_sys_privs where grantee=’connect’;

Resource : 创建各种数据库对象的权限

              Select privilege dba_sys_privswhere grantee=’resource’;

Dba: 数据库管理员所有权限

Exp_full_database:可把数据库整个备份输出的权限

Imp_full_database:可把数据库整个备份恢复输入的权限

 

将角色授予用户：grant connect,resource to test；（user_role_privs）

撤销用户的角色：revoke角色名[,角色名2，……] from 用户名

13.       数据库的备份和恢复

exp 导出

imp 导入

 

数据库约束管理

14.       表管理

                       i.             创建表：

Create table [方案名.]表名（列名 列类型 [default默认值] [ 列级约束 ]，……[ constaint  标记约束]）；

                     ii.             修改表

添加表：alter table 表名 add( 列名 类型 [ default 默认值 ] …… );

修改表：alter table 表名 modify( 列名 类型 [ default 默认值 ] …… );

删除表：alter table 表名 drop column 列名;

为表重命名：rename 表名 to 新表名；

                   iii.             删除表

drop table 表名; 删除表中所有行和表结构

                     彻底删除：drop table 表名 purge；

使用上面语句删除时只是把表放置在回收站

       显示回收站的对象：show recyclebin；

       恢复回收站中的表：flashback table 表名 to before drop;

       删除回收站中的表：purge table 表名；

截断表：truncate table 表名；删除表中所有行，重置表的存储空间

 

15.       约束管理：

( not null /unique  / check / primary key /foreignkey )

创建表时指定约束：

Create table [方案名.]表名（列名  列类型  [ default 默认值 ] [ 列级约束类型 ]，……[ constaint 约束条件表级]）

       使用单独的sql语句添加约束：

              Altertable 表名 add [constraint 约束名 ] 约束类型（要约束的列名）；

             

Alter table 表名 add  [ constraint 约束名 ]  foreign key（要约束的列名）  reference 主表名（主表的某一列名）；

 

删除约束

       Alter tablet 表名 drop constraint 约束名；

16.       序列管理

序列：一种用于生成唯一数字的数据库对象，序列生成器会自动生成顺序递增的序列号

建立序列： create sequence 序列名 [ increment by 递增量值 ]

                            [ start with 开始值 ] [ maxvalue 最大值 ] [ minvalue 最小值 ]