数据库技术课程复习5---MySQL语言(2)(单表查询)

2022-08-06

MySQL语言(单表查询

    • 0.学习前言
    • 1.语句格式
    • 2.选择表中的若干列(Select)
    • 3.选择表中的若干元组(Where)
    • 4.对查询结果排序(Order by)
    • 5.使用集函数
    • 6.对查询结果分组(Group by)

0.学习前言

单表查询理解起来是比较简单的,关键要注意格式化指令的使用方法,一起加油鸭!

1.语句格式

语句格式

SELECT [ALL|DISTINCT] <目标列表达式> [<目标列表达式>] …
FROM <表名或视图名> [<表名或视图名> ][ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]

其中的字符意义如下:

SELECT子句:指定要显示的属性列
FROM子句:指定查询对象(基本表或视图),当查询对象为多个表的时候,就是多表连接查询了
WHERE子句:指定查询条件
GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用集函数。
HAVING短语:筛选出只有满足指定条件的组
ORDER BY子句:对查询结果表按指定列值的升序或降序排序

下面逐一详细举例分析,单表查询可分为以下五类:
一、选择表中的若干列
二、选择表中的若干元组
三、对查询结果排序
四、使用集函数
五、对查询结果分组

2.选择表中的若干列(Select)

查询指定列SELECT Sno,Sname FROM Student;
查询全部列SELECT * FROM Student;
查询经过计算后的值
如计算出生年份可将年龄用2020减后输出:SELECT Sname,2020-Sage FROM Student;
同时也可以输出任意字符串设置大小写(LCASE):SELECT Sname,‘Year of Birth: ’,2018-Sage,LCASE(Sdept) FROM Student;
还可以给输出结果取别名(这个别名的作用是 把输出表格的第一行,即属性集变为自己设置的别名):SELECT Sname NAME,’Year of Birth:’ BIRTH,2018-Sage BIRTHDAY, LCASE(Sdept) DEPARTMENT FROM Student;
结果如下:

mysql> select sname NAME,'Year of Birth:' BIRTH,2019-sage BIRTHDAY,lcase(sdept) DEPARTMENT from student;
+----------+----------------+----------+------------+
| NAME     | BIRTH          | BIRTHDAY | DEPARTMENT |
+----------+----------------+----------+------------+
| 张三     | Year of Birth: |     1998 | cs         |
| 李四     | Year of Birth: |     1996 | is         |
| 王五     | Year of Birth: |     2000 | ma         |
| 马六     | Year of Birth: |     1999 | cs         |
| 苏三     | Year of Birth: |     1999 | is         |
| 刘七     | Year of Birth: |     2000 | is         |
| 刘三姐   | Year of Birth: |     1996 | is         |
| 欧阳锋   | Year of Birth: |     1995 | ma         |
| 欧阳大侠 | Year of Birth: |     1996 | ma         |
| 陈冬     | Year of Birth: |     2000 | is         |
| 张成民   | Year of Birth: |     2000 | cs         |
+----------+----------------+----------+------------+
11 rows in set (0.00 sec)

3.选择表中的若干元组(Where)

(1)使用distinct短语消除输出结果中的重复行:SELECT DISTINCT Sno FROM SC;这个不加distinct的时候默认为all,即全部输出,包括重复的行。
注意 DISTINCT短语的作用范围是所有目标列

例:查询选修课程的各种成绩

错误的写法

SELECT DISTINCT Cno,DISTINCT Grade FROM SC;

正确的写法

SELECT DISTINCT Cno,Grade FROM SC; 

(2)使用where字句对行进行筛选
where后应为bool表达式,常用的查询条件如下表:

使用谓词 BETWEEN … AND … 和 NOT BETWEEN … AND …
如:查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。

SELECT Sname,Sdept,Sage FROM   Student WHERE Sage BETWEEN 20 AND 23;

使用谓词 IN <值表>, NOT IN <值表> 。 <值表>:用逗号分隔的一组取值
如:查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。

SELECT Sname,Ssex FROM  Student WHERE Sdept IN ( 'IS''MA''CS' );

使用LIKE字符串匹配,’%’ 表示任意长度任意字符(长度可以为0),’_'表示一个任意字符
如:查询名字中第2个字为“三"字的学生的姓名和学号。

SELECT Sname,Sno FROM Student WHERE Sname LIKE ‘_三%';

查询所有不姓刘的学生姓名。

SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE '刘%'

如果查询对象包含%或_,可以使用换码字符将通配符转义为普通字符
如:查询以"DB_"开头,且倒数第3个字符为 I的课程的详细情况。

  SELECT  * FROM   Course WHERE  Cname LIKE  ‘DB\_%I_ _‘ ;

使用谓词 IS NULL 或 IS NOT NULL

某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno FROM SC WHERE Grade IS NULL;

用逻辑运算符AND和 OR来联结多个查询条件
查询计算机系年龄在20岁以下的学生姓名。

SELECT Sname FROM  Student WHERE Sdept= 'CS' AND Sage<20;

4.对查询结果排序(Order by)

使用ORDER BY子句可以按一个或多个属性列排序。升序:ASC;降序:DESC;缺省值为升序
当排序列含空值
ASC:排序列为空值的元组最先显示
DESC:排序列为空值的元组最后显示

例子:查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。

SELECT Sno,Grade FROM  SC WHERE  Cno= ' 3 ' ORDER BY Grade DESC;

limit m,n表示从第m行开始取出n个元组,即输出n行,m缺省值为0。
如:输出第一名。

SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC limit 0,1;

多属性排序
例子:查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。(先对Sdept升序,再对Sage降序)

SELECT  * FROM  Student ORDER BY Sdept,Sage DESC; 

5.使用集函数

5类主要集函数
计数 COUNT([DISTINCT|ALL] *),COUNT([DISTINCT|ALL] <列名>)
计算总和 SUM([DISTINCT|ALL] <列名>)
计算平均值 AVG([DISTINCT|ALL] <列名>)
求最大值 MAX([DISTINCT|ALL] <列名>)
求最小值 MIN([DISTINCT|ALL] <列名>) ,DISTINCT短语:在计算时要取消指定列中的重复值,ALL短语:不取消重复值,ALL为缺省值

例: 计算1号课程的学生平均成绩。

SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 '

6.对查询结果分组(Group by)

(1)使用GROUP BY子句分组
他可以细化集函数的作用对象:
未对查询结果分组,集函数将作用于整个查询结果
对查询结果分组后,集函数将分别作用于每个组

例: 求选课表中各个被选课程号及相应的选课人数。

SELECT Cno,COUNT(Sno)  FROM  SC GROUP BY Cno; 

使用GROUP BY子句后,SELECT子句的列名列表中只能出现分组属性集函数,因为MySQL中表结构只有二维表,没有嵌套表,不能输出被分组后的多个表。

(2)使用HAVING短语筛选最终输出结果
例: 查询选修了3门以上课程的学生学号。

SELECT Sno FROM  SC GROUP BY Sno HAVING  COUNT(*) >=3;       

HAVING短语与WHERE子句的区别:作用对象不同
WHERE子句作用于基表,从中选择满足条件的元组。
HAVING短语作用于,从中选择满足条件的组。

本文地址:https://blog.csdn.net/qq_41842192/article/details/107294982