Demo entry 6741934

vswer

   

Submitted by he on May 17, 2018 at 04:48
Language: HTML. Code size: 7.3 kB.

<link rel='stylesheet' type='text/css' href='http://tools.oschina.net/js/syntaxhighlighter_3.0.83/styles/shCoreDefault.css'/><div id="highlighter_766829" class="syntaxhighlighter  sql"><div class="toolbar"><span><a href="#" class="toolbar_item command_help help">?</a></span></div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="gutter"><div class="line number1 index0 alt2">1</div><div class="line number2 index1 alt1">2</div><div class="line number3 index2 alt2">3</div><div class="line number4 index3 alt1">4</div><div class="line number5 index4 alt2">5</div><div class="line number6 index5 alt1">6</div><div class="line number7 index6 alt2">7</div><div class="line number8 index7 alt1">8</div><div class="line number9 index8 alt2">9</div><div class="line number10 index9 alt1">10</div><div class="line number11 index10 alt2">11</div><div class="line number12 index11 alt1">12</div><div class="line number13 index12 alt2">13</div><div class="line number14 index13 alt1">14</div><div class="line number15 index14 alt2">15</div><div class="line number16 index15 alt1">16</div><div class="line number17 index16 alt2">17</div></td><td class="code"><div class="container"><div class="line number1 index0 alt2"><code class="sql plain">4.&nbsp;将“ssex”字段设置为只能取“男”,“女”两值:</code></div><div class="line number2 index1 alt1"><code class="sql plain">当“student”表已存在则执行:</code></div><div class="line number3 index2 alt2"><code class="sql keyword">alter</code>&nbsp;<code class="sql keyword">table</code>&nbsp;<code class="sql plain">student&nbsp;</code><code class="sql keyword">add</code>&nbsp;<code class="sql keyword">constraint</code>&nbsp;<code class="sql plain">CK_Sex&nbsp;</code><code class="sql keyword">check</code>&nbsp;<code class="sql plain">(ssex&nbsp;</code><code class="sql color1">in</code>&nbsp;<code class="sql plain">(</code><code class="sql string">'男'</code>&nbsp;<code class="sql plain">,</code><code class="sql string">'女'</code><code class="sql plain">))</code></div><div class="line number4 index3 alt1"><code class="sql plain">当“student”表不存在则执行:</code></div><div class="line number5 index4 alt2"><code class="sql keyword">Create</code>&nbsp;<code class="sql keyword">table</code>&nbsp;<code class="sql plain">student(sno&nbsp;</code><code class="sql keyword">CHAR</code><code class="sql plain">(10)&nbsp;</code><code class="sql keyword">primary</code>&nbsp;<code class="sql keyword">key</code>&nbsp;<code class="sql plain">,&nbsp;</code></div><div class="line number6 index5 alt1"><code class="sql plain">sname&nbsp;</code><code class="sql keyword">CHAR</code><code class="sql plain">(10),</code></div><div class="line number7 index6 alt2"><code class="sql plain">ssex&nbsp;&nbsp;</code><code class="sql keyword">CHAR</code><code class="sql plain">(2)&nbsp;</code><code class="sql keyword">check</code>&nbsp;<code class="sql plain">(ssex&nbsp;</code><code class="sql color1">in</code>&nbsp;<code class="sql plain">(</code><code class="sql string">'男'</code>&nbsp;<code class="sql plain">,</code><code class="sql string">'女'</code><code class="sql plain">))&nbsp;,</code></div><div class="line number8 index7 alt1"><code class="sql plain">sage&nbsp;</code><code class="sql keyword">int</code><code class="sql plain">,&nbsp;sdept&nbsp;</code><code class="sql keyword">CHAR</code><code class="sql plain">(4))</code></div><div class="line number9 index8 alt2"><code class="sql plain">5.&nbsp;设置学号字段只能输入数字:</code></div><div class="line number10 index9 alt1"><code class="sql spaces">&nbsp;&nbsp;&nbsp;</code><code class="sql keyword">alter</code>&nbsp;<code class="sql keyword">table</code>&nbsp;<code class="sql plain">student&nbsp;</code><code class="sql keyword">add</code>&nbsp;<code class="sql keyword">constraint</code>&nbsp;<code class="sql plain">CK_Sno_Format&nbsp;</code><code class="sql keyword">check</code>&nbsp;<code class="sql plain">(sno&nbsp;</code><code class="sql color1">like</code>&nbsp;<code class="sql string">'[0-9][0-9][0-9][0-9][0-9]'</code><code class="sql plain">)</code></div><div class="line number11 index10 alt2"><code class="sql plain">6.&nbsp;设置身份证号的输入格式:</code></div><div class="line number12 index11 alt1"><code class="sql keyword">alter</code>&nbsp;<code class="sql keyword">table</code>&nbsp;<code class="sql plain">student&nbsp;</code><code class="sql keyword">add</code>&nbsp;<code class="sql keyword">constraint</code>&nbsp;<code class="sql plain">CK_ID_Format&nbsp;</code><code class="sql keyword">check</code>&nbsp;<code class="sql plain">((id&nbsp;</code><code class="sql color1">like</code>&nbsp;<code class="sql string">'[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_'</code><code class="sql plain">)&nbsp;</code><code class="sql color1">OR</code>&nbsp;<code class="sql plain">(id&nbsp;</code><code class="sql color1">like</code>&nbsp;<code class="sql string">'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'</code><code class="sql plain">))</code></div><div class="line number13 index12 alt2"><code class="sql plain">7.&nbsp;设置18位身份证号的第7位到第10位为合法的年份(1900-2050)</code></div><div class="line number14 index13 alt1"><code class="sql keyword">alter</code>&nbsp;<code class="sql keyword">table</code>&nbsp;<code class="sql plain">student&nbsp;</code><code class="sql keyword">add</code>&nbsp;<code class="sql keyword">constraint</code>&nbsp;<code class="sql plain">CK_ID_Format2&nbsp;</code><code class="sql keyword">check</code>&nbsp;<code class="sql plain">(&nbsp;</code><code class="sql color1">not</code>&nbsp;<code class="sql plain">len(id)=18&nbsp;</code><code class="sql color1">or</code>&nbsp;<code class="sql plain">(&nbsp;(</code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">smallint</code><code class="sql plain">,</code><code class="sql color2">substring</code><code class="sql plain">(id,7,4)&nbsp;)&gt;=1900)&nbsp;</code><code class="sql color1">and</code><code class="sql plain">(</code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">smallint</code><code class="sql plain">,</code><code class="sql color2">substring</code><code class="sql plain">(id,7,4)&nbsp;)&lt;=2050))&nbsp;)</code></div><div class="line number15 index14 alt2"><code class="sql plain">三、参照完整性</code></div><div class="line number16 index15 alt1"><code class="sql spaces">&nbsp;&nbsp;&nbsp;</code><code class="sql plain">9.&nbsp;设置男生的年龄必须大于22,&nbsp;女生的年龄必须大于20.</code></div><div class="line number17 index16 alt2"><code class="sql spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code class="sql keyword">Alter</code>&nbsp;<code class="sql keyword">table</code>&nbsp;<code class="sql plain">student&nbsp;</code><code class="sql keyword">add</code>&nbsp;<code class="sql keyword">constraint</code>&nbsp;<code class="sql plain">CK_age&nbsp;</code><code class="sql keyword">check</code>&nbsp;<code class="sql plain">(ssex=</code><code class="sql string">'男'</code>&nbsp;<code class="sql color1">and</code>&nbsp;<code class="sql plain">sage&gt;=22&nbsp;</code><code class="sql color1">or</code>&nbsp;<code class="sql plain">ssex=</code><code class="sql string">'女'</code>&nbsp;<code class="sql color1">and</code>&nbsp;<code class="sql plain">sage&gt;=20&nbsp;)</code></div></div></td></tr></tbody></table></div>

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).