数据库笔记（二）

一、数据类型

1字符数据类型

1). char类型

格式: char[(n)]

功能: 定义长度为n个字节的固定长度非Unicode字符数据，每个字符占一个字节。

说明: n:1~8000

存储大小: n个字节（n个字符）

2). varchar类型

格式: varchar[(n)]

功能: 定义长度最多为n个字节的可变长度非Unicode字符数据,每个字符占一个字节。

说明: n: 1~8000。

存储大小: 输入字符的实际长度。长度可为零

注意：n的缺省长度为1。

3). nchar类型

格式: nchar[(n)]

功能: 定义包含n个字符的固定长度Unicode字符数据

说明: n:1~4000

存储大小: 2n个字节

4). nvarchar类型

格式: nvarchar(n)

功能:定义包含最多n个字符的可变长度Unicode字符数据

说明: n: 1~4000。

存储大小:所输入的字符实际个数的两倍。长度可以为零。

注：n的缺省长度为1。

 

2日期和时间数据类型

日期和时间数据类型用于存储日期和时间的结合体。

datetime类型

存储大小: 8个字节

表示范围:1753年1月1日零时~9999年12月31日23时59分59秒。

   例: 2000-5-29 12:30:48

二、表的管理

1表的创建

使用CREATE TABLE语句创建表

列名    列的数据类型    列说明，是否允许为空（NULL或NOT NULL）

2表的约束

l  主键（PRIMARY KEY）约束

主键是表中的一列或一组列，它们的值可以唯一地标识表中的每一行。在创建和修改表时，可以定义主键约束。主键列的值不允许为空。

语法：PRIMARY KEY(列名1,列名2)

l  唯一性（UNIQUE）约束

唯一性约束可以保证除主键外的其他一个或多个列的数据唯一性，以防止在列中输入重复的值。唯一性约束允许一个空值。

语法：CONSTRAINT 约束名 UNIQUE(列名)

l  检查（CHECK）约束

检查约束指定表中一列或多列可以接受的数据值或格式

语法：CONSTRAINT 约束名 CHECK (逻辑表达式) |（in|between|like）  

       如：CONSTRAINTchkStuSex CHECK(StuSex IN（‘男‘,‘女‘）) ,

            CONSTRAINT chkStuScore CHECK (StuScore between 0 and 100)    

            CONSTRAINTchkPostCode CHECK (PostCode LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')

l  默认（DEFAULT）约束

默认约束可以为指定列定义一个默认值。在输入数据时，如果没有输入该列的值，则将该列的值设置为默认值。

语法：列名 数据类型 DEFAULT 约束表达式

       如：StuSex char(2) DEFAULT ‘男’

l  外键（FOREIGN KEY）约束

外键约束是用于建立两个表之间的联系。通过将当前表中的某一列关联到另一个表的主键列，可创建两个表之间的连接。当前表中的列就成为外键。

语法：CONSTRAINT约束名 FOREIGN KEY(列名) REFERENCES 关联表(关联列名)

如：CONSTRAINTfkDepID FOREIGN KEY(DepID) REFERENCES Department(DepID)

注：

建表时，按照主表---->从表的顺序创建，删除表时，按照从表---->主表的顺序删除。

建表格式：

CREATETABLE 表名

(

    列名1 数据类型和长度1  列说明1,

    列名2 数据类型和长度2  列说明2,

    …

    约束说明[…n],

)

三、表的维护

1插入数据

语法：

INSERT INTO 表名 [ (列名1, 列名2, ..., 列名n) ]

VALUES (值1, 值2, ..., 值n)

注意：当所有字段均需赋值时，表名之后的列名可以不写，默认为全部字段。

2更新数据

语法：

   UPDATE 表名

    SET 列名1=值1 [, 列名2=值2, ..., 列名n=值n ]

    [WHERE 更新条件]

注意：

在插入（更新）数据时，还需要考虑到表的约束等因素，如果插入（更新）的数据违反表约束或规则，则无法正常插入（更新）数据。例如：

（1）不允许设置标识列的值

（2）不允许向唯一性约束列中插入（设置）相同的数据

（3）不能违反检查约束

（4）不能违反外键约束

3删除数据

语法：

DELETE FROM 表名 WHERE 删除条件

如：

    DELETE FROM Student

    WHERE StuID=6

 

注：

DELETE和FROM之间不加任何字段，否则编译器报错！

4清空表中数据

使用TRUNCATE TABLE语句可以清空表中所有数据

例：

    TRUNCATE TABLE Student

 

四、表的查询

1查询表

使用SELECT语句实现查询

基本语法：

    SELECT子句

    FROM 子句

    [ WHERE 子句 ]

    [ GROUP BY 子句]

    [ HAVING 子句 ]

    [ ORDER BY 子句 ]

语法：

       SELECT [ALL|DISTINCT][TOP n [PERCENT]]<字段说明>

FROM 表名

<字段说明> ::={ * |{列名|表达式} [ ,...n ]

l  where查询条件：

   WHERE子句用于指定返回的行的搜索条件。相当于选择运算。

条件表达式中可包含：

逻辑运算符 (NOT,AND,OR)

比较运算符(>,<=…)

范围运算符（BETWEEN）

列表运算符（IN,LIKE…）

NULL关键字

l  聚合函数：

SELECT语句不仅可以显示表或视图中的列，还可以对列应用聚合函数，实现对表中指定数据的统计，如求总和、计数、求平均值等。聚合函数不能出现在WHERE子句中。

聚合函数有：COUNT()，AVG()，MAX()，MIN()，SUM()

l  对查询结果分组：

当SELECT子句中包含聚合函数时，可以使用GROUP BY子句对查询结果进行分组统计，计算每组记录的汇总值。

注意：在使用GROUP BY子句时，SELECT子句中每一个非聚合表达式内的所有列都应包含在GROUP BY列表中。否则将会返回错误信息。

GROUP BY子句的基本语法如下：

GROUP BY  分组表达式 [,...n ]

例：SELECTStuSex, StuClass, MAX(StuScore)

    FROM Student

    GROUP BYStuSex,StuClass

l  指定组或聚合的搜索条件

HAVING子句的功能是指定组或聚合的搜索条件。HAVING通常与GROUP BY子句一起使用。如果不使用GROUP BY子句，HAVING的作用与WHERE子句一样。

   HAVING与WHERE的区别在于：

1.WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。

2.HAVING语法与WHERE语法类似，但在HAVING子句中可以包含聚合函数,而WHERE子句不能包含聚合函数。

l  连接查询

在很多情况下，需要从多个表中提取数据，组合成一个结果集。如果一个查询需要对多个表进行操作，则将此查询称为连接查询。 

   连接查询包括内连接、外连接和交叉连接。(INNER,OUTER,CROSS)

内连接语法：

    SELECT 列名 [,…n]

    FROM 表1 JOIN 表2

    ON 表1.连接字段 = 表2.连接字段

    JOIN 表3 ON 连接条件2…

    [WHERE search_condition]

外链接：

与内连接相对，参与外连接的表有主次之分。以主表的每一行数据去匹配从表中的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。

   外连接可以分为左向外连接、右向外连接和完整外部连接3种情况。(LEFT OUTER JOIN , RIGHT OUTERJOIN, FULL JOIN)。

语法：

    SELECT 列名 [,…n]

    FROM 表1 [LEFT|RIGHT]OUTER JOIN 表2

    ON 表1.连接字段 = 表2.连接字段

    [WHERE search_condition]

左向外连接：

  左向外连接以连接（JOIN）子句左侧的表为主表，主表中所有记录都将出现在结果集中。如果主表中的记录在右表中没有匹配的数据，则结果集中右表的列值为NULL。（条件也为NULL）

2子查询

  子查询就是在一个SELECT语句中又嵌套了另一个SELECT语句。在WHERE子句和HAVING子句中都可以嵌套SELECT语句。

语法：

        SELECT 列名列表

        FROM表名1

        WHERE 列名x = | [NOT] IN (SELECT列名x FROM 表名2 [WHERE子句])

例：

       SELECT DepID,AVG(StuScore)  

      FROMStudent

      GROUP BY DepID

      HAVING AVG(StuScore) >

                     (SELECT AVG(StuScore) FROMStudent)

使用EXISTS关键字连接子查询：（相当于for循环）

语法：

     SELECT 列名列表

     FROM 表名1

     WHERE [NOT] EXISTS (SELECT *  FROM 表名2 [WHERE子句])

例： SELECT StuID,StuName FROM Student WHERE EXISTS （SELECT * FROM SC WHERE StuID= Student.StuID AND CourseID = 1)

1.  父查询传送列值给子查询

2.  子查询获取父查询传送的列值

3.  子查询返回查询值给父查询

4.  父查询传送下一行的列值给子查询(重复1-3步)

3视图

创建视图。

SQLCREATE VIEW 语法

    CREATE VIEW view_name

    AS

    SELECT column_name(s)

    FROM table_name

    WHERE condition

更新视图。

例：

    update vwStuInfo

    set StuAge = StuAge+1,Score=Score+1

    where StuID = 'A00001'

五、授权与回收权限

SQLServer的安全管理模型中包括SQL Server登录、数据库用户、权限和角色4个主要方面。

SQLServer登录：要想连接到SQL Server服务器实例，必须拥有相应的登录账户和密码。SQL Server的身份认证系统验证用户是否拥有有效的登录账户和密码，从而决定是否允许该用户连接到指定的SQL Server服务器实例。

数据库用户：通过身份认证后，用户可以连接到SQL Server服务器实例。但是，这并不意味着该用户可以访问到指定服务器上的所有数据库。在每个SQL Server数据库中，都存在一组SQL Server用户账户。登录账户要访问指定数据库，就要将自身映射到数据库的一个用户账户上，从而获得访问数据库的权限。

权限：权限规定了用户在指定数据库中所能进行的操作。

角色：类似于Windows的用户组，角色可以对用户进行分组管理。可以对角色赋予数据库访问权限，此权限将应用于角色中的每一个用户。

1.创建登录账户

使用sp_addlogin存储过程可以创建新的登录账户。该登录允许用户使用 SQL Server 身份验证连接到 SQLServer 实例。

语法：

sp_addlogin‘登录名称’,‘登录密码’,‘数据库名’

2.修改登录账户密码

使用sp_password存储过程可以修改登录账户的密码。

语法：

sp_password'旧密码', '新密码', '登录账户名'

3.删除登录账户

使用sp_droplogin存储过程可以删除登录账户。

语法：

sp_droplogin'登录账户名'            

4.创建数据库用户

通过SQL Server的身份验证并不代表用户就能够访问SQL Server中的数据，要访问某个具体的数据库，还必须使登录账户成为某数据库的用户。

   使用sp_grantdbaccess存储过程创建数据库用户。

语法：

   sp_grantdbaccess ‘登录名’

5.删除数据库用户

使用sp_revokedbaccess存储过程删除数据库用户。

语法：

   sp_revokedbaccess‘数据库用户名’

6.创建角色

利用角色，SQL Server管理者可以将某些用户设置为某一角色，对一个角色授予、拒绝或废除的权限也适用于该角色的任何成员，这样只需对角色进行权限设置便可以实现对所有用户权限的设置，大大减少了管理员的工作量。

   sp_addrole存储过程的功能是创建SQL Server角色，

语法：

   sp_addrole '数据库角色名'

7.删除角色

sp_droprole存储过程的功能是创建SQLServer角色，

语法：

   sp_droprole '数据库角色名'

8.为角色添加成员

sp_addrolemember存储过程的功能是向角色中添加用户，

语法如下：

   sp_addrolemember '数据库角色名','数据库用户名'

9.为角色删除成员

sp_droprolemember存储过程的功能是向角色中添加用户，

语法如下：

   sp_droprolemember '数据库角色名','数据库用户名'

 

权限管理

权限决定了用户在数据库中可以进行的操作。可以对数据

库用户或角色设置权限。

权限的种类：

(1) 对象权限

SELECT

INSERT

UPDATE

DELETE

(2) 语句权限

语句权限表示一个用户对数据库的操作权限，如能否执行创建和删除对象的语句，能否执行备份和恢复数据库的语句等。

语句权限如下：

BACKUP DATABASE

BACKUP LOG

CREATE DATABASE

CREATE PROCEDURE

CREATE TABLE

CREATE VIEW

DROP DATABASE

1.设置权限包括：

l  授予权限：授予用户、组或角色的语句权限和对象权限，使数据库用户在当前数据库中具有执行活动或处理数据的权限。

l  拒绝权限：包括删除以前授予用户、组或角色的权限，停用从其他角色继承的权限，确保用户、组或角色将来不继承更高级别的组或角色的权限。

l  废除权限：废除以前授予或拒绝的权限。废除类似于拒绝，因为二者都是在同一级别上删除已授予的权限。但是，废除权限是删除已授予的权限，并不妨碍用户、组或角色从更高级别继承已授予的权限。

2.授予权限

基本语法如下：

       GRANT { ALL | 权限 [ ,...n ] }

       {ON { 表 | 视图 |存储过程}

       TO {安全账户 | 角色} [ ,...n ]

      [WITH GRANT OPTION ]

例：

    GRANT SELECT,UPDATE (Score)ON SC TO NewRole

    WITH GRANT OPTION

（更新Score字段，允许该角色将此权限再授予其他用户或角色）

注:当授予UPDATE或DELETE权限时，需要与SELECT配合，否则权限无效，而INSERT不受SELECT影响。

3.废除权限

基本语法如下：

     REVOKE { ALL | 权限 [,...n ] }

         {ON { 表 | 视图 |存储过程}

    FROM 安全账户 [,...n]

例：

    REVOKE SELECT,UPDATE ON SC FROMNewRole

    CASCADE

（如果其授予此权限给其他用户或角色，也将废除其他用户或角色拥有的此权限）

注：假设A和B同时授予C对表的SELECT权限，此时A废除了C的SELECT权限，但B并没有废除，因此，C仍然对表有SELECT权限。

4.拒绝权限

基本语法如下：

    DENY{ ALL| 权限 [ ,...n ] }

         {ON { 表 | 视图 |存储过程}

    FROM 安全账户 [,...n]

例：

    DENY SELECT,UPDATE ON SC TONewRole

    CASCADE

注：只有sa才有权限使用DENY语句。