sql查询语句.docx
《sql查询语句.docx》由会员分享,可在线阅读,更多相关《sql查询语句.docx(26页珍藏版)》请在冰豆网上搜索。
sql查询语句
数据定义语言DDL
create数据库对象的创建
alter修改数据库对象
drop删除数据库对象
truncate清空表数据
数据操纵语言DML
insert插入操作
update更新操作
delete删除操作
数据查询语言DQL
select查询操作
事务控制语句DCL
commit提交数据
rollback数据回滚
savepoint保存点
数据类型
数字:
number(n)数字(最长n位)
number(n,m)浮点数(总长n为,小数点后m位)
例:
number(7,2)表示最大数为99999.99
字符串:
char(n)表示定长字符串(方便查询)
最长放入n个字符,放入的数据如果不够n个字符则补空格,无论如何都占n个字符长度。
varchar(n)表示变长字符串(节省空间)
最长放入n个字符,放入的数据是几个长度就占多大空间
varchar2(n)Oracle自己定义的变长字符串
日期
date日期
三种SQL语句总结
建表
createtable表名(
列名1列类型,
列名2列类型,
…..);
插入数据
insertinto表名values(列值,……);
查询
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;
等同于
SQL>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分组查询:
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;
例子:
把时间数据按指定格式输出
selectto_char(sysdate,'yearmonthdddaydy')fromdual;
selectto_char(sysdate,'yyyy/mm/dd')fromdual;
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
例子:
查询谁的薪水比所有叫张无忌的薪水都高?
selectenamefromemp_xxxwheresalary>ALL(selectsalaryfromemp_xxxwhereename='张无忌');
Any
例子:
哪些人的薪水比仸何一个叫张无忌的员工工资高?
(大于最小值)
selectenamefromemp_xxxwheresalary>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>6000
交集intersect
selectename,salaryfromemp_xxxwheredeptno=10intersectselectename,salaryfromemp_xxxwheresalary>6000;
差集minus
selectename,salaryfromem