sql查询语句.docx

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