MySQL学习笔记四

1.数据准备

创建数据表

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

插入数据

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

无限分类的数据表设计

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

2.子查询

a.子查询是指出现在其它SQL语句内的select语句

例如：select * from t1 where col1=(select t2 from col2)

b.子查询可以包含多个关键字和条件，如distinct、group by、order by、limit，函数等。

c.子查询的外层查询可以是select、insert、update、set或do

d.子查询可以返回标量、一行、一列或子查询

e.使用比较运算符的子查询：=、>、<、>=、<=、<>、！=、<=>，语法结构：operand  comparision_operator subquery

案例：

求商品的平均价格：

select avg(goods_price) from tdb_goods;

select round(avg(goods_price),2) from tdb_goods;

查询大于商品平均价格的商品：

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='超级本';

f.用any、some或all修饰的比较运算符

案例：

查询分类为超级本的价格，大于其中最小价的商品

 select goods_id,goods_name,goods_price from tdb_goods where goods_price > any (select goods_price from tdb_goods where goods_cate='超级本');

g.使用[not] ni子查询

语法结构：operand  comparision_operator subquery [not] in (subquery)

=any运算符与in等效

!=all或<>all运符与not in等效

h.使用[not] exists的子查询

如果子查询返回任何行，exists将返回true，否则为false

3.使用insert…select插入记录

创建“商品分类”表

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

将商品表中的分类写入进去

insert tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;

4.多表更新

update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;

多表更新一步到位（创建数据表的同时将查询结果写入到数据表）

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;

更新表tdb_goods

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;

修改商品表中列名字和类型

alter table tdb_goods
change goods_cate cate_id smallint unsigned not null,
change brand_name brand_id smallint unsigned not null;

5.连接查询

语法结构：

table_reference

{[inner | cross] join | {left | right}[outer] join}

table_reference

on conditional_expr

连接条件：

使用on关键词来设定连接条件，也可以使用where来代替

内连接inner join：

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

内连接案例：

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;

左外连接left outer join：

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

右外连接right outer join：

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

多表连接：

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;

6.无限分类表设计

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) 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 type_id;

7.多表删除

delete tbl_name[*] [,tbl_name[*]] ……

from table_references

[where where_condition]

案例：

删除表tdb_goods的重复记录

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;