表结构
student | sc | course | tesacher |
---|---|---|---|
sid | sid | - | tname |
sname | cid | cid | - |
sage | score | cname | - |
ssex | - | tid | tid |
增删改操作
建表
create table student(
sid varchar(20),
sname varchar(20),
sage number,
ssex varchar(20)
)
插入
insert into student(sid, sname, sage, ssex)
values('001', 'kangyue', 29, 'male')
修改
update sc
set sc.score = '60'
where sc.sid = '001'
删除
删除叶平老师的sc记录
delete sc
from sc, teacher as t, course as c
where sc.cid = c.cid and t.tid = c.tid and t.tname = '叶平'
查询
查询”001”课程比”002”课程成绩高的所有学生的学号
select s.sid
from
(select sid,score from sc where sc.cid = '001') as a,
(select sid,score from sc where sc.cid = '002') as b
where a.sid = b.sid and a.score > b.score
查询平均成绩大于60分的同学的学号和平均成绩
select sc.sid, avg(sc.age)
from sc
group by sc.sid
having avg(sc.score) > 60
sql中的分组
- where 对分组前的属性进行筛选
- having
对分组后的属性进行筛选
使用聚合函数判断
不是用group by默认整个表为一组
查询姓李的老师的个数
select count(t.tid)
from teacher as t
where t.tname like "李%"
sql中的通配符
_ 匹配单字符-> ‘_im’ 匹配 Tim Jim
% 匹配一个或多个字符 -> ‘李%’
[] 匹配单字符 -> ‘[JT]im’
[^] 反向匹配 -> ‘m[^c]%’
也可以使用REGEX()函数匹配正则
查询所有同学的学号,姓名,选课书,总成绩
select s.sid, s.sname, count(sc.id), sum(sc.score)
from student as s, sc
where s.sid = sc.sid
group by sc.sid
使用左连接:
select s.sid, s.sname, count(sc.id), sum(sc.score)
from student as s left join sc on s.sid = sc.sid
group by s.sid
内连接,外连接,左连接,右连接
- 内连接 inner join = join 两边有匹配才选择
- 外连接
- 左连接 left join 左边都选择
- 右连接 right join 右边都选择
查询没学过叶平老师课的学生的学生,姓名
select st.sid, st.sname
from student as st
where st.sid not in
(select s.sid
from sc, course as c, teacher as t
where sc.cid = c.cid and c.tid = t.tid and t.tname = '叶平')
子查询,not in取反
查询所有课程成绩小于60分的同学的学号姓名
select s.sid,s.sname
from student as s
where s.sid not in
(select sc.sid
from sc
where sc.score >= 60)
查询每门课程平均成绩,结果按平均成绩升序,平均成绩相同按课程号降序
select avg(sc.sccore)
from sc
group by sc.cid
order by sc. avg(sc.score) asc, sc.cid desc
排序,升序,降序
查询任何一门课程成绩在70分以上的姓名,课程名,分数
SELECT distinct s.sid,s.sname
FROM student as s,sc
where s.sid = sc.sid and sc.score > 70
distinct去重
查找001课程第三高的学生学号
select top 1 sid
from
(select top 3 sc.sid, sc.score
from sc
where sc.cid = '001'
order by sc.score desc
)
order by score asc
查询第n高
先降序排取前n个,后升序排取第一个。