SQLite点滴

--创建表: SL_Register_Info
CREATE TABLE [SL_Register_Info] (
  [RegID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[UserID] INT NOT NULL CONSTRAINT [ForeingKey_UserID] REFERENCES [SL_User_Info]([UserID]) ON DELETE CASCADE ON UPDATE CASCADE,[MachineCode] VARCHAR(128),[RegCode] VARCHAR(200),[RegType] INT NOT NULL DEFAULT (1),[RegDate] DATE DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')),[EndRegDate] DATE,[AllowMaxIns] INT DEFAULT (0));
--设置唯一键
CREATE UNIQUE INDEX [Unique_User] ON [SL_Register_Info] ([UserID],[MachineCode]);

--创建表:SL_User_Info
CREATE TABLE [SL_User_Info] (
  [UserID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[UserName] VARCHAR(50) NOT NULL,[PassWord] VARCHAR(20) NOT NULL,[BindType] INTEGER NOT NULL DEFAULT (0),[Telephone] VARCHAR(20),[QQ] VARCHAR(20),[RegisterDate] DATETIME DEFAULT (datetime(CURRENT_TIMESTAMP,[ReMark] VARCHAR(200));
--设置唯一键
CREATE UNIQUE INDEX [Unique_UserName] ON [SL_User_Info] ([UserName]);

--创建视图
CREATE VIEW [SL_User_Reg_View] AS 
select A.UserID AS 用户编号,UserName AS 用户名,BindType AS 绑定类型,B.MachineCode AS 机器码,B.RegCode AS 注册码,B.RegType AS 注册类型,B.RegDate AS 注册日期 
from SL_User_info AS A LEFT JOIN 
     SL_Register_Info AS B ON A.UserID = B.UserID;

--使用事务
BEGIN TRANSACTION;	--开始事务
UPDATE SL_User_Info SET UserName = 'ManSoy' WHERE UserID = 2;
COMMIT TRANSACTION;	--提交事务

BEGIN TRANSACTION;	--开始事务
UPDATE SL_User_Info SET UserName = 'ManSoy1' WHERE UserID = 2;
ROLLBACK TRANSACTION	--回滚事务

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!