内容介绍
1.oracle的表的管理。
2.基本查询。
3.复杂查询。
4.oracle数据库的创建。
期望目标
1.掌握oracle表的管理(创建/维护)。
2.掌握对oracle表的各种查询技巧。
3.学会创建新的oracle数据库。
表名和列的命名规则:
1.必须以字母开头。
2.长度不能超过30字符。
3.不能使用oracle的保留字。
4.只能使用如下字符A-Z,a-z,0-9,$,#等。
Oracle支持的数据类型
1.字符型
Char 定长 最大2000字符。
例子:char(10) ‘小明’前四个字符放’小明’,后添6个空格补全。
Varchar2(20) 变长 最大4000字符。
例子:varchar2(10) ‘小明’ oracle分配四个字符。这样可以节省空间。
Clob(character large object) 字符型大对象 最大4G。
2.数字型
Number范围
-10的38次方到10的38次方
可以表示整数,也可以表示小数。
Number(5,2)
表示一个小数有5位有效数,2位小数
范围-999.99 999.99
Number(5)
表示一个五位整数
范围-99999 99999
3.日期类型
Date 包括年月日和时分秒。
Timestamp这是oracle9i对date数据类型的扩展。
4.图片
Blob 二进制数据 可以存放图片/声音 4G
Oracle表的管理
怎样创建表:
建表
学生表
createtablestudent
(
snonumber(4),
snamevarchar2(20),
sexchar(2),
birthdaydate,
salnumber(7,2)
);
|
班级表
createtableclasses
(
cidnumber(2),
cnamevarchar2(40)
);
|
修改表
添加一个字段
altertablestudentadd(cidnumber(2));
|
修改字段的长度
altertablestudentmodify(sanmevarchar2(30));
|
修改字段的类型或是名字(不能有数据)
altertablestudentmodify(snamechar(30));
|
删除一个字段
altertablestudentdropcolumnsal;
|
修改表的名字
删除表
添加数据
所有字段都插入
insertintostudentvalues('A001','zhangsan','man','01-05月-05',10);
|
Oracle中默认的日期格式为’dd-mon-yy’
改日期的格式:
altersessionsetnls_date_format = 'yyyy-mm-dd';
|
修改后就可以用我们熟悉的方式进行数据插入
insertintostudentvalues('A002','Mike','male','2003-05-09',10);
|
插入部分字段
insertintostudent(sid,sname,sex)values('A003','john','female');
|
插入空值
insertintostudent(sid,sname,sex,birth) values('A004','martin','male',null);
|
修改数据
修改一个字段
updatestudentsetsex
= 'male'wheresid = 'A001'
|
修改多个字段
updatestudentsetsex
= 'male', birth = '1980-04-01' wheresid = 'A001'
|
修改含有null值的数据
updatestudentsetbirth
= '1900-01-01'wherebirthisnull;
|
删除数据
删除所有记录,表结构还在,写日志,可以恢复的,速度慢。
删除表的结构和数据。
删除一条记录
deletefromstudentwheresid
= 'A001';
|
删除表中所有记录,表结构还在,不写日志,无法找回删除的记录,速度快。
一个有经验的DBA ,在开始工作前会先做一个保存点
以便于在操作出现错误时,可以返回到操作之前的状态
表的基本查询
以scott用户中的表(emp,dept)作演示;
Sql>clear;--清屏
查看表结构:
查询所有列
查询指定列
selectename,sal,job,deptnofromemp;
|
如何取消重复行
selectdistinctdeptno,jobfromemp;
|
显示操作时间
演示查询所有列和查询指定列耗时的差别
createtableusers
(
useridvarchar2(20),
usernamevarchar2(30),
userpssvarchar2(30)
);
insertintousersvalues('A0001','zhangfachao','zfc2201@126.com');
--重复执行这条语句直到插入8192行
insertintousers(userid,username,userpss)select*fromusers;
|
效果对比
select*fromusers;
8192 rows selected
Executed in 102.5 seconds
selectusernamefromusers;
8192 rows selected
Executed in 66.86 seconds
|
从结果可以看出,查询所有列比查询指定列耗时得多。
查询指定一行记录
selectename,deptno,job,salfromempwhereename='SMITH';
|
使用算术表达式和列的别名
selectename,sal*12asyearsalfromemp;
|
如何处理null值
selectsal*13+nvl(comm,0)*13asyearsal,enamefromemp;
|
Nvl函数用于某字段为空时,给一个默认值。
|
如何连接字符串(||)
selectename || ' is a ' || jobfromemp;
|
使用where子句
显示工资高于3000的员工
selectename,sal,jobfromempwheresal
> 3000;
|
查出1982.1.1后入职的员工
selectename,sal,jobfromempwherehiredate
> '1-1月-1982';
|
显示工资在2000到2500的员工情况
selectename,sal,jobfromempwheresalbetween2000and2500;
|
如何使用like操作符:
%:表示0到多个字符
_:表示单个字符
显示首字符为S的员工姓名和工资
selectename,salfromempwhereenamelike'S%';
|
显示第三个字母为大写O的所有员工的姓名和工资
selectename,salfromempwhereenamelike'__O%';
|
在where条件中使用in:
显示empno为7844,7788,7698…的雇员情况
select*fromempwhereempnoin(7844,7788,7698);
|
使用is null的操作符:
显示没有上级的雇员的情况
select*fromempwheremgrisnull;
|
使用逻辑操作符号
查询工资高于500或是岗位为manager的雇员,同时还要满足他们的姓名首写字母为大写的J
select*fromempwhere(sal
>500orjob='MANAGER') andenamelike'J%';
|
使用order by字句
按照工资从低到高的顺序显示雇员的信息
select*fromemporderbysalasc;
|
按照部门号升序而雇员的工资降序排列
select*fromemporderbydeptnoasc,saldesc;
|
使用列的别名排序(中文的列名要使用””号括起来)
selectename,sal*12 "年薪"fromemporderby"年薪"asc;
|
Oracle表复杂查询
说明:在实际应用中经常需要执行复杂的数据统计,经常需要显示多张表的数据,主要用到了select语句
数据分组-max,min,avg,sum,count
如何显示所有员工中最高工资和最低工资
selectmax(sal),min(sal)fromemp;
|
显示所有员工的平均工资和工资总和
selectavg(sal),sum(sal)fromemp;
|
计算共有多少员工
扩展要求
请显示工资最高的员工的名字,工作岗位。
selectename,jobfromempwheresalin(selectmax(sal)fromemp);
|
请显示工资高于平均平均工资的员式信息。
select*fromempwheresal
> (selectavg(sal)fromemp);
|
Group by和having子句
Group by用于对查询的结果分组统计
Having子句用于限制分组显示结果
如何显示每个部门的平均工资和最高工资
selectmin(sal),avg(sal),max(sal),deptnofromempgroupbydeptno;
|
显示每个部门的每种岗位的平均工资和最高工资
selectmin(sal),avg(sal),max(sal),deptno,job fromemp groupbydeptno,job;
|
显示平均工资低于2000的部门号和它的平均工资
selectmin(sal),avg(sal),max(sal),deptno fromemp groupbydeptno havingavg(sal) > 2000 orderbydeptno;
|
对数据分组的总结
1.分组函数只能出现在选择列表、having、order by子句中。
2.如果在select语句中同时包含有group by, having,order by,那么他们的顺序是
Group by
Having
Order by
3.在选择列中如果有列,表达式和分组函数,那么这些列和表达式必须有一个出现在group by子句中,否则就会出错。
如:
selectmin(sal),avg(sal),max(sal),deptno fromemp groupbydeptno havingavg(sal) < 2000;
|
这里deptno一定要出现在group by中。
Oracle表复杂查询
多表查询
多表查询是指基于两个以上的表或是视图的查询。在实际应用中,查询单个表可能不能满足你的需求(如显示sales部门位置和其员工的姓名),这种情况下需要使用到dept表和emp表。
规定:多表查询的条件是至少不能少于表的个数减1
显示雇员员,雇员工资及所在部门的名字(笛卡尔集)
selectename,sal,dnamefromdept,empwheredept.deptno
= emp.deptno;
|
如何显示部门为10的员工名、工资、部门名称。
selecte.ename,e.sal,d.dname fromemp e,dept d wheree.deptno= d.deptno ande.deptno = 10;
|
显示各个员工的姓名,工资及其工资的级别。
selecte.ename,e.sal,s.grade fromemp e,salgrade s wheree.salbetweens.losalands.hisal;
|
扩展要求:
显示雇员名,雇员工资及所在部门的名字,并按部门排序。
selecte.empno,e.sal,d.dname fromemp e,dept d wheree.deptno = d.deptno orderbyd.deptno;
|
自连接
自连接是指在同一张表的连接查询
显示某个员工的上级领导的名字
selecte1.ename worker,e2.ename boss fromemp e1,emp e2 wheree1.mgr = e2.empno;
|
扩展要求:
只显示”FORD”的上级
selecte1.ename worker,e2.ename boss fromemp e1,emp e2 wheree1.mgr = e2.empno ande1.ename = 'FORD';
|
子查询
子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
请思考:如何显示与SMITH同一部门的所有员工?
select*fromemp wheredeptno = (selectdeptnofromempwhereename
= 'SMITH');
|
多行子查询
多行子查询指返回多行数据的子查询。
请思考:如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号。
select*fromemp wherejobin(selectdistinctjobfromempwheredeptno
= 10);
|
在多行子查询中使用all操作符
请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
selectename,sal,deptno fromemp wheresal >all(selectsalfromempwheredeptno
= 30);
|
扩展要求:
想想还有没有别人的查询方法。
selectename,sal,deptno fromemp wheresal > (selectmax(sal)fromempwheredeptno
= 30);
|
在多行子查询中使用any操作符
请思考:如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资、部门号
selectename,sal,deptno fromemp wheresal >any(selectsalfromempwheredeptno
= 30);
|
扩展要求:
想想还有没有别的方法
selectename,sal,deptno fromemp wheresal > (selectmin(sal)fromempwheredeptno
= 30);
|
多列子查询
多列子查询是指查询返回多个列数据的子查询语句。
请思考:如何查询与SMITH的部门和岗位完全相同的所有雇员。
select*fromemp where(deptno,job) =
(selectdeptno,jobfromempwhereename='SMITH');
|
在from子句中使用子查询
请思考:如何显示高于自己部门平均工资的员工的信息。
selecte1.ename,e1.sal,e2.avgsal,e1.deptnofromemp
e1,
(selectdeptno,avg(sal)
avgsalfromempgroupbydeptno)
e2 wheree1.deptno = e2.deptno ande1.sal > e2.avgsal;
|
这里需要说明的是,当在from子句中使用子查询时,该子查询会被当作为一个视图来对待,因此叫作内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名。
分页查询
Oracle分页一共有三种:
假设有七万条记录
1.rownum分页(花了0.1秒,排第二,查询效率一般,容易理解)
select*from
(selectt.*,rownumrnfrom
(select*fromemp)
twhererownum<10
)wherern > 4;
|
在分页基础上的几个查询变化:
a.指定查询列,只需修改最里层的子查询
select*from
(selectt.*,rownumrnfrom
(selectename,deptno,sal,jobfromemp)
twhererownum<10
)wherern > 4;
|
b.如何排序
select*from
(selectt.*,rownumrnfrom
(selectename,deptno,sal,jobfromemporderbydeptnodesc)
twhererownum<10
)wherern > 4;
|
2.根据rowid来分(花了0.03秒,排第一,查询效率最高,最不容易理解)
select*fromemp whererowidin
(selectridfrom
(selectrownumrn ,ridfrom
(selectrowidrid,deptnofromemporderbydeptnodesc)whererownum<10) wherern>4)orderbydeptnodesc;
|
3.按分析函数来分(花了1.01秒,排第三,查询效率最差,最容易理解)
select*from
(selectt.*,row_number() over (orderbydeptnodesc)
rkfromemp t) where rk<10andrk
>4;
|
推荐使用第一和第二种。
用查询结果创建新表
这个命令是一种快捷的建表方法
createtablet_emp(id,name,sal,job,deptno) asselectempno,ename,sal,job,deptnofromemp;
|
合并查询
有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号
Union
union all
intersect
minus
a.union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行,并按第一列默认升序排序。
selectename,sal,jobfromempwheresal
> 2500 union selectename,sal,jobfromempwherejob
= 'MANAGER';
|
b.union all
该操作相似于union,但是它不会取消重复行,而且不会排序。
selectename,sal,jobfromempwheresal
> 2500 unionall selectename,sal,jobfromempwherejob
= 'MANAGER';
|
c.intersect
使用该操作符用于取得两个结果集的交集。
selectename,sal,jobfromempwheresal
> 2500 intersect selectename,sal,jobfromempwherejob
= 'MANAGER';
|
d.minus
使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不显示存在第二个集合中的数据。
selectename,sal,jobfromempwheresal
> 2500 minus selectename,sal,jobfromempwherejob
= 'MANAGER';
|
创建数据库有两种方法:
1,通过oracle提供的向导工具database configuration assistant。
2,我们可以手工步骤直接创建。
分享到:
相关推荐
Oracle Database 21c 是最新的版本;...oracle-database-ee-21c-1.0-1.ol8文件分割成 三个 压缩包,必须集齐 三个 文件后才能一起解压一起使用: Oracle Database 21c (oracle-database-ee-21c-1.0-1.ol8.part3.rar) ...
Oracle Database 21c 是最新的版本;...oracle-database-ee-21c-1.0-1.ol8文件分割成 三个 压缩包,必须集齐 三个 文件后才能一起解压一起使用: Oracle Database 21c (oracle-database-ee-21c-1.0-1.ol8.part3.rar) ...
oracle-instantclient-basic-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-basic-21.6.0.0.0-1.x86_64.zip oracle-instantclient-devel-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-sqlplus-21.6.0.0.0-1.x86_...
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-basiclite-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-jdbc-...
Oracle Database 19c 是最新的长期版本,支持期限...oracle-database-ee-19c文件分割成 三个 压缩包,必须集齐 三个 文件后才能一起解压一起使用: Oracle Database 19c (oracle-database-ee-19c.x86_64.part3.rar) ...
oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包,为了运行OCI、OCCI、JDBC-OCI 这几个应用程序; oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm --补充包/文件,是为了运行sql*plus...
在Linux上安装oracle数据库,... 在oracle linux 6_64bit上,提供了oracle-rdbms-server-11gR2-preinstall包,可以自动化的将需要安装的rpm包,需要调整的内核参数自动化的处理,对DBA安装数据库确实节省了不少的时间。
oracle-xe-11.2.0-1.0.x86_64.rpm
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包,为了运行OCI、OCCI、JDBC-OCI 这几个应用程序; oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm --补充包/文件,是为了运行sql*plus...
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-basiclite-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-odbc-...
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包,为了运行OCI、OCCI、JDBC-OCI 这几个应用程序; oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm --补充包/文件,是为了运行sql*plus...
linux环境中的oracle客户端远程安装包 oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64....
c3p0-oracle-thin-extras-0.9.2.1
oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient11.2-sdk-11.2.0.1.0-1.x86_64.zip
oracle-rdbms-server-11gR2-preinstall-1.0-4.el7.x86_64.rpm
oracle-database-preinstall-19c-1.0-2.el7.x86_64 .rpm,这是linux版本的,win请勿下载