mysql实例

查询所有学生信息  
SELECT * FROM tb_student;

查询所有课程名及学分 
SELECT couname, credit FROM tb_course;

查询所有女学生的姓名  
//ALTER TABLE tb_student ADD COLUMN gender bit DEFAULT 1;
SELECT stuname FROM tb_student WHERE gender=0;

查询所有80后学生的姓名、性别和出生日期 
SELECT stuname, if(gender,'男','女'),birth FROM tb_student WHERE birth BETWEEN '1980-1-1' AND '1989-12-31';

查询姓 ’王‘的学生姓名和性别(模糊)
SELECT stuname, gender FROM tb_student WHERE stuname LIKE '王%';

查询姓’王‘名字两个字的学生姓名和性别（模糊）
SELECT stuname, gender FROM tb_student WHERE stuname LIKE '王_';

查询名字中有'义'或'大'字的学生的姓名 
SELECT stuname FROM tb_student WHERE stuname LIKE '%义%'  or stuname LIKE '%大%';

 查询录入了家庭住址的学生姓名(空值)
SELECT stuname FROM tb_student WHERE addr is NOT NULL;

查询没有录入家庭地址的学生姓名(空值)
SELECT stuname FROM tb_student WHERE addr is NULL;

查询选过课的学生的id(去重)
SELECT DISTINCT stuid  FROM tb_score;

查询选过课的学生的名字(去重)
SELECT stuname FROM tb_student WHERE stuid in (SELECT stuid  FROM tb_score);

查询没有选过课的学生的id()  
SELECT stuid FROM (SELECT tb_student.stuid, couid FROM tb_score 
RIGHT JOIN tb_student on tb_score.stuid = tb_student.stuid) as t1 WHERE couid is NULL;

查询没有选过课的学生的名字()
SELECT stuname FROM (SELECT stuname, couid FROM tb_score 
RIGHT JOIN tb_student on tb_score.stuid = tb_student.stuid) as t1 WHERE couid is NULL;


查询学生的家庭住址(去重)
SELECT DISTINCT addr FROM tb_student;

查询男学生的姓名和生日按年龄从大到小排序(排序)
SELECT stuname, birth FROM tb_student WHERE gender=1 ORDER BY birth ASC;

查询年龄最大的学生的出生日期(聚合函数)
SELECT min(birth) FROM tb_student;

查询年龄最小的学生的出生日期（聚合函数）
SELECT max(birth) FROM tb_student;

查询年龄最小的学生的名字和出生日期
SELECT stuname, birth FROM tb_student WHERE birth = (SELECT max(birth) FROM tb_student);

查询课程编号为2的课程的平均成绩(筛选和聚合函数)
SELECT avg(mark)  FROM tb_score WHERE couid=2;

查询学号为101的学生所有课程的总成绩(筛选和聚合函数)
SELECT sum(mark) FROM tb_score WHERE stuid=101;

查询男女学生的人数(分组和聚合函数)
SELECT if(gender,'男','女') as '性别',COUNT(stuid) as '人数' FROM tb_student GROUP BY(gender);

查询每个学生的学号和成绩平均值(分组和聚合函数) 
SELECT stuid,avg(mark) as avgmark FROM tb_score GROUP BY(stuid);  

查询所有学生的平均成绩
SELECT stuname, avgmark FROM (SELECT stuid,avg(mark) as avgmark FROM tb_score GROUP BY(stuid)) as t1 
RIGHT JOIN tb_student ON tb_student.stuid=t1.stuid;

查询平均成绩大于等于90分的学生的学号和平均成绩 
SELECT stuid,avg(mark) as avgmark FROM tb_score GROUP BY(stuid) HAVING avg(mark)>90;  

查询年龄最大的学生的姓名(子查询)  
SELECT stuname FROM tb_student WHERE birth=(SELECT min(birth) FROM tb_student);

查询选课学生的姓名和平均成绩(子查询和连接查询)  
SELECT stuname, avgmark FROM (SELECT stuid, avg(mark) as avgmark FROM tb_score GROUP BY(stuid)) as temp 
INNER JOIN tb_student on temp.stuid=tb_student.stuid;