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>
