必备的MySQL语句

1.1条件

根据条件搜索数据

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4
select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);
select * from info where id in (1,2,3);

exists select * from depart where id=5；--去查数据是否存在，如果存在，如果不存在。
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);

select * from (select * from info where id>2) as T where age > 10;

1.2 通配符

一般用于模糊搜索

select * from info where name like "k%y"; -- %可用以匹配多个字符
select * from info where email like "_@live.com"; -- _用于匹配一个字符

1.3 映射

想要获取的列

select id, name                from info;
select id, name as NM         from info;
select id, name as NM, 123  from info;
--注意：少些select * ,自己需求。
select 
    id,
    name,
    666 as num,
    ( select max(id) from depart ) as mid, -- max/min/sum
    ( select min(id) from depart) as nid, -- max/min/sum
    age
from info;

高级用法

select 
    id,
    name,
    case depart_id when 1 then "第1部门" end v1
from info;

select 
    id,
    name,
    case depart_id when 1 then "第1部门" else "其他" end v2
from info;

select 
    id,
    name,
    case depart_id when 1 then "第1部门" end v1,
    case depart_id when 1 then "第1部门" else "其他" end v2,
    case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,  -- 可实现自动分类
    case when age<18 then "少年" end v4,
    case when age<18 then "少年" else "油腻男" end v5,
    case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

1.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从大到小。

1.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 limit 3 offset 2;    -- 从位置2开始，向后获取前3数据

1.6 分组

select age,count(1) from info group by age; --count可实现统计总数

注意：要筛选分组后数据时不能用where，而要用having

select age,count(id) from info where id > 4 group by age having count(id) > 2;  -- 聚合条件放在having后面

1.7 左右链表

多个表可以连接起来进行查询

一般的格式为：主表 left outer join 从表 on 主表.x = 从表.y （可简写为：主表 left join 从表 on ）如：

select * from info left outer join depart on info.depart_id = depart.id;

注意：主表中的元素在从表中必须要有对应否则会显示为NULL；多张表同样可以连接

补充：内链接格式为：表  inner join 表  on 条件

1.8 联合

select id,title from depart 
union
select id,name from info;


select id,title from depart 
union
select email,name from info;
-- 列数需相同

select id from depart 
union
select id from info;

-- 自动去重 

select id from depart 
union all
select id from info;

-- 保留所有