数据库学习(十)— 查询演练

查询演练的数据表以及相应数据创建 

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;

 参考:mysql修改字段名称的方法 - MySQL数据库 - 亿速云

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把除了最后一个数据表,之前数据表连接的结果作为整体表看具体情况全部展示出来

        最后看,具体要查询的情况。

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>