2019-05-12

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