MySQL高级语句与函数

最新推荐文章于 2021-07-09 16:43:09 发布

liuqingyunCSDN

最新推荐文章于 2021-07-09 16:43:09 发布

阅读量201

点赞数

分类专栏： mysql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/liuqingyunCSDN/article/details/111793567

版权

mysql 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

一、按关键字排序

使用ORDER BY 语句来实现排序
排序可针对一个或多个字段
ASC：升序，默认排序方式
DESC：降序
ORDER BY的语法结构
- SELECT column1，column2,…FROM table_name

1.1 按单字段降序排序

mysql> select id,name,score from student order by score desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  3 | xiaobai  |   100 |
|  2 | xiaohong |    60 |
+----+----------+-------+
3 rows in set (0.00 sec)

1.2 按单字段排序升序

mysql> select id,name,score from student order by score asc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  2 | xiaohong |    60 |
|  1 | xiaoming |   100 |
|  3 | xiaobai  |   100 |
+----+----------+-------+
3 rows in set (0.00 sec)

1.3 按多字段排序

mysql> select id,score from student order by score desc,id asc;
+----+-------+
| id | score |
+----+-------+
|  1 |   100 |
|  3 |   100 |
|  2 |    60 |
+----+-------+
3 rows in set (0.00 sec)
## 先比较分数，分数若相同，比较辅助字段id，升序排序

二、对结果进行分组

使用GROUP BY 虞姬来实现分组
通常结合聚合函数一起使用
可以按一个或多个字段对结果进行分组
GROUP BY 的语法结构
- select column_name,aggregate_function(column_name) from table_name where column_name operator value GROUP BY column_name;

mysql> select count(name),score from student where score>=80 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 |    80 |
|           2 |   100 |
+-------------+-------+
2 rows in set (0.00 sec)
## 对分数进行分组，显示每个分数段的人有几个

mysql> select count(name),score from student where score>60 group by score order by score desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           2 |   100 |
|           1 |    80 |
|           1 |    70 |
+-------------+-------+
3 rows in set (0.00 sec)
## 对分数大于60的进行降序排序，并显示每个分段有多少人

三、限制结果条目

只返回select查询结果的第一行或前几行
使用limit语句限制条目
-select column1,column2,…from table_name limit [offset]

## 显示三行索引
mysql> select * from student limit 3；
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
+----+----------+-------+
3 rows in set (0.00 sec)

## 显示从4开始的，显示两行索引
mysql> select * from student limit 4,2;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  5 | zhangsan |    60 |
|  6 | xiaoliu  |    70 |
+----+----------+-------+
2 rows in set (0.00 sec)

## 结合排序，显示前三名
mysql> select * from student order by score desc limit 3;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  3 | xiaobai  |   100 |
|  7 | wangwu   |    80 |
+----+----------+-------+

四、设置别名

使用AS语句设置别名，关键字AS可省略
设置别名时，保证不能与库中其他表或字段名称冲突
别名的语法结构
- select column_name AS alias_name from table_name;
- select column_name(s) from table_name AS alias_name;

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) as totle from student;
+-------+
| totle |
+-------+
|     7 |
+-------+
1 row in set (0.00 sec)

## 对表设置别名，student表的别名为a，查询a表的name和score字段
mysql> select a.name,a.score from student as a;
+----------+-------+
| name     | score |
+----------+-------+
| xiaoming |   100 |
| xiaohong |    60 |
| xiaobai  |   100 |
| lisi     |    40 |
| zhangsan |    60 |
| xiaoliu  |    70 |
| wangwu   |    80 |
+----------+-------+
7 rows in set (0.00 sec)

五、通配符

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

## 查询student表中name字段是以x开头的
mysql> select * from student where name like 'x%';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  6 | xiaoliu  |    70 |
+----+----------+-------+
4 rows in set (0.00 sec)

## 单个字符匹配
mysql> select * from student where name like 'lis_';
+----+------+-------+
| id | name | score |
+----+------+-------+
|  4 | lisi |    40 |
+----+------+-------+
1 row in set (0.00 sec)

## 多个字符匹配，几个字符几个_
mysql> select * from student where name like 'z_______';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  5 | zhangsan |    60 |
+----+----------+-------+
1 row in set (0.00 sec)

## 第一个字符任意匹配，中间字符为iao，结尾任意字符结尾
mysql> select * from student where name like '_iao%';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  6 | xiaoliu  |    70 |
+----+----------+-------+
4 rows in set (0.00 sec)

六、子查询

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

## 查看id为6和7的是否在给定的集合中
mysql> select id,name,score from student where id in(6,7);
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  6 | xiaoliu |    70 |
|  7 | wangwu  |    80 |
+----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from student where id in(select id from student where score>60);
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  3 | xiaobai  |   100 |
|  6 | xiaoliu  |    70 |
|  7 | wangwu   |    80 |
+----+----------+-------+
4 rows in set (0.00 sec)

mysql> select * from student where id=(select id from student where name='lisi');
+----+------+-------+
| id | name | score |
+----+------+-------+
|  4 | lisi |    40 |
+----+------+-------+
1 row in set (0.00 sec)

## 查询除了lisi以外的信息  ！=等同<>
mysql> select * from student where id!=(select id from student where name='lisi');
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  5 | zhangsan |    60 |
|  6 | xiaoliu  |    70 |
|  7 | wangwu   |    80 |
+----+----------+-------+
6 rows in set (0.00 sec)

## 多层嵌套
mysql> select * from student where id in (select student.id from (select id from student where score>80)student);
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  3 | xiaobai  |   100 |
+----+----------+-------+
2 rows in set (0.00 sec)

七、NULL值

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

mysql> select * from student;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  4 | lisi     |    40 |
|  5 | zhangsan |    60 |
|  6 | xiaoliu  |    70 |
|  7 | wangwu   |    80 |
|  8 | lili     |  NULL |
+----+----------+-------+
8 rows in set (0.00 sec)
## count（）计算时，空值NULL会忽略不计
mysql> select count(score) from student;
+--------------+
| count(score) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

## 查询student表中，score字段空值
mysql> select * from student where score is null;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  8 | lili |  NULL |
+----+------+-------+
1 row in set (0.00 sec)

## 查询student表中，score字段不是空值
mysql> select * from student where score is not null;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  4 | lisi     |    40 |
|  5 | zhangsan |    60 |
|  6 | xiaoliu  |    70 |
|  7 | wangwu   |    80 |
+----+----------+-------+
7 rows in set (0.00 sec)

正则表达式

根据指定的匹配模式匹配记录中符合要求的特殊字符
使用REGEXP关键字指定匹配模式
常用的匹配模式
- ^匹配开始字符
- p1|p2匹配p1或p2
- $匹配结束字符
- […] 匹配字符集中的任意一个字符
- .匹配任意单个字符
- [^…]匹配不在中括号内的任何字符
- *匹配任意个前面的字符
- {n}匹配前面的符
- +匹配前面字符至少1次
- {n,m}匹配前面的字符

## 查询以l开头的记录
mysql> select * from student where name regexp '^l';
+----+------+-------+
| id | name | score |
+----+------+-------+
|  4 | lisi |    40 |
|  8 | lili |  NULL |
+----+------+-------+
2 rows in set (0.00 sec)

## 查询以u结尾的记录
mysql> select * from student where name regexp 'u$';
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  6 | xiaoliu |    70 |
|  7 | wangwu  |    80 |
+----+---------+-------+
2 rows in set (0.00 sec)

## 查询包含指定字符串‘xiao’的记录
mysql> select * from student where name regexp 'xiao';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  6 | xiaoliu  |    70 |
+----+----------+-------+
4 rows in set (0.01 sec)

## 查询name字符为xiaom缺少的字符用.表示
mysql> select * from student where name regexp 'xiaom...';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
+----+----------+-------+
1 row in set (0.00 sec)

## 查询name字段中有liu和wu的记录
mysql> select * from student where name regexp 'liu|wu';
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  6 | xiaoliu |    70 |
|  7 | wangwu  |    80 |
+----+---------+-------+
2 rows in set (0.00 sec)

##“*”匹配前面字符的任意多次
mysql> select * from student where name regexp 'xiao*';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  6 | xiaoliu  |    70 |
+----+----------+-------+
4 rows in set (0.00 sec)

## “+”匹配前面字符至少一次
mysql> select * from student where name regexp 'xiaoo*';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  6 | xiaoliu  |    70 |
+----+----------+-------+
4 rows in set (0.00 sec)

## 匹配指定字符集中的任意一个
mysql> select * from student where name regexp '^[a-z]';
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaoming |   100 |
|  2 | xiaohong |    60 |
|  3 | xiaobai  |   100 |
|  4 | lisi     |    40 |
|  5 | zhangsan |    60 |
|  6 | xiaoliu  |    70 |
|  7 | wangwu   |    80 |
|  8 | lili     |  NULL |
+----+----------+-------+
8 rows in set (0.00 sec)

八、运算符

用于对记录中的字段值进行运算
MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符

8.1 算术运算符

## 加减乘除
mysql> select 1+1,1-1,1*4,4/2,7%3;
+-----+-----+-----+--------+------+
| 1+1 | 1-1 | 1*4 | 4/2    | 7%3  |
+-----+-----+-----+--------+------+
|   2 |   0 |   4 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

8.2 比较运算符

1）等于运算符
用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。其中字符的比较是根据 ASCII 码来判断的。

mysql> select 1=1,1=3,'a'='a','a'='z';
+-----+-----+---------+---------+
| 1=1 | 1=3 | 'a'='a' | 'a'='z' |
+-----+-----+---------+---------+
|   1 |   0 |       1 |       0 |
+-----+-----+---------+---------+
1 row in set (0.00 sec)

2）不等于运算符
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0。
需要注意的是不等于运算符不能用于判断 NULL。

mysql> select 1!=1, 'a'!='ab',1<>2,'a'<>null;
+------+-----------+------+-----------+
| 1!=1 | 'a'!='ab' | 1<>2 | 'a'<>null |
+------+-----------+------+-----------+
|    0 |         1 |    1 |      NULL |
+------+-----------+------+-----------+

3）大于、大于等于、小于、小于等于运算符

mysql> select 'a'>'z',2<1,3>1;
+---------+-----+-----+
| 'a'>'z' | 2<1 | 3>1 |
+---------+-----+-----+
|       0 |   0 |   1 |
+---------+-----+-----+
1 row in set (0.01 sec)

4） IS NULL、IS NOT NULL
IS NULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0。
IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0。

mysql> select 1 is null ,'a' is not null ,null is null,null is not null;
+-----------+-----------------+--------------+------------------+
| 1 is null | 'a' is not null | null is null | null is not null |
+-----------+-----------------+--------------+------------------+
|         0 |               1 |            1 |                0 |
+-----------+-----------------+--------------+------------------+

5） between and
用于判断一个值是否落在某两个值之间

mysql> select 's' between 'a' and 'z','a' between 'v' and 'z';
+-------------------------+-------------------------+
| 's' between 'a' and 'z' | 'a' between 'v' and 'z' |
+-------------------------+-------------------------+
|                       1 |                       0 |
+-------------------------+-------------------------+

6） least、greatest
LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。
GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL，则返回结果就为 NULL。

mysql> select least(11,43,112),greatest(11,43,112),least('f','s','r'),greatest('t','s','p');
+------------------+---------------------+--------------------+-----------------------+
| least(11,43,112) | greatest(11,43,112) | least('f','s','r') | greatest('t','s','p') |
+------------------+---------------------+--------------------+-----------------------+
|               11 |                 112 | f                  | t                     |
+------------------+---------------------+--------------------+-----------------------+
1 row in set (0.00 sec)

7） in、not in
IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。

mysql> select 's' in('a','s',1),1 in (1,2,'a'),3 not in (1,2,3);
+-------------------+----------------+------------------+
| 's' in('a','s',1) | 1 in (1,2,'a') | 3 not in (1,2,3) |
+-------------------+----------------+------------------+
|                 1 |              1 |                0 |
+-------------------+----------------+------------------+

8） like、not like
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0；NOT LIKE 正好跟 LIKE 相反。
LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。

mysql> select 'abc' like 'a%','abc' like 'a__';
+-----------------+------------------+
| 'abc' like 'a%' | 'abc' like 'a__' |
+-----------------+------------------+
|               1 |                1 |
+-----------------+------------------+
1 row in set (0.00 sec)

8.3 逻辑运算符

1）逻辑非
逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真

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

2）逻辑与
如果所有值都是真返回 1，否则返回 0。（全1出1，有0出0）

mysql> select 0 and 0,1 and 1,1&&0;
+---------+---------+------+
| 0 and 0 | 1 and 1 | 1&&0 |
+---------+---------+------+
|       0 |       1 |    0 |
+---------+---------+------+
1 row in set (0.00 sec)

3）逻辑或
任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。（全0出0，有1出1）

mysql> select 1 or 0, 1 or 1 , 0 or 0;
+--------+--------+--------+`mysql> select 1 xor 0, 1 xor 1, 0 xor 0;
+---------+---------+---------+
| 1 xor 0 | 1 xor 1 | 0 xor 0 |
+---------+---------+---------+
|       1 |       0 |       0 |
+---------+---------+---------+
1 row in set (0.00 sec)
`
| 1 or 0 | 1 or 1 | 0 or 0 |
+--------+--------+--------+
|      1 |      1 |      0 |
+--------+--------+--------+
1 row in set (0.00 sec)

4）逻辑异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0，另一个为非 0，则返回结果为 1；
当任意一个值为 NULL 时，返回值为 NULL。

8.4 位运算符

运算符	描述
&	按位与
I	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

位运算符，化为二进制进行运算，对二进制数进行计算的运算符。

九、连接查询

通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。
要先确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。

9.1 内连接

在from子句中使用关键字 inner join 来连接多张表，并使用 on子句设置连接条件。
mysql> select info.name,hob.hobbyname from info inner join hob on info.hobby=hob.id;
内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。

9.2 外连接

1）左连接，主表在左边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来

mysql> select student.name,teacher.name from student left join teacher on student.id=teacher.id;
+----------+------+
| name     | name |
+----------+------+
| xiaoming | kai  |
| xiaohong | li   |
| xiaohong | tian |
| xiaobai  | NULL |
| lisi     | NULL |
| zhangsan | NULL |
| xiaoliu  | NULL |
| wangwu   | NULL |
| lili     | NULL |
+----------+------+
9 rows in set (0.00 sec)

2）右连接，主表在右边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来

mysql> select student.name,teacher.name from student right join teacher on student.id=teacher.id;
+----------+------+
| name     | name |
+----------+------+
| xiaoming | kai  |
| xiaohong | li   |
| xiaohong | tian |
+----------+------+
3 rows in set (0.00 sec)

十、数据库函数

MySQL提供了实现各种功能的函数
常用的函数分类
- 数学函数
- 聚合函数
- 字符串函数
- 日期时间函数

10.1常用的数学函数

abs（x）返回x的绝对值
rand（）返回0~1的随机数
mod（x，y）返回x除以y以后的余数
power（x，y）返回x的y次方
round（x）返回离x最近的证书
round（x，y）保留x的y位小数四舍五入后的值
sqrt（x）返回x的平方根
truncate（x，y）返回数字x截断为y位小数的值
ceil（x）返回大于或等于x的最小整数
floor（x）返回小于或等于x的最大整数
greatest（x1，x2，…）返回集合中最大的值

10.2 聚合函数

对表中数据记录进行集中概括而设计的一类函数
常用的函数聚合
- avg（）返回指定列的平均值
- count（）返回指定列中非NULL值的个数
- min（）返回指定列的最小值
- max（）返回指定列的最大值
- sum（）返回指定列的所有值之和

10.3 字符串函数

常用的字符串函数
- length（x）返回字符串x的长度
- trim（）返回去除指定格式的值
- concat（x，y）将提供的参数x和y拼接成一个字符串
- upper（x）将字符串x的所有字母变成大写字母
- lower（x）将字符串x的所有字母变成小写字母
- left（x，y）返回字符串x的前y个字符
- right（x，y）返回字符串x的后y个字符
- repeat（x，y）将字符串x重复y次
- space（x）返回x个空格
- replace（x，y，z）将字符串z替代字符串x中的字符串y
- strcmp（x，y）比较x和y，返回的值可以为-1,0,1
- substring（x，y，z）获取从字符串x中的第y个位置开始长度为z的字符串
- reverse（x）将字符串x反转

10.4 日期时间函数

常用的日期时间函数
- curdate（）返回当前时间的年月日
- curtime（）返回当前时间的时分秒
- now（）返回当前时间的日期和时间
- month（x）返回日期x中的月份值
- week（x）返回日期x是年度第几个星期
- hour（x）返回x中的小时值
- minute（x）返回x中的分钟值
- second（x）返回x中的秒钟值
- dayofweek（x）返回x是星期几，1是星期日，2是星期一
- dayofmonth（x）计算日期x是本月的第几天
- dayofyear（x）计算日期x是本年的第几天

liuqingyunCSDN

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL高级语句与函数

MySQL高级语句一、按关键字排序1.1 按单字段降序排序1.2 按单字段排序升序1.3 按多字段排序二、对结果进行分组三、限制结果条目四、设置别名五、通配符六、子查询七、NULL值正则表达式八、运算符8.1 算术运算符8.2 比较运算符8.3 逻辑运算符8.4 位运算符九、连接查询9.1 内连接9.2 外连接一、按关键字排序使用ORDER BY 语句来实现排序排序可针对一个或多个字段ASC：升序，默认排序方式DESC：降序ORDER BY的语法结构SELECT column1，colum
复制链接

扫一扫