数据库实验2

// 1. DBMS 登录帐号管理
CREATE LOGIN SQLUSER WITH PASSWORD = 'pswdforUser_1'
CREATE LOGIN SQLUSER2 WITH PASSWORD = 'pswdforUser_123'
CREATE LOGIN USELESSUSER WITH PASSWORD = 'useless_123'

ALTER LOGIN SQLUSER WITH NAME = SQLUSER1
ALTER LOGIN SQLUSER2 WITH PASSWORD = 'pswdforUser_2'
DROP LOGIN USELESSUSER

// 2. 数据库用户管理
USE mobilephone
CREATE USER SQLUSER1 WITH DEFAULT_SCHEMA=dbo
CREATE USER SQLUSER2 
DROP USER SQLUSER2
CREATE USER DB_USER FOR LOGIN SQLUSER2 WITH DEFAULT_SCHEMA=dbo

// 3. 对数据库用户进行权限/收回权限
USE MobilePhone
GRANT SELECT, UPDATE(CName)
ON Company
TO SQLUSER1
WITH GRANT OPTION

GRANT SELECT
ON Phone
TO PUBLIC

GRANT ALL PRIVILEGES
ON OperatingSystem
TO DB_USER

GRANT ALTER ON SCHEMA::DBO TO SQLUSER1

GRANT CREATE VIEW, CREATE PROCEDURE, BACKUP DATABASE
TO SQLUSER1, DB_USER

REVOKE INSERT, DELETE
ON OperatingSystem
FROM DB_USER

REVOKE CREATE TABLE
FROM SQLUSER1, DB_USER

DENY CREATE VIEW
TO DB_USER

// 4. 创建角色，为角色分配用户及权限
CREATE ROLE UPDATOR
AUTHORIZATION SQLUSER1

CREATE ROLE DELETER 
AUTHORIZATION DB_USER

EXEC sp_addrolemember 'DELETER', 'SQLUSER1'
CREATE ROLE theOtherRole
AUTHORIZATION SQLUSER1

GRANT UPDATE ON Phone TO UPDATOR
GRANT DELETE ON Phone TO DELETER

EXEC sp_addrolemember 'UPDATOR', DB_USER
DROP ROLE theOtherRole

//1. 存储过程的创建、执行与管理
CREATE PROC phoneCount
    @osName char(20),
    @cnt int output
AS
    SELECT @cnt = COUNT(PhoneName)
    FROM Phone
    WHERE OS = @osName

DECLARE @cnt int
EXEC phoneCount 'iOS', @cnt output
PRINT @cnt

ALTER PROCEDURE phoneCount
    @introduced_year int,
    @cnt int output
AS
    SELECT @cnt = COUNT(PhoneName)
    FROM Phone
    WHERE introducedyear = @introduced_year

DROP PROCEDURE phoneCount
DECLARE @cnt int
EXEC phoneCount 2014, @cnt output
PRINT @cnt

DROP PROC phoneCount

//2. 触发器的创建与管理
CREATE TRIGGER tri_newPhone
ON Phone INSTEAD OF INSERT
AS
IF NOT EXISTS(
    SELECT * FROM INSERTED
    WHERE introducedyear > YEAR(GETDATE())
)
INSERT INTO Phone SELECT * FROM INSERTED
ELSE PRINT '不得插入今年未上市的手机'

DROP TRIGGER tri_newPhone
INSERT INTO Phone
VALUES('NewPhone', 'Apple', 'iOS', 'FDD-LTE', 6.3, 2015)

SELECT * FROM PHONE

CREATE TRIGGER tri_notphone
ON Phone AFTER INSERT
AS
IF EXISTS(
    SELECT * FROM Phone
    WHERE DisplaySize NOT BETWEEN 1.0 AND 6.2
)
ROLLBACK

DROP TRIGGER tri_notphone
//1. 对所创建的数据库进行完整、差异、日志备份
EXEC sp_addumpdevice 'disk', 'MobilePhoneBak', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MP.bak'
BACKUP DATABASE MobilePhone
TO MobilePhoneBak WITH init

BACKUP DATABASE MobilePhone
TO MobilePhoneBak WITH differential

BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog.dat'
USE master
BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog.dat' WITH NORECOVERY

//2. 数据库恢复
BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog.dat' WITH NORECOVERY
RESTORE DATABASE MobilePhone
FROM MobilePhoneBak WITH FILE=1

BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog2.dat' WITH NORECOVERY
RESTORE DATABASE MobilePhone
FROM MobilePhoneBak WITH FILE=1, NORECOVERY
RESTORE DATABASE MobilePhone
FROM MobilePhoneBak WITH FILE=2