mysql 保存sql查询结果 sql怎么保存查询语句

转载

mob6454cc67bcfb 2024-05-06 20:58:48

文章标签 mysql 保存sql查询结果 navicat保存查询语句子查询 SQL 标量 文章分类 MySQL 数据库

摘要

视图#1

子查询#2

标量子查询#3

关联子查询#4

如何使用SQL解决业务问题#5

各种函数#6

视图#1

什么是视图？用一句话概述的话，就是“从SQL的角度来看视图就是一张表”。但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是SELECT语句，我们从视图中读取数据时，视图会在内部执行该SELECT语句并创建出一张临时表。

mysql 保存sql查询结果 sql怎么保存查询语句_子查询

视图有什么用？视图的优点大体有两点：

第一：由于视图无需保存数据，因此可以节省存储设备的容量。。

第二：可以将频繁使用的SELECT语句保存成视图，这样就不用每次都重新书写了。创建好视图之后，只需在SELECT语句中进行调用，就可以方便地得到想要的结果了。特别是在进行汇总以及复杂的查询条件导致SELECT语句非常庞大时，使用视图可以大大提高效率。而且，视图中的数据会随着原表的变化自动更新。视图归根到底就是SELECT语句，所谓“参照视图”也就是“执行SELECT语句”的意思，因此可以保证数据的最新状态。这也是将数据保存在表中所不具备的优势。

本文内的大部分代码将以下面表的数据进行演练，分别是student、score。

mysql 保存sql查询结果 sql怎么保存查询语句_标量_02

student表

mysql 保存sql查询结果 sql怎么保存查询语句_子查询_03

score表

如何创建视图？

通过数据库管理工具Navicat输入查询语句：

create view 视图名称（<视图列名1>,<视图列名2>,...）

<select 查询语句>;

练习：创建按性别汇总视图。

create view 按性别汇总(性别,人数)
as
select 性别,count(*)
from student
group by 性别;

创建完成后，在Navicat对应的数据库下刷新视图，就能显“按性别汇总的视图。

mysql 保存sql查询结果 sql怎么保存查询语句_子查询_04

有哪些注意事项？

注意事项1：避免在视图下再创建视图，对多数DBMS来说，多重视图会降低SQL的性能。

注意事项2：不能往视图里插入数据，不然会报错。

子查询#2

什么是子查询？子查询以视图为基础。子查询的特点概括起来就是“一张一次性视图”，子查询将用来定义视图的SELECT语句直接用于FROM子句当中。

在FROM子句中直接写定义视图的SQL查询语句，运行顺序是先运行括号里面的再运行括号外面的。

练习：用子查询实现查询student表中各个性别的人数。

select 性别,人数
from(
select 性别,count(*) as 人数
from student
group by 性别
) as 按性别汇总;

mysql 保存sql查询结果 sql怎么保存查询语句_navicat保存查询语句_05

如何使用子查询？

常见的子查询搭配有：...in（子查询）、...any（子查询）、...all（子查询）

练习：找出每个课程里成绩最低的学号。

当用select语句直接查找每个课程里成绩最低的学号，这里select语句识别的学号会随机出现。

mysql 保存sql查询结果 sql怎么保存查询语句_mysql 保存sql查询结果_06

正确的方法是分解成两个步骤（分析思路）：

-- 第1步：查找出每门课程的最低成绩有哪些值

select 课程号,min(成绩)

from score

group by 课程号

-- 第2步：在成绩表里查找这些值

select 学号,成绩

from score

where 成绩 in(80,60,80);

-- 合并后的SQL，括号里面的语句为子查询

select 学号,成绩
from score
where 成绩 in(
select min(成绩)
from score
group by 课程号
);

在使用any时需要与比较运算符一起使用

...any(子查询)与some(子查询）相同，一般表达式为：

select <列名1>

from <表名1>

where <列名1> > any（子查询）;

练习：哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？

（分析思路）

第1步：课程0002的全部成绩，比如是（10，30）——any括号里面的语句

select 成绩

from score

where 课程号 = '0002';

第2步：某个学生的成绩大于任意一个第1步里的成绩，就符合条件——任意一个高any（子查询）

select 学号,成绩

from score

where 成绩 > any(子查询);

完整代码：

select 学号,成绩
from score
where 成绩 > any(
select 成绩
from score
where 课程号='0002'
);

mysql 保存sql查询结果 sql怎么保存查询语句_子查询_07

...all（子查询）

练习：哪些学生的成绩比课程0002的全部成绩里的都高呢？

select 学号,成绩
from score
where 成绩 > all(
select 成绩
from score
where 课程号 ='0002')：

mysql 保存sql查询结果 sql怎么保存查询语句_SQL_08

注意事项：

1、注意书写规则，a> 3*all(b) 为错误书写，a/3 > all(b) 为正确书写；

2、 select...from（子查询（子查询））层次嵌套，由于子查询的层数原则上没有限制，因此可以像“子查询的FROM子句中还可以继续使用子查询，该子查询的FROM子句中还可以再使用子查询……”这样无限嵌套下去。但是，随着子查询嵌套层数的增加，SQL语句会变得越来越难读懂，性能也会越来越差。因此，应该尽量避免使用多层嵌套的子查询。

3、子查询后连接as，可以添加子查询名称，用法为“as 子查询名称”，尽管as 子查询名称可以省略，但尽量养成良好的习惯，方便维护。

标量子查询#3

标量子查询有一个特殊的限制，就是必须而且只能返回1行1列的结果，也就是返回表中某一行的某一列的值。由于返回的是单一的值，因此标量子查询的返回值可以用在=或者<>这样需要单一值的比较运算符之中。这也正是标量子查询的优势所在。

标量子查询的书写位置并不仅仅局限于WHERE子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是SELECT子句、GROUP BY子句、HAVING子句，还是ORDER BY子句，几乎所有的地方都可以使用。

标量子查询通常与in、any、all、between共同实现查询条件。

练习：找出大于平均成绩学生的学号和成绩。

（以下代码会报错，原因是在where 子句中不能使用汇总函数，只能是单一的值）

select 学号,成绩

from score

where 成绩 > avg(成绩)；

由于标量子查询中返回单一的值，所以可以使用运算符（分析思路）：

-- 第1步：通过标量子查询，运行结果avg（成绩）=81.125

select avg(成绩)

from score

);

-- 第2步：查找大于81.125的成绩和学号

select 学号,成绩

from score

where 成绩 >81.125

-- 合并后的SQL，大于平均成绩学生的学号和成绩

select 学号,成绩

from score

where 成绩 > (

select avg(成绩)

from score

);

mysql 保存sql查询结果 sql怎么保存查询语句_标量_09

练习：找出差生（成绩<=60）优等生（成绩 >=80）的学号和成绩

select 学号,成绩
from score
where 成绩 between
(select avg(成绩)
from score
where 成绩 <=60)and
(selectr avg(成绩)
from score
where 成绩 >=80);

mysql 保存sql查询结果 sql怎么保存查询语句_navicat保存查询语句_10

注意事项：

在一行select子句中不能使用多行数据的子句，也就是不能返回多行数据，不然会报错。原因是该子查询绝对不能返回多行结果，如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在=或者<>等需要单一输入值的运算符当中，也不能用在SELECT等子句当中。

关联子查询#4

关联子查询会在细分的组内进行比较时使用。关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。关联子查询的结合条件如果未出现在子查询之中就会发生错误。

在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“< 表名 >.< 列名 >”的形式记述。在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。

练习：查找出每个课程中大于对应课程平均成绩的学生。

select 学号,课程号,成绩
from score as s1
where 成绩 > (select avg(成绩)
-- 关联名称s2仅在子查询里有效
from score as s2
-- 关联条件
where s1.课程号 = s2.课程号 
group by 课程号
);

mysql 保存sql查询结果 sql怎么保存查询语句_标量_11

使用关联子查询时，该子查询的结果必须是单一的。起到关键作用的就是在子查询中添加的WHERE子句的条件。该条件的意思就是，在每个课程中对应的成绩和平均成绩进行比较。

为什么外部的表别名s1可以在子查询里识别出来呢？

是由于关联名称的作用域在起作用，关联名称就是像s1、s2这样作为表别名的名称，作用域（scope）就是生存范围（有效范围）。也就是说，关联名称存在一个有效范围的限制。

子查询内部设定的关联名称，只能在该子查询内部使用。换句话说，就是“内部可以看到外部，而外部看不到内部”。

我们想得到一个查询结果，但机器并不能简单识别，因此我们需要创作一个连接他们的桥梁，这个桥梁就是关联子查询，这有点类似数学中虚数的概念，因此如果要掌握这种思维，便需要加强练习

《SQLZOO——SELECT within SELECT Tutorial》练习题7：

在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area 
FROM world x
WHERE area >= ALL
 (SELECT area FROM world y
WHERE y.continent=x.continent
AND area>0)

mysql 保存sql查询结果 sql怎么保存查询语句_SQL_12

《SQLZOO——SELECT within SELECT Tutorial》练习题8：

列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

select continent,name
from world x
where x.name=
(select y.name
from world y
-- 通过条件限定，与group by类似，通过where y.continent=x.continent，达到把不同continent切分的效果
where y.continent=x.continent
order by name
limit 1);

mysql 保存sql查询结果 sql怎么保存查询语句_navicat保存查询语句_13

《SQLZOO——SELECT within SELECT Tutorial》练习题9：

找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

SELECT name, continent, population
FROM world x
WHERE 25000000>=ALL
(SELECT population
FROM world y
WHERE y.continent=x.continent
AND population>0);

mysql 保存sql查询结果 sql怎么保存查询语句_标量_14

如何使用SQL解决业务问题#5

1、翻译成大白话；

2、写出分析思路；

3、写出对应的sql子句。

练习：哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？

第1步：课程0002的全部成绩，比如是（10，30）

select 查询结果【成绩】

from 从哪张表中查找数据【成绩表】

where 查询条件【课程号是0002】

group by 分组【没有】

having 对分组结果指定条件【没有】

order by 对查询结果排序【没有】

limit 从查询结果中取出指定行【没有】；

注意运行顺序：

select 成绩

from score

where 课程号 = '0002';

查找出课程0002的全部成绩

mysql 保存sql查询结果 sql怎么保存查询语句_mysql 保存sql查询结果_15

第2步：某个学生的成绩大于任意一个第1步里的成绩，就符合条件

比任意一个高，需要用到any（子查询）

select 学号,成绩

from score

where 成绩 > all (子查询)；

两个步骤合并到一起的SQL是：

select 学号,成绩
from score
where 成绩 > ALL
(select 成绩
from score
where 课程号 ='0002');

mysql 保存sql查询结果 sql怎么保存查询语句_标量_16

《SQLZOO——SELECT within SELECT Tutorial》练习题6：

哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

select name
from world
where gdp > all
-- 由于下面的子查询并不只返回单一值，因此需要在比较运算符后加上all
(select gdp
from world
where continent ='Europe'
and gdp >0);

mysql 保存sql查询结果 sql怎么保存查询语句_navicat保存查询语句_17

当某些数据为NULL时，由于空值无法比较，因此需要加上列名>0 这个条件。

《SQLZOO——SELECT within SELECT Tutorial》练习题10：

有些國家的人口是同洲份的所有其他國的3倍或以上。列出國家名字name 和洲份 continent。

select name,continent
from world x
-- 根据sql书写规则，算术运算符应该在比较运算符前面
where x.population/3 >= all
(select population
from world y
-- 建立子查询，虚构表y，其中表y的continent等于表x，等同于把continent切分，同一个continent的数据进行比较，表y的name不能等于表x，原因是表y的name需要与表x的name逐行比较，要设置name不一样才能进行相互比较
where x.continent=y.continent
and y.name<>x.name
and y.population>0)