查询语句

高级查询语句

模糊查询

LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号%来表示任意0个或多个字符，下划线_表示任意一个字符

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1

e.g. 
select * from class where name like "T%";
select * from class where name like "____";
select * from hobby where hobby like "%draw%";

as 用法

在sql语句中as用于给字段或者表重命名

select name as 姓名,score as 分数 from class;

select cls.name,cls.score from class as cls where cls.score>80;

排序

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

默认情况ASC表示升序，DESC表示降序

select * from class order by score desc;
select * from class where sex='m' order by score;

复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序

select * from class order by age,score desc;

限制

LIMIT 子句用于限制由 SELECT 语句返回的数据数量 或者 UPDATE,DELETE语句的操作数量

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num] [OFFSET num]

update class set score=83 limit 1;

--男生第一名
select * from class where sex='m' order by score desc limit 1;

--男生第二名
select * from class where sex='m' order by score desc limit 1 offset 1;

联合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

默认删除结果集中重复的数据。如果使用ALL则返回所有结果集， 包含重复数据

--分数大于80的男生和分数大于90的女生
select * from class where score>80 and sex='m'
union
select * from class where score>90 and sex='w';

--可以查询不同字段，但是字段数量必须一直
select name,age,score from class where score>80
union
select name,hobby,price from hobby;

--all表示如果查询结果有重复不去重，
--order by只能加在最后表示对union结果一起排序
select * from class where sex='m'
union all
select * from class where score>80
order by score;

子查询

定义 ： 当一个语句中包含另一个select 查询语句，则称之为有子查询的语句

  • from 之后 ，此时子查询的内容作为一个新的表内容，再进行外层select查询
select * from (select * from class where sex='m') as man 
where score > 80;
  • where子句中，此时select查询到的内容作为外层查询的条件值
--查询与tom同岁的学生
select * from class
where age=(select age from class where name='Tom');
  1. 子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。
  2. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。

查询过程

  • 通过之前的学习看到，一个完整的select语句内容是很丰富的。下面看一下select的执行过程：
SELECT DISTINCT <select_list>                     

FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

WHERE <where_predicate>

GROUP BY <group_by_specification>

HAVING <having_predicate>

ORDER BY <order_by_list>

LIMIT <limit_number>

聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛选范围

聚合函数

方法 功能
avg(字段名) 该字段的平均值
max(字段名) 该字段的最大值
min(字段名) 该字段的最小值
sum(字段名) 该字段所有记录的和
count(字段名) 统计该字段记录的个数
  • 找出表中的最大攻击力的值？
select max(attack) from sanguo;
  • 表中共有多少个英雄？
select count(name) as number from sanguo;
  • 蜀国英雄中攻击值大于200的英雄的数量
select count(*) from sanguo where attack > 200;

注意： 此时select 后只能写聚合函数，无法查找其他字段，除非该字段值全都一样

聚合分组

给查询的结果进行分组

  • 计算每个国家的平均攻击力
select country,avg(attack) from sanguo group by country;
  • 对多个字段创建分组，此时多个字段都相同时为一组
--统计每个国家男性英雄和女性英雄的平均攻击力
select country,gender,avg(attack) from sanguo
group by country,gender;
  • 所有国家的男英雄中 英雄数量最多的前2名的 国家名称及英雄数量
select country,count(id) as number from sanguo 
where gender='男' group by country
order by number DESC
limit 2;

注意： 使用分组时select 后的字段为group by分组的字段和聚合函数，不能包含其他内容。group by也可以同时依照多个字段分组，如group by A，B 此时必须A,B两个字段值均相同才算一组。

聚合筛选

对分组聚合后的结果进行进一步筛选

--统计平均攻击力大于250的国家的英雄数量
select country,count(*) from sanguo
group by country
having avg(attack)>250;
  1. having语句必须与group by联合使用
  2. having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段

去重语句

不显示字段重复值

eg1 : 表中都有哪些国家
  select distinct country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;

注意: distinct和from之间所有字段都相同才会去重