-- - 按照性别分组，统计人数
SELECT (SELECT COUNT(*) AS man FROM student WHERE sex='1') AS man,
(SELECT COUNT(*) AS woman FROM student WHERE sex='0') AS woman
FROM student
-- 按照地区分组，统计平均零花钱，只统计男同学的
SELECT addr,AVG(gold) FROM student WHERE sex='1' GROUP BY addr
-- - 按照地区分组，统计人数，只要看到人数超过3个的地区
SELECT addr,COUNT(*) FROM student GROUP BY addr ORDER BY COUNT(*)>3
-- - 查询娄底地区的学生的成绩，展示姓名和成绩
SELECT student.name,score FROM student
INNER JOIN score
ON student.`stuid`=score.`stuid`
WHERE addr='娄底'
-- - 查询女同学的成绩以及其他信息，展示姓名，年龄，地区，成绩
SELECT score,NAME,age,addr FROM student
INNER JOIN score
ON
student.`stuid`=score.`stuid`
WHERE sex='0'
-- - 查询有java成绩的同学的信息，展示姓名和成绩
SELECT NAME,score FROM student INNER JOIN score
ON student.`stuid`=score.`stuid`
WHERE className='java'
SELECT st.name,st.`stuid`,zf
FROM
(SELECT stuid,NAME FROM student) AS st
INNER JOIN
(SELECT stuid,SUM(score)AS zf FROM score
GROUP BY stuid) AS sc
ON st.`stuid` =sc.`stuid`
-- - 查询既有java也有c#成绩的同学，展示学生id
SELECT stuid FROM score
UNION
SELECT stuid FROM score WHERE className='java'
UNION
SELECT stuid FROM score WHERE className='C#'
-- - 查询年龄最大的同学的信息，展示姓名和地区
SELECT NAME,age FROM student WHERE age=(SELECT MAX(age) FROM student)
-- - 查询java成绩最高的同学的信息，展示姓名和电话
SELECT NAME,phone,MAX(score) FROM score
INNER JOIN student ON student.`stuid`=score.`stuid`
WHERE className='java'
SELECT NAME,phone FROM student INNER JOIN
(SELECT MAX(score) FROM score WHERE className='java') AS score
ON student.`stuid`=score.`stuid`
-- - 查新平均分最高的同学的零花钱，展示姓名和零花钱
SELECT NAME,gold FROM student INNER JOIN (SELECT AVG(score) FROM score) AS sc
ON student.`stuid`=score.`stuid`
v
-- - 查询所有男同学，再从男同学中查询年龄大于red'17岁的同学，展示姓名和年龄
SELECT NAME,age FROM student WHERE sex='1' AND age>17
-- - 查询所有同学的平均分和总分，展示姓名和平均分，总分
SELECT NAME,AVG(score),SUM(score) FROM score
INNER JOIN student
ON
score.`stuid`=student.`stuid`
-- - 查询出生日期在2000-05-05到2000-09-09之间的同学，展示姓名和出生日期
SELECT NAME,birth FROM student WHERE birth BETWEEN '2010-05-05' AND '2010-09-09';
-- - 查询java成绩为99的同学的学生信息，展示姓名和电话
SELECT NAME,phone
FROM student
INNER JOIN score
ON student.`stuid`=score.`stuid`
WHERE className=
(
SELECT className FROM score WHERE score=99
);
-- - 对每个同学的成绩数量进行统计，展示姓名和成绩个数
SELECT NAME,gs FROM student s INNER JOIN
(
SELECT stuid,COUNT(*) AS gs FROM score GROUP BY stuid
) a
ON s.`stuid`=a.stuid;
-- - 删除出生年月早的学生信息-
SELECT MAX(birth) FROM student;
DELETE FROM student WHERE birth = '2010-09-21';
你学会了吗？有问题可以私聊小编或留言~
网友评论