Oracle学习系列1-7

时间:2021-11-11 11:52:18

Oracle学习系列1

两个服务必须启动:

OracleOraDb10g*TNListener 和 OracleService***

使用sqlplusw先进行环境的设置

set linesize 300    ;

set pagesize 30     ;

编辑sql命令:

ed a.sql

执行 @a

切换用户:

conn User/passwd   [as sysdba|sysoper ]

conn system/manager

conn sys/change_on_install  as sysdba

显示用户:

show user;

获取所有表的名字:

select * from tab;

查看表的结构:

desc emp;  //emp 表

清空屏幕

clear scr

****************************************************************

SQL语句:

简单查询   限定查询   单行查询

所需表:scott用户下的表 --  雇员表,部门表,奖金表,薪水表

查询语句的基本格式

使用DISTINCT 关键字去掉重复的查询列

使用限定查询

对查询结果进行排序

掌握Oracle中提供的各主要单行函数

***********************************************************************************************

SQL包含DML(数据操作语言)、DDL(数据定义语言)、DCL(数据控制语言)

简单查询 :

select  {distinct } *|具体的列  别名  from 表

select * from emp;

select empno,ename, job  from emp;

指定查询返回列的名称 ,为列起个别名

select  empno 编号 ,ename 姓名 ,job 工作 from emp;

要求查询所有工作:

select job from emp;   //出现重复值

sel distinct job from emp;  //消除重复列。但是在消除重复列时,如果要同时查询多列,则必须保证所有列都重复才能消除

查询雇员的编号和工作:

select ename, job   from emp;

查询雇员的编号,姓名,工作,显示格式为:编号是7369的雇员,姓名是:SMITH,工作是:CLERK   :

select   '编号是:' || empno ||' 的雇员,姓名是:||'ename ',工作是:' || gob  from emp;

要求求出每个雇员的姓名及年薪:

select ename , sal*12 from emp;

select ename , sal*12  income  from emp;  // 别名回避中文

***********************************************************************************************

限定查询(where子句):

格式: select  {distinct } *|具体的列  别名

from 表

{ where 条件s }

查询出工资大于1500的所有雇员信息:

select *

from emp

where  sal>1500;

查询每月可以得到奖金的雇员信息:

select *

from emp

where comm IS NOT NULL; //comm字段不为null

查询无奖金的雇员:

select *

from emp;

where comm IS NULL;

查询出基本工资大于1500,同时可以领取奖金的所有雇员信息:

select *

from emp

where sal>1500 and comm IS NOT NULL;

查询出基本工资大于1500,或者可以领取奖金的所有雇员信息:

select *

from emp

where sal>1500 or comm IS NOT NULL;

查询出基本工资不大于1500,同时不可以领取奖金的所有雇员信息:

select *

from emp

where  not ( sal>1500 or comm IS NOT NULL ) ; //通过()表示一组条件

查询基本工资大于1500,但是小于3000的全部雇员信息:

select *

from emp

where  sal>1500  and sal <3000 ;

****SQL专门制定范围的查询的过滤语句:between  min and  max (包含等于的功能)*****

select *

from emp

where sal between 1500 and 3000 ; //between and  等价于sal>=1500 and sal<=3000

查询出1981年雇佣的全部雇员信息:

select *

from emp

where  hiredate between ‘ 1-1月 -81 ‘and  ’31-12月 -81 ‘ ;//日期表示加上’‘

**结论:between ...and...查询除了支持数字之外,还支持日期的查询(日期实际上以数组的形式表示出来)**

查询出姓名是smith的雇员的信息:

select *

from emp

where ename ='SMITH' ;  //Oracle中对大小写敏感,smith不能查询出

查询出雇员编号7369,7499,7521的具体信息:

**查询的范围,可以用IN()操作符完成,还有NOT IN()**

select *

from emp

where  emp IN( 7369,7499,7521 );

查询出编号不是7369,7499,7521的具体信息:

select *

from emp

where  emp NOT IN( 7369,7499,7521 );

要求查询出姓名是SMITH,ALLEN,KING的雇员信息:

select *

from emp

where ename IN( 'SMITH','ALLEN','KING' );

结论:IN操作符可以用在数字和字符串上,指定的额外的值不影响程序的运行

模糊查找功能,SQL中使用LIKE语句,但要注意通配符:% -->匹配任意长度的内容

_ -->匹配一个长度的内容

查询出所有雇员姓名中第二个字母中包含“M”的雇员信息:

select *

from emp

where ename like '_M%';

查询出雇员姓名中包含字母“M”的雇员信息:

select *

from emp

where ename like '%M%' ;

查询出在1981年雇佣的雇员信息:

select *

from emp

where hiredate like '%81%';

查询雇员编号不是7369的雇员信息:

select * from emp where empno <>7369 ;

select * from emp where enpno !=7369 ;

***********************************************************************************************

对查询的结果进行排序(order by子句)(重点) -->排序操作永远放在SQL语句最后执行

格式: select  {distinct } *|具体的列  别名

from 表

{ where 条件s }

{ order by 排序字段1,排序字段2  ASC|DESC } //默认低->高(升序)

要求按照工资由低到高排序:

select *

from emp

order by sal ;

要求查询出10部门的所有雇员信息,信息按照工资降序排序,若工资相等,按照雇佣日期升序排序

select *

from emp

where deptno =10

order by sal desc, hiredate (asc) ;//asc可不写

***********************************************************************************************

单行函数(重点)

格式:function_name( column | expression , [ arg1,arg2,...] )

分类:

字符函数:接收字符输入并且返回字符或数值

数值函数:接收数值输入并返回数值

日期函数:对日期数据进行操作

转换函数:从一种数据类型转换成另一宗数据类型

通用函数:NVL函数,DECODE函数

字符函数:

专门处理字符的,例如将大小写转换

将小写字符转换成大写字符

select upper('smith') from dual ;// dual是张数据表

查询smith雇员的所有信息:

select *

from emp

where ename = upper('smith') ;

lower()函数:

select lower('SMITH') from dual ; //将字符串全部变为小写

initcap()函数:

select initcap('hello, world')

from dual ;  //将单词的第一个字母大写

将雇员表中的雇员姓名变为开头字母大写

select initcap(ename)

from emp;

<字符串可以使用’||‘ 连接之外,还可以使用concat()函数进行连接操作>

select concat('hello' , 'world') from dual ; //不如’||‘好用

字符串截取:substr()

select substr('hello',1,3) from dual ; //index从(0/1)开始  结果:hel

字符串长度:length()

select length('hello') from dual ; //  结果:5

内容替换:replace()

select replace('hello','l','x') from dual ; // 结果:hexxe

要求显示所有雇员的姓名及姓名的后三个字符:

tips:先求整个长度-2,再截取

select ename ,substr(ename, length(ename)-2)

from emp;

or:

select ename ename, substr(ename ,-3,3)

from emp;

***********************************************************************************************

数值函数:

四舍五入: round()

截断小数点: trunc()

取余: mod

执行四舍五入操作:

select round(789.536) from dual ; // result:790

保留两位小数:

select round(789.536,2) from dual ; // result:790.54

对整数进行四舍五入的进位:

select round(789.536,-2) from dual ; // result:800

验证trunc()函数:

select trunc(789.536) from dual ;//result : 789

select trunc(789.536,-2) from dual ;//result : 700

使用mod()进行取余操作:

select mod(10,3) from dual ;//result : 1

***********************************************************************************************

日期函数:

当前日期: select sysdate  from dual ;

规则: 日期-数字=日期

日期+数字=日期

日期-日期=数字(天数)

函数: months_between() :求给定日期范围的月数

add_months(): 在指定日期上加指定的月数,求出之后的日期

next_day( date, char): 指定时间的下一个星期几(由char指定)所在的日期

last_day(): 返回指定日期对应月份的最后一天

----------------------------------------------------

显示10部门雇员进入公司的星期数:

tip: sysdate -hiredate = days /7 = weeks

select  empno, ename,round( (sysdate - hiredate)/7 ) weeks

from emp ;

验证months_between():

select empno, round( ename, months_between(sysdate, hiredate) ) months

from emp;

验证add_months():

select add_months(sysdate, 4) from dual ;

验证next_day():

select next_day(sysdate, '星期一') from dual ;//当前系统日期的下一个周一

验证last_day():

select last_day( sysdate ) from dual ;

***********************************************************************************************

转换函数 (重点)

函数:

to_char(): 转换成字符串

to_number(): 转换成数字

to_date():  转换成日期

===to_char()====

查询所有雇员的雇员编号,姓名,雇佣日期:

select enpno, ename, hiredate from emp ;

将年,月,日进行分开,可以用to_char()函数进行拆分,

必须指定通配符 yyyy-mm-dd

select enpno, ename, to_char( hiredate ,'yyyy') year,

to_char(hiredate, 'mm') months,

to_char(hiredate, 'dd') days

from emp;

to_char()函数具有日期显示转换的功能:

select empno, ename, to_char(hiredate ,'yyyy-mm-dd')   //1994-05-04

from emp ;

select empno, ename, to_char(hiredate ,'fmyyyy-mm-dd')  //1994-5-4

from emp ;

to_char()具有分隔数字功能:  (表示$:美元    L:本地货币)

select empno, ename,to_char(sal, '$99.999')

from emp;    // sal   $1,600

====to_number()=====

将字符串转变为数字

select to_number('123')+to_number('123') from dual ; //246

====to_number()=====

将字符串变为date类型

select to_date('2009-02-16','yyyy-mm-dd')  from dual ;

***********************************************************************************************

通用函数:

NVL() :  将指定的null值变为指定的内容

DECODE() :DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value 等于if1时,   DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。

decode(value ,值1,返回值1,值2,返回值2,值3,返回值3,值4,返回值4,缺省值 )

求出年薪的时候应该加上奖金,格式:(sal+comm)*12:

select empno, ename,  (sal+comm)*12 from emp ; //错误,有些雇员的comm为null

select empno, ename, (sal+NVL(comm, 0)*12 ) income

from emp ;

<对需要进行计算时,必须对null使用NVL()函数进行转换操作>

验证decode()函数:

select decode(1, 1,'内容为1',2,'内容为2',3,'内容为3',4,'内容为4')

from dual ;   // result :内容为为1

雇员的工作:业务员clerk, 销售人员salesman   经理 manager  分析员analyst  总裁president

要求查询出雇员的编号,姓名,雇佣日期及工作,将工作替换成以上的信息:

select empno 雇员编号, ename 雇员姓名, hiredate 雇佣日期,

decode(job, 'clerk','业务员','salesman','销售人员','manager','经理','analyst','分析师','president','总裁') 职位

from emp ;

***********************************************************************************************

SUMMARY

1,oracle主要用户:

超级管理员:sys/change_on_install

普通管理员:system/manage

普通用户:scott/tiger

2,sqlplusw的一些常用命令:

设置行显示数量:set linesize 300

设置页显示数量:set pagesize 30

ed a.sql以及@a

连接:conn  user/passwd  as sysdba

3,SQL基础语法的格式

4,单行函数,decode()函数最重要

Oracle学习系列2

SQL语法练习:

1,选择部门30中的所有员工

select * from emp

where deptno=30;

2,列出办事员的姓名,编号和部门编号

select ename, empno, deptno

from emp

where job=upper('clerk');

3,找出佣金高于薪金的员工

select  *

from emp

where comm>sal ;

4,找出佣金高于薪金的60%的员工

select *

from emp

where comm > sal * 0.6;

5,找出部门10中所有经理和部门20中所有办事员的详细资料

select *

from emp

where (deptno=10 and job='manager') or ( deptno=20 and job='clerk' );

6,找出部门10中所有经理,部门20中所有办事员,既不是经理也不是办事员但其薪金大于或等于2000的所有员工 的详细资料

select *

from emp

where (deptno=20 and job='manager')or(deptno=20 and job='clerk')

or (job NOT IN('manager', 'clerk') and sal >=2000) ;

7,找出收取佣金的员工的不同工作

select distinct job

from emp

where comm IS NOT NULL;

8,找出不收取佣金或者收取的佣金低于100的员工

select *

from emp

where (comm is null ) or (comm<100);

9,找出各月倒数第三天受雇的所有员工

select *

from emp

where last_day(hiredate)-2 =hiredate ;

10,找出早于12年前受雇的员工

select *

from emp

where (months_between(sysdate, hiredate) /12 ) > 12 ;

11,以首字母大学的方式显示所有员工的姓名

select initcap(ename)

from emp;

12,显示正好为5个字符的员工的姓名

select ename

from emp

where length(ename)=5 ;

13,显示不带’R‘的员工的姓名

select ename

from emp

where ename not like '%R%' ;

14,显示所有员工姓名的前三个字符

select substr(ename,0,3)

from emp ;

15,显示所有员工姓名,用’a‘替换所有的’A‘

select replace(ename, 'A','a')

from emp ;

16,显示满10年服务年限的员工的姓名和受雇日期

select ename ,hiredate

from emp

where (months_between(sysdate, hiredate) /12 ) > 10 ;

17,显示员工的详细信息,按姓名排序

select *

from emp

order by ename;

18,显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面

select ename, hiredate

from emp

order by hiredate;

19,显示所有员工的姓名,工作和薪金,按工作的降序排序,若工作相同则按薪金排序

select ename , job, sal

from emp

where job desc, sal ;

20,显示所有员工的姓名,加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面

select ename,to_char(hiredate,'yyyy') year, to_char(hiredate,'yy') months

from emp

order by months ,year ;

21,显示在一个月为30天的情况的所有员工的日薪金,忽略余数

select ename ,round(sal/30)

from emp

22,找出在任何年份的2月受聘的所有员工

select *

from emp

where  to_char(hiredate,'mm') = 2 ;

23,对于每个员工,显示其加入公司的天数

select ename, round(sysdate-hiredate) days

from emp ;

24,显示姓名字段的任何位置包含’A‘的所有员工的姓名

select *

from emp

where ename like '%A%' ;

25,以年月日的方式显示所有员工的服务年限。

select ename ,trunc( months_between( sysdate , hiredate )/12 ) years,

trunc( mod( months_between( sysdate,hiredate ),12 ) ) months,

trunc( mod( (sysdate-hiredate),30 ) )days

from emp ;

Oracle学习系列3

************************************************************************************

多表查询:

1,SQL1999语法对多表查询的支持

2,分组统计及统计函数的使用

3,子查询,并结合多表查询,分组统计做复杂查询

4,数据库的更新操作

5,事务处理和数据库死锁

************************************************************************************

多表查询:

语法:

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

同时查询emp表和dept表:

select *

from emp, dept ;   //产生笛卡尔积

加入where语句:

select *

from emp  e,dept  d

where e.deptno = d.deptno;

要求查询出雇员的编号,姓名,部门编号,部门名称,部门位置:

select e.empno, e.ename, d.deptno, d.dname, d.loc

from emp e, dept d

where e.deptno=d.deptno;

要求查询出每个雇员的姓名,工作,雇员的直接上级领导的姓名:

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 ;

要求查询出每个雇员的姓名,工资,部门名称,工资在公司的等级,及其领导的姓名及工资所在公司的等级:

select e.ename, e.sal, d.dname, s.grade, m.ename, m.sal, ms.grade

from emp e, dept d, salgrade s, emp m, salgrade ms

where e.deptno=d.deptno and (e.sal between s.losal and s.hisal)

and e.mgr=m.empno     and ( m.sal between ms.losal and ms.hisal);

进一步:按照下面样式显示工资等级:

1:第五等工资

2:第四等工资

3:第三等工资

4:第二等工资

5:第一等工资

此时肯定使用decode()函数:

select e.ename, e.sal, d.dname, DECODE(s.grade, 1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',1,'第一等工资'),

m.ename,m.sal,DECODE(ms.grade, 1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',1,'第一等工资'),

from emp e, dept d, salgrade s, emp m, salgrade ms

where e.deptno=d.deptno and (e.sal between s.losal and s.hisal)

and e.mgr=m.empno

and ( m.sal between ms.losal and ms.hisal);

************************************************************************************

左、右连接<重点>

(+)=    -->右连接,

=(+)   -->左连接,  默认

select e.empno, e.ename, d.deptno, d.dname, d.loc

from emp e, dept d

where e.deptno(+)=d.deptno; //表示以dept表为准  。右连接

************************************************************************************

SQL:1999语法对SQL的支持<了解>

交叉连接(cross join):  <产生笛卡尔积>

select * from emp corss join dept ;

自然连接(natural join):<自动进行关联字段的匹配>

select * from emp natural join dept ;

USING子句:<直接指定关联的操作列>

select *

from emp e join dept d USING(deptno)

where deptno=30;

ON子句:<用户自定义连接条件>

select *

from emp e join dept d  ON(e.deptno=d.deptno)

where e.deptno=30 ;

左连接(left join),右连接(right join):

select e.ename, d.deptno, d.dname, d.loc

from emp e right outer join dept d

on(e.deptno=d.deptno) ;

************************************************************************************

组函数和分组统计:<重点>

count(): //求出全部记录数

max(): // 求出一组中最大值

min(): //最小值

avg(): //平均值

sum():      //求和

count()函数:

select count(emp) from emp ;  //查询多少行记录

max(),min()函数:求出所有员工的最低工资

select max(sal) ,min(sal) , sum(sal) from emp ;

分组统计<group by>:

语法:

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件}

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

求出每个部门的雇员数量,可定按照部门编号划分:

select deptno, count(empno)

from emp

group by deptno ;

求出每个部门的平均工资:

select deptno, avg(sal)

from emp

group by deptno ;

---------------------------------------------------------------------------------

select deptno, count(empno) from emp ;// error:不是单组分组函数

/**

若程序使用了分组函数,条件如下;

1.程序中存在group by ,并且指定了分组条件,这样可以将分组条件一起查询出来

2.若不用分组的话,则只能单独使用分组函数

3.在使用分组函数时,不能出现分组函数和分组条件(group by )之外的字段

|

*/     |

|

^

select deptno ,empno, count(empno)   //error:empno不是group by 的表达式

from emp

group by deptno ;

按部门分组,并显示部门的名称,及每个部门的员工数:

select d.dname, count(e.empno)

from dept d, emp e

where d.deptno = e.deptno

group by d.dname ;

要求显示出平均工资大于2000的部门编号和平均工资:

error: select deptno , avg(sal)

from emp

where avg(sal) >2000    //此处不允许使用分组函数

group by deptno ;

/**

分组函数只能在分组中使用,不允许出现在where语句中,若现在假设要指定分组条件,则只能通过第二种指令:having,此时SQL语法格式:

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

*/

correct: select deptno, avg(sal)

from emp

group by deptno  having avg(sal)>2000 ;

显示非销售人员工作名称以及从事统一工作雇员的月工资的总和,并且满足从事同一工作的雇员的月工资合计大于$5000,输出结果按月工资的合计升序排列:

/**

分组简单的原则:

1,只要一列上存在重复的内容才有可能考虑到分组

2,分组函数可以嵌套使用,但是嵌套使用时不能再出现group by 后的字段:

ex:求出平均工资最高的部门:

error: select  deptno, max(avg(sal))  //不是单组分组函数

from emp

group by deptno;

correct: select max(avg(sal))

from emp

group by deptno ;

--------------------------------------------------------------------------------

分析:1,显示全部非销售人员:job<> 'salesman'

select * from emp  where job <>'salesman' ;

2,按工作分组,同时求出工资的总和:

select job,sum(sal)

from emp

where job<> 'salesman'

group by job ;

3,对分组条件进行限制,工资总和大于500:

select job,sum(sal)

from emp

where job<> 'salesman'

group by job having sum(sal)>5000 ;

4,按升序排序:

select job,sum(sal) su

from emp

where job<> 'salesman'

group by job having sum(sal)>5000

order by su ;

*/

************************************************************************************

子查询:

在一个查询内部还可以包括另一个查询:

分类:

单列子查询:返回的结果是一列的一个内容 (出现几率最高)

单行子查询:返回多个列,有可能为一条完整的记录

多行子查询:返回多个记录

格式:

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 ,

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

)别名x tab3 别名3 ,...

{where 条件s

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

}

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

要求查询出比7654工资高的全部雇员的信息:

分析:

1,7654雇员的工资是多少:

select sal from emp where empno=7654 ;

2,只要是其他工资大于7654编号雇员的工资,则符合条件:

select *

from emp

where sal > (

select sal from emp where empno=7654;  //子查询语句处

) ;

要求查询出工资比7654高,同时与7788从事相同工作的全部雇员信息:

查询出7654雇员的工资:

select sal from emp where empno=7654 ;

与7788从事相同的工作:

select job from emp

where empno = 7788;

综合查找:

select *

from emp

where sal >(

select sal from emp where empno=7654

)and  job =(

select job from emp where empno =7788

) ;

要求查询出工资最低雇员姓名,工作,工资:

最低工资:

select min(sal) from emp ;

查询所有:

select *

from emp

where sal =(

select min(sal) from emp

) ;

要求查询:部门名称,部门员工数,部门的平均工资,部门的最低收入的雇员的姓名:

1,求出每个部门的员工数量,平均工资

select deptno,count(empno) ,avg(sal)

from emp

group by  deptno ;

2,查出部门的名称:

select d.dname, ed.c, ed.a

from dept d, (

select deptno,count(empno) ,avg(sal)

from emp

group by  deptno

) ed

where d.deptno=ed.deptno ;

3,求出最低收入的雇员姓名:

select d.dname, ed.c, ed.a  ,e.ename

from dept d, (

select deptno,count(empno) ,avg(sal),min(sal) min from emp

group by  deptno

) ed ,emp e

where d.deptno=ed.deptno

and e.sal =ed.min ;

//若一个部门中存在两个最低工资的雇员,则该脚本会出现错误

------------------------------------------------------

子查询中的三种操作符:

IN, ANY, ALL

求出每个部门的最低工资的雇员信息:

select *

from emp

where sal IN (  //指定查询范围

select min(sal)

from emp

group by deptno

) ;

=ANY : 与IN功能一样

select *

from emp

where sal =ANY (  //指定查询范围

select min(sal)

from emp

group by deptno

) ;

>ANY:比里面最小的值要大

select *

from emp

where sal >ANY (  //指定查询范围

select min(sal)

from emp

group by deptno

) ;

<ANY :比里面最大的值要小

select *

from emp

where sal <ANY (  //指定查询范围

select min(sal)

from emp

group by deptno

) ;

==========================

>ALL:比最大的值要大

select *

from emp

where sal >ALL (  //指定查询范围

select min(sal)

from emp

group by deptno

) ;

<ALL:比最大的值要小

select *

from emp

where sal <ALL (  //指定查询范围

select min(sal)

from emp

group by deptno

) ;

/**

对于子查询中,还可以进行多列子查询,一个子查询中同时返回多个查询的列

select  *

from emp

where (sal, NVL(comm,-1)) IN(

select sa,NVL(comm, -1)

from emp

where deptno=20

) ;

*/

Oracle学习系列4

************************************************************************************

数据库更新操作:

分类:

查询操作:select

更新操作:insert ,update , delete

为了保存原始的emp表的信息,在进行增删改之前备份词表:

create table emp_bak as select * from emp ; //将表emp结构和数据完整的复制出来

添加数据:

insert into table ( [ col1,col2,col3,. . .] )

values( 值1,值2,值3,...) ;

ex:

insert into emp(enpno, ename, job, mgr, hiredate, sal, comm, deptno)

values (7899,'kevin_dfg','captian',7369,'14-2月-95',100000,300,40) ;

select * from emp;//查询记录是否添加

ex:插入新雇员,无领导,无奖金:

insert into emp(enpno, ename, job, hiredate, sal, deptno)

values (7879,'Dustin_fg','captian','14-2月-95',10000,40) ;

ex: 使用to_date()函数将字符串类型的数据变为date类型的数据

insert into emp(enpno, ename, job, mgr, hiredate, sal, comm, deptno)

values (7899,'kevin_dfg','captian',7369,to_date('2016-04-05','yyyy-mm-dd '),100000,300,40) ;

更新数据:

update table

set  字段1=值1 , 字段2=值2 ,... ;//全部修改

update table

set  字段1=值1 , 字段2=值2 ,...

where 修改条件 ;//局部修改(重点推荐)

ex: update emp

set comm=1000

where empno=7899 ;

ex: update emp

set mgr=null

where empno=7899 ;

ex:将7399,8899,7788的领导及奖金取消:

update emp

set mgr=null, comm=null

where empno IN( 7399,8899,7788 ) ;

删除数据:

delete from table   ;//全部删除

delete from table

where  条件;   //局部删除

ex: delete from emp

where empno =7899;

delete from emp

where emp IN( 7399,8899,7788 ) ;

where comm is not null ;

************************************************************************************

数据库的事务处理:

事务处理:保证数据的完整性,具有ACID特性

创建一张包含10部门的临时表:

create table emp10

as select * from emp

where deptno=10 ;

delete from emp10

where empno=7772 ;

/**

在oracle中,每个连接到数据库上的用户都表示创建了一个session,一个session队数据库所做的修改不会立刻反映到数据库的真实数据之上,当session提交所有的操作之后,数据库才真正做出修改。

提交事务:commit

回滚事务:rollback

>>>>>>>>若事务已经提交了,则肯定无法回滚<<<<<<<<<<

*/

************************************************************************************

死锁:

一个session更新了数据库中的记录,其他session事无法立刻更新的,要等待对方提交后才允许更新

SQL语法练习:

1,列出至少有一个员工的所有部门:

a,列出所有部门的员工数量

select deptno ,count(empno)

from emp

group by deptno ;

b,列出部门人数大于1的所有部门编号

select deptno ,count(empno)

from emp

group by deptno  having count(empno)>1 ;

c , 通过部门表,查询出部门的信息即可:

select d.*, ed.cou

from emp d, (

select deptno,count(empno) cou

from emp

group by deptno having count(empno) >1

) ed

where d.deptno=ed.deptno ;

2,列出新金比Smith多的所有员工:

a,先求出smith的工资:

select sal from emp where ename ='SMITH';

b,以上面的结果为条件,查询所有符合条件的雇员信息

select * from emp

where sal > (

select sal from emp where ename ='SMITH';

) ;

3,列出所有员工的姓名及其直接上级的姓名:

select e.ename, m.ename

from emp e, emp m

where e.mgr=m.empno(+);

4,列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称:

a,查找mgr=empno的同时还要比较hiredate,先查询编号,姓名

select e.empno, e.ename

from emp e, emp n

where e.mgr=m.empno and e.hiredate < m.hiredate ;

b, 查询部门编号

select e.empno, e.ename ,d.dname

from emp e, emp n, dept d

where e.mgr=m.empno and e.hiredate < m.hiredate

and e.deptno=d.deptno;

5,列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门:

select d.deptno, d.dname, e.empno, e.ename

from dept d, emp e

where d.deptno=e.deptno(+);

6,列出所有办事员的姓名及其部门名称,部门人数:

select e.ename, d.dname, ed.cou

from emp e, dept d, (

select deptno, count(empno) cou

from emp

group by deptno

) ed

where job='CLERK' and e.deptno=d.deptno and ed.deptno=e.deptno;

7,列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数:

select e.job, count(e.empno)

from emp e

where e.job in(

select job

from emp

group by job having min(sal)>1500

)

group by e.job ;

8,列出部门销售部工作的员工的姓名,假定不知道销售部的部门编号:

select ename

from emp

where deptno=(

select deptno from dept

where dname='SALES'

) ;

9,列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级:

select e.empno, e.ename, s.grade, m.empno, m.ename, d.deptno, d.dname, d.loc

from emp e, dept d, emp m, salgrade s

where e.sal>(

select avg(sal) from emp

)and e.deptno=d.deptno

and e.mgr=m.empno(+)

and e.sal between s.losal and s.hisal ;

10,列出与“SCOTT”从事相同工作的所有员工及部门名称:

select e.empno, e.ename, e.job, e.sal, d.dname, d.loc

from emp e, dept d

where job =(select job from emp where ename='SCOTT')

and ename !='SCOTT'

and e.deptno=deptno ;

11,列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金:

select ename,sal

from emp

where sal IN(

select sal from emp where deptno=30

)

and deptno !=30 ;

12,列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称:

select e.ename, e.sal, d.dname, d.loc

from emp e, dept d

where e.sal >ALL(

select sal from emp where deptno=30

)

and e.deptno!=30 and e.deptno = d.deptno ;

13,列出每个部门工作的员工数量,平均工资和平均服务期限:

select d.dname, count(e.empno), avg(e.sal), avg(months_between(sysdate,e.hiredate)/12) years

from emp e, dept d

where e.deptno=d.deptno

group by d.dname ;

14,列出所有员工的姓名,部门名称和工资:

select e.ename, d.dname , e.sal

from emp e,dept d

where e.detpno=d.deptno ;

15,列出所有部门的详细信息和部门人数:

select d.* ,nvl(ed.cou ,0)

from dept d,(

select deptno dno,count(empno) cou from emp group by deptno

) ed

where d.deptno=ed.dno (+);

16,列出各种工作的最低工资及从事此工作的雇员姓名:

select * from emp

where sal IN (

select min(sal) from emp group by job

)  ;

17,列出各个部门的manager的最低薪金:

select deptno ,min(sal)

from emp

where job='MANAGER'

group by deptno ;

18,列出所有员工的年工资,按年薪升序排序:

select ename, ( sal+NVL(comm,0))*12 income

from emp

order by income ;

19,查出某个员工的上级主管,并要求这些主管中的年薪不超过3000:

select distinct m.*

from emp e , emp m

where e.mgr=m.empno and m.sal >3000 ;

20,求出部门名称中带“S“字符的部门员工的,工资合计,部门人数:

select deptno, sum(sal), count(empno)

from emp

where deptno in(

select deptno from dept where dname like '%S%'

)

group by deptno ;

21,给任职日期超过10年的人加薪10%:

update emp

set sal=sal*0.1

where months_between(sysdate,hiredate)/12 >10 ;

--------------------------------

commit ;

************************************************************************************

SUMMARY

1,多表查询,注意产生笛卡尔积

2,分组统计,所有统计函数只能在分组语句中使用

3,子查询:子查询可以在任意的位置上编写,多表查询,子查询,分组统计一起完成复杂查询

4,数据库的增删改

5,了解一下事务的处理方式,commit,rollback

Oracle学习系列5

************************************************************************************

1,掌握表的建立与删除

了解Oracle中的主要数据类型

2,约束的分类和使用

3,表的关联:交,并,补

4,掌握ROWNUM伪列的作用

5,掌握序列的使用

6,掌握视图的作用

7,了解同义词,用户管理,权限分配与撤销,数据库的备份

------------------------------------------------------------------

创建和管理表(重点):

常用的数据类型:

number[int ,float], varchar ,date ,clob, blob,

表的建立:

create  table tab_name(

col1    type   [defalut 默认值],

col2    type   [default 默认值],

...

coln    type    [default 默认值]

) ;

create table  tab_name as (子查询)  ;//赋值表

create table  tab_name as (select * from emp  where 1=2) ;//仅复制表结构

ex:创建表person

create table person(

UID  varchar(18),

name  varchar(200),

age   number(3),

birthday  date,

sex     varchar(2)  default '男'

) ;

ex:向表中增加数据:

insert into person(UID,name,age,birthday,sex)

values('111111111111111111','keivn',30,to_date('1994-04-09','yyyy-mm-dd'),'女');

ex:表的删除:

drop table tab_name ;

eg:drop table person;

ex:修改表结构:增加列,修改列

alter  table tab_name  add (col1 type default 默认值,

col2 type default 默认值,

...

coln type default 默认值

) ;  //增加列

alter table person add (address  varchar(200)  default '暂无地址') ;

alter table tab_name  modify (col1 type default 默认值,

col2 type default 默认值,

...

coln type default 默认值

) ;  //修改列

alter table person modify (name varchar2(20) default '无名氏');

/**

若数据库中对应字段里有一个很长的数据,则无法将表长度缩小

*/

ex:为表重命名

在oracle中提供了rename命令,对表进行重新命名。

rename  tab_name_old   to   tab_name_new ;

ex:

rename person to tperson ;

ex:截断表

若要清空一张表的数据,同时不需要回滚,可以立即释放资源就要使用

截断表的语法:

truncate  table tab_name ;

ex: truncate table tperson ;  //清空表数据

rollback ;     //使用rollback无效

************************************************************************************

思考题:

现有一张国家表,里面有字段:中国,美国,巴西,荷兰,

现要求通过查询实现对战功能:

中国 ----> 美国

中国 ----> 巴西

中国 ----> 荷兰

美国 ----> 中国

美国 ----> 巴西

美国 ----> 荷兰

问:此程序该如何实现?

分析:使用笛卡尔积完成

create table national(

name varchar(30)

) ;

insert into national(name)

values('中国') ;

insert into national(name)

values('美国') ;

insert into national(name)

values('巴西') ;

insert into national(name)

values('荷兰') ;

select t1.name ,t2.name

from national t1, national t2

where t1.name <>t2.name ;

************************************************************************************

约束(重点):

分类:

1,主键约束 -->是唯一的表示,本身不能为空,如身份证

2,唯一约束 -->在一个表中只能建立一个主键约束,其他列不想出现重复值可使用唯一性约束

3,非空约束 -->列的内容不能为空

4,检查约束 -->检查一个列的内容是否合法

5,外键约束 -->在两张表中进行的约束操作

---------------------------------------------------------------------------

1,主键约束:

create table person(

UID  varchar(18)  primay key,     //主键约束

name  varchar(200),

age   number(3),

birthday  date,

sex     varchar(2)  default '男'

) ;

/**

create table person(

UID  varchar(18)

name   varchar(200),

age      number(3),

birthday  date,

sex       varchar(2)  default '男',

constraint person_uid_pk primary key(uid)  //指定uid主键约束名称

) ;

*/

---------------------------------------------------------------------------

2,唯一约束 :unique

表示一个字段中的内容是唯一的,其他列不允许重复

create table person(

UID  varchar(18)

name   varchar(200)  unique  not null,  //非空约束,唯一约束

age      number(3) not null,      //非空约束

birthday  date,

sex       varchar(2)  default '男',

constraint person_uid_pk primary key(uid)  //指定uid主键约束名称

<constraint person_name_uk unique(name)  >  //指定name唯一约束名称

) ;

---------------------------------------------------------------------------

3,非空约束: not null

表示一个字段的内容不允许为空,即插入数据时必须插入该字段的值

create table person(

UID  varchar(18)

name   varchar(200)  not null,  //非空约束

age      number(3) not null,      //非空约束

birthday  date,

sex       varchar(2)  default '男',

constraint person_uid_pk primary key(uid)  //指定uid主键约束名称

<constraint person_name_nk not null(name)  >  //指定name非空约束名称

) ;

---------------------------------------------------------------------------

4,检查约束

create table person(

UID  varchar(18)

name   varchar(200)  not null,  //非空约束

age      number(3) not null  check( age between 0 and 150),   //非空约束,检查约束

birthday  date,

sex       varchar(2)  default '男' check(sex in('男','女',‘中’)), //检查约束

constraint person_uid_pk primary key(uid)  //指定uid主键约束名称

<constraint person_name_ck check(name)  >  //指定name检查约束名称

<constraint person_sex_ck check(sex)  >  //指定sex检查约束名称

) ;

---------------------------------------------------------------------------

5,外键约束(foreign key):

create table person(

UID  varchar(18)

name   varchar(200)  not null,  //非空约束

age      number(3) not null  check( age between 0 and 150),   //非空约束,检查约束

birthday  date,

sex       varchar(2)  default '男' check(sex in('男','女',‘中’)), //检查约束

constraint person_uid_pk primary key(uid)  //指定uid主键约束名称

<constraint person_name_ck check(name)  >  //指定name检查约束名称

<constraint person_sex_ck check(sex)  >  //指定sex检查约束名称

) ;//创建person表

create table book(

bid    number  primary key not null,

bname   varchar(30),

bprice    number(5,2)

uid     varchar(18)

constraint person_book_uid_fk  foreign key(uid)  references person(uid)  //外键约束

);//创建book表

/**

1,在子表book中设置的外键在父表person中必须是主键

2,删除时,先删除子表book,再删除父表person:

drop table book;

drop table person ;

*/

ex:插入测试数据:

insert into person(UID,name,age,birthday,sex)

values('111111111111111111','keivn',30,to_date('1994-04-09','yyyy-mm-dd'),'女');

insert into book (bid,bname,bprice)

values(1,'java',89.8,'1111111111111111111');

级联删除:

create table book(

bid    number  primary key not null,

bname   varchar(30),

bprice    number(5,2)

uid     varchar(18)

constraint person_book_uid_fk  foreign key(uid)  references person(uid) on delete cascade  //外键约束,级联删除

);//创建book表

delete from person where pid ='1111111111111'; //删除一条记录,同时删除子表book中的记录

Oracle学习系列6

************************************************************************************

删除约束(重点):

格式:

alter table person drop constraint constraint_name ;

创建person表:

create table person(

UID  varchar(18)

name   varchar(200)  not null,  //非空约束

age      number(3) not null,      //非空约束

birthday  date,

sex       varchar(2)  default '男',

) ;

/**

主键约束:主键字段_PK

唯一约束:字段_UK

检查约束:字段_CK

外键约束:父字段_子字段_FK

*/

ex:为表添加若干个约束,

格式:

alter table tab_name add constraint constraint_name   constraint_type(constrain_colx)  ;

ex:添加约束

alter table person add constraint person_uid_PK primary key(uid);//增加主键约束

alter talbe person add constraint person_name_UK unique(uid); //增加唯一约束

alter table person add constraint person_age_CK checke(age between 0 and 150) ;

alter table person add constraint person_sex_CK check(sex in ('男','女','中')) ;

ex:删除约束

alter table person drop constraint person_uid_PK ;//删除主键约束

alter talbe person drop constraint person_name_UK ; //删除唯一约束

alter table person drop constraint person_age_CK  ; //删除检查约束

alter table person drop constraint person_sex_CK  ; //删除检查约束

************************************************************************************

rownum(重点):

rownum: 表示行号,手机上是一个伪列,可在每张表中出现

select rownum, empno, ename ,job,sal,hiredate

from emp ;  //  ROWNUM 采用自动编号的方式呈现

ex:

select rownum, empno, ename, job, sal hiredate

from emp

where rownum <=5 ;

************************************************************************************

建表、约束、查询综合练习:

题目背景:

有某个学生运动会比赛信息的数据库,保存了如下的表:

运动员sporter(运动员编号sporterid, 运动员姓名name, 运动员性别sex, 所属系好department)

项目item (项目编号itemid, 项目名称itemname, 项目比赛地点location)

成绩grade(运动员编号sportid,项目编号itemid, 积分mark)

1建表要求:

a,定义各个表的主键外码约束

b,运动员的姓名和所属系别不能为空值

c,积分要么为null,要么为6,4,2,0,分别代表第一二三和其他名词的积分

create table sporter(

sporterid       nummber(4)  primary key  not null,

name        varchar2(50)  not null,

sex varchar2(2)  not null,

department varchar2(30)  not null,

constraint sporter_sex_CK  check(sex in('男','女'))

);

create table item(

itemid varchar2(4)  primary key not null,

itemname varchar2(50)  not null,

location varchar2(50)  not null

);

create table grade(

sporterid number(4),

itemid varchar2(4),

mark number(2),

constraint sporter_grade_sporterid_FK  foreign key(sporterid)

references sporter(sporterid)  on delete cascade

constraint item_grade_itemid_FK  foreign key(itemid)

references item(itemid)  on delete cascade

constraint grade_mark_CK check(mark in (6,4,2,0))

);

查看错误:show error

测试数据:

insert into  sporter(sportid, name, sex, department )

values(1101,'黎明','男','计算机系');

insert into  sporter(sportid, name, sex, department )

values(1102,'张三','男','数学系系');

insert into  sporter(sportid, name, sex, department )

values(1103,'李四','男','计算机系');

insert into  sporter(sportid, name, sex, department )

values(1104,'王五','男','物理系');

insert into  sporter(sportid, name, sex, department )

values(1105,'李楠','女','心理系');

insert into  sporter(sportid, name, sex, department )

values(1106,'孙俪','女','艺术系');

---------------------------------------------------------

insert into item(itemid ,itemname, location)

values('x001','男子五千米','一操场') ;

insert into item(itemid ,itemname, location)

values('x002','男子标枪','一操场') ;

insert into item(itemid ,itemname, location)

values('x003','男子跳远','二操场') ;

insert into item(itemid ,itemname, location)

values('x004','女子跳高','二操场') ;

insert into item(itemid ,itemname, location)

values('x005','女子三千米','三操场') ;

---------------------------------------------------------

insert into grade(sporterid, itemid,mark)

values(1001,'x001',6);

insert into grade(sporterid, itemid,mark)

values(1002,'x001',4);

insert into grade(sporterid, itemid,mark)

values(1003,'x001',2);

insert into grade(sporterid, itemid,mark)

values(1004,'x003',2);

insert into grade(sporterid, itemid,mark)

values(1005,'x006',6);

insert into grade(sporterid, itemid,mark)

values(1006,'x004',0);

insert into grade(sporterid, itemid,mark)

values(1003,'x005',2);

insert into grade(sporterid, itemid,mark)

values(1003,'x003',4);

insert into grade(sporterid, itemid,mark)

values(1003,'x002',6);

要求:

求出目前总积分最高的系名,及其积分:

select * from (

select s.department , sum(g.mark) sum

from  sporter s, grade g

where s.sporterid=g.sporterid

group by s.department

order by sum desc

)

where rownum=1

找出在一操场进行比赛的各项目名称及其冠军的姓名:

select i.itemname, s.name, g.mark

from item i, grade g,sporter s

where i.location='一操场'

and i.itemid=g.itemid

and s.sporterid=g.sporterid

and g.mark=6 ;

找出参加了张三所参加过的项目的其他同学的姓名:

select distinct s.name

from sporter s, grade g

where s.sporterid=g.sporterid and s.name <>'张三'

and g.itemid IN (

select g.itemid from sporter s ,grade g

where s.sporterid=g.sporterid

and s.name='张三'

) ;

经查张三使用了违禁药品,其成绩都记0分,请在数据库中做出相依修改:

update grade

set mark=0

where sporterid =(

select sportid from sporter where name ='张三'

) ;

经组委会协商,需要删除女子跳高比赛项目:

delete from item

where itemname='女子跳高' ;

------------------------------------------------------------

删除顺序:    //先删子表,再删主表

drop table grade;

drop table sporter;

drop table item ;

************************************************************************************

集合操作:

分类:

并(UNION) :将多个查询的结果组合到一个查询结果中,无重复值 //UNIONALL:包含重复值

交(INTERSECT):返回多个查询结果相同的部分

差(MINUS) :返回两个结果的差集

复制emp表,将部门20的雇员信息取出来:

create table emp20

as select * from emp

where deptno=20 ;

验证UNION:

select * from emp

union

select * from emp20 ;

验证UNIONALL:

select * from emp

unionall

select * from emp20 ;

验证INTERSECT:

select * from emp

INTERSECT

select * from emp20 ;

验证MINUS:

select * from emp

minus

select * from emp20 ;

----------------------------------------------------------------------------------

SQL查询最终格式:

////////////////////////////////////////////////////////////////////////////////////////////

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 ,

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

)别名x tab3 别名3 ,...

{where 条件s

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

}

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

{UNION | INTERSECT |MINUS}

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 ,

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

)别名x tab3 别名3 ,...

{where 条件s

select { distinct } * | col1 别名1   col2 别名2 ...

from  tab1 别名1 , tab2 别名2 , tab3 别名3 ,...

{where 条件s }

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

}

{group by 分组条件   { having  分组条件 }  }

{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;

////////////////////////////////////////////////////////////////////////////////////////////

Oracle学习系列7

************************************************************************************

关联表的约束:

强制删除关联表中的父表:

drop table tab_name cascade constraint ;

约束本身是可以修改的,但是不建议修改约束

知识点:

1,掌握视图的作用及定义

2,掌握序列的使用:SEQUENCE

3,掌握PowerDesigner设计工具的使用

4,了解同义词,了解用户管理,了解嵌套表及可变数组

5,理解数据库的设计范式

--------------------------------------------------------

1,视图:

封装了一条复杂的查询语句

语法:

create view view_name

as 子查询

{with check option | with read only } ; //不能更新视图的创建条件,不能更改视图数据

建立一个视图,此视图包含了全部部门20的信息:

create view view_emp20

as

select  empno,ename, job,hiredate

from emp

where deptno=20 ;

查询视图:

sele * from view_emp20 ;

删除视图:

drop view view_name ;

ex:

drop view view_emp20 ;

完整语法格式:

create or replace view  view_name

as 子查询   //系统会为用户自动进行删除及重建的功能

ex:

create or replace view view_emp20

as

select d.dname, count(e.empno), avg(e.sal), avg(months_between(sysdate,e.hiredate)/12) years

from emp e, dept d

where e.deptno=d.deptno

group by d.dname ;

更新视图:

修改视图中7369的部门编号:

update view_emp20

set deptno=30

where empno=7369; //提示更新成功,但是视图表中无编号7369的雇员

--------------------------------------------------------

序列(重点)

在oracle完成自动序列增长的功能,则只能依靠序列完成

格式:

create sequence  seq_name

[increment by n] [start with n]

[{maxvalue n |nomaxvalue}]

[{minvalue n | nominvalue }]

[{cycle | nocycle}]

[{cache n | nocache }];

删除:

drop sequence seq_name

ex:创建一个myseq的序列,验证自动增长的操作:

create sequence myseq  increment by 2;

在序列中提供两种操作:

nextVal:取得序列的下一个内容

currVal:取得序列的当前内容

创建表test_seq:

create table test_seq(

curr number,

next number

);

使用序列:

insert into test_seq(curr,next)

values(myseq.currval,myseq.nextVal)  ;

--------------------------------------------------------

同义词(了解):

功能:可以让其他用户通过一个名称方便的访问‘user.table_name’.

语法:

create  synonym  syn_name  for  user.tab_name  ; //建立

drop  synonym syn_name ;   //删除

ex: create synonym   dual  for sys.dual ;

------------------------------------------

select  sysdate from dual ;//dual是张虚拟表

conn sys/change_on_install as sysdba ;

select * from tab

where TNAME='DUAL';//在sys用户下存在此表

--------------------------------------------------------

用户管理(了解):

语法:

create user user_name  identified by passwd //创建用户(sys用户权限)

[default tablespace  default_tablespace]

[temporary tablespace  temporary_tablespace ]

grant 权限1,权限2,权限3.. to user ;//给用户授权   create session

alter user user_name identified by passwd_new ; //修改用户密码

eg:

create user kevin  identified by root ;

grant  conncet, resource to kevin ;

角色:connect 、resource   //每个角色有好多不同的权限

解/锁住用户:

alter user user_name  account  lock /unlock;

eg:

alter user kevin account lock /unlock ;

授权emp表:

grant 权限1,权限2  on   user_name.tabl_name  to user_name ;

将scott用户下的emp表的查询权限及删除权限给kevin:

grant select ,delete on scott.emp to kevin;

回收权限:

revoke 权限1,权限2,.. on  user.tab_name  from user_name ;

eg:

revoke select ,delete on scott.emp from kevin ;

grant/(revoke)  权限1,权限2,权限3   on  user.tab_name  to / (from)  user_name ;

--------------------------------------------------------

数据库的备份与恢复(了解):

数据库备份: exp

D:\data>exp

数据库恢复: imp

D:\data>impf

查看错误:show error

--------------------------------------------------------

嵌套表(了解):

在一个表中包含另外一个子表

//创建project_ty类型

create type project_ty as object(

proid number(4),

proname  varchar(50),

prodate date

) ;

/                 //最后一个‘/' 不可少

//使用porject_nt类型

create type project_nt as table of project_ty;

/                    //最后一个‘/' 不可少

create table department(

deptno  number(2) primary key not null,

dname   varchar2(50) not null,

projects   project_nt

) nested table projects store  as project_nt_tab_temp ;

对于插入数据来讲,需要指定每个project_ty的数据类型

insert into department(deptno,dname,projects)

values(

1,'技术部',

project_nt(

project_ty(1001,'erp',sysdate),

project_ty(1002,'crm',sysdate),

project_ty(1003,'oa',sysdate),

)

);

查询:

select * from department ;

若此时需要查看一个部门的全部项目的话,则需要查询嵌套表:

select * from table( select projects from department where deptno=1 ) ;

更新:

update table (select projects from department where deptno=1 ) pro

set values(pro )=project_ty('1001','测试项目',to_date('2016-02-12','yyyy-mm-dd'))

where pro.proid=1001 ;

--------------------------------------------------------

可变数组(了解):

属于嵌套表的升级版,在可变数组中手机上就是将内部的嵌套表的内容的长度进行了限制。

//定义类型

create type worker_info as object(

id number,

name varchar2(50),

sex varchar2(6)

);

/

//定义数组类型

create type worker_info_list as varray(10) of worker_info ;

/

//创建可变数组表

create table department(

deptno number(2) primary key not null,

dname varchar2(50) not null,

workers worker_info_list

);

//插入数据

insert into department(deptno,dname,workers)

values( 20,'后勤部',

worker_info_list(

worker_info(1,'dustin','F'),

worker_info(2,'kevin','F'),

worker_info(3,'allen','M')

)

);

--------------------------------------------------------

数据库设计范式(了解):

1.第一范式(确保每列保持原子性)

2.第二范式(确保表中的每列都和主键相关)

3.第三范式(确保每列都和主键列直接相关,而不是间接相关)

--------------------------------------------------------

数据库设计工具(重点):

powerDesigner工具的使用