【SQL server数据库基础】增、删、改基本语法与使用

1. 插入 insert

① 插入单条记录

insert into dbo.StudentCourse -- insert 需要插入的表名 into 可写可不写
values(1,50,0,0,0,null,null) -- 插入的值，跟随原表字段输入数据，自动增长值 identity 不写入括号类

② 插入多条记录

insert dbo.StudentCourse 
values(1,50,0,0,0,null,null),
	  (2,50,0,0,0,null,null),
	  (3,50,0,0,0,null,null),
	  (4,50,0,0,0,null,null)  -- 逗号分隔

③ 插入部分数据

insert dbo.TeachCourse(TeachInfoID,CourseInfoID)  -- 教师课程表
values(18,17)  -- 张绪良老师教师编号 18，《软件工程》课程编号为 17。外键约束不能随意插入

④插入其他表的数据

create table StudentInfo1  -- 先创建表
(
	StdInfoNum varchar(20) not null,
    StdInfoName varchar(30) not null,
    StdInfoSex varchar(2) not null,
    StdInfoCard varchar(18),
    StdInfoAddress varchar(60),
    StdInfoTel varchar(30)
)
insert dbo.StudentInfo1 -- 往里面插入数据
select StdInfoNum,StdInfoName,StdInfoSex,StdInfoCard,StdInfoAddress,StdInfoTel
from dbo.StudentInfo
where StdInfoSex = '女'

2. 更新 update

update dbo.StudentInfo  -- update 需要修改的表名
set StudentName = '李禅林',  -- set 设置
	StudentTel = '15874584299',
	StudentEmail = 'lichan8299@163.com'
where StudentName = '李禅林'  -- 一定要加 where 条件语句，否则会修改字段全部记录

update dbo.TeachInfo
set TeachInfoRMK = '每周上课不超过10课时'
from dbo.TeachInfo a join dbo.TeachType b
on a.TeachTypeID = b.TeachTypeID
where TeachTypeName = '兼职'

update dbo.StudentCourse
set StudCourseExamGrd += 5
where StudCourseExamGrd < 60 and TeachCourseID in
(
	select TeachCourseID from dbo.TeachCourse
    where CourseInfoID = (
    	select CourseInfoID from dbo.CourseInfo
        where CourseInfoName = '网页设计'
    )
)

3. 删除 delete

delete dbo.StudentCourse
where StdInfoID = 50 and TeachCourseID =4

delete dbo.StudentCourse
from dbo.StudentCourse a join dbo.StudentInfo b
on a.StdInfoID = b.StdInfoID
where StdInfoName = '刘立'

delete dbo.ClassInfo -- 有约束先删约束，
truncate table dbo.StudentInfo -- truncate 效率更高

主文章：备考计算机三级数据库——SQL 案例