SQL Server
查询 card 的记录为两次以上的 card,记录数:
select count(card), card from TableName group by card having count(card) > 1
级联更新,级联删除:
ColumnName type not null constraint FK_Name foreign key(ColumnName) references PrimaryTable(ColumnName) on update/delete cascade
自动计算列:
create table TableName
(
ID int,
Price decimal(10,2) not null,
Number int not null,
Total as Price * number -- Total 不存储值。
)
insert into TableName values(1, 100, 123)
insert into TableName values(1, 100, 123)
insert into TableName values(2, 200, 456)
insert into TableName values(2, 200, 456)
select ID, Total=SUM(Total) from TableName where ID = 1 group by ID
修改现有商品数量:
begin
begin tran
declare @ForeignError int, @PrimaryError int
insert into ForeignTable values(@ID, @Number)
select @ForeignError=@@Error -- @@Error: 返回执行的上一个 Transact-SQL 语句的错误号。
update PrimaryTable set Number=Number ± @Number where ID=@ID --进货时用加号。
select @PrimaryError=@@Error
if (@ForeignError = 0 and @PrimaryError = 0)
commit tran
else
rollback tran
end
begin
begin tran
declare @ForeignError int, @PrimaryError int, @NewNumber int
update ForeignTable set Number=@Number, @NewNumber=@Number - Number where ID=@ID
select @ForeignError=@@Error
update PrimaryTable set Number=Number ± @NewNumber where ID=@ID --进货时用加号。
select @PrimaryError=@@Error
if (@ForeignError = 0 and @PrimaryError = 0)
commit tran
else
rollback tran
end
相关文档:
系统环境:Windows 7
软件环境:Visual C++ 2008 SP1 +SQL Server 2005
本次目的:编写一个航空管理系统
这是数据库课程设计的成果,虽然成绩不佳,但是作为我用VC++ 以来编写的最大程序还是传到网上,以供参考。用VC++ 做数据库设计并不容易,但也不是不可能。以下是我的程序界面,后面 ......
在连接SQL Server 2000数据库时,系统提示如下错误:
在网上寻找到处理方法:
(把SQL server update到sp4)
1、在查询分析器中输入 select @@version 并执行
SQL Server 2000 版本和级别
@@VERSION
产品级别
SQL Server 2000 原始版本
8.00.194
RTM
Database Components SP1
8.00.384
SP1 ......
Next up in the Inside the Storage Engine series is a discussion of page structure. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset ......
1. 定义游标定义
游标语句的核心是定义了一个游标标识名,并把游标标识名和一个查询语句关联起来。DECLARE语句用于声明游标,它通过SELECT查询定义游标存储的数据集合。语句格式为:
DECLARE 游标名称 [INSENSITIVE] [SCROLL]
CURSOR FOR select语句
[FOR{READ ONLY|UPDATE[OF 列名字表]}]
参数说明:
INSENS ......
1.监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
2.分析表
analyze table tablename compute statistics for all indexes;
analyze table tablename compute statistics for all indexed col ......