MySQL入门之查询操作

单表查询

SELECT [DISTINCT] *|<字段名1,字段名2,字段名3,...>
	FROM <表名>
	[WHERE <条件查询表达式1>]
	[GROUP BY <字段名> [HAVING <条件表达式2>]]
	[ORDER BY <字段名> [ASC|DESC]]
	[LIMIT [OFFSET] <记录数>]

• DISTINCT：对查询的字段数据进行去重
• *：表示全字段
• WHERE：指定查询条件
• GROUP BY：用于将查询结果按照指定字段进行分组
• HAVING：用于对分组后的结果进行过滤
• ORDER BY：用于将查询结果按照指定字段进行排序，默认为ASC升序
• LIMIT：用于限制查询结果的数量，其中的OFFSET是数字，表示偏移量-即从哪条查询结果开始，默认值为0-即第一条查询结果

条件查询

带关系运算符的查询

关系运算符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于

带IN 关键字的查询

​ IN 关键字用于判断某个字段是否在指定的集合中，如果字段的值在集合中，则满足条件，该字段所在的记录将被查询出来。

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	WHERE <字段名> [NOT] IN (元素1,元素2,...);

NOT IN 则表示不存在

带BETWEEN AND 关键字的查询

​ BETWEEN AND 用于判断某个字段的值是否在指定的范围之内（包含两边临界值），如果在，则满足条件，该字段所在的记录将被查询出来，反之则不会被查询出来。

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	WHERE <字段名> [NOT] BETWEEN <值1> AND <值2>;

同理，加了NOT 则表示不在这个范围

空值查询

在数据表中，某些列的值可能为空值（NULL），空值不同于0，也不同于空字符串。在MySQL中，使用IS NULL 关键字来判断字段的值是否为空值。

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	WHERE <字段名> IS [NOT] NULL;

同理，使用NOT 关键字用来判断字段不是空值

另一种写法：

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	WHERE [NOT] <字段名> IS NULL;

带LIKE 关键字的查询

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	WHERE <字段名> [NOT] LIKE '匹配字符串';

NOT 的作用同上，其中匹配字符串分为 % 和 _

1. 百分号(%)通配符

匹配任意长度的字符串，包括空字符串

SELECT * FROM student WHERE name LIKE "s%"

表示查找以 s 开头的姓名数据

2. 下划线(_)通配符

只匹配单个字符，如果要匹配多个字符，需要多个下划线通配符

SELECT * FROM student WHERE name LIKE "_s%"

表示查找第二个字符为 s 的姓名数据

如果上面的操作想匹配字符串中的百分号和下划线，就需要在通配符字符串中使用右斜线（“\”）对百分号和下划线进行转义，例如："%"

带AND 关键字的多条件查询

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	WHERE <条件表达式1> AND <条件表达式2>;

AND 表示并且的关系，其中的AND 也可以用 && 表示

带OR 关键字的多条件查询

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	WHERE <条件表达式1> OR <条件表达式2>;

OR 表示或的关系

注意：AND 的优先级比OR 高，其两者搭配用的判断逻辑与数学上的与、或逻辑一样

高级查询

聚合函数

函数名称	作用
COUNT()	返回某列的行数
SUM()	返回某列值的和
AVG()	返回某列的平均值
MAX()	返回某列的最大值
MIN()	返回某列的最小值

函数用于对一组值进行统计，并返回唯一值，这些函数被称为聚合函数。

切忌：聚合函数是不能直接与WHERE 关键字进行条件判断的

关于COUNT(字段)和COUNT(*)的区别：COUNT(字段)会忽略NULL 值，而COUNT( * )不会

具体参考：count(1)、count(*)、count（字段）的区别

对查询结果排序

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	ORDER BY <字段名1> [ASC | DESC] [,<字段名2> [ASC | DESC],...];

值得注意的是：NULL值是被认为最小的值。

分组查询

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	GROUP BY <字段名1>[,<字段名2>,...] [HAVING 条件表达式];

1. 单独使用

略

2. 跟聚合函数一起使用

SELECT COUNT(*),gender FROM student GROUP BY gender;

统计不同性别下的人数

其实弄懂MySQL中的SQL语句的执行顺序就都懂了

3. GROUP BY 和 HAVING 关键字一起使用

SELECT SUM(grade),gender FROM student GROUP BY gender HAVING SUM(grade)<300;

这里要注意HAVING 是可以跟聚合函数的，而WHERE不能，并且HAVING 也只能用于分组后

用LIMIT限制查询结果数量

SELECT *|<字段名1,字段名2,...>
	FROM <表名>
	LIMIT [OFFSET,] <记录数>;

要注意：OFFSET 为偏移量，默认值为0。表示从第OFFSET条数据往后显示<记录数>条数据

多表操作

外键

通俗点，外键就是一张表的字段引入了另一张表的主键。

为表添加外键约束

要想真正连接两个表的数据，就需要为表添加外键约束。

ALTER TABLE <表名> ADD CONSTRAINT <外键名> FOREIGN KEY (外键字段名) REFERENCES <主表表名> (主键字段名);

• 表名表示你要为哪张表创建外键约束——暂且定义为从表
• 外键名表示创建外键约束后的外键叫什么名字，一般命名为FK_ID
• 外键字段名表示要以表名中的哪个字段名作为外键约束
• 主表名表示要关联哪张表
• 主键字段名表示主表的主键

注意点：建立外键的表必须是 InnoDB 类型，不能是临时表

​ 定义外键名时，不能加引号，如CONSTRAINT 'FK_ID' 或 CONSTRAINT "FK_ID" 都是错的

# 建表时创建外键
CREATE TABLE student(
	sid INT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    sname VARCHAR(20) DEFAULT NULL,
    gid INT(11) NOT NULL,
    CONSTRAINT `FK_ID` FOREIGN KEY (gid) REFERENCES `grade` (id)
);

注意：在创表添加外键时，外键名时可以用反引号括起来的

删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键名>;

操作关联表

当创建外键之后，表和表的关系一般有多对多、多对一、一对一

添加数据

注意点：

• 当表创建了外键关系，在给从表的外键字段插入数据时，必须要对应主表中已有的数据

删除数据

删除数据要遵循先删从表的数据，再删主表的数据

如果从表有跟主表对应的数据关系，那么在删除主表数据时会报错，必须先删除从表中的数据，或修改主表数据，使对应关系不存在

连接查询

交叉连接

SELECT * FROM <表1> CROSS JOIN <表2>;

使用CROSS JOIN 查询，可以查询出两张表之间的所有组合。等于笛卡尔积

内连接

内连接使用比较运算符对两个表中的数据进行比较，并列出与连接条件匹配的数据行，组成新的记录。

SELECT <查询字段> FROM <表1> [INNER] JOIN <表2> ON <表1.关系字段> = <表2.关系字段>;

INNER JOIN 用来连接两张表，ON用来指定连接条件

外连接

1. LEFT JOIN(左外连接)

返回包括左表中的所有记录和右表中符合连接条件的记录

SELECT <所查字段> FROM <表1—左表> LEFT [OUTER] JOIN <表2-右表> ON <表1.关系字段> = <表2.关系字段>;

2. RIGHT JOIN(右连接)

返回包括右表中的所有记录和左表中符合连接谈条件的记录

SELECT <所查字段> FROM <表1—左表> RIGHT [OUTER] JOIN <表2-右表> ON <表1.关系字段> = <表2.关系字段>;

全连接

原文

通过UNION 实现将多条SELECT 的查询结果进行连接

[sql1]
UNION [ALL | DISTINCT]
[sql2]
UNION [ALL | DISTINCT]
[sql3]
....

语法格式说明

• sql1、sql2、sql3：平时写的查询 sql，可以连接很多条 sql
• ALL：可选参数，返回所有结果集，包含重复数据
• distinct：可选参数，删除结果集中重复的数据（默认只写 union 也会删除重复数据，所以不加也没事）

演示数据：

mysql> select * from a;
+------+
| a_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select * from b;
+------+
| b_id |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
+------+
4 rows in set (0.00 sec)

测试

select * from a left join b on a.a_id = b.b_id
union all
select * from a right join b on a.a_id = b.b_id;
+------+------+
| a_id | b_id |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 |    3 |
|    4 |    4 |
|    3 |    3 |
|    4 |    4 |
| NULL |    5 |
| NULL |    6 |
+------+------+
8 rows in set (0.00 sec)

select * from a left join b on a.a_id = b.b_id
union
select * from a right join b on a.a_id = b.b_id;
+------+------+
| a_id | b_id |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 |    3 |
|    4 |    4 |
| NULL |    5 |
| NULL |    6 |
+------+------+
6 rows in set (0.00 sec)

关于上面的各种连接查询，都是可以接一些条件查询语句，来达到更精准的查询效果

子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询。它可以嵌套在一个SELECT、SELECT...INTO 语句、INSERT...INTO 等语句中。在执行查询语句时，首先会执行子查询中的语句，然后将返回的结果作为外层查询的过滤条件，在子查询中通常可以使用IN、EXISTS、ANY、ALL操作符

带IN 关键字的子查询

使用IN 关键字进行子查询时，内层查询语句仅返回一个数据列，这个数据列中的值将提供外层查询语句进行比较操作。

# 当a_id 的值存在 IN 列表里面时，就返回相应的查询结果
SELECT * FROM a WHERE a_id IN (SELECT b_id FROM b);
+------+
| a_id |
+------+
|    3 |
|    4 |
+------+

其中也可以用NOT IN 来表示不存在

带EXISTS 关键字的子查询

EXISTS 关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不产生任何数据，只返回TRUE 或 FALSE，当返回值为 TRUE 时，外层查询才会执行。

SELECT * FROM a WHERE EXISTS (SELECT b_id FROM b);
+------+
| a_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

带ANY 关键字的子查询

ANY 表示满足其中任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

SELECT * FROM a WHERE a_id < ANY (SELECT b_id FROM b);
+------+
| a_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

带ALL 关键字的子查询

ALL 关键字与ANY 有点类似，只不过带ALL 关键字的子查询返回的结果需要同时满足所有内层查询条件。

SELECT * FROM a WHERE a_id < ALL (SELECT b_id FROM b);
+------+
| a_id |
+------+
|    1 |
|    2 |
+------+

带比较运算符的子查询

SELECT * FROM a WHERE a_id = (SELECT b_id FROM b WHERE b_id = 3);
+------+
| a_id |
+------+
|    3 |
+------+

用比较运算符的话，子查询的返回值就必须是一行数据