mysql想实现两列表格相邻行内容互换报错

mysql想实现两列表格相邻行内容互换报错,第1张

如果只是结果集中交换两列:

select name,id from 表名

如果更改表结构:

alter table 表名 modify column id int auto_increment after name

比如:

#建立测试表 t1

mysql>create table t1(id int auto_increment,name varchar(20),primary key(id))engine=innodb,default charset=utf8

Query OK, 0 rows affected (0.11 sec)

#插入3条数据

mysql>insert into t1(name) values ("aa"),("bb"),("cc")

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql>select * from t1

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

+----+------+

3 rows in set (0.00 sec)

#结果集中排列name到id前

mysql>select name,id from t1

+------+----+

| name | id |

+------+----+

| aa | 1 |

| bb | 2 |

| cc | 3 |

+------+----+

3 rows in set (0.00 sec)

#改变表结构,使name在id前

mysql>alter table t1 modify column id int auto_increment after name

Query OK, 3 rows affected (0.19 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql>select * from t1

+------+----+

| name | id |

+------+----+

| aa | 1 |

| bb | 2 |

| cc | 3 |

+------+----+

3 rows in set (0.00 sec)

mysql>desc t1

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| name | varchar(20) | YES | | NULL | |

| id | int(11) | NO | PRI | NULL | auto_increment |

+-------+-------------+------+-----+---------+----------------+

   SELECT a.date_3 - b.date_3 FROM tab1 a,tab2 b WHERE b.numb = a.numb

前提是tab1和tab2都要有对应的numb

首先是测试表/数据

mysql>

select

*

from

t1

+------+------+------+---------+

|

id

|

name

|

age

|

address

|

+------+------+------+---------+

|

1

|

小张

|

0

|

北京

|

|

2

|

小丽

|

0

|

上海

|

|

3

|

小花

|

0

|

云南

|

|

4

|

小刚

|

0

|

四川

|

|

5

|

小陈

|

0

|

重庆

|

+------+------+------+---------+

5

rows

in

set

(0.00

sec)

mysql>

select

*

from

t2

+------+------+------+---------+

|

id

|

name

|

age

|

address

|

+------+------+------+---------+

|

1

|

小白

|

15

|

北京

|

|

2

|

大侠

|

48

|

上海

|

|

3

|

小花

|

28

|

云南

|

|

4

|

小刚

|

28

|

四川

|

|

5

|

小陈

|

24

|

重庆

|

|

6

|

小丽

|

24

|

天津

|

+------+------+------+---------+

6

rows

in

set

(0.02

sec)

查询一下

mysql>

SELECT

->

t1.name

AS

t1Name,

->

t1.age

AS

t1Age,

->

t2.name

AS

t2Name,

->

t2.age

AS

t2Age

->

FROM

->

t1,

t2

->

WHERE

->

t1.name

=

t2.name

+--------+-------+--------+-------+

|

t1Name

|

t1Age

|

t2Name

|

t2Age

|

+--------+-------+--------+-------+

|

小花

|

0

|

小花

|

28

|

|

小刚

|

0

|

小刚

|

28

|

|

小陈

|

0

|

小陈

|

24

|

|

小丽

|

0

|

小丽

|

24

|

+--------+-------+--------+-------+

4

rows

in

set

(0.00

sec)

建立一个

VIEW

mysql>

CREATE

VIEW

temp_view

AS

->

SELECT

->

t1.name

AS

t1Name,

->

t1.age

AS

t1Age,

->

t2.name

AS

t2Name,

->

t2.age

AS

t2Age

->

FROM

->

t1,

t2

->

WHERE

->

t1.name

=

t2.name

Query

OK,

0

rows

affected

(0.03

sec)

更新数据

mysql>

UPDATE

temp_view

SET

t1Age

=

t2Age

Query

OK,

4

rows

affected

(0.03

sec)

Rows

matched:

4

Changed:

4

Warnings:

0

检查结果

mysql>

select

*

from

t1

+------+------+------+---------+

|

id

|

name

|

age

|

address

|

+------+------+------+---------+

|

1

|

小张

|

0

|

北京

|

|

2

|

小丽

|

24

|

上海

|

|

3

|

小花

|

28

|

云南

|

|

4

|

小刚

|

28

|

四川

|

|

5

|

小陈

|

24

|

重庆

|

+------+------+------+---------+

5

rows

in

set

(0.00

sec)


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/7508889.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-06
下一篇 2023-04-06

发表评论

登录后才能评论

评论列表(0条)

保存