增删改查的查之高级查询

前言

如果忘记了简单查询,可以再次查看:增删改查的查之简单查询

今天主要是讲高级查询部分,如果碰到不懂的可以在微信群里跟我交流,如果没有加到群里的朋友,可以先加我的微信(evangline7),我再把你们拉进去。

1.连接查询/多表查询

假设有二张表t1和t2:

t1的字段(id,name,age)

t2的字段(id,class,score)

(1)查询出学生的姓名,年龄和分数

mysql> SELECT name,age,score

FROM t1 INNER JOIN t2 ON t1.id = t2.id;

INNER JOIN 可以省略写成JOIN

注意:这就是内连接,返回的行都是二个表相匹配的数据

(2)mysql> SELECT t1.id,name,age,score

FROM t1 LEFT JOIN t2 ON t1.id = t2.id;

注意:这是左连接,除了返回二个表相匹配的数据,还会返回左表多余的数据,与右表不匹配以null显示

(3)mysql> SELECT t1.id,name,age,score

FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;

注意:这是右连接,除了返回二个表相匹配的数据,还会返回右表多余的数据,与左表不匹配以null显示

注意:mysql中只有内连接、左连接和右连接,没有全连接

内连接返回二个表关联字段共同都有的数据

左连接除了返回内连接的数据,还返回左表多余的数据,左连接只要记住左表为大

右连接除了返回内连接的数据,还返回右表多余的数据,右连接只要记住右表为大

(4)假设是三表连接,内连接如下写法,其它连接相似

SELECT * FROM t1 JOIN t2 ON t1.id= t2.id JOIN t3 ON t2.id = t3.id;

上面的写法等于下面这种写法:

==SELECT * FROM t1,t2,t3

WHERE t1.id = t2.id AND t2.id =t3.id;

王豆豆更喜欢下面这种写法,更简单,更快。

接下的多表查询都将使用这种方法。

现在有三张表,表结构如下:

班级(class)表(学号(id),姓名(name),班级(class),年龄(age))

课程(course)表(课程名(cname),课程号(cno))

分数(score)表(学号(xuehao),课程号(scno),分数(score))

(5)查询出学员的姓名,班级,课程名以及相应课程的分数,并以分数进行降序排列

SELECT c.name,c.class,co.cname,s.score

FROM class c,course co,score s

WHERE c.id = s.xuehao AND co.cno = s.scno

ORDER BY score desc;

(6)查询出学员姓名以张开头的学员姓名,课程,分数

SELECT c.name,c.class,co.cname,s.score

FROM class c,course co,score s

WHERE c.id = s.xuehao AND co.cno = s.scno AND c.name like '张%';

(7)查询学员分数大于70的学员姓名,班级,课程

SELECT c.name,class,cname

FROM class c,course co, score s

WHERE c.id = s.xuehao AND co.cno=s.scno AND s.score > '70';

(8)查询学员班级为T1001的学员课程号

SELECT s.scno

FROM class c,score s

WHERE c.id = s.xuehao AND c.class = 'T1001';

(9)查询学员姓名包含王的学员分数,并以降序的方式排列

SELECT s.score

FROM class c,score s

WHERE c.id = s.xuehao AND c.name LIKE '%王%'

ORDER BY score DESC;

(10)查询年龄不在25到27之间的学员姓名,课程编号,分数

解题步骤:

1.查询出年龄不在25到27之间学员信息

SELECT * FROM class WHERE age NOT BETWEEN 25 AND 27;

2.查询出学员的姓名,课程编号,分数

SELECT name,scno,score FROM class c,score s

WHERE c.xuehao = s.xuehao;

3.合并

SELECT name,scno,score FROM class c,score s

WHERE c.xuehao = s.xuehao AND (age NOT BETWEEN 25 AND 27);

2.子查询

一个查询语句包含其他的查询语句,则叫子查询

子查询有几种结果:

子查询返回一列语句:也就是一列一个数据,这是使用情况最多的

返回一行语句:查询结果返回一行数据,有可能是一行完整的数据

返回多行语句:查询结果返回一组数据

格式:

SELECT [ALL|DISTINCT] {*|table1.*|{table1.field1} [as alias1]|{table2.field2} [as alias2],....}

FROM table1 [别名],table2,{

SELECT [ALL|DISTINCT] {*|table1.*|{table1.field1} [as alias1]|{table2.field2} [as alias2],....}

FROM table1 [别名],table2,....

[WHERE 某列名称 = 某值]

[ORDER BY ...]

}别名,....

[WHERE (条件s){

SELECT [ALL|DISTINCT] {*|table1.*|{table1.field1} [as alias1]|{table2.field2} [as alias2],....}

FROM table1 [别名],table2,....

[WHERE 某列名称 = 某值]

[ORDER BY ...]

}]

[ORDER BY ...]

(1)查询出分数大于tester测试基础的分数的学员姓名和分数

解题步骤:

1.查询tester测试基础的分数

SELECT score

FROM score s,course c

WHERE s.scno = c.cno AND c.cname ='测试基础' AND s.name = 'tester';

2.查询出分数大于1题查询结果的学员姓名和分数

SELECT name,score

FROM score

WHERE score > (SELECT score

FROM score s,course c

WHERE s.scno = c.cno AND c.cname ='测试基础' AND s.name = 'tester');

(2)查询出分数小于测试基础最低分数的学员姓名和课程,分数

解题步骤:

1.查询出测试基础的分数(查询结果为一组数据)

SELECT score

FROM score s,course c

WHERE s.scno = c.cno AND c.cname ='测试基础';

2.查询出分数小于最低分数的学员姓名和课程,分数

SELECT s.name,c.cname,s.score

FROM score s,course c

WHERE s.scno = c.cno AND score <ALL(SELECT score

FROM score s,course c

WHERE s.scno = c.cno AND c.cname ='测试基础');

(3)查询出分数大于测试基础最低分数的学员姓名和课程,分数

SELECT s.name,c.cname,s.score

FROM score s,course c

WHERE s.scno = c.cno AND score >ANY(SELECT score

FROM score s,course c

WHERE s.scno = c.cno AND c.cname ='测试基础');

注意:

1.ANY

>ANY() : 比里面最小值的大

<ANY() : 比里面最大值的小

=ANY() == IN 包含

2.ALL

>ALL() : 大于里面最大值

<ALL() : 小于里面最小值

(5).以课程号 '1-201'为例,查询成绩高于'3'号同学的所有同学的记录。

SELECT c.*

FROM class c,score s

WHERE s.cno = '1-201'

AND s.score >(SELECT score FROM scroe WHERE xuehao = '3');

(6)查询与学号为'12'的同学同岁的所有学生的学号、姓名和年龄。

SELECT xuehao,name,age

FROM class

WHERE age = (SELECT age FROM class WHERE xuehao = '12');

(7)查询出分数小于课程号为1106的学员分数最大值的学员姓名和分数

SELECT c.cname,s.score

FROM class c,score s

WHERE c.cno = s.sno AND

s.score <ANY(SELECT score FROM score WHERE sonum = "1106");

3.合集查询

函数格式:

FUNCTION(列名称|字符串,[参数1,参数2])

FUNCTION---函数名称

列名称:表示表的字段列名称

字符串:表示其它字符串

参数:根据函数不同,有不同的参数

1.单行函数也就是标量函数

(1)UCASE()---将小写字母转换成大写字母

格式:UCASE(列名称|str)

1.转换STR

SELECT ucase('test');

==SELECT upper('test');

2.转换列名称

SELECT ucase(name) FROM class;

注意:函数里面的字符串都要用引号括起来,列名称不需要

(2)将大写字母转换成小写字母

格式:LCASE(列名称|STR)

1.转换字符串

SELECT lcase('TESTEWERQWE');

查询结果为:testewerqwe

2.转换列名称

SELECT lcase(ename) FROM emp;

==SELECT lower(ename) FROM emp;

(3)MID()---截取字符串

格式:MID(列名称|str,x,y)

str---字符串

x---表示从x位开始截取

y---表示截取y个字符

1.截取字符串

SELECT mid('abcdef',3,2);

查询结果为:cd

2.可以省略参数y

SELECT mid('abcdef',3);

查询结果为:cdef

从字符串第3位截取,一直到最后一个字符

3.截取列名称

SELECT mid(ename,2,3) FROM emp;

4.从class表中将name字段从第二位开始截取所有字符串,然后将截取的字符串转换成小写(lower)

SELECT lower(mid(ename,2)) FROM class;

注意:这就是嵌套函数

(4)LENGTH() ----返回某个文本域(字符串或列名称)的长度

格式:LENGTH(列名称|字符串)

1.查询字符串的长度

SELECT length('asdfasdfasd');

查询结果为:11

2.查询class表中name的长度

SELECT length(ename) FROM class;

3.查询出class表name字段最后三位字符串

SELECT mid(name,length(name)-2) FROM class;

==SELECT mid(name,-3) FROM class;

(5)ROUND() ----对某个数值进行四舍五入

格式:ROUNT(列|数值,位数)

如果有位数,则表示数值保留多少位小数

如果没有,取整

1.对数值

SELECT round(3234234.23423,1);

查询结果为:3234234.2

SELECT round(3234234.25423,1);

查询结果为:3234234.3

2.要数值 123.568789保留3位小数

SELECT ROUND(123.568789,3);

3.省略参数(位数)

SELECT ROUND(13.56);

注意:省略参数就是取整

4.列名称

SELECT round(score,1) FROM score;

(6)REPLACE() --替换字符串的字符

格式:REPLACE(str,x,y)

将str中x替换成y

1.替换字符串

SELECT REPLACE('adasfcdasdsdafasd','d','12');

查询结果为:a12asfc12as12s12afas12

2.合集函数

也称为组函数

COUNT():求表的列数

MAX():求最大值

MIN():求最小值

AVG():求平均

SUM():求和

(1)求出class表中总的行数

SELECT count(*) FROM class;

(2)求出class表中学生的个数

SELECT count(name) FROM class;

(3)求班级的个数

SELECT count(distinct class) FROM class;

注意:COUNT(*):求表中的行数

COUNT(列名称):求表中该列的行数,不包含null值

COUNT(DISTINCT 列名称):求表中该列去重后的个数

(4)求出score表中学生的最高分数

SELECT max(score) FROM score;

(5)求出score表中分数最高的学号和课程号

SELECT xuehao,scno

FROM score

WHERE score = (SELECT max(score) FROM score);

(6)求出score表中最低分数是多少

SELECT min(score) FROM score;

(7)求出分数表中最高分数和最低分数分别是多少

SELECT max(score),min(score) FROM score;

(8)在7的基础上,分别命别名为最高分数和最低分数,且取整

SELECT round(max(score)) 最高分数,round(min(score)) 最低分数 FROM score;

(9)求出score表中学生的平均分数,并保留二位小数

SELECT round(avg(score),2) 平均分数 FROM score;

(10)求score表中课程号为3-110的平均分数,最高分数,最低分数

SELECT avg(score) 平均分数,max(score) 最高分数,min(score) 最低分数

FROM score

WHERE scno = “3-110";

(11)求出score表中所有学生的分数总和

SELECT sum(score) FROM score;

(12)求出score表中课程号为3-110的分数且大于课程号为1-141的最高分数的学生学号

SELECT xuehao FROM score

WHERE scno = "3-110" AND score > (SELECT max(score) FROM score WHERE scno = “1-141");

4.分组统计查询---GROUP BY

SELECT语句加上GROUP BY 后的格式:

SELECT {DISTINCT} *|具体的列 别名

FROM 表名称

{WHERE 条件(s)}

{GROUP BY 分组条件}

{ORDER BY 排序的字段 1,排序的字段 2,ASC|DESC}

(1)求出每个班级的人员数量(FROM后是表名,SELECT后面是字段名,这里字段名和表名命名是一样的)

SELECT class,count(*)

FROM class

group by class;

(2)求出每个课程的平均分数

SELECT scno,avg(score)

FROM score

group by scno;

注意:

错误写法:SELECT scno,avg(score) FROM score;

第一种正确写法:SELECT avg(score) FROM score;

第二种正确写法:SELECT scno,avg(score) FROM score group by scno;

为什么会出现上面的情况,是因为分组函数的存在:

1.如果分组函数存在,要显示分组条件的字段,则需要加上分组条件

2.如果分组函数单独使用,可以不用加上分组条件

在没有分组条件存在的情况下,分组函数必须单独存在

如果有分组条件GROUP BY,显示结果集可以包含分组条件的字段,其它的字段不能出现

错误的写法:

SELECT scno,xuehao,avg(score) FROM score group by scno;

5.分组条件---HAVING

HAVING语句只出现在GROUP BY后面的语句。

SELECT完整的格式:

SELECT {DISTINCT} *|具体的列 别名

FROM 表名称,{

SELECT {DISTINCT} *|具体的列 别名

FROM 表名称

{WHERE 条件(s)}

{GROUP BY 分组条件 HAVING 分组函数}

{ORDER BY 排序的字段1 ASC|DESC,排序的字段2 ASC|DESC}} 别名……

{WHERE 条件(s){

SELECT {DISTINCT} *|具体的列 别名

FROM 表名称

{WHERE 条件(s)}

{GROUP BY 分组条件 HAVING 分组函数}

{ORDER BY 排序的字段1 ASC|DESC,排序的字段2 ASC|DESC}}

{GROUP BY 分组条件 HAVING 分组函数}

{ORDER BY 排序的字段1 ASC|DESC,排序的字段2 ASC|DESC}

(1)求出平均分数大于90的课程号

错误写法:SELECT scno FROM score WHERE avg(score) > 2000;

错误提示:1111 - Invalid use of group function

分组函数不允许出现在WHERE条件中,必须与group by 分组条件搭配使用

正确写法:SELECT scno FROM score

GROUP BY scno HAVING avg(score) > 90;

(2)查询出班级人数大于5人的班级名称

SELECT class FROM class

GROUP BY class HAVING count(*) >5;

因为查询语句的字符串过串,而手机屏幕又小,不方便查看的,可以把链接发到电脑上查看。

原文发布于微信公众号 - 资深Tester(zishentester)

原文发表时间:2017-11-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏码云1024

sql 复习练习

3596
来自专栏北京马哥教育

优化临时表使用,SQL语句性能提升100倍

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SE...

2688
来自专栏Albert陈凯

Hadoop数据分析平台实战——130Hive Shell命令介绍 02(熟悉Hive略过)离线数据分析平台实战——130Hive Shell命令介绍 02(熟悉Hive略过)

离线数据分析平台实战——130Hive Shell命令介绍 02(熟悉Hive略过) 导入数据 Hive的导入数据基本上可以分为三类, 第一种是从linux系...

2645
来自专栏python成长之路

My SQL常用操作汇总

1824
来自专栏沃趣科技

其他混杂存储过程 | 全方位认识 sys 系统库

在上一篇《用于查看配置的存储过程 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中用于查看performance_schema配置信息的快捷存储过...

863
来自专栏web编程技术分享

【php增删改查实例】第十节 - 部门管理模块(新增功能)

1406
来自专栏数据和云

innodb实例损坏情况下恢复数据及相关工具的开发

作者介绍:谢浩,现任职于云和恩墨(北京)信息技术有限公司,具有多年oracle数据库企业级运维经验,擅长结合业务、硬件系统制定各种项目方案,具有丰富mysql相...

2978
来自专栏技术碎碎念

T-SQL 查询、修改数据表

T-SQL修改表数据 INSERT语句 语法: INSERT [TOP(expression) [PERCENT]] [INTO] ...

4194
来自专栏程序小工

【实战】Tp5+小程序(三)--微信登录与令牌

ThinkPHP5 从入门到深入学习,结合实战项目深入理解 ThinkPHP5 的特性和使用方法。深入学习 api 开发,学习微信登录和令牌的相关知识,并理解微...

2762
来自专栏游戏杂谈

Node.js调用mysql的存储过程

例子仅在windows下测试通过,没有放在linux下测试。如有问题,可以电邮给我~

651

扫码关注云+社区