大白糖奶兔的Blog
大白糖奶兔的Blog
数据库第三章-单表查询

数据案例

insert into student values('201215121','李勇','男',20,'CS');
insert into student values('201215122','刘晨','女',18,'CS');
insert into student values('201215123','王敏','女',18,'MA');
insert into student values('201215125','张立','男',19,'IS');
INSERT INTO course VALUES ('1','数据库', null, 4);
INSERT INTO course VALUES ('2','数学',null,2);
INSERT INTO course VALUES ('3','信息系统', 1 , 4);
INSERT INTO course VALUES ('4', '操作系统', null, 3);
INSERT INTO course VALUES ('5', '数据结构', null, 4);
INSERT INTO course VALUES ('6', '数据处理', null, 2);
INSERT INTO course VALUES ('7', 'PASCAL语言', 6, 4);
INSERT INTO sc VALUES ('201215121','1',92);
INSERT INTO sc VALUES ('201215121','2',85);
INSERT INTO sc VALUES ('201215121','3',88);
INSERT INTO sc VALUES ('201215122','2',90);
INSERT INTO sc VALUES ('201215122','3',80);

语句格式

select [all|distinct] <目标列表达式>[,<目标列表达式>]...
from <表名或视图名>[,<表名或视图名>]...
[where <条件表达式>] #条件查询
[group by <列名1> [having <条件表达式>]] #分组查询
[order by <列名2> [asc|desc]]; # 排序

单表查询

1、选择表中若干列

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_faa9871d356b0286331d72fcc3373ed9.jpg
select sno,sname
from student;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_2de021c3b31abf05c8ed90a05718d7f8.jpg
select sname,sno,sdept #支持交换律
from student;

查询全部列

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_060ca4d60dbd01f7dd3db55311474850.jpg
select sno,sname,ssex,sage,sdept
from student;

select * from student;

查询经过计算的值

select的<目标列表达式>可以是:
- 算术表达式
- 字符串常量
- 函数
- 列别名

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_c6c8258fa778cb90a1f695b754ff414c.jpg

select sname,2020-sage as "出生年份"
from student;

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_fe18889240e8dbe6f8dfab4a574f3696.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_fb92587151c9acabe88ca4ab49ba81c2.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_5d19f4f5e66ebb8f8145e45d77999bad.jpg

select sname,'出生年份:',2020-sage,lower(sdept)
from student;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_22826a7bc2cefb219a37a788bd61ea1d.jpg

使用列别名改变查询结果的列标题

select sname as NAME,'Year of birth' as BIRTH,2020-sage as BIRTHDAY ,
lower(sdept) DEPARTMENT
from student;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_be010671271f3362414e205b7992920f.jpg

2、选择表中的若干元祖

消除取值重复的行

如果没有指定distinct关键字,则缺省为all

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_220f9ed9b2e146367f16b77b5464e8fb.jpg

select sno from sc;

等价于

select all sno from sc;

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_cc98dd3a6182fde753f8840c75f12bc0.jpg

合并显示数据

select distinct sno
from sc;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_5452da59eb900ff1d5baa7fc6ce054d9.jpg

查询满足条件的元组

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_464707c62731235f08d5b6f0ffb471e9.jpg

1.比较大小

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_3d1968e864efdd5ddcca203ee637db6d.jpg
select sname
from student
where sdept = 'CS';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_fcc2826cf9285149bf5e5d9904ff0b45.jpg
select sname,sage
from student
where sage < 20;

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_888f2b09cdb8615d66990919a389a904.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_2df31e85f50e225eb073034583811569.jpg

select distinct sno
from sc
where grade < 60;

加上Distinct之后,只要有一门挂科就会在结果中

2.确定范围(Between ... and .../Not between ... and ...)

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_4bed55724effcb1582cbd6e6303db5df.jpg
select sname,sdept,sage
from student
where sage between 20 and 23;

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_687fe5f7c09422cd55b701c78b43ea8c.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_b77feb05abeffbdb41d05b6e70675366.jpg

select sname,sdept,sage
from student
where sage not between 20 and 30;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_e5920fdad1b016fcf89216e53c57a53a.jpg

3.确定集合(in <值表>,not in <值表> )

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_94229389e18ee1ad07a9755cf81cc278.jpg
select sname,ssex
from student
where lower(sdept) in ('is','ma','cs');

我担心有的sdept大写有的小写,会匹配不到,所以用了lower

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_4cfcc7d4dc8473096d6e9ea8d4589e3e.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_3164e8428a5a2313a4d5f6d1367afe31.jpg

select sname,ssex
from student
where lower(sdept) not in ('is','ma','cs');
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_ab379d4371cabfb5421ee5c3a139bb12.jpg

4.字符匹配

[not] like '<匹配串>' [escape '<转换字符>']

%表示任意长度字符串
_表示

匹配固定字符串
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_db64bf2c6f78b93c0028f1998276c17e.jpg
select *
from student
where sno like '201215121';

或者

select *
from student
where sno = '201215121';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_e237faa9f3ebb22ae726998a54ae7ded.jpg
匹配串为含通配符的字符串
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_d5b4048d15f1bafcd6cab6875d1a16d7.jpg
select sname,sno,ssex
from student
where sname like '刘%';

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_7e63c5b282566b75a1975542d854c651.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_69dfb5eef55f7c50a7705a1c8aa5158b.jpg

select sname
from student
where sname like '欧阳__';
#一个中文两个字符,所以是两个下划线

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_919e11942cdefb506fabe49d6c360eca.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_34c71de253d8e9a9c94063a19c4783bd.jpg

select sname,sno
from student
where sname like '__阳%';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_a8cc7484710dad7249d6adb5e1a8227a.jpg
select sname,sno
from student
where sname not like '刘%';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_77894a3fa2fa319d308ec7ca463f6e8e.jpg
使用换码字符将通配符转义为普通字符

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_1d908210b1641d4f696a1bf56ec58ffd.jpg

万一下划线理解为通配符咋办呢

select cno,ccredit
from course
where cname like 'DB\_Design' escape '\';
#告诉数据库\后面的字符是普通字符
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_d74c003cedeb91817e634afa98ff3391.jpg
select *
from course
where cname like 'DB\_%i__' escape '\';
# 先按要求把DB_表示出来,涉及到转换普通字符,添加‘\’
# 再按照要求把倒数第三位的i表示出来,i之前的字符任意长度%

4.涉及空值的查询

is null
#或
not null

is不能用‘=’代替

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_dfaa2bad1d0262140d599719cee37621.jpg

select sno,cno
from sc
where grade is null;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_0222a279ff8c79bf908b1d2802451f06.jpg
select sno,cno
from sc
where grade is not null;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_19cfc847c419921eb093a49becdbdc5c.jpg

6.多重条件查询

逻辑运算符:and和or联结多个查询条件
- and的优先级高于or
- 可以用括号改变优先级

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_aaa4f579f2ab8a2b027388b921390e2d.jpg

select sname
from student
where sdept = 'CS' and sage < 20;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_d935665f75daf35c35e2e71d07e39685.jpg
select sname,ssex
from student
where sdept = 'IS' or sdept = 'MA' or sdept = 'CS';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_b26e4380d82c8c70a2d5e7af4fea12e0.jpg

3、Order by子句

  • 可以按一个或多个属性列排序
  • 升序:asc(小的在前,大的在后) 降序:desc(大的在前,小的灾后);缺省值升序
    当排序列含空值时:
    asc:排序列为空值的元组最后显示
    desc:排序列为空值的元组最先显示
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_5687e3efeef9a15d03e505c752743f4b.jpg
select sno,grade
from sc
where cno = '3'
order by grade desc;
#降序排列

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_8a6eeac2355df1ebb0ab17a765b82766.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_6b090d1a6ecda93fe484540680c699f9.jpg

select *
from student
order by sdept,sage;

先按照sdept升序排列,再按照sage升序排列

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_6750adef56cdc74bac035e0eafa65c3f.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_a6e4729eb5d9fbf99cc6ae7e47f28d03.jpg

select top 3 sno
from sc
where cno = '3'
order by grade desc;

上面这种不行用下面的

select sno
from sc
where cno = '3'
order by grade desc
limit 3;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_c8444e9a225934e35e44ac3a3d11dd1e.jpg

4、聚集函数

计数元组个数COUNT

  • count(*):返回表的所有元组数(行数)
  • count ([distinct|all] <列名>)
    返回某列值的个数,不包括空值
    默认为all
    distinct相同的值只计算为一个
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_364424273a87052105322e818adb1abe.jpg
select count(*)
from student;

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_5e86bb7814979595d8f5b503958da388.jpg

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_ff1ecc1b25a7a9ffda29f69cd254368a.jpg

select count(distinct sdept)
from student;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_a4acb58d65131214cf71e9247991bdb0.jpg

计算综合SUM

  • sum([distinct|all] <列名>)
    返回某个数值列的和,空值计算为0
    默认为all,返回某列所有值的和
    distinct,返回某列不同值的和(相同的值只计算一个)
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_8ac9d238d8964597df43fbc9d0ccffeb.jpg
select sum(ccredit)
from course;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_3339f434fcae163971568d64ad80b304.jpg

计算平均值AVG

  • avg([distinct|all]<列名>)
    返回某个数值列的平均值,空值计算为0
    默认为all,返回某列所有制的平均值
    distinct,返回某列不同值的平均值(相同的只计算一个)
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_ad2bd0a2ed79fde48678aada92c11b5b.jpg
select avg(grade)
from sc
where cno = '1';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_5c31f5f1690f047c48ddc5ee47152b2b.jpg

计算最大值MAX(字符型,日期型都可以计算)

  • max([distinct|all]<列名>)
    返回某列的最大值,空值不参与运算
    字符串型数据以其ASCII码相比较
    可比较的数据类型:整型、实型、字符型、日期型
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_ea3a08337e5ab8eff89afb842b9f965c.jpg
select max(grade)
from sc
where cno = '3';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_655decc844aa505a08420c4b5a799a37.jpg

计算最小值MIN(字符型,日期型都可以计算)

  • min([distinct|all]<列名>)
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_308d0bd84cf667089ebef6c05d361cc6.jpg
select min(grade)
from sc
where cno = '3';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_9e3c39460d2f36bdac42a4c25ff5215c.jpg
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_033752fed838df4a3b5fa18bf4bdbc6d.jpg
select count(distinct sno)
from sc;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_cec75d61f74216f93eecbeb8541ad8cb.jpg
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_c8bed91c2cef64cf5d9531548bfc46e4.jpg
select count(distinct cno)
from sc;

5、Group by子句

细化聚集函数的作用对象
- 未对查询结果分组,聚集函数将作用于整个查询结果
- 对查询结果分组后,聚集函数将分别作用于每个组
- 按指定的一列或多列值分组,值相等的为一组
- having选项用于筛选符合条件的分组

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_1888f8360b5f916c873ce26b12bea275.jpg
select cno,count(sno)
from sc
group by cno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_95ca3cb5ccaf0a570fbef665405dee0d.jpg
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_058f8b06d2b773f5725688de85ccc00d.jpg
select sno
from sc
group by sno
having count(*) > 3;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_aad4b45d27667fb5b55a94e30155a4ed.jpg
select sno
from sc
group by sno
having count(*) >= 2;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_49f6a67f390797696b15915943bc9559.jpg

Having和Where的区别

  • 作用对象不同
    where子句作用于基表或视图,从中选择满足条件的元组
    Having子句作用于组,从中选择满足条件的组
  • 分组后不能再选择满足条件的组
    where条件中不允许出现聚集函数
    having条件多数为带有聚集函数的条件

发表评论

textsms
account_circle
email

大白糖奶兔的Blog

数据库第三章-单表查询
数据案例 insert into student values('201215121','李勇','男',20,'CS'); insert into student values('201215122','刘晨','女',18,'CS'); insert into student values('201215123','王…
扫描二维码继续阅读
2020-08-08
Title - Artist
0:00