mysql作业–yxj

-- (1)删除数据库
drop database if exists SchoolDB;
-- (2)创建数据库
create database if not exists SchoolDB;
-- (3)使用数据库
use SchoolDB;
-- 一、
drop table if exists student;
create table student(
stuid char(10) not null comment'学号' primary key,
stuname char(10) not null comment'姓名',
stusex char(2) not null comment'性别',
studate date comment'出生日期',
address varchar(20) comment'地区',
stumz char(10) comment'民族',
classid char(6) comment'班级编号'
);
-- 二、
drop table if exists course;
create table course(
kechenghao char(6) not null comment'课程号' primary key,
kechengming varchar(20) not null comment'课程名',
xuefen int not null comment'学分',
xueshi int not null comment'学时',
xueqi char(2) comment'学期',
qianzhi char(6) comment'前置课'
);
-- 三、
drop table if exists score;
create table score(
scid char(10) not null comment'学号',
sckecheng char(6) not null comment'课程号',
chengji float(5,2) comment'成绩',
primary key(scid,sckecheng)
);
-- 四、
drop table if exists class;
create table class(
classID char(6) not null comment'班级编号' primary key,
classname varchar(20) not null comment'班级名称',
yuanxi varchar(30) comment'院系',
nianji int comment'年级',
renshu int comment'人数'
);
-- 外键
alter table student add foreign key(classID) references class(classID);
alter table course add foreign key(qianzhi) references course(kechenghao);
alter table score add foreign key(scid) references student(stuid);
alter table score add foreign key(sckecheng) references course(kechenghao);
-- 添加约束score
alter table score add check(chengji>=0 and chengji<=100);
-- 添加约束student
alter table student add check(stusex in ('男','女'));

drop database if exists LibraryDB;
create database if not exists LibraryDB;
use LibraryDB;

-- 1. 读者表 (Readers Table)
drop table if exists readers;
create table readers (
    -- 读者编号 (Reader Number) 主键，不能为空
    ReaderNumber char(6) not null primary key,
    -- 姓名 (Name)，不能为空
    Name char(10) not null,
    -- 类别号 (Category Number)，不能为空
    CategoryNumber char(2) not null,
    -- 单位 (Organization)，可为空
    Organization VARCHAR(20),
    -- 有效性 (Validity)，可为空
    Validity char(10)
);

-- 2. 读者类型表 (Reader Types Table)
drop table if exists ReaderType;
create table ReaderType (
    -- 类别号 (Category Number) 主键，不能为空
    CategoryNumber char(2) not null primary key,
    -- 类名 (Category Name)，不能为空
    CategoryName char(10) not null,
    -- 可借数量 (Maximum Borrows)，可借书的数量
    MaximumBorrows int,
    -- 可借天数 (Maximum Borrow Days)，可借书的天数
    MaximumBorrowDays INT
);

-- 3. 库存表 (Stock Piles Table)
drop table if exists stockpiles;
create table stockpiles (
    -- 条码 (Barcode) 主键，不能为空
    Barcode char(20) not null primary key,
    -- 书号 (Book Number)，不能为空
    BookNumber char(10) not null,
    -- 位置 (Location)，库存位置，不能为空
    Location VARCHAR(20) NOT NULL,
    -- 库存状态 (Stock Status)，库存的状态
    StockStatus CHAR(10)
);

-- 4. 图书表 (Books Table)
drop table if exists books;
create table books (
    -- 书号 (Book Number) 主键，不能为空
    BookNumber char(10) not null primary key,
    -- 书名 (Book Title)，不能为空
    BookTitle VARCHAR(20) not null,
    -- 类别 (Category)，不能为空
    Category char(10) NOT NULL,
    -- 作者 (Author)，不能为空
    Author char(10) NOT NULL,
    -- 出版社 (Publisher)，不能为空
    Publisher VARCHAR(20) NOT NULL,
    -- 单价 (Unit Price)，图书的单价
    UnitPrice FLOAT(5,2),
    -- 数量 (Quantity)，库存的数量
    Quantity INT
);

-- 5. 借阅表 (Borrow Table)
drop table if exists borrow;
create table borrow (
    -- 借阅号 (Borrow Number) 主键，不能为空
    BorrowNumber INT not null primary key,
    -- 条码 (Barcode)，不能为空
    Barcode CHAR(20) not null,
    -- 读者编号 (Reader Number)，不能为空
    ReaderNumber char(6) NOT NULL,
    -- 借阅日期 (Borrow Date)，借阅图书的日期
    BorrowDate DATE,
    -- 还书日期 (Return Date)，归还图书的日期
    ReturnDate DATE,
    -- 借阅状态 (Borrow Status)，借阅的状态
    BorrowStatus char(6)
);


-- (1)为LibraryDB中的读者表指定主键为“读者编号”。
ALTER TABLE readers
ADD PRIMARY KEY (ReaderNumber);
-- (2)为读者表创建外键，其“类别号”列的值必须是读者类型表中“类别号”列存在的值，删除或修改读者类型表中的类别号值时，读者表中“类别号”列的数据也要随之变化。
ALTER TABLE readers
ADD FOREIGN KEY (CategoryNumber) REFERENCES ReaderType(CategoryNumber) ON DELETE CASCADE ON UPDATE CASCADE;
-- (3)为借阅表创建外键，其“读者编号”列的值必须是读者表中“读者编号”列存在的值，删除或修改读者表中的读者编号值时，如果借阅表中该读者还有记录，则不得删除或修改。
ALTER TABLE borrow
ADD FOREIGN KEY (ReaderNumber) REFERENCES readers(ReaderNumber) ON DELETE RESTRICT ON UPDATE CASCADE;
-- (4)为借阅表创建外键，借阅表中“条码”列中的值必须是库存表中“条码”列存在的值，删除或修改库存表中的条码值时，借阅表中“条码”列的数据也要随之变化。
ALTER TABLE borrow
ADD FOREIGN KEY (Barcode) REFERENCES stockpiles(Barcode) ON DELETE CASCADE ON UPDATE CASCADE;
-- (5)修改读者类型表，可借数量必须在0~30本的范围内。
ALTER TABLE ReaderType
ADD CONSTRAINT CHK_MaximumBorrows CHECK (MaximumBorrows BETWEEN 0 AND 30);
-- (6)修改库存表，库存状态只能是“在馆”“借出”“丢失”3种状态之一。
ALTER TABLE stockpiles
ADD CONSTRAINT CHK_StockStatus CHECK (StockStatus IN ('在馆', '借出', '丢失'));