时间:2021-07-01 10:21:17 帮助过:10人阅读
- <span style="color: #008080">/*</span><span style="color: #008080">*
- 创建Employees员工表
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">USE</span><span style="color: #000000"> TSQL2012
- </span><span style="color: #0000ff">IF</span> <span style="color: #ff00ff">OBJECT_ID</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.Employees</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">U</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">IS</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span>
- <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Employees
- </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Employees
- (
- empid </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- firstname </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- lastname </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- hiredate DATE </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- mgrid </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- ssn </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- salary </span><span style="color: #0000ff">MONEY</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
- )
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 添加主键约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> PK_Employees
- </span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">(empid);
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 添加唯一约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> UNQ_Employees_ssn
- </span><span style="color: #0000ff">UNIQUE</span><span style="color: #000000">(ssn);
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 添加外键约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> FK_Employees_Employees
- </span><span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(mgrid) <span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> dbo.Employees(empid);
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 添加CHECK约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> CHK_Employees_salary
- </span><span style="color: #0000ff">CHECK</span>(salary<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000">)
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 删除CHECK约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">CONSTRAINT</span> CHK_Employees_salary
2.Orders订单表
- <span style="color: #008080">/*</span><span style="color: #008080">*
- 创建Orders订单表
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">USE</span><span style="color: #000000"> TSQL2012
- </span><span style="color: #0000ff">IF</span> <span style="color: #ff00ff">OBJECT_ID</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.Orders</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">U</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">IS</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Orders;
- </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Orders
- (
- orderid </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- empid </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- custid </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- orderts DATETIME2 </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- qty </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #0000ff">CONSTRAINT</span> PK_Orders <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">(orderid)
- );
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 添加主键约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Orders <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span> FK_Orders_Employees <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">(empid)
- </span><span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> dbo.Employees(empid);
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 添加默认约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Orders <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> DFT_Orders_orderts
- </span><span style="color: #0000ff">DEFAULT</span>(SYSDATETIME()) <span style="color: #0000ff">FOR</span><span style="color: #000000"> orderts;
- </span><span style="color: #008080">/*</span><span style="color: #008080">*
- 删除CHECK约束
- *</span><span style="color: #008080">*/</span>
- <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">CONSTRAINT</span> DFT_Orders_orderts
SQL Server(第一章) 创建表 删除表 创建主键约束、唯一约束、外键约束、CHECK约束、默认约束
标签:int pos creat gpo ble 删除 time arc 主键约束