mysql中的decode函数用法 mysql中desc的用法

目录

• 前言
• 一 、进阶查询介绍

• 1.1 按关键字排序
• 1.2 对结果进行分组
• 1.3 限制结果条目
• 1.4 设置别名
• 1.5 通配符的使用
• 1.6 子查询
• 1.7 NULL值

• 二、正则表达式
• 三、运算符

• 3.1 算数运算符
• 3.2 逻辑运算符
• 3.3 位运算符
• 3.4 连接查询

前言

在对 MySQL 数据库的增、删、改、查操作有一定了解之后，就可以学习一些 SQL 语句的高级使用方法，下面咱们一起来研究吧！

一 、进阶查询介绍

对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等…

1.1 按关键字排序

• 使用order by语句来实现排序
• 排序可针对一个或多个字段
• ASC：升序，默认排序方式
• DESC：降序
• order by的语法结构
  select 字段1，字段2 from 表名 order by 字段1 desc|asc，字段2 desc|asc；
• 按单字段排序

mysql> select * from info;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | alice   |   19 |
|  2 | ben     |   14 |
|  3 | chalice |   26 |
|  4 | david   |   17 |
|  5 | ellen   |   24 |
|  6 | fros    |   19 |
+----+---------+------+
6 rows in set (0.00 sec)

mysql> select * from info where age > 20 order by age;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  5 | ellen   |   24 |
|  3 | chalice |   26 |
+----+---------+------+
2 rows in set (0.00 sec)

mysql> select * from info order by age desc;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  3 | chalice |   26 |
|  5 | ellen   |   24 |
|  1 | alice   |   19 |
|  6 | fros    |   19 |
|  4 | david   |   17 |
|  2 | ben     |   14 |
+----+---------+------+
6 rows in set (0.00 sec)

• 多字段排序

mysql> select * from info order by age ,id desc;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | ben     |   14 |
|  4 | david   |   17 |
|  6 | fros    |   19 |
|  1 | alice   |   19 |
|  5 | ellen   |   24 |
|  3 | chalice |   26 |
+----+---------+------+
6 rows in set (0.00 sec)

1.2 对结果进行分组

• 使用group by语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组
• group by分组

mysql> select count(name),age from info group by age;
+-------------+------+
| count(name) | age  |
+-------------+------+
|           1 |   14 |
|           1 |   17 |
|           2 |   19 |
|           1 |   24 |
|           1 |   26 |
+-------------+------+
5 rows in set (0.00 sec)

• group by 结合 order by

mysql> select count(name),age from info group by age order by age desc;
+-------------+------+
| count(name) | age  |
+-------------+------+
|           1 |   26 |
|           1 |   24 |
|           2 |   19 |
|           1 |   17 |
|           1 |   14 |
+-------------+------+
5 rows in set (0.00 sec)

1.3 限制结果条目

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

语法：
select column1，column2，... from 库名 limit 位置偏移量

mysql> select * from info limit 3;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | alice   |   19 |
|  2 | ben     |   14 |
|  3 | chalice |   26 |
+----+---------+------+
3 rows in set (0.00 sec)

mysql> select * from info limit 2,3;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  3 | chalice |   26 |
|  4 | david   |   17 |
|  5 | ellen   |   24 |
+----+---------+------+
3 rows in set (0.00 sec)

#2，3表示从第三行开始数(0,1,2)，显示后三行

1.4 设置别名

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

列的别名：
select 列名 as 列名别名 from 库名；
表的别名：
select 列名 from 库名  as 库名别名；

mysql> select i.id as 学号,name 姓名,age as 年龄 from info as i;
+--------+---------+--------+
| 学号   | 姓名    | 年龄   |
+--------+---------+--------+
|      1 | alice   |     19 |
|      2 | ben     |     14 |
|      3 | chalice |     26 |
|      4 | david   |     17 |
|      5 | ellen   |     24 |
|      6 | fros    |     19 |
+--------+---------+--------+
6 rows in set (0.00 sec)

• as作为连接语句

mysql> create table test as select * from info;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | alice   |   19 |
|  2 | ben     |   14 |
|  3 | chalice |   26 |
|  4 | david   |   17 |
|  5 | ellen   |   24 |
|  6 | fros    |   19 |
+----+---------+------+
6 rows in set (0.00 sec)

1.5 通配符的使用

• 用于替换字符串中的部分字符
• 通常配合like一起使用，并协同where完成查询
• 常用通配符
• %：表示0个，1个或多个
• _：表示单个字符

mysql> select * from info where name like 'a%';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | alice |   19 |
+----+-------+------+
1 row in set (0.00 sec)

mysql> select * from info where name like 'be_';
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | ben  |   14 |
+----+------+------+
1 row in set (0.00 sec)

1.6 子查询

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

查询:
mysql> select id,age from info where age in (select age from info where age >=20);
+----+------+
| id | age  |
+----+------+
|  3 |   26 |
|  5 |   24 |
+----+------+
2 rows in set (0.00 sec)

查询结合降序使用:
mysql> select id,age from info where age in (select age from info where age >=20)order by age;
+----+------+
| id | age  |
+----+------+
|  5 |   24 |
|  3 |   26 |
+----+------+
2 rows in set (0.00 sec)

插入:
mysql> create table test as select * from info;
mysql> delete from test_;
mysql> insert into test select * from info where age in (select age from info where age >=20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  3 | chalice |   26 |
|  5 | ellen   |   24 |
+----+---------+------+
2 rows in set (0.00 sec)

修改:
mysql> update test set score=80 where age in (select age from info where age >=20);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from test;
+----+---------+------+-------+
| id | name    | age  | score |
+----+---------+------+-------+
|  3 | chalice |   26 |    80 |
|  5 | ellen   |   24 |    80 |
+----+---------+------+-------+
2 rows in set (0.00 sec)


删除:
mysql> create table test as select * from info;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> delete from test where age in (select age from (select * from info where age >=20)a);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from test;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | alice |   19 |
|  2 | ben   |   14 |
|  4 | david |   17 |
|  6 | fros  |   19 |
+----+-------+------+
4 rows in set (0.00 sec)

1.7 NULL值

• 表示缺失的值
• 与数字0或者空白（spaces）是不同的
• 使用IS NULL或IS NOT NULL进行判断
• NULL值和空值的区别
• 空值长度为0，不占空间；NULL值的长度为NULL，占用空间
• IS NULL无法判断空值
• 空值使用“=”或者“<>”来处理
• COUNT（）计算时，NULL会忽略，空值会加入计算

插入空值:
mysql> alter table test add column height varchar(8);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test values(7,'gary',14,'');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-------+------+--------+
| id | name  | age  | height |
+----+-------+------+--------+
|  1 | alice |   19 | NULL   |
|  2 | ben   |   14 | NULL   |
|  4 | david |   17 | NULL   |
|  6 | fros  |   19 | NULL   |
|  7 | gary  |   14 |        |
+----+-------+------+--------+
5 rows in set (0.00 sec)

null的用法:
mysql> select * from test where height is null;
+----+-------+------+--------+
| id | name  | age  | height |
+----+-------+------+--------+
|  1 | alice |   19 | NULL   |
|  2 | ben   |   14 | NULL   |
|  4 | david |   17 | NULL   |
|  6 | fros  |   19 | NULL   |
+----+-------+------+--------+
4 rows in set (0.01 sec)

mysql> select * from test where height is not null;
+----+------+------+--------+
| id | name | age  | height |
+----+------+------+--------+
|  7 | gary |   14 |        |
+----+------+------+--------+
1 row in set (0.00 sec)

二、正则表达式

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

字符	说明
^	匹配开始字符
$	匹配结束字符
.	匹配任意单个字符
*	匹配任意个前面的字符
+	匹配前面字符至少1次
p1|p2	匹配p1或p2
[…]	匹配字符集中括号内的任何字符
[^…]	匹配不在括号内的任何字符
{n}	匹配前面的字符串n次
{n,m}	匹配前面的字符串至少n次，至多m次

mysql> select * from info;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | alice   |   19 |
|  2 | ben     |   14 |
|  3 | chalice |   26 |
|  4 | david   |   17 |
|  5 | ellen   |   24 |
|  6 | fros    |   19 |
|  7 | alley   |   21 |
|  8 | allen   |   27 |
+----+---------+------+
8 rows in set (0.00 sec)

以a开头的姓名：(^)
mysql> select * from info where name regexp '^a';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | alice |   19 |
|  7 | alley |   21 |
|  8 | allen |   27 |
+----+-------+------+
3 rows in set (0.00 sec)

以e结尾的姓名：($)
mysql> select * from info where name regexp 'e$';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | alice   |   19 |
|  3 | chalice |   26 |
+----+---------+------+
2 rows in set (0.01 sec)

匹配单个字符（.）
mysql> select * from info where name regexp 'alic.';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | alice   |   19 |
|  3 | chalice |   26 |
+----+---------+------+
2 rows in set (0.00 sec)

匹配前面字符至少1次（+）
mysql> insert into info values(9,'lee',14),(10,'leee',18),(11,'leeee',24);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from info where name regexp 'leee+';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
| 10 | leee  |   18 |
| 11 | leeee |   24 |
+----+-------+------+
2 rows in set (0.00 sec)

匹配任意个前面的字符(*)
mysql> select * from info where name regexp 'leee*';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  9 | lee   |   14 |
| 10 | leee  |   18 |
| 11 | leeee |   24 |
+----+-------+------+
3 rows in set (0.00 sec)

匹配p1或p2(p1|p2)
mysql> select * from info where name regexp 'le|d';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  4 | david |   17 |
|  5 | ellen |   24 |
|  7 | alley |   21 |
|  8 | allen |   27 |
|  9 | lee   |   14 |
| 10 | leee  |   18 |
| 11 | leeee |   24 |
+----+-------+------+
7 rows in set (0.00 sec)

匹配字符集中括号内的任何字符([...])
mysql> select * from info where name regexp '[ab]';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | alice   |   19 |
|  2 | ben     |   14 |
|  3 | chalice |   26 |
|  4 | david   |   17 |
|  7 | alley   |   21 |
|  8 | allen   |   27 |
+----+---------+------+
6 rows in set (0.00 sec)

匹配前面的字符串n次{n}
mysql> select * from info where name regexp 'e{2}';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  9 | lee   |   14 |
| 10 | leee  |   18 |
| 11 | leeee |   24 |
+----+-------+------+
3 rows in set (0.00 sec)

匹配前面的字符串至少n次，至多m次({n,m})
mysql> select * from info where name regexp 'e{3,4}';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
| 10 | leee  |   18 |
| 11 | leeee |   24 |
+----+-------+------+
2 rows in set (0.00 sec)

三、运算符

3.1 算数运算符

• MySQL支持的算术运算符

字符	说明
+	加法
-	减法
*	乘法
/	除法
%	取余数

mysql> select 3+2,7-3,3*5,6/3,5%3;
+-----+-----+-----+--------+------+
| 3+2 | 7-3 | 3*5 | 6/3    | 5%3  |
+-----+-----+-----+--------+------+
|   5 |   4 |  15 | 2.0000 |    2 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

• 比较运算符
• 字符串的比较默认不区分大小写，可使用binary来区分
• 常用比较运算符

运算符	说明
=	等于
>	大于
<	小于
>=	大于或等于
<=	小于或等于
!=或<>	不等于
IN	在集合中
LIKE	通配符匹配
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
BETWEEN AND	两者之间
GREATEST	两个或多个参数时返回最大值
LEAST	两个或多个参数时返回最小值

mysql> select 1=2,2='2','ab'='ab',(1+4)=(2+3),'h'=NULL;
+-----+-------+-----------+-------------+----------+
| 1=2 | 2='2' | 'ab'='ab' | (1+4)=(2+3) | 'h'=NULL |
+-----+-------+-----------+-------------+----------+
|   0 |     1 |         1 |           1 |     NULL |
+-----+-------+-----------+-------------+----------+
1 row in set (0.00 sec)

mysql> select 4!=5,null is null,null is not null,3 between 5 and 8;
+------+--------------+------------------+-------------------+
| 4!=5 | null is null | null is not null | 3 between 5 and 8 |
+------+--------------+------------------+-------------------+
|    1 |            1 |                0 |                 0 |
+------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> select greatest(1,6,3),least(5,3,36);
+-----------------+---------------+
| greatest(1,6,3) | least(5,3,36) |
+-----------------+---------------+
|               6 |             3 |
+-----------------+---------------+
1 row in set (0.00 sec)

注：

• 如果两者都是整数，则按整数值进行比较
• 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较
• 如果两者都是字符串，则按照字符串进行比较
• 如果两者中至少有一个值是NULL，则比较的结果是NULL

3.2 逻辑运算符

• 称为布尔运算符
• 用来判断表达式的真假
• 常用的逻辑运算符

运算符	说明
NOT或！	逻辑非
AND或&&	逻辑与
OR或||	逻辑或
XOR	逻辑异或

mysql> select not 3,!5,not 0,!(6-6);
+-------+----+-------+--------+
| not 3 | !5 | not 0 | !(6-6) |
+-------+----+-------+--------+
|     0 |  0 |     1 |      1 |
+-------+----+-------+--------+
1 row in set (0.00 sec)

mysql> select 2 and 5,3 && 0, 1 || NULL ,0 and NULL;
+---------+--------+-----------+------------+
| 2 and 5 | 3 && 0 | 1 || NULL | 0 and NULL |
+---------+--------+-----------+------------+
|       1 |      0 |         1 |          0 |
+---------+--------+-----------+------------+
1 row in set (0.00 sec

3.3 位运算符

• 对二进制数进行计算的运算符
• 常用的位运算符

运算符	说明
&	按位与
|	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

mysql> select 5&6,5|6,5&~6,5^6,3<<2,3>>1;
+-----+-----+------+-----+------+------+
| 5&6 | 5|6 | 5&~6 | 5^6 | 3<<2 | 3>>1 |
+-----+-----+------+-----+------+------+
|   4 |   7 |    1 |   3 |   12 |    1 |
+-----+-----+------+-----+------+------+
1 row in set (0.00 sec)

3.4 连接查询

MySQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接，首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上，使用较多的连接查询包括：内连接、左连接和右连接

• 原始表

mysql> select * from name;
+------+---------+
| id   | name    |
+------+---------+
|    1 | alice   |
|    2 | ben     |
|    3 | chalice |
|    5 | ellen   |
+------+---------+
4 rows in set (0.00 sec)

mysql> select * from hobby;                                    
+------+------------+
| id   | hobby      |
+------+------------+
|    1 | volleyball |
|    2 | basketball |
|    3 | football   |
|    4 | ping-pang  |
+------+------------+
4 rows in set (0.00 sec)

• 内连接

mysql> select n.id,n.name,h.hobby from name n inner join hobby h on n.id=h.id;
+------+---------+------------+
| id   | name    | hobby      |
+------+---------+------------+
|    1 | alice   | volleyball |
|    2 | ben     | basketball |
|    3 | chalice | football   |
+------+---------+------------+
3 rows in set (0.00 sec)

• 左连接

mysql> select n.id,n.name,h.hobby from name n left join hobby h on n.id=h.id;
+------+---------+------------+
| id   | name    | hobby      |
+------+---------+------------+
|    1 | alice   | volleyball |
|    2 | ben     | basketball |
|    3 | chalice | football   |
|    5 | ellen   | NULL       |
+------+---------+------------+
4 rows in set (0.00 sec)

• 右连接

mysql> select n.id,n.name,h.hobby from name n right join hobby h on n.id=h.id;
+------+---------+------------+
| id   | name    | hobby      |
+------+---------+------------+
|    1 | alice   | volleyball |
|    2 | ben     | basketball |
|    3 | chalice | football   |
| NULL | NULL    | ping-pang  |
+------+---------+------------+
4 rows in set (0.00 sec)