SQL必备 和 表关联
通配符
一般用于模糊搜索
在 SQL 中，通配符与 SQL LIKE 操作符一起使用。
SQL 通配符用于搜索表中的数据。
在 SQL 中，可使用以下通配符：
通配符 | 描述 |
% | 替代0或多个字符 |
_ | 替代一个字符 |
[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
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;
-- 保留所有