数据库 表

6.2 修改表

6.2.1 用SSMS修改表

 右击表 选择设计

 6.2.2 使用 T-SQL 修改表

  alt  table 进行修改

 1）添加一列: alter table 表名  add 列名  数据类型  是否为空

alter table course add teacher varchar(10)

 2）删除一列：alter table 表名 drop column 列名

alter table course drop column teacher 

 3）修改一列：一般不要修改列名，在设计时，定义好就不要改了。修改的是数据类型，是否为空

  alter table 表名 alter column 列名 类型 是否为空

alter table student alter column sname varchar(10) null

6.3 约束

6.3.1 PRIMARY KEY 约束

PRIMARY KEY 约束是通过主键对给定的一列或多列 强制实体完整性的约束。每个表只能创建一个PRIMARY KEY 约束。主键不允许空值，且不同两行的键值不能相同，即主键可以唯一标识一个元组。

1.在SSMS定义表的同时设置主键约束

鼠标右击score表 点击设计  选中sno 和cno 右击鼠标设置主键

2.使用T-SQL语句在已有表上添加主键约束

alter table 表名 add  constraint 约束名 primary  key (列名）

use MyDB
go
alter table student add constraint PK_ST primary key (sno)

 3.删除主键约束

alter table 表名 drop  constraint 约束名

use MyDB
go
alter table student drop constraint PK_ST 

6.3.2 UNIQUE约束

唯一性约束确保在非主键列中不输入重复的值，用于指定一个或者多个列的组合值具有唯一性，以防在列中输入重复的值。可以对一个表定义多个UNIQUE约束，UNIQUE约束允许NULL值，当表中已经有一个主键时，就可以使用唯一性约束。

1.在创建表的同时设置唯一性约束

将课程号cno设置为主键约束，课程名cname 设置为唯一约束

use MyDB
go
create table course 
(cno char(4）primary key,
cname varchar(10) unique  not null)

2.在修改表时设置唯一约束

alter table 表名 add constraint 约束名 unique（列名）

将课程名cname设置为唯一约束

use MyDB
go
alter table course add constraint UK_NA UNIQUE(cname)

 

6.3.3 FOREIGN KEY约束

外键(foreign key) 表示两个表之间的相关联系。如果某个字段在一个表中是主键，在另外一个表里面也作为一列保存，那么这个主键被称为另一个表的外键。
用于强制参照的完整性，提供单个字段或多个字段的参照完整性

1.在SSMS中创建表之间的关系

 

 将student表主键的图表拖动到Sc表sno字段上建立关系 分别为主键表和外键表

2.使用T-SQL语句在创建表时定义外键约束

constraint 约束名 foreign key(主键) references 从表名(从表主键)

create table A (
       anumber int primary key , -- 字段名 约束
       aname char(11) unique
       bnumber int
);
create table B (
      Bnumber int primary key,
      bname varchar(11),
      anumber int,
      constraint a_b foreign key(bnumber) references B(bnumber)
);

3.表已经创建成功后再添加外键约束

alter table 从表名 add constraint 外键约束名称 foreign key(列名) references 主表名称(主键列名)

alter table B add constraint B_A foreign key(bnumber) references A(anumber);

 4.删除外键约束

alter table 表名 drop constraint  约束名

alter table B drop constraint B_A

6.3.4 check 约束、

1.在SSMS管理check约束

鼠标右击student表 点击设计

找到 管理check约束 选项卡

点击添加选项 再输入表达式 ssex='男' or ssex='女'  

再点击关闭

再student中点击 ➕展开

再展开约束 可以看到check约束已经创建完成

2.使用T-SQL创建表时创建check约束

以下省略部分代码

create table student
( sno char(10) not null primary key,
sage tinyint not null check(sage>18)

3.使用T-SQL修改表时创建检查约束

alter table 表名 add constraint 约束名 check (逻辑表达式)

alter table student add constraint CK_student 
check(sage>10)

4.删除check约束

alter table 表名 drop constraint 约束名

alter table student 
drop constraint CK_student

5.检查约束表达式

6.3.5 default 约束

1.在SSMS中设置默认值约束

 打开student表 设计 选项 单击ssex

在下方列属性中找到默认值或绑定

下图我将ssex的默认值设置为 男

2.在使用T-SQL创建表时创建默认值约束

create table student
（ sno char(10) primary key,
sname varchar(10) null,
ssex char(2) default '男')

3.在使用T-SQL修改表时创建默认约束

alter table 表名 add constraint 约束名 default （默认值) for 列名

ALTER TABLE student
ADD CONSTRAINT DF_student DEFAULT getdate() for en_time

GETDATE() 函数从 SQL Server 返回当前的时间和日期

4.删除默认值约束

alter table 表名 drop constraint 约束名

alter table student
drop constraint DF_student

 6.4 表数据的操作

6.4.1 插入数据

1.使用SSMS工具插入数据

鼠标右击student表 点击编辑前200行

 再向表中编辑数据，输入完成后，关闭窗口系统自动保存数据

2.使用T-SQL插入数据

​

    --1.使用 insert values 插入一行数据 
	语法:
	insert [into] 表名[字段,字段,字段,...] 
	values('值','值'，'值',...)
	例如:
	insert into Student(StudentNo,StudentName,Age)
	values('202202','小明',19)
	
	--2.简写 省略列名,列表数量和表中的值以及数据类型必须一致,自增列除外
	语法:
	insert 表名
	values('值','值','值',...)
	例如:
	insert Student
	values('202202','小明',19)

	--3.使用 insert values 插入多行数据
	语法:
	insert [into] 表名[字段,字段,字段,...]
	values('值','值','值',...),('值','值','值',...)....
	例如:
	insert Student
	values('202202','小明',19),('202203','小红',20)
​

6.4.2修改数据

1.使用SSMS工具修改数据

和插入数据类似，点击“编辑前200行”命令，然后就可进行修改操作

2.使用T-SQL修改数据

--使用 update 更新数据
    语句:
    update 表名
    set 列=更新值,列=更新值,...
    [where 更新条件]

 注意事项: 1.如果不添加where条件会更新所有数据
                2.在更新数据时,最好添加上where条件

    例如: 

--不带条件会更新所有数据
	update Student
	set Sex='男'
--待条件会更新满足条件的数据
	update Student
	set Sex='女'
	where StudentName='小明'

6.4.3 删除数据

1.使用SSMS工具删除数据

同样，选择“编辑前200行”命令，右击要删除的数据，删除后单击“保存”

2.使用T-SQL语句删除数据

两种删除方式的区别：
    1.truncate删除数据后,标识列会从新标记
      delete删除数据后,标识列不会从新标记
    2.truncate比delete删除时运行数据快，消耗系统资源和事务日志资源更少
    3.truncate删除数据后不能恢复,一般情况下不会使用

--使用delete 删除数据
    语法:
    delete  表名
    [where 删除条件]

注意事项：不带where条件，会删除所有数据

--带条件删除一行数据
	delete from Student
	where StudentName='小明'
--不带条件删除所有数据
	delete from Student

--使用truncate table 删除所有数据
    语法:
    truncate table 表名

truncate table Student

6.4.4 同步数据

MERGE关键字是一个神奇的DML关键字，它能将INSERT，UPDATE，DELETE等操作并为一句，根据与源表联接的结果，对目标表执行插入、更新或删除操作。

MERGE的语法

MERGE INTO 目标表 a
USING 源表 b
ON（a.条件字段=b.条件字段）
WHEN MATCHED THEN 
操作语句
WHEN NOT MATCHED THEN 
操作语句

注意：其中最后语句分号不可以省略，且源表既可以是一个表也可以是一个子查询语句 

merge无法多次更新同一行，也无法更新和删除同一行

当源表和目标表不匹配时：

• 若数据是源表有目标表没有，则进行插入操作；

• 若数据是源表没有而目标表有，则进行更新或者删除数据操作

当源表和目标表匹配时：

• 进行更新操作或者删除操作

MERGE的使用场景

• 数据同步

• 数据转换

• 基于源表对目标表做INSERT,UPDATE,DELETE操作

我们常用的是第三种场景

MERGE使用限制

  1. 在 MERGE MATCHED 操作中，只能允许执行 UPDATE 或者 DELETE 语句。

  2.  在 MERGE NOT MATCHED 操作中，只允许执行 INSERT 语句。

  3. 一个 MERGE语句中出现的MATCHED操作，只能出现一次 UPDATE 或者 DELETE 语句

MERGE示例

下面我们通过一个示例来介绍一下该如何使用MERGE，我们以Customers表和Orders表为例。数据如下：

Customers

Orders

Q：当Customers表里的客户有购买商品，我们就更新一下他们的下单时间，将他们的下单时间往后推迟一小时，如果客户没有购买商品，那么我们就将这些客户的信息插入到订单表里。

根据上面的要求我们可以这样写SQL：
 

MERGE INTO Orders O
--确定目标表Orders
USING Customers C ON C.客户ID=O.客户ID  
--从源表Customers确定关联条件 C.客户ID=O.客户ID

WHEN MATCHED  
--当匹配时对目标表的订单日期执行更新操作
THEN UPDATE SET O.订单日期=DATEADD(HOUR,1,O.订单日期)

WHEN NOT MATCHED BY TARGET
--当不匹配时对目标表进行插入操作
THEN INSERT (客户ID,员工ID,订单日期,发货ID)
VALUES (C.客户ID,NULL,NULL,NULL)
;

 Order表变为

 

我们发现与Customers表里匹配上的订单日期被修改了，订单日期往后推迟了一小时，而没有匹配上的在订单表尾部增加了几行记录。