SQL Server 数据库设计和高级查询

SQL Server数据库设计和高级查询

 

数据库设计

设计数据库的步骤：

需求分析、概要设计、详细设计、代码编写、运行测试和打包方行

数据库的需求分析：

         收集信息、标识对象、标识每个对象需要存储的详细系信息、标识对象之间的关系

概要设计阶段和详细设计阶段：

         绘制E-R图、将E-R图转换为表、应用三大范式规范化表设计。

数据规范化：

         第一范式：确保每列的原子性

         第二范式：在第一范式的基础上确保表中的每列都和主键相关

         第三范式：在第二范式的基础上确保确保每列都和主键列直接相关，而不是间接相关

数据库的实现

T-SOL语句回顾：

1.         添加数据

INSERT [INTO] 表名 （字段1，字段2，…） VALUES（值1，值2,…）

2.         修改数据

UPDATE 表名 SET 字段1=值1，字段2=值2，… WHERE （条件）

3.         查询数据

SELECT  字段1，字段2，… FROM 表名 WHERE （条件）ORDER BY 字段名

4.         删除数据

DELETE  FROM 表名 WHERE （条件）

 

使用T-SOL语句创建和删除数据库

创建数据库

CREATE  DATABASE        数据库名

         ON   [PRIMARY]                 ---指定主文件组中的文件

         (

         /*数据文件的具体描述*/

         NAME=’stuDB_data         ’,       ---主数据文件的逻辑名称

         FILENAME=’D:/project/stuDB_data.mdf’,      ---主数据文件的物理名称

         SIZE=5MB,       ---主数据文件的初始大小

         MAXSIZE=100MB,            ---主数据文件增长的最大值

         FILEGROWTH=15%           ---主数据文件的增长率

         )

         [LOG ON]                             ---指明事物日志文件的明确定义

         (

         /*日志文件1的具体描述*/

         NAME=’stuDB_log’,                  ---日志文件的逻辑名称

         FILENAME=’D:/project/stuDB_log.ldf’,           ---日志文件的物理名称

         SIZE=2MB,

         FILEGROWTH=1MB

         )

         ，

(

         /*日志文件2的具体描述*/

         NAME=’stuDB_log2’,                ---日志文件的逻辑名称

         FILENAME=’D:/project/stuDB_log2.ldf’,                  ---日志文件的物理名称

         SIZE=2MB,

         MAXSIZE=5MB,

         FILEGROWTH=1MB

         )

 

GO                      ---和后续的SQL 语句分开

 

删除数据库

UES  master     ---设置当前数据库为master，以便访问sysdatabases表

GO

/*---先检测是否存在数据库stuDB，如果存在先删除再创建---*/

IF      EXISTS ( SELECT * FROM sysdatabases WHERE NAME=’stuDB’ )

DROP        DATABASE        stuDB               ---删除数据库

…..(创建数据库)

GO

 

使用SQL 语句创建和删除表

建表的步骤

1)         确定表中有哪些列

2)         确定每列的数据类型

3)         给表添加各种约束

4)         创建各表之间的关系

SQL Server数据类型一览表.docx

创建表

CREATE    TABLE      表名

         (

                   字段1      数据类型         列的特征，

                   字段2      数据类型         列的特征，

                   …

         )

列的特征包括

是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否是主键等。

创建学员信息表

SUE stuDB        ---将当前数据库设置为stuDB，以便在stuDB中创建表

GO

CREATE   TABLE       stuInfo     /*创建学院信息表*/

(

         stuName         VARCHAR(20)  NOT NULL,       ---学员姓名，非空

         stuNo       CHAR(6)   NOT NULL,       ---学号，非空

         stuAge    INT           NOT NULL,       ---年龄，非空

         stuID        NUMERIC(18,0),      ---身份证号，NUMERIC(18,0) 表示18位数字，小数位是0

         stuSeat    SMALLINT         IDENTITY (1,1),         ---座位号，自动编号（标识列），从1开始

         stuAddress       TEXT,                  ---地址

)

GO

创建学员成绩表

CRATE     TABLE       stuMarks

(

         ExamNo   CHAR(7)   NOT NULL,                 ---考号

         stuNo       CHAR(6)   NOT NULL,                 ---学号

         writtenExam    INT   NOT NULL,                 ---笔试成绩

         LabExam  INT           NOT NULL                           ---机试成绩

)

GO

 

删除表

USE stuDB                 ---将当前数据库设置为stuDB

GO

/*---先检查是否存在表stuInfo，如果存在先删除在创建---*/

IF      EXISTS      ( SELECT * FROM sysobjects WHERE NAME=’stuInfo’ )

         DROP        TABLE       stuInfo

…/*创建表stuInfo*/

GO

 

使用SQL 语句创建和删除约束

常用的约束类型：

Ø  主键约束（Primary Key constraint）

Ø  唯一约束（Unique Constraint）:要求该列唯一，允许为空，但只能出现一个空值

Ø  检查约束（Check Constraint）:某列取值范围、格式限制等

Ø  默认约束（Default Constrain）:某列的默认值

Ø  外键约束（Foreign Key Constraint）

添加约束

ALTER      TABLE       表名

ADD CONSTRAINT   约束名     约束类型         具体的约束说明

---添加主键约束

ALTER      TABLE       stuInfo

ADD CONSTRAINT   PK_stuNo                   PRIMARY         KEY  (stuInfo),

---添加唯一约束

         CONSTRAINT   UQ_stuID                   UNIQUE   (stuID),

---添加默认约束

         CONSTRAINT DF_stuAddress           DEFAULT(‘地址不详’)      FOR stuAddress,

---添加检查约束

         CONSTRAINT   CK_stuAge       CHECK(stuAge                   BETWEEN 15   ADD 40)

/*---添加外键约束---*/

ALTER      TABLE stuMarks

         ADD CONSTRAINT   FK_stuNo

                   FROEIGN         KEY( stuNo )     REFERENCES             stuInfo(stuNo)

GO

 

删除约束

ALTER TABLE 表名

         DROP CONSTRAINT 约束名

使用T-SQL语句创建登陆

创建登陆账户

/*添加windows登陆账户*/

EXEC    sp_grantlogin  ‘jbtraining/s26301’

---windows用户为jbtraining/s26301,jbtraining表示域

 

/*添加SQL登陆账户*/

EXEC        sp_addlogin  ‘zhangsan’,’1234’

---账户名为zhangsan，密码1234

GO

创建数据库用户

EXEC         sp_grantdbaccess  ‘登陆账户’,’数据库用户’

/*在stuDB数据库中添加两个用户*/

USE stuDB

GO

EXEC         sp_grantdbaccess   ‘jbtraining/s26301',’S26301DBser’

EXEC         sp_grantdbaccess   ‘zhangsan’,’zhangsanDBUser’

给数据库用户授权

GRANT 权限 [ON 表名]     TO    数据库用户

常用权限

添加数据（INSERT）、删除数据（DELETE）、更新数据（UPDATE）、查看数据（SELECT）、创建表（CREATE  TABLE）

USE stuDB

GO

CRENT      select,insert,update  ON       stuInfo     TO    zhangsanDBUser

GRENT     create  table  TO          S26031DBUser

T-SQL  编程

局部变量

DECLAR   @variable_name     DataType          ---variable_name为局部变量，DataType为数据类型

DECLARE          @name             varchar(8)         ---声明一个存放学员姓名的变量，最多可以存放8个字符

DECLARE          @seat               int              ---声明一个存放学员座位号的变量seat

局部变量的赋值有两种方法：使用SET语句或SELECT语句

SET @variable_name=value

SELECT @variable_name=value

全局变量（不能自定义全局变量）：

@@ERROR:最后一个T-SQL错误的错误号

                     @@IDENTITY:最后一次插入的标识值

输出语句

常用的输出语句

Print        局部变量或字符串

SELECT     局部变量         AS              自定义列名

 

Print                   ‘服务器的名称：’+@@SERVERNAME

SELECT    @@SERVERNAME           AS              ‘服务器名称’

把数值转换为字符串

Print  ‘当前错误’+convert(varchar(5),@@ERROR)

Print  ‘最后插入的标识值’+convert(varchar(5),@@IDENTITY);

逻辑控制语句

IF-ELSE

IF      (条件)

         语句或语句块

ELSE                                                                  ----ELSE为可选块

         语句或语句块

 

/*如果有多条语句，需要使用语句块-BEGIN    END */

IF      (条件)

         BEGIN

                   语句1

                   语句2

                   …

         END

ELSE

         …

While循环语句

While (条件)

       语句或语句块    

       [BREAK]/[CONTIUNE]

CASE    多分枝语句

CASE

              WHEN       条件1      THEN          结果1

              WHEN        条件2              THEN          结果2

              [ELSE    其他结果]

END

 

高级查询

简单子查询（嵌套子查询）

SELECT    …      FROM     表1          WHERE    字段1     >       (自查询)

一般表链接都可以用子查询替换，但有的子查询不能用表链接来替换

SELECT stuName FROM stuInfo INNER JOIN stuMarks          ---INNER JOIN 内部链接

ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam=60

 

IN和 NOT IN 子查询

/*采用IN子查询*/

SELECT stuName FROM stuInfo

         WHERE stuNo IN (SELECT stuMarks WHERE writtenExam=60 )

GO

IN后面的子查询可以返回多条记录，用于限制学号的筛选范围

/*采用NOT     IN 子查询，查看没有参加考试的学员*/

SELECT stuName FROM stuInfo

         WHERE stuNo NOT IN (SELECT stuNo FROM stuMarks)

GO

EXISTS和NOT EXISTS子查询（相关子查询）

IF  EXISTS       ( 子查询 )

         语句

如果子查询的结果非空，则EXISTS (子查询)将返回真（true）,否则返回假

SELECT …           WHERE  EXISTS(子查询)  ---子查询执行多遍

事物、索引和视图....待整理中...s