达梦数据库 使用ddl语句新增字段
时间: 2023-07-16 17:15:24 浏览: 333
要使用DDL语句在达梦数据库中新增字段,可以按照以下步骤操作:
1. 使用ALTER TABLE语句修改表结构,添加新字段。例如:
```
ALTER TABLE table_name ADD column_name column_type;
```
其中,table_name为要修改的表名,column_name为新增的字段名,column_type为字段类型。
2. 如果需要设置字段的默认值,可以使用DEFAULT关键字。例如:
```
ALTER TABLE table_name ADD column_name column_type DEFAULT default_value;
```
其中,default_value为字段的默认值。
3. 如果需要设置字段的约束条件(如NOT NULL约束),可以使用约束名称和CONSTRAINT关键字。例如:
```
ALTER TABLE table_name ADD CONSTRAINT constraint_name column_name column_type NOT NULL;
```
其中,constraint_name为约束名称。
4. 执行完ALTER TABLE语句后,需要使用COMMIT语句提交事务,否则修改不会生效。例如:
```
COMMIT;
```
注意:在执行ALTER TABLE语句时,需要有足够的权限才能修改表结构。如果没有权限,需要联系数据库管理员进行授权。
相关问题
达梦数据库使用DDL语句修改varchar长度
可以使用ALTER TABLE语句修改varchar字段的长度,具体语法如下:
```
ALTER TABLE table_name MODIFY column_name varchar(new_length);
```
其中,table_name是要修改的表名,column_name是要修改的字段名,new_length是新的长度值。例如,要将表student中的name字段长度改为50,可以使用以下语句:
```
ALTER TABLE student MODIFY name varchar(50);
```
达梦数据库DDL语句加字段
在达梦数据库中,可以使用DDL语句来给表添加字段。具体的语法为:
```
ALTER TABLE table_name ADD column_name data_type [DEFAULT default_value];
```
其中,`table_name`是要添加字段的表名,`column_name`是要添加的字段名,`data_type`是字段的数据类型,`default_value`是字段的默认值(可选)。
例如,要给名为`employee`的表添加一个名为`email`的新字段,数据类型为`VARCHAR(100)`,默认值为`'-'`,可以使用以下语句:
```
ALTER TABLE employee ADD email VARCHAR(100) DEFAULT '-';
```
执行完毕后,`employee`表就会新增一个名为`email`的字段。
相关推荐
![application/x-rar](https://img-home.csdnimg.cn/images/20210720083606.png)
ddl语言自定义数据库字段
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
<body>
<%
sFileName = "alter_manage.asp"
tableName = "C_alter"
tableName2 = "product"
action = Trim(Request.QueryString("action"))
%>
<%
Select Case action
case ""
Call ShowContent()
case "add"
Call add()
case "addsave"
Call addsave()
case "modify"
Call modify()
case "modifysave"
Call modifysave()
case "delall"
Call delall()
End Select
%>
<%
Sub ShowContent()
Call gRsClose()
sSql = "select * from view_alter"
oRs.Open sSql,oConn,1,1
%>
<Form method="post" id="form1" name="form1">
字 段 信 息 管 理
添加
选择
产品类别
字段类型
字段名称
字段意义
操作
<%
do while not oRs.eof and not oRs.bof
select case oRs("a_type")
case "nvarchar(255)"
a_type = "文本类型"
case "int"
a_type = "整数类型"
case "float"
a_type = "浮点类型"
case else
a_type = "未知类型"
end select
%>
<%Response.Write("<input type='checkbox' id='checkzi' name='checkzi' value='" & oRs("a_id") & "'>" & vbCrlf)%>
<%Response.Write(oRs("c_name"))%>
<%Response.Write(a_type)%>
<%Response.Write(oRs("a_name"))%>
<%Response.Write(oRs("a_detail"))%>
">删除 ">修改
<%
oRs.movenext
loop
%>
<%
Response.Write("<input type='checkbox' id='checkall' name='checkall' onclick='selectAll();'>全/反选" & vbCrlf)
Response.Write("<input type='button' value='删除所选' class='input_submit' onClick=""checkSelect('" & sFileName & "','?action=delall');"">" & vbCrlf)
%>
</form>
<%
Call gRsClose()
End Sub
%>
<%
Sub add()
%>
<Script Language=JavaScript>
function doSubmit(){
if(parseInt(document.getElementById("a_classid").options[document.getElementById("a_classid").selectedIndex].value)==0)
{
alert("请选择产品类型!");
return false;
}
if(parseInt(document.getElementById("a_type").options[document.getElementById("a_type").selectedIndex].value)==0)
{
alert("请选择字段类型!");
return false;
}
if(document.getElementById("a_name").value=="")
{
alert("字段名称不能为空!");
document.getElementById("a_name").select();
return false;
}
if(document.getElementById("a_detail").value=="")
{
alert("请填写该字段的意义!");
document.getElementById("a_detail").select();
return false;
}
}
</script>
<form name="myform" method="post" action="<%=sFileName%>?action=addsave" onSubmit="return doSubmit();">
<input type="hidden" name="Action" value="AddSave">
字 段 信 息
添 加
选择类别:
<%=gMakeClassSelect("a_classid","p_class",0)%>
字段类型:
<label>
<select name="a_type" class="INPUT" id="a_type">
<option value="0" selected>请您选择类型</option>
<option value="int">整数型</option>
<option value="nvarchar(255)">文本型</option>
<option value="float">浮点型</option>
</select>
</label>
字段名称:
<input name="a_name" type="text" id="a_name" size="80" maxlength="50" class="input_text" >
字段意义:
<input name="a_detail" type="text" id="a_detail" size="80" maxlength="50" class="input_text" >
<input type="submit" name="Submit" value=" 提 交 " class="input_submit" >
<input type="reset" name="Submit" value="重 置" class="input_submit">
</form>
<%
End Sub
%>
<%
Sub addsave()
a_classid = GetSafeStr(Trim(Request.Form("a_classid")))
a_name = GetSafeStr(Trim(Request.Form("a_name")))
a_detail = GetSafeStr(Trim(Request.Form("a_detail")))
a_type = GetSafeStr(Trim(Request.Form("a_type")))
a_regtime = now()
'判断主表中要添加的字段是否存在
eSql = "select * from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'"
Call gRsClose()
oRs.Open eSql,oConn,1,3
if not oRs.EOF then
Call ErrorMsg(sFileName,"主表中字段已经存在在,三秒后返回",1)
end if
Call gRsClose()
'将字段相关信息添加到从表
sSql = "select * from "& tableName &" where 1=1"
Call gRsClose()
oRs.Open sSql,oConn,1,3
oRs.AddNew()
oRs("a_classid") = a_classid
oRs("a_name") = a_name
oRs("a_detail") = a_detail
oRs("a_type") = a_type
oRs("a_regtime") = a_regtime
oRs.Update()
'添加字段
oConn.Execute("ALTER TABLE "& tableName2 &" ADD "& a_name &" "& a_type &"")
Call ErrorMsg(sFileName,"添加字段成功,三秒后返回",1)
Call gRsClose()
End Sub
%>
<%
Sub modify()
%>
<Script Language=JavaScript>
function doSubmit(){
if(parseInt(document.getElementById("a_classid").options[document.getElementById("a_classid").selectedIndex].value)==0)
{
alert("请选择产品类型!");
return false;
}
if(parseInt(document.getElementById("a_type").options[document.getElementById("a_type").selectedIndex].value)==0)
{
alert("请选择字段类型!");
return false;
}
if(document.getElementById("a_name").value=="")
{
alert("字段名称不能为空!");
document.getElementById("a_name").select();
return false;
}
if(document.getElementById("a_detail").value=="")
{
alert("请填写该字段的意义!");
document.getElementById("a_detail").select();
return false;
}
}
</script>
<%
Call gRsClose()
a_id = Trim(Request.QueryString("a_id"))
sSql = "select * from view_alter where a_id = "& a_id
oRs.Open sSql,oConn,1,1
If not oRs.eof and not oRs.bof Then
a_classid = oRs("a_classid")
a_name = oRs("a_name")
a_detail = oRs("a_detail")
a_type = oRs("a_type")
End If
Call gRsClose()
%>
<form name="myform" method="post" action="<%=sFileName%>?action=modifysave&a_id=<%=a_id%>" onSubmit="return doSubmit();">
<input type="hidden" name="Action" value="AddSave">
字 段 信 息
添 加
选择类别:
<%=gMakeClassSelect("a_classid","p_class",a_classid)%>
字段类型:
<label>
<select name="a_type" class="INPUT" id="a_type">
<option value="0" <%If a_type="" Then Response.Write("selected")%>>请您选择类型</option>
<option value="int" <%If a_type="int" Then Response.Write("selected")%>>整数型</option>
<option value="nvarchar(255)" <%If a_type="nvarchar(255)" Then Response.Write("selected")%>>文本型</option>
<option value="float" <%If a_type="float" Then Response.Write("selected")%>>浮点型</option>
</select>
</label>
字段名称:
<input name="a_name" type="text" class="input_text" id="a_name" value="<%=a_name%>" size="80" maxlength="50" >
字段意义:
<input name="a_detail" type="text" class="input_text" id="a_detail" value="<%=a_detail%>" size="80" maxlength="50" >
<input type="submit" name="Submit" value=" 修 改 " class="input_submit" >
<input type="reset" name="Submit" value="重 置" class="input_submit">
</form>
<%
End Sub
%>
<%
Sub modifysave()
a_classid = GetSafeStr(Trim(Request.Form("a_classid")))
a_name = GetSafeStr(Trim(Request.Form("a_name")))
a_detail = GetSafeStr(Trim(Request.Form("a_detail")))
a_type = GetSafeStr(Trim(Request.Form("a_type")))
a_regtime = now()
a_id = Trim(Request.QueryString("a_id"))
'将字段相关信息添加到从表
sSql = "select * from "& tableName &" where a_id = "& a_id
Call gRsClose()
oRs.Open sSql,oConn,1,3
aname = oRs("a_name")
atype = oRs("a_type")
on error resume next
oRs("a_classid") = a_classid
'判断主表中是否有相同的字段(保持主从表中的数据一致)
set rs1=oConn.Execute("select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'")
If rs1.eof and rs1.bof Then
oRs("a_name") = a_name
End If
rs1.close()
oRs("a_detail") = a_detail
oRs("a_type") = a_type
oRs.Update()
If a_name<>aname Then
'修改字段名称(判断主表中是否有相同的字段)
oConn.Execute("if not exists(select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"') EXEC sp_rename '"& tableName2 &".["& aname &"]', '"& a_name &"', 'COLUMN'")
End If
If a_type<>atype Then
'修改字段类型
oConn.Execute("ALTER TABLE "& tableName2 &" ALTER COLUMN "& a_name &" "& a_type &"")
End If
Call ErrorMsg(sFileName,"修改字段成功,三秒后返回",1)
Call gRsClose()
End Sub
%>
<%
Sub delall()
checkzi = Trim(Request("checkzi"))
If checkzi <> "" Then
Dim z1
z1 = tableName
Dim temp_id,last_id
last_id="("
temp_id = Split(Request("checkzi"),",")
For i=0 To UBound(temp_id)
last_id = last_id & Trim(temp_id(i)) & ","
Next
last_id = Left(last_id,Len(last_id)-1)
last_id = last_id & ")"
'last_id为最终要删除的记录ID
'取出要删除的对应字段
sSql = "select a_name from "& z1 &" where a_id in " & last_id
Call gRsClose()
oRs.Open sSql,oConn,1,1
dim a_names
a_names = ""
jj=1
do while not oRs.eof and not oRs.bof
If jj <> oRs.recordcount Then
a_names = a_names & oRs("a_name") &","
Else
a_names = a_names & oRs("a_name")
End If
oRs.movenext
jj = jj + 1
loop
Call gRsClose()
'删除主表对应字段
a_name = Split(a_names,",")
for i=0 to Ubound(a_name)
oConn.Execute("ALTER TABLE "& tableName2 &" DROP COLUMN "& a_name(i) &"")
next
'删除从表中字段的意义信息
sSql = "delete from " & z1 & " where a_id in " & last_id
oConn.Execute(sSql)
Call ErrorMsg(sFileName,"成功删除所选记录,三秒后将返回……",1)
End If
End Sub
%>
</body>
</html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
<body>
<%
sFileName = "alter_manage.asp"
tableName = "C_alter"
tableName2 = "product"
action = Trim(Request.QueryString("action"))
%>
<%
Select Case action
case ""
Call ShowContent()
case "add"
Call add()
case "addsave"
Call addsave()
case "modify"
Call modify()
case "modifysave"
Call modifysave()
case "delall"
Call delall()
End Select
%>
<%
Sub ShowContent()
Call gRsClose()
sSql = "select * from view_alter"
oRs.Open sSql,oConn,1,1
%>
<Form method="post" id="form1" name="form1">
<%
do while not oRs.eof and not oRs.bof
select case oRs("a_type")
case "nvarchar(255)"
a_type = "文本类型"
case "int"
a_type = "整数类型"
case "float"
a_type = "浮点类型"
case else
a_type = "未知类型"
end select
%>
<%
oRs.movenext
loop
%>
<%
Response.Write("<input type='checkbox' id='checkall' name='checkall' onclick='selectAll();'>全/反选" & vbCrlf)
Response.Write("<input type='button' value='删除所选' class='input_submit' onClick=""checkSelect('" & sFileName & "','?action=delall');"">" & vbCrlf)
%>
</form>
<%
Call gRsClose()
End Sub
%>
<%
Sub add()
%>
<Script Language=JavaScript>
function doSubmit(){
if(parseInt(document.getElementById("a_classid").options[document.getElementById("a_classid").selectedIndex].value)==0)
{
alert("请选择产品类型!");
return false;
}
if(parseInt(document.getElementById("a_type").options[document.getElementById("a_type").selectedIndex].value)==0)
{
alert("请选择字段类型!");
return false;
}
if(document.getElementById("a_name").value=="")
{
alert("字段名称不能为空!");
document.getElementById("a_name").select();
return false;
}
if(document.getElementById("a_detail").value=="")
{
alert("请填写该字段的意义!");
document.getElementById("a_detail").select();
return false;
}
}
</script>
<form name="myform" method="post" action="<%=sFileName%>?action=addsave" onSubmit="return doSubmit();">
<input type="hidden" name="Action" value="AddSave">
添 加
<select name="a_type" class="INPUT" id="a_type">
<option value="0" selected>请您选择类型</option>
<option value="int">整数型</option>
<option value="nvarchar(255)">文本型</option>
<option value="float">浮点型</option>
</select>
</label>
<input type="submit" name="Submit" value=" 提 交 " class="input_submit" >
<input type="reset" name="Submit" value="重 置" class="input_submit">
</form>
<%
End Sub
%>
<%
Sub addsave()
a_classid = GetSafeStr(Trim(Request.Form("a_classid")))
a_name = GetSafeStr(Trim(Request.Form("a_name")))
a_detail = GetSafeStr(Trim(Request.Form("a_detail")))
a_type = GetSafeStr(Trim(Request.Form("a_type")))
a_regtime = now()
'判断主表中要添加的字段是否存在
eSql = "select * from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'"
Call gRsClose()
oRs.Open eSql,oConn,1,3
if not oRs.EOF then
Call ErrorMsg(sFileName,"主表中字段已经存在在,三秒后返回",1)
end if
Call gRsClose()
'将字段相关信息添加到从表
sSql = "select * from "& tableName &" where 1=1"
Call gRsClose()
oRs.Open sSql,oConn,1,3
oRs.AddNew()
oRs("a_classid") = a_classid
oRs("a_name") = a_name
oRs("a_detail") = a_detail
oRs("a_type") = a_type
oRs("a_regtime") = a_regtime
oRs.Update()
'添加字段
oConn.Execute("ALTER TABLE "& tableName2 &" ADD "& a_name &" "& a_type &"")
Call ErrorMsg(sFileName,"添加字段成功,三秒后返回",1)
Call gRsClose()
End Sub
%>
<%
Sub modify()
%>
<Script Language=JavaScript>
function doSubmit(){
if(parseInt(document.getElementById("a_classid").options[document.getElementById("a_classid").selectedIndex].value)==0)
{
alert("请选择产品类型!");
return false;
}
if(parseInt(document.getElementById("a_type").options[document.getElementById("a_type").selectedIndex].value)==0)
{
alert("请选择字段类型!");
return false;
}
if(document.getElementById("a_name").value=="")
{
alert("字段名称不能为空!");
document.getElementById("a_name").select();
return false;
}
if(document.getElementById("a_detail").value=="")
{
alert("请填写该字段的意义!");
document.getElementById("a_detail").select();
return false;
}
}
</script>
<%
Call gRsClose()
a_id = Trim(Request.QueryString("a_id"))
sSql = "select * from view_alter where a_id = "& a_id
oRs.Open sSql,oConn,1,1
If not oRs.eof and not oRs.bof Then
a_classid = oRs("a_classid")
a_name = oRs("a_name")
a_detail = oRs("a_detail")
a_type = oRs("a_type")
End If
Call gRsClose()
%>
<form name="myform" method="post" action="<%=sFileName%>?action=modifysave&a_id=<%=a_id%>" onSubmit="return doSubmit();">
<input type="hidden" name="Action" value="AddSave">
添 加
<select name="a_type" class="INPUT" id="a_type">
<option value="0" <%If a_type="" Then Response.Write("selected")%>>请您选择类型</option>
<option value="int" <%If a_type="int" Then Response.Write("selected")%>>整数型</option>
<option value="nvarchar(255)" <%If a_type="nvarchar(255)" Then Response.Write("selected")%>>文本型</option>
<option value="float" <%If a_type="float" Then Response.Write("selected")%>>浮点型</option>
</select>
</label>
<input type="submit" name="Submit" value=" 修 改 " class="input_submit" >
<input type="reset" name="Submit" value="重 置" class="input_submit">
</form>
<%
End Sub
%>
<%
Sub modifysave()
a_classid = GetSafeStr(Trim(Request.Form("a_classid")))
a_name = GetSafeStr(Trim(Request.Form("a_name")))
a_detail = GetSafeStr(Trim(Request.Form("a_detail")))
a_type = GetSafeStr(Trim(Request.Form("a_type")))
a_regtime = now()
a_id = Trim(Request.QueryString("a_id"))
'将字段相关信息添加到从表
sSql = "select * from "& tableName &" where a_id = "& a_id
Call gRsClose()
oRs.Open sSql,oConn,1,3
aname = oRs("a_name")
atype = oRs("a_type")
on error resume next
oRs("a_classid") = a_classid
'判断主表中是否有相同的字段(保持主从表中的数据一致)
set rs1=oConn.Execute("select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'")
If rs1.eof and rs1.bof Then
oRs("a_name") = a_name
End If
rs1.close()
oRs("a_detail") = a_detail
oRs("a_type") = a_type
oRs.Update()
If a_name<>aname Then
'修改字段名称(判断主表中是否有相同的字段)
oConn.Execute("if not exists(select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"') EXEC sp_rename '"& tableName2 &".["& aname &"]', '"& a_name &"', 'COLUMN'")
End If
If a_type<>atype Then
'修改字段类型
oConn.Execute("ALTER TABLE "& tableName2 &" ALTER COLUMN "& a_name &" "& a_type &"")
End If
Call ErrorMsg(sFileName,"修改字段成功,三秒后返回",1)
Call gRsClose()
End Sub
%>
<%
Sub delall()
checkzi = Trim(Request("checkzi"))
If checkzi <> "" Then
Dim z1
z1 = tableName
Dim temp_id,last_id
last_id="("
temp_id = Split(Request("checkzi"),",")
For i=0 To UBound(temp_id)
last_id = last_id & Trim(temp_id(i)) & ","
Next
last_id = Left(last_id,Len(last_id)-1)
last_id = last_id & ")"
'last_id为最终要删除的记录ID
'取出要删除的对应字段
sSql = "select a_name from "& z1 &" where a_id in " & last_id
Call gRsClose()
oRs.Open sSql,oConn,1,1
dim a_names
a_names = ""
jj=1
do while not oRs.eof and not oRs.bof
If jj <> oRs.recordcount Then
a_names = a_names & oRs("a_name") &","
Else
a_names = a_names & oRs("a_name")
End If
oRs.movenext
jj = jj + 1
loop
Call gRsClose()
'删除主表对应字段
a_name = Split(a_names,",")
for i=0 to Ubound(a_name)
oConn.Execute("ALTER TABLE "& tableName2 &" DROP COLUMN "& a_name(i) &"")
next
'删除从表中字段的意义信息
sSql = "delete from " & z1 & " where a_id in " & last_id
oConn.Execute(sSql)
Call ErrorMsg(sFileName,"成功删除所选记录,三秒后将返回……",1)
End If
End Sub
%>
</body>
</html>
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)