04 高级查询语句

高级查询语句

• 模糊查询

  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 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

  使用 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语句的操作数量

  带有 LIMIT 子句的 SELECT 语句的基本语法如下：

  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 语句会删除重复的数据。

  UNION 操作符语法格式：

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

  默认UNION后卫 DISTINCT表示删除结果集中重复的数据。如果使用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 查询语句，则称之为有子查询的语句

  • 子查询使用位置：

    1. from 之后 ，此时子查询的内容作为一个新的表内容，再进行外层select查询

    select * from (select * from class where sex='m') as man 
    where score > 80;
    

    注意： 需要将子查询结果集重命名一下，方便where子句中的引用操作

    2. where子句中，此时select查询到的内容作为外层查询的条件值

     --查询与tom同岁的学生
    select * from class
    where age=(select age from class where name='Tom');
    
    

  –class 表中 谁报了兴趣爱好班
  select * from class
  where name in (select name from hobby);

  > 注意：
  >
  > 1. 子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。
  > 2. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。
  > 3. 子查询语句也可以放在 insert update delete中，修改数据的表与子查询的表不能为同一张数据表。示例:update class set score=score+1 where score >(select score from (select * from class) as c where name="Tom");
  

• 查询过程

select的执行过程：

(5)SELECT DISTINCT <select_list>                     

(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

(2)WHERE <where_predicate>

(3)GROUP BY <group_by_specification>

(4)HAVING <having_predicate>

(6)ORDER BY <order_by_list>

(7)LIMIT <limit_number>

高级查询

在stu下创建数据报表 sanguo

字段：id  name  gender  country  attack  defense

create table sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum('男','女'),
country enum('魏','蜀','吴'),
attack smallint,
defense tinyint
);


insert into sanguo
values (1, '曹操', '男', '魏', 256, 63),
       (2, '张辽', '男', '魏', 328, 69),
       (3, '甄姬', '女', '魏', 168, 34),
       (4, '夏侯渊', '男', '魏', 366, 83),
       (5, '刘备', '男', '蜀', 220, 59),
       (6, '诸葛亮', '男', '蜀', 170, 54),
       (7, '赵云', '男', '蜀', 377, 66),
       (8, '张飞', '男', '蜀', 370, 80),
       (9, '孙尚香', '女', '蜀', 249, 62),
       (10, '大乔', '女', '吴', 190, 44),
       (11, '小乔', '女', '吴', 188, 39),
       (12, '周瑜', '男', '吴', 303, 60),
       (13, '吕蒙', '男', '吴', 330, 71);

查找
1. 查找所有蜀国人信息，按照攻击力排名
2. 吴国英雄攻击力超过300的改为300，最多改2个
3. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
4. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
5. 查找名字为3字的
6. 找到魏国防御力排名2-3名的英雄
7. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的
8. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄


1. 查找所有蜀国人信息，按照攻击力排名
select * from sanguo
where country="蜀"
order by attack desc;

2. 吴国英雄攻击力超过300的改为300，最多改2个
update sanguo set attack=300
where country="吴" and attack>300
limit 2;

3. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
select name as 姓名,attack as 攻击力
from sanguo
where attack>200 and country="魏";

4. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
select * from sanguo
order by attack desc,defense;

5. 查找名字为3字的
select * from sanguo where name like "___";

6. 找到魏国防御力排名2-3名的英雄
select * from sanguo
where country="魏"
order by defense desc
limit 2 offset 1;

7. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的
select * from sanguo where gender="女" and attack>180
union
select * from sanguo where gender="男" and attack<250;

8. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄
select * from sanguo
where country="蜀" and
 attack > (select attack from sanguo
where country="魏" order by attack desc
limit 1);