以学生表Student (Sno,Sname,Ssex,Sage,Sdept),
课程表Course(Cno,Cname,Cpno,Ccredit),
学生选课表SC(Sno,Cno,Grade)
代码如下:
CREATE Table Student(
Sno CHAR(9) PRIMARY KEY,
Sname VARCHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept VARCHAR(20)
);
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('201215121','李勇','男',20,'CS');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('201215122','刘晨','女',19,'CS');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('201215123','王敏','女',18,'MA');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('201215125','张立','男',19,'IS');
CREATE TABLE Course(
Cno CHAR(4) PRIMARY KEY,
Cname VARCHAR(40) not NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);
INSERT INTO Course(Cno,Cname,Ccredit)
VALUES('6','数据处理',2);
INSERT INTO Course(Cno,Cname,Ccredit)
VALUES('2','数学',2);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('4','操作系统','6',3);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('7','Pascal语言','6',4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('5','数据结构','7',4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('1','数据库','5',4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('3','信息系统','1',4);
Create Table SC(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
);
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215121','1',92);
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215121','2',85);
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215121','3',88);
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215122','2',90);
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215122','3',80);
单表查询:
eg1:查询全体学生的详细信息
eg2:查询全体学生的姓名和出生年份
select *
from Student;
select Sname,2021-Sage '出生年份'
from Student;
distinct的使用:
主要是解决两个本来不完全相同的元组在投影到指定的某些列上后,可能会变成相同的行。说白了就是解决查询时,出现取值重复的问题。
例:查询选修了课程的学生学号。
select DISTINCT Sno
from SC;
如果不加distinct的话,就会出现查询结果中的学号重复。
按条件查询
查询条件 | 谓词 |
---|---|
多重条件(逻辑运算) | AND,OR,NOT |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN,NOT IN |
字符匹配 | LIKE,NOT LIKE |
空值 | IS NULL,IS NOT NULL |
比较 | =,>,<,>=,<=,!=,<>,!>,!<等 |
1、确定范围查询
BETWEEN AND 可以用来查找属性值在指定范围内的元组
eg: 查询年龄在20~30岁之间的学生姓名,系别和年龄。
SELECT Sname,Sdept,Ssex
FROM student
WHERE Sage BETWEEN 20 AND 23;
2、确定集合查询(IN)
谓词IN可以用来查找属性值属于指定的集合内
eg:查询计算机科学系(CS),数学系(MA)和信息系(IS)学生的姓名和性别。
select Sname,Ssex
FROM student
WHERE Sdept IN ('CS','MA','IS')
3、字符匹配(like)
字符匹配主要是用于模糊查询,其中有两个通配符,%和_。
%:代表任意长度(长度可为0)的字符串,例如a%b,就是以a开头,b结尾的任意长度的字符串。
_:代表任意单个字符,例如a_b表示以a开头,以b结尾的长度为3的任意字符串。
如果like后面的匹配串没有通配符,那么可以用=运算符取代like,
例如where Sno like '201215121’就等价于where Sno = ‘201215121’
eg1:查询所有姓刘的学生的姓名,学号,性别。
eg2:查询姓“欧阳”且全名为三个汉字的学生的姓名。
eg3:查询所有不姓刘的学生的姓名,学号和性别。
SELECT Sname,Sno,Ssex
from student
where Sname like "刘%";
SELECT Sname
from student
where Sname like "欧阳_";
SELECT Sname,Sno,Ssex
from student
where Sname not like "刘%";
另外匹配串就是含有通配符%或者_的情况,这时就要使用ESCAPE ‘转码字符’ 对通配符进行转义。
例如:DB_Design课程的课程号和学分。
select Cno,Ccredit
from Course
where Cname like 'DB\_Design' ESCAPE '\';
4、涉及空值的查询
eg1: 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩,查询缺少成绩的学生学号和相应的课程号。
eg2:查询所有有成绩的学生学号和课程号。
SELECT Sno,Cno
FROM SC
where Grade is NULL; -- “IS”不能用=代替
SELECT Sno,Cno
FROM SC
where Grade is not NULL;
ORDER BY子句
order by子句主要是用来对查询结果按照一个或多个属性列进行
升序(ASC)或者降序(DESC)排列,默认为升序。
eg1:查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列。
eg2:查询全体学生的情况,查询结果按所在系的系号升序排列,同一个系中的学生按年龄降序排列。
SELECT Sno,Grade
from SC
where Cno = '3'
ORDER BY Grade DESC; -- 注意是对Grade降序
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC; -- 这里的系号是默认升序排列。
聚集函数(COUNT,SUM,AVG等)
聚集函数 | 解释 |
---|---|
COUNT(*) | 统计元组的个数 |
COUNT([DISTINCT/ALL] <列名>) | 统计一列中值的个数 |
SUM([DISTINCT/ALL] <列名>) | 计算一列值的总和(必须是数值型) |
AVG([DISTINCT/ALL] <列名>) | 计算一列值的平均值(必须是数值型) |
MAX([DISTINCT/ALL] <列名>) | 求一列值的最大值 |
MIN([DISTINCT/ALL] <列名>) | 求一列值的最小值 |
如果制定DISTINCT短语,则表示在计算时要取消指定列中的重复值。
eg1:查询学生的总人数。
eg2:查询选修了课程的学生人数
eg3:查询选修了1号课程的平均成绩
eg4:查询选修了1号课程的最高分数
eg5:查询学生‘201215122’选修课程的总学分数(Ccredit)
SELECT COUNT(*)
from student;
SELECT COUNT(DISTINCT Sno)
from SC;
SELECT AVG(Grade)
from SC
WHERE Cno = '1';
SELECT max(Grade)
from SC
WHERE Cno = '1';
SELECT SUM(Ccredit)
from SC,Course
where Sno = '201215122' AND SC.Cno = Course.Cno;
GROUP BY子句
Group By子句将查询结果按某一列或者多列的值分组,值相等的为一组。对查询结果分组的目的是为了细化聚集函数的作用对象。如果为对查询结果分组,聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一组,即每一组都有函数值。简单的说就是对某个或者某些属性进行单独分组,然后在这个分组的基础上,统计一些数据等。比如对班级进行分组Group by Class_No,然后统计班级人数大于30人的班级和人数,HAVING Count(Student) > 30。
eg1:查询选修了三门以上课程的学生学号。
eg2:查询平均成绩大于等于90分的学生学号和平均成绩。
SELECT Sno
from sc
GROUP BY Sno
HAVING COUNT(*) > 2;
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;
可能有人会想用下面的语句进行查询:
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade) >= 90
GROUP BY Sno;
但是是不对的,会下面错误:
HAVING和WHERE的区别
区别在于作用对象不同,where子句作用于基本表或视图,从中选择满足条件的元组。HAVING作用于组,从中选择满足条件的组。简单的说就是where不能用聚集函数(如AVG)作为条件表达式,而HAVING可以
自身连接
表与自己表的连接。
eg:查询每一门课的间接选修课(即先修课的先修课)
SELECT FIRST.Cno,SECOND.Cpno
from Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
嵌套查询
将一个查询块(SELECT-FROM-WHERE)嵌套在另一个查询块的WHERE子句或者HAVING条件语句中,简单说就是在条件中在嵌套一个查询语句。
父查询(外层查询):就是最外层(最上层查询块)块
子查询(内查询):就是下层查询块,说白了就是条件语句内的查询语句块。
注意:子查询的select语句不能使用order by子句,order by子句只能对最总查询结果排序
1、带IN的子查询
eg1:查询与“刘晨”在同一个系学习的学生。
思路:先查询刘晨所在的系,然后在父查询中的条件语句就是学生的系等于(在)刘晨的系。一般思路就是先写子查询,然后再写父查询。
SELECT Sno,Sname,Sdept
FROM student
where Sdept IN(
SELECT Sdept
from student
WHERE Sname = '刘晨'
)
eg2:查询选修了课程名为“信息系统”的学生学号和姓名。
思路:信息系统(Cname)在课程表Course(Cno,Cname,Cpno,Ccredit)中,学生学号(Sno)和姓名(Sname)都在学生表Student(Sno,Sname,Ssex,Sage,Sdept)中,而两张表之间没有任何外键等联系,连接这两张表的只有SC(Sno,Cno,Grade)。
嵌套查询思路:先在课程表中找到课程名为“信息系统”对应的课程号Cno,然后在用sc(Sno,Cno,Grade)表,在这个课程号Cno的基础上找对应的学号Sno,最后用学生Student(Sno,Sname,Ssex,Sage,Sdept),在这些学号的集合内找出学生姓名。
/*不加嵌套的情况*/
SELECT Student.Sno,Sname
from Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno
AND Course.Cname = '信息系统';
/*加嵌套的情况*/
SELECT Sno,Sname
FROM Student
WHERE Sno IN(
SELECT Sno
FROM SC
WHERE Cno IN (
SELECT Cno
FROM Course
WHERE Cname = '信息系统'
)
)
2、带有比较运算符的子查询
eg:找出每个学生超过他自己的选修课程的平均成绩的课程号。
思路:这个题目是相关子查询问题,解决相关子查询不能像不相关查询那样一次将子查询求解出来,然后求解父查询。一般考虑是对子和父中的表进行重命名,然后将父查询嵌入到子查询进行查询。
SELECT Cno
FROM SC x
WHERE Grade > (
SELECT AVG(Grade)
FROM SC y
WHERE x.Sno = y.Sno
);
这个很难确认是那个学生的课程号等,下面去除所有数据就很清楚的看见是哪位同学对应的的课程号
SELECT *
FROM SC x
WHERE Grade > (
SELECT AVG(Grade)
FROM SC y
WHERE x.Sno = y.Sno
);
3、带有EXISTS谓词的子查询
EXISTS代表存在,带有EXISTS的子查询返回的是布尔型,即逻辑真“true”或者逻辑假“false”。使用存在量词EXISTS后,若内层查询结果非空,则外层的where子句返回真,否则返回假。
eg1:查询所有选修了1号课程的学生姓名
eg2:查询没有选修1号课程的学生姓名。
eg3:查询选修了全部课程的学生姓名。
-- 查询所有选修了1号课程的学生姓名
SELECT Sname
from Student
where EXISTS (
SELECT *
FROM SC
WHERE Student.Sno = Sno AND Cno = '1'
);
-- 查询没有选修1号课程的学生姓名。
SELECT Sname
from Student
where not EXISTS (
SELECT *
FROM SC
WHERE Student.Sno = Sno AND Cno = '1'
);
-- 查询选修了全部课程的学生姓名。
/*这个题目的意思找出选修了所有课程的学生,
但是选修了所有课程怎么表示是一个问题,
有人可能想在eg1中的内查询里面将所有课程号一一并列就可以了啊,
但是课程新的课程是会增加的,所以只能表示当前的所有课程号,
一旦课程增加或者删除那么就得不到想要的结果了。而且没有全称量词,
这个时候就想到类似于逆否命题等价的情况了。
也就可以换个说法就是没有一个课程是他不选修的。或者说不存在他没有选修的课程。*/
SELECT Sname
FROM Student
WHERE NOT EXISTS (
SELECT *
FROM Course
WHERE NOT EXISTS (
SELECT *
FROM SC
WHERE Sno = Student.Sno
AND Cno = Course.Cno
)
);
集合查询
就是对多个select语句的结果进行集合操作(并操作UNION,交操作INTERSECT和差操作EXCEPT)。但是mysql目前只支持并操作。
注意:参与集合操作的各查询结果的列数必须相同,对应项的数据类型必须相同。
1、并操作(UNION)
查询选修了课程1或者选修了课程2的学生学号。
SELECT Sno
FROM SC
WHERE Cno = '1'
UNION
SELECT Sno
FROM SC
WHERE Cno = '2';
修改数据
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
eg1:将学生201215121的年龄修改为22岁。
eg2:将所有学生的年龄增加1。
eg3:将计算机科学系全体学生的成绩置零。
-- 将学生201215121的年龄修改为22岁。
UPDATE Student
SET Sage = 22
WHERE Sno = '201215121';
-- 将所有学生的年龄增加1。
UPDATE Student
SET Sage = Sage + 1;
-- 将计算机科学系全体学生的成绩置零。
UPDATE SC
SET Grade = 0
WHERE Sno in (
SELECT Sno
FROM Student
WHERE Sdept = 'CS'
)
删除数据
DELETE
FROM <表名>
[WHERE <条件>];
注意:DELETE语句删除的是表中的数据,而不是关于表的定义。
-- 删除学号为201215128的学生记录
DELETE
FROM Student
WHERE Sno = '201215128';
-- 删除所有学生的选课记录
DELETE
FROM SC;
-- 删除计算机科学系所有学生的选课记录
DELETE
FROM SC
WHERE Sno IN (
SELECT Sno
FROM Student
WHERE Sdept = 'CS'
);
作为一个小白,有可能会出现错误,如果各位发现错误,非常感谢能够评论我的错误,我会积极改进。加油!!!