MySQL基础知识

order by,group by ,模糊查询 ，分页查询 的原理和使用方法

1.order by 排序

原理：

/*
MySQL 会给每个线程分配一块内存用于排序，称为sort_buffer。
全字段排序:
一般带有order by 的SQL语句 会初始化sort_buffer，放入需要返回的字段(比如 name,age)。
查找一条记录就放入到sort_buffer,继续找，直到找完符合条件的为止。
对sort_buffer中的数据按照字段如name快速排序(归并排序);
排序这个动作可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。
缺点: 如果查询要返回的字段很多的话，那么sort_buffer 要放的字段数太多，要分成很多个临时文件来归并排序。排序的心梗会很差。
rowid排序:
参数 max_length_for_sort_data 是MySQL 中专门控制用于排序的行数据的长度的。
如果单行的长度超过这个值，MySQL就认为单行太大，需要换一个算法。
新的算法只将要排序的字段和主键id 放入sort_buffer中，最后排完序根据主键id的值回表去取对应的字段返回给客户端。
*/

方法：

按一个或多个字段对查询结果进行排序 用法：order by col1,col2,col3…

说明：

先按 col1 排序如果 col1 相同就按照 col2 排序，依次类推

col1，col2，col3 可以是 select 后面的字段也可以不是

默认是升序，也可以在字段后面加 asc 显示说明是升序，desc 为降序 order by 后面除了跟 1 个或多个字段，还可以写表达式，函数，别名等

ORDER BY [排序]
#查询显示所有学生信息 [排序：性别(降序)、年龄(升序)]
SELECT * FROM students ORDER BY s_sex DESC, s_age ASC;

2.关于 mysql 的 group by 的特殊：

原理：

/*对某个列进行分组，列相同的可以放在一起再用聚合函数来查找想要的结果。
使用COUNT()、AVG()、MIN()、MAX()等聚合函数可实现对分组的过滤，聚合函数会分别对各组数据进行聚合。*/

方法：

注意：在 SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的，换句话说，SELECT 列表中最好不要出现 GROUP BY 子句中没有的列。

分组查询 [GROUP BY]
#统计男女生人数
#SELECT s_sex '性别',COUNT(*) '人数' FROM students GROUP BY s_sex;
#[不推荐] SELECT s_name, COUNT(*) FROM students GROUP BY s_sex;
SELECT s_sex, COUNT(*) FROM students GROUP BY s_sex;
SELECT s_name, s_sex, COUNT(*) FROM students GROUP BY s_sex,s_name;
SELECT s_name, s_sex, COUNT(*) FROM students GROUP BY s_name,s_sex;
​
#统计年龄最大者
SELECT * FROM students WHERE s_age=
(
    SELECT MAX(s_age) FROM students
);
#SELECT MAX(s_age) FROM students GROUP BY s_age;#error
​
#统计男女生中年龄最大者[s_sex]
SELECT s_sex as '性别',MAX(s_age) '最大年龄' FROM students GROUP BY s_sex;
​

3.having 筛选

having 与 where 类似,可筛选数据

having 与 where 不同点

where 针对表中的列发挥作用,查询数据；having 针对查询结果中的列发挥作用, 筛选数据 where 后面不能写分组函数，而 having 后面可以使用分组函数 having 只用于 group by 分组统计语句

#WHERE子句之后不可以编写聚合[分组]函数
#查询年龄最大者信息
#SELECT * FROM students WHERE s_age=MAX(s_age);#error
#但可以将其[聚合函数]置于子查询中
SELECT * FROM students WHERE s_age=
(
    SELECT MAX(s_age) FROM students
);
​
#SELECT [SELECT列表] FROM 表名 WHERE [针对原表数据进行筛选]
​
#3.HAVING子句
#GROUP BY [分组依据] HAVING [基于分组后结果的进一步筛选]
​
#统计男女人数 [增加筛选条件：人数达到3人以上的]
SELECT s_sex, COUNT(1) num FROM students 
    GROUP BY s_sex HAVING num>=3;
​
SELECT s_sex, COUNT(1) FROM students 
    GROUP BY s_sex HAVING COUNT(1)>=3;

4.模糊查询

原理：

/*模糊查询是通过将输入的查询条件作为一个子字符串到一个字符串中查询是否查询此子字符串。如现在有一个字符串未“张三和李四”，输入“张三”或者“李四“都可以查询到此字符串。因为“张三”和“李四“都是属于字符串“张三和李四”的子字符串。那么就可以根据这种原理去查询数据是通过筛选数据。*/

方法：

LIKE语句的语法格式为：

select * from 表名 where 字段名 like 对应值（字符串）;

注：主要是针对字符型字段的，它的作用是在一个字符型字段列中检索包含对应字符串的。

下面列举常用的几种标识含义；

A:% 表示零个或多个字符的任意字符串：

1. LIKE'Mi%' 将搜索以字母 Mi开头的所有字符串（如 Michael）。
​
2. LIKE'%er' 将搜索以字母 er 结尾的所有字符串（如 Worker、Reader）。
​
3. LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 When、Green）。

B:_（下划线）表示任何单个字符：

 1. LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。

C：[ ] 表示指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符：

LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。 LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。 D：[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符：

  1. LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。

E：* 它同于DOS命令中的通配符，代表多个字符：

   1. c*c代表cc,cBc,cbc,cabdfec等多个字符。

F：？同于DOS命令中的？通配符，代表单个字符 :

   1. b?b代表brb,bFb等。

G：# 大致同上，不同的是代只能代表单个数字。

   1. k#k代表k1k,k8k,k0k 。

F：[!] 排除 它只代表单个字符。 下面举例说明： 例1，查询name字段中包含有“明”字的。 select * from table1 where name like '%明%'

例2，查询name字段中以“李”字开头。 select * from table1 where name like '李*'

例3，查询name字段中含有数字的。 select * from table1 where name like '%[0-9]%'

例4，查询name字段中含有小写字母的。 select * from table1 where name like '%[a-z]%'

例5，查询name字段中不含有数字的。 select * from table1 where name like '%[!0-9]%'

以上例子能列出什么值来显而易见。但在这里，我们着重要说明的是通配符“*”与“%”的区别。

很多朋友会问，为什么我在以上查询时有个别的表示所有字符的时候用"%"而不用“*”？先看看下面的例子能分别出现什么结果： select * from table1 where name like '明' select * from table1 where name like '%明%'

大家会看到，前一条语句列出来的是所有的记录，而后一条记录列出来的是name字段中含有“明”的记录，所以说，当我们作字符型字段包含一个子串的查询时最好采用“%”而不用“”,用“”的时候只在开头或者只在结尾时，而不能两端全由“*”代替任意字符的情况下。

5.limit 分页

原理：

/*
limit m,n
​
m 表示从下标为 m 的记录开始查询，第一条记录下标为 0，n 表示取出 n 条出来，如 果从 m 开始不够 n 条了，就有几条取几条。m=(page-1)*n，（page 页码，n 表示每页显示的条数）
​
如果第一页 limit 0,n
​
如果第二页 limit n,n
​
依次类推，得出公式 limit (page-1)*n , n
*/

方法：

#5.LIMIT m,n
SELECT * FROM students LIMIT 0,5;
SELECT * FROM students LIMIT 0,10;
​
#查询获取原表前三天记录
SELECT * FROM students LIMIT 0,3;
​
#查询年龄最小的两位同学的信息
SELECT * FROM students ORDER BY s_age LIMIT 0,2;
​
#查询年龄最大的三位同学的信息
SELECT * FROM students ORDER BY s_age DESC LIMIT 0,3;
​
#查询年龄最大者信息[方案-1：子查询]
SELECT * FROM students WHERE s_age=
(
    SELECT MAX(s_age) FROM students
);
#查询年龄最大者信息[方案-2：LIMIT]
SELECT * FROM students ORDER BY s_age DESC LIMIT 0,1;
​
#补充测试数据
INSERT INTO student VALUES
    (5,'我爱罗','男',18,'13884558123',1),
    (6,'小李','男',18,'13884558123',1),
    (7,'漩涡鸣人','男',23,'13884558123',1),
    (8,'彭于晏','女',24,'13884558123',2),
    (9,'张学友','女',23,'13884558123',2),
    (10,'胡歌','男',21,'13884558123',2),
    (11,'黎明','男',20,'13884558123',2),
    (12,'郭富城','男',21,'13884558123',3),
    (13,'周星驰','女',22,'13884558123',3),
    (14,'成龙','女',25,'13884558123',3),
    (15,'马龙','男',19,'13884558123',3);
​
#查看所有信息
SELECT * FROM students;
​
#将students表中的数据[15条] 分三页显示
#第一页 [1-5]
SELECT * FROM students LIMIT 0,5;
#第一页 [6-10]
SELECT * FROM students LIMIT 5,5;
#第一页 [11-15]
SELECT * FROM students LIMIT 10,5;
​
​
#公式limit (page-1)*n , n [page表示页码 n表示每页显示的条数]
SELECT * FROM students LIMIT 0,5;
#SELECT * FROM students LIMIT (1-1)*5,5;
​
SELECT * FROM students LIMIT 5,5;
#SELECT * FROM students LIMIT (2-1)*5,5;
​
SELECT * FROM students LIMIT 10,5;
#SELECT * FROM students LIMIT (3-1)*5,5;

左连接，右连接，内部连接

1、左连接的定义：

是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

下边以student表和teacher表为例子，student、teacher之间的左连接条件为：student.class_id=teacher.class_id;查询语句为：

select * from student left join teacher on student.class_id=teacher.class_id;

student表中的有15条记录，teacher表中2条记录

查询结果：将student表的记录都查询出来，student表中的class_id在teacher表中不存在，则记录为空

2、右连接的定义

是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

查询语句为：

select * from student right join teacher on student.class_id=teacher.class_id;

结果：student表只显示和teacher表classid相等的11行数据，teacher表的记录全部显示出来

3、内链接：

使用比较运算符根据每个表共有的列的值匹配两个表中的行；

查询语句为：

select * from student inner join teacher on student.class_id=teacher.class_id;

结果：显示student.class_id=teacher.class_id的11行记录

内连接和左连接的区别

内连接用于一对一，内连接不会有全null的数据

左右连接用于一对多

碰见多表连接查询的时候可以一直用左连接，肯定能做到查询结果，虽然不太合适

左连接之前用where和连接之后用where结果会被影响

什么时候用where?