数据库_MySQL_复杂的表结构创建

来源：互联网发布：space矩阵分析编辑：程序博客网时间：2024/05/22 03:44

本例示例下复杂的表结构创建，

大致分为两张表 user表和 authority(权限表)

每个用户对应一种权限，默认为1(普通用户) 灵感来源：(仿照 进程的优先级, 优先级越高的进程，数字越小)

功能清单：

1.用户表对应的数据删除了，权限表对应的数据也应该删除

2.新用户注册应该分配默认权限

示意如下：

其中用到的技术与难点：

1.表的级联更新与级联删除

写法截取：

CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`, `user_name`) REFERENCES `user` (`user_id`, `user_name`)ON DELETE CASCADE ON UPDATE CASCADE

2.外键约束，非主键的外键约束，

注意事项：

1.若对应的项不是主键，

2.建立外键约束前, 需要为对应字段其建立索引，可利用key 建立，（主表和从表都需要建立）示例：

KEY `FK_authority` (`user_id`,`user_name`),

3.外键约束

CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`, `user_name`) REFERENCES `user` (`user_id`, `user_name`) ON DELETE CASCADE ON UPDATE CASCADE

完整的SQL:

/*Navicat MySQL Data TransferSource Server         : localhost_3306Source Server Version : 50528Source Host           : localhost:3306Source Database       : warehouse_managerTarget Server Type    : MYSQLTarget Server Version : 50528File Encoding         : 65001Date: 2015-11-25 11:52:24*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `employee`-- ----------------------------DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` (  `user_id` int(11) NOT NULL AUTO_INCREMENT,  `user_name` varchar(50) NOT NULL,  `authority` int(11) DEFAULT '1' COMMENT '用户的权限',  PRIMARY KEY (`user_id`),  KEY `FK_authority` (`user_id`,`user_name`),  CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`, `user_name`) REFERENCES `user` (`user_id`, `user_name`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ------------------------------ Records of employee-- ----------------------------INSERT INTO `employee` VALUES ('1', 'szh', '0');INSERT INTO `employee` VALUES ('2', 'sss', '0');INSERT INTO `employee` VALUES ('3', 'ssk', '1');INSERT INTO `employee` VALUES ('4', '2', '1');INSERT INTO `employee` VALUES ('5', '3', '1');INSERT INTO `employee` VALUES ('6', 'sds', '1');INSERT INTO `employee` VALUES ('7', 'sd', '1');-- ------------------------------ Table structure for `user`-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `user_id` int(11) NOT NULL AUTO_INCREMENT,  `user_name` varchar(50) NOT NULL,  `user_pass` varchar(50) NOT NULL,  PRIMARY KEY (`user_id`),  UNIQUE KEY `user_name` (`user_name`),  KEY `user_id` (`user_id`,`user_name`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES ('1', 'szh', '123');INSERT INTO `user` VALUES ('2', 'sss', '123');INSERT INTO `user` VALUES ('3', 'ssk', '132');INSERT INTO `user` VALUES ('4', '2', '3');INSERT INTO `user` VALUES ('5', '3', '123');INSERT INTO `user` VALUES ('6', 'sds', '123');INSERT INTO `user` VALUES ('7', 'sd', '123');

后来发现user_authority表中 user_name为冗余字段，修改后的表结构如图：

创建表的sql

/*Navicat MySQL Data TransferSource Server         : localhost_3306Source Server Version : 50528Source Host           : localhost:3306Source Database       : warehouse_managerTarget Server Type    : MYSQLTarget Server Version : 50528File Encoding         : 65001Date: 2015-11-25 23:26:28*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `user`-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `user_id` int(11) NOT NULL AUTO_INCREMENT,  `user_name` varchar(50) NOT NULL,  `user_pass` varchar(50) NOT NULL,  PRIMARY KEY (`user_id`),  UNIQUE KEY `user_name` (`user_name`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES ('1', 'szh', '123');INSERT INTO `user` VALUES ('2', 'sss', '123');INSERT INTO `user` VALUES ('3', 'ssk', '132');INSERT INTO `user` VALUES ('4', '2', '3');INSERT INTO `user` VALUES ('5', '3', '123');INSERT INTO `user` VALUES ('6', 'sds', '123');INSERT INTO `user` VALUES ('7', 'sd', '123');-- ------------------------------ Table structure for `user_authority`-- ----------------------------DROP TABLE IF EXISTS `user_authority`;CREATE TABLE `user_authority` (  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  `authority` int(11) DEFAULT '1' COMMENT '用户的权限',  PRIMARY KEY (`user_id`),  KEY `FK_authority` (`user_id`),  CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user_authority-- ----------------------------INSERT INTO `user_authority` VALUES ('1', '0');INSERT INTO `user_authority` VALUES ('2', '0');INSERT INTO `user_authority` VALUES ('3', '0');INSERT INTO `user_authority` VALUES ('4', '1');INSERT INTO `user_authority` VALUES ('5', '1');INSERT INTO `user_authority` VALUES ('6', '1');INSERT INTO `user_authority` VALUES ('7', '1');

0 0