查询数据

MySQL

select
{* | <字段列表>}
[
    from <表1>,<表2>...
    [
        where <表达式>
        [group by <group by definition>]
        [having <expression> [{<operator> <expression>}...]]
        [order by <order by definition>]
        [limit [<offset>,] <row count>]
    ]
]
select count(*) sum(`num`) avg(`price`) max(`num`) min(`num`) distinct `name`, `s_id`, group_concat(`name`) as names
from `table_name`
where `id` > 50
and
`id` not in (89,99)
and
`name` like `%jiang--zhan%`
and
`email` is not null
or
`nickname` is null
and 
`id` not between 30 and 40
# with rollup 统计计量数量 rollup 和 order by 是互相排斥的
group by `s_id`,`name` [with rollup]
having count(`name`) > 1
order by `id` desc, `name` asc
limit 4,3;

内连接

select suppliers.s_id, s_name, f_name, f_price
from fruits, suppliers
where fruits.s_id = suppliers.s_id;
select suppliers.s_id, s_name, f_name, f_price
from fruits inner join suppliers
on fruits.s_id = suppliers.s_id;
# 自连接查询
select f1.f_id, f1.f_name
from fruits as f1, fruits as f2
where f1.s_id=f2.s_id and f2.f_id='a1';

外连接查询

select customers.c_id, orders.o_num
from customers left outer join orders
on customers.c_id=orders.c_id;
select customers.c_id, orders.o_num
from customers right outer join orders
on customers.c_id=orders.c_id;

复合条件查询

select customers.c_id, orders.o_num
from customers right outer join orders
on customers.c_id=orders.c_id and customers.c_id=1001;
select suppliers.s_id, s_name, f_name, f_price
from fruits inner join suppliers
on fruits.s_id = suppliers.s_id
order by fruits.s_id;

子查询

# any
select num1
from tbl1
where num1 > any (select num2 from tbl2);
# some
select num1
from tbl1
where num1 > some (select num2 from tbl2);
# all
select num1
from tbl1
where num1 > all (select num2 from tbl2);
# exists
select * from fruits
where exists
(select s_name from suppliers where s_id=107);
select * from fruits
where f_price > 10.20 and exists
(select s_name from suppliers where s_id=107);
# exists 和 not exists 的结果只取决于是否会返回行,而不取决于这些行的内容,所以这个子查询输入列表通常是无关紧要的.
select * from fruits
where not exists
(select s_name from suppliers where s_id=107);
# in
select c_id 
from orders 
where o_num not in
(select o_num from orderitems where f_id='c0');

合并查询结果

# union
select s_id, f_name, f_price
from fruits
where f_price < 9.0
union all
select s_id, f_name, f_price
from fruits
where s_id in(101,103);

正则表达式匹配

# ^ 匹配文本的开始字符
select * from fruits where f_name regexp '^b';
# $ 匹配文本的结束字符
select * from fruits where f_name regexp 'y$';
# . 匹配任何单个字符
select * from fruits where f_name regexp 'a.g';
# * 匹配零个或多个在它前面的字符
select * from fruits where f_name regexp '^ba*';
# + 匹配前面的字符1次或多次
select * from fruits where f_name regexp '^ba+';
# 匹配指定字符串文本
select * from fruits where f_name regexp 'on';
select * from fruits where f_name regexp 'on | ap';
# [] 匹配指定字符串中的任意一个
select * from fruits where f_name regexp [ot];
select * from fruits where f_name regexp [^0-9];
select * from fruits where f_name regexp [0-9a-z];
# 使用{n,}或者{n,m}来指定其前面的字符串连续出现的次数
select * from fruits where f_name regexp 'ba{1,3}';

参考资料:
MySQL5.7从入门到精通