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