MySql 多表联合查询.docx

MySql 多表联合查询Mysql多表查询，多表插入和多表更新多表查询：CREATE TABLE IF NOT EXISTS contact(contact_idint(11) NOT NULL AUTO_INCREMENT,user_namevarchar(255),nom varchar(255),prenomvarchar(255),mail varchar(64),passcode char(64),PRIMARY KEY(contact_id));CREATE TABLE IF NOT EXISTS droit(droit_idint( 11 ) NOT NULL AUTO_INCREMENT ,droit varchar(255),PRIMARY KEY(droit_id));CREATE TABLE IF NOT EXISTS contactdroit(contactdroit_idint(11) NOT NULL AUTO_INCREMENT,contact_idint( 11 ),droit_idint( 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 dwhere c.contact_id = cd.contact_idand cd.droit_id = d.droit_id;结果：contact_id ??? droit_id ??? droit1 ??? ???????????????? 1 ??? ????? admin2 ??? ???????????????? 1 ??? ????? admin3 ??? ??? ???????? 2 ??? ???? superuser多表联查例子：两个方法都可以，inner join on 更好点。表结构没贴出来，但比较好懂了。简单方法：selectc.nom, e.nomfrom consultant c, affaire a, besoin b, salariesa, site s, entreprise ewhere c.consultant_id=a.consultant_id and a.besoin_id=b.besoin_id and b.salarie_id=sa.salarie_id and sa.site_id=s.site_id and s.entreprise_id=e.entreprise_idinner join方法：selectc.nom, e.nomfrom consultant cinner join affaire a on c.consultant_id=a.consultant_idinner join besoin b on a.besoin_id=b.besoin_idinner join salariesa on b.salarie_id=sa.salarie_idinner join site s onsa.site_id=s.site_idinner join entreprise e on s.entreprise_id=e.entreprise_id多表插入：?php$conn = mysql_connect(“localhost”,”charles”,”charles”);mysql_select_db(“test”);$query = “INSERT INTO contact(user_nam