oracle基础笔记-如果不是DBA,作为一个程序设计人员,特别是要使用Oracle的程序人员.非常有必要了解以下
2011-04-08 17:42:24| 分类： ORACLE | 标签： |字号大中小 订阅
1.版本:oracle 8 -->oracle 8i(oracle正式进驻互联网) -->oracle 9i -->oracle 10g(网格技术)
2.oracel 10g安装问题:企业版,全局数据库名(以后的服务选项,记住此名)并勾选带"创建带样本方案的数据库",口令设置,进行口令管理并对相关用户解锁(普通用户:scott<tiger> 普通管理员system 超级管理员sys) 安装完后,会启动EM企业管理器
3.必须启动的两个服务才能保证oracle正常工作:TNSListener:作用是连接到数据库 Service全局数据库名:数据库的主服务,必须启用
4.运行中可以键入"sqlplus"或"sqlplusw"便可以分别进入不同的操作界面. sqlplus:以命令行的方式进入数据库连接;sqlplusw:以窗口的形式启动命令行工具,会提出输入"主机字串",如果我们有多个数据库,则在此 处输入要连接到的数据库的名称,如是不输入,则会默认连接最后建立好的数据库,这里我们可以输入安装时的全局数据库名"ora10g".
5.sqlplusw常用命令:(说明,为了方便读,命令多省略了分号)
>>文本编辑sql命令:由于不方便修改错,所以可以使用此命令.方法一,步骤一,在sqlplusw输入"ed a.sql" 如果a.sql不存在,会提出创建. 步骤二,在确定创建后,在创建的a.sql中即可以编辑sql命令.
步骤三,编辑完成后,在sqlplusw中输入"@a.sql"即可以执行刚编辑的sql命令. 方法二,在d盘根目录下新建一个名为"a.txt" 的文件,并编辑sql命令保存. 然后在sqlplusw中执行"@d:\a.txt"(或不要"",直接写成"@d:a.txt"也是可以的) 补充说明,如果我们在两个方法中创建的是以.sql结尾的文件,则可以在@引用时不要其后缀名,因为它会自动查找sql文件.
>>切换其他用户连接:假定sys和system密码都为123456 只要不是连接超级管理员sys,我们都可以这样做,以连接system为例:在sqlplusw中直接输入"conn system/123456"即可,如果只是输入"conn system"系统会提出输入密码. 这里需要注意,如果是连接sys超级管理员,需要这样写:"conn sys/123456 as sysdba" 它的基本格式为: conn 用户名/密码 [as sysdba|sysoper]
>>查询非自身的表:假定我们当前是以超级管理员sys来连接,且在scott用户下有一张名为"temp"的表(sys下无此表). 如果我们直接输入"select * form temp"将会提示"错误,表或视图不存"这样的信息.如果要想查此表,必须这样写"select * from scott.temp" 总结,如果是查找其它用户下的表,必须输入表的完整名(表的完整名是指 用户名.表名)
>>显示当前正连接的用户:直接输入"show user"
>>显示数据库的全部表:直接输入"select * from tab"
>>查看一个表的完整表结构:直接输入"desc 表名称"
>>清屏: clear scr ---> 退出: EXIT QUIT
6.sql功能概括:
>>DML(Data Manipulation Language,数据操作语言)---用于检索或者修改数据
>>DDL(Data Definition Language,数据定义语言)---用于定义数据的结构,如创建,修改,或者删除数据库对象.
>>DCL(Data Control Language,数据控制语言)---用于定义数据库用户的权限.
7.基本查询实例
>>给查询显示结果取别名:select job 工作,name,名字 from emp;
>>消除重复:select distinct job from emp; 此句将消除jop这一列中所有的重复. 但是如果是这样: select job,name from emp;这样要保证name和job在后面的查询中都是重复的才会消除.
>>实现特殊显示:比如要显示: 编号是: 7369 的雇员, 姓名是: jack 工作是: clerk 可以借助'和||来实现完整实例为: select '编号是:' ||7369|| '的雇员, 姓名是:' ||jack|| '工作是:' ||clerk from emp; 注意要所||括起来的内容换成相应的字段名,比如7369对应enumber,jack对应ename, clerk对应job. 则换好后为:select '编号是:' ||enumber|| '的雇员, 姓名是:' ||ename|| '工作是:' ||job from emp; 注意最后一个字段只在左边用了||
>>简单的运算:select name sar*12 年薪 from emp; 意为从emp表中查得每个员工的月工资乘以12来表示年薪(取的别名,尽管oracle支持中文,但是我们一般不应给别名成中文)
8.where限定查询
>>where:select * from student where age>18 ; 查询年龄大于18岁的学生
>>select * form student where busary is not null; 查询领取了奖学金的学生(busary字段表示奖学金,领取了的填写相相应的金额,没领取的不填为空,注意不是0) 如果是查询未领取奖学金的同学直接把where改成where busary is null即可.
>>select * from student where age>18 and busary is not null; 查询年龄大于且领取了奖学金的同学.
>>select * from student where not (age>18 and busary is not null); 意为查询年龄不大于18,且未领取奖学金的同学.这里以and"与"举例,"或"操作用or
>>范围查询语句: select * from student where age between 18 and 20; 查询年龄大于等于18小于等于20的学生(注意的是它是包括了18和20).等价于:select 8 from student where age>=18 and age<=20;
>>between and的优势在上例还未体现出.现假定birday表示学生的出生日期,且该字段用 28-2月 -86 这样的形式表示(86年2月28号),如果我们要查出1985年2月15号出生到1986年5月10号出生的学生则可以这样写: select * from where birday between '15-2月 -85' and '10-5月 -86'; 注意要使用''引起来. 这说明and除了支持数字还支持日期(日期本质也是数字)
>>注意大小写比如字段name表示学生的名字,如果知道张三的name字段为"zhangsan",如果我们想查询张三的全部信息 select * from student where name='ZhangSan'; 这样是查不出的,因为name字段中的"zhangsan"全部是小写的. 正确的只需改: where name='zhangsan'
>>使用IN,基本格式为:字段 IN(值1,值2,...值n) 如果我们想查询学号为01,02学生的信息(num表示学号),以前可以这样写 select * from where num=01 or num=02; 但是这里可以用IN来达到同样的目的:select * from where num IN(01,02);
>>模糊查询like:select * from student where name LIKE '_s%'; 意为查出姓名第二个字母为s的所有学生信息 "-"表示匹配一个长度,"%"表示匹配任意长度. 如果要想查出名字任意位置中出现s的学生信息,可以这样写: select * from student where name LIKe '%s%'; 而LIKE '%%'表示查询全部. 另LIKE更方便查询日期,比如学生出生日期字段为birday(格式为 25-3月 -86),如果要想查询86年出生的学生信息,可以这样写: select * from student where birday LIKE '?%'; 查询应用于数字,比如要查询学号带6(06,16,26等)的所有学生信息可以这样写: select * from student where num LIKE '%6%';
>>在操作中,可以使用的数学计算符:> >= < <= = <> !=
9.ORDER BY的使用：
>>基本格式:ORDER BY 排序字段1,排序的字段2 ASC|DESC --->ASC表示升序,DESC表示降序 举例:
select * from student ORDER BY age; 表示查询结果按年龄升序排列(未指定即是升序排列). 如果想是把此结果按降序排列,可以这样写: select * from student ORDER BY age Desc;
>>组合:select * from student where age=20 order by score desc,Num asc; 意为查出年龄为20的所有学生信息并把结果按成绩降序排列,成绩相同则学号按升序排列.
>>ORDER BY 排序操作必须是放在SQL语句的最后执行
10.Oracle单行函数分类:字符函数,数值函数,日期函数,转换函数,通用函数
11.字符函数:
>>小写字母转成大写:upper(字串) 体验函数执行效果,在sqlplusw窗口中输入: select upper('String test') from dual; 执行后将会在窗口中立即看到效果.
>>大写字母转成小写:lower(字串) 应用:假定一个人的名字name字段全部是小写字母的形式,但是如果想让提交的大写(或是大小混写)能够也能查到.可以这样做: select * from emp where name=lower('JaCk'); 这样做即是说把'JaCk'全部转成小写作为条件查找,这样便可以找到了.
>>把字串中的单词开头字母变成大写:initcap(字串);
>>字串截取:substr(字串,截取开始位置,截取长度) 举例:select substr('hello',1,3) from dual; 截取结果: hel -->换成 select substr('hello',0,3) from dual;发现结果仍为0,说明0和1都是从第一个字符开始.我们可以这样理解,0是作为一个特殊也从第一位置开始.
>>字串长度:length(字串) 应用: 截取字串"testvip"的最后三个字符: select substr('testvip',length('testvip')-2) from dual; 等效于: select substr('testvip',-3) from dual;
>>替换字符:replace(字串,原字符,替换的字符) 举例: select replace('hello word','h','H') 字串替换,length('hello word') 求字串长度 from dual;
12.数值函数:
>>四舍五入:Round(); 举例,select Round(658.568,2) from dual; 四舍五入保留两位小数,结果为658.57 -->select Round(658.568) from dual;表示不保留小数,结果为659; -->select Round(6568.568,-2) from dual; 表示四舍五入整数,正数的第二位为6所以进位,结果为6600;
>>直接截断:Trunc 举例:select Trunc(485.56) from dual; 直接舍掉小数,结果为485 -->select Trunc(568.596,2) from dual; 结果为保留两位小数,不过后面的小数不管是多少都舍去,其结果为568.59 --> select Trunc(5680.58,-2) from dual; 表示从整数开始的第二位8舍去,其结果为5600;
>>求余(取模):Mod 举例: select Mod(12,5) from dual; 其结果为2
13.日期函数:
>>操作规律:日期 +/- 数字 =日期 --->日期 +/- 日期=数字(天数)
>>sysdate: 举例, select sysdate from dual; 返回当前日期
>>MONTHS_Between():求出给定范置的月数,举例, select Months_between(sysdate,'25-3月 -09') from dual; 注意加第二个参数加引号,且是用前面的参数减后面的参数(如果后面的大于前面的将会得到负数). 另此法算出是带小数的,可以借助round四舍五入
>>ADD_months(): 在指定日期上加上指定月数后的日期 举例, select Add_months('15-3月 -09',5) from dual;
>>Next_day():下一个星期几的日期,举例, select Next_day(sysdate,'星期二') from dual;
>>Last_day():一个日期所在月的最后一天的日期, 举例, select last_day(sysdate) from dual;
14.转换函数:
>>TO_CHAR():转换成字串 举例一, select to_char(sysdate,'yyyy') year,to_char(sysdate,'mm') month,to_char(sysdate,'dd') day from dual; 补充注意:select to_Date('2009-02-26','yyyy') from dual;这样直接引用一个日期却不能转换!! --->例二, select to_char(sysdate,'yyyy-mm--dd') from dual;
>>to_number():转换成数字, 举例, select to_number('1785') from dual; 注意的是内面的参数只能是数值型的字串
>>to_Date():转换日期,举例,select to_Date('2009-02-26','yyyy-mm-dd') from dual; 转换后成功会得到一个oracle的日期格式.
15.通用函数
>>如果某个字段允许为null,我们把这个字段用来进行加等操作仍会得到空.(举例,比如某些员工可能无奖金,奖金字段就设为空,而当我们 想计算年薪时,需要用到奖金,如果某员工的奖金为空,再加上他的工资会导致年薪计算为空,这时就需要对null处理) NVL函数专门用来处理null值为指定的内容. 举例, select nvl(null,0) from dual; 指定null为0 -->select ename, sal*12+Nvl(comm,0) from emp 这里意为算员工的年薪,并处理奖金字段comm为null的值为0
>>Decode()函数 :举例, select decode(1,1,'内容是1',2,'内容是2') from dual; 执行后会显示内容是1
16.oracle卸载方案:
--停止所有oracle服务(services.msc) -->进入oracle自带的卸载安装统一执行文件,点卸载删除即开始卸载 -->注册表删除以下内容: 在HKEY_LOCAL_MACHINE\SOFTWARE下，删除Oracle目录
在KEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services下，删除所有Oracle项;
17.多表查询:
>>查询记录数: select count(*) from student;
>>如果查询两张表,而没有进行字段关联,将会以笛卡尔积的形式查询出交互式结果,即是说会查出两张表的记录数乘积. 这种查询一般不使用,没有实际意义,且如果是大量的表查询会大大影响效率.
>>关联字段查询表: select * from emp, dept where emp.deptno=dept.deptno; 此查询会把以部门编号来关联存储数据. 这样就会只查出14条记录. 而如果没有where来关联相关字段则会查出14*4=56条记录 如果表名过长,将不方便多次引用操作,所以我们可以采取别名方式来简化操作,修改查询为: select * from emp e,dept d where e.deptno=d.deptno ; 基查询效果是一样的
>>查询指定的每个表中指定的字段: select e.job, e.ename, d.dname, e.deptno, d.deptno from emp e,dept d where e.deptno=d.deptno; 注意这里我故意分别引用了它们的共有字段deptno,因而结果会显示两次deptno信息. 从查询结果可以看出它们会引关联字段为准来作升序排列.
>>自身关联: select e.ename, e.job, m.ename from emp e,emp m where e.mgr=m.empno ; 意为查出员工的姓名,工作及直接领导(由于原表中给出的是领导的编号). 在此基础上再增加查询其员工所属部门, select e.ename, e.job, m.ename, d.dname from emp e,emp m, dept d where e.mgr=m.empno AND e.deptno=d.deptno;
18.组函数常用函数:
>>count():求出全部的记录 --举例:select count(empno) from emp; 查询字段empno的记录数
>>max():求出一组中的最大值 --举例:select max(sal) from emp; 求出emp表中的最高工资
>>min():求出一组中的最小值 --举例:select min(sal) from emp; 求出emp表中的最低工资
>>avg():求出平均值 --举例:select avg(sal) from emp ;求出所有员工的平均工资
>>sum():求和 --举例:select sum(sal) from emp where job='CLERK';求出工作是CLERK的所有员工的所有工资之和
19.分组应用:
>>求出每个部门的雇员数量(提示,按照部门来编号划分,即是说按deptno分组):select deptno, count(empno) from emp GROUP BY deptno;
>>注意事项:1.如果程序中使用了分组函数,则有两种可以使用的情况: 一是程序中存在了GROUP BY,并指定了分组条件,这样可以将分组条件一起查询出来. 二是如果不使用分组,则只能单独的使用分组函数 2.在使用分组函数的时候,不能出现分组函数和分组条件之外的字段.
>>实例一:select d.dname,count(e.empno) from dept d,emp e WHERE d.deptno=e.deptno GROUP BY d.dname; 按部门分组,并显示部门的名称,及每个部门的员工数 实例二:求出平均工资大于2000的部门编号和平均工资 select deptno ,avg(sal) from emp WHERE avg(sal)>2000 GROUP BY deptno; 这样写会提示"此处不允许使用分组函数",原因是分组函数只能在分组中使用,不能用于WHERE语句中. 那么如果要使用分组条件,则只能通过第二种条件指令,HAVAING来达到目的 格式为"GROUP BY 分组条件 {HAVING 分组条件}" 此实例可以这样写: select deptno ,avg(sal) from emp GROUP BY deptno HAVING AVG(sal)>2000; 实例三: select job ,sum(sal) su from emp WHERE job<>'SALESMAN' GROUP BY job HAVING sum(sal)>5000 ORDER BY su; 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于5000,最后结果按月工资的合计升序排 列
>>总结:只有列信息存在相同要求的内容(比如都是男性,或者年龄在20-30)才可能使用分组; 分组函数可以嵌套使用,但是在组函数嵌套使用的时候不能再出现分组条件的查询语句
20.子查询:在一个查询内部还包括另外一个查询
>>查询比7654号员工工资要高的全部雇员信息: 步骤一,先查出雇员7654的工资: select sal from emp WHERE empno=7654; 这一步查询雇员7654的工次为1250. 步骤二,原题转成工资大于1250的所有员工信息 select * from emp WHERE sal>( select sal from emp WHERE empno=7654); 这里就是把此步骤一查询的结果作为了查询条件. 说明,所有子查询必须写在()内
>>实例一,查询出工资比7654高,同时与7788从事相同工作的全部雇员的信息
select * from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);
>>子查询中的IN:指定一个查询范围. 举例,求出每个部门的最低工资的雇员信息. select * from emp WHERE sal IN(select min(sal) from emp GROUP BY deptno);
>>子查询中的ANY:=ANY,与IN的操作是等价的,可以在上例用此替换:select * from emp WHERE sal=ANY (select min(sal) from emp GROUP BY deptno);
>>子查询中的ALL操作: >ALL 举例: select * from emp WHERE sal >ALL (select min(sal) from emp GROUP BY deptno); 比最大的值要大
21.数据库实际就是CRUD(增删改查 Create,Read,Update,Delete)操作:难点基本集中在"查Select".对于所谓的增删改操作,在练习前,最好是对原表复制一份 后操作复制的表. 例如,复制emp表: CREATE table myemp as select * from emp; 这样就完成了对表emp表的复制 说明,这种复制的格式为:CREATE TABLE 表名称 as (子查询); 且只在oracle中起作用. 补充:CREATE table temp as (select * from where 1=2); 像此处,如果子查询中用到的where限定查询,且where后一个永远不能成立的条件,则此时表示只是复制表结构,但不复制表内容
22.增加INSERT INTO格式一: INSERT INTO 表名称 VALUES (值1, 值2,....);
>>实例插入非标准日期:INSERT INTO myemp (empno,ename,job,hiredate,sal,deptno) VALUES(8755,'jack','管理员',To_Date('2009-01-21','yyyy-mm-dd'),900,40);
23.修改数据UPDATE 格式: UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 说明:这里的WHERE尽管是可选的,但是一般的更新都是有WHERE限制的
>>实例:UPDATE myemp set mgr=null,comm=null where empno IN(7499,7782,7876); 将编号是7499,7782,7876的员工的领导取消,并取消他们的奖金.
24.删除数据DELETE 格式:DELETE FROM 表名称 WHERE 列名称 = 值 说明:如果没指定WHERE条件,则全部删除.
>>实例:DELETE FROM myemp where empno IN(7499,7782,7876);删除编号是7499,7782,7879的员工
25.事务处理:为了保证数据的完整性,所有的操作在没有commit之前,所 进行的所有操作都并未真正的在数据库作出修改,这时我们可以通过rollback来回滚事务,恢复操作. 但是如果数据一旦被提交,则肯定无法回滚. 在Oracle中,对于每个连接到数据库的窗口(sqlplusw,sqlplus)在连接之后都会与数据库的连接建立一个session,即是说每一个 连接到数据库的用户都表示创建一个会话session. 由于oracle中存在这种事务处理机制,所以会带来死锁的可能,比如在一个sqlplusw窗口中更新A字段为一新值,并未commit提交. 又在另一个sqlplusw窗口中同样又要更新字段为另一新值,这时将会产生死锁. 原因是上一个更新并未真正的在数据库发生.这时,如果在先打开的窗口中提交事务,死锁便解除了.
26.小结查询:多表查询,子查询,分组统计结合在一起共同完成复杂查询
>>多表查询,注意对产生的笛卡尔积的消除(另对于复杂查询应使用别名以简化查询)
>>分组统计,所有的统计函数只能在分组语句中使用;如果要加入分组条件,则编写HAVING子句,即是如果统计函数作为条件只能在 HAVING中出现,不能在WHERE中出现; 在使用分组时,要注意查询的时候只能出现分组函数或是分组条件;分组查询允许嵌套,但是嵌套后就不能在查询其他的字段,包括分组字段
>>子查询可以在任意位置上编写. 但主要集中出现在FROM WHERE SELECT
27.创建表:
CREATE TABLE 表名称(
列名称1 数据类型,
列名称2 数据类型,
.......
)
28.删除表: DROP TABLE 表名称;
29.表的修改: 一般不建议修改表的结构,这就要求我们在建表时要考虑好表的结构.(在DB2中就不存在对表的结构修改的指令)
>>添加新的字段: ALTER TABLE 表名称 ADD (列名称 数据类型 DEFAULT 默认值, 列名称 数据类型 DEFAULT 默认值)
>>修改存在的字段: ALTER TABLE 表名称 MODIFY (列名称 数据类型 DEFAULT 默认值) 修改时候要注意, 如果字段长度进行缩小修改,且这时字段中的某记录的长度如果大于修改后的字段长度,则对字段的修改将无法实现.
>>删除存在的字段: ALTER TABLE 表名称 DROP COLUMN 列名称
30.为表重命名:RENAME,此语句只能在oracle中使用 格式:RENAME 旧的表名称 to 新的表名称
31.截断表:我们知道通过delete删除了数据如果没有提交 事务,则可以通过rollback回滚. 现如果要想清空一张表的数据,但同是又不需要回滚,即是说能立即释放资源. 则可以使用截断,语法为: TRUNCATE TABLE 表名称; 这样执行后,资源立即释放,且回滚无效.
32.约束:使用约束可以保证数据的完整性.
>>主键约束(PRIMARYKEY):主键表示一个唯一的标识,本身不能为空. 例如,身份证号是唯一的,不可重复,不可为空,则可以使用主键约束 一般在id上使用,而且本身默认了内容不能为空,主键约束可以在建表时指定(也就是在字段定义后面添加"PRIMARY KEY"). 如果字段限定了主键约束,则在插入时,如果是为空或者出现重复,都不能成功插入新的数据.
>>唯一约束(UNIQUE):在一个表中只允许建立一个主键约束,而其他列如果不希望出现重复值,则可以使用唯一约束
>>检查约束(CHECK):检查一个列的内容是否合法. 例如,年龄只能在0-150之间,在字段定义后加CHECK(age between 0 and 150)
>>非空约束(NOT NULL):字段中的内容不能为空,否则插入不成功.
>>主-外键约束:在两张表中进行约束的操作.主要表现在一个表中,限制另一个表中的字段.例如,CONSTRAINT person_book_pid_fk FOREIGN KEY (pid) REFERENCES person(pid) 这句话定义在book表中,意为在book表中插入pid字段数据时,要参照person表中的pid字段. 像这里,book表意为子表.对于像这种主-外键约束有以下两点注意: 在子表中设置的外键在父表中必须是主键;删除时应该先删除子表,再删除父表. 但是我们也可以采用强制方法删除子表,方法如下:DROP TABLE book CASCADE CONSTRAINT; 这种强调操作不推荐使用. 提升:在两张有主-外键关系的表,如果要想删除父表中的某记录时,是无法直接删除的,原因是子表引用了父表. 如果要想在删除父表中的某记录时,能直接级联删除子表相关的记录,则可以在子表使用主-外键约束时,增加ON DELETE CASCADE,即完整的约束为: CONSTRAINT person_book_pid_fk FOREIGN KEY (pid) REFERENCES person(pid) ON DELETE CASCADE 当然如果不想级联删除,则不使用"ON DELETE CASCADE"
>>说明:除了上明提到的在字段后直接加上约束条件外,也可如下在创建表时,按如下方法来添加约束条件,举例如下:
CREATE TABLE person(
pid
name
age
sex
CONSTRAINT person_pid_pk PRIMARY KEY(pid),
CONSTRAINT person_name_uk UNIQUE(name),
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150),
CONSTRAINT person_sex_CK CHECK(sex IN('男','女'),
)
33.修改约束
>>添加约束:语法, ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段) 对于约束名称的命名建议采用如下格式
PRIMARY KEY:主键字段_PK
UNIQUE:字段_UK
CHECK:字段_CK
FOREING KEY:父字段_子字段_FK
>>删除约束:ALTER TABLE 表名称 DROP CONSTRAINT 约束名称
34.ROWNUM:表示行的编号,实际上是一个列,但是这个列是一个伪列,可以在每张表中使用. 比如,SELECT ROWNUM,empno,job,sal,hiredate FROM emp; 这样将在查出的结果在ROWNUM中排序. 特别的,它支持<这种操作. 比如SELECT ROWNUM,empno,job,sal,hiredate FROM emp WHERE ROWNUM<=5; 这里查<=5是可以的,但是如果是>5,则不行. 如果想查询5-10条记录,不能使用between语句,因为ROWNUMS本身不支持,如果想查询中间记录,则只能使用子查询. 比如,这里,我们应先查1-10条记录,再查询5-10条记录. 完整代码如下:
select * from
(SELECT ROWNUM rn,empno,job,sal,hiredate FROM emp WHERE ROWNUM<=10) temp
where temp.rn>=5;
如果要想查询最后5条记录,则代码如下:
select * from
(SELECT ROWNUM rn,empno,job,sal,hiredate FROM emp WHERE ROWNUM<=15) temp
where temp.rn>=10;
35.视图:一个视图实际就是封装了一条复杂的查询语句.
>>创建视图:语法-- CREATE VIEW 视图名称 AS 子查询 此时的子查询表示一个复杂的查询
>>查询视图:语法-- SELEDCT * FROM 视图名称
>>删除视图:DROP VIEW 视图名称
>>修改视图:CREATE OR REPLACE view 视图名称 AS 子查询.使用此语法就可以自动删除或更新视图.但是如果不使用此语法,想更改子查询语句,以便重新封装视图名称且要与以前的视图名称相同,则要先删除以前的视图,再重新创建.
>>创建带条件的视图:参数WITH CHECK OPTION :不能更新创建条件. WITH READ ONLY:只读视图
举例,创建新的视图:
CREATE OR REPLACE VIEW test AS
select ename,sal,empno,deptno from emp
WHERE deptno=20;
这样便创建了新的视图,对test视图的deptno修改: UPDATE test SET deptno=30 where empno=7369;这样执行后,发现操作更改了编号为7369的员工,而test视图创建的时候条件是,deptno=20,这样7369号员工被修 改成了30部门,这样7369号从test视图中移除了. 而为了防止这种移除,我们限定不能更新创建条件,即是说修改时候原记录永远存在. 注意,这时查emp表发现7369的deptno字段也被更改. 为了恢复原始数据,我们先回滚,再创建带WITH CHECK OPTION参数的视图:
CREATE OR REPLACE VIEW test AS
select ename,sal,empno,deptno from emp
WHERE deptno=20 WITH CHECK OPTION;
创建这样的参数后,当我们再执行UPDATE test SET deptno=30 where empno=7369;语句时会报错. 但时,如果执行 UPDATE test SET ename='名字' where empno=7369;发现test视图中7369编号的ename被修改.且这时emp表中的相应数据也被改变. 先回滚,再重新创建带"WITH READ ONLY"参数的视图:
CREATE OR REPLACE VIEW test AS
select ename,sal,empno,deptno from emp
WHERE deptno=20 WITH READ ONLY;
这样创建的视图,不管怎样都是不允许被修改的.
36.序列:在很多数据库中都有一个自动增加的序列,如果现在要想在oracle中完成自动增长功能,则只能依靠序列完成.所有的自动增长操作,需要用户手工完成处理.
>>序列创建格式:
CREATE SEQUENCE [user.]sequence_name --序列名称
>>更改序列号格式:
ALTERSEQUENCE [user.]sequence_name
修改序列可以是:修改未来序列值的增量 --设置或撤消最小值或最大值 --转变缓冲序列的数目 --指定序列号是否是有序
>>删除序列:DROP SEQUENCE 序列名称
>>一个实例:首先在scott用户下创建一个temp表:
CREATE TABLE temp(
num NUMBER ,
age NUMBER
);
create sequence myqu
minvalue 2
maxvalue 12
start with 3
increment by 2
cache 5
cycle;
上面的序列指定了缓冲和循环输入. 接着我们便可以在插入数据时,使用这个序列了.
INSERT INTO temp (num,age) values(myqu.nextval,myqu.currval); 这条语句反复执行10次,观察结果,我们知道,在第一次从3开始,直到循环到11,再退回到最小值2重新开始循环.
37.同义词:在一般情况下,我们如果要访问其它用户下的表,可以使用"用户名.表名称"的形式来访问,但是为了方便操作,我们也可以定义同义词的方式来访问
>>同义词创建的语法: CREATE SYNONYM 同义词名称 FOR 用户名.表名称
>>删除同义词:DROP SYNONYM 同义词名称
>>一个应用实例:输入"conn system/a123456"切换到system用户下(可以show user来验证当前的连接用户) --查询emp:select * from emp;发现查不到,而 select * from scott.emp; 这样可以查到. --创建同义词: CREATE SYNONYM emp FOR scott.emp; 创建后,再查询 select * from emp;发现可以查到. --如果不想使用,则删除同义词DROP SYNONYM emp,删除后再查询 select * from emp;发现查不到.
38.用户管理
>>创建用户的语法:CREATE USER 用户名 IDENTIFIED BY 密码 要想创建用户,必须以管理员身份登录 为用户授权的格式:GRANT 权限1,权限2... TO 用户名
>>实例:使用conn system/a123456 切换到system管理员用户下. 然后创建一个用户名: CREATE USER test IDENTIFIED BY test; -->然后新打开一个sqlplusw窗口,用刚创建的用户登录,发现会出现"user TEST lacks CREATE SESSION privilege; logon denied"这样的错误,这即是提示用户没有创建session权限,没有session权限就意味着无法登录 -->现给刚创建的用户授权:GRANT CREATE SESSION TO test;
这里意为给test用户授CREATE SESSION权. 再conn test/test 发现正常连接-->这时候,我们在创建一个表内容如下:
CREATE TABLE temp(
id
username varchar2(20)
); 一执行后,发现权限不足. 说明,实际上一个新的用户要想真正的被使用,必须给其赋予相应的操作权限,但是如果想赋予多个权限会很麻烦,这时我们可以采用赋予角色的方式来让新创建的 用户有相应权限(角色实质就是具有一组权限的集合). 在oracle中提供了两个重要的角色:CONNECT RESOURCE. 这里我们给test用户赋予这两个角色,切换到system管理员用户下,执行:GRANT CONNECT,RESOURCE TO test;后,显示授权成功,再切换到test用户下,再创建上面的表,发现此时便可成功创建表.
>>修改密码格式:ALTER USER 用户名 IDENTIFIED BY 密码 比如,修改test用户的密码为vip 可以在system管员登录下这样写:ALTER USER test IDENTIFIED BY vip;这样密码更改成功(说明,如果想把密码改成是以数字开头的是不行的,这点在安装oracle时,为system等用户设置密码时就已有说明).
>>让用户名失效:ALTER USER 用户名 PASSWORD EXPIRE 这样一旦执行后,在切换失效的用户名,将会让我们修改密码. 实例-->在system用户下执行 ALTER USER test PASSWORD EXPIRE;后,显示"用户已更改",随后新打开一个窗口,输入test的用户名,密码vip后立即显示"the password has expired",并弹出一个修改test用户密码的窗口,旧口令中输入vip,新口令自己随意. 这个实际就是模拟用户第一次登录时,提示修改密码.
>>锁住一个用户:ALTER USER 用户名 ACCOUNT LOCK;
>>解锁用户:ALTER USER 用户名 ACCOUNT UNLOCK;
39.数据库备份:数据库在运行期间都是需要不断进行备份,这们系统崩溃后,便可以恢复数据.
>>备份:exp
>>恢复:imp
>>实例操作:cmd进行F:\temp>后,执行exp后,将开始执行备份,要求输入用户名密码.我们输入scott来测试.用户名 密码成功后,按默认走,出现"导入文件: EXPDAT.DMP"的提示,接着默认走,一直到全部执行完后.可以在F:\temp>下看到EXPDAT.DMP文件便是数据库备份文件. -->执行:DROP TABLE myemp; 删除scott下的myemp表并提交后. -->然后在cmd的F:\temp>下执行imp依次走默认,中间要出现提示输入用户名. 这里说明,对于导入文件,导出文件,默认的是"EXPDAT.DMP"但是我们可以在执行exp或imp时,进行人为的修改.
40.嵌套表:在一个表中还包含另一个子表.在oracle中通常正确的做法是使用主外键关联的方式,而并非使用嵌套表.这里对嵌套表只作了解.
>>一个实例:要想完成一个嵌套表,首先要在创建数据库表的时候指定数据类型.所以嵌套表本身也需要指定嵌套类型,因而这种类型就需要单独定义. 定义方法如下:
CREATE TYPE project_ty AS OBJECT(
proid
proname varchar(50) ,
prodate
);
/ 注意这里必须加"/"且要换行加. 定义此类型后,还需要为此类型指定一个名称:
CREATE TYPE project_nt AS TABLE OF project_ty;
/ 同样"/换行写".这里的名称定义表示以后可以直接使用project_nt来表示project_ty,类似于用varchar2来表示字串. 下面创建一个表,表中来引用此类型.内容如下:
CREATE TABLE department(
deptno
dname
projects project_nt
)NESTED TABLE projects STORE AS project_nt_temp;
下面来测试插入数据:需要指定每个project类型.
INSERT INTO department(deptno,dname,projects) values(1,'技术部',
project_nt(
project_ty(1001,'ER',SYSDATE),
project_ty(1006,'QR',SYSDATE),
project_ty(1008,'DR',SYSDATE))
);
现在查询:select * from department; 查询可以看出project内容以一张嵌套表出现.如果要想查询这个内部表可以指定条件来实现,如: select * from table(select projects from department WHERE deptno=1); 将查询这个嵌套表的一部门信息
更新 UPDATE TABLE (select projects from department WHERE deptno=1) pro
SET VALUE(pro)=project_ty('1001','测试项目',TO_DATE('2000-01-25','yyyy-mm-dd'))
WHERE pro.proid=1001;
更新后,再次查询:select * from table(select projects from department WHERE deptno=1);
41.可变数组:实际就是将内部嵌套表的内容长度进行限制.
>>一个实例: 首先创建一个工人信息类型.
CREATE TYPE worker_info AS OBJECT(
id number,
name varchar2(20),
sex varchar2(5)
);
/
CREATE TYPE worker_info_list AS VARRAY(10) OF worker_info;
/
CREATE TABLE department2(
deptno
dname
workers
); 创建表成功后,再来插入数据
INSERT INTO department2(deptno,dname,workers) values(20,'信息部',
worker_info_list(
worker_info(201,'张三','男'),
worker_info(202,'李四','女'),
worker_info(203,'王五','男'))
);
插入数据后,再来查询数据: select * from department2;
42.数据库的设计范式.数据库表的关联越少越好,SQL语句复杂度越低越好
>>第一范式:表中的字段是不可再分的.
>>第二范式:多对多关系设计.
>>第三范式:一对多关系设计.
43.必须掌握PowerDesigner工具的使用.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
