记录查询操作

单表更新记录

插入记录三种方法

hhhhh

-- 查找重复记录

  SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;

-- 删除重复记录

  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;

修改数据表

1、无限极分类表设计自身连接

-- 无限分类的数据表设计

   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;

1、多表连接和两表连接类似：

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\G;

-- 查询所有商品的详细信息(通过左外连接实现)

   SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g

   LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

   LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

-- 查询所有商品的详细信息(通过右外连接实现)

   SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g

   RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

   RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

1、通常使用on关键字来设定连接条件，使用where关键字进行结果集记录的过滤

2、内连接只显示双方都有的记录

3、

-- 查询所有商品的详细信息(通过内连接实现)

   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\G;

1、table_subquery可以作为子查询使用在from字句中，这样的子查询必须为其赋予别名

在外键约束当中，在实际的开发过程中，我们很少使用物理的外键约束，很多都去使用逻辑的外键约束，因为物理的外键约束只有INNODB这种引擎才会支持，像我们另外的一种引擎MYISAM的引擎则不支持，反过来说，如果我想创建的数据表，假设存储引擎为MYISAM，而且又想使用外键约束的话，其实是不可能实现的，所以说，我们在实际的项目开发中，我们不去定义物理的外键，所谓的逻辑外键指的是就是我们在定义两张表的结构的时候，我们是按照存在的某种结构的方式去定义，但是不去使用FOREIGN KEY这个关键词来定义。

1、create……select：创建数据表的同时将查询结果写入到数据表，即将两步合为一步 

 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;

2、

-- 通过tdb_goods_brands数据表来更新tdb_goods数据表(错误)

  UPDATE tdb_goods  INNER JOIN tdb_goods_brands ON brand_name = brand_name

  SET brand_name = brand_id;

  -- Column 'brand_name' in field list is ambigous【含义不明确】

  -- 正确

  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;

3、需要修改tdb_goods的表结构

ALTER TABLE tdb_goods  

  CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

  CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

4、上述操作添加了外键，但并非物理外键。该种方法是添加外键的主要方法。

5、再插入几条记录：

-- 分别在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');

这里的12和4存在问题，因为在tdb_goods_cate 和tdb_goods_brand中无对应记录。

外键约束的参照操作

-- 创建“商品分类”表

  CREATE TABLE IF NOT EXISTS tdb_goods_cates(

    cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    cate_name VARCHAR(40)

  );

2、

-- 查询tdb_goods表的所有记录，并且按"类别"分组

  SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

3、

-- 将分组结果写入到tdb_goods_cates数据表

  INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

4、需要参照分类表去更新商品表