MySQL（三）之表的查询(聚合函数、常用关键字、运算符、多表查询)

申明：资料来源于网络及书本，通过理解、实践、整理成学习笔记。

MySQL聚合函数

示例表格：
select * from userinfo_1;
执行结果：
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 路飞 |    90 |
|  2 | 索隆 |    85 |
|  3 | 娜美 |    87 |
|  4 | 罗宾 |    96 |
+----+------+-------+

1. 最大值（max）：select max(字段名) from 表名

   mysql> select max(score) from userinfo_1;
   执行结果：示例表格中score的最大值为96
   +------------+
   | MAX(score) |
   +------------+
   |         96 |
   +------------+
   

2. 最小值（min）：select min(字段名) from 表名

   mysql> select min(score) from userinfo_1;
   执行结果：示例表格中score的最小值为85
   +------------+
   | min(score) |
   +------------+
   |         85 |
   +------------+
   

3. 平均值（avg）：select avg(字段名) from 表名

   mysql> select avg(score) from userinfo_1;
   执行结果：示例表格中score的绝对值为89.5000
   +------------+
   | avg(score) |
   +------------+
   |    89.5000 |
   +------------+
   

4. 计数（count）：select count(字段名) from 表名

   mysql> select count(name) from userinfo_1;
   执行结果：示例表格中name的个数为4
   +-------------+
   | count(name) |
   +-------------+
   |           4 |
   +-------------+
   

5. 求和（sum）：select sum(字段名) from 表名

   mysql> select sum(score) from userinfo_1;
   执行结果：实例表格中score的总和为358
   +------------+
   | sum(score) |
   +------------+
   |        358 |
   +------------+
   

MySQL常用关键字

1. group By：分组
   select 字段2,聚合函数字段 from 表名 group by 字段1
2. order by：排序(asc升序 desc降序)
   select * from 表名 order by 字段名 asc/desc
3. limit：返回指定的记录数(n指定第一个返回记录行的偏移量，m指定返回记录行的最大数目。)
   select * from 表名 order by 字段名 asc/desc limit n,m
4. distinct：去除重复数据
   select distinct name,id from user;
5. as：取表的别名
6. Constraints ：SQL 约束
7. primary key ：主键约束
8. auto_increment ：自增
9. foreign key：外键约束
10. unique：唯一约束
11. not null：非空约束
12. default：默认值约束
13. unsigned：无符号
14. Index 索引
15. like：模糊查询
16. % ：通配符，匹配所有字符、数字和空字符，可以匹配多个
17. -：通配符，匹配单个字符、数字和空字符

MySQL运算符

算术运算符

符号	作用	符号	作用
+	加法运算	%	求余运算
-	减法运算	DIV	除法运算，返回商
*	乘法运算	MOD	求余运算，返回余数
/	除法运算

比较运算符

运算符	名称	运算符	名称
=	等于	is not null	不为空
>	大于	between and	两者之间
<	小于	in	在…中
>=	大于等于	not in	不在…中
<=	小于等于	like	模式匹配
!=	加法运算	not like	模式匹配
is null	为空	regexp	正则表达式

逻辑运算符

符号	作用	符号	作用
AND	与	NOT	非
OR	或	XOR	异或

位运算符

符号	作用
&	按位与。进行该运算时，数据库系统会先将十进制的数转换为二进制的数。然后对应操作数的每个二进制位上进行与运算。1和1相与得1，与0相与得0。运算完成后再将二进制数变回十进制数
|	按位或。将操作数化为二进制数后，每位都进行或运算。1和任何数进行或运算的结果都是1，0与0或运算结果为0
~	与按位取反。将操作数化为二进制数后，每位都进行取反运算。1取反后变成0，0取反后变成I
^	按位异或。将操作数化为二进制数后，每位都进行异或运算。相同的数异或之后结果是0，不同的数异或之后结果为l
<<	按位左移。“m<<n”表示m的二进制数向左移n位，右边补上n个0。例如，二进制数001左移l位后将变成010
>>	按位右移。“m>>n”表示m的二进制数向右移n位，左边补上n个0。例如，二进制数011右移l位后变成001，最后一个1直接被移出

运算符的优先级

符号	运算符
1	！
2	~
3	^
4	*,/,%,MOD
5	+,-
6	>>,<<
7	&
8	|
9	=,<=>,<,>,<=,>=,<>,IN,IS,NULL,LIKE,REGEXP
10	BETWEEN AND,CASE,WHEN,THEN,ELSE
11	NOT
12	&&,AMD
13	||,OR,XOR

MySQL多表查询

实例表格
表1：
mysql> select * from t1;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 路飞 |    90 |
|  2 | 索隆 |    85 |
|  3 | 娜美 |    87 |
|  4 | 罗宾 |    96 |
+----+------+-------+
表2：
mysql> select * from t2;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 路飞 |    90 |
|  2 | 索隆 |    85 |
|  3 | 凯多 |    99 |
|  4 | 红发 |    98 |
+----+------+-------+
4 rows in set (0.00 sec)

内连接

内连接：(inner join …on)查询两个表的公有部分

select 表1.字段,表2.字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段

mysql> select * from t1 inner join t2 on t1.name = t2.name;
+----+------+-------+----+------+-------+
| id | name | score | id | name | score |
+----+------+-------+----+------+-------+
|  1 | 路飞 |    90 |  1 | 路飞 |    90 |
|  2 | 索隆 |    85 |  2 | 索隆 |    85 |
+----+------+-------+----+------+-------+

左连接

左连接：(left join …on)以左表为基准，右表提供数据与左表组成新的表，如果右表中有左表没有的数据则不显示，右表中缺失左表中的数据则以null补充

select 表1.字段,表2.字段 from 表1 left join 表2 on 表1.字段 = 表2.字段

mysql> select * from t1 left join t2 on t1.name = t2.name;
+----+------+-------+------+------+-------+
| id | name | score | id   | name | score |
+----+------+-------+------+------+-------+
|  1 | 路飞 |    90 |    1 | 路飞 |    90 |
|  2 | 索隆 |    85 |    2 | 索隆 |    85 |
|  3 | 娜美 |    87 | NULL | NULL |  NULL |
|  4 | 罗宾 |    96 | NULL | NULL |  NULL |
+----+------+-------+------+------+-------+

右连接

右连接：(right join …on)以左表为基准，左表提供数据与右表组成新的表，如果左表中有右表没有的数据则不显示，左表中缺失右表中的数据则以null补充

select 表1.字段,表2.字段 from 表1 right join 表2 on 表1.字段 = 表2.字段

mysql> select * from t1 right join t2 on t1.name = t2.name;
+------+------+-------+----+------+-------+
| id   | name | score | id | name | score |
+------+------+-------+----+------+-------+
|    1 | 路飞 |    90 |  1 | 路飞 |    90 |
|    2 | 索隆 |    85 |  2 | 索隆 |    85 |
| NULL | NULL |  NULL |  3 | 凯多 |    99 |
| NULL | NULL |  NULL |  4 | 红发 |    98 |
+------+------+-------+----+------+-------+

全连接

select 表1.字段,表2.字段 from 表1 left join 表2 on 表1.字段 = 表2.字段
union
select 表1.字段,表2.字段 from 表1 right join 表2 on 表1.字段 = 表2.字段

mysql> select * from t1 left join t2 on t1.name = t2.name
    -> union
    -> select * from t1 right join t2 on t1.name = t2.name;
+------+------+-------+------+------+-------+
| id   | name | score | id   | name | score |
+------+------+-------+------+------+-------+
|    1 | 路飞 |    90 |    1 | 路飞 |    90 |
|    2 | 索隆 |    85 |    2 | 索隆 |    85 |
|    3 | 娜美 |    87 | NULL | NULL |  NULL |
|    4 | 罗宾 |    96 | NULL | NULL |  NULL |
| NULL | NULL |  NULL |    3 | 凯多 |    99 |
| NULL | NULL |  NULL |    4 | 红发 |    98 |
+------+------+-------+------+------+-------+

一个坚持学习，坚持成长，坚持分享的人，即使再不聪明，也一定会成为优秀的人！

整理不易，如果看完觉得有所收获的话，记得一键三连哦，谢谢！