2020-09-08

2020.9.7

数据库小练习

首先建立两个表：

1）请写出计算所有学生的英语平均的英语成绩的sql语句

select studentno,avg(english) from mark GROUP BY studentno;

2）现有五个学生，其学号假定为11，22，33，44，55；请用一条SQL语句实现列出这五个学生的数学成绩及其姓名、学生地址、电话号码

SELECT
mark.math,
cust. NAME,
cust.address,
cust.telno
FROM
cust
JOIN mark ON cust.Studentno = mark.studentno
WHERE
mark.studentno IN (1, 2, 3, 4, 5);

3）查询所有学生的姓名、计算机成绩，按照计算机成绩从高到低排序

SELECT
cust. NAME,
mark.computer
FROM
cust
JOIN mark ON cust.Studentno = mark.studentno
ORDER BY
mark.computer DESC;

4）查询所有总成绩大于240分的学生学号、姓名、总成绩，按照总成绩从高到低排序

SELECT
a.Studentno,
a. NAME,
sum(b.english + b.math + b.computer) zf
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
GROUP BY
a.Studentno,
a. NAME
having zf>240
ORDER BY
zf
DESC;

数据库高级知识

内连接/join：
内连接显示的数据是取⾃2表之间的交集

外连接
左连接：left join
右连接：right join

再来两张形象的图：

分组：(必须配合聚合函数来使用)

group by (后接分组条件)
having (作用和 where 一样，不过分组后的数据用having，分组前的数据用where)

栗子如下：（注意其中的顺序，不能弄乱哇）

SELECT
b.cname, #分组条件
avg(c.chinese + c.math + c.English) pjf
FROM
t_student a
JOIN t_class b ON a.cid = b.id
JOIN t_grade c ON c.sid = a.id
WHERE
c.chinese > 100
GROUP BY
b.cname #分组条件
HAVING
avg(c.chinese) > 105
ORDER BY
pjf DESC
LIMIT 1;

数据排序：
order by 字段 # asc 的一般不写，默认为asc排序
order by 字段 desc

限制数据显示的数量：limit
select * from t_student order by age desc limit 0,5;
0 ： 代表从哪里开始显示
5 ：一共显示多少条

条件语句：
case when…then…

SELECT id,sname,(CASE WHEN age < 30 THEN ‘青年’
WHEN age >= 30 AND age <= 60 THEN ‘中年’
ELSE ‘老年’ END)
FROM t_student

嵌套查询（套娃查询）
把查询结果作为表，把查询结果作为条件，把查询结果作为列展示

select * from
(SELECT b.cname,avg(c.chinese) cc

FROM
t_student a
JOIN t_class b ON a.cid = b.id
JOIN t_grade c ON a.id = c.sid
GROUP BY b.cname) a
where cc >110

聚合函数：
sum 、avg、 min、 max 、count（只能对数字用）

取别名：as

数据库的判断符和 逻辑连接符

表的类型

1、实实在在的表：通过创建的表
2、虚拟表：通过select 操作出来显示的表
3、临时表（temporary）:
当查询的结果很复杂的时候，我们就会把虚拟表的结果存到临时表中，然后直接对临时表做操作，这样可以让查询的过程更加简单。

特点：临时表⽆法被查询到，临时表可以正常的操作，断开连接后，临时表会⾃动销毁