2020-12-3学习MySQL笔记

1.执行该语句报错。

CREATE TABLE grade(low TINYINT,upp TINYINT,rank CHAR(1));

报错：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘rank CHAR(1))’ at line 1

原因：
rank在MySQL中是函数名称，不能直接引用为列名，需在该名字上打``反引号，不是用’'和""

改正语句：

CREATE TABLE grade(low TINYINT,upp TINYINT,`rank` CHAR(1));

2.执行该语句报错

SELECT Sno,Cno,rank
FROM Scores INNER JOIN grade
ON (Scores.Degree>=grade.low AND Scores.Degree<=grade.upp)
ORDER BY Sno;

报错：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM Scores INNER JOIN grade
ON (Scores.Degree>=grade.low AND Scores.Degree<=gr’ at line 2

原因：
rank在MySQL中是函数名称，不能直接引用，需在该名字上打``反引号，不是用’'和""

改正语句：

SELECT Sno,Cno,`rank`
FROM Scores INNER JOIN grade
ON (Scores.Degree>=grade.low AND Scores.Degree<=grade.upp)
ORDER BY Sno;

3.SQL语句中比较赋值较为简单

Scores.Degree>=grade.low AND Scores.Degree<=grade.upp

存在多个需要比较时，只要列名与列名比较然后进行匹配就可以。最后得出结果
101 3-105 D
101 6-166 B
103 3-245 B
103 3-105 A
105 3-245 C
105 3-105 B
107 3-105 A
107 6-106 C
108 3-105 C
108 6-166 B
109 3-245 D
109 3-105 C

4.相同表中的参数做对比

SELECT sname,s1.sno,s1.degree FROM students,scores as s1,scores as s2
where students.sno=s1.sno and s1.cno=s2.cno and s1.cno='3-105' and s2.sno='109' and s1.degree>s2.degree
ORDER BY sname;

或者

SELECT s1.Sno,s1.Degree
FROM Scores AS s1 INNER JOIN Scores AS s2
ON(s1.Cno=s2.Cno AND s1.Degree>s2.Degree)
WHERE s1.Cno='3-105' AND s2.Sno='109'
ORDER BY s1.Sno;

此时需要对同一张表划分为s1和s2，通过对s1.degree和s2.degree的引用做判断。

5.group by和select多层嵌套
题目：查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

答案：两种答案
（1）

select sno,cno,degree from scores
where degree not in (select max(degree) from scores group by sno) #成绩不等于该学生所考成绩的最大值
and sno in (select sno from scores group by sno having count(cno)>1) #满足该学生所学课程大于1门课的筛选
ORDER BY sno;

（2）

select t1.sno,t2.cno,t2.degree
from scores t2,
(select sno,max(degree) as degree from scores group by sno having count(sno)>1 )as t1 #找出根据sno分组后选学1门课以上的学生最高成绩
where t1.sno=t2.sno #连接t1和t2
and t2.degree<t1.degree
order by sno;