一，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
