MySQL--表数据的添加和查询操作

题目：

1. 基于当前的四张表 完成数据的添加操作(每张表添加两条数据)
2. 完成查询操作(课堂内所有Demo)

1-2. 根据生日降序（DESC）[升序ASC]排序查看各学生信息：

SELECT * FROM student ORDER BY BORNDate DESC;
#LIMIT 初始行数 容量数
SELECT * FROM student ORDER BY BORNDate DESC.LIMIT 9,5

查询学生姓名、生日、性别，所在班级名称：

SELECT student.StudentName,student.BornDate,student.Sex,grade.GradeName
FORM student INNER JOIN grade ON student.GradeId = grade.GradeId;

 查询学生姓名，参考科目名，考试成绩：

SELECT student.StudentName,'subject'.SubjectName,result.StudentResult
FROM result,student,subject
WHERE result.StudentNo = student.StudentNo AND result.SubjectNo ='subject'.SubjectNo;
#或
SELECT student.StudentName,'subject'.StudentName,result.StudentResult
FROM result INNER JOIN student ON result .StudentNo = student.StudentNo
INNER JOIN student ON result.SubjectNo ='subject.SubjectNo';

根据学号进行分组，查询各学生总分，平均分，过滤掉平均分80以下的信息：

SELECT student.StudentName AS '学号',SUM(result.StudentResult) AS '总分'，AVG(result.StudentResult) AS'平均分'
FROM result.INNER JOIN student ON result.Student.No =student.StudentNo
GROUP BY (result.StudentNo)
HAVING AVG (result.StudentResult)>=80;

查询班级名大一的女学生信息（子查询实现）：

SELECT grade.GradeID FROM grade WHERE grade.GradeName ='大一';
SELECT * FROM student
WHERE student.Sex = 2
AND student.GradeId = (SELECT grade.GradeID FROM grade WHERE grade.GradeName)='大一';
#连接查询实现：
SELECT * FROM
student.grade
WHERE student.GradeId =grade.GradeID AND student.Sex = 2 AND grade.GradeNaame='大一';

查询student列表所以列的内容：

SELECT * FROM student;
#查询student指定列的内容：
SELECT studentName.Phone FROM student;
#查询student指定列的内容（学号1008）：
SELECT StudentName.Phone FROM student WHEREStudent.No = 1008;
#查询student所以女生的姓名，邮箱：
SELECT StudentName,Eamil FROM student WHERE Sex=2;
#查询课时数在100-110之间的课程名称：
SELECT SubjectName FROM subject WHERE ClassHour BETWEEN 100 AND 110;
#或
SELECT StudentName FROM subject WHERE ClassHour >=100 AND ClassHour <= 110;

查询邮箱为空的学生的姓名和生日（重设列名）:

SELECT StudentName AS '姓名',BoreDate AS '生日'
FROM student
WHERE BoreDate='2002-10-10';
#查询班级编号2或3的学生姓名和邮箱（使用IN关键字）：
SELECT StudentName,Eamil
FROM student
WHERE Grade IN (2-3);
#查询名字中含‘才’字的学生信息：
SELECT * FROM student WHERE StudentName LIKE '%才';
#找出1986年出生的学生信息：
SELECT * FROM student WHERE BoreDate regexp '^1986';

根据科目进行分组，查询各学生总分、平均分、最高分，最低分：

SELECT SubjectNo AS '科目名'，SUM(result.StudentResult) AS '总分',MIN(result.StudentResult) AS '最低分',MAX(result.StudentResult) AS '最高分'，AVG(result.StudentResult) AS '平均分'
FROM result
GROUP BY (result.StudentNo);
#过滤掉平均分80分以下的信息：
SELECT result.StudentNo AS'学号',SUM(result.StudenResult) AS'总分'，AVG(result.StudentResult) AS'平均分'
FROM result
GROUP BY (result.StudentResult)
HAVING AVG (resulT.StudentResult) >=80;
#根据班级编号分组查询学生姓名：
SELECT student.GradeId AS'班级编号',GROUP_CONCA(StudentName) AS'人员名单'
FROM student
GROUP BY (student.GradeId)