day44---MySQL总结二

1. MySQL的语法

select distinct 字段1 as 字段1别名,字段2 as 字段2别名 ... 
from 左表 as 左表别名 inner/left/right 右表 as 右表别名 on 联表条件
    where 判断条件
    group by 分组字段
    having 过滤条件
    order by 排序字段
    limit 开始行数,显示行数;

2. MySQL语法的定义顺序

select
distinct
from
join
on
where
group by
having
order by
limit

• (1) 指定查询的字段

• (2) 指定是否去重

• (3) 指定表名

• (4) 指定联表方式

• (5) 指定联表条件

• (6) 指定判断条件

• (7) 指定分组字段

• (8) 指定分组后的过滤条件

• (9) 指定排序方式

• (10) 指定分页显示方式

3. MySQL语法的执行数序

from
on
join
where
group by
having
select
distinct
order by
limit

• (1) 先找到查询的左表

• (2) 指定左表和右表联表的条件

• (3) 找到联表的右表生成笛卡尔积临时表

• (4) 根据判断条件找出符合条件的数据

• (5) 把结果按照指定的字段进行分组

• (6) 通过分组再次过滤出符合条件的数据

• (7) 执行查询

• (8) 数据去重

• (9) 按照正/倒序进行排序

• (10) 限制显示条数

4. select普通查询

• 简单查询：

select * from 表名;
select 字段1,字段2 ... from 表名;

• 去重查询：

select distinct 字段1,字段2 from 表名;

• 四则混合运算查询：

select 字段1 * 10, 字段2 + 100 from 表名;

• concat()关键字查询(内置函数拼接)：

select concat('关键字1:',字段1),concat('关键字2',字段2) from 表名;

• concat_ws()关键字查询(内置函数指定分隔符拼接)：

select concat_ws('分隔符','字段1','字段2') from 表名;

• 查询字段和表的别名(as)：

select 字段1 as 别名1,字段2 * 100 as 别名2,concat('关键字',字段3) as 别名3,conct_ws('分隔符','字段4','字段5') as 别名4 from 表名 as 表别名;

5. where判断条件

• 比较运算符查询('=','!=','<>','>','<','>=','<=')：

select * from 表名 where 字段 = 值;
select * from 表名 where 字段 != 值;
select * from 表名 where 字段 <> 值;
select * from 表名 where 字段 > 值;
select * from 表名 where 字段 < 值;
select * from 表名 where 字段 >= 值;
select * from 表名 where 字段 <= 值;

• 逻辑运算符查询('not','and','or')：

select * from 表名 where not 条件判断;
select * from 表名 where 条件判断1 and 条件判断2;
select * from 表名 where 条件判断1 or 条件判断2;

• 位运算符查询('in','not in')：

select * from 表名 where 字段 in (值1,值2 ...);
select * from 表名 where 字段 not in (值1,值2 ...);

• 区间范围查询('between...and...')：

select * from 表名 where 字段 between 值1 and 值2;

• 模糊匹配查询('like')：'%'代表任意字符，'_'代表任意一个字符

select * from 表名 where 字段 like '字符%';
select * from 表名 where 字段 like '%字符';
select * from 表名 where 字段 like '%字符%';
select * from 表名 where 字段 like '字符_';
select * from 表名 where 字段 like '_字符';
select * from 表名 where 字段 like '_字符_';

• 正则表达式查询('regexp')：

select * from 表名 where 字段 regexp '正则匹配条件';

6. group by 分组查询

• 分组后如果查询非分组字段默认显示组内的第一条数据(无意义)

• 修改sql模式(set global sql_mode='ONLY_FULL_GROUP_BY';)之后，坚持查询非分组字段会报错

• 分组之后默认只能查找到分组字段的数据，也可以使用聚合函数得到其他字段的数据

select 字段 from 表名 group by 字段;
select 字段1,聚合函数(字段2) from 表名 group by 字段1;

• 常用的聚合函数：

  • max()：求最大值

  • min()：求最小值

  • sum()：求和

  • avg()：求平均值

  • count()：统计数量(建议使用count(*)或使用count(1)进行统计，一般是按照字段的主键)

• group_concat()：查看组内的数据

select group_concat(字段2) from 表名 group by 字段1;

7. having过滤条件

• having在group by之后进行过滤分组后的条件

• having只能对分组字段进行过滤，无法对非分组字段过滤

• 对非分组条件进行过滤，需要使用聚合函数

select * from 表名 group by 字段 having 字段 过滤条件;
select * from 表名 group by 字段1 having 聚合函数(字段2) 过滤条件;

8. order by排序

• 按照单列排序：

select * from 表名 order by 字段 asc/desc；

• 按照多列排序(先按照字段1排序，如果仍有重复的数据就按照字段2排序)：

select * from 表名 order by 字段1 asc/desc,字段2 asc/desc；

9. limit分页

• 开始条数从0开始

• 开始条数不指定默认从0开始

• 查询条数代表限制每页显示的条数

select * from 表名 limit 查询条数;
select * from 表名 limit 0,查询条数;
select * from 表名 limit 开始条数,查询条数;

10. 多表联查

• 多表联查需要使用join联表，使用on指定联表条件，如果不指定联表条件，会产生交叉连接，生成笛卡尔积

• 连接分为内链接和外连接

  • 内连接(inner join)：显示左表和右表共同的数据

  select * from 左表 inner join 右表 on 左表.字段 = 右表.字段;
  

• 外连接分为左外连接、右外连接和全外连接

  • 左外连接(left join)：优先显示左表的数据，右表不存在的使用NULL填充

  select * from 左表 left join 右表 on 左表.字段 = 右表.字段;
  

  • 右外连接(right join)：优先显示右表的数据，左表不存在的使用NULL填充

  select * from 左表 right join 右表 on 左表.字段 = 右表.字段;
  

• MySQL默认不支持全外连接，可以使用union关键字进行关联

  • union：连接左表和右表的记录，然后进行去重

  select * from 左表 left join 右表 on 左表.字段 = 右表.字段 union select * from 左表 right join 右表 on 左表.字段 = 右表.字段;
  

  • union all：连接左表和右表的记录，不会去重，会显示所有数据，没有数据的使用NULL填充

  select * from 左表 left join 右表 on 左表.字段 = 右表.字段 union all select * from 左表 right join 右表 on 左表.字段 = 右表.字段;
  

• 子查询：把一个查询语句的结果当作另外一个查询语句的条件

select * from 表名1 where 字段 判断条件 (select 字段 from 表名2 where 字段 判断条件 值);

• exists关键字子查询：一个查询语句的返回布尔值的结果(True/False)，条件为True运行另外一个查询语句，如果为False则不运行，not exists与exists用法相反

select * from 表名1 where exists (select 字段 from 表名2 where 字段 判断条件 值);
select * from 表名1 where not exists (select 字段 from 表名2 where 字段 判断条件 值);