SqlServer课件

--以用户SA登录 use master go --检验数据库是否存在，如果为真，删除此数据库-- IF EXISTS(SELECT NAME FROM master.dbo.SYSDATABASES WHERE NAME=N'stuDB') DROP DATABASE stuDB GO --创建数据库-- CREATE DATABASE stuDB ON (NAME=N'stuDB', FILENAME='d:\stuDB.mdf', SIZE=5mb, MAXSIZE=10mb, FILEGROWTH=15%) LOG ON (NAME=N'stuDB_log', FILENAME='d:\stuDB_log.ldf', SIZE=2mb, MAXSIZE=4mb, FILEGROWTH=15%) GO USE stuDB --判断此表是否存在，如果为存在，删除此表-- IF EXISTS(SELECT NAME FROM stuDB.dbo.SYSOBJECTS WHERE NAME=N'stuInfo') DROP TABLE stuInfo GO --创建主表stuInfo-- CREATE TABLE stuInfo (stuName NVARCHAR(20) NOT NULL, stuNo NCHAR(6) NOT NULL, stuSex NCHAR(4) NOT NULL, stuAge SMALLINT NOT NULL, stuSeat SMALLINT IDENTITY(1,1), stuAddress NTEXT) GO --为主表stuInfo创建约束-- ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNo), CONSTRAINT CK_stuNo CHECK(stuNo LIKE 'S253[0-9][0-9]'), CONSTRAINT CK_stuSex CHECK(stuSex='男' OR stuSex='女'), CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40), CONSTRAINT CK_stuSeat CHECK(stuSeat<=30), CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress GO use master EXEC sp_addlogin 'zhangsan', '1234' -- 帐户：zhangsan 密码：1234 GO use studb EXEC sp_grantdbaccess 'zhangsan', 'zhangsanDBUser' GRANT select ON stuInfo TO zhangsanDBUser --断开连接，以用户zhangsandbuser的登录名登录''zhangsan,1234' --为stuInfo插入数据-- use studb go select * from stuinfo go INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES('张秋丽','s25301','男',18,'北京海淀') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('李斯文','s25303','女',22,'河南洛阳') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('李文才','s25302','男',31) INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('欧阳俊雄','s25304','男',28,'新疆威武哈')