通配符

一般用于模糊搜索

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

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

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

通配符

描述

%

替代0或多个字符

_

替代一个字符

[abc]

字符列中的任何单一字符

[^abc]

或

[!abc]

不在字符列中的任何单一字符

like操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

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 "_@live.com";select * from info where email like "_upeiqi@live.com";select * from info where email like "__peiqi@live.com";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 子句用于提取那些满足指定条件的记录。

# 下面的 SQL 语句从 "Websites" 表中选取国家为 "CN" 的所有网站：SELECT * FROM Websites WHERE country='CN';

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;

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 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 unionselect id,name from info;select id,title from depart unionselect email,name from info;-- 列数需相同

select id from depart unionselect id from info;-- 自动去重

select id from depart union allselect id from info;-- 保留所有