数据库建表操作SQL代码大全
|
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 |
新建表： <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> [表名] ( [自动编号字段] <span class="hljs-built_in">int</span> <span class="hljs-keyword">IDENTITY</span> (<span class="hljs-number">1</span>,<span class="hljs-number">1</span>) PRIMARY <span class="hljs-keyword">KEY</span> , [字段<span class="hljs-number">1</span>] <span class="hljs-keyword">nVarChar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">default</span> <span class="hljs-string">'默认值'</span> <span class="hljs-literal">null</span> , [字段<span class="hljs-number">2</span>] ntext <span class="hljs-literal">null</span> , [字段<span class="hljs-number">3</span>] datetime, [字段<span class="hljs-number">4</span>] money <span class="hljs-literal">null</span> , [字段<span class="hljs-number">5</span>] <span class="hljs-built_in">int</span> <span class="hljs-keyword">default</span> <span class="hljs-number">0</span>, [字段<span class="hljs-number">6</span>] <span class="hljs-built_in">Decimal</span> (<span class="hljs-number">12</span>,<span class="hljs-number">4</span>) <span class="hljs-keyword">default</span> <span class="hljs-number">0</span>, [字段<span class="hljs-number">7</span>] image <span class="hljs-literal">null</span> , ) 删除表： <span class="hljs-keyword">Drop</span> <span class="hljs-keyword">table</span> [表名] 插入数据： <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> [表名] (字段<span class="hljs-number">1</span>,字段<span class="hljs-number">2</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">100</span>,<span class="hljs-string">'51WINDOWS.NET'</span>) 删除数据： <span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> [表名] <span class="hljs-keyword">WHERE</span> [字段名]><span class="hljs-number">100</span> 更新数据： <span class="hljs-keyword">UPDATE</span> [表名] <span class="hljs-keyword">SET</span> [字段<span class="hljs-number">1</span>] = <span class="hljs-number">200</span>,[字段<span class="hljs-number">2</span>] = <span class="hljs-string">'51WINDOWS.NET'</span> <span class="hljs-keyword">WHERE</span> [字段三] = <span class="hljs-string">'HAIWA'</span> 新增字段： <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [表名] <span class="hljs-keyword">ADD</span> [字段名] <span class="hljs-keyword">NVARCHAR</span> (<span class="hljs-number">50</span>) <span class="hljs-literal">NULL</span> 删除字段： <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [表名] <span class="hljs-keyword">DROP</span> <span class="hljs-keyword">COLUMN</span> [字段名] 修改字段： <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [表名] <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">COLUMN</span> [字段名] <span class="hljs-keyword">NVARCHAR</span> (<span class="hljs-number">50</span>) <span class="hljs-literal">NULL</span> 重命名表：(<span class="hljs-keyword">Access</span> 重命名表，请参考文章：在<span class="hljs-keyword">Access</span>数据库中重命名表) sp_rename <span class="hljs-string">'表名'</span>, <span class="hljs-string">'新表名'</span>, <span class="hljs-string">'OBJECT'</span> 新建约束： <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [表名] <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">CONSTRAINT</span> 约束名 <span class="hljs-keyword">CHECK</span> ([约束字段] <= <span class="hljs-string">'2000-1-1'</span>) 删除约束： <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [表名] <span class="hljs-keyword">DROP</span> <span class="hljs-keyword">CONSTRAINT</span> 约束名 新建默认值 <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [表名] <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">CONSTRAINT</span> 默认值名 <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">'51WINDOWS.NET'</span> <span class="hljs-keyword">FOR</span> [字段名] 删除默认值 <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [表名] <span class="hljs-keyword">DROP</span> <span class="hljs-keyword">CONSTRAINT</span> 默认值名 删除<span class="hljs-keyword">Sql</span> <span class="hljs-keyword">Server</span> 中的日志，减小数据库文件大小 dump <span class="hljs-keyword">transaction</span> 数据库名 <span class="hljs-keyword">with</span> no_log <span class="hljs-keyword">backup</span> <span class="hljs-keyword">log</span> 数据库名 <span class="hljs-keyword">with</span> no_log dbcc shrinkdatabase(数据库名) exec sp_dboption <span class="hljs-string">'数据库名'</span>, <span class="hljs-string">'autoshrink'</span>, <span class="hljs-string">'true'</span> \<span class="hljs-string">'添加字段通用函数 Sub AddColumn(TableName,ColumnName,ColumnType) Conn.Execute("Alter Table "&TableName&" Add "&ColumnName&" "&ColumnType&"") End Sub \'更改字段通用函数 Sub ModColumn(TableName,ColumnName,ColumnType) Conn.Execute("Alter Table "&TableName&" Alter Column "&ColumnName&" "&ColumnType&"") End Sub \'检查表是否存在 sql="select count(*) as dida from sysobjects where id = object_id(N'</span>[所有者].[表名]<span class="hljs-string">') and OBJECTPROPERTY(id, N'</span>IsUserTable<span class="hljs-string">') = 1" set rs=conn.execute(sql) response.write rs("dida")'</span>返回一个数值，<span class="hljs-number">0</span>代表没有，<span class="hljs-number">1</span>代表存在 判断表的存在: <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sysobjects <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span> = object_id(<span class="hljs-keyword">N</span><span class="hljs-string">'[dbo].[tablename]'</span>) <span class="hljs-keyword">and</span> OBJECTPROPERTY(<span class="hljs-keyword">id</span>, <span class="hljs-keyword">N</span><span class="hljs-string">'IsUserTable'</span>) = <span class="hljs-number">1</span> 某个表的结构 <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> syscolumns <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span> = object_id(<span class="hljs-keyword">N</span><span class="hljs-string">'[dbo].[你的表名]'</span>) <span class="hljs-keyword">and</span> OBJECTPROPERTY(<span class="hljs-keyword">id</span>, <span class="hljs-keyword">N</span><span class="hljs-string">'IsUserTable'</span>) = <span class="hljs-number">1</span> </span> |
支付宝扫一扫
