第一部分
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(+);其中,加号是外连接。