MySQL单表查询完整语法
数据按一定结构存入表中，应该能够按照我们的意愿增删改查，而查这一步，我们最基础的语法是：
select * from 表名查找某张表的所有字段的所有数据
select 字段名1，字段名2 from 表名查询某张表指定字段的所有数据。
而数据库还提供了很多语法来筛选我们的表数据。
| 关键字|符号 | 用法 | 配合用法|位置 |
|---|---|---|
| where | 分组前筛选 | 条件判断<,>,=,and,or,not,between,模糊查询 |
| group by | 分组 | 聚合函数max,min,avg,sum,group_concat |
| having | 分组后筛选 | 条件判断<,>,=,and,or,not,between,模糊查询 |
| distinct | 去重 | distinct放在字段名前 |
| order by | 排序 | 跟在最后，最后执行 |
| limit | 分页 | 跟在后面，最后执行 |
| regexp | 正则 | 作为匹配条件 |
本文将从下面所准备的表入手，提出各种筛选条件进行查询，笔者可以进行复制跟随尝试。
准备用于演示的表
create table role(
id int not null unique auto_increment,
name varchar(20) not null,
gender enum('male','female') not null default 'male', # 默认男性
age int(3) unsigned not null default 28,
birth_date date not null,
game varchar(50), # 所属游戏
game_comment varchar(100),
price double(15,2),
office int, # 工作室门牌号
official_id int # 工作室编号
);
#添加数据
insert into role(name,gender,age,birth_date,game,price,office,official_id) values
('蔚','female',18,'20170301','LOL',7300.33,401,1),
('金克斯','female',16,'20150302','LOL',1000000.31,401,1),
('杰斯','male',27,'20130305','LOL',8300,401,1),
('维克多','male',22,'20140701','LOL',3500,401,1),
('凯特琳','female',17,'20121101','LOL',2100,401,1),
('希尔克','male',50,'20110211','LOL',9000,401,1),
('范德尔','male',60,'19000301','LOL',30000,401,1),
('亚瑟','male',48,'20101111','HOK',10000,402,1),
('艾琳','female',48,'20150311','HOK',3000.13,402,2),
('云缨','female',38,'20101101','HOK',2000.35,402,2),
('东方曜','male',18,'20110312','HOK',1000.37,402,2),
('宫本武藏','male',18,'20160513','HOK',3000.29,402,2),
('司空震','male',28,'20170127','HOK',4000.33,402,2),
('巴巴托斯','male',28,'20160311','GI',10000.13,403,3),
('摩拉克斯','male',18,'19970312','GI',20000,403,3),
('巴尔泽布','female',18,'20130311','GI',19000,403,3),
('布耶尔','male',18,'20150411','GI',18000,403,3),
('天理','female',18,'20140512','GI',17000,403,3);
where筛选
between A and B 在。。之间
# 查询id大于等于3小于等于6的数据
select * from role where id >= 3 and id <= 6; # 支持逻辑运算符
select * from role where id between 3 and 6; # between关键字，指两者之间
in (A,B,C) 成员运算
# 查询价格是20000或者18000或者17000的数据
select * from role where price=20000 or price=18000 or price=17000;
select * from role where price in (20000,18000,17000); # 支持成员运算
not 非
# 查询id小于3大于16的数据
select * from role where id<3 or id>16;
select * from role where id not between 3 and 16; # 有not between 和 not in都可以，就是条件取反
模糊查询 like
因为查询的条件比较模糊被称之为模糊查询
其关键字也很契合这个意思 -- like -- 就像
模糊查询常用符号： % 匹配任意个任意字符 _ 匹配单个任意字符
如：
%m%指匹配到含m的字符串，像tom、mobile、come都是符合条件的%m指匹配到结尾为m的字符串，tom，sam都符合_o_指匹配类似som、mob、pop这样的o两侧只有一个字符的
# 查询角色姓名中开头为'巴'的角色姓名与价格
select name,price from role where name like '巴%';
# 查询角色姓名是由四个字符组成的角色姓名与价格
select name,price from role where name like '____';
group by分组
分组指按照某个条件将数据划分为组，
如将全国人民按名族划分就是将全国人民的记录归纳为名族的记录。
分组的目的是为了更好的提现组的数据特性，如我们将全国人民的记录按名族划分后可以统计各名族的人数，占比等数据。
only_full_group_by模式
在5.6版本中配置文件中sql_mode默认没有这种模式，这是为了更好的提现组的特性而增加的模式，即在划分组后，个人的数据应该被隐藏起来，如果不隐藏则如下：
可以看见分组后，实际是将每个分组的第一条记录保留下来了，但是其他的字段的数据对于组来说是片面的，不全面的，所以应该被隐藏。
我们可以在my.ini文件中将sql_mode再添加一个only_full_group_by，不让分组依据以外的其他字段的数据可以被访问到。(加完记得保存配置文件并重启服务端)
查看sql_mode:select @@global.sql_mode
在这个模式下，再查询分组依据以外的字段就会报错：
聚合函数
虽然不能直接访问我们的单条记录的数据，但是可以以聚合函数的方式访问到我们这些分类好的数据的记录所统合的一些数据。
| 聚合函数 | 作用 |
|---|---|
| max\min | 最大\最小 |
| avg | 平均值 |
| sum | 总和 |
| count | 统计 |
| group_concat | 罗列、字符拼接 |
-
获取每个游戏最贵的角色
select game, max(price) from role group by game; # 以game字段划分，game还是可以访问的。 # 还可以给查询的字段起别名，让其显示更直观 select game, max(price) as '最贵角色' from role group by game;
-
获取最大，最小，平均，总和等数据
select game,max(price) '最贵',min(price) '最便宜',avg(price) '平均价格',sum(price) '总价' from role group by game; # 起别名时可以不加as
-
统计每个游戏的角色个数
select game,count(id) as '角色总数' from role group by game;
-
查看每个游戏下有哪些角色（相当于更改了表的组织结构）
# 错误用法，其他字段不能直接访问 select game,name from role group by game; # group_concat最基础的用法，罗列分组下的字段数据 select game,group_concat(name) from role group by game;
# 还可以拼接其他字符串，或者按记录拼接多个字段 select game,group_concat('角色','【',name,'】')as '游戏角色' from role group by game;
having 分组后筛选
- where是分组前的就可以做的筛选，在语法上一定在group by的前面。
- having是对分组后的组织表进行筛选，在语法上也跟随在group by的后面。
问：统计各游戏角色年龄在20岁以上的平均价格，并保留大于10000的记录
分析：
- 角色年龄20岁以上是对分组前的每条数据进行筛选 -- where
- 按照游戏进行分组 -- group by
- 分组后，对组特征‘平均价格’进行筛选 -- having
select game '游戏', avg(price) '角色平均价格' from role
where age > 20
group by game
having avg(price) > 10000;
distinct去重
放在搜索字段的前面：select distinct 字段1,字段2 from 表
当字段组合重复的时候，只显示一条记录，反过来说只显示不同的字段组合的记录。
select distinct id,age from role; # 关键字针对的是多个字段组合的结果
select distinct age from role;
select distinct gender, office from role;
order by排序
对任何表结果都可以进行排序，where筛选过的、分组过的、having筛选过的都可以，
不过分组前的结果可以以表中原本的字段为依据来排序，
而分组后的结果只能以分组依据和组字段来为依据来排序。
单字段排序
select * from role order by age; # 默认升序
select * from role order by age asc; # 升序(asc可以省略)
select * from role order by age desc; # 降序
多字段排序
先对一个字段进行排序，对于这个字段数据相同的值再按第二个字段排序。
select * from role order by age,price desc; # 先对age升序排，再对价格降序排
对分组后的结果进行排序
统计各部门年龄在10岁以上的角色平均价格，并且保留平均价格大于1000的部门，然后对平均价格进行排序。
select game, avg(price) from role
where age>10 # 初次筛选
group by game # 分组依据
having avg(price)>1000 # 二次筛选
order by avg(price); # 通过组字段为依据排队
limit分页
当表中的数据级别很大时，我们不会一次获取全部数据，所以我们对于拿到的结果所显示的记录条数应该做限制，就要用到limit(n)和limit(n1,n2)
限制显示条数
select * from role limit 5; # 显示5条
选择起点
select * from role limit 5,6; # 从5后面开始显示6条（如果没有第一个参数默认为0）
regexp正则表达式
正则是门独立的语言，而mysql也提供了接口来支持正则，关于正则的详细用法，可以看：内置模块之re模块及正则表达式语法简述
select * from role where name regexp '^[巴金].*[斯布]$';
ps:笔者在用正则表达式时，.*?组合取消贪婪失效，反之报错操作数重复，
这可能是版本问题，将*?都当做量词了。
组合练习
-
查询男女角色的平均价格
select gender, avg(price) from role group by gender; -
统计各游戏角色年龄在18岁以上20岁以下的角色价格总和
select game, sum(price) from role where age between 18 and 20 group by game；