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)
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）