Mysql_Study(高级)

-- Mysql_Study(高级)
-- 链接查询: 内链接、左链接、右链接

-- 案例1 内连接查询
select * from students inner join classes c on students.cls_id = c.id;

-- 案例2 左链接查询
select * from students as s left join classes as c on s.cls_id = c.id;

-- 案例3 右链接查询
select * from students as s right join classes c on s.cls_id = c.id where gender is not null;

-- 例4：查询学生姓名及班级名称
select s.name, c.name from students as s inner join classes c on s.cls_id = c.id;

--  with rollup
-- 作用: 在最后新增一行，来记录当前列里所有记录的总和
select gender, count(*) from students group by gender with rollup;
select gender, group_concat(id) from students group by gender with rollup;


# 没有areas表sql执行语句的可以评论区评论
-- 创建areas表
create table tb_areas(
    aid int primary key,
    atitle varchar(20),
    pdi int
);
-- 执行sql文件 插入全国所有省市区表信息
-- 想要这个文件的小伙伴可以 私信我~

-- 查询所有省的信息
select * from tb_areas where pdi is null;

-- 查询某一个省下面的所有城市信息
-- 查询城市信息
select city.* from tb_areas as city
inner join tb_areas as province on city.pdi=province.aid
where province.atitle='贵州省';

-- 查询某个市区下面所有县区信息
select city.* from tb_areas as city
inner join tb_areas as province on province.aid=city.pdi
where province.atitle='贵阳市';

-- 子查询(不建议用)  可用链接查询代替
-- select avg(age) from students;
select * from students where age > (select avg(age) from students);

-- 视图  (相对动态， 表中数据改变视图也会变化)
-- 把视图理解为函数 这个函数封装了一个查询语句
-- creat view 视图名称 as select语句;
-- 视图的作用

-- 1. 提高了重用性，就像一个函数
-- 2. 对数据库重构，却不影响程序的运行
-- 3. 提高了安全性能，可以对不同的用户
-- 4. 让数据更加清晰

-- 视图的创建
-- 可执行的查询语句
-- select * from tb_areas as city inner join tb_areas as pro on pro.aid=city.pdi where pro.atitle='贵州省';

-- 视图创建的公式
-- create view <视图名> as <查询语句>;
create view v_city_guizhou as select city.* from tb_areas as city inner join tb_areas as pro on pro.aid=city.pdi where pro.atitle='贵州省';

-- 查询已创建的视图
select * from v_city_guizhou;

-- 删除视图
drop view v_city_guizhou;

-- 查看视图是否存在
show tables;

-- 事务

-- 事务四大特征(简称ACID)
-- 1.原子性
--   一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，
--   对于一个事务来说，不可能执行到其中的一部分操作，这就是事务的原子性。

-- 2.一致性
--   数据库总是从一个一致性的状态转换到另一个一致性的状态。(在前面的例子中，一致性确保了，即使在执行
--   第三、第四条语句之间系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所作的修改也不会保存到数据库中。)

-- 3.隔离性
--   通常来说，一个事务所做的修改在最终提交钱，对其他事物是不可见的。(在前面的例子中，当执行玩完第三、第四语句还未开始时，此时
--   有另外一个账户汇总程序开始运行，则其看到的支票账户的余额并没有)

-- 4.持久性
-- 一旦事务提交，则其所做的修改会永远保存到数据库。(此时即使系统崩溃，修改的数据也不会丢失)

-- 表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
-- 查看表的创建语句，可以看到engine=innodb
show create table students;

-- 开启事务
-- 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
-- begin; 或者 start transaction;

-- 提交事务
-- 将缓存的数据变更维护到物理表中
-- commit;

-- 回滚事务
-- 放弃缓存中变更的数据
-- rollback;

-- 注意: 一旦commit之后相当于事务结束了， commit在python中是一个方法，


-- 数据库操作-- pycharm
-- 安装命令: pip install pymysql -i https://pypi.douban.com/simple