[Ms SQL] 基本創建、修改與刪除

##創建 table student， 內涵 id ,name ,tel三種columne，設定id為primary key 
create table student
(
id int primary key,
name char(20),
tel char(20),
)

##創建 table course，內涵 id ,name ,score三種columne，其中將id與student的id相綁
create table course

(
id int,
name char(20),
score int,
foreign key(id) references student(id)
)

#輸入一筆數據，輸入資料依照順序，或是給定指定順序
insert into student values(100, 'Andy', '0950623548');
insert into student(id, name) values(101, 'Mandy');

#查詢成果
select * From student

#輸入一筆數據。因id綁定參照，若student中無可參照的id值則會報錯
insert into course values(101, 'Java', 80)
insert into course values(101, 'Java2', 70)
insert into course values(109, 'Java2', 70)

#查詢成果
select * From course

#修改時where為指定之位置
update student set tel ='0956235846' where id = 101
update course set score = 80 where id = 101

#直接刪除student會報錯，因為course仍有資料參照student的id
delete from student where id = 101
#要先刪除course中有資料參照指定student id的資料，才能刪除student的id
delete from course where id = 101
delete from student where id = 101