外键SQL语句的编写及实例

目录

外键SQL语句的编写

1.外键约束作用

2.对于已经存在的字段添加外键约束

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

4.删除外键约束

5.小结

实战演练 - 分组和聚合函数的组合使用

1.数据准备

2. Sql语句演练

3.将查询结果插入到其它表中

---

外键SQL语句的编写

1.外键约束作用

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

2.对于已经存在的字段添加外键约束

 ---- 为c_id字段添加外键约束
 alter table students add froeign key(c_id) references classes(id);

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

 ---- 创建学校表
  create table school(
     -> id int not null primary key auto_increment,
     -> name varchar(10)
     -> );
     
  ----- 创建老师表
  create table teacher(
     -> id int not null primary key auto_increment,
     -> name varchar(10),
     -> s_id int not null,
     -> foreign key(s_id) references scholl(id)
     -> );

4.删除外键约束

 --- 需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称
 show create table teacher;
 ​
 --- 获取名称之后就可以根据名称来删除外键约束
 alter table teacher drop foreign key 外键名;

5.小结

• 添加外键约束：alter table 表名 add foreign key(外键字段) references 主表(主键字段);

• 删除外键约束: alter table 表名 drop foreign key(外键名)

实战演练 - 分组和聚合函数的组合使用

1.数据准备

 -- 创建‘京东’数据库
 create database jing_dong charset=utf8;
 ​
 ​
 --- 使用“京东”数据库
 use jing_dong;
 ​
 --- 创建一个商品goods数据表
  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,'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);

2. Sql语句演练

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

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

2.显示商品的分类

 select cate_name from goods group by cate_name;

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

round(x,d) x指要处理的数据，d是要保留几位小数

 -- avg(price)
 select round(avg(price),2) as avg_price from goods;

4.显示每种商品的平均价格

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

5.查询每种类型的商品最贵，最便宜，平均价，数量

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

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

 --- price > avg(price) desc
 select * from goods where price > (select round(avg(price),2) as avg_price from goods) order by price desc;

3.将查询结果插入到其它表中

目前只有一个goods表，我想要增加一个商品分类信息，比如：移动设备

只通过goods表无法完成商品分类的添加，那么如何实现添加分类信息的操作？

答案：

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

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

3.1 创建商品分类表

 -- 创建商品分类表
 create table good_cates(
     id int unsigned not null primary key auto_increment,
     name varchar(50) not null
 );

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

 --- 查询goods表中商品的分类信息
 select cate_name from goods group by cate_name;
 ​
 ---- 将查询结果插入到good_cate表中
 insert into good_cates(name) select cate_name from goods group by cate_name;
 ​
 ​
 --- 添加移动设备分类信息
 insert into good_cates(name) values('移动设备');

3.3 更新goods表种的商品分类信息

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

 --- 查看goods表中的商品分类名称对应的商品分类id
 select * from goods inner join good_cates on goods.cate_name = good_cates.name;
 ​
 update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name = gc.id;