oracle数据库
三表关联(查询)
where对原始数据进行筛选,将结果呈现出来,这些数据存放于结果集
模糊查询
select * from 表名 where 字段名 like '%???????????% ';
between...and...
常用函数(聚合函数)
COUNT、SUM、AVG、MAX、MIN
select count(统计的字段) from 表名
内连接(显示查询)
inner join … on …
select stu.字段名...
from sn_stuinfo stu inner join sc_score sco on stu.stuno = sco.stuno inner join sn_subject sub on sco.subject_id = sub.subject_id;
分组
group by
select leibie 类别 from ty_test2 group by leibie;
select leibie 类别,sum(shuliang) 数量 from ty_test2 group by leibie;
排序:
select leibie 类别,sum(shuliang) 数量 from ty_test2 group by leibie order by sum(shuliang) desc;
--where 后是不能使用聚合函数的,要改为having
--例:
select leibie 类别,sum(shuliang) 数量 from ty_test2 group by leibie having sum(shuliang)>150 order by sum(shuliang) desc;
序列
--语法
create sequence 名称(例:sn_id)minvalue 1 maxvalue 99999
increment by 1 --(说明:每增加一条数据按1累加)
start with 1 --(从哪里开始)
--查询:
select * from sn_stuinfo;
--添加数据时使用:
insert into sn_stuinfo(id,stuno,username,sex)
values(sn_id.nextval,1001,'yoyo','女');