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 外键约束名;

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

当一对一时：

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

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

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