标签归档:MySQL

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

多表插入

<?php
$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@p.com', '123')";
$result = mysql_query($query) or die("insert contact failed:".mysql_error());
$lastid = mysql_insert_id(); //得到上一个 插入的id值
echo "last insert id :".$lastid."<br>";
$query2 = "INSERT INTO contactdroit(contact_id, droit_id) values('$lastid','11')";
echo $query2."<br>";
$result2 = mysql_query($query2) or die("insert contactdroit failed: ".mysql_error());
if(isset($result) && isset($result2)){
echo "Good Insertion<br>";
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 工程部

用phpMyadmin创建MySQL数据库及独立数据库帐号的图文教程

在一个服务器上一般来讲都不止一个站点,更不止一个MySQL(和PHP搭配之最佳组合)数据库。
为了防止安全隐患,我们一般针对每个数据库都设置了独立的数据库访问帐号,该帐号仅有访问该数据库的权限。下面就让我们来具体演示一下:

1、首先我们要登陆php(做为现在的主流开发语言)MyAdmin,不做演示。

2、创建一个数据库,如下图,在php(做为现在的主流开发语言)MyAdmin右边窗口中,填写数据库名称,点创建即可。 



例如我们这里创建一个名字为:cncmstest 的数据库

创建成功会有如下提示:

3、点击左上角的主页按钮,返回php(做为现在的主流开发语言)MyAdmin主界面:

4、在主界面的右边点击“权限”来创建数据库帐号。

5、在权限页面中,我们点击“添加新用户”

6、在该页面中,我们填写要创建的数据库用户名,该用户的访问范围,及密码。



如上图,我们填写了用户名为:cncmsuser,该数据库用户只允许本机访问,主机一项选择本地;密码我们使用自动生成的,点下面的“Generate”会生成一个随机密码,然后点“Copy”会自动填写到密码框中。

下面的框都不选,直接拉到页面最下面点执行即可创建一个新用户。

数据库用户创建成功,会返回如下页面:

7、最重要的一步,设置该用户的数据库访问权限

在数据库用户添加成功返回的页面中可以直接设置权限。这里我们选择按数据库指定权限:



如上图,在数据库列表中选择我们刚刚创建的cncmstest,即会自动进入该数据库的权限设置页面。



在上图的权限设置中,我们把“数据”、“结构”两列的权限全部选中,管理权限都不要选。点执行即可。

到这里,我们已经全部设置完毕了,创建了一个数据库:cncmstest,并创建了数据库用户cncmsuser,特别指定了该用户只对cncmstest的访问权限。如此,便达到了我们一开始所讲的目的:为每一个数据库指定独立的用户访问权限。

VC连接MySQL

一、MySQL的安装
注意选择“完全安装”(只有这样才会安装VC编译时需要的头文件等)。安装后期会进行服务器配置,你可以设置你的服务器登陆密码,也可以不设置密码。

二、VC6.0的设置

(1)打开VC6.0 工具栏Tools菜单下的Options选项,在Directories的标签页中右边的“Show directories for:”下拉列表中选中“Includefiles”,然后在中间列表框中添加你本地安装MySQL的include目录路径。(我的是D:\Program Files\MySQL\MySQL Server 5.0\include)。

(2)在上面说到的“Show directories for:”下拉列表中选中“Library files”,然后添加你本地安装MySQL的Lib目录路径。Lib目录下还有debug和opt两个目录,建议选debug。(我的是D:\Program Files\MySQL\MySQL Server 5.0\lib\debug)。

(3)在“Project settings->Link:Object/library modules”里面添加“libmysql.lib”。

(4)在stdafx.h里面添加如下的内容:

#include "mysql.h"
#include "winsock.h"   // 如果编译出错,则把该行放到#include "mysql.h"之前
#pragma comment(lib,"libmySQL.lib")   // 如果在附加依赖项里已增加,则就不要添加了
(5)建议将“libmySQL.lib、libmySQL.dll”拷到你所建的工程的目录下。

三、数据库、表的创建
打开“开始->所有程序->MySQL->MySQL Server 5.0->MySQL Command Line Client.exe”,如果没有设置密码就直接按回车,会提示服务器启动成功。

mysql> SHOW DATABASES;//显示所有的数据库,注意一定要 敲“;”后再按回车
mysql> CREATE DATABASE mydb;//创建数据库mydb
mysql> USE mydb;//选择你所创建的数据库mydb
mysql> SHOW TABLES; //显示数据库中的表
mysql> CREATE TABLE mytable (username VARCHAR(100), visitelist VARCHAR(200),                                 remark VARCHAR(200));//创建一个表mytable: 用户名;访问列表;备注
mysql> DESCRIBE mytable;//显示表的结构
四、VC编程

MYSQL mysql; //数据库连接句柄
mysql_init (&mysql);
if(!mysql_real_connect(&mysql,"localhost","root",NULL,"mydb",3306,NULL,0))  {//mydb为你所创建的数据库,3306为端口号,可自行设定
AfxMessageBox("数据库连接失败");
return FALSE;
}
(1)实现添加 功能

CString strUsername,strList,strRemark,strSQL;
strSQL.Format("insert into mytable(username,visitelist,remark) values(\'%s\',\'%s\',\'%s\')",                                        strUsername,strList,strRemark);//注意一定要写在一行,而且必须要有\'\'
if(mysql_real_query(&mysql,(char*)(LPCTSTR)strSQL,(UINT)strSQL.GetLength())!=0){
AfxMessageBox("增添失败");
}

(2)实现修改功能
CString strUsername,strList,strRemark,strSQL,str_PreName;//str_PreName用于记录想要修改的行,详情请看源代码
strSQL.Format("update mytable set username=\'%s\',visitelist=\'%s\',                              remark=\'%s\' where username=\'%s\'",strUsername,strList,strRemark,str_PreName);
if(mysql_real_query(&mysql,(char*)(LPCTSTR)strSQL,(UINT)strSQL.GetLength())!=0){
AfxMessageBox("修改失败");
}
(3)实现删除功能

CString strSQL;
strSQL.Format("delete from mytable where username=\'%s\'",str_PreName);//必须要有\'\'
if(mysql_real_query(&mysql,(char*)(LPCTSTR)strSQL,(UINT)strSQL.GetLength())!=0){
AfxMessageBox("删除失败");
}
(4)读取表格内容到CListCtrl控件m_list

m_list.DeleteAllItems();
char *ch_query;
ch_query="select * from mytable";
if(mysql_real_query(&mysql,ch_query,(UINT)strlen(ch_query))!=0){
AfxMessageBox("数据库中表格出错");
}
CString str;
MYSQL_RES *result;
MYSQL_ROW row;
if(!(result=mysql_use_result(&mysql))){
AfxMessageBox("读取数据集失败");
}
int i=0;
while(row=mysql_fetch_row(result)){
str.Format("%s",row[0]);
m_list.InsertItem(i,str);
str.Format("%s",row[1]);
m_list.SetItemText(i,1,str);
str.Format("%s",row[2]);
m_list.SetItemText(i,2,str);
i++;
}
mysql_free_result(result);
(5)关闭数据库

mysql_close(&mysql);//最好写到OnDestroy()函数中

MySQL 备份还原数据库批处理

1、备份数据库(单个表备份) bk_table.bat

mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database1 table1>table1.sql
mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database2 table2>table2.sql
mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database2 table3>table3.sql
pause

2、还原数据库(单个表还原) restore.bat

mysql -h127.0.0.1 -P3306 -uroot -proot database1 < table1.sql
mysql -h127.0.0.1 -P3306 -uroot -proot database2 < table2.sql
mysql -h127.0.0.1 -P3306 -uroot -proot database2 < table3.sql
pause

3、备份数据库并自动打包压缩成.rar文件(单个表备份) bk_table.bat

mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database1 table1>table1.sql
mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database2 table2>table2.sql
mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database2 table3>table3.sql
winrar a Db_Bak_%date:~0,10%.rar D:\DB_Backup\*.sql
DEL D:\DB_Backup\*.sql
pause

执行批处理后,你的备份数据脚本就会被压缩到你的电脑D:\DB_Backup 的 Db_Bak_日期.rar文件中。
如果出现“'WinRAR' 不是内部或外部命令,也不是可运行的程序或批处理文件。” 错误,在计算机->属性->高级->环境变量->在Path项添加你的WinRAR路径,如:C:\Program Files\WinRAR即可。
 
4、备份整个库的数据

mysqldump -h127.0.0.1 -P3306 -uroot -proot database1 > database1.sql
Pause

说明:
1、打开记事本->把以上脚本拷贝到记事本->另存为*.bat文件即可双击打开执行。
2、需要将你的MySql安装bin目录路径添加到计算机环境变量的Path里:
计算机->属性->高级->环境变量->在Path项添加 你的Mysql安装bin目录路径 如:C:\Program Files\MySQL\MySQL Server 5.0\bin或者将批处理文件拷贝到bin目录下C:\Program Files\MySQL\MySQL Server 5.0\bin执行。