Mysql数据库综合演练

一、演练-mysql高级使用

 

  我们接下来通过一个小型的演练来操作mysql中的高级SQL语句使用。

希望大家通过这个演练可以让大家掌握mysql的高级SQL语句并且也顺便复习以下之前的mysql基础语句知识。毕竟高级语句是建立在基础语句之上的。

 

1.数据准备

// 创建“京东”数据库

create database jing_dong;



// 使用京东数据库

use jing_dong



// 创建一个商品goods数据表

       create table goods(

id int unsigned primary key auto_increment not null,

name varchar(100) not null,

care_name varchar(40) not null,

brand_name varchar(40) not null,

price decimal(10,3) not nulle default 0,

is_show bit not null defalut 1,

is_saleoff bit not null default 0

);



// 向goods表中插入数据



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,'x550cc 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,'svp13226scb 触控超极本','超级本','索尼','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 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);

insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);

insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','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);

 

 

表结构说明：

• id 代表主键 无符号整数类型 自增 非空
• name 表示商品名称 字符型 非空
• cate_name 表示分类名称 字符型 非空
• brand_name 表示品牌名称 字符型 非空
• price 表示价格 浮点型 10位数其中保留3位小数 默认值为0
• is_show 表示是否显示 二进制整型 非空 默认为1
• is_saleoff 表示是否售完 二进制整型 非空 默认为0

 

好了 表已经介绍和准备完成了，接下来我们开始实际演练吧

2.SQL语句练习

1. 查询类型cate_name为”平板电脑的”的商品名称和价格
   select name,price from goods where cate_name = “平板电脑”;
   
   显示商品的分类
   select cate_name from goods group by cate_name;
   
   求所有电脑产品的平均价格，并且保留两位小数
   select name,round(avg(price),2) as “平均价格” from goods;
   
   显示每种商品的平均价格
   select cate_name,avg(price) from goods group by cate_name
   
   显示每种类型的商品中最贵、最便宜、平均价和数量
   select cate_name,avg(price),max(price),min(price),count(*) from goods group by cate_name;
   
   显示所有价格大于平均价格的商品，并且按价格降序排序
   select * from goods price > (select avg(price) from goods) order by price desc;

    

      以上都是咱们之前学习过的，大家自行消化，没有什么难度。

              接下来咱们来做一些SQL高级语句的演练题

• 思考 2

目前只有一个goods表，我们想要增加一个商品分类信息，比如移动产品这个分类信息，只通过goods表无法完成商品分类的添加，那么如何时间添加商品分类信息的操作呢？

答案：

1.创建一个商品分类表，把goods表中的商品分类信息添加到表中

2.将goods表中的分类名称更改为商品分类表中对应的分类id

    

（1）创建一个商品分类的表

create table good_cates(

id int primary key auto_increment not null,

name varchar(40) not null

);

（2）把goods表中的商品分类添加到商品分类表

    // 查询goods表中商品的分类信息

  select cate_name from goods group by cate_name;

  //将查询结果插入到good_cates表中

 Insert into good_cates (name) select cate_name from goods group by cate_name;



// 添加移动产品分类信息

Insert into good_cates(name) values (“移动产品”);

 

说明：insert into 表名(指定字段1,指定字段2,…) select 查询结果

可以实现把select语句的查询结果插入到指定表中，相当于是实现了表复制。

 

     ●思考 2

刚才我们创建了一个商品分类表(good_cates)，并且完成了商品分类信息的插入，现在我们需要更新goods表中的商品分类信息，把商品分类名称改成商品分类id。

接下来我们来实现第二部操作：

将goods表中的分类名称更改为商品分类表中对应的分类id

// 把 表1 inner join 表2 on 条件 看作是一张虚表 一个整体

update goods as a inner join good_cates as b on a.cate_name = b.name set a.cate_name=b.id

说明：update…join…set 语句可以实现多个表中的更新，也就是连接更新表中数据。

 

 ●思考 3

假如我们想要添加一个品牌，比如：燕双鹰这个品牌信息，只通过goods表无法完成品牌信息的添加，那么如何实现品牌信息添加的操作呢？

答案:

1. 创建一个品牌表，把goods表中的品牌信息添加到该表。
2. 对goods表中的品牌名称更改成品牌表中对应的品牌id

 

（1）创建品牌表

create table good_brands(

id int unisgned primary key auto_increment not null,

name varchar(40) not null)

select brand_name as name from goods group by brand_name;



（2）更新goods表中的品牌信息



update goods as a join good_brands as b on a.brand_name = b.name set a.brand_name = b.id;

说明：创建表并且给字段插入数据使用：create table …select 语句

但是select 语句中的查询出来的字段名字如果在新建表中没有，则需要把字段名设置别名为新建表中的字段名，否则mysql找不到。

 

●思考 4

目前我们已经把good表中的商品分类和品牌信息改成了商品的分类id和品牌id，接下来我们把cate_name和brand_name字段分别改称cate_id和brand_id字段，类型都改成int类型

 

（1）更改表结构

alter table goods change cate_name cate_id int ,change brand_name brand_id int;

说明：同时修改多个表结构和数据类型的时候可以使用

alter table change…,change… 多个修改字段之间使用逗号分隔。