表1
CREATE TABLE `salesorderdetails` (
`orderlineno` int(11) NOT NULL default '0',
`orderno` int(11) NOT NULL default '0',
`stkcode` varchar(20) NOT NULL default '',
`qtyinvoiced` double NOT NULL default '0',
`unitprice` double NOT NULL default '0',
`quantity` double NOT NULL default '0',
`estimate` tinyint(4) NOT NULL default '0',
`discountpercent` double NOT NULL default '0',
`actualdispatchdate` datetime NOT NULL default '0000-00-00 00:00:00',
`completed` tinyint(1) NOT NULL default '0',
`narrative` text,
`itemdue` date default NULL COMMENT 'Due date for line item. Some customers require \r\nacknowledgements with due dates by line item',
`poline` varchar(10) default NULL COMMENT 'Some Customers require acknowledgements with a PO line number for each sales line',
PRIMARY KEY (`orderlineno`,`orderno`),
KEY `OrderNo` (`orderno`),
KEY `StkCode` (`stkcode`),
KEY `Completed` (`completed`),
CONSTRAINT `salesorderdetails_ibfk_1` FOREIGN KEY (`orderno`) REFERENCES `salesorders` (`orderno`),
CONSTRAINT `salesorderdetails_ibfk_2` FOREIGN KEY (`stkcode`) REFERENCES `stockmaster` (`stockid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;表2
CREATE TABLE IF NOT EXISTS `orders` (
`orders_id` int(11) NOT NULL auto_increment,
`customers_id` int(11) NOT NULL default '0',
`customers_name` varchar(64) NOT NULL default '',
`customers_company` varchar(32) default NULL,
`customers_street_address` varchar(64) NOT NULL default '',
`customers_suburb` varchar(32) default NULL,
`customers_city` varchar(32) NOT NULL default '',
`customers_postcode` varchar(10) NOT NULL default '',
`customers_state` varchar(32) default NULL,
`customers_country` varchar(32) NOT NULL default '',
`customers_telephone` varchar(32) NOT NULL default '',
`customers_email_address` varchar(96) NOT NULL default '',
`customers_address_format_id` int(5) NOT NULL default '0',
`delivery_name` varchar(64) NOT NULL default '',
`delivery_company` varchar(32) default NULL,
`delivery_street_address` varchar(64) NOT NULL default '',
`delivery_suburb` varchar(32) default NULL,
`delivery_city` varchar(32) NOT NULL default '',
`delivery_postcode` varchar(10) NOT NULL default '',
`delivery_state` varchar(32) default NULL,
`delivery_country` varchar(32) NOT NULL default '',
`delivery_address_format_id` int(5) NOT NULL default '0',
`billing_name` varchar(64) NOT NULL default '',
`billing_company` varchar(32) default NULL,
`billing_street_address` varchar(64) NOT NULL default '',
`billing_suburb` varchar(32) default NULL,
`billing_city` varchar(32) NOT NULL default '',
`billing_postcode` varchar(10) NOT NULL default '',
`billing_state` varchar(32) default NULL,
`billing_country` varchar(32) NOT NULL default '',
`billing_address_format_id` int(5) NOT NULL default '0',
`payment_method` varchar(32) NOT NULL default '',
`payment_info` text,
`payment_id` int(11) NOT NULL default '0',
`cc_type` varchar(20) default NULL,
`cc_owner` varchar(64) default NULL,
`cc_number` varchar(90) default NULL,
`cc_expires` varchar(90) default NULL,
`last_modified` datetime default '0000-00-00 00:00:00',
`date_purchased` datetime default '0000-00-00 00:00:00',
`orders_status` int(5) NOT NULL default '0',
`orders_date_finished` datetime default '0000-00-00 00:00:00',
`currency` char(3) default NULL,
`currency_value` decimal(14,6) default NULL,
`account_name` varchar(32) default NULL,
`account_number` varchar(20) default NULL,
`po_number` varchar(12) default NULL,
`purchased_without_account` tinyint(1) unsigned NOT NULL default '0',
`paypal_ipn_id` int(11) NOT NULL default '0',
`cc_start` varchar(4) default NULL,
`cc_bank_phone` varchar(32) NOT NULL default '',
`cc_ccv` varchar(90) default NULL,
`ipaddy` varchar(15) NOT NULL default '',
`cc_issue` char(3) default NULL,
`ipisp` varchar(15) NOT NULL default '',
PRIMARY KEY (`orders_id`),
KEY `date_purchased` (`date_purchased`),
KEY `idx1_orders` (`customers_id`)
) ENGINE=MyISAM 急啊,高手帮帮心
CREATE TABLE `salesorderdetails` (
`orderlineno` int(11) NOT NULL default '0',
`orderno` int(11) NOT NULL default '0',
`stkcode` varchar(20) NOT NULL default '',
`qtyinvoiced` double NOT NULL default '0',
`unitprice` double NOT NULL default '0',
`quantity` double NOT NULL default '0',
`estimate` tinyint(4) NOT NULL default '0',
`discountpercent` double NOT NULL default '0',
`actualdispatchdate` datetime NOT NULL default '0000-00-00 00:00:00',
`completed` tinyint(1) NOT NULL default '0',
`narrative` text,
`itemdue` date default NULL COMMENT 'Due date for line item. Some customers require \r\nacknowledgements with due dates by line item',
`poline` varchar(10) default NULL COMMENT 'Some Customers require acknowledgements with a PO line number for each sales line',
PRIMARY KEY (`orderlineno`,`orderno`),
KEY `OrderNo` (`orderno`),
KEY `StkCode` (`stkcode`),
KEY `Completed` (`completed`),
CONSTRAINT `salesorderdetails_ibfk_1` FOREIGN KEY (`orderno`) REFERENCES `salesorders` (`orderno`),
CONSTRAINT `salesorderdetails_ibfk_2` FOREIGN KEY (`stkcode`) REFERENCES `stockmaster` (`stockid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;表2
CREATE TABLE IF NOT EXISTS `orders` (
`orders_id` int(11) NOT NULL auto_increment,
`customers_id` int(11) NOT NULL default '0',
`customers_name` varchar(64) NOT NULL default '',
`customers_company` varchar(32) default NULL,
`customers_street_address` varchar(64) NOT NULL default '',
`customers_suburb` varchar(32) default NULL,
`customers_city` varchar(32) NOT NULL default '',
`customers_postcode` varchar(10) NOT NULL default '',
`customers_state` varchar(32) default NULL,
`customers_country` varchar(32) NOT NULL default '',
`customers_telephone` varchar(32) NOT NULL default '',
`customers_email_address` varchar(96) NOT NULL default '',
`customers_address_format_id` int(5) NOT NULL default '0',
`delivery_name` varchar(64) NOT NULL default '',
`delivery_company` varchar(32) default NULL,
`delivery_street_address` varchar(64) NOT NULL default '',
`delivery_suburb` varchar(32) default NULL,
`delivery_city` varchar(32) NOT NULL default '',
`delivery_postcode` varchar(10) NOT NULL default '',
`delivery_state` varchar(32) default NULL,
`delivery_country` varchar(32) NOT NULL default '',
`delivery_address_format_id` int(5) NOT NULL default '0',
`billing_name` varchar(64) NOT NULL default '',
`billing_company` varchar(32) default NULL,
`billing_street_address` varchar(64) NOT NULL default '',
`billing_suburb` varchar(32) default NULL,
`billing_city` varchar(32) NOT NULL default '',
`billing_postcode` varchar(10) NOT NULL default '',
`billing_state` varchar(32) default NULL,
`billing_country` varchar(32) NOT NULL default '',
`billing_address_format_id` int(5) NOT NULL default '0',
`payment_method` varchar(32) NOT NULL default '',
`payment_info` text,
`payment_id` int(11) NOT NULL default '0',
`cc_type` varchar(20) default NULL,
`cc_owner` varchar(64) default NULL,
`cc_number` varchar(90) default NULL,
`cc_expires` varchar(90) default NULL,
`last_modified` datetime default '0000-00-00 00:00:00',
`date_purchased` datetime default '0000-00-00 00:00:00',
`orders_status` int(5) NOT NULL default '0',
`orders_date_finished` datetime default '0000-00-00 00:00:00',
`currency` char(3) default NULL,
`currency_value` decimal(14,6) default NULL,
`account_name` varchar(32) default NULL,
`account_number` varchar(20) default NULL,
`po_number` varchar(12) default NULL,
`purchased_without_account` tinyint(1) unsigned NOT NULL default '0',
`paypal_ipn_id` int(11) NOT NULL default '0',
`cc_start` varchar(4) default NULL,
`cc_bank_phone` varchar(32) NOT NULL default '',
`cc_ccv` varchar(90) default NULL,
`ipaddy` varchar(15) NOT NULL default '',
`cc_issue` char(3) default NULL,
`ipisp` varchar(15) NOT NULL default '',
PRIMARY KEY (`orders_id`),
KEY `date_purchased` (`date_purchased`),
KEY `idx1_orders` (`customers_id`)
) ENGINE=MyISAM 急啊,高手帮帮心
select fl03,fd09 from db1.table1
where (fl03,fd09) not in (select col1,col2 from db2.table)
Database changed
mysql> select host,user,a.* from user,test.a;
+-----------+------+------+-------+-------+
| host | user | name | test | score |
+-----------+------+------+-------+-------+
| 127.0.0.1 | root | chen | test1 | 45 |
| localhost | | chen | test1 | 45 |
| localhost | root | chen | test1 | 45 |
| 127.0.0.1 | root | chen | test2 | 66 |
| localhost | | chen | test2 | 66 |
| localhost | root | chen | test2 | 66 |
| 127.0.0.1 | root | zhao | test1 | 37 |
| localhost | | zhao | test1 | 37 |
| localhost | root | zhao | test1 | 37 |
| 127.0.0.1 | root | sun | test2 | 59 |
| localhost | | sun | test2 | 59 |
| localhost | root | sun | test2 | 59 |
| 127.0.0.1 | root | zhao | test3 | 87 |
| localhost | | zhao | test3 | 87 |
| localhost | root | zhao | test3 | 87 |
| 127.0.0.1 | root | chen | test3 | 92 |
| localhost | | chen | test3 | 92 |
| localhost | root | chen | test3 | 92 |
+-----------+------+------+-------+-------+
18 rows in set (0.00 sec)然后进行操作