mysql 高级查询总结_MySQL高级查询总结

(一)模糊查询

- 1、-like:

mysql> select name as "姓名" from score where name like '张%';

+--------+

| 姓名|

+--------+

| 张三|

| 张玉洁|

| 张三|

| 张三|

| 张三|

| 张玉洁|

| 张玉洁|

| 张玉洁|

+--------+

8 rows in set

2、is null

mysql> select name as "姓名" from score where name is null;

Empty set

3、between

mysql>  select name , score from score where score between 80 and 100;

+--------+-------+

| name   | score |

+--------+-------+

| 张玉洁|    98 |

| 张三|    86 |

| 张三|    97 |

| 张玉洁|    98 |

| 李一|    80 |

+--------+-------+

5 rows in set

4、in

mysql> select name,score from score where score in (97,98);

+--------+-------+

| name   | score |

+--------+-------+

| 张玉洁|    98 |

| 张三|    97 |

| 张玉洁|    98 |

+--------+-------+

3 rows in set

(二)聚合函数

1、sum()

mysql> select sum(score) as "李一总成绩" from score where name="李一";

+------------+

| 李一总成绩|

+------------+

| 223        |

+------------+

1 row in set

2、avg()

mysql> select avg(score) as "李一平均成绩" from score where name="李一";

+--------------+

| 李一平均成绩|

+--------------+

| 74.3333      |

+--------------+

1 row in set

3.max() min()

mysql> select max(score) from score;

+------------+

| max(score) |

+------------+

|         98 |

+------------+

1 row in set

4.count()

mysql> select count(score) as "大于70分的成绩个数" from score where score > 70;

+--------------------+

| 大于70分的成绩个数|

+--------------------+

|                  6 |

+--------------------+

1 row in set

(三)分组查询：

mysql> select name,avg(score),courseName from score group by name,courseName;

+--------+------------+------------+

| name   | avg(score) | courseName |

+--------+------------+------------+

| 张三| 97.0000    |数学|

| 张三| 67.0000    |英语|

| 张三| 60.0000    |语文|

| 张玉洁| 43.0000    |数学|

| 张玉洁| 98.0000    |英语|

| 张玉洁| 98.0000    |语文|

| 李一| 80.0000    |数学|

| 李一| 66.0000    |英语|

| 李一| 77.0000    |语文|

+--------+------------+------------+

mysql> select name,avg(score),courseName from score group by name,courseName having count(courseName)>1;

+--------+------------+------------+

| name   | avg(score) | courseName |

+--------+------------+------------+

| 张三| 60.0000    |语文|

| 张玉洁| 43.0000    |数学|

+--------+------------+------------+

2 rows in set

分组查询总结:

1.WHERE子句从数据源中去掉不符合其搜索条件的数据

2.GROUP BY子句搜集数据行到各个组中

3.统计函数为各个组计算统计值

4.HAVING子句去掉不符合其组搜索条件的各组数据行

5使用GROUP BY时，select后面出现的内容要么为聚合函数，要么为group by后面出现的内容

(四)多表链接查询：

1.内连接：

(1)用inner join 但这种方法不常用 常用第二种；

mysql> select username,starname from users as u inner join star as s on u.starid=s.starid;

+----------+----------+

| username | starname |

+----------+----------+

| 张一|白羊座|

| 李二|金牛座|

| 王三|双子座|

| 张四|巨蟹座|

| 李五|狮子座|

| 张八|处女座|

| 王九|天秤座|

| 张言|天蝎座|

| 李志|射手座|

| 王月|水瓶座|

| 张欣|双鱼座|

+----------+----------+

11 rows in set

(2)from where

select username,starname from users as u,star s where u.starid=s.starid;

+----------+----------+

| username | starname |

+----------+----------+

| 张一|白羊座|

| 李二|金牛座|

| 王三|双子座|

| 张四|巨蟹座|

| 李五|狮子座|

| 张八|处女座|

| 王九|天秤座|

| 张言|天蝎座|

| 李志|射手座|

| 王月|水瓶座|

| 张欣|双鱼座|

+----------+----------+

11 rows in set

2.外连接：

(1)左连接，返回左表的所有数据以及右表对应的数据。  Left join

mysql> select starname,username from users as u left join star s on u.starid=s.starid;

+----------+----------+

| starname | username |

+----------+----------+

| 白羊座|张一|

| 金牛座|李二|

| 双子座|王三|

| 巨蟹座|张四|

| 狮子座|李五|

| 处女座|张八|

| 天秤座|王九|

| 天蝎座|张言|

| 射手座|李志|

| 水瓶座|王月|

| 双鱼座|张欣|

+----------+----------+

11 rows in set

(2)右连接，返回右表的所有数据以及左表相对应的数据。Right join

(五)子查询

在某些特定的业务需求下，当进行查询的时候，需要的条件是另外一个 select语句的结果，这个时候，就要用到子查询。

用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists……

1. 比较运算符  >   

mysql> select username,height from users where height >(select height from users where username="张四");

+----------+--------+

| username | height |

+----------+--------+

| 李二|    155 |

| 王三|    160 |

| 李五|    160 |

| 张八|    168 |

| 王九|    170 |

| 张言|    176 |

| 李志|    180 |

| 王月|    176 |

| 张欣|    180 |

+----------+--------+

9 rows in set

2. 子查询 in

mysql> select starid,username,height from users where starid in(select starid from users where height>170);

+--------+----------+--------+

| starid | username | height |

+--------+----------+--------+

|      8 | 张言|    176 |

|      9 | 李志|    180 |

|     11 | 王月|    176 |

|     12 | 张欣|    180 |

+--------+----------+--------+

4 rows in set

3. Exists

(1)mysql> select starname from star where exists(select * from star where starid=13);

Empty set

(2) mysql> select starname from star where exists(select * from star where starid<13);

+----------+

| starname |

+----------+

| 白羊座|

| 金牛座|

| 双子座|

| 巨蟹座|

| 狮子座|

| 处女座|

| 天秤座|

| 天蝎座|

| 射手座|

| 摩羯座|

| 水瓶座|

| 双鱼座|

+----------+

12 rows in set

Exists()括号内为真 则前面的语句执行，为假则前面的语句不执行；EXISTS也可以作为WHERE语句的子查询，但一般都能用IN子查询替换。

4. not exists

与exists刚好是反的。

5. mysql> select sum(avg_score) from(select avg(score) as avg_score from score group by name) as t1;

+----------------+

| sum(avg_score) |

+----------------+

| 215.8333       |

+----------------+

1 row in set

mysql>