一个关于Mysql插入的问题!

显示全部楼层 · 2007-1-6 21:14:12

CREATE TABLE orders
(
orderid INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR (12) NOT NULL,
goodid INT UNSIGNED,
type INT UNSIGNED NOT NULL,
goodname VARCHAR (64),
goodprice float(5,2),
goodquality INT UNSIGNED,
goodpublish VARCHAR (128),
PRIMARY KEY(orderid)
);

CREATE TABLE cart
(
username Char UNSIGNED NOT NULL,
goodid INT UNSIGNED NOT NULL,
num INT UNSIGNED NOT NULL
);
上面是这两个表的结构1!
是这样,选择表cart的字段插入表orders中，我是这样的,
insert into orders(username,goodid,goodquality) select username goodid
num from cart where username = 'qixiang';
因为type 这个字段必须设置为NOT null,,所以在插入时候，不让插入.
而且我还想把type 的值设置为 0, 大家有什么更好的方法呢，请指教!!!!

千问 · 2007-1-6 21:14:12

[PHP]
mysql> CREATE TABLE orders
-> (
-> orderidINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> username VARCHAR (12) NOT NULL,
-> goodid INT UNSIGNED,
-> type INT UNSIGNED NOT NULL,
-> goodname VARCHAR (64),
-> goodpricefloat(5,2),
-> goodqualityINT UNSIGNED,
-> goodpublishVARCHAR (128),
-> PRIMARY KEY(orderid)
-> );
Query OK, 0 rows affected (0.16 sec)
mysql>
mysql> CREATE TABLE cart
-> (
-> username varchar(20) NOT NULL,
-> goodid INT UNSIGNEDNOT NULL,
-> numINT UNSIGNEDNOT NULL
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> insert into cart values('a',1,2);
Query OK, 1 row affected (0.03 sec)
mysql> insert into orders(username,goodid,goodquality,type)
-> select username,goodid,num,0 from cart;
Query OK, 1 row affected (0.03 sec)
Records: 1Duplicates: 0Warnings: 0
mysql>
[/PHP]

千问 · 2007-1-6 21:14:12

谢谢,朋友，原来这样也可以啊，可惜我刚注册，没有奖金,我还以为有呢