--------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流! ----------------------
1.表结构的修改:
在创建好关系表的情况下,如果需要对关系表结构进行修改,可以采用如下的方式。
--给已经存在的表添加字段,如果添加多字段,多字段之间用逗号隔开,如果表中没有数据,则可对新加列--使用NOT NULL,如果有数据,则使用NULL,如果一定要使用NOT NULL,则必须给新加列一个默认值,
--以下是在有数据的情况下添加新列
ALTER TABLE Student
ADD Phone CHAR(11) CONSTRAINT DF_Phone_Empty DEFAULT('') NOT NULL ,
Birthday varchar(50) CONSTRAINT DF_Birthday_Empty DEFAULT('') NOT NULL ;
GO
--如果是空表(表中无数据),则可改写为
ALTER TABLE Student
ADD Phone CHAR(11) NOT NULL ,
Birthday varchar(50) NOT NULL ;
GO
--修改字段数据类型,也可以用此方式修改,数据列可为空,先删除对应的约束
ALTER TABLE Student DROP CONSTRAINT DF_Phone_Empty
ALTER TABLE Student ALTER COLUMN Phone BIGINT NULL;
--给已经存在的字段添加约束
ALTER TABLE Student
ADD CONSTRAINT UQ_Phone UNIQUE(Phone),
CONSTRAINT CK_NotNull CHECK (Phone IS NOT NULL),
CONSTRAINT CK_PhoneLen CHECK(LEN(Phone)=11),
CONSTRAINT DF_Sex default('男') FOR Sex; --默认约束
--在已经存在的表中删除一个字段,如果有依赖于此字段的约束,先删除约束
ALTER TABLE Student
DROP CONSTRAINT CK_NotNull, CONSTRAINT CK_PhoneLen, CONSTRAINT UQ_Phone;
GO
ALTER TABLE Student DROP COLUMN Phone;
ALTER TABLE Student DROP COLUMN Birthday;
GO
--修改Sourse表的列PreNo可以为空值
ALTER TABLE Course ALTER COLUMN PreNo INT NULL;--创建Course表示故意定义NOT NULL
2.数据录入:
--向Student表插入数据
INSERT INTO Student(No,Name,Sex,Age,Dept)values('200215121','李晨','男',20,'CS');
INSERT INTO Student(No,Name,Sex,Age,Dept)values('200215122','刘晨','女',19,'CS');
INSERT INTO Student(No,Name,Sex,Age,Dept)values('200215123','王敏','女',18,'MA');
INSERT INTO Student(No,Name,Sex,Age,Dept)values('200215124','张立','男',19,'IS');
GO
--向Course表插入数据
INSERT INTO Course(No,Name,PreNo,Credit)values(2,'数学',null,2);
INSERT INTO Course(No,Name,Credit)values(6,'数据处理',2);
INSERT INTO Course(No,Name,PreNo,Credit)values(4,'操作系统',6,3);
INSERT INTO Course(No,Name,PreNo,Credit)values(7,'PASCAL语言',6,4);
INSERT INTO Course(No,Name,PreNo,Credit)values(5,'数据结构',7,4);
INSERT INTO Course(No,Name,PreNo,Credit)values(1,'数据库',5,4);
INSERT INTO Course(No,Name,PreNo,Credit)values(3,'信息系统',1,4);
GO
--向SC表插入数据
INSERT INTO SC(StudentNo,CourseNo,Grade)values('200215121',1,92);
INSERT INTO SC(StudentNo,CourseNo,Grade)values('200215121',2,85);
INSERT INTO SC(StudentNo,CourseNo,Grade)values('200215121',3,88);
INSERT INTO SC(StudentNo,CourseNo,Grade)values('200215122',2,90);
INSERT INTO SC(StudentNo,CourseNo,Grade)values('200215122',3,80);
GO
--数据查询
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM SC;
3.数据更新:
将计算机科学系全体学生的成绩置零
UPDATE SC
SET Grade=0
WHERE 'CS'=
(SELECT Dept
FROM Student
WHERE Student.No=SC.StudentNo);
4.删除数据:
三种删除方式:
删除某一个元组的值
DELETE FROM Student WHERE No='200215128';
删除多个元组的值
DELETE FROM SC;
带子查询的删除语句
DELETE FROM SC
WHERE 'CS'=
(SELECT Dept
FROM Student
WHERE Student.No=SC. StudentNo);
---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流! ----------------------
详细请查看: http://edu.csdn.net