MySQL 版本 5.1.41-community创建表语句
CREATE TABLE `goods_in` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `person` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET gbk NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `bcode` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`in_id` int(6) NOT NULL DEFAULT '0',
`brand_id` int(6) NOT NULL,
`qty` int(3) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `in_id` (`in_id`),
CONSTRAINT `bcode_ibfk_1` FOREIGN KEY (`in_id`) REFERENCES `goods_in` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `piece` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bid` int(11) NOT NULL,
`pid` int(4) NOT NULL,
`qty` int(2) NOT NULL,
`mid` int(4) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `bid` (`bid`),
KEY `pid` (`pid`),
CONSTRAINT `piece_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `bcode` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `piece_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;添加数据
INSERT INTO `person`(`id`,`name`) VALUES
(1,'张三'),
(2,'关二'),
(3,'刘大');INSERT INTO `goods_in`(`date`) VALUES
('2010-03-06'),
('2010-03-06'),
('2010-03-07'),
('2010-03-08');INSERT INTO `bcode`(`id`,`in_id`,`brand_id`,`qty`) VALUES
(1000,1,35,12),
(1001,1,36,12),
(1002,1,37,12),
(1003,1,38,12),
(1004,2,39,12),
(1005,2,40,12),
(1006,3,41,7),
(1007,3,42,12),
(1008,3,43,12),
(1009,3,44,6),
(1010,3,45,12),
(1011,4,46,12),
(1013,4,47,8);INSERT INTO `piece`(`bid`,`pid`,`qty`,`mid`,`date`) VALUES
(1000,1,5,3,'2010-03-06'),
(1000,1,7,3,'2010-03-06'),
(1001,1,12,3,'2010-03-06'),
(1002,2,10,5,'2010-03-06'),
(1002,2,2,5,'2010-03-06'),
(1003,3,12,8,'2010-03-06'),
(1004,1,9,3,'2010-03-06'),
(1004,1,3,3,'2010-03-06'),
(1005,3,12,8,'2010-03-06'),
(1006,1,3,3,'2010-03-07'),
(1006,1,4,3,'2010-03-07'),
(1007,1,12,3,'2010-03-07'),
(1008,2,12,5,'2010-03-07'),
(1009,3,6,8,'2010-03-07'),
(1010,3,6,8,'2010-03-07'),
(1010,3,6,8,'2010-03-07'),
(1011,2,5,8,'2010-03-08'),
(1011,3,7,8,'2010-03-08'),
(1012,2,4,8,'2010-03-08'),
(1012,3,4,8,'2010-03-08');
期望结果
+-------------+----------+----------------+-----------+-------------+
| goods_in.id | bcode.id | bcode.brand_id | bcode.qty | person.name |
+-------------+----------+----------------+-----------+-------------+
| 1 | 1000 | 35 | 12 | 张三 |
+-------------+----------+----------------+-----------+-------------+
| 1 | 1002 | 37 | 12 | 关二 |
+-------------+----------+----------------+-----------+-------------+
| 2 | 1004 | 39 | 12 | 张三 |
+-------------+----------+----------------+-----------+-------------+
| 3 | 1006 | 41 | 7 | 张三 |
+-------------+----------+----------------+-----------+-------------+
| 3 | 1010 | 45 | 12 | 刘大 |
+-------------+----------+----------------+-----------+-------------+也就是说,在piece中本来应该是一个或多个bid的qty加起来,等于bcode中对应的qty的
而且piece中多个bid不可能存在同一个pid的情况,但因为之前错误输入,导致现在数据库中存在一些这样的数据
所以希望能把他们查找出来既然都提问了..那顺便...
如果可能,还希望能查询出这样的结果
+-----------+--------------+--------------+
| piece.bid | person.name1 | person.name2 |
+-----------+--------------+--------------+
| 1011 | 关二 | 刘大 |
+-----------+--------------+--------------+
| 1012 | 关二 | 刘大 |
+-----------+--------------+--------------+也就是说,如果piece中的bid是由多个person来完成的(暂时2个就行),则显示出bid和person的名字
CREATE TABLE `goods_in` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `person` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET gbk NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `bcode` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`in_id` int(6) NOT NULL DEFAULT '0',
`brand_id` int(6) NOT NULL,
`qty` int(3) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `in_id` (`in_id`),
CONSTRAINT `bcode_ibfk_1` FOREIGN KEY (`in_id`) REFERENCES `goods_in` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `piece` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bid` int(11) NOT NULL,
`pid` int(4) NOT NULL,
`qty` int(2) NOT NULL,
`mid` int(4) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `bid` (`bid`),
KEY `pid` (`pid`),
CONSTRAINT `piece_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `bcode` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `piece_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;添加数据
INSERT INTO `person`(`id`,`name`) VALUES
(1,'张三'),
(2,'关二'),
(3,'刘大');INSERT INTO `goods_in`(`date`) VALUES
('2010-03-06'),
('2010-03-06'),
('2010-03-07'),
('2010-03-08');INSERT INTO `bcode`(`id`,`in_id`,`brand_id`,`qty`) VALUES
(1000,1,35,12),
(1001,1,36,12),
(1002,1,37,12),
(1003,1,38,12),
(1004,2,39,12),
(1005,2,40,12),
(1006,3,41,7),
(1007,3,42,12),
(1008,3,43,12),
(1009,3,44,6),
(1010,3,45,12),
(1011,4,46,12),
(1013,4,47,8);INSERT INTO `piece`(`bid`,`pid`,`qty`,`mid`,`date`) VALUES
(1000,1,5,3,'2010-03-06'),
(1000,1,7,3,'2010-03-06'),
(1001,1,12,3,'2010-03-06'),
(1002,2,10,5,'2010-03-06'),
(1002,2,2,5,'2010-03-06'),
(1003,3,12,8,'2010-03-06'),
(1004,1,9,3,'2010-03-06'),
(1004,1,3,3,'2010-03-06'),
(1005,3,12,8,'2010-03-06'),
(1006,1,3,3,'2010-03-07'),
(1006,1,4,3,'2010-03-07'),
(1007,1,12,3,'2010-03-07'),
(1008,2,12,5,'2010-03-07'),
(1009,3,6,8,'2010-03-07'),
(1010,3,6,8,'2010-03-07'),
(1010,3,6,8,'2010-03-07'),
(1011,2,5,8,'2010-03-08'),
(1011,3,7,8,'2010-03-08'),
(1012,2,4,8,'2010-03-08'),
(1012,3,4,8,'2010-03-08');
期望结果
+-------------+----------+----------------+-----------+-------------+
| goods_in.id | bcode.id | bcode.brand_id | bcode.qty | person.name |
+-------------+----------+----------------+-----------+-------------+
| 1 | 1000 | 35 | 12 | 张三 |
+-------------+----------+----------------+-----------+-------------+
| 1 | 1002 | 37 | 12 | 关二 |
+-------------+----------+----------------+-----------+-------------+
| 2 | 1004 | 39 | 12 | 张三 |
+-------------+----------+----------------+-----------+-------------+
| 3 | 1006 | 41 | 7 | 张三 |
+-------------+----------+----------------+-----------+-------------+
| 3 | 1010 | 45 | 12 | 刘大 |
+-------------+----------+----------------+-----------+-------------+也就是说,在piece中本来应该是一个或多个bid的qty加起来,等于bcode中对应的qty的
而且piece中多个bid不可能存在同一个pid的情况,但因为之前错误输入,导致现在数据库中存在一些这样的数据
所以希望能把他们查找出来既然都提问了..那顺便...
如果可能,还希望能查询出这样的结果
+-----------+--------------+--------------+
| piece.bid | person.name1 | person.name2 |
+-----------+--------------+--------------+
| 1011 | 关二 | 刘大 |
+-----------+--------------+--------------+
| 1012 | 关二 | 刘大 |
+-----------+--------------+--------------+也就是说,如果piece中的bid是由多个person来完成的(暂时2个就行),则显示出bid和person的名字
mysql> INSERT INTO `piece`(`bid`,`pid`,`qty`,`mid`,`date`) VALUES
-> (1000,1,5,3,'2010-03-06'),
-> (1000,1,7,3,'2010-03-06'),
-> (1001,1,12,3,'2010-03-06'),
-> (1002,2,10,5,'2010-03-06'),
-> (1002,2,2,5,'2010-03-06'),
-> (1003,3,12,8,'2010-03-06'),
-> (1004,1,9,3,'2010-03-06'),
-> (1004,1,3,3,'2010-03-06'),
-> (1005,3,12,8,'2010-03-06'),
-> (1006,1,3,3,'2010-03-07'),
-> (1006,1,4,3,'2010-03-07'),
-> (1007,1,12,3,'2010-03-07'),
-> (1008,2,12,5,'2010-03-07'),
-> (1009,3,6,8,'2010-03-07'),
-> (1010,3,6,8,'2010-03-07'),
-> (1010,3,6,8,'2010-03-07'),
-> (1011,2,5,8,'2010-03-08'),
-> (1011,3,7,8,'2010-03-08'),
-> (1012,2,4,8,'2010-03-08'),
-> (1012,3,4,8,'2010-03-08');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`csdn`.`piece`, CONSTRAINT `piece_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `
bcode` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>
INSERT INTO `bcode`(`id`,`in_id`,`brand_id`,`qty`) VALUES
(1000,1,35,12),
(1001,1,36,12),
(1002,1,37,12),
(1003,1,38,12),
(1004,2,39,12),
(1005,2,40,12),
(1006,3,41,7),
(1007,3,42,12),
(1008,3,43,12),
(1009,3,44,6),
(1010,3,45,12),
(1011,4,46,12),
(1012,4,47,8);
最后一条数据写错了...
那是后来手工添加的...