Oracle数据库语法查询大全（附实战操作）

置顶柒八九.

已于 2022-11-06 15:07:06 修改

阅读量2.8w

点赞数 54

分类专栏：数据库技术专区文章标签： mysql 数据库 oracle

于 2022-04-22 14:02:28 首次发布

本文链接：https://blog.csdn.net/wlc_1111/article/details/124325142

版权

数据库技术专区专栏收录该内容

3 篇文章 9 订阅

订阅专栏

接触数据库的同志，数据查询经常会遇使用，对于小白甚至‘’高手‘’来说有些命令语句也不好拿捏，有的还要不停百度，是不是挺浪费时间。本文章带你走进日常数据查询，也劳烦老板们抬手点个收藏，有想法的欢迎交流，指正，下面开始！

注意：重点操作是第三章《实战》哦，欢迎指正，不断更新中…
（上次更新增加重复数据查询、删除，详见第二章中第6点的5和6小节）

一. 简单增删改

注：1. 复制仅复制表的结构或数据，其他信息不会进行复制，比如（主键、外键、唯一键、索引等）
2. WHERE 1=2 指查询的结果为空，若写成 WHERE 1=1 ，则会将整个表结构及其表数据复制过去

1、表结构

1.插入表结构  《 alter table 表名 add (字段名 字段类型 默认值 是否为空); 》
alter table T_JS add primary key(id);                                --增加主键
alter table T_JS add (xm varchar(50) default '空' not null);         --增加单个列字段
alter table T_JS add (xm varchar(50),xb varchar(10));                --增加多个列字段
alter table T_JS add constraint ck_JS_xb check(xb in ('男','女'));   --增加性别检查约束
alter table T_JS add constraint ck_JS_nl check(nl>0 and nl<=130);    --增加年龄检查约束

2.修改表结构  《 alter table 表名 modify (字段名 字段类型 默认值 是否为空); 》
alter table T_JS rename to T_JS_bak;                   --修改表名称                             
alter table T_JS modify (xm varchar(50),xb char(10));  --修改多个列字段类型 
alter table T_JS rename column dw to dwmc;             --修改列名称

3.删除表结构
alter table T_JS drop primary key(id);                 --删除主键
drop table T_JS;                                       --删除整个表及数据
alter table T_JS drop column dw;                       --删除单个列 
alter table T_JS drop (xm,xb,...);                     --删除多个列

4.复制表结构
create table T_JS_bak as select * from T_JS where 1=2;               
create table T_JS_bak like T_JS;                                     

5.复制表结构和数据
create table T_JS_bak as select * from T_JS;

2、表数据

1.插入表数据
insert into T_JS values (1,' 张三','男',....);          --插入所有列的值
insert into T_JS(id,xm,xb) values (1,'张三','男');      --插入对应列的值           

2.修改表数据
update T_JS  set xh='20221111' where xh='20223333';     --更新满足where条件的数据
update T_JS  set xh='20221111';                         --更新所有数据

3.删除表数据
delete from T_JS  where id=1;                           --删除满足where条件的数据
delete from T_JS;                                       --删除所有数据，保留日志，慢
truncate table T_JS;                                    --删除所有数据，不保留日志，快

4.复制表数据
insert into T_JS_bak select * from T_JS;                             --复制相同表数据
insert into T_JS_bak( id,xm...) select id,xm... from T_JS;           --复制不同表数据

二. 常用语法查询

1、关于=><=、between…and…、like、in、and、or 语句

< 小于、<= 小于等于、> 大于、>= 大于等于、!= 不等于
betwee…and…的时候，必须左小右大，左大有小查不出来值，等同于>= and <=
in 包含，相当于多个 or （not in不在这个范围中）
and为并，or为交，无先后顺序，and的优先级比or高，想要or先执行加小括号

1.查询表T_JS大于等于5，小于等于10  
select * from T_JS  where num>=5 and num<=10
select * from T_JS  where num between 5 and 10

2.查询表T_JS 所有性(xm)张..的人
select * from T_JS  where xm like '张%';

3.查询表T_JS 包含(xm)超并且是男性(xb)
select * from T_JS where xm like '%超%' and xb='1';

4.查询表T_JS 包含张(xm)或单位在海淀(dw)的，并且是男性(xb)
select * from T_JS where (xm like '%张%' or dw like '%海淀%') and xb=1;

5.查询表T_JS 名称(xm)为张三、李四的人
select * from T_JS where xm in ('张三'，'李四');

2、关于 to_char、to_number、to_date 、cast 语句

--oracle
-to_char：转到字符串，例如
	日期转到字符串：to_char(sysdate,'yyyy-mm-dd')  
-to_number：转到数字，例如
	字符串转数字：to_number('100')
-to_date：转到日期，例如
	字符串转日期：to_date('2017-01-01','yyyy-mm-dd')
	
--mysql
-cast：转到字符串，例如
	日期转到字符串：cast(now() as char)  或  DATE_FORMAT(NOW(), '%Y-%m-%d') 
-cast：转到字符串，例如
	字符串转到数字：cast('100' as unsigned)
-cast：转到字符串，例如
	字符串转到日期：cast('19981122' as date)  或  STR_TO_DATE('2017-01-06','%Y-%m-%d')

3、关于 max、min、avg、sum 、count 语句

1.查询T_JS 中缴费(num)最大、最小、平均、总和
select max(num) from T_JS;
select min(num) from T_JS;
select avg(num) from T_JS;
select sum(num) from T_JS;

2.查询T_JS 中姓别(xb)为男性的所有人数
select count(xb) 男性人数 from T_JS where xb='男'

4、关于 round、trunc 语句

round()：数值取值，四舍五入；trunc()：数值截取，非四舍五入

1.查询教师T_JS中金额  例如：1234.567，保留2位小数（需要四舍五入）   ---返回结果 1234.57
select round(1234.567,2) from  T_JS; 
或者                                 ---采用cast与decimal结合，cast表示: cast(字段名 as 转换的类型 )
select cast(1234.567 as decimal(7,2)) from T_JS;  ---decimal(7,2)表示数值中共有7位，保留2位小数


2.查询教师T_JS中金额  例如：1234.567，保留2位小数（不需要四舍五入）  ---返回结果 1234.56
--oralce
select trunc(1234.567,2) from T_JS;
--mysql
select truncate(1234.567,2) from T_JS;

5、关于distinct 、decode、listagg 语句

distinct()：去重；decode(替换)；listagg()：转行

1.查询T_JS 性别(xb)去重
select distinct(xb) from T_JS;

2.分组转行表T_JS中李四（xm）得多个手机号（sjh），并用，分开
select xm, listagg(sjh,',') within group (order by xm) as sjh from T_JS where xm='李四' group by xm;

3.查询教师表T_JS（xb）中1、2更换为男、女
---oracle
select xm,decode(xb,'1','男','2','女') as sex from T_JS;
---mysql中
select xm,IF(xb='1','男','女') as sex from T_JS;

6、关于 order by、group by、having 语句

group by ：分组查询；having：查询排序，常配合asc、desc使用（重复数据查询）

1.查询T_JS 姓名（xm）排序
select * from T_JS order by xm; (默认升序)
select * from T_JS order by xm desc; (降序)

2.查询T_JS 所在单位（dw）人数
select dw,count(dw) from T_JS group by dw;

3.查询T_JS 男女性别（xb）人数
select xb,COUNT(xb) FROM T_JS where xb IN (1, 2) GROUP BY xb;

4.查询T_JS 所在单位（dw）人数,并且人数大于100
select dw,count(dw) from T_JS group by dw having count(dw)>100;

5.查询T_JS 工号（xh）大于1的重复数据   （根据单字段查询重复数据）
#根据单个字段查重
select xh,count(*) from T_JS group by xh having count(*)>1;     ---查询重复数据数

select * from T_JS where xh in 
(select xh from T_JS group by xh having count(xh)>1);           ---查询重复数据信息
或者   
#using 当同名字段作连接条件，可代替on更好用，可以使用多个字段作为条件（using(id)== on.a.id=b.id）
select * from T_JS inner join 
(select xh from T_JS group by xh having count(xh)>1) as t using(xh);  

#删除以上重复数据信息，根据xh查询，id作为唯一性判断
delete from T_JS where id in                                    
(select t.id from (
select * from T_JS
where xh in (select xh from T_JS group by xh having count(xh)>1)
and id not in (select min(id) from T_JS group by xh having count(xh)>1)) as t   ---mysql特殊需要增加一层嵌套和赋值
);

6.查询T_JS 工号（xh）、姓名（xm）大于1的重复数据  （根据多字段查询重复数据，可同第5点单字段）
#根据多个字段查重
select xh,xm,count(*) from T_JS group by xh,xm having count(*)>1;

select * from T_JS where (xh,xm) in (select xh,xm from T_JS group by xh,xm having count(xh)>1);
或者
select * from T_JS inner join (select xh,xm from T_JS group by xh,xm having count(xh)>1) as t using(xh,xm);

#删除以上重复数据信息，根据xh，xm判断，id作为唯一性判断
delete from T_JS where id in
(select t.id from (
select * from T_JS 
where (xh,xm) in (select xh,xm from T_JS group by xh,xm having count(xh)>1)
and id not in (select min(id) from T_JS group by xh,xm having count(xh)>1)) as t  ---mysql特殊需要增加一层嵌套和赋值
);

7、关于 case when 语句

case when…:：判断条件，满足返回真，不满足返回假 ;例如：case when 1 then ‘男’ when 0 then ‘女’ else ‘未知’ end

1.查询教师T_JS男女性别（xb）所在比例    --如果性别为男女，一般1为男、2为女）
select count(1) as 人口总数,
sum(case when xb='1' then 1 else 0 end) 男生,
sum(case when xb='1' then 1 else 0 end)*1/count(1) 男生所在比,
sum(case when xb='2' then 1 else 0 end) 女生,
sum(case when xb='2' then 1 else 0 end)*1/count(1) 女生所占比,
sum(case when xb='0' then 1 else 0 end) 其他,                  ----考虑到个别情况，'0'代表其他性别，这句可删除
sum(case when xb='0' then 1 else 0 end)*1/count(1) 其他所占比   ----考虑到个别情况，'0'代表其他性别，这句可删除
from T_JS;

8、关于 left join / right join / inner join…on 语句

left join：（左连接），以左表为基础，返回左表所有与右边等值的记录
right join：（右连接），以右表为基础，返回右表所有与左边等值的记录
inner join：(等值连接)，返回两表等值的记录

1.根据T_dw表查询教师T_JS的单位（dw）
select a.*,b.dw from T_JS a left join T_dw b on a.dw=b.dw

9、关于 length、concat、substr 语句

length()：字符串长度；concat()：字符串拼接；substr()：字符串截取

1.查询教师T_JS 身份证号(sfzj)的长度
select length(sfzh) from T_JS;

2.查询教师T_JS 姓名（xm）和身份证号（sfzh）拼接  
select concat(xm,sfzh) from T_JS;
select concat(concat(xm,' '),sfzh) from T_JS   = select xm||' '||sfzh from T_JS;    ---两个字段用空格分开

3.截取教师T_JS 身份证号（sfzh）出生日期  
select substr(sfzh,7,4) from T_JS  where length(sfzh)=18;   --(身份证号确认是18位的，从第7位开始，取4位)

10、关于 union all、union、intersect、minus 语句

union all：表示不去掉重复数据
union：表示去掉重复数据
intersect：表示交集，共有的数据
minus：表示差集，先查出第一个，然后减去第二个的数据

1.查询本科生T_BKS和研究生T_YJS的所有专业（zy）数据           --保证查询字段一致
select xh,zy from T_BKS
union 
select xh,zy from T_YJS;

三、实战（重点）

⾝份证的前2位代表：省级政府代码
3、4位是地、市级政府代码
5、6位为县、区级政府的代码
7到14位为出⽣年份，包括年、⽉、⽇
第17位表⽰性别，偶数位⼥，奇数为男
第18位随机⽣成

1、根据身份证号查询教师的性别(xb)

注：Mod(a,b) ：判断奇偶数，例如mod(id,2)=1 是指id是奇数，mod(id,2)=0 是指id是偶数

select xm,case mod(substr(sfzh,17,1),2)when 0 then '女' else '男' end as sex 
from T_JS where length(sfzh)=18;
										        	--性别是身份证号第17位，截取1位																		
--oracle
select xm,decode(mod(to_number(substr(sfzh, 17, 1)), 2),0,'女','男') as sex 
from T_JS where length(sfzh)=18;

--mysql
select xm,case if(length(sfzh)=18, cast(substring(sfzh,17,1) as UNSIGNED)%2,
 if(length(sfzh)=15,cast(substring(sfzh,15,1) as UNSIGNED)%2,3)) 
 when 1 then '男' when 0 then '女' else '未知' end as sex
from T_JS;

2、根据身份证号查询教师的性别(xb)人数

select t.sex 性别,count(t.sex) 总数 from 
(select case mod(substr(sfzh,17,1),2)when 1 then '男' when 0 then '女' else '未知' end as sex 
from T_JS where length(sfzh)=18) t group by t.sex;             

--oracle
select sex 性别,count(sex) 总数 from (select decode(mod(to_number(substr(sfzh,17,1)), 2),0,'女','男') as sex 
from T_JS where length(sfzh)=18) group by sex; 

--mysql
select t.sex 性别,count(t.sex) 总数 from 
(select case if(length(sfzh)=18, cast(substring(sfzh,17,1) as UNSIGNED)%2, 
if(length(sfzh)=15,cast(substring(sfzh,15,1) as UNSIGNED)%2,3)) 
when 1 then '男' when 0 then '女' else '未知' end as sex from T_JS) t
group by t.sex;

3、查询教师性别(xb)所占百分比，并保留最后两位小数

–两种（round、cast）取值不同用法，如果想要取整数%，可以把保留最后2位小数换成0 （特殊的：建议先对性别（xb）去一下重，看需求，考虑到可能会有其他性别，当然我也不知道…（1）你可以再复制一个其他、其他所在比，（2）你就把范围缩到1和2中，加一个条件where xb in(1,2) ）

select count(1) as 人口总数,
 sum(case when xb='1'then 1 else 0 end) 男生,
 concat(round(sum(case when xb='1' then 1 else 0 end)*100/count(1),2),'%') 男生所在比,
 sum(case when xb='2' then 1 else 0 end) 女生,
 concat(round(sum(case when xb='2' then 1 else 0 end)*100/count(1),2),'%') 女生所占比
from T_JS;
或
select count(1) as 人口总数,
 sum(case when xb='1' then 1 else 0 end) 男生,
 concat(cast(sum(case when xb='1' then 1 else 0 end)*100/count(1) as decimal(15,2)),'%') 男生所在比,
 sum(case when xb='2' then 1 else 0 end) 女生,
 concat(cast(sum(case when xb='2' then 1 else 0 end)*100/count(1) as decimal(15,2)),'%') 女生所占比
from T_JS;

4、根据身份证号(sfzh)求出生日期

--oracle
select to_date(substr(sfzh,7,8),'yyyy-mm-dd') as 出生日期 from T_JS where length(sfzh)=18;

--mysql
select cast(substring(sfzh,7,4) as date) as 出生日期 from T_JS where length(sfzh)=18;

5、根据身份证号查询教师年龄

--oracle
select to_char(sysdate,'YYYY')-substr(sfzh,7,4) as 年龄 from T_JS where length(sfzh)=18;   --从身份证号第7位，截取4位,得到出生日期                     

--mysql
select xh,xm,date_format(NOW(),'%Y') - substring(sfzh,7,4) as 年龄 from T_JS where length(sfzh)=18;
或
select xh,xm,(year(NOW()) - substring(sfzh,7,4)) 年龄 from T_JS where length(sfzh)=18;

6、根据身份证号(sfzh)查询教师年龄段(nl)在54-64岁的人

--oracle
select count(1) as 在54-64岁总数 from 
(select to_char(sysdate,'YYYY')-substr(sfzh,7,4) as nl from T_JS where length(sfzh)=18)
where nl>'54' and nl<'65';

--mysql
select count(1) as 在54-64岁总数 from 
(select date_format(NOW(), '%Y')-substr(sfzh,7,4) as nl from T_JS where length(sfzh)=18) nl
where nl>54 and nl<65;

7、查询教师姓名(xm)排名的前3位

注意不同数据库中substr(xm)语句的取值位置

--oralce
select * from (select substr(xm,0,1) as 姓氏,count(substr(xm,0,1)) as 姓氏人数排名 from T_JS
group by substr(xm,0,1) having count(substr(xm,0,1))>1 
order by count(substr(xm,0,1)) desc)
where rownum <=3;

--mysql
select * from (select substr(xm,1,1) as 姓氏,count(substr(xm,1,1)) as 姓氏人数排名 from T_JS
group by substr(xm,1,1) having count(substr(xm,1,1))>1 
order by count(substr(xm,1,1)) desc) t                  ---需要赋予别名
limit 3;

8、根据身份证号(sfzh)查询教师所在省份排名前三的人数

如果库里有码表，直接匹配；如果库里没有省份码表，这个略微麻烦，只能这样

select *from(SELECT
count(*) as renshu,
 case substr(a.sfzh,0,2)
  when '11' then '北京市'
  when '12' then '天津市'
  when '13' then '河北省'
  when '14' then '山西省'
  when '15' then '内蒙古自治区'
  when '21' then '辽宁省'
  when '22' then '吉林省'
  when '23' then '黑龙江省'
  when '31' then '上海市'
  when '32' then '江苏省'
  when '33' then '浙江省'
  when '34' then '安徽省'
  when '35' then '福建省'
  when '36' then '江西省'
  when '37' then '山东省'
  when '41' then '河南省'
  when '42' then '湖北省'
  when '43' then '湖南省'
  when '44' then '广东省'
  when '45' then '广西壮族自治区'
  when '46' then '海南省'
  when '50' then '重庆市'
  when '51' then '四川省'
  when '52' then '贵州省'
  when '53' then '云南省'
  when '54' then '西藏自治区'
  when '61' then '陕西省'
  when '62' then '甘肃省'
  when '63' then '青海省'
  when '64' then '宁夏回族自治区'
  when '65' then '新疆维吾尔自治区'
  when '71' then '台湾省'
  when '81' then '香港特别行政区'
  when '82' then '澳门特别行政区'
  else '其他'
  end AS province
 FROM T_JS a 
 group by case substr(a.sfzh,0,2)
    when '11' then '北京市'
    when '12' then '天津市'
    when '13' then '河北省'
    when '14' then '山西省'
    when '15' then '内蒙古自治区'
    when '21' then '辽宁省'
    when '22' then '吉林省'
    when '23' then '黑龙江省'
    when '31' then '上海市'
    when '32' then '江苏省'
    when '33' then '浙江省'
    when '34' then '安徽省'
    when '35' then '福建省'
    when '36' then '江西省'
    when '37' then '山东省'
    when '41' then '河南省'
    when '42' then '湖北省'
    when '43' then '湖南省'
    when '44' then '广东省'
    when '45' then '广西壮族自治区'
    when '46' then '海南省'
    when '50' then '重庆市'
    when '51' then '四川省'
    when '52' then '贵州省'
    when '53' then '云南省'
    when '54' then '西藏自治区'
    when '61' then '陕西省'
    when '62' then '甘肃省'
    when '63' then '青海省'
    when '64' then '宁夏回族自治区'
    when '65' then '新疆维吾尔自治区'
    when '71' then '台湾省'
    when '81' then '香港特别行政区'
    when '82' then '澳门特别行政区'
    else '其他'end  order by renshu desc ) where rownum <=3;

四.视图

1、简单创建、删除

--创建
create [or replace] [force] view 视图名称 as 
	select *from 表名 where 条件 [with check option ]/[with read only]

--删除
drop view 视图名称

or replace ：若试图已存在，重新创建该视图
force ：不管表是否存在，都会创建该视图
with check option ：插入或修改的数据行必须满足视图定义的约束
with read only ：视图只读，不能修改

2、其他有深意，暂时先到这吧…

柒八九.

关注

54
点赞
踩
359

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录