小提示,本文编写于 1,701 天前，最后编辑于 1,485 天前，某些信息可能有些出入，仅供参考。
需求1
查询两个表之间你需要的数据
主表fi_inspect_result_detail：
次表fi_task：
需要查多个字段，然后计算差值，再判断在允差范围内的正常和超标,正常则为"safety",超标标记则"danger"：
- sql实现如下
SELECT
a.id,
a.rollNum,
a.verifiedLength,
a.supplierLength,
(SELECT CEIL(COUNT(id)/10) FROM fi_inspect_result_detail WHERE taskId = 2472) AS totalPage,
100-ROUND((a.verifiedLength/a.supplierlength*100),2)AS lengthPercent,
CASE WHEN 100-ROUND((a.verifiedLength/a.supplierlength*100),2)>3 OR 100-ROUND((a.verifiedLength/a.supplierlength*100),2)<-3
THEN "danger " ELSE "safety" END AS lengthPercentStatus,
a.actualUsableWidth,
a.supplierUsableWidth,
100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2) AS usableWidthPercent,
CASE
WHEN 100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2)>5 OR 100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2)<-5
THEN "danger" ELSE "safety" END AS usableWidthPerecentStatus,
a.actualGsm,
b.gramWeight,
100-ROUND((a.actualGsm/b.gramWeight*100),2) AS gsmPercent,
CASE
WHEN 100-ROUND((a.actualGsm/b.gramWeight*100),2)>2 OR 100-ROUND((a.actualGsm/b.gramWeight*100),2)<-2
THEN "danger" ELSE "safety" END AS gsmPerecentStatus,
a.actualGrosssWeight,
a.supplierGrossWeight,
100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2) AS grossWeightPercent,
CASE
WHEN 100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2)>2 OR 100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2)<-2
THEN "danger" ELSE "safety" END AS grossWeightPerecentStatus,
a.actualNetWeight,
a.supplierNetWeight,
100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2) AS netWeightPercent,
CASE
WHEN 100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2)>2 OR 100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2)<-2
THEN "danger" ELSE "safety" END AS netWeightPerecentStatus
FROM fi_inspect_result_detail a
LEFT JOIN fi_task b
ON a.taskId = b.id
WHERE a.taskId = 2472
AND a.TYPE=1
AND a.isValid=1
ORDER BY lengthPercent DESC
LIMIT 80,10;
运行结果为:
图中红色标记意思对应为 "分页总数量"--totalPage,"差值计算出来的百分比"----****percent,"百分比状态是否正常"-----percentStatus
用命令行运行结果
查询完成。。。。。。
+--------+---------+----------------+----------------+-----------+---------------+---------------------+-------------------+---------------------+--------------------+---------------------------+-----------+------------+------------+-------------------+--------------------+---------------------+--------------------+---------------------------+-----------------+-------------------+------------------+-------------------------+
| id | rollNum | verifiedLength | supplierLength | totalPage | lengthPercent | lengthPercentStatus | actualUsableWidth | supplierUsableWidth | usableWidthPercent | usableWidthPerecentStatus | actualGsm | gramWeight | gsmPercent | gsmPerecentStatus | actualGrosssWeight | supplierGrossWeight | grossWeightPercent | grossWeightPerecentStatus | actualNetWeight | supplierNetWeight | netWeightPercent | netWeightPerecentStatus |
+--------+---------+----------------+----------------+-----------+---------------+---------------------+-------------------+---------------------+--------------------+---------------------------+-----------+------------+------------+-------------------+--------------------+---------------------+--------------------+---------------------------+-----------------+-------------------+------------------+-------------------------+
| 178026 | 1-8 | 99.3 | 101.4 | 14 | 2.07 | safety | 134 | 136.1 | 1.54 | safety | 120 | 186 | 35.48 | danger | 16.3 | 32.3 | 49.54 | danger | 16 | 30.91 | 48.24 | danger |
| 178027 | 1-8 | 99.3 | 101.4 | 14 | 2.07 | safety | 134 | 136.1 | 1.54 | safety | 120 | 186 | 35.48 | danger | 16.3 | 32.3 | 49.54 | danger | 16 | 30.91 | 48.24 | danger |
| 177971 | 1-8 | 99.3 | 101.4 | 14 | 2.07 | safety | 134 | 136.1 | 1.54 | safety | 120 | 186 | 35.48 | danger | 16.3 | 32.3 | 49.54 | danger | 16 | 30.91 | 48.24 | danger |
| 177639 | 1-8 | 99.3 | 101.4 | 14 | 2.07 | safety | 134 | 136.1 | 1.54 | safety | 120 | 186 | 35.48 | danger | 16.3 | 32.3 | 49.54 | danger | 16 | 30.91 | 48.24 | danger |
| 178028 | 1-9 | 143 | 146 | 14 | 2.05 | safety | 135 | 136.1 | 0.81 | safety | 120 | 186 | 35.48 | danger | 24.2 | 32.3 | 25.08 | danger | 23.9 | 30.91 | 22.68 | danger |
| 178029 | 1-9 | 143 | 146 | 14 | 2.05 | safety | 135 | 136.1 | 0.81 | safety | 120 | 186 | 35.48 | danger | 24.2 | 32.3 | 25.08 | danger | 23.9 | 30.91 | 22.68 | danger |
| 177978 | 1-9 | 143 | 146 | 14 | 2.05 | safety | 135 | 136.1 | 0.81 | safety | 120 | 186 | 35.48 | danger | 24.2 | 32.3 | 25.08 | danger | 23.9 | 30.91 | 22.68 | danger |
| 177646 | 1-9 | 143 | 146 | 14 | 2.05 | safety | 135 | 136.1 | 0.81 | safety | 120 | 186 | 35.48 | danger | 24.2 | 32.3 | 25.08 | danger | 23.9 | 30.91 | 22.68 | danger |
| 177997 | 1-3 | 151.6 | 154.7 | 14 | 2.00 | safety | 135 | 136.1 | 0.81 | safety | 120 | 186 | 35.48 | danger | 25.45 | 32.3 | 21.21 | danger | 25.15 | 30.91 | 18.63 | danger |
| 178015 | 1-3 | 151.6 | 154.7 | 14 | 2.00 | safety | 135 | 136.1 | 0.81 | safety | 120 | 186 | 35.48 | danger | 25.45 | 32.3 | 21.21 | danger | 25.15 | 30.91 | 18.63 | danger |
+--------+---------+----------------+----------------+-----------+---------------+---------------------+-------------------+---------------------+--------------------+---------------------------+-----------+------------+------------+-------------------+--------------------+---------------------+--------------------+---------------------------+-----------------+-------------------+------------------+-------------------------+
10 rows in set (0.00 sec)
需求2
根据前端请求，页面数和分页数，下发对应的数据给前端显示，未定。。。
2019.08.27 补充：上次更到一半之后忘记补了
因为一次性下发数据和总条数不太优雅（其实是我不会，，，），所以，分页另外写一条sql，查询 筛选完之后的总条数，也就是 前端要显示的分页数量。如1.2.3.4.5...10 例
需求确定，获取筛选完的分页数
上面的sql写的 稍显复杂，这里我们只需要获取总条数，因此一些条件可以省去，结合思路，sql整理如下：
SELECT COUNT(*) AS totalPage FROM
(SELECT a.id,a.rollNum,
100-ROUND((a.verifiedLength/a.supplierlength*100),2)AS lengthPercent,
100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2) AS usableWidthPercent,
100-ROUND((a.actualGsm/b.gramWeight*100),2) AS gsmPercent,
100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2) AS grossWeightPercent,
100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2) AS netWeightPercent
FROM fi_inspect_result_detail a
LEFT JOIN fi_task b
ON a.taskId = b.id
WHERE a.taskId = 2472
AND a.TYPE=1
AND a.isValid=1
HAVING lengthPercent > '-2'
) heart
这个地方有个逻辑，用HAVING 筛选的问题，前端传过来的筛选条件肯定是变化的，因此，这里采用了拼接sql的方式来接HAVING 包含的筛选条件，然后去数据库查询，获取总条数。
查询结果为总条数，查询举例
至此完成一个小Demo
0911更新：
今天get了一个新姿势，substring不仅可以用在java上，在MySQL里面，居然也能用，有时间真的得好好深入研究下MySQL了，功能强大的一批，不知道之前，我还在琢磨着怎么将text转型......
拼接字符串,简单介绍下用法：
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
例子：str=www.noheart.cn
substring_index(str,'.',1)
结果是：www
substring_index(str,'.',2)
结果是：www.noheart
也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容
相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：
substring_index(str,'.',-2)
结果为：noheart.cn
有人会问，如果我要中间的的wikibt怎么办？
很简单的，两个方向：
从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：
substring_index(substring_index(str,'.',-2),'.',1);
mysql> SELECT substring_index('www.noheart.cn','.',1) AS col1,substring_index(substring_index('www.noheart.cn','.',-2),'.',1) AS coll2;
+------+---------+
| col1 | coll2 |
+------+---------+
| www | noheart |
+------+---------+
1 row in set (0.00 sec)
新的需求是要查询卷id，将存储为text结构的 其实就是int类型的数组，将其中的数值取出来 分别一对一 取得卷ID对应的内容
sql语句如下
mysql> SELECT A.LEVEL,A.remark,A.picGroupId,A.dyelotNo,CASE WHEN LEVEL IN ('4','4/5','5','0') THEN 'Conformed' ELSE 'NotConformed' END AS operateUserName,
SUBSTRING_INDEX(substring_index(A.volumeIds,',',B.help_topic_id+1),',',-1) AS volumeIds FROM fba_head_tail_difference AS A JOIN mysql.help_topic AS B
ON B.help_topic_id< (LENGTH(A.volumeIds)-length(REPLACE (A.volumeIds,',',''))+1) WHERE taskId=2125 AND A.isValid=1 HAVING LEVEL='3/4';
+-------+------------+-------------------------------------------------------------------------------------+----------+-----------------+-----------+
| LEVEL | remark | picGroupId | dyelotNo | operateUserName | volumeIds |
+-------+------------+-------------------------------------------------------------------------------------+----------+-----------------+-----------+
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2 | NotConformed | 25121 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2 | NotConformed | 25122 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2 | NotConformed | 25124 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2 | NotConformed | 25127 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2 | NotConformed | 25133 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2 | NotConformed | 25135 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 3 | NotConformed | 25145 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 3 | NotConformed | 25159 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 4 | NotConformed | 25164 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 4 | NotConformed | 25176 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 5 | NotConformed | 25203 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6 | NotConformed | 25204 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6 | NotConformed | 25211 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6 | NotConformed | 25212 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6 | NotConformed | 25217 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6 | NotConformed | 25225 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 7 | NotConformed | 25229 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 7 | NotConformed | 25230 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 7 | NotConformed | 25239 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 8 | NotConformed | 25245 |
| 3/4 | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 8 | NotConformed | 25256 |
+-------+------------+-------------------------------------------------------------------------------------+----------+-----------------+-----------+
21 rows in set (0.03 sec)
至此，over，完成需求，再一次的感叹MySQL的强大，以及自己多么的菜菜
导入数据问题
我在使用MySQL8.0.11时候，用的sqlyog图形化工具，导入csv，excel格式的数据时候，提示:
The used command is not allowed with this MySQL version
于是我尝试使用命令行导入数据，
LOAD DATA local INFILE
于是在sql编辑器中输入 SHOW VARIABLES LIKE '%local%';
local_infile OFF 即该变量未开启。
解决方法：
输入SET GLOBAL local_infile=1（其中global是全局变量的意思）。
注意:这样还是解决不了~
于是！我们命令行登陆MySQL时附上一句声明：
mysql --local-infile -uroot -ppassword
重新进行导入数据操作：
LOAD DATA LOCAL INFILE 'G:/data.txt' INTO TABLE chinadb (id,parentId,name,country,province,city,district,levelType,cityCode,zipCode,pinYin,JianPin,firstChar,remarks);
搞定~
MySQL 对于数字，字符，数字 组合的字段 进行排序：
如 1-2，1-3，1-2，2-3，1-9，1-6
我们直接 ORDER BY 对于它进行排序是不严谨的
因此，我们可以对该字段进行 拆分 为两个数字 分别排序：
用到SUBSTRING_INDEX 这个函数，
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
直接上代码：
SELECT SUBSTRING_INDEX(rollNum,'-',1)AS sortFirst,SUBSTRING_INDEX(rollNum,'-',-1)AS sortSecond,rollNum FROM a WHERE id=2125 ORDER BY sortFirst,CAST(sortSecond AS UNSIGNED)
+-----------+------------+---------+
| sortFirst | sortSecond | rollNum |
+-----------+------------+---------+
| 1 | 1 | 1-1 |
| 1 | 2 | 1-2 |
| 1 | 3 | 1-3 |
| 1 | 4 | 1-4 |
| 1 | 5 | 1-5 |
| 1 | 6 | 1-6 |
| 1 | 7 | 1-7 |
| 1 | 8 | 1-8 |
| 1 | 9 | 1-9 |
| 1 | 10 | 1-10 |
| 1 | 11 | 1-11 |
| 1 | 12 | 1-12 |
| 1 | 13 | 1-13 |
| 1 | 14 | 1-14 |
| 1 | 15 | 1-15 |
| 1 | 16 | 1-16 |
| 1 | 17 | 1-17 |
| 1 | 18 | 1-18 |
| 1 | 19 | 1-19 |
| 1 | 20 | 1-20 |
| 1 | 21 | 1-21 |
| 2 | 22 | 2-22 |
| 2 | 23 | 2-23 |
| 2 | 24 | 2-24 |
| 2 | 25 | 2-25 |
| 2 | 27 | 2-27 |
| 2 | 31 | 2-31 |
| 2 | 34 | 2-34 |
| 2 | 36 | 2-36 |
| 2 | 38 | 2-38 |
| 2 | 40 | 2-40 |
| 2 | 42 | 2-42 |
| 2 | 43 | 2-43 |
| 2 | 45 | 2-45 |
| 2 | 48 | 2-48 |
| 2 | 50 | 2-50 |
| 2 | 52 | 2-52 |
| 2 | 54 | 2-54 |
| 2 | 55 | 2-55 |
| 2 | 58 | 2-58 |
| 2 | 60 | 2-60 |
| 2 | 62 | 2-62 |
| 3 | 26 | 3-26 |
| 3 | 28 | 3-28 |
| 3 | 29 | 3-29 |
| 3 | 30 | 3-30 |
| 3 | 32 | 3-32 |
| 3 | 33 | 3-33 |
| 3 | 35 | 3-35 |
| 3 | 37 | 3-37 |
| 3 | 39 | 3-39 |
| 3 | 41 | 3-41 |
| 3 | 44 | 3-44 |
| 4 | 64 | 4-64 |
| 4 | 67 | 4-67 |
| 4 | 72 | 4-72 |
上面代码" CAST(sortSecond AS UNSIGNED) "，它的属性：就是将数字类型无符号化，通俗的来讲，就是变成非负数，在无负数的情况下，SIGNED比UNSIGNED的性能相应更高
如上所述，实现对字符串的升序排序，
GROUP BY 语法
“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
SELECT name FROM test
GROUP BY name
GROUP BY 单个字段和多个字段 本质上其实是一样的；
GROUP BY多个字段可以把它看成一个整体字段，以他们整体来进行分组的
GROUP BY可以配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。
#常用聚合函数
count() 计数
sum() 求和
avg() 平均数
max() 最大值
min() 最小值
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
如果我们现在想知道每个部门有多少名在职员工，步骤如下：
筛选在职员工 where to_date='9999-01-01';
对部门进行分组group by dept_no
对员工进行计数 count(emp_no)
SELECT
dept_no as 部门,
count( emp_no) as 人数
FROM
dept_emp
WHERE
to_date = '9999-01-01'
GROUP BY
dept_no
MYSQL 通配符使用
最近遇到个问题，给字符串类型的字段排序，该字段由字母和数字组成，字母可以是任意的，简单的order by 肯定是不适用的
一翻思考，将字母与数字分隔开来，然后再对数字进行排序，但这里的字母可以是任意的，所以考虑到使用通配符判断过滤字母
sql如下：
SELECT DISTINCT dyelotNo FROM fba_roll_divide_dyelot
WHERE id=111 ORDER BY
CAST(CASE WHEN SUBSTRING(dyelotNo,2) = '' THEN 0 ELSE 1 END AS UNSIGNED),
CAST(CASE WHEN dyelotNo REGEXP '[a-z]' THEN SUBSTRING(dyelotNo,2) ELSE dyelotNo END AS UNSIGNED)
排序结果：
+----------+
| dyelotNo |
+----------+ |
| N1 |
| N2 |
| N4 |
| N5 |
| N6 |
| N7 |
| N8 |
| N11 |
| N12 |
| N17 |
| N19 |
| N20 |
| N21 |
+----------+
13 rows in set (0.00 sec)
关键字，正则表达式操作符：REGEXP，
[1-9]意为：匹配1到9任意数字，[a-z]意为匹配任意字符。需要注意的是，对字母的匹配是不区分大小写的，如果需要区分，则在 REGEXP 后加上 BINARY
