MySQL 练习题3

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);
创建表和数据

2. 求所有电脑产品的平均价格,并且保留两位小数,AVG,MAX,MIN,COUNT,SUM为聚合函数

1
SELECT  ROUND( AVG (goods_price),2)  AS  avg_price  FROM  tdb_goods;

 

3.查询所有价格大于平均价格的商品,并且按价格降序排序

1
2
3
4
5
SELECT  goods_id,goods_name,goods_price  FROM  tdb_goods
 
     WHERE  goods_price > ( SELECT  ROUND( AVG (goods_price),2)  AS  avg_price  FROM  tdb_goods)
 
ORDER  BY  goods_price  DESC ;

 

4.查询类型为“超记本”的商品价格 

1
SELECT  goods_price  FROM  tdb_goods  WHERE  goods_cate =  '超级本' ;

 

5.查询价格等于"超级本"价格的商品,并且按价格降序排列  

1
2
3
4
5
SELECT  goods_id,goods_name,goods_price  FROM  tdb_goods
 
   WHERE  goods_price  IN  ( SELECT  goods_price  FROM  tdb_goods  WHERE  goods_cate =  '超级本' )
 
ORDER  BY  goods_price  DESC ;

  

6.创建“商品类别”表

1
2
3
4
5
6
7
CREATE  TABLE  IF  NOT  EXISTS tdb_goods_cates(
 
     cate_id  SMALLINT  UNSIGNED  PRIMARY  KEY  AUTO_INCREMENT,
     
     cate_name  VARCHAR (40)
 
   );

 

7.查询tdb_goods表的类别记录,并且按"类别"分组

1
SELECT  goods_cate  FROM  tdb_goods  GROUP  BY  goods_cate;

 

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

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

 

9.通过tdb_goods_cates数据表来更新tdb_goods表中的'类别字段'

1
2
3
4
5
6
7
-- 1.通过内连接得到两个表的结果
select  from  tdb_goods  INNER  JOIN  tdb_goods_cates  ON  goods_cate = cate_name;
 
-- 2.通过上面的得到的临时表进行 类别字段更新
UPDATE  tdb_goods  INNER  JOIN  tdb_goods_cates  ON  goods_cate = cate_name
  
  SET  goods_cate = cate_id;

 

10.通过CREATE...SELECT来 创建[品牌]表 并且同时写入记录

1
2
3
4
5
6
7
8
9
10
11
-- 1.获得品牌名称
SELECT  brand_name  FROM  tdb_goods  GROUP  BY  brand_name;
 
-- 2.创建品牌表,并且插入品牌数据
   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;

 

11.通过tdb_goods_brands 品牌表 来更新 tdb_goods商品表

1
2
3
4
5
6
7
8
9
10
-- 错误<br>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  ON  g.brand_name = b.brand_name
 
SET  g.brand_name = b.brand_id;

 

12.查看tdb_goods的数据表结构 

1
DESC  tdb_goods;

 

13.通过ALTER TABLE语句修改商品表结构,goods_cate更新为cate_id, brand_name更新为brand_id

1
2
3
4
5
ALTER  TABLE  tdb_goods 
 
CHANGE goods_cate cate_id  SMALLINT  NOT  NULL ,
 
CHANGE brand_name brand_id  SMALLINT  NOT  NULL ;

 

14.分别在tdb_goods_cates(类别表)和tdb_goods_brands(品牌表)插入记录

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

 

15.在tdb_goods数据表写入任意记录

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

 

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

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

 

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

1
2
3
4
5
SELECT  goods_id,goods_name,cate_name,brand_name,goods_price  FROM  tdb_goods  AS  g
 
    LEFT  JOIN  tdb_goods_cates  AS  ON  g.cate_id = c.cate_id
 
    LEFT  JOIN  tdb_goods_brands  AS  ON  g.brand_id = b.brand_id;

 

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

1
2
3
4
5
SELECT  goods_id,goods_name,cate_name,brand_name,goods_price  FROM  tdb_goods  AS  g
 
   RIGHT  JOIN  tdb_goods_cates  AS  ON  g.cate_id = c.cate_id
 
   RIGHT  JOIN  tdb_goods_brands  AS  ON  g.brand_id = b.brand_id;

 

19.无限分类的数据表设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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);

 

20.查找所有分类及其父类(将自身作为临时表使用)

1
2
3
4
5
6
select  FROM  tdb_goods_types p1;
select  FROM  tdb_goods_types p2;
 
SELECT  p1.type_id,p1.type_name,p2.type_name  as  '父类'
     FROM  tdb_goods_types p1  LEFT  JOIN  tdb_goods_types p2
on  p1.parent_id = p2.type_id

  

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

1
2
3
4
5
   SELECT  FROM  tdb_goods  WHERE  goods_id  IN  (19,20);
 
-- INSERT ... SELECT实现复制
 
   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);

 

22.查找重复记录

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

 

23. 删除重复记录 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#方式一:
   1.查询重复记录,获得重复字段
   SELECT  goods_name  FROM  tdb_goods ROUP  BY  goods_name  HAVING  count (goods_name) >= 2
   
   2.通过重复字段进行删除
     -- 错误
     DELETE  FROM  tdb_goods  WHERE  goods_name  in ( SELECT  goods_name  FROM  tdb_goods
     GROUP  BY  goods_name  HAVING  count (goods_name) >= 2)
     
     -- [Err] 1093 - You can't specify target table 'tdb_goods' for update in FROM clause
     -- 不能在同一个表中即查询数据又删除数据
 
     -- 正确
     DELETE  FROM  tdb_goods  WHERE  goods_name  in ( SELECT  from  ( SELECT  goods_name  FROM  tdb_goods
         GROUP  BY  goods_name  HAVING  count (goods_name) >= 2) as  别名)
     注意: 使用临时表 将子查询包裹,并起个别名
 
#方式二:保留一条
     DELETE  FROM  tdb_goods  WHERE  goods_name  in ( SELECT  from  ( SELECT  goods_name  FROM  tdb_goods
         GROUP  BY  goods_name  HAVING  count (goods_name) >= 2) as  ss)
     and  goods_id  not  in ( SELECT  from  ( SELECT  goods_id  FROM  tdb_goods
         GROUP  BY  goods_name  HAVING  count (goods_name) >= 2) as  别名)

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值