文章目录
一、学习目标二、数表介绍三、SQL简单查询四、SQL复杂查询
一、学习目标
1、掌握
S
E
L
E
C
T
SELECT
SELECT语句的语法格式,各句子的使用; 2、掌握别名的概念、掌握
D
I
S
T
I
N
C
T
DISTINCT
DISTINCT、
A
L
L
ALL
ALL的使用; 3、掌握空值的概念及查询判断条件的表达,了解查询时系统对
N
U
L
L
NULL
NULL值的处理; 4、掌握使用
G
R
O
U
P
B
Y
GROUP BY
GROUPBY子句、
H
A
V
I
N
G
HAVING
HAVING子句、
O
R
D
E
R
B
Y
ORDER BY
ORDERBY子句对查询结果进行排序和分组; 5、掌握
S
U
M
SUM
SUM、
A
V
G
AVG
AVG、
C
O
U
N
T
COUNT
COUNT、
M
A
X
MAX
MAX、
M
I
N
MIN
MIN等聚合函数的用法。
二、数表介绍
本次是数据库中最典型的学生课程表,如下如所示:
tb_sc tb_student tb_course
三、SQL简单查询
use tb;#首先要选择数据库
SELECT * FROM tb_student;
SELECT Sname 姓名,2020-Sage 出生年份, LOWER(Sdept) 所属系院 FROM tb_student;
SELECT Sno FROM tb_sc;
SELECT Sname,Sage FROM tb_student WHERE Sage=19;
SELECT Sname,Sdept,Sage FROM tb_student WHERE Sage NOT IN (20,23);
SELECT Sname ,Ssex FROM tb_student WHERE Sdept NOT IN ('IS','MA','CS');
SELECT * FROM tb_student st ,tb_sc sc WHERE st.Sno='201812119'AND sc.Sno='201812119';
SELECT Sname FROM tb_student WHERE Sname LIKE '刘_礼';
SELECT Sname FROM tb_student WHERE Sname not LIKE '刘%';
SELECT * FROM tb_course WHERE Cname LIKE 'DB#_%i__' ESCAPE '#';
SELECT Sno,Cno FROM tb_sc WHERE Grade IS NOT NULL;
SELECT Sname, Sage, Sdept FROM tb_student WHERE Sage2;
SELECT Sno 学号,GROUP_CONCAT(Grade) score FROM tb_sc WHERE Grade>80 GROUP BY Sno HAVING COUNT(Grade)>2 ;
SELECT SUM(Grade) 总分 FROM tb_sc WHERE Sno='201215121';
其中有两个sql语句有图展示:
SELECT Cno,GROUP_CONCAT(Grade) AS 成绩 ,COUNT(Grade)FROM tb_sc GROUP BY Cno;#根据Cno分类 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210629211750965.png#pic_center) SELECT Sno 学生学号,GROUP_CONCAT(Cno) 课程编码 FROM tb_sc GROUP BY Sno HAVING count(Cno)>2; ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210629211818147.png#pic_center)
四、SQL复杂查询
查询每一门课的间接先修课(即先修课的先修课)
SELECT a.Cno,b.Cpno FROM tb_course a,tb_course b WHERE a.Cpno=b.Cno
查询每个学生选修课程的情况
SELECT * FROM tb_student,tb_sc WHERE tb_student.Sno=tb_sc.Sno;
查询每个学生及其选修课程的情况包括没有选修课程的学生(用外连接)
SELECT tb_student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM tb_student LEFT JOIN tb_sc USING(Sno) ;
查询选修2号课程且成绩在90分以上的所有学生的学号、姓名
SELECT tb_student.Sno,Sname FROM tb_student,tb_sc WHERE tb_student.Sno=tb_sc.Sno AND tb_sc.Cno='2' AND tb_sc.Grade>90;
查询学生201215121选修课程的总学分
SELECT SUM(Grade) 总分 FROM tb_sc WHERE Sno='201215121';
查询每个学生选修课程的总学分
SELECT Sno,GROUP_CONCAT(Grade) 分数 ,SUM(Grade) 总和 FROM tb_sc GROUP BY Sno;
查询每个学生的学号、姓名、选修的课程名及成绩
SELECT tb_student.Sno,Sname,Cname,Grade FROM tb_sc,tb_course,tb_student WHERE tb_student.Sno=tb_sc.Sno AND tb_sc.Cno=tb_course.Cno;
查询与“刘晨礼”在同一个系学习的学生(分别用嵌套查询和连接查询)
-- 嵌套查询
SELECT Sno,Sname,Sdept FROM tb_student WHERE Sdept = (SELECT Sdept FROM tb_student WHERE Sname='刘晨礼');
-- 自身连接查询
SELECT a.Sno,a.Sname,a.Sdept FROM tb_student a,tb_student b WHERE a.Sdept=b.Sdept AND b.Sname='刘晨礼';
查询选修了课程名为“信息系统”的学生学号和姓名
SELECT tb_student.Sno,Sname FROM tb_sc,tb_course,tb_student WHERE tb_student.Sno=tb_sc.Sno AND tb_sc.Cno=tb_course.Cno AND tb_course.Cname='信息系统';
|