mysql 查询更新两个表_MySQL多表查询、多表更新的例子

一，MySQL多表更新的方法

MySQL多表更新：

update contact c, contactdroit cd

set c.user_name = '$username', c.nom = '$lastname', c.prenom = '$firstname', c.passcode = '$password', cd.droit_id = '$droitid'

where c.contact_id = '$id' and c.contact_id = cd.contact_id;

示例：

复制代码 代码示例:

mysql> create table one(id int(10), name varchar(20));

Query OK, 0 rows affected (0.03 sec)

mysql> create table two(id int(10), name varchar(20));

Query OK, 0 rows affected (0.05 sec)

mysql> insert one value(1, '1');

Query OK, 1 row affected (0.00 sec)

mysql> insert two value(22, '22');

Query OK, 1 row affected (1.02 sec)

mysql> update one o, two t set o.name='oo', t.name='tt';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from one;

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

| id   | name |

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

|    1 | oo   |

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

1 row in set (0.00 sec)

mysql> select * from two;

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

| id   | name |

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

|   22 | tt   |

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

1 row in set (0.00 sec)

二，Mysql多表查询的实现

通过实例学习下Mysql多表查询的实现方法。

多表查询

复制代码 代码示例:

CREATE TABLE IF NOT EXISTS contact(

contact_id int(11) NOT NULL AUTO_INCREMENT,

user_name varchar(255),

nom varchar(255),

prenom varchar(255),

mail varchar(64),

passcode char(64),

PRIMARY KEY(contact_id)

);

CREATE TABLE IF NOT EXISTS droit(

droit_id int( 11 ) NOT NULL AUTO_INCREMENT ,

droit varchar(255),

PRIMARY KEY(droit_id)

);

CREATE TABLE IF NOT EXISTS contactdroit(

contactdroit_id int(11) NOT NULL AUTO_INCREMENT,

contact_id int( 11 ),

droit_id int( 11 ),

PRIMARY KEY( contactdroit_id )

);

Insert into contact(contact_id, user_name) values(1,'user1');

Insert into contact(contact_id, user_name) values(2,'user2');

Insert into contact(contact_id, user_name) values(3,'user3');

Insert into droit(droit_id, droit) values(1,'admin');

Insert into droit(droit_id, droit) values(2,'superuser');

Insert into contactdroit(contact_id, droit_id) values(1, 1);

Insert into contactdroit(contact_id, droit_id) values(2, 1);

Insert into contactdroit(contact_id, droit_id) values(3, 2);

SELECT c.contact_id, d.droit_id, d.droit FROM contact c, contactdroit cd, droit d

where c.contact_id = cd.contact_id

and cd.droit_id = d.droit_id;

结果：

contact_id     droit_id     droit

1                      1           admin

2                      1           admin

3                  2          superuser

多表联查例子：

两个方法都可以，inner join on 更好点。

例子：

复制代码 代码示例:

select c.nom, e.nom

from consultant c, affaire a, besoin b, salarie sa, site s, entreprise e

where c.consultant_id=a.consultant_id and a.besoin_id=b.besoin_id and b.salarie_id=sa.salarie_id and ssa.site_id=s.site_id and s.entreprise_id=e.entreprise_id

inner join方法：

复制代码 代码示例:

select c.nom, e.nom

from consultant c

inner join affaire a on c.consultant_id=a.consultant_id

inner join besoin b on a.besoin_id=b.besoin_id

inner join salarie sa on b.salarie_id=sa.salarie_id

inner join site s on ssa.site_id=s.site_id

inner join entreprise e on s.entreprise_id=e.entreprise_id