MySQL高级SQL语句（一）

一：MySQL进阶查询

1.1：按关键字排序

• 使用ORDERBY语句来实现排序
• 排序可针对一个或多个字段
• ASC：升序（默认排序方式）
• DESC：降序
• OREDER BY的语法结构
  1. 按单字段排序

语法:select column1,coumn2,...from table_name(表名) order by column1,coulmn2(字段）... asc|desc;
例如：
mysql> select * from info order by score desc;
+----+---------+-------+-------+
| id | name    | score | hobby |
+----+---------+-------+-------+
|  3 | wangwu  | 90.00 |     1 |
|  1 | zhangsn | 88.00 |     1 |
|  5 | zhaoliu | 81.00 |     2 |
|  2 | lisi    | 77.00 |     2 |
|  4 | tianqi  | 66.00 |     3 |
+----+---------+-------+-------+

2.按多字段排序

mysql> select id,hobby from info order by hobby desc,id desc;
+----+-------+
| id | hobby |
+----+-------+
|  4 |     3 |
|  5 |     2 |
|  2 |     2 |
|  3 |     1 |
|  1 |     1 |
+----+-------+
5 rows in set (0.01 sec)

1.2：对结果进行分组

• 使用GROUPBY语句来实现分组
• 通常集合聚合函数一起使用
• 可以按一个过多个字段 对结果进行分组
• GROUPBY的语法结构

例如：
mysql> select count(name),hobby from info group by hobby;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
|           2 |     1 |
|           2 |     2 |
|           1 |     3 |
+-------------+-------+
3 rows in set (0.00 sec)  ###(表示每个hobby里面包含多少count(name)

1.3:限制结果条目

• 只返回select查询结果的第一行或者前几行
• 使用LIMIT语句限制条目
• LIMIT语法结构

语法：select column1,column2,...from table_name(表名) limit[offset（位置偏移量，从0开始）,] number（返回记录的最大数目）
例如：
mysql> select * from info limit 3;
+----+---------+-------+-------+
| id | name    | score | hobby |
+----+---------+-------+-------+
|  1 | zhangsn | 88.00 |     1 |
|  2 | lisi    | 77.00 |     2 |
|  3 | wangwu  | 90.00 |     1 |
+----+---------+-------+-------+
3 rows in set (0.00 sec)  ###查询info表的前三行）

mysql> select * from info limit 2,3;
+----+---------+-------+-------+
| id | name    | score | hobby |
+----+---------+-------+-------+
|  3 | wangwu  | 90.00 |     1 |
|  4 | tianqi  | 66.00 |     3 |
|  5 | zhaoliu | 81.00 |     2 |
+----+---------+-------+-------+
3 rows in set (0.00 sec)   ###（查询info表偏移量从2开始（也就是第三行）往后三行的数据）

1.4：设置别名

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

语法：
select column_name(列的别名) as alias_name（别名） from table_name（表的别名）
例如：
mysql> select name as 姓名,score as 成绩 from info;
+---------+--------+
| 姓名    | 成绩   |
+---------+--------+
| zhangsn |  88.00 |
| lisi    |  77.00 |
| wangwu  |  90.00 |
| tianqi  |  66.00 |
| zhaoliu |  81.00 |
+---------+--------+
5 rows in set (0.00 sec)
例如：设置表的别名
mysql> select i.name as 姓名,i.score as 成绩 from info as i;
+---------+--------+
| 姓名    | 成绩   |
+---------+--------+
| zhangsn |  88.00 |
| lisi    |  77.00 |
| wangwu  |  90.00 |
| tianqi  |  66.00 |
| zhaoliu |  81.00 |
+---------+--------+
5 rows in set (0.00 sec)

1.4：AS的其他用法

• AS可以作为连接语句使用

例如：
mysql> create table tmp as select * from info;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
| tmp              |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from tmp;
+----+---------+-------+-------+
| id | name    | score | hobby |
+----+---------+-------+-------+
|  1 | zhangsn | 88.00 |     1 |
|  2 | lisi    | 77.00 |     2 |
|  3 | wangwu  | 90.00 |     1 |
|  4 | tianqi  | 66.00 |     3 |
|  5 | zhaoliu | 81.00 |     2 |
+----+---------+-------+-------+
5 rows in set (0.00 sec)

注意：
这里用AS创建的表只是导入了原表的数据，并没有把数据的约束条件起义复制

1.5：通配符的用法

• 用于替换字符串中的部分字符
• 通常配合LIKE一起使用
• 常用通配符：%表示零个、一个或多个；_表示单个字符

1. “_”的用法

mysql> select name,score from info where name like 'l___';  ###3个_表示后面3个随机字符
+------+-------+
| name | score |
+------+-------+
| lisi | 77.00 |
+------+-------+
1 row in set (0.00 sec)

2. “%”的用法

mysql> select name,score from info where name like 'z%';  ###表示过滤出以z开头后面为任意字符的数据
+---------+-------+
| name    | score |
+---------+-------+
| zhangsn | 88.00 |
| zhaoliu | 81.00 |
+---------+-------+
2 rows in set (0.01 sec)

3. 二者结合的使用方法

mysql> select name,score from info where name like '_h%';
+---------+-------+
| name    | score |
+---------+-------+
| zhangsn | 88.00 |
| zhaoliu | 81.00 |
+---------+-------+
2 rows in set (0.00 sec)

1.6：子查询的用法

• 也称作内查询或者嵌套查询
• 先于著查询被执行，其结果将作为外层主查询的条件
• 在增删改查中都可以使用子查询
• 支持多层嵌套
• IN语句是用来判断某个值是否在给定的结果集中

mysql> select * from info where id in (select id from num);  ###只有当后面的子查询条件成立时，前面的主查询语句才可以被执行，就是如果num表中的id字段如果没有info表中id的类型，查询语句不会被执行）
+----+---------+-------+-------+
| id | name    | score | hobby |
+----+---------+-------+-------+
|  1 | zhangsn | 88.00 |     1 |
|  3 | wangwu  | 90.00 |     1 |
|  5 | zhaoliu | 81.00 |     2 |
+----+---------+-------+-------+
3 rows in set (0.01 sec)

结合其他语句进行子查询

mysql> select count(*) from info where exists (select * from info where name='zhangsan');  ### 只有当info表中有zhangsan时才会对前面的数据进行统计
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

1.7：MySQL算数运算符号

mysql> select 1+5;
+-----+
| 1+5 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)

mysql> select 1-5;
+-----+
| 1-5 |
+-----+
|  -4 |
+-----+
1 row in set (0.00 sec)

mysql> select 1*5;
+-----+
| 1*5 |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

mysql> select 1/5;
+--------+
| 1/5    |
+--------+
| 0.2000 |
+--------+
1 row in set (0.00 sec)
mysql> select 1=2;  ###条件判断错误返回值为0
+-----+
| 1=2 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

mysql> select 1<2; ###条件判断正确返回值为1
+-----+
| 1<2 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

1.8：null值

• 表示缺失的值

• 与数字0或者空白(spaces）是不同的

• 使用IS NULL或IS NOT NULL进行判断

• NULL值和空值的区别：
  空值长度为0，不占空间;NULL值的长度为NULL，占用空间
  .IS NULL无法判断空值
  空值使用“="或者“<>"来处理
  .cOUNT()计算时，NULL会忽略，空值会加入计算

• NULL里面没有任何值 ，占用一部分空间
  示例：

创建一个表
mysql> create table num (id int(4) not null primary key auto_increment,name char(10));
#查看表结构
mysql> desc num;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
#插入内容
mysql> insert into num (id,name) values (2,'wang');
Query OK, 1 row affected (0.00 sec)
#查询数据记录
mysql> select * from num;
+----+-------+
| id | name  |
+----+-------+
|  2 | wang |
+----+-------+
1 row in set (0.00 sec)

#再次插入
mysql> insert into num (id) values (3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from num;
+----+-------+
| id | name  |
+----+-------+
|  2 | wang |
|  3 | NULL  |
+----+-------+
2 rows in set (0.00 sec)

#在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL

二：正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式。

• 根据指定的匹配模式匹配记录中符合要求的特殊字符
• 使用REGEXP关键字指定匹配模式
• 常用匹配模式

2.1：正则表达式匹配表

匹配模式	描述	实例
^	匹配文本的开始字符	‘^bd’ 匹配以 bd 开头的字符串
$	匹配文本的结束字符	‘qn$’ 匹配以 qn 结尾的字符串
.	匹配任何单个字符	‘s.t’ 匹配任何s 和t 之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	‘fo*t’ 匹配 t 前面有任意个 o
+	匹配前面的字符 1 次或多次	‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串	匹配包含指定的字符串	‘clo’ 匹配含有 clo 的字符串
1I2	匹配 1 或 2	‘bg
[…]	匹配字符集合中的任意一个字符	‘[abc]’ 匹配 a 或者 b 或者 c
[^…]	匹配不在括号中的任何字符	‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n}	匹配前面的字符串 n 次	‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}	匹配前面的字符串至少 n 次，至多m 次	‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

了解正则表达式的匹配规则之后，就可以将这些规则技巧应用于 SQL 语句中，从而可以更准确、更快速的查出所需的记录。下面通过示例的方式详细介绍 MySQL 正则表达式的使用方法。
示例：
查询以wa开头的

mysql> select * from info where name regexp '^wa';
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  3 | wangwu | 90.00 |     1 |
+----+--------+-------+-------+
1 row in set (0.00 sec)

查询以qi结尾

mysql> select * from info where name regexp 'qi$';
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  4 | tianqi | 66.00 |     3 |
+----+--------+-------+-------+
1 row in set (0.00 sec)