sql查询语句Word文档下载推荐.docx
《sql查询语句Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《sql查询语句Word文档下载推荐.docx（26页珍藏版）》请在冰豆网上搜索。
查询
select*from表名;
Sql语句中函数的使用
nvl（d1,d2）
作用：
如果d1为null则用d2替代，nvl函数的两个参数可以是数字、字符或日期,但两个参数的数据类型必须一致。
例子：
查询emp表中所有员工的工资，若工资为空，则返回0；
Selectnvl（salary，0）fromemp；
“||”符号
“||”符号表示两个数据串接起来,类似于Java中的两个字符串之间的+号；
连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成OUT_PUT
提示1:
字符串连接符||提示2：
用别名控制列头显示
selectename||'
'
||job||'
||salaryOUT_PUTfromemp;
as复制表
把emp_yyy表的内容复制到新建的emp_xxx表中；
SQL>
createtableemp_xxx
as
select*fromemp_yyy;
distinct关键字
注意：
distinct必须（只能）跟在select后边
显示出职员表中的不重复的职位；
selectdistinctjobfromemp;
lower（）函数
将字符数据转换为小写（如果不知道职位的大小写形式,可以使用lower函数,忽略大小写）
查找职位为“analyst”的员工
Select*fromempwherelower（job）=‘analyst’；
upper（）函数
将数据转换为大写
忽略大小写,查找职位为“analyst”的员工
select*fromemp_xxxwhereupper（job）='
ANALYST'
;
between…and…
在区间中：
between低值and高值
闭区间：
[低值,高值]
薪水大于5000并且小于10000的员工数据？
in（列表）
列出职位是Manager或者Analyst的员工
模糊匹配like%
“%”表示0到多个字符,跟like配合使用“_”下划线表示一个字符
列出职位中第二个字符是a的员工数据？
select*fromemp_xxxwherejoblike'
_a%'
count
（1）
查询数据库中有多少个名字中包含'
EMP'
的表？
selectcount
（1）fromuser_tableswheretable_namelike'
%EMP%'
SQL>
selectcount
（1）fromemp_xxxwherehiredateisnotnull;
等同于
selectcount（hiredate）fromemp_xxx;
--注意：
count函数忽略空值
count（*）
查询数据库中有多少个名字中以'
S_'
开头的表？
selectcount（*）fromuser_tableswheretable_namelike'
S\_%'
escape'
\'
如果要查询的数据中有特殊字符（比如_或%）,在做模糊查询时,--需要加上\符号表示转义,并且用escape短语指明转义字符\；
isnull
查询哪些员工没有奖金？
isnotnull
哪些员工有奖金？
select*fromemp_xxxwherebonusisnotnull;
薪水不在5000至8000的员工？
select*fromemp_xxxwheresalarynotbetween5000and8000;
notin（list）
不是部门20和部门30的员工？
select*fromemp_xxxwheredepnonotin（20,30）;
round（数字,小数点后的位数）
用于数字的四舍五入
计算金额的四舍五入
trunc（）
trunc（数字,小数点后的位数）用于截取，如果没有第二个参数,默认是0
计算金额,末尾不做四舍五入
函数coalesce（）
coalesce（参数列表）函数的作用：
返回参数列表中第一个非空参数,参数列表中最后一个值通常为常量
计算员工的年终奖金
要求：
1）如果bonus不是null,发年终奖金额为bonus
2）如果bonus是null,发年终奖金额为salary*0.5
3）如果bonus和salary都是null,发100元安慰一下
selectename,bonus,salary,coalesce（bonus,salary*0.5,100）bonusfromemp_xxx;
返回参数列表中第一个非空数据
最后一个参数通常是常量
decode函数
decode（）函数是Oracle中等价于casewhen语句的函数,作用同case语句相同。
decode函数语法如下：
decode（判断条件,匹配1,值1,匹配2,值2,…,默认值）表达的意思是：
如果判断条件=匹配1,则返回值1判断条件=匹配2,则返回值2
根据员工的职位,计算加薪后的薪水数据
和case语句相同
1）如果职位是Analyst：
加薪10%
2）如果职位是Programmer：
加薪5%
3）如果职位是clerk：
加薪2%
4）其他职位：
薪水不变
selectename,salary,job,decode（job,'
Analyst'
salary*1.1,'
Programmer'
salary*1.05,'
clerk'
salary*1.02,salary）new_salaryfromemp_xxx;
orderby
薪水由低到高排序（升序排列）
select*fromemp_xxxorderbysalaryasc;
--正序排列,asc可以省略
空值被看做最大
--desc（descend）降序排列不可省略
select*fromemp_xxxorderbysalarydesc;
数据字典
1）user_tables用户所有的数据表
2）user_constraints用户所有的约束条件
3）user_objects用户所有的对象（表、视图、索引等）
4）all_tables用户能访问的数据表
包括自己的和别的用户允许自己访问的
5）all_constraints用户能访问的约束条件
6）all_objects用户能访问的对象（表、视图、索引等）
7）数据字典的格式如：
user_XXX:
用户自己的对象
all_XXX：
用户能访问的对象
dba_XXX：
数据库所有的对象
1）user_tables
字段table_name表名
当前帐户（scott）下有多少个表？
selectcount
（1）fromuser_tables;
scott帐户下有多少个名字中包含emp的表？
selectcount
（1）fromuser_tableswherelower（table_name）like'
%emp%'
;
2）user_objects
字段created表的创建时间
1年12月07日后创建的表,删除过时的表。
例子2：
计算五个月之前创建的数据表的个数
selectcount（a.table_name）fromuser_tablesajoinuser_objectsbona.table_name=b.object_namewhereb.created<
add_months（sysdate,-5）;
分组查询：
groupby
groupby列名：
表示按指定列分组查询
按部门计算每个部门的最高和最低薪水分别是多少？
selectdeptno,max（salary）max_s,min（salary）min_sfromemp_xxxgroupbydeptno;
计算每个部门的薪水总和和平均薪水？
selectdeptno,max（salary）,min（salary）,sum（salary）,avg（nvl（salary,0））fromemp_xxx
groupbydeptno;
每个部门的统计信息：
selectdeptno,max（salary）max_s,min（salary）min_s,sum（salary）sum_s,
avg（nvl（salary,0））avg_s,count（*）emp_numfromemp_xxxgroupbydeptno;
按职位分组,每个职位的最高、最低薪水和人数？
selectjob,max（salary）max_s,min（salary）min_s,count（*）emp_numfromemp_xxxgroupbyjoborderbyemp_num
select后出现的列,凡是没有被组函数包围的列,必须出现在groupby短语中
having子句
having子句用于对分组后的数据进行过滤。
注意区别where是对表中数据的过滤;
having是对分组得到的结果数据进一步过滤
平均薪水大于5000元的部门数据,没有部门的不算在内？
selectdeptno,avg（nvl（salary,0））avg_sfromemp_xxxwheredeptnoisnotnullgroupbydeptnohavingavg（nvl（salary,0））>
5000;
薪水总和大于20000元的部门数据？
selectdeptno,sum（salary）sum_sfromemp_xxxwheredeptnoisnotnullgroupbydeptnohavingsum（salary）>
20000;
日期函数
常用日期格式
yyyy四位数字年如：
2011
year全拼的年如：
twentyfifteen
month全拼的月如：
November或11月（中文）
mm两位数字月如：
11
mon简拼的月如：
nov（中文没有简拼）
dd两位数字日
day全拼的星期如：
tuesday
dy简拼的星期如：
tue
am上午/下午如：
am/pm
sqlplus中日期的默认格式是：
DD-MON-RR日-月-年
日期函数sysdate
获取系统当前时间
selectsysdatefromdual;
--dual为虚表
计算员工入职多少天
selectename,hiredate,（sysdate-hiredate）daysfromemp_xxx;
日期数据相减,得到两个日期之间的天数差,不足一天用小数表示。
可以用round函数处理一下。
selectename,hiredate,round（sysdate-hiredate）daysfromemp;
计算员工入职多少个月？
selectename,hiredate,round（months_between（sysdate,hiredate））monthsfromemp_xxx;
add_months
计算12个月之前的时间点
selectadd_months（sysdate,-12）fromdual;
last_day（sysdate）
计算本月的最后一天
selectlast_day（sysdate）fromdual;
to_char（日期数据,格式）
转换函数to_char（日期数据,格式）:
把日期数据转换为字符数据
把时间数据按指定格式输出
selectto_char（sysdate,'
yyyy-mm-ddhh24：
mi：
ss'
）fromdual;
yearmonthdddaydy'
yyyy/mm/dd'
to_date（）
按指定时间格式插入数据
insertintoemp_xxx（empno,ename,hiredate）values（1012,'
amy'
to_date（'
2011-10-10'
'
yyyy-mm-dd'
））;
小结：
转换函数to_date（）和to_char（）
to_date（）和to_char（）是时间处理的函数
to_date将字符串数据按指定格式转换为日期数据
to_char将日期数据按指定格式转换为字符串数据
子查询
（1）
查询最高薪水的是谁？
selectenamefromemp_xxxwheresalary=（selectmax（salary）fromemp_xxx）;
selectenamefromemp_xxxwheresalary=（selectmin（salary）fromemp_xxx）;
（2）
谁的薪水比张无忌高？
selectenamefromemp_xxxwheresalary>
（selectsalaryfromemp_xxxwhereename='
张无忌'
）;
研发部有哪些职位？
selectdistinctjobfromemp_xxxwheredeptno=（selectdeptnofromdept_xxxwheredname='
developer'
All
查询谁的薪水比所有叫张无忌的薪水都高？
ALL（selectsalaryfromemp_xxxwhereename='
Any
哪些人的薪水比仸何一个叫张无忌的员工工资高？
（大于最小值）
ANY（selectsalaryfromemp_xxxwhereename='
in
每个部门拿最高薪水的是谁？
selectename,salary,job,deptnofromemp_xxxwhere（deptno,salary）in（selectdeptno,max（salary）fromemp_xxxwheredeptnoisnotnullgroupbydeptno）;
having子查询
哪个部门的人数比部门30的人数多？
selectdeptno,count（*）fromemp_xxxgroupbydeptnohavingcount（*）>
（selectcount（*）fromemp_xxxwheredeptno=30）;
关联子查询
子查询中不再是独立的Sql语句,需要依赖主查询传来的参数,这种方式叫关联子查询
哪些员工的薪水比公司的平均薪水低？
selectename,salaryfromemp_xxxwheresalary<
（selectavg（nvl（salary,0））fromemp_xxx）;
Exists关键字
哪些人是其他人的经理？
（查找有下属的员工）
selectenamefromemp_xxxawhereexists（select1fromemp_xxxwheremgr=a.empno）;
•exists关键字判断子查询有没有数据返回,有则为ture,没有则为false
•Exists不关心子查询的结果,所以子查询中select后面写什么都可以本例中我们写常量“1”
•sql执行顺序从主查询开始,把主查询中的empno数据传入子查询,作为条件中的参数
方法2：
普通子查询
selectenamefromemp_xxxwhereempnoin（selectdistinctmgrfromemp_xxx）;
哪些部门没有员工？
selectdeptno,dnamefromdept_xxxdwherenotexists（select1fromemp_xxxwheredeptno=d.deptno）;
查询语句的基本格式
函数：
组函数
与单行函数如round（）、to_date（）、to_char（）、coalesce（）等不同,单行函数是每行数据返回一行结果,组函数是多行数据返回一行结果。
count/avg/sum/max/min
记得：
组函数忽略空值
sum（）
计算员工的薪水总和是多少？
selectsum（salary）sum_salaryfromemp_xxx;
max（）
min（）
avg（）
组函数：
count/avg/sum/max/min如果函数中写列名,默认忽略空值
avg/sum针对数字的操作
max/min对所有数据类型都可以操作
计算最早和最晚的员工入职时间
Selectmax（hiredate）,min（hiredate）fromemp_xxx;
单行函数
字符函数：
upper/lower/initcap/length/lpad/rpad/replace/trim
1）upper转换为大写
2）lower转换为小写
3）initcap转换为首字母大写
4）length取长度
5）lpad左补丁
6）rpad右补丁
7）replace字符替换
8）trim去除前后的空格
Lpad（）
将ename字段设置为10个长度,如果不够左边用“*”号补齐
selectlpad（ename,10,'
*'
）fromemp_xxx；
rpad（）
将ename字段设置为10个长度,如果丌够右边用“#”号补齐
selectrpad（ename,10,'
#'
）fromemp_xxx;
mod（）
求salary对5000取模
selectsalary,mod（salary,5000）fromemp_xxx;
集合操作
数据库中的查询语句的结果集（ResultSet）：
集合A和集合B
集合A：
{1,2,3,4,5}
集合B：
{1,3,5,7,9}
A与B的合集：
{1,2,3,4,5,7,9}
A与B的交集：
{1,3,5}
A与B的差集：
A-B{2,4}
两个结果集必须结构相同
当列的个数、列的顺序、列的数据类型一致时,我们称这两个结果集结构相同
只有结构相同的结果集才能做集合操作
2）合集union和unionall
union和unionall的区别
union去掉重复记录,unionall不去重
union排序,unionall不排序
在满足功能的前提下,优选unionall
3）交集intersect
4）差集minus（两个集合做减法）
union去重,排序
selectename,salaryfromemp_xxxwheredeptno=10unionselectename,salaryfromemp_xxxwheresalary>
6000
unionall不去重,不排序
selectename,salaryfromemp_xxxwheredeptno=10unionallselectename,salaryfromemp_xxxwheresalary>
交集intersect
selectename,salaryfromemp_xxxwheredeptno=10intersectselectename,salaryfromemp_xxxwheresalary>
6000;
差集minus
selectename,salaryfromem
升级会员 