前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL之单表查询

SQL之单表查询

作者头像
wsuo
发布2020-07-30 22:47:15
1.7K0
发布2020-07-30 22:47:15
举报
文章被收录于专栏:技术进阶之路

学习下面的内容之前请先执行这些代码,确保数据库和表存在 !

附上下面要用到的数据库和表的 SQL 语句,在数据库管理页面新建一个查询然后使用 CV 大法转移过去执行即可:

代码语言:javascript
复制
-- 创建数据库 schoolTest
create database schoolTest CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 使用数据库 schoolTest
use schoolTest;

-- 创建 Student 表(学生表)
create table student
(
    Sno   varchar(9) primary key, -- 学号
    Sname varchar(20), -- 姓名
    Ssex  varchar(2), -- 性别
    Sage  smallint, -- 年龄
    Sdept varchar(20) -- 所在系
);

-- 创建 Course 表(课程表)
create table Course
(
    Cno     varchar(4) primary key, -- 课程号
    Cname   varchar(40), -- 课程名
    Cpno    varchar(4), -- 先行课
    Ccredit smallint, -- 学分
    foreign key (Cpno) references Course (Cno) -- 外键关联到课程表的课程号,代表该课程之前必须上的课
);

-- 创建 SC 表(学生选课表)
create table SC
(
    Sno   varchar(9), -- 学号
    Cno   varchar(4), -- 课程号
    Grade smallint, -- 成绩
    primary key (Sno, Cno), -- 两个主键
    foreign key (Sno) references student (Sno), -- 外键关联到学生表的学号
    foreign key (Cno) references Course (Cno) -- 外检关联到课程表的课程号
);

-- 插入学生数据
insert into student
values ('201215121', '李勇', '男', '20', 'CS'),
       ('201215122', '刘晨', '女', '19', 'CS'),
       ('201215123', '王敏', '女', '18', 'MA'),
       ('201215125', '张立', '男', '19', 'IS');
-- 因为有外键约束,所以必须按照顺序来
insert into Course values ('2', '数学', null, 2);
insert into Course values ('6', '数据处理', null, 2);
insert into Course values ('4', '操作系统', '6', 3);
insert into Course values ('7', 'PASCAL语言', '6', 4);
insert into Course values ('5', '数据结构', '7', 4);
insert into Course values ('1', '数据库', '5', 4);
insert into Course values ('3', '信息系统', '1', 4);

-- 学生选课数据
insert into SC values ('201215121', '1', 92),
                      ('201215121', '2', 85),
                      ('201215121', '3', 88),
                      ('201215122', '2', 90),
                      ('201215122', '3', 80);

建完表之后是这样的:

下面请一定跟着我动手操作哦,这样印象才能更深刻!

我觉得学习 SQL 最重要的就是这一块内容,因为这是应用层,也就是说以后从事后端操作数据库就是用的这一内容,掌握了之后以后不管有什么业务需求你都可以搞定。

1、基本语法

代码语言:javascript
复制
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
       FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)      
                   [AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
代码语言:javascript
复制
SELECT子句:指定要显示的属性列
FROM子句:指定查询对象(基本表或视图)
WHERE子句:指定查询条件
GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语:只有满足指定条件的组才予以输出
ORDER BY子句:对查询结果表按指定列值的升序或降序排序 

2、单表查询

查询只涉及一个表的查询成为单表查询

2.1、选择表中的若干列
2.1.1、查询指定列:

查询全体学生的学号与姓名。

代码语言:javascript
复制
SELECT Sno,Sname
FROM Student;

查询全体学生的姓名、学号、所在系。

代码语言:javascript
复制
SELECT Sname,Sno,Sdept
FROM Student;
2.1.2、查询全部列:

查询全体学生的详细记录:

代码语言:javascript
复制
SELECT  Sno,Sname,Ssex,Sage,Sdept 
FROM Student; 
或
SELECT  *
FROM Student; 

注意这个 select * from ,相信我,这将会是你使用最频繁的 SQL 语句!

SELECT 子句的 <目标列表达式> 不仅可以为表中的属性列,也可以是表达式:

例如:查全体学生的姓名及其出生年份。

代码语言:javascript
复制
SELECT Sname,2020-Sage          /*假设现在为2020年*/
FROM Student;

查询全体学生的姓名、出生年份和所在的院系,要求用小写字母表示系名。

代码语言:javascript
复制
SELECT Sname,'Year of Birth: ',2020-Sage,LOWER(Sdept)
FROM Student;

可以使用列别名改变查询结果的列标题,只需加个空格即可:

代码语言:javascript
复制
SELECT Sname NAME,'Year of Birth:'  BIRTH, 2014-Sage  BIRTHDAY,LOWER(Sdept)  DEPARTMENT
FROM Student;
2.2、 选择表中的若干元组
2.2.1、消除取值重复的行

如果没有指定 DISTINCT 关键词,则缺省为 ALL

栗子:查询选修了课程的学生学号。

代码语言:javascript
复制
SELECT Sno   FROM SC;
	等价于:
SELECT ALL  Sno  FROM SC;

输出结果为:

指定 DISTINCT 关键词,去掉表中重复的行,DISTINCT 是不同的意思。

代码语言:javascript
复制
SELECT DISTINCT Sno
FROM SC;

执行结果为:

2.2.2、查询满足条件的元组 ☆

这部分内容是重点中的重点,请大家一定重点掌握。

常用的查询条件如图:

a、比较大小

查询计算机科学系全体学生的名单。

代码语言:javascript
复制
SELECT Sname
FROM   Student
WHERE  Sdept='CS';

查询所有年龄在20岁以下的学生姓名及其年龄。

代码语言:javascript
复制
SELECT Sname,Sage 
FROM     Student    
WHERE  Sage < 20;

查询考试成绩有不及格的学生的学号。

代码语言:javascript
复制
SELECT DISTINCT Sno
FROM SC
WHERE Grade < 60;

该表中没有不及格的学生,故查询结果为空。

b、确定范围

这里使用谓词:

代码语言:javascript
复制
BETWEEN …  AND  …   NOT BETWEEN  …  AND  …

栗子: 查询年龄在 20~23 岁(包括 20 岁和 23 岁)之间的学生的姓名、系别和年龄:

代码语言:javascript
复制
SELECT   Sname, Sdept, Sage
FROM     Student
WHERE    Sage BETWEEN 20 AND 23; 

查询年龄不在 20~23 岁之间的学生姓名、系别和年龄:

代码语言:javascript
复制
SELECT  Sname, Sdept, Sage
FROM    Student
WHERE   Sage NOT BETWEEN 20 AND 23; 
c、确定集合

使用谓词:`IN <值表>, NOT IN <值表>

栗子:

`查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。

代码语言:javascript
复制
SELECT Sname, Ssex
FROM Student
WHERE Sdept in ('CS', 'MA', 'IS');

查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别。

代码语言:javascript
复制
SELECT Sname, Ssex
FROM Student
WHERE Sdept not in ('CS', 'MA', 'IS');

查询结果为空。

d、字符匹配

使用谓词:[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

<匹配串> 可以是一个完整的字符串,也可以含有通配符%_

代码语言:javascript
复制
% (百分号)  代表任意长度(长度可以为0)的字符串
例如 a%b 表示以 a 开头,以 b 结尾的任意长度的字符串

_ (下横线)  代表任意单个字符。
例如 a_b 表示以 a 开头,以 b 结尾的长度为 3 的任意字符串

在编程语言中 % _ #,一般都为占位符,这里也可以理解为占位符

栗子: 查询学号为 201215121 的学生的详细情况。

代码语言:javascript
复制
SELECT *
FROM Student
WHERE Sno LIKE '201215121';

等价于:

代码语言:javascript
复制
SELECT *
FROM Student
WHERE Sno = '201215121';

查询所有姓刘学生的姓名、学号和性别。

代码语言:javascript
复制
SELECT  Sname, Sno, Ssex
FROM    Student
WHERE   Sname LIKE '刘%';

查询姓"欧阳"且全名为三个汉字的学生的姓名。

代码语言:javascript
复制
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';

查询结果为空。

查询名字中第2个字为"阳"字的学生的姓名和学号。

代码语言:javascript
复制
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '__阳%';

查询结果为空。

查询所有不姓刘的学生姓名、学号和性别。

代码语言:javascript
复制
SELECT   Sname, Sno, Ssex
FROM     Student
WHERE    Sname NOT LIKE '刘%';

但是设想一种情况就是,如果你要查询的属性名就带有_这个字符该怎么办 ? 因为他默认会被看作是占位符。

解决方案就是使用转义字符,是的,这一点和其他编程语言一样,就是用 \

栗子: 查询以"DB_"开头,且倒数第 3 个字符为 i 的课程的详细情况。

代码语言:javascript
复制
SELECT  *
FROM    Course
WHERE   Cname LIKE  'DB\_%i_ _' ESCAPE '\ ' ;

ESCAPE ‘’ 表示“ ” 为换码字符

e、涉及空值的查询

在实际的开发环境中,有时候会有很多数据查出来是空的,这对我们来说是没有用处的数据,我们可以使用 SQL 语句过滤掉这些数据。

使用谓词IS NULL 或 IS NOT NULL

“IS” 不能用 “=” 代替

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

代码语言:javascript
复制
SELECT Sno, Cno
FROM SC
WHERE Grade IS NULL;

查询结果为空。

查所有有成绩的学生学号和课程号。

代码语言:javascript
复制
SELECT  Sno, Cno
FROM    SC
WHERE   Grade IS NOT NULL;
f、多重条件查询

使用逻辑运算符:AND和 OR

AND 的优先级高于 OR 可以用括号改变优先级

栗子: 查询计算机系年龄在 20 岁以下的学生姓名。

代码语言:javascript
复制
SELECT Sname
FROM Student
WHERE Sdept = 'CS'
  AND Sage < 20;

查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。

还记得这个例子吗? 这是使用谓词 IN 确定集合的例子,当时我们是这样实现的:

代码语言:javascript
复制
SELECT   Sname, Ssex
FROM     Student
WHERE    Sdept IN ('CS ','MA ','IS');

现在他也可以这样实现:

代码语言:javascript
复制
SELECT Sname, Ssex
FROM Student
WHERE Sdept = 'CS'
   OR Sdept = 'MA'
   OR Sdept = 'IS';
2.3、 ORDER BY子句

ORDER BY 子句的作用就是字面意思 排序 :

可以按一个或多个属性列排序 升序:ASC;降序:DESC;缺省值为升序

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

代码语言:javascript
复制
SELECT   Sno, Grade
FROM     SC
WHERE    Cno= ' 3 '
ORDER BY Grade DESC;

查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。

代码语言:javascript
复制
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;
2.4、 聚集函数

聚集函数是啥子? 有什么用?其实他就是个函数,方便我们运算。

代码语言:javascript
复制
统计元组个数
     COUNT(*)
     
统计一列中值的个数
     COUNT([DISTINCT|ALL] <列名>)
     
计算一列值的总和(此列必须为数值型)
	 SUM([DISTINCT|ALL] <列名>)	
	 
计算一列值的平均值(此列必须为数值型)
	 AVG([DISTINCT|ALL] <列名>)
	 
求一列中的最大值和最小值
 	 MAX([DISTINCT|ALL] <列名>)
	 MIN([DISTINCT|ALL] <列名>)

栗子: 查询学生总人数。

代码语言:javascript
复制
SELECT COUNT(*)
FROM   Student; 

查询选修了课程的学生人数。

代码语言:javascript
复制
SELECT COUNT(DISTINCT Sno)
FROM   SC;

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

代码语言:javascript
复制
SELECT  AVG(Grade)
FROM    SC
WHERE   Cno='1';

查询选修 1 号课程的学生最高分数。

代码语言:javascript
复制
SELECT  MAX(Grade)
FROM    SC
WHERE   Cno='1';

查询学生 201215121 选修课程的总学分数。

代码语言:javascript
复制
SELECT SUM(Ccredit)
FROM SC,
     Course
WHERE Sno = '201215121'
  AND SC.Cno = Course.Cno; 
2.5、 GROUP BY子句

它的作用是细化聚集函数的作用对象:

  • 如果未对查询结果分组,聚集函数将作用于整个查询结果
  • 对查询结果分组后,聚集函数将分别作用于每个组
  • 按指定的一列或多列值分组,值相等的为一组

栗子:

求各个课程号及相应的选课人数。

代码语言:javascript
复制
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;

查询结果为:

查询选修了 2 门以上课程的学生学号。

代码语言:javascript
复制
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 2;

查询平均成绩大于等于 80 分的学生学号和平均成绩:

下面的语句是不对的

代码语言:javascript
复制
SELECT  Sno, AVG(Grade)
FROM    SC
WHERE AVG(Grade)>=80
GROUP BY Sno;

因为 WHERE子句中是不能用聚集函数作为条件表达式

代码语言:javascript
复制
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 80;

下面我们来看一下HAVING短语与WHERE子句的区别:

  • 作用对象不同
  • WHERE 子句作用于基表或视图,从中选择满足条件的元组
  • HAVING 短语作用于组,从中选择满足条件的组。

以上所有操作的源码如下:

代码语言:javascript
复制
-- 查询全体学生的学号与姓名
SELECT Sno, Sname
FROM Student;

-- 查询全体学生的姓名、学号、所在系
SELECT Sname, Sno, Sdept
FROM Student;

-- 查询全体学生的详细记录
SELECT *
FROM Student;

-- 查全体学生的姓名及其出生年份
SELECT Sname, 2020 - Sage /*假设现在为2020年*/
FROM Student;

-- 查询全体学生的姓名、出生年份和所在的院系,要求用小写字母表示系名
SELECT Sname, 'Year of Birth: ', 2020 - Sage, LOWER(Sdept)
FROM Student;

-- 可以使用列`别名`改变查询结果的列标题,只需加个空格即可
SELECT Sname NAME, 'Year of Birth:' BIRTH, 2014 - Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT
FROM Student;

-- 查询选修了课程的学生学号
SELECT Sno
FROM SC;

-- 指定 DISTINCT 关键词,去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;

-- 查询计算机科学系全体学生的名单
SELECT Sname
FROM Student
WHERE Sdept = 'CS';

-- 查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname, Sage
FROM Student
WHERE Sage < 20;

-- 查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno
FROM SC
WHERE Grade < 60;

-- 查询年龄在 20~23 岁(包括 20 岁和 23 岁)之间的学生的姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

-- 查询年龄不在 20~23 岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

-- 查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别
SELECT Sname, Ssex
FROM Student
WHERE Sdept in ('CS', 'MA', 'IS');

-- 查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别
SELECT Sname, Ssex
FROM Student
WHERE Sdept not in ('CS', 'MA', 'IS');

-- 查询学号为 201215121 的学生的详细情况
SELECT *
FROM Student
WHERE Sno LIKE '201215121';
-- 等价于
SELECT *
FROM Student
WHERE Sno = '201215121';

-- 查询所有姓刘学生的姓名、学号和性别
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';

-- 查询姓"欧阳"且全名为三个汉字的学生的姓名
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';

-- 查询名字中第2个字为"阳"字的学生的姓名和学号
SELECT Sname, Sno
FROM Student
WHERE Sname LIKE '__阳%';

-- 查询所有不姓刘的学生姓名、学号和性别
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';

-- 某些学生选修课程后没有参加考试,所以有选课记录,但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号
SELECT Sno, Cno
FROM SC
WHERE Grade IS NULL;

-- 查所有有成绩的学生学号和课程号
SELECT Sno, Cno
FROM SC
WHERE Grade IS NOT NULL;

-- 查询计算机系年龄在 20 岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept = 'CS'
  AND Sage < 20;

-- 查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别
SELECT Sname, Ssex
FROM Student
WHERE Sdept = 'CS'
   OR Sdept = 'MA'
   OR Sdept = 'IS';

-- 查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列
SELECT Sno, Grade
FROM SC
WHERE Cno = '3'
ORDER BY Grade DESC;

-- 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;

-- 查询学生总人数
SELECT COUNT(*)
FROM Student;

-- 查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno)
FROM SC;

-- 计算 1 号课程的学生平均成绩
SELECT AVG(Grade)
FROM SC
WHERE Cno = '1';

-- 查询选修 1 号课程的学生最高分数
SELECT MAX(Grade)
FROM SC
WHERE Cno = '1';

-- 查询学生 201215121 选修课程的总学分数
SELECT SUM(Ccredit)
FROM SC,
     Course
WHERE Sno = '201215121'
  AND SC.Cno = Course.Cno;

-- 求各个课程号及相应的选课人数
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;

-- 查询选修了 2 门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 2;

-- 查询平均成绩大于等于 80 分的学生学号和平均成绩
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 80;
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2020/04/04 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1、基本语法
  • 2、单表查询
    • 2.1、选择表中的若干列
      • 2.1.1、查询指定列:
      • 2.1.2、查询全部列:
    • 2.2、 选择表中的若干元组
      • 2.2.1、消除取值重复的行
      • 2.2.2、查询满足条件的元组 ☆
    • 2.3、 ORDER BY子句
      • 2.4、 聚集函数
        • 2.5、 GROUP BY子句
        相关产品与服务
        数据库智能管家 DBbrain
        数据库智能管家(TencentDB for DBbrain,DBbrain)是腾讯云推出的一款为用户提供数据库性能、安全、管理等功能的数据库自治云服务。DBbrain 利用机器学习、大数据手段、专家经验引擎快速复制资深数据库管理员的成熟经验,将大量传统人工的数据库运维工作智能化,服务于云上和云下企业,有效保障数据库服务的安全、稳定及高效运行。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档