实验3：数据库表创建、维护及数据的增删改（2学时）

一、实验目的
1、理解实体完整性、参照完整性和用户自定义完整性；
2、掌握创建表、修改表、删除表、五种数据约束（PRIMARY KEY约束，FOREIGN KEY约束，UNIQUE约束，CHECK约束和DEFAULT约束）的使用；
3、掌握数据库表中数据的增、删、改命令的使用；
4、理解存储引擎、字符集。

二、实验环境
装有软件MySQL5.7或更高版本的PC电脑一台；

三、实验步骤及实验要求
按照下面的实验内容要求完成作业，将作业结果的每一步截图粘贴到word文档中即可。每一个实验都必须编写实验报告，要求如下：
1、实验报告用word编写；
2、word文件命名的格式统一要求：为以“杨健”同学19号为例，他所做的第4次实验的实验报告应该命令为：“DB实验4— 19号—杨健”（即格式是：实验序号—课内序号—姓名）；课内序号现在是一个人一门课一个号，同一个人在不同课序号会不同，回头我会将课程名单发到群里，自己查阅你自己在本门课中的序号。
3、实验报告用统一的封面，封面模板到时发给大家；
4、报告中截取每题主要步骤结果的截图、实验结果截图；
5、实验报告最后要加上实验总结，总结部分必须写出自己的切身体会；
6、实验报告如有雷同、抄袭现象，后果自负；
7、实验报告上交截止时间：上机后一周之内；
8、实验上交方式：由学委收集齐全后，统一交付老师：
四、实验内容
1、完成课堂所讲的数据表操作相关的命令；

CREATE TABLE [IF NOT EXISTS] 表名(
    字段1, 数据类型 [约束条件] [默认值],
    字段2, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件]
);
#查看数据表结构
SHOW CREATE TABLE 表名\G
#追加一个列
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
#修改一个列
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
#重命名一个列
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
#删除一个列
ALTER TABLE 表名 DROP 【COLUMN】字段名
#删除表
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
#清空表
TRUNCATE TABLE detail_dept;

2、完成课堂所讲的数据表数据增、删、改操作相关的命令；

#插入数据
INSERT INTO 表名
VALUES (value1,value2,....);
INSERT INTO 表名(column1 [, column2, …, columnn]) 
VALUES (value1 [,value2, …, valuen]);
INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
#更新数据
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]
#删除数据
DELETE FROM table_name [WHERE <condition>];

3、简易图书管理数据库（假定数据库名为：BooksDB）包含4个关系（即：数据表）。关系名、属性及说明分别如下述各表所示。
读者类别表：ReaderType

读者信息表：Reader

图书信息表：Book

借阅信息表：Borrow

要求完成如下任务：
（1）使用SQL命令创建数据库及表；

create table ReaderType_3
(
   rdType       int,
   rdTypeName   varchar(20),
   canLendQty   int,
   canLendDay   int,
   primary key (rdType)
);
tgyhyu
create table reader_3
(
   rdID         char(9),
   rdType       int  not null,
   rdName       varchar(20),
   rdDept       varchar(40),
   rdQQ         varchar(13),
   rdBorrowQty  int(2),
   primary key (rdID),
     foreign key (rdType) references ReaderType_3(rdType)  
);
create table Book_3
(
   bkID              char(9),
   bkName            varchar(50),
   bkAuthor          varchar(50),
   bkPress           varchar(50),
   bkPrice           decimal(5,2),
   bkStatus          int,
   primary key (bkID)
);
create table Borrow_3
(
   rdID            char(9),
   bkID            char(9),
   DateBorrow      DateTime,
   DateLendPlan    DateTime,
   DateLendAct     DateTime,
     foreign key (rdID) references reader_3(rdID),
     foreign key (bkID) references Book_3(bkID)
);

（2）使用SQL命令向数据表中录入合理的测试数据。例如：
ReaderType表中中测试数据如下

INSERT INTO ReaderType_3 VALUES (1,"教师",10,60);
INSERT INTO ReaderType_3 VALUES (2,"本科生",5,30);
INSERT INTO ReaderType_3 VALUES (3,"硕士研究生",6,40);
INSERT INTO ReaderType_3 VALUES (4,"博士研究生",8,50);

Reader表中的测试数据

INSERT INTO reader_3 VALUES ("rd2017001",1,"王绕群","计算机科学学院","3635751",0);
INSERT INTO reader_3 VALUES ("rd2017002",2,"孙小美","英语学院","11224444",0);
INSERT INTO reader_3 VALUES ("rd2017003",3,"连晓燕","管理学院","55996633",0);
INSERT INTO reader_3 VALUES ("rd2017004",4,"许苗","物理学院","88552277",0);
INSERT INTO reader_3 VALUES ("rd2017005",5,"wengxiulin1","计算机科学学院","2304658432",0);
INSERT INTO reader_3 VALUES ("rd2017006",6,"wengxiulin2","计算机科学学院","2304658432",0);
INSERT INTO reader_3 VALUES ("rd2017007",7,"wengxiulin3","计算机科学学院","2304658432",0);
INSERT INTO reader_3 VALUES ("rd2017008",8,"wengxiulin4","计算机科学学院","2304658432",0);

Book表中的测试数据

INSERT INTO Book_3 VALUES ("bk2017001","数据库原理及应用","王丽艳","机械工业出版社","33.00",1);
INSERT INTO Book_3 VALUES ("bk2017002","高等数学","同济大学数学系","高等教育出版社","32.00",1);
INSERT INTO Book_3 VALUES ("bk2017003","当代健康心理学","陈琦","北京师范出版社","37.20",1);
INSERT INTO Book_3 VALUES ("bk2017004","古代汉语","王力","中华书局","20.40",1);
INSERT INTO Book_3 VALUES ("bk2017005","SQL1","wengxiulin","长江大学出版社","5.20",1);
INSERT INTO Book_3 VALUES ("bk2017006","SQL2","wengxiulin","长江大学出版社","31.20",1);
INSERT INTO Book_3 VALUES ("bk2017007","SQL3","wengxiulin","长江大学出版社","70.20",1);
INSERT INTO Book_3 VALUES ("bk2017008","SQL4","wengxiulin","长江大学出版社","10.20",1);

Borrow表中的测试数据（自行完成）

INSERT INTO Borrow_3 VALUES ("rd2017001","bk2017001","2022-10-27 16:12:10","2022-11-27 16:12:10","2022-11-27 16:12:10");
INSERT INTO Borrow_3 VALUES ("rd2017002","bk2017002","2022-10-27 16:12:10","2022-11-27 16:12:10","2022-11-27 16:12:10");
INSERT INTO Borrow_3 VALUES ("rd2017003","bk2017003","2022-10-27 16:12:10","2022-11-27 16:12:10","2022-11-27 16:12:10");
INSERT INTO Borrow_3 VALUES ("rd2017004","bk2017004","2022-10-27 16:12:10","2022-11-27 16:12:10","2022-11-27 16:12:10");
INSERT INTO Borrow_3 VALUES ("rd2017001","bk2017005","2022-10-27 16:12:10","2022-11-27 16:12:10","2022-11-27 16:12:10");
INSERT INTO Borrow_3 VALUES ("rd2017002","bk2017006","2022-10-27 16:12:10","2022-11-27 16:12:10","2022-11-27 16:12:10");

3）在此数据库中，演示增/删/改操作；

#3）在此数据库中，演示增/删/改操作；
SELECT *
FROM   book_3;
INSERT INTO Book_3 VALUES ("bk2017009","演示增操作","wengxiulin","长江大学出版社","10.20",1);
DELETE FROM book_3 WHERE bkID = "bk2017009";
UPDATE book_3
SET    bkName = "演示改操作"
WHERE  bkID = "bk2017005";

4、利用数据库客户端，执行上述《实验1：数据库设计》“交通违章处罚通知书”中的SQL语句，建立所有的表格，并初始化部分数据，并演示增/删/改操作；

#4、利用数据库客户端，执行上述《实验1：数据库设计》“交通违章处罚通知书”中的SQL语句，建立所有的表格，并初始化部分数据
#，并演示增/删/改操作；
INSERT INTO drving_license VALUES ("1","wengxiulin","湖北","111111","13367656448");
INSERT INTO drving_license VALUES ("2","weng","武汉","111112","13367656789");
INSERT INTO drving_license VALUES ("3","xiulin","锦州","111113","13367657899");
INSERT INTO drving_license VALUES ("4","lin","荆州","111114","13367656786");
INSERT INTO drving_license VALUES ("5","wenglin","南宁","111115","13345656448");
INSERT INTO drving_license VALUES ("6","wulin","湖北","111116","11237656448");
INSERT INTO police VALUES ("1","wulin");
INSERT INTO police VALUES ("2","wulin1");
INSERT INTO police VALUES ("3","wulin2");
INSERT INTO police VALUES ("4","wulin3");
INSERT INTO police VALUES ("5","wulin4");
INSERT INTO vehicle_information VALUES ("1","0001","山东第一制造场","2022-10-27");
INSERT INTO vehicle_information VALUES ("2","0002","山东第二制造场","2022-11-27");
INSERT INTO vehicle_information VALUES ("3","0001","湖北第一制造场","2022-01-27");
INSERT INTO vehicle_information VALUES ("4","0003","广东第一制造场","2022-12-27");
INSERT INTO vehicle_information VALUES ("5","0001","湖北第一制造场","2022-12-27");
INSERT INTO traffic_violation_penalties VALUES ("1","1","1","1","2022-10-27","18:26:47","南宁","测试数据879468两个内容和呢的咯卡","1","100","wulin","wengxiulin");
INSERT INTO traffic_violation_penalties VALUES ("2","2","2","1","2022-10-27","18:26:47","湖北","测试数据879468两个内容和呢的咯卡","1","100","wulin1","weng");
INSERT INTO traffic_violation_penalties VALUES ("3","1","1","1","2022-10-27","18:26:47","南宁","测试数据879468两个内容和呢的咯卡","1","100","wulin","wengxiulin");
SELECT *
FROM   drving_license;
INSERT INTO drving_license VALUES ("7","测试增","湖北","111116","11237656448");
DELETE FROM drving_license WHERE dl_id = "7";
UPDATE drving_license
SET    dl_name = "演示改操作"
WHERE  dl_id = "5";