mysql多表查询

(2009-05-03 19:26:53) 

转载

标签： 

mysql

name

from

sex

包含了员工的一些基本信息，如姓名、性别、出生日期、出生地。我们再创建一个表，该表用于描述员工

所发表的文章，内容包括作者姓名、文章标题、发表日期。

　　1、查看第一个表 mytable 的内容：

　　mysql> select * from mytable;

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

　　| name | sex | birth | birthaddr |

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

　　| abccs |f | 1977-07-07 | china |

　　| mary |f | 1978-12-12 | usa |

　　| tom |m | 1970-09-02 | usa |

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

　　2、创建第二个表 title （包括作者、文章标题、发表日期）:

　　mysql> create table title(writer varchar(20) not null,

　　-> title varchar(40) not null,

　　-> senddate date);

　　    向该表中填加记录，最后表的内容如下：

　　　　bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center">

 mysql> select * from title;
　　+--------+-------+------------+
　　| writer | title | senddate |
　　+--------+-------+------------+
　　| abccs | a1 | 2000-01-23 |
　　| mary | b1 | 1998-03-21 |
　　| abccs | a2 | 2000-12-04 |
　　| tom | c1 | 1992-05-16 |
　　| tom | c2 | 1999-12-12 |
　　+--------+-------+------------+
　　5 rows in set (0.00sec)
　　3、多表查询
　　现在我们有了两个表: mytable 和 title。利用这两个表我们可以进行组合查询：
　　上面例子中，由于作者姓名、性别、文章记录在两个不同表内，因此必须使用组合来进行查询。必须
要指定一个表中的记录如何与其它表中的记录进行匹配。
　　注意：如果第二个表 title 中的 writer 列也取名为  name（与mytable表中的name列相同）而不是
write r时，就必须用 mytable.name 和 title.name 表示，以示区别。
　　再举一个例子，用于查询文章 a2 的作者、出生地和出生日期：
　　mysql> select title,writer,birthaddr,birth from mytable,title
　　-> where mytable.name=title.writer and title=′a2′;
　　+-------+--------+-----------+------------+
　　| title | writer | birthaddr | birth |
　　+-------+--------+-----------+------------+
　　| a2 | abccs | china | 1977-07-07 |
　　+-------+--------+-----------+------------+
　　修改和备份、批处理
　　有时我们要对数据库表和数据库进行修改和删除，可以用如下方法实现：
　　1、增加一列：
　　如在前面例子中的 mytable 表中增加一列表示是否单身 single:
　　mysql> alter table mytable add column single char(1);
　　2、修改记录
　　将 abccs 的 single 记录修改为“y”：
　　mysql> update mytable set single=′y′ where name=′abccs′; 现在来看看发生了什么：
　　mysql> select * from mytable;
　　+----------+------+------------+-----------+--------+
　　| name | sex | birth | birthaddr | single |
　　+----------+------+------------+-----------+--------+
　　| abccs |f | 1977-07-07 | china | y |
　　| mary |f | 1978-12-12 | usa | NULL |
　　| tom |m | 1970-09-02 | usa | NULL |
　　+----------+------+------------+-----------+--------+
　　3、增加记录
　　前面已经讲过如何增加一条记录，为便于查看，重复与此：
　　mysql> insert into mytable
　　-> values (′abc′,′f′,′1966-08-17′,′china′,′n′);
　　Query OK, 1 row affected (0.05 sec)
　　查看一下：
　　mysql> select * from mytable;
　　+----------+------+------------+-----------+--------+
　　| name | sex | birth | birthaddr | single |
　　+----------+------+------------+-----------+--------+
　　| abccs |f | 1977-07-07 | china | y |
　　| mary |f | 1978-12-12 | usa | NULL |
　　| tom |m | 1970-09-02 | usa | NULL |
　　| abc |f | 1966-08-17 | china | n |
　　+----------+------+------------+-----------+--------+
　　4、删除记录
　　用如下命令删除表中的一条记录：mysql> delete from mytable where name=′abc′;
　　DELETE 从表中删除满足由 where 给出的条件的一条记录。再显示一下结果：
　　mysql> select * from mytable;
　　+----------+------+------------+-----------+--------+
　　| name | sex | birth | birthaddr | single |
　　+----------+------+------------+-----------+--------+
　　| abccs |f | 1977-07-07 | china | y |
　　| mary |f | 1978-12-12 | usa | NULL |
　　| tom |m | 1970-09-02 | usa | NULL |
　　+----------+------+------------+-----------+--------+
　　5、删除表：
　　mysql> drop table ****(表 1 的名字)，*** 表 2 的名字; 可以删除一个或多个表，小心使用。
　　6、数据库的删除：
　　mysql> drop database 数据库名; 小心使用。
　　7、数据库的备份：
　　退回到 DOS：
　　mysql> quit
　　d:\\mysqlbin
　　使用如下命令对数据库 abccs 进行备份：
　　mysqldump --opt abccs>abccs.dbb
　　abccs.dbb 就是你的数据库 abccs 的备份文件。
　　8、用批处理方式使用 MySQL:
　　首先建立一个批处理文件 mytest.sql，内容如下：
　　use abccs;
　　select * from mytable;
　　select name,sex from mytable where name=′abccs′;
　　在 DOS 下运行如下命令：d:mysqlbin mysql < mytest.sql
　　在屏幕上会显示执行结果。
　　如果想看结果，而输出结果很多，则可以用这样的命令： mysql < mytest.sql | more
　　我们还可以将结果输出到一个文件中： mysql < mytest.sql > mytest.out 
MySQL多表连接查询Left Join,Right Join 
作者：魏泽言 发布时间：2009-04-28 22:05:35 
在讲MySQL的Join语法前还是先回顾一下联结的语法，呵呵，其实连我自己都忘得差不多了，那就大家一起温习吧（如果内容有错误或有疑问，国内关于MySQL联结查询的资料十分少，相信大家在看了本文后会对MySQL联结语法有相当清晰的了解，也不会被Oracle的外联结的（“＋”号）弄得糊涂了。
在SQL标准中规划的（Join）联结大致分为下面四种： 
1． 内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。 
2． 外联结：分为外左联结和外右联结。 
左联结A、B表的意思就是将表A中的全部记录和表B中联结的字段与表A的联结字段符合联结条件的那些记录形成的记录集的联结，这里注意的是最后出来的记录集会包括表A的全部记录。 
右联结A、B表的结果和左联结B、A的结果是一样的，也就是说： 
Select A.name B.name From A Left Join B On A.id=B.id 
和Select A.name B.name From B Right Join A on B.id=A.id执行后的结果是一样的。 
3．全联结：将两个表中存在联结关系的字段的所有记录取出形成记录集的联结（这个不需要记忆，只要是查询中提到了的表的字段都会取出，无论是否符合联结条件，因此意义不大）。 
4．无联结：不用解释了吧，就是没有使用联结功能呗，也有自联结的说法。 
这里我有个比较简便的记忆方法，内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。其实大家回忆高等教育出版社出版的《数据库系统概论》书中讲到关系代数那章（就是将笛卡儿积和投影那章）的内容，相信不难理解这些联结功能的内涵。 
MySQL支持Select和某些Update和Delete情况下的Join语法，具体语法上的细节有： 
table_references: 
table_reference [, table_reference] … 
table_reference: 
table_factor 
| join_table 
table_factor: 
tbl_name [[AS] alias] 
[{USE|IGNORE|FORCE} INDEX (key_list)] 
| ( table_references ) 
| { OJ table_reference LEFT OUTER JOIN table_reference 
ON conditional_expr } 
join_table: 
table_reference [INNER | CROSS] JOIN table_factor [join_condition] 
| table_reference STRAIGHT_JOIN table_factor 
| table_reference STRAIGHT_JOIN table_factor ON condition 
| table_reference LEFT [OUTER] JOIN table_reference join_condition 
| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor 
| table_reference RIGHT [OUTER] JOIN table_reference join_condition 
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor 
join_condition: 
ON conditional_expr | USING (column_list) 
上面的用法摘自权威资料，不过大家看了是否有点晕呢？呵呵，应该问题主要还在于table_reference是什么，table_factor又是什么？这里的table_reference其实就是表的引用的意思，因为在MySQL看来，联结就是一种对表的引用，因此把需要联结的表定义为table_reference，同时在SQL Standard中也是如此看待的。而table_factor则是MySQL对这个引用的功能上的增强和扩充，使得引用的表可以是括号内的一系列表，如下面例子中的JOIN后面括号： 
Select * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 
这个语句的执行结果和下面语句其实是一样的： 
Select * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) 
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 
这两个例子不仅让我们了解了MySQL中table_factor和table_reference含义，同时能理解一点CROSS JOIN的用法，我要补充的是在MySQL现有版本中CROSS JOIN的作用和INNER JOIN是一样的（虽然在SQL Standard中是不一样的，然而在MySQL中他们的区别仅仅是INNER JOIN需要附加ON参数的语句，而CROSS JOIN不需要）。 
既然说到了ON语句，那就解释一下吧，ON语句其实和Where语句功能大致相当，只是这里的ON语句是专门针对联结表的，ON语句后面的条件的要求和书写方式和Where语句的要求是一样的，大家基本上可以把ON当作Where用。 
大家也许也看到了OJ table_reference LEFT OUTER JOIN table_reference这个句子，这不是MySQL的标准写法，只是为了和ODBC的SQL语法兼容而设定的，我很少用，Java的人更是不会用，所以也不多解释了。
Mysql多表查询，多表插入和多表更新
2010年08月14日 星期六 13:27
多表查询：
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(),
passcode char(),
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
mysql_select_db("test");
$query = "INSERT INTO contact(user_name,nom, prenom, mail, passcode) values('sa','se','sf', 
'safd@p.com', '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;
}
?>
需注意的是：
The mysql_insert_id() function returns the AUTO_INCREMENT ID generated from the previous 
INSERT operation.
mysql_insert_id()函数的作用是：取得上一步 INSERT 操作产生的 ID。
This function returns 0 if the previous operation does not generate an AUTO_INCREMENT ID, or 
FALSE on MySQL connection failure.
如果先前的操作不产生一个自动增加的ID[AUTO_INCREMENT ID]，那么，函数返回0；如果MySQL连接失败
，将返回False。
多表更新：
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;
示例：
【以下为引用：http://www.javaeye.com/problems/340 】
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)
==================================================================================
学习数据库查询的时候对多表连接查询的有些概念还比较模糊。而连接查询是在数据库查询操作的时候肯
定要用到的。对于此概念 
我用通俗一些的语言和例子来进行讲解。这个例子是我讲课的时候经常采用的例子。 
首先我们做两张表：员工信息表和部门信息表，在此，表的建立只为讲述连接的概念，所以字段非常的简
单 
EmployeeTB（员工信息表）： 
employeeid employeename deptid 
0001 张三 01 
0002 李四 01 
0003 王五 02 
0004 赵六 02 
0005 郑七 NULL 
DeptTB（部门信息表） 
deptid deptname 
01 技术部 
02 市场部 
03 工程部 
我们现在需要进行连接查询，连接两张表检索数据。分别检索员工信息表的员工编号、员工姓名和部门信
息表中的部门名称。 
显然，两个表的连接条件是 员工表的部门编号=部门表的部门编号 
注意：郑七不属于任何部门（新来的员工，还没有分配到任何的部门），而工程部不存在任何的员工（比
如是一个新成立的部门，还没有员工） 
1、内连接查询 
我们可以有两种方式，这两种是等效的 
一种是：SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e,DeptTB AS d WHERE 
e.deptid=d.deptid 
另外一个是：SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e INNER JOIN 
DeptTB AS d ON e.deptid=d.deptid 
检索的结果都是： 
employeeid employeename deptname 
0001 张三 技术部 
0002 李四 技术部 
0003 王五 市场部 
0004 赵六 市场部 
而“郑七”和“工程部”的信息是不会检索出来。因为采用内连接计算的时候必须要保证连接的条件
e.deptid=d.deptid匹配，结果才会被检索出来。当我们连接两张检索数据的时候，检索的方式是首先逐
行扫描“员工信息表”中的记录，然后根据连接条件来决定此记录是否被检索。比如对于张三，这条记录
的deptid是01（部门编号），它在部门表中能找到和它匹配的编号01，而编号01的部门名称（deptname）
是“技术部”所以张三这条记录会被检索，最终的结果肯定是： 
0001 张三 技术部 
同样，李四、王五、赵六也能。但是郑七的部门编号是NULL，它在部门信息表中找不到匹配的项（因为部
门信息表中不存在部门编号为NULL的部门），所以郑七不会被检索。 
同理，没有任何人员的部门编号为03，所以工程部的记录也不会被检索 
2、左外联结 
但是有些情况下，我们需要知道所有员工的信息，即使他不属于任何部门。这样我们就可以采用外连接，
在这里为左外连接，也就是连接中的左表的表中的记录，无论能不能在右表中找到匹配的项，都要检索，
如果没有匹配的项目，那么右表中的字段值为NULL（空），在这里就代表，此员工不属于任何部门。 
检索语句为： 
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e LEFT OUTER JOIN DeptTB AS 
d ON e.deptid=d.deptid 
检索的结果都是： 
employeeid employeename deptname 
0001 张三 技术部 
0002 李四 技术部 
0003 王五 市场部 
0004 赵六 市场部 
0005 郑七 NULL 
但是在这里，工程部同样不会被检索，因为，deptname是在连接的右边的表中，“工程部”在左表中不存
在任何的记录，所以不会被检索。这里关注的是“连接中的左边的表” 
3、右外连接 
有时，我们需要知道，全部部门的信息，即使它没有任何的员工。在我们的查询中部门表在连接的右边，
如果我们想知道右边表中的所有记录信息，那么就可以采用右外连接，如果此记录在左边的表中找不到匹
配项，则相应字段（employeeid,employeename)为NULL 
检索语句为： 
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e RIGHT OUTER JOIN DeptTB 
AS d ON e.deptid=d.deptid 
检索的结果都是： 
employeeid employeename deptname 
0001 张三 技术部 
0002 李四 技术部 
0003 王五 市场部 
0004 赵六 市场部 
NULL NULL 工程部 
但在这里，郑七是不会被检索了，因为它在右表中找不到匹配项，这里关注的是“连接中的右边的表” 
4、完全外连接 
如果我们想知道所有的记录呢？无论员工有没有部门，部门有没有员工，我们都需要检索。这里就可以使
用完全外连接。关注连接中的两部分。如果没有部门，部门为空，没有员工，员工信息为空。 
检索语句为： 
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e FULL OUTER JOIN DeptTB AS 
d ON e.deptid=d.deptid 
检索的结果都是： 
employeeid employeename deptname 
0001 张三 技术部 
0002 李四 技术部 
0003 王五 市场部 
0004 赵六 市场部 
0005 郑七 NULL 
NULL NULL 工程部