php复习 第十三天 mysql基础2

<?php
#测试表 user 存储员工基本信息：
#mysql> show create table user\G;查询创建数据表的结构
mysql> describe user;
+------------+-------------+------+-----+-------------+----------------+
| Field      | Type        | Null | Key | Default     | Extra          |
+------------+-------------+------+-----+-------------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL        | auto_increment |
| username   | varchar(32) | NO   |     | NULL        |                |
| department | varchar(32) | YES  |     | NULL        |                |
| address    | varchar(32) | YES  |     | NULL        |                |
| email      | varchar(32) | YES  |     | 126@126.com |                |
| salary     | double      | YES  |     | 0           |                |
+------------+-------------+------+-----+-------------+----------------+
6 rows in set (0.01 sec)


#测试表 user_record 存储员工履历信息：
#mysql> show create table user_record\G 查询创建数据表的结构

mysql> describe user_record;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| uid    | int(11)     | YES  |     | NULL    |       |
| record | varchar(32) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)


//内表连接 & 外表连接

#内连接：选取2张表中相互匹配的记录.

#案例1 查询有员工履历的员工id,姓名，邮箱，履历及工资。（使用别名区分2表id）
mysql> select user.id userId,user_record.uid userRecordId,user.username,user.email,user.salary,user_record.record from user,user_record where user.id = user_record.uid;
+--------+--------------+----------+-------------+--------+--------+
| userId | userRecordId | username | email       | salary | record |
+--------+--------------+----------+-------------+--------+--------+
|      1 |            1 | testtest | 126@126.com |   2000 | 搜狐   |
|      2 |            2 | local    | 126@126.com |   2500 | 雅虎   |
|      5 |            5 | maliu    | 123@126.com |   3500 | 百度   |
+--------+--------------+----------+-------------+--------+--------+
3 rows in set (0.00 sec)

#案例2 查询有员工履历的员工id,姓名，邮箱，履历及工资。（使用别名区分表名称）
mysql> select u.id,ur.uid,u.username,u.email,u.salary,ur.record from user u,user_record ur where u.id = ur.uid;
+----+------+----------+-------------+--------+--------+
| id | uid  | username | email       | salary | record |
+----+------+----------+-------------+--------+--------+
|  1 |    1 | testtest | 126@126.com |   2000 | 搜狐   |
|  2 |    2 | local    | 126@126.com |   2500 | 雅虎   |
|  5 |    5 | maliu    | 123@126.com |   3500 | 百度   |
+----+------+----------+-------------+--------+--------+
3 rows in set (0.00 sec)

#外连接：不仅选取2张表的记录，并且会选出其他不匹配的记录。
1）左外连接。
    概念：包含左边表中的所有记录（包括右表中没有和他匹配的记录）
mysql> select * from user left join user_record on user.id=user_record.uid;
+----+----------+------------+---------+--------------+--------+------+--------+
| id | username | department | address | email        | salary | uid  | record |
+----+----------+------------+---------+--------------+--------+------+--------+
|  1 | testtest | 100        | NULL    | 126@126.com  |   2000 |    1 | 搜狐   |
|  2 | local    | 100        | NULL    | 126@126.com  |   2500 |    2 | 雅虎   |
|  3 | zhangsan | 110        | NULL    | 126@126.com  |   2900 | NULL | NULL   |
|  4 | lisi     | 110        | NULL    | 126@126.com  |   9000 | NULL | NULL   |
|  5 | maliu    | 130        | NULL    | 123@126.com  |   3500 |    5 | 百度   |
|  6 | zhangqi  | 130        | NULL    | 123@126.com  |   3550 | NULL | NULL   |
|  7 | wabfqi   | 130        | NULL    | 1233@126.com |   3250 | NULL | NULL   |
+----+----------+------------+---------+--------------+--------+------+--------+
7 rows in set (0.00 sec)

2）右外连接。
    概念：包含右边表中的所有记录（包括左表中没有和他匹配的记录）
mysql> select * from user right join user_record on user.id=user_record.uid;
+------+----------+------------+---------+-------------+--------+------+--------+
| id   | username | department | address | email       | salary | uid  | record |
+------+----------+------------+---------+-------------+--------+------+--------+
|    1 | testtest | 100        | NULL    | 126@126.com |   2000 |    1 | 搜狐   |
|    2 | local    | 100        | NULL    | 126@126.com |   2500 |    2 | 雅虎   |
|    5 | maliu    | 130        | NULL    | 123@126.com |   3500 |    5 | 百度   |
+------+----------+------------+---------+-------------+--------+------+--------+
3 rows in set (0.00 sec)

?>