MySQL数据查询
一、数据查询
数据查询是数据库的核心操作。SQL提供了SELECT语句进行数据查询,该语句具有灵活的使用方式和丰富的功能
一般格式为:
SELECT [ALL(显示所有)|DISTINCT(消除重复)] <目标列表达式>[<目标列表达式>]...
FROM <表名或视图名> | <SELECT语句> [AS] <临时派生表名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING<条件表达式>]] /*分组子句*/
[ORDER BY <列名2> [ASC|DESC]]; /*查询结果升降序*/
1.1基础范围查询
基本查询.例程1.1.1:
在表student中查询全体学生的学号与姓名
SELECT Sno,Sname
FROM Student;
SELECT 子句的<目标列表达式> 不仅可以是表中的属性列,也可以是表达式。
例程1.1.2:
查询全体学生的姓名及其出生年分;
SELECT Sname,2020-Sage
FROM Student;
查询消除重复列
SELECT DISTINCT Sage /*DISTINCT 消除重复行 默认是ALL显示全部*/
FROM Student;
范围查询
查询满足条件的元组
以下为常用到的查询条件。
其中仅列举几个重点例程。
确定范围查询 (BETWEEN AND)
查询年龄在20~23岁(包含20与23)之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
确定集合(IN,NOT IN)
查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别
SELECT Sname,Ssex
FROM Student
WHERE Sage IN ( 'CS','MA','IS');
字符匹配( LIKE %、_)
基本格式:
[NOT] LIKE '<匹配串>' [ESCAPE'<换码字符>']
%(百分号)代表任意长度(长度可以为0)的字符串。
_(下横线) 代表任意单个字符。
LIKE等价于 ‘=‘
SELECT *
FROM Student
WHERE Sno LIKE '201215121';
等价于:
SELECT *
FROM Student
WHERE Sno = '201215121';
查询所有刘姓的学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%';
ESCAPE ’ \ ’ 表示“ \ ”,“ \ ”为换码字符 " \ "后面的字符 % 或 _ 不具有通配符的含义。
1.2ORDER BY 子句(查询结果升降序)
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC: /*默认是升序,所以仅设置降序即可*/
1.3聚集函数
聚集函数仅能用到SELECT子句与GROUP BY的 HAVING 子句中去。
例程:查询平均成绩大于等于90分的学生学号与平均成绩
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
1.4GROUP BY 分组语句
例程:求各个课程号及相应的选课人数。
SELECT Con,COUNT(Sno)
FROM SC
GROUP BY Cno; /*根据课程号(Cno)进行分组*/
分组后由HAVING子句进行判断,由于WHERE 子句与HAVING 短语的区别在于作用对象不同。WHERE子句作用于基本表或视图从中选择满足条件的元组。HAVING 短语作用于组,从中选择满足条件的组。
所以WHERE 子句不能用聚集函数作为条件表达式。
1.5连接查询
连接查询可分为等值连接与非等值连接
等值连接:当连接运算符为=时,为等值查询
非等值连接:当连接运算符不为=时,为非等值查询
若在等值连接中把目标列中的重复的属性列去掉则为自然连接。
例程:查询每个学生及其选修课程的情况。
学生情况存放在Student表中,学生选课情况存放在SC表中,所以本查询实际上涉及Student与SC两个表的。这两个表之间的联系是通过公共属性Sno实现的。
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.sno=SC.sno; /*将两表中学号相同的元组连接起来*/
/****改为自然连接****/
SELECT Student.sno,sname,ssex,sage,sdept,Cno,Grade /*消除多余的sno列,仅使用Student表中的sno值*/
FROM Student,SC
WHERE Student.sno=SC.sno;
自身连接:一个表与其自己进行连接,称为表的自身连接。简单的说就是我查我自己
直接看例程吧,直接明了
查询每一门课的间接先修课(先修课的先修课)
SELECT FIRST.Cno ,SECOND.Cpno
FROM Course FIRST , Course SECOND /*将自身表起别名,从未完成自身调用自身*/
WHERE FIRST .Cpno = SECOND .Cno;
外连接
如果把悬浮元组也保存在结果关系中,而在其他属性上填控制(null),那么这种连接就叫做外连接,如果仅保留左边关系R中的悬浮元组就叫左外连接,保留右边关系S中的悬浮元组就叫做右外连接。
对应代码
/*外连接*/
SELECT Student.sno,sname,ssex,sage,sdept,Cno,Grade
FROM R ,S
ON (R.B=S.B);
/*右外连接*/
SELECT Student.sno,sname,ssex,sage,sdept,Cno,Grade
FROM R RIGHT OUTER JOIN S /*右外连接 left左 right右*/
ON (R.B=S.B);
/*左外连接*/
SELECT Student.sno,sname,ssex,sage,sdept,Cno,Grade
FROM R LEFT OUTER JOIN S /*左外连接 left左 right右*/
ON (R.B=S.B);
1.6嵌套查询(子查询出现在WHERE中)
在SQL语言中,一个SELECT-FROM-WHERE语句为一个查询块,将一个查询块嵌套在另一个查询块的WHERE 子句或HAVING短语的条件中,这种查询成为嵌套查询。
例如:
SELECT Sname /*外层查询或父查询*/
FROM Student
WHERE Sno IN (SELECT Sno /*内层查询或子查询*/
FROM SC
WHERE Cno='2')
相关子查询与不相关子查询
子查询的查询条件不依赖于父查询,称为不相关子查询。
如过子查询的查询条件依赖于父查询,这类子查询称为相关子查询或将整个查询语句称为相关嵌套查询。
例如:
SELECT sno,cno
FROM SC X
WHERE Grade >=(SELECT AVG(Grade)
FROM SC Y
WHERE Y.sno=X.sno); /*相关子查询*/
1.7带有ANY(SOME) 或ALL谓词的子查询
用于多值比较,ANY表示任意、ALL表示所有,在使用时必须同时使用比较运算符。
ANY、ALL、IN的等价转换关系表
1.8带有EXISTS谓词的子查询
EXISTS代表存在量词∃。带有EXISTS的谓词的子查询不返回任何数据,只产生逻辑真值"true"或逻辑假值“false”
例程:
SELECT Sname
FROM Student
WHERE EXISTS /*存在返回真值,NOT EXISTS不存在返回真值 */
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1'); /*相关子查询*/
1.9集合查询(查询结果的并、交、差)
集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。
在此仅举一个例程,其他操作使用方法类似
例程:
查询即选修了课程1又选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT /*两表结果进行交操作*/
SELECT Sno
FROM SC
WHERE Cno='2';
1.10基于临时派生表的查询(子查询出现在FROM中)
子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中,这时子查询生成的临时派生表成为主查询对象。
例程:
找出每个学生超过他自己选修课平均成绩的课程号
SELECT Sno、Cno
FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno) AS Avg_SC(avg_sno,avg_grade)
WHERE SC.Sno = avg_sc.avg_sno AND SC.Grade >= avg_sc.avg_grade;