2019-05-06

select * from student
select * from lession
select * from grade

-- 基本语法：
-- SELECT * from 表名
-- where 条件
-- GROUP BY 分组的列名
-- HAVING 分组条件
-- ORDER BY 排序

-- 1)查询表student的学号，姓名，班级，把年龄+1岁新列名为‘现在年龄’。
select sid,sname ,age+1 as 现在年龄 from student

-- 2查询学号在3到6之间的学生的学号，姓名，JAVA成绩, 按学号降序排。
SELECT
student.sid,
student.sname,
grade.sgrade,
grade.Type
FROM
student,
grade
WHERE
student.sid = grade.sid
AND student.sid BETWEEN 3 AND 6
AND type = 'java'
ORDER BY student.sid DESC

-- 3)查询男生基本信息按学号降序排
SELECT
*
FROM
student
WHERE
sex = '男'
ORDER BY sid DESC

-- 4)查询学号为1的同学所学的课程和讲课教师
SELECT
lession.type,
lession.teacher
FROM
lession,
student
WHERE
lession.sclass = student.sclass AND student.sid = 1

-- 5)查询姓名为张三的学生的课程名，老师 ，成绩(两种方法)
SELECT
l.type,
l.teacher,
g.sgrade,
s.sname
FROM
lession l,
grade g,
student s
WHERE l.type = g.type
AND l.sclass = s.sclass
AND s.sid = g.sid
AND s.sname = '张三'

SELECT
l.type,
l.teacher,
g.sgrade
FROM
lession l
JOIN grade g ON l.Type = g.Type
JOIN student s ON l.sclass = s.sclass
AND s.sid = g.sid
WHERE s.sname = '张三'

-- 6)查询student表有多少条数据
select count(*) from student

-- 7）查询1号同学的姓名和总成绩（两种方法）
SELECT
sname,
sum(sgrade) AS 总成绩
FROM
student
JOIN grade ON student.sid = grade.sid
WHERE
student.sid = 1
GROUP BY sname

-- 8）查询一班学生的学号，姓名，总成绩
SELECT
student.sid,
student.sname,
sum(sgrade)
FROM
student,
grade
WHERE
student.sid = grade.sid
AND student.sclass = '一班'
GROUP BY student.sid,student.sname

--9)查询平均分大于85，总分大于170的学生学号，姓名，班级
SELECT
student.sid,
student.sname,
avg(sgrade),
sum(sgrade),
student.sclass
FROM
student,
grade
WHERE
student.sid = grade.sid
GROUP BY
student.sid,
student.sname,
student.sclass
HAVING
avg(sgrade) > 85 AND sum(sgrade) > 170