MySql查询进阶

1.1 as关键字

用于给显示结果中字段或者表 起别名

select 别名.字段名 from 表名 as 别名 where 条件语句

# 对字段起别名
select id as '编号', name as '大名',age as '年龄' from students;
# 修改的显示结果中的字段名称不影响表中实际名称；别名只在当前SQL中有效

# 对表名起别名 如果需要通过表名获取字段 别名.字段
mysql> select s.id,s.name from students as s;
# select students.id,students.name from students;

as 在 MySQL中可以省略
select id '编号', name '大名',age '年龄' from students;

1.2 distinct

把显示的结果中去除重复的行数据

select distinct 字段 from 表名

mysql> select age,height,gender from students;
+------+--------+--------+
| age | height | gender |
+------+--------+--------+
| 19 | 172.00 | 男 |
| 88 | 173.00 | 男 |
| 100 | 160.00 | 女 |
| 100 | 165.00 | 未知 |
| 0 | 180.00 | NULL |
| 0 | NULL | NULL |
| 0 | NULL | NULL |
mysql> select distinct age,height,gender from students;
+------+--------+--------+
| age | height | gender |
+------+--------+--------+
| 19 | 172.00 | 男 |
| 88 | 173.00 | 男 |
| 100 | 160.00 | 女 |
| 100 | 165.00 | 未知 |
| 0 | 180.00 | NULL |
| 0 | NULL | NULL |

1.3 where 子句

应用场景 - 筛选记录中满足条件的数据如果满足就予以显示。

# 1 2 3 判断 可以使用其中元素 也可以使用元素对应的位置（从1开始）
enum ('男','女','中性');

mysql> select * from students where gender = '男';
mysql> select * from students where gender = 1;
mysql> select * from students where age >=10 and age <= 50;

# 模糊查询 只知道数据的一部分 查询数据 like 
# % 匹配 任意个任意字符
# _ 匹配 1 个任意字符
mysql> select * from students where name like '张%';
mysql> select * from students where name like '张_';
mysql> select * from students where name like '张__';

# 范围查询 
# 1.连续范围：字段 between 下限 and 上限 [下限, 上限]
mysql> select * from students where age between 10 and 50;

# 2.不连续范围 字段名 in (值1, 2...)
mysql> select * from students where age = 19 or age = 29 or age = 39;
mysql> select * from students where age in (19,29,39);

# 判空 IS NULL 判断非空 IS NOT NULL
# NULL是一种状态 不能使用 = 判断
-- select * from students where height = NULL;
mysql> select * from students where height IS NULL;
mysql> select * from students where height IS NOT NULL;

1.4 排序order by

应用场景-需要将数据按照一定顺序排列。如竞价点击量重要程度。

select * from 表名 order by 字段名降序DESC|升序ASC（默认）;

mysql> select * from students order by age ASC;
mysql> select * from students order by age;

-- 以年龄 降序排序 
mysql> select * from students order by age DESC;

-- 需要用到多个字段排序： 如果第一个字段数据相等 就按第二个字段
order by 字段名 排序方式, 字段名2 排序方式,....;

-- 先以年龄升序排序 如果有相等的 使用身高降序排序
mysql> select * from students order by age asc,height desc;

1.5 分页limit

应用在数据量大一次性展示不完一次给用户展示一页。

热搜榜 Top 50 order by 热点降序分页;

limit 数据下标=0,数量 # 数据下标默认从0开始 显示数量条信息
limit 30 == limit 0,30

-- 显示身高最高的前三名
mysql> select * from students order by height desc limit 0,3;
mysql> select * from students order by height desc limit 3;

-- 显示身高最高的第四名-第6名
mysql> select * from students order by height desc limit 3,3;

起始下标 结束下标 100条
第1页 0 99
2 100 199
3 200 299
n (n-1)*100 100n-1
----------------------每页数据条数是 m 个---------——------------
n limit m*(n-1),m

1.6 聚合函数

对一组数据进行统计计算得到一个值。组函数。

组函数默认对字段为 NULL 的不统计一个值 = 组函数(字段名)

组函数默认情况下把整个表当做一个组来进行统计计算的

求和 sum()
select sum(字段名) from 表名;

计数/统计数量 count()
常用于统计表中数据行数 select count(*) from 表名;

平均数 avg()
mysql> select sum(height)/count(height) '平均身高'from students;
mysql> select avg(height) from students;

最大值 max()
mysql> select max(height) from students;

最小值 min()
mysql> select min(age) from students;

ifnull(参数1,参数2) 判断参数1是否为NULL 如果为 NULL 返回参数2；如果不为 NULL 返回参数1

1.7 分组

按照1个或者多个字段进行分类，每一类数据在同一个小组中，分别对每个小组进行统计。

组函数结合分组使用统计的不再是整个表而是每个小组

-- 分组之后 结果 已经发生的结构上的变化 而不能使用之前的 SQL 看所有数据
-- select * from students group by gender; # 报错

-- group_concat(） 所有成员的某个字段 合并在一行显示
mysql> select gender,group_concat(height) from students group by gender;
+--------+----------------------+
| gender | group_concat(height) |
+--------+----------------------+
| NULL | 180.00 |
| 男 | 172.00,173.00 |
| 女 | 160.00 |
| 未知 | 165.00 |
mysql> select gender,group_concat(name) from students group by gender;
+--------+-------------------------------+
| gender | group_concat(name) |
+--------+-------------------------------+
| NULL | 张三,张三丰,张三丰了 |
| 男 | Tom,司马狗剩 |
| 女 | jerry |
| 未知 | 老宋 |
mysql> select gender,group_concat(id,name) from students group by gender;
mysql> select gender,group_concat(name),group_concat(id) from students group by gender;

-- 组函数 会对分组之后的每个小组分别统计
mysql> select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| NULL | 3 |
| 男 | 2 |
| 女 | 1 |
| 未知 | 1 |


-- having 条件 是针对 分组结果进行过滤的 和 where 不能混淆
mysql> select gender,count(*) from students group by gender
-> having count(*) < 3;

mysql> select gender,avg(age) from students group by gender
-> having avg(age) > 80;


-- with rollup 在分组结果后新开一行 显示汇总结果
mysql> select gender,group_concat(name),count(*) from students group by gender with rollup;
+--------+-----------------------------------------------------+----------+
| gender | group_concat(name) | count(*) |
+--------+-----------------------------------------------------+----------+
| NULL | 张三,张三丰,张三丰了 | 3 |
| 男 | Tom,司马狗剩 | 2 |
| 女 | jerry | 1 |
| 未知 | 老宋 | 1 |
| NULL | 张三,张三丰,张三丰了,Tom,司马狗剩,jerry,老宋 | 7 | 汇总行

1.8 连接

如果需要将来自多个表的多个字段拼接在一个结果中显示

select * from (左表 join 右表 on 连接条件)； --on条件用于过滤笛卡尔积(左表每行拼接右表每行)

# 内连接 inner join / join
# 取出左表和右表有关联的数据
mysql> select * from hero join gongfu on hero.gongfuid=gongfu.id;
+----+-----------+----------+----+--------------+
| id | name | gongfuid | id | name |
+----+-----------+----------+----+--------------+
| 2 | 李白 | 1 | 1 | 吟诗作对 |
| 1 | 妲己 | 2 | 2 | 魅惑 |
| 3 | 程咬金 | 3 | 3 | 三板斧 |

# 外连接 outer join 内连接基础上添加额外数据
# 左外连接 left outer join / left join 内连接基础上添加来自左表的额外数据 右表位置 NULL 填充
mysql> select * from hero left join gongfu on hero.gongfuid=gongfu.id;
+----+-----------+----------+------+--------------+
| id | name | gongfuid | id | name |
+----+-----------+----------+------+--------------+
| 2 | 李白 | 1 | 1 | 吟诗作对 |
| 1 | 妲己 | 2 | 2 | 魅惑 |
| 3 | 程咬金 | 3 | 3 | 三板斧 |
| 4 | 公孙离 | 5 | NULL | NULL |

# 右外连接 right outer join / right join内连接基础上添加来自右表的额外数据 左表位置 NULL 填充
mysql> select * from hero right join gongfu on hero.gongfuid=gongfu.id;
+------+-----------+----------+----+--------------+
| id | name | gongfuid | id | name |
+------+-----------+----------+----+--------------+
| 1 | 妲己 | 2 | 2 | 魅惑 |
| 2 | 李白 | 1 | 1 | 吟诗作对 |
| 3 | 程咬金 | 3 | 3 | 三板斧 |
| NULL | NULL | NULL | 4 | 沉默 |

-- 以下为数据来源：
-- 执行下面语句 构造数据表
create table hero(
id int unsigned primary key auto_increment,
name varchar(64) not null,
gongfuid int unsigned not null
);

create table gongfu(
id int unsigned primary key auto_increment,
name varchar(64) not null
);

insert into hero (name,gongfuid) values ('妲己',2),('李白',1),('程咬金',3),('公孙离',5);
insert into gongfu (name) values ('吟诗作对'),('魅惑'),('三板斧'),('沉默');

1.9 自连接

自关联 self join

当连接时左表和右表的数据是来自于同一张表时 - 称为自连接

由于左表和右表名字一样所以需要给表起别名。

左表 as 别名 join 右表 as 别名 on 左表字段关联右表字段

-- 导入 sql 文件
source /home/python/Desktop/areas.sql;

-- 把areas city join areas pro on city.pid = pro.id当做一个虚表
mysql> select * from areas city join areas pro on city.pid = pro.id 
where pro.title = '河南省';

1. 10子查询

概念: 主查询中嵌套的子查询

方式：将第一次查询的结果用作第二次查询的范围

种类: 是根据子查询返回的结果类型

标量 -子查询返回的结果是一行一列(一个值)：主要使用聚合函数即可

行 - 一行多列

列 - 一列多行

表 - 多行多列

-- 标量  求班级中 身高高于平均身高的学生信息
	1 求出平均身高
		select avg(height) from students;
	2 根据平均身高 比较 身高大于平均身高的学习信息
		select * from students where height > 170；

子查询语句:
select * from students where height > (select avg(height) from students);

-- 行  求出 班级中身高最高并且年龄最大的同学
	1 求出最高身高 最大年龄
	select max(age), max(height) from students;
	
	2 根据1步数据去学生表中查询信息
	mysql> select * from students where age = 101 and height = 180;
	mysql> select * from students where (age,height) = (101,180);

子查询语句:
select * from students where (age,height) = (select max(age), max(height) from students);

-- 列  查询出所有使用了技能表中技能的所有英雄
	1 获取所有的技能 id
	select id from gongfu;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    |  4 |
	2 据1步数据去英雄表中查询信息
	select * from hero where gongfuid in (1,2,3,4);

子查询语句:
	select * from hero where gongfuid in (select id from gongfu);

保留两位小数的写法round(avg(price),2)

1.11. 外键约束

作用:

子表：引用主表字段中数据的表（外键在子表中）

主表：提供数据给别表使用的

当对子表中外键字段数据进行更新时要求新值必须在主表对应字段中存在，如不存在则更新失败

如果创建外键失败 1SQL错误 2表中已有数据不满足外键约束 3子表外键字段类型和主表不一样

创建外键:

表已经存在：添加外键约束
	alter table 子表名 add foreign key (子表字段) references 主表名 (主表字段)；

创建子表同时添加外键约束
	create table haha(
  	字段名 类型 约束,
    ....,
    foreign key (字段名) references 主表名(主表字段)
  )
  
查看外键约束名称
	show create table hero; 
	CONSTRAINT `hero_ibfk_1` FOREIGN KEY // hero_ibfk_1就是外键约束名称 用来删除外键约束
	
删除外键约束 知道外键约束的名字
	alter table 表名 drop foreign key 外键约束名;

外键还有一种提法：一对多和一对一。

当一对一时：

当一对多时：多是子表，一是主表。一般主表(一)中的一个字段能关联到子表(多)中的多个字段。

例如：表一：游戏名表二：游戏中的英雄名。

显然，一款游戏中有多个英雄。所以表一是主表(一)，表二是子表(多)。英雄表的每一个字段都由一个外键连接到游戏表中的唯一一款游戏。外键也是在游戏表中。