SQL课设之报刊订阅管理

E-R图：

 

一些常用命令：

1.登录mysql

2.选择进入数据库

 相关SQL代码如下：

CREATE TABLE Administrator(
    Gname varchar(50)PRIMARY KEY,
    Gnum varchar(50)
);
CREATE TABLE Department(
    Dnum int PRIMARY KEY,
    Dname varchar(50)
);
CREATE TABLE User(
    Uaccount varchar(50)PRIMARY KEY,
    Unum varchar(50)NOT NULL,
    Uname varchar(50)NOT NULL,
    U_identity_number varchar(50)NOT NULL,
    U_contact_number varchar(50)NOT NULL,
    Uaddress varchar(100) NOT NULL,
    Dnum int,
    FOREIGN KEY(Dnum)REFERENCES Department(Dnum)
);
CREATE TABLE Category(
    Cnum int PRIMARY KEY,
    Cname varchar(50)
);
CREATE TABLE Paper_infor(
    Pnum int PRIMARY KEY,
    Pname varchar(50)NOT NULL,
    Publishing varchar(100),
    Period int,
    Price int,
    PIntroduction varchar(200),
    Cnum int,
    FOREIGN KEY(Cnum)REFERENCES Category(Cnum)
);
CREATE TABLE Order_form(
    Onum int PRIMARY KEY,
    Uaccount varchar(50),
    Pnum int,
    Ocnt int,
    Ostart varchar(100),
    Oend varchar(100),
    FOREIGN KEY(Uaccount)REFERENCES User(Uaccount),
    FOREIGN KEY(Pnum)REFERENCES Paper_infor(Pnum)
);
select *
FROM User;

insert into Administrator values('陶海云','123456');
insert into Administrator values('陶宝','654321');

insert into Department values('人事部','1');
insert into Department values('运维部','2');
insert into Department values('开发部','3');
insert into Department values('产品部','4');

insert into User values('20162292','123123','陶一','3402211998','15526856768','安徽省','1');
insert into User values('20162200','321321','陶二','3402210921','15523213213','江苏省','2');
insert into User values('20162233','111222','陶三','3402213213','18325387936','吉林省','3');
insert into User values('20162211','222111','陶四','2132132123','18209762703','上海市','4')

insert into Category values('1','时事政治');
insert into Category values('2','财政金融');
insert into Category values('3','科技数码');
insert into Category values('4','人文风情');

insert into Paper_infor values('1','经济日报','经济出版社','12','10','分析时代经济形势','1');
insert into Paper_infor values('2','人民日报','人民教育出版社','6','10','简述政治政策看点','2');
insert into Paper_infor values('3','IT时代报','科学出版社','24','15','分享科技前沿','3');
insert into Paper_infor values('4','中文新报','商务印书馆','12','8','领略文学艺术','4');

insert into Order_form values('1','20162292','1','6','2018-3-1','2018-9-1');
insert into Order_form values('2','20162200','2','9','2018-4-29','2017-7-15');
insert into Order_form values('3','20162233','3','3','2018-6-23','2018-10-22');
insert into Order_form values('4','20162211','4','2','2017-5-1','2018-11-23');

select Uname
FROM User;

select *
FROM Paper_infor
WHERE Price='10';

select COUNT(*)
FROM User;

select Onum,Uaccount,Pnum,Ocnt
FROM Order_form
WHERE Ocnt 
BETWEEN 5 AND 10;

select Uaccount,Uname,Dnum
FROM User
WHERE Uname LIKE '陶%';

select Price,COUNT(*)
FROM Paper_infor
GROUP BY Price; 

select Order_form.Uaccount,User.Uname,Ocnt
FROM Order_form,User
WHERE Order_form.Uaccount=User.Uaccount;

create view v1
as
select Uaccount,Uname,Dnum
FROM User
WHERE Uname LIKE '陶%'; 

create trigger tri1 after insert
on Order_form for each row
updata Onum set Ocnt=Ocnt+1 where new.Onum=Order_form.Onum;

alter table project
add constraint FK_employee_id
FOREIGN KEY(employee_id)REFERENCES employee(employee_id);