数据库的设计与表的管理 oracle笔记
1.1oracle的表的管理
1.1.1表名和列的命名规则
1)表名必须以字母开头
2)表名的长度不能超过30
3)不能oracle关键字
1.1.2oracle支持的数据类型
1)字符型
char 最大是2000
例子 char(10) '小寒' 在数据库中 '小寒 '
varchar2 最大时4000
例子 varchar2(10) '小寒' 在数据库中 '小寒'
clob 字符型大对象 最大4g
2)数字型
number 范围 -(10的38次方) 到 10的38次方
可以表示整数，也可以表示小数
number(5,2) 表示的 -999.99 到 999.99
numer(5) 表示一个五位数的一个整数 -99999 ~ 999999
3)日期类型
date
timestamp
4)图片
blob 二进制数据 (放入图片或者是声音文件 4g)
1.1.3管理表
1)建立表
create table student(
xh number(4), --学号
xm varchar2(50),--姓名
sex char(2),--性别
birthday date,--生日
sal number(7,2) --奖金
);
2)如何察看表的结构
desc 表名
3)如何给一个表添加字段
alter table student add (classId number(2));
4)修改字段的长度
alter table student modify(xm varchar2(1000));
5)删除字段
alter table student drop column sal;
6)删除表
drop table student;
1.1.4如何操作数据表
(查询!!!/添加/修改/删除)
//加入一个数据
insert into student values(1,'MIKE','男','12-8月-67',12);
二 基本查询
2.1简单的查询语句
1)显示某张表的结构
desc 表名
2)查询所有列
select * from emp;
3)查询指定列
4)如何取消重复行
select distinct deptno,job from emp;
5)使用算术表达式
//如何计算年工资,每个雇员
nvl(comm,0):是oracle自己的一个函数
如果没有奖金 给0
如果有奖金 ,就给comm
select ename, sal*12+nvl(comm,0)*12 年工资 from emp;
6)使用列的别名
//显示年工资大于20000的所有员工
select ename, sal*12+nvl(comm,0)*12 from emp where sal*12+nvl(comm,0)*12>20000
7)如何处理null值
nvl(comm,0):是oracle自己的一个函数
如果没有奖金 给0
如果有奖金 ,就给comm
2.2使用where子句
1)在where条件中使用数字
如何显示工资高于3000的员工
select ename, sal from emp where sal>3000;
2)在where条件中使用字符值
注意: 1.要用单引号引住 2.区分大小写
3)在where条件中使用日期值
注意: 1.要有单引号引住2.要符合日期的形式 否则要出错
请问如何查找1982.1.1后入职的员工
select * from emp where hiredate>'1-1月-82';
4)在where条件中使用between ... and
请问：如何显示工资在2000到2500的员工情况
select * from emp where sal between 2000 and 2500;
5)在where条件中如何使用like操作符(模糊查询)
%: 表示0到多个字符
_: 表示任意一个字符
请思考:1如何显示首字符为S的员工姓名和工资
2如何显示第三个字符为大写O的所有员工的姓名和工资
select ename,sal from emp where ename like '__O%'
7)在where条件中使用in
如何显示empno为 123,345,800,1250...的雇员情况
select * from emp where empno in(123,345,800,1250)
8)在where条件中使用is null的操作符
请思考:如何显示没有上级的雇员的情况
select * from emp where mgr is null;
9)在where条件中使用逻辑操作符号
and,or
请思考如何查询工资高于2500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J
select * from emp where ename like 'J%' and (sal>2500 or job='MANAGER')
2.3使用order by字句
当执行查询操作时，默认情况下会按照行数据插入的先后顺序来显示数据.但是在实际的应用中常常需要对
数据进行排序,可以使用order by
注意: 在select语句中可以包含多个子句(where , group by , having ,order by) 这是order by 必须是
最后一个子句
1)升序排列
请思考：如何按照工资的从低到高的顺序显示雇员的信息
select * from emp order by sal;
//从高到低(desc 从高到低 asc 低到高)
select * from emp order by sal desc;
3)使用多列排序
请思考如何按照部门号升序而雇员的工资降序排列
select * from emp order by deptno,sal desc;
4)使用列的别名排序
如果在where子句中为列或是表达式定义了别名，那么当执行排序操作时，即可以使用列或是表达式排序，
也可使用列别名排序
请思考如何按照年薪从低到高排序
5)使用列的编号进行排序
6)查询表中限定位置记录
请大家思考一个问题,分页..hehe
按雇员的id号升序取出
1)返回前5行数据
2)返回6到10行数据
3)返回最后5行数据
总结 ;
1.
2.
3.
4.
三 复杂查询
前面介绍了使用select语句执行简单的select语句、查询、排序、等。但是这些功能远远不能满足实际应用
的需要，在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的
select语句
3.1数据分组
1)分组函数
3.1.1max和min
请思考如何显示所有员工中最高工资和最低工资
select max(sal),min(sal) from emp;
3.1.2avg和sum
请思考如何显示所有员工的平均工资和工资总和
select avg(sal),sum(sal) from emp;
3.1.3count
请思考，如何得到共有多少员工
select count(*) from emp;
2)group by 和 having子句
group by用于对查询的结果分组统计,having子句用于限制分组显示结果.
3.2.1 使用group by 进行单列分组
请思考如何显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from emp group by deptno;
3.2.2 使用group by 进行多列分组
多列分组是指基于两个或是两个以上的列生成分组统计结果.
请思考: 如何显示每个部门的每种岗位的平均工资和最低工资
select deptno,job ,avg(sal),max(sal),min(sal) from emp group by deptno,job;
3.2.3 使用having 子句限制分组显示结果
having 子句用于限制分组统计的结果,同时要求having必须要跟在group by子句后面
请思考: 如何显示平均工资低于2000的部门号、平均工资、最高工资
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
我们这里对数据分组作一个小小的总结:
1。分组函数只能出现在选择列表、having、order by子句种
2. 如果在select 语句种同时包含有group by ,having ,order by 那么他们的顺序是
group by , having , order by .
3。在选择列种如果有列、表达式、和分组函数,那么这些列和表达式必须有一个出现在group by 子句中，
否则就会出错
如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
这里deptno就一定要出现在 group by 中 呵呵
3)rollup 和 cube
在直接使用group by 执行数据统计时，只会生成列的统计数据,但是可能这样还是不能满足你的需求，你可
能希望产生横向或是纵向的统计结果,这是可以使用rollup cube
思考:
3.2连接查询
连接查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求,
（如显示sales部门位置和其员工的姓名),这种情况下需要使用到连接查询(dept表和emp表)
3.2.1相等连接
1)相等连接
请思考:显示雇员名,雇员工资及所在部门的名字
select ename ,sal ,dname from emp ,dept where emp.deptno=dept.deptno
2）使用and指定其它条件
请思考:如何显示部门号为10的部门名、员工名和工资
select ename ,sal ,dname from emp ,dept where emp.deptno=dept.deptno and dept.deptno=10
3.2.2不等连接
在salgrade这张表中存放了工资级别的信息
请思考如何显示各个员工的姓名，工资，及其工资的级别
select ename,sal,grade from emp , salgrade where sal between losal and hisal
3.2.3自连接
自连接是指在同一张表的连接查询。
请思考:如何显示某个员工的上级领导的姓名
select manager.ename from emp manager,emp worker where manager.empno=worker.mgr and
worker.ename='BLAKE'
3.2.4内连接和外连接//不作要求
3.3子查询
子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询
3.3.1单行子查询
单行子查询是指只返回一行数据的子查询语句
请思考：如何显示与scott同一部门的所有员工
select ename,sal,deptno from emp where deptno=(select deptno from emp where ename='soctt');
3.3.2多行子查询
多行子查询指返回多行数据的子查询
1)在多行子查询中使用in操作符号
请思考:如何查询匹配于部门10员工的岗位的雇员名、岗位、工资、部门号
select ename,job,sal,deptno from emp where job in (select distinct job from emp where
deptno=10);
2）在多行子查询中使用all操作符
请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,dept from emp where sal>all (select sal from emp where deptno=30);
3）在多行子查询中使用any操作符
请思考:如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
select ename,sal,dept from emp where sal>any (select sal from emp where deptno=30);
4)多列子查询
单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的，而
多列子查序则是指查询返回多个列数据的子查询语句
请思考如何查询于smith部门和岗位完全相同的所有雇员
select ename,job,sal,deptno from emp where (deptno,job)=(select deptno,job form emp where
ename='smith');
3.3.3其它子查讯
1)相关子查询
在子查询中使用exists谓词来实现
请思考如何显示工作在new york的员工
select ename,job,sal,deptno from emp where exists (select 1 from dept where
dept.deptno=emp.deptno and dept.loc='new york');
2)在from子句中使用子查询
请思考：如何显示高于部门平均工资的员工的信息
这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此页叫作内嵌视图，当
在from子句中使用子查讯时，必须给子查询指定别名.
select ename,job,sal from emp ,(select deptno,avg(sal) avgsal from emp group by deptno) dept
where emp.deptno=dept.deptno and emp.sal>dept.avgsal;
3)在dml语句中使用子查询(如insert ,update语句,delete)
请大家思考什么是dml语句(......) data manipulate language
请问如何将一张表的内容（数据）装载到另一张表中，这个是在dba工作中经常遇到的事情
insert int employee (id,name,titel,salary) select empno,ename,job,sal from emp
请思考:如何删除emp表中所有sales（销售部门）的员工//比如该公司要撤销销售部门
delete from emp where deptno=(select deptno from dept where dname='sales');
4)在ddl语句中使用子查询
出了可以在select,update,insert,delete语句中使用子查询外，还可以在ddl（data definiton language)
中使用子查询
1)如何创建一张新表
create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
3.3.4合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union , union
all,intersect , minus
1)union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行,并且会以第一列进
行排序后显示结果集.
请思考: 如何显示工资高于2500的员工和岗位是manager的员工(or)
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where
job='manager';
2)union all
该操作赋于union相似，但是它不会取消重复行,而且不会排序.
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where
job='manager';
3)intersect
使用该操作符用于取得两个结果集的交集，并以第一列排序
请思考: 如何显示工资高于2500的员工并且岗位是manager的员工(and)
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where
job='manager';
4)minus
使用该操作符用于取得两个结果集的查集，它只会显示存在第一个集合中，而不存在第二个集合中的数据.
并以第一列排序
请思考: 如何显示工资高于2500的员工并且岗位不是manager的员工(and)
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='manager';
3.3.4其它复杂查询
请思考; 如何查询部门工资总和高于员工工资总和1/3的部门名及工资总和(两次使用相同子查询)
select dname,sum(sal) as dept_total from emp,dept where emp.deptno=dept.deptno group by dname
having sum(sal)>(select sum(sal)*1/3 from emp,dept where emp.deptno=dept.deptno)
请思考:在 sql语句中是否可以使用if...then...else的语法（case);
使用case 表达式时,使用when指定条件语句.下面我给大家举例说明
select ename,sal,case when sal>3000 then 3 when sal>2000 then 2 else 1 end grade from emp
where deptno=10;
========================================================================================
考虑给大家用eclipse+myeclipse--->struts工程
考虑用一个jdbc的案例去连接数据库scott/ebook
二 oracle数据库的创建
创建数据库有两种方法,一是通过oracle提供的向导工具(database configuration assistant)创建、修改
、删除数据库;另外我们可以用手工步骤直接创建.
1)启动 dbca
这里我们需要说明在运行dbca时关闭其它的数据库.如演示的情况
2)选择创建数据库的类型:
2.1数据仓库
2.2通用数据库
2.3新数据库
2.4事务处理
3)确定数据库库的名字、数据库实例、核心字符集.
这里我们需要给各位说明一下:数据库是"database_name.database_domain"形式,比如mytest.us.acme.com
这里mytest是数据库名，us.acme.com是数据库所在的数据库域,要求在同一个数据库域中不能有相同的数据库中.
这里需要说明的是数据库名是一个长度不超过8个字符的字符串,可以包含字母、数字、及其它字符。域部分
是一个长度不超过128个字符的字符串，同样也可以包含字母、数字、及其它字符..
4）确定数据库网络连接模式
4.1专用服务器模式
要求每个用户进程都有专用的服务器进程。每增加一个客户端，oracle服务器就自动的增加一个服
务器进程。
4.2共享服务器模式
共享服务器模式也叫多线程服务器体系结构(multi_threaded server architecture mts),这种配
置允许大量用户进程共享少量服务器进程的服务器，因此可以支持更多的用户数量。在这种配置中，允许少量服务器
进程对大量的客户机提供服务。共享服务器模式与专用服务器相比，当用户数量增加是，mts减少了大量内存的使用
。调整和优化全部系统性能，充分使用了网络连接共享。
766
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
