mysql 按父级id排序 mysql从低到高排序

转载

mob64ca1404476b 2023-11-03 11:17:38

文章标签 mysql 按父级id排序数据库正则表达式 mysql 字段 文章分类 MySQL 数据库

MySQL进价查询
1.1 单字段排序

■ 使用ORDERBY语句来实现排序

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

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

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

■ ORDER BY的语法结构

■ ORDER BY后面跟字段名

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

mysql> select * from info order by score;
+----+----------+-------+------+
| id | name     | score | addr |
+----+----------+-------+------+
|  3 | oowooo   | 60.00 |      |
|  4 | oowo     | 60.00 |      |
|  2 | lisi     | 70.00 | NULL |
|  5 | wangwu   | 77.00 | NULL |
|  1 | zhangsan | 88.00 | NULL |
+----+----------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from info order by score desc;
+----+----------+-------+------+
| id | name     | score | addr |
+----+----------+-------+------+
|  1 | zhangsan | 88.00 | NULL |
|  5 | wangwu   | 77.00 | NULL |
|  2 | lisi     | 70.00 | NULL |
|  3 | oowooo   | 60.00 |      |
|  4 | oowo     | 60.00 |      |
+----+----------+-------+------+
5 rows in set (0.00 sec)

1.2 多字段排序

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

###只有第一个字段出现和第二字段相同的情况下，第二字段才有意义###

1.3 对结果进行分组-1

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

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

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

■ GROUP BY的语法结构

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

1.4 对结果进行分组-2

■ GROUP BY分组

###对info表的name进行统计在hobby字段显示统计个数###

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

■ GROUP BY结合ORDER BY

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

1.5 对结果进行分组

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

■ 使用LIMIT语句限制条目

■ LIMIT语法结构

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

###查看前三行###

mysql> select * from info limit 3;
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  2 | lisi     | 70.00 | NULL |     2 |
|  3 | oowooo   | 60.00 |      |     2 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

###查看3-5行###

mysql> select * from info limit 2,3;
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
|  5 | wangwu | 77.00 | NULL |     3 |
+----+--------+-------+------+-------+
3 rows in set (0.00 sec)

1.6 设置别名

■ 使用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 |  88.00 |
| lisi     |  70.00 |
| oowooo   |  60.00 |
| oowo     |  60.00 |
| wangwu   |  77.00 |
+----------+--------+
5 rows in set (0.00 sec)

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

mysql> select name  姓名,score  成绩 from info;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |  88.00 |
| lisi     |  70.00 |
| oowooo   |  60.00 |
| oowo     |  60.00 |
| wangwu   |  77.00 |
+----------+--------+
5 rows in set (0.00 sec)

###给info表设置别名i，然后在name和score前面也要加i.使用，不加也可以使用###

mysql> select i.name as 姓名,i.score as 成绩 from info as i;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |  88.00 |
| lisi     |  70.00 |
| oowooo   |  60.00 |
| oowo     |  60.00 |
| wangwu   |  77.00 |
+----------+--------+
5 rows in set (0.00 sec)

###创建tmm新表，将info表的 score字段>=80的数据放在新表tmm上###

mysql> create table tmm as select * from info where score >= 80;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

注意：虽然把数据类型和表结构导过去了，但是表的约束没有，像主键什么的都没有导入过去！！！
1.7 通配符

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

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

■ 常用的通配符

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

● _表示单个字符

###查询z开头的，%表示零个、一个或多个###

mysql> select * from info where name like 'z%';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

###_下划线代表单个字符###

mysql> select * from info where name like '_i_i';
+----+------+-------+------+-------+
| id | name | score | addr | hobby |
+----+------+-------+------+-------+
|  2 | lisi | 70.00 | NULL |     2 |
+----+------+-------+------+-------+
1 row in set (0.00 sec)

1.8 子查询

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

###先建一个num表，里面只有id，在id字段在添加一些数据###

mysql> select * from num;
+------+
| id   |
+------+
|    1 |
|    3 |
|    5 |
|    7 |
+------+
4 rows in set (0.00 sec)

###然后根据刚刚的info表和num表进行多表相连，按照num表的1、3、5、7显示出info表的1、3、5、7行数据###
###后面输出的结果赋予了前面的值###

mysql> select * from info where id in(select id from num);
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  3 | oowooo   | 60.00 |      |     2 |
|  5 | wangwu   | 77.00 | NULL |     3 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

###多层嵌套，从内部括号到外面匹配###

mysql> select * from info where id in(select id from num where name in(select name from num));
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  3 | oowooo   | 60.00 |      |     2 |
|  5 | wangwu   | 77.00 | NULL |     3 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

1.9 视图

■ 数据库中的虚拟表，这张虚拟表中不包含任何数据，只是做了数据映射；

###创建视图v_score表###

mysql> create view v_score as select * from info where score >=80;

###查看一下刚刚创建的视图###

mysql> select * from v_score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88.00 |
+----+----------+-------+
1 row in set (0.00 sec)

mysql> show table status;  ###查看视图表的信息

###这种被称为临时结果集，放在内存当中，重启就会没有###

mysql> select id,name from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | oowooo   |
|  4 | oowo     |
|  5 | wangwu   |
+----+----------+
5 rows in set (0.00 sec)

###要定义别名，比如下图我定义了别名a，就可以使用了###

mysql> select a.id from (select id,name from info) a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

###count()代表统计，exists代表真或假###

mysql> select count(*) from info where exists (select * from info where name='zhangsan');
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

1.10 NULL值

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

###查询info表的addr字段为null值的记录###

mysql> select * from info where addr is null;
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
|  2 | lisi     | 70.00 | NULL |     2 |
|  5 | wangwu   | 77.00 | NULL |     3 |
+----+----------+-------+------+-------+
3 rows in set (0.00 sec)

1.11 正则表达式

■ 根据指定的匹配模式匹配记录中符合要求的特殊字符
■ 使用REGEXP关键字指定匹配模式
■ 常用匹配模式
匹配模式描述实例
^ 匹配文本的开始字符 ‘^bd’ 匹配以 bd 开头的字符串
$ 匹配文本的结束字符 ‘qn$’ 匹配以 qn 结尾的字符串
. 匹配任何单个字符 ‘s.t’ 匹配任何s 和t 之间有一个字符的字符串

匹配零个或多个在它前面的字符 ‘fo*t’ 匹配 t 前面有任意个 o
匹配前面的字符 1 次或多次 ‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串匹配包含指定的字符串 ‘clo’ 匹配含有 clo 的字符串
p1 p2 匹配 p1 或 p2
[…] 匹配字符集合中的任意一个字符 ‘[abc]’ 匹配 a 或者 b 或者 c
[^…] 匹配不在括号中的任何字符 ‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n} 匹配前面的字符串 n 次 ‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m} 匹配前面的字符串至少 n 次，至多m 次 ‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次
查询以z开头的

mysql> select * from info where name regexp '^z';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

查询以wu为结尾的

mysql> select * from info where name regexp 'wu$';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  5 | wangwu | 77.00 | NULL |     3 |
+----+--------+-------+------+-------+
1 row in set (0.00 sec)

.代表任意字符，查询zhang.an的记录

mysql> select * from info where name regexp 'zhang.an';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

查询oo任意个前面的字符，零次或多次

mysql> select * from info where name regexp 'oo*';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
+----+--------+-------+------+-------+
2 rows in set (0.00 sec)

查询ow前面字符至少一次

mysql> select * from info where name regexp 'ow+';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
+----+--------+-------+------+-------+
2 rows in set (0.00 sec)

查询z开头的

mysql> select * from info where name regexp '^[z]';
+----+----------+-------+------+-------+
| id | name     | score | addr | hobby |
+----+----------+-------+------+-------+
|  1 | zhangsan | 88.00 | NULL |     1 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

查询不是z开头的

mysql> select * from info where name regexp '^[^z]';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  2 | lisi   | 70.00 | NULL |     2 |
|  3 | oowooo | 60.00 |      |     2 |
|  4 | oowo   | 60.00 |      |     2 |
|  5 | wangwu | 77.00 | NULL |     3 |
+----+--------+-------+------+-------+
4 rows in set (0.01 sec)

匹配两个o

mysql> select * from info where name regexp 'oo{2}';
+----+--------+-------+------+-------+
| id | name   | score | addr | hobby |
+----+--------+-------+------+-------+
|  3 | oowooo | 60.00 |      |     2 |
+----+--------+-------+------+-------+
1 row in set (0.00 sec)

1.12 运算符

■ 算数运算符
MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符。下面分别对这四种运算符进行说明。
1.12.1 算数运算符

MYSQL支持使用的运算符号

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余数

mysql> select 2+3,3-2,3*4,8/2,9%2;
+-----+-----+-----+--------+------+
| 2+3 | 3-2 | 3*4 | 8/2    | 9%2  |
+-----+-----+-----+--------+------+
|   5 |   1 |  12 | 4.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

1.12.2 比较运算

常用比较运算符
运算符 | 描述 |运算符| 描述

1.12.2.1 等于运算符

等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同。例如，等于运算符在数字、字符串和表达式上的使用，具体操作如下所示.

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

1.12.2.2 不等于运算符

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

mysql> mysqlt 'shuai'<>'chou',13!=17,NULL=NULL;
+-----------------+--------+-----------+
| 'shuai'<>'chou' | 13!=17 | NULL=NULL |
+-----------------+--------+-----------+
|               1 |      1 |      NULL |
+-----------------+--------+-----------+
1 row in set (0.00 sec)

从下面两组字符串比较可知，字符串比的是字符的个数和顺序

mysql> select 'abc'='abc';
+-------------+
| 'abc'='abc' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select 'abc'='bca';
+-------------+
| 'abc'='bca' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

1.12.2.3 大于、大于等于、小于、小于等于运算符

大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。

小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。

大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。

小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。

数值比较会自动转换ASCII表的数值

我们需要大体记住几个常用Dec（十进制）的就行：

0是48

大写A是65 B是66 依次往后推算

小写a是97 b是9

mysql> mysql> select 5>3,'a'>'b',3>=4,(5+6)>=(3+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 5>3 | 'a'>'b' | 3>=4 | (5+6)>=(3+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
|   1 |       0 |    0 |            1 |     0 |   1 |        1 |      1 |      NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
1 row in set (0.00 sec)

一个有趣的发现，比较是或的关系，一旦开头的b比a大，后面就不在比较

mysql> select 'abc'<'baa';
+-------------+
| 'abc'<'baa' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

1.12.2.4 IS NULL、IS NOT NULL

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

mysql> select 2 is NULL,'d' is not NULL,NULL IS NULL;
+-----------+-----------------+--------------+
| 2 is NULL | 'd' is not NULL | NULL IS NULL |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)

1.12.2.5 BETWEEN AND

BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间。

mysql> select 5 between 2 and 8,7 between 5 and 10,'d' between 'a' and 'z';
+-------------------+--------------------+-------------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 'd' between 'a' and 'z' |
+-------------------+--------------------+-------------------------+
|                 1 |                  1 |                       1 |
+-------------------+--------------------+-------------------------+
1 row in set (0.00 sec)

数字能否与字符比较？

mysql> select 5 between 2 and 8,7 between 5 and 10,8 between 'a' and 'z'; 
+-------------------+--------------------+-----------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 8 between 'a' and 'z' |
+-------------------+--------------------+-----------------------+
|                 1 |                  1 |                     0 |
+-------------------+--------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#数字不能与字符比较的，而且between是包含头和尾的

1.12.2.6 LEAST、GREATEST

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

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

具体操作如下

mysql> select least(10,20,30),greatest(10,20,30);
+-----------------+--------------------+
| least(10,20,30) | greatest(10,20,30) |
+-----------------+--------------------+
|              10 |                 30 |
+-----------------+--------------------+
1 row in set (0.00 sec)

1.12.2.7 IN、NOT IN

IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。

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

mysql> select 'a' in ('a','b','c'),'a' not in ('a','b','c');
+----------------------+--------------------------+
| 'a' in ('a','b','c') | 'a' not in ('a','b','c') |
+----------------------+--------------------------+
|                    1 |                        0 |
+----------------------+--------------------------+
1 row in set (0.00 sec)
从以上结果可以看出，IN 和 NOT IN 的返回值正好相反。

1.12.2.8 IN、NOT IN

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

mysql> select 'abcd' like '_bcd','abcd' like '%d','abcd' not like '%cd';
+--------------------+------------------+-----------------------+
| 'abcd' like '_bcd' | 'abcd' like '%d' | 'abcd' not like '%cd' |
+--------------------+------------------+-----------------------+
|                  1 |                1 |                     0 |
+--------------------+------------------+-----------------------+
1 row in set (0.00 sec)

1.12.3 逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示。

又被称布尔值运算符

用来判断表达式的真假

常用的逻辑运算符号
运算符描述
NOT 或！逻辑非
AND 或 && 逻辑与
OR 逻辑或
XOR 逻辑异或

1.12.3.1 逻辑非

逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。例如，对非 0 值和 0 值分别作逻辑非运算，具体操作如下所示。

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

1.12.3.2 逻辑与

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算，具体操作如下所示。

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

1.12.3.3 逻辑或

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算。
一旦有个非0值，出来就是非0值
逻辑或其实还可以用 ||,但是还是建议用or，因为使用||会出现问题，输出不到你想要的结果

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

1.12.3.4 逻辑异或

两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0，另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL。例如，对非0 值、0 值和 NULL 值分别作逻辑异或运算，具体操作如下所示。

mysql> select 0 xor 3,0 xor 0,0 xor null,1 xor null;
+---------+---------+------------+------------+
| 0 xor 3 | 0 xor 0 | 0 xor null | 1 xor null |
+---------+---------+------------+------------+
|       1 |       0 |       NULL |       NULL |
+---------+---------+------------+------------+
1 row in set (0.00 sec)
可以看出 有null结果就会为null

1.12.4 位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。

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

示例：对数字进行按位与、或和取反运算

mysql> select 10&15,10|15,10^15,5 &~1;
+-------+-------+-------+-------+
| 10&15 | 10|15 | 10^15 | 5 &~1 |
+-------+-------+-------+-------+
|    10 |    15 |     5 |     4 |
+-------+-------+-------+-------+
1 row in set (0.00 sec)

    对数字进行左移位运算，向左移动位数，空缺处补0

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

对数字进行右移位运算，向右移动位数，多余的位数直接删除

mysql> select 15>>2;
+-------+
| 15>>2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

1.12.5 运算符的优先级

以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级，可以使用（）改变优先级。

优先级	运算符	优优先级	运算符
1	！	8	I
2	~	9	=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
3	^	10	BETWEEN,CASE,WHEN,THEN,ELSE
4	* , /(DIV), %(MOD)	11	NOT
5	+,-	12	&&,AND
6	>>,<<	13	II,OR,XOR
7	&	14	:=

1.12.6 连接查询

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

1.12.6.1 内连接

■ 两张或多张表中同时符合某种条件的数据记录组合
■ FROM子句中使用INNER JOIN关键字连接多张表，并使用ON设置连接条件
■ 是系统默认的表连接方式，可以省略INNER关键字
■ 多表支持连续使用INNER JOIN，建议不超过三个表
■ 语法结构

mysql> select info.id,info.name from info inner join num on info.id=num.id;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  3 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

1.12.6.2 左连接

■ 也被称为左外连接
■ 在FROM子句中使用LEFT JOIN关键字来表示
■ 匹配左表中所有及右表中符合条件的行

mysql> select info.id,info.name,Hub.name from info left join Hub on info.hobby=Hub.id;
+----+----------+--------------+
| id | name     | name         |
+----+----------+--------------+
|  1 | zhangsan | 云计算       |
|  3 | wangwu   | 大数据       |
|  2 | lisi     | 人工智能     |
+----+----------+--------------+
3 rows in set (0.00 sec)

1.12.6.3 右连接

■ 也被称为右外连接
■ 在FROM子句中使用RIGHT JOIN 关键字来表示
■ 匹配右表中所有行及左表中符合条件的行

mysql> select info.id,info.name,Hub.name from info right join Hub on info.hobby=Hub.id;
+------+----------+--------------+
| id   | name     | name         |
+------+----------+--------------+
|    1 | zhangsan | 云计算       |
|    2 | lisi     | 人工智能     |
|    3 | wangwu   | 大数据       |
+------+----------+--------------+
3 rows in set (0.00 sec)

二、MySQL数据库函数
2.1 数据库函数

■ MySQL提供了实现各种功能的函数
■ 常用的函数分类

数学函数
聚合函数
字符串函数
日期时间函数

2.2 数学函数

■ 常用的数学函数

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(x 1， x 2…) 返回集合中最大的值
least(x 1， x 2…) 返回集合中最小的值

实例操作：

abs(x) 返回x的绝对值

mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

rand() 返回0到1的随机数

mysql> select rand();
+----------------------+
| rand()               |
+----------------------+
| 0.012290180672230368 |
+----------------------+
1 row in set (0.00 sec)

mod(x,y) 返回x除以y以后的余数

mysql> select mod (7,2);
+-----------+
| mod (7,2) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

power（x,y）返回x的y次方

mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)

round(x) 返回离x最近的整数

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

round(x,y）保留x的y位小数四舍五入后的值

mysql> select round(1.49,1);
+---------------+
| round(1.49,1) |
+---------------+
|           1.5 |
+---------------+
1 row in set (0.00 sec)

sqrt(x) 返回x的平方根

mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

truncate(x,y) 返回数字x截断为y位小数的值

mysql> select truncate(1.449,2);
+-------------------+
| truncate(1.449,2) |
+-------------------+
|              1.44 |
+-------------------+
1 row in set (0.00 sec)

ceil（x）返回大于或等于x的最小整数
floor(x) 返回小于或等于x的最大整数

mysql> select ceil(1.1),floor(1.1);
+-----------+------------+
| ceil(1.1) | floor(1.1) |
+-----------+------------+
|         2 |          1 |
+-----------+------------+
1 row in set (0.00 sec)

greatest(x1,x2…) 返回集合中最大的值
least(x1,x2…) 返回集合中最小的值

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

2.3 聚合函数

■ 对表中数据记录进行集中概述而设计的一类函数
■ 常用的聚合函数

avg() 返回指定列的平均值
count() 返回指定列中非NULL值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum() 返回指定列的所有值之和
操作实例：
avg() 返回指定列的平均值

mysql> select avg(score) from info;
+------------+
| avg(score) |
+------------+
|  80.333333 |
+------------+
1 row in set (0.01 sec)

count() 返回指定列中非NULL值的个数

mysql> select count(3);
+----------+
| count(3) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

min() 返回指定列的最小值

mysql> select min(score) from info;
+------------+
| min(score) |
+------------+
|      70.00 |
+------------+
1 row in set (0.00 sec)

max() 返回指定列的最大值

mysql> select max(score) from info;
+------------+
| max(score) |
+------------+
|      88.00 |
+------------+
1 row in set (0.00 sec)

sum() 返回指定列的所有值之和

mysql> select sum(score) from info;
+------------+
| sum(score) |
+------------+
|     241.00 |
+------------+
1 row in set (0.00 sec)

2.4 字符串函数

■ 常用的字符串函数

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反转

操作实例：

length(x) 返回字符串x的长度

mysql> select length('abc');
+---------------+
| length('abc') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

trim() 返回去除指定格式的值

mysql> select length(trim(' a bc '));
+------------------------+
| length(trim(' a bc ')) |
+------------------------+
|                      4 |
+------------------------+
1 row in set (0.00 sec)
由上实验可知，只能去掉头尾空格，字符中间的空格不会去掉

concat(x， y) 将提供的参数x和y拼接成一个字符串

mysql> select length(concat('abc','de f'));
+------------------------------+
| length(concat('abc','de f')) |
+------------------------------+
|                            7 |
+------------------------------+
1 row in set (0.00 sec)
由上实验可知，空格也计算进去了

upper(x) 将字符串x的所有字母变成大写字母
lower(x) 将字符串x的所有字母变成小写字母

mysql> select upper('abc'),lower('ABC');
+--------------+--------------+
| upper('abc') | lower('ABC') |
+--------------+--------------+
| ABC          | abc          |
+--------------+--------------+
1 row in set (0.01 sec)

left(x， y) 返回字符串x的前y个字符，也就是从左数三个
right(x， y) 返回字符串x的后y个字符，也就是从右数两个

mysql> select left('abcdefg',3),right('abcdefg',2);
+-------------------+--------------------+
| left('abcdefg',3) | right('abcdefg',2) |
+-------------------+--------------------+
| abc               | fg                 |
+-------------------+--------------------+
1 row in set (0.00 sec)

repeat(x， y) 将字符串x重复y次

mysql> select repeat('abc',3);
+-----------------+
| repeat('abc',3) |
+-----------------+
| abcabcabc       |
+-----------------+
1 row in set (0.00 sec)

space(x) 返回x个空格

mysql> select length(concat('abc',space(3),'abc'));
+--------------------------------------+
| length(concat('abc',space(3),'abc')) |
+--------------------------------------+
|                                    9 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select concat('abc',space(3),'abc');
+------------------------------+
| concat('abc',space(3),'abc') |
+------------------------------+
| abc   abc                    |
+------------------------------+
1 row in set (0.00 sec)

replace(x， y， z) 将字符串z替代字符串x中的字符串y

###把bc替换成12###

mysql> select replace('abc','bc','12');
+--------------------------+
| replace('abc','bc','12') |
+--------------------------+
| a12                      |
+--------------------------+
1 row in set (0.00 sec)

strcmp(x， y) 比较x和y，返回的值可以为-1， 0， 1

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

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

substring(x， y， z) 获取从字符串x中的第y个位置开始长度为z的字符串

###截取从左到右第三个字符开始的三个字符###

mysql> select substring('abcdefg',3,3);
+--------------------------+
| substring('abcdefg',3,3) |
+--------------------------+
| cde                      |
+--------------------------+
1 row in set (0.00 sec)

reverse(x)将字符串x反转

###数值倒过来###

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

2.5 日期时间函数

MySQL 也支持日期时间处理，提供了很多处理日期和时间的函数。一些常用的日期时间函数
字符串函数描述

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 是本年的第几天

MySQL 日期时间函数的使用方法，具体操作如下所示。

curdate()、curtime()、now()

mysql> select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2020-10-16 | 13:46:45  | 2020-10-16 13:46:45 |
+------------+-----------+---------------------+
1 row in set (0.01 sec)

month、week、hour

mysql> select month('2020-10-10'),week('2020-10-16'),hour('08:08');
+---------------------+--------------------+---------------+
| month('2020-10-10') | week('2020-10-16') | hour('08:08') |
+---------------------+--------------------+---------------+
|                  10 |                 41 |             8 |
+---------------------+--------------------+---------------+
1 row in set (0.00 sec)

minute、second

mysql> select minute(now()),second(now());
+---------------+---------------+
| minute(now()) | second(now()) |
+---------------+---------------+
|            51 |            47 |
+---------------+---------------+
1 row in set (0.00 sec)

dayofweek(）、dayofmonth()、dayofyear()

mysql> select dayofweek(now()),dayofmonth(now()),dayofyear(now());
+------------------+-------------------+------------------+
| dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) |
+------------------+-------------------+------------------+
|                6 |                16 |              290 |
+------------------+-------------------+------------------+
1 row in set (0.00 sec)

2.6 存储过程

前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，
有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程，轻松而高效的去完成这个需求。
2.6.1 创建存储过程语法

使用 CREATE PROCEDURE 语句创建存储过程，其语法格式如下所示。

声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

存储过程开始和结束符号:

BEGIN .... END

变量赋值:

SET @p_in=1

变量定义:

DECLARE l_int int unsigned default 4000000;

创建mysql存储过程、存储函数:

create procedure 存储过程名(参数)

存储过程体:

create function 存储函数名(参数)

完整的实例：

mysql> delimiter $$
mysql> 
mysql> create procedure pic()
  -> begin
  -> select id,name,score from info limit 3;
  -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call pic();
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88.00 |
|  2 | lisi     | 83.00 |
|  3 | wangwu   | 70.00 |
+----+----------+-------+
3 rows in set (0.00 sec)

注意：最后的delimiter ; 分号之间一定要有空格

2.6.2 存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

1、in 输入参数

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;


 
mysql> set @p_in=1;
 
mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+
 
+------+
| P_in |
+------+
|    2 |
+------+
 
mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

以上可以看出，p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为前者为局部变量、后者为全局变量。

2、out输出参数

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
 
mysql> set @p_out=1;
 
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+
 
mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

3、inout输入参数

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

2.6.3 MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3()  
     -> begin 
     -> declare x1 varchar(5) default 'outer';  
     -> begin 
     -> declare x1 varchar(5) default 'inner';  
      -> select x1;  
      -> end;  
       -> select x1;  
     -> end;  
     -> //  
mysql > DELIMITER ;

(2). 条件语句

if-then-else 语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

case语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 
case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case

(3). 循环语句

while ···· end while

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

while 条件 do
    --循环体
endwhile

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：python如何做含7和7的倍数 python中7%2

下一篇：python可以做生产系统吗 python可以编写操作系统吗

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql 按父级id排序 mysql从低到高排序

mysql 按父级id排序 mysql从低到高排序

51CTO博客