Mysql 进阶学习

准备工作

• 初始化数据库

CREATE DATABASE db;
USE db;

• 创建一个学生表

CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
age INT
)

• 创建一个课程表

CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(255),
# 课程学分
credits INT
)

• 创建一个成绩表

CREATE TABLE grade(
gid INT PRIMARY KEY AUTO_INCREMENT,
gsid INT,
gcid INT,
score FLOAT,
CONSTRAINT `fk1` FOREIGN KEY (`gsid`) REFERENCES `student`(`sid`),
CONSTRAINT `fk2` FOREIGN KEY (`gcid`) REFERENCES `course`(`cid`)
)

• 插入测试数据

INSERT INTO student(sname,age) VALUES('张三',15),('李四',13),('王五',17),('赵六',14),('刘七',16)
INSERT INTO course(cname,credits) VALUES('语文',2),('数学',3),('英语',1)
INSERT INTO grade(gsid,gcid,score) VALUES(1,1,75.2),(1,2,56.8),(1,3,36.0),(2,1,98.2),(2,2,72.2),(2,3,88),
(3,1,78.2),(3,2,62.5),(3,3,91),(4,1,86.5),(4,2,98.8),(4,3,36),(5,1,82),(5,2,85),(5,3,79.6)

以上三张表的第一列都是主键自增的，成绩表与学生表和课程表建立了外键，有依赖关系。

一、单表查询

• 查出年龄不等于 16 岁的学生

SELECT * FROM student WHERE age != 16

• 查出年龄在 [13，15，17] 中的学生

SELECT * FROM student WHERE age in (13,15,17)

• 查出姓名以 张 开头的学生

SELECT * FROM student WHERE sname LIKE '张%'

• 将成绩表按分数倒序排列

SELECT * FROM grade ORDER BY score DESC

• 查出学生的序号和总成绩

# 因为这里 score 是小数类型，sum(score) 精度有问题，所以用 as decimal(5,2) 指定结果总长度不超过 5，小数部分长度不超过 2.
SELECT gsid,CAST(SUM(score) AS DECIMAL(5,2)) FROM grade GROUP BY gsid

二、多表查询

• 查出学生的姓名和对应的总成绩

SELECT sname,CAST(SUM(score) AS DECIMAL(5,2)) FROM student,grade WHERE student.sid = grade.gsid GROUP BY gsid 

• 查出学生的姓名，数学得分并按数学分数降序排列

SELECT sname,score FROM student,grade  WHERE student.sid = grade.gsid AND grade.gcid = 2 ORDER BY score DESC

• 查出学生三科中的最高分并列出姓名，科目，得分

SELECT sname,cname,score FROM (SELECT gsid,gcid,score FROM grade ORDER BY score DESC) tmp,student,course 
WHERE tmp.gsid =  student.sid AND tmp.gcid = course.cid GROUP BY tmp.gsid 

• 查出语数英三科的平均分

SELECT cname '科目', CAST(AVG(score) AS DECIMAL(5,2)) '平均分' FROM course,grade WHERE course.cid = grade.gcid GROUP BY gcid

• join 连接
  内连接，左右表必须都不为空才返回，否则不返回。
  注意 on 和 where 关键字在这里的区别，on 是在生成临时表时起作用，表现了返回结果表的条件；where 是在生成了结果表之后再对结果表进行过滤，此时相当于单表的 where 语句。

SELECT a.sname,b.gcid,b.score FROM student a JOIN grade b ON a.sid = b.gsid WHERE b.score > 80

• left join 和 right join 和 inner join (join) 类似，区别是 left join 以左表为主，只要左表不为空，即返回该行数据，right join 与之相反。