createdatabase edug;
use edug;
createtable 登记档案(
档案编号 char(18) primary key,
档案名称 char(40),
档案提供人 char(20),
归档日期 date
);
createtable 档案(
内部编码 char(20) primary key,
检索号 char(4),
档案编号 char(18),
foreignkey(档案编号) references 登记档案(档案编号)
);
createtable 调档人员(
调档证号 char(20) primary key,
姓名 char(20),
单位 char(50)
);
createtable 调档(
调档证号 char(20),
内部编码 char(20),
调档日期 date,
入档日期 date,
primary key(调档证号,内部编码),
foreignkey(调档证号) references 调档人员(调档证号),
foreignkey(内部编码) references 档案(内部编码),
check(调档日期<入档日期)
);
insertinto 调档人员(调档证号,姓名,单位) values('1234','梨花','教育');
select * from 调档人员;
update 调档人员 set 姓名='杨话'where 调档证号 ='1234';
select * from 调档人员;
selectdistinct tid,lno from sc where yyear='2024'and tid in (
select tid from sc where yyear='2024'groupby tid havingcount(*)>=2
);
select c.cno,speciality,count(sno) from c,sc where c.cno=s.cno and
c.cno in(
select sc.cno from sc,l where sc.lno=l.lno and l.lname='安卓'
) and c.cno notin(
select sc.cno from sc,l where sc.lno=l.lno and l.lname='MIS'
)
groupby c.cno,speciality;
createview v1 asselect s.sno,s.sname,s.grade,s.cno,g.scores
from s
join g on s.sno=g.sno
join c on s.cno=c.cno
and g.lno inselect lno from l where lname='数据库'where c.department='计算机系';
createview v1 asselect s.sno,s.sname,s.grade,s.cno,g.scores
from s
join g on s.sno=g.sno
join c on s.cno=c.cno
and g.lno in(select lno from l where lname='shujuku')
where c.department='计算机系';