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

第一部分

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

alteruserscottaccountunclock;

1.连接命令：

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

Showuser;

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

conn[ect]用户名/密码@网络[assysdba/assysoper];

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

disc[onnect];

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

exit;

e)用户名修改密码：

passw[ord];

f)交互命令：

select*fromempwhere列名="&abc";

g)编辑sql文本的命令：

editd:/aa.sql;

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

2.sqlplus命令

a)设置宽度：

setlinesize120;

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

setlinesize120;

3.用户管理

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

createuser用户名identifiedby密码;

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

alteruser用户名identifiedby新密码;

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

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

defaulttablespaceusers用户的表空间在user上

temporarytablespacetemp用户的临时表建在temp空间

quota3monusers表明用户建立的数据对象最大只能是3兆。

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

grantconnectto用户名;

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

grandresourceto用户名;

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

grantdbato用户名;

仅仅可以登录：

grandcreatesessionto用户名;

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

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

综合案例：

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

A)使用system创建xiaohong：

Createuserxiaohongidentifiedby1223;

B)给xiaohong分配两个角色：

Grantconnecttoxiaohong;

Grantresourcetoxiaohong;

C)xiaohong登陆：

Connectxiaohong/1223;

D)修改密码：

Passwordxiaohong;

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

Createtableusers(intnumber);

F)回收权限和角色：

revokeconnectfromxiaohong;

revokeresourcefromxiaohong;

G)删除用户：

Dropuserxiaohong[cascade];//中括号表示可选可不选。

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

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

实例运用1：

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

A)Connscott/1223;

B)Grantselect/update/delate/indert/allonemptoxiaohong;

C)Connxiaohong;

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

实例运用2：

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

Connsystem/1WHYwhy1223;

Createuserteaidentifiedbytea;

Grantresourcetotea;

Grantconnecttotea;

Createuserstuidentifiedbystu;

Grantresourcetostu;

Grantconnecttostu;

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

Connscott/1223;

Grantselectonemptotea;

C)使用tea查询scott的emp表

Conntea/tea;

Select*fromscott.emp;

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

Connscott/1223;

Grantallonemptotea;

E)使用scott收回权限

Connscott/1223;

Revokeselectonempfromtea;

Revokeallonempfromtea;

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

Connscott/1223;

Grantallonemptoscott.emptoteawithgrantoption;

//withgrantoption对象权限。

//withadminoption系统权限。

Conntea/tea;

Grantselectoncott.emptostu;

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

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

基本语法：

CreateprofilemyProfile1limitfailed_login_attempts3 password_lock_time2;

AlteruserscottprofilemyProfile1;

b)给账户解锁。

Alteruserscottaccountunlock;

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

CreateprofilemyProfile2limitpassword_life_time10 password_grace_time2;

AlteruserscottprofilemyProfile1;

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

CreateprofilemyProfile3limitpassword_life_time10 password_grace_time2password_reuse_time1;

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

DropprofilemyProfile;

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

5.启动oracle的流程。

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

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

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

7.丢失管理员密码：

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

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

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

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

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

A)尝试登录最多四次

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

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

D)练习如何给用户解锁

E)联系如何删除profile

第二部分：

1.创建表：

Createtabletable_name(

idnumber,

namevarchar2(32),

passwordvarchar2(32),

birthdaydate);

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：(characterlargeobject)：字符型大对象。变长。最大8TB。只能存储字符型

F)Blob：(binarylargeobject)：变长，二进制大对象。存放图片、声音。最大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为日期中的小数位数。不推荐。

建表综合案例：

建立一个学生表：

Createtablestudents(

Idnumber,

Namevarchar2(64)，

Sexchar(2),

　 Birthdaydate,

Fellowshipnumber(10,2),

Resumeclob);

Createtableclass_(

Idnumber,

Namevarchar2(32)

);

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

3.表的管理和修改表。

//添加一个新的字段

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

//修改字段的类型

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

//删除一个字段

ALTERTABLE表名DROPCOLUMN列名；

//给表修改名字

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

实例应用：

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

Altertablestudentsadd(classIdnumber);

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

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

Altertablestudentsmodify(namevarchar2(30));

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

Altertablestudentsmodify(namechar(30));

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

Altertablestudentsdropcolumnfellowship;

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

enamestudentstostu;

6.删除学生表：

Droptablestu;

第三部分：

Oracle的增删改查

CRUD=CREATE+READ+UPDATE+DELETE

linsert增加操作：

INSERTINTOtable_name(colunms_name)VALUES(columns_values);

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

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

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

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

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

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

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

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

insertintostudents

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

lupdate修改操作：

UPDATEtable_nameSETcol_name=expr1WHERE条件

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

举例：updatestudentssetsex='女'wherename='郑志春';

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

l案例update要求：

n将所有人薪水改为5000元

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

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

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

updatestudentssetfellowship=10wherefellowshipisnull;

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

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

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

使用emp，dept，salgrade三张表。

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

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

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

selectename,sal*13+commfromemp;

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

selectename,sal*13+nvl(comm,0)*13fromemp;

如果comm为空null则返回0。

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

selectename,sal*13+nvl(comm,0)*13as"年薪"fromemp;

selectename||sal*13+nvl(comm,0)*13as"年薪"fromemp;

使用||拼接多列数据作为一列返回：selectename||sal*13fromemp;

面试题：

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

1.锁定该用户：alteruserscottaccountlock;

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

3.解锁该用户：alteruserscottaccountunlock;

lwhere子句的用法：

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

select*fromempwheresal>3000;

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

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

whereto_char(hiredate,'mm')>'6';

whereto_char(hiredate,'yyyy‘)>'1988';

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

select*fromempwheresalbetween2000and2500;

between是一个闭区间

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

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

selectename,salfromempwhereenamelike'S%';

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

selectename,salfromempwhereenamelike'__o';

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

lwhere里面如何使用in

select*fromempwhereempno=123orempno=345;

=select*fromempwhereempnoin(123,345);

lisnull显示没有相应列数据的数据。

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

l使用orderby对结果进行排序：

select*fromemporderbyename[asc];

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

loracle支持使用别名排序：

　selectename,sal*13年薪fromemporderby"年薪";

第四部分：

lOracle复杂表查询

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

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

selectmax(sal)fromemp;(有多个结果也只显示一个)

selectmin(sal)fromemp;

selectavg(sal)fromemp;(有空值null则不参与运算)

selectsum(sal)/count(*)fromemp;(count计算行数)

？统计有多少员工？

selectcount(*)fromemp;（字段亦可，空值不参与运算）

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

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

selectename,jobfromempwheresal=(selectmax(sal)fromemp);(SQL语句默认从右往左执行)

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

selectename,jobfromempwheresal>(selectavg(sal)fromemp);

ngroupby和having子句：

groupby对结果进行分组统计，

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

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

selectavg(sal),max(sal),deptnofromempgroupbydeptno;

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

selectavg(sal),max(sal),deptno,jobfromempgroupbydeptno,joborderbydeptno;

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

selectavg(sal),deptnofromempgroupbydeptnohavingavg(sal)<2000;（having不支持别名）

l注意事项：

n1.分组函数（avg）只能出现在选择列表、having、orderby子句中。

n2.如果select中同时出现，顺序是：groupby、having、orderby，顺序不能出错。

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

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

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

select*fromemp,dept;

2.如何实现多表查询：

selectemp.ename,emp.sal,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;

3.如何避免笛卡尔积：

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

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

selectemp.ename,emp.sal,dept.dnamefromemp,deptwhereemp.deptno=dept.deptnoandemp.deptno=10;

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

selectt1.ename,t1.sal,t2.dnamefromempt1,deptt2wheret1.deptno=t2.deptnoandt1.deptno=10;

l自连接：

显示FORD的上级：selectmgrfromempwhereename='FORD';

显示FORD上级的信息：select*fromempwhereempno=(selectmgrfromempwhereename='FORD');

显示各员工的姓名和他的上级领导的姓名：selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.empno;

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