mysql 链接查询 as_mysql连接查询

一、外键

新建一个score 表，外键关联student 表的stu_id

mysql> desc student;

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

| Field | Type | Null | Key | Default | Extra |

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

| stu_id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(50) | YES | | NULL | |

| register_date | date | NO | | NULL | |

| gender | enum('M','F') | NO | | NULL | |

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

4 rows in set (0.00 sec)

# 新建score表

mysql> create table score(

-> id int not null auto_increment primary key,

-> score tinyint,

-> stu_id int not null,

-> KEY `fk_student_key` (`stu_id`),

-> constraint `fk_student_key` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`)

-> );

Query OK, 0 rows affected (0.07 sec)

mysql> desc score;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| score | tinyint(4) | YES | | NULL | |

| stu_id | int(11) | NO | MUL | NULL | |

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

3 rows in set (0.00 sec)

插入数据

mysql> insert into score (score,stu_id) values (90,1);

Query OK, 1 row affected (0.00 sec)

mysql> insert into score (score,stu_id) values (85,2);

Query OK, 1 row affected (0.00 sec)

mysql> insert into score (score,stu_id) values (89,2);

Query OK, 1 row affected (0.00 sec)

mysql> select * from score;

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

| id | score | stu_id |

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

| 1 | 90 | 1 |

| 2 | 85 | 2 |

| 3 | 89 | 2 |

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

3 rows in set (0.00 sec)

删除数据

删除score表中的数据不受影响，但是删除student表的数据，如果和score表相关联，则不能删除

mysql> delete from score where id=3;

Query OK, 1 row affected (0.03 sec)

mysql> delete from student where stu_id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`study`.`score`, CONSTRAINT `fk_student_key` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`))

二、Mysql NULL值处理

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

IS NULL: 当列的值是NULL,此运算符返回true。

IS NOT NULL: 当列的值不为NULL, 运算符返回true。

<=>: 比较操作符(不同于=运算符)，当比较的的两个值为NULL时返回true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。在MySQL中，NULL值与任何其它值的比较(即使是NULL)永远返回false，即 NULL = NULL 返回false 。MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

三、联合查询

可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN(内连接,或等值连接)：获取两个表中字段匹配关系的记录。

LEFT JOIN(左连接)：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN(右连接)： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录

3.1 inner join

联合查询student 和score的id, name, score

mysql> select a.stu_id,a.name,b.score from student as a

-> inner join score as b

-> on a.stu_id = b.stu_id;

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

| stu_id | name | score |

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

| 1 | bigberg | 90 |

| 2 | zhansan | 85 |

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

2 rows in set (0.00 sec)

3.2 left join

LEFT JOIN 关键字从左表(table1)返回所有的行，即使右表(table2)中没有匹配。如果右表中没有匹配，则结果为 NULL。

mysql> select a.stu_id as id ,a.name, b.score from student as a

-> left join score as b

-> on a.stu_id = b.stu_id;

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

| id | name | score |

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

| 1 | bigberg | 90 |

| 2 | zhansan | 85 |

| 3 | lisi | NULL |

| 5 | wangwu | NULL |

| 6 | Lily | NULL |

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

5 rows in set (0.04 sec)

3.3 right join

与left join 相反

mysql> select a.stu_id, a.name, b.score from student as a

-> right join score as b

-> on a.stu_id = b.stu_id;

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

| stu_id | name | score |

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

| 1 | bigberg | 90 |

| 2 | zhansan | 85 |

| 5 | wangwu | 99 |

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

3 rows in set (0.00 sec)

3.4 full join

mysql 并不直接支持full join，但可以通过其他方法解决

mysql> select a.stu_id, a.name, b.score from student as a left join score as b on a.stu_id = b.stu_id

-> union

-> select a.stu_id, a.name, b.score from student as a right join score as b on a.stu_id = b.stu_id;

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

| stu_id | name | score |

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

| 1 | bigberg | 90 |

| 2 | zhansan | 85 |

| 5 | wangwu | 99 |

| 3 | lisi | NULL |

| 6 | Lily | NULL |

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

5 rows in set (0.00 sec)