MYSQL数据查询

一、单表查询

        数据库是为了更方便有效的管理信息而存在的，使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出、数据查询是数据库最重要的功能。通过SQL语句可以从表或视图中迅速方便的检索数据。

1、SELECT 语句定义

可以实现对表的选择、投影及连接操作。

可以从一个或多个表中根据用户需要从数据库中选出匹配的行和列，结果通常是生成一个临时表。

语法格式：

SELECT [ALL|DISTINCT] 输出表达式，……
	[FROM 表名1[,表名2]……]                        FROM 子句 
	[WHERE 条件]								WHERE 子句
	[GROUP BY 列名]
		[ASC|DEAC],……							GROUP BY  子句
	[HAVING 条件]									HAVING 子句
	[ORDER BY {列名 | 表达式 | 列编号}				ORDER BY
		[ASC | DESC],……]
	[LIMIT {[偏移量,] 行数|行数OFFSET 偏移量}]         LIMIT子句

1、1选择列

选择指定的列

SELECT 表达式;

输出列表达式可以是MYSQL 所支持的任何运算的表达式，若SELECT 语句的表达式是表中的字段变量名，则字段名变量之间要以逗号分隔。

 定义列的别名

当希望查询结果的列使用自定义的列标题是，可以在列名之后使用AS 子句来更改查询结果的列名

格式为：

SELCECT 列名 [AS] 别名;

 当定义的列标题中含有空格时，必须使用引号将标题括起来。

 

 注：在WHERE 子句中不能使用别名，因为在执行WHERE代码时可能尚未确定列值。

替换查询结果中的数据

在对表格进行查询时，有时希望对所查询的某列希望得到的是一种概念，而不是具体的数据。

要替换查询结果中的数据，则使用查询中的CASE 表达式

格式如下：

CASE
	when 条件1 then 表达式1
	when 条件2 then 表达式2
	……
	ELSE 表达式n
END

语法说明：

CASE：表达式以CASE开始，END结束，MYSQL从条件1开始判断，条件1成立输出表达式1，结束；若表达式1不成立，判断条件2，若成立输出表达式2，结束；如果条件都不成立输出表达式n。

 计算列值

使用SELECT 对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果

mysql> select 图书编号,订购册数*订购单价 as 订购金额
    -> from sell
    -> where 是否发货='已发货';

 消除结果集中的重复行

对表只选择某些列时，可能会出现重复行。可以使用DISTINCT 关键字消除结果集中的重复行。

可以使结果集中的重复行只选择一行，保证行的唯一性。

格式如下：

SELECT DISTINCT 列名1[,列名2……]

 聚合函数

SELECT 子句的表达式中可以包含所谓的聚合函数（Aggregation Function）。聚合函数通常用于一组值进行计算，然后返回单个值。除COUNT() 函数外，聚合函数都会忽略空值。聚合函数通常与GROUP BY 子句一起使用。若SELECT语句中有一个GROUP BY 子句，则该聚合函数对所有列起作用；若没有在，则SELECT 语句只产生一行作为结果

常用聚合函数

常用聚合函数

常用聚合函数	常用聚合函数
函数名	说明
COUNT	求组中的项数，返回int类型整数
MAX	求最大值
MIN	求最小值
SUM	返回表达式中所有值得和
AVG	求组中值得平均值

（1）COUNT()函数

聚合函数中最常使用的是COUNT()函数 ，用于统计表中满足条件的行数或总行数，返回SELECT 语句检索到的行中非NULL值得数目；若找不到匹配的行，则返回0；

格式如下：

COUNT({[ALL | DISTINCT] 表达式}|*)

语法说明：

表达式：可以是常量、列、函数或表达式，其数据的类型是blob或text之外的任何类型。

ALL|DISTINCT : ALL表示对所有的值进行运算，DISTINCT表示去重复值，默认为ALL。

注：使用COUNT(*)时将返回检索的总行数目，不论其是否包含NULL值

mysql> select count(是否结清) as '已结清的订单数'
    -> from sell;

 注：这里的COUNT(是否结清)，只统计是否结清列表中不为NULL的行。若在创建表格数据的使用，使用了' '或'null'值，是不属于NULL的，NULL不需要单引号，也不区分大小写。

(2) MAX()函数 和 MIN()函数

MAX()函数和MIN()函数分别用于求表达式中所有 值项的最大值与最小值

格式如下：

MAX / MIN ([ALL | DISTINCT] 表达式)

语法说明：

当给定列 上只有空值或检索出的中间结果为空时，MAX和MIN函数的值也为空。

mysql> select max(单价),min(单价)
    -> from book
    -> where 图书类别='计算机';

 （3） SUM()函数 和 AVG() 函数

SUM和AVG分别用于求表达式中的所有值的总和与平均值

格式如下：

SUM / AVG ([ALL | DISTINCT] 表达式)

语法说明：

表达式：可以是常量、列、函数或表达式，其数据类型只能是数值型数据。

mysql> select sum(数量) as '数量'
    -> from book
    -> where 图书类别='计算机';

 WHERE 子句

WHERE子句必须跟在FROM子句之后；在WHERE子句中，使用一个条件从FROM子句中间结果中选取行。

格式如下：

WHERE <判定运算>

判定运算：结果为 TRUE、FALSE 或UNKNOWN

格式如下：

表达式 {= | < | <= | > | >= |  <=> | <> | != } 表达式         //比较运算符
|表达式 [NOT] LIKE 表达式									//LIKE 运算符
|表达式 [NOT] BETWEEN 表达式1 AND 表达式2					//指定范围
|表达式 IS [NOT] NULL										//判断是否为空值
|表达式 [NOT] IN (子查询|表达式1[,……表达式n])					//IN子句

WHERE 子句会根据条件对FROM子句的中间结果中的行，一行一行的进行判断，当条件为TRUE时，一行就被包含到WHERE 子句的中间结果集中

在SQL中，返回逻辑值（TRUE 或FALSE）的运算符或关键字都可称为谓词，判定运算符包括比较运算、模式匹配、范围比较、空值比较和子查询。

（1） 比较运算

比较运算符用于比较 两个表达式值，MYSQL 支持的比较运算符有：=（等于）、<（小于）、<=(小于等于)、>(大于)、>=(大于等于)、<=>(相等或都等于空)、<>(不等于)、!=(不等于)。

格式如下：

表达式 {=|<|<=|>|>=|<==>|<>|!=} 表达式

表达式时除TEXT 和BLOB类型外的表达式

当两个表达式均不为空值（NULL）时，除了"<=>"运算符，其他比较运算返回逻辑值 TRUE（真） 或 FALSE（假）；而当两个表达式值中有一个为空值或 都为空值时，将返回UNKNOWN。

mysql> select *
    -> from book
    -> where 图书名称='c++程序设计基础';

 <=>，这是MYSQL数据库中特殊的等于运算符，当两个表达式彼此相等或i都等于空值时，其运算的值为TRUE，其中有一个空值或都是非空值但不相等时该条件结果就是FALSE，而没有UNKNOWN的情况。

mysql> select * from sell
    -> where 是否收货<=>NULL;

 (2) 逻辑运算

逻辑运算可以将多个判定运算的结果通过逻辑运算符（AND、OR、XOR和NOT））组成更为复杂的查询条件

逻辑运算符用于对某个条件进行测试，运算结果为TRUE(1) 或 FALSE(0)。

MYSQL提供的常见逻辑运算符如下：

运算符	运算规则	运算符	运算规则
NOT 或 ！	逻辑非	OR 或 ||	逻辑或
AND 或 &&	逻辑与	XOR	逻辑异或

mysql> select * from sell
    -> where 是否收货='已收货' and 是否结清='已结清';

 注：逻辑运算符 AND 前后必须用空格隔开，否则或出现语法错误

(3) 模式匹配

LIKE 运算符用于指出一个字符串是否与指定字符串相匹配，其运算对象可以是 char 、varchar、text、datetime等类型的数据，返回逻辑值TRUE 或 FALSE。

语法格式如下：

表达式 [NOT] LIKE 表达式

使用LIKE进行模式匹配时，常使用特殊符号 _ 和 % 进行模糊查询。

% 代表0个或多个字符

_ 代表单个字符

mysql> select 身份证号码,姓名,注册时间
    -> from member
    -> where  姓名 like '酒%';

 

若要查询特殊符号中的一个或全部（_ 和 %），需使用一个转义字符。

如当要查询_ 时，可以使用ESCAPE '#' 来定义#为转义字符，这样，语句中在#后面的就失去了其原有的特殊含义，被视为正常下划线。

mysql> select 图书编号,图书名称
    -> from book
    -> where 图书名称 like '%#_%' escape '#';

(4) 范围比较

BETWEEN 和 IN

当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。

BETWEEN关键字指出查询的范围

格式如下：

表达式 [NOT] BETWEEN 表达式1 AND 表达式2

语法说明：

若表达式的值在表达式1与表达式2之间，(包括这两个值)，则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。

表达式1的值不能大于表达式 2的值

mysql> select * from book
    -> where 出版时间 between '2010-1-1' and '2022-1-1';

 使用NOT BETWEEN 表示不在指定范围内

mysql> select * from book
    -> where 出版时间 not between '2010-1-1' and '2010-12-30';

 使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表 中的任一个匹配时，即返回TRUE 否则返回FALSE

格式如下：

表达式 [NOT] IN (子查询 | 表达式1[,……表达式n])

IN关键字应用最多的表达子查询，也可以用于OR运算

表达式 [NOT] IN (子查询 | 表达式1[,……表达式n])

 (5) 空值比较

当需要判定一个表达式的值是否为空值时，使用IS NULL 关键字。

格式如下：

表达式 IS [NOT] NUNLL

若表达式的值为空值，返回TRUE，否则返回FALSE；当使用 NOT时结果刚好相反

mysql> select * from sell
    -> where 是否发货 is null;

 二、多表查询

1、FROM子句

格式如下：

FROM 表名1[[AS] 别名1][,表名2[[AS] 别名2]]……     //查询表
	|JOIN 子句								//连接表

语法说明：

表名1 [ [AS ] 别名1 ]：与列别名一样，可以使用AS选项为表指定别名，表 别名主要用于相关子查询及连接查询中。

FROM子句指定了表别名，该SELECT语句中的其他子句都必须使用表别名来替代原始的表名。当同一个表在SELECT语句中多次被提到时，就必须要使用表别名 来加以区分

FROM子句可以用两种方法引用一个表

（1）、使用USE语句让一个数据库成为当前数据库，在该情况下，若在FROM子句中指定表名，则该表应该属于当前数据库

（2）、指定的时候在表名前带上表所属的数据库的名字。如下 ：
 

SELECT * FROM 库名.表名

mysql> select * from student.member;

 2、多表连接

若要在不同的表中查询数据，则必须在FROM子句中指定多个表。将不同列的数据组合到一个表中叫做表的连接。

（1）、全连接

指将每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中 出现的列，也就是笛卡尔积。

（2）、内连接

通常要设定条件来将结果集减少且有意义的表，这样的连接为内连接。若设定条件是等值条件，也叫等值连接。

（3）、外连接

A、左外连接（LEFT OUTER JOIN）：结果表中除了匹配行外，还包括左表有的但右表不匹配的行，对于这样的行，从右表被选择的列设置为NULL

B、右外连接（RIGHT　OUTER　JOIN）：结果表中除了匹配行外，还包括　右表有的但左表不匹配的行，对于　这样的行，，从　左表被选择的列设置为NULL

若FROM子句中将个表用逗号分隔，就指定了全连接，全连接得到的表产生数量非常多的行

mysql> select book.图书名称,sell.订购册数,sell.订购时间         //查询内容
    -> from book,sell									//从哪些表中查询
    -> where book.图书编号 != sell.图书编号;				//判断条件,这里因为我为给两个表对应创建 ,所以 就 判断了不等于

 3、JOIN 连接

使用JOIN关键字建立多表连接时，JOIN子句定义了如何使用JOIN关键字连接表

格式如下：

表名1 INNER  JOIN 表名2
|表名1 {LEFT | RIGHT} [OUTER] JOIN 表名2

NO 连接条件 | USING(列名)

（1）、内连接

指定 INNER关键字的连接时 内连接。内连接是在FROM子句中产生 的中间结果中应用ON条件后得到的结果

mysql> select book.书名,sell.订购册数,sell.订购时间
    -> from book inner join sell
    -> on (book.图书编号=sell.图书编号);

 

这里内连接ON（BOOK.图书编号=SELL.图书编号 ）条件是等值比较。等值连接是内连接的子集，当内连接的条件是等值比较时，等值 连接和内连接的结果相同。

内连接是 系统默认的，可以省略INNER 关键字。使用内连接后，FROM子句中的ON条件主要用来连接表，其他并不属于连接表的条件可以使用 WHERE子句来 指定

mysql> select 书名,订购册数
    -> from book join sell
    -> on  book.图书编号  = sell.图书编号
    -> where 书名 ='数值分析'  and  订购册数>100;

 内连接可以 用于多个表

mysql> select book.图书编号,姓名,书名,订购册数
    -> from sell join book on book.图书编号=sell.图书编号
    -> join member on sell.用户 = member.用户
    -> where 书名='模式分类' and 订购册数>50;

 作为特例，可以将一个表与其自身进行连接，称为自连接。若要在一个 表中 查找具有 相同 列值 的 行，则可以使用自连接。使用自连接时需为 表指定两个别名，且对所有列的引用均要用别名限定。

mysql> select a.订单号,a.图书编号,a.订购册数
    -> from sell as a  join sell as b
    -> on a.图书编号=b.图书编号 and a.订单号=b.订单号;

 

若要连接的表中有列名相同，并且连接的条件 就是列名 相等，则ON条件也可以换成USING子句。

USING（column_list) 子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中column_list 为两表中相同的列名。

mysql> select 姓名 from member
    -> join sell using(用户);

(2)、外连接  

指定OUTER 关键字的连接为外连接

mysql> select book.图书编号,book.数量,用户
    -> from book left outer join sell
    -> ON book.图书编号 =sell.图书编号;

 

若不使用LEFT OUTER JOIN ，则结果中不会包含未订购过的图书信息。

使用了左外连接后，结果返回的行中有 未订购过的图书 信息，相应的用户字段值为NULL

mysql> select 订单号,图书编号,订购册数,member.姓名
    -> from sell right join  member
    -> on member.用户=sell.用户;

 若用户没有购买过图书，sell表中就没有该用户 信息，则 结果表中相应的该行 的订单信息字段值均为 NULL

4、子查询

在查询 条件中，可以使用另一个查询的结果作为条件的一部分。

SQL标准允许SELECT多层嵌套使用，用来表示复杂的查询 。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE、DELETE语句中。

子查询通常与IN、EXIST谓词及比较运算符结合使用

（1）IN子查询

IN子查询用于进行一个给定值是否在子查询结果集中的判断。

格式为：

表达式 [NOT]  IN (子查询)

语法说明：

当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE 否知返回FALSE；若使用了NOT，则返回的值恰好相反。

IN(子查询)：只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。

mysql> select *
    -> from sell
    -> where 用户 in
    -> (select 用户 from member where 姓名="司徒寄凡");

 

在执行包含子查询的 SELECT语句时，系统先执行子查询，产生一个结果表，在执行查询。

子查询返回的数据，在执行外查询时，若外查询表中的每行等于子查询的结果表中的人一个值，则该行就被选择。

mysql> select * from member where 用户 in
    -> (select 用户 from sell where 图书编号 NOT in
    -> (select 图书编号 from book where 书名='克林姆特画集'));

 (2)、比较子查询

该子查询可以认为是IN子查询的扩展，其表达式的值与子查询的结果进行比较运算。

格式如下：

表达式  {<|<=|>|>=|!=|<>} {ALL | SOME | ANY} (子查询)

语法说明：

表达式：要进行比较的表达式

ALL|SOME|ANY：说明对比较运算的限制

若子查询的结果只返回一行数据时，可以通过比较运算符直接比较。若子查询的结果集返回多行数据时，需要用{ALL|SOME|ANY}来限定

ALL 指定表达式要与子查询结果集中的每个值都进行比较当表达式中的每个值都满足比较关系时 ，才返回TRUE 否则返回FALSE

SOME与ANY是同义词，表示表达式只要与子查询结果集中的某个值 满足比较关系时，就返回TRUE 否则返回FALSE

mysql> select * from member where 用户=ANY
    -> (select 用户 from sell
    -> where 图书编号 ='ISBN 654-8-84-886451-0');

 (3)、EXISTS 子查询

EXISTS 谓词 用于测试子查询的结果 是否为空表，若 子查询 的结果集不为空，则EXISTS返回TRUE否则返回FALSAE。EXISTS还可与NOT结合使用，即NOT EXISTS其返回值与EXISTS刚好相反。

格式如下：

[NOT] EXISTS (子查询)

mysql> select 姓名 from member where exists
    -> (select * from sell where 用户=member.用户
    -> and 订购册数>10);

 3、分类汇总与排序

3.1、GROUP BY 子句

GROUP BY 子句主要用于根据字段对行分组

格式如下：

GROUP BY [列名] [ASC|DESC],……[WTTH ROLLUP]

语法说明：

GROUP BY：子句后通常包含列名

NYSQL 对GROUP BY子句进行了扩展，可以在列后面指定ASC（升序）或DESC（降序）

GROUP BY可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合喊函数一起 使用

mysql> select 图书类别
    -> from book
    -> group by 图书类别;

 使用带ROLLUP 操作符的GROUP BY子句，指定在结果集内 不包含由GROUP BY提供的正常运行，还包含汇总行。

mysql> select  图书类别,出版社,sum(数量) as '库存数'
    -> from book group by 图书类别,出版社;

 若需要对统计 的数据进行分类小计，可以使用WITH ROLLUP 短语

mysql> select 图书类别 ,出版社,sum(数量) as '库存数'
    -> from book
    -> group by 图书类别,出版社
    -> with rollup;

 4、HAVING 子句

使用HAVING子句的目的与WHERE子句类似，不同的是WHERE子句是用来在FROM 子句之后选择行，而HAVING子句用来在GROUP BY子句后选择 行。

格式如下：

HAVING 条件

语法说明 ：

条件：定义和WHERE子句中的条件类似，不过HAVING子句中的条件可以包含聚合函数，而WHERE子句中则不可以。

SQL标准要求HAVING必须引用GROUP BY 子句中的列或用于 聚合函数中的列。不过MYSQL支持对此工作性质的扩展，并允许HAVING引用SELECT清单中的列和外部查询的列。

mysql> select 用户,avg(订购册数)  as '平均订购册数'
    -> from sell
    -> group by  用户
    -> HAVING avg(订购册数)>100;

ORDER BY子句  

在一条SELECT 语句中，若不使用 ORDER BY子句 ，结果中，行的顺序 是不可预料的 ，使用ORDER BY子句 后 可以保证 结果中的 行按一定 顺序排列

格式如下：

ORDER BY {列名|表达式|列编号} [ASC|DESC],……

语法说明：

ORDER BY子句后可以是一个列、一个 表达式或 一个正整数。列编号 是正整数，表示 按结果表中该位置上的 列排序

关键字 ASC表示升序排列，DESC表示降序排列，系统默认为ASC

mysql> select *
    -> from book
    -> order by 出版时间;

 5、LIMIT 子句

LIMIT 子句是 SELECT 语句的最后一个 子句，主要用于限制被SELECT语句返回的行数

格式如下：

LIMIT {[偏移量,] 行数|行数 OFFSET 偏移量}

语法说明：

偏移量和行数都必须是非负的整数常数

偏移量：指返回的第一行的偏移量

行数 ：指返回 的行数

mysql> select *
    -> from  member
    -> order by 注册时间
    -> limit 5;