-
Notifications
You must be signed in to change notification settings - Fork 0
/
26960.html
310 lines (301 loc) · 37.9 KB
/
26960.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>MySQL常用 | 征蓬</title><meta name="keywords" content="MySQL"><meta name="author" content="huy"><meta name="copyright" content="huy"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="整理常用的查询语句、函数等。">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL常用">
<meta property="og:url" content="http://example.com/26960.html">
<meta property="og:site_name" content="征蓬">
<meta property="og:description" content="整理常用的查询语句、函数等。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://example.com/pics/scenery/29.jpg">
<meta property="article:published_time" content="2021-08-17T01:46:37.000Z">
<meta property="article:modified_time" content="2022-03-08T07:56:41.253Z">
<meta property="article:author" content="huy">
<meta property="article:tag" content="MySQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://example.com/pics/scenery/29.jpg"><link rel="shortcut icon" href="/pics/avatar/8.png"><link rel="canonical" href="http://example.com/26960"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://lf3-cdn-tos.bytecdntp.com/cdn/expire-1-M/node-snackbar/0.1.16/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="/font/wcsf.ttf" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = {
root: '/',
algolia: undefined,
localSearch: {"path":"search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
translate: undefined,
noticeOutdate: undefined,
highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
copy: {
success: '复制成功',
error: '复制错误',
noSupport: '浏览器不支持'
},
relativeDate: {
homepage: false,
post: false
},
runtime: '',
date_suffix: {
just: '刚刚',
min: '分钟前',
hour: '小时前',
day: '天前',
month: '个月前'
},
copyright: undefined,
lightbox: 'fancybox',
Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"bottom-left"},
source: {
jQuery: 'https://lf26-cdn-tos.bytecdntp.com/cdn/expire-1-M/jquery/3.6.0/jquery.min.js',
justifiedGallery: {
js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
},
fancybox: {
js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
}
},
isPhotoFigcaption: false,
islazyload: false,
isanchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
title: 'MySQL常用',
isPost: true,
isHome: false,
isHighlightShrink: false,
isToc: true,
postUpdate: '2022-03-08 15:56:41'
}</script><noscript><style type="text/css">
#nav {
opacity: 1
}
.justified-gallery img {
opacity: 1
}
#recent-posts time,
#post-meta time {
display: inline !important
}
</style></noscript><script>(win=>{
win.saveToLocal = {
set: function setWithExpiry(key, value, ttl) {
if (ttl === 0) return
const now = new Date()
const expiryDay = ttl * 86400000
const item = {
value: value,
expiry: now.getTime() + expiryDay,
}
localStorage.setItem(key, JSON.stringify(item))
},
get: function getWithExpiry(key) {
const itemStr = localStorage.getItem(key)
if (!itemStr) {
return undefined
}
const item = JSON.parse(itemStr)
const now = new Date()
if (now.getTime() > item.expiry) {
localStorage.removeItem(key)
return undefined
}
return item.value
}
}
win.getScript = url => new Promise((resolve, reject) => {
const script = document.createElement('script')
script.src = url
script.async = true
script.onerror = reject
script.onload = script.onreadystatechange = function() {
const loadState = this.readyState
if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
script.onload = script.onreadystatechange = null
resolve()
}
document.head.appendChild(script)
})
win.activateDarkMode = function () {
document.documentElement.setAttribute('data-theme', 'dark')
if (document.querySelector('meta[name="theme-color"]') !== null) {
document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
}
}
win.activateLightMode = function () {
document.documentElement.setAttribute('data-theme', 'light')
if (document.querySelector('meta[name="theme-color"]') !== null) {
document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
}
}
const t = saveToLocal.get('theme')
if (t === 'dark') activateDarkMode()
else if (t === 'light') activateLightMode()
const asideStatus = saveToLocal.get('aside-status')
if (asideStatus !== undefined) {
if (asideStatus === 'hide') {
document.documentElement.classList.add('hide-aside')
} else {
document.documentElement.classList.remove('hide-aside')
}
}
const detectApple = () => {
if (GLOBAL_CONFIG_SITE.isHome && /iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
document.documentElement.classList.add('apple')
}
}
detectApple()
})(window)</script><link rel="stylesheet" href="/css/custom.css" media="defer" onload="this.media='all'"><!-- hexo injector head_end start --><link rel="stylesheet" href="https://npm.elemecdn.com/hexo-butterfly-tag-plugins-plus@latest/lib/assets/font-awesome-animation.min.css" media="defer" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hexo-butterfly-tag-plugins-plus@latest/lib/tag_plugins.min.css" media="defer" onload="this.media='all'"><script async src="https://npm.elemecdn.com/hexo-butterfly-tag-plugins-plus@latest/lib/assets/carousel-touch.js"></script><!-- hexo injector head_end end --><meta name="generator" content="Hexo 5.4.1"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/pics/Asoul/8.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">54</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">17</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">12</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('/pics/scenery/29.jpg')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">征蓬</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">MySQL常用</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-08-17T01:46:37.000Z" title="发表于 2021-08-17 09:46:37">2021-08-17</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2022-03-08T07:56:41.253Z" title="更新于 2022-03-08 15:56:41">2022-03-08</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/MySQL/">MySQL</a></span></div><div class="meta-secondline"></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><blockquote>
<p><a target="_blank" rel="noopener" href="https://www.docs4dev.com/docs/zh/mysql/5.7/reference/">MySQL 中文文档 5.7</a></p>
<p><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/5.7/en/">MySQL 官方文档 5.7</a></p>
<p><a target="_blank" rel="noopener" href="http://mysql.taobao.org/monthly/">阿里数据库内核月报</a></p>
</blockquote>
<h2 id="索引">索引</h2>
<h3 id="重建索引">重建索引</h3>
<p>MySQL 5.7 常用的重建索引方式有以下三种：</p>
<ul>
<li>
<p>Dump and Reload</p>
</li>
<li>
<p>ALTER TABLE</p>
</li>
<li>
<p>REPAIR TABLE</p>
</li>
</ul>
<blockquote>
<p>详情参考官方或者中文文档的 2.11.12 Rebuilding or Repairing Tables or Indexes。</p>
</blockquote>
<h4 id="Dump-and-Reload">Dump and Reload</h4>
<p>使用的是 导出、重新导入 的方法重建表，同时索引也会被重置：</p>
<ol>
<li>
<p>重建某个表，需要指定表名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysqldump db_name table1 <span class="operator">></span> dump.sql</span><br><span class="line"></span><br><span class="line">mysql db_name <span class="operator"><</span> dump.sql</span><br></pre></td></tr></table></figure>
</li>
<li>
<p>重建单个数据库中全部表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysqldump db_name <span class="operator">></span> dump.sql</span><br><span class="line"></span><br><span class="line">mysql db_name <span class="operator"><</span> dump.sql</span><br></pre></td></tr></table></figure>
</li>
<li>
<p>重建全部数据库中的全部表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysqldump <span class="comment">--all-database > dump.sql</span></span><br><span class="line"></span><br><span class="line">mysql <span class="operator"><</span> dump.sql</span><br></pre></td></tr></table></figure>
</li>
</ol>
<h4 id="ALTER-TABLE">ALTER TABLE</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> tbl_user_info ENGINE <span class="operator">=</span> INNODB;</span><br></pre></td></tr></table></figure>
<p>通过修改存储引擎的方法重建索引，本质和 重建表 没有区别：</p>
<ol>
<li>
<p>在执行命令前，查询表创建时间</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line"> table_name,</span><br><span class="line"> create_time </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line"> information_schema.TABLES </span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line"> table_name <span class="operator">=</span> <span class="string">'tbl_user_info'</span>;</span><br></pre></td></tr></table></figure>
<p>查询结果如下：</p>
<table>
<tr>
<th>table_name</th>
<th>create_time</th>
</tr>
<tr>
<td>tbl_user_info</td>
<td>2021-08-18 09:21:31</td>
</tr>
</table>
</li>
<li>
<p>执行命令后再次查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> tbl_user_info ENGINE <span class="operator">=</span> INNODB;</span><br></pre></td></tr></table></figure>
<p>查询结果如下：</p>
<table>
<tr>
<th>table_name</th>
<th>create_time</th>
</tr>
<tr>
<td>tbl_user_info</td>
<td>2021-08-18 10:45:30</td>
</tr>
</table>
</li>
</ol>
<p>因为索引创建时间并不可能比表创建时间早，因此该方式可以成功重建索引。</p>
<h4 id="REPAIR-TABLE">REPAIR TABLE</h4>
<p>该方法只能修复引擎为 MyISAM, ARCHIVE, CSV 的表。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">REPAIR table1</span><br></pre></td></tr></table></figure>
<h3 id="查看索引基数">查看索引基数</h3>
<p>索引基数（cardinality），表示一个索引上不同值的个数。</p>
<article class="message is-info"><div class="message-header">
<p>举例：</p>
</div><div class="message-body">
<p>假设表 t 有 1w 条记录，在字段 a 上建了索引。<br>
<br><br>如果 a 全都是 1，那 1w 条记录的 a 都是相同的，<b>索引基数很低（只有1）</b>。<br>
<br><br>如果 a 从 1 ~ 1w，每个值都不同，<b>索引基数很高（1w）</b>。</p>
</div></article>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> index <span class="keyword">FROM</span> TABLE_NAME</span><br></pre></td></tr></table></figure>
<h3 id="重新统计索引信息">重新统计索引信息</h3>
<p>重新填统计索引信息命令，解决采样导致的扫描行数出错的问题。</p>
<p>如果发现 explan 命令结果的 rows 值和实际情况相差较大，可以使用该命令重置。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">analyze <span class="keyword">TABLE</span> TABLE_NAME</span><br></pre></td></tr></table></figure>
<h2 id="常用查询">常用查询</h2>
<h3 id="时间相关">时间相关</h3>
<p><a target="_blank" rel="noopener" href="https://www.w3school.com.cn/sql/func_date_format.asp">DATE_FORMAT() 函数参数</a></p>
<p>常用的查询时间函数为</p>
<ul>
<li>
<p><code>CURDATE()</code></p>
<p>只能查询当天开始的时间点，默认格式为 2021-08-18 (%Y-%m-%d)。</p>
<p>可以使用 DATE_FORMAT() 函数格式化，例如 DATE_FORMAT(CURDATE(),‘%Y-%m-%d %H:%i:%s’)，则查询结果为 2021-08-18 00:00:00。</p>
</li>
<li>
<p><code>NOW()</code></p>
<p>查询的是当前时间点，默认格式为 2021-08-18 11:26:06 (%Y-%m-%d %H:%i:%s)。</p>
</li>
</ul>
<h4 id="当天">当天</h4>
<ol>
<li>当前时间<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NOW(); <span class="comment">-- 查询结果 2021-08-18 11:26:06</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> CURDATE(); <span class="comment">-- 查询结果 2021-08-18</span></span><br></pre></td></tr></table></figure>
</li>
<li>当天 0 点<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(CURDATE(),<span class="string">'%Y-%m-%d %H:%i:%s'</span>); <span class="comment">-- 查询结果 2021-08-18 00:00:00</span></span><br></pre></td></tr></table></figure>
</li>
<li>当天 9 点<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_ADD(CURDATE(), <span class="type">INTERVAL</span> <span class="number">9</span> <span class="keyword">HOUR</span>); <span class="comment">-- 查询结果 2021-08-18 09:00:00</span></span><br></pre></td></tr></table></figure>
</li>
</ol>
<h4 id="日、月、年">日、月、年</h4>
<p>这里不能使用 CURDATE() - 1 的方式查询，在月末会出现错误，例如：7月31日使用，得到的结果为 8月0日。</p>
<ol>
<li>日<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 前一天</span></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( curdate( ), <span class="type">INTERVAL</span> <span class="number">1</span> <span class="keyword">DAY</span> ); <span class="comment">-- 查询结果 2021-08-17</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 当天</span></span><br><span class="line"><span class="keyword">SELECT</span> CURDATE(); <span class="comment">-- 查询结果 2021-08-18</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 后一天</span></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( curdate( ), <span class="type">INTERVAL</span> <span class="number">-1</span> <span class="keyword">DAY</span> ); <span class="comment">-- 查询结果 2021-08-19</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询七天之前的数据</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> 表名 <span class="keyword">WHERE</span> DATE_ADD( createdate, <span class="type">INTERVAL</span> <span class="number">7</span> <span class="keyword">DAY</span> ) <span class="operator"><</span> NOW( );</span><br></pre></td></tr></table></figure>
</li>
<li>月<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 前一月</span></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( curdate( ), <span class="type">INTERVAL</span> <span class="number">1</span> <span class="keyword">MONTH</span> ); <span class="comment">-- 查询结果 2021-07-18</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 后一月</span></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( curdate( ), <span class="type">INTERVAL</span> <span class="number">-1</span> <span class="keyword">MONTH</span> ); <span class="comment">-- 查询结果 2021-09-18</span></span><br></pre></td></tr></table></figure>
</li>
<li>年<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 前一年</span></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( curdate( ), <span class="type">INTERVAL</span> <span class="number">1</span> <span class="keyword">YEAR</span> ); <span class="comment">-- 查询结果 2020-08-18</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 后一年</span></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( curdate( ), <span class="type">INTERVAL</span> <span class="operator">-</span> <span class="number">1</span> <span class="keyword">YEAR</span> ); <span class="comment">-- 查询结果 2022-08-18</span></span><br></pre></td></tr></table></figure>
</li>
</ol>
<h4 id="指定日期">指定日期</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> date_sub( <span class="string">'2017-08-01'</span>, <span class="type">INTERVAL</span> <span class="number">0</span> <span class="keyword">DAY</span> ); <span class="comment">-- 查询结果 2017-08-01</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( <span class="string">'2017-08-01'</span>, <span class="type">INTERVAL</span> <span class="number">1</span> <span class="keyword">DAY</span> ); <span class="comment">-- 查询结果 2017-07-31</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( <span class="string">'2017-08-01'</span>, <span class="type">INTERVAL</span> <span class="number">-1</span> <span class="keyword">DAY</span> ); <span class="comment">-- 查询结果 2017-08-02</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> date_sub( <span class="string">'2017-07-31'</span>, <span class="type">INTERVAL</span> <span class="number">-1</span> <span class="keyword">DAY</span> ); <span class="comment">-- 查询结果 2017-08-01</span></span><br></pre></td></tr></table></figure>
<h3 id="查询结果拼接字段">查询结果拼接字段</h3>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> CONCAT(userName,<span class="string">'('</span>,userAge,<span class="string">')'</span>) <span class="keyword">FROM</span> tbl_user_info;</span><br></pre></td></tr></table></figure>
<h2 id="JOIN">JOIN</h2>
<h3 id="自联结（self-join）">自联结（self join）</h3>
<p>自联结作为外部语句，用来一袋从相同表中检索数据使用的子查询语句。</p>
<p>虽然结果相同，但是许多 DBMS 处理联结的速度要比子查询快。</p>
<p>实际操作中应该试一下两种方法，以确定哪种方法性能更好。</p>
<p>例如：需要获取用户信息表中，和张三在同一个公司的全部用户信息</p>
<ul>
<li>使用子查询 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> user_info </span><br><span class="line"><span class="keyword">WHERE</span> company <span class="operator">=</span> (<span class="keyword">SELECT</span> company <span class="keyword">FROM</span> user_info <span class="keyword">WHERE</span> username <span class="operator">=</span> <span class="string">'张三'</span>);</span><br></pre></td></tr></table></figure>
</li>
<li>使用自联结 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> user_info u1, user_info u2</span><br><span class="line"><span class="keyword">WHERE</span> u1.company <span class="operator">=</span> u2.company </span><br><span class="line"> <span class="keyword">AND</span> u2.username <span class="operator">=</span> <span class="string">'张三'</span>;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="视图">视图</h2>
<p>视图是虚拟的表，只包含使用时动态检索数据的查询。</p>
<p>常见用途：</p>
<ul>
<li>重用 SQL 语句</li>
<li>简化复杂的 SQL 操作</li>
<li>使用表的一部分而不是整个表</li>
<li>保护数据，可以指定用户访问表的特定部分权限</li>
<li>更改数据格式和表示</li>
</ul>
<p>使用规则：</p>
<ul>
<li>名称必须唯一，不能和其他视图或者表重复</li>
<li>视图的数量没有限制</li>
<li>可以嵌套其他视图</li>
<li>不能索引，也不能有关联的触发器或默认值</li>
</ul>
<h2 id="批量插入数据脚本">批量插入数据脚本</h2>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- TRUNCATE TABLE log_info; #清空表数据</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">PROCEDURE</span> IF <span class="keyword">EXISTS</span> proc_init_data; <span class="comment">-- 如果存在此存储过程则删掉</span></span><br><span class="line">DELIMITER $ <span class="comment">-- 使用delimiter后，将不把分号当做语句结束，会将该段整个提交</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> proc_init_data()</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line"> <span class="keyword">DECLARE</span> i <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="number">1</span>;</span><br><span class="line"> WHILE i<span class="operator"><=</span><span class="number">100</span> DO</span><br><span class="line"> <span class="keyword">INSERT</span> <span class="keyword">INTO</span> log_info (`userid`, <span class="type">time</span>) <span class="keyword">VALUES</span> (i, CURDATE());</span><br><span class="line"> <span class="keyword">SET</span> i <span class="operator">=</span> i<span class="operator">+</span><span class="number">1</span>;</span><br><span class="line"> <span class="keyword">END</span> WHILE;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"><span class="keyword">CALL</span> proc_init_data();</span><br></pre></td></tr></table></figure>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">huy</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="http://example.com/26960.html">http://example.com/26960.html</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://example.com" target="_blank">征蓬</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/MySQL/">MySQL</a></div><div class="post_share"><div class="social-share" data-image="/pics/scenery/29.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://jsdelivr.pai233.top/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://jsdelivr.pai233.top/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/11256.html" title="MySQL实战 基础篇（一）"><img class="cover" src="/pics/scenery/06.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-17</div><div class="title">MySQL实战 基础篇（一）</div></div></a></div><div><a href="/48002.html" title="MySQL实战 基础篇（二）"><img class="cover" src="/pics/scenery/22.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-26</div><div class="title">MySQL实战 基础篇（二）</div></div></a></div><div><a href="/47099.html" title="MySQL实战 基础篇（三）"><img class="cover" src="/pics/scenery/29.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-05-12</div><div class="title">MySQL实战 基础篇（三）</div></div></a></div><div><a href="/7044.html" title="MySQL实战 基础篇（五）"><img class="cover" src="/pics/scenery/28.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-07-02</div><div class="title">MySQL实战 基础篇（五）</div></div></a></div><div><a href="/55610.html" title="MySQL实战 基础篇（四）"><img class="cover" src="/pics/scenery/16.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-06-21</div><div class="title">MySQL实战 基础篇（四）</div></div></a></div><div><a href="/51857.html" title="索引补充"><img class="cover" src="/pics/scenery/27.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-09-01</div><div class="title">索引补充</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95"><span class="toc-number">1.</span> <span class="toc-text">索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E9%87%8D%E5%BB%BA%E7%B4%A2%E5%BC%95"><span class="toc-number">1.1.</span> <span class="toc-text">重建索引</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#Dump-and-Reload"><span class="toc-number">1.1.1.</span> <span class="toc-text">Dump and Reload</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#ALTER-TABLE"><span class="toc-number">1.1.2.</span> <span class="toc-text">ALTER TABLE</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#REPAIR-TABLE"><span class="toc-number">1.1.3.</span> <span class="toc-text">REPAIR TABLE</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E7%9C%8B%E7%B4%A2%E5%BC%95%E5%9F%BA%E6%95%B0"><span class="toc-number">1.2.</span> <span class="toc-text">查看索引基数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E9%87%8D%E6%96%B0%E7%BB%9F%E8%AE%A1%E7%B4%A2%E5%BC%95%E4%BF%A1%E6%81%AF"><span class="toc-number">1.3.</span> <span class="toc-text">重新统计索引信息</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2"><span class="toc-number">2.</span> <span class="toc-text">常用查询</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%97%B6%E9%97%B4%E7%9B%B8%E5%85%B3"><span class="toc-number">2.1.</span> <span class="toc-text">时间相关</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%BD%93%E5%A4%A9"><span class="toc-number">2.1.1.</span> <span class="toc-text">当天</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%97%A5%E3%80%81%E6%9C%88%E3%80%81%E5%B9%B4"><span class="toc-number">2.1.2.</span> <span class="toc-text">日、月、年</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%8C%87%E5%AE%9A%E6%97%A5%E6%9C%9F"><span class="toc-number">2.1.3.</span> <span class="toc-text">指定日期</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C%E6%8B%BC%E6%8E%A5%E5%AD%97%E6%AE%B5"><span class="toc-number">2.2.</span> <span class="toc-text">查询结果拼接字段</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#JOIN"><span class="toc-number">3.</span> <span class="toc-text">JOIN</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%87%AA%E8%81%94%E7%BB%93%EF%BC%88self-join%EF%BC%89"><span class="toc-number">3.1.</span> <span class="toc-text">自联结（self join）</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A7%86%E5%9B%BE"><span class="toc-number">4.</span> <span class="toc-text">视图</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%89%B9%E9%87%8F%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE%E8%84%9A%E6%9C%AC"><span class="toc-number">5.</span> <span class="toc-text">批量插入数据脚本</span></a></li></ol></div></div></div></div></main><footer id="footer" style="background-image: url('/pics/scenery/29.jpg')"><div id="footer-wrap"><div class="copyright">©2021 - 2022 By huy</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><div class="search-dialog__title" id="local-search-title">本地搜索</div><div id="local-input-panel"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div></div><hr/><div id="local-search-results"></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="https://lf26-cdn-tos.bytecdntp.com/cdn/expire-1-M/node-snackbar/0.1.16/snackbar.min.js"></script><script src="/js/search/local-search.js"></script><div class="js-pjax"></div><script defer="defer" id="ribbon" src="https://jsdelivr.pai233.top/npm/butterfly-extsrc@1/dist/canvas-ribbon.min.js" size="150" alpha="0.6" zIndex="-1" mobile="false" data-click="false"></script><script id="click-heart" src="https://jsdelivr.pai233.top/npm/butterfly-extsrc@1/dist/click-heart.min.js" async="async" mobile="false"></script></div><!-- hexo injector body_end start --><script async src="//at.alicdn.com/t/font_2032782_8d5kxvn09md.js"></script><!-- hexo injector body_end end --></body></html>