【一天学会MySQL课程 64集完整阶段二 P54 ~P64 】 _MySQL数据库（学习记录及拓展）

查询练习
43. 现查询所有同学的Sno、Cno和grade列。
select sno, cno, grade from score, grade where degree between low and upp;

54. 查询练习-按等级查询

43. 现查询所有同学的Sno、Cno和grade列。

– 43、假设使用如下命令建立了一个grade表：

mysql> create table grade(
    -> low int(3),
    -> upp int(3),
    -> grade char(1)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into grade values(90, 100, 'A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(80, 89, 'B');
Query OK, 1 row affected (0.00 sec)

mysql> insert into grade values(70, 79, 'C');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(60, 69, 'D');
Query OK, 1 row affected (0.00 sec)

mysql> insert into grade values(0, 59, 'E');
Query OK, 1 row affected (0.00 sec)

mysql> select * from grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+
5 rows in set (0.00 sec)

– 现查询所有同学的sno、cno 和 grade列

mysql> select sno, cno, grade from score, grade where degree  between low  and  upp;
+-----+-------+-------+
| sno | cno   | grade |
+-----+-------+-------+
| 101 | 3-105 | A     |
| 101 | 9-888 | B     |
| 102 | 3-105 | A     |
| 103 | 3-105 | A     |
| 103 | 3-245 | B     |
| 103 | 6-166 | B     |
| 104 | 3-105 | B     |
| 105 | 3-105 | B     |
| 105 | 3-245 | C     |
| 105 | 6-166 | C     |
| 109 | 3-105 | C     |
| 109 | 3-245 | D     |
| 109 | 6-166 | B     |
+-----+-------+-------+
13 rows in set (0.00 sec)

55. 连接查询-内连接、左连接、右连接

SQL 的四种连接查询
$内连接$
inner join 或者 join

内联查询，其实就是两张表中的数据，通过某个字段相等，查询出相关记录数据。

$外连接$

1. 左连接 left join 或者 left outer join
2. 右连接 right join 或 right outer join
3. 完全外连接 full join 或者 full outer join

创建两个表：
– 1、person表

mysql> create table person(
    -> id int,
    -> name varchar(20),
    -> cardId int
    -> );
Query OK, 0 rows affected (0.04 sec)

– 2、card表

mysql> create table card(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

添加数据

mysql> insert into card values(1, '饭卡');
Query OK, 1 row affected (0.02 sec)

mysql> insert into card values(2, '建行卡');
Query OK, 1 row affected (0.00 sec)

mysql> insert into card values(3, '农行卡');
Query OK, 1 row affected (0.00 sec)

mysql> insert into card values(4, '工商卡');
Query OK, 1 row affected (0.00 sec)

mysql> insert into card values(5, '邮政卡');
Query OK, 1 row affected (0.00 sec)

mysql> select * from card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+
5 rows in set (0.00 sec)

mysql> insert into person values(1, '张三', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into person values(2, '李四', 3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into person values(3, '王五', 6);
Query OK, 1 row affected (0.01 sec)

mysql> select * from person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+
3 rows in set (0.00 sec)

/* ------ 没有创建外键！！！ ------ */

1. inner join 查询（内连接）/ join on 查询（内联查询）

mysql> select * from person inner join card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+
2 rows in set (0.00 sec)

join on 查询（内联查询），同 inner join 效果差不多

mysql> select * from person join card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+
2 rows in set (0.00 sec)

2. 【left join、left outer join】左外连接

左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来；
如果没有，就会补NULL。

left join

mysql> select * from person  left join card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡