表如下所示
| 代码如下 | 复制代码 |
|
SELECT * FROM `test_table_draw` WHERE `borough_id`>=10266 and `borough_id` <=12354 户型表 SELECT * FROM `test_table_pic` WHERE `borough_id`>=10266 and `borough_id` |
|
<=12354
相册表
创建一个备份表
| 代码如下 | 复制代码 |
|
CREATE TABLE IF NOT EXISTS `test_table_tp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pic_url` varchar(200) NOT NULL, `pic_thumb` varchar(200) DEFAULT NULL, `pic_desc` varchar(200) DEFAULT NULL, `borough_id` int(11) NOT NULL, `creater` varchar(20) DEFAULT NULL, `addtime` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `borough_id` (`borough_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=97289 ; |
|
语法
| 代码如下 | 复制代码 |
|
Insert into Table2(field1,field2,...) select value1,value2,... from Table1 例子 Insert into test_table_tp select * from test_table_draw where `borough_id`>=10266 and `borough_id` |
|
错误提示
| 代码如下 | 复制代码 |
|
INSERT INTO test_table_tp SELECT * FROM test_table_draw WHERE `borough_id` >=10266 AND `borough_id` MySQL 返回： #1136 - Column count doesn't match value count at row 1 |
|
解决
| 代码如下 | 复制代码 |
|
Insert into(id,pic_url,pic_thumb,pic_desc,borough_id,creater,addtime) test_table_tp select id,pic_url,pic_thumb,pic_desc,borough_id,creater,addtimefrom test_table_draw where `borough_id`>=10266 and `borough_id` <=12354 |
|
后来发现上面不对于时又找
| 代码如下 | 复制代码 |
|
Select * Into new_table_name from old_table_name; 测试仪 Select * Into test_table_tp1 from test_table_draw where `borough_id`>=10266 and `borough_id` <=12354 |
|
执行后错误
错误
SQL 查询:
| 代码如下 | 复制代码 |
|
SELECT * INTO test_table_tp FROM test_table_draw WHERE `borough_id` >=10266 AND `borough_id` LIMIT 0 , 30 MySQL 返回： #1327 - Undeclared variable: test_table_tp |
|
网上找到提示要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
| 代码如下 | 复制代码 |
| Select * Into test_table_tp1 from test_table_draw where `borough_id`>=10266 and `borough_id` <=12354 | |
gg查一下发现原来 mysql 数据库是不支持 SELECT INTO FROM 这种语句的，但是经过研究是可以通过另外一种变通的方法解决这个问题的
| 代码如下 | 复制代码 |
| Create table Table2 (Select * from Table1); | |
这种语句代替
| 代码如下 | 复制代码 |
|
SELECT vale1, value2, value3 into Table2 from Table1; |
|
下面看一个本人实际操作中的例子吧！
| 代码如下 | 复制代码 |
|
Create table www.111cn.net (Select A.id,B.id as typeId,A.brand,A.quanpin,A.simple from brands as A,cartype as B where A.type=B.name group by A.brand); |
|
这个是关联两个表得出一个查询结果，然后将结果插入到一个新创建的表 www.111cn.net 中。
好了现在我再来做
| 代码如下 | 复制代码 |
|
Create table pic_test (Select * from test_table_draw where `borough_id`>=10266 and `borough_id` <=12354) |
|
成功了啊
您运行的 SQL 语句已经成功运行了。 (查询花费 0.5645 秒)
SQL 查询:
| 代码如下 | 复制代码 |
| CREATE TABLE pic_test( SELECT * FROM test_table_draw WHERE `borough_id` >=10266 AND `borough_id` <=12354 ) |
|
时间： 2024-09-14 18:18:37