您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:前面一个学数据库之多表操作,前

新葡亰496net:前面一个学数据库之多表操作,前

发布时间:2019-10-05 12:50编辑:网络数据库浏览(108)

    如今的话

      上一篇博文中介绍了子查询的相关内容,最后大家将查询结果存款和储蓄到二个新的数目表中。上边我们将接着子查询的案例,详细介绍数据库中的多表操作

     

    前边一个学数据库之多表操作,前端数据库

    ×

    MySQL学习笔记-子查询和连接,mysql学习笔记查询

    MySQL学习笔记-子查询和一而再   使客户端步向gbk编码格局浮现:

    mysql> SET NAMES gbk;
    

     

    1.子查询 子查询的定义: 子查询(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,O福睿斯DEPAJERO BY,LIMIT函数等。 子查询的表面查询能够是:SELECT , INSERT , UPDATE , SET 或 DO   子查询的重临值: 子查询可以回去标量、一行、一列或子查询   2.行使比较运算符的子查询 使用对比运算符的子查询: =、>、<、>=、<=、<>、!=、 <=> 语法结构: operand(操作数)、comparison_operator (相比较运算符)、subquery(子查询) 用ANY  、SOME 或ALL修饰的比较运算符 operand comparison_operator ANY (subquery) operand comparison_operator SOME (subquery) operand comparison_operator ALL (subquery) 新葡亰496net 1   1.语法结构  操作数  相比较运算符 any(子查询)             操作数  相比较运算符 some(子查询)             操作数  比较运算符 all(子查询) 2.相符于子查询有多少个结果 3.any 和some结果一致 all与any、some 相反   例,从tdb_goods表中 查询平均价格,小数点保留2位,:

    mysql> SELECT ROUND(avg(goods_price),2) AS avg_price FROM tdb_goods;
     ----------- 
    | avg_price |
     ----------- 
    |   5391.30 |
     ----------- 
    

     

    查询平均价格以上的物品:

    mysql> SELECT * FROM tdb_goods WHERE goods_price > 5391.30; 
    

     

    新葡亰496net 2

    mysql> SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
    

     

    新葡亰496net 3   查询超极本的列表,按价格升序排列:

    mysql> SELECT * FROM tdb_goods WHERE goods_cate = '超级本' ORDER BY goods_price ASC; 
    

     

    新葡亰496net 4

    • 询问比超极本的最实惠格高的列表,按价格降序的方法排列:
    mysql> SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;
    

     

    新葡亰496net 5

    • 询问比超极本的万丈价格高的列表,按价格降序的点子排列:
    mysql> SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;
    

     

    新葡亰496net 6   3.使用[NOT]IN/EXISTS引发的子查询 子查询情势: 1.选拔IN的子查询 2.接纳[NOT]IN的子查询   语法结构:operand comparison_operator [NOT] IN (subquery)   =ANY 运算符与IN 等效,!=ALL或<>ALL运算符与NOT IN等效 3.运用[NOT]EXISTS的子查询(用的周旋非常少)   假设实查询再次回到任何行,EXISTS将重返TRUE,不然为FALSE 例,查找不是超极本的商品列表:

    mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE goods_cate = '超级本');
    

     

    新葡亰496net 7     4.运用INSERT...SELECT插入记录 例:在tdb_goods_cates表中插入tdb_goods表中的goods_cate分类;

    mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
    mysql> SELECT * FROM tdb_goods_cates;
     --------- --------------------- 
    | cate_id | cate_name           |
     --------- --------------------- 
    |       1 | 台式机              |
    |       2 | 平板电脑            |
    |       3 | 服务器/工作站       |
    |       4 | 游戏本              |
    |       5 | 笔记本              |
    |       6 | 笔记本配件          |
    |       7 | 超级本              |
     --------- --------------------- 
    

     

    5.多表更新 多表更新:

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

     

    其中,table_references即延续的语法结构   连接类型: INNE帕杰罗 JOIN,内连接    在MySQL中,JOIN,CROSS JOIN和INNEENCOREJOIN是等价的 LEFT [OUTER] JOIN,左外连接 奥迪Q3IGHT [OUTER] JOIN,右外连接   连接—语法结构

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

     

    例,将tdb_goods中的goods_cate更新为tdb_goods_cate表中的对应cate_id。 

    mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
    

     

    新葡亰496net 8   6.多表更新之一步到位 CREATE...SELECT 创设数据表同不常间将查询结果写入到数据表(合併了CREATE 和 INSERT...SELECT多少个操作步骤)

    CREATE TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    select_statement
    

     

    例:创建品牌分类数据表tdb_goods_brand,并将tdb_goods表中的brand_name写入

    mysql> CREATE TABLE tdb_goods_brand(
        -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        -> brand_name VARCHAR(40) NOT NULL
        -> )
        -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
    

     

    新葡亰496net 9

    • 例,将tdb_goods中的brand_name更新为tdb_goods_brand表中的对应brand_id。错误写法(程序分别不出哪个brand_name属于哪个表):
    mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id;
    ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous
    

     

    缓慢解决情势是给表起小名:

    mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;
    

     

    新葡亰496net 10

    • 查询tdb_goods的数据表结构
    mysql>  DESC tdb_goods;
     ------------- ------------------------ ------ ----- --------- ---------------- 
    | Field       | Type                   | Null | Key | Default | Extra          |
     ------------- ------------------------ ------ ----- --------- ---------------- 
    | goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
    | goods_name  | varchar(150)           | NO   |     | NULL    |                |
    | goods_cate  | varchar(40)            | NO   |     | NULL    |                |
    | brand_name  | varchar(40)            | NO   |     | NULL    |                |
    | goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
    | is_show     | tinyint(1)             | NO   |     | 1       |                |
    | is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
     ------------- ------------------------ ------ ----- --------- ---------------- 
    

     

    goods_cate和brand_name任然是varchar,以往大家修改字段名goods_cate为cate_id,brand_name修改为brand_id,  为了节约空间,我们修改数据类型为smallant

    mysql> ALTER TABLE tdb_goods;
        -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
        -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
    mysql> DESC tdb_goods;
    
     ------------- ------------------------ ------ ----- --------- ---------------- 
    | Field       | Type                   | Null | Key | Default | Extra          |
     ------------- ------------------------ ------ ----- --------- ---------------- 
    | goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
    | goods_name  | varchar(150)           | NO   |     | NULL    |                |
    | cate_id     | smallint(5) unsigned   | NO   |     | NULL    |                |
    | brand_id    | smallint(5) unsigned   | NO   |     | NULL    |                |
    | goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
    | is_show     | tinyint(1)             | NO   |     | 1       |                |
    | is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
     ------------- ------------------------ ------ ----- --------- ---------------- 
    

     

    7.连连的语法结构 连接在MySQL在SELECT语句、多表更新、多表删除语句中援救JOIN操作。

    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子句中, 那样的子查询必得予以外号。   8.内连接INNERAV4 JOIN INNE奥迪Q5 JOIN,内连接 在MySQL中,JOIN,CROSS JOIN 和 INNELANDJOIN 是等价的 LEFT [OUTER] JOIN,左外连接 奇骏IGHT [OUTER] JOIN,右外连接 连接条件: 使用ON关键字来设定连接条件,也能够使用WHERE来替代平日使用ON关键字来设定连接条件 使用WHERE关键字展开结果集记录的过滤   内连接和外接连的界别 内连接 ,展现左表及右表切合连接条件的记录,即交集 譬喻 插入几条记下 -- 分别在tdb_goods_cates和tdb_goods_brands表插入记录

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

     

    -- 在tdb_goods数据表写入狂妄记

    INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
    

     

    例,查看切合内连接的笔录,表中只出现商品表和品牌表都有的记录,这正是内接连(这里商品表为左表,分类表为右表):

    mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods
    -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
    

     

    新葡亰496net 11     9.外连接OUTELANDJOIN 左外连接 新葡亰496net 12

    • 体现左表的一体记下及右表符合连接条件的笔录
    • 例,查看切合左外连接的记录,表中出现商品表的万事和2表表都有的记录,那正是左外连接(这里商品表为左表,分类表为右表)
    mysql> SELECT goods_id,cate_name,goods_price FROM tdb_goods 
    LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
    

     

    新葡亰496net 13    

    • 右外连接

    新葡亰496net 14  

    • 显示右表的方方面面笔录及左表相符连接条件的笔录
    • 例,查看相符右外连接的笔录,表中出现品牌表的全部和2表表都有个别记录,那就是右外连接(这里商品表为左表,分类表为右表)
    mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods 
    RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
    

     

      新葡亰496net 15   10.多表连接 /**     多表的连年跟两张表的连年同样  表的总是实质就是外键的逆向约束 */ 例,查看符合内接连的记录,表中只出现商品表和品牌表都有的记录,那就是内连接的多表连接(这里商品表为左表,品牌表和分类表为右表):

    mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods
        -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id
        -> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id;
    

     

    新葡亰496net 16   11.Infiniti级分类表设计 本人连接  

    • 例,查找全数分类及其父类:
    mysql> 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;
     --------- ----------------- ----------------- 
    | type_id | type_name       | type_name       |
     --------- ----------------- ----------------- 
    |       1 | 家用电器        | NULL            |
    |       2 | 电脑、办公      | NULL            |
    |       3 | 大家电          | 家用电器        |
    |       4 | 生活电器        | 家用电器        |
    |       5 | 平板电视        | 大家电          |
    |       6 | 空调            | 大家电          |
    |       7 | 电风扇          | 生活电器        |
    |       8 | 饮水机          | 生活电器        |
    |       9 | 电脑整机        | 电脑、办公      |
    |      10 | 电脑配件        | 电脑、办公      |
    |      11 | 笔记本          | 电脑整机        |
    |      12 | 超级本          | 电脑整机        |
    |      13 | 游戏本          | 电脑整机        |
    |      14 | CPU             | 电脑配件        |
    |      15 | 主机            | 电脑配件        |
     --------- ----------------- ----------------- 
    

     

    • 例,查找全部分类及其子类:
    mysql> SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id; 
     --------- ----------------- -------------- 
    | type_id | type_name       | type_name    |
     --------- ----------------- -------------- 
    |       1 | 家用电器        | 大家电       |
    |       1 | 家用电器        | 生活电器     |
    |       2 | 电脑、办公      | 电脑整机     |
    |       2 | 电脑、办公      | 电脑配件     |
    |       3 | 大家电          | 平板电视     |
    |       3 | 大家电          | 空调         |
    |       4 | 生活电器        | 电风扇       |
    |       4 | 生活电器        | 饮水机       |
    |       5 | 平板电视        | NULL         |
    |       6 | 空调            | NULL         |
    |       7 | 电风扇          | NULL         |
    |       8 | 饮水机          | NULL         |
    |       9 | 电脑整机        | 笔记本       |
    |       9 | 电脑整机        | 超级本       |
    |       9 | 电脑整机        | 游戏本       |
    |      10 | 电脑配件        | CPU          |
    |      10 | 电脑配件        | 主机         |
    |      11 | 笔记本          | NULL         |
    |      12 | 超级本          | NULL         |
    |      13 | 游戏本          | NULL         |
    |      14 | CPU             | NULL         |
    |      15 | 主机            | NULL         |
     --------- ----------------- -------------- 
    

     

    • 例,查找全部分类及其子类的数额
    mysql> SELECT p.type_id,p.type_name,COUNT(s.type_name) 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;
     --------- ----------------- -------------------- 
    | type_id | type_name       | COUNT(s.type_name) |
     --------- ----------------- -------------------- 
    |       1 | 家用电器        |                  2 |
    |       2 | 电脑、办公      |                  2 |
    |       3 | 大家电          |                  2 |
    |       4 | 生活电器        |                  2 |
    |       5 | 平板电视        |                  0 |
    |       6 | 空调            |                  0 |
    |       7 | 电风扇          |                  0 |
    |       8 | 饮水机          |                  0 |
    |       9 | 电脑整机        |                  3 |
    |      10 | 电脑配件        |                  2 |
    |      11 | 笔记本          |                  0 |
    |      12 | 超级本          |                  0 |
    |      13 | 游戏本          |                  0 |
    |      14 | CPU             |                  0 |
    |      15 | 主机            |                  0 |
     --------- ----------------- -------------------- 
    

     

    • 为tdb_goods_types添加child_count字段
    mysql> UPDATE tdb_goods_types AS t1 INNER JOIN
        -> (SELECT p.type_id,p.type_name,count(s.type_name) 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)AS t2
        -> ON t1.type_id = t2.type_id
        ->  SET t1.child_count = t2.child_count;
    
    mysql> SELECT * FROM tdb_goods_types;
     --------- ----------------- ----------- ------------- 
    | type_id | type_name       | parent_id | child_count |
     --------- ----------------- ----------- ------------- 
    |       1 | 家用电器        |         0 |           2 |
    |       2 | 电脑、办公      |         0 |           2 |
    |       3 | 大家电          |         1 |           2 |
    |       4 | 生活电器        |         1 |           2 |
    |       5 | 平板电视        |         3 |           0 |
    |       6 | 空调            |         3 |           0 |
    |       7 | 电风扇          |         4 |           0 |
    |       8 | 饮水机          |         4 |           0 |
    |       9 | 电脑整机        |         2 |           3 |
    |      10 | 电脑配件        |         2 |           2 |
    |      11 | 笔记本          |         9 |           0 |
    |      12 | 超级本          |         9 |           0 |
    |      13 | 游戏本          |         9 |           0 |
    |      14 | CPU             |        10 |           0 |
    |      15 | 主机            |        10 |           0 |
     --------- ----------------- ----------- ------------- 
    

     

    11.多表删除

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

     

    例,查找重复记录:

    mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name) >= 2;                
     ---------- ----------------------------- 
    | goods_id | goods_name                  |
     ---------- ----------------------------- 
    |       18 |  HMZ-T3W 头戴显示设备       |
    |       19 | 商务双肩背包                |
     ---------- ----------------------------- 
    

     

    除去重复记录

    mysql> 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;
    

     

    12.复制记录 复制编号为19,20的两条记下

    mysql>  SELECT goods_id,goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);
     ---------- ------------------------------------ --------- ---------- 
    | goods_id | goods_name                         | cate_id | brand_id |
     ---------- ------------------------------------ --------- ---------- 
    |       19 | 商务双肩背包                       |       6 |        7 |
    |       20 | X3250 M4机架式服务器 2583i14       |       3 |        1 |
     ---------- ------------------------------------ --------- ---------- 
    
    mysql> INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN(19,20);
    

     

    新葡亰496net 17

    MySQL学习笔记-子查询和接二连三 使客商端步向gbk编码形式展现: mysql SET NAMES gbk; 1.子查询 子查...

    多少企图

    预备职业

      在上一篇博文,大家将详见数据存储到tdb_goods数据表中,将详细数据中的种类音讯存款和储蓄到tdb_goods_cates数据表中

    新葡亰496net 18

      接下去,大家要钻探什么通过tdb_goods_cates数据表来更新tdb_goods表

     

    目录

    [1]预备工作 [2]多表更新 [3]两步更新[4]连接[5]极端级表

    回顾

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

    那章器重学习:
    子查询
    连接
    多表删除
    多表更新

    数码打算:
    差不离的超级市场数据库

    新葡亰496net 19

    Paste_Image.png

    tdb_good表结构:

    新葡亰496net 20

    Paste_Image.png

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

    在乎编码方式,插入的时候是以utf8的方式插入的,展现会乱码,此时接纳SET NAMES gbk;设置顾客端的编码格局(不会耳闻则诵服务器)

    多表更新

      多表更新类似于单表更新

    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
    

      从结果中看出,tdb_goods数据表中goods_cate列中的值早就更新为tdb_goods_cates数据表中对应的cate_id的值。这样一来,用数字代表字符串,十分的大地节约了积累空间

    新葡亰496net 21

     

    前方的话

      上一篇博文中介绍了子查询的连带内容,最终大家将查询结果存款和储蓄到二个新的数量表中。下边我们将接着子查询的案例,详细介绍数据库中的多表操作

     

    子查询简要介绍

    子查询:
    子查询(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语句中采纳

    两步更新

      在地点的多表更新的操作中,实际上咱们经过了四个步骤,先创建了一个空表,将原数据表的询问结果写入空表,再采纳写入结果的表反向立异原数据表

      假使运用CREATE SELECT语句将得以兑现两步更新,在创设数据表相同的时间将查询结果写入到数据表(合併了CREATE和INSERT...SELECT三个操作步骤),再利用写入结果的表反向革新原数据表

    CREATE TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    select_statement
    

      上面来管理原数据表tdb_goods中的品牌新闻,首先查询tdb_goods表的"品牌",并分组

    SELECT brand_name FROM tdb_goods GROUP BY brand_name;
    

    新葡亰496net 22

      将品牌消息放入新表tdb_goods_brands中

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

    新葡亰496net 23

      再参谋品牌表,更新原商品数据表

    新葡亰496net 24

      这里要留意的是,两张表中,同期设有brand_name那么些字段。要区分它们,须求给它们起差异的外号或在字段前边插石英钟名

    新葡亰496net 25

      查看商品数据表的列结构,大家开掘,就算数额被修改为了数字,但数据类型还是是字符型

    新葡亰496net 26

      上边修改商品数据表中goods_cate和brand_name的列名称和列类型

    新葡亰496net 27

      那样,我们曾经将七个大的数据表分为小的数据表进行仓库储存了。现在,分别在tdb_goods_cates和tdb_goods_brands表再插入几条新的记录

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

      在tdb_goods数据表也写入新的记录

     INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
    

    新葡亰496net 28

     

    预备职业

      在上一篇博文,我们将详细数据存款和储蓄到tdb_goods数据表中,将详细数据中的类别音信囤积到tdb_goods_cates数据表中

    新葡亰496net 29

      接下去,大家要研讨怎么通过tdb_goods_cates数据表来更新tdb_goods表

     

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

    子查询分类:
    运用相比运算符的子查询
    选择相比较运算符的子查询(=、>、<、>=、<=、<>、!=、<=>)
    语法结构: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关键字:

    新葡亰496net 30

    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='超极本');#选择的其实就是子查询里查询的结果

    连接

      通过地点的操作,已经把重复的数量分散到不相同的数据表中举行仓库储存了,尽可能的节约存款和储蓄空间了。可是,呈现时,却须要把原来的数额显示出来,那就供给运用上边要介绍的定义——连接

    语法结构

      MySQL在SELECT语句、多表更新、多表删除语句中援助连接(JOIN)操作

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

      数据表参照(table_reference)时,数据表能够利用tbl_name AS alias_name 或tbl_name alias_name赋予外号

      table_subquery可以用作子查询使用在FROM子句中,这样的子查询必得为其授予外号

    tbl_name[[AS] alias] | table_subquery [AS] alias
    

    连日类型

      连接类型首要不外乎内接连(INNE奥德赛 JOIN)、左外连接(LEFT [OUTER] JOIN)、右外连接(福睿斯IGHT [OUTER] JOIN)

      在mysql中,JOIN、CROSS JOIN 和 INNE瑞鹰 JOIN是等价的

    连日条件

      使用ON关键字来设定连接条件,也足以采用WHERE来代替。日常地,使用ON关键字来设定连接条件,使用WHERE关键字张开结果集记录的过滤

    内连接

      内连接展现左表及右表符合连接条件的笔录

    新葡亰496net 31

      上面通过内接连来询问全部商品的详细音信,原来商品表中有24件物品,但只展现出23件,因为那一件不符合连接条件

    新葡亰496net 32

    新葡亰496net 33  

      关于内接连,有以投注意:使用内连接查找的记录在连接数据表中不设有,况兼在WHERE子句中品尝一下操作:column_name IS NULL 。如果 column_name 被钦定为 NOT NULL,MySQL将要找到契合连接着标准的笔录后停下搜索越来越多的行(查找冲突)

    左外连接

      左外连接指显示左表的一体笔录及右表切合连接条件的笔录

    新葡亰496net 34

      下边通过左外连接来询问全部商品的详细音信,原本商品表中有24件商品,以后也出示出24件,但聊起底一件物品的分类为NULL,那是因为右表的那三个分拣不相符条件,所以展示为NULL

    新葡亰496net 35

    右外连接

      右外接连指展现右表的全部笔录及左表契合连接条件的记录

    新葡亰496net 36

      上边通过右外连接来查询全数商品的详细消息,原本商品表中有24件商品,未来来得出26件,多出来的是契合右表但不适合左表的笔录

    新葡亰496net 37

      关于外接连,有以下几点注意,以左外连接为例

    A LEFT JOIN B join_condition
    

      数据表B的结果集依赖于数据表A,数据表A的结果集依照左连接条件信赖全体数据表(B表除了那么些之外)

      左外连接条件决定怎么着搜索数据表B(在并未有一点名WHERE条件的地方下)

      要是数额表A的某条记下相符WHERE条件,不过在多少表B空头支票符合连接条件的笔录,将生成贰个负有列为空的额外的B行

    多表连接

      三张表以上的总是称为多表连接,原理与两张表的接连同样

      上面通过内接连达成查询全部商品的详细新闻

    新葡亰496net 38

     

    多表更新

      多表更新类似于单表更新

    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
    

      从结果中看出,tdb_goods数据表中goods_cate列中的值曾经更新为tdb_goods_cates数据表中对应的cate_id的值。那样一来,用数字代表字符串,十分的大地节约了仓库储存空间

    新葡亰496net 39

     

    由[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

    Infiniti级表

    新葡亰496net 40

      上海教室中是tdb_goods_cates表的笔录。但实则的分类而不是那10类,而是无限分类。下边来介绍Infiniti分类的数据表的落实

      无限级表起码须求八个列,多个是项目id,二个等级次序名称,一个是父级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
    ); 
    

      然后,写入加以数据

    新葡亰496net 41

    自身连接

      本人连接指同多少个数据表对其自身实行三番五次。为作区分,要求增加别称。字表别称定义为s,父表外号定义为p

      上边来探索全体分类及其父类

    新葡亰496net 42

      下边来寻找全数分类及其子类

    新葡亰496net 43

      下边来索求全部分类及其子类的数量

    新葡亰496net 44

    剔除重复项

      从记录中,能够看来24条记下中存在重新的项,今后要想办法把重复的项删除

    新葡亰496net 45

      首先,先查找到重复的项

    新葡亰496net 46

      然后,须要使用多表删除来兑现删除操作

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

    新葡亰496net 47

    两步更新

      在上面的多表更新的操作中,实际上我们由此了四个步骤,先创设了八个空表,将原数据表的查询结果写入空表,再使用写入结果的表反向立异原数据表

      借使选取CREATE SELECT语句将得以兑现两步更新,在创制数据表同不经常候将查询结果写入到数据表(合併了CREATE和INSERT...SELECT多个操作步骤),再使用写入结果的表反向立异原数据表

    CREATE TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    select_statement
    

      下边来拍卖原数据表tdb_goods中的品牌消息,首先查询tdb_goods表的"品牌",并分组

    SELECT brand_name FROM tdb_goods GROUP BY brand_name;
    

    新葡亰496net 48

      将品牌音信归入新表tdb_goods_brands中

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

    新葡亰496net 49

      再参谋品牌表,更新原商品数据表

    新葡亰496net 50

      这里要当心的是,两张表中,同有时间存在brand_name这几个字段。要有别于它们,需求给它们起分歧的小名或在字段前面参加表名

    新葡亰496net 51

      查看商品数据表的列结构,我们发现,纵然数额被涂改为了数字,但数据类型依旧是字符型

    新葡亰496net 52

      上面修改商品数据表中goods_cate和brand_name的列名称和列类型

    新葡亰496net 53

      那样,我们已经将贰个大的多少表分为小的数据表实行仓库储存了。今后,分别在tdb_goods_cates和tdb_goods_brands表再插入几条新的记录

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

      在tdb_goods数据表也写入新的笔录

     INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
    

    新葡亰496net 54

     

    运用INSERT...SELECT插入记录

    后边讲过INSERT 和 INSERT SET的界别是INSERT SET 能够使用子查询(SET 能够使XX=XX引发子查询)

    tdb_goods表中有很多弊,存在那大多种新的音信,如品牌有多数Sony,分类中有过多台式机配件,字符串比数字占的字节数多,如若记录更加的多,数据表就能更粗大,查找时进程就能变慢,最棒的不二诀窍是运用外键,需求两张数据表。

    创造分类表:

    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;查看已经插入成功

    唯独仍旧不曾应用外键,应该参照分类表来更新商品表

    连接

      通过上边的操作,已经把重复的多少分散到差异的数据表中展张开货仓储了,尽也许的节约存款和储蓄空间了。可是,显示时,却须求把原本的数量彰显出来,那就必要选用上边要介绍的概念——连接

    语法结构

      MySQL在SELECT语句、多表更新、多表删除语句中帮忙连接(JOIN)操作

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

      数据表参照(table_reference)时,数据表能够行使tbl_name AS alias_name 或tbl_name alias_name赋予外号

      table_subquery能够充当子查询使用在FROM子句中,那样的子查询必得为其予以小名

    tbl_name[[AS] alias] | table_subquery [AS] alias
    

    连日来类型

      连接类型重要回顾内连接(INNE智跑 JOIN)、左外连接(LEFT [OUTER] JOIN)、右外连接(LacrosseIGHT [OUTER] JOIN)

      在mysql中,JOIN、CROSS JOIN 和 INNE福睿斯 JOIN是等价的

    连日来条件

      使用ON关键字来设定连接条件,也能够利用WHERE来代替。平日地,使用ON关键字来设定连接条件,使用WHERE关键字展开结果集记录的过滤

    内连接

      内三番五次呈现左表及右表适合连接条件的记录

    新葡亰496net 55

      上边通过内接连来询问全数商品的详细消息,原本商品表中有24件货色,但只展示出23件,因为那一件不切合连接条件

    新葡亰496net 56

    新葡亰496net 57  

      关于内连接,有以压宝意:使用内连接查找的笔录在连年数据表中不设有,而且在WHERE子句中品尝一下操作:column_name IS NULL 。如果 column_name 被钦点为 NOT NULL,MySQL将在找到切合连接着标准的笔录后停下寻觅更加多的行(查找冲突)

    左外连接

      左外连接指展现左表的百分百笔录及右表切合连接条件的笔录

    新葡亰496net 58

      下边通过左外连接来询问全体商品的详细音讯,原来商品表中有24件物品,以往也显得出24件,但最后一件商品的分类为NULL,那是因为右表的那二个分类不切合条件,所以体现为NULL

    新葡亰496net 59

    右外连接

      右外接连指呈现右表的整个记录及左表符合连接条件的笔录

    新葡亰496net 60

      上面通过右外连接来询问全体商品的详细音讯,原本商品表中有24件货色,现在呈现出26件,多出去的是切合右表但不契合左表的笔录

    新葡亰496net 61

      关于外接连,有以下几点注意,以左外连接为例

    A LEFT JOIN B join_condition
    

      数据表B的结果集正视于数据表A,数据表A的结果集依照左连接条件正视全数数据表(B表除此之外)

      左外连接条件决定哪些寻觅数据表B(在平素不点名WHERE条件的动静下)

      倘诺数据表A的某条记下切合WHERE条件,不过在多少表B不设有切合连接条件的笔录,将生成叁个存有列为空的额外的B行

    多表连接

      三张表以上的接连称为多表连接,原理与两张表的连接同样

      上面通过内再三再四达成查询全部商品的详细新闻

    新葡亰496net 62

     

    多表更新

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

    新葡亰496net:前面一个学数据库之多表操作,前端数据库。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和INNE哈弗 JOIN是等价的。
    LEFT [OUTER] 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已经被更新

    然则级表

    新葡亰496net 63

      上海体育场面中是tdb_goods_cates表的记录。但实际上的分类而不是那10类,而是Infiniti分类。上面来介绍Infiniti分类的数据表的贯彻

      Infiniti级表起码须求三个列,一个是连串id,三个品类名称,三个是父级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
    ); 
    

      然后,写入给定数据

    新葡亰496net 64

    自身连接

      本人连接指同二个数据表对其本身进行延续。为作区分,需求增添别称。字表小名定义为s,父表别称定义为p

      下边来寻觅全数分类及其父类

    新葡亰496net 65

      下边来搜寻全数分类及其子类

    新葡亰496net 66

      下边来搜索全部分类及其子类的数额

    新葡亰496net 67

    删去重复项

      从记录中,能够看出24条记下中留存双重的项,以往要想艺术把重复的项删除

    新葡亰496net 68

      首先,先查找到重复的项

    新葡亰496net 69

      然后,需求选用多表删除来促成删除操作

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

    新葡亰496net 70

    目录 [1]预备工作 [2]多表更新 [3]两步更新[4]连接[5]Infiniti级表 前边的话 上一篇博文中介绍了子查询的...

    多表更新之一步到位

    上述更新操作参照其余表更新了本表,一共动用了三步:
    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修改成了数字类型

    至于选取外键:不必然要运用FOSportageIGN 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,那时就要求接纳到连年了

    总是的语法结构

    连接:
    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

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

    连日条件:
    运用ON关键字来设定连接条件,也足以行使WHERE来替代。
    普普通通选用ON关键字来设定连接条件,
    利用WHERE关键字展开结果集记录的过滤。

    内链接:展现左表及右表切合连接条件的记录

    新葡亰496net 71

    内连接

    实例:
    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

    左外连接:展现左表的满贯记下及右表切合连接条件的笔录

    新葡亰496net 72

    左外连接

    演示: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

    右外连接:呈现右表的整套记录及左表切合连接条件的记录

    新葡亰496net 73

    右外连接

    演示: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的记录,又多了三条分类的记录,右外连接指的是突显右表中的全部和左表中相符连接条件的记录

    那二种连接中内连接用的想对相当多

    多表连接

    商品表中设有商品归类和品牌
    贯彻三张表的接连:
    演示:

    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;
    

    能够看来那是有还原了笔者们最先原始的结果,不等同的是此番是经过多张表的总是完毕的,在此之前是经过一张表查询出来的

    实则表的连天仿佛外键的逆向操作,外键把表分开储存,连接把多张表连接起来查询

    关于连接的几点表达

    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将要找到切合连接推行规范的笔录后结束寻找更加的多的行。

    极端极分类表设计

    查看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 ;
    能够观看独有14个分类了,遵照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;
    

    就能够知见子类的数量了

    多表删除

    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;

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:前面一个学数据库之多表操作,前

    关键词:

上一篇:登录和卸载,关于sqlserver帐号被禁用问题

下一篇:没有了