SQL之操作数据表详解

SQL之操作数据表

数据表基础

基本数据类型

基本数据类型按照数据的表现方式及存储方式的不同可以分为：

• 整数数据类型：常用的数据类型，可以存储整数或小数。数据类型如下：
  • BIT
  • INT
  • SMALLINT
  • TINGINT
• 货币数据类型：用于存储或货币值，使用时在数据前面加上货币符号，不加货币符号的情况下默认为”￥“。数据类型如下：
  • MONEY
  • SMALLMONEY
• 浮点数据类型：用于存储十进制小数。数据类型如下：
  • REAL
  • FLOAT
  • DECIMAL
  • NUMERIC
• 日期/时间数据类型：用于存储日期类型和时间类型的组合数据。数据类型如下：
  • DATETIME
  • SMALLDATETIME
  • DATA
  • DATETIME(2)
  • DATETIMESTAMPOFFSET
• 字符数据类型：用于存储各种字母、数字符号和特殊符号。数据类型如下：
  • CHAR
  • NCHAR(n)
  • VARCHAR
  • NVARCHAR(n)
• 二进制数据类型：用于存储二进制数。数据类型如下：
  • BINARY
  • VARBINRY
• 图像和文本数据类型：用于存储大量的字符及二进制数据。数据类型如下：
  • TEXT
  • NTEXT(n)
  • IMAGE

用户自定义数据类型

用户自定义类型并不是真的数据类型，知识提供一种加强数据内部元素和基本数据类型之间的一致性的机制。通过使用用户自定义是英语话剧能够简化对常用规则和默认值的管理。

使用Transcat-SQL语句创建用户自定义数据类型

在SQL Server2012中使用数据类型sp_addtype创建用户自定义数据类型。

语法如下：

sp_addtype[@typename=]type,
[@phystype=]system_data_type
[,[@nulltyoe=]'null_type']
[,[@owner=]'owner_name']

参数说明：

• [@typename=]type:指定待创建的用户自定义数据类型名称。用户自定义数据类型名称必须遵循标识符的命名规则，而且在数据库中唯一
• [@phystype=]system_data_type:指定用户自定义数据类型所依赖的系统数据类型
• [@nulltype=]’null_type’:指定用户自定义数据类型的可空属性。

实例

在SPJ数据库中，创建用来存储邮政编码的信息的postcode用户自定义数据类型。SQL语句如下：

use SPJ
exec sp_addtype
postcode, 'char(8)', not null

数据表的数据完整性

• 数据完整性：指列中每个事件都有正确的数据值。数据值的数据类型必须正确，并且数据值必须唯一正确的域中。

• 引用完整形：指示表之间的关系得到正确维护。一个表中的数据只应只想宁一个表中的享有行，不应指向不存在的行。

SQL Server 2012中多种强制性数据完整机制如下：

• 空值与非空值（NULL或NOT NULL）：列可以定义为允许或不允许
  • 允许空值（NULL）：默认情况下，列允许空值，即允许用户在添加数据是省略该列的值
  • 不允许空值（NOT NULL）：不允许空值在没有指定列默认值的情况下省略该列的值
• 特定表示属性（IDENTITY）：数据表中如果某列被指派特定表示属性（IDENTITY），系统将自动为表中插入的新行生成连续递增的编号。因为标识值通常唯一，所以标识列常定义为主键。
• 约束：用来定义Microsoft SQL　Server 2012自动强制数据库完整性的方式。使用约束优先于使用触发器、规则和默认值。SQL Server 2012中共有一下物种约束：
  • 非空（NOT NULL）：使用户必须在表的指定列中输入一个值。每个表中可以有多个非空约束。
  • 检查（Check）：用来指定一个布尔操作，限制输入到表中的值。
  • 唯一性（Unique）：使用户的应用程序必须向列中输入一个唯一的值，值不能重复，但可以为空。
  • 主键（Primary Key）：建立一列或多列的组合以唯一表示表中的每一行。主键可以保证实体完整性，一个表只能由一个主键，同时主键中的列不能接受空值
  • 外键（Foreign Key）：用于建立和加强两个数据表数据之间的连接的一列或多列。当一个表中作为主键的一列被添加到另一个表中时，连接就建立了，主要目的是控制存储在外键表中的数据。

数据表的创建与管理

使用create table语句创建表

create table语句基本语法

create table
[database_name . [schema_name] . |schema_name . ] table_name
(
{<column_definition>|<computed_column_definition>|<column_set_definition>}
[<table_constraint>]
)
<column_definition>::=column_name <data_type>[FILESTREAM ][COLLATE collation_name][NULL | NOT NULL]

参数说明

• database_name:在其中创建数据表的数据库的名称。database_name必须指定现有的数据库的名称。如果未指定，则默认当前数据库
• schema_name:新表所属架构的 名称
• table_name:新表的名称。表名必须符合标识符规划
• <column_difinition>:列的定义
• column_name:表中列的名称

实例

使用create table语句创建数据表student，ID字段为int类型并且不允许为空；Name字段长度为50的varchar类型；Age字段为int类型。

use STC -- 打开数据库

crtea table [STC].[dbo].student -- 创建表
(
ID int not null,
Name varchar(50),
Age int    
)

使用alter table语句修改表结构

alter table语句基本语法

alter table [[database_name].[schema_name].|schema_name .] table_name
{
alter column column_name
{
[type_schema_name.] type_name [({precision [,scale]|max|xml_schema_colllection})]
[colleate collate_name][NULL | NOT NULL]
|{rowguidcol | presisted | not for replication | sparse}
}
|[with {Check | NOCheck}]
|add {<column_definition>|<computed_column_definition>|<table_constraint>|<column_set_definition>}
|drop {[constraint] constraint_name [with (<drop_clistered_constraint_option>)]
|column column_name
}
}

参数说明

• database_name:创建表时数据库名称
• schema_name:表所属架构名称
• table_name:要更改表的名称
• alter column:指定要更改命名列
• column_name:要更改、添加或删除的列的名称
• [type_schema_mame]type_name:更改后的列的数据类型或新添加的列的数据类型
• precision:指定的数据类型的精度
• scale:指定的数据类型的小数位数
• max:仅应用于varchar、nvarchar、varbinary
• xml_schema_collection:仅应用于xml数据类型
• collection<collection_name>:指定更改后的列的新排序规则
• NULL | NOT NULL:指定列是否可插入空值
• [{add|drop}rowguiedcol]:指定在列的添加或删除rowguiedcol属性
• [{add|drop}persisted]:指定列中添加或删除persisted属性
• drop{[constraint] constraint_name | [column] column_name}:指定从表中删除constraint_name或column_name，可以列出多个列或约束

实列

向数据库STC中的student表中添加Sex字段。

use STC -- 使用数据库

alter table student -- 更改表
add Sex char(2) -- 添加Sex字段

删除student表中的Sex字段。

use STC

alter tbale student
drop column Sex

使用drop table语句删除表

drop table语句基本语法

drop table [database_name].[schema_name].table_name[......,n]

参数说明

• database_name:要在其中删除表的数据库名称
• schema_name:表所属架构名称
• table_name:要删除的表的名称

实例

删除STC数据库中的student表。

use STC

drop table student

数据管理

使用insert语句添加数据

insert语句可以实现向表中添加新纪录的操作。该语句可以向表中插入一条新纪录或插入一个结果集。

insert语句语法

insert [into] table_or_view_name
V(eAxLpUreEsSsion) [,...n]

参数说明

• table_or_view_name:要接收数据的表或视图的名称
• values:引入要插入的数据值的列表
• expression:一个常量、变量或表达式。

实例

利用insert语句向数据表student添加数据记录。

use STC

insert into student(ID, Name, Age)
values(123, '李四', 15)

如果要向表中添加所有的字段，可以省略要插入的数据的列名。

use STC

insert into student
values(123, '李四', 15)

使用update语句修改数据

修改表中不符合要求的数据或错误的字段时，可以使用update语句进行修改。

update语句语法

update table_or_view_name
[from{<table_sourse>}[,...n]]
set 
{
column_name = {expression | default | BULL}
[where <serch_condition]
}

参数说明

• table_or_view_name:要更改行的表或视图名称
• from<table_sourse>:指定表、视图或派生表源用于为更新操作提供条件
• expression:返回单个值的变量、文字值、表达式或嵌套select语句
• default:指定用列定义中的默认值替换列中现有值
• where:指定条件来限定更新的行，更据使用的where子句形式，有两种更新形式
• <serch_condition>:为更新的行指定满足的条件。

实例

将student表中所有学生的年龄加两岁。

use STC

update student
set Age = Age + 2

将student表中“李四”的性别修改为女。

use STC

update student
set Sex = '女'
where Name = '李四'

使用delete语句删除数据

delete语句用于从表中或视图中删除行。

delete语句语法

delete 
[from <table_sourse>][,...n]]
[where {<search_condition>}]

参数说明

• from<table_sourse>:指定表、视图或派生表源用于为更新操作提供条件
• where:指定用于限定删除行数的条件。如果没有提供where子句，则删除表中所有行
• <search_condition>:指定删除行的限定条件

实例

删除student表中ID为123的学生的信息。

use STC

delete from student 
where ID = 123

创建、删除和修改约束

非空约束

创建非空约束

可以使用create table创建表时，使用NOT NULL关键字指定非空约束。

创建表时指定非空约束语法

[constraint <约束名>] NOT NULL

修改非空约束

修改非空约束语法

alter table table_name
alter column column_name
column_type NUll | NOT NULL

参数说明

• table_name:要修改非空约束的表名称
• column_name:要修改非空约束的列的名称
• column_type:要修改非空约束的类型
• NOT NULL | NULL:修改为空或非空

实例

修改student表中的非空约束。

use STC -- 使用数据库

alter table student 更改表
alterr column ID int NULL -- 更改ID字段属性

主键约束

可以通过定义primary key约束来创建主键，用于强制表的实体完整性。

创建主键约束

在创建表时创建主键约束

实例

创建数据表student并将字段ID设置为主键约束。

use STC -- 使用数据库

create table [STC].[dbo].student -- 创建数据表
(
ID int constraint P_ID primary key, -- ID字段，设为主键约束
Name char(50),
Sex char(2),
Age int    
)

在现有表中创建主键约束

在现有表汇总创建主键约束语法

use STC

alter table student
add constraint constraint_name primary key
[clustered | nonclustered]{(column[,...n])}

参数说明

• constraint：创建约束的关键字
• constraint_name:创建约束的名称
• primary key:表示所创建约束的类型为主键约束

实例

将student表中的ID字段指定为主键约束。

use STC

alter constraint P_ID primary key(id) -- 对ID字段添加主键约束

修改主键约束

若要修改primary key约束，必须先删除现有的primary key约束，然后重新定义主键约束。

删除主键约束

删除主键约束语法

alter table tble_name
drop constraint constraint_name[,...n]

实例

删除student表中的主键约束

use STC

alter tbale student
drop constraint P_ID -- 删除主键约束

唯一约束

唯一约束UNIQUE用于强制实施列集中中值的唯一性。

创建唯一性约束

在创建表时创建唯一性约束

实例

在STC数据库中创建数据表student，并将字段ID设置唯一性约束。

use STC

create table [dbo].student
(
ID int constraint UNIQ_ID unique, --设置唯一性约束
Name cher(50),
Sex char(2),
Age int
)

在现有表中创建唯一性约束

语法

alter table table_name

add constraint constraint_name unique(ID)

参数说明

• table_name:要创建唯一性约束的表名称
• constraint_name:唯一性约束名称
• column:要创建唯一约束的列名称

实例

将student表中的ID字段指定设置为唯一约束。

use STC

alter table student
add constraint UNIQ_ID unique(ID)

修改唯一约束

若要修改UNIQUE约束，必须先删除现的UNIQUE约束，然后用新定义重新创建

删除唯一约束

删除唯一约束语法

alter table table_name
drop constraint constraint_name[,...n]

实例

删除student表中的唯一约束。

use STC

alter table student
drop constraint UNIQ_ID

检查约束

检查约束CHECK可以强制域的完整性

创建检查约束

在创建表时创建检查约束

实例

创建数据表student并将字段Sex设置检查约束，在输入性别字段时，只能接受“男”或者“女”，而不能接受其他数据。

use STC

create table [STC].[dbo].student
(
ID int,
Name char(50),
Sex char(2) copnstraint CK_SEX check(sex in ('男', '女'))，
Age int    
)

在现有表中创建检查约束

在现有表中创建检查约束的语法

alter table table_name
add constraint constraint_name check(logic_expression)

参数说明

• table_name:要创建检查约束的表名称
• constraint_name:检查约束名称
• logic_expression:要检查约束的条件表达式

实例

为student表中的Sex字段设置检查约束，在输入性别时只能接受”女“或者”男“，不能解接受其他字段。

use STC

alter table student
add constraint CK_SEX check(Sex in ('男', '女'))

修改检查约束

修改表中某列的CKECK约束使用的表达式，必须首先删除现有的CHECK约束，然后使用新定义重新创建，才能修改CHECK约束。

删除检查约束

删除检查约束语法

alter table table_name

drop constraint constraint_name[,...n]

默认约束

在创建或修改表时可通过定义默认约束DEFAULT来创建默认值。

创建默认约束

在创建表时创建默认约束

实例

创建数据表student并将字段Sex设置默认约束”女“。

use STC

create table [STC].[dbo].student
(
ID int,
Name char(50),
Sex char(2) constraint D_SEX default '女'，
Age int    
)

在现有表中创建默认约束

在现有表中创建检查默认的语法

alter table table_name
add constraint constraint_name default constant_expression [for column_name]

参数说明

• table_name:要创建默认约束的表名称
• constraint_name:检查默认名称
• constant_expression:默认值

实例

为student表中的Sex字段设置默认约束为‘男’。

use STC

alter table student
add constraint D_SEX defaulr '男' for Sex

修改默认约束

修改表中某列的DEFAULT约束使用的表达式，必须首先删除现有的DEFAULT约束，然后使用新定义重新创建，才能修改DEFAULT约束。

删除默认约束

删除默认约束语法

alter table table_name

drop constraint constraint_name[,...n]

实例

删除student表中的默认约束

use STC

alter table student
drop constraint D_SEX

外键约束

通过定义FOREIGN KEY约束来创建外键。

创建外键约束

在创建表时创建外键约束

实例

创建表course，并为course表创建外键约束，该约束把course中的百年好ID字段和表student中的编号ID字段关联起来，实现course中的编号ID字段的取值参照student中的编号ID字段的数据值。

use STC

create table [STC].[dbo].course
(
ID int constraint F_ID foreign key references student(ID)
Name char(50),
time datetime
)

在现有表中创建外键约束

在现有表中创建外键约束的语法

alter table table_name
add constraint constraint_name [foreign key]{(column_name[,...n])}
references ref_table [ref_column_name[,...n]]

参数说明

• table_name:要创建外键约束的表名称
• constraint_name:外键约束名称
• foreign key…references:为列中提供应用完整性约束
• ref_table:foreign key约束所引用的表名
• ref_column[,…n]:foreign key约束所引用的表中的一列或多列

实例

将student表中的ID字段设置为course表中的外键。

use STC

alter table course
add constraint F_ID foreign key(ID) references student(ID)

修改外键约束

修改表中某列的FOREIGN KEY约束使用的表达式，必须首先删除现有的FOREIGN约束，然后使用新定义重新创建，才能修改FOREIGN KEY约束。

删除外键约束

删除外键约束语法

alter table table_name

drop constraint constraint_name[,...n]

实例

删除course表中的外键约束

use STC

alter table course
drop constraint F_ID