1、查询学生表信息
SELECT
*
FROM
student;
2、查询学号为150101的学生信息
SELECT
*
FROM
student
WHERE
stuid = '150101';
3、查询学生张林浩、王申的信息
SELECT
*
FROM
student
WHERE
stuname IN ( '张林浩', '赵申' );
4、查询名字第二个是佳字的学生信息
SELECT
*
FROM
student
WHERE
stuname LIKE '_佳%';
5.查询名字第二个是佳字并且籍贯为河北张家口的学生信息
SELECT
*
FROM
student
WHERE
stuname LIKE '_佳%'
AND Native = '河北张家口';
6、按照籍贯统计学生表里,每个地方有多少学生。
SELECT
Native,
count( StuName )
FROM
student
GROUP BY
Native;
7、按照籍贯统计学生表里,每个地方有多少学生。只展示人数大于1的地方。
SELECT
Native,
count( StuName )
FROM
student
GROUP BY
Native
HAVING
count( StuName )> 1;
8、按照籍贯统计学生表里,每个地方有多少学生。并且按照人数升序排序。
SELECT
Native,
count( StuName ) AS c1
FROM
student
GROUP BY
Native
ORDER BY
c1 ASC;
9、按照籍贯统计学生表里,每个地方有多少学生。并且按照人数升序排序,对同样人数的地方,按照地方名称升序排序。
(注意使用convert(native using gbk))
SELECT
Native,
count( StuName ) AS c1
FROM
student
GROUP BY
Native
ORDER BY
c1 ASC,
CONVERT ( Native USING gbk ) ASC;
10、按照籍贯统计学生表里,每个地方有多少学生。并且按照人数升序排序,对同样人数的地方,按照地方名称升序排序。取第3到第4行记录
SELECT
Native,
count( StuName ) AS c1
FROM
student
GROUP BY
Native
ORDER BY
c1 ASC,
CONVERT ( Native USING gbk ) ASC
LIMIT 3,
4;
11、查询籍贯为河北保定学生的各科考试成绩。
SELECT student.StuName,score.PartScore,student.Native from student,score
where student.StuID=score.StuID and student.Native='河北保定';