编辑代码

create database edug;
use edug;

create table 登记档案(
    档案编号 char(18) primary key,
    档案名称 char(40),
    档案提供人 char(20),
    归档日期 date
);

create table 档案(
    内部编码 char(20) primary key,
    检索号 char(4),
    档案编号 char(18),
    foreign key(档案编号) references 登记档案(档案编号)
);
create table 调档人员(
    调档证号 char(20) primary key,
    姓名 char(20),
    单位 char(50)
);
create table 调档(
    调档证号 char(20),
    内部编码 char(20),
    调档日期 date,
    入档日期 date,
    primary key(调档证号,内部编码),
    foreign key(调档证号) references 调档人员(调档证号),
    foreign key(内部编码) references 档案(内部编码),
    check(调档日期<入档日期)
);

insert into 调档人员(调档证号,姓名,单位) values('1234','梨花','教育');
select * from 调档人员;
update 调档人员 set 姓名='杨话' where 调档证号 ='1234';
select * from 调档人员;
select distinct tid,lno from sc where yyear='2024' and tid in (
    select tid from sc where yyear='2024' group by tid having count(*)>=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 not in(
    select sc.cno from sc,l where sc.lno=l.lno and l.lname='MIS'
)
group by c.cno,speciality;

create view v1 as 
select 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='数据库'
where c.department='计算机系';

create view v1 as 
select 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='计算机系';