子查询与连接

  -- 创建一个数据表

  CREATE TABLE IF NOT EXISTS tdb_goods(
    goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_name  VARCHAR(150) NOT NULL,
    goods_cate  VARCHAR(40)  NOT NULL,
    brand_name  VARCHAR(40)  NOT NULL,
    goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
    is_show     BOOLEAN NOT NULL DEFAULT 1,
    is_saleoff  BOOLEAN NOT NULL DEFAULT 0
  );

1.CREATE ...SELECT

创建数据表同时将查询结果写入到数据表

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

根据上表的内容创建新表
crate table user
(

id int primary key auto_increment,
brand_name varchar(40) not null
)
select brand_name from tdb_goods;

2多表更新

将tdb_goods数据库中的brand_name的值 更新为tdb_good_brands的brand_id

//update tdb_goods inner join tdb_goods_brands on brand_name=brand_name set brand_name=brand_id;

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;



将tdb_goods表中的brand_name字段修改为brand_id字段
alter table tdb_goods change brand_name brand_id smallint unsigned not null;

3连接

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 JOIN 左外连接

RIGHT JOIN右外连接

 

 

 

无限分类

parent_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
  ); 
  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;
   
-- 查找所有分类及其子类

  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;

-- 查找所有分类及其子类的数目

  SELECT p.type_id,p.type_name,count(s.type_name) AS children_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;

 

一般引发子查询的有  比较运算符，in和not in   exists和not exists

连接一般分为左连接和右连接