--连接mysql
安装MySQL的ODBC驱动MyODBC
1、为MySQL建立一个ODBC系统数据源,例如:选择数据库为test ,数据源名称为 myDSN
2、建立链接数据库
EXEC sp_addlinkedserver @server = 'MySQLTest', @srvproduct='MySQL', @provider = 'MSDASQL', @datasrc = 'myDSN'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname='MySqlTest',@useself='false',@locallogin='sa',@rmtuser='mysql的用户名',@rmtpassword='mysql的密码'
3、查询数据
SELECT * FROM OPENQUERY (MySQLTest ,'select * from 表' )
如果只是结果集中交换两列: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 |
+-------+-------------+------+-----+---------+----------------+
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)