首页 > 数据库 >Mysql--查询

Mysql--查询

时间：2024-09-14 08:52:07浏览次数：10  
标签：语句 name -- 查询 select Mysql WHERE SELECT

目录

基本语法

查询常量

SELECT 常量值;

SELECT 100;

查看表达式

SELECT 表达式;

SELECT 100%80;

查询函数

SELECT 函数;

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;

查询指定字段

查询数据时，有时不需要查询所有字段的信息。
查询指定字段可以在SELECT语句的字段列表中指定要查询的字段。

在 MySQL 中，查询指定字段通常是通过 SELECT 语句实现的。SELECT 语句允许你从一个或多个表中检索数据，并指定你希望从表中检索哪些列（字段）。以下是一些基本的 SELECT 语句示例，展示了如何查询指定字段。

比如一个列表中姓名栏中查询某个姓氏我们可以用两种方法

1.LIKE操作符

对于姓“张”的用户，假设名字格式遵循“姓 名”或“姓 名 姓”（对于复姓的情况）这样的常规格式，并且名字之间可能有空格或其他分隔符（但这里我们假设主要是空格），你可以使用以下SQL查询：

SELECT *FROM t_user WHERE `name` LIKE'张%';
2.使用正则表达式

来匹配更复杂的名字格式，你可以考虑使用MySQL的REGEXP或RLIKE操作符（它们是等价的），但这通常比简单的LIKE查询更慢，并且需要更复杂的正则表达式来准确匹配。例如：

SELECT *FROM t_user WHERE `name`REGEXP'^张';
单个字段查询

如果你只想从表中检索一个字段，你可以在 SELECT 语句后直接指定该字段的名称。

SELECT column_name FROM table_name;

示例：

假设有一个名为 employees 的表，它包含 id, name, 和 salary 字段，你想查询所有员工的名字：

SELECT name FROM employees;
多个字段查询

如果你想从表中检索多个字段，你可以在 SELECT 语句后使用逗号分隔字段名称。

SELECT column1, column2, column3 FROM table_name;

示例：

继续使用上面的 employees 表，如果你想查询员工的 id 和 name：

SELECT id, name FROM employees;
使用别名（AS）

在查询中，你还可以为列名指定别名，这有助于在结果集中更清晰地标识列。

SELECT column_name AS alias_name FROM table_name;

示例：

查询员工的 name，并将结果集中的列名显示为 EmployeeName：

SELECT name AS EmployeeName FROM employees;
使用 WHERE 子句

虽然这不是直接查询指定字段的一部分，但通常你会想根据某些条件来限制检索的数据。WHERE 子句允许你指定这些条件。

SELECT column1, column2 FROM table_name WHERE condition;

示例：

查询薪资超过 5000 的员工的 id 和 name：

SELECT id, name FROM employees WHERE salary > 5000;
汇总函数

你还可以使用 SQL 的汇总函数（如 COUNT(), SUM(), AVG(), MIN(), MAX()）来查询指定字段的统计数据。

示例：

查询 employees 表中员工的总数：

SELECT COUNT(*) FROM employees;

例如查询诺贝尔奖中所有奖项获奖次数

SELECT 奖项名,COUNT(*) AS total_anywinnner 
FROM 表名
GROUP BY奖项名 ;

显示每个奖项第一次获得该奖的年份

SELECT 奖项名,MIN(yr) AS first_award_year 
FROM 表名
GROUP BY 奖项名;

这里，* 表示计算所有行的数量，但你也可以指定一个字段来计算非空值的数量。

通过组合使用这些基本的 SQL 查询技术，你可以构建出强大的查询语句来满足你的数据检索需求。

SELECT 字段名1 , 字段名2 , ... FROM 数据表名 ;

查询所有列

查询指定 数据库中,指定表的所有字段(指定表的,所有列)

SELECT
    COLUMN_NAME
FROM
    information_schema.COLUMNS
WHERE
    table_schema = '数据库名'
    AND table_name = '表名'

列别名

有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。
用法：

　　SELECT
　　 [column_1 | expression] [AS] descriptive_name #要给列添加别名，可以使用AS关键词后跟别名。
　　FROM table_name;

如果别名包含空格，则必须引用：

　SELECT
　　 [column_1 | expression] [AS] 'descriptive name' #因为AS关键字是可选的，可以在语句中省略它。
　　FROM table_name;

查询选择员工的名字和姓氏，并将其组合起来生成全名。 CONCAT_WS函数用于连接名字和姓氏

SELECT
CONCAT_WS(', ', lastName, firstname) [AS] 'Full name'
FROM
employees;
子句对列别名的使用

在MySQL中，可以使用ORDER BY，GROUP BY和HAVING子句中的列别名来引用该列。

以下查询使用ORDER BY子句中的列别名按字母顺序排列员工的全名：

SELECT
CONCAT_WS(' ', lastName, firstname) [as] 'Full name'
FROM
employees
ORDER BY
'Full name';

以下语句查询总金额大于60000的订单。它在GROUP BY和HAVING子句中使用列别名

SELECT
orderNumber [as] 'Order no.',
SUM(priceEach * quantityOrdered) [as] total
FROM
orderdetails
GROUP BY
'Order no.'
HAVING
total > 60000;

表别名

可以使用别名为表添加不同的名称。使用AS关键字在表名称分配别名，如下查询语句语法：

table_name [AS] table_alias

两个表都具有相同的列名称：customerNumber。如果不使用表别名来指定是哪个表中的customerNumber列：

SELECT
customerName,
COUNT(o.orderNumber) [as] total	#列别名
FROM
customers [as] c	INNER JOIN orders [as] o #表别名

ON c.customerNumber = o.customerNumber
GROUP BY
customerName
HAVING total >=5
ORDER BY total DESC;

条件查询

条件查询运算符

显示每个奖项在2000年获奖的人数

SELECT 奖项,COUNT(*) AS award_count_2000
FROM 表名
WHERE 该列名字 = 指定该列下的
GROUP BY 奖项;

条件查询运算符用于在WHERE子句中指定条件，以便从表中检索符合这些条件的行。以下是一些常用的条件查询运算符：

  • 等于 (=): 用来判断两个值是否相等。

    SELECT * FROM table_name WHERE column_name = value;

    示例：

    联表左外连接查询

    找出所有song(歌名)和title(专辑名)相同的曲目

    select album.title,track.song
from album left join track
on album.albumCode = track.album
where album.title = track.song;

  • 不等于 (<> 或 !=): 用来判断两个值是否不相等。

    SELECT * FROM table_name WHERE column_name <> value;  
-- 或者  
SELECT * FROM table_name WHERE column_name != value;

  • 大于 (>): 用来判断左边的值是否大于右边的值。

    SELECT * FROM table_name WHERE column_name > value;

  • 小于 (<): 用来判断左边的值是否小于右边的值。

    SELECT * FROM table_name WHERE column_name < value;

  • 大于等于 (>=): 用来判断左边的值是否大于或等于右边的值。

    SELECT * FROM table_name WHERE column_name >= value;

  • 小于等于 (<=): 用来判断左边的值是否小于或等于右边的值。

    SELECT * FROM table_name WHERE column_name <= value;

  • IN: 用来判断列中的值是否在给定的集合中。

    sql复制代码

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);

  • BETWEEN: 用来判断列中的值是否在给定的两个值之间（包括这两个值）。

    SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

  • LIKE: 用来在WHERE子句中搜索列中的指定模式。

    SELECT * FROM table_name WHERE column_name LIKE pattern;

  • IS NULL/IS NOT NULL: 用来判断列中的值是否为NULL。

    SELECT * FROM table_name WHERE column_name IS NULL;  
-- 或者  
SELECT * FROM table_name WHERE column_name IS NOT NULL;

逻辑查询运算符

逻辑查询运算符用于组合多个条件，以便在单个查询中根据这些条件的组合来检索数据。以下是一些常用的逻辑查询运算符：

  • AND: 用来组合多个条件，只有当所有条件都为真时，结果才为真。

    SELECT * FROM table_name WHERE condition1 AND condition2;

  • OR: 用来组合多个条件，只要其中一个条件为真，结果就为真。

    SELECT * FROM table_name WHERE condition1 OR condition2;

  • NOT: 用来对条件的结果取反。

    SELECT * FROM table_name WHERE NOT condition;

通过组合使用这些条件查询运算符和逻辑查询运算符，你可以构建出强大且灵活的SQL查询语句，以满足各种复杂的数据检索需求

排序与分页

排序

使用ORDER BY子句排序

ASC（ascend）：升序
DESC（descend）：降序
ORDER BY 子句在SELECT语句的结尾

假设有一个名为students的表，包含id（学生ID）、name（学生姓名）和score（分数）字段，你想要按照分数从高到低排序所有学生。

SELECT * FROM students ORDER BY score DESC;

如果你还希望在分数相同的情况下，按照学生ID升序排序，可以这样做：

SELECT * FROM students ORDER BY score DESC, id ASC;

分页

分页是限制查询结果集大小的一种方式，常用于在Web应用中实现数据的分页显示。MySQL提供了LIMIT子句来实现分页，而从MySQL 8.0开始，也支持了OFFSET子句，用于指定从哪条记录开始返回数据。

LIMIT 子句：

LIMIT子句用于限制查询结果的数量。

示例：

如果你只想获取students表中分数最高的前5名学生，可以这样做：

SELECT * FROM students ORDER BY score DESC LIMIT 5;

LIMIT 和 OFFSET 子句结合使用：

当需要实现更复杂的分页逻辑时，比如显示第2页的数据（假设每页显示5条），可以结合使用LIMIT和OFFSET。

SELECT * FROM students ORDER BY score DESC LIMIT 5 OFFSET 5;

这里的OFFSET 5表示跳过前5条记录，然后LIMIT 5表示从第6条记录开始取5条记录。

分组查询

单子段分组

当你只需要根据一个字段进行分组时，就使用单字段分组。这通常用于统计每个分类下的记录数、总和等。

示例：

假设有一个sales表，包含year（年份）和amount（销售额）两个字段，你想知道每年的总销售额。

SELECT year, SUM(amount) AS total_sales  
FROM sales  
GROUP BY year;

这个查询会按照year字段进行分组，并计算每个年份的总销售额。

多字段分组

当你需要根据多个字段的组合进行分组时，就使用多字段分组。这通常用于更复杂的统计需求，比如同时按年份和地区分组统计销售额。

示例：

假设sales表还包含一个region（地区）字段，你想知道每年每个地区的总销售额。

SELECT year, region, SUM(amount) AS total_sales  
FROM sales  
GROUP BY year, region;

这个查询会按照year和region两个字段的组合进行分组，并计算每个组合的总销售额。

group by分组限制

用法：查询一个或多个列对结果集进行分组，通过一定的规则将一个数据集划分为若干个小区域

格式：select后面跟1）分组字段2）聚合函数，其他字段不允许

order by

用法：对查询结果排序默认升序排序，按照一个或多个列对结果集进行排序若需要指定排序对结果集进行升序(ASC)或降序(DESC)排序

格式：

select column1,column2.....
order by column_name[ASC|DESC]

示例：显示每张album(专辑)的title(名称)和包含track曲目数

select album.title,count(track.album) as song_count
from album LEFT JOIN track
on album.albumCode=track.album
GROUP BY album.title,album.albumCode
order by album.title;

where和having区别(很少与子查询嵌套)

  • WHERE子句：在数据分组前进行过滤，即它作用于原始数据行。如果条件不满足，则这些行不会包含在分组中。WHERE子句不能包含聚合函数。
  • HAVING子句：在数据分组后进行过滤，即它作用于分组后的结果。HAVING子句可以包含聚合函数，用于过滤分组后的结果。

示例：

假设你想找出销售额总和大于1000的年份。

使用WHERE（错误用法，因为WHERE不能包含聚合函数）：

-- 错误示例  
SELECT year, SUM(amount) AS total_sales  
FROM sales  
WHERE SUM(amount) > 1000  
GROUP BY year;

上面的查询会报错，因为WHERE子句不能直接使用聚合函数。

使用HAVING（正确用法）：

SELECT year, SUM(amount) AS total_sales  
FROM sales  
GROUP BY year  
HAVING SUM(amount) > 1000;

这个查询会先按年份分组，然后计算每个年份的总销售额，最后过滤出总销售额大于1000的年份。

常用函数

数值型函数

字符串函数

字符串函数是MySQL中最常用的一类函数，主要用于处理表中的字符串。

2.1 insert(s1,x,len,s2)函数

insert(s1,x,len,s2)函数将字符串s1中x位置开始长度为len的字符串替换为s2

INSERT INTO students (name, age, grade) VALUES ('Alice', 20, 'Sophomore');

2.2 upper(x)函数ucase(x)函数

upper(x)函数和ucase(x)函数将字符串x的所有字母变成大写。

  • upper(x),ucase(x)用于将字母转成大写，x可以是单个字母也可以是字符串

  • select upper("a");--A
select upper("abc");--ABC

  • lower(x),lcase(x)用于将字母转成小写，x可以是单个字母也可以是字符串

  • select lower("a");--a
select lower("Abc");--abc

  • 对于已经是了的，不会进行大小写转换。

2.3 left(x,n)函数

left(x,n)函数返回字符串x的前n个字符。

例如：把字符串’我叫张三来自中国‘返回前四个字符
在这里插入图片描述

2.4 concat(x1,x2,x3…)函数

返回结果为连接参数产生的字符串。如果有任何一个参数为null，则返回值为null。

如果想要在多个字符串合并结果中将每个字符串都分隔一下，可以使用**concat_ws(分隔符,str1,str2,str3…),如果传入的分隔符为null，那么最终结果是null（不过这时候如果str有为null不影响结果）

select concat("i","am","superman");--iamsuperman
select concat_ws("","i","am","superman");--i am superman

2.5 rtrim(x)函数

rtrim(x)函数将去掉字符串x结尾处的空格。

例如：
在这里插入图片描述

2.6 substring(x,n,len)函数

substring(x,n,len)函数从字符串x的第n个位置开始获取长度为len的字符串。

在这里插入图片描述

2.7 reverse(x)函数

reverse(x)函数将字符串x的顺序反过来。

在这里插入图片描述

2.8 field(x,x1,x2,x3…)函数

field(x,x1,x2,x3…)函数返回第一个与字符串x匹配的字符串的位置。

在这里插入图片描述

2.9 position(x1 in x)函数和instr(x,x1)函数

(1)position(x1 in x)函数表示子字符串x1在字符串x中的开始位置。
(2)instr(x,x1)函数表示子字符串x1在字符串x中的开始位置。

2.10 substring_index()函数

格式：substring_index(str,delim,count)。

日期和时间函数

流程控制函数（了解）

子查询

一、子查询定义

　　定义：

　　子查询允许把一个查询嵌套在另一个查询当中。

　　子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。

　　子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete、set或 者do。

　　子查询的位置：
　　　　select 中、from 后、where 中.group by 和order by 中无实用意义。

二、子查询分类

　　子查询分为如下几类：

　　　1. 标量子查询：返回单一值的标量，最简单的形式。

　　　2. 列子查询：返回的结果集是 N 行一列。

　　　3. 行子查询：返回的结果集是一行 N 列。

　　　4. 表子查询：返回的结果集是 N 行 N 列。

　　可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS

　　一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询。

　　如果子查询返回一个标量值（就一个值），那么外部查询就可以使用：=、>、<、>=、<=和<>符号进行比较判断；如果子查询返回的不是一个标量值，而外部查询使用了比较符和子查询的结果进行了比较，那么就会抛出异常。

　　1. 标量子查询：

　　　　是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧
　　　　示例： 　　

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)``SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)``SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

　2. MySQL 列子查询：

　　　　指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。
　　　　可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧
　　　　可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。
　　　　示例：

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)``SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)``SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

　　　　NOT IN 是 <> ALL 的别名，二者相同。
　　　　特殊情况
　　　　　　如果 table2 为空表，则 ALL 后的结果为 TRUE；
　　　　　　如果子查询返回如 (0,NULL,1) 这种尽管 s1 比返回结果都大，但有空行的结果，则 ALL 后的结果为 UNKNOWN 。
　　　　注意：对于 table2 空表的情况，下面的语句均返回 NULL：

SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)``SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)

　3. MySQL 行子查询：

　　　　指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。
　　　　例子：

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)``
注：(1,2) 等同于 row(1,2)``SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

　4. MySQL 表子查询：

　　　　指子查询返回的结果集是 N 行 N 列的一个表数据。
　　　　例子：

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

三、子查询例举

　　1. ANY进行子查询

　　any关键词的意思是“对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE”。

　　好比“10 >any(11, 20, 2, 30)”，由于10>2，所以，该该判断会返回TRUE；只要10与集合中的任意一个进行比较，得到TRUE时，就会返回TRUE。　　

select` `table1.customer_id,city,count(order_id)``from` `table1 ``join` `table2``on` `table1.customer_id=table2.customer_id``where` `table1.customer_id<>``'tx'` `and table1.customer_id<>``'9you'``group` `by` `customer_id``having count(order_id) >``any (``select` `count(order_id)``from` `table2``where` `customer_id=``'tx'` `or customer_id=``'9you'``group` `by` `customer_id);

　　any的意思比较好明白，直译就是任意一个，只要条件满足任意的一个，就返回TRUE。

　　2. 使用IN进行子查询

　　使用in进行子查询，这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。

　　in是“=any”的别名，在使用“=any”的地方，我们都可以使用“in”来进行替换。

　　有了in，肯定就有了not in；not in并不是和<>any是同样的意思，not in和<>all是一个意思。

　　3. 使用SOME进行子查询

　　some是any的别名，用的比较少。
　　

　　4. 使用ALL进行子查询

　　all必须与比较操作符一起使用。all的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。

　　好比“10 >all(2, 4, 5, 1)”，由于10大于集合中的所有值，所以这条判断就返回TRUE；而如果为“10 >all(20, 3, 2, 1, 4)”，这样的话，由于10小于20，所以该判断就会返回FALSE。

　　<>all的同义词是not in，表示不等于集合中的所有值，这个很容易和<>any搞混，平时多留点心就好了。

　　5.标量子查询

　　根据子查询返回值的数量，将子查询可以分为标量子查询和多值子查询。在使用比较符进行子查询时，就要求必须是标量子查询；如果是多值子查询时，使用比较符，就会抛出异常。

　　6. 多值子查询

　　与标量子查询对应的就是多值子查询了，多值子查询会返回一列、一行或者一个表，它们组成一个集合。我们一般使用的any、in、all和some等词，将外部查询与子查询的结果进行判断。如果将any、in、all和some等词与标量子查询，就会得到空的结果。

　　7. 独立子查询

　　独立子查询是不依赖外部查询而运行的子查询。什么叫依赖外部查询？先看下面两个sql语句。

　　sql语句1：获得所有hangzhou顾客的订单号。　

select` `order_id``from` `table2``where` `customer_id ``in``     ``(``select` `customer_id``     ``from` `table1``     ``where` `city=``'hangzhou'``);

　sql语句2：获得城市为hangzhou，并且存在订单的用户。

select` `*``from` `table1``where` `city=``'hangzhou'` `and exists``        ``(``select` `*``        ``from` `table2``        ``where` `table1.customer_id=table2.customer_id);

　　上面的两条sql语句，虽然例子举的有点不是很恰当，但是足以说明这里的问题了。

　　　　对于sql语句1，我们将子查询单独复制出来，也是可以单独执行的，就是子查询与外部查询没有任何关系。

　　　　对于sql语句2，我们将子查询单独复制出来，就无法单独执行了，由于sql语句2的子查询依赖外部查询的某些字段，这就导致子查询就依赖外部查询，就产生了相关性。

　　对于子查询，很多时候都会考虑到效率的问题。当我们执行一个select语句时，可以加上explain关键字，用来查看查询类型，查询时使用的索引以及其它等等信息。比如这么用：

explain ``select` `order_id`` ``from` `table2`` ``where` `customer_id ``in``      ``(``select` `customer_id``      ``from` `table1``      ``where` `city=``'hangzhou'``);

　使用独立子查询，如果子查询部分对集合的最大遍历次数为n，外部查询的最大遍历次数为m时，我们可以记为：O(m+n)。而如果使用相关子查询，它的遍历 次数可能会达到O(m+m*n)。可以看到，效率就会成倍的下降；所以，大伙在使用子查询时，一定要考虑到子查询的相关性。

　　8.相关子查询

　　相关子查询是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次计算。但是在MySQL的内部，会进行动态优化，会随着情况的不同会 有所不同。使用相关子查询是最容易出现性能的地方。而关于sql语句的优化，这又是一个非常大的话题了，只能通过实际的经验积累，才能更好的去理解如何进 行优化。

　　9.EXISTS谓词

　　EXISTS是一个非常牛叉的谓词，它允许数据库高效地检查指定查询是否产生某些行。根据子查询是否返回行，该谓词返回TRUE或FALSE。与其 它谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN，对于EXISTS来说，UNKNOWN就是FALSE。 还是上面的语句，获得城市为hangzhou，并且存在订单的用户。

select` `*``from` `table1``where` `city=``'hangzhou'` `and exists``        ``(``select` `*``        ``from` `table2``        ``where` `table1.customer_id=table2.customer_id);

　

　　关于IN和EXISTS的主要区别在于三值逻辑的判断上。EXISTS总是返回TRUE或FALSE，而对于IN，除了TRUE、FALSE值外， 还有可能对NULL值返回UNKNOWN。但是在过滤器中，UNKNOWN的处理方式与FALSE相同，因此使用IN与使用EXISTS一样，SQL优化 器会选择相同的执行计划。

　　说到了IN和EXISTS几乎是一样的，但是，就不得不说到NOT IN和NOT EXISTS，对于输入列表中包含NULL值时，NOT EXISTS和NOT IN之间的差异就表现的非常大了。输入列表包含NULL值时，IN总是返回TRUE和UNKNOWN，因此NOT IN就会得到NOT TRUE和NOT UNKNOWN，即FALSE和UNKNOWN。

　　10. 派生表

　　上面也说到了，在子查询返回的值中，也可能返回一个表，如果将子查询返回的虚拟表再次作为FROM子句的输入时，这就子查询的虚拟表就成为了一个派生表。语法结构如下：

FROM (subquery expression) AS derived_table_alias

　由于派生表是完全的虚拟表，并没有也不可能被物理地具体化。

四、子查询优化

　　很多查询中需要使用子查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死。子查询可以使查询语 句很灵活，但子查询的执行效率不高。

　　子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕 后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

　　在MySQL中可以使用连接查 询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。

　　　　使用连接(JOIN)来代替子查询
　　　　如： 　　

``SELECT * FROM t1``WHERE t1.a1 NOT ``in` `(SELECT a2 FROM t2 )``优化后：``SELECT * FROM t1``LEFT JOIN t2 ON t1.a1=t2.a2``WHERE t2.a2 IS NULL

例子2：

``SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)``优化后：``SELECT * FROM article``inner ``join` `blog``on` `(article.title=blog.title AND article.content=blog.content AND article.uid=blog.uid)

　不能优化的子查询：
　　1、mysql不支持子查询合并和聚合函数子查询优化，mariadb对聚合函数子查询进行物化优化；
　　2、mysql不支持from子句子查询优化，mariadb对from子句子查询进行子查询上拉优化；
　　3、mysql和mariadb对子查询展开提供有限的支持，如对主键的操作才能进行上拉子查询优化；
　　4、mysql不支持exists子查询优化，mariadb对exists关联子查询进行半连接优化，对exists非关联子查询没有进一步进行优化；
　　5、mysql和mariadb不支持not exists子查询优化；
　　6、mysql和mariadb对in子查询，对满足半连接语义的查询进行半连接优化，再基于代价评估进行优化，两者对半连接的代价评估选择方式有差异；
　　7、mysql不支持not in子查询优化，mariadb对非关联not in子查询使用物化优化，对关联not in子查询不做优化；
　　8、mysql和mariadb对>all非关联子查询使用max函数，<all非关联子查询使用min函数，对=all和非关联子查询使用exists优化；
　　9、对>some和>any非关联子查询使用min函数，对<some和<any非关联子查询使用max函数，=any 和=some子查询使用半连接进行优化，对>some和>any关联子查询以及<some和<any关联子查询只有exists 优化。

联表查询

笛卡尔乘积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X ×
Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员在MySQL中，笛卡尔积是指从两个或多个表中获取所有可能的组合。当你在查询中没有指定任何连接条件时，MySQL将返回这些表之间的笛卡尔积。

下面是一个简单的示例，展示了如何使用笛卡尔积：

假设我们有两个表：表A和表B。表A有列a1和a2，表B有列b1和b2。我们想要获取这两个表的笛卡尔积。

select * from tableA, tableB;(推荐使用)

*select from tableA cross join tableB;

这将返回一个结果集，其中包含表A和表B中所有行的所有可能组合。结果集将具有以下列：a1、a2、b1和b2。

笛卡尔积可能会生成非常大的结果集，尤其是当表的行数较多时。因此，在实际应用中，应该避免无意义的笛卡尔积，确保使用适当的连接条件来限制结果集的大小。

实例：

select*from t_subject cross join t_class #方式一

select*from t_subject,t_class -- 方式二(推荐使用)

内连接（默认）

特点：左表和右表都不能为null

方式一 select*from 表一 inner join 表二 on 表一.列=表二.列

方式二 select*from 表一,表二 where 表一.列=表二.列

实例：

内连接(方式一)推荐使用特点左表和右表都不能为null
select *from t_subject inner join t_class on t_subject.cid = t_class.cid
-- 方式二
select * from t_subject,t_class where t_subject.cid = t_class.cid

左外连接

特点：左表不能为null,右表可以为null

左连接返回左表中所有记录和右表中匹配的记录，如果右表中没有匹配的记录，则返回 NULL 值

格式：select *from 表一 left join 表二 on 表一.列=表二.列

select *from t_subject left join t_class on t_subject.cid = t_class.cid

实例：

找出包含song(歌曲)'Alison'(track表)的专辑(album表)title(名称)和artist(作者)

select album.title,album.artist
from album left join track 
on album.albumCode = track.album
where track.song='Alison';

右外连接

特点：左表可以为null,右表不可以为null

右连接返回右表中所有记录和左表中匹配的记录，如果左表中没有匹配的记录，则返回 NULL 值

select *from 表一 right join 表二 on 表一.列=表二.列

select *from t_subject right join t_class on t_subject.cid

全连接（mysql不支持）

返回左右表中所有的记录和左右表中连接字段相等的记录。在from子句中使用关键字“full outer join”或关键字“full join”来连接两张表。

select column
from table1
full join table2
on table1.column = table2.column

自连接

select *from t_subject as s1 join t_subject as s2

临时表

格式举例

create TEMPORARY table tmp_table_subject
select name,subject from t_subject where score > 70
select * from tmp_table_subject;

SQL执行顺序

  1. SQL语句关键字的执行顺序

通常我们执行一条SQL语句它的执行顺序如下

  • FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

  • JOIN table2 所以先是确定表，再确定关联条件

  • ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

  • WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3

  • GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

  • HAVING 对分组后的记录进行聚合 产生中间表Temp5

  • SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6

  • DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

  • ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

  • LIMIT 对中间表Temp8进行分页，产生中间表Temp9

二、mySql的执行计划
1、什么是执行计划

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数

2、执行计划的内容

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

①.id sql执行计划的顺序 或子查询表的执行顺序

id一样，按照顺序执行；id越大，执行的优先级就越高（如子查询）

②.select_type 表示查询中每个select子句的类型

id select_type
1 SIMPLE
2 PRIMARY
3 SUBQUERY
4 DERIUED
5 UNION
6 UNION RESULT

a.SIMPLE：查询中不包含子查询或者UNION

b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）

e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED

f.从UNION表获取结果的SELECT被标记为：UNION RESULT

③.type

MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：

ALL index range ref eq_ref const,system NULL

由左至右，由最差到最好

ALL：全表扫描

index：index类型只遍历索引树

索引的存在形式是文件，按存储结构划分：FULLTEXT，HASH，BTREE，RTREE。

对应存储引擎支持如下：

MyISAM BTREE,FULLTEXT,RTREE
Innodb BTREE,RTREE
Memory HASH,BTREE
NDB BTREE,HASH,RTREE

转载mySql索引：http://blog.csdn.net/jesseyoung/article/details/38037543

range：索引范围扫描

对索引字段进行范围查询，使用in则是使用rang范围查询； 使用">" ,"<" 或者 "between" 都是可以使用索引的，但是要控制查询的时间范围，一般查询数据不要超过数据总数的 15%

ref：非唯一性索引

类似 select count(1) from age = '20';

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

④.key

表示在执行语句用到的索引

查询中若使用了覆盖索引，则该索引仅出现在key列表中

覆盖索引：查询数据只需要通过索引就可以查询出，如55万条数据，使用索引，立刻可以查询出 2000条数据，同时Extra字段是Using index

⑤.Extra

Using index : 使用覆盖索引的时候就会出现

using index condition：查找使用了索引，但是需要回表查询数据

Using where ：在查找使用索引的情况下，需要回表去查询所需的数据

using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

Using temporary：需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：无法利用索引完成的排序操作称为“文件排序”；

很多场景都是索引是一个字段，order by 排序的字段与索引字段不一致，导致的Using fileSort;

此时可以给排序字段和where条件字段，添加为组合索引，同时保证索引查询的数据不超过总量的15%，避免fileSort

注：回表的含义是，先根据索引查询数据，然后在根据确定的数据id和查询条件再去查询具体的数据的过程

SQL语句分析

SQL（Structured Query Language）是一种用于存储、操作和检索数据库中数据的标准编程语言。SQL语句可以执行多种操作，包括数据查询、数据更新、数据定义和数据访问控制等。以下是一些基本的SQL语句类型及其分析：

  1. SELECT语句：

    • 用于从数据库中检索数据。
    • 基本语法：SELECT column1, column2, ... FROM table_name;
    • 可以包含条件（WHERE子句）、排序（ORDER BY子句）、分组（GROUP BY子句）和限制结果数量（LIMIT子句）。

  2. INSERT INTO语句：

    • 用于向数据库表中插入新数据。
    • 基本语法：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

  3. UPDATE语句：

    • 用于修改数据库中已存在的数据。
    • 基本语法：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

  4. DELETE语句：

    • 用于从数据库表中删除数据。
    • 基本语法：DELETE FROM table_name WHERE condition;

  5. CREATE语句：

    • 用于创建新的数据库、表、视图或其他数据库对象。
    • 基本语法：CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

  6. ALTER语句：

    • 用于修改已存在的数据库结构，如添加或删除表的列。
    • 基本语法：ALTER TABLE table_name ADD column datatype;

  7. DROP语句：

    • 用于删除数据库或表。
    • 基本语法：DROP TABLE table_name;

  8. JOIN语句：

    • 用于结合来自两个或多个表的行，基于相关的列之间的关系。
    • 基本语法：SELECT columns FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

  9. TRANSACTION语句：

    • 用于管理数据库事务，确保数据的完整性。
    • 基本语法：BEGIN TRANSACTION; (开始事务) COMMIT; (提交事务) ROLLBACK; (回滚事务)

  10. GRANT和REVOKE语句：

    • 用于控制用户对数据库的访问权限。
    • 基本语法：GRANT privilege_type ON object TO user; REVOKE privilege_type ON object FROM user;

每个SQL语句都有其特定的语法和用途，正确使用它们可以有效地与数据库进行交互。在实际应用中，SQL语句可能会更加复杂，包含多个子句和复杂的条件逻辑。理解这些基本语句是掌握SQL和进行数据库编程的基础。

标签：语句,name,--,查询,select,Mysql,WHERE,SELECT
From： https://www.cnblogs.com/yangcurry/p/18413247

相关文章

  • 浅谈pSLC ,鱼和熊掌如何兼得
    浅谈pSLC,鱼和熊掌如何兼得一、什么是pSLCpSLC（Pseudo-SingleLevelCell）即伪SLC，是一种将MLC/TLC改为SLC的一种技术，现NandFlash基本支持此功能，可以通过指令控制MLC进入pSCL模式，存储时在MLC的每个单元中仅存储1bit数据，使MLC拥有SLC的性能，同时具有ML......
  • 秸秆焚烧烟雾监测报警系统
    秸秆焚烧烟雾监测报警系统在监测区全自动对秸秆焚烧行为实时监测，不用人工控制。一旦发现烟雾和火苗，秸秆焚烧烟雾监测报警系统会自动报警并通知监控后台工作人员，并提醒负责人妥善处置。秸秆焚烧烟雾监测报警系统对监测区域内的秸秆焚烧信息进行7×24h无间断识别分析，减少人力巡视成......
  • PbootCMS提交留言成功后跳转到指定的网址
    要实现在PbootCMS中留言表单提交成功后跳转到指定网址，可以通过修改相关文件来实现。以下是详细的步骤：步骤1：定位相关文件首先找到包含留言表单提交逻辑的相关文件。通常情况下，留言表单的提交逻辑会在 /core/function/helper.php 文件中。步骤2：修改提交成功后的跳转逻辑打......
  • 宝塔面板安装 MySQL8.0，阿里云服务器配置不够
    mysql更新到8.0版本，mysql8.0对比以往的版本有了很大的提升，但是要求的服务器配置也就变得越来越高。对于低配置服务器，在宝塔面板进行安装时，总会出现至少需要XXG 内存才能安装”。但我们又想要安装 MySQL8.0新版本，这时候该怎么办呢？只有强制在宝塔面板中安装MySQL8.0。低......
  • Java入门：08.Java中的static关键字01
    1static关键字可以修饰属性变量，方法和代码段static修饰的属性称为静态属性或类属性，在类加载时就在方法区为属性开辟存储空间，无论创建多少个对象，静态属性在内存中只有一份。可以使用类名.静态属性的方式引用static修饰的方法称为静态方法或类方法，在类加载时就在方法......
  • PbootCMS判断一个字段为空的时候调用另外一个字段
    在PbootCMS中，如果你想在一个字段为空时调用另一个字段，可以使用条件判断标签来实现。下面是一个具体的示例，展示了如何在某个字段为空时调用另一个字段。示例场景假设你有一个字段 ext_icos 用于存储图片，还有一个字段 ico 作为备用的缩略图。当 ext_icos 字段不为空时，调用......
  • 如何搭建短视频平台，微服务架构为系统性能加成
    如何搭建短视频平台，微服务架构为系统性能加成什么是微服务？微服务是一种架构风格，其中单体应用被划分为若干个小型、松散耦合且独立的服务。所有这些微服务共同工作，形成一个更大的系统。在微服务架构中，每个服务代表一个特定的业务能力，并作为一个拥有自己的数据库和逻辑的独立......
  • Java入门：08.Java中的static关键字02（拓展：单例模式）
    1.3对象代码段和静态代码段什么是代码段？就是个{}里面包含一堆代码代码段可以写在方法中，也可以写在方法外代码段可以方法中和方法外独立存在，也可以配合着分支结构和循环结构存在classA{ {   ..... }  publicvoidt1(){   { ......
  • 【LeetCode 算法笔记】49. 字母异位词分组
    目录问题描述计数法：计数法（用哈希表）：排序法：问题描述给你一个字符串数组，请你将字母异位词组合在一起。可以按任意顺序返回结果列表。字母异位词是由重新排列源单词的所有字母得到的一个新单词。示例1:输入:strs=[“eat”,“tea”,“tan”,“ate”,“nat”......
  • 【CSS in Depth 2 精译_030】5.2 Grid 网格布局中的网格结构剖析（下）
    当前内容所在位置（可进入专栏查看其他译好的章节内容）第一章层叠、优先级与继承（已完结）1.1层叠1.2继承1.3特殊值1.4简写属性1.5CSS渐进式增强技术1.6本章小结第二章相对单位（已完结）2.1相对单位的威力2.2em与rem2.3告别像素思维2.4视口的相对单位2.5......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99