MySQL高级操作

每日分享：

梦想成真之前，看上去总是那么遥不可及。

目录

一、数据库设计之三范式

二、外键SQL语句的编写

三、分组和聚合函数的组合使用

1. 数据准备

2. SQL语句编写

四、将查询结果插入到其他表中

1. 创建商品分类表

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

五、使用连接更新表中某个字段数据

六、创建表并给某个字段添加数据（类似与四、五的整合）

1. 创建品牌表

2. 更新goods表中的品牌信息

七、修改goods表结构

八、远程登录MySQL数据库

1. 注释掉bind-address

2. 将mysql数据库中的user表中的root用户的host改为%

一、数据库设计之三范式

范式：对设计数据库提出的一些规范，一般遵守三范式即可

• 第一范式（1NF）：强调的是列的原子性，即列不能够再分为其他几列
• 第二范式（2NF）：满足1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分
• 第三范式（3NF）：满足2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键A依赖于非主键B，非主键B依赖于主键的情况

第一范式：

说明：

• 这种表结构的设计就没有达到1NF，要符合1NF需要把contact字段拆分开

第二范式：

说明：

• 这种表结构设计就没有达到2NF，因为Discount（折扣），Quantity（数量）完全依赖于主键（OrderID），而UnitPrice（单价），ProductName（产品名称）只依赖于ProductID，所以OrderDetail不符合2NF

第三范式：

说明：

• 这种表结构设计就没有达到3NF，因为CustomerName，CustomerAddr，CustomerCity直接依赖的是CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键

E-R模型：

E-R模型即实体-关系模型，E-R模型就是描述数据库存储数据的结构模型

开发项目，一般先画出实体-关系模型（E-R模型）之后再根据三范式设计数据库表结构

E-R模型示例：

说明：

• 实体：用矩形表示，并标注实体名称
• 属性：用椭圆表示，并标注属性名称
• 关系：用菱形表示，并标注关系名称
  • 一对一
  • 一对多
  • 多对多

一对一：

说明：

• 关系也是一种数据，需要通过一个字段存储在表中
• 1对1关系，在表A或表B中创建一个字段，存储另一个表的主键值

一对多：

说明：

• 一对多的关系，在多的一方表（学生表）中创建一个字段，存储班级表的主键值

多对多：

说明：

• 多对多关系，新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

二、外键SQL语句的编写

外键约束：对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

对已经存在的字段添加外键约束：

alter table students add foreign key(c_id) references classes(id);

在创建数据表时设置外键约束：

创建学校表

create table school(

id int unsigned not null primary key auto_increment,

name varchar(10)

);

创建老师表

create table teacher(

id int unsigned not null primary key auto_increment,

name varchar(10),

s_id int not null,

foreign key(s_id) references school(id)

);

删除外键约束：

需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称

show create table teacher;

获取名称之后就可以根据名称来删除外键约束

alter table teacher drop foreign key 外键名;

之后再查看表创建语句，确定是否删除：

三、分组和聚合函数的组合使用 1. 数据准备

创建“京东”数据库

create database jing_dong charset=utf8;

使用“京东”数据库

use jing_dong;

创建一个商品goods数据表

create table goods(

id int unsigned not null primary key auto_increment,

name varchar(30) not null,

cate_name varchar(30) not null,

brand_name varchar(30) 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,'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 表示价格
• is_show 表示是否显示
• is_saleoff 表示是否售罄

2. SQL语句编写

查询类型cate_name为‘超极本’的商品名称、价格

select name,price from goods where cate_name='超极本';

显示商品的分类

select cate_name from goods group by cate_name;

或者

select distinct cate_name from goods;

求所有电脑产品的平均价格，并且保留两位小数

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

显示每种商品的平均价格

select cate_name,avg(price) from goods group by cate_name;

查询每种类型的商品中最贵、最便宜、平均价、数量

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

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

select * from goods where price > (select avg(price) from goods) order by price desc;

四、将查询结果插入到其他表中

目前只有一个goods表，我们想要增加一个商品分类信息（移动设备），只通过goods表无法完成商品分类的添加，那么如何实现添加商品分类信息的操作

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

1. 创建商品分类表

create table goods_cates(

id int unsigned not null primary key auto_increment,

name varchar(30) not null

);

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

查询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;

添加移动设备分类信息

insert into goods_cates(name) values('移动设备');

说明：

• insert into ... select ... 表示把查询结果插入到指定表中，也就是表复制

五、使用连接更新表中某个字段数据

上面已经创建了一个商品分类表（goods_cates），并完成了商品分类信息的插入，现在需要更新goods表中的商品分类信息，把商品分类名称改成商品分类id

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

查看goods表中的商品分类名称对应的商品分类id

select * from goods g inner join goods_cates gc on g.cate_name = gc.name;

将分类名称修改为对应商品的分类id

update goods g inner join goods_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

六、创建表并给某个字段添加数据（类似与四、五的整合）

之前完成了商品分类表（goods_cates）的创建和商品分类信息的添加以及把商品表（goods）中的商品分类名称改成了对应商品分类id，如果我们还想添加一个品牌（双飞燕）品牌信息，只通过goods表无法完成品牌信息的添加，那么该如何添加品牌信息的操作

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

1. 创建品牌表

查询品牌信息

select brand_name from goods group by brand_name;

通过create table ... select 来创建数据表并且同时插入数据

创建商品分类表，注意：需要对brand_name 起别名为name

create table goods_brands(

id int unsigned not null primary key auto_increment,

name varchar(30) not null) select brand_name name from goods group by brand_name;

说明：

• create table ... select 列名 ... 表示创建并插入数据

2. 更新goods表中的品牌信息

update goods g inner join goods_brands gb on g.brand_name=gb.name set g.brand_name=gb.id;

七、修改goods表结构

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

查看表结构

desc goods;

修改表结构

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

说明：

• alter table 可以同时修改多个字段

八、远程登录MySQL数据库

需要做两件事：

1. 注释掉bind-address

2. 将mysql数据库中的user表中的root用户的host改为%

到这里，信息更改已经完成，我们可以把Windows作为客户端远程登录ubuntu服务端：

在Navicat Premium中尝试连接，连接成功后发现可以查看ubuntu中的数据库信息