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
网友评论