Oracle系列学习(五)

查询

单表查询

主要涉及到sq|运算符、单行函数、多行函数、排序、去重

首先创建一个学生表stu, 包含如下字段:

学号id、姓名name、年龄age、性别sex、java成绩score、 体重(kg) weight、 星座xz、 生日birth、 寝室room

create table stu(
id varchar2(30) primary key,
name varchar2(30) not null,
age int,
sex varchar2(10)，
score number(4,1)，
weight number(4,1)，
xz varchar2(10)，
birth date ,
room varchar2(10)
);

向表中插入一条学生信息:

insert into stu values( '17820135', '张三' ,19,'男',81,50，'狮子' ,to_date('1999-08-12,'yyyy-mm-dd'),'201');

sq|运算符

Like匹配 通配符**%**

例如:

查询所有姓张的学生

SELECT * FROM STU WHERE NAME LIKE '张%'

查找姓名中带有“文”字的学生信息

SELECT * FROM STU WHERE NAME LIKE '%文%'

between… and…

在两个值之间

例如:

查找班级里体重在60~70kg的学生姓名

SELECT NAME FROM STU WHERE weight BETWEEN 60 AND 70
--等价于
select * from stu where weight>F60 and weight<=70;

in

--查询体重是60或体重是70，或体重是80的同学
--查询体重是60，和体重是70，和体重是80的同学
--都是
select * from stu where weight in (60, 70, 80);
--或者
select * from stu where weight=60 or weight=70 or weight=80;

is null

值为空，即没有填写值

例如:
查找没有填写星座的学生信息

select * from stu where XZ is null;

not

与其他sq|运算符一起使用， 表示取反:

not like not between not in is not null

例如:

查找所有不姓张的学生信息

select * from stu where name not like '张%'

单行函数

使用规范: 函数名(参数)根据参数, 获取函数的结果

to_date

将字符串转换为日期

例如: to_ date( ‘2018-09-01’ ,’ yyyy-mm-dd’ )

表示将2018-09-01这个字符串，转换为日期数据类型

查询所有1999年出生的学生

select * from stu where birth between to_date( '1999-01-01' ,'YYYY-MM-DD') and to_date('1999-12-31','YYYY-MM-DD');

to_char

select * from stu where to_char(birth, 'YYYY' )='1999' ;
select * from stu where to_char(birth, 'YYYY-MM-DD') like '1999%' ;|

length

获取指定字符串值的长度

例如:

查找所有姓名是两个字的学生信息

SELECT * FROM STU WHERE LENGTH(NAME)=2;

查询学生的姓名，以及姓名的长度

SELECT NAME "学生姓名", LENGTH(NAME) "姓名长度" FROM STU ;

concat

拼接两个值，concat(值1,值2)—> 值1值2

查询学生的学号信息(学号信息是指 学号+姓名)

select concat(id,name) as 学生信息 from stu;

修改stu表,规范星座信息，将所有的星座保存为**座.

update stu set xz=concat(xz,'座') where length(xz)=2;

查询截止到当前时间每个学生都活了多少天、多少个月

月

select months_between(sysdate,(BIRTH))As 相差月份 from STU;

select NAME 姓名,FLOOR(months_between(sysdate,(BIRTH)))As 相差月份 from STU;

天

select TO_NUMBER(sysdate- (BIRTH))As 相差天数 from STU;

查询当前日期向后50天是多少天

select sysdate+50 next_day from dual;

查询每个学生名字的最后一个字是什么字

select substr(NAME,LENGTH(NAME),1) from STU

多行函数

多行/聚合/分组函数,作用在多行记录上，

avg(字段)字段的平均值

查询班级java成绩的平均分

SELECT AVG(SCORE) as java平均分 FROM "STU"

查询平均体重

SELECT avg(WEIGHT) as 平均体重 FROM stu

查询班级男同学的平均体重

SELECT avg(WEIGHT) as 平均体重 FROM stu WHERE SEX='男'

sum(字段)字段求和

查询班级所有学生的体重的和

SELECT sum(SCORE) FROM stu

max(字段)最大值

查询最高分的学生的成绩

SELECT max(SCORE) as 最高分数 FROM stu

查询最重的学生的体重

SELECT max(WEIGHT) as 最大体重 FROM stu

注意:多行函数，不能出现在where子包含

例如:查询最重的学生的姓名

SELECT name as 最大体重学生姓名 FROM stu where WEIGHT=(SELECT max(WEIGHT) FROM stu)

min(字段)最小值

查询最轻的学生的体重

SELECT min(WEIGHT) as 最大体重 FROM stu

查询男同学中，最轻的学生的体重

SELECT min(WEIGHT) as 男同学最小体重 FROM stu where sex='男'

count(*)查询数量

查询表中有多少条记录

select COUNT(*) FROM STU

查询班里有多少个男生

select COUNT(*) FROM STU where sex='男'

查询班级里60~ 70分之间的学生有多少个

select COUNT(*) FROM STU WHERE SCORE BETWEEN 60 AND 70

排序

order by字段 desc

默认从小到大升序，添加desc从大到小降序

查询所有学生信息，并按照体重从小到大排列

select *FROM stu ORDER BY WEIGHT

查询所有学生信息，按照成绩从大到小排序

select *FROM stu ORDER BY SCORE DESC

去重distinct

一般distinct都用在select查询字段中

查询本班级有哪些寝室

select DISTINCT room FROM stu 

---

分组

--@1确定按照哪个字段进行分组
--@2确定用哪个分组函数
--@3按照语法组建sq1语句

查询班级里分别有多少个男生和女生

SELECT sex,COUNT(*) FROM stu GROUP BY SEX

查询男生和女生的平均分都是多少

SELECT sex,AVG(SCORE) FROM stu GROUP BY SEX

查询每个寝室有多少成绩大于80分的同学

--注意，从语法上where子句要写在group by前，从逻辑上，where 也是在分组前进行条件过滤
SELECT room,COUNT(*) FROM stu WHERE SCORE>80 GROUP BY ROOM 

通过查询确认一下是否有同年同月同日生的学生

select birth,count(*) from stu WHERE BIRTH is not NULL group by birth;

查询每个男生寝室有多少成绩大于80分的同学和名字

select wm_concat(name) , room, count(*) from stu where sex= '男' and score>80 group by room;

分组后的筛选

having

having子句里添加条件,对group by的结果进行条件筛选

having写在group by之后，并且逻辑上也是先得到分组结果，再执行筛选

查询寝室中人数大于5人的那些寝室和人数

select count(*) from stu group by room having count(*)>5

查询班里有没有同年同月同日生

查询平均分大于75分的寝室的寝室号和平均分