mysql常用连接查询

连接数据库PDO

$user = "root";  //数据库连接账号

$pass = "root";  //数据库连接密码

$dbname = "test";		//数据库名

try {
    $db = new PDO("mysql:host=localhost;dbname=$dbname", $user, $pass);  //连接数据库
	echo "数据库连接成功";
} catch (PDOException $e) {
	echo "数据库连接失败";
}

表一：user表

id	name 姓名	sex 性别	age 年龄
1	fan	0	18
2	jack	0	25
3	xiaoming	0	33
4	laowang	1	50
5	julia	0	22
6	pangda	1	53

表二：user_Identity表

id	identity_number 身份号码
1	111
2	222
3	3
4	444
5	555
6	666

1、内连接（Inner Join）

$sql = "select * from user as a inner join user_identity as b on a.id = b.identity_number"; //内连接user和user_identity表，as 起别名：user代表a，user_identity代表b

//$sql = "select * from user inner join user_identity on user.id = user_identity.id"; // 效果同上

//$sql = "select a.id,a.name,b.identity_number from user as a inner join user_identity as b on a.id = b.id";  //select 和 from 之间的意思是 - 输出a表的id字段，输出a表的name字段，输出b表的identity_number;

$data = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);

print_r ($data);

输出：

Array
(
    [0] => Array
        (
            [id] => 3
            [name] => xiaoming
            [sex] => 0
            [age] => 33
            [identity_number] => 3
        )

)

 2、左连接 （Left Join）

$sql = "select * from user as a left join user_identity as b on a.id = b.identity_number";

$data =  $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);

print_r($data);

输出：

Array
(
    [0] => Array
        (
            [id] => null
            [name] => fan
            [sex] => 0
            [age] => 18
            [identity_number] => null
        )

    [1] => Array
        (
            [id] => null
            [name] => jack
            [sex] => 0
            [age] => 25
            [identity_number] => null
        )

    [2] => Array
        (
            [id] => 3
            [name] => xiaoming
            [sex] => 0
            [age] => 33
            [identity_number] => 3
        )

    [3] => Array
        (
            [id] => null
            [name] => laowang
            [sex] => 1
            [age] => 50
            [identity_number] => null
        )

    [4] => Array
        (
            [id] => null
            [name] => julia
            [sex] => 0
            [age] => 22
            [identity_number] => null
        )

    [5] => Array
        (
            [id] => null
            [name] => pangda
            [sex] => 1
            [age] => 53
            [identity_number] => null
        )

)

 3、右连接 （Right Join）

$sql = "select * from user as a right join user_identity as b on a.id = b.identity_number";

$data =  $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);

print_r($data);

输出：

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => null
            [sex] => null
            [age] => null
            [identity_number] => 111
        )

    [1] => Array
        (
            [id] => 2
            [name] => null
            [sex] => null
            [age] => null
            [identity_number] => 222
        )

    [2] => Array
        (
            [id] => 3
            [name] => xiaoming
            [sex] => 0
            [age] => 33
            [identity_number] => 3
        )

    [3] => Array
        (
            [id] => 4
            [name] => null
            [sex] => null
            [age] => null
            [identity_number] => 444
        )

    [4] => Array
        (
            [id] => 5
            [name] => null
            [sex] => null
            [age] => null
            [identity_number] => 555
        )

    [5] => Array
        (
            [id] => 6
            [name] => null
            [sex] => null
            [age] => null
            [identity_number] => 666
        )

)

 4、其他连接-省略。。。