常用demo 的sql 语句

--将密码改为 123
--UPDATE _user SET [password] = '202CB962AC59075B964B07152D234B70' WHERE userid='???' -- '=:?pabcd'

--建 用户表  
IF EXISTS (    
       SELECT *    
       FROM   sysobjects    
       WHERE  id = OBJECT_ID(N'Demo_User')    
              AND OBJECTPROPERTY(id, N'IsUserTable') = 1    
   )    
BEGIN  
    DROP TABLE Demo_User  
END  
GO  
CREATE TABLE Demo_User  
(  
    UserID VARCHAR(32) PRIMARY KEY,  
    UserName NVARCHAR(32) NOT NULL,  
    LoginID VARCHAR(32) NOT NULL,  
    UserPwd VARCHAR(32) NOT NULL,  
    Gender  NCHAR(1),  
    Age     INT,  
    EntryTime DATETIME,  
    DepartmentID VARCHAR(32),  
    Position NVARCHAR(MAX)  
)  
GO  
--建 部门表  
IF EXISTS (    
       SELECT *    
       FROM   sysobjects    
       WHERE  id = OBJECT_ID(N'Demo_Department')    
              AND OBJECTPROPERTY(id, N'IsUserTable') = 1    
   )    
BEGIN  
    DROP TABLE Demo_Department  
END  
GO  
CREATE TABLE Demo_Department  
(  
    DepartmentID VARCHAR(32) PRIMARY KEY,  
    DepartmentName NVARCHAR(50) NOT NULL  
)  
GO  
  
--插入数据到部门表  
INSERT INTO Demo_Department  
SELECT REPLACE(NEWID(),'-',''),'高管' union  
SELECT REPLACE(NEWID(),'-',''),'市场部' union  
SELECT REPLACE(NEWID(),'-',''),'电信业务' union  
SELECT REPLACE(NEWID(),'-',''),'金融及其它业务' union  
SELECT REPLACE(NEWID(),'-',''),'品质管理部' union  
SELECT REPLACE(NEWID(),'-',''),'人力资源部' union  
SELECT REPLACE(NEWID(),'-',''),'财务部'  
  
  
--插入数据到用户表  
DECLARE @i INT,@max INT,@strI VARCHAR(MAX)  
SET @i=1
set @max=500  
  
WHILE (@i<=@max)  
BEGIN  
    IF(@i<10)  
        SET @strI='00'+CONVERT(CHAR(1),@i)  
    ELSE IF (@i<100)  
        SET @strI='0'+CONVERT(CHAR(2),@i)  
    ELSE  
        SET @strI=CONVERT(VARCHAR(MAX),@i)  
          
    INSERT INTO Demo_User  
    (  
        UserID,  
        UserName,  
        LoginID,  
        UserPwd,  
        Gender,  
        Age,  
        EntryTime,  
        DepartmentID,  
        Position  
    )  
    SELECT TOP 1  
        /* UserID   */ REPLACE(NEWID(),'-','') ,  
        /* UserName */ '柳大华'+@strI,  
        /* LoginID  */ @strI ,  
        /* UserPwd  */ '123456',  
        /* Gender   */ '男',  
        /* Age  */     25,  
        /* EntryTime    */ '2013-01-30',  
        /* DepartmentID */ dd.DepartmentID ,  
        /* Position */ '工程师'  
    FROM Demo_Department dd WHERE dd.DepartmentName='电信业务'  
    
    INSERT INTO Demo_User  
    (  
        UserID,  
        UserName,  
        LoginID,  
        UserPwd,  
        Gender,  
        Age,  
        EntryTime,  
        DepartmentID,  
        Position  
    )  
    SELECT TOP 1  
        /* UserID   */ REPLACE(NEWID(),'-','') ,  
        /* UserName */ '田美丽'+@strI,  
        /* LoginID  */ @strI ,  
        /* UserPwd  */ '123456',  
        /* Gender   */ '女',  
        /* Age  */     25,  
        /* EntryTime    */ '2013-01-01',  
        /* DepartmentID */ dd.DepartmentID ,  
        /* Position */ '美工'  
    FROM Demo_Department dd WHERE dd.DepartmentName='电信业务'
      
    SET @i=@i+1  
END