5.4.4 赋值运算符

        MySQL, 中“=”是一个比较特殊的运算符,既可以用于比较数据是否相等,又可以表示赋值。因此,MySQL,为了避免系统分不清楚运算符“=”表示赋值还是比较的含义,特意增加一个符号“:=”用于表示赋值运算。
例如,査看 sh goods 表中评分为 4.5的商品 id、name 和 stock,具体 SL语句及执行结果如下。

        在上述 SQL语句中,WHERE后的运算符“=”用于比较 sh goods 表中 score 字段的值是否与 4.5相等,若相等,则返回查询的记录。
        接下来,在促销活动时,将评分等于 4.5 的商品库存修改为 1000。具体 SQL, 语句。
UPDATE sh goods SET stock=1000 WHERE score =4.5,

        在上述 UPDATE 语句中,“stock=1000”中的运算符“=”表示赋值的含义,将符合WHERE 比较条件的记录对应的 stock 字段值设置为 1000。因此,“stock=1000”中的“=”可使用 MySQL, 提供的专门用于赋值的运算符“:=”代替,修改后 SOL语句如下
UPDAIE sh goods sET stock : 1000 WHERE score =4.5,
        值得一提的是,在 MySQL中,INSERT…SET和 UPDATE…SET 中出现的运算符“="都会被认为是赋值运算符。因此,建议除此之外的其他情况,若需要赋值运算符,推荐使用“:=”,如为变量赋值,后面的章节会详细地讲解,此处读者了解即可

5.4.5 位运算符

        位运算符是针对二进制数的每一位进行运算的符号,运算的结果类型为 BIGINT,最大范围可以是 64 位。具体如表 5-8 所示。

        需要注意的是,MySQL 5.7中参与位运算的数据只能是 BIGINT 类型(64 位的整数),而在 MySQL 8.0中则允许二进制字符串类型的参数,如 BINARY、VARBINARY 和BLOB。因此,MySQL5.7中二进制类型字段的位运算可能在 MySQL8.0中产生不同结果,系统也会报相关的警告信息。下面以一个案例演示 VARBINARY类型的数据位运算。

从上述操作可知,在对 VARBINARY 类型的字段进行位运算时,产生了两个警告行，使用 SHOW WARNINGS\G 查看,具体信息如下.

为了解决这个问题,可以在进行位运算时,使用 CAST(… AS UNSIGNED)将二进制类型的数据显式地转换为 BIGINT 类型。

        在上述 SQL,语句中,将参与位运算的字段转换为整型后,运算的结果不再有警告信息为方便查看与显示,使用 AS为位运算表达式设置了别名,分别是 one 和 two。关于位运算,除了上面讲解的位运算符外,MySQL,中还提供了进行位运算的函数,常用的如表 5-9 所示。

5.4.6 运算符优先级

        运算符优先级可以理解为运算符在一个表达式中参与运算的先后顺序,优先级别越高则越早参与运算:优先级别越低,则越晚参与运算。下面看一下 MySQL,中所有运算符从高到低的优先级,如表 5-10 所示。

        在表 5-10 中,同行的运算符具有相同的优先级,除赋值运算符从右到左运算外,其余相同级别的运算符,在同一个表达式中出现时,运算的顺序为从左到右依次进行。除此之外,若要提升运算符的优先级别,可以使用圆括号“()”,当表达式中同时出现多个圆括号时,最内层的圆括号中的表达式优先级最高。具体SQL,语句及执行结果如下

在上述 SQL,语句中,表达式“2+3*5"首先按运算符的优先级,计算乘法,然后再计算加法,因此结果为 17;而表达式“(2+3)*5”则先计算圆括号内的加法,然后再计算乘法,因此结果为 25。
在实际开发中,为复杂的表达式适当地添加圆括号,让编写的 SQL,语句更为清楚,避免因不清楚运算符优先级顺序而导致运算发生问题。

5.5 动手实践:商品评论表的操作

        数据库的学习在于多看、多学、多想、多动手,只有将理论与实际相结合,才能够体现出数据开发与管理的重要性,展现知识学习的价值与力量。接下来请结合本章所学的知识完成商品评论表的操作。
【实践目标】
此实践的目标就是能够根据文字提示,完成商品表(sh_goods)与商品评论表(sh_goodscomment)各种需求的查询操作
实践需求】
(1)查询商品id等于8且有效的评论内容,
(2)查询每个用户评论的商品数量。
(3)查询最新发布的5条有效商品评论信息。
(4)查询评论过两种以上不同商品的用户 id 及对应的商品 id。
(5)结合 sh_goods 和 sh_goods_comment 表,查询没有任何评论信息的商品 id 和
name。

(6)结合 sh_goods 和 sh_goods_comment 表,查询商品评分为5 星的商品评论信息。
【动手实践】(1)查询商品id等于8且有效的评论内容,利用比较运算符“=”和逻辑运算符“&&”完成指定需求记录的査询,具体 SQL语句及执行结果如下
my3gl>SELECT id,content EROM sh goods comment->WHERE goods id 8 && is show 1;

(2)查询每个用户评论的商品数量。
由于一个用户可以评论多件商品,因此,若要查询每个用户评论的商品数量,首先需要根据用户 id进行分组,然后再利用聚合函数 COUNTO)获取每个分组下商品的数量。具体SQL语句及执行结果如下

从上述执行结果可以看出,只有 user_id等于4的用户评论过3件商品,其他用户都只评论过1件商品。
(3)查询最新发布的5条有效商品评论信息。
根据文字描述可知,查询的评论信息,首先是有效的,然后是最新发布的5条记录。具体 SQL 语句及执行结果如下。

(4)查询评论过两种以上不同商品的用户id 及对应的商品 id。
根据文字提示可知,首先需要根据用户id 进行分组,然后再利用 HAVING 执行查询的需求,限定评论的商品数量必须大于等于2,最后获取用户id 和该用户评价过的商品 id。具体 SQL 语句及执行结果如下。

(5)结合 sh_goods 和 sh_goods_comment 表,查询没有任何评论信息的商品 id 和
na11ea根据文字提示可知,首先从 sh_goods 表中查询 id 和 name,然后使用 WHERE 指定查询的需求,利用 NOT IN()判断 id 是否在 sh_goods_comment 表查询出的商品 id 内。具体SQL 语句及执行结果如下。

(6)结合 sh_goods 和 sh_goods_comment 表,查询商品评分为5星的商品评论信息从 sh_goods_comment 表中获取评论信息,然后使用 WHERE 指定查询的条件,利用IN()判断 goods id 是否在 sh goods 表中査询出的5 星商品 id 内。具体 SQL 语句及执行结果如下。

5.6 本章小结

        本章主要讲解了如何根据一张已有的数据表创建新表,以及数据复制的插入,数据查询、更新、删除的排序与限量,数据查询的分组以及如何通过运算符完成条件表达式的编写其中,单表的查询操作是本章需要重点掌握的内容。

第6章 多表操作

学习目标
掌握多表之间的内连接，左外连接以及右外连接查询。掌握子查询的分类以及带关键字的子查询
熟患外键约来的添加，副除以及关联表之间的操作
前面章节所涉及的都是针对一张表的操作,即单表操作。然而实际开发中业务逻辑较为复杂,通常都需要对两张以上的表进行操作,即多表操作。本章将针对多表操作进行详细讲解。

6.1 多表查询

6.1.1 联合查询

        联合查询是多表查询的一种方式,在保证多个SELECT语句的查询字段数相同的情况下,合并多个查询的结果。联合查询经常应用在分表操作中,具体会在后面的章节中详细讲解,此处读者只需掌握联合查询的作用及语法即可。联合查询的基本语法格式如下，
SELECT...
UNIOU IALL,I DISTIMCT] SELECT...
UNION(ALL I DISTIMT] SELECT... ],

在上述语法中,UNION 是实现联合查询的关键字,ALI,和 DISTINCT 是联合查询的选项。其中,ALL表示保留所有的查询结果;DISTINCT 是默认值,可以省略,表示去除完全重复的记录。
例如,在 shop.sh_goods 表中,以联合査询的方式获取 category_id为9的商品 id、name和 price,以及 category id 为6 的商品 id、name 和 keyword。具体 SQL语句如下

        在上述 SQL,语句中,SELECT 查询的字段个数必须相同,且联合查询的结果中只保留第一个 SELECT 语句对应的字段名称,即使 UNION 后 SELECT 查询的字段与第一个SELECT 查询的字段表达含义或数据类型不同,MySQL,也仅会根据查询字段出现的顺序对结果进行合并。

        例如,category_id为9的price 字段和 category_id为6的keyword 字段在合并时只保留了 price 字段的名称,而 keyword 字段的值依然合并到了 price 字段下。除此之外,若要对联合查询的记录进行排序等操作,需要使用圆括号“()”包裹每一个SELECT语句,在 SELECT语句内或在联合查询的最后添加 ORDER BY语句。并且若要排序生效,必须在 ORDER BY后添加 LIMIT 限定联合查询排序的数量,通常推荐使用大于表记录数的任意值。
        例如,以联合查询的方式,对 sh_goods表中 category_id为3的商品按价格升序排序其他类型的产品按价格降序排序,查询的商品信息为 id、name 和 price。具体 SQL,语句及执行结果如下。

        在上述 SQL,语句中,在每个联合査询的 SELECT 语句中添加 ORDER BY 和 LIMIT让每个查询语句按照指定的方式进行升序或降序排序。从执行结果中可知,前7条记录按照价格降序排序,后3条记录按价格升序排序。

6.1.2 连接查询

        在实际应用中,可以根据多表之间存在的关联关系,将多张数据表连到一起。MySQL中常用的连接査询有交叉连接、内连接、左外连接和右外连接。接下来将针对不同的连接查询进行详细讲解。

1.交叉连接
        交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积。例如,商品分类表中有3个字段、4 条记录,商品表中有5个字段、10 条商品信息,那么交叉连接后的笛卡儿积就等于 4*10 条记录数,每条记录中含有 3+5个字段。在 MySQL, 中,交叉连接的基本语法格式如下。
SELECT查询字段 EROM表1CROSS JOIN表2;
        上述语法格式中,CROSSJOIN用于连接两个要查询的表,通过该语句可以查询两个表中所有的数据组合。
        为了方便读者理解,下面以交叉连接商品分类表 sh_goods_category 和商品表 shgoods 为例进行演示。具体 SQL语句如下。

在上述 SQL, 语句中,使用 sh_goods_category 表中的每一条记录与 sh_goods 表中的记录进行连接,因此最后查询出的记录数为 160(sh_goods_category 的记录数 16 乘以 sh
goods 表中的记录数 10)。值得一提的是,以上的交叉连接査询与 MySQL,中多表查询的语法等价。例如,上述示例可以使用以下 SQL 语句实现。

SELECT c.id,c.name,g.id,g.name
FROM sh goods category AS c,sh goods As g:
需要注意的是,交叉连接产生的结果是笛卡儿积,并没有实际应用的意义
2.内连接
内连接是一种常见的连接查询,它根据匹配条件返回第1个表与第2个表所有匹配成功的记录。在 MySQL中,内连接的基本语法格式如下。
SELECT 查询字段 FROM 表 1
[INMER]JOIN表2匹配条件/
        在上述语法中,ON用于指定内连接的查询条件,在不设置ON时,与交叉连接等价此时可以使用 WHERE 完成条件的限定,效果与 ON一样。但由于 WHERE 是限定已全部查询出来的记录,那么在数据量很大的情况下,此操作会浪费很多性能,所以此外推荐体用 ON 实现内连接的条件匹配。
        为了方便读者理解,下面以内连接的方式查询商品表 sh_goods 和商品分类表 shgoods_category 表中对应商品的分类 id 及 name。具体 SQL,语句及执行结果如下

        从上述执行结果可知,只有 sh_goods 表的 category_id与sh_goods_category 表中的 id相等的商品信息(gid 和 gname)和分类信息(cid 和 cname)才会被显示。另外,在查询数据时,由于 sh_goods 和 sh_goods_category 表中含有同名的字段,为了避免重名出现错误,使用“数据表.字段名”或“表别名,字段名”的方式进行区分。
        除此之外,自连接查询是内连接中的一种特殊查询。它是指相互连接的表在物理上为同一个表,但逻辑上分为两个表。例如,要查询“钢笔”所在的分类下有哪些商品,就可以使用自连接查询。具体 SQL语句及执行结果如下。

        在上述 SQL, 语句中,别名为 g1 和 g2的表在物理上是同一个数据表 sh_goods,然后在ON 的匹配条件中,指定 g1 表与 g2 表内商品名为“钢笔”且它们必须是同分类的记录进行自连接,从而获取 sh_goods 表中“钢笔”分类下的所有商品。
        小提示:在标准的 SQL中,交叉连接(CROSS JOIN)与内连接(INNER JOIN)表示的含义不同,前者一般只连接表的笛卡儿积,而后者则是获取符合 ON筛选条件的连接数据。但是在 MySQL中,CROSS JOIN与INNER JOIN(或JOIN)语法的功能相同,都可以使用 ON设置连接的筛选条件,可以互换使用,但是此处不推荐读者将交叉连接与内连接混用
3.左外连接
        左外连接是外连接查询中的一种,也可以将其称为左连接。它用于返回连接关键字(LEFTJOIN)左表中所有的记录,以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配的记录时,右表中相关的记录将设为NULL。其基本语法格式如下。
SELECT 查询字段
FROM表1LEET [OUTER]JOIN表2ON匹配条件
        在上述语法中,关键字 LEFT「OUTER JOIN 左边的表(表 1)被称为左表,也可称为主表:关键字右边的表(表 2)被称为右表,也可称为从表。其中,OUTER 在查询时可以省略。
        下面利用左连接査询,以 sh_goods 表为主表,sh_goods_category 表为从表,查询所有 5星商品对应的分类名称,具体 SQL 语句及执行结果如下。

        从上述执行结果可知,左连接查询,即使主表 sh_goods 中的记录与从表 sh_goodscategory 中任何记录都不符合匹配条件时,也会在查询结果中保留主表 sh_goods 中的此条记录(如 gid 等于1的记录),而从表 sh_goods_category 对应的字段值则为 NULL(如 cid、cname 字段)。
4.右外连接
        右外连接也是外连接查询中的一种,可以将其称为右连接。它用于返回连接关键字(RIGHT JOIN)右表(主表)中所有的记录,以及左表(从表)中符合连接条件的记录。当右表的某行记录在左表中没有匹配的记录时,左表中相关的记录将设为NULL。其基本语法格式如下。
SELECT 查询字段
FROM表1RIGHT[OUIER]JOIM表2ON配条件;
        下面利用右连接査询,以 sh_goods_category 为主表,sh_goods 为从表,查询所有5星评价商品的对应分类名称,具体 SQL语句及执行结果如下

        从上述执行结果可知,右连接查询的结果保存了主表 sh_goods_category 中的所有记录。其中,主表 sh_goods_category 中与从表 sh_goods 没有符合匹配条件的记录,从表 shgoods 对应的字段 gid 和 gname 值为 NULL。
        总之,外连接是最常用的一种查询数据的方式,分为左外连接(LEFT JOIN)和右外连接(RIGHT JOIN)。它与内连接的区别是,内连接只能获取符合连接条件的记录,而外连接不仅可以获取符合连接条件的记录,还可以保留主表与从表不能匹配的记录。
        另外,右连接查询正好与左连接相反。因此,在应用外连接时仅调整关键字(1EFT或RIGHT JOIN)和主从表的位置,即可实现左连接和右连接的互换使用。
多学一招:USING 关键字
        在连接查询时,若数据表连接的字段同名,则连接时的匹配条件可以使用 USING 代替ON。其基本语法格式如下。
SELECT 查询字段
EROM1[CROSSIIMNERIIEETIRIGHT]JOIN2
USIG(同名的连接字段列表)
        上述语法中,多个同名的连接字段之间使用逗号分隔。下面使用 USING 关键字实现自连接查询“钢笔”所在的分类下有哪些商品。具体SQL语句及执行结果如下。

需要注意的是,USING 关键字在实际开发中并不常使用,原因在于设计表的时候不能确定使用相同的字段名称保存对应的数据。