精华内容
下载资源
问答
  • 码农不识贝叶斯,虽数据枉然

    千次阅读 2017-08-06 11:55:19
    码农不识贝叶斯,虽数据枉然呀!

    数据的重要性毋庸置疑,但是如何让数据产生价值呢?

    对一个全栈老码农而言,经常在开发或者研发管理的时候遇到各种预测、决策、推断、分类、检测、排序等诸多问题。面对“你的代码还有bug么?”这样的挑战,一种理智的回答是,我们已经执行了若干测试用例,代码中存在bug的可能性是百分之零点几。也就是说,我们对当前程序中没有bug的信心是百分之九十九点几。这实际上就是一直贝叶斯思维,或者说使用了贝叶斯方法。不论我们看到,还是没有看到,它都在那里,熠熠生辉。

    如果预测当前软件有没有bug呢?还是要从贝叶斯定理看起。

    贝叶斯定理的浅解

    对老码农来说,贝叶斯定理的概率表达相对清晰,理解起来会相对容易。回忆一下我们学过的概率论,联合概率是满足交换律的,即:

    P(A and B) = P (B and A)
    

    对联合概率以条件概率展开:

    P(A and B ) = P(A) P(B|A)
    P(B and A ) = P(B) P(A|B)
    

    从而得到:

    P(A) P(B|A) = P(B) P(A|B)
    

    简单的变换一下,得到:

    P(B|A=P(A|B)P(B)P(A)

    大功告成,这就是神奇的贝叶斯定理。其中:

    • P(B) 为先验概率,即在得到新数据前某一假设的概率;
    • P(B|A) 为后验概率,即在观察到新数据后计算该假设的概率;
    • P(A|B)为似然度,即在该假设下得到这一数据的概率;
    • P(A)为标准化常量,即在任何假设下得到这一数据的概率。

    还可以加点料,在计算P(A)的时候,可以用加法定理表示:

    P(A) = P(A and B) + P(A and B_) = P(A|B)P(B)+ P(A|B_) P(B_) 
    

    从而有:

    P(B|A)=P(A|B)P(B)P(A|B)P(B)+P(A|B)P(B)

    其中B_ 是与B相反的事件。就测试与bug 之间的估算而言,《贝叶斯推断的思想》(http://www.jianshu.com/p/0a038974d48c)一文给出了贝叶斯推断的结果,其中就使用了这样的方法。

    贝叶斯方法

    贝叶斯方法是一个非常通用的推理框架,用客观的新信息更新我们最初关于某个事物的信念后,就会得到一个新的改进了的信念。通过引入先验的不确定性,允许了初始推断的错误,获得了更新的证据后,也没有放弃初始的推断,而是调整为更符合目前的证据。

    但是,P(A|B) 和 P(B|A) 之类的经常让人混淆,@待字闺中的陈老师给出了理解的一个关键点,区分出规律和现象,就是将A看成“规律”,B看成“现象”,那么贝叶斯公式看成:

    P(|=P(|PP

    陈老师在《这的理解贝叶斯公式吗》和《又一个生活中的贝叶斯应用》给出了几个通俗易懂的例子,这里不再赘述。

    回归到码农生活,我们在改善系统功能的时候,通常的一个手段是AB测试。AB测试是用来检测两种不同处理方式的差异化程度的一种统计设计模式,例如两个网站谁会带来更高的转化率,这里的转化可以是用户的购买、注册、或其他的行为。AB测试的关键点在于组别之间只能容许一个不同点。实验后的分析一般都是用假设检验完成的,例如均值差异检验或者比例差异检验,往往涉及Z分数或令人困惑的p值,而用贝叶斯方法则会自然的多。

    对A,B两个网站的转化概率进行建模。转化率在0~1之间,可采用Beta分布。如果先验是Beta(a1,b1),且 观测到N次访问里有X次转化,那么此时的后验分布是Beta(a1+X,b1+N-X). 假设先验是Beta(1,1),等价于【0,1】上的均匀分布,则示例代码如下:

    from spicy.stats import beta
    a1_prior = 1
    b1_prior =1
    visitors_A = 12345 // 网站A的访问人数
    visitors_B = 1616  // 网站B的访问人数
    conversions_from_A = 1200 // 网站A的转化人数
    conversions_from_B = 15 0  // 网站B的转化人数
    
    posterior_A = beta(a1_prior+ conversions_from_A,b1_prior + visitors_A -conversions_from_A)
    posterior_B = Beta(a1_prior+converiosns_from_B,b1_prior + visitors_B-conversions_from_B)
    // 对后验概率进行采样,用rvs方法生成样本
    samples = 20000
    samples_posterior_A = posterior_A.rvs(samples)
    samples_posterior_B = posterior_B.rvs(samples)
    // 对后验概率进行比较
    print (samples_posterior_A > samples_posterior_B).mean()
    

    使用贝叶斯方法,是从思考数据是如何产生的开始。
    1)什么随机变量能过描述这些统计数据
    2)确实概率分布的所需参数
    3)参数对应早期行为,或后期行为,定义各种变化点
    4)定义参数的概率分布
    5)参数概率分布的变量选择,直到一个可以假设的均匀分布

    对先验及后验概率的选择,针对应用场景而定。就先验分布而言,除了常见的分布外,还有:
    * Gamma分布,指数随机变量的推广
    * 威沙特分布 ,是所有半正定矩阵的分布,是一个协方差矩阵的适当的先验。
    * Beta分布,随机变量定义在0到1之间,使其成为概率和比例的热门选择。
    * 幂律分布,满足公司规模和公司数量之间的关系

    在AB测试中使用了Beta分布, 应用了一个Beta先验分布连同二项式生成的观测数据形成一个Beta后验分布这一原理。

    当面对多种对象之间的因果关系的时候,贝叶斯方法演变成为了贝叶斯网络。

    贝叶斯网络

    贝叶斯网络是为了解决不定性和不完整性问题而提出的,在多个领域中获得了广泛应用。贝叶斯网络是基于概率推理的图形化网络,而贝叶斯公式则是这个概率网络的基础。贝叶斯网络中的每个点代表一个随机变量,都是具有实际含义、需要人为设计的,点和点之间的边代表不确定的因果关系,例如 节点E直接影响到节点H,即E→H,则用从E指向H的箭头建立结点E到结点H的有向弧(E,H),权值(即连接强度)用条件概率P(H|E)来表示。

    实际上,如果事物之间的关系能够用一条链串起来,形成了贝叶斯网络的一个特例——马尔可夫链,换个角度看, 贝叶斯网络是马尔可夫链的非线性扩展。贝叶斯网络中当某点的一个证据出现后,整个网络中事件的概率都会变化。

    简单地,由于多个变量间存在着可能的依赖性,贝叶斯网络说明了其中的联合条件概率分布,允许在变量的子集间定义条件独立性。使用贝叶斯网络的过程与使用贝叶斯方法的过程是类似的:

    1. 通过多个离散变量建立网络,是一个有向无环图
    2. 参数的设置或学习,即对DAG进行遍历,计算各节点的概率表
    3. 网络推理,对因果关系得到置信概率
    4. 推理结果

    例如, 社交网络中不真实账户的检测问题。首先确定网络中的随机变量:
    * 账户的真实性 A
    * 头像的真实性 H
    * 发帖即日志的密度 L
    * 好友的密度 F

    使用观测值示例化H,L,F,把随机值赋给A,得到

    P(A|H,L,F) = P(H|A)P(L|A)P(F|A,H)

    然后就可以在社交网络中尝试使用该推理结果了。在《算法杂货铺——分类算法之贝叶斯网络》一文中对这一例子给出了相对详细的说明。

    可以说,贝叶斯方法席卷了整个概率论,并将应用延伸到各个问题领域,所有需要作出概率预测的地方都可以见到贝叶斯方法的影子,特别地,贝叶斯方法对机器学习能够有什么帮助呢?

    贝叶斯与机器学习

    机器学习在业界炙手可热,但我们在机器学习里同样会遇到预测、决策、分类、检测等问题,贝叶斯方法同样大有用武之地。

    机器学习中有大量的模型,如线性模型、非线性模型,可以采用贝叶斯方法来做模型的预测。也就是说,某一场景可能采用的模型是无限多的,可以用概率分布去描述它。对于假设的先验,对新来的样本做预测如计算它的似然,然后用前面推出来的后验分布做积分,这个给定模型下样本的似然,就是所有可能模型的分布。

    机器学习中模型的选择和比较也是一个常见的问题。例如,在分类问题时,我们使用线性模型还是深度学习的非线性模型呢?贝叶斯方法是这样考虑的: 用A 表示一个模型类别,可能是线性模型,B 表示另一个模型类别,可能是非线性模型。在同样的数据集X下,计算在A,B 情况下观察到训练集的似然Ma,Mb,然后比较Ma和Mb,这是贝叶斯方法做模型选择的一个基本规则。

    实际上, 贝叶斯定理是信息处理的一种准则, 输入是一个先验分布和一个似然函数,输出是一个后验分布。对机器学习中的模型本身,也可以通过贝叶斯方法尝试改进,例如贝叶斯SVM, 高斯过程的贝叶斯等等。

    另外,贝叶斯方法对深度学习而言,至少在调参的这一环节还是很有用的。在神经网络中,每一层参数如卷积核的大小和数量等,都不会在深度学习中被模型自动优化的,需要手工指定,这或许就是贝叶斯优化。

    感慨一下,码农不识贝叶斯,虽知数据也枉然呀!


    其他参考资料

    《贝叶斯方法-概率编程与贝叶斯推断》

    《贝叶斯思维:统计建模的python学习法》

    《数学之美番外篇:平凡而又神奇的贝叶斯方法》

    《Bayesian Method for Machine Learning》www.cs.toronto.edu/~radford/ftp/bayes-tut.pdf

    展开全文
  • 摘自《庄子 秋水》 ===========================秋水时至,百川灌河;泾流大,两涘渚崖之间不辩牛马。于是焉河伯欣然自喜,以天下美为尽在己。...今我睹子之难,吾至于子殆矣,吾长见笑于

    摘自《庄子 秋水》

    ===========================

    秋水时至,百川灌河;泾流之大,两涘渚崖之间不辩牛马。于是焉河伯欣然自喜,以天下之美为尽在己。顺流而东行,至于北海,东面而视,不见水端。于是焉河伯始旋其面目,望洋向若而叹曰:“野语有之曰,‘闻道百,以为莫己若’者,我之谓也。且夫我尝闻少仲尼之闻而轻伯夷之义者,始吾弗信;今我睹子之难穷也,吾非至于子之门则殆矣,吾长见笑于大方之家。”

    北海若曰:“井鼃不可以语于海者,拘于虚也;夏虫不可以语于冰者,笃于时也;曲士不可以语于道者,束于教也。今尔出于崖涘,观于大海,乃知尔丑,尔将可与语大理矣。天下之水,莫大于海,万川归之,不知何时止而不盈;尾闾泄之,不知何时已而不虚;春秋不变,水旱不知。此其过江河之流,不可为量数。而吾未尝以此自多者,自以比形于天地而受气于阴阳,吾在于天地之间,犹小石小木之在大山也。方存乎见少,又奚以自多!计四海之在天地之间也,不似礨空之在大泽乎?计中国之在海内,不似稊米之在大仓乎?号物之数谓之万,人处一焉;人卒九州,谷食之所生,舟车之所通,人处一焉;此其比万物也,不似豪末之在于马体乎?五帝之所连,三王之所争,仁人之所忧,任士之所劳,尽此矣!伯夷辞之以为名,仲尼语之以为博,此其自多也;不似尔向之自多于水乎?”

    河伯曰:“然则吾大天地而小豪末,可乎?”

    北海若曰:“否。夫物,量无穷,时无止,分无常,终始无故。是故大知观于远近,故小而不寡,大而不多,知量无穷,证曏今故,故遥而不闷,掇而不跂,知时无止;察乎盈虚,故得而不喜,失而不忧,知分之无常也;明乎坦涂,故生而不说,死而不祸,知终始之不可故也。计人之所知,不若其所不知;其生之时,不若未生之时;以其至小求穷其至大之域,是故迷乱而不能自得也。由此观之,又何以知豪末之足以定至细之倪?又何以知天地之足以穷至大之域?”

    河伯曰:“世之议者皆曰:‘至精无形,至大不可围。’是信情乎?”

    北海若曰:“夫自细视大者不尽,自大视细者不明。夫精,小之微也;垺,大之殷也;故异便。此势之有也。夫精粗者,期于有形者也;无形者,数之所不能分也;不可围者,数之所不能穷也。可以言论者,物之粗也;可以致意者,物之精也。言之所不能论,意之所不能察致者,不期精粗焉。是故大人之行,不出乎害人,不多仁恩;动不为利,不贱门隶;货财弗争,不多辞让;事焉不借人,不多食乎力,不贱贪污;行殊乎俗,不多辟异;为在从众,不贱佞谄,世之爵禄不足以为劝,戮耻不足以为辱;知是非之不可为分,细大之不可为倪。闻曰:‘道人不闻,至德不得,大人无己’。约分之至也。”

    河伯曰:“然则我何为乎?何不为乎?吾辞受趣舍,吾终奈何?”

    北海若曰:“以道观之,何贵何贱,是谓反衍;无拘而志,与道大蹇。何少何多,是谓谢施;无一而行,与道参差。严乎若国之有君,其无私德,繇繇乎若祭之有社,其无私福;泛泛乎其若四方之无穷,其无所畛域。兼怀万物,其孰承翼

    北海若曰:“知道者必达于理,达于理者必明于权,明于权者不以物害己。至德者,火弗能热,水弗能溺,寒暑弗能害,禽兽弗能贼。非谓其薄之也,言察乎安危,宁于祸福,谨于去就,莫之能害也。故曰:天在内,人在外,德在乎天。知天人之行,本乎天,位乎得;蹢而屈伸,反要而语极。”

    曰:“何谓天?何谓人?”

    北海若曰:“牛马四足,是谓天;落马首,穿牛鼻,是谓人。故曰:无以人灭天,无以故灭命,无以得殉名。谨守而勿失,是谓反其真。”

    夔怜蚿,蚿怜蛇,蛇怜风,风怜目,目怜心。

    夔谓蚿曰:“吾以一足趻踔而行,予无如矣!今子之使万足,独奈何?”蚿曰:“不然。予不见乎唾者乎?喷则大者如珠,小者如雾,杂而下者不可胜数也。今予动吾天机,而不知其所以然。”

    蚿谓蛇曰:“吾以众足行而不及子之无足,何也?”蛇曰:“夫天机之所动,何可易邪?吾安用足哉!”

    蛇谓风曰:“予动吾脊胁而行,则有似也。今子蓬蓬然起于北海,蓬蓬然入于南海,而似无有,何也?”风曰:“然。予蓬蓬然起于北海而入于南海也,然而指我则胜我,我亦胜我。虽然,夫折大木、蜚大屋者,唯我能也,故以众小不胜为大胜也。为大胜者,唯圣人能之”。

    孔子游于匡,宋人围之数币,而弦歌不惙。子路入见,曰:“何夫子之娱也?”孔子曰:“来,吾语女!我讳穷久矣,而不免,命也;求通久矣,而不得,时也。当尧、舜而天下无穷人,非知得也;当桀、纣而天下无通人,非知失也。时势适然。夫水行不避蛟龙者,渔人之勇也。陆行不避兕虎者,猎夫之勇也。白刃交于前,视死若生者,烈士之勇也。知穷之有命,知通之有时,临大难而不惧者,圣人之勇也。由,处矣!吾命有所制矣!”

    无几何,将甲者进,辞曰:“以为阳虎也,故围之;今非也,请辞而退。”

    公孙龙问于魏牟曰:“龙少学先王之道,长而明仁义之行;合同异,离坚白;然不然,可不可;困百家之知,穷众口之辩;吾自以为至达已。今吾闻庄子之言,汒焉异之。不知论之不及与?知之弗若与?今吾无所开吾喙,敢问其方”。

    公子牟隐机大息,仰天而笑曰:“子独不闻夫埳井之鼃乎?谓东海之鳖曰:‘吾乐与!出跳梁乎井干之上,入休乎缺甃之崖;赴水则接腋持颐,蹶泥则没足灭跗;还虷、蟹与科斗,莫吾能若也!且夫擅一壑之水,而跨跱埳井之乐,此亦至矣。夫子奚不时来入观乎?’东海之鳖左足未入,而右膝已絷矣,于是逡巡而却,告之海曰:‘夫千里之远,不足以举其大;千仞之高,不足以极其深。禹之时十年九潦,而水弗为加益;汤之时八年七旱,而崖不为加损。夫不为顷久推移,不以多少进退者,此亦东海之大乐也。’于是埳井之鼃闻之,适适然惊,规规然自失也。且夫知不知是非之竟,而犹欲观于庄子之言,是犹使蚊负山,商蚷驰河也,必不胜任矣!且夫知不知论极妙之言,而自适一时之利者,是非埳井之鼃与?且彼方跐黄泉而登大皇,无南无北,奭然四解,沦于不测;无东无西,始于玄冥,反于大通。子乃规规然而求之以察,索之以辩,是直用管窥天,用锥指地也,不亦小乎?子往矣!且子独不闻夫寿陵余子之学行于邯郸与?未得国能(38),又失其故行矣,直匍匐而归耳。今子不去,将忘子之故,失子之业。”

    公孙龙口呿而不合,舌举而不下,乃逸而走。

    庄子钓于濮水,楚王使大夫二人往先焉,曰:“愿以境内累矣!”

    庄子持竿不顾,曰:“吾闻楚有神龟,死已三千岁矣,王巾笥而藏之庙堂之上。此龟者,宁其死为留骨而贵乎?宁其生而曳尾于涂中乎?”二大夫曰:“宁生而曳尾涂中。”庄子曰:“往矣,吾将曳尾于涂中。”

    惠子相梁,庄子往见之。或谓惠子曰:“庄子来,欲代之相。”于是惠子恐,搜于国中,三日三夜。

    庄子往见之,曰:“南方有鸟,其名为鹓,子知之乎?夫鹓,发于南海而飞于北海;非梧桐不止,非练实不食,非醴泉不饮。于是鸱得腐鼠,鹓过之,仰而视之曰:‘吓’!今子欲以子之梁国而吓我邪?”

    庄子与惠子游于濠梁之上。庄子曰:“儵鱼出游从容,是鱼之乐也?”惠子曰:“子非鱼,安知鱼之乐?”庄子曰:“子非我,安知我不知鱼之乐?”惠子曰:“我非子,固不知子矣;子固非鱼也,子之不知鱼之乐,全矣。”庄子曰:“请循其本。子曰‘汝安知鱼乐’云者,既已知吾知之而问我。我知之濠上也。”

    展开全文
  • MySQL必必会

    千次阅读 多人点赞 2021-08-09 16:00:17
    数据过滤第八章:用通配符进行过滤LIKE操作符使用技巧第九章:用正则表达式进行搜索使用MySQL正则表达式基本字符匹配进行OR匹配匹配几个字符一匹配范围匹配特殊字符匹配字符类匹配多个实例定位符第十章:创建计算...

    姊妹篇——Hive必知必会(数据仓库):https://hiszm.blog.csdn.net/article/details/119907136

    文章目录

    第一章:数据库基础

    基本概念

    • 数据库(database)保存有组织的数据的容器(通常是一个文件或一组文件)。
    • 表(table)某种特定类型数据的结构化清单。
    • 模式(schema)关于数据库和表的布局及特性的信息。
    • 列(column)表中的一个字段。所有表都是由一个或多个列组成的。
    • 数据类型(datatype)所容许的数据的类型。每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据。
    • 行(row)表中的一个记录
    • 主键(primary key)一一列(或一组列),其值能够唯一区分表中每个行。

    我的理解可以将 数据库 比作一个书柜 ,然后里面的 看作一本本,每本书的名字是不同的。模式 可以看作你在书柜里面摆放书的方式不同,而 你可以看作书的每一数据类型 比作书的页面,是文字还是插图就是书的内容主键,可以看作是找到一段话的方法,常见的是页码

    什么是SQL

    SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库通信的语言。

    第二章:MySQL 简介

    $ mysql -u root -p
    

    -u 用户名,-p 输入密码, -h 主机名, -P 端口,注意此时的大小写。
    mysql --help 命令行选项和参数列表

    连接到数据库需要:主机名(本地为localhost)、端口(如果使用默认端口3306之外的端口)、合法的用户名、用户口令(如果需要)


    下载create.sqlpopulate.sql两个sql脚本文件,其中,create.sql包含创建6个数据库表的MySQL语句,populate.sql包含用来填充这些表的INSERT语句。执行下列操作:

    -- 创建数据库
    CREATE DATABASE testdb;
    -- 使用数据库
    -- 必须先使用USE打开数据库,才能读取其中的数据。
    USE testdb;
    -- 执行sql脚本
    

    以上为准备工作。

    create.sql

    ########################
    # Create customers table
    ########################
    CREATE TABLE customers
    (
      cust_id      int       NOT NULL AUTO_INCREMENT,
      cust_name    char(50)  NOT NULL ,
      cust_address char(50)  NULL ,
      cust_city    char(50)  NULL ,
      cust_state   char(5)   NULL ,
      cust_zip     char(10)  NULL ,
      cust_country char(50)  NULL ,
      cust_contact char(50)  NULL ,
      cust_email   char(255) NULL ,
      PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;
    
    #########################
    # Create orderitems table
    #########################
    CREATE TABLE orderitems
    (
      order_num  int          NOT NULL ,
      order_item int          NOT NULL ,
      prod_id    char(10)     NOT NULL ,
      quantity   int          NOT NULL ,
      item_price decimal(8,2) NOT NULL ,
      PRIMARY KEY (order_num, order_item)
    ) ENGINE=InnoDB;
    
    
    #####################
    # Create orders table
    #####################
    CREATE TABLE orders
    (
      order_num  int      NOT NULL AUTO_INCREMENT,
      order_date datetime NOT NULL ,
      cust_id    int      NOT NULL ,
      PRIMARY KEY (order_num)
    ) ENGINE=InnoDB;
    
    #######################
    # Create products table
    #######################
    CREATE TABLE products
    (
      prod_id    char(10)      NOT NULL,
      vend_id    int           NOT NULL ,
      prod_name  char(255)     NOT NULL ,
      prod_price decimal(8,2)  NOT NULL ,
      prod_desc  text          NULL ,
      PRIMARY KEY(prod_id)
    ) ENGINE=InnoDB;
    
    ######################
    # Create vendors table
    ######################
    CREATE TABLE vendors
    (
      vend_id      int      NOT NULL AUTO_INCREMENT,
      vend_name    char(50) NOT NULL ,
      vend_address char(50) NULL ,
      vend_city    char(50) NULL ,
      vend_state   char(5)  NULL ,
      vend_zip     char(10) NULL ,
      vend_country char(50) NULL ,
      PRIMARY KEY (vend_id)
    ) ENGINE=InnoDB;
    
    ###########################
    # Create productnotes table
    ###########################
    CREATE TABLE productnotes
    (
      note_id    int           NOT NULL AUTO_INCREMENT,
      prod_id    char(10)      NOT NULL,
      note_date datetime       NOT NULL,
      note_text  text          NULL ,
      PRIMARY KEY(note_id),
      FULLTEXT(note_text)
    ) ENGINE=MyISAM;
    
    
    #####################
    # Define foreign keys
    #####################
    ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
    ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
    ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-84Rh0Lg7-1628496025724)(https://github.com/Jeanhwea/mysql-crash-course/raw/master/mysql_crash_course_ER_diagram.png)]

    populate.sql

    ##########################
    # Populate customers table
    ##########################
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
    VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
    INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
    VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');
    
    
    ########################
    # Populate vendors table
    ########################
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
    
    
    #########################
    # Populate products table
    #########################
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
    INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
    VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');
    
    
    
    #######################
    # Populate orders table
    #######################
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20005, '2005-09-01', 10001);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20006, '2005-09-12', 10003);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20007, '2005-09-30', 10004);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20008, '2005-10-03', 10005);
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(20009, '2005-10-08', 10001);
    
    
    ###########################
    # Populate orderitems table
    ###########################
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 1, 'ANV01', 10, 5.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 2, 'ANV02', 3, 9.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 3, 'TNT2', 5, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 4, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20006, 1, 'JP2000', 1, 55);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20007, 1, 'TNT2', 100, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20008, 1, 'FC', 50, 2.50);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 1, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 2, 'OL1', 1, 8.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 3, 'SLING', 1, 4.49);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 4, 'ANV03', 1, 14.99);
    
    #############################
    # Populate productnotes table
    #############################
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(101, 'TNT2', '2005-08-17',
    'Customer complaint:
    Sticks not individually wrapped, too easy to mistakenly detonate all at once.
    Recommend individual wrapping.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(102, 'OL1', '2005-08-18',
    'Can shipped full, refills not available.
    Need to order new can if refill needed.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(103, 'SAFE', '2005-08-18',
    'Safe is combination locked, combination not provided with safe.
    This is rarely a problem as safes are typically blown up or dropped by customers.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(104, 'FC', '2005-08-19',
    'Quantity varies, sold by the sack load.
    All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(105, 'TNT2', '2005-08-20',
    'Included fuses are short and have been known to detonate too quickly for some customers.
    Longer fuses are available (item FU1) and should be recommended.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(106, 'TNT2', '2005-08-22',
    'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(107, 'SAFE', '2005-08-23',
    'Please note that no returns will be accepted if safe opened using explosives.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(108, 'ANV01', '2005-08-25',
    'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(109, 'ANV03', '2005-09-01',
    'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(110, 'FC', '2005-09-01',
    'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(111, 'SLING', '2005-09-02',
    'Shipped unassembled, requires common tools (including oversized hammer).'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(112, 'SAFE', '2005-09-02',
    'Customer complaint:
    Circular hole in safe floor can apparently be easily cut with handsaw.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(113, 'ANV01', '2005-09-05',
    'Customer complaint:
    Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
    );
    INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
    VALUES(114, 'SAFE', '2005-09-07',
    'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
    Comment forwarded to vendor.'
    );
    

    第三章:了解数据库和表

    • 使用数据库

      use testdb;
      
    • 显示可用的数据库列表

      SHOW DATABASES;
      
    • 获得一个数据库内的表的列表

      SHOW TABLES;
      
    • 用来显示表列

      SHOW COLUMNS FROM customers;
      DESCRIBE customers;
      
    • 其他SHOW语句

      SHOW STATUS -- 用于显示广泛的服务器状态信息
      SHOW CREATE DATABASE -- 显示创建特定数据库的MySQL语句
      SHOW CREATE TABLE -- 显示创建特定表的语句
      SHOW GRANTS -- 显示授予用户(所有用户或特定用户)的安全权限
      SHOW ERRORS -- 显示服务器错误
      SHOW WARNINGS -- 警告信息
      

    第四章:检索数据

    SELECT语句

    • 检索单个列

      -- 检索products表中的prod_name列
      SELECT prod_name FROM products;
      
    • 检索多个列

      -- 检索products表中的prod_id,prod_name和prod_price列
      SELECT prod_id, prod_name, prod_price FROM products;
      
    • 检索所有列

      -- 检索products表中的所有列
      SELECT * FROM products;
      
    • 检索不同的行

    -- DISTINCT关键字必须直接放在列名的前面,不能部分使用DISTINCT,DISTINCT关键字应用于所有列而不仅是前置它的列。
    SELECT DISTINCT vend_id FROM products;
    
    • 限制结果,指定返回前几行

      -- 返回不多于5行
      SELECT prod_name FROM products LIMIT 5;
      -- 返回从第5行开始的5行
      SELECT prod_name FROM products LIMIT 5,5;
      

    检索出来的第一行为行0,因此LIMIT 1,1检索出来的是第二行而不是第一行

    MySQL 5 支持LIMIT的另一种替代语法
    LIMIT 4 OFFSET 3为从行3开始取4行,同LIMIT 3,4

    -- 使用完全限定的表名
    SELECT products.prod_name FROM products;
    SELECT products.prod_name FROM crashcoures.products;
    

    第五章:排序检索数据

    -- 排序数据
    SELECT prod_name
    FROM products
    ORDER BY prod_name;
    
    -- 按多个列排序
    SELECT prod_id, prod_price, prod_name
    FROM products
    ORDER BY prod_price, prod_name;
    

    对于上述例子中的输出,仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的,则不会按prod_name排序。

    -- 指定排序方向
    -- 默认升序排序,降序使用DESC关键字
    SELECT prod_id, prod_price, prod_name
    FROM products
    ORDER BY prod_price DESC;
    
    SELECT prod_id, prod_price, prod_name
    FROM products
    ORDER BY prod_price DESC, prod_name;
    

    DESC关键字只应用到直接位于其前面的列名。上例中,只对prod_price列指定DESC,对prod_name列不指定。
    升序关键字ASC,可省略

    找出一列中最高或最低的值

    SELECT prod_proce FROM products
    ORDER BY prod_price DESC LIMIT 1;
    

    给出ORDER BY句子时,应保证位于FROM句子之后,如果使用LIMIT,应位于ORDER BY之后。

    order by —— limit

    第六章:过滤数据

    使用WHERE子句

    -- 返回prod_price为2.50的行
    
    SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50
    

    WHERE子句操作符

    符号说明
    =等于
    <>不等于
    !=不等于
    <小于
    <=小于等于
    >大于
    >=大于等于
    BETWEEN在指定的两个值之间
    -- 检查单个值
    -- 返回prod_name为Fuses的一行(匹配时默认不区分大小写)
    SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
    
    -- 列出小于10美元的所有产品
    SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
    
    -- 列出小于等于10美元的所有产品
    SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
    
    -- 不匹配检查
    -- 列出不是1003的所有产品
    SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
    SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;
    
    -- 范围值检查
    -- 检索价格在5-10美元之间的所有产品
    SELECT prod_name, prod_price FROM products
    WHERE prod_price BETWEEN 5 AND 10;
    
    -- 空值检查
    -- 返回价格为空的所有产品
    SELECT prod_name FROM products WHERE prod_price IS NULL;
    

    第七章:数据过滤

    运算等级运算符
    1!
    2-(负号)、~(按位取反)
    3^(按位异或)
    4*、/(DIV)、%(MOD)
    5+、-
    6>>、<<
    7&
    8|
    9=(比较运算)、<=>、<、<=、>、>=、!=、<>、IN、IS NULL、LIKE、REGEXP
    10BETWEEN AND、CASE、WHEN、THEN、ELSE
    11NOT
    12&&、AND
    13XOR
    14||、OR
    15=(赋值运算)、:=
    -- AND操作符
    -- 检索由1003制造且价格小于等于10美元的所有产品的名称和价格
    SELECT prod_id, prod_price, prod_name FROM products
    WHERE vend_id = 1003 AND prod_price <= 10;
    
    -- OR操作符
    -- 检索由1002和1003制造的产品的名称和价格
    SELECT prod_name, prod_price FROM products
    WHERE vend_id = 1002 or vend_id = 1003;
    
    -- 计算次序
    -- AND的优先级高于OR【见上表】
    SELECT prod_name, prod_price FROM products
    WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
    
    -- IN操作符
    -- 用来指定条件范围,取合法值的由逗号分隔的清单全部在圆括号中。
    -- IN比OR执行更快,最大的优点是可以包含其他SELECT语句,能够更动态地建立WHERE子句
    SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003)
    ORDER BY prod_name;
    
    -- NOT操作符
    -- 列出除1002,1003之外所有供应商供应的产品
    SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)
    ORDER BY prod_name;
    
    
    

    第八章:用通配符进行过滤

    LIKE操作符

    LIKE指示MYSQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

    -- 百分号(%)通配符
    -- 表示任何字符出现任意次数
    -- 例:找出所有jet起头的产品
    SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
    
    -- 例:使用多个通配符,匹配任何位置包含anvil的值,不论它之前或之后出现什么字符
    SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
    
    -- 例:找出s起头e结尾的所有产品
    SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
    

    %可以匹配0个字符,%代表搜索模式中给定位置的0个、1个或多个字符
    尾空格可能会干扰通配符,例如,在保存词anvil时,如果它后面有一个或多个空格,则子句WHERE prod_name LIKE '%anvil'将不会匹配它们,因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模 式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。

    -- 下划线(_)通配符
    -- 只匹配单个字符而不是多个字符
    SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
    

    使用技巧

    • 不要过度使用通配符,如果其他操作符能够达到目的应该使用其他操作符
    • 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索的开始处。
      把通配符置于搜索模式的开始处搜索起来是最慢的。
    • 仔细注意通配符的位置

    第九章:用正则表达式进行搜索

    使用MySQL正则表达式

    基本字符匹配

    -- 例:检索prod_name包含文本1000的所有行
    -- REGEXP后所跟的东西作为正则表达式处理
    SELECT prod_name FROM products WHERE prod_name REGEXP '1000'
    ORDER BY prod_name;
    
    -- `.`表示匹配任意一个字符
    SELECT prod_name FROM products WHERE prod_name REGEXP '.000'
    ORDER BY prod_name;
    
    • LIKE和REGEXP的区别:
      LIKE '1000’匹配整个列值,等于’1000’时才会返回相应行,而REGEXP '1000’在列值内进行匹配,如果包含’1000’则会返回相应行。
    -- 区分大小写
    -- 使用关键字BINARY,例如
    WHERE prod_name REGEXP BINARY 'JetPack .000';
    

    进行OR匹配

    -- `|`为正则表达式的OR操作符,表示匹配其中之一
    SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'
    ORDER BY prod_name;
    
    
    -- 可以给出两个以上的OR条件
    `1000|2000|3000`
    

    匹配几个字符之一

    -- `[]`表示匹配[]中的任意一个字符,例如`[123]`是`[1|2|3]`的缩写
    SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'
    ORDER BY prod_name;
    -- output
    +-------------+
    | prod_name   |
    +-------------+
    |1 ton anvil  |
    |2 ton anvil  |
    +-------------+
    
    -- 和直接使用OR的区别:
    SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton'
    ORDER BY prod_name
    
    -- 匹配的是1 OR 2 OR 3 Ton,应该使用'[1|2|3] Ton'
    -- output
    +-------------+
    | prod_name   |
    +-------------+
    |1 ton anvil  |
    |2 ton anvil  |
    |JetPack 1000 |
    |JetPack 2000 |
    |TNT (1 stick)|
    +-------------+
    

    字符集合也可以被否定,为否定一个字集,在集合的开始处放置^,例如[^123]匹配除这些字符的任何东西

    匹配范围

    -- `[0123456789]`可以写成`[0-9]`,其他范围如`[a-z]`
    SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton'
    ORDER BY prod_name
    
    -- output
    +-------------+
    |  prod_name  |
    +-------------+
    | .5 ton anvil|
    | 1 ton anvil |
    | 2 ton anvil |
    +-------------+
    

    匹配特殊字符

    -- 匹配'.'字符,如果使用
    SELECT vend_name FROM vendors WHERE vend_name REGEXP '.'
    ORDER BY vend_name;
    
    -- output
    +---------------+
    |   vend_name   |
    +---------------+
    | ACME          |
    | Anvils R Us   |
    | Furball Inc.  |
    | Jet Set       |
    | Jouets Et Ours|
    | LT Supplies   |
    +---------------+
    
    -- 因为'.'为匹配任意字符,因此匹配特殊字符,必须用'\\'为前导
    
    SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.'
    ORDER BY vend_name;
    
    -- output
    +---------------+
    |   vend_name   |
    +---------------+
    | Furball Inc.  |
    +---------------+
    

    正则表达式中具有特殊意义的所有字符都要通过这种方式转义
    \\也用来引用元字符

    元字符说明
    \\f换页
    \\n换行
    \\r回车
    \\t制表
    \\v纵向制表

    为了匹配\本身,需要使用\\\

    匹配字符类

    说明
    [:alnum:]任意字母和数字(同[a-zA-Z0-9])
    [:alpha:]任意字符(同[a-zA-Z])
    [:cntrl:]空格和制表(同[\\t])
    [:digit:]ASCII控制字符(ASCII)0到31和127
    [:graph:]任意数字(同[0-9])
    [:lower:]任意小写字母(同[a-z])
    [:print:]任意可打印字符
    [:punct:]既不在[:alnum:]又不在[:cntrl:]中的任意字符
    [:space:]包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])
    [:upper:]任意大写字母(同[A-Z])
    [:xdigit:]任意十六进制数字(同[a-fA-F0-9])

    匹配多个实例

    元字符说明
    *0个或多个匹配
    +1个或多个匹配(等于{1,})
    0个或1个匹配(等于{0,1})
    {n}指定数目的匹配
    {n,}不少于指定数目的匹配
    {n.m}匹配数目的范围(m不超过255)

    例:

    SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
    ORDER BY prod_name
    -- output
    +---------------+
    |   prod_name   |
    +---------------+
    | TNT (1 stick) |
    | TNT (5 sticks)|
    +---------------+
    
    -- '\\('匹配'('
    '[0-9]'匹配任意数字
    'stick?'匹配'stick''sticks'
    '\\)'匹配')'
    

    例:匹配连在一起的4位数字

    SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'
    ORDER BY prod_name;
    -- output
    +---------------+
    |   prod_name   |
    +---------------+
    | JetPack 1000  |
    | JetPack 2000  |
    +---------------+
    -- 也可以写成 '[0-9][0-9][0-9][0-9]'
    

    定位符

    元字符说明
    ^文本的开始
    $文本的结尾
    [:<:]词的开始
    [:>:]词的结尾

    例:找出以一个数(包括小数点开头)开始的所有产品

    SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'
    ORDER BY prod_name;
    -- output
    +---------------+
    |   prod_name   |
    +---------------+
    | .5 ton anvil  |
    | 1 ton anvil   |
    | 2 ton anvil   |
    +---------------+
    

    第十章:创建计算字段

    计算字段

    应用程序需要的数据需要通过从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。

    字段:基本上与列的意思相同,经常互换使用,不过数据库一般称为列,而属于字段通常用在计算字段的连接上。

    拼接字段

    拼接:将值联结到一起构成单个值

    在SELECT语句中,可使用Concat()函数来拼接两个列。Concat()函数需要一个或多个指定的串,各个串之间用逗号分隔。

    SELECT Concat(vend_name, ' (',vend_country,')') FROM vendors
    ORDER BY vend_name;
    #output
    +-----------------------------------------+
    | Concat(vendname,' (',vend_country,')') |
    +-----------------------------------------+
    | ACME (USA)                              |
    | Anvils R Us (USA)                       |
    | Furball Inc. (USA)                      |
    | Jet Set (England)                       |
    | Jouets Et Ours (France)                 |
    | LT Supplies (USA)                       |
    +-----------------------------------------+
    

    使用 RTrim()函数可以删除右侧多余的空格来整理数据,例:

    SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')')
    FROM vendors
    ORDER BY vend_name;
    
    函数说明
    Trim()去掉两边的空格
    LTrim()去掉左边的空格
    RTrim()去掉右边的空格

    使用别名

    拼接的结果只是一个值,未命名。可以用AS关键字赋予别名

    常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它,在原来的名字含混或容易误解时扩充它等等。
    别名有时也称为导出列(derived column)

    SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')') AS vend_title
    FROM vendors
    ORDER BY vend_name;
    #output
    +----------------------------+
    | vend_name                  |
    +----------------------------+
    | ACME (USA)                 |
    | Anvils R Us (USA)          |
    | Furball Inc. (USA)         |
    | Jet Set (England)          |
    | Jouets Et Ours (France)    |
    | LT Supplies (USA)          |
    +----------------------------+
    #指示SQL创建一个包含指定计算的名为vend_title的计算字段
    

    执行算术计算

    例:汇总物品的价格(单价乘以订购数量)

    SELECT prod_id,
    	   quantity,
           item_price,
           quantity * item_price AS expanded_price
    FROM orderitems
    WHERE order_num = 20005;
    #output
    +---------+----------+------------+----------------+
    | prod_id | quantity | item_price | expanded_price |
    +---------+----------+------------+----------------+
    | ANV01   |       10 |       5.99 |          59.90 |
    | ANV02   |        3 |       9.99 |          29.97 |
    | TNT2    |        5 |      10.00 |          50.00 |
    | FB      |        1 |      10.00 |          10.00 |
    +---------+----------+------------+----------------+
    
    操作符说明
    +
    -
    *
    /

    SELECT Now() 利用 Now()函数返回当前日期和时间

    第十一章:使用数据处理函数

    函数没有SQL的可移植性强

    使用函数

    大多数SQL实现支持以下类型的函数

    • 用于处理文本串的文本函数
    • 在数值数据上进行算术操作的数值函数
    • 处理日期和时间值并从这些值中提取特定成分的日期和时间函数
    • 返回DBMS正是用的特殊信息的系统函数

    文本处理函数

    常用的文本处理函数

    函数说明
    Left()返回串左边的字符
    Length()返回串的长度
    Locate()找出串的一个子串
    Lower()将串转换为小写
    LTrim()去掉串左边的空格
    Right()返回串右边的字符
    RTrim()去掉串右边的空格
    Soundex()返回串的SOUNDEX值
    SubString()返回子串的字符
    Upper()将串转换为大写

    SOUNDEX是一个将任何文本转换为描述其语音表示的字母数字模式的算法,使得能对串进行发音比较而不是字母比较。MySQL提供对SOUNDEX的支持。

    例:联系人Y.Lie输入错误为Y.Lee,使用SOUNDEX检索,匹配发音类似于Y.Lie的联系名

    SELECT cust_name, cust_contact FROM customers
    WHERE Soundex(cust_contact)= Soundex('Y Lie');
    #output
    +-------------+--------------+
    | cust_name   | cust_contact |
    +-------------+--------------+
    | Coyote Inc. | Y Lee        |
    +-------------+--------------+
    

    日期和时间处理函数

    函数说明
    AddDate()增加一个日期(天、周等)
    AddTime()增加一个时间(时、分等)
    CurDate()返回当前日期
    CurTime()返回当前时间
    Date()返回日期时间的日期部分
    DateDiff()计算两个日期之差
    Date_Add()高度灵活的日期计算函数
    Date_Format()返回一个格式化的日期或时间串
    Day()返回一个日期的天数部分
    DayOfWeek()对于一个日期,返回对应的星期几
    Hour()返回一个时间的小时部分
    Minute()返回一个时间的分钟部分
    Month()返回一个日期的月份部分
    Now()返回当前日期和时间
    Second()返回一个时间的秒部分
    Time()返回一个日期时间的时间部分
    Year()返回一个日期的年份部分
    MySQL使用的日期格式

    日期必须为格式yyyy-mm-dd
    支持2位数字的年份,MySQL处理00-69为2000-2069,70-99为1970-1999,但使用4为数字年份更可靠。
    例:

    SELECT cust_id, order_num FROM orders
    WHERE order_date = '2005-09-01';
    

    order_date类型为datetime,样例表中的值全部具有时间值00:00:00,但是如果order_date的值为2005-09-01 11:30:05则上面的WHERE order_date = '2005-09-11’不会检索出这一行,因此必须使用Date()函数。

    SELECT cust_id, order_num FROM orders
    WHERE Date(order_date) = '2005-09-01';
    

    例:检索出2005年9月下的所有订单

    SELECT cust_id, order_num FROM orders
    WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
    #BETWEEN把2005-09-01和2005-09-30定义为一个要匹配的日期范围。
    #另一种方法
    SELECT cust_id, order_num FROM orders
    WHERE Year(roder_date) = 2005 AND Month(order_date) = 9;
    

    数值处理函数

    函数说明
    Abs()返回一个数的绝对值
    Cos()返回一个角度的余弦
    Exp()返回一个数的指数值
    Mod()返回除操作的余数
    Pi()返回圆周率
    Rand()返回一个随机数
    Sin()返回一个角度的正弦
    Sqrt()返回一个数的平方根
    Tan()返回一个角度的正切

    第十二章:汇总数据

    函数说明
    AVG()返回某列的平均值
    COUNT()返回某列的行数
    MAX()返回某列的最大值
    MIN()返回某列的最小值
    SUM()返回某列值之和

    AVG()函数

    例:返回products表中所有产品的平均价格

    SELECT AVG(prod_price) AS avg_price FROM products;
    

    例:返回特定供应商所提供产品的平均价格

    SELECT AVG(prod_price) AS avg_price
    FROM products
    WHERE vend_id = 1003;
    

    COUNT()函数

    例:返回customer表中客户的总数

    SELECT COUNT(*) AS num_cust FROM customers;
    

    例:只对具有电子邮件地址的客户计数

    SELECT COUNT(cust_email) AS num_cust
    FROM customers;
    

    MAX()函数

    例:返回products表中最贵的物品价格

    SELECT MAX(prod_price) AS max_price
    FROM products;
    

    对非数值数据使用MAX()
    MySQL允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行。MAX()函数忽略列值为NULL的行。

    MIN()函数

    例:

    SELECT MIN(prod_price) AS min_price FROM products;
    

    SUM()函数

    返回指定列值的和(总计)
    例:检索所订购物品的总数

    SELECT SUM(quantity) AS items_ordered
    FROM orderitems
    WHERE order_num = 20005;
    

    例:合计计算值,合计每项物品item_price*quantity,得出订单总金额

    SELECT SUM(item_price*quantity) AS total_price
    FORM orderitems
    WHERE order_num = 20005;
    

    聚集不同值(适用于5.0.3后的版本)

    上述五个聚集函数都可以如下使用:

    • 对所有的行执行计算,指定ALL参数或不给参数(ALL为默认)
    • 只包含不同的值,指定DISTINCT参数

    例:

    SELECT AVG(DISTINCT prod_price) AS avg_price
    FROM products
    WHERE vend_id = 1003;
    

    注意:如果指定列名,则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*),因此不允许使用COUNT(DISTINCT), 否则会产生错误。类似地,DISTINCT必须使用列名,不能用于计算或表达式。

    组合聚集函数

    SELECT语句可根据需要包含多个聚集函数

    SELECT COUNT(*) AS num_items;
    	   MIN(prod_price) AS price_min,
           MAX(prod_price) AS price_max,
           AVG(prod_price) AS price_avg
    FROM products;
    #output
    +-----------+-----------+-----------+-----------+
    | num_items | price_min | price_max | price_avg |
    +-----------+-----------+-----------+-----------+
    |        14 |      2.50 |     55.50 | 16.133571 |
    +-----------+-----------+-----------+-----------+
    

    第十三章:分组数据

    创建分组

    例:根据vend_id分组,对每个分组分别计算总数

    SELECT vend_id, COUNT(*) AS num_prods
    FROM products
    GROUP BY vend_id;
    
    #output
    +---------+-----------+
    | vend_id | num_prods |
    +---------+-----------+
    |    1001 |         3 |
    |    1002 |         2 |
    |    1003 |         7 |
    |    1005 |         2 |
    +---------+-----------+
    
    • GROUP BY 子句可以包含任意数目的列,使得能对分组进行嵌套,为数据分组提供更细致的控制
    • 如果GROUP BY子句中中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
    • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
    • 聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出。
    • 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
    • GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。【详细信息,见下表】

    使用WITH ROLLUP关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值,如下所示:

    SELECT vend_id, COUNT(*) AS num_prods
    FROM products
    GROUP BY vend_id WITH ROLLUP;
    

    过滤分组

    WHERE指定的是行,不是分组,WHERE没有分组的概念

    使用HAVING过滤分组

    SELECT cust_id, COUNT(*) AS orders
    FROM orders
    GROUP BY cust_id
    HAVING COUNT(*) >= 2;
    #output
    +---------+--------+
    | cust_id | orders |
    +---------+--------+
    |   10001 |      2 |
    +---------+--------+
    

    WHERE不起作用,因为过滤是基于分组聚集值而不是特定行值的。

    例:列出具有2个(含)以上、价格为10(含)以上的产品的供应商

    SELECT vend_id, COUNT(*) AS num_prods
    FROM products
    WHERE prod_price >= 10
    GROUP BY vend_id
    HAVING COUNT(*) >=2
    #output
    +---------+-----------+
    | vend_id | num_prods |
    +---------+-----------+
    |    1003 |         4 |
    |    1005 |         2 |
    +---------+-----------+
    

    分组和排序

    例:检索总计订单价格大于等于50的订单的订单号和总计订单价格

    SELECT order_num, SUM(quantity*item_price) AS ordertotal
    FROM orderitems
    GROUP BY order_num
    HAVING SUM(quantity*item_price) >= 50
    ORDER BY ordertital;
    

    SELECT子句顺序

    SELECT子句及其顺序

    子句说明是否必须使用
    SELECT要返回的列或表达式
    WHERE从中检索数据的表仅在从表选择数据时使用
    GROUP BY分组说明尽在按组计算聚集是使用
    HAVING组级过滤
    ORDER BY输出排序顺序
    LIMIT要检索的行数

    上述子句使用时必须遵循该顺序

    这个是书写的时候需要注意的,但是实际数据库在运行的时候是这样的吗?

    • 先执行from关键字后面的语句,明确数据的来源,它是从哪张表取来的。

    • 接着执行where关键字后面的语句,对数据进行筛选。

    • 再接着执行group by后面的语句,对数据进行分组分类。

    • 然后执行select后面的语句,也就是对处理好的数据,具体要取哪一部分。

    • 最后执行order by后面的语句,对最终的结果进行排序。

    第十四章:使用子查询

    要求4.1以上版本

    例:列出订购物品TNT2的所有客户

    1. 检索包含物品TNT2的所有订单的编号
    2. 检索具有前一步骤列出的订单编号的所有客户的ID
    3. 检索前一步骤返回的所有客户ID的客户信息
    #(1)
    SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
    #output
    +-----------+
    | order_num |
    +-----------+
    |     20005 |
    |     20007 |
    +-----------+
    #(2)
    SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
    +-----------+
    |  cust_id  |
    +-----------+
    |     10001 |
    |     10004 |
    +-----------+
    
    #(1)+(2)
    SELECT cust_id
    FROM orders
    WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
    
    +-----------+
    | order_num |
    +-----------+
    |     20005 |
    |     20007 |
    +-----------+
    
    #(3)
    SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001, 10004)
    
    #(1)+(2)+(3)
    SELECT cust_name, cust_contact FROM customers
    WHERE cust_id IN(SELECT cust_id FROM orders
    				 WHERE order_name IN(SELECT order_num FROM orderitems
                     					 WHERE prod_id ='TNT2'));
    #output
    +----------------+--------------+
    | cust_name      | cust_contact |
    +----------------+--------------+
    | Coyote Inc.    | Y Lee        |
    | Yosemite Place | Y Sam        |
    +----------------+--------------+
    

    在WHERE子句中使用子查询应保证SELECT语句有与WHERE子句中相同数目的列。

    作为计算字段使用子查询

    需要显示customers表中每个客户的订单总数,订单与相应的客户ID存储在orders表中

    1. 从customers表中检索客户列表
    2. 对于检索出的每个客户,统计其在orders表中的订单数目
    # 对客户10001的订单进行计数
    SELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001;
    # 为了对每个客户执行COUNT(*)计算,应该将COUNT(*)作为一个子查询
    SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
                                  WHERE orders.cust_id = customers.cust_id) AS orders
    FROM customers ORDER BY cust_name;
    

    相关子查询:涉及外部查询的子查询
    在任何时候只要列明可能有多义性,就必须使用这种语法(表明和列名由一个句点分隔)

    第十五章:联结表

    连结

    关系表

    例如:两个表,一个存储供应商信息,另一个存储产品信息。vendors表包含所有供应商信息,每个供应商占一行,每个供应商应具有唯一的标识,称为主键(primary key)。products表只存储产品信息,除了存储供应商ID之外不存储其他的供应商信息。vendors表的主键又叫products的外键,它将vendors表与products表关联,利用供应商ID能从vendors表中找出相应供应商的详细信息。

    外键:外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。

    例:定义外键

    ALTER TABLE orderitems
    ADD CONSTRAINT fk_irderitems_orders
    FOREIGN KEY (order_num) REFERENCES orders(order_num);
    
    ALTER TABLE orderitems
    ADD CONSTRAINT fk_irderitems_products
    FOREIGN KEY (prod_id) REFERENCES products(prod_id);
    
    ALTER TABLE orders
    ADD CONSTRAINT fk_irderitems_customers
    FOREIGN KEY (cust_id) REFERENCES customers(cust_id);
    
    ALTER TABLE products
    ADD CONSTRAINT fk_irderitems_vendors
    FOREIGN KEY (vend_id) REFERENCES vendors(vend_id);
    

    在使用关系表时,仅在关系列中插入合法的数据非常重要,如果在products表中插入拥有非法供应商ID(即没有在vendors表中出现)的供应商生产的产品,则这些产品是不可访问的,因为它们没有关联到某个供应商。

    创建连结

    我们把tableA看作左表,把tableB看成右表,

    那么INNER JOIN是选出两张表都存在的记录:

    inner-join

    LEFT OUTER JOIN是选出左表存在的记录:

    left-outer-join

    RIGHT OUTER JOIN是选出右表存在的记录:

    right-outer-join

    FULL OUTER JOIN则是选出左右表都存在的记录:

    full-outer-join

    SELECT vend_name, prod_name, prod_price FROM vendors, products
    WHERE vendors.vend_id = products.vend_id
    ORDER BY vend_name, prod_name;
    

    两个表用WHERE子句联结

    笛卡儿积:由没有连结条件的表关系返回的结果为笛卡尔积,检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

    内部连结

    等值联结:基于两个表之间的相等测试,也叫内部连结

    可以使用另一种语法来明确指定联结的类型

    SELECT vend_name, prod_name, prod_price
    FROM vendors INNER JOIN products
    ON vendors.vend_id = products.vend_id;
    

    FROM 子句的组成部分,以INNER JOIN指定,联结条件用ON子句

    联结多个表

    SELECT prod_name, vend_name, prod_price, quantity
    FROM orderitems, products, vendors
    WHERE products.vend_id = vendors.vend_id
    AND orderitems.prod_id = products.prod_id
    AND order_num = 20005;
    #显示编号为20005的订单中的物品。订单物品存储在orderitems表中,按每个产品的ID存储。
    它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商
    

    例:返回订购产品INT2的客户列表

    SELECT cust_name,cust_contact
    FROM customers, orders, orderitems
    WHERE customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num
    AND prod_id = 'TNT2';
    

    第十六章:创建高级联结

    使用表别名

    SELECT Concat(RTrim(vend_name),'('Rtrim(vend_country),')') AS vend_title
    FROM vendors ORDER BY vend_name;
    

    别名除了用于列名和计算字段外,SQL还允许给表起别名,这样做有两个主要理由:

    • 缩短SQL语句
    • 允许在单条SELECT语句中多次使用相同的表。

    例:

    SELECT cust_name, cust_contact
    FROM customers AS c, orders AS o, orderitems AS oi
    WHERE c.cust_id = o.cust_id
    AND oi.order_num = o.order_num
    AND prod_id = 'TNT2';
    

    使用不同类型的联结

    自联结

    例:如果某物品(ID为DTNTR)存在问题,因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到ID为DTNTR的物品的供应商,然后找出这个供应商生产的其他物品。

    #子查询
    SELECT prod_id, prod_name
    FROM products
    WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
    #output
    +---------+----------------+
    | prod_id | prod_name      |
    +---------+----------------+
    | DTNTR   | Detonator      |
    | FB      | Bird seed      |
    | FC      | Carrots        |
    | SAFE    | Safe           |
    | SLING   | Sling          |
    | TNT1    | TNT (1 stick)  |
    | TNT2    | TNT (5 sticks) |
    +---------+----------------+
    #使用自联结
    SELECT p1.prod_id, p1.prod_name
    FROM products AS p1, products AS p2
    WHERE p1.vend_id = p2.vend_id
    AND p2.prod_id = 'DTNTR';
    

    自然联结

    无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结(内部联结)返回所有数据,甚至相同的列多次出现,自然联结排除多次出现,使每个列只返回一次。

    自然联结是这样一种联结,其中你只能选择那些唯一的列,这一版是通过使用通配符,对所有其他表的列使用明确的字集来完成的。

    SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
    FROM customers AS c, orders AS o, orderitems AS oi
    WHERE c.cust_id = o.cust_id
    AND oi.order_num = o.order_num
    AND prod_id = 'FB';
    

    这个例子中,通配符只对第一个表使用所有其他列明确列出,所以没有重复的列被检索出来。

    外部联结

    有时候需要包含没有关联的那些行

    例:需要联结来完成以下工作

    • 对每个客户下了多少订单进行计数,包括那些至今尚未下订单的客户
    • 列出所有产品及订购数量,包括没人订购的产品
    • 计算平均销售规模,包括那些至今尚未下订单的客户
    #内部联结
    SELECT customers.cust_id, orders.order_num
    FROM customers INNER JOIN orders
    ON customers.cust_id = orders.cust_id;
    
    #外部联结
    SELECT customers.cust_id, orders.order_num
    FROM customers LEFT OUTER JOIN orders
    ON customers.cust_id = orders.cust_id;
    

    外部联结语法类似。可以检索所有客户,包括没有订单的客户。

    在使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表。(RIGHT指出的是OUTER JOIN右边的表,而LEFT指出的是OUTER JOIN左边的表。)上面的例子中从customers中选择所有的行。

    SELECT customers.cust_id, orders.order_num
    FROM customers RIGHT OUTER JOIN orders
    ON orders.cust_id = customers.cust_id;
    

    使用带聚集函数的联结

    例:检索所有客户及每个客户所下的订单数

    SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
    FROM customers INNER JOIN orders
    ON customers.cust_id = orders.cust_id
    GROUP BY customers.cust_id;
    

    此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据,因此,函数调用COUNT(orders.order_num)对每个客户的订单计数,将它作为num_ord返回。

    聚集函数也可以方便地与其他联结一起使用。例:

    SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
    FROM customers LEFT OUTER JOIN orders
    ON customers.cust_id = orders.cust_id
    GROUP BY customers.cust_id;
    

    这个例子使用左外部联结来包含所有客户,甚至包含那些没有任何下订单的客户。

    使用联结和联结条件

    • 注意所使用的联结类型,一般我们使用内部联结,但使用外部联结也是有效的。
    • 保证使用正确的联结条件,否则将返回不正确的数据。
    • 应该总是提供联结条件,否则会得出笛卡尔积。
    • 在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型。应该在一起测试他们前分别测试每个联结。

    第十七章:组合查询

    组合查询

    MySQL允许执行多个查询并将结果作为单个查询结果返回。
    两种情况:

    • 在单个查询中从不同的表返回类似结构的数据
    • 对单个表执行多个查询,按单个查询返回数据

    创建组合查询

    使用UNION

    给出每条SELECT语句,在各条语句之间放上关键字UNION
    例:需要价格小于等于5的所有物品的一个列表,并且包含供应商1001和1002生产的所有物品

    #单条语句
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE prod_price <= 5;
    
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE vend_id IN (1001,1002);
    
    #组合上述语句
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE prod_price <= 5
    UNION
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE vend_id IN (1001, 1002);
    
    #等于
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE prod_price <= 5
    OR vend_id IN (1001, 1002);
    

    UNION规则

    • UNION 必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔
    • UNION中的每个查询必须包含先沟通的列、表达式或聚集函数
    • 列数据类型必须兼容:类型不必完全向东,但必须是DBMS可以隐含地转换的类型

    包含或取消重复的行

    UNION从查询结果集中自动去除了重复的行,如果需要返回所有行,可以使用UNION ALL

    对组合查询结果排序

    使用ORDER BY子句排序,只能使用一条ORDER BY子句,必须在最后一条SELECT语句之后。

    第十八章:全文本搜索

    并非所有引擎都支持全文本搜索

    mysql 5.6.4之前只有 Myisam支持,5.6.4之后则Myisaminnodb都支持,不过mysql中的全文索引目前只支持英文(不支持中文)

    其实根本原因是因为英文检索是用空格来对分词进行分隔,而中文肯定不能用空格来分隔,只能通过语义进行分词,用空格的话是肯定检索不出某些词汇的。

    良心的是,在Mysql 5.7版本时,MySQL内置了ngram全文检索插件,用来支持中文分词,但是仅对MyISAMInnoDB引擎有效。

    MyISAM支持,InnoDB不支持

    LIKE、正则表达式的限制

    • 性能:由于被搜索行不断增加,这些搜索可能非常耗时
    • 明确控制:很难明确控制匹配什么和不匹配什么
    • 智能化的结果:不能提供一种智能化的选择结果的方法,例如:一个特殊词的搜索将会返回包含该词的所有行而不区分包含单个匹配的行和多个匹配的行。

    使用全文本搜索

    为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引。
    在索引之后,SELECT可与Match()Against()一起使用以实际执行搜索。

    启用全文本搜索支持

    一般在创建表时启用全文本搜索
    CREATE TABLE语句接收FULLTEXT子句,它给出被索引列的一个逗号分隔的列表
    例:

    CREATE TABLE productnotes
    (
    	note_id int NOT NULL AUTO_INCREMENT,
        pord_id char(10) NOT NULL,
        note_date datetime NOT NULL.
        note_text text NULL,
        PRIMARY KEY(note_id),
        FULLTEXT(note_text)
    )ENGINE=MyISAM;
    

    可以在创建表时指定FULLTEXT或在稍后指定(所有数据必须立即索引)
    不要在导入数据时使用FULLTEXT 应线导入所有数据再修改表,定义FULLTEXT

    进行全文本搜索

    Match() 指定被搜索的列
    Against() 指定要使用的搜索表达式

    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('rabbit');
    

    传递个Match()的值必须与FULLTEXT()定义中的相同。

    除非使用BINARY方式,否则全文本搜索不区分大小写

    全文本搜索对结果排序,具有较高等级的行先返回

    SELECT note_text,
    Match(note_text) Against('rabbit') AS rank
    FROM productnotes;
    

    显示所有行及他们的等级

    使用扩展查询

    (MySQL 4.1.1及更高版本)
    例如找出所有提到anvils的注释,和与搜索有关的其他行,即使它们不包含这个词

    #不使用扩展查询
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('anvils');
    +------------------------------------------------------------------------------+
    | note_text                                                                    |
    +------------------------------------------------------------------------------+
    | Multiple custoer returns, anvils failing to drop fast enough or falling      |
    | backwords on purchaser, Recomend that customer considers using havier        |
    | anvils.                                                                      |
    +------------------------------------------------------------------------------+
    #使用扩展查询
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
    +------------------------------------------------------------------------------+
    | note_text                                                                    |
    +------------------------------------------------------------------------------+
    | Multiple custoer returns, anvils failing to drop fast enough or falling      |
    | backwords on purchaser, Recomend that customer considers using havier        |
    | anvils.                                                                      |
    | Customer complaint: Sticks not individually wrapped, too easy to mistakenly  |
    | detonate all at once. Recommend individual wrapping.                         |
    | Customer compliant: Not heavy enouth to generate flying stars around heardof |
    | victim. If veing purchased for dropping, recommend ANV02 or ANV03 instead.   |
    | Please note that no returns will be accepted if safe opened using explosives.|
    | Customer complaint: rabbit has been able to detect trap, food apparently     |
    | less effective now.                                                          |
    | Customer complaint: Circular hole in safe floor can apparently be easily cut |
    | with handsaw.                                                                |
    | Matches not include, recomend purchase of matches or detonator (item DTNTR)  |
    +------------------------------------------------------------------------------+
    

    返回7行,第一行包含anvils,因此等级最高,第二行与anvils无关,但包含第一行中的两个次,因此被检索出来。

    布尔文本搜索

    可以提供如下内容的细节:

    • 要匹配的词
    • 要排斥的次
    • 排列提示
    • 表达式分组
    • 另外一些内容
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
    

    例:匹配包含heavy但不包含任意以rope开始的词的行

    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
    

    *MySQL4.x版本中使用-ropes而不是-rope **

    全文本布尔操作符

    布尔操作符说明
    +包含,词必须存在
    -排除,词必须不出现
    >包含,而且增加等级值
    <包含,且减少等级
    ()把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
    ~取消一个词的排序值
    *取消一个词的排序值
    “”定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)

    例:

    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
    #匹配包含词rabbit和bait的行。
    
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
    #匹配包含rabbit和bait中的至少一个词的行
    
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
    #匹配rabbit bait而不是匹配两个词
    
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE);
    #匹配rabbit和carrot,增加前者的等级,降低后者的等级
    
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
    #匹配词safe和combination,降低后者的等级
    
    

    全文本搜索的使用说明

    • 在索引全文本数据时,短词被忽略且从索引中排除。短词定义为 那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
    • MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略。如果需要,可以覆盖这个列表(请参阅MySQL文档以了解如何完成此工作)。
    • 许多词出现的频率很高,搜索它们没有用处(返回太多的结果)。 因此,MySQL规定了一条50%规则,如果一个词出现在50%以上 的行中,则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
    • 如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词 或者不出现,或者至少出现在50%的行中)。
    • 忽略词中的单引号。例如,don’t索引为dont。
    • 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文 本搜索结果。
    • 如前所述,仅在MyISAM数据库引擎中支持全文本搜索。

    第十九章:插入数据

    INSERT

    • 插入完整的行
    • 插入行的一部分
    • 插入多行
    • 插入某些查询的结果

    插入完整的行

    INSERT INTO Customers
    VALUES(NULL,
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angles',
        'CA',
        '90046',
        'USA',
        NULL,
        NULL);
    

    语法简单但不安全。更安全的方法为:

    INSERT INTO customers(cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country,
        cust_contact,
        cust_email)
    VALUES('Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046'
        'USA'
        NULL,
        NULL);
    #下面的INSERT语句填充所有列(与前面的一样),但以一种不同的次序填充。
    #因为给出了列名,所以插入结果仍然正确:
    INSERT INTO customers(cust_name,
        cust_contact,
        cust_email,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country)
    VALUES('Pep E. LaPew',
        NULL,
        NULL,
        '100 Main Street',
        'Los Angles',
        'CA',
        '90046',
        'USA');
    

    不管哪种INSSERT语法,都必须给出VALUES的正确数目,如果不提供列名,则必须给每个表提供一个值。

    如果提供列名,则必须对每个列出的列值给出一个值。

    列名被明确列出时,可以省略列,如果表的定义允许则可以省略列

    • 该列定义为允许NULL值(无值或空值)
    • 在表定义中给出默认值。

    插入多个行

    INSERT INTO customers(cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country)
    VALUES('Pep E. LaPew',
        '100 Main Street'
        'Los Angeles',
        'CA',
        '90046',
        'USA');
    INSERT INTO customers(cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country)
    VALUES('M. Martian',
        '42 Galaxy Way'
        'New York',
        'NY',
        '11213',
        'USA');
    
    #使用组合句
    INSERT INTO customers(cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country)
    VALUES('Pep E. LaPew',
        '100 Main Street'
        'Los Angeles',
        'CA',
        '90046',
        'USA')('M. Martian',
        '42 Galaxy Way'
        'New York',
        'NY',
        '11213',
        'USA');
    
    #单条INSERT语句有多组值,每组值用一对圆括号括起来,用逗号分隔。
    

    插入检索出的数据

    INSERT INTO customers(cust_id,
        cust_contact,
        cust_email,
        cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country)
    SELECT cust_id,
        cust_contact,
        cust_email,
        cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country
    FROM custnew;
    

    第二十章:更新和删除数据

    更新数据

    UPDATE

    • 更新表中特定行
    • 更新表中所有行
      例:客户10005更新电子邮件
    UPDATE customers
    SET cust_email = 'elmer@fudd.com'
    WHERE cust_id = 10005;
    

    例:更新多个列

    UPDARTE customers
    SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
    WHERE cust_id = 10005;
    

    在更新多个列时,只需要使用单个SET命令,每个“列=值”对之间 用逗号分隔(最后一列之后不用逗号)。在此例子中,更新客户10005的cust_name和cust_email列。

    IGNORE关键字:如果用UPDATE语句更新多行,并且在更新这些 行中的一行或多行时出一个现错误,则整个UPDATE操作被取消 (错误发生前更新的所有行被恢复到它们原来的值)。为即使是发生错误,也继续进行更新,可使用IGNORE关键字,如下所示:UPDATE IGNORE customers…

    为了删除某列的值,可以设置为NULL

    UPDATE customers
    SET cust_email = NULL
    WHERE cust_id = 10005;
    

    删除数据

    使用DELETE语句

    • 从表中删除特定的行
    • 从表中删除所有的行
    DELETE FROM customers
    WHERE cust_id = 10006;
    

    更新和删除的指导原则

    下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯。

    • 除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
    • 保证每个表都有主键,尽可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。
    • 在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE子句不正确。
    • 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。

    第二十一章:创建和操纵表

    创建表

    创建表基础

    CREATE TABLE

    • 新表的名字,在关键字CREATE TABLE之后给出
    • 表列的名字和定义,用逗号分隔。
      例:
    CREATE TABLE customers
    (
        cust_id int NOT NULL AUTO_INCREMENT,
        cust_name char(50) NOT NULL,
        cust_address char(50) NULL,
        cust_city char(50) NULL,
        cust_state char(5) NULL,
        cust_zip char(10) NULL,
        cust_country char(50) NULL,
        cust_contact char(50) NULL,
        cust_email char(255) NULL,
        PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;
    

    在创建新表时,指定的表名必须不存在,否则将出错。如果要防止意外覆盖已有的表,SQL要求首先手工删除该表,然后再重建它,而不是简单地用创建表语句覆盖它。

    如果你仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在,并且仅在表名不存在时创建它。

    CREATE TABLE IF NOT EXISTS customers
    (
        cust_id int NOT NULL AUTO_INCREMENT,
        cust_name char(50) NOT NULL,
        cust_address char(50) NULL,
        cust_city char(50) NULL,
        cust_state char(5) NULL,
        cust_zip char(10) NULL,
        cust_country char(50) NULL,
        cust_contact char(50) NULL,
        cust_email char(255) NULL,
        PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;
    

    使用NULL值

    每个表列或者是NULL列或者是NOT NULL列,这种状态在创建时由表的定义规定
    例:

    CREATE TABLE orders
    (
        order_num int NOT NULL AUTO_INCREMENT,
        order_date datetime NOT NULL,
        cust_id int NOT NULL,
        PRIMARY KEY (order_num)
    ) ENGINE-InnoDB;
    

    例:混合了NULL和NOT NULL列的表

    CREATE TABLE vendors
    (
        vend_id int NOT NULL AUTO_INCREMENT,
        vend_name char(50) NOT NULL,
        vend_address char(50) NULL,
        vend_city char(50) NULL,
        vend_state char(5) NULL,
        vend_zip char(10) NULL,
        vend_country char(50) NULL,
        PRIMARY KEY(vend_id)
    ) ENGINE = InnoDB;
    

    主键

    主键值必须唯一。可以由一个或者多个

    • 如果主键使用单个列,则它的值必须唯一。
    • 如果使用多个列,则这些列的组合值必须唯一。
    • PRIMARY KEY (order_num, order_item)

    例:创建多个列组成的主键

    CREATE TABLE orderitems
    (
        order_num int NOT NULL,
        order_item int NOT NULL,
        prod_id char(10) NOT NULL,
        quantity int NOT NULL,
        item_price decimal(8,2) NOT NULL,
        PRIMARY KEY (order_num, order_item)
    )ENGiNE = InnoDB;
    

    使用AUTO_INCREMENT

    AUTO_INCREMENT告诉MySQL,本列每当增加一行时自动增量。每次 执行一个INSERT操作时,MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT),给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id,从而可以用作主键值。

    覆盖AUTO_INCREMENT:如果一个列被指定为AUTO_INCREMENT,则它需要使用特殊的值吗?你可以简单地INSERT语句中指定一个值,只要它是唯一的(至今尚未使用过)即可,该值将被用来替代自动生成的值。

    后续的增量将开始使用该手工插入的值。

    指定默认值

    CREATE TABLE orderitems
    (
        order_num int NOT NUL,
        order_item int NOT NULL,
        prod_id char(10) NOT NULL,
        quantity int NOT NULL DEFAULT 1,
        item_price decimal(8,2) NOT NULL,
        PRIMARY KEY (order_num,order_item)
    ) ENGINE = InnoDB;
    

    MySQL不允许使用函数作为默认值,只支持常量

    引擎类型

    • InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;【后面版本已经支持了~】

    • MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘) 中,速度很快(特别适合于临时表);

    • MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。

    • 引擎类型可以混用。

      外键不能跨引擎 混用引擎类型有一个大缺陷。

      外键(用于强制实施引用完整性)不能跨引擎,

      即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

    更新表

    使用ALTER TABLE更改表的结构,必须给出以下信息:

    • ALTER TABLE之后给出要更改的表名(该表必须存在,否则将出错);
    • 所做更改的列表。

    例:

    ALTER TABLE vendors
    ADD vend_phone CHAR(20);
    

    例:删除刚增加的列

    ALTER TABLE vendors
    DROP COLUMN vend_phone;
    

    为了对单个表进行多个更改,可以使用单条ALTER TABLE语句,每个更改用逗号分隔

    复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:

    • 用新的列布局创建一个新表;
    • 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要,可使用转换函数和计算字段;
    • 检验包含所需数据的新表;
    • 重命名旧表(如果确定,可以删除它);
    • 用旧表原来的名字重命名新表;
    • 根据需要,重新创建触发器、存储过程、索引和外键。

    使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据。

    删除表

    DROP TABLE customers2;
    

    重命名表

    RENAME TABLE customers2 TO customers;
    
    #对多个表重命名
    RENAME TABLE backup_customers TO customers,
    	         backup_vendors TO vendors,
                 backup_products TO products;
    

    补充

    MySQL的注释方法
    一共有三种,分别为

    #单行注释可以使用"#"
    -- 单行注释也可以使用"--",注意与注释之间有空格
    /*
    用于多行注释
    */
    

    第二十二章:使用视图(适用于MySQL 5及之后的版本)

    视图

    例:

    SELECT cust_name, cust_contact FROM customers, orders, orderitems
    WHERE customers.cust_id = orders.cust_id
    AND orderitems,order_num = orders.order_num
    AND prod_id = 'TNT2';
    #此查询用来检索订购了某个特定产品的客户。假如把整个查询包装成一个名为productcusotmers的虚拟表,则
    SELECT cust_name, cust_contact FROM productcustomers
    WHERE prod_id = 'TNT2';
    

    productcustomers是一个视图。

    为什么使用视图

    • 重用SQL语句。
    • 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
    • 使用表的组成部分而不是整个表。
    • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
    • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

    视图的规则和限制

    • 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
    • 对于可以创建的视图数目没有限制。
    • 为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
    • 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
    • ORDER BY可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖。
    • 视图不能索引,也不能有关联的触发器或默认值。
    • 视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。

    使用视图

    • 视图用CREATE VIEW语句来创建。
    • 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
    • DROP删除视图,其语法为DROP VIEW viewname;
    • 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。

    利用视图简化复杂的联结

    CREATE VIEW productcustomers AS
    SELECT cust_name, cust_contact, prod_id
    FROM customers, orders, orderietms
    WHERE customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num;
    

    创建可重用的视图:创建不受特定数据限制的视图是一种好办法。

    例如,上面创建的视图返回生产所有产品的客户而不仅仅是生产TNT2的客户。扩展视图的范围不仅使得它能被重用,而且甚至更有用。这样做不需要创建和维护多个类似视图。

    用视图重新格式化检索出的数据

    SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    FROM vendors
    ORDER BY vend_name;
    

    现在,假如经常需要这个格式的结果。不必在每次需要时执行联结,创建一个视图,每次需要时使用它即可。为把此语句转换为视图,可按如下进行:

    CREATE VIEW vendorlocations AS
    SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    FROM vendors
    ORDER BY vend_name;
    

    这条语句使用与以前的SELECT语句相同的查询创建视图。为了检索出以创建所有邮件标签的数据,可如下进行:

    SELECT *
    FROM vendorlocations;
    

    用视图过滤不想要的数据

    例:排除没有电子邮件地址的用户

    CREATE VIEW customeremaillist AS
    SELECT cust_id, cust_name, cust_email
    FROM customers
    WHERE cust_email IS NOT NULL;
    
    SELECT * FROM customeremaillist;
    

    使用视图与计算字段

    SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
    FROM orderitems
    WHERE order_num = 20005;
    #将其转换为视图
    CREATE VIEW orderitemsexpanded AS
    SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
    FROM orderitems;
    
    SELECT *
    FROM orderitemsexpanded
    WHERE order_num = 20005;
    

    更新视图

    视图的数据能否更新视情况而定。

    通常,视图是可更新的(即,可以对它们使用INSERT、UPDATE和DELETE)。

    更新一个视图将更新其基表(可以回忆一下,视图本身没有数据)。如果你对视图增加或删除行,实际上是对其基表增加或删除行。

    但是,并非所有视图都是可更新的。基本上可以说,如果MySQL不能正确地确定被更新的基数据,则不允许更新(包括插入和删除)。

    这实际上意味着,如果视图定义中有以下操作,则不能进行视图的更新:

    • 分组(使用GROUP BYHAVING);
    • 联结;
    • 子查询;
    • 并;
    • 聚集函数(Min()、Count()、Sum()等);
    • DISTINCT;
    • 导出(计算)列。

    第二十三章:存储过程(适用于MySQL5及之后的版本)

    什么是存储过程?

    我的理解,存储过程就是将一条或多条MySQL语句进行封装成一个函数

    为什么使用存储过程?

    • 通过把处理封装在容易使用的单元中,简化复杂的操作(正如前面例子所述)。
    • 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程,则所使用的代码都是相同的。 这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能 性就越大。防止错误保证了数据的一致性。
    • 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
      这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。
    • 提高性能。因为使用存储过程比使用单独的SQL语句要快。
    • 存在一些只能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码(在下一章的例子中可以看到。)
      换句话说,使用存储过程有3个主要的好处,即简单、安全、高性能。显然,它们都很重要。不过,在将SQL代码转换为存储过程前,也必须知道它的一些缺陷。

    怎么使用存储过程?

    创建存储过程

    例:返回产品平均价格的存储过程

    CREATE PROCEDURE priductpricing()
    BEGIN
    	SELECT Avg(prod_price) AS priceaverage
        FROM priducts;
    END;
    

    使用存储过程:

    CALL priductpricing();
    


    • mysql命令行客户机的分隔符【可以改,但没必要】

    默认的MySQL语句分隔符为’;’。mysql命令行实用程序也使用’;'作为语句分隔符。

    如果命令行实用程序要解释存储过程自身内的’;'字符,则它们最终不会成为存储过程的成分,这会使存储过程中的SQL出现句法错误。
    解决办法是临时更改命令行实用程序的语句分隔符,如下所示:

    DELIMTER//
    
    CREATE PROCEDURE productpricing()
    BEGIN
    	SELECT Avg(prod_price) AS priceaverage
        FROM products;
    END//
    
    DELIMITER ;
    
    
    -- 其中,DELIMITER//告诉命令行实用程序使用//作为新的语句结束分隔符,可以看到标志存储过程结束的END定义为END//而不是END;。
    -- 这样,存储过程体内的;仍然保持不动,并且正确地传递给数据库引擎。
    -- 最后,为恢复为原来的语句分隔符,可使用DELIMITER ;。
    -- 除\符号外,任何字符都可以用作语句分隔符。
    


    删除存储过程

    DROP PROCEDURE productpricing;
    

    仅当存在时删除使用DROP PROCEDURE IF EXISTS

    使用参数

    CREATE PROCEDURE productpricing(
    	OUT pl DECIMAL(8,2),
        OUT ph DECIMAL(8,2),
        OUT pa DECIMAL(8,2)
    )
    BEGIN
    	SELECT Min(prid_price)
        INTO pl
        FROM products;
        SELECT Max(prod_price)
        INTO ph
        FROM products;
        SELECT AVG(prod_price)
        INTO pa
        FROM prodcts;
    END;
    

    此存储过程接受3个参数:

    pl存储产品最低价格,

    ph存储产品最高价格,

    pa存储产品平均价格。

    DECIMAL(P,D);

    • P是表示有效数字数的精度。 P范围为1〜65
    • D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P

    每个参数必须具有指定的类型,这里使用十进制值。

    关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。

    MySQL支持IN(传递给存储过程)、OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数。

    存储过程的代码位于BEGIN和END语句内,如前所见,它们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字)。

    为调用此修改过的存储过程,必须指定3个变量名,如下所示:

    CALL productpricing(@pricelow,
    					@pricehigh,
                        @priceaverage);
    #为了显示检索出的产品价格,可如下进行
    SELECT @priceaverage#为了获得3个值,可以使用如下语句
    SELECT @pricehigh, @pricelow, @priceaverage;
    

    例:使用IN和OUT参数,ordertotal接收订单号并返回该订单的合计。

    CREATE PROCEDURE ordertotal(
    	IN onumber INT,
        OUT ototal DECIMAL(8,2)
    )
    BEGIN
    	SELECT Sum(item_price*quantity)
        FROM orderitems
        WHERE order_num = onumber
        INTO ototal;
    END;
    
    #调用:必须传递两个参数,1为订单号,2为包含计算出来的合计的变量名
    CALL ordertotal(20005, @total);
    
    #显示合计
    SELECT @total;
    
    • 建立只能存储过程

    例:获得与以前一样的订单合计,但需要对合计增加营业税,不过只针对某些顾客

    • 获得合计
    • 把营业税有条件地添加到合计
    • 返回合计
    -- Name: ordertotal
    -- Parameters: onumber = order number
    --             taxable = 0 if not taxable, 1 if taxable
    --             ototal  = order total variable
    
    CREATE PROCEDURE ordertotal(
    	IN onumber TNT,
        IN taxable BOOLEAN,
        OUT ototal DECIMAL(8,2)
    )COMMENT 'Obtain order total, optionally adding tax'
    BEGIN
        -- DECLARE variable for total
        DECLARE total DECIMAL (8,2);
        -- DECLARE tax percentage
        DECLARE taxrate INT DEFAULT 6;
    
        -- Get the order total
        SELECT Sum(item_price*quantity)
        FROM orderitems
        WHERE order_num = onumber
        INTO total;
    
        -- Is this taxable?
        IF taxable THEN
        	-- Yes, so add taxrate to the total
            SELECT total +(total/100*taxrate) INTO total;
        END IF;
    
        -- And finally, save to our variable
        SELECT total INTO ototal;
    END;
    
    -- 试验
    CALL ordertotal(20005, 0, @total);
    SELECT @total;
    
    CALL ordertotal(20005, 1, @total);
    SELECT @total;
    
    

    此存储过程有很大的变动。

    首先,增加了注释(前面放置–)。在存储过程复杂性增加时,这样做特别重要。添加了另外一个参数taxable,它是一个布尔值(如果要增加税则为真,否则为假)。

    在存储过程体中,用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型,它也支持可选的默认值(这个例子中的taxrate的默 认被设置为6%)。

    SELECT语句已经改变,因此其结果存储到total(局部变量)而不是ototal。IF语句检查taxable是否为真,如果为真,则用另一SELECT语句增加营业税到局部变量total。最后,用另一SELECT语句将total(它增加或许不增加营业税)保存到ototal。

    本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的,但如果给出,将在SHOW PROCEDURE STATUS的结果中显示

    • 检查存储过程

    显示用来创建一个存储过程的CREATE语句,使用

    SHOW CREATE PROCEDURE ordertotal;
    

    为获得包括何时、由谁创建等详细信息的存储过程列表,使用SHOW PRODUCEDURE STATUS
    SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出,可使用LIKE指定一个过滤模式:

    SHOW PROCEDURE STATUS LIKE 'ordertotal';
    

    第二十四章:使用游标(适用于MySQL5及以上版本)

    有时,需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。

    游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。

    在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。

    MySQL游标只能用于存储过程(和函数)

    使用游标

    使用游标涉及几个明确的步骤。

    • 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句。
    • 一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
    • 对于填有数据的游标,根据需要取出(检索)各行。
    • 在结束游标使用时,必须关闭游标。

    创建游标

    CREATE PROCEDURE processorders()
    BEGIN
    	DECLARE ordernumbers CURSOR
        FOR
        SELECT order_num FROM orders;
    END;
    

    这个存储过程并没有做很多事情,DECLARE语句用来定义和命名游标,这里为ordernumbers。存储过程处理完成后,游标就消失(因为它局限于存储过程)。

    打开和关闭游标

    --打开游标
    OPEN ordernumbers;
    --处理完成后,应当使用下句关闭游标
    CLOSE ordernumbers;
    

    CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭
    MySQL会在达到END语句时自动关闭它

    CREATE PROCEDURE processorders()
    BEGIN
    	-- Declare the cursor
        DECLARE ordernumbers CURSOR
        FOR
        SELCET order_num FROM orders;
    
        -- Open the cursor
        OPEN ordernumbers;
    
        --Close the cursor
        CLOSE ordernumbers;
    
    END;
    

    这个存储过程声明、打开和关闭一个游标,但对检索出的数据什么也没做。

    使用游标数据

    例:从游标中检索单个行

    CREATE PROCEDURE processorders()
    BEGIN
    	-- Declare local variables
        DECLARE o INT;
    
        -- Declare the cursor
        DECLARE orderumbers CURSOR
        FRO
        SELECT order_num FROM orders;
    
        -- Open the cursor
        OPEN ordernumbers;
    
    	-- Get order number
        FETCH ordernumbers INTO o;
    
    	-- Close the cursor
        CLOSE ordernumbers;
    
    END;
    

    其中FETCH用来检索当前行的order_num列(将自动从第一行开始)到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

    例:循环检索数据,从第一行到最后一行

    CREATE PROCEDURE processorders()
    BEGIN
    	--Declare local variables
        DECLARE done BOOLEAN DEFAULT 0;
        DECLARE o INT;
    
        --Declare the cursor
        DECLARE ordernumbers CURSOR
        FOR
        SELECT order_num FROM orders;
    
    	--Declare continue handler
        DECLARE CONTINUE HANDLER FRO SQLSTATE '02000' SET done=1;
    
    	--Open the cursor
        OPEN ordernumbers;
    
    	--Loop through all rows
        REPEAT
        	--Get order numbers
            FETCH ordernumbers INTO o;
        --End of loop
        UNTIL done END REPEAT;
        --CLose the cursor
        CLOSE ordernumbers;
    END;
    

    这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是,这个例子中的FETCH是在REPEAT内,因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)。为使它起作用,用一个DEFAULT 0(假,不结束)定义变量done。

    使用以下语句将done在结束时设置为真:
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1

    这条语句定义了一个CONTINUE HANDLER,它是在条件出现时被执行的代码。这里,它指出当SQLSTATE '02000’出现时,SET done=1。SQLSTATE '02000’是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件。

    例:

    CREATE PROCEDURE processorders()
    BEGIN
    	--Declare local variables
        DECLARE done BOOLEAN DEFAULT 0;
        DECLARE o INT;
        DECLARE t DECIMAL(8,2);
        --Declare the cursor
        DECLARE ordernumbers CURSOR
        FOR SELECT order_num FROM orders;
        --Declare continue handler
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
        --Create a table to store the results
        CREATE TABLE IF NOT EXISTS ordertotals
        	(order_num INT, total DECIMAL(8,2));
        --Open the cursor
        OPEN ordernumbers;
        --Loop through all rows
        REPEAT
        	--Get order number
            FETCH ordernumbers INTO o;
            --Get the total for this order
            CALL ordertoal(o, 1, t);
            --Insert order and total into ordertotals
            INSERT INTO ordertotals(order_num, total)
            VALUES(o,t);
        --End of loop
        UNTIL done END REPEAT;
        --Close the cursor
        CLOSE ordernumbers;
    END;
    

    在这个例子中,增加了另一个名为t的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话),名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num,然后用CALL执行另一个存储过程来计算每个订单的带税的合计(结果存储到t)。最后,用INSERT保存每个订单的订单号和合计。
    次存储过程不返回数据
    可以用SELECT * FROM ordertotals;查看该表

    第二十五章:使用触发器

    想要某条语句(或某些语句)在事件发生时自动执行
    触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句

    • DELETE
    • INSERT
    • UPDATE
      其他MySQL语句不支持触发器

    创建触发器

    需要

    • 唯一的触发器名
    • 触发器关联的表
    • 触发器应该响应的活动
    • 触发器合适执行

    使用CREATE TRIGGER语句创建。例:

    CREATE TRIGGER newproduct ALTER INSERT ON products
    FOR EACH ROW SELECT 'Product added';
    

    CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行,这里给出了AFTER INSERT,所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW,因此代码对每个插入行执行。在这个例子中,文本Product added将对每个插入的行显示一次。

    只有表支持触发器,视图不支持。

    每个表最多支持6个触发器(每条INSERT、UPDATE和DELETE前后)单一触发器不能与多个事件或多个表关联。

    删除触发器

    DROP TRIGGER newproduct;
    

    使用触发器

    INSERT触发器

    INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点:

    • 在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行;
    • 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);
    • 对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值。

    例:确定新值生成

    CREATE TRIGGER neworder AFTER INSERT ON orders
    FOR EACH ROW SELECT NEW.order_num;
    

    此代码创建一个名为neworder的触发器,它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时,MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行,因为在BEFORE INSERT语句执行之前,新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

    DELETE触发器

    DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两 点:

    • 在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行;
    • OLD中的值全都是只读的,不能更新。
      例:使用OLD保存将要被删除的行到一个存档表中:
    CREATE TRIGGER deleteorder BEFORE DELETE ON orders
    FOR EACH ROW
    BEGIN
    	INSERT INTO archive_orders(order_num, order_date, cust_id)
        VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
    END;
    

    在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_orders的存档表中(为实际使用这个例子,你需要用与orders相同的列创建一个名为archive_orders的表)。

    UPDATE触发器

    UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点:

    • 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新 更新的值;
    • 在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);
    • OLD中的值全都是只读的,不能更新。
      下面的例子保证州名缩写总是大写(不管UPDATE语句中给出的是大写还是小写):
    CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
    FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state);
    

    每次更新一个行时,NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)替换。

    关于触发器的进一步介绍

    • 与其他DBMS相比,MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
    • 创建触发器可能需要特殊的安全访问权限,但是,触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行,则相关的触发器也能执行。
    • 应该用触发器来保证数据的一致性(大小写、格式等)。在触发器中执行这种类型的处理的优点是它总是进行这种处理,而且是透明地进行,与客户机应用无关。
    • 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器,把更改(如果需要,甚至还有之前和之后的状态)记录到另一个表非常容易。
    • 遗憾的是,MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

    第二十六章:管理事务处理

    事务处理

    InnoDB支持事务处理

    事务处理可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。

    事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。

    事务处理过程:

    1. 检查数据库中是否存在相应的客户,如果不存在,添加他/她。
    2. 提交客户信息。
    3. 检索客户的ID。
    4. 添加一行到orders表。
    5. 如果在添加行到orders表时出现故障,回退。
    6. 检索orders表中赋予的新订单ID。
    7. 对于订购的每项物品,添加新行到orderitems表。
    8. 如果在添加新行到orderitems时出现故障,回退所有添加的orderitems行和orders行。
    9. 提交订单信息。

    在使用事务和事务处理时,有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语:

    • 事务(transaction)指一组SQL语句;
    • 回退(rollback)指撤销指定SQL语句的过程;
    • 提交(commit)指将未存储的SQL语句结果写入数据库表;
    • 保留点(savepoint)指事务处理中设置的临时占位符(place-holder),你可以对它发布回退(与回退整个事务处理不同)。

    控制事务处理

    使用ROLLBACK

    ROLLBACK撤回MySQL语句

    SELECT * FROM ordertitals;
    START TRANSCITION;
    DELETE FROM ordertotals;
    SELECT * FROM ordertotals;
    ROLLBACK;
    SELECT * FROM ordertotals;
    

    使用COMMIT

    一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit),即提交(写或保存)操作是自动 进行的。
    但是,在事务处理块中,提交不会隐含地进行。为进行明确的提交,使用COMMIT语句,如下所示:

    START TRAMSCATION;
    DELETE FROM orderitems WHERE order_num = 20010;
    DELETE FROM orders WHERE order_num = 20010;
    COMMIT;
    

    在这个例子中,从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems,所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用,但第二条失败,则DELETE不会提交(实际上,它是被自动撤销的)。

    使用保留点

    为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符
    SAVEPOINT

    SAVEPOINT delete1;
    ROLLBACK TO delete1;
    

    每个保留点都取标识它的唯一名字,以便在回退时,MySQL直到要回退到何处。

    更改默认的提交行为

    默认MySQL行为是自动提交所有更改,为指示MySQL不自动提交更改,需要使用

    SET autocommit=0;
    

    第二十七章:全球化和本地化

    使用字符集和校对顺序

    • 字符集为字母和符号的集合;
    • 编码为某个字符集成员的内部表示;
    • 校对为规定字符如何比较的指令。
    --查看所支持的字符集完整列表
    SHOW CHARECTER SET;
    --查看所支持校对的完整列表
    SHOW COLLATION;
    --确定所用的字符集和校对
    SHOW VARIABLSE LIKE 'character%';
    SHOW VARIABLES LIKE 'collation%';
    

    为了给表指定字符集和校对,可以使用带子句的CREATE TABLE

    CREATE TABLE mytable
    (
    	columnn1 INT,
        columnn2 VARCHAR(10)
    ) DEFAULT CHARACTER SET hebrew
      COLLATE hebrew_general_ci;
    

    除了能指定字符集和校对的表范围外,MySQL还允许对每个列设置它们

    CREATE TABLE mytable
    (
    	columnn1 INT,
        columnn2 VARCHAR(10)
        column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
    ) DEFAULT CHARACTER SET hebrew
      COLLATE hebrew_general_ci;
    

    校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句,可以在SELECT语句自身中进行:

    SELECT * FROM customers
    ORDER BY lastname, firstname COLLATE latin1_general_cs;
    

    此SELECT使用COLLATE指定一个备用的校对顺序(在这个例子中,为区分大小写的校对)
    最后,值得注意的是,如果绝对需要,串可以在字符集之间进行转换。为此,使用Cast()或Convert()函数。

    第二十八章:安全管理

    访问控制

    MySQL服务器的安全基础是:用户应该对他们需要的数据具有适当的访问权

    考虑以下内容:

    • 多数用户只需要对表进行读和写,但少数用户甚至需要能创建和删除表;
    • 某些用户需要读表,但可能不需要更新表;
    • 你可能想允许用户添加数据,但不允许他们删除数据;
    • 某些用户(管理员)可能需要处理用户账号的权限,但多数用户不需要;
    • 你可能想让用户通过存储过程访问数据,但不允许他们直接访问数据;
    • 你可能想根据用户登录的地点限制对某些功能的访问。

    不过在现实世界的日常工作中,决不能使用root。应该创建一系列的账号,有的用于管理,有的供用户使用,有的供开发人员使用,等等。

    管理用户

    MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般不需要直接访问mysql数据库和表,但有时需要直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表时。为此,可使用以下代码:

    USE mysql;
    SELECT user FROM user;
    

    创建用户账号

    CREATE USER ben IDENTIFIED BY 'p@ssw0rd';
    

    删除用户账号

    DROP USER bforta;
    

    MySQL 5y以前的版本需要先用REVOKE删除与账号相关的权限,再用DROP USER删除账号

    设置访问权限

    查看账号的权限

    SHOW GRANTS FOR bforta
    -- output
    +---------------------------------------------+
    | Grants for bforta@%                         |
    +---------------------------------------------+
    | GRANT USAGE ON *.* TO 'bforta'@'%'          |
    +---------------------------------------------+
    
    -- 输出结果显示用户bforta有一个权限USAGE ON *.*。USAGE表示根本没有权限,所以,此结果表示在任意数据库和任意表上对任何东西没有权限。
    

    为设置权限,使用GRANT语句。GRANT要求你至少给出以下信息:

    • 要授予的权限;
    • 被授予访问权限的数据库或表;
    • 用户名。

    例:

    GRANT SELECT ON crashcourse.* TO beforta;
    
    -- 此GRANT允许用户在crashcourse.*(crashcourse数据库的所有表)上使用SELECT。通过只授予SELECT访问权限,用户bforta对crashcourse数据库中的所有数据具有只读访问权限。
    
    
    SHOW GRANTS FRO bforta;
    +--------------------------------------------------+
    | Grants for bforta@%                              |
    +--------------------------------------------------+
    | GRANT USAGE ON *.* TO 'bforta'@'%'               |
    | GRANT SELECT ON 'charshcourse'.* TO 'bforta'@'%' |
    +--------------------------------------------------+
    

    撤销特定权限 REVOKE

    REVOKE SELECT ON crashcourse.* FROM bforta
    

    这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在,否则会出错

    权限说明
    ALL除 GRANT OPTION 外的所有权限
    ALTER使用 ALTER TABLE
    ALTER ROUTINE使用 ALTER PROCEDURE 和 DROP PROCEDURE
    CREATE使用 CREATE TABLE
    CREATE ROUTINE使用 CREATE PROCEDURE
    CREATE TEMPORARY TABLES使用 CREATE TEMPORARY TABLE
    CREATE USER使用 CREATE USER、DROP USER、RENAME USER 和 REVOKE ALL PRIVILEGES
    CREATE VIEW使用 CREATE VEIW
    DELETE使用 DELET
    DROP使用 DROP TABLE
    EXECUTE使用 CALL 和存储过程
    FILE使用 SELECT INTO OUTFILE 和 LOAD DATA INFILE
    GRANT OPTION使用 GRANT 和 REVOKE
    INDEX使用 CREATE INDEX 和 DROP INDEX
    INSERT使用 INSERT
    LOCK TABLES使用 LOCK TABLES
    PROCESS使用 SHOW FULL PROCESSLIST
    RELOAD使用 FLUSH
    REPLICATION CLIENT服务器位置的访问
    REPLICATION SLAVE由复制从属使用
    SELECT使用 SELECT
    SHOW DATABASES使用 SHOW DATABASES
    SHOW VIEW使用 SHOW CREATE VIEW
    SHUTDOWN使用 mysqladmin shutdown(用来关闭 MySQL)
    SUPER使用 CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和 SET GLOBAL。还允许 mysqladmin 调试登录 使用 UPDATE
    UPDATE使用 UPDATE
    USAGE无访问权限

    更改口令

    SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');
    

    设置自己的口令

    SET PASSWORD = Password('n3w p@$$w0rd');
    

    不指定用户名时SET PASSWORD更新当前登陆用户的口令

    第二十九章:数据库维护

    备份数据

    • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行,以便能正确地备份转储文件。
    • 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序)。
    • 可以使用MySQLBACKUP TABLESELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名,此系统文件必须不存在,否则会出错。数据可以用RESTORETABLE来复原。

    进行数据维护

    ANALYZE TABLE检查表键是否正确

    ANALYZE TABLE orders;
    --output
    +-----------------------+-----------+-----------+-----------+
    | Table                 | Op        | Msg_type  | Msg_text  |
    +-----------------------+-----------+-----------+-----------+
    | crashcourse.orders    | analyze   | status    | OK        |
    +-----------------------+-----------+-----------+-----------+
    

    CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查,FAST只检查未正常关闭的表,MEDIUM检查所有被删除的链接并进行键检验,QUICK只进行快速扫描。如下所示,CHECK TABLE发现和修复问题:

    CHECK TABLE orders, orderitems;
    

    诊断启动问题

    -- help 显示帮助
    -- safe-mode 装在减去某些最佳配置的服务器
    -- verbose 显示全文本消息
    -- version显示版本信息然后推出
    

    查看日志文件

    MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种。

    • 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err,位于data目录中。此日志名可用 --log-error命令行选项更改。
    • 查询日志。它记录所有MySQL活动,在诊断问题时非常有用。此日志文件可能会很快地变得非常大,因此不应该长期使用它。此日志通常名为hostname.log,位于data目录中。此名字可以用 --log命令行选项更改。
    • 二进制日志。它记录更新过数据(或者可能更新过数据)的所有 语句。此日志通常名为hostname-bin,位于data目录内。此名字可以用–log-bin命令行选项更改。注意,这个日志文件是MySQL 5中添加的,以前的MySQL版本中使用的是更新日志。
    • 缓慢查询日志。顾名思义,此日志记录执行缓慢的任何查询。这 个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ,位于 data 目录中。此名字可以用–log-slow-queries命令行选项更改。

    第三十章:改善性能

    • 首先,MySQL(与所有DBMS一样)具有特定的硬件建议。在学习和研究MySQL时,使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说,应该坚持遵循这些硬件建议。
    • 一般来说,关键的生产DBMS应该运行在自己的专用服务器上。
    • MySQL是用一系列的默认设置预先配置的,从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。(为查看当前设置,可使用SHOW VARIABLES;SHOW STATUS;。)
    • MySQL一个多用户多线程的DBMS,换言之,它经常同时执行多个任务。如果这些任务中的某一个执行缓慢,则所有请求都会执行缓慢。如果遇到显著的性能不良,可使用SHOW PROCESSLIST显示所有活动进程(以及它们的线程ID和执行时间)。你还可以用KILL命令终结某个特定的进程(使用这个命令需要作为管理员登录)。
    • 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等,找出最佳的方法。
    • 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
    • 一般来说,存储过程执行得比一条一条地执行其中的各条MySQL语句快。
    • 应该总是使用正确的数据类型。
    • 决不要检索比需求还要多的数据。换言之,不要用SELECT *(除非你真正需要每个列)。
    • 有的操作(包括INSERT)支持一个可选的DELAYED关键字,如果使用它,将把控制立即返回给调用程序,并且一旦有可能就实际执行该操作。
    • 在导入数据时,应该关闭自动提交。你可能还想删除索引(包括 FULLTEXT索引),然后在导入完成后再重建它们。
    • 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务,需要分析使用的SELECT语句以找出重复的 WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长,则可以断定其中使用的列(或几个列)就是需要索引的对象。
    • 当SELECT语句中有一系列复杂的OR条件时,使用多条SELECT语句和连接它们的UNION语句,可以极大地改进性能。
    • 索引改善数据检索的性能,但损害数据插入、删除和更新的性能。如果你有一些表,它们收集数据且不经常被搜索,则在有必要之前不要索引它们。(索引可根据需要添加和删除。)
    • LIKE很慢。一般来说,最好是使用FULLTEXT而不是LIKE。
    • 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改,理想的优化和配置也会改变。
    • 最重要的规则就是,每条规则在某些条件下都会被打破。

    在这里插入图片描述
    https://hiszm.cn/

    展开全文
  • 程序员必知之浮点数运算原理详解

    万次阅读 多人点赞 2016-09-14 14:30:05
    导读:浮点数运算是一个非常有技术含量的话题,不太容易掌握。... 随着你经验的增长,你肯定 想去深入了解一些常见的东西的细节,浮点数运算就是其中一。1. 什么是浮点数? 在计算机系统的发展...

      导读:浮点数运算是一个非常有技术含量的话题,不太容易掌握。许多程序员都不清楚使用==操作符比较float/double类型的话到底出现什么问题。 许多人使用float/double进行货币计算时经常会犯错。这篇文章是这一系列中的精华,所有的软件开发人员都应该读一下。

      随着你经验的增长,你肯定 想去深入了解一些常见的东西的细节,浮点数运算就是其中之一。

    1. 什么是浮点数?

      在计算机系统的发展过程中,曾经提出过多种方法表达实数。

      【1】典型的比如相对于浮点数的定点数(Fixed Point Number)。在这种表达方式中,小数点固定的位于实数所有数字中间的某个位置。货币的表达就可以使用这种方式,比如 99.00 或者 00.99 可以用于表达具有四位精度(Precision),小数点后有两位的货币值。由于小数点位置固定,所以可以直接用四位数值来表达相应的数值。SQL 中的 NUMBER 数据类型就是利用定点数来定义的。

      【2】还有一种提议的表达方式为有理数表达方式,即用两个整数的比值来表达实数。

      定点数表达法的缺点在于其形式过于僵硬,固定的小数点位置决定了固定位数的整数部分和小数部分,不利于同时表达特别大的数或者特别小的数。最终,绝大多数现代的计算机系统采纳了所谓的浮点数表达方式

      【3】浮点数表达方式, 这种表达方式利用科学计数法来表达实数,即用一个尾数(Mantissa ),一个基数(Base),一个指数(Exponent)以及一个表示正负的符号来表达实数。比如 123.45 用十进制科学计数法可以表达为 1.2345 × 102 ,其中 1.2345 为尾数,10 为基数,2 为指数。浮点数利用指数达到了浮动小数点的效果,从而可以灵活地表达更大范围的实数。提示: 尾数有时也称为有效数字(Significand)。尾数实际上是有效数字的非正式说法。

      同样的数值可以有多种浮点数表达方式,比如上面例子中的 123.45 可以表达为 12.345 × 101,0.12345 × 103 或者 1.2345 × 102。因为这种多样性,有必要对其加以规范化以达到统一表达的目标。规范的(Normalized)浮点数表达方式具有如下形式:

      d.dd...d × βe , (0 ≤ di < β)

      其中 d.dd...d 即尾数,β 为基数,e 为指数。尾数中数字的个数称为精度,在本文中用 p(presion) 来表示。每个数字 d 介于 0 和基数β之间,包括 0。小数点左侧的数字不为 0。

    (1)  基于规范表达的浮点数对应的具体值可由下面的表达式计算而得:(p是精度个数)

      ±(d0 + d1β-1 + ... + dp-1β-(p-1)e , (0 ≤ di < β)

      对于十进制的浮点数,即基数 β 等于 10 的浮点数而言,上面的表达式非常容易理解,也很直白。计算机内部的数值表达是基于二进制的。从上面的表达式,我们可以知道,二进制数同样可以有小数点,也 同样具有类似于十进制的表达方式。只是此时 β 等于 2,而每个数字 d 只能在 0 和 1 之间取值。

    (2)  比如二进制数 1001.101 相当于:精度为7

       1 × 2 3 + 0 × 22 + 0 × 21 + 1 × 20 + 1 × 2-1 + 0 × 2-2 + 1 × 2-3,对应于十进制的 9.625。

      其规范浮点数表达为 1.001101 × 23

    (3)  IEEE (美国电气和电子工程师学会)浮点数

      计算机中是用有限的连续字节保存浮点数的。

      IEEE定义了多种浮点格式,但最常见的是三种类型:单精度、双精度、扩展双精度,分别适用于不同的计算要求。一般而言,单精度适合一般计算,双精度适合科学计算,扩展双精度适合高精度计算。一个遵循IEEE 754标准的系统必须支持单精度类型(强制类型)、最好也支持双精度类型(推荐类型),至于扩展双精度类型可以随意。单精度(Single Precision)浮点数是32位(即4字节)的,双精度(Double Precision)浮点数是64位(即8字节)的。

      保存这些浮点数当然必须有特定的格式,Java 平台上的浮点数类型 float 和 double 采纳了 IEEE 754 标准中所定义的单精度 32 位浮点数和双精度 64 位浮点数的格式。注意: Java 平台还支持该标准定义的两种扩展格式,即 float-extended-exponent 和 double-extended-exponent 扩展格式。这里将不作介绍,有兴趣的读者可以参考相应的参考资料。

      在 IEEE 标准中,浮点数是将特定长度的连续字节的所有二进制位分割为特定宽度的符号域,指数域和尾数域三个域,其中保存的值分别用于表示给定二进制浮点数中的符号,指数和尾数。这样,通过尾数和可以调节的指数(所以称为"浮点")就可以表达给定的数值了。

      具体的格式参见下面的表格:

      

      需要特别注意的是,扩展双精度类型没有隐含位,因此它的有效位数与尾数位数一致,而单精度类型和双精度类型均有一个隐含位,因此它的有效位数比位数位数多一个。


           

      IEEE754标准规定一个实数V可以用:  V=(-1)s×M×2^E的形式表示,说明如下:
      (1)符号s(sign)决定实数是正数(s=0)还是负数(s=1),对数值0的符号位特殊处理。
      (2)有效数字M是二进制小数,M的取值范围在1≤M<2或0≤M<1。
      (3)指数E(exponent)是2的幂,它的作用是对浮点数加权。


       为了强制定义一些特殊值,IEEE标准通过指数将表示空间划分成了三大块:

      【1】最小值指数(所有位全置0)用于定义0和弱规范数

      【2】最大指数(所有位全值1)用于定义±∞和NaN(Not a Number)

      【3】其他指数用于表示常规的数

      这样一来,最大(指绝对值)常规数的指数不是全1的,最小常规数的指数也不是0,而是1。

      

      S:符号位,    Exponent:指数域    Fraction:尾数域

      注意:尾数有时也称为有效数字(Significand),

       一般如1.001001*2EValue,即一个尾数(Mantissa ),一个基数(底数Base),一个指数Evalue表示

      即: M * B尾数 * 底数指数

      通常情况,IEEE标准写法,尾数的1,省略,Fraction= 0.001001,因为标准写法,前面的1总是省略Fraction = 尾数 - 1 IEEE规定小数点左侧的 1 是隐藏的

      如果指数值:加上相应的浮点数偏执后的值:即 Exponent = EValue + Bias

      所以上述的值: X = (-1)S  X ( 1 + Fraction) (Exponent - Bias), 也就不足为奇了


     

      在上面的图例中:

      ①  第一个域:为符号域。其中 0 表示数值为正数,而 1 则表示负数

      ②  第二个域为指数域,对应于我们之前介绍的二进制科学计数法中的指数部分。

      指数阈:通常使用移码表示:

      移码和补码只有符号位相反,其余都一样。对于正数而言,原码反码补码都一样对于负数而言,补码就是其绝对值的原码全部取反,然后加1(不包括符号位))。

      其中单精度数为 8 位,双精度数为 11 位。以单精度数为例,8 位的指数为可以表达 0 到 255 之间的 255 个指数值

      但是,指数可以为正数,也可以为负数。为了处理负指数的情况,实际的指数值按要求需要加上一个偏差(Bias)值作为保存在指数域中的值,单精度数的偏差值为 127(0-111 1111)(8位),而双精度数的偏差值为 1023(0-1 1111 1111)(10位)。比如,单精度的实际指数值 0 在指数域中将保存为 127;而保存在指数域中的 64 则表示实际的指数值 -63偏差的引入使得对于单精度数,实际可以表达的指数值的范围就变成 -127 到 128 之间(包含两端)[-127, 128]

      我们不久还将看到:

      实际的指数值 -127(保存为 全 0),即: 首先-127原码1-111 1111,的补码1-000 0001,然后加上单精度偏执: 0-111 111 ,即结果:0-000 0000,全0.  所以0-000 0000 指数位表示:-127,即e-127

      以及 +128(保存为全 1), 即:首先+128原码‘1’-000 0000,的补码, ‘1’-000 0000,然后加上单精度偏执:0-111 111 ,, 即结果:‘1’-111 1111,全1。   即全1 指数位表示:+128,即e+128

      这些特殊值,保留用作特殊值的处理。这样,实际可以表达的有效指数范围就在 -127 和 127 之间。在本文中,最小指数和最大指数分别用 emin 和 emax 来表达。


     

      计算机中的符号数有三种表示方法,即原码、反码和补码。

      如补码的求取:
        ①  正数(符号位为0的数)补码与原码相同.
        ②  负数(符号位为1的数)变为补码时符号位不变,其余各项取反,最后在末尾+1;即求负数的反码不包括符号位
      例如:正数  原码01100110,补码为:01100110
         负数  原码11100110,先变反码:10011001,再加1变为补码:10011010
      计算机中的符号数有三种表示方法,即原码、反码和补码。三种表示方法均有符号位和数值位两部分符号位都是用0表示“正”,用1表示“负”,而数值位,三种表示方法各不相同。
      在计算机系统中,数值一律用补码来表示和存储。原因在于:①使用补码,可以将符号位和数值域统一处理;②同时,加法和减法也可以统一处理。此外,③补码与原码相互转换,其运算过程是相同的,不需要额外的硬件电路
      特性
      ①  一个负整数(或原码)与其补数(或补码)相加,和为模。eg:原码11100110, 补码:10011010 和:
      ②  对一个整数的补码再求补码,等于该整数自身。
      ③  补码的正零负零表示方法相同。即 0-0000000, 1-0000000取反加1, 0-0000000


     

      ③  图例中的第三个域为尾数域,其中单精度数为 23 位长,双精度数为 52 位长。除了我们将要讲到的某些特殊值外,IEEE 标准要求浮点数必须是规范的。这意味着尾数的小数点左侧必须为 1,因此我们在保存尾数的时候,可以省略小数点前面这个 1,从而腾出一个二进制位来保存更多的尾数。这样我们实际上用 23 位长的尾数域表达了 24 位的尾数。比如对于单精度数而言,二进制的 1001.101(对应于十进制的 9.625)可以表达为 1.001101 × 23,所以实际保存在尾数域中的值为 00110100000000000000000,即去掉小数点左侧的 1,并用 0 在右侧补齐。

       根据IEEE(美国电气和电子工程师学会)754标准要求,无法精确保存的值必须向最接近的可保存的值进行舍入。这有点像我们熟悉的十进制的四舍五入,即不足一半则舍,一半以上(包括一半)则进。不过对于二进制浮 点数而言,还多一条规矩,就是当需要舍入的值刚好是一半时,不是简单地进,而是在前后两个等距接近的可保存的值中,取其中最后一位有效数字为零者。从上面 的示例中可以看出,奇数都被舍入为偶数,且有舍有进。我们可以将这种舍入误差理解为"半位"的误差。所以,为了避免 7.22 对很多人造成的困惑,有些文章经常以 7.5 位来说明单精度浮点数的精度问题。

      据以上分析,IEEE 754标准中定义浮点数的表示范围为:

      单精度浮点数  二进制:± (2-2^-23) × 2127    对应十进制:  ~ ± 10^38.53

      双精度浮点数    二进制:± (2-2^-52) × 21023

      浮点数的表示有一定的范围,超出范围时会产生溢出(Flow),一般称大于绝对值最大的数据为上溢(Overflow),小于绝对值最小的数据为下溢(Underflow)。

     

    2. 浮点数的表示约定

      单精度浮点数和双精度浮点数都是用IEEE 754标准定义的,其中有一些特殊约定,例如:

      (1)  当P=0,M=0时,表示0。
      (2)  当P=255,M=0时,表示无穷大,用符号位来确定是正无穷大还是负无穷大。
      (3)  当P=255,M≠0时,表示NaN(Not a Number,不是一个数)。

     

    3. 特殊值

     通过前面的介绍,你应该已经了解的浮点数的基本知识,这些知识对于一个不接触浮点数应用的人应该足够了。不过,如果你兴趣正浓,或者面对着一个棘手的浮点数应用,可以通过本节了解到关于浮点数的一些值得注意的特殊之处。

      我们已经知道,单精度浮点数指数域实际可以表达的指数值的范围为 -127 到 128 之间(包含两端)。其中,值 -127(保存为全0)以及 +128(保存为全1)保留用作特殊值的处理。本节将详细 IEEE 标准中所定义的这些特殊值。

      浮点数中的特殊值主要用于特殊情况或者错误的处理。比如在程序对一个负数进行开平方时,一个特殊的返回值将用于标记这种错误,该值为 NaN(Not a Number)。没有这样的特殊值,对于此类错误只能粗暴地终止计算。除了 NaN 之外,IEEE 标准还定义了 ±0,±∞ 以及非规范化数(Denormalized Number)。

      对于单精度浮点数,所有这些特殊值都由保留的特殊指数值 -127 和 128 来编码。如果我们分别用 emin 和 emax 来表达其它常规指数值范围的边界,即 -126 和 127,则保留的特殊指数值可以分别表达为 emin - 1 和 emax + 1; 。基于这个表达方式,IEEE 标准的特殊值如下所示:

     

      其中 f 表示尾数中的小数点右侧的(Fraction)部分,即标准记法中的有效部分-1

      第一行即我们之前介绍的普通的规范化浮点数。随后我们将分别对余下的特殊值加以介绍。

      第2,3,4,5行,是特殊值。

    (1)NaN

      NaN 用于处理计算中出现的错误情况,比如 0.0 除以 0.0 或者求负数的平方根。

      由上面的表中可以看出,对于单精度浮点数,NaN 表示为指数为 emax + 1 = 128(指数域全为 1),且尾数域不等于零的浮点数。IEEE 标准没有要求具体的尾数域,所以 NaN 实际上不是一个,而是一族

      不同的实现可以自由选择尾数域的值来表达 NaN,比如 Java 中的常量 Float.NaN 的浮点数可能表达为 0-11111111-10000000000000000000000,其中尾数域的第一位为 1,其余均为 0(不计隐藏的一位),但这取决系统的硬件架构。Java 中甚至允许程序员自己构造具有特定位模式的 NaN 值(通过 Float.intBitsToFloat() 方法)。比如,程序员可以利用这种定制的 NaN 值中的特定位模式来表达某些诊断信息。定制的 NaN 值,可以通过 Float.isNaN() 方法判定其为 NaN,但是它和 Float.NaN 常量却不相等。

      实际上,所有的 NaN 值都是无序的。数值比较操作符 <,<=,> 和 >= 在任一操作数为 NaN 时均返回 false。等于操作符 == 在任一操作数为 NaN 时均返回 false,即使是两个具有相同位模式的 NaN 也一样。而操作符 != 则当任一操作数为 NaN 时返回 true。

      这个规则的一个有趣的结果是 x!=x 当 x 为 NaN 时竟然为真

      NaN

      此外,任何有 NaN 作为操作数的操作也将产生 NaN。用特殊的 NaN 来表达上述运算错误的意义在于避免了因这些错误而导致运算的不必要的终止。比如,如果一个被循环调用的浮点运算方法,可能由于输入的参数问题而导致发生这些错误,NaN 使得 即使某次循环发生了这样的错误,也可以简单地继续执行循环以进行那些没有错误的运算。你可能想到,既然 Java 有异常处理机制,也许可以通过捕获并忽略异常达到相同的效果。但是,要知道,IEEE 标准不是仅仅为 Java 而制定的,各种语言处理异常的机制不尽相同,这将使得代码的迁移变得更加困难。何况,不是所有语言都有类似的异常或者信号(Signal)处理机制

    (2)无穷

      和 NaN 一样,特殊值无穷(Infinity)的指数部分同样为 emax + 1 = 128,不过无穷的尾数域必须为零。无穷用于表达计算中产生的上溢(Overflow)问题。比如两个极大的数相乘时,尽管两个操作数本身可以用保存为浮点数,但其结果可能大到无法保存为浮点数,而必须进行舍入。根据 IEEE 标准,此时不是将结果舍入为可以保存的最大的浮点数(因为这个数可能离实际的结果相差太远而毫无意义),而是将其舍入为无穷对于负数结果也是如此,只不过此时舍入为负无穷,也就是说符号域为 1 的无穷。有了 NaN 的经验我们不难理解,特殊值无穷使得计算中发生的上溢错误不必以终止运算为结果。

      无穷和除 NaN 以外的其它浮点数一样是有序的,从小到大依次为负无穷,负的有穷非零值,正负零(随后介绍),正的有穷非零值以及正无穷。除 NaN 以外的任何非零值除以零,结果都将是无穷,而符号则由作为除数的零的符号决定。  

      回顾我们对 NaN 的介绍,当零除以零时得到的结果不是无穷而是 NaN 。原因不难理解,当除数和被除数都逼近于零时,其商可能为任何值,所以 IEEE 标准决定此时用 NaN 作为商比较合适。

    (3)有符号的零

      因为 IEEE 标准的浮点数格式中,小数点左侧的 1 是隐藏的,而零显然需要尾数必须是零。所以,零也就无法直接用这种格式表达而只能特殊处理。实际上,零保存为尾数域为全为 0,指数域为 emin - 1 = -127,也就是说指数域也全为 0。考虑到符号域的作用,所以存在着两个零,即 +0 和 -0。不同于正负无穷之间是有序的,IEEE 标准规定正负零是相等的

     零有正负之分,的确非常容易让人困惑。这一点是基于数值分析的多种考虑,经利弊权衡后形成的结果。有符号的零可以避免运算中,特别是涉及无穷的运算中,符号信息的丢失。举例而言,如果零无符号,则等式 1/(1/x) = x 当x = ±∞ 时不再成立。原因是如果零无符号,1 和正负无穷的比值为同一个零,然后 1 与 0 的比值为正无穷,符号没有了。解决这个问题,除非无穷也没有符号。但是无穷的符号表达了上溢发生在数轴的哪一侧,这个信息显然是不能不要的。零有符号也造成了其它问题,比如当 x=y 时,等式1/x = 1/y 在 x 和 y 分别为 +0 和 -0 时,两端分别为正无穷和负无穷而不再成立。当然,解决这个问题的另一个思路是和无穷一样,规定零也是有序的。但是,如果零是有序的,则即使 if (x==0) 这样简单的判断也由于 x 可能是 ±0 而变得不确定了。两害取其轻者,零还是无序的好。

    (4)非规范化数

      我们来考察浮点数的一个特殊情况。选择两个绝对值极小的浮点数,以单精度的二进制浮点数为例,比如 1.001 × 2-125 和 1.0001 × 2-125 这两个数(分别对应于十进制的 2.6448623 × 10-38 和 2.4979255 × 10-38)。显然,他们都是普通的浮点数(指数为 -125,大于允许的最小值 -126;尾数更没问题),按照 IEEE 754 可以分别保存为 00000001000100000000000000000000(0x1100000)和 00000001000010000000000000000000(0x1080000)。
      
    现在我们看看这两个浮点数的差值。不难得出,该差值为 0.0001 × 2-125,表达为规范浮点数则为 1.0 × 2-129。问题在于其指数大于允许的最小指数值,所以无法保存为规范浮点数。最终,只能近似为零(Flush to Zero)。这中特殊情况意味着下面本来十分可靠的代码也可能出现问题:

    if (x != y) {
    
     z = 1 / (x -y);
    
    }

      正如我们精心选择的两个浮点数展现的问题一样,即使 x 不等于 y,x 和 y 的差值仍然可能绝对值过小,而近似为零,导致除以 0 的情况发生。

      为了解决此类问题,IEEE 标准中引入了非规范(Denormalized)浮点数。规定当浮点数的指数为允许的最小指数值,即 emin 时,尾数不必是规范化的。比如上面例子中的差值可以表达为非规范的浮点数 0.001 × 2-126,其中指数 -126 等于 emin。注意,这里规定的是"不必",这也就意味着"可以"。当浮点数实际的指数为 emin,且指数域也为 emin 时,该浮点数仍是规范的,也就是说,保存时隐含着一个隐藏的尾数位。为了保存非规范浮点数,IEEE 标准采用了类似处理特殊值零时所采用的办法,即用特殊的指数域值 emin - 1 加以标记,当然,此时的尾数域不能为零。这样,例子中的差值可以保存为 00000000000100000000000000000000(0x100000),没有隐含的尾数位。
      有了非规范浮点数,去掉了隐含的尾数位的制约,可以保存绝对值更小的浮点数。而且,也由于不再受到隐含尾数域的制约,上述关于极小差值的问题也不存在了,因为所有可以保存的浮点数之间的差值同样可以保存。

    4. 范围和精度

     很多小数根本无法在二进制计算机中精确表示(比如最简单的 0.1)由于浮点数尾数域的位数是有限的,为此,浮点数的处理办法是持续该过程直到由此得到的尾数足以填满尾数域,之后对多余的位进行舍入。

      换句话说,除了我们之前讲到的精度问题之外,十进制到二进制的变换也并不能保证总是精确的,而只能是近似值

      事实上,只有很少一部分十进制小数具有精确的二进制浮点数表达。再加上浮点数运算过程中的误差累积,结果是很多我们看来非常简单的十进制运算在计算机上却往往出人意料。这就是最常见的浮点运算的"不准确"问题。

      参见下面的 Java 示例:

    System.out.print("34.6-34.0=" + (34.6f-34.0f));

      这段代码的输出结果如下:

    34.6-34.0=0.5999985

      产生这个误差的原因是 34.6 无法精确的表达为相应的浮点数,而只能保存为经过舍入的近似值这个近似值与 34.0 之间的运算自然无法产生精确的结果。

      存储格式的范围和精度如下表所示:

            

    5. 舍入

      值得注意的是,对于单精度数,由于我们只有 24 位的尾数(其中一位隐藏),所以可以表达的最大指数为 224 - 1 = 16,777,215。

      特别的,16,777,216 是偶数,所以我们可以通过将它除以 2 并相应地调整指数来保存这个数,这样 16,777,216 同样可以被精确的保存。相反,数值      16,777,217 则无法被精确的保存。由此,我们可以看到单精度的浮点数可以表达的十进制数值中,真正有效的数字不高于 8 位

      事实上,对相对误差的数值分析结果显示有效的精度大约为 7.22 位。

      实例如下所示:

      

      根 据标准要求,无法精确保存的值必须向最接近的可保存的值进行舍入。这有点像我们熟悉的十进制的四舍五入,即不足一半则舍,一半以上(包括一半)则进。不过 对于二进制浮点数而言,还多一条规矩,就是当需要舍入的值刚好是一半时,不是简单地进,而是在前后两个等距接近的可保存的值中,取其中最后一位有效数字为 零者。从上面的示例中可以看出,奇数都被舍入为偶数,且有舍有进。我们可以将这种舍入误差理解为"半位"的误差。所以,为了避免 7.22 对很多人造成的困惑,有些文章经常以 7.5 位来说明单精度浮点数的精度问题。

      提示: 这里采用的浮点数舍入规则有时被称为舍入到偶数(Round to Even)。相比简单地逢一半则进的舍入规则,舍入到偶数有助于从某些角度减小计算中产生的舍入误差累积问题。因此为 IEEE 标准所采用

    展开全文
  • 不知不觉《其所以然》系列竟然写到第三篇了,虽然前面两篇说了不少,但是总觉得还有东西没有说“透”,或者说没有说“好”。所以这篇试图从不同的角度用更好的例子来继续深入阐述。(感谢 silwile 对本文的...
  • 【原文】天地与我并生,而万物与我为一。  [译文]天地和我共生,万物和我为一体。...这就是说,只有你看透人生种种难堪的局面,使自己的内心宽广无垠,才“清淡心”的作用。曾国藩淡泊名利,即在于能
  • 正则表达式必必会

    千次阅读 多人点赞 2021-03-04 00:33:37
    一、概述 正则表达式用于文本内容的查找和替换。 正则表达式内置于其它语言或者软件产品中,它...正则表达式一般是区分大小写的,但有些实现不区分。 正则表达式 rabbit 匹配结果 My name is rabbit . 三、匹配一
  • 韩非子《说》及译文

    千次阅读 2010-12-19 11:55:00
    原文: <br />凡说之难知之有以说之之难也,又吾辩能明吾意之难也,又吾敢横失而能尽之难也。凡说之难:在所说心,可以吾说当。所说出于为名高者,而说以厚利,见下节而遇卑贱,...
  • 软件测试MySQL数据库必必会,面试必备!

    千次阅读 多人点赞 2020-07-05 12:21:02
    软件测试MySQL数据库必必会,面试必备! 一、前言 1.1 数据库概念及分类 1.2 SQL语句概念及分类 1.3 MySQL数据类型 二、常用SQL语句 2.1 数据库相关SQL 2.2 表相关SQL 2.3 修改表相关SQL 2.4 操作表记录相关SQL ...
  • 紧随其后,国内外的其它互联网搜索引擎公司纷纷构建了自己的知识图谱,如微软的Probase、搜狗的立方、百度的知心。知识图谱在语义搜索、智能问答、数据挖掘、数字图书馆、推荐系统等领域有着广泛的应用。  下...
  • 網絡技術識大全

    万次阅读 2011-09-07 10:20:21
    WLAN的工作速率可以高达108 Mbps,距接入点的最远距离可以达到100米,专用天线可以提高工作范围。   接入点有时与其它功能结合在一起,如路由器、网关或交换机。最终,无线网络会采用网桥或路由技术,通过接入点...
  • 尤其是shader,感觉要做到其然易,其所以然太,本文主要记录学习过程中的体会,再好的记忆不如烂笔头。 引言 本文主要分析,在学习shader阶段经常出现的一个函数,源码如下 UnityObjectToWorldNormal源码...
  • 有无相生 相成 长短相形 高下相盈 音声相和也,先后相随 恒 是以圣人居无为事 行不言教 万物作而弗始 为而弗恃 成功而弗居夫唯弗居 是以弗去 (3)第三章 不上贤 使民不争 不贵...
  • 【我所認的BIOS】->汇编语言宏汇编1By LightSeed2010-2-2 其实早就想写点关于汇编语言的文章了,但是最近感觉比较累,自己比较懒今天才动手写。哎。。。真是身心俱疲,房价涨了,小菜涨了,妹儿的要求涨...
  • 应届生求职面试真的有那么

    千次阅读 多人点赞 2019-10-27 02:05:46
    在全国重点城市重点高校的宣讲,台下的听众是该行业新一代人才的聚集,在公司校招宣讲的同时,无形中更是对公司的推广、介绍,让更多的人,了解、知道该公司的存在,所属领域等等,这种宣传堪称前所未有,直击重点...
  • 《庄子·外篇·北游第二十二》

    千次阅读 2006-01-05 20:12:00
    北游于玄水上,登隐囗(上“分”下“廾”音fen2)...问乎狂屈。狂屈曰:“唉!予知之,将语若。”中欲 言而忘其所欲言。不得问,反于帝宫,见黄帝而问焉。黄帝曰:“ 无思无虑始知道,无处无服始
  • 胡思 胡写(技术文章)

    千次阅读 2015-09-14 03:00:31
    生不生死不死,静不静动不动。反之亦然。不该多为事而多为,当为事,恐难为。动静明了,动静皆得。人生难为 ,为事明了。人生思,万物混同。... 为事,其不全,思不全,敢为
  • 参数统计

    千次阅读 2017-08-23 21:56:39
    参数统计 出自 MBA智库百科(http://wiki.mbalib.com/) 参数统计(Nonparametric statistics) 目录 [隐藏] 1 什么是非参数统计2 参数统计的适用范围3 参数统计的特点4 参数统计...
  • 当然如果要硬杠,不是完全没办法——这就需要需分析相位谱了,但在实际应用中,有谁会不嫌麻烦地去看相位谱呢? 总而言之,傅里叶变换非常擅长分析那些频率特征均一稳定的平稳信号。但是对于平稳信号,傅立叶...
  • 海量数据挖掘Mining Massive Datasets(MMDs) -Jure Leskovec courses学习笔记关联规则Apriori算法的改进:hash方法 - 大数据集下的频繁项集:挖掘随机采样算法、SON算法、Toivonen算法 Apriori算法
  • Tuxedo 初学必

    千次阅读 2010-12-28 17:06:00
    在ATMI编程环境中,客户机和服务器之间使用消息缓冲区来进行数据交换,由于ATMI消息缓冲区具有格式化和自描述的特点,因此又称为类型缓冲区(Typed Buffers),类型缓冲区克服了平台差异,为不同系统下的数据表示...
  • 本文基于 Netty 4.1 展开介绍相关理论模型,使用场景,基本组件、整体架构,其然且其所以然,希望给大家在实际开发实践、学习开源项目方面提供参考。 Netty 是一个异步事件驱动的网络应用程序框架,用于快速...
  • 我本来一直觉得NoSQL其实很容易理解的,我本身已经对NoSQL有了非常深入的研究,但是在最近准备YunTable的Chart的时候,发现NoSQL不仅非常博大精深,而且我个人对NoSQL的理解只是皮毛而已,但我还算是一个“耻...
  • 必会JVM二-运行时数据区

    万次阅读 多人点赞 2020-06-01 17:45:12
    之前所以说 i++ 不是原子操作,即使使用 volatile 修饰不是线程安全,就是因为,可能 i 被从局部变量表(内存)取出,压入操作栈(寄存器),操作栈中自增,使用栈顶值更新局部变量表(寄存器更新写入内存),...
  • 大数据岗位必必会的53个Java基础

    万次阅读 多人点赞 2020-04-26 21:56:23
    但有时候会存在一些使用接口很解决的问题,这个时候我们可以利用内部类提供的、可以继承多个具体的或者抽象的类的能力来解决这些程序设计问题。可以这样说,接口只是解决了部分问题,而内部类使得多重继承的解决...
  • ARM汇编必必会

    万次阅读 2013-01-15 20:16:17
    这里,我们找出了这两种体系结构最明显的不同处,并对此进行介绍,让大家对于RISC体系结构的汇编有一个基本的了解。首先,我们就来看一看基于RISC的ARM的体系结构。 基于RISC 的ARM CPU ARM是一种RISC体系...
  •   作者:周洋 (清华大学);李森林 (中南财经政法大学);连玉君 (中山大学) Stata 连享会: 知乎 | 简书 | 码云 | CSDN ...然而,在很多情况下,解释变量和被解释变量可能存在线性关系。在模型中加入平方...
  • 而我不知道,到底会有多少人在观看一幕戏、一场电影或任何一个场面时,意识到让你们可以享受到色彩、美景与动感的视觉是个奇迹,并对此抱有感激情呢?” ——《假如给我三天光明》[美国]海伦•凯...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 49,544
精华内容 19,817
关键字:

则非知之难也