--关于主外键的练习--添加和删除主键与外键语法--删除外键 语法：alter table 表名 drop constraint 外键约束名 --添加外键 语法：alter table 表名 add constraint 外键约束名 foreign key(列名) references 引用外键表(列名) --删除主键 语法：alter table 表名 drop constraint 主键约束名 --增加主键 语法：alter 表名 add constraint 主键约束名 primary key(列名) --删除主键alter table sc drop constraint pk_sc--删除外键alter table sc drop constraint fk_studentalter table sc drop constraint fk_course--创建学生表create table student(Id int identity(1,1) Primary key,name varchar(12),age Int,sex char(1),class varchar(6));insert into student values('p0001','王军',21,1,'c101'); -- ('p0001','王军’,20,1,‘c101');insert into student values('p0002','张宇',21,1,'c102');  --('p0002','张宇’,21,1,‘cl01');insert into student values('p0003','刘飞',22,1,'c103');INSERT INTO student VALUES('p0004','赵燕',18,0,'c103');INSERT INTO student VALUES('p0005','曾婷',19,0,'c103');INSERT INTO student VALUES('p0006','周慧',21,0,'c104');INSERT INTO student VALUES('p0007','小红',23,0,'c104');INSERT INTO student VALUES('p0008','杨晓',18,0,'c104');INSERT INTO student VALUES('p0009','李杰',20,1,'c105');INSERT INTO student VALUES('p0010','张良',22,1,'c105');--创建课程表create table course(id int identity(1,1) Primary key,  --identity(1,1) 从1开始,每次自增1sname varchar(12) ,credit numeric(2,1), --数字类型，总长度2  = 整数长度+小数长度。保留1位小数。teacher varchar(12));insert into course values('c001','Java',3.5,'李老师');insert into course values('c002','高等数学',5.0,'赵老师');insert into course values('c003','JavaScript',3.5,'王老师');INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');--创建选修表--选修表就是关系表或中间表，提供了学生表到课程表的映射关系create Table sc(sid varchar(10) not null,cid varchar(10) not null);alter table sc Add constraint pk_sc Primary key(sid,cid); --添加主键组alter table sc add constraint fk_student Foreign key(sid) references student(id); --添加外键约束alter table sc add constraint fk_course Foreign key(cid) references course(id);--添加外键约束insert into sc values('p0001','c001');insert into sc values('p0001','c002');insert into sc values('p0001','c003');insert into sc values('p0002','c001');insert into sc values('p0002','c004');INSERT INTO sc VALUES('p0003','c002');INSERT INTO sc VALUES('p0003','c005');INSERT INTO sc VALUES('p0004','C003');INSERT INTO sc VALUES('p0005','C001');INSERT INTO sc VALUES('p0006','C004');INSERT INTO sc VALUES('p0007','C002');INSERT INTO sc VALUES('p0008','C003');INSERT INTO sc VALUES('p0009','C001');INSERT INTO sc VALUES('p0009','C005');--查询没有选课的学生信息和无学生选的课程信息select *from student where student.id not in(select sid from sc);select *from course where course.id not in (select cid from sc);SELECT student.* FROM student LEFT JOIN sc ON student.id=sc.sid LEFT JOIN course ON course.id = sc.cid WHERE course.sname IS NULL;SELECT course.* FROM course LEFT JOIN sc ON course.id=sc.cid LEFT JOIN student ON student.id = sc.sid WHERE student.sname IS NULL;--删除表drop table student;drop table course;drop table sc; --以上练习材料来自互联网