sql表的创建

--单行注解
/*在此之间多行注解*/

create table dept
(dno char(2)primary key,
 dname varchar(20) not null unique,    --注意not null 和unique之间不要加逗号
);

insert into dept
values('D1','计科系');
insert into dept
values('D2','电信系');

insert into dept
values('D3','机械系');
insert into dept
values('D4','机电系');

 

create table student
(sno char(2) primary key,
 sname varchar(20) not null,
 ssex char(2) not null,check(ssex in('男','女')),
 sage int not null,
 dno char(2) not null         --本方法字段后不用加逗号，另外一种写法是外键的建立不与相应字段紧接
 foreign key references       --如foreign key sno references dept(dno)--sno是外键参照dept表的dno（举例而已实际并不成立）
dept(dno) on delete cascade   --on delete cascade on update cascade(注意在外主键后不要加逗号)
 on update cascade            --可以不使用级联
)

insert into student
values('S1','张三','男',18,'D2');  --整型数据不需要加单引号。
insert into student
values('S2','李四','男',19,'D1');
insert into student
values('S3','王五','男',20,'D3');
insert into student
values('S4','马六','男',19,'D1');
insert into student
values('S5','李白','男',18,'D3');
insert into student
values('S6','杜甫','男',19,'D2');
insert into student
values('S7','韦应物','男',18,'D1');
insert into student
values('S8','白居易','男',20,'D4');
insert into student
values('S9','孟浩然','男',19,'D4');

delete from student where sno='S3';

update student set sname='李商隐',dno='D1'where sno='S4';
update student set ssex='女',sage=23 where sno='S2';
update student set dno='D1'where sno='S1';
update dept set dno='D5'where dno='D1';
delete from dept where dno='D5';

 

drop table student；

drop table dept；--被参照的表最后删除