MySQL数据库技术（18)[MySQL防范]

3.8 办理筹划随笔
? ? 本节内容相当杂;介绍了怎样编写办理各种问题的查询.大都内容是在邮件清单上看到的办理问题的筹划（谢谢清单上的那些朋友,他们为办理筹划作了很多工作）.
? ? 3.8.1 将子挑选编写为衔接
? ? MySQL自3.24版本以来才具有子挑选功效.这项功效的贫乏是MySQL 中一件常常令人惋惜的事,但有一件事很多人仿佛没有熟习到,那就是用子挑选编写的查询普通可以用衔接来编写.实事上,即便MySQL 具有了子查询,查抄用子挑选编写的查询也是一件苦差事;用衔接而不是用子挑选来编写会更为有效.
? ? 1. 重新编写挑选匹配值的子挑选
? ? 下面是一个包含一个子挑选查询的样例,它从score 表中挑选全部测试的学分（即,忽视查验的学分）:

? ? 可通过将其转换为一个简单的衔接,不用子挑选也可以编写出相同的查询,以下所示:

? ? 下面的例子为挑选女学生的学分:

? ? 可将其转换为衔接,以下所示:

? ? 这里是一个情势,子挑选查询以下情势:

? ? 这样的查询可转换为以下情势的衔接:

? ? 2. 重新编写挑选非匹配值的子挑选查询
? ? 另一种常用的子挑选查询是查找一个表中有的而另一个表中没有的值.正如从前所看到的那样,"那些未给出的值"这一类的问题是LEFT JOIN 大概有效的一个线索.下面的查询包含一个子挑选（它探求那些全勤的学生）:


? ? 3.8.2 查抄表中未给出的值
? ? 我们已经在3 . 6节"检索记录"中看到,在要想知道一个表中哪些值不呈目前另一表中时,可对两个表利用LEFT JOIN 并查找那些从第二个表中选中NULL 的行.并用下列两个表举例:

? ? 目前让我们来考虑一种更为艰难的情形,"缺了哪些值".关于第1 章中提到的学分保存筹划中,有一个列出学生的student 表,一个列出已经呈现过的学分事件的event 表,以及列出每个学生的每次学分事件学分的一个score 表.但是,假如一个学生在某个测试或查验的同一天病了,那么score 表中将不会有这个学生的该事件的学分,因此,要举行查验或测试的补考.我们怎样查找这些贫乏了的记录,以便能保证让这些学生举行补考?问题是要对全部的学分事件肯定哪些学生没有某个学分事件的学分.换个说法,就是我们但愿知道学生和事件的哪些组合不呈目前学分表中.这就是我们但愿LEFT JOIN 所做的事.这个衔接不像前例中那样简单,因为我们不但仅要查找不呈目前单列中的值;还需求查找两列的组合.
? ? 我们想要的这种组合是全部学生/事件的组合,它们由student 表与event 表的叉积产生:
? ? FROM student, event
? 　然后我们取出此衔接的后果,与score 表履行一个LEFT JOIN 语句找出匹配者:
? ? FROM student, event
? ? LEFT JOIN score ON student.student_id = score.student.id
? ? ? ? ? ? ? ? ? ? AND event.event_id = score.event_id
? ? 请注意,ON 子句使得score 表中的行按照差别表中的匹配者举行衔接.这是办理本问题的关键.LEFT JOIN 强迫为由student 和event 表的叉衔接生成的每行产生一个行,即便没有呼应的score 表记录也是这样.这些贫乏的学分记录的后果行可通过一个事实来辨认,就是来自score 表的列将满是NULL 的.我们可在WHERE 子句中选出这些记录.来自score 表的任何列都是这样,但因为我们查找的是贫乏的学分,测试score 列从概念上大概最为清楚:
? ? WHERE score.score IS NULL
? ? 可操纵ORDER BY 子句对后果举行排序.两种最公道的排序辨别是按学生和按事件举行,我们挑选第一种:
? ? ORDER BY student.student_id, event.event_id
? ? 目前需求做的就是命名我们但愿在输出后果中看到的列.终究的查询以下:
? ? SELECT
? ? ? ? student.name, student.student_id,
? ? ? ? event.date, event,event_id, event.type
? ? FROM
? ? ? ? student,event
? ? ? ? LEFT JOIN score ON student.student_id = score.student_id
? ? ? ? ? ? ? ? ? ? ? ? AND event.event_id = score.event_id
? ? WHERE
? ? ? ? score.score IS NULL
? ? ORDER BY
? ? ? ? student.student_id, event.event_id
? ? 运行此查询得出以下后果:

? ? 这里有一个问题要惹起注意.此输出列出了学生的ID 和事件的I D.student_id 列呈目前student 和score 表中,因此,开始您大概会认为挑选列表可以给出student.student_id 或score . student _ id.但实际不是这样,因为可以找到感爱好记录的底子是全部学分表字段返回N U L L.挑选score.student_id 将只在输出中产生NULL 值的列.近似的推理可利用到event_id 列,它也呈目前event 和score 表中.
? ? 3.8.3 履行UNION 操作
? ? 假如想通过从具有相同构造的多个表中成立一个后果集,可在某些数据库系统中利用某种UNION 语句来实现.MySQL 没有UNION（至少直到3 . 2 4版还没有）,但有很多办法来办理这个问题,下面是两种可行的筹划:
? ? ■ 履行多个SELECT 查询,每个表履行一个.假如不关心所选出行的次序,这样做就行了.
? ? ■ 将每个表中的行选入一个暂时存储表,然后挑选该表的内容.这样可对行按所需的次序举行排序.在MySQL 3.23版及今后的版本中,可通过答应服务器成立存储表来办理这个问题.并且,还可以使该表为暂时表,以便在您与服务器的会话完毕时,自动删除该表.
? ? 在下面的代码中,我们明确地删除该表使服务器释放与其有关的资源.假如客户机会话将持续履行进一步的查询,这样做很有好处.为了取到更好的性能,还可以操纵HEAP（在内存中）表.

? ? 关于3 . 2 3版本,除了必须自己明肯定义hold_tbl 表中的列外,其设法是近似的,并且末尾处的DROP TABLE 是强迫性的,用来避免在以下客户机会话生命周期之后持续存在:

? ? 3.8.4 增添序列号列
? ? 假如用A LTER TABLE 增添AUTO_INCREMENT 列,则该列用序列号自动地填充.下面这组mysql 会话中的语句示出了怎样成立一个表,在此中存放数据,然后增添一个AUTO_INCREMENT 列:

　 ? 3.8.5 对某个已有的摆列行排序
　 ? 假若有一个数值列,可对其按以下举行排序（或对其重排序,假如已对其排过序,但删除了行并且想要对值重新排序使其持续）:
　 ? ALTER TABLE t MODIFY i INT NULL
　 ? UPDATE t SET i = NULL
　 ? ALTER TABLE t MODIFY i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
　?? 但是有一种更简单的办法,那就是删除该列,然后再作为一个A U TO_INCREMENT 列追加它.A LTER TABLE 答应指定多个活动,因此,上述工作可在单个语句中完成:
? ? ALTER TABLE t
? ? DROP i,
? ? ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
? ? 3.8.6 非正常次序的串
? ? 假定有一个表示体育机构人员的表,如橄榄球队,假如按人员职位举行排序,以便以特别的次序表示它,如:教练、教练助理、四分卫、流动后卫、接球员、巡查员等.可将列定义为ENUM 并按但愿呈现的次序定义列举元素.对该列的排序将会以所指定的次序自动举行.
? ? 3.8.7 成立计数表
? ? 在第2章的"利用序列"小节中,我们介绍了怎样操纵L A S T _ I N S E RT_ID(expr) 生成一个序列.那个例子阐明了怎样操纵单列的表举行计数.那样做关于只需求单个计数器的情形可以满意需求,但是,假如需求几个计数器,该办法将会惹起不必要的表反复.假定有一个Web 站点并且想要在几个页面上安排"此页面已经被拜候nnn 次"这样的计数器.那么为每个具有一个计数器的页面成立一个单独的表就有些多余了.避免成立多个计数器表的一种办法是成立一个两列的表.此中一列存放计数值;另一列存放计数器名.这时仍旧可以利用LAST _ INSERT_ID( ) 函数,但可用计数器名来决意用哪一行.这个表以下所示:
? ? CREATE TABLE counter
? ? (
? ? count INT UNSIGNED,
? ? name varchar(255) NOT NULL PRIMARY KEY
? ? )
? ? 此中计数器名为一个串,从而可以调用任何想要的计数器,我们将其定义为PRIMARY KEY 免得名称反复.这里假定利用这个表的利用程序知道他们将利用的名称.关于前面所说的Web 计数器,可通过操纵文件树中每个页面的途径名作为其计数器名的办法,保证计数器名的唯一性.比方,要为站点的主页成立一个新计数器,可履行下列语句:
? ? INSERT INTO counter(name) VALUES("index.HTML")
? ? 它用零值初始化称为" index.html"的计数器.为了生成序列中的下一个值,增添表中呼应行的计数值,然后用LAST _ INSERT_ID( ) 检索它:
? ? UPDATE counter
? ? SET count = LAST_INSERT_ID(count+1)
? ? WHERE name = "index.html"
? ? SELECT LAST_INSERT_ID()
? ? 另一种办法是不用LAST _ INSERT_ID( ) 增添计数器的值,以下所示:
? ? UPDATE counter SET count = count+1 WHERE name = "index.html"
? ? SELECT count FROM counter WHERE name = "index.html"
? ? 但是,假如另一个客户在您公布U P D ATE 语句与SELECT 语句之间增添了该计数器的值,则这种办法工作不正常.不过可在此两条语句的前后辨别安排LOCK TABLES 和U N L O C KTABLES,在您利用该计数器时阻塞其他客户,以办理上述问题.但用L A S T _ I N S E RT_ID( )办法完成一样的工作更为简单一些.因为它的值是客户专用的,您总能得到自己插入的值,而不是其他客户插入的值,并且没必要阻塞其他客户使代码复杂化.
? ? 3.8.8 查抄表能否存在
? ? 在利用程序内部知道一个表能否存在有时很有效.为了做到这一点,可以利用下列任一条语句:
? ? SELECT COUNT(*) FROM tb1_name
? ? SELECT * FROM tb1_name WHERE 1=0
? ? 假如指定的表存在,则上述两条语句都将履行成功,假如不存在,则都失利.它们是这种测试的很好的查询.它们履行速度快,所以不会费太多的时间.这种办法最合适您自己编写的利用程序,因为您可以测试查询的成功与失利并采纳呼应的办法.但在从mysql 运行的批量脚本中不分外有效,因为发生错误时除了终止运行外不大概做任何事（大概可以忽视呼应的错误,但是明显无法再运行该查询了）.

　　以上是“MySQL数据库技术（18)[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：