SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-------Table structure for bookcategory
DROP TABLE IF EXISTS 'bookcategory';
CREATE TABLE 'bookcategory'(
'Category' varchar(20) CHARACTER SET utf8 COLLECT utf8_general_ci NOT NULL COMMENT '类别',
PRIMARY KEY ('Category') USING BTREE
) ENGING = InnoDB CHARACTER SET = utf8 COLLECT = utf8_general_ci ROW_FORMAT = Compact;
-------Table structure for booktable
DROP TABLE IF EXISTS 'booktable';
CREATE TABLE 'booktable'(
'bookid' int(10) NOT NULL AUTO_INCREMENT COMMENT '书号',
'category' varchar(20) CHARACTER SET utf8 COLLECT utf8_general_ci NOT NULL COMMENT '类别',
'bookname' varchar(100) CHARACTER SET utf8 COLLECT utf8_general_ci NOT NULL COMMENT '书名',
'author' varchar(20) CHARACTER SET utf8 COLLECT utf8_general_ci NULL DEFAULT NULL COMMENT '作者',
'press' varchar(20) CHARACTER SET utf8 COLLECT utf8_general_ci NULL DEFAULT NULL COMMENT '出版社',
'state' varchar(20) CHARACTER SET utf8 COLLECT utf8_general_ci NOT NULL COMMENT '状态',
PRIMARY KEY ('bookid') USING BTREE,
INDEX 'category'('category') USING BTREE,
CONSTRAINT 'booktable_ibfk_1' FOREIGN KEY ('category') REFERENCES 'bookcategory' ('Category') ON DELETE NO ACTION ON UPDATE CASCADE
)ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
------Table structure for borrowrecords
DROP TABLE IF EXISTS 'borrowrecords';
CREATE TABLE 'borrowrecords'(
'id' int(10) NOT NULL AUTO_INCREMENT COMMENT '借书序列',
'user' varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
'bookid' int(10) NOT NULL COMMENT '书号';
'bookname' varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书名',
'borrowtime' date NOT NULL COMMENT '借书时间',
'returntime' date NOT NULL COMMENT '还书时间',
'state' varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '状态',
PRIMARY KEY ('id') USING BTREE
) ENGING = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
------Table structure for usertable
DROP TABLE IF EXISTS 'usertable';
CREATE TABLE 'usertable'(
'user' varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
'studentid' varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
'name' varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
'password' varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
'admin' int(10) NOT NULL COMMENT '是否为管理员账户 1为管理员,0为普通用户,默认为0',
PRIMARY KEY ('user') USING BTREE
)ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
SET FOREING_KEY_CHECKS = 1;
/*如果指定了CHARACTER SET X和COLLATE Y,那么采用字符集X和校对规则Y
B-tree索引是数据库中存取和查找文件(称为记录或键值)的一种方法.B-tree算法减少定位记录时所经历的中间过程,从而加快存取速度.
MYISAM和InnoDB存储引擎只支持BTREE索引;MEMORY和HEAP存储引擎可以支持HASH和BTREE索引
ON DELETE NO ACTION ON UPDATE CASCADE :
cascade方式 在父表上update/delete记录时,同步update/delete掉子表的匹配记录 (级联删除)
No action方式 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
NULL DEFAULT NULL 默认为null(null与DEFAULT NULL都需要)
*/
mysql建表实例
最新推荐文章于 2024-06-25 11:37:52 发布