MySQL--基础二
2019-04-18 本文已影响0人
昆仑草莽
本节总结MySQL的筛选条件，聚合与分组，子查询，连接查询。
MySQL的筛选条件
MySQL中的比较运算符：
| 比较运算符 | 含义 |
|---|---|
| = | 等于 （注意！不是 ==） |
| != 或 <> | 不等于 |
| >= | 大于等于 |
| <= | 小于等于 |
| > | 大于 |
| < | 小于 |
| is null | 属于空 |
| is not null | 不属于空 |
比较运算符实例：
mysql> select * from student where age>=18; #使用比较运算符
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 1 | apple | 19 | F | first |
| 3 | banana | 18 | F | first |
| 4 | mango | 20 | M | first |
| 6 | lichee | 19 | F | second |
| 7 | longan | 18 | M | second |
| 8 | loquat | 20 | F | second |
| 10 | nectarine | 19 | F | third |
| 11 | olive | 18 | M | third |
| 12 | orange | 20 | F | third |
+------+-----------+------+------+--------+
9 rows in set (0.00 sec)
MySQL中的逻辑运算符：
| 逻辑运算符 | 含义 |
|---|---|
| not | 非 |
| and | 与 |
| or | 或 |
逻辑运算符实例：
mysql> select *from student where age=19 and sex='F';
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 1 | apple | 19 | F | first |
| 6 | lichee | 19 | F | second |
| 10 | nectarine | 19 | F | third |
+------+-----------+------+------+--------+
3 rows in set (0.00 sec)
MySQL筛选条件的其他操作：
1、排序：
select col from tb_name order by col [asc/desc]; asc:正序,desc:倒序
mysql> select * from student order by age; #对age进行排序
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 2 | pear | 17 | M | first |
| 5 | marc | 17 | M | second |
| 9 | melon | 17 | M | third |
| 3 | banana | 18 | F | first |
| 7 | longan | 18 | M | second |
| 11 | olive | 18 | M | third |
| 1 | apple | 19 | F | first |
| 6 | lichee | 19 | F | second |
| 10 | nectarine | 19 | F | third |
| 4 | mango | 20 | M | first |
| 8 | loquat | 20 | F | second |
| 12 | orange | 20 | F | third |
+------+-----------+------+------+--------+
12 rows in set (0.00 sec)
2、限制：
select col from tb_name limit [start count]；limit start:开始的行,limit count需要限制的行数
mysql> select * from student limit 3; #最前面3行数据
+------+--------+------+------+-------+
| id | name | age | sex | grade |
+------+--------+------+------+-------+
| 1 | apple | 19 | F | first |
| 2 | pear | 17 | M | first |
| 3 | banana | 18 | F | first |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from student limit 1,4; #从第2行到第5行数据，因为索引是从0开始的。
+------+--------+------+------+--------+
| id | name | age | sex | grade |
+------+--------+------+------+--------+
| 2 | pear | 17 | M | first |
| 3 | banana | 18 | F | first |
| 4 | mango | 20 | M | first |
| 5 | marc | 17 | M | second |
+------+--------+------+------+--------+
4 rows in set (0.00 sec)
3、去重：
select distinct * from tb_name; distinct:表示去重
mysql> select * from student; #原始表
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 1 | apple | 19 | F | first |
| 2 | pear | 17 | M | first |
| 3 | banana | 18 | F | first |
| 4 | mango | 20 | M | first |
| 5 | marc | 17 | M | second |
| 6 | lichee | 19 | F | second |
| 7 | longan | 18 | M | second |
| 8 | loquat | 20 | F | second |
| 9 | melon | 17 | M | third |
| 10 | nectarine | 19 | F | third |
| 11 | olive | 18 | M | third |
| 12 | orange | 20 | F | third |
| 1 | apple | 19 | F | first |
+------+-----------+------+------+--------+
13 rows in set (0.01 sec)
mysql> select distinct * from student; #去重之后的表
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 1 | apple | 19 | F | first |
| 2 | pear | 17 | M | first |
| 3 | banana | 18 | F | first |
| 4 | mango | 20 | M | first |
| 5 | marc | 17 | M | second |
| 6 | lichee | 19 | F | second |
| 7 | longan | 18 | M | second |
| 8 | loquat | 20 | F | second |
| 9 | melon | 17 | M | third |
| 10 | nectarine | 19 | F | third |
| 11 | olive | 18 | M | third |
| 12 | orange | 20 | F | third |
+------+-----------+------+------+--------+
12 rows in set (0.00 sec)
4、模糊查询：
select * from tb_name where col like '%'; %:表示任意多个字符,_:表示任意单个字符
mysql> select * from student where name like 'm%'; #数值中m开头的全部查出来
+------+-------+------+------+--------+
| id | name | age | sex | grade |
+------+-------+------+------+--------+
| 4 | mango | 20 | M | first |
| 5 | marc | 17 | M | second |
| 9 | melon | 17 | M | third |
+------+-------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from student where name like '%o%'; #数值中有o的全部查询出来
+------+--------+------+------+--------+
| id | name | age | sex | grade |
+------+--------+------+------+--------+
| 4 | mango | 20 | M | first |
| 7 | longan | 18 | M | second |
| 8 | loquat | 20 | F | second |
| 9 | melon | 17 | M | third |
| 11 | olive | 18 | M | third |
| 12 | orange | 20 | F | third |
+------+--------+------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from student where name like '%o_'; #数值中0后面只有一个字符的
+------+-------+------+------+-------+
| id | name | age | sex | grade |
+------+-------+------+------+-------+
| 9 | melon | 17 | M | third |
+------+-------+------+------+-------+
1 row in set (0.00 sec)
mysql> select * from student where name like '%o____'; #数值中0后面有四个字符的 这里是四个 _
+------+--------+------+------+--------+
| id | name | age | sex | grade |
+------+--------+------+------+--------+
| 7 | longan | 18 | M | second |
| 8 | loquat | 20 | F | second |
| 11 | olive | 18 | M | third |
+------+--------+------+------+--------+
3 rows in set (0.00 sec)
5、范围查询：
select * from student where between a and b； between:表示a和b之间的数
select * from student where in (a,b,c)； in:表示从a,b,c之间取值
mysql> select * from student where age between 17 and 19; #从 age 为17到19的全部取出
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 1 | apple | 19 | F | first |
| 2 | pear | 17 | M | first |
| 3 | banana | 18 | F | first |
| 5 | marc | 17 | M | second |
| 6 | lichee | 19 | F | second |
| 7 | longan | 18 | M | second |
| 9 | melon | 17 | M | third |
| 10 | nectarine | 19 | F | third |
| 11 | olive | 18 | M | third |
| 1 | apple | 19 | F | first |
+------+-----------+------+------+--------+
10 rows in set (0.00 sec)
mysql> select * from student where age in (19,20); #取出age为19 和20 的所有数据。
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 1 | apple | 19 | F | first |
| 4 | mango | 20 | M | first |
| 6 | lichee | 19 | F | second |
| 8 | loquat | 20 | F | second |
| 10 | nectarine | 19 | F | third |
| 12 | orange | 20 | F | third |
| 1 | apple | 19 | F | first |
+------+-----------+------+------+--------+
7 rows in set (0.00 sec)
MySQL的聚合与分组
MySQL常用的聚合函数：
| 聚合函数 | 含义 |
|---|---|
| count(col) | 统计列里面数据的个数 |
| sum(col) | 对列的数据进行求和 |
| max(col) | 列出列内的最大值 |
| min(col) | 列出列内的最小值 |
| avg(col) | 求出列内的平均值 |
| group_concat | 列出字段全部数值 |
mysql>select count(id),avg(age),max(age),min(age),sum(age),group_concat(name)from student;
+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------+
| count(id) | avg(age) | max(age) | min(age) | sum(age) | group_concat(name) |
+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------+
| 13 | 18.5385 | 20 | 17 | 241 | apple,pear,banana,mango,marc,lichee,longan,loquat,melon,nectarine,olive,orange,apple |
+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
MySQL分组查询：
select count(age),grade from student group by grade；group by:分组查询关键字在分组情况下，只能出现分组字段和聚合字段，其他的字段没有意义，会报错！
mysql> select group_concat(name),grade from student group by grade; #以grade字段分组
+-------------------------------+--------+
| group_concat(name) | grade |
+-------------------------------+--------+
| apple,pear,banana,mango,apple | first |
| marc,lichee,longan,loquat | second |
| melon,nectarine,olive,orange | third |
+-------------------------------+--------+
3 rows in set (0.01 sec)
MySQL的聚合筛选：
select count(age) ,grade from student where id>9 group by grade,age having age>18; 加having条件表达式，可以对输出结果进行限制。having后的条件必须是group by后的字段
mysql> select count(age) ,grade from student where age>18 group by grade,age having age>18;
+------------+--------+
| count(age) | grade |
+------------+--------+
| 2 | first |
| 1 | first |
| 1 | second |
| 1 | second |
| 1 | third |
| 1 | third |
+------------+--------+
6 rows in set (0.00 sec)
| 假如说一个查询语句同时包含了别名（as），聚合函数，where，having，那么，他执行的顺序是 |
|---|
| 1、先执行where>>>>2、然后执行聚合函数和别名>>>>3、最后执行having |
MySQL子查询
子查询：将一个查询结果留下来用与下一次查询（select中嵌套select）本文对子查询只做简单的总结。后期会有全面的子查询总结。
要求：
嵌套在查询内部
必须始终都出现在（）内。
select avg(age) from student; 查询学生的平均年龄
select * from student where age>(select avg(age) from student); 查出大于平均年龄的数据
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
| 18.5385 |
+----------+
1 row in set (0.00 sec)
mysql> select * from student where age>(select avg(age) from student);
+------+-----------+------+------+--------+
| id | name | age | sex | grade |
+------+-----------+------+------+--------+
| 1 | apple | 19 | F | first |
| 4 | mango | 20 | M | first |
| 6 | lichee | 19 | F | second |
| 8 | loquat | 20 | F | second |
| 10 | nectarine | 19 | F | third |
| 12 | orange | 20 | F | third |
| 1 | apple | 19 | F | first |
+------+-----------+------+------+--------+
7 rows in set (0.00 sec)
MySQL的连接查询（多表查询）
本文对多表查询只做简单的描述，全面的总结将在后期完成。
MySQL的内连接inner join
1、无条件内连接：
无条件内连接，又名交叉连接/笛卡尔连接，第一张表种的每一项会和另一张表的每一项依次组合，会产生笛卡儿积，数据多的话会影响数据库的性能。
2、有条件内连接：
在无条件内链接的基础上，加上一个on子句，当连接的时候，筛选出那些有实际意义的记录来进行组合，其实笛卡儿积不会消失，只是隐藏不显示而已，性能上少了显示这一环节，会有不小的提升。
mysql> select * from student join class ; #产生笛卡儿积，
+------+-----------+------+------+--------+------+--------+--------+
| id | name | age | sex | grade | id | name | grade |
+------+-----------+------+------+--------+------+--------+--------+
| 1 | apple | 19 | F | first | 1 | class1 | first |
| 1 | apple | 19 | F | first | 1 | class2 | second |
| 1 | apple | 19 | F | first | 3 | class3 | third |
| 2 | pear | 17 | M | first | 1 | class1 | first |
| 2 | pear | 17 | M | first | 1 | class2 | second |
| 2 | pear | 17 | M | first | 3 | class3 | third |
| 3 | banana | 18 | F | first | 1 | class1 | first |
| 3 | banana | 18 | F | first | 1 | class2 | second |
| 3 | banana | 18 | F | first | 3 | class3 | third |
| 4 | mango | 20 | M | first | 1 | class1 | first |
| 4 | mango | 20 | M | first | 1 | class2 | second |
| 4 | mango | 20 | M | first | 3 | class3 | third |
| 5 | marc | 17 | M | second | 1 | class1 | first |
| 5 | marc | 17 | M | second | 1 | class2 | second |
| 5 | marc | 17 | M | second | 3 | class3 | third |
| 6 | lichee | 19 | F | second | 1 | class1 | first |
| 6 | lichee | 19 | F | second | 1 | class2 | second |
| 6 | lichee | 19 | F | second | 3 | class3 | third |
| 7 | longan | 18 | M | second | 1 | class1 | first |
| 7 | longan | 18 | M | second | 1 | class2 | second |
| 7 | longan | 18 | M | second | 3 | class3 | third |
| 8 | loquat | 20 | F | second | 1 | class1 | first |
| 8 | loquat | 20 | F | second | 1 | class2 | second |
| 8 | loquat | 20 | F | second | 3 | class3 | third |
| 9 | melon | 17 | M | third | 1 | class1 | first |
| 9 | melon | 17 | M | third | 1 | class2 | second |
| 9 | melon | 17 | M | third | 3 | class3 | third |
| 10 | nectarine | 19 | F | third | 1 | class1 | first |
| 10 | nectarine | 19 | F | third | 1 | class2 | second |
| 10 | nectarine | 19 | F | third | 3 | class3 | third |
| 11 | olive | 18 | M | third | 1 | class1 | first |
| 11 | olive | 18 | M | third | 1 | class2 | second |
| 11 | olive | 18 | M | third | 3 | class3 | third |
| 12 | orange | 20 | F | third | 1 | class1 | first |
| 12 | orange | 20 | F | third | 1 | class2 | second |
| 12 | orange | 20 | F | third | 3 | class3 | third |
| 1 | apple | 19 | F | first | 1 | class1 | first |
| 1 | apple | 19 | F | first | 1 | class2 | second |
| 1 | apple | 19 | F | first | 3 | class3 | third |
+------+-----------+------+------+--------+------+--------+--------+
39 rows in set (0.00 sec)
mysql> select * from student join class on student.grade=class.grade; #消除笛卡儿积，其实笛卡儿积不会消失，只是隐藏不显示而已
+------+-----------+------+------+--------+------+--------+--------+
| id | name | age | sex | grade | id | name | grade |
+------+-----------+------+------+--------+------+--------+--------+
| 1 | apple | 19 | F | first | 1 | class1 | first |
| 2 | pear | 17 | M | first | 1 | class1 | first |
| 3 | banana | 18 | F | first | 1 | class1 | first |
| 4 | mango | 20 | M | first | 1 | class1 | first |
| 5 | marc | 17 | M | second | 1 | class2 | second |
| 6 | lichee | 19 | F | second | 1 | class2 | second |
| 7 | longan | 18 | M | second | 1 | class2 | second |
| 8 | loquat | 20 | F | second | 1 | class2 | second |
| 9 | melon | 17 | M | third | 3 | class3 | third |
| 10 | nectarine | 19 | F | third | 3 | class3 | third |
| 11 | olive | 18 | M | third | 3 | class3 | third |
| 12 | orange | 20 | F | third | 3 | class3 | third |
| 1 | apple | 19 | F | first | 1 | class1 | first |
+------+-----------+------+------+--------+------+--------+--------+
13 rows in set (0.00 sec)
MySQL的外连接（左连接，有连接）
左外连接： （以左表为基准）
两张表做连接的时候，在连接条件不匹配的时候留下左表中的数据，而右表中的数据以NULL填充
右外连接： （以右表为基准）
对两张表做连接的时候，在连接条件不匹配的时候，留下右表中的数据，而左表中的数据以NULL填充
mysql> select * from student right join class on student.id=class.id; #右连接，以右表为基准，就是class表
+------+--------+------+------+-------+------+--------+--------+
| id | name | age | sex | grade | id | name | grade |
+------+--------+------+------+-------+------+--------+--------+
| 1 | apple | 19 | F | first | 1 | class1 | first |
| 1 | apple | 19 | F | first | 1 | class2 | second |
| 3 | banana | 18 | F | first | 3 | class3 | third |
| 1 | apple | 19 | F | first | 1 | class1 | first |
| 1 | apple | 19 | F | first | 1 | class2 | second |
+------+--------+------+------+-------+------+--------+--------+
5 rows in set (0.01 sec)
mysql> select * from student left join class on student.id=class.id; #左连接，以左表为基准，就是student表
+------+-----------+------+------+--------+------+--------+--------+
| id | name | age | sex | grade | id | name | grade |
+------+-----------+------+------+--------+------+--------+--------+
| 1 | apple | 19 | F | first | 1 | class1 | first |
| 1 | apple | 19 | F | first | 1 | class1 | first |
| 1 | apple | 19 | F | first | 1 | class2 | second |
| 1 | apple | 19 | F | first | 1 | class2 | second |
| 3 | banana | 18 | F | first | 3 | class3 | third |
| 2 | pear | 17 | M | first | NULL | NULL | NULL |
| 4 | mango | 20 | M | first | NULL | NULL | NULL |
| 5 | marc | 17 | M | second | NULL | NULL | NULL |
| 6 | lichee | 19 | F | second | NULL | NULL | NULL |
| 7 | longan | 18 | M | second | NULL | NULL | NULL |
| 8 | loquat | 20 | F | second | NULL | NULL | NULL |
| 9 | melon | 17 | M | third | NULL | NULL | NULL |
| 10 | nectarine | 19 | F | third | NULL | NULL | NULL |
| 11 | olive | 18 | M | third | NULL | NULL | NULL |
| 12 | orange | 20 | F | third | NULL | NULL | NULL |
+------+-----------+------+------+--------+------+--------+--------+
15 rows in set (0.00 sec)
