elementplus强制刷新组件_子查询


elementplus强制刷新组件_tkmybatis 子查询_02


1. 视图

1.1 概念

视图是虚拟的表，创建时只保存sql逻辑，不保存查询结果，在使用时再动态生成临时表。当经常用到同样的查询结果，或较复杂的查询语句时，就很适合使用视图。

1.2 视图的增删改查

#创建视图


CREATE VIEW <视图名>
AS
<查询语句>;


#使用视图


SELECT <视图中的列>
FROM <视图名>；


#修改视图


CREATE OR REPLACE VIEW <视图名>
AS
<查询语句>;


#修改视图


ALTER VIEW <视图名>
AS
<查询语句>;


#删除视图


DROP VIEW <视图名1>,<视图名2>,...；


#查看视图


-- 语法1：
DESC <视图名>;
-- 语法2：
SHOW CREATE VIEW <视图名>;


elementplus强制刷新组件_子查询_03


2. 子查询

2.1 定义

子查询，即嵌套在其他查询中的查询，可以理解为是一次性的视图。

2.2 执行顺序

子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果。

2.3 不同类型

【分类一】按子查询出现的位置：


select后面：
          仅仅支持标量子查询
from后面：
          表子查询
where或having后面：
          标量子查询、列子查询、行子查询（较少）
exists后面（相关子查询）：
          子查询


【分类二】按功能（结果集的行列数）不同：

  • 标量子查询，结果集只有一行一列
  • 列子查询，结果集只有一列多行，又称多行子查询
  • 行子查询，结果集有一行多列或多行多列（使用较少）
  • 表子查询，结果集一般为多行多列

2.3.1 列子查询

最常见的子查询就是列子查询，子查询的结果是一个字段的多行值，一般搭配着比较运算符（< > = <= >= <>）、多行操作符（in、any/some、all）使用。


elementplus强制刷新组件_element tree不刷新视图_04


案例：


elementplus强制刷新组件_tkmybatis 子查询_05


elementplus强制刷新组件_子查询_06


2.3.2 标量子查询

当子查询的结果只有一个值（一行一列）时，就是标量子查询，一般搭配着单行比较操作符（< > = <= >= <>）使用。

  • 出现在where子句里的标量子查询：


elementplus强制刷新组件_标量_07


  • 出现在SELECT后面的标量子查询：


elementplus强制刷新组件_标量_08


2.3.3 关联子查询

应用场景：在每个组里进行比较。

使用方法：在where子句里建立子查询，并在子查询的where后设置和主查询同组的关联条件。


elementplus强制刷新组件_标量_09


elementplus强制刷新组件_标量_10


2.4 子查询报错

当有子查询的语句出现报错后，一般很难检查，可以采用逐一排查法，先单独运行子查询并查看结果，以此先排除是否是子查询的问题，再逐一检查主查询语句问题。

3 . 常用函数

3.1 汇总函数

COUNT、SUM、AVG、MAX、MIN

详情见：https://zhuanlan.zhihu.com/p/264441366

3.2 算术函数

ROUND(X,D)

作用：对数据进行四舍五入


elementplus强制刷新组件_tkmybatis 子查询_11


D可以是负数，表示向小数点左边整数位四舍五入。

例：ROUND(2348.56,-2) →结果：2300

ABS(X)

取绝对值

CEIL(X)

向上取整，返回>=该参数的最小整数

FLOOR(X)

向下取整，返回<=该参数的最大整数

TRUNCATE(X,D)

截断，返回数值 X 保留到小数点后 D位的值（与 ROUND 最大的区别是不会进行四舍五入）

RAND()

获取随机数，返回0-1之间的小数

MOD(N,M)

取余数，返回N除以M以后的余数。

也可以使用取余符号 %

例：

SELECT MOD(7,2);

SELECT 7%2;

注意：取余数原理，mod(a,b) ：a - a/b*b ，规律是正负号和a一致。

mod(-13,-3): (-13) - (-13)/(-3)*(-3)= -1

mod( 13,-3): 13 - 13/(-3)*(-3)= 1

mod(-13,3): (-13) - (-13)/3 * -3= -1

3.3 字符串函数

LENGTH()

获取参数值的字节个数

注意：utf8的1个汉字=3个字节

CONCAT()

拼接字段、字符串、分隔符，实现连接

SELECT CONCAT(str1,str2,...)

注：当其中有一个字段有NULL值时该行结果为NULL。

UPPER()、LOWER()

例:SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名FROM employees

SUBSTR()

substr、substring，索引pos都是从1开始数。

1、截取从指定索引处后面所有字符。

2、截取从指定索引处指定字符长度的字符。


elementplus强制刷新组件_子查询_12


INSTR()

INSETR(str,substr) 返回子串第一次出现的索引位置，如果找不到返回0。

TRIM()

去掉str开始或结尾处的空格，也可以去掉指定字符串


elementplus强制刷新组件_标量_13


LPAD

用指定的字符实现左填充指定长度。


elementplus强制刷新组件_element tree不刷新视图_14


例：


elementplus强制刷新组件_子查询_15


RPAD

用指定的字符实现右填充指定长度。

REPLACE

替换


elementplus强制刷新组件_子查询_16


3.4 日期函数

NOW()

返回当前系统日期+时间

CURDATE()

CURDATE() 和 CURRENT_DATE() 返回当前系统日期，不包含时间。

CURTIME()

CURTIME() 和 CURRENT_TIME() 返回当期系统时间，不包含日期。

获取指定时间部分

YEAR(date), 获取 年份

MONTH(date) ，获取 月份

MONTHNAME(date)

以英文形式返回月

STR_TO_DATE

STR_TO_DATE(str,format) 将日期形式的字符串通过指定格式符转换成日期值。


elementplus强制刷新组件_element tree不刷新视图_17


例：


elementplus强制刷新组件_elementplus强制刷新组件_18


DATE_FORMAT

DATE_FORMAT(date,format) 将日期通过指定的格式符转换成字符串。


elementplus强制刷新组件_element tree不刷新视图_19


DATEDIFF()

datediff(日期1,日期2）计算日期1减去日期2的天数

TIMESTAMPDIFF()

TIMESTAMPDIFF(DAY|MONTH|YEAR|HOUR,日期2,日期1）计算日期1减去日期2的天数|月数|年数|小时数

DATE_ADD()

DATE_ADD(date,INTERVAL expr TYPE) 计算起始日期加上一个时间段后的日期。


elementplus强制刷新组件_tkmybatis 子查询_20


DATE_SUB()

DATE_SUB(date,INTERVAL expr TYPE) 计算起始日期减去一个时间段后的日期。

4. sqlzoo练习

表名：world

字段：name, continent, area, population, gdp


#1.列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。
SELECT name
FROM world
WHERE population>(
      SELECT population
      FROM world
      WHERE name='Russia');


#2.列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。
SELECT name
  FROM world
WHERE continent='Europe'
 AND  gdp/population>(
      SELECT gdp/population
      FROM world
      WHERE name='United Kingdom');


#3.在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序
SELECT name,continent
FROM world
WHERE continent IN(
      SELECT continent
      FROM world
      WHERE name='Argentina'
         OR name='Australia')
ORDER BY name;


#4.哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。
SELECT name,population
FROM world
WHERE population>(
      SELECT population
      FROM world
      WHERE name='Canada')
AND population<(
      SELECT population
      FROM world
      WHERE name='Poland');


#5.顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示
SELECT name,
       CONCAT(ROUND(population*100/(
         SELECT population
         FROM world
         WHERE name='Germany')),'%')AS new_p
FROM world
WHERE continent='Europe';


#6.哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)
SELECT name
FROM world
WHERE gdp>ALL(
             SELECT gdp
             FROM world
             WHERE continent='Europe'
              AND  gdp IS NOT NULL);


#7.在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)
SELECT continent,name,area
FROM world AS a
WHERE area=(
           SELECT MAX(area)
           FROM world b
           WHERE b.continent=a.continent );


#8.列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
SELECT continent,name
FROM world AS a
WHERE name=(
           SELECT MIN(name)
           FROM world AS b
           WHERE b.continent=a.continent);


SELECT continent,name
FROM world AS a
WHERE name<=ALL(
           SELECT name
           FROM world AS b
           WHERE b.continent=a.continent);


#9.找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。
SELECT name,continent,population
FROM world AS a
WHERE 25000000>=ALL(
                  SELECT population
                  FROM world AS b
                  WHERE b.continent=a.continent);


#10.有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。
#这题注意题干信息，是每个国的3倍，不是所有国之和的3倍！还有比较时要排除自己
SELECT name,continent
FROM world AS a
WHERE population/3>=ALL(SELECT population
                    FROM world AS b
                    WHERE b.continent=a.continent
                    AND b.name<>a