Oracle
分页
关键字 ROWNUM
SELECT * FROM(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM 分数表) A (A 别名)
WHERE ROWNUM <= 3) (ROWNUM <=3 从第几条开始到第三条)
WHERE RN >= 2 (RN 别名 从第二条开始到第几条)
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT *
FROM 分数表
WHERE id >= 2 // 加了条件
ORDER BY 分数 DESC) A (ORDER BY 分数 DESC) // 加了排序 已分数为主 DESC 进行降序排列
WHERE ROWNUM <= 3)
WHERE RN>= 1;
删除外键
alter table 表名 drop constraint 外键约束名
模糊查询
SELECT 字段 FROM 表 WHERE 某字段 Like 条件
原创地址 https://blog.csdn.net/qq_39356357/article/details/77869435
在他的基础上做的题,因为笨! 所以记录进行分析, 可以试着直接做他的题,看不懂的再来看我的
group by 是分组函数
表
select (需要展示的条件) from (表名) group by (列名)
select SNO from SC group by SNO;
结果
加入其他条件,展示平均分大于60 SNO和平均分
1) select SNO , avg(score) from sc group by SNO having avg(score)> 60;
having 需要紧跟 group by 之后 对结果进行筛选,
2) select SNO , avg(score) from sc group by SNO having avg(score)> 60 order by SNO DESC;
结果
(1)
(2)
select
s.sno,
s.sname,
a.选择总科目,
a.总分
from
(select (子查询(应该是吧?) 创建的新表 对 sc 表进行改变)
sno,
count(cno) 选择总科目 ,
sum(score) 总分
from
sc
group by
sno) a 别名 a
right outer join (右链接 以右表为主,进行查询 所以以 s表为主)
student1 s 别名 s
on 关键字 类似group by 后面的 having
s.sno=a.sno;
-----------(结束)
and a.总分>200 加上and
where a.总分>200 加上where
两种结果
select count(tname) from teacher where tname like '刘%'; -----like 关键字 模糊查询使用
好菜啊 搞了 好一个小时才出来
select
s.sno
from
student1 s
where
s.sno
not in 取反(取相反的结果)
(select 获取学习谌燕学生的id(sno)
distinct 去重
sc.sno
from
sc sc,
(select 获取谌燕 教的课程
c.cno
from
course c,
teacher t
where
t.tno = c.tno
and
t.tname = '谌燕'
)a
where
sc.cno = a.cno
);
select st.* from
sc a, sc b, 为什么定义两次,因为下面同一个表要使用两次,所以 这里要定义两次
student1 st
where
a.cno='c001'
and
b.cno='c002'
and
st.sno=a.sno
and
a.sno = b.sno;
select 跟上面那个差不多
s.sno
from
student1 s ,(select
distinct
sc.sno
from
sc sc,
(select
c.cno
from
course c,
teacher t
where
t.tno = c.tno
and
t.tname = '谌燕'
)a
where
sc.cno = a.cno
)a
where
s.sno
= a.sno;
select * from sc a join sc b on a.sno=b.sno where a.cno='c001' and b.cno='c002' and a.score < b.score
select * from sc a ,sc b where a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score < b.score;
注意约束 两种写法都可以
select distinct sname from student1 s,(select * from sc where score<60)a 方法有很多种
where s.sno=a.sno;
select s.sno ,s.sname,count(sc.cno) from
student1 s
left join
sc
on
s.sno = sc.sno
group by
s.sno,s.sname
having
count(sc.cno)!=(select count(distinct cno)from course);
select
stu.sno,
stu.sname,
count(sc.cno)
from
student1 stu
left join
sc
on
stu.sno=sc.sno
group by
stu.sno,
stu.sname
having
count(sc.cno)<(select count(distinct cno)from course);
select
st.*
from
student1 st,
(select distinct sno from sc ) h
where
st.sno=h.sno
and
st.sno<>'s001';
select st.* from
student1 st,
(select distinct sno from sc ) h
where
st.sno=h.sno
and
st.sno!='s001'; <> 等于 !=
select * from
sc
left join
student1 st
on
st.sno=sc.sno
where
sc.sno!='s001'
and
sc.cno
in
(select cno from sc where sno='s001');