通配符

一般用于模糊搜索

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。

SQL 通配符用于搜索表中的数据。

在 SQL 中，可使用以下通配符：

select * from info where name like "%沛%";
select * from info where name like "%沛";
select * from info where email like "%@live.com";
select * from info where name like "武%齐";
select * from info where name like "k%y";
select * from info where email like "wupeiqi%";


select * from info where email like "[email protected]";
select * from info where email like "[email protected]";
select * from info where email like "[email protected]";
select * from info where email like "__peiqi_live.co_";

 
使用正则表达式

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

SELECT * FROM Websites  WHERE name REGEXP '^[GFs]';

下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站：

SELECT * FROM Websites WHERE name REGEXP '^[A-H]';

下面的 SQL 语句选取 name 不以 A 到 H 字母开头的网站：

SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';

条件 where

WHERE 子句用于提取那些满足指定条件的记录。

# 下面的 SQL 语句从 "Websites" 表中选取国家为 "CN" 的所有网站：

SELECT * FROM Websites WHERE country='CN';

排序 order by age

select * from info order by age desc; -- 倒序
select * from info order by age asc;  -- 顺序

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


select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;

limit 取部分

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数据

数据库表中：1000条数据。

• 第一页：select * from info limit 10 offset 0;
• 第二页：select * from info limit 10 offset 10;
• 第三页：select * from info limit 10 offset 20;
• 第四页：select * from info limit 10 offset 30;

分组 group by

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

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 age,count(id) from info where id > 4 group by age having count(id) > 2;  -- 聚合条件放在having后面

select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条

到目前为止SQL执行顺序：
    where 
    group by
    having 
    order by
    limit 

左右连表 left join

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

主表 left outer join 从表 on 主表.x = 从表.id 

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

select info.id,info.name,info.email,depart.title from info left outer join 
depart on info.depart_id = depart.id;

从表 right outer join 主表 on 主表.x = 从表.id

select info.id,info.name,info.email,depart.title from info right outer join depart on 
info.depart_id = depart.id;

select * from info left outer join depart on ....
select * from depart left outer join info on ....

简写：select * from depart left join info on ....

上下连表

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;

-- 保留所有