MySQL4_联合-子查询-视图-事务-索引

将多个select语句的结果纵向拼接在一块；不重复显示重合部分的数据

select * from stuinfo where sex=1 union select * from stuinfo where sex=2;

select * from stuinfo union select * from stuinfo1;

#union语句的使用范围?
数据库优化,分表,假设每一张最大存储限制是10w,需要复制一张表,复制的表的id从100001开始


#不同表的连接
select sname,sex,age from stuinfo union select * from score;

select * from score union select sname,sex,age from stuinfo;

union要求:
1.两边select语句的字段数必须一样
2.两边可以具有不同数据类型的字段
3.字段名默认按照左边的表来设置

#查找深圳的男生和北京的女生
select * from stuinfo where sex=1 and city='深圳' union select * from stuinfo where sex=2 and city='北京';
#不使用union
select * from stuinfo where (sex=1 and city='深圳') or (sex=2 and city='北京');



#男生的年龄降序排列 女生的年龄升序排列
#limit
(select * from stuinfo where sex=1 order by age desc limit 999999999) union (select * from stuinfo where sex=2 order by age asc limit 999999999);

1.内连接查询
2.外连接查询
3.交叉连接查询
3.自然连接查询

#查询所有学生的信息加上成绩
#innerjoin 连接以后,需要指定公共字段
select * from stuinfo inner join score on stuinfo.sid = score.stuno;

#as 是起别名,原先的名字不可以使用了
select a.*,b.python,b.java from stuinfo as a 
inner join score as b 
on a.sid = b.stuno;
#as 可以省略不写,as也可以给结果集起别名
select a.*,b.python,b.java class from stuinfo a 
inner join score b 
on a.sid = b.stuno;
#inner可以不写 ,直接写join默认就是内连接
select a.*,b.python,b.java class from stuinfo a 
join score b 
on a.sid = b.stuno;


内连接特性:
关联表的数据不完整,默认不返回

#左外连接  left outer join 
#以左边表中的数据为主,如果右表中的数据不对应,则用Null补齐
select * from stuinfo a left join score b 
on a.sid=b.stuno;

#右外连接  right outer join
#以右边表中的数据为主,如果左表中的数据不对应,则用Null补齐
select * from stuinfo a right join score b 
on a.sid=b.stuno;

#思考:
select * from A inner join B on A.id = B.id;
select * from B inner join A on B.id = A.id;
#一样嘛?查询的结果是一样

select * from A left outer join B on A.id = B.id;
select * from B right join A on B.id = A.id;
select * from A right outer join B on A.id = B.id;

#left join可以拼接多少层?
select A.*,B.*,C.* from A     
left join B on A.id =B.id       
left join C on B.id = C.id;

#返回一个笛卡尔积
select * from stuinfo cross join score;
#但是,在mysql中被破坏了,cross join被添加上了一个on的功能
#被破坏的cross join 和inner join的作用一样
select * from stuinfo a 
cross join score b 
on a.sid=b.stuno;

1.natural join 自然内连接
2.natural left join  自然左外连接
3.natural right join  自然右外连接
#特点:
1.可以自动判断连接的条件,依据的是同名字段
2.如果没有同名字段,返回的是笛卡尔积
3.自动返回整理好的结果
	a.连接的字段只返回一个
	b.连接的字段放在最前面
	
select * from stuinfo a natural join score b;
select * from stuinfo a natural left join score b;

select * from stuinfo a left join score b 
on a.sid=b.sid;

#主要作用,是自动查找关联字段,依据的是同名字段,但是同名字段是自己制定

select * from stuinfo a join score b 
using(sid);

#显示地区 和 每个地区参加java考试的人数,并且人数按照降序排列
select a.city,count(b.java) `count` from stuinfo a 
join score b 
using(sid) 
group by a.city 
order by `count` 
desc;

#显示男生和女生人数
select sex,count(sex) from stuinfo group by sex;

(select sex,count(sex) from stuinfo where sex=1) union (select sex,count(sex) from stuinfo where sex=2);

select sum(sex=1) 男 ,sum(sex=2) 女 from stuinfo;

#查询Python成绩大于等于80的学生的信息,不要成绩
select a.* from stuinfo a left join score b using(sid) where python>=80;

select * from stuinfo 
where sid in 
(select sid from score where python>=80);

#查找python最高分的学生,不要成绩
select * from stuinfo 
where sid in 
(select sid from score 
 where python in 
 (select max(python) from score)
);

在什么时候使用in,在任何情况下都可以使用in 

#查询python成绩不及格的学生
select * from stuinfo 
where sid in 
(select sid from score where python<60);

#查询没有参加java考试的学生
select * from stuinfo 
where sid in 
(select sid from score where java is null);

#in = 
#some | any | all 用在=后面
some 和 any 是一样的 和 in比较相似
all 表示全部的 ,和=号一样
select * from stuinfo 
where sid =some 
(select sid from score where python<60);


select * from stuinfo 
where sid =any 
(select sid from score where python<60);


select * from stuinfo 
where sid =all 
(select sid from score where python<60);

#!=some !=any  和 not in 不一样
#返回及格
select * from stuinfo 
where sid not in 
(select sid from score where python<60);


select * from stuinfo 
where sid !=some 
(select sid from score where python<60);


select * from stuinfo 
where sid !=any 
(select sid from score where python<60);

#!=all  和 not in一致
select * from stuinfo 
where sid !=all 
(select sid from score where python<60);

#如果有学生的python成绩达到100分,则显示所有人的信息
select * from stuinfo 
where exists
(select * from score where python=100);

select * from stuinfo 
where not exists
(select * from score where python!=100);

#找出java成绩最高的男生和女生

select * from stuinfo where sid in 
(select sid from score where java in 
(select max(java) from score join stuinfo using(sid) 
group by sex)
);


#没有问题的
(select * from stuinfo where sid in (select sid from score where java in (select max(java) from score join stuinfo using(sid) where sex=1)) and sex=1) union (select * from stuinfo where sid in (select sid from score where java in (select max(java) from score join stuinfo using(sid) where sex=2)) and sex=2);

#用最普通查询

1.视图是一张虚拟的表,视图当中包含了多个表的数据；暂存查询的数据
2.视图中实际上没有数据,只有表的结构,数据从基础表中去获取
3.一张表可以创建多个视图,一个视图可以引用多张表

create view view1
as 
select * from stuinfo left join score using(sid);

create view view2
as 
select * from stuinfo;
#视图是一张表
select * from view1;

#视图主要就是方便查询

alter view `视图名`
as
select a.sid,a.sname,b.python from stuinfo a left join score b using(sid);

show create view `视图名`;

desc `视图名`;

show tables;

drop view [if exists] `视图名字`;

1.merge :合并算法(默认算法)
2.temptable:零时表算法
3.undefined:未定义的算法,自定义算法

create or replace algorithm=merge view `视图名` 
as
select a.sid,a.sname,b.python from stuinfo a left join score b using(sid);

什么是mysql中的事务?
类似git操作
1.事务是一个不可拆分的工作单元
2.事务作为一个整体向系统提交,要么一起执行成功,要么一起失败
3.事务不支持嵌套

事务指针对写的动作 insert  update  delete
1. 开始事务
start transaction; (mac: begin;)
update tb_name set id=10;

2.只能回滚未提交的事务;
  当前事务结束；
  对与已提交事务不能回滚到提交之前；
rollback;

3. 提交事务，当前终端事务结束；
commit;

ACID
atomicity   [ˌætəˈmɪsəti]:  1.原子性:不可被拆分
consistency [kənˈsɪstənsi]: 2.一致性:要么一起执行成功,要么一起失败

isolation   [ˌaɪsəˈleɪʃn]:  3.隔离性:事务彼此之间没有关系
durability  [dərəˈbɪlɪti]:  4.永久性:一旦执行成功,不可被修改;未commit前保存在内存中，commit之后保存日志，保证操作的持久化。

 1. 查看事务的可隔离级别
 	select @@tx_isolation;
 2. 设置事务的隔离级别；
 	set session transaction isolation level read uncommitted;
 3. 另起mysql终端进入事务(start transaction)获取当前设置隔离级别的数据
 
 
 隔离级别：
 1. read uncommitted
	当其它终端未提交修改时就同步 其他终端修改的值:级别最低
 2. read committed
	当其它终端提交修改时 才同步其他终端修改的值
 3. repeatable read
	当前事务：保持首次进入事务select查询到的值，不同步其他终端修改的值；
 4.serializable
	当前事务不能进行修改操作，其他终端commit或者rollback后才能操作：级别最高。（锁住）

1. 查看自动提交事务状态是否开启，自动提交事务是一个机制
	show variables like 'autocommit';
2. 修改自动提交的状态
	set autocommit= 0; 关闭
	set autocommit= 1; 开启

#事务只有在开启的状态下才能使用
#事务只能在innodb的引擎下才能使用,myisam中没有这个机制

1. 查看日志开启状态及日志的保存路径
	show variables like 'gen%';
2. 开启日志
	set global general_log=on;
3. 更改日志的保存路径
	set global general_log_file='/var/log/mysql/mysql.log';

1. 查看日志输出等级，默认是file,改为table
	show variables like 'log_output%';
2. 修改的日志的输出等级为table
	set global log_output='table';
3. 查看查mysql操作的历史记录
	select * from mysql.general_log;

1. for update : 只有当前终端事务update更新完数据并commit/rollback之后,其他终端才能操作 ： 与隔离模式设置的seriablizable相反
	select * from test for update;

1. 条件控制，不满足条件时不执行
update test set id=10 where id=9 and count=0;
> changed:1
update test set id=10 where id=10 and count=0;
> changed:0

#key
优点:加速了查找的速度
缺点:
1.额外的使用了一些存储的空间
2.索引会让写的操作变慢
#mysql中的索引算法叫做 B+tree(二叉树)

适用于myisam的表引擎
#适合
1.用于频繁查找的列
2.经常用于条件判断的列
3.经常由于排序的列
#不适合
1.列中数据并不多
2.不经常查询的列
3.表中数据量过小

1.主键索引(primary key)
2.外键索引(foreign key)   #只能在innodb的表引擎下使用
3.唯一键(unique) 
4.全文索引(fulltext key)  #在模糊查询的使用,myisam下可以使用
5.普通索引(index)

#联合索引
index key('sid','sname')
#只要同时查询两个字段,才会触发
where sid=1 and sname='tom';

1.表类型的不同
2.myisam中建立索引 
2.水平分表,垂直分表
3.插入优化
insert为什么需要优化

#一次插入一条
insert into `表名` set `字段`=值;
#爬虫,现将数据给爬取下来,在数据插入到数据库
#数据库 连接数据库服务器 选择数据库 语法分析 检查 插入 关闭链接

#对于python来说sql语句就是一个字符串,每次插入1w条
insert into `表名` values(值),(值);
#数据库 100次 连接数据库服务器 选择数据库 语法分析 检查 插入100w次 关闭链接

4.select语句优化
结果集尽量不要写*,要使用那些字段,就填写那些字段
#使用选择排序,还要加上limit
select sid,sname from stuinfo where order by xxx limit 20;

5.分库#讲一个项目拆解为多个项目,多个项目就是多个库
6.分机器:主从复制(一主多从,多主多从)
#主服务器中的表示innodb(用于增删改操作)的,多台从服务器myisam(用于读操作)的表
7.硬盘的选择,王者,把mysql的服务器换成固态硬盘