版权声明：本文为博主原创文章，转载注明出处http://blog.csdn.net/u013142781
本文适合将w3school的SQL教程（http://www.w3school.com.cn/sql/sql_create_table.asp）都基本看过一遍的猿友阅读。
说说博主的情况吧。毕业找工作之前确实有大概看过w3school的SQL教程，然后参加校园招聘，每次遇到一些SQL笔试题，立马懵逼了（大写的）。其实我那时候大概知道怎么写的，只是总是写不正确，或者是对一些特定的而且没有见过的场景的SQL语句，根本写不出来。相信不少猿友工作之后，其实挺多都用得不熟吧（如果白板编写的话）。
因为大部分Java猿友工作做的事情，其实比较少情况自己去动手写特定场景的SQL（可能有也是百度，接触过一个会一个），简单SQL也是直接由框架（hibernate和Mybatis）提供接口。当然，那种专门做后台，经常跟数据打交道的Java猿友除外，因此只能说大部分。
如果还是继续保持这样的状态的话，下次自己找工作遇到SQL笔试题，估计也会继续懵逼（大写的）。
下面小宝鸽整理了一些实例（实例主要来自网上），以提升自己写SQL的某些关键字的理解。
1、用一条SQL 语句 查询出每门课都大于80 分的学生姓名。（表结构如下图）
答案可以有如下两种：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> student_name <span class="hljs-keyword">from</span> table_test_one <span class="hljs-keyword">where</span> student_name <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> student_name <span class="hljs-keyword">from</span> table_test_one <span class="hljs-keyword">where</span> score<=<span class="hljs-number">80</span>);</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li></ul>
或者
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">select</span> student_name <span class="hljs-keyword">from</span> table_test_one <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> student_name <span class="hljs-keyword">having</span> <span class="hljs-aggregate">min</span>(score)><span class="hljs-number">80</span>;</span></code><ul class="pre-numbering" style="display: block;"><li>1</li></ul>
第二种方法是group by 、min函数 结合 having的使用，w3school教程里面也提到过（在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用）
似乎看懂了，但是还是没有自己运行一遍深刻！！！自己能动手敲一遍就更好了！
下面我们自己造数据，后面的例子也会用到。
建表然后倒入初始数据：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> <span class="hljs-string">`table_test_one`</span>;</span> <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`table_test_one`</span> ( <span class="hljs-string">`id`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`student_no`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-string">`student_name`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-string">`subject_no`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-string">`subject_name`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-string">`score`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>) ) ENGINE=InnoDB AUTO_INCREMENT=<span class="hljs-number">8</span> <span class="hljs-keyword">DEFAULT</span> CHARSET=utf8;</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li></ul>
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_one`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>, <span class="hljs-string">'201601'</span>, <span class="hljs-string">'张三'</span>, <span class="hljs-string">'0001'</span>, <span class="hljs-string">'数学'</span>, <span class="hljs-string">'98'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_one`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2'</span>, <span class="hljs-string">'201601'</span>, <span class="hljs-string">'张三'</span>, <span class="hljs-string">'0002'</span>, <span class="hljs-string">'语文'</span>, <span class="hljs-string">'66'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_one`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'3'</span>, <span class="hljs-string">'201602'</span>, <span class="hljs-string">'李四'</span>, <span class="hljs-string">'0001'</span>, <span class="hljs-string">'数学'</span>, <span class="hljs-string">'60'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_one`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'4'</span>, <span class="hljs-string">'201602'</span>, <span class="hljs-string">'李四'</span>, <span class="hljs-string">'0003'</span>, <span class="hljs-string">'英语'</span>, <span class="hljs-string">'78'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_one`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'5'</span>, <span class="hljs-string">'201603'</span>, <span class="hljs-string">'王五'</span>, <span class="hljs-string">'0001'</span>, <span class="hljs-string">'数学'</span>, <span class="hljs-string">'99'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_one`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'6'</span>, <span class="hljs-string">'201603'</span>, <span class="hljs-string">'王五'</span>, <span class="hljs-string">'0002'</span>, <span class="hljs-string">'语文'</span>, <span class="hljs-string">'99'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_one`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'7'</span>, <span class="hljs-string">'201603'</span>, <span class="hljs-string">'王五'</span>, <span class="hljs-string">'0003'</span>, <span class="hljs-string">'英语'</span>, <span class="hljs-string">'98'</span>);</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li></ul>
可以运行一下上面两个语句试试结果是不是你想要的。
2、删除除了id不同, 其他都相同的学生冗余信息，表如下：
答案：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">delete</span> table_test_one <span class="hljs-keyword">where</span> id <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> <span class="hljs-aggregate">min</span>(id) <span class="hljs-keyword">from</span> table_test_one <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> student_no, student_name, subject_no, subject_name, score);</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li></ul>
是否有看懂？如果没能看懂的话，继续往下看：
先来造数据，题1中的数据只需要执行如下SQL就变成题2中的数据了：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">update</span> table_test_one <span class="hljs-keyword">set</span> subject_no = <span class="hljs-string">'0001'</span>, subject_name = <span class="hljs-string">'数学'</span> <span class="hljs-keyword">where</span> id = <span class="hljs-number">6</span>;</span></code><ul class="pre-numbering" style="display: block;"><li>1</li></ul>
然后我们先执行这个看看：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-aggregate">min</span>(id) <span class="hljs-keyword">from</span> table_test_one <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> student_no, student_name, subject_no, subject_name, score</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li></ul>
这个的执行结果如下：
如果还不懂就再看看几次吧。
PS：GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。刚刚就是GROUP BY 对多列的使用场景。
3、行转列：
表数据如下：
希望查询到结果如下：
答案：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">year</span>, (<span class="hljs-keyword">select</span> amount <span class="hljs-keyword">from</span> table_test_two t <span class="hljs-keyword">where</span> t.<span class="hljs-keyword">month</span> = <span class="hljs-number">1</span> <span class="hljs-keyword">and</span> t.<span class="hljs-keyword">year</span> = table_test_two.<span class="hljs-keyword">year</span>) <span class="hljs-keyword">as</span> month1, (<span class="hljs-keyword">select</span> amount <span class="hljs-keyword">from</span> table_test_two t <span class="hljs-keyword">where</span> t.<span class="hljs-keyword">month</span> = <span class="hljs-number">2</span> <span class="hljs-keyword">and</span> t.<span class="hljs-keyword">year</span> = table_test_two.<span class="hljs-keyword">year</span>) <span class="hljs-keyword">as</span> month2, (<span class="hljs-keyword">select</span> amount <span class="hljs-keyword">from</span> table_test_two t <span class="hljs-keyword">where</span> t.<span class="hljs-keyword">month</span> = <span class="hljs-number">3</span> <span class="hljs-keyword">and</span> t.<span class="hljs-keyword">year</span> = table_test_two.<span class="hljs-keyword">year</span>) <span class="hljs-keyword">as</span> month3 <span class="hljs-keyword">from</span> table_test_two <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">year</span>;</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li></ul>
利用group by 实现行转列，这种场景在数据统计的时候经常用到。
猿友可以造数据自己运行试试：
<code class="hljs sql has-numbering"><span class="hljs-comment">-- ----------------------------</span> <span class="hljs-comment">-- Table structure for `table_test_two`</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> <span class="hljs-string">`table_test_two`</span>;</span> <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`table_test_two`</span> ( <span class="hljs-string">`year`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-string">`month`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-string">`amount`</span> <span class="hljs-keyword">decimal</span>(<span class="hljs-number">10</span>,<span class="hljs-number">1</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`year`</span>,<span class="hljs-string">`month`</span>,<span class="hljs-string">`amount`</span>) ) ENGINE=InnoDB <span class="hljs-keyword">DEFAULT</span> CHARSET=latin1;</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-comment">-- Records of table_test_two</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_two`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1991'</span>, <span class="hljs-string">'1'</span>, <span class="hljs-string">'1.1'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_two`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1991'</span>, <span class="hljs-string">'2'</span>, <span class="hljs-string">'1.2'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_two`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1991'</span>, <span class="hljs-string">'3'</span>, <span class="hljs-string">'1.3'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_two`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1992'</span>, <span class="hljs-string">'1'</span>, <span class="hljs-string">'2.1'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_two`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1992'</span>, <span class="hljs-string">'2'</span>, <span class="hljs-string">'2.2'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_two`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1992'</span>, <span class="hljs-string">'3'</span>, <span class="hljs-string">'2.3'</span>);</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li></ul>
4、复制表( 只复制结构, 源表名：table_test_two 新表名：table_test_three)
答案：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> table_test_three <span class="hljs-keyword">as</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table_test_two <span class="hljs-keyword">where</span> <span class="hljs-number">1</span>=<span class="hljs-number">2</span>;</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li></ul>
PS：如果需要将数据也复制过去，则上面改成where 1=1
5、复制表数据（将表 table_test_two 的数据复制到表table_test_three 里面）
答案：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> table_test_three (<span class="hljs-keyword">year</span>,<span class="hljs-keyword">month</span>,amount) <span class="hljs-keyword">select</span> <span class="hljs-keyword">year</span>,<span class="hljs-keyword">month</span>,amount <span class="hljs-keyword">from</span> table_test_two;</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li></ul>
6、两张关联表，删除主表中已经在副表中没有的信息
答案：
<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> table_test_student <span class="hljs-keyword">where</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> (<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table_test_class <span class="hljs-keyword">where</span> table_test_student.class_id = table_test_class.calss_id);</span> </code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li></ul>
我们先造点数据吧：
<code class="hljs sql has-numbering"><span class="hljs-comment">-- ----------------------------</span> <span class="hljs-comment">-- Table structure for `table_test_class`</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> <span class="hljs-string">`table_test_class`</span>;</span> <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`table_test_class`</span> ( <span class="hljs-string">`calss_id`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`calss_name`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">CHARACTER</span> <span class="hljs-keyword">SET</span> utf8 <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`calss_id`</span>) ) ENGINE=InnoDB AUTO_INCREMENT=<span class="hljs-number">2</span> <span class="hljs-keyword">DEFAULT</span> CHARSET=latin1;</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-comment">-- Records of table_test_class</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_class`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>, <span class="hljs-string">'一班'</span>);</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li></ul>
<code class="hljs sql has-numbering"><span class="hljs-comment">-- ----------------------------</span> <span class="hljs-comment">-- Table structure for `table_test_student`</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> <span class="hljs-string">`table_test_student`</span>;</span> <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`table_test_student`</span> ( <span class="hljs-string">`student_id`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`student_name`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">CHARACTER</span> <span class="hljs-keyword">SET</span> utf8 <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-string">`class_id`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>, <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`student_id`</span>) ) ENGINE=InnoDB AUTO_INCREMENT=<span class="hljs-number">3</span> <span class="hljs-keyword">DEFAULT</span> CHARSET=latin1;</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-comment">-- Records of table_test_student</span> <span class="hljs-comment">-- ----------------------------</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_student`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>, <span class="hljs-string">'罗国辉'</span>, <span class="hljs-string">'1'</span>);</span> <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`table_test_student`</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2'</span>, <span class="hljs-string">'小宝鸽'</span>, <span class="hljs-string">'2'</span>);</span></code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li></ul>
执行后数据如下：
显然副表student中小宝鸽这条数据的calss_id，主表没有对应的class_id.
执行对应SQL语句就会把小宝鸽这条数据删除掉了。
9534
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
