SqlServer对表的基本操作

SqlServer对表的基本操作

手动建库建表 


 

脚本建库建表 

  SQL Server关系数据库的数据表结构主要是由记录（行）和字段（列）构成，每一行代表唯一的一条记录（Record），而每列则代表所有记录中的一个域（Field）（也称为字段、属性）。 
注意：在同一个数据库里，表的名字也必须是唯一的。在同一个表里，列的名字必须是唯一的。
SQL Server中的表一共有两类，即永久表和临时表。

• 永久表都保存在数据库文件中
  
• 临时表虽然与永久表很相似，但它们却是存储在tempdb数据库中的，而且当不再使用这些临时表时，它们会被自动删除。
  

SQL Server的数据类型可以分为系统内置的数据类型和用户自定义数据类型。系统数据类型是系统内置的数据类型，主要有：
  1）整数型
    
  2）小数数据类型（精确数据类型）
  3）近似数值型（浮点数据类型）

  4）字符型和Unicode字符型

  5）逻辑数值型

  6）日期和时间数据

  7）二进制数据类型

  8）货币型数据

  9）其他数据类型

定义：
  数据完整性就是要求数据库表中的数据具有准确性。
方法：
  为了维护数据库中数据的准确性，通常是在创建表时为表中的字段定义约束，防止将错误的数据插入到表中。
分类：
  SQL Server中数据完整性包含四种类型分别是：实体完整性、域完整性、参照完整性、用户定义完整性。
（1）实体完整性
  实体完整性将记录（行）定义为特定表的唯一实体，即每一行数据都反映不同的实体，不能存在相同的数据行。
  通过索引、UNIQUE（唯一）约束、PRIMARY KEY（主键）约束、标识列属性可以实现实体完整性。

约束种类	功能描述
PRIMARY	KEY(主键)约束，唯一识别每一条记录的标志，可以有多列共同组成
IDENTITY(自增)约束	列值自增，一般使用此属性设置的列作为主键
UNIQUE(唯一)约束	可以使用UNIQUE约束确保在非主键列中不存在重复值，但列值可以是NULL(空)

（2）域完整性
域完整性指特定字段项的有效性。
可以强制域完整性限制类型（通过使用数据类型）、限制格式（通过使用CHECK约束和规则）或限制可能值的范围（通过使用FOREIGN KEY 约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则）。

名称	描述
CHECK(检查)约束	用于限制列中值得范围
FOREIGN KEY(外键)	一个表中的FORENIGN KEY 指向另一个表中的PRIMARY KEY
DEFAULT(默认值)约束	用于向列中插入默认值
NOT NULL(非空)约束	用于强制列不接受NULL(空)值

（3）参照完整性
在输入或删除数据行时，参照完整性约束用来保持表与表之间已定义的关系。在SQL Server 2016中，参照完整性通过FOREIGN KEY和CHECK约束，以外键与主键之间或外键与唯一键之间的关系为基础。参照完整性确保键值在所有表中一致。这类一致性要求不能引用不存在的值，如果一个键值发生更改，则整个数据库中，对该键值的所有引用要进行一致的更改。
（4）用户定义完整性
用户自定义完整性用来定义特定的规则。例如，输入学生年龄时，只能输入大于0的值。所有完整性类别都支持用户定义完整性。这包括创建表中所有列级约束和表级约束、存储过程以及触发器。
创建表的步骤:

  1）定义表结构：给表的每一列取字段名，并确定每一列的数据类型、数据长度、列数据是否可以为空等。
  2）设置约束：设置约束是为了限制该列输入值的取值范围，以保证输入数据的正确性和一致性。
  3）添加数据：表结构建立完成之后，就可以向表中输入数据了。
create table

1. CREATE TABLE 表名                                        
2. （列1定义，                                  
3.    列2定义，                                    
4.    列n 定义）
5.    
6.    /*其中：
7. < 列定义 > ::= { 列名 数据类型 }
8.        [ [ DEFAULT 约束表达式 ]
9.         | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
10.        ]
11.        [ < 列约束> ] [ ...n ]
12.        */
13.    

复制代码

  案例1.
    创建带有参照约束的学生表，学生表的表结构定义如下表所示。“学号”字段为学生表的主键，“班级代码”字段为学生表的外键，它必须参照班级表中的“班级代码”字段的值。

1. CREATE TABLE 学生(   
2.     // identity表示自增列的意思，而intidentity(1,1)表示从1开始递增，每次自增1。
3.     // primary key的作用是定义主键；主键的值不可以重复，也不可以为空
4. 学号  char(12)   IDENTITY(1,1)  PRIMARY KEY,  
5. 姓名 varchar(20)   NOT NULL,  // not null为不为空
6. 性别 char(2), 
7. 出身日期 datatime, 
8. 入学时间 datatime, 
9. 班级代码 char(9)   CONSTRAINT fk_bjdm REFERENCES 班级(班级代码)  )
11. // 代码中的PRIMARY IDENTITY CONSTRAINT等关键字是对表中各列进行完整性约束的一些词语

复制代码

表的查看

1、 使用系统存储过程Sp_help 查看
  使用系统存储过程sp_help来查看表的属性的方法是：在sp_help后面加上要看的表名作为参数。其格式如下： 
  EXEC sp_help  表名 
    例如，要查看上述在Students数据库中所创建的学生表的属性，可以使用如下语句： EXEC sp_help 学生表
    如果用户想查看该数据库中所有表的属性，则可以直接使用系统存储过程sp_help后不带任何参数来进行显示。 
2、使用SQL Server Management Studio（SSMS）查看 
  除了上述使用系统存储过程来显示表的属性外，在SQL Server Management Studio中查看表的定义会更加方便。其方法如下： 
  1）、在【对象资源管理器】下展开数据库并选中“表”选项，在需要查看表的名称上右键单击，在弹出的快捷菜单中选择“属性”选项，可以打开“表属性”对话框，用户可以查看表中每一列的定义。
表的修改

  要修改表，可以使用ALTER TABLE语句或SQL Server ManagementStudio两种方法来进行。
  1、使用T-SQL 语句ALTER TABLE命令修改表
  使用ALTER TABLE语句对表进行修改常见的情况有以下几种。 
    1、向表中添加新列 

    向表中添加新列时  :  需要在  ALTER TABLE  语句中使用 ADD子句。
    语法格式如下：
      修改表中列的定义  ALTER TABLE 表名 ALTER COLUMN 列名  ;

1. ALTER TABLE 表名 ADD 列名  数据类型  属性1 属性2…….. 
3. //【例】向教材表增加单价列，列名为单价，数据类型为float，默认空值，代码如下： 
5. ALTER TABLE 教材  
7. ADD 单价 float(2)  NULL;
9. GO

复制代码

 
注意：向已存在的表中增加列时，应使新增加的列具有默认值或允许其为空值。添加列完成时，SQL Server将向表中已存在的行填充新增列的默认值或空值。如果既没有提供默认值也不允许为空值，那么新增列的操作将出错，因为SQL Sever不知道该怎么处理那些已经存在的行。
     2、删除表中的列 

      删除表中的列需要使用ALTER TABLE语句的DROP COLUMN子句。 格式： 
      ALTER TABLE 表名 DROP COLUMN 列名 ;  // column是字段，也就是列！
    【例】将教材表中建立的“单价”列删除。其代码如下： 

1. ALTER TABLE 教材 DROP COLUMN单价;
2. GO

复制代码

         3、修改表中列的定义 

        修改表中某列的属性定义语法如下：
             ALTER TABLE 表名 ALTER COLUMN 列名  ;
    例】将教材表中教材名称字段数据类型的长度修改为20

1. ALTER TABLE 教材 ALTER COLUMN 教材名称 varchar(20);
2. GO

复制代码

 

   4、修改表中列的名称

        修改表中某列的列名语法如下：
     EXEC SP_RENAME‘表名.原列名’,‘新列名’, ‘COLUMN’ ;
    【例】将班级表中系部名称修改为院系名称

1. EXEC SP_RENAME ‘班级.系部代码’,‘院系代码’, ’COLUMN’;
2. GO

复制代码

 
 
   数据删除

   1）普通DELETE语句。

     基本语法： DELETE FROM 表名 [WHERE 逻辑表达式 ]
   2）关联DELETE语句。

     DELETE 表名 [FROM 源表名 [ , … N ]] [WHERE 逻辑表达式 ]
     【例】将“选课”表中的学分字段值小于课程表中学分的课程删除。
    代码如下：
[code]DELETE 选课 FROM 选课 a,课程 b WHERE a.课程号=b.课程号 AND a.学分