如何在SQL Server中创建表？ [英] How to Create Table in sql Server?
问题描述
我必须为一般店铺管理员申请...作为分配
i已经创建了所有必需的表....除了帐户表...
该怎么办我不知道
在账户表中，店主想要保留客户的记录。
例如，当一位老客户到达商店时，我希望店主在进行任何新交易之前查看此客户的旧帐户详细信息。意味着他先前未支付的费用等......
i一个但是当我
选择 sum时它会产生问题（ accountBalnce）来自 tblAccount 其中 act_date = @ april
它是列ammountbalance的汇总，如果店主减去这个汇总的一些金额，则无法存回这笔金额。
我的表
clientID
act_date
OrderID
AccountID //主要密钥
AccountReceive
AccountBalance
TotalAccount
我的问题是我应该制作多少个表以及我应该如何为帐户制作
i想要添加全部客户端AmmountBalce列的值
然后在preouscharges_textbox中显示
并且还想知道我是否减去preouscharges_textbox中的一些金额如何保存所做的更改
如果回答很长，那么给我一些提示
我的C＃代码：
公开 无效 accountinfo（）
{
尝试
{
conn.Open（）;
string qrystr = 从tblaccount中选择* ClientId = + txtid.Text + and orderId = + orderidstrip.Text ;
SqlCommand acountcmd = new SqlCommand（qrystr，conn）;
acountcmd.CommandType = CommandType.Text;
SqlDataAdapter actdr = new SqlDataAdapter（acountcmd）;
SqlDataReader acoountdr = acountcmd.ExecuteReader（CommandBehavior.CloseConnection）;
while （acoountdr.Read（））{
txtrece.Text = acoountdr [ AmountRece]。ToString（）;
txtamtbal.Text = acoountdr [ AmountBal]。ToString（）;
txttotalleft.Text = acoountdr [ totalleft_amount]。ToString（）;
}
conn.Close（）;
}
catch （Exception ex）
{
MessageBox.Show（ex.Message）;
}
您可以创建两个表，一个是事务表，一个是汇总表。这两个脚本都在这里：
表1
USE [DBNAME ]
GO
/ ******对象：表[dbo]。[tbl_transactions]脚本日期：02/13 / 2014 19:21:21 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]。[tbl_transactions]（
[InvoiceID] [nchar ]（10）NULL，
[act_date] [date] NULL，
[ItemID] [int] NULL，
[AccountID] [ int] NULL，
[ItemAmount] [decimal]（18,0）NULL
）ON [PRIMARY]
GO
表2
USE [DBNAME]
GO
/ ******对象：表[dbo]。[tbl_summary]脚本日期：02/13/2014 19:23:40 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
创建表[dbo]。[tbl_summary]（
[AccountID] [int] NULL，
[InvoiceID] [int] NULL，
[Amount_Received ] [decimal]（18,0）NULL，
[Amount_Balance] [decimal]（18,0）NULL，
[Invoice_paid] [bit] NULL
）ON [主要]
GO
在保存数据时，您可以从交易表中保存摘要
步骤：
1。首先保存表1中的数据（交易表）
2.在您的select语句中使用此查询（请记住，在您的选择条件中，您没有使用group by子句和act_date有多个值所以使用group by子句）
选择SUM（itemamount）作为总数，来自tbl_transactions的InvoiceID其中AccountID = 1 group by InvoiceID
3.现在在汇总表中插入以上数据。
4.现在，您输入一个表单，用于在输入AccountID时接收付款，InvoiceID an d收到金额。
5.对于下次访问，当您输入AccountID时，根据您的accountID和invoice_paid从摘要表中选择数据。
保存更新功能请阅读我的文章http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=8171172 [ ^ ]
如果您使用的是最新的sql或evn之前没有必要去...
创建表tblTesr
（
一个varchar（100）不为空，
两个varchar（100）不为空，
三个varchar（100）不为空，
四个varchar（100）不为空
）
从创建突出显示到最后一个括号（））然后点击Execute
还要注意不要把所有内容放在varchar中...如果它只获取int值，那么使用int not null，
希望这个helpa
a几个命令
select * from tblTest
drop table tblTesr //删除表
i have to make an application for general shop keeper...as an Assignment
i have created all Tables Required.... except Account Table ...
what to do i dont know
in account table the shop keeper want to keep records of customer.
e.g when an old customer Arrive at shop i want the shop keeper to see this customer old account details before making any new transaction. means his previous unpaid charges etc...
i made one but it give problem when i
select sum(accountBalnce) from tblAccount where act_date=@april
it was aggregate of column ammountbalance if shopkeeper Minus some amount from this aggregate it was not possible to store back this amount.
My Table
clientID
act_date
OrderID
AccountID //Primary Key
AccountReceive
AccountBalance
TotalAccount
my problem is how many table should i make and how may columns should i make for account
i want to add all values of a client AmmountBalce Columns
then desplay in a preouscharges_textbox
and also want to know if i minus some amount from preouscharges_textbox how to save back the change made
if it's lengthy to answer then give me some tips
My C# Code:
public void accountinfo()
{
try
{
conn.Open();
string qrystr = "Select * from tblaccount where ClientId= " + txtid.Text + " and orderId= " + orderidstrip.Text;
SqlCommand acountcmd = new SqlCommand(qrystr,conn);
acountcmd.CommandType = CommandType.Text;
SqlDataAdapter actdr = new SqlDataAdapter(acountcmd);
SqlDataReader acoountdr = acountcmd.ExecuteReader(CommandBehavior.CloseConnection);
while (acoountdr.Read()) {
txtrece.Text=acoountdr["AmountRece"].ToString();
txtamtbal.Text = acoountdr["AmountBal"].ToString();
txttotalleft.Text = acoountdr["totalleft_amount"].ToString();
}
conn.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}You can create two tables one is transaction table and one is summary table. Both scripts are here:
Table 1
USE [DBNAME]
GO
/****** Object: Table [dbo].[tbl_transactions] Script Date: 02/13/2014 19:21:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_transactions](
[InvoiceID] [nchar](10) NULL,
[act_date] [date] NULL,
[ItemID] [int] NULL,
[AccountID] [int] NULL,
[ItemAmount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
Table 2
USE [DBNAME]
GO
/****** Object: Table [dbo].[tbl_summary] Script Date: 02/13/2014 19:23:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_summary](
[AccountID] [int] NULL,
[InvoiceID] [int] NULL,
[Amount_Received] [decimal](18, 0) NULL,
[Amount_Balance] [decimal](18, 0) NULL,
[Invoice_paid] [bit] NULL
) ON [PRIMARY]
GO
At the time of saving data you save summary from your transaction tables
Steps:
1. First save data in your Table 1 (Transaction Table)
2. use this query in your select statement (Remember in your selection criteria you have not used group by clause and act_date have multiple values so use group by clause)
select SUM(itemamount) as total, InvoiceID from tbl_transactions where AccountID =1 group by InvoiceID
3. now insert above data in summary table.
4. Now you make a form for receiving payments where you input AccountID, InvoiceID and Received Amount.
5. For next visit when you enter AccountID select data from summary table according your accountID and invoice_paid.
for Save update functions please read my article http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=8171172[^]
if your using the newest sql or evn before that there is no need for go ...
Create table tblTesr
(
One varchar(100) not null,
Two varchar(100) not null,
Three varchar(100) not null,
Four varchar(100) not null
)
highlight from create to the last bracket(")") and then click on Execute
also note do not put everything in varchar... if it only gets int values then use int not null,
hope this helpa
a few command
select * from tblTest
drop table tblTesr // deletes table
这篇关于如何在SQL Server中创建表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
