数据库基础及常用SQL语句

SQL语句

表结构

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个，后升序排取第一个。

#数据库