MySQL高阶sql语句

按关键字段排序

语法结构：select 字段1，字段2…字段n from 表名 order by 字段（想要按什么字段排序就写哪个字段）ASC | DESC
ASC表示升序（默认升序），DESC表示降序
注意点：ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序，ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定

测试数据如下：

create table info(id int(4),name varchar(20),subject varchar(20),score double);
insert into info values(1,'张三','语文',90);
insert into info values(2,'张三','数学',85);
insert into info values(3,'张三','英语',50);
insert into info values(4,'李四','语文',87);
insert into info values(5,'李四','数学',95);
insert into info values(6,'李四','英语',40);
insert into info values(7,'王五','语文',70);
insert into info values(8,'王五','数学',80);
insert into info values(9,'王五','英语',59);

mysql> select * from info order by score desc; //按成绩降序排序
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    5 | 李四   | 数学    |    95 |
|    1 | 张三   | 语文    |    90 |
|    4 | 李四   | 语文    |    87 |
|    2 | 张三   | 数学    |    85 |
|    8 | 王五   | 数学    |    80 |
|    7 | 王五   | 语文    |    70 |
|    9 | 王五   | 英语    |    59 |
|    3 | 张三   | 英语    |    50 |
|    6 | 李四   | 英语    |    40 |
+------+--------+---------+-------+
9 rows in set (0.00 sec)
mysql> select * from info order by name desc,score; //按姓名降序排序，名字相同的按成绩升序排序
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    9 | 王五   | 英语    |    59 |
|    7 | 王五   | 语文    |    70 |
|    8 | 王五   | 数学    |    80 |
|    6 | 李四   | 英语    |    40 |
|    4 | 李四   | 语文    |    87 |
|    5 | 李四   | 数学    |    95 |
|    3 | 张三   | 英语    |    50 |
|    2 | 张三   | 数学    |    85 |
|    1 | 张三   | 语文    |    90 |
+------+--------+---------+-------+
9 rows in set (0.00 sec)
mysql> 

区间判断查询

语法结构：select * from 表名 where 字段=值 (and|or) 字段=值
and：表示并且的意思；or：表示或者的意思

mysql> select * from info where score>50 and score<90;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    2 | 张三   | 数学    |    85 |
|    4 | 李四   | 语文    |    87 |
|    7 | 王五   | 语文    |    70 |
|    8 | 王五   | 数学    |    80 |
|    9 | 王五   | 英语    |    59 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from info where score>90 or score<60;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    3 | 张三   | 英语    |    50 |
|    5 | 李四   | 数学    |    95 |
|    6 | 李四   | 英语    |    40 |
|    9 | 王五   | 英语    |    59 |
+------+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> 

查询不重复记录

语法结构：select distinct 字段名 from 表名;
distinct关键字：表示查询后面跟着的字段名在表中的不重复的记录

mysql> select distinct name from info;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
| 王五   |
+--------+
3 rows in set (0.00 sec)
mysql> select distinct subject from info;
+---------+
| subject |
+---------+
| 语文    |
| 数学    |
| 英语    |
+---------+
3 rows in set (0.00 sec)
mysql>

分组查询

使用 GROUP BY 语句来实现分组，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、 求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。
语法结构：select count(*),字段名1，字段名2… from 表名 group by

mysql> select * from info;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 张三   | 语文    |    90 |
|    2 | 张三   | 数学    |    85 |
|    3 | 张三   | 英语    |    50 |
|    4 | 李四   | 语文    |    87 |
|    5 | 李四   | 数学    |    95 |
|    6 | 李四   | 英语    |    40 |
|    7 | 王五   | 语文    |    70 |
|    8 | 王五   | 数学    |    80 |
|    9 | 王五   | 英语    |    59 |
+------+--------+---------+-------+
9 rows in set (0.00 sec)
mysql> select count(*),name from info group by name;
+----------+--------+
| count(*) | name   |
+----------+--------+
|        3 | 张三   | //按name分组，统计行数
|        3 | 李四   |
|        3 | 王五   |
+----------+--------+
3 rows in set (0.00 sec)
mysql> 

子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。

mysql> select * from info where score in (select score from info where score>70);
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 张三   | 语文    |    90 |
|    2 | 张三   | 数学    |    85 |
|    4 | 李四   | 语文    |    87 |
|    5 | 李四   | 数学    |    95 |
|    8 | 王五   | 数学    |    80 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from info where score not in (select score from info where score>70);
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    3 | 张三   | 英语    |    50 |
|    6 | 李四   | 英语    |    40 |
|    7 | 王五   | 语文    |    70 |
|    9 | 王五   | 英语    |    59 |
+------+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> 

设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者 多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性
使用 AS 来设置别名
语法结构：select 别名.字段名1…别名.字段名n from 表名 AS 别名;

mysql> select i.id,i.name,i.subject,i.score from info as i where id<3;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 张三   | 语文    |    90 |
|    2 | 张三   | 数学    |    85 |
+------+--------+---------+-------+
2 rows in set (0.00 sec)
mysql> 
mysql> select id 学号,name 姓名,subject 科目,score 成绩 from info; //设置别名时 as 可以省略
+--------+--------+--------+--------+
| 学号   | 姓名   | 科目   | 成绩   |
+--------+--------+--------+--------+
|      1 | 张三   | 语文   |     90 |
|      2 | 张三   | 数学   |     85 |
|      3 | 张三   | 英语   |     50 |
|      4 | 李四   | 语文   |     87 |
|      5 | 李四   | 数学   |     95 |
|      6 | 李四   | 英语   |     40 |
|      7 | 王五   | 语文   |     70 |
|      8 | 王五   | 数学   |     80 |
|      9 | 王五   | 英语   |     59 |
+--------+--------+--------+--------+
9 rows in set (0.01 sec)

mysql> 

模糊查询

使用 LIKE 关键字，通常与 WHERE 子句一起使用来完成查询任务。
%：百分号表示零个、一个或多个字符 *
_：下划线表示单个字符

mysql> select * from info where name like '王%';
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    7 | 王五   | 语文    |    70 |
|    8 | 王五   | 数学    |    80 |
|    9 | 王五   | 英语    |    59 |
+------+--------+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from info where name like '王_';
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    7 | 王五   | 语文    |    70 |
|    8 | 王五   | 数学    |    80 |
|    9 | 王五   | 英语    |    59 |
+------+--------+---------+-------+
3 rows in set (0.01 sec)
mysql>