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
b
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
c
ON
g.cate_id = c.cate_id
INNER
JOIN
tdb_goods_brands
AS
b
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
c
ON
g.cate_id = c.cate_id
LEFT
JOIN
tdb_goods_brands
AS
b
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
c
ON
g.cate_id = c.cate_id
RIGHT
JOIN
tdb_goods_brands
AS
b
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
别名)
|
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
