Mysql笔记3

背景：两个表，students、classes

– 子查询 select …. select

-- 标量子查询  子查询得到的结构 是一行一列
-- 子查询的语句 必须在小括号之内
-- 查询学生的平均身高
select avg(height) from students;

-- 查询出高于平均身高的信息
select * from students where height > 172;(ps:avg=172)

select * from students where height > (select avg(height) from students);



-- 列级子查询  查询的结果是一列多行
-- 查询哪些班级有学生
-- 主查询: 班级名字
select classes.name from classes where id in (select distinct cls_id from students);

-- 列级子查询对应的关键字 in

-- 查找哪些班级没有学生
select classes.name from classes where id not in (select distinct cls_id from students);

-- 也可以通过内连接查询实现
select classes.name from classes inner join students on classes.id = students.cls_id;
select distinct classes.name from classes inner join students on classes.id = students.cls_id; 


-- 行级子查询 

-- 表级子查询

-- 查询班级上身高最高的学生的信息
select * from students where height = (select max(height) from students);
select * from students where age = (select min(age) from students);

– 数据库备份与恢复
– 备份
mysqldump liunx下
mysqldump -uroot -p 备份的数据库名字 > xxx.sql

-- 恢复  需要退出mysql终端交互环境
1. 创建一个数据库: demo
2. mysql -uroot -p demo < xxx.sql

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

-- E-R模型

E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表
R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多
关系也是一种数据，需要通过一个字段存储在表中
实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值

– 类似京东商城
– 求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) from goods;

– 查询所有价格大于平均价格的商品，并且按价格降序排序
– 标量子查询
select * from goods where price > (select round(avg(price),2) from goods) order by price desc;

– 查询类型cate_name为 ‘超级本’ 的商品名称、价格
select name, price from goods where cate_name = “超级本”;

– 显示商品的种类
select cate_name from goods group by cate_name;

– 显示每种商品的平均价格
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 inner join (select cate_name, max(price) as max_price, min(price),avg(price), count(*) from goods group by cate_name) as temp on goods.cate_name = temp.cate_name and goods.price = temp.max_price;

– insert … select
insert into goods_cates (name) select cate_name from goods group by cate_name;

– 根据goods_cates 表 更新goods 表 goods.cate_name = goods_cates.name
update goods inner join goods_cates on goods.cate_name = goods_cates.name set goods.cate_name = goods_cates.id;

– 修改表结构
alter table goods change cate_name cate_id int unsigned not null;

– brand_name 品牌名称
– 创建表 并且插入数据 一步到位
– 查询的字段名需要创建表的字段名一致 才能够一一对应
– create … select
– create table goods_brands(
– id int unsigned primary key auto_incrment not null,
– name varchar(40) not null
– ) select brand_name from goods group by brand_name;

create table goods_brands(
id int unsigned primary key auto_increment not null,
name varchar(40) not null
) select brand_name as name from goods group by brand_name;
– 字符串某些情况下也可以存储到int 中 “56” -> int 错误:”56bnc” xx–> int

— 根据goods_brand表 来更新 goods表
update goods inner join goods_brands on goods.brand_name = goods_brands.name set goods.brand_name = goods_brands.id;

– 显示商品的完整信息
– inner join
– 多表的连接 不要添加 逗号 和 and
select * from goods as g inner join goods_cates as c on g.cate_id = c.id inner join goods_brands as b on g.brand_id = b.id;

– left join 左外连接查询
select * from goods as g left join goods_cates as c on g.cate_id = c.id left join goods_brands as b on g.brand_id = b.id;
– 以最左边的表做主表 依次向后连接其他的表

– 外键
– 对于已经存在的表 设置外键约束–> 更新
– goods_brands 必须存在 并且有id这个字段
alter table goods add foreign key (brand_id) references goods_brands(id);

alter table goods add foreign key (cate_id) references goods_cates(id);

– 删除外键约束
alter table goods drop foreign key goods_ibfk_1;

– 索引 提高查询的效率 新华字典

[1,5,8,10,20,50,60]
– 遍历查找 时间复杂度: O(n) 最低的效率
– 二分查找 有序的序列: O(logn) 2 ^ n = 8 –> log8 = 3 8 / 2/ 2/ 2 = 1 –> 3 42亿 需要32查询
– 数据库: 比logn 时间复杂度还要低: B+树 多路查找的平衡树
– 创建表的时候 直接设置外键约束

– 学习在python调用 mysql
py3.0 pymysql
py2.0 mysqldb

insert into goods (name,cate_id,brand_id,price)
values(‘惠普LaserJet Pro P1606dn 黑白激光打印机’, 12, 4,’1849’);