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);

SQL课设之报刊订阅管理的相关教程结束。