SQL语法整理[2]——用主外键实现表关联及数据库管理

 

SQL语法整理[2]——用主外键实现表关联及数据库管理

用主外键实现表关联

主键

主键是表中的一列或者多列数据,用来惟一标识一行数据.在表中,不允许有主键相同的行存在.在受主键约束的列中,不能有空值(NULL)出现。为了有效实现数据的管理，每一张表有应该有一个主键,而且只能有一个。主键可能是列的组合。

 

创建表时创建主键

id  INT  CONSTRAINT  pk_test1_id    PRIMARY  KEY

 

查看表格属性

EXECUTE   SP_HELP    [表名]

 

有时候需要有多个列的联合,才能惟一标识一条信息

CREATE     TABLE    book

(

   isbn  INT,              ---图书编号

   copy_no   INT,         ---图书副本号

   book_name   CHAR(20),  ---图书名称

   CONSTRAINT  pk_book_isbn_copy_no  PRIMARY KEY(isbn,copy_no) ---定义表级主键约束

)

 

创建表后添加主键

ALTER TABLE table

ADD CONSTRAINT constraint_name PRIMARY KEY(column[,…n])

 

修改列的值，用update。修改列的属性，用alter table…alter column [列名]

 

外键

外键约束主要是为了维护两个表之间的一致性关系,是建立在主键的基础之上的,其值是参考主键的内容。主键的值是惟一的,不能为空,而外键的值可以是重复的、为空的，外键的数值必须在主键中出现。在同一张表中可以有多个外键，每一个外键的创建都必须有一个主键相对应，外键可以是一个列，也可以是多个列的组合。

 

在创建表时，定义外键

CREATE TABLE table_name    

(                          

column_name datatype       

[CONSTRAINT constraint_name]

{                          

[FOREIGN KEY]              

REFERENCES referenced_table_name[(ref_column)]

}                                             

)                                             

e.g=〉

CREATE     TABLE       junior_member

(

  member_id   INT,

  adult_member_id  INT,

  member_name   CHAR(10),

  member_gender  CHAR(2),

  CONSTRAINT    pk_junior_member_id   PRIMARY  KEY(member_id),

  CONSTRAINT    fk_junior_ adult_member_id FOREIGN  KEY (adult_member_id)

  REFERENCES   adult_member(member_id)

)

 

/*为表格adult_member创建一个外键约束*/

ALTER     TABLE       adult_member

ADD  CONSTRAINT   fk_adult_member_id_member_id

FOREIGN   KEY(member_id)   REFERENCES    member(member_id)

 

表关联种类

表之间(也就是实体之间)有一定的关联种类，实体之间的不同种类，决定着实体或表格之间的对应关系。有的可以拆成1对1的关系，有的可以拆成1对n的关系，有的可以拆成n对n的关系，不同的关系拆成不同数量的表

 

在关系数据库中有3个要素：

实体(Entity):客观存在的事物。例如：学生，老师，家长。

属性(Attribute):实体所具有的特性。例如：学生的姓名，年龄，性别。

关系(Relation):实体与实体之间的关系。例如：学生和老师，学生与家长。

 

a.一对一的关系

     两个实体中，如果有一个实体的数值确定了，则另一个实体的数值也就惟一确定了， 反之也是如此,那么这种对应关系就是一对一(1:1).

     一对一的关系实体在数据库中能够拆分为两张表,为了描述表之间的关联关系，通过  主外键的方式联系起来.

b.一对多的关系

     实体A与B是一对多(1:n)的关系,是指当A中的一个值确定以后,可以对应到B中的  多个值;反过来，当B中的一个值确定以后,惟一地能够对应A中的一个值.

     一对多的关系可以拆分成两张表，通过主外键的方式联系起来。

c.多对多的关系

     实体A与B是多对多(m:n)的关系,是指当选中A的一个数值确定后，可以对应到B        中的多个值;反过来，当B中一个值确定以后也可以对应A中的多个值.

     多对多的关系可以拆分成为3张表，中间的关系表与其他两张表建立起主外键的关   系,通过这张关系表来体现这两个实体之间的关系。

 

删除主外键

删除是有一定的顺序的。

要先删除外键的信息，然后再删除主键，否则就会报错。

如果要删除主键，要先看是否被外键引用，如果被引用那么就会报错。

 

数据库的管理

查看数据库属性

EXECUTE     SP_HELPDB    [数据库名]

 

数据库更名

EXECUTE   SP_RENAMEDB   ‘newdemo’,’demo’

（将数据库newdemo变更名称为demo）

 

文件组更名

USE    master

GO

ALTER    DATABASE    school

MODIFY    FILEGROUP   teachers

NAME=teacher

GO

 

数据文件更名

/*将school数据库中数据文件逻辑名称为school2变更为school3*/

USE      school

GO

ALTER     DATABASE     school

MODIFY    FILE

(

   NAME=school2,

   NEWNAME=school3,

)

GO

 

修改数据文件大小

/*修改数据库school中逻辑名称为school2的大小，将其变为23MB*/

USE    school

GO

ALTER    DATABASE      school

MODIFY    FILE

(

   NAME=school2,

   SIZE=23

)

GO

 

/*为数据库school添加一个叫linshi2的文件组*/

ALTER     DATABASE   school

ADD   FILEGROUP    linshi1

GO

 

/*为数据库school中文件组linshi1添加一个数据文件叫linshi2*/

ALTER    DATABASE    school

ADD   FILE

(

   NAME=linshi2,

   FILENAME=’C:\DATA\linshi2.ndf’,

   SIZE=3,

   MAXSIZE=20,

   FILEGROWTH=1

)

TO   FILEGROUP   linshi1

GO

 

/*为数据库school添加一个日志文件，逻辑名称为school_log4*/

ALTER    DATABASE    school

ADD   LOG  FILE

(

  NAME=school_log4,

  FILENAME=’C:\DATA\school_log4.ldf’,

  SIZE=3,

  MAXISZE=20,

  FILEGROWTH=1

)

GO

 

删除文件组/数据文件/日志文件

如果要删除文件组,必须要先删除文件组内的数据文件,否则不可能删除文件组.

如果要删除数据文件,数据文件不能为空。如果数据文件内容不为空也不能删除数据文件,日志文件也是如此,如果不为空的情况下是不能删除的，只有为空的情况下才能删除.

如果数据文件有内容，应先执行TRUNCATE TABLE table_name(删除表中的所有数据，只留下一个表的定义),然后再删除数据文件，最后删除文件组。

 

/*先删除数据文件*/

ALTER    DATABASE    school

REMOVE   FILE      linshi2

GO

 

/*再删除文件组*/

ALTER     DATABASE    school

REMOVE   FILEGROUP   linshi1

GO

/*最后删除日志文件*/

ALTER     DATABASE   school

REMOVE   FILE    school_log4

GO

 

分离附加用户数据库

分离、附加用户数据库是一种很好的转移数据库、备份数据库的方法。

/*分离数据库demo*/

USE    master

GO

EXECUTE    SP_DETACH_DB    ‘demo’

GO

 

/*将分离的demo数据库附加到SQL Server实例上*/

USE   master

GO

EXECUTE   SP_ATTACH_DB    ‘demo’,

‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\demo.mdf’

GO

 

（如果数据库正在应用，或者在这个数据库操作环境下，是不可以分离该数据库的，只有在其他数据库的操作环境下，而且要分离的数据库不被使用的前提下，才能够使用SP_DETACH_DB命令分离数据库。）

 

理解master数据库以及系统表在系统中的作用

由master数据库中的系统表识别出用户自定义数据库以后，SQL Server实例将读取用户自定义数据库中的系统表。用户自定义数据库中的系统表将识别出其数据库中的文件组名称，数据文件名称与数据文件物理位置以及数据库对象等。

master数据库中的sysdatabases表中的信息，存储着整个SQL Server实例的数据库信息，由这个表识别了其他数据库，再由用户数据库中类似于sys.filegroups以及sys.database_files的表来识别用户自定义数据库中的内容。就这样一级一级地识别出数据库中的所有对象。

 

 

 

 

 

——大二的期末考试整理 [e-mail]