Oracle一些基础语法(四)

2024年 6月 30日 57.8k 0

1、Oracle

1.1、多表查询

1) 多表查询的基本语法

前面的查询都是针对一张表的操作,如果在查询的时候涉及到多张表,那么就称为多表查询,夺标查询的语法如下:

select *|具体的列名 from 表名称1,表名称2 where 条件表达式 order by 排序字段 asc|desc;

下面看一个例子,对emp表和dept表进行查询

select * from emp,dept;

如此多的数据,我们要向知道当前的记录总数,如何操作呢?

select count(*) from emp,dept;--56

select count(*) from emp;--14

select count(*) from emp;--4

此处查询使用count(*|具体的列名)查询总记录数

上面的三条查询语句分别得到了多表查询,单表查询的总记录数,很明显的看到,多表查询的记录数56并不等于单表查询的总记录数之和18,怎么回事呢?因为,在进行多表查询时,会产生笛卡尔积,如果表的数据越多,那么笛卡尔积就会越大。如果现在有5张表,每张表有10000条数据,查询5张表会产生10000的5次方条数据,所以在实际开发中多表查询不建议过多的使用。

要向去掉笛卡尔积,可以使用关联来实现。现在我们知道emp表中有字段deptno,dept表中有字段deptno,emp.deptno=dept.deptno就是灌篮字段。在多表查询中加入where语句就可以消除笛卡尔积

select * from emp,dept where emp.deptno=dept.deptno;

此时查询的记录数为14条,但是如果表名过长的话,不方便我们使用,所以一般我们会为表名设置别名,

select * from emp e,dept d where e.deptno=d.deptno;

如果在进行多表查询时,最好为表名设置别名

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

select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

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

select e.ename,e.job,m.ename from emp e,emp m where e.mgr=m.empno;

此处查询将emp表做自身的关联

继续扩展之前的程序,要求将雇员素在部门名称同时列出

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 * from salgrade;

在查询出每个雇员的姓名,工资,部门名称和工资等级

select e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

查询其领导的姓名及工资等级

select e.ename,e.sal,m.ename,decode(ms.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),decode(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),d.dname from emp e,emp m,dept d,salgrade s,salgrade ms where e.mgr=m.empno and m.sal between ms.losal and ms.hisal and e.deptno=d.deptno and e.sal between s.losal and s.hisal;

2) 左、右连接

现在我们先查询下dept表中的数据

select * from dept;

可以看到,dept表中包含了4条记录,现在我们将emp表和dept表关联查询,查询一下指定的字段

select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

有查询结果可以看到,部门表中的部门号只出现了3个,因为在雇员表中没有指定40部门的雇员,但是我们现在需要查看两张表关联后的完整信息,该如何进行呢?

select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;

现在的结果中没有指定雇员的部门信息也显示出来了。这里我们使用到了右连接。有如下规律:

(+)在=左边表示右连接,查询时以=右边的表作为标准

(+)在=右边表示左连接,查询时以=左边的表作为标准

在前面我们有过如下操作:查询雇员的编号,姓名及其领导的编号、姓名

select e.empno,e.ename,m.ename from emp e,emp m where e.mgr=m.empno;

但是我们仔细观察会发现,雇员名称为KING的雇员信息没有显示出来,我们知道KING为president,没有领导,所以按照上面的方式是不能查询出来的,修改如下:

select e.empno,e.ename,m.ename from emp e,emp m where e.mgr=m.empno(+);

发现在加入做链接后KING出现了。

3) SQL:1999语法对SQL的支持

SQL:1999语法格式如下:

select table1.column,table2.column from table1

[cross join table2]|

[natural join table2]|

[join table2 using(column_name)]|

[join table2 on(table1.column_name=table2.column_name)]|

[left|right|full outer join table2 on(table1.column_name=table2.column_name)];

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

select * from emp cross join dept;--56

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

select * from emp natural join dept;

相当于

select * from emp,dept where emp.deptno=dept.deptno;

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 d.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);

1.2、组函数及分组统计

什么是分组?

举例吧,把所有男生分为一组,把所有女生分为一组。

如果想要求出每一组的平均身高,评价呢年龄等,就需要使用分组函数。

1) 组函数

在SQL中常用的组函数有以下几个:

count()-->求出全部的记录数

max()-->求出一组数据中的最大值

min()-->求出一组数据中的最小值

avg()-->求出平均值

sum()-->求和

count()函数:

现在我们需要查询出emp中有多少个雇员

select count(*) from emp;--14

max()、min(),求最大最小值,一般是针对数字的应用

求出所有雇员的最低工资

select min(sal) from emp;

求出所有雇员的最高工资

select max(sal) from emp;

sum()、avg(),求和,求平均值

求出部门20中的总工资

select sum(sal) from emp where deptno=20;

求出所有雇员的平均工资

select avg(sal) from emp;

2) 分组统计

要想使用分组统计,则首先应该固定其语法,使用group by进行分组,此时SQL语法格式如下:

select *|具体的列

from 表名称1

where 条件表达式

group by 分组条件

order by 排序字段 asc|desc

求出每个部门的雇员数量,这里需要按照部门编号划分,及按照deptno分组

select deptno,count(empno) from emp group by deptno;

求出每个部门的平均工资

select deptno,avg(sal)

from emp

group by deptno;

现在,我们观察下面的一行代码:

select deptno,count(empno) from emp;

以上代码不能正确执行,报错为:不是单组分组函数,为什么呢?

如果程序中使用了分组函数,则有两种可以使用的情况:

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

2-如果不使用分组的话,则只能单独的使用分组函数

在使用分组函数的时候,不能出现分组函数和分组条件之外的字段。

看下面的代码:

select deptno,empno,count(empno) from emp group by deptno;

程序会报错,提示empno不是group by表达式,因为在这里,我们使用了组函数count(empno),group by deptno,根据先前的规则,empno的出现是不合法的。

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

select d.dname,count(e.ename) 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;

程序报错,提示avg(sal)>2000处不允许使用分组函数。因为分组函数只能在分组中使用,不允许出现在where语句之中,那么如果现在假设要指定分组的条件,则只能通过第二种条件的指令,having,此时SQL语法格式为:

select * | 具体的列名

from 表名称

where 条件表达式

group by 分组条件

having 分组条件

order by 排序字段 asc|desc

所以,我们使用having完成上面的操作

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

下面我们看一个这样的需求:显示非销售人员工作名称以及从事同一工作的雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于5000,输出结果按照月工资的合计升序排列

·-首先我们查询出全部的非销售人员,限定条件job'SALESMAN'

select * from emp where job'SALESMAN';

·-按照工作名称分组,并且求出每组的工资总和

select job,sum(sal) from emp

where job'SALESMAN'

group by job;

·-对分组的调价进行限制,工资总和大于5000

select job,sum(sal) from emp

where job'SALESMAN'

group by job

having sum(sal)>5000;

·-对查询结果按照月工资的合计升序排列

select job,sum(sal) from emp

where job'SALESMAN'

group by job

having sum(sal)>5000

order by sum(sal) asc;

下面我们总结下分组的简单原则:

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

使用分组时的注意点:

--分组函数可以嵌套使用,但是在组函数嵌套使用的时候不能再出现分组条件的查询语句

例如,我们要求求出平均工资最高的部门工资

select deptno,max(avg(sal)) from emp

group by deptno;

程序会报错,提示deptno不是单组分组函数

修改代码如下:

select max(avg(sal)) from emp

group by deptno;

1.3、子查询

子查询:在一个查询的内部还包括另外一个查询,则此查询称为子查询,子查询的格式如下:

select * | 具体的列名称

from 表名称

where 条件表达式(

select * | 具体的列名称

from 表名称

where 条件表达式(

...

)

group by 分组条件

having 分组条件

order by 排序字段 asc|desc

)

group by 分组条件

having 分组条件

order by 排序字段 asc|desc

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

·-首先要知道7654雇员的工资是多少

select sal from emp where empno=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 ename,job,sal from emp

where sal=(select min(sal) from emp);

要求查询出:部门名称,部门的员工数,部门的平均工资,部门的最低收入的雇员姓名,此时,程序需要两张表关联:dept、emp

·-如果要想求出每个部门的员工数,平均工资,要使用分组统计,这里我们按照deptno进行分组

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

group by deptno;

·-但是我们要查询的是部门的名称,所以这里需要与dept表进行关联

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

from dept d,

(select deptno,count(empno) c,avg(sal) a from emp

group by deptno) ed

where d.deptno=ed.deptno;

·-求出最低收入的雇员姓名

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

from dept d,(select deptno,count(empno) c,avg(sal) a,min(sal) min from emp

group by deptno) ed,emp e

where d.deptno=ed.deptno and e.sal=ed.min;

但是此程序中有一个问题,如果一个部门中同时存在两个给你工资最低的雇员,则程序会出现错误。

在子查询中,存在以下三种查询的操作符号:

in、any、all

in操作符的作用是指定一个查询的范围

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

分析:每个部门的最低工资,返回值肯定是多个,所以此时可以使用in指定一个操作范围。

select * from emp

where sal in(select min(sal) from emp group by deptno);

any操作符的一般用法:=any(与in操作符的功能完全一样)、>any(比里面最小的值要大)、all(比最大值要大)、all(select min(sal) from emp group by deptno);

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

select * from emp

where (sal,nvl(comm,-1)) in(select sal,nvl(comm,-1) from emp where deptno=20);

1.4、数据库更新操作

数据库的主要操作分为两种:

--数据库的查询操作:select

--数据库的更新操作:insert、update、delete

此时,为了保存原始的emp表的信息,在进行增加、修改、删除操作之前,先将此表复制一份

create table myemp as select * from emp;

1) 添加数据

添加数据的语法是:

insert into 表名称[(字段名1,字段名2,......)] values(值1,值2,......);

为myemp表添加一条新的记录,按照标准的做法完成

insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7899,'张三','清洁工',7369,'14-2月 -1995',9000,300,40);

在添加数据时,需要强调的是:对于数字,不用加单引号,但是字符串必须加上单引号。可以使用简略写法,当我们想全部的阻断插入数据时,可以不用指定需要插入数据的字段名,但是,我们并不推荐这种写法,因为这样写的坏处在于,当我们向部分字段插入数据时,需要和表的字段顺序一一对一个才能插入成功,而使用标准写法时,只需要指定的字段名和value中的值一一对应就可以完成插入操作。

插入部分数据,现在要求插入一个新的雇员,但是此雇员暂时没有领导,也没有奖金,也就是说,在插入数据的时候,mgr和comm字段的值要置空。

第一种做法:不明确写出要插入的字段名 ,没有数据的字段写成null

insert into myemp values(8889,'王五','清洁工',null,'14-2月 -1982',9000,null,40);

第二种做法:明确写出要插入的字段名

insert into myemp(empno,ename,job,hiredate,sal,deptno) values(8889,'王五','清洁工','14-2月 -1982',9000,40);

在上面的插入操作中,我们应该发现了这么一个问题,插入的日期都是按照Oracle默认的日期格式进行书写的,但是,现在有一个“2009-01-19”这样格式的日期,该如何插入到数据库表中呢?我们英爱还记得在前面学习单行函数的时候,介绍了一个叫做to_date()的函数,该函数的功能是将一个字符串类型的数据变为date类型的数据。

insert into myemp(empno,ename,job,hiredate,sal,deptno) values(8888,'赵六','保洁工',to_date('2009-01-19','yyyy-mm-dd'),9000,40);

2) 修改数据

在SQL语法中使用update语句就可以完成数据的修改功能,此语句的语法格式如下:

修改全部:update 表名称 set 要修改的字段=新值,要修改的字段=新值,......;

修改局部:update 表名称 set 要修改的字段=新值,要修改的字段=新值,...... where 修改条件;

但是,从一般的开发角度上讲,我们都在修改数据时加上修改条件

现在将myemp表中的所有雇员的奖金修改为1000-->修改全部

update myemp set comm=1000;

将编号为7899的雇员的工资修改为5000-->指定了更新条件

update myemp set sal=5000 where empno=7899;

将编号为7899的雇员的领导取消

update myemp set mgr=null where empno=7899;

注意点:在进行数据库更新的操作时,一定要写上更新的条件,拒绝批量更新。

将7369、8899、7788的领导及奖金取消

update myemp set mgr=null,comm=null where empno in(7369,7788,8899);

3) 删除数据

在SQL语法中可以使用delete命令删除记录,语法格式如下:

删除全部:delete from 表名称;

删除局部:delete from 表名称 where 删除条件;

删除编号是7899的雇员信息

delete from myemp where empno=7899;

删除编号是8889,7889,8888的雇员信息

delete from myemp where empno in(8889,7889,8888);

删除掉全部领取奖金的雇员

delete from myemp where comm is not null;

删除表的全部内容,此时不需要指定删除条件

delete from myemp;

在实际开发中不建议使用全部删除,在执行删除命令的时候都要指定删除条件。

1.5、事务处理

创建一个只包含10部门雇员信息的临时表

create table emp10 as select * from emp where deptno=10;

删除emp10中7782的雇员信息

delete from emp10 where empno=7782;

当我们再次查询emp10表的数据时,该数据确实删除了,接下来,我们做如下操作,再次开启一个sqlplus窗口,再次查询emp10的数据,我们发现雇员编号为7782的雇员信息仍然存在,这是为什么?

这就是Oracle中事务处理的概念了。

事务处理:所谓事务处理就是保证数据的完整性,所有的操作要么同时成功,要么同时失败。

在Oracle中对于每一个连接到数据库的窗口(sqlplus、sqlplusw)连接之后实际上都会与数据库建立一个session,即:每一个连接到数据库上的用户表示创建了一个session。

一个session对数据库所做的修改,不会立刻反映到数据库的真实数据之上,是允许回滚的,当一个session提交所有的操作之后,数据库才真正的做出了修改。

在数据库的操作中提供了以下两个主要命令完成事物的处理:

--提交事物:commit

--回滚事物:rollback

如果数据已经被提交了则肯定无法回滚,所以,回滚只有在事物未被提交时才有效。

在Oracle中关于事物的处理上夜壶存在一种死锁的概念。

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

下面我们来测试下Oracle的事务处理是如何进行的。

首先,我们在窗口1中查询出emp10的数据

select * from emp10;

现在做一个更新的操作,将编号为7934的雇员的工资更改为3000

update emp10 set sal=3000 where empno=7934;

现在我们再次查询emp10的数据,发现,编号为7934的雇员工资确实更改为3000来 ,但是我们应该知道,对于这个更新操作,我们是没有提交的,我们现在再打开一个窗口,查询emp10的数据,发现编号为7934的雇员工资还是1300,这就验证了我们所说的“事物未提交”,接下来,我们在窗口2中进行一个更新操作,将7839的奖金设置为10000

update emp10 set comm=10000 where empno=7839;

下面我们在窗口1中提交事物

在窗口2中再次查询emp10的数据,更新成功。

在窗口2中提交事物

同样在窗口1中查询emp10的数据,更新成功。

1.6、查询练习

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

select deptno ,count(empno) from emp

group by deptno;

此查询使用了组函数,分组,注意,如果不加分组,该程序会报错

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

select deptno,count(empno) from emp

group by deptno having count(empno)>1;

这里没使用where设置查询限定条件,因为where子句中时不能出现函数的。

通过部门表,查询出部门的信息

select d.*,ed.cou from dept d,(select deptno,count(empno) cou from emp

group by deptno) ed

where d.deptno=ed.deptno;

列出工资比“SMITH”多的所有雇员。

--求出SMITH的工资

select sal from emp where ename='SMITH';

--将上面的结果作为查询条件,只要工资比上面的结果大的就是符合条件的

select * from emp where sal>(select sal from emp where ename='SMITH');

列出所有员工的姓名和其直接上司的姓名

--此程序属于自身关联查询,为了得到KING,我们使用左连接查询,以等号左边的表为标准

select e.ename,m.ename from emp e,emp m

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

列出雇佣日期早于其直接上级的所有雇员的编号、姓名和部门名称

--自身关联,查找mgr=empno的同时还要比较hiredate,我们先查询编号和姓名

select e.empno,e.ename

from emp e,emp m

where e.mgr=m.empno and e.hiredate1500

)

group by e.job;

列出在部门“SALES”工作的员工姓名,假定不知道销售部的部门编号

--通过dept表查询出销售部的部门编号

select deptno from dept where dname='SALES';

--将上面的查询结果作为下一步查询的条件

select ename from emp

where deptno=(

select deptno from dept where dname='SALES'

);

列出工资高于公司平均工资的所愿雇员,所在部门,上级领导,公司的工资等级

--求出公司的平均工资

select avg(sal) from emp;

--列出工资高于平均工资的雇员信息

select * from emp where sal>(select avg(sal) from emp);

--与部门表关联,查出所在部门的信息

select e.*,d.dname,d.loc from emp e,dept d

where sal>(select avg(sal) from emp) and e.deptno=d.deptno;

--要想查出上级领导,需要和自身进行关联查询

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

from emp e,dept d,emp m

where e.sal>(select avg(sal) from emp)

and e.deptno=d.deptno

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

--与工资等级表关联,查出工资等级

select e.empno,e.ename,s.grade,m.ename,m.empno,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;

列出与scott从事相同工作的所有雇员及部门名称

--找到scott的工作

select job from emp where ename='SCOTT';

--找到和上面查询工作相同的雇员

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

--使用dept表查询出所在部门名称

select e.*,d.dname from emp e,dept d

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

and ename!='SCOTT'

and d.deptno=e.deptno;

列出工资等于部门30中雇员的工资的所有雇员姓名和工资

--查出部门30中雇员的工资

select sal from emp where deptno=30;

--找出工资等于上面结果的雇员姓名

select ename,sal from emp

where sal in(select sal from emp where deptno=30)

and deptno!=30;

列出工资高于在30部门工作的所有雇员的工资的雇员姓名和工资,部门名称

--在之前的程序上进行修改,使用>all,比最大的还要大

select ename,sal

from emp where sal>all(

select sal from emp where deptno=30

)

and deptno!=30;

--使用dept表,查询出部门名称

select e.ename,e.sal,d.dname from emp e,dept d

where sal>all(

select sal from emp where deptno=30

)

and e.deptno!=30

and e.deptno=d.deptno;

列出在每个部门工作的雇员数量,平均工资和平均工龄

--求出每个部门的雇员数量,按照部门名称分组

select d.dname,count(e.empno)

from emp e,dept d

where e.deptno=d.deptno

group by d.dname;

--求出每个部门的平均工资和工龄

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

from emp e,dept d

where e.deptno=d.deptno

group by d.dname;

列出所有雇员的姓名、部门名称和工资

--直接两张表关联

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

from emp e,dept d

where e.deptno=d.deptno;

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

--列出每个部门的雇员人数

select deptno,count(empno) cou

from emp

group by deptno;

--把以上的查询作为一张临时表

select d.*,ed.cou from dept d,

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

where d.deptno=ed.dto;

查询结果中没包含40部门,修改如下

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

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

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

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

--按照工作分组,使用min()求出最低工资

select job,min(sal) from emp group by job;

--按照工资查询出雇员的信息

select * from emp

where sal in(select min(sal) from emp group by job);

列出各个部门的MANAGER的最低工资

select deptno,min(sal)

from emp

where job='MANAGER'

group by deptno;

列出所有雇员的年薪,按照年薪从低到高排序

select ename,(sal+nvl(comm,0))*12 yearsal from emp order by yearsal asc;

查询某个雇员的上级主管,并求出这些主管中的薪水超过3000的

select distinct m.* from emp e,emp m

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

求出部门名称中带“S”的部门雇员的工资合计和部门人数

--查询部门表的部门名称,使用模糊查询,来确定部门的编号

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

--查询出符合上述条件的雇员工资合计和部门人数

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

where deptno in(select deptno from dept where dname like '%S%')

group by deptno;

第四次

1、Oracle

1.1、 创建和管理表

1) 常用的数据类型

varchar\varchar2-->表示的是一个字符串,有长度限制,255,

number-->number(n):表示一个整数,数字的长度是n,可以使用int

number(m,n):表示一个小数,数字小数长度为n,整数长度为m-n,可以使用float

date-->表示日期的类型,日期要按照标准的日期格式进行存放

clob-->大对象,表示大文本数据,一般可以存放4G的文本

blob-->大对象,表示二进制数据,最大可以存放4G,如:电影,歌曲,图片

2) 表的建立

表的建立还是按照标准的语法进行,但是在表的建立时有时候会指定约束,那么此处先给出一个建立表的简单语法。

create table 表名称(

字段名称1 字段类型 [default 默认值],

字段名称2 字段类型 [default 默认值],

....

字段名称n 字段类型 [default 默认值]

)

在前面我们使用了一种这样的语法来创建表:

create table 表名称 as (子查询)--将子查询的结果作为一张表

如果现在子查询写的是:select * from emp;表示将表结构和表的内容一起复制

如果现在子查询写的是:select * from emp where 1=2;加入了一个永远都不会成立的条件,则此时表示我们复制的只是表的结构,不复制表的内容

复制表结构:

create table temp as(select * from emp where 1=2);

3) 表的删除

表的删除语法如下:

drop table 表名称;

4) 表的修改

在SQL语法操作中,提供了alter指令,通过alter指令就可以增加新的列

为emp表添加一个address列

alter table emp add(address varchar2(200) default'暂无地址');

修改emp表中的ename,将长度改为50

alter table emp modify(ename varchar2(50));

5) 为表重命名

在Oracle中提供了rename命令,可以为表重新进行命名,但是此语句只能在Oracle中使用。语法格式如下:

rename 旧的表名称 to 新的表名称;

6) 截断表

如果现在我们需要清空一张表的数据,但是同时不需要回滚,可以立刻释放资源就需要使用截断表的语法:

truncate table 表名称;

思考下面的问题:现在有一张国家表,里面只有一个国家名称的字段,内容如下:“中国、美国、巴西、荷兰“,现在要求通过查询实现对战功能:

中国->美国

中国->巴西

中国->荷兰

美国->中国

美国->巴西

美国->荷兰

......

分析:本程序只能使用笛卡尔积完成

首先,建立一张表

create table national(

name varchar2(30)

)

向表中增加测试数据

insert into national(name) values('中国');

insert into national(name) values('美国');

insert into national(name) values('巴西');

insert into national(name) values('荷兰');

查询的时候表自己和自己关联

select n1.name,n2.name from national n1,national n2 where n1.namen2.name;

1.2、约束

在数据库表的开发中,余数是必不可少的支持。使用约束可以更好的保证数据库中的数据完整性。

数据库中的约束分类:

--在实际中,约束主要分为以下五种:

···主键约束primary key:主键表示是一个唯一的标识,本身是不能为空的

|-例如:身份证号是唯一的,不可重复,不可为空

···唯一约束unique:在一个表中只允许建立一个主键约束,而其他列如果不希望出现重复值的话,则可以使用唯一约束。

···检查约束:检查一个列的内容是否合法

|-例如:年龄。只能在0~150之间

|-例如:性别,只能是男、女、中性

···非空约束:姓名这样的字段里面的内容就不能为空

···外键约束:在两张表中进行约束操作。

1) 主键约束(primary key)

主键约束一般都是使用在id上,而且本身已经默认了内容不能为空,主键约束可以再建表的时候指定

现在我们建立一张person表,在pid上增加主键约束

drop table person;

create table person(

pid varchar2(18) primary key,

name varchar2(200),

age number(3),

birthday date,

sex varchar2(3) default '男'

)

现在我们向表中插入数据

insert into person(pid,name,age,birthday,sex) values('1111111111111111','张三',30,to_date('1976-08-09','yyyy-mm-dd'),'女');

insert into person(pid,name,age,birthday,sex) values('1111111111111111','李四',30,to_date('1976-08-04','yyyy-mm-dd'),'男');

当插入第二条语句时,会提示:违反唯一约束,那么我们将pid的值设置为null

insert into person(pid,name,age,birthday,sex) values(null,'李四',30,to_date('1976-08-04','yyyy-mm-dd'),'男');

同样会提示错误:无法将 NULL 插入 ("SCOTT"."PERSON"."PID"),以上的约束是系统自动分配好的约束名称,也可以通过constraint指定一个约束的名字,

将person表的pid指定名称

drop table person;

create table person(

pid varchar2(18),

name varchar2(200),

age number(3),

birthday date,

sex varchar2(3) default '男',

constraint person_pid_pk primary key(pid)

)

2) 非空约束(not null)

使用非空约束,表示一个字段的内容不允许为空,即:插入数据的时候必须插入内容

drop table person;

create table person(

pid varchar2(18),

name varchar2(200) not null,

age number(3) not null,

birthday date,

sex varchar2(3) default '男',

constraint person_pid_pk primary key(pid)

)

3) 唯一约束(unique)

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

假设:现在姓名不允许出现重名的情况

drop table person;

create table person(

pid varchar2(18),

name varchar2(200) unique not null,

age number(3) not null,

birthday date,

sex varchar2(3) default '男',

constraint person_pid_pk primary key(pid)

)

4) 检查约束(check)

使用检查约束来判断一个列中插入的内容是否合法,例如,年龄的取值范围,性别的取值范围

drop table person;

create table person(

pid varchar2(18),

name varchar2(200) unique not null,

age number(3) not null check(age between 0 and 150),

birthday date,

sex varchar2(3) default '男' check(sex in('男','女','中')),

constraint person_pid_pk primary key(pid)

)

5) 主-外键约束(foreign key)

之前的约束都是针对一张表的,那么主-外键约束是针对两张表的约束。为什么需要主-外键约束呢?

要求完成一个程序,一本书只属于一个人

书本身应该是一张表,一本书中必然有一个字段表示属于某个人的

drop table person;

drop table book;

create table person(

pid varchar2(18),

name varchar2(200) not null,

age number(3) not null,

birthday date,

sex varchar2(3) default '男',

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('男','女','中'))

);

create table book(

bid number primary key not null,

bname varchar2(20),

bprice number(5,2),

pid varchar2(18)

);

insert into person(pid,name,age,birthday,sex) values('1111111111111111','张三',30,to_date('1976-08-09','yyyy-mm-dd'),'女');

insert into book(bid,bname,bprice,pid) values(1,'JAVA SE',89.9,'0000000000000');

在插入第二条数据前,我们看看pid字段的值,很明显,在我们的person表中不存在这样的person,那么, 这样的数据时不应该插入到数据库中的,为了解决这样的问题,我们使用主-外键关联,关联之后字表的数据要跟随父表的数据内内容。

drop table person;

drop table book;

create table person(

pid varchar2(18),

name varchar2(200) not null,

age number(3) not null,

birthday date,

sex varchar2(3) default '男',

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('男','女','中'))

);

create table book(

bid number primary key not null,

bname varchar2(20),

bprice number(5,2),

pid varchar2(18),

constraint person_book_pid_fk foreign key(pid) references person(pid)

);

现在我们再次执行数据的插入操作,此时系统提示:违反完整约束条件 (SCOTT.PERSON_BOOK_PID_FK) - 未找到父项关键字,根据上面的分析没我们修改如下:

insert into book(bid,bname,bprice,pid) values(1,'JAVA SE',89.9,'1111111111111111');

此时插入数据成功。

在使用主-外键关联的时候有几点需要注意:

|-在子表中设置的外键在父表中必须是主键

|-删除时应该先删除子表,再删除父表

在主-外键关联中也可以使用级联删除

以现有数据库中的数据为例

delete from person where pid='1111111111111111';

要删除person表中编号为1111111111111111的人员,但是这个人在book表中存在一本书的记录。提示错误:违反完整约束条件 (SCOTT.PERSON_BOOK_PID_FK) - 已找到子记录,那么,如果想删除成功必须先删除book表中pid对应的记录,再删除此人的信息

如果我们希望一个表中的数据在删除时,可以自动删除掉其对应的子表记录,则可以使用级联删除来实现。

drop table person;

drop table book;

create table person(

pid varchar2(18),

name varchar2(200) not null,

age number(3) not null,

birthday date,

sex varchar2(3) default '男',

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('男','女','中'))

);

create table book(

bid number primary key not null,

bname varchar2(20),

bprice number(5,2),

pid varchar2(18),

constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade

);

6) 修改约束

如果一张表已经建立完成,则可以为其添加约束

关于约束类型的命名,一定要统一:

--primary key-->主键字段_pk

--unique-->字段_uk

--check-->字段_ck

--foreign key-->父字段_子字段_fk

为person添加一个约束

alter table person add constraint person_pid_pk primary key(pid);

将person的主键约束删除掉该怎么操作呢?

alter table person drop constraint person_pid_pk;

1.3、rownum

rownum:表示行号,实际上这是一个列的列名,但是这个列我们称为伪列,此列尅在每张表中出现。

例如,在我们查询雇员表的时候,加上rownum这个列名称

select rownum,empno,ename,job,sal,hiredate from emp;

从执行的效果来看,rownum本身采用自动编号的形式出现。

我们扩展下rownum的应用,现在我们只想显示前5条雇员信息,该如何实现呢?

select rownum,empno,ename,job,sal,hiredate from emp where rownum

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论