Ubuntu MySQL8.0 SQL语句操作(三)

数据的准备和基本查询
create database jingdong char set = utf8;

use jingdong;

商品表

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 #是否售馨
);

订单表

create table orders(
id int unsigned primary key auto_increment not null ,
order_data_time datetime not null ,
customers_id int unsigned not null
);

设置外键

alter table orders add foreign key (customers_id) references customers(id);

顾客表

create table customers(
id int unsigned primary key auto_increment not null ,
name varchar(150) not null ,
address varchar(150) not null ,
tel varchar(20) not null ,
passwd varchar(30) not null
);

订单详情表

create table order_detail(
id int unsigned auto_increment primary key not null ,
quality int unsigned not null ,
order_id int unsigned not null ,
good_id int unsigned not null
);
alter table order_detail add foreign key (order_id) references orders(id);
alter table order_detail add foreign key (good_id) references goods(id);

desc orders;

desc goods;

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

select * from goods;

select * from goods where cate_name=“超级本”;

select * from goods where price < 3000;

显示种类

select distinct cate_name from goods;

select distinct cate_id from goods;

select cate_name from goods group by cate_name;

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

select round(avg(price), 4) from goods;

select cate_name, avg(price) from jingdong.goods group by cate_name;
select cate_name, avg(price), min(price), max(price), count(*), group_concat(name) from jingdong.goods group by cate_name;

查询所有价格大于平均价格的商品，并且按照降序排列

思路

select round(avg(price), 4) from goods;
select id, name, price from goods where price > 5570.5741;
select id, name, price from goods where price > (select round(avg(price), 4) from goods) order by price desc;

查询每种类型电脑中最贵的电脑信息

select cate_name, max(price), group_concat(cate_name, " ", name), count(*) from goods group by cate_name; #说明：进行分组的时候使用group by,前面的select查询的信息必须要有进行分组的字段依据.
select * from goods group by cate_name; #错误

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

#左连接查询
select * from (select cate_name, max(price) as max_price from goods group by cate_name) as new_goods_info
left join goods on new_goods_info.cate_name = goods.cate_name and new_goods_info.max_price = goods.price;

select * from (select cate_name, max(price) as max_price from goods group by cate_name) as new_goods_info
right join goods on new_goods_info.cate_name = goods.cate_name and new_goods_info.max_price = goods.price;
#一个表不方便管理

十分注意：通过另一个表的字段来存储一个表的主键的时候，两个字段的数据类型必须相同

创建goods_cates表并直接插入值

create table if not exists goods_cates(
id int unsigned primary key auto_increment not null,
name varchar(50) not null
);
select cate_name as name from goods group by cate_name;

#间接插入值
insert into goods_cates (name) select cate_name from goods group by cate_name; #为指定字段插入值。注：当把查看的一堆数据插入到一个字段中时，省略value

select * from goods_cates;

同步表数据 通过goods_cate表来更新goods表

十分注意：通过另一个表的字段来存储一个表的主键的时候，两个字段的数据类型必须相同

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

#增加产品类型
insert into goods_cates (name) values (‘路由器’),(‘交换机’),(‘网卡’);

select * from goods;
select * from goods_cates;

desc goods;
desc goods_cates;

alter table goods change cate_name cate_id int unsigned not null; #change表示重命名改动

给goods表cate_id 字段添加外键进行约束

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

insert into goods (name, cate_id, brand_name, price) values (‘LasterJet Pro P1606dn 黑白激光打印机’, 12, 4, ‘1489’);

创建商品品牌数据表 先查询紧接着插入 注意name值要一样

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

select * from goods_brand;

同步数据

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

desc goods;

修改表结构 g.brand_name与b.id字段的数据类型要一致

alter table goods change brand_name brand_id int unsigned not null;

可以同时修改多个字段

alter table goods

change brand_name brand_id int unsigned not null,

change cate_name cate_id int unsigned not null;

设置外键

alter table goods add foreign key (brand_id) references goods_brand(id);

show create table goods;

在实际开发中很少使用外键 外键的使用会极大降低数据库的更新的效率 可以禁止使用外键 真正开发过程中约束可以通过语法逻辑进行控制

取消外健 首先获取外键约束的名称 通过show create table goods;来获取 然后通过获取的名称来删除外健 外健名称在show create table goods;生成的语句里面

alter table goods drop foreign key goods_ibfk_2;

select * from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brand as b on g.brand_id=b.id;
select g.*, c.name as cate_name, b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brand as b on g.brand_id=b.id order by price;

select * from goods;