|
|
|
|
|
<!DOCTYPE HTML>
|
|
|
<html lang="" >
|
|
|
<head>
|
|
|
<meta charset="UTF-8">
|
|
|
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
|
|
|
<title>（三）SQL语句 · GitBook</title>
|
|
|
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
|
|
|
<meta name="description" content="">
|
|
|
<meta name="generator" content="GitBook 3.2.3">
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<link rel="stylesheet" href="../gitbook/style.css">
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<link rel="stylesheet" href="../gitbook/gitbook-plugin-prism/prism.css">
|
|
|
|
|
|
|
|
|
|
|
|
<link rel="stylesheet" href="../gitbook/gitbook-plugin-search/search.css">
|
|
|
|
|
|
|
|
|
|
|
|
<link rel="stylesheet" href="../gitbook/gitbook-plugin-fontsettings/website.css">
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<link rel="stylesheet" href="../styles/website.css">
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<meta name="HandheldFriendly" content="true"/>
|
|
|
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no">
|
|
|
<meta name="apple-mobile-web-app-capable" content="yes">
|
|
|
<meta name="apple-mobile-web-app-status-bar-style" content="black">
|
|
|
<link rel="apple-touch-icon-precomposed" sizes="152x152" href="../gitbook/images/apple-touch-icon-precomposed-152.png">
|
|
|
<link rel="shortcut icon" href="../gitbook/images/favicon.ico" type="image/x-icon">
|
|
|
|
|
|
|
|
|
<link rel="next" href="ORM映射.html" />
|
|
|
|
|
|
|
|
|
<link rel="prev" href="索引规约.html" />
|
|
|
|
|
|
|
|
|
</head>
|
|
|
<body>
|
|
|
|
|
|
<div class="book">
|
|
|
<div class="book-summary">
|
|
|
|
|
|
|
|
|
<div id="book-search-input" role="search">
|
|
|
<input type="text" placeholder="Type to search" />
|
|
|
</div>
|
|
|
|
|
|
|
|
|
<nav role="navigation">
|
|
|
|
|
|
|
|
|
|
|
|
<ul class="summary">
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<li class="chapter " data-level="1.1" data-path="../">
|
|
|
|
|
|
<a href="../">
|
|
|
|
|
|
|
|
|
前言
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2" >
|
|
|
|
|
|
<span>
|
|
|
|
|
|
|
|
|
一、编程规约
|
|
|
|
|
|
</span>
|
|
|
|
|
|
|
|
|
|
|
|
<ul class="articles">
|
|
|
|
|
|
|
|
|
<li class="chapter " data-level="1.2.1" data-path="../编程规约/命名风格.html">
|
|
|
|
|
|
<a href="../编程规约/命名风格.html">
|
|
|
|
|
|
|
|
|
（一）命名风格
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2.2" data-path="../编程规约/常量定义.html">
|
|
|
|
|
|
<a href="../编程规约/常量定义.html">
|
|
|
|
|
|
|
|
|
（二）常量定义
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2.3" data-path="../编程规约/代码格式.html">
|
|
|
|
|
|
<a href="../编程规约/代码格式.html">
|
|
|
|
|
|
|
|
|
（三）代码格式
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2.4" data-path="../编程规约/OOP规范.html">
|
|
|
|
|
|
<a href="../编程规约/OOP规范.html">
|
|
|
|
|
|
|
|
|
（四）OOP规范
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2.5" data-path="../编程规约/集合处理.html">
|
|
|
|
|
|
<a href="../编程规约/集合处理.html">
|
|
|
|
|
|
|
|
|
（五）集合处理
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2.6" data-path="../编程规约/并发处理.html">
|
|
|
|
|
|
<a href="../编程规约/并发处理.html">
|
|
|
|
|
|
|
|
|
（六）并发处理
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2.7" data-path="../编程规约/控制语句.html">
|
|
|
|
|
|
<a href="../编程规约/控制语句.html">
|
|
|
|
|
|
|
|
|
（七）控制语句
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.2.8" data-path="../编程规约/注释规约.html">
|
|
|
|
|
|
<a href="../编程规约/注释规约.html">
|
|
|
|
|
|
|
|
|
（八）注释规约
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
|
|
|
</ul>
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.3" >
|
|
|
|
|
|
<span>
|
|
|
|
|
|
|
|
|
二、异常日志
|
|
|
|
|
|
</span>
|
|
|
|
|
|
|
|
|
|
|
|
<ul class="articles">
|
|
|
|
|
|
|
|
|
<li class="chapter " data-level="1.3.1" data-path="../异常日志/异常处理.html">
|
|
|
|
|
|
<a href="../异常日志/异常处理.html">
|
|
|
|
|
|
|
|
|
（一）异常处理
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.3.2" data-path="../异常日志/日志规约.html">
|
|
|
|
|
|
<a href="../异常日志/日志规约.html">
|
|
|
|
|
|
|
|
|
（二）日志规范
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.3.3" data-path="../异常日志/其他.html">
|
|
|
|
|
|
<a href="../异常日志/其他.html">
|
|
|
|
|
|
|
|
|
（三）其他
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
|
|
|
</ul>
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.4" data-path="../单元测试.html">
|
|
|
|
|
|
<a href="../单元测试.html">
|
|
|
|
|
|
|
|
|
三、单元测试
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.5" data-path="../安全规约.html">
|
|
|
|
|
|
<a href="../安全规约.html">
|
|
|
|
|
|
|
|
|
四、安全规约
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.6" >
|
|
|
|
|
|
<span>
|
|
|
|
|
|
|
|
|
五、MySQL数据库
|
|
|
|
|
|
</span>
|
|
|
|
|
|
|
|
|
|
|
|
<ul class="articles">
|
|
|
|
|
|
|
|
|
<li class="chapter " data-level="1.6.1" data-path="建表规约.html">
|
|
|
|
|
|
<a href="建表规约.html">
|
|
|
|
|
|
|
|
|
（一）建表规约
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.6.2" data-path="索引规约.html">
|
|
|
|
|
|
<a href="索引规约.html">
|
|
|
|
|
|
|
|
|
（二）索引规约
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter active" data-level="1.6.3" data-path="SQL语句.html">
|
|
|
|
|
|
<a href="SQL语句.html">
|
|
|
|
|
|
|
|
|
（三）SQL语句
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.6.4" data-path="ORM映射.html">
|
|
|
|
|
|
<a href="ORM映射.html">
|
|
|
|
|
|
|
|
|
（四）ORM映射
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
|
|
|
</ul>
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.7" >
|
|
|
|
|
|
<span>
|
|
|
|
|
|
|
|
|
六、工程结构
|
|
|
|
|
|
</span>
|
|
|
|
|
|
|
|
|
|
|
|
<ul class="articles">
|
|
|
|
|
|
|
|
|
<li class="chapter " data-level="1.7.1" data-path="../工程结构/应用分层.html">
|
|
|
|
|
|
<a href="../工程结构/应用分层.html">
|
|
|
|
|
|
|
|
|
（一）应用分层
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.7.2" data-path="../工程结构/二方库依赖.html">
|
|
|
|
|
|
<a href="../工程结构/二方库依赖.html">
|
|
|
|
|
|
|
|
|
（二）二方库依赖
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.7.3" data-path="../工程结构/服务器.html">
|
|
|
|
|
|
<a href="../工程结构/服务器.html">
|
|
|
|
|
|
|
|
|
（三）服务器
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
|
|
|
</ul>
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.8" data-path="../版本历史.html">
|
|
|
|
|
|
<a href="../版本历史.html">
|
|
|
|
|
|
|
|
|
附1：版本历史
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
<li class="chapter " data-level="1.9" data-path="../本手册专有名词.html">
|
|
|
|
|
|
<a href="../本手册专有名词.html">
|
|
|
|
|
|
|
|
|
附2：本手册专有名词
|
|
|
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</li>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<li class="divider"></li>
|
|
|
|
|
|
<li>
|
|
|
<a href="https://www.gitbook.com" target="blank" class="gitbook-link">
|
|
|
Published with GitBook
|
|
|
</a>
|
|
|
</li>
|
|
|
</ul>
|
|
|
|
|
|
|
|
|
</nav>
|
|
|
|
|
|
|
|
|
</div>
|
|
|
|
|
|
<div class="book-body">
|
|
|
|
|
|
<div class="body-inner">
|
|
|
|
|
|
|
|
|
|
|
|
<div class="book-header" role="navigation">
|
|
|
|
|
|
|
|
|
<!-- Title -->
|
|
|
<h1>
|
|
|
<i class="fa fa-circle-o-notch fa-spin"></i>
|
|
|
<a href=".." >（三）SQL语句</a>
|
|
|
</h1>
|
|
|
</div>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<div class="page-wrapper" tabindex="-1" role="main">
|
|
|
<div class="page-inner">
|
|
|
|
|
|
<div id="book-search-results">
|
|
|
<div class="search-noresults">
|
|
|
|
|
|
<section class="normal markdown-section">
|
|
|
|
|
|
<h2 id="三-sql语句">(三) SQL语句</h2>
|
|
|
<ol>
|
|
|
<li>【强制】不要使用count(列名)或count(常量)来替代count(<em>)，count(</em>)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
|
|
|
<br><span style="color:orange">说明</span>：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。 </li>
|
|
|
<li>【强制】count(distinct col) 计算该列除NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。 </li>
|
|
|
<li>【强制】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。
|
|
|
<br><span style="color:green">正例</span>：可以使用如下方式来避免sum的NPE问题：<pre class="language-">SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table; </pre></li>
|
|
|
<li>【强制】使用<code>ISNULL()</code>来判断是否为NULL值。 说明：NULL与任何值的直接比较都为NULL。<br>1） <code>NULL<>NULL</code>的返回结果是NULL，而不是<code>false</code>。<br>2） <code>NULL=NULL</code>的返回结果是NULL，而不是<code>true</code>。<br>3） <code>NULL<>1</code>的返回结果是NULL，而不是<code>true</code>。 </li>
|
|
|
<li>【强制】 在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。 </li>
|
|
|
<li>【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
|
|
|
<br><span style="color:orange">说明</span>：以学生和成绩的关系为例，学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。 </li>
|
|
|
<li>【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。 </li>
|
|
|
<li>【强制】数据订正（特别是删除、修改记录操作）时，要先select，避免出现误删除，确认无误才能执行更新语句。 </li>
|
|
|
<li>【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。 </li>
|
|
|
<li>【参考】如果有全球化需要，所有的字符存储与表示，均以utf-8编码，注意字符统计函数的区别。
|
|
|
<br><span style="color:orange">说明</span>：
|
|
|
<pre class="language-">SELECT LENGTH("轻松工作")； 返回为12
|
|
|
SELECT CHARACTER_LENGTH("轻松工作")； 返回为4</pre>
|
|
|
如果需要存储表情，那么选择utf8mb4来进行存储，注意它与utf-8编码的区别。 </li>
|
|
|
<li>【参考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发trigger，有可能造成事故，故不建议在开发代码中使用此语句。
|
|
|
<br><span style="color:orange">说明</span>：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。 </li>
|
|
|
</ol>
|
|
|
|
|
|
|
|
|
</section>
|
|
|
|
|
|
</div>
|
|
|
<div class="search-results">
|
|
|
<div class="has-results">
|
|
|
|
|
|
<h1 class="search-results-title"><span class='search-results-count'></span> results matching "<span class='search-query'></span>"</h1>
|
|
|
<ul class="search-results-list"></ul>
|
|
|
|
|
|
</div>
|
|
|
<div class="no-results">
|
|
|
|
|
|
<h1 class="search-results-title">No results matching "<span class='search-query'></span>"</h1>
|
|
|
|
|
|
</div>
|
|
|
</div>
|
|
|
</div>
|
|
|
|
|
|
</div>
|
|
|
</div>
|
|
|
|
|
|
</div>
|
|
|
|
|
|
|
|
|
|
|
|
<a href="索引规约.html" class="navigation navigation-prev " aria-label="Previous page: （二）索引规约">
|
|
|
<i class="fa fa-angle-left"></i>
|
|
|
</a>
|
|
|
|
|
|
|
|
|
<a href="ORM映射.html" class="navigation navigation-next " aria-label="Next page: （四）ORM映射">
|
|
|
<i class="fa fa-angle-right"></i>
|
|
|
</a>
|
|
|
|
|
|
|
|
|
|
|
|
</div>
|
|
|
|
|
|
<script>
|
|
|
var gitbook = gitbook || [];
|
|
|
gitbook.push(function() {
|
|
|
gitbook.page.hasChanged({"page":{"title":"（三）SQL语句","level":"1.6.3","depth":2,"next":{"title":"（四）ORM映射","level":"1.6.4","depth":2,"path":"MySQL数据库/ORM映射.md","ref":"MySQL数据库/ORM映射.md","articles":[]},"previous":{"title":"（二）索引规约","level":"1.6.2","depth":2,"path":"MySQL数据库/索引规约.md","ref":"MySQL数据库/索引规约.md","articles":[]},"dir":"ltr"},"config":{"gitbook":"*","theme":"default","variables":{},"plugins":["book-summary-scroll-position-saver","github","prism","-sharing","-highlight"],"pluginsConfig":{"github":{"url":"https://github.com/alibaba/p3c"},"book-summary-scroll-position-saver":{},"prism":{},"search":{},"lunr":{"maxIndexSize":1000000,"ignoreSpecialCharacters":false},"fontsettings":{"theme":"white","family":"sans","size":2},"theme-default":{"styles":{"website":"styles/website.css","pdf":"styles/pdf.css","epub":"styles/epub.css","mobi":"styles/mobi.css","ebook":"styles/ebook.css","print":"styles/print.css"},"showLevel":false}},"structure":{"langs":"LANGS.md","readme":"README.md","glossary":"GLOSSARY.md","summary":"SUMMARY.md"},"pdf":{"pageNumbers":true,"fontSize":12,"fontFamily":"Arial","paperSize":"a4","chapterMark":"pagebreak","pageBreaksBefore":"/","margin":{"right":62,"left":62,"top":56,"bottom":56}},"styles":{"website":"styles/website.css"}},"file":{"path":"MySQL数据库/SQL语句.md","mtime":"2018-01-13T07:52:52.580Z","type":"markdown"},"gitbook":{"version":"3.2.3","time":"2018-01-15T14:41:43.583Z"},"basePath":"..","book":{"language":""}});
|
|
|
});
|
|
|
</script>
|
|
|
</div>
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook.js"></script>
|
|
|
<script src="../gitbook/theme.js"></script>
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook-plugin-book-summary-scroll-position-saver/book-summary-scroll-position-saver.js"></script>
|
|
|
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook-plugin-github/plugin.js"></script>
|
|
|
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook-plugin-search/search-engine.js"></script>
|
|
|
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook-plugin-search/search.js"></script>
|
|
|
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook-plugin-lunr/lunr.min.js"></script>
|
|
|
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook-plugin-lunr/search-lunr.js"></script>
|
|
|
|
|
|
|
|
|
|
|
|
<script src="../gitbook/gitbook-plugin-fontsettings/fontsettings.js"></script>
|
|
|
|
|
|
|
|
|
|
|
|
</body>
|
|
|
</html>
|
|
|
|