数据库系统原理] 第 4 章 SQL 和关系数据库基本操作

最编程 2024-07-09 10:10:50

...

第一节 SQL概述

SQL已成为关系数据库的标准语言。

一 SQL的发展

目前没有一个DBS能够支持SQL标准的全部概念和特征。各DBMS产品在实现标准SQL时各有差别，与SQL标准准的符合程度也不尽相同，但它们仍都遵循SQL标准，并以SQL标准为主体进行相应地扩展或简化。

二 SQL的特点

①SQL不是某个特定DB供应商专有语言；几乎所有重要的RDMS都支持SQL。

②SQL简单易学。

③SQL尽管看上去简单，但它实际上是一种强有力的语言，可以进行非常复杂和高级的数据库操作。

SQL语句实际上不区分大小写，但实际使用这种为了易读性，建议对所有关键字使用大写。

三 SQL的组成

SQL的组成：

①数据查询（Data Query）

②数据定义（Data Definition）

③数据操纵（Data Manipulation）

④数据控制（Data Control）

其核心包括：

1 数据定义语言（DDL）

主要用于对DB本身即DB中各种对象进行创建、删除、修改等操作。其中，数据库对象有：表、默认约束、规则、触发器、存储过程等。

DDL包括的主要SQL语句有以下三个：

①CREATE：创建DB或DB中的对象

②ALTER：对DB和DB中的对象进行修改

③DROP：用于删除DB或DB中的对象

对于不同的DB对象，SQL语句所使用的语法格式不同。

2 数据操纵语言（DML）

主要用于操纵DB中的各种对象；特别是检索和修改数据。

宝库偶点主要SQL语句有：

①SELECT：从表或视图中检索Data，使用最频繁。

②INSERT：将Data插入到表或视图中。

③UPDATE：修改表或视图中的数据，可一行、多行或全部行。

④DELETE：从表或视图中删除数据，可根据条件。

3 数据控制语言（DCL）

主要用于安全管理，比如指定哪些用户可以查看或修改DB中的哪些数据。

主要的SQL语句有：

①GRANT：用于授予权限

②REVOKE：用于回收权限

4 嵌入式和动态SQL规则

它规定了SQL语句在高级程序设计语言中使用的规范方法，以适应较复杂的应用。<本教程并不讲这部分的内容>

5 SQL调用和会话规则

SQL调用提供灵活性、有效性、共享性以及使SQL具有更多高级语言特征。

SQL会话规则则可使应用程序连接到多个SQL服务器中的某一个。

<本教程并不讲这部分的内容>

第二节 MySQL预备知识

MySQL是RDBMS，具有C/S结构，最初是由瑞典MySQL AB公司开发。

具有体积小、速度快、开发源代码、兼容GPL的特点。

尽管与Oracle、DB2、SQL Server等大型RDB相比还有一些不足，但由于成本优势而大受欢迎。

一 MySQL使用基础

目前有两种部署架构：

①LAMP（Linux + Apache + MySQL + PHP/Perl/Python）

②WAMP（Windows + Apache + MySQL + PHP/Perl/Python）

<安装过程很简单，且网上教程很多，这里就不再介绍了>

二 MySQL中的SQL

MySQL作为RDBMS，遵循SQL标准，提供DDL、DML、DCL；且同样支持RDB的三级模式结构。

在MySQL中一个关系对应一个基本表，一个或多个基本表对应一个存储文件，一个表可以有若干索引，索引也是存放在与之相关的表的存储文件中。

其中，存储文件的逻辑结构组成了MySQL的内模式，并且存储文件的物理结构对最终用户是隐藏的。

视图是从一个或几个基本表中导出的表，尽管它也是关系，但其本身不独立存储在DB中，即DB指存储视图的定义而不存储视图对应的数据；因此视图是虚表。

且用户可以在视图上再定义视图。整个关系的示意图如下：

【数据库系统原理】第四章 SQL与关系数据库基本操作_SQL

此外，为了方便用户编程，MySQL在SQL的标准基础上增加了部分扩展的语言要素；包括：常量、变量、运算符、表达式、函数、流程控制语句和注解等。

（1）常量

常数很好理解，我们平时用到的数字、字符串、时间值什么的都可以被称为常数，它是一个确定的值，比如数字1，字符串'abc'，时间值2024-03-06 00:10:43啥的。

（2）变量

变量分为系统变量和用户变量；

系统变量和用户变量一样都是一个值和一个数据类型，但是不同的是，系统变量在MySQL服务器启动时就被引入并初始化完成了。

全局变量分为：

a) 全局系统变量：在MySQL启动时，全局系统变量就被初始化了，并且用于每个启动的会话。，如果使用global来设置系统变量，则该变量会被更改并用于新的连接，直到服务器被重新启动为止。

b) 会话系统变量：只适用于当前会话，大多数会话系统变量的名字和全局系统变量名字相同，当启动会话时，每个会话系统变量都和同名的全局系统变量相同，一个会话系统变量时可以更改的，但是这个新的值仅适用于正在运行的会话，不适用于所有其他会话。

用户变量：

有两种方法可以为用户定义的变量赋值(用户变量)。第一种方法是使用SET如下语句：

SET @variable_name := value;

您可以使用:=或=作为SET语句中的赋值运算符。例如，语句将数字100赋给变量@counter。

SET @counter := 100;

为变量赋值的第二种方法是使用SELECT语句。在这种情况下，必须使用：=赋值运算符，因为在SELECT语句中，MySQL将=运算符视为等于运算符。

SELECT @variable_name := value;

注意：变量名不区分大小写，@ID和@id是一样的。

select时必须用“:=赋值”

set时可以用“=”或“:=”

查看用户变量

show [session | global] variables like '%char%'; #默认情况下，不写session或global就表示session  #查看某个用户变量
SHOW VARIABLES LIKE '@%';  #查看所有用户变量

全局系统变量：

set global variable = value;
set @@global.variable = value;

select @@global.[variableName];     -- 查看指定变量的全局级系统变量的值
select @@global.autocommit;

或者
show global variables;
show global variables like [pattern];

会话级系统变量：

set session variable = value;
set variable = value;
set @@session.variable = value;
set @@variable = value;

show session variables;     -- 显示所有当前会话级系统变量
show variables;
show session variables like [pattern];

（3）运算符

算术运算符：+、-、*、/和%

位运算符：&位与、|位或、^未异或、~位取反、>>位右移、<<位左移

比较运算符：=、>、>=、<、<=、<>不等于、!=不等于、<=>相对或都为空

逻辑运算符：NOT或!（逻辑非）、AND或&&（逻辑与）、OR或||（逻辑或）、XOR（异或）

（4）表达式

一个表达式也可以作为一个操作数与另一个操作数来形成一个更复杂的表达式。

（5）内置函数

MySQL有100多个内置函数；可分为：数学函数、聚合函数、字符串函数、日期与时间函数、加密函数、控制流程函数、格式化函数、类型转换函数和系统信息函数等等。

（6）注释

单行注释

#和--的区别就是：#后面直接加注释内容，而--的第 2 个破折号后需要跟一个空格符在加注释内容。

多行注释

多行注释使用/* */注释符。/*用于注释内容的开头，*/用于注释内容的结尾。多行注释格式如下：

/*
 第一行注释内容
 第二行注释内容
*/

注释内容写在/*和*/之间，可以跨多行。

第三节数据定义

SQL标准提供的数据库定义语句有：

可以看到SQL标准并没有提供有关索引的语句；而MySQL中对某些对象的操作进行了扩展，提供额外支持；比如：

操作对象	操作方式
操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA	无
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW	无
索引	无	无	无

ALTER SCHEMA

ALTER VIEW

CREATE INDEX

DROP INDEX

ALTER INDEX

等

一数据库模式定义

包括创建、选择、修改、删除、查看。

1 创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXIST] db_name
[DEFAULT] CHARACTER SET [=] charset_name |
[DEFAULT] COLLATE [=] collaction_name

其中"[]"表示其中的内容是可选的；

"|"表示其两边的内容任选其一；

"IF NOT EXIST"用于在创建数据库前检查数据库名是否已经存在，不存在时才执行创建；

"db_name"是根据自己的情况自行定义的数据库名字，但必须符合命名规则，MySQL中不区分大小写，所以"DB01"和"db01"是一样的；

"DEFAULT"用来指定默认值；

"CHARACTER SET"用来指定字符集；

"COLLATE"用来指定字符集的校对规则。

2 选择数据库

USE语句实现从一个数据库到另一个数据库的跳转。

USE db_name;

3 修改数据库

语法：

ALTER {DATABASE | SCHEMA} db_name

例子：

ALTER DATABASE mysql_test
DEFAULT CHARACTER SET = gb1213
DEFAULT COLLATE gn1312_chinese_ci;

4 删除数据库

语法：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

5 查看数据库

语法：

SHOW {DATABASE | SCHEMA} [LIKE 'xxx' | WHERE expr];

expr表示一个表达式。

二表定义

只有成功创建DB后，才能在DB中创建表。

创建表的过程实际上就是定义每个字段的过程，同时也是实施Data完整性约束的过程。

1 创建表

语法：

CREATE [TEMPORARY] TABLE tbl_name(
col1 DataType [列级完整性约束条件] [默认值]
[,col2 DataType [列级完整性约束条件] [默认值]]
[,...]
)[ENGINE = 引擎类型];

例：在数据库mysql_test中新建一个名为customers的表，其中包含：cust_id，cust_name，cust_sex，cust_address，cust_contact字段，并设cust_id为主键。

USE mysql_test;

CREATE TABLE customers(
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_sex CHAR(1) NOT NULL DEFAULT 0,
cust_address CHAR(50) NULL,
cust_contact CHAR(50) NULL,
PRIMARY KEY(cust_id)
);

（1）临时表与持久表

创建时加"TEMPORARY"关键字则代表创建临时表；不加则创建持久表。

持久表创建后一直存在；临时表生命周期短，且只能对创建他的用户可见，当断开与该DB的连接时，MySQL会自动删除它们。

临时表一般使用场景：存储某些复杂SELECT语句结果。而后可能重复地使用这个结果，但它不需要永久保存。

（2）数据类型

（3）关键字AUTO_INCREMENT

自增属性：顺序是从1开始，每个表只能由一个自增列；且必须被索引。

自增列的值可以手动指定，只要被指定的值是唯一的（没被使用过的）,且后续自增讲以此值为基础+1。

（4）指定默认值

不指定的话，系统会根据字段的类型自动分配；不同的类型由不同的默认值。

（5）NULL值

允许插入时不给该字段添加值；默认是NULL，即允许空值。NOT NULL代表不允许空值。

注意：''不是NULL，它在NOT NULL里是合法的。

（6）主键

主键列里的所有值不允许重复，因此，必须加上NOT NULL的约束。

主键由一个列构成，则该列的值必须唯一。声明主键时可以在字段或列级完整性约束条件中指定，也可以通过PRIMARY KEY(属性名)完成指定。

主键由多个列构成，则这些列的组合值必须唯一。声明主键时只能通过PRIMARY KEY(属性名1, 属性名2，...)完成指定。

2 更新表

更新原表结构、评注和表的引擎类型等。

或重建触发器、存储过程、索引和外键等

语法：

ALTER TABLE tbl_name
子句

（1）ADD [COLUMN]子句

表中添加新的列。

例如：

ALTER TABLE mysql_test.customers
ADD COLUMN cust_city CHAR(10) NOT NULL DEFAULT "SHANGHAI" AFTER cust_sex;

AFTER cust_sex：添加到cust_sex列之后；

FIRST：添加到表的第一列；

没有指定位置时默认会添加到整个表的最后一列。

ADDPRIMARY KEY：添加主键

ADD FOREIGN KEY：添加外键

ADD INDEX：添加索引

（2）CHANGE [COLUMN]子句

修改列名、数据类型或者默认值等；可同时使用多个CHANGE子句，它们之间用逗号隔开。

修改原列的数据类型可能会使原列数据丢失；若改变后的数据类型与原列数据类型不兼容，SQL命令不会被执行且会报错；兼容情况下，数据可能会被截断。

（3）ALTER [COLUMN]子句

修改或删除表中指定列的默认值。

例如：

ALTER TABLE mysql_test.customers
ALTER COLUMN cust_city SET DEFAULT "BeiJing";

（4）MODIFG [COLUMN]子句

只会修改列的数据类型和位置二不会干涉列名。

例如：

ALTER TABLE mysql_test.customers
MODIFY COLUMN cust_name CHAR(20) FIRST;

（5）DROP [COLUMN]子句

删除列及其内容。

ALTER TABLE mysql_test.customers
DROP COLUMN cust_contact;

DROP PRIMARY KEY子句

DROP FOREIGN KEY子句

DROP INDEX子句

（6）RENAME [TO]子句

为表重新赋予表明。

ALTER TABLE mysql_test.customers
RENAME TO mysql_test.customers_new

3 重命名表

语法：

RENAME TABLE tbl_name TO new_name
[,tbl_name2 TO new_name2]...

4 删除表

语法：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name[,tbl_name2]...
[RESTRICT | CASCADE]

若选择RESTRICT，该表的删除是有限制条件的。该表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束），不能有触发器，不能有视图，不能有函数和存储过程等。如果该表存在这些依赖的对象，此表不能删除。

若选择CASCADE，该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象将会被一起删除。

默认是RESTRICT。

5 查看表

（1）显示表名称

SHOW [FULL] TABLE [{FROM | IN} db_name]
[LIKE '表达式' | WHERE 表达式]

（2）显示表结构

SHOW [FULL] COLUMN {FROM | IN} tbl_name
[{FROM | IN} db_name]
[LIKE '表达式' | WHERE 表达式]

或者

{DESCRIBE | DESC} tbl_name [colname | wild]

DESCRIBE和DESC是SHOW COLUMN FROM的快捷方式。

wild：通配符

三索引定义

索引就是DBMS根据表中的一列或者若干列按照一定顺序建立的列值与记录行之间的对应关系表。索引本质上是一张描述索引列的列值与原表中记录行站之间的一一对应的有序表。

在列上创建索引后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快速地查找到数据。

如若没有索引，DBMS则会通过表扫描的方式逐个地读取指定表中的数据记录进行查询。

索引时提高数据文件访问效率的有效办法。但是过多地使用索引也会增加系统开销。

1）索引是以文件形式存储的，DBMS会将一个表的所有索引保存在同一个索引文件中，索引文件需要占用磁盘空间。如果由大量索引则索引文件可能会必数据文件更快地达到最大文件尺寸。

特别是若在一个大表上创建多种组合索引，索引文件会膨胀的非常快。

2）索引在提高查询速度的同时，却也会降低更新表的速度。

因此，若DB中存在大量的表，则应认真研究建立更加优秀的索引或优化查询语句。

索引可分为以下几类：

（1）普通索引（INDEX）

它没有任何限制；创建普通索引的关键字是INDEX或者KEY。

（2）唯一索引（UNIQUE）

索引列中的所有值都只能出现一次；关键字UNIQUE。

（3）主键索引（PRIMARY KEY）

主键是一种唯一索引；创建主键时必须指定关键字PROMARY KRY，且不允许空值。

单列索引：一个索引只含原表中的一个列。

组合索引：一个索引包含原表中的多个列；又叫复合索引或多列索引。

1 索引创建

（1）使用CREATE INDEX语句

CREATE [UNIQUE] INDEX index_name
ON tbl_table(col_name[(length)] [ASC|DESC],...)

通常，将在查询语句中出现在WHERE和JOIN里的列作为索引列。

可选项length用来指定使用列的前length个字符来创建索引。

ASC和DESC用于指定索引按照ASC升序还是DESC降序来排列。

例如：

CREATE INDEX index_customers
ON mysql_test.customers(cust_name(3) ASC);

或者：

CREATE INDEX index_cust
ON mysql_test.customers(cust_name,cust_id);

查询创建结果：

SHOW INDEX FROM mysql_test.customers;

（2）使用CREATE TABLE语句

与创建表时一同创建。

①主键默认就是索引；单列主键索引可以直接在列级约束中指定 PRIMARY KEY关键字就行，也可以在字段最后单独标明PRIMARY KEY(col1)；多列构成的主键索引则必须在字段最后单独标明PRIMARY KEY(col1, col2, ...)。

②INDEX 和 KEY是同义词。

例如：

CREATE TABLE users (
  id INT PRIMARY KEY,   #单列主键索引
  name VARCHAR(50) UNIQUE,    #唯一索引
  age INT,
  INDEX name_age_index (name, age)   #使用INDEX创建的一个复合索引；可用KEY代替
);

（3）使用ALTER TABLE语句

例如：

ALTER TABLE mysql_test.seller
ADD INDEX index_001(seller_name);

2 索引的查看

语法：

SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]

3 索引删除

（1）DROP INDEX

语法：

DROP INDEX index_name ON tbl_name;

（2）使用ALTER TABLE

语法：

ALTER TABLE dbname.tbl_name
{DROP PRIMARY KEY子句 | DROP INDEX子句 | DROP FOREIGN KEY子句}

第四节数据更新

①向表中添加数据：INSERT

②删除表中数据：DELETE

③修改表中数据：UPDATE

一插入数据

①INSERT ... VALUES语句

②INSERT ... SET语句

③INSERT ... SELECT语句

1 使用INSERT ... VALUES语句

语法：

INSERT [INTO] tbl_name[(col1, col2, ...)]
{VALUE | VALUES} ({expr1 | DEFAULT},{expr1 | DEFAULT}),(...),...

expr：表示一个常量、变量或者表达式，也可以是NULL。

若值为字符型则需要使用单引号括起来。

"DEFAULT"：用于指定插入的值是该列的默认值。

2 使用INSERT ... SET语句

语法：

INSERT [INTO] tbl_name
SET col_name = {expr | DEFAULT}, ...;

例如：

INSERT INTO mysql_test.customers
SET cust_name = '李四', cust_address='SHANGHAI', cust_sex=DEFAULT;

3 使用INSERT ... SELECT语句

语法：

INSERT [INTO] tbl_name[(col1,col2,...)]
SELECT ...;

SELECT子句用于快速地从一个或多个表中取出数据，并将这些数据作为值插入到另一个表中。注意，查询的数据列数和对应得数据类型必须与将要插入的列保持一致。

二删除数据

使用DELETE语句删除表中的一行或者多行。

语法：

DELETE FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

例如：

DELETE FROM mysql_test.customers
WHERE cust_name = '李四';

三修改数据

使用UPDATE语句。

语法：

UPDATE tbl_name
SET col1 = {expr | DEFAULT}[,col2 = {expr | DEFAULT}]...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

例如：

UPDATE mysql_test.customers
SET cust_address = 'BeiJing'
WHERE cust_name = '张三';

第五节数据查询

是数据库的核心功能，使用最多。

一 SELECT语句

语法：

SELECT
	[ALL | DISTINCT | DISTINCTROW]
	select_expr[, select_expr,...]
	FROM tbl_name
	[WHERE where_condition]
	[GROUP BY {col_name | expr | position}
	 {ASC | DESC},... [WITH ROLLUP]]
	[HAVING where_condition]
	[ORDER BY {col_name | expr | position} [ASC | DESC],...]
	[LIMT {[offset,] row_count | row_count OFFSET offset}]

关键字含义：

子句/关键字	说明	是否必须
SELECT	要返回的列或表达式	是
FORM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组	仅在需要按组计算聚合时使用
HAVING	组级过滤	否
ORDER BY	排序	否
LIMIT	要检索的行数	否
ALL	默认值；返回结果包括重复值	否
DISTINCT/DISTINCTROW	同义词，返回结果不包括重复值	否

二列的选择与指定

1 选择指定列

例如：

SELECT cust_name,cust_sex,cust_age
FROM mysql_test.customers;

或者

SELECT * FROM mysql_test.customers;

2 定义并使用列的别名

语法：

col_name AS col_alias

例如：

SELECT cust_name,cut_address AS '地址'
FROM mysql_test.customers;

3 替换查询结果集中的对象

语法：

SELECT col_name
	CASE
	WHEN condition1 THEN expr1
	.
	.
	.
	ELSE expr2
	END [AS col_alias]
FROM tbl_name;

例如：

SELECT cust_name
	CASE
	WHEN cust_sex='M' THEN '男'
	ELSE '女'
	END AS '性别'
FROM mysql_test.customers;

4 计算列值

例如：

SELECT cust_name,cust_age + 10
FROM mysql_test.customers;

5 聚合函数

使用GROUP BY子句。

三 FROM子句与多表连接查询

SELECT语句的查询对象是由FROM子句指定的对象；可实现单表或多表查询。

1 交叉连接（CROSS JOIN）

又称笛卡尔积；关键字"CROSS JOIN"。

语法：

SELECT * FROM tbl1 CROSS JOIN tbl2;

返回的表的个数是两张表行数的乘积；因此应避免对大量行的表使用交叉查询。

语法上可以简化为：

SELECT * FROM tbl1,tbl2;

2 内连接（INNER JOIN）

最常使用的连接类型；原理是在查询中设置连接条件。

语法：

SELECT col1[,col2...]
FROM tbl1
INNER JOIN tbl2
ON condition_xxx;

例如：在学生基础信息表和选课成绩表中使用内连接查询每个学生的成绩。

SELECT *
FROM tbl_students
INNER JOIN tbl_score
ON tbl_students.sid = tbl_score.sid;

INNER 关键字可以省略，因为内连接是默认连接。

内连接的三种情况：

①等值连接"="；一个主键，一个外键。

②非等值连接：除"="之外

③自连接：两个表是同一张表，用于用户查询表中具有相同列值的行；比如查询"出生地址"和"工作地址"相同的记录。

3 外连接（LEFT JOIN/RIGHT JOIN）

外连接又分为：

①左外连接（LEFT [OUTER] JOIN）和②右外连接（RIGHT [OUTER] JOIN）。

外连接将连接的两张表区别为基表和参考表。

左外连接：以左表为基表，右表为参考表；在交叉连接内，包括内连接及左表中没得到匹配的所有记录。

右外连接：以右表为基表，左表为参考表；在在交叉连接内，包括内连接及右表中没得到匹配的所有记录。

例如1：

SELECT *
FROM tbl_students
LEFT JOIN tbl_score
ON tbl_students.sid = tbl_score.sid;

tbl_students是基表；tbl_score是参考表；在返回的结果中包含满足连接条件的行外还含有tbl_students表中存在但是在tbl_score没有对应sid匹配的行。

简单来说就是左表先抄下来；再在按条件补齐满足条件的右表信息。

例如2：

SELECT *
FROM tbl_students
RIGHT JOIN tbl_score
ON tbl_students.sid = tbl_score.sid;

tbl_students是参考表；tbl_score是基表；在返回的结果中包含满足连接条件的行外还含有tbl_score表中存在但是在tbl_students没有对应sid匹配的行。

简单来说就是右表先抄下来；再在按条件补齐满足条件的左表信息。

四 WHERE子句与条件查询

指定过滤条件。

1 比较运算

比较运算符有：

比较运算符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
<=>	不等于（不返回UNKNOWN）

当被比较的两个操作数其中一个为空时：<=>返回FALSE，=返回UNKNOWN。

当被比较的两个操作数两个都为空时；<=>返回TRUE，=返回UNKNOWN。

2 判定范围

（1）BETWEEN ... AND ...

例如：

SELECT * FROM tbl1
WHERE col1 BETWEEN 10 AND 20;

（2）IN

例如：

SELECT * FROM tbl1
WHERE col1 IN(10, 20, 30)

3 判断空值

例子：

SELECT * FROM tbl1
WHERE col1 IS NULL;

4 子查询

（1）结合IN

例如：

SELECT * FROM tbl1
WHERE col1 IN(
	SELECT col_a FROM tbl2 WHERE id>100
);

（2）结合比较运算符

WHERE col_name {= | <> | != | < | <= | > | >= | <=>} {ALL | SOME | ANY}(sub_query);

ALL：子查询中的每个值都满足条件时则结果为TRUE，否则结果为FALSE。

SOME/ANY：同义词，子查询中任一满足条件结果则为TRUE，否则结果为FALSE。

（3）结合EXISTS

WHERE EXISTS(sub_query);

子查询的结果不为空则代表TRUE，否则代表FALSE。

五 GROUP BY子句与分组数据

将结果集中的数据行根据一个或多个选择列的值进行逻辑分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具，尤其在处理大量数据时，它能够提供有用的汇总信息。

语法：

GROUP BY {col_name | expr | position} [ASC | DESC] [,...] [WITH ROLLUP]

①col_name：被选中用来分组的列，可以多列，多列用逗号分开。

expr和position是col_name的替代方式，被指是一样的。

②WITH ROLLUP：可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如1：

SELECT score_sname, SUM(score_sc_value) AS '总分'
FROM mysql_test.scores
GROUP BY scores.score_sc_value;

score_sc_value：单科成绩字段。

例如2：

SELECT score_sname, SUM(score_sc_value) AS '总分'
FROM mysql_test.scores
GROUP BY scores.score_sc_value WITH ROLLUP;

这个例子的WITH ROLLUP在按照score_sc_value分组统计的每个人的总分在计算全员SUM()。

score_sname列最后会显示null；

可以用：

SELECT coalesce(score_sname,'全部总分'), SUM(score_sc_value) AS '总分' FROM mysql_test.scores GROUP BY scores.score_sc_value WITH ROLLUP;

将null替换为"全部总分"。

六 HAVING子句

过滤分组，在结果集中规定包含哪些分组和排除哪些分组。

HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。

请注意，HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。

语法：

HAVING where_condition

where_condition：过滤条件。

示例：

SELECT 
    ordernumber,  #订单号
    SUM(quantityOrdered) AS itemsCount,   #订单中零件数量
    SUM(priceeach*quantityOrdered) AS total   #订单的总价（每一零件的数量和单价成绩再累加）
FROM
    orderdetails   #表
GROUP BY ordernumber  #按订单号分组
HAVING total > 55000;  #只显示那些总价大于55000的订单

HAVING与WHERE的区别：

①WHERE过滤行，HAVING过滤分组。

②HAVING条件可以包含和函数，WHERE不行。

③WHERE在分组前过滤，HAVING在分组后过滤。

七 ORDER BY子句

MySQL 表中使用 SELECT 语句来读取数据。

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序（ASC）或降序（DESC）排序。

否则，结果集中数据行的顺序是不可预知的。

语法：

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

可以选择多列；优先级是前面的字段优先。

被选则用来排序的字段也可以使用SELECT中指定的别名。

八 LIMIT子句

限制SELECT结果集返回的数据行数。

语法：

LIMIT {[offset,] row_count | row_count OFFSET offset}

①[offset,]：是可选项，默认值是数字0，用于指定第一行数据在SELECT语句结果集中的偏移量；必须是非负整数常量。

上一篇：深入分析 OpenJDK G1 FullGC 原理

下一篇： 3.4 数据库系统入门 - 数据查询 - SELECT（单表查询、连接查询、嵌套查询、汇总查询、多表查询）