脚本宝典收集整理的这篇文章主要介绍了

[MySQL]查询学生选课的情况(一)

脚本宝典小编觉得挺不错的,现在分享给大家,也给大家做个参考,希望能帮助你少写一行代码,多一份安全和惬意。

这是我工作遇到的问题,现在自己设计一个简化的类似场景,现实中这样的数据表设计可能有很多不合理的地方。
首先看表结构:

+--------+--------------+------+-----+---------+-------+ | Field  | Type         | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id     | varchar(38)  | NO   | PRI | NULL    |       | | name   | varchar(255) | YES  |     | NULL    |       | | course | varchar(300) | YES  |     | NULL    |       | +--------+--------------+------+-----+---------+-------+ 

这里只是记录学生的ID,名字,还有选课的科目,科目有很多,在没有关联表的情况下,这么多科目只保存在一个字段中,用逗号隔开。
再看一些数据:

+--------------------------------------+--------+--------------------------------+ | id                                   | name   | course                         | +--------------------------------------+--------+--------------------------------+ | 32268995-f33d-11e4-a31d-089e0140e076 | 张三   | Math,English,Chinese           | | 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四   | Math,English,Chinese,Algorithm | | 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五   | Math,English,Algorithm         | | 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese          | | 656a247a-f33d-11e4-a31d-089e0140e076 | 曹达华 | Chesses                        | +--------------------------------------+--------+--------------------------------+ 

那么如何查找到选择了Math课程的学生?

想想使用关联表的时候,张三, 李四, 李五, 王小明这四个人都一条选择了Math这门课的记录,还有其他不是Math的记录。此时要查找选择了Math课程的学生,一般使用IN语句就可以了:

select * from student_course where course IN ('Math'); 

如果要查找选择了MathAlgorithm课程的学生呢:

select * from student_course where course IN ('Math', 'Algorithm'); 

如此,回到原来的问题,如果我设计一个类似IN一样的函数,那么就可以解决这个问题了。
这个流程我们可以想象出来,是这样子的:
我们取张三的课程信息Math,English,Chinese,首先切割成Math, English,Chinese三个字段,然后分别与与查找条件做比较,类似'Math'.indexOf('Math');,'Math'.indexOf('English');...
只要找到一个就认为符合查找条件。
同样的,如果要查找选择了MathAlgorithm课程的学生,比较过程就变成了:
'Math,Algorithm'.indexOf('Math');,'Math,Algorithm'.indexOf('English');...

切割函数 getSplitTotalLength, getSplitString

CREATE DEFINER = `root`@`%` FUNCTION `getSplitTotalLength`(`f_string` varchar(500),`f_delimiter` varchar(5))  RETURNS int(11) BEGIN     # 计算传入字符串能切分成多少段     return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));       RETURN 0; END; 
CREATE DEFINER = `root`@`%` FUNCTION `getSplitString`(`f_string` varchar(500),`f_delimiter` varchar(5),`f_order` int)  RETURNS varchar(500) BEGIN     #拆分传入的字符串,分隔符,顺序,返回拆分所得的新字符串     declare result varchar(500) default '';     set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));     RETURN result; END; 

类似IN的那个函数 isInSearch

CREATE DEFINER=`root`@`%` FUNCTION `isInSearch`(f_course VARCHAR(300), f_string VARCHAR(300)) RETURNS INT BEGIN      DECLARE len INT DEFAULT 0;     DECLARE idx INT DEFAULT 0;     DECLARE item_code VARCHAR(300) DEFAULT '';     DECLARE item_index INT DEFAULT 0;     IF f_course IS NULL THEN         RETURN 0;     END IF;     SELECT getSplitTotalLength(f_course, ',') INTO len;     label: LOOP         SET idx = idx + 1;         IF idx > len THEN             LEAVE label;         END IF;         SELECT getSplitString(f_course , ',', idx) INTO item_code;         # f_string.indexOf(item_code) > -1 ?         SELECT LOCATE(item_code, f_string) INTO item_index;         IF item_index > 0 THEN             RETURN 1; # got one         END IF;     END LOOP label;     RETURN 0;     END; 

这里说下locate函数,locate(item_code, f_string),如果item_codef_string的子串,返回的结果大于0,是item_codef_string的起始下标(从1开始算起),这个一般的indexOf函数有些不同。

mysql> select locate('Math','Math,Algorithm'); +---------------------------------+ | locate('Math','Math,Algorithm') | +---------------------------------+ |                               1 | +---------------------------------+ mysql> select locate('Math','Chinese,Math,Algorithm'); +-----------------------------------------+ | locate('Math','Chinese,Math,Algorithm') | +-----------------------------------------+ |                                       9 | +-----------------------------------------+ mysql> select locate('Math','Chinese,Algorithm'); +------------------------------------+ | locate('Math','Chinese,Algorithm') | +------------------------------------+ |                                  0 | +------------------------------------+ 

可以看到isInSearch函数返回的是INT类似,因为MySQLIN也是这样的机制。

mysql> select 'Math' in ('Math','Algorightm'); +---------------------------------+ | 'Math' in ('Math','Algorightm') | +---------------------------------+ |                               1 | +---------------------------------+ mysql> select 'Math' in ('Chinese','Algorightm'); +------------------------------------+ | 'Math' in ('Chinese','Algorightm') | +------------------------------------+ |                                  0 | +------------------------------------+ 

如果存在返回1,不存在返回0。

在SELECT语句中使用自定义的函数

mysql> select * from student_course where isInSearch(course, 'Math'); +--------------------------------------+--------+--------------------------------+ | id                                   | name   | course                         | +--------------------------------------+--------+--------------------------------+ | 32268995-f33d-11e4-a31d-089e0140e076 | 张三   | Math,English,Chinese           | | 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四   | Math,English,Chinese,Algorithm | | 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五   | Math,English,Algorithm         | | 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese          | +--------------------------------------+--------+--------------------------------+  mysql> select * from student_course where isInSearch(course, 'Chinese,Japanese'); +--------------------------------------+--------+--------------------------------+ | id                                   | name   | course                         | +--------------------------------------+--------+--------------------------------+ | 32268995-f33d-11e4-a31d-089e0140e076 | 张三   | Math,English,Chinese           | | 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四   | Math,English,Chinese,Algorithm | | 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese          | +--------------------------------------+--------+--------------------------------+ 

总结

以上是脚本宝典为你收集整理的

[MySQL]查询学生选课的情况(一)

全部内容,希望文章能够帮你解决

[MySQL]查询学生选课的情况(一)

所遇到的程序开发问题,欢迎加入QQ群277859234一起讨论学习。如果觉得脚本宝典网站内容还不错,欢迎将脚本宝典网站推荐给程序员好友。 本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。

80%的人都看过