1.準備數據

數據準備

create database jing_dong charset = utf8mb4;

創建一個商品goods數據表：

create table goods( id int unsigned primary key auto_increment not null, name varchar(150) not null, cate_name varchar(40) not null, brand_name varchar(50) not null, price decimal(10,3) not null default 0,is_show bit not null default 1, is_saleoff bit not null default 0);

分別表示id，名字，類別名，品牌名，價格，是否顯示，是否售罄。

插入數據：

insert into goods values (0,'r510vc 15.6英寸筆記本','筆記本','華碩','3399',default,default), (0,'y400n 14.0英寸筆記本電腦','筆記本','聯想','4999',default,default), (0,'g150th 15.6英寸筆記本','游戲本','雷神','8499',default,default), (0,'x550cc 15.6英寸筆記本','筆記本','華碩','2799',default,default), (0,'x240 超級本','超級本','聯想','4880',default,default), (0,'u330p 13.3英寸超級本','超級本','聯想','4299',default,default), (0,'svp13226scb 觸控超級本','超級本','索尼','7999',default,default), (0,'ipad mini 7.9英寸平板電腦','平板電腦','蘋果','1998',default,default), (0,'ipad air 9.7英寸平板電腦','平板電腦','蘋果','3388',default,default), (0,'ipad mini 配備retina顯示屏','平板電腦','蘋果','2788',default,default), (0,'ideacentre c340 20英寸一體電腦','臺式機','聯想','3499',default,default), (0,'vostro 3800-r1206 臺式電腦','臺式機','戴爾','2899',default,default), (0,'imac me086ch/a 21.5英寸一體電腦','臺式機','蘋果','9188',default,default), (0,'at7-7414lp 臺式電腦 linux','臺式機','蘋果','3699',default,default), (0,'z220sff f4f06pa工作站','服務器/工作站','惠普','4288',default,default), (0,'powereedge ii服務器','服務器/工作站','戴爾','5388',default,default), (0,'mac pro專業級臺式電腦','服務器/工作站','蘋果','28888',default,default), (0,'hmz-t3w 頭戴顯示設備','筆記本配件','索尼','6999',default,default), (0,'商務雙肩背包','筆記本配件','索尼','99',default,default), (0,'x3250 m4機架式服務器','服務器/工作站','ibm','6888',default,default), (0,'商務雙肩背包','筆記本配件','索尼','99',default,default);

查看數據表：

2. SQL語句實戰：

1查找超級本：select * from goods where cate_name='筆記本'; select name,price from goods where cate_name='筆記本'; select name as 商品名稱,price as 商品價格 from goods where cate_name='筆記本';

2查看品牌的分類：select distinct brand_name from goods;select brand_name from goods group by brand_name;

?

select brand_name,group_concat(name) from goods group by brand_name;

?group by功能對于后續更加強大。

求所有電腦的平均價格： select avg(price) from goods;求所有電腦的平均價格并保留2位小數：select round(avg(price),2) from goods;

顯示每種商品的平均價格：select cate_name,avg(price) from goods group by cate_name;

顯示每種類型的商品中，最貴、最便宜、平均價、數量： select cate_name,max(price) as 最貴,min(price) as 最便宜,avg(price) as 平均,count(*) as 數量 from goods group by cate_name;

• ?查詢所有價格大于平均價格的商品，并且按價格降序排序：

select id,name,price from goods where price > (select round(avg(price),2) as avg_price from goods) order by price desc;

• ?查詢每種最貴的商品的信息：

首先得到每種最貴價格電腦：

select cate_name,max(price) from goods group by cate_name;

?然后將最大值表和原表關聯【可以inner join 或者 left join? ?on】

left join on方法：

select * from (select cate_name,max(price) as max_price from goods group by cate_name) as g_new left join goods as g on g_new.cate_name=g.cate_name and g_new.max_price=g.price;

?來寫一個綜合的：

select * from goods inner join( select cate_name,max(price) as max_price,min(price) as min_price,avg(price) as avg_price,count(*) as num from goods group by cate_name) as goods_new on goods_new.cate_name=goods.cate_name and goods_new.max_price=goods.price;

?如果出現相同的價格則需要，對品牌進行排序（）；

select g_new.cate_name,g.name,g.price from (select cate_name,max(price) as max_price from goods group by cate_name) as g_new left join goods as g on g_new.cate_name=g.cate_name and g_new.max_price=g.price order by g_new.cate_name;

顯示信息篩選一下：

3. 拆分為多個表（商品分類表）

所有數據都在一個表中會導致修改麻煩，

create table if not exists goods_cates(id int unsigned primary key auto_increment,name varchar(40) not null);

if not exists 如果表存在則不建立，不存在則建立。

3.1 去重復存儲---分組：

查詢goods表中商品的種類

select cate_name from goods group by cate_name;

把name插入即可；

• ?將分組結果寫入到goods_cates數據表中

insert into goods_cates(name) select cate_name from goods group by cate_name;

**注意這里不用values

select * from goods_cates;

?之后把主表中的cate_name 改成新建的goods_cates的Id

SET是SQL Server 中對已經定義的變量賦值的方式，經常與update語句一起使用。

3.2同步表數據

通過goods_cates數據表來更新goods表

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

關聯修改成功：

?3.3 修改表結構

?同時發現對應表類型不同

• ?更改表結構：

alter table goods change cate_name cate_id int unsigned not null, change brand_name brand_id int unsigned not null;

寫：int unsigned? 保證表結構相同都是 int類型且數值范圍相同，

• ?設置外鍵

關聯cate_id和表goods_cate的id

外鍵約束也可以在修改表時添加，但是添加外鍵約束的前提是：從表中外鍵列中的數據必須與主表中主鍵列中的數據一致或者是沒有數據。

在修改數據表時添加外鍵約束的語法格式如下：

ALTER TABLE <數據表名> ADD CONSTRAINT <外鍵名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

• 刪除外鍵約束

當一個表中不需要外鍵約束時，就需要從表中將其刪除。外鍵一旦刪除，就會解除主表和從表間的關聯關系。
刪除外鍵約束的語法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外鍵約束名>;

約束如下：

alter table goods add foreign key (cate_id) references goods_cates(id);

通過cate_id關聯到goods_cates中的id。

這樣保證數據唯一性，插入數據不會越界。

3.4 修改brands_name品牌如上述

• 創建表和插入數據一起操作

create table goods_brands(id int unsigned primary key auto_increment,name varchar(40) not null) select brand_name as name from goods group by brand_name;

需要注意：brand_name as name? ?需要與創建一致

• ?同步數據（關聯）

update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

• ?修改表結構【修改名字+類型】

alter table goods change brand_name brand_id int unsigned not null;

• ?添加外鍵

alter table goods add foreign key (brand_id) references goods_brands(id); show create table goods;

可以看到約束（外鍵）

如何取消外鍵約束呢？

show create table goods;alter table goods drop foreign key 外鍵名稱;

外鍵名稱

alter table goods drop foreign key goods_ibfk_1;

在實際開發中，很少使用外鍵約束，會極大的降低表更新效率；

mysql存儲在硬盤上，性能比較差，cpu>內存>固態>機械硬盤：

總結

以上是生活随笔為你收集整理的MySQL【四】---案例实战｛拆分多表、外键创建等｝的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。