一篇文章概括SQL查询

SQL的两个顺序

书写顺序：select [distinct]  字段名 [as 别名] [分组聚合操作] -> from -> where -> group by -> having -> order by ->limit

执行顺序：from -> where -> group by [as 别名]  [分组聚合操作] （不分先后）-> having -> select [distinct]  -> order by ->limit

[distinct]、聚合操作、别名是跟着select一起的

目录

一、基础查询

二、条件查询

1、逻辑查询

2、比较查询

3、范围查询

4、非空查询

5、模糊查询

6、排序查询

7、聚合查询

8、分组查询（重要）

9、分页查询

补充：having和where的区别

三、多表查询

多表关系

1、连接查询

2、自连接查询

3、子查询

快速复制表

扩展——开窗函数

---

一、基础查询

关键字
    select：查什么
    from ：从哪查
格式：

其中[ ]:里面的内容表示可以省略，|：表示或者， *：表示所有字段，distinct：表示去重  as:表示可以给表或者字段取别名（建议不要使用中文）

select [distinct] 字段名[as 别名] from表名 [as 别名];

 如：查找学生表的所有信息

SELECT * from student;

如：查找查询商品表的商品名和商品价格

select pname,price from product;

如：算术运算符在sql中可以直接使用（+-*/%）

select pname,price+10 from product;

  另：

 distinct：对字段值去重
    as：给表和字段名起别名
    *：默认代表所有字段

二、条件查询

关键字
    where：查询条件是什么
格式：

select [distinct] 字段名 from 表名 where 条件

1、逻辑查询

and：同时满足所有条件
or： 满足其中一个条件
not：不成立

逻辑查询配合其他查询用

2、比较查询

大于、小于、不等于、大于（小于）等于（不能取区间——如不能取0<age<22）

格式

select * from 表名 where 列名>值;

如：查询价格[大于、小于、大于（小于）等于、不等于]3000的商品信息

SELECT * FROM product WHERE price > 3000;
SELECT * FROM product WHERE price < 3000;
SELECT * FROM product WHERE price >= 3000;
SELECT * FROM product WHERE price <= 3000;
SELECT * FROM product WHERE price != 3000;#不等于
SELECT * FROM product WHERE price <> 3000;#不等于

注意：sql中，取范围时不能连续判断范围，不会报错，但是有错！输出结果与要求不符
如：

SELECT * FROM product WHERE 800< price <3000;

但是可以配合逻辑查询使用，如：查询800至3000的商品信息

SELECT  *FROM product WHERE price >=800 AND  price <=3000;
SELECT * FROM product WHERE NOT (price<800 OR price>3000);

3、范围查询

between  a and b：在a和b范围之间

格式

select * from 表名 where price between a and b；

如：查询800至3000的商品信息

SELECT *FROM product WHERE price BETWEEN 800 AND 3000;

in（a，b，c）值为a，b，c

格式

select * from 表名 where price in（a,b,c）;

如：查询价格为3000、50000、800的商品信息

SELECT *FROM product WHERE price IN (3000,5000,800);

4、非空查询

 is  no null ：不为空

#查询学生表中id不为空的信息
select * from student where id is no null;

is null：为空

#查询学生表中id为空的信息
select * from student where id is null;

null易错类型：

在数据表中
<<null>>：空的，没有任何意义
  '   '  空字符串
null：字符串"null"

5、模糊查询

like  ‘%’中%表示0个或者多个字符
like  ‘_’中_表示1个字符

如：

#查询商品名称以香开头的商品信息
SELECT * FROM product WHERE pname LIKE '香%';
#查询商品名称是3个字且以香开头的商品信息
SELECT * FROM product WHERE pname LIKE '香__';
#查询商品名称有想字的商品信息
SELECT * FROM product WHERE pname LIKE '%想%';

查询两个条件时不能直接用or连接，要写全
如： 查询学生表中姓"百"或者姓”孙”的学生的基本信息

SELECT * FROM students WHERE name LIKE "孙%" or name LIKE '百%';

6、排序查询

格式

SELECT * FROM 表名 ORDER BY 字段名 ASC或DESC;

如：

#升序排序
SELECT * FROM student ORDER BY age ASC ;
#降序排序
SELECT * FROM student ORDER BY age DESC ;

7、聚合查询

聚合函数也可以叫分组函数、统计函数

格式

SELECT 聚合函数(字段名) from 表名；

    所有的聚合函数（字段名）都会省略null值
    count():统计指定列不为NULL的记录行数

SELECT count(*) FROM product;

    sum():计算总和，如果不是数值类型那么计算结果为0

# 查询分类为'c001'的所有商品价格的总和
SELECT sum(price) FROM product WHERE category_id = 'c001';

    max()：计算最大值，如果是字符串类型会使用字符串排序运算
    min()：计算最小值，如果是字符串类型会使用字符串排序运算

# 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;

    avg()：计算平均值，如果不是数值类型那么计算结果为0

# 查询分类为'c002'所有商品的平均价格
SELECT avg(price) FROM product WHERE category_id='c002';

    round(浮点数，x)：保证指定浮点数保留x位小数

SELECT round(avg(price),2) FROM product;

聚合函数无法同时展示统计后的和统计前的，因为统计后数量对应不上会报错

如：不能用SELECT price,avg(price) FROM product WHERE category_id='c002';

因为统计后price和avg(price)数量对应不上会报错

8、分组查询（重要）

在分组查询中，select后的字段名要么在聚合函数内出现，要么在group by后面出现过，否则就报only_full_group_by错误

格式：其中聚合条件可以说是含聚合函数

SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段名;
#配合聚合使用时格式
SELECT 分组字段名，聚合函数（字段名）FROM 表名 GROUP BY 分组字段名HAVING 聚合条件;

如：

#统计各个分类的商品的平均价格，并且按照平均价格降价排序展示
SELECT avg(price) FROM product GROUP BY category_id ORDER BY avg(price) DESC ;
#统计各个分类的商品的平均价格，并且按照平均价格降价排序展示同时展示分类
SELECT category_id,avg(price) FROM product GROUP BY category_id ORDER BY avg(price) DESC;

常见误区

#统计信息分组是没有意义的会报错，对应不上按所有
SELECT * FROM product GROUP BY pname;

在分组查询中，select后的字段名要么在group by后面出现过，要么直接放到聚合函数中，否则会报only_full_group_by错误

#可以展示类型，不能展示像pname、id这种，因为统计后的数量不等，会报only_full_group_by错误
SELECT name,avg(price) FROM product GROUP BY category_id ORDER BY avg(price) DESC;
SELECT id,avg(price) FROM product GROUP BY category_id ORDER BY avg(price) DESC;

9、分页查询

格式：

SELECT 字段1，字段2... FROM 表名 LIMIT M,N

        M（起始索引）: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数  索引从0开始
        N: 整数，表示查询多少条数据

如：

        #16条数据，每页展示4条
#第1页数据
SELECT * FROM product LIMIT 0,4;
#第2页数据
SELECT * FROM product LIMIT 4,4;
#第3页数据
SELECT * FROM product LIMIT 8,4;
#第4页数据
SELECT * FROM product LIMIT 12,4;
#假设12页，每页60条数据
SELECT * FROM product LIMIT 660,60;

大数据人员一般limit不用于查分页，用于查topN数据
如：

#查询商品价格最高的商品
SELECT * FROM product ORDER BY  price DESC LIMIT 0,1;
#limit x，y  其中x是起始索引，如果它是0的话可以省略不写
SELECT * FROM product ORDER BY  price DESC LIMIT 1;

补充：having和where的区别

where是在查询之前过滤，having是在查询之后过滤
      书写顺序不同：where在group by之前··having在group by之后
    执行顺序不同，where在分组前，having在分组后
      条件不同：where只能跟非聚合条件，having能跟聚合条件也能跟非聚合条件（但是非聚合条件效率低，一般不用）
        非聚合条件用WHERE
        聚合条件用HAVING
      别名： where后不能使用别名,having后可以使用别名

三、多表查询

本质：就是把多个表合并成一个表再去做单表查询
通过主键和外键关联关系，连接（join）成一个大表，再查询

多表操作的sql语句一般比较长，可以选中以后按ctrl+alt+L格式化代码

多表关系

一对多关系（主要）
    主外键关系：在有一对多关系的两个表中，一方的主键就是主键，多方中引用一方主键的那一列就是外键
    外键的类型必须和主键的类型保持一致
    外键的内容也需要和主键的内容保持一致

一对一关系：省略
多对多关系：一般拆为两个一对多

1、连接查询

交叉连接(基本不使用): 得到的是两个表的乘积

#交叉连接（慎用，没啥用）
select * from hero cross join kongfu;

内连接——只取交集，内连接查询一般用于获取两个表的交集

# 内连接(左表存在，右表也存在的数据被保留)——其中INNER可以省略，也可以把INNER JOIN换成,
#格式：SELECT * FROM 表名1 [INNER] JOIN 表名2 ON 表1.主键（外键） = 表2.外键（主键）;
SELECT * FROM hero INNER JOIN kongfu ON hero.kongfu_id = kongfu.kid;
SELECT * FROM kongfu JOIN hero ON hero.kongfu_id = kongfu.kid;
SELECT * FROM kongfu,hero ON hero.kongfu_id = kongfu.kid;

外连接：左表和右表只是位置关系,在前面是左表,在后面的是右表
左外连接：除了取交集外，还要把左边全部没有连接的放上去，右边补null，即左表展示所有数据，右表展示有交集的数据，其他地方用null补全

右外连接：除了取交集外，还要把右边全部没有连接的放上去，左边补null，即右表展示所有数据，左表展示有交集的数据，其他用null补全

#左右连接——其中outer可以省略
#左连接格式：SELECT * FROM 左表 left outer JOIN 右表 ON 表1.主键（外键） = 表2.外键（主键）;
#右连接格式：SELECT * FROM 左表 right outer JOIN 右表 ON 表1.主键（外键） = 表2.外键（主键）;
select * from hero left outer join kongfu on hero.kongfu_id = kongfu.kid;
select * from hero right outer join kongfu on hero.kongfu_id = kongfu.kid;

图解：

2、自连接查询

自连接可以认为是一种解决方案, 使用场景比较局限,一般用于省市区三级信息在一个表,或者上下级信息在一个表中

特例，可以将一个表和自己连接
语法：与内外连接的语法一样，只不过在一张表上操作
特点，左表和右表是同一张表，只是取了不一样的别名（别名必须取）
注意：用自连接的时候要为表指定多个别名，对所有字段名的引用也要用别名区分

#格式：select * from 表名 别名1 JOIN 表名 别名2 ON 别名1.主键=别名2.外键 where 条件;
select * from areas c JOIN areas p ON c.pid = p.id WHERE p.title ='某某省';

3、子查询

子查询作为表(效率会高一点)是一种优化手段，连接查询都能查，子查询只是多了一种查询方法

思想：一个完整的select语句作为另外一个完整的select语句的表或者条件使用
格式：

select ....查询字段 ... from ... 表.. where ... 查询条件;

如：

#先查询筛选出category中的'化妆品'，再去连接products表
SELECT *
FROM
    products p
        JOIN (SELECT * FROM category WHERE cname = '化妆品') c ON p.category_id = c.cid
WHERE
    cname = '化妆品';

USE db_4;
#查某某省的所有城市
SELECT * FROM areas WHERE pid = (SELECT id FROM areas WHERE title = '某某省');

快速复制表

复制表的三种方式

①快速查看建表语句：

        选中表然后Ctrl+Q
        然后手动一条一条插入数据
②快速复制表结构——建表后含有原表的约束
        建表：create table 新表名 like 旧表名; 如：CREATE TABLE areas_hb LIKE areas;
        建好表再插入数据：insert into 表名 select 语句；如： INSERT INTO areas_hb SELECT * FROM areas;
③快速复制表结构和数据——建表后约束会丢失
        create table 新表名 as select 语句;
        如：CREATE TABLE areas_hb3 AS SELECT * FROM areas ;

扩展——开窗函数

开窗函数在MySQL8.0版本后才开始支持

格式:哪个要开窗就在哪个后面用over（）

<开窗函数> over （[PAPTITION] by 分组字段） order· by 排序字段 ASC|DESC）;

    比如：注意    over（）要在max（price）后面，不能在max（price）-price后面

# 需求2: 查询每个商品和最高价格的差额
SELECT *, (MAX(price) OVER () - price) AS 与最小差值 FROM products;

核心思想：保证输出的结果数与输入的记录数一致，并且单独作为一列在表的末尾展示
    比如：

select pname，min（price ） over（） as min_price from product;

原来不加over（）会报only_full_group_by错误，现在min(price)会展示在最后一列。as min_price为命名别名为min_price

---

三个排序开窗函数
row_number()：无论排名是否有相同都按顺序1,2,3...n
        巧记：12345

如：

SELECT id, name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num
FROM students;

rank()：排名相同的，同一个排名有几个后面排名就跳过几次
        巧记：12225

SELECT id, name, age, RANK() OVER (ORDER BY age DESC) AS rank_num
FROM students;

dense_rank()： 排名相同的，后面名次不跳过
        巧记：12223

SELECT id, name, age, DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank_num
FROM students;