保存在机器内部的范围:
1~22bytes
1.number可以存放整数,也可以存放小数
2.语法number(p,s)
3.举例说明
4.是变长
特别说明:
如果在实际开发中,我们明确要求保留到小数点几位,则明确指定,如果没有,可以直接使用number
⑧date日期类型
1.用于表示日期(年/月/日时/分/秒)
2.案例:
createtabletest_1(birthdaydate);
insertintotest_1values('23-9月-2013');
添加时候,使用默认格式(dd-mm-yyyy)
oracle日期的默认格式dd-mm-yyyy,如果我们希望使用自己习惯的日期格式,也可以,需要借助oracle函数
8timestamp
和date的主要区别,当更新数据时,timestamp类型自动更新为当前时间。
建表的综合案例
字段
字段类型
Id
整形
name
字符型
sex
字符型
brithday
日期型
fellowship
小数型
resume
大文本型
createtablestudents(
Idnumber,
namevarchar2(64);
sexchar
(2),
birthdaydate,
fellowshipnumber(10,2),
resumeclob);
■表的管理---修改表
//添加一个字段
//修改字段类型
//删除一个字段
■select查询语句
查询过程我们使用三张表
第一张表
empno:
雇员编号Ename:
名字job:
职位MGR:
上级编号Hiredate:
入职日期sal:
薪水comm:
奖金deptno:
部门编号
deptno:
部门编号dname:
部门名loc:
地点
Grade:
工资级别losal:
最低工资hisal:
最高工资
基本语法:
select[distinct]*|{列名1,列名2...}from表名[where{条件}]
●Select指定查询哪些列的数据。
●column指定列名。
●*号代表查询所有列。
●From指定查询哪张表。
●where是条件
●DISTINCT可选,指显示结果时,是否剔除重复数据
select*from表名
select列名1,列名2from表名
规定:
尽可能返回较少的列,不要轻易使用*
如何取消重复行?
selectdistinctdeptno,jobfromemporderbydeptno;
什么是重复行?
返回的数据完全一样才是重复行
?
查询SMITH的薪水,工作,所在部门
selectsal,job,deptnofromempwhereename=’smith’;
特别说明:
oracle的sql语句不区分大小写,但是查询的内容是区分大小写。
?
显示每个雇员的年工资
selectsal*13+nvl(comm,0)*13年薪fromemp;
说明:
nvl函数是oracle提供的,用于处理数据null的问题,基本用法nvl(comm,0);如果comm为空null,则返回0,如果不为空,则返回本身的值
||的使用
在查询时候,如果希望将多列拼接起来,作为一列返回,可以使用||
具体用法:
selectename||'是一个'||jobfromemp;
where子句
?
如何显示工资高于3000的员工
select*fromempwheresal>3000;
?
如何查找1982.1.1后入职的员工
select*fromempwherehiredate>’1982-1-1’;
?
如何显示工资在2000到2500的员工情况
select*fromempwheresal>-=2000andsal<=2500;
select*fromempwheresalbetween2000and2500;
如何使用like操作符
%表示0到多个字符
_表示任意一个字符
?
如何显示首字符为S的员工姓名和工资
selectename,salfromempwhereenamelike‘S%’;
?
如何显示第三个字符为大写O的所有员工的姓名和工资
selectename,salfromempwhereenamelike‘__O%’;
在where条件中使用in
?
如何显示empno为123,345,800...的雇员情况
select*fromempwhereempno=123orempno=345orempno=800;
select*fromempwhereempnoin(123,345,800);
使用isnull的操作符
?
如何显示没有上级的雇员的情况
select*fromempwheremgrisnull;
使用逻辑操作符号
?
查询工资高于500或是岗位为MANAGER的雇员,同时还要满足他们的姓名首写字母为大写的J
select*fromempwhere(sal>500orjob=’manager’)andenamelike‘J%’;
使用orderby子句
orderby子句主要的用途是对结果进行排序显示
?
如何按照工资的从低到高的顺序显示雇员的信息
select*fromemporderbysal[descend]ascend;
默认情况下,orderby后面的字段是升序排序,如果希望降序,则desc
?
按照部门号升序而雇员的入职时间降序排列
selectename,deptno,hiredatefromemporderbydeptno,hiredatedesc;
使用列的别名排序
selectsal*13+nvl(comm,0)*13年薪fromemporderby年薪;
■表的复杂查询
数据分组-max,min,avg,sum,count
?
如何显示所有员工中最高工资和最低工资
selectmax(sal)fromemp;
selectmin(sal)fromemp;
selectmax(sal),min(sal)fromemp;
?
显示所有员工的平均工资和工资总和
selectavg(sal),sum(sal)fromemp;
avg(sal)会不会把sal为空统计进来?
不会把sal为null统计进来,因此,如果希望为null的值也考虑,则我们可以这样做:
selectsum(sal)/count(*)fromemp;
?
计算共有多少员工
selectcount(*)fromemp;
说明:
count(*)也可以针对一个字段进行统计,比如count(comm);
扩展要求:
?
请显示工资最高的员工的名字,工作岗位
思路:
1.查询出最高工资十多少?
selectmax(sal)fromemp;
2.看看谁的工资有这么多?
selectename,jobfromempwheresal=(selectmax(sal)fromemp);
?
请显示工资高于平均工资的员工信息
1.查询出平均工资是多少?
selectavg(sal)fromemp;
2.
selectename,job,salfromempwheresal>(selectavg(sal)fromemp);
groupby和having子句
?
如何显示每个部门的平均工资和最高工资
selectavg(sal),max(sal),deptnofromempgroupbydeptno;
?
显示每个部门的每种岗位的平均工资和最低工资
selectavg(sal),min(sal),deptno,jobfromempgroupbydeptno,job
?
显示部门平均工资低于2000的部门号和它的平均工资
思路
1.查处每个部门的平均工资
selectavg(sal),deptnofromempgroupbydeptno;
2.
selectdeptno,avg(sal)fromempgroupbydeptnohavingavg(sal)<2000;
对数据分组的总结
1分组函数只能出现在选择列表、having、orderby子句中,where子句中不可以包含分组函数
2如果在select语句中同时包含有groupby,having,orderby那么他们的顺序是groupby,having,orderby
3在选择列表中如果有列、表达式、和分组函数,那么选择列表中任一非分组函数的所有列都应出现在groupby子句中,否则就会出错
如selectdeptno,avg(sal),max(sal)fromempgroupbydeptnohavingavg(sal)<2000;
这里deptno就一定要出现在groupby中
■自连接
比如显示’FORD’的上级.
思路:
1.显示ford上级编号
selectmgrfromempwhereename=’FORD’;
2.显示ford上级的信息
select*fromempwhereempno=(selectmgrfromempwhereename=’FORD’);
显示员工的上级领导的姓名
思路:
把emp表看作两个表,一个表是雇员表worker,一个表是boss表
selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.empno;
疑惑:
这里我们看到KING没有显示,因为他没有上级,如果希望把没有上级的人显示出来,则需要使用外连接。
■子查询
什么是子查询?
子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询
1单行子查询
如何显示与SMITH同一部门的所有员工?
思路:
1.SMITH在哪个部门
selectdeptnofromempwhereename=’SMITH’;
2.显示该部门所有员工
select*fromempwheredeptno=(selectdeptnofromempwhereename=’SMITH’);
2多行子查询
如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
1.先查询10号部门有哪些职位
selectjobfromempwheredeptno=10;
2.显示信息
selectename,job,sal,deptnofromempwherejobin(selectjobfromempwheredeptno=10);
3all操作符
如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
selectename,sal,deptnofromempwheresal>all(selectsalfromempwheredeptno=30);
selectename,sal,deptnofromempwheresal>(selectmax(sal)fromempwheredeptno=30);
4any操作符
如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
selectename,sal,deptnofromempwheresal>any(selectsalfromempwheredeptno=30);
selectename,sal,deptnofromempwheresal>(selectmin(sal)fromempwheredeptno=30);
5多列子查询
如何查询与SMITH的部门和岗位完全相同的所有雇员
1.显示smith的部门和岗位是什么
selectdeptno,jobfromempwhereename=’SMITH’;
2.显示雇员信息
select*fromempwheredeptno=(selectdeptnofromempwhereename=’SMITH’)andjob=(selectjobfromempwhereename=’SMITH’);
select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename=’SMITH’);
⑥在from子句中使用子查询
如何显示高于自己部门平均工资的员工的信息
1.显示部门平均工资
selectavg(sal),deptnofromempgroupbydeptno;
2.把上面结果当作一个临时表来处理,显示员工信息
select*fromempt2,(selectavg(sal)myavg,deptnofromempgroupbydeptno)t1wheret2.sal>t1.myavgandt1.deptno=t2.deptno;
selecte1.*fromempe1,t1.myavgwheree1.sal>(selectavg(sal)myavgfromempwheredeptno=e1.deptno)t1
!
!
!
这个知识点一定要掌握
查找每个部门工资最高的人的详细资料
1.先找出每个部门的最高工资
selectmax(sal),deptnofromempgroupbydeptno;
2.显示详细信息
selectt2.ename,t2.deptno,t2.sal,t1.mymaxfromempt2,(selectmax(sal)mymax,deptnofromempgroupbydeptno)t1wheret2.sal=t1.mymaxandt2.deptno=t1.deptno;
显示每个部门的信息(编号,名称)和人员数量
显示每个部门的人员数量
selectdeptno,count(*)fromempgroupbydeptno;
显示每个部门的信息
selectt2.deptno,t2.dname,t1.mynumfromdeptt2,(selectdeptno,count(*)mynumfromempgroupbydeptno)t1wheret2.deptno=t1.deptno(+);
+表示外连接
这里需要说明的当在from子句中使用子查询时
该子查询会被作为一个临时表来对待,
当在from子句中使用子查询时,必须给子查询
指定别名.
■分页查询
分页查询是我们学习数据库,必须掌握的一个要点
mysql的分页查询
select*from表名where条件limit从第几条取,取几条
sqlserver:
selecttop3*from表名whereidnotin(selecttop3idfrom表名where条件)
排除前3条,再取3条,这个案例实际上取4-6
oracle:
selectt2.*from(selectt1.*,rownumrnfrom(select*fromemp)t1whererownum<=6)t2wherern>=4;
三层过滤:
1.第一层过滤
select*fromempwhere条件(多表查询);
2.第二层过滤
(selectt1.*,rownumrnfrom(select*fromemp)t1whererownum<=6)
3.第三层过滤
selectt2.*from(selectt1.*,rownumrnfrom(select*fromemp)t1whererownum<=6)t2wherern>=4;
实际上,我们可以把上面的sql语句当作一个模板来对待
6:
表示取到第几条
4:
表示从第几条开始取
如果我们需要针对不同的情况,分页,请在最内层进行处理,包括多表
请思考:
请按照入职时间的先后顺序,查询从第7到第10个人都是谁?
selectt2.*from(selectt1.*,rownumrnfrom(select*fromemp_test)t1whererownum<=600010)t2wherern>=600000;
看一下它的分页查询效率
模拟100w的一个表
createtableemp_testasselect*fromemp;
自我复制
insertintoemp_testselect*fromemp_test;
■合并查询
1)union
该操作符用于取得两个结果集的并集。
当使用该操作符时,会自动去掉结果集中重复行。
selectename,sal,jobfromempwheresal>2500union
selectename,sal,jobfromempwherejob=‘MANAGER';
2)unionall
该操作赋与union相似,但是它不会取消重复行,而且不会排序。
selectename,sal,jobfromempwheresal>2500
unionallselectename,sal,jobfromempwhere
job='manager';
3)intersect
使用该操作符用于取得两个结果集的交集。
selectename,sal,jobfromempwheresal>2500
intersectselectename,sal,jobfromempwhere
job='manager';
4)minus
使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据。
selectename,sal,jobfromempwheresal>2500minus
selectename,sal,jobfromempwherejob='manager';
■oracle的内连接和外连接
1内连接
内连接是我们用的做多一种连接,前面我们讲的都是内连接
举例:
比如我们显示员工的姓名和部门名称
selectemp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;<====>
selectemp.ename,dept.dnamefromempinnerjoindeptonemp.deptno=dept.deptno;
2外连接
分为三种,左外连,右外连,完全外连
--表stu
idname
1,Jack
2,Tom
3,Kity
4,nono
createtablestu(idnumber,namevarchar2(32);
insertintostuvalues(1,’jack’);
insertintostuvalues(2,’tom);
insertintostuvalues(1,’kity’);
insertintostuvalues(1,’nono’);
--表exam
idgrade
1,56
2,76
11,80
createtableexam(idnumber,gradenumber);
显示所有人的成绩,如果没有成绩,也要显示该人的姓名和id号,成绩显示为空)
select*fromstu,examwherestu.id=exam.id;
上面我们使用的内连接,它的特点是只有两张表同时匹配,才被选中
使用左