SQL Server數據庫操作代碼

 -- 1.創建數據庫
 create database TestDB1
 go
 
 create database TestDB2
 go
 
 -- 2.創建測試表:在數據庫TestDB1/TestDB2中創建表TestTable 默認dbo schema
 use TestDB1
 go
 create table TestTable
 (
     Num        int primary key,
     Name    nvarchar(20),
     Addr    nvarchar(50)
 )
 go
 
 use TestDB2
 GO
 create table TestTable
 (
     Num        int primary key,
     Name    nvarchar(20),
     Addr    nvarchar(50)
 )
 go
 
 -- 3.向表TestTable中插入數據
 use TestDB1
 insert into TestTable values(101, 'ACCOUNTING', 'NEW YORK');
 insert into TestTable values(201, 'RESEARCH', 'DALLAS');
 insert into TestTable values(301, 'SALES', 'CHICAGO');
 insert into TestTable values(401, 'OPERATIONS', 'BOSTON');
 select * from TestTable        -- 查看插入結果
 
 use TestDB2
 insert into TestTable values(101, 'ACCOUNTING', 'NEW YORK');
 insert into TestTable values(201, 'RESEARCH', 'DALLAS');
 insert into TestTable values(301, 'SALES', 'CHICAGO');
 insert into TestTable values(401, 'OPERATIONS', 'BOSTON');
 select * from TestTable        -- 查看插入結果
 
 -- 4.查看數據庫sechema, user的存儲結果
 use TestDB1
 select * from sys.database_principals
 select * from sys.schemas
 select * from sys.server_principals
 
 -- 5.創建登陸賬戶: DuanLaoYe 密碼123456 
 create login DuanLaoYe with password = '123456', default_database = TestDB1
 
 -- 6.為登陸賬戶創建數據庫用戶
 use TestDB2
 create user DLY for login DuanLaoYe with default_schema = dbo
 
 -- 7.通過假如數據庫角色,賦予數據庫用戶'db_owner'權限
 use TestDB1
 exec sp_addrolemember 'db_owner', 'DLY'
 
 -- 8.讓登陸用戶DuanLaoYe訪問多個數據庫
 use TestDB2
 go
 create user DLY2 for login DuanLaoYe with default_schema = dbo
 go
 exec sp_addrolemember 'db_owner',DLY2
 go
 
 -- 9.禁用登陸賬戶
 alter login DuanLaoYe disable
 
 -- 10.啟用登陸賬戶
 alter login DuanLaoYe enable
 
 -- 11.更改登陸賬戶名稱
 alter login DuanLaoYe with name = Change_Name_DuanLaoYe
 
 -- 12.更改登陸賬戶密碼
 alter login Change_Name_DuanLaoYe with password = '654321'
 
 -- 13.更改數據庫用戶名稱
 use TestDB1
 alter user DLY with name = Change_Name_DLY1
 
 use TestDB2
 alter user DLY with name = Change_Name_DLY2
 
 -- 14.刪除數據庫用戶: Change_Name_DLY1 / Change_Name_DLY2
 use TestDB1
 drop user Change_Name_DLY1
 
 use TestDB2
 drop user Change_Name_DLY2
 
 -- 15.刪除登陸賬戶:Change_Name_DuanLaoYe
 drop login Change_Name_DuanLaoYe
 
 -- 16.刪除數據庫: TestDB1 / TestDB2
 drop database TestDB1
 go