mysql 练习

数据准备、基本查询

-- 创建数据库
create databse jing_dong charset=utf8;

-- 创建数据表
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(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);

-- 插入数据
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','笔记本','雷神','8499',default,default);
insert into goods values(0,'x550th 15.6寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240超级本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超极本','联想','4299',default,default);
insert into goods values(0,'','','','',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超极本','索尼','7999',default,default);
insert into goods values(0,'iPad mini 7.9英寸平板电脑','平板电脑','苹果','1988',default,default);
insert into goods values(0,'iPad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'iPad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414p 台式电脑 liunx','台式机','宏基','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'Mac pro 专业台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','IBM','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

-- 显示商品种类
select cate_name from goods group by cate_name;
select distinct cate_name from goods;

-- 显示每个种类的平均价格
select avg(price) from goods group by cate_name;

-- 查询每个分类中最高价格、最低价格、平均价格、数量：
select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

-- 查询价格大于平均价格的商品id,名字、价格，并排序
select id,name,price from goods where price>(select round(avg(price),2)from goods)order by price desc;

--查询每个种类中最高的价格：
select group_concat(name),max(price) from goods group by cate_name;

--查询每个种类中价格最贵的电脑信息：
select * from goods inner join
(
select cate_name,
max(price) as maxprice from goods group by cate_name
) as newgoods 
on maxprice=goods.price and newgoods.cate_name=goods.cate_name;

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

 

-- 拆为多个表

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

-- 查询goods表中的分类：
select cate_name from goods group by cate_name;

-- 将分组结果写入到goods_cates表中：
insert into goods_cates (name) select cate_name from goods group by cate_name;

--上面三个步骤可以合为一个,注意brand_name后面必须跟一个别名，且别名需要跟新表中要添加到的字段名相同：
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null 
) select brand_name as name from goods group by barnd_name;

-- 同步数据表
-- 通过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;

-- 修改表结构
-- 查看goods表结构发现cate_name、brand_name对应的类型是varchar，但是存储的都是数字：
desc goods;

-- 通过alter table修改表结构,alter table table_name 语句1，语句2，语句3....;
alter table goods 
change cate_name cate_id int unsigned not null,

-- 外键 foreign key 外键会非常影响数据库效率，一般尽量不用，可以在应用程序层面约束数据，代替外键
-- 一个字段添加外键后，插入数据就受到约束，以下cate_id添加了外键goods_cate(id)
-- 如果向cate_id中添加goods_cates(id)中不存在的数字，会报错
alter table goods add foreign key (cate_id) references goods_cates(id); 

--如何取消外键约束
-- 1、查看外键名称，其有系统自动生成的
show caeate table goods;
-- 以下看到的‘goods_ibfk_4’就是外键名
 CONSTRAINT `goods_ibfk_4` FOREIGN KEY (`brand_id`) REFERENCES `goods_brands` (`id`),
-- 2、删除外键
alter table goods drop foreign key 外键名