Oralce学习笔记（一）

一、用户管理

1.创建用户: create user 用户名 identified by 密码 ；通常情况下使用 system 或 sys 用户创建，如果是普通用户则需要有相应的权限

 

2.修改用户密码：password 用户名  + 输入新口令

 

3.删除用户：drop  user 用户名 ；在删除用户时，注意如果要删除的用户，已经创建了表，那么在删除的时候带一个参数即：cascade 叫级联删除；在删除用户的同时也删除该用户创建的表

 

4.新创建的用户是不能登录到数据库的，因为它没有相应的权限；通常是由system或sys为其赋权限 ；语法为：

(1.)grant connect to 用户名   (其中connect叫做角色，角色是由各种操作数据库的权限组成)

 (2.)grant  dba  to 用户名  (数据库管理员的操作，不能轻易为用户赋该角色)

 (3.)grant resource  to 用户名 (可用于操作当前数据库的所有表)

 (4.)权限分类: a.系统权限：指用户对数据库的相关权限，如 建库、建表、建视图、建存储过程等操作的权限 

               b.对象权限:用户对其他用户的数据的对象操作的权限

 (5.)角色：有各种相应权限组成的各种操作数据库的方式

 

5.grant 操作方式(如.查询、修改、删除、插入等操作) on 表名 to 用户名 (数据库管理员，和当前对象的所有者)，查询时的语句为：     

  select * from  用户名.表名  (用户名是指表的所有者而不是当前用户) ，如果将操作方式换为all 则表示将增、删、改、查的操作一起赋给该用户  

 

6.收回权限：revoke 操作方式 on 表名 from 用户名 如：revokeselect on user from xiaoming 表示将查询user表的权限从用户xiaoming那里收回 ，由谁授予的就由谁收回

 

7.自己不能为自己授权，也不能为自己收回权限

 

8.权限的传递性：是指当将一张表的权限赋给一个用户，那么这个用户可以将这个权限再赋给其他的用户

语法：a.如果是对象权限，则只需在后面加上 with grant option如：

grant select on user from xiaoming with grant option  表示在给小明授予查询user表后,小明可以将此权限继续授予给其他的用户  如要将cott用户的user表的使用权限由小明赋给小红,则语句如下：

           grant select on cott.user to xiaohong (此时要保证登录用户是小明)

      b.如果是系统权限，则只需在后面加上 with admin option

9.当cott将赋给小明的查询user表的权限时，那么小红的此权限将同时被收回

 

二．表管理

1.表名和列的命名规则：a.必须以字母开头         b.长度不能超过3字符

                     c.不能使用oracle的保留字  d.只能使用如下字符 A-Z,a-z,0-9,$,#等

2.数据类型：

(1.)字符型：

A. char  定长 最大2000字符  速度快 效率高

B. varchar2(20)  变长 最大4000字符  会自动缩减空间 这样可以节省空间C C. clob 字符型大对象

(2.)数字型：number 范围 10的-38次方—10的38次方 可以表示整数，也可以表示小数

           number(5,2)  表示一个小数有5位有效数，其中2位是小数e.g-999.99—999.99

           number(5)    表示5位整数

(3.)日期类型：Date  包含年月日和时分秒

             Timestamp 是对date数据类型的扩展  （精度更高些）

(4.)图片类型：blob  二进制数据   可以存放图片、声音和视频(通常存放的都是路径 而不是真正的文件，若要提高安全性，则可以将文件存放到数据库中)

3.建表：语法同SQL Server2008

4.表空间：主要是用来存储表。 通常可以将同一个项目的表存放到一个表空间中

5.操作表：

  (1.)查看表结构：desc 表名

  (2.)添加字段：altertable 表名 add (列名 数据类型)

(3.)修改字段的长度：alter table 表名 nodify(列名 修改后的长度)

(4.)删除一个字段：alter table 表名 drop 列名 sal (通常不使用)

(5.)修改表的名字：rename 表名 to  新表名(通常不使用)

(6.)删除表：drop  table 表名

6.添加数据（插入）：语法同SQL Server2008 大致相同

  区别：(1.)oracle中默认的日期格式为‘DD—MON—YY’表示 天—月份—年，若要修改为：yyyy-mm-dd格式，则需要在命令行中执行如下语句：

          Alter session set nls_date_format=’yyyy_mm_dd’;

        (2.)添加部分字段：与SQL Server2008相同

        (3.)添加空值：将需要添加为空值的列的值设置为null.

        (4.)查询空值的数据：select * from 表名 where 列名 is null;

7.修改数据：与SQL Server 2008 相同

8.删除数据：(1.)delete from 表名 这种方式在数据删除后 其中的数据可以恢复。但需要在删除前设置回滚点 其格式为：

                a.设置保存点： savepoint aa; (aa表示回滚点) Oracle可以设置多个保存点

                b.删除数据： delete from 表名

                c.回滚数据：  rollback to aa.

9.Oracle数据基本查询（Oracle中是区分大小写的）数据表使用的是scott用户的表进行操作

  (1.) set timing on命令：  表示打开显示操作时间的开关

(2.) 基本查询与SQL Server2008相同

(3.)如何取消重复行：select distinct  deptno ,job fromemp 作用是过滤那些重复的数据

(4.)使用算式表达式：计算一个员工在一年的总收入：select sal*13,ename from scott.emp;使用月工资乘以月数

(5.)如何处理查询中为空值的列：使用nvl函数 如：查询员工一年的总收入，也就是月工资*月数+每月奖金数*月份数（有的员工没有奖金）查询语句为：

   Selectsal*13+nvl(comm.,0)*13,ename from scott.emp; 对代码的解释：

       表示如果comm的值为null时，那么comm的值就用0代替，若不为空，则使用相应的值进行计算

 

(6.)若where后有多个条件应使用and 关键字进行连接

   如：select ename,sal from scott.empwhere sal>=2000 and sal<=2500 ;

(7.)使用orderby排序 如

（1）按工资从高到低排列

   Select * from scoot.emp sal order by sal desc;

 （2）按照员工部门编号顺序和按工资的倒序排序

     Select * from scott.emp order by deptnoasc,sal desc;

(8.)使用列的别名排序()  如按年薪排序

     Select ename,(sal+nvl(comm.,0))*12 “年薪” from scott.emp order by “年薪”

10.Oracle数据的复杂查询：

 （1.）使用聚合函数查询：max  min  avg sum  count

   A．计算并输出员工的最高工资和最低工资

         select max(sal),min(sal) from scott.emp;

   B.将最高工资的人的名字和工资打印出来(利用子查询)

         Select ename,sal from scott.emp wheresal=(select max(sal) from scott.emp);

   C.计算月工资大于平均工资的员工的信息(利用子查询)

      Select * from scott.emp wheresal>(select avg(sal) from scott.emp);

  (2.)使用group by 和 having 子句

     A．按部门分组显示员工的平均工资和最高工资（注意：分组的列必须出现在输出列中）

select avg(sal),max(sal),fromscott.emp group by deptno

11.对数据分组语句的总结：

   （1.）分组语句只能出现在选择列表、having、order by 子句中

（2.）如果在select语句中同时包含有group by ,having,order by 那么他们的顺序是：

     Groupby（分组）,having(对结果进行帅选) , Order by(排序)，此顺序绝对不能颠倒

        select avg(sal),max(sal),deptnofrom scott.emp group by deptno having avg(sal)>2000 order by avg(sal);

（3.）在选择列中如果有列，表达式和分组函数，那么这些列和表达式必须有一个出现在group by 语句中

  12.多表查询：（大部分与SQL Server2008的语法差不多，在此只举几个简单的例子）

（1.）两表查询：

A．查询员工的姓名，工资以及所在部门的名称(dept 员工部门表)

   Selecta1.ename,a1.sal,a2.dname from scott.emp a1 ,scott.dept a2 where a1.deptno=a2.deptno

B．显示各员工的姓名，工资以及工资的级别（salgrade 工资级别表）

Select a1.ename,a1.sal,a2.gradefrom scott.emp a1,scott.salgrade a2 where a1.sal between a2.losal and a2.hisal;

C. 查询部门为10的部门名，员工名字和员工工资

Select a1.ename,a1.sal,a2.dname fromscott.emp a1 ,scott.dept a2 where a1.deptno=a2.deptno and a1.deptno=10;

13．自连接查询：自连接是指在同一张表的连接查询

   如：要查询某个员工的上级领导的姓名 例如要显示“FORD”的上级的名字

        selectworker.ename,boss.ename from scott.emp worker,scott.emp boss whereworker.mgr=boss.empno and worker.ename='FORD';

14.子查询：它是指嵌入在其它sql语句中的select语句，也叫嵌套查询

   （1）.单行子查询：查询‘SMITH’所在部门的所有员工

         select * from scott.empwhere deptno= (select deptno from scott.emp where ename='SMITH');

           单行子查询是指：只返回一行数据的子查询语句

     （2）.form子句的子查询：当在from子句中使用子查询是，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当from子句中使用子查询时，必须给子查询指定别名

           注意：在给表取别名时不能使用as关键字，给列取别名时可以使用。

查询高于自己部门平均工资的员工的信息

         A.第一步：根据员工的部门分组计算出各部门的平均工资和部门号

            Select deptno,avg(sal) mysal fromscott.emp group by deptno;

         B.第二步：根据上一步的结果查询需要的内容  （将上一步的查询语句看着是一张子表） 取一个别名;完整语句如下：

            select * from scott.empa1 ,( select deptno,avg(sal) mysal from scott.emp group by deptno)a2 wherea1.deptno=a2.deptno and a1.sal>a2.mysal;      

15.Oracle的分页查询：一共有三种方式：在此介绍最容易理解的一种

(1).rownum方式（内部机制规定，在一条查询语句中 rownum只能用一次）

    A．第一步：rownum分页 ：select * from emp

    B. 第二步：显示rownum(Oracle分配):

               Select a1.* ,rownum rnfrom(select * from emp) a1;

    C. 第三步：要显示第6到10之间的语句

              首先：显示出前十条数据:

Selecta1.* ,rownum rn from(select * from emp) a1 where rownum<=10;

           其次：显示出6到10条之间的语句：

Select * from (Select a1.* ,rownum rnfrom(select * from emp) a1 where rownum<=10) where rownum>=6

(2)查询中的几种变化：

    A.若要指定查询列，则只需要修改最里层的子查询的基础上修改，即修改：select * from emp 语句

    B. 如何在分页查询中排序，同样的也只需要修改最里层的子查询

    C．若要显示4到9条语句，只需要修改其中的10和6 即将10改为9，将6改为4；

（3）.分页查询的另外两种方式：a.根据rowid来分  b.按分析函数来分

16.其他操作：

（1）.用查询结果创建新表：create table 新表名 （id,ename,sal）as select empno,ename,sal fromemp;

   （2）.合并查询（实际应用中很少使用）：它的用法是：利用union,union all,intersert,minus关键字将两条或多条查询语句合并在一起，同时显示两条或多条语句的查询结果，当有重复的结果时，使用union关键字连接两条语句它会自动过滤重复的记录。

Union all 不会过滤重复的结果，intersect取两个表中结果的交集，minus是取差集