一些SQL操作语句(9),COPY tables
返回
<![CDATA[
用show create table来复制table
mysql> SHOW CREATE TABLE enq2\G
*************************** 1. row ***************************
Table: enq2
Create Table: CREATE TABLE `enq2` (
`id` int(11) NOT NULL auto_increment,
`time` date default '2011-00-00',
`enq1id` int(11) default NULL,
`LANGUAGEid` int(11) default NULL,
`ORIGINid` int(11) default NULL,
`PRICEid` int(11) default NULL,
`PAYMENTid` int(11) default NULL,
`makerid` int(11) default NULL,
`DELIVERY` text,
`type1id` text,
`partsid` text,
`QTY` text,
`memo` text,
`price` text,
`discount` text,
`discount0` int(11) default NULL,
`money` int(11) default NULL,
`price1` text,
`makerref` text,
`disc` text,
`charges` text,
`status` int(11) NOT NULL default '0',
`post` char(100) NOT NULL default 'NO',
`arrivaltime` date NOT NULL default '2030-01-01',
`arrivalmemo` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13946 DEFAULT CHARSET=utf8
1 row in set (1.00 sec)
---------------------------------------------------------
Create tables 觉得有问题,需要修改!
mysql> CREATE TABLE packing_ok (id INT, writing VARCHAR(20));
Query OK, 0 rows affected (0.17 sec)
mysql> INSERT INTO packing_ok (id,writing) VAULES('1','OK');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'VAULE
S('1','OK')' at line 1
mysql> INSERT INTO packing_ok (id,writing) VAULES(1,'OK');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'VAULE
S(1,'OK')' at line 1
mysql> show columns from packing_ok
-> ;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| writing | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> INSERT INTO packing_ok (writing) VALUES('OK');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO packing_ok (writing) VALUES('NG');
Query OK, 1 row affected (0.01 sec)
mysql> select * from packing_ok;
+------+---------+
| id | writing |
+------+---------+
| NULL | OK |
| NULL | NG |
+------+---------+
2 rows in set (0.00 sec)
mysql> update packing_ok set id = 1 where writing = 'OK';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update packing_ok set id = 2 where writing = 'NG';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from packing_ok;
+------+---------+
| id | writing |
+------+---------+
| 1 | OK |
| 2 | NG |
+------+---------+
2 rows in set (0.00 sec)
]]>
<![CDATA[
packing_ok.sql
DROP TABLE IF EXISTS packing_ok;
CREATE TABLE packing_ok
(
id INT AUTO_INCREMENT,
PACKING1 varchar(20),
PRIMARY KEY (id)
);
INSERT INTO packing_ok (PACKING1) VALUES("OK");
INSERT INTO packing_ok (PACKING1) VALUES("NG");
c:\database\sql>mysql cookbook < packing_ok.sql -u cbuser -p
Enter password: ******
c:\database\sql>mysql -u cbuser -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use cookbook;
Database changed
mysql> select * from packing_ok;
+----+----------+
| id | PACKING1 |
+----+----------+
| 1 | OK |
| 2 | NG |
+----+----------+
2 rows in set (0.00 sec)
]]>
返回