mysql 多表联合查询

mysql 多表联合查询

Mysql多表查询，多表插入和多表更新

2010年8月27日星期四 11:24 A.M.

/*************************************by

garcon1986*****************************************************/

多表查询：

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 sa.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 sa.site_id=s.site_id

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

多表插入：

$conn = mysql_connect("localhost","charles","charles");

mysql_select_db("test");

$query = "INSERT INTO contact(user_name,nom, prenom, mail, passcode) values('sa','se','sf', 'safd@', '123')";

$result = mysql_query($query) or die("insert contact

failed:".mysql_error());

$lastid = mysql_insert_id(); //得到上一个插入的id值

echo "last insert id :".$lastid."
";

$query2 = "INSERT INTO contactdroit(contact_id, droit_id)

values('$lastid','11')";

echo $query2."
";

$result2 = mysql_query($query2) or die("insert contactdroit failed: ".mysql_error());

if(isset($result) && isset($result2)){

echo "Good Insertion
";

echo $lastid;