Python---必备SQL

1. 数据准备

 根据条件搜索结果 
 select * from info where age > 30;
 select * from info where id between 2 and 4;
 select * from info where id in(select id from depart);
 select * from info where exists (select * from depart where id=5); 
  --如果select * from depart where id=5存在，则显示info数据
  select * from (select * from info where id > 5) as T where age>10;
  select * from info where info.id > 10;

2.通配符

 一般用于模糊搜索
 select * from info where name like "%pei%"; --中间有pei
 select * from info where email like "%@live.com"; --结尾
 select * from info where email like "_@live.com"; -- 前有一个

3.映射

 想要获取的列，少写select * ，自己需求
 select id,name,666 as num,(select max(id) from depart) as mid from info;
 select id,name,(select title from depart where depart.id=info.depart_id) as x1 from info; 
 -- 效率低，子查询
 select id,name,case depart_id when 1 then "第一部门" else "其他" end v2 from info;

4.排序

select * from info order by age desc; -- 倒序
select * from info order by age asc; -- 顺序
select * from info order by age asc,id desc; -- 优先按age从小到大；如果age相同则按id从大到小

5.取部分

一般要用于获取部分数据
select * from info limit 5; -- 获取前5条数据
select * from info order by id desc limit 3; -- 先排序的获取前3条数据
select * from info where id > 4 order by id desc limit 3; -- 先排序再获取前3条数据
select * from info where id > 4 order by id desc limit 3;
select * from info limit 3 offset 2; -- 从位置2开始，向后取3条数据

6.分组

select age,count(1) from info group by age;
select depart_id ,count(id) from info group by depart_id;
 select depart_id ,count(id) from info group by depart_id having count(id) > 2;
  -- 聚合条件筛选搜索
 select count(id) from info;

优先级：where > group by > having > order by > limit
聚合条件放在having后面

7.左右连表

多个表可以连接起来进行查询
select * from 主表 left outer join 从表 on
主表.(id) = 从表.(id);
或 从表 right outer join 主表 on 主表.x=从表.id;
谁是主表就以谁的数据为主，从表后加的数据不显示。
eg: select into.id,info.name,info.email,depart.title from info left outer join depart 
on info.depart_id=depart.id;
内连接:select * from info inner join depart on info.depart_id=depart.id;
执行顺序：join > on > where > group by > having > order by > limit

8.上下连表

 select id,title from depart union;
 select id,name from info;--列数需相同
 select id from depart union select id from info;-- 自动去重
 select id from depart union all select id from info; -- 保留所有