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)