收集的一些SQL（更新中）

moonights

于 2009-07-20 17:01:14 发布

阅读量112

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： SQL Oracle

本文链接：https://blog.youkuaiyun.com/moonights/article/details/83431906

oracle 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍SQL查询技巧，包括条件查询、比较查询、逻辑运算、模糊查询等，并通过实例讲解了如何使用IN、LIKE、BETWEEN AND等关键字，以及如何进行嵌套查询和分组查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql语句的条件查询有：in,like,beteen...and,is null,is not null等，在条件中还可以包含select子查询。
sql比较查询运算符有：= ,>,<>,<,!=,<>,>=,<=,!>,!<;一般有数字比效，字符串比较，日期比效。
sql逻辑运算符共有3种：and,or,not
sql谓词in查询：例
select * from 定单 where zhigong (not) in('a','b','c','d')
sql模糊like查询：
例1 select * from zhigong where xingming not like '王%'
例2 select * from zhigong where xingming not like '王_'
sql空值查询：例 select * from zhigong where xingbie is null
例2 select * from zhigong where xingbie is not null
sql限制范围查询：between...and 例 select * from zhigong where gongzi between 1300 and 1900
sql消除重复字段数据distinct查询例 select distinct gongzi from zhigong
sql单表嵌套查询 select * from canghu where mianji>(select mianji from cangku where cangkuhao='wh')
sql多表嵌套查询
例1 select * from zhigong where cangkuhao in (select cangkuhao from cangku where chengshi='北京')
例2 select * from dingdan where zhigonghao in(select zhigonghao from zhigong where cangkuhao in ( select cangkuhao from cangku where chengshi='beijing'))
例3 select * from dingdan where zhigonghao in(select zhigonghao from zhigong where gongzi>1200 and cangkuhao in(select cangkuhao from cangku where chengshi='北京'))and 订购日期 between '2003-09-03'and '2006-01-01'
例4 select * from zhigong where zhigonghao in(select zhigonghao from dinggoudan)
例5 select * from zhigong where cangkuhao in(select cangkuhao from cangku where chengshi is not null) and gongzi between 1300 and 2100 and zhigonghao in (select zhigonghao from dinggoudan where jine not between 8000 and 12000)
例6 select * from zhigong where gongzi between(select gongzi from zhigong where zhigonghao='ad') and (select gongzi from zhigong where zhigonghao='df') and cangkuhao in (select cangkuhao from cangku where chengshi='beijin')
分组查询分组查询关键词是group by 分组之前的关键词要用where 分组这后要用having
例 select cangkuhao,max(gongzi) as zuodagongai,min(gongzi)as zuixiaogongzi sum(gongzi) as gongzizonghe,avg(gongzi) as pinjungongzi,count(*)as zhigongrenshu from zhigong where gongzi>(select gongzi from zhigong where zhigonghao='aa') group by cangkuhao having avg(gongzi)>1760
*/

快速清空表内容：
例 truncate table xxxx \ truncate table user.xxxx 注：user为数据库的用户，就是清空user用户的xxxx表内容。

复制一个表中的数据到另外一个表：
例 create table yyy as select * from uuu。即把uuu表中的数据复制到yyy中。

同样可以复制到同服务器上的oracle中的其他用户里（用user为例）：
create table user.yyy as select * from uuu