SQLServer数据库设计和高级查询SQLAdvanceV实用教案

1、会计学1SQL Server数据库设计数据库设计(shj)和高级查和高级查询询SQL AdvanceV第一页，共34页。第1页/共33页第二页，共34页。第2页/共33页第三页，共34页。数据库文件的组成：主数据文件：*.mdf次要数据文件：*.ndf日志文件：*.ldf数据库的其他属性：文件存放位置(wi zhi)，分配的初始空间，属于哪个文件组文件增长：可以按百分比或实际大小指定增长速度文件容量设置：可以指定文件增长的最大值或不受限第3页/共33页第四页，共34页。CREATE DATABASE 数据库名 ON PRIMARY ( ，n ) LOG ON ( ，n )T-SQL创建(chu

2、ngjin)数据库的语法：主文件组，可选参数，默认第4页/共33页第五页，共34页。CREATE DATABASE stuDB ON PRIMARY -默认就属于(shy)PRIMARY主文件组，可省略( NAME=stuDB_data, -主数据文件的逻辑名 FILENAME=D:projectstuDB_data.mdf, -主数据文件的物理名 SIZE=5mb, -主数据文件初始大小 MAXSIZE=100mb, -主数据文件增长的最大值 FILEGROWTH=15% -主数据文件的增长率)LOG ON( NAME=stuDB_log, FILENAME=D:projectstuDB_l

3、og.ldf, SIZE=2mb, FILEGROWTH=1MB)GO 数据文件的具体(jt)描述日志文件的具体描述演示创建数据库1：一个数据文件和一个日志文件 第5页/共33页第六页，共34页。CREATE DATABASE employees ON ( /*-主数据文件的具体描述(mio sh)-*/ NAME = employee1, FILENAME = D:projectemployee1_Data.mdf , SIZE = 10, FILEGROWTH = 10% ), ( /*-次要数据文件的具体描述(mio sh)-*/ NAME = employee2, FILENAME =

4、 D:projectemployee2_Data.ndf , SIZE = 20, MAXSIZE = 100, FILEGROWTH = 1 ) LOG ON ( /*-日志文件(wnjin)1的具体描述-*/ NAME = employeelog1, FILENAME = D:projectemployeelog1_Log.ldf , SIZE = 10, FILEGROWTH = 1 ), ( /*-日志文件(wnjin)2的具体描述-*/ NAME = employeelog2, FILENAME = D:projectemployeelog2_Log.ldf , SIZE = 10,

5、 MAXSIZE = 50, FILEGROWTH = 1 )GO 演示创建数据库2：多个数据文件和多个日志文件 第6页/共33页第七页，共34页。CREATE DATABASE stuDBON (.)LOG ON(.)GO演示(ynsh)：再次运行示例1第7页/共33页第八页，共34页。USE master -设置(shzh)当前数据库为master，以便访问sysdatabases表GOIF EXISTS（SELECT * FROM sysdatabases WHERE name =stuDB） DROP DATABASE stuDBCREATE DATABASE stuDBON ( .)

6、LOG ON( )GODROP DATABASE 数据库名 EXISTS（）语句：检测是否存在stuDB数据库如果存在stuDB数据库，则删除 第8页/共33页第九页，共34页。建表的基本步骤：确定表中有哪些列确定每列的数据类型给表添加(tin ji)各种约束创建各表之间的关系SQL Server中的数据类型 整型数据(shj)：int smallint 浮点数据(shj)：float numeric字符数据固定长度：char(6)可变长度: varchar(10) unicode编码：nchar(10) 布尔数据（真/假） bit: 1/ 0 货币数据 money日期数据 datetime第

7、9页/共33页第十页，共34页。CREATE TABLE 表名 ( 字段1 数据类型 列的特征(tzhng)， 字段2 数据类型 列的特征(tzhng)， .)列的特征：包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。 第10页/共33页第十一页，共34页。USE stuDB -将当前数据库设置为stuDB GOCREATE TABLE stuInfo /*-创建学员信息(xnx)表-*/( stuName VARCHAR(20) NOT NULL , -姓名，非空（必填） stuNo CHAR(6) NOT NULL, -学号，非空（必填） stuAge

8、 INT NOT NULL, -年龄，INT类型默认为4个字节 stuID NUMERIC(18,0), -身份证号 stuSeat SMALLINT IDENTITY (1,1), -座位号，自动编号 stuAddress TEXT -住址，允许为空，即可选输入) GONUMERIC (18,0)代表(dibio)18位数字，小数位数为0演示：创建学员信息表stuInfoIDENTITY（起始值，递增量） 第11页/共33页第十二页，共34页。CREATE TABLE stuMarks( ExamNo CHAR(7) NOT NULL, -考号 stuNo CHAR(6) NOT NULL,

9、 -学号 writtenExam INT NOT NULL, -笔试(bsh)成绩 LabExam INT NOT NULL -机试成绩)GO演示：创建(chungjin)学员成绩表 stuMarks第12页/共33页第十三页，共34页。第13页/共33页第十四页，共34页。DROP TABLE 表名USE stuDB -将当前数据库设置(shzh)为stuDB ，以便在stuDB数据库中建表GOIF EXISTS（SELECT * FROM sysobjects WHERE name=stuInfo ） DROP TABLE stuInfoCREATE TABLE stuInfo /*-创建

10、学员信息表-*/(.) GO第14页/共33页第十五页，共34页。某列取值范围限制、格式限制等，如有关年龄的约束n默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”n外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列第15页/共33页第十六页，共34页。ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(lixng) 具体的约束说明约束名的取名规则推荐采用：约束类型_约束字段主键（Primary Key）约束：如 PK_stuNo唯一（Unique Key）约束：如

11、UQ_stuID默认（Default Key）约束：如 DF_stuAddress检查（Check Key）约束：如 CK_stuAge外键(Foreign Key)约束：如 FK_stuNo 第16页/共33页第十七页，共34页。ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)ALTER TABLE stuInfo ADD CONSTRAINT UQ_stuID UNIQUE (stuID)ALTER TABLE stuInfo ADD CONSTRAINT DF_stuAddress DEFAULT (地址(dz

12、h)不详) FOR stuAddressALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)GO演示：给学员信息(xnx)表stuInfo添加约束添加主键约束（stuNo作为主键）添加唯一约束（因为每人的身份证号全国唯一）添加默认约束（如果地址不填，默认为“地址不详”）添加检查check约束，要求年龄只能在1540岁之间

13、添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）第17页/共33页第十八页，共34页。演示(ynsh)：查看约束结果第18页/共33页第十九页，共34页。ALTER TABLE 表名 DROP CONSTRAINT 约束(yush)名 例如：删除stuInfo表中地址默认约束ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddress第19页/共33页第二十页，共34页。数据库 1数据库 2数据库 3表 1表 2表 1表 2表 1表 2增删改查增删改查增删改查增删改查增删改查增删改查数据库用户数据库用户数据库用户登录帐

14、号SQLserver三层安全管理第20页/共33页第二十一页，共34页。第21页/共33页第二十二页，共34页。演示创建(chungjin)登录EXEC表示调用存储过程，存储过程类似C语言的函数内置的系统管理员 帐户sa密码默认为空，建议修改密码 域名用户名用户名,密码第22页/共33页第二十三页，共34页。第23页/共33页第二十四页，共34页。EXEC sp_grantdbaccess 登录帐户名，数据库用户名 其中，“数据库用户“为可选参数，默认(mrn)为登录帐户，即数据库用户默认(mrn)和登录帐户同名。USE stuDBGOEXEC sp_grantdbaccess jbtrain

15、ingS26301, S26301DBUser EXEC sp_grantdbaccess zhangsan, zhangsanDBUser在stuDB数据库中添加两个用户第24页/共33页第二十五页，共34页。第25页/共33页第二十六页，共34页。系统(xtng)内置的数据库用户(shnch)第26页/共33页第二十七页，共34页。EmployeesEmployeeID123LastNameDavolioFullerLeverling FirstName NancyAndrewJanetReportsTo22 .delete FROM EmployeesSELECT * FROM Employees第27页/共33页第二十八页，共34页。USE stuDBGO/*-为zhangsanDBUser分配(fnpi)对表stuInfo的select, insert, update权限-*/GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-为S26301DBUs