数据库学习(十)— 查询演练
查询演练的数据表以及相应数据创建
drop table if exists goods;
create table goods
(
id int unsigned primary key auto_increment,
name varchar(150),
cate varchar(40),
brand_name varchar(40) ,
price decimal(10,3) default 0,
is_show bit default 1,
is_saleoff bit 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, 'x55occ 15.6英寸笔记本', '笔记本', '华硕', '2799', default, default);
insert into goods values(0, '×240超级本', '超级本', '联想', '4999', default, default);
insert into goods values(0, 'u330p 13.3J寸超级本', '超级本', '联想', '4299', default, default);
insert into goods values(0, 'swp13226scb 触控超极本', '超级本',"索尼", "7999" ,default,default);
insert into goods values(0, 'ipad mini 7.9英寸平板电脑', '平板电脑', '苹果', '1998', 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 3860-r1286台式电脑', '台式机', '戴尔', '2899', default ,default);
insert into goods values(0, 'imac meo86ch/a 21.5英寸一体电脑', '台式机','苹果','9188',default ,default);insert into goods values(0, 'at7-74141p 台式电脑 linux ', '台式机', '宏基', '3699', default, default);
insert into goods values(0, 'z220sff f4f86pa工作站', '服务器/工作站','惠普', '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, 'x3250m4机架式服务器', '服务器/工作站', ' ibm', '6888', default,default);
insert into goods values(0, 'hz-t3w头戴显示设备', '笔记本配件', '索尼', '6999', default , default);
insert into goods values(0, '高务双肩背包', '笔记本配忤', '索尼', '99', default, default);
1.1 查询演练
?例1: 求所有电脑产品的平均价格,并保留2位小数
select round(avg(price), 2) from goods
?例2: 查询所有价格大于平均价格的商品,并且按价格降序排序
select * from goods
where price > (select avg(price) from goods)
order by price desc
?例3: 查询类型为超级本的商品价格
select price from goods where cate = '超级本'
?例4: 查询价格大于或等于’超级本‘的商品价格,并且按价格降序排序
select * from goods
where price >=any(select price from goods where cate = '超级本' )
order by price desc
?知识点:
子查询返回的结果
- 只有一个值: 用比较符号
- 返回多值:用in, some,any
1.2 数据分表——更新商品表
?知识点:
我们创建的数据表等数据库数据都被放在电脑硬盘中:
若表中写一堆重复数据,也会存在硬盘中,就会造成空间的浪费,及造成数据冗余(数据重复)。如图:
数据冗余:易造成空间浪费,若该字段的某个字符更新,对其重复数据修改耗时。不利于管理
对于冗余的数据:不会与常规数据写一起,而是单独列表,以后修改重复字段的内容,只对单独表进行修改,不会造成原表的大幅度修正。如图:
创建表并且插入语句
㈠、创建类型表并将数据插入语句。(方法一)
①、创建类型表
-- 创建类型表
-- create table goods_cate(
-- cate_id int unsigned primary key auto_increment,
-- cate_name varchar(10)
-- )
②、去重
-- 去重,查询goods表中的所有焦虑,并且按’类别‘分组
-- select distinct cate from goods
select cate from goods group by cate
③、将分组结果,插入到数据表
-- 插入数据,将分组结果写入到goods_cate数据表
insert into goods_cate(cate_name) select cate from goods group by cate
㈡、 创建品牌表(用另外一种方法,方法二)
创建品牌表的同时插入数据,根据相同名称插入字段,若字段名称不同不能直接插入到表中。
通过create ——slect 来创建数据表并且同时写入记录,一步到位
-- 创建品牌表的同时插入数据,根据相同名称插入字段,若字段名称不同不能直接插入到表中。
--通过create ——slect 来创建数据表并且同时写入记录,一步到位
create table goods_brands(
brands_id int unsigned primary key auto_increment,
brand_name varchar(10)
) select distinct brand_name from goods
?知识点:
创建表若创建只2个字段,若在创建表语句的尾部添加select,select意思是把查询出来的数据插入到表中:插入时就会根据select查询返回的列名,到表中找对应的字段看能不能找到相同的字段,若无法找到新建一列,若找到相同字段则直接插入到原先创建的。
此处:
查询时返回的字段名称必须与创建表中的字段一样,跟谁一样查询的数据就插入到那个字段中
㈢、方法三:快速备份表
①、若对品牌表进行备份,快速备份一张表
-- 若对品牌表进行备份,快速备份一张表
create table brand_bak select * from goods_brands
②、 第二步:
商品表还是记录着还是名称还没有变化,接着更改商品表,将冗余字段的数据变成编号
-- 通过goods_cate数据表来更新商品表
通过连接查询,把类型所对应的编号查出来,只需要把select * from改为update
select * from goods
inner join goods_cate on goods.cate = goods_cate.cate_name
update goods
inner join goods_cate on goods.cate = goods_cate.cate_name
set goods.cate = goods_cate.cate_id
-- 通过goods_brands数据表来更新商品表的品牌信息
update goods
inner join goods_brands on goods.brand_name = goods_brands.brand_name
set goods.brand_name = goods_brands.brands_id
?小技巧
查看goods的数据表结构会发现cate 和brand_name对应的类型为 varchar但是存储的都是字哥串修改数据表结构, 因为只是把goods表中类型和品牌的值改了,若让一般人来看,就不知道是什么意思了,就需要把冗余字段名修改成相应的cate_id, brands_id。
把cate字段改为cate_id且类型为int unsigned。把brand_name字段改为brands_id且类型为int unsigned
mysql修改字段名称的方法: 执行【ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;】 语句即可修改字段名称。
alter table goods change cate cate_id int unsigned; alter table goods change brand_name brands_id int unsigned;
1.3 更新完后查询 --连接查询
若需要商品的所有信息,就需要连接查询。
select goods.name, goods_cate.cate_name, goods_brands.brand_name from goods
inner join goods_cate on goods_cate.cate_id = goods.cate_id
inner join goods_brands on goods_brands.brands_id = goods.brands_id
说明:
此处使用左连接和右连接的数据结果与内连接一样,
对于左连接,没有商品是对应不到品牌或类型。没有存在商品里面有的类型在类型表找不到
对于右连接一样,没有品牌或类型对应不到商品。也没有类型表里面有多余的类型在商品里面没有。
对于左连接和右连接看效果,分别在good_scates 和 goods_branwus表中插入记录
insert into goods_cate(cate_name) values('路由器'),('交换机'),('网卡');
insert into goods_brands(brand_name) values('海尔'),('清华同方'),('神舟');
在goods数据表中写入任意数据。
insert into goods(name, cate_id, brands_id, price)
values('LaserJet Pro P1606dn 黑白激光打印机', '12', '4', '1894');
?例1: 查询所有商品的详细信息(通过内连接)
select goods.name, goods_cate.cate_name, goods_brands.brand_name from goods
inner join goods_cate on goods_cate.cate_id = goods.cate_id
inner join goods_brands on goods_brands.brands_id = goods.brands_id
?例2: 查询所有商品的详细信息(通过左连接)
select goods.name, goods_cate.cate_name, goods_brands.brand_name from goods
left join goods_cate on goods_cate.cate_id = goods.cate_id
left join goods_brands on goods_brands.brands_id = goods.brands_id
?例3: 查询所有商品的详细信息(通过右连接)
select goods.name, goods_cate.cate_name, goods_brands.brand_name from goods
right join goods_cate on goods_cate.cate_id = goods.cate_id
right join goods_brands on goods_brands.brands_id = goods.brands_id
?知识点:
多个表在连接的时候,最终数据到底把哪个显示的更全一些,对于整体来说,看最后一个join,
写right把最后一个数据表全部展示出来,
写left把除了最后一个数据表,之前数据表连接的结果作为整体表看具体情况全部展示出来
最后看,具体要查询的情况。