mysql 基本操作快速复习（三）

 
 

  
  学习python和数据分析推荐地方： www.lynda.com  
 
 
获取lynda官网会员的方式：http://item.taobao.com/item.htm?id=557746408785


-- 子查询 select .... select 
	-- 在一个select 语句中有嵌套了一个select 语句
	-- 子查询语句 需要出现在 小括号之内
	-- 标量子查询  子查询得到的结构 是一行一列
	-- 查询学生的平均身高
	select avg(height) from students;
	-- 查询出高于平均身高的信息
	select * from students where height > 172.64;

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


	-- 列级子查询  查询的结果是一列多行
	-- 使用 in 关键字 表示一个不连续的范围
	-- 查询年龄是18或者34的学生
	select * from students where age in (18,34);
	select * from students where age in (select age from students where age = 18 or age = 34);
	-- 查询哪些班级有学生  查找的是班级的名字
	select classes.name from classes where classes.id in (select students.cls_id from students);

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

	-- 行级子查询 
	select * from students where (height,age) = (select max(height),max(age) from students);
	-- 表级子查询 子查询语句查询出来的结果是多行多列




-- 数据库备份与恢复
	-- 备份 mysqldump
	mysqldump -uroot -p python_test_1 > ~/Desktop/dump.sql
	

	-- 恢复  
		-- 通过dump.sql文件将数据恢复到指定的数据库
		1. 创建一个新的数据库
		2. 将备份的数据恢复到新的数据库中: mysql -uroot -p python_restore < ~/Desktop/dump.sql



-- 数据库设计
	-- 三范式 

	-- E-R模型




-- 类似京东商城
-- 求所有电脑产品的平均价格,并且保留两位小数
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 distinct cate_name from goods;
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) as max_price,min(price),avg(price),count(*) from goods group by cate_name;


-- 查询每种类型中最贵的商品信息
-- 子查询--> 表级子查询
select * from goods as g inner join (select cate_name, max(price) as max_price,min(price),avg(price),count(*) from goods group by cate_name) as temp on g.cate_name = temp.cate_name and g.price = temp.max_price;



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

-- insert ... select
-- insert into xx表 () values ();
-- 也称作子查询语句
-- sql 结构化查询语言
insert into goods_cates (name) select cate_name from goods group by cate_name;


-- 根据goods_cates 表 更新goods 表  一个sql 语句中有个表
-- 数字可以直接存储到字符串没有任何问题  字符串 -> int 某些情况是可以的 比如 "123" --> int 成功  但是如果是 "1sd" --> int 就会失败
update goods as g inner join goods_cates as c on g.cate_name = c.name  set g.cate_name = c.id;


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


-- brand_name 品牌名称
-- 创建表 并且插入数据 一步到位  create ... select
-- 1. goods_brands表中的name 改为 brand_name 2. 把查询语句中的brand_name as name;
create table if not exists goods_brands(
    id int unsigned primary key auto_increment,
    name varchar(40) not null
) select brand_name as name from goods group by brand_name;



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

-- 修改表结构
alter table goods change brand_name brand_id int unsigned not null;


-- 连接多张表完成查询
-- 显示商品的完整信息
-- inner join
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表中的 brand_id 添加外键约束  goods表中的brand_id 需要引用goods_brands中的主键
alter table goods add foreign key(brand_id) references goods_brands(id); 


-- 添加外键约束会有问题  因为goods表中已经存在了不满足外键约束的数据 , 需要删除惠普黑白激光打印机cate_id外键约束才能够生效
alter table goods add foreign key(cate_id) references goods_cates(id);



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





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





-- 学习在python调用 mysql (数据的增删改查)
-- pip install pymysql 或者 mysqldb
-- python 提供api的模块叫做 py3.0:pymysql  py2.0: mysqldb

-- python 使用 pymysql 的流程


-- 如果通过字符串的拼接来完成sql语句的编写 就会导致一个安全漏洞 --> sql注入