增删改查的查之高级查询

前言

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

今天主要是讲高级查询部分，如果碰到不懂的可以在微信群里跟我交流，如果没有加到群里的朋友，可以先加我的微信（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;

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