MYSQL数据库进阶查询 非常详细 对后期数据库操作用处很大

一、MySQL进价查询

1、排序

■ 使用ORDERBY语句来实现排序

■ 排序可针对一个或多个字段

■ ASC:升序，默认排序方式 【升序是从小到大】

■ DESC:降序 【降序是从大到小】

■ ORDER BY的语法结构

■ ORDER BY后面跟字段名

SELECT column1, column2,....FROM table_name ORDER BY column1,column2,...ASC|DESC;

例：建一张表

mysql> create table info (id int(4) auto_increment primary key,name varchar(10) not null,score int(4));
Query OK, 0 rows affected (0.02 sec)
插入数据后
mysql> select * from info;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |    82 |
|  2 | lisi     |    89 |
|  3 | wangwu   |    92|
|  4 | zhaoliu  |    65 |
|  5 | zhaosi   |    75 |
|  6 | zhouliu  |    84 |
+----+----------+-------+
6 rows in set (0.00 sec)

1.1、 单字段排序

不加排列顺序，默认是升序排列

mysql> select * from info order by score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  4 | zhaoliu  |    65 |
|  5 | zhaosi   |    75 |
|  1 | zhangsan |    82 |
|  6 | zhouliu  |    84 |
|  2 | lisi     |    89 |
|  3 | wangwu   |    92 |
+----+----------+-------+
6 rows in set (0.00 sec)

加desc 降序排列

mysql> select * from info order by score desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  3 | wangwu   |    92 |
|  2 | lisi     |    89 |
|  6 | zhouliu  |    84 |
|  1 | zhangsan |    82 |
|  5 | zhaosi   |    75 |
|  4 | zhaoliu  |    65 |
+----+----------+-------+
6 rows in set (0.01 sec)

1.2、 多字段排序

默认状态

mysql> select * from info;
+----+----------+-------+-------+
| id | name     | score | hobby |
+----+----------+-------+-------+
|  1 | zhangsan |    82 |     2 |
|  2 | lisi     |    89 |     3 |
|  3 | wangwu   |    92 |     1 |
|  4 | zhaoliu  |    65 |     1 |
|  5 | zhaosi   |    75 |     2 |
|  6 | zhouliu  |    84 |     1 |
+----+----------+-------+-------+
6 rows in set (0.00 sec)

mysql>  select * from info where 2=2 order by hobby desc,score desc;
+----+----------+-------+-------+
| id | name     | score | hobby |
+----+----------+-------+-------+
|  2 | lisi     |    89 |     3 |
|  1 | zhangsan |    82 |     2 |
|  5 | zhaosi   |    75 |     2 |
|  3 | wangwu   |    92 |     1 |
|  6 | zhouliu  |    84 |     1 |
|  4 | zhaoliu  |    65 |     1 |
+----+----------+-------+-------+
6 rows in set (0.00 sec)


只有第一个字段相同的情况下，第二字段排序才有意义

2 分组

■ 使用GROUP BY语句来实现分组

■ 通常结合聚合函数一起使用

■ 可以按一个或多个字段对结果进行分组

■ GROUP BY的语法结构

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;

2.1、GROUP BY分组

按hobby进行分组，统计每组的name个数
mysql>  select count(name),hobby from info group by hobby;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
|           3 |     1 |
|           2 |     2 |
|           1 |     3 |
+-------------+-------+
3 rows in set (0.00 sec)

2.2、GROUP BY结合ORDER BY

mysql> select count(name),hobby from info group by hobby order by count(name) desc;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
|           3 |     1 |
|           2 |     2 |
|           1 |     3 |
+-------------+-------+
3 rows in set (0.00 sec)

3、限制结果条目LIMIT

■ 只返回SELECT查询结果的第一行或前几行

■ 使用LIMIT语句限制条目

■ LIMIT语法结构

SELECT column1,column2,...FROM table_name LIMIT[offset,] number;
number：返回记录行的最大数目
[offset,]：位置偏移量，从0开始

查看前三行
mysql> select * from info limit 3;
+----+----------+-------+-------+
| id | name     | score | hobby |
+----+----------+-------+-------+
|  1 | zhangsan |    82 |     2 |
|  2 | lisi     |    89 |     3 |
|  3 | wangwu   |    92 |     1 |
+----+----------+-------+-------+
3 rows in set (0.00 sec)

查看4-6行
mysql> select * from info limit 3,3;
+----+---------+-------+-------+
| id | name    | score | hobby |
+----+---------+-------+-------+
|  4 | zhaoliu |    65 |     1 |
|  5 | zhaosi  |    75 |     2 |
|  6 | zhouliu |    84 |     1 |
+----+---------+-------+-------+
3 rows in set (0.00 sec)

4、设置别名

■ 使用AS语句设置别名，关键字AS可省略
■ 设置别名时，保证不能与库中其他表或字段名称冲突
■ 别名的语法结构

SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name ASalias_name;

■ AS的用法
对于字段设置

给name和score设置别名
mysql> select name as 姓名,score as 成绩 from info;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |     82 |
| lisi     |     89 |
| wangwu   |     92 |
| zhaoliu  |     65 |
| zhaosi   |     75 |
| zhouliu  |     84 |
+----------+--------+
6 rows in set (0.00 sec)

不加as语法也可以设置别名

mysql> select name  姓名,score  成绩 from info;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |     82 |
| lisi     |     89 |
| wangwu   |     92 |
| zhaoliu  |     65 |
| zhaosi   |     75 |
| zhouliu  |     84 |
+----------+--------+
6 rows in set (0.00 sec)

对于表设置，一般在多表查询时使用

给info表设置别名i，然后在name和score前面也要加i.使用，不加as也可以使用
mysql> select i.name as 姓名,i.score as 成绩 from info as i;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |     82 |
| lisi     |     89 |
| wangwu   |     92 |
| zhaoliu  |     65 |
| zhaosi   |     75 |
| zhouliu  |     84 |
+----------+--------+
6 rows in set (0.00 sec)

作为连接语句

创建good新表，将info表的 score字段>=85的数据放在新表good上
mysql> create table good as select * from info where score >=85;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

可以看到主键、自增约束都没有了，
mysql> desc good;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | 0       |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| score | int(4)      | YES  |     | NULL    |       |
| hobby | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

数据导过去了
mysql> select * from good;
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  2 | lisi   |    89 |     3 |
|  3 | wangwu |    92 |     1 |
+----+--------+-------+-------+
2 rows in set (0.00 sec)

5、通配符

■ 用于替换字符串的部分字符

■ 通常配合LIKE一起使用，并协同WHERE完成查询

■ 常用的通配符

● %表示零个、一个或多个

● _表示单个字符

查询l开头的，%表示零个、一个或多个
mysql>  select * from info where name like 'l%';
+----+------+-------+-------+
| id | name | score | hobby |
+----+------+-------+-------+
|  2 | lisi |    89 |     3 |
+----+------+-------+-------+
1 row in set (0.00 sec)

_下划线代表单个字符
mysql> select * from info where name like '_i_i';
+----+------+-------+-------+
| id | name | score | hobby |
+----+------+-------+-------+
|  2 | lisi |    89 |     3 |
+----+------+-------+-------+
1 row in set