Oracle

我们就使用强大的Oracle开发工具PL/SQL Develoer

首先使用sys超级管理员的账号去解锁scott账号

在命令窗口输入：

Alter user scott account unlock

退出，重新利用scott账户登录

查询一下默认的员工表

执行一下以下SQL语句：

Select * from emp where ename=’JAMES’

部分sql server的sql语句在ORACLE 数据库上面无法执行，例如：

select top 2 from emp;

有的Oracle的也是独有的，例如在查询字符串加上&字符，会谈出一个窗口,这个功能不实用，但是要知道有：

SQL> select * from emp where ename='&name';

Linesize 每行显示多少个字符

Pagesize 每页显示多少条记录

1. Orale的权限管理

1.1 创建用户

注意，权限的管理只能在dba的用户才能创建。在别的用户创建就会报权限不足：

SQL> create user deng identified by d123;

create user deng identified by d123

ORA-01031: 权限不足

下面就来演示正确的创建用户：

先连接系统用户：

SQL> conn system/sise;

接着再执行以下语句：

SQL> create user deng identified by d123;

User created

这时候，用户deng就已经创建好了。

1.2 修改密码

用户给自已修改密码

SQL> password deng;

给别人修改密码就需要拥有dba权限或者alter user权限

的用户：

SQL> alter user deng identified by m1234;

User altered

1.3 删除用户

删除用户需要拥有dba身份的用户或者drop user权限的用户去删除：

注意：不能自已删除自已，sys用户是谁也不能删除的。

SQL> drop user deng;

1.4 权限管理

新创建的用户没有任何的权限，甚至无法登录，这时候就需要为用户赋予权限，赋予权限使用grant关键字，删除权限使用revoke关键字：

Oracle权限分为两种，一种是系统权限，一种是对象权限。

角色也可以分为两种，一种是预定义角色，一种是自定义角色。

Connect角色：

SQL> grant connect to deng;

Grant succeeded

此时，deng用户已经可以登录了，但是，我们使用他来查询数据库的表和创建表等操作，却被告知没有权限。

赋予角色创建表的权限：

SQL> grant resource to deng;

查看一个表结构：

desc emp;

赋予用户查询表的权限，必须在创建表的用户才有权限赋予：

grant select on emp to deng;

上面的deng已经赋予了查询权限，那么怎么查询呢？按照以往的查询语句肯定是不行的，会报表不存在的错误：

应该这样写：

SQL> select * from scott.emp;

赋予用户更新：

grant update on emp to deng;

赋予用户插入：

grant insert on emp to deng;

赋予用户删除：

grant delete on emp to deng;

一次性赋予以上四个权限：

grant all on emp to deng;

1.5 撤销权限

revoke select on emp from deng

1.6 赋予权限给别人，再通过别人传递给另一个:

执行语句：

对象:

SQL> grant select on emp to deng with grant option;

这样，deng用户也可以把emp的select权限赋予他人了

1.7 Profile口令管理

账户锁定：

先创建profile文件

SQL> create profile aa limit failed_login_attempts 3 password_lock_time 2;

接着：

SQL> alter user deng profile aa;

2. Oracle表的管理

表名规则：

表名必须以字母打头，长度不能超过30个字符，不能使用保留关键字，只能使用字母，数字，$,#等字符

字段类型：

Char 定长最大 2000个字符

Varchar 变长最大4000个字符

Clob字符对象，最大4G

Number类型：

范围-10的38次方到10的38次方

Number（4，2）表示有4个有效数字，其中有两个小数，范围-99.99-99.99

Number（4）4个有效整数，范围-9999-9999

日期类型：

Date时间类型，年月日，时分秒

Timestamp对date的扩展

Blob，存放二进制数据

视频，图片，声音·····

例如，新建一个学生表：

SQL> create table student(

2 xh number(4),

3 xm varchar(20),

4 sex char(2),

5 birthday date,

6 sal number(7,2)

7 );

SQL> create table class(

2 classaiId number(2),

3 cname varchar2(40)

4 );

这样，表就创建好了

添加一个字段

SQL> alter table student add(classId number(2));

修改字符的长度

SQL> alter table student modify(xm varchar2(30));

修改字符的类型或者是名称，只能是空表（无数据）

SQL> alter table student modify(xm varchar2(30));

删除一个字段

SQL> alter table student drop column sal;

修改表名

SQL> rename student to ss;

删除表

SQL> drop table student;

插入数据

SQL> insert into student values(12,'邓家海','n','11-9月-2002',3456.4，12);

修改日期格式：

SQL> alter session set nls_date_format="yyyy-mm-dd";

查询所有姓名为空的记录：

SQL> select * from student where xm is null;

查询所有姓名不为空的记：

SQL> select * from student where xm is not null;

更新数据：

SQL> update student set sal=sal/2 where sex='n';

更新多个数据：

SQL> update student set sal=sal/2,name=’xx’ where sex='n';

删除表

删除数据，表结构存在，写日志，可以恢复的，但是速度很慢：

SQL> Savepoint a;

SQL> delete from student;

SQL> Rollback to a;

表结构和数据一起删除

SQL> delete table student;

删除一条数据

SQL> delete from student where nsme=’邓家海’;

删除表数据，表结构还在，不写日志，速度快，但是不能恢复

SQL> truncate table student；

3. 查询

先使用scott用户登录

条件筛选：

SQL> select * from emp where sal>1000;

排序：

升序（默认）：

SQL> select * from emp order by sal;

降序（desc）：

SQL> select * from emp order by sal desc;

查询每个部门按照降序排列

select * from emp order by deptno,sal desc;

nvl（string，0）函数就是判断某个字符串是否为空，如果为空则用一个值（0）替换：

SQL> select ename,(sal+nvl(comm,0)) as "年薪" from emp;

查询最高工资的员工的工资和姓名：

SQL> select ename,sal as n from emp where sal=(select max(sal) from emp)

查询大于员工平均工资的员工的信息：

SQL> select * from emp where sal>(select avg(sal) from emp);

查询每个部门的最高工资和最低工资：

SQL> select ename,max(sal),min(sal) from emp group by ename;

查询每个部门每个岗位的平均工资：

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

查询各个部门工资平均大于2000的员工的信息：

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

分组函数只能出现在select，group by ，having 字句里面，绝对不能出现在where字句里面

如果group by ，having， order by同时出现，那么顺序一定是：group by ，having， order by

查询具有相同部门号的两个表的所有信息：

SQL> select * from emp,dept where emp.deptno=dept.deptno;

3.1多表查询

显示部门号为1的员工名、部门名和工资:

SQL> select t1.dname,t2.ename,t2.sal from dept t1,emp t2 where t1.deptno=t2.deptno;

把上面的部门名改为工资级别要怎么做：

SQL> select t1.ename,t1.sal,t2.grade from emp t1,salgrade t2 where t1.sal between t2.losal and hisal;

单表度多查询：

显示雇员的上级姓名：

SQL> select t1.ename,t2.ename from emp t1,emp t2 where t1.mgr=t2.empno;

显示FORD的上司：

SQL> select t1.ename,t2.ename from emp t1,emp t2 where t1.mgr=t2.empno AND t1.ename='FORD';

单行子查询:

Select ····from ··· where ····=（select ····from····）

只是返回一行结果

多行子查询，返回多行结果

Select ····from ··· where ····in（select ····from····）

多列子查询：

Select * from table where (ROW1,ROW2) = （seletct ROW1 ,ROW2 from table2 where````）