当前位置: 澳门新濠3559 > 数据库 > 正文

澳门新濠3559但实际数据库中的存储数据的编码格

时间:2019-11-07 13:43来源:数据库
1.子查询简介 数据准备 回顾 记录操作: 写操作:INSERT,UPDATE,DELETE 读取操作:SELECT 这章主要学习: 子查询 连接 多表删除 多表更新 数据准备: 简单的商城数据库 Paste_Image.png tdb_good表

1.子查询简介

数据准备


澳门新濠3559 1 澳门新濠3559 2

回顾

记录操作:
写操作:INSERT,UPDATE,DELETE
读取操作:SELECT

这章主要学习:
子查询
连接
多表删除
多表更新

数据准备:
简单的商城数据库

澳门新濠3559 3

Paste_Image.png

tdb_good表结构:

澳门新濠3559 4

Paste_Image.png

插入数据:略(见下载文件中的”子查询.txt“)

注意编码方式,插入的时候是以utf8的形式插入的,显示会乱码,此时使用SET NAMES gbk;设置客户端的编码方式(不会影响服务器)

1、创建练习使用的数据表

澳门新濠3559 5

goods_id —— 商品编号
goods_name —— 商品名称
goods_cate —— 商品分类
brand_name —— 商品品牌
goods_price —— 商品价格
is_show —— 商品是否上架,默认为在售
is_saleoff —— 商品是否售罄,默认为否

由于商品中有中文字符出现,因此需要首先设置客户端显示数据的编码为“gbk”,但实际数据库中的存储数据的编码格式仍然是之前设定的“utf8”,不会受影响。

澳门新濠3559 6

此次举例共填入了22条记录,手动输入过于繁琐,因此提供源码,点击下载使用。


其中,所谓的“外层查询”并不是指“查找”,指的是所有SQL语句的统称;结构化查询语言(Structured Query Language),简称SQL。

子查询简介

子查询:
子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。
例如:
SELECT * FROM t1 WHERE col1= (SELECT col2 FROM t2);
其中SELECT * FROM t1,称为Outer Query/Outer Statement
SELECT col2 FROM t2,称为SubQuery

子查询指嵌套在查询内部,且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件,
如DISTINCT、GROUP BY、ORDER BY、LIMIT,函数等。
子查询的外层查询可以是:SELECT,INSERT,UPDATE,DELETE,SET或DO。

子查询中的外层查询是指SQL语句的统称,而不仅仅是SELECT(SQL:结构化查询语言)

子查询返回值:子查询可以返回标量、一行、一列或子查询。

拿到结果后就可以在INSERT,UPDATE,SELECT,DELETE等其他的SQL语句中使用

2、子查询简介

子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。

例如:
SELECT*FROM t1 WHERE col1 = ( SELECT col2 FROM t2);
其中“SELECT*FROM t1”被称为Outer Query/Outer Statement,即外层查询;
而“SELECT col2 FROM t2”被称为SubQuery,即子查询。

  • 子查询指嵌套在查询内部,且必须始终出现在圆括号内;
  • 子查询可以包含多个关键字或条件,例如:DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等;
  • 子查询的外层查询可以是:SELECT、INSERT、UPDATE、SET、DO。
    注意:这里的“外层查询”并不仅仅指“SELECT”查找,而是所有的SQL命令的统称,因为SQL语言被称为是结构化查询语言,包括增、删、改、查等。
  • 子查询可以返回标量、一行、一列或子查询。

澳门新濠3559 7澳门新濠3559 8

由比较运算符引发的子查询

子查询分类:
使用比较运算符的子查询
使用比较运算符的子查询(=、>、<、>=、<=、<>、!=、<=>)
语法结构:operand comparison_operator subquery

查找平均价格
SELECT AVG(goods_price) FROM tdb_goods;
avg()聚合函数,和i有一个返回值,类似的函数还有sum(),count(),max(),min()

SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;#ROUND(AVG(goods_price),2)指的是对平均值进行四舍五入,最后保留l两位小数

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5636.36;#选择价格大于平均价格(5636.36)的商品

将上两条查询合并:
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);#使用了比较运算符,而且使用了小括号

查询超极本类型的价格:
SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本';

查询价格大于超极本价格的商品:
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本';#错误,因为子查询中返回的不是一个数据而是三条记录,WHERE中应该告诉系统大于哪个数据

用ANY、SOME或ALL修饰的比较运算符

  • operand comparison_operator ANY (subquery)
  • operand comparison_operator SOM(subquery)
  • operand comparison_operator ALL(subquery)

ANY、SOME是等价的,只要符合其中的一个就行,ALL是要符合全部

ANY、SOME、ALL关键字:

澳门新濠3559 9

Paste_Image.png

ANY演示:
`SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price> ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

ALL演示:
`SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price> ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

等于ALL演示:
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');#选择的其实就是子查询里查询的结果

3、由比较运算符引发的子查询

语法结构:
operand comparison_operator subquery
比较运算符包括:
=、>、<、>=、<=、<>、!=、<=>

查询所有商品的平均价格,还可以对查询的结果进行四舍五入,保留2位小数:

澳门新濠3559 10

  在所有商品中,查询售价大于或等于平均价格的商品,显示其编号、名称及价格:

澳门新濠3559 11

  可以看到,在查找大于等于平均价格的商品中,“5636.36”其实就是上一条SQL语句所求得的结果,将这两条语句合并,就是子查询:

澳门新濠3559 12

通过查询可知在所有商品中属于“超级本”分类的商品共有3件,售价分别是“4999”、“4299”、“7999”。如果想使用子查询的方式,查找售价大于“超级本”的商品,结果报错:

澳门新濠3559 13

出现错误的原因是之前提到的,子查询的返回值只能是一行,虽然要查找售价大于“超级本”价格的商品,但“超级本”的价格有3个,系统无法得知要与哪个进行比较,此时就需要使用ANY、SOME、ALL关键字来修饰比较运算符:

语法结构:
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)

operand comparison_operator ALL (subquery)

运算符\关键字 ANY SOME ALL

、>= | 最小值     | 最小值     | 最大值    
<、<= | 最大值 | 最大值| 最小值
= | 任意值 | 任意值|
<>、!= | | | 任意值

根据上表修改之前的SQL语句,例如添加“ALL”关键字,要求比所有的“超级本”价格都高,即大于最大值“7999”即可:

澳门新濠3559 14


2.由比较运算符引发的子查询

由[NOT] IN/EXISTS引发的子查询

使用[NOT] IN的子查询
语法结构:
operand comparison_operator [NOT] IN (subquery)=ANY 运算符与IN等效。
!=ALL或<>ALL运算符与NOT IN等效

演示:
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price <> ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');
等价于
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

使用[NOT] EXISTS的子查询
如果子查询返回任何行,EXISTS将返回TRUE;否则返回FALSE。

用的较少,子查询返回了结果EXISTS返回TRUE,否则返回FALSE

4、由[NOT] IN / EXISTS引发的子查询

使用[NOT] IN的语法结构:
operand comparison_operator [NOT] IN (subquery)
= ANY 运算符与 IN 等效;
!= ALL 或<> ALL运算符与 NOT IN 等效。

对于上条SQL语句,首先修改为“!= ALL”关键字,即除去3款“超级本”商品,查询剩余的全部19款商品:

澳门新濠3559 15

  可见结果确实是19款:

澳门新濠3559 16

由于篇幅限制,不便详细展示19条结果,可自行查询验证

再修改为“NOT IN”关键字:

澳门新濠3559 17

  结果与之前相同:

澳门新濠3559 18

由于篇幅限制,不便详细展示19条结果,可自行查询验证

使用[NOT] EXISTS的语法结构:
operand comparison_operator [NOT] EXISTS (subquery)
EXISTS:当子查询返回任何行时,EXISTS返回TRUE,触发外层查询;否则返回FALSE。
NOT EXISTS:与EXISTS相反。

例如,子查询中“id”为100的商品不存在,因此不返回任何行,EXISTS返回FALSE,外层查询为空:

澳门新濠3559 19

当查询“id”为10的商品时,可以返回结果,EXISTS返回TRUE,外层查询为全部的22件商品:

澳门新濠3559 20

NOT EXISTS与EXISTS相反,当查询“id”为10的商品时,可以返回结果,NOT EXISTS返回FALSE,外层查询为空;当子查询中“id”为100的商品不存在,不返回任何行时,NOT EXISTS返回TRUE,外层查询为全部的22件商品:

澳门新濠3559 21


澳门新濠3559 22        澳门新濠3559 23

使用INSERT...SELECT插入记录

之前讲过INSERT 和 INSERT SET的区别是INSERT SET 可以使用子查询(SET 可以使XX=XX引发子查询)

tdb_goods表中有很多弊,存在这很多重复的信息,如品牌有很多索尼,分类中有很多笔记本配件,字符串比数字占的字节数多,如果记录越来越多,数据表就会越来越庞大,查找时速度就会变慢,最好的方法是使用外键,需要两张数据表。

创建分类表:

CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL
);

不用一条一条分类的插入,应该使用子查询
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;#一共有7类,需要写入分类表中
使用INSERT...SELECT将查询写入数据表:

INSERT...SELECT
将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

实现:
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;#不能省略插入表的列名,不用写VALUES
使用SELECT * FROM tdb_goods_cates;查看已经插入成功

但是还是没有使用外键,应该参照分类表来更新商品表

5、使用INSERT……SELECT插入记录

其实目前用于演示的表在实际使用中,有一个严重的缺陷,即重复数据过多,而且中文字符占有更多的字节,会导致数据表使用效率降低,例如下图所示的“笔记本配件”、“索尼”等等:

澳门新濠3559 24

  针对上述问题,可以使用外键来解决,创建新的数据表“tdb_goods_cates”:

澳门新濠3559 25

  查询“tdb_goods”表的所有记录,并且按"类别"分组:

澳门新濠3559 26

将查询的分组结果写入到“tdb_goods_cates”数据表中:

澳门新濠3559 27


澳门新濠3559 28  澳门新濠3559 29

多表更新

UPDATE table_references
SET col_name1={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]

CREATE...SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [ID NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

FROM子句中的子查询
语法结构
SELECT...FROM(subquery) [AS] name...
说明:
名称为必选项,且子查询的列名称必须唯一。

连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作

表的连接条件,第一张表+连接类型+第二张表+连接条件
语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

表的参照可以给表赋予别名也可以不赋予别名

连接类型
INNER JOIN,内连接
在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER]澳门新濠3559但实际数据库中的存储数据的编码格式仍然是之前设定的,1.子查询简介。 JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;#tdb_goods表中的goods_cate已经被更新

6、多表更新

语法结构
UPDATE table_references SET col_name1 = {expr1 | DEFAULT} [,col_name2 = {expr2 | DEFAULT}……
[WHERE where_condition]

在使用多表更新时,需要使用“连接”,这里先简单了解一下:

table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }
table_reference ON conditional_expr

可以理解为:
表1“table_reference”+参照关系“{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }”
+表2“table_reference”+连接条件“ON conditional_expr”

连接类型分为:

  • INNER JOIN(内连接)
    在MySQL中,JOIN、CROSS JOIN、INNER JOIN是等价的。
  • LEFT [OUTER] JOIN(左外连接)
  • RIGHT [OUTER] JOIN(右外链接)

此时需要将新创的“tdb_goods_cates”表中的“id”替换到原“tdb_goods”表的“类别”中:

澳门新濠3559 30

  对于上述SQL语句的简单理解是:要更新“tdb_goods”表,以内连接“INNER JOIN”的方式连接“tdb_goods_cates”表,连接的条件是“tdb_goods”表中的商品类别“goods_cate”等于“tdb_goods_cates”表中的“cate_name”,最后将“tdb_goods”表中的商品类别“goods_cate”更新为“tdb_goods_cates”表中的“cate_id”。

通过查询可见已实现更新:

澳门新濠3559 31

但对于多表更新,还存在一种更为简便的方法,目前的方法是首先创建数据表,之后查询数据并写入,最后进行连接更新;那么使用如下语句,便可将创建与查询写入合二为一:

CREATE……SELECT
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,……)] select_statement
创建数据表的同时,将查询的结果写入数据表。

首先查询“tdb_goods”表的所有记录,并且按"品牌"分组:

澳门新濠3559 32

此步骤仅用作展示品牌数量

之后创建数据表“tdb_goods_brands”并写入查询数据:

澳门新濠3559 33

可见数据表以创建完成且存在记录:

澳门新濠3559 34

此时可以使用之前的语句进行连接:

澳门新濠3559 35

系统提示出错,因为无法分辨两个“brand_name”分别属于哪一张表,此时可以在字段前添加表名来加以区分,但更常使用别名来区分,例如:

澳门新濠3559 36

可见所有的类别及品牌都已修改,虽然数字可能相同,但实际意义不同:

澳门新濠3559 37

再来查看该数据表的结构:

澳门新濠3559 38

虽然此时已修改了记录,但表的根本结构没有改变,此时的“1、2、3……”仍然是字符型,因此此时建议修改表的结构:

澳门新濠3559 39

此时数据表才算真正的完成了“瘦身”操作,不过可能会有疑问:之前所说的外键,怎么没看到使用“FOREIGN KEY”,其实在实际开发中,使用物理外键即“FOREIGN KEY”的机会并不多,相反这种事实外键会应用的更广泛一些。


澳门新濠3559 40  澳门新濠3559 41

多表更新之一步到位

以上更新操作参照别的表更新了本表,一共使用了三步:
1.创建表
2.通过使用INSERT...SELECT把记录写入新表
3.多表更新

把三步合并为一步:
可以使用CREATE...SELECT实现:
CREATE...SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

将表中的品牌也独立出一张表,创建表的同时将查询的数据写入:

CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(49) NOT NULL
)
SELECT brand name FROM tdb_goods GROUP BY brand_name;

查看tdb_goods_brands表可以看到数据写入成功
还有一步应该参照品牌表更新商品表中的品牌:
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name SET brand_name = brand_id;#会报错,提示brand_name含义不明确,因为两张表中都有brand_name,系统不知道那两个brand_name是哪张表中的

要解决这个问题,只能给表起别名或者在字段前边加上表名
通常是给表起别名:
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;#更新成功
使用SELECT * FROM tdb_goodsG查看表中的记录,看到表中的brand_name已经被更新

此时,使用SHOW COLUMNS FROM tdb_goods;查看表结构发现表中的goods_cate和brand_name仍然是varchar类型,表中的数字代表的是字符而不是tdb_goods_cate和tdb_goods_brands中的id(数字型)

修改标结构:

ALTER TABEL tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

再使用SHOW COLUMNS FROM tdb_goods;查看表结构看到表结构已经修改成功goods_cate和brand_name修改成了数字类型

关于使用外键:不一定要使用FORIGN KEY物理外键,可以用这种外键,称为事实外键,通常较多使用事实外键,物理外键用的不多

在分类表和品牌表中插入一些记录:

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

插入三个不同的分类和三个不同的品牌

在商品表中插入记录:
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');#此时插入数据成功,但是有一个小错误,goods_cate写的是12,但是tdb_goods_cate表中并没有id为12的分类
把表中的记录查询出来呈现出来,存储时cate_id和brand_id存储的是其他表中的id,显示的时候就不能这样直接显示了,应该显示商品品牌和分类而不是id,这时就需要使用到连接了

7、连接的语法结构

MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }
table_reference ON conditional_expr

可以理解为:
 表1“table_reference”+参照关系“{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }”
 +表2“table_reference”+连接条件“ON conditional_expr”

连接条件:
 使用“ON”关键字来设定连接条件,也可以使用WHERE来代替。
 “ON”关键字更多的用来设定连接条件;
 “WHERE”关键字则进行结果集记录的过滤。

数据表参照:
  table_reference
  tbl_name [[AS] alias] | table_subquery [AS] alias
  数据表可以使用“tbl_name AS alias_name”或“tbl_name alias_name”赋予别名;
  table_subquery可以作为子查询使用在FROM子句中,此类子查询必须为其赋予别名。

连接类型分为:
  INNER JOIN(内连接):在MySQL中,JOIN、CROSS JOIN、INNER JOIN是等价的。
  LEFT [OUTER] JOIN(左外连接)
  RIGHT [OUTER] JOIN(右外链接)

<br />
<br />

注意:当子查询返回多个结果的时候,可以用ANY、SOME、ALL修饰,ANY、SOME是等价的:符合返回结果中的一个即可;ALL:符合全部结果。

连接的语法结构

连接:
MySQL在SELECT语句、多表更新语句中支持JOIN操作

三种连接:内连接,左外连接,右外连接
A表+连接类型+B表+连接条件
语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

数据表参照:
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名。

内连接 INNER JOIN

澳门新濠3559 42

内连接

在演示内连接之前,先分别向数据表“tdb_goods_cates”、“tdb_goods_brands”中添加三个商品分类及三个品牌:

澳门新濠3559 43

之后向数据表“tdb_goods”中添加一条记录:

澳门新濠3559 44

要注意,该记录中“cate_id”添加的“12”仅仅是符合数据类型的要求而没有报错,但实际上根本没有“id”为“12”的商品分类,此时数据表中共有23条记录。

在实际使用中,对于客户来讲,可能根本不清楚例如“cate_id”中的“6”代表的是什么意思:

澳门新濠3559 45

因此需要联合查询数据表“tdb_goods”以及数据表“tdb_goods_cates”,由于两张数据表都有“cate_id”字段,需要添加表名加以区分:

澳门新濠3559 46

此时共查询到结果22条,没有新加入的记录的原因是在“tdb_goods_cates”表中不存在“cate_id”为12的商品分类,而“INNER JOIN”只显示符合连接条件的记录,即共有的部分,因此没有第23条记录:

澳门新濠3559 47

<br />
<br />

澳门新濠3559 48

内连接INNER JOIN

连接类型:
INNER JOIN,内连接
在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接

连接条件:
使用ON关键字来设定连接条件,也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件,
使用WHERE关键字进行结果集记录的过滤。

内链接:显示左表及右表符合连接条件的记录

澳门新濠3559 49

内连接

实例:
SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;
可以看到22条记录,并没有刚刚添加的第23条记录,因为第23条记录不符合连接条件,刚才添加的cate_id是12,在tdb_goods_cate表中并不存在id为12的记录,而且刚刚新添加的几个分类也没有显示出来,这就是内连接(两张表都会有的才会显示出来),仅显示符合连接条件的记录

外连接 OUTER JOIN

澳门新濠3559 50

左外连接

  修改之前的SQL语句,将“INNER JOIN”改为“LEFT JOIN”,“OUTER”可以省略:

澳门新濠3559 51

此时共查询到结果23条,由于是左外连接,因此显示“tdb_goods”表的全部23条记录,但是“tdb_goods_cates”表中没有符合连接条件的记录,即“cate_id”为12的商品分类,因此显示“NULL”:

澳门新濠3559 52

<br />

澳门新濠3559 53

右外连接

修改之前的SQL语句,将“LEFT JOIN”改为“RIGHT JOIN”,“OUTER”可以省略:

澳门新濠3559 54

此时共查询到结果25条,由于是右外连接,因此显示“tdb_goods_cates”表的全部10条记录,但是“tdb_goods”表中没有符合连接条件的记录,即分类属于“路由器”、“交换机”、“网卡”的商品,因此显示“NULL”,而且不显示“cate_id”为“12”的商品:

澳门新濠3559 55

关于外链接的几点说明(以左外连接为例):

  • tbl_A LEFT JOIN tbl_B ON join_condition
  • 数据表B的结果集依赖数据表A,即数据表A中存在的记录,在数据表B中才会显示,而数据表B的其他记录则不会显示;
  • 数据表A的结果集根据左连接条件依赖所有数据表(B表除外);
  • 左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下);
  • 如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外的B行(例如“右外链接”中的查询结果)。

<br />
<br />

澳门新濠3559 56   澳门新濠3559 57

外连接OUTER JOIN

左外连接:显示左表的全部记录及右表符合连接条件的记录

澳门新濠3559 58

左外连接

演示:SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;#OUTER可以写,也可以不写
得到23条记录,但是第23条记录的cate_name为空,左外连接指的是左表中的全部和右表中符合条件的,如果右表中没有符合条件的会显示为NULL

右外连接:显示右表的全部记录及左表符合连接条件的记录

澳门新濠3559 59

右外连接

演示:SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;#OUTER可以写,也可以不写
得到25条记录,没有了那个cate_id为12的记录,又多了三条分类的记录,右外连接指的是显示右表中的全部和左表中符合连接条件的记录

这三种连接中内连接用的想对较多

多表连接

除去之前的两张表连接之外,还可以更多的表进行连接:

澳门新濠3559 60

此时查询的结果与之前最开始的单表结果相同,但此时的意义完全不同:

澳门新濠3559 61


3.由【NOT】IN/EXISTS引发的子查询

多表连接

商品表中存在商品分类和品牌
实现三张表的连接:
演示:

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g 
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id 
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;

可以看到这是有恢复了我们最初原始的结果,不一样的是这次是通过多张表的连接实现的,以前是通过一张表查询出来的

其实表的连接就像是外键的逆向操作,外键把表分开存储,连接把多张表连接起来查询

8、无限级分类表设计

本篇作为案列使用数据表“tdb_goods”中的商品分类,在实际开发中是远远不够的,那么例如“X宝”、“X东”等电商网站的商品分类是如何实现的,可以参考以下案列:

首先创建数据表“tdb_goods_types”:

澳门新濠3559 62

type_id —— 分类编号
type_name —— 分类名称
parent_id —— 父类编号

插入相关记录:

澳门新濠3559 63

此次举例共填入了15条记录,手动输入过于繁琐,因此提供源码,点击下载使用。

查看该表的所有记录:

澳门新濠3559 64

其中:
“家用电器”与“电脑、办公”都是顶级分类,没有父类;
“大家电”与“生活电器”都属于“家用电器”,父类编号为“1”,即“家用电器”的商品编号;
“平板电视”与“空调”都属于“大家电”,父类编号为“3”,以此类推。

对这种数据表进行查询就需要使用“自身连接”,例如使查询结果显示子类商店的编号、名称以及父类商品的名称,就可以这样理解:

想象有两张完全相同的表,分别是父表与子表,至于如何确定父表、子表的方式不唯一,位置可以交换,只是为了满足别名的需要,否则系统将无法区分这些相同的字段;当确定父表与子表后,父表中“parent_id”字段就没有用处了,因为本身就是父表,而子表中“parent_id”其实就是父表中的“type_id”,因此,就可以做如下考虑:

澳门新濠3559 65

以子表为参照

此时的查询结果为:

澳门新濠3559 66

由于MySQL无法实现递归查询,因此只能显示一级父类

例如此时需要查询父类的商品编号、名称以及其子类的名称:

澳门新濠3559 67

以父表为参照

此时的查询结果为:

澳门新濠3559 68

但这种显示方式比较混乱,对此修改为显示父类商品的编号、名称以及其子类商品的数目:

澳门新濠3559 69

第一步,简化父类的数目,以父类商品的名称分组

澳门新濠3559 70

第二步,按照编号排序

澳门新濠3559 71

第三步,修改SQL语句,使用“count”计数,同时赋予别名


 澳门新濠3559 72     澳门新濠3559 73

关于连接的几点说明

A LEFT JOIN B join_condition

  • 数据表B的结果集依赖数据表A。
  • 数据表A的结果集根据左连接条件依赖所有数据表(B表除外)。
  • 左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)。
  • 如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外的B行。

查找到的结果为NULL但是含有约束NOT NULL的情况:
如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试以下操作:col_namd IS NULL时,如果col_name被定义为NOT NULL,MySQL将在找到符合连接执行条件的记录后停止搜索更多的行。

9、多表删除

语法结构
DELETE tbl_name[.*] [,tbl_name[.*]] …… FROM tbl_references [WHERE where_condition]

通过查询发现,在“tdb_goods”数据表中有部分商品名称重复的记录,例如:

澳门新濠3559 74

细化查询条件,通过商品名称分组,并查询商品名称大于或等于2个的商品,即为重复商品:

澳门新濠3559 75

与之前的“自身连接”类似,仍然使用这同一张表演示多表删除操作:

澳门新濠3559 76

相同商品的编号分别是18、19与21、22,WHERE语句的目的是删除“id”编号较大的重名商品

输入SQL语句后提示“有两条记录被删除”,此时查询记录只有21条:

澳门新濠3559 77


澳门新濠3559 78  

无限极分类表设计

查看tdb_goods_cates表的记录,这些分类远远达不到现实中分类的要求,很多网站中,这些分类有很多级分类,一级分类、二级分类、三级分类……这种分类就是无限分类,数据表应该怎样设计,可以设计很多张表,随着分类的增多,表的数目也会逐渐增多,查找起来就不方便了,所以,一般都采用在表中增加父分类的id字段实现:

实例:

CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);#parent_id为父分类的id,为0表示没有父分类,为一级分类

然后插入数据:

  INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

那么问题来了:如何查询这张表

可以通过自身连接查询:
自身连接:同一个数据表对其自身进行连接

示例演示:
一张表做自身连接必须要起别名,要不就分不清这两个相同名称的字段从哪来的了
想象一下有两张相同的表,左边是父表,右边是子表
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;#就可以查到子类的id,子类的名字以及父类的名字

查找子类,父类以及父类下的子类:
左边是子表,右边是父表
SELECT p.type_id,p.type_name,s.typename FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;
含有重复的父类,使用GROUP BY分组:
SELECT p.type_id,p.type_name,s.typename FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ;
可以看到只有15个分类了,按照id排序:

SELECT p.type_id,p.type_name,s.typename FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

不要子类的名字,需要子类的数目:

SELECT p.type_id,p.type_name,count(s.typename) AS child_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

就可以看到子类的数量了

10、操作数据表记录的SQL语句汇总:

  • 子查询
    SELECT*FROM t1 WHERE col1 = ( SELECT col2 FROM t2);
    由[NOT] IN 引发的子查询:
    operand comparison_operator [NOT] IN (subquery)
    = ANY 运算符与 IN 等效;
    != ALL 或<> ALL运算符与 NOT IN 等效。
    由[NOT] EXISTS引发的子查询:
    operand comparison_operator [NOT] EXISTS (subquery)
    EXISTS:当子查询返回任何行时,EXISTS返回TRUE,触发外层查询;否则返回FALSE。
    NOT EXISTS:与EXISTS相反。
    由比较运算符引发的子查询:
    operand comparison_operator ANY (subquery)
    operand comparison_operator SOME (subquery)
    operand comparison_operator ALL (subquery)
运算符\关键字 ANY SOME ALL

、>= | 最小值     | 最小值     | 最大值    
<、<= | 最大值 | 最大值| 最小值
= | 任意值 | 任意值|
<>、!= | | | 任意值

  • 多表更新
    UPDATE table_references SET col_name1 = {expr1 | DEFAULT} [,col_name2 = {expr2 | DEFAULT}……
    [WHERE where_condition]

  • 连接
    table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }
    table_reference ON conditional_expr
    连接分类:
    INNER JOIN(内连接):在MySQL中,JOIN、CROSS JOIN、INNER JOIN是等价的。
    LEFT [OUTER] JOIN(左外连接)
    RIGHT [OUTER] JOIN(右外链接)
    连接条件:
    使用“ON”关键字来设定连接条件,也可以使用WHERE来代替。
    “ON”关键字更多的用来设定连接条件;
    “WHERE”关键字则进行结果集记录的过滤。
    数据表参照:
    table_reference
    tbl_name [[AS] alias] | table_subquery [AS] alias
    数据表可以使用“tbl_name AS alias_name”或“tbl_name alias_name”赋予别名;
    table_subquery可以作为子查询使用在FROM子句中,此类子查询必须为其赋予别名。

  • 使用CREATE……SELECT插入记录
    CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,……)] select_statement

  • 多表删除
    DELETE tbl_name[.*] [,tbl_name[.*]] …… FROM tbl_references [WHERE where_condition]

版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓

4.使用INSERT...SELECT插入记录

多表删除

DELETE tbl_name[.] [,tbl_name[.]]...
FROM table_references
[WHERE where_condition]

使用SELECT * FROM tdb_goods;查看表中的记录,看到第18、19和第21、22条记录是重复的,这是,想要把重复的记录删除,保留id较小的记录。
可以通过多表删除实现,也就是采用一张表模拟两张表实现
演示:
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name;#从23条记录中得到了21个商品,因为有些记录是相同的

我们只想要相同商品名称超过两个以上的记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name) > 1;#得到重复商品的记录,这就是我们将要删除或者要保留的记录
可以通过这张表来删除原表中的数据:
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id > t2.goods_id;

 澳门新濠3559 79

澳门新濠3559 80   澳门新濠3559 81

5.多表更新

澳门新濠3559 82   澳门新濠3559 83 澳门新濠3559 84

 

澳门新濠3559 85   澳门新濠3559 86

6.多表更新之一步到位

澳门新濠3559 87  澳门新濠3559 88

澳门新濠3559 89   澳门新濠3559 90

澳门新濠3559 91  澳门新濠3559 92

注意:1.因为表tdb_goods和表tdb_goods_brands中都有字段brand_name,所以如果不给两张表分别起别名的话,系统会提示brand_name是模糊的(ambiguous);2.表tdb_goods中的字段goods_cate和brand_name现在的值虽然是数字了,但是数字“3”、“8”等仍然是字符型,占用空间较大,

澳门新濠3559 93  澳门新濠3559 94

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

编辑:数据库 本文来源:澳门新濠3559但实际数据库中的存储数据的编码格

关键词: