[Oracle]学习Oracle数据库的简单笔记（未完待续）

第一部分

0.通过system管理员来解锁用户：

alter user scott account unclock ;

1.连接命令：

a) 可以用来显示当前用户名是什么：

Show user ;

b)该命令经常用于切换用户。建议使用普通用户登录。如果确实需要system用户则可以使用该命令切换为管理员用户：

conn[ect] 用户名/密码@网络[as sysdba/as sysoper] ;

c)用于断开当前用户与oracle连接但是并不退出sqlplus窗口：

disc[onnect] ;

d)用于断开当前用户与oracle连接同时退出sqlplus窗口：

exit ;

 e)用户名修改密码：

passw[ord] ;

f)交互命令：

select * from emp where 列名 = "&abc" ;

g)编辑sql文本的命令：

edit d:/aa.sql ;

h)需求如下，把屏幕上显示的记录保存到文件中，以供以后分析。首先spool on，然后spool 文件路径，然后执行sql操作，最后spool off。

2. sql plus 命令

a) 设置宽度：

set linesize 120 ;

b)设置每页显示多少行：

set linesize 120 ;

3.用户管理

a) 创建用户，不能用数字开头，并且必须要有创建用户的权限。

create user 用户名 identified by 密码 ;

b)需要具有dba的权限，或者拥有修改密码的系统权限：

alter user 用户名 identified by 新密码 ;

c)表空间指表存在的空间，指向具体的数据文件。相关创建用户的细节如下：

Identified by 表明该用户将用数据库方式验证

default tablespace users 用户的表空间在user上

temporary tablespace temp 用户的临时表建在temp空间 

quota 3m on users 表明用户建立的数据对象最大只能是3兆。

d)刚刚创建的用户是没有权限的，所以需要dba给该用户授权：

grant connect to 用户名 ;

如果你希望该用户建表没有空间限制：

grand resource to 用户名 ;

如果你希望该用户成为dba：

grant dba to 用户名 ;

仅仅可以登录：

grand create session to 用户名 ;

e)赋予/收回  权限/角色  的语法相同：

grand/revoke 权限/角色 to 用户名 ;

综合案例：

创建用户小明，然后给她分配两个角色，可以登陆，创建表，可以操作（crud）自己创建表，然后回收角色，最后删除用户。

A)使用system创建xiaohong：

Create user xiaohongidentified by 1223 ;

B)给xiaohong分配两个角色：

Grant connect to xiaohong ;

Grant resource to xiaohong ;

C)xiaohong登陆：

Connect xiaohong/1223 ;

D)修改密码：

Password xiaohong ;

E)xiaohong创建一张最简单的表：

Create table users (int number) ;

F)回收权限和角色：

revoke connect from xiaohong ;

revoke resource from xiaohong ;

G)删除用户：

Drop user xiaohong [cascade] ; //中括号表示可选可不选。

PS：当我们删除一个用户的时候，如果这个用户已经创建过程数据对象，那么我们在删除该用户的时候需要加这个选项：cascade。表示删除该用户的同时把该对象一并删除。

PS2：方案之小技巧：如果希望看到某个方案有什么数据对象，使用PL/SQL登陆，就可以看到所有的数据对象。没有新建数据对象就没有对应的用户方案。

实例运用1：

要求：完成一个功能，让xiaohong用户去查询scott emp表。

A)Conn scott/1223 ;

B)Grant select/update/delate/indert/all on emp to xiaohong ;

C)Conn xiaohong ;

D)Select * from scott.emp ;  //要带上这个方案名，要不然就是自己的方案名。

实例运用2：

A)创建用户tea，stu，并给这两个用户resource和connect角色。

Conn system/1WHYwhy1223 ;

Create user tea identified by tea ;

Grant resource to tea ;

Grant connect to tea ;

Create user stu identified by stu ;

Grant resource to stu ;

Grant connect to stu ;

B)使用scott用户把emp表的select权限给tea

Conn scott/1223 ;

Grant select on emp to tea ;

C)使用tea查询scott的emp表

Conn tea/tea ;

Select * from scott.emp ;

D)使用scott用户把emp的所有权限给tea

Conn scott/1223 ;

Grant all on emp to tea ;

E)使用scott收回权限

Conn scott/1223 ;

Revoke select on emp from tea ;

Revoke all on emp from tea ;

F)让tea把自己拥有的对scott.emp的权限转给stu。

Conn scott/1223 ;

Grant all on emp to scott.emp to tea with grant option ; 

//with grant option对象权限。

//with admin option系统权限。

Conn tea/tea ;

Grant select on cott.emp to stu ; 

4.使用profile文件对口令进行管理。

a) 需求：只允许某个用户最多尝试登陆三次。如果三次没有成功则锁定两天。两天后才能重新的登陆。

基本语法：

Create profile myProfile1 limit failed_login_attempts 3  password_lock_time 2 ;

Alter user scott profile myProfile1 ;

b)给账户解锁。

Alter user scott account unlock ; 

c)终止口令。让用户定期修改密码。需求：一个账号的密码最多 用十天，宽限期为两天。到时必须设置新密码。

Create profile myProfile2 limit password_life_time 10  password_grace_time 2 ;

Alter user scott profile myProfile1 ;

d)口令历史。修改密码是不准使用以前使用的密码。

Create profile myProfile3 limit password_life_time 10  password_grace_time 2 password_reuse_time 1 ;

e)删除profile口令管理。删除后用户不受到约束。

Drop profile myProfile ;

PS : windows 的 dos下输入 systeminfo打印当前操作系统信息。

5.启动oracle的流程。

a) (dos控制台下运行)lsnrctl start ;//用于启动监听服务

b) oradim-startup-sid 数据库实例名 ; //用于启动数据库实例。 

6.特权用户，默认是以操作系统认证的，比如：conn system/orlhsp as sysdba   dbms一看到as sysdba则认为要以特权用户登录。前面的用户名和密码不看。

7.丢失管理员密码：

a) 搜索名为    PWD数据库实例名.ora  文件。数据库实例名是根据实际情况定的。

b)删除该文件。为以防万一，建议提前备份。

c)生成新 的密码文件。（如果需要新的密码生效则需要重新启动数据库实例）在dos输入：

orapwd file = 原来密码文件的全路径\密码文件名.ora  password=密码 entries = 登陆sys的最多用户数目;  

课堂练习：给scott用户分派一个profile，要求如下：

A)尝试登录最多四次

B)若四次均输入错误，则锁定用户2天

C)密码每隔五天修改一次，宽限天数为2天

D)练习如何给用户解锁

E)联系如何删除profile

第二部分：

1.创建表：

Create table table_name (

id number ,

name varchar2(32),

password varchar2(32),

birthday date ) ;

2.oracle基本数据类型讲解。

A) Char(size)：存放字符，最大2000字符，是定长。固定比较，速度快，如果存放数据长度不变则char比较便捷。

B) Varchar2(size)：变长，最大可以存放4000个字符。如果存放数据长度变化则char比较便捷。

C) Nchar(size)：定长，使用unicode编码，不管中英文均是当做一个字符。而一个中文字要占用两个char的字节。最大字符2000。

D) Nvarchar2(size)：变长，使用unicode编码。最大可以存放4000个字符。

E) Clob：(character large object)：字符型大对象。变长。最大8TB。只能存储字符型

F) Blob：(binary large object)：变长，二进制大对象。存放图片、声音。最大8TB。

G) Number：变长。 ①可以存放整数，可以存放小数。②number(p,s)中，p：有效位，s：保留到小数点第几位。比如：number(5,2)范围：-999.99~999.99。超出小数位的四舍五入。如果s是负数则在整数部分开始精确。③原则：实际开发中，我们有明确要求保留到小数点第几位，则明确指定。如果没有就可以以直接使用number。④举例：0.00000000000000023：p=2 & s=17。

H) Date：日期类型，默认格式DD-MM月-YYYY。添加的时候要使用默认格式。PS：借助oracle函数可以改成使用自己习惯的日期类型。特别注释：中间数字的月字不能少！

I) TIMESTAMP(n)：邮戳类型，自动更新日期。N为日期中的小数位数。不推荐。

建表综合案例：

建立一个学生表：

Create table students(

Id number ,

Name varchar2(64) ，

Sex char(2) ,

　 Birthday date ,

  Fellowship number(10,2) ,

  Resume clob );  

Create table class_(

Id number ,

Name varchar2(32) 

);

PS：最后一个数据不要加逗号！

3.表的管理和修改表。

//添加一个新的字段

ALTER TABLE 表名 ADD (新的列名 列的数据类型) ;

//修改字段的类型

ALTER TABLE 表名 MODIFY(列名 列的数据类型) ;

//删除一个字段

ALTER TABLE 表名 DROP COLUMN 列名 ；

//给表修改名字

RENAME 旧表的名字 TO 新表的名字。

实例应用：

1.给学生表添加班级编号：

Alter table students add(classId number) ;

（PS：desc 表名：查看表结构。）

2.学生姓名改成varchar2(30)：

Alter table students modify( name varchar2(30)) ;

3.学生姓名变成char(30)：

Alter table students modify( name char(30)) ;

4.删除学生表的felloship字段：

Alter table students drop column fellowship ;

5.把学生表名student改成stu：

ename students to stu;

6.删除学生表：

Drop table stu ;

第三部分：

Oracle的增删改查

CRUD = CREATE + READ + UPDATE +DELETE

l insert 增加操作：

INSERT INTO table_name (colunms_name) VALUES(columns_values) ;

1.插入数据应该与字段的数据相同。

2.数据的大小应该在规定范围内。

3.在value中列出的数据位置必须与列的排列位置对应。

4.字符和日期类型必须包含在单引号里面。

5.插入空值，不指定或使用null。在oracle的字段中，‘单个空 格’=null。

6.如果全部添加，可以不加列名。

PS：select age,dump(age) from test_table ;这个dump显示 本列的详细信息。

具体案例：向students里面添加几条数据。

insert into students 

values (1 , '汪海洋','男','11-11月-2012',1000，'Hello,World!') ;

l update修改操作：

UPDATE table_name SET col_name = expr1 WHERE 条件

1.可以用新值更新原有表行中的各列。

举例：update students set sex = '女' where name ='郑志春' ;

2.where特别注意限制，update和delete的遗失数据不可撤销。

l 案例update要求：

n 将所有人薪水改为5000元

n 将姓名为张三的同学薪水改为3000元

n 将李四的薪水在原来的基础上增加1000块钱

n 将没有奖学金的同学改为10元

update students set fellowship=10 where fellowship is null ;

l delete语句：delete from table_name where 条件。只能删除一行，删除数据本身，但是不能删除表的结构。如果删除一列则要使用update语句。而要删除整个表单需要drop语句。（如果一不小心删除，使用savepoint aa然后输入rollback回滚到保存点）。

l truncate table 表名：速度很快但是不能找回，没有记录，无法撤消。

l 查询语句select的使用（重点）：

使用emp，dept，salgrade三张表。

n select [distinct] *|{列名1，列名2...} from 表名[where ｛条件｝] ;中括号表示可选，大括号表示必填，|表示或。distinct可选：除去重复行（记录的各个字段都相同才算是重复行）。

PS：表名、字段等语句本身不区分大小写，只有对数据内容区分大小写。

n select可以先使用算术表达式进行数据处理再呈现出来：

select ename ,sal*13+comm from emp;

注意，null加减乘除任意值均为null。使用nvl函数：

select ename ,sal*13+nvl(comm,0)*13 from emp;

如果comm为空null则返回0。

n 使用as+双引号，显示在列头的的别名：

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

select ename||sal*13+nvl(comm,0)*13 as "年薪" from emp ;

使用||拼接多列数据作为一列返回：select ename||sal*13 from emp ;

面试题：

我们希望删除用户，同时保留该用户的数据对象，怎么处理？

1.锁定该用户：alter user scott account lock ; 

2.这时该用户已经不能登录到数据库了。但是我们的system用户依旧可以使用他的数据类型。

3.解锁该用户：alter user scott account unlock ; 

l where子句的用法：

n 如何显示工资高于3000的员工？

select * from emp where sal>3000 ;

n 如何查找1982年11月以后入职的员工：select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1982.1.1';

【这时可能oracle会提示格式不匹配的错误，需要to_char函数】

where to_char(hiredate,'mm')>'6';

where to_char(hiredate,'yyyy‘)>'1988';

n 如何查找工资在2000~2500的员工的情况？

select * from emp where sal between 2000 and 2500 ;

between是一个闭区间

l 使用like查询（模糊查询）

n 如何显示首字母为S的姓名和工资？

select ename ,sal from emp where ename like 'S%' ;

n 如何显示第三个字符为大写O的所有员工？

select ename ,sal from emp where ename like '_ _o' ;

(%表示0-多个字符，_表示1个任意字符)

l where里面如何使用in

select * from emp where empno =123 or empno =345 ;

=select * from emp where empno in (123,345) ;

l is null 显示没有相应列数据的数据。

l 使用逻辑运算符：or 和and

l 使用order by 对结果进行排序：

select * from emp order by ename [asc];

asc：升序排列，默认状态。desc：倒序排列。

l oracle支持使用别名排序：

　select ename,sal*13 年薪 from emp order by "年薪";

第四部分：

l Oracle 复杂表查询

n 数据分组： -max,min,avg,sum,count

？如何显示所有员工中最高工资和最低工资？

select max(sal) from emp ;  (有多个结果也只显示一个)

select min(sal) from emp ;

select avg(sal) from emp ;   (有空值null则不参与运算)

select sum(sal)/count(*) from emp ;   (count计算行数)

？统计有多少员工？

select count(*) from emp ;（字段亦可，空值不参与运算）

？显示工资最高的员工的名字和工作岗位？

（我们可以使用子查询来完成1.先查询最大工资是多少2.查找谁的工资是最大工资）

select ename , job from emp where sal = (select max(sal) from emp) ;  (SQL语句默认从右往左执行)

？显示工资高于平均工资的员工信息？

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

n group by和having子句：

group by对结果进行分组统计，

having进行限制(过滤)分组显示结果，通常与group by 同时出现。

？如何显示每个部门的平均工资和最高工资？

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

？先是每个部门每个岗位的平均工资和最低工资？

select avg(sal),max(sal) ,deptno ,job from emp group by deptno,job order by deptno ;

？显示部门平均工资低于2000的部门和平均工资？

select avg(sal),deptno from emp group by deptno having avg(sal)<2000 ;（having不支持别名）

l 注意事项：

n 1.分组函数（avg）只能出现在选择列表、having、order by子句中。

n 2.如果select中同时出现，顺序是：group by、having、order by，顺序不能出错。

n 3.在选择列中，如果有列，表达式，分组函数，那么这些列和表达式必须有一个出现在group by中，否则会出错。

多表查询：在实际开发中不可避免存在对两张或多张表的复杂查询。

1.我们看看多表查询的原理：

select * from emp,dept ;

2.如何实现多表查询：

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno ;

3.如何避免笛卡尔积：

多表查询的条件是 至少不能少于表的个数-1。

4.如何显示部门号为10的部门名、员工名和工资：

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=10 ;

注意：我们建议大家在进行多表查询的时候使用别名。

 select t1.ename,t1.sal,t2.dname from emp t1 ,dept t2 where t1.deptno=t2.deptno and t1.deptno=10;

l 自连接：

显示FORD的上级：select mgr from emp where ename='FORD' ;

显示FORD上级的信息：select * from emp where empno = (select mgr from emp where ename='FORD' );

显示各员工的姓名和他的上级领导的姓名：select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno ;

把worker的人员全部列出：select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno (+) ; 其中，加号是外连接。