黑马程序员---自连接---子查询---三范式和ER模型图---外键

分手后的思念是犯贱 2022-11-27 15:45 18阅读 0赞

一：自连接：自连接是内连接的特殊情况，相当于左表和右表都是自己。写的时候必须要给自己起别名。  
1：查询有多少个省：也就是找pid为null的。

mysql> select * from areas where pid is null;
    +--------+--------------------------+------+
    | id     | title                    | pid  |
    +--------+--------------------------+------+
    | 110000 | 北京市                   | NULL |
    | 120000 | 天津市                   | NULL |
    | 130000 | 河北省                   | NULL |
    | 140000 | 山西省                   | NULL |
    | 150000 | 内蒙古自治区             | NULL |
    | 210000 | 辽宁省                   | NULL |
    | 220000 | 吉林省                   | NULL |
    | 230000 | 黑龙江省                 | NULL |
    | 310000 | 上海市                   | NULL |
    | 320000 | 江苏省                   | NULL |
    | 330000 | 浙江省                   | NULL |
    | 340000 | 安徽省                   | NULL |
    | 341402 | 居巢区                   | NULL |
    | 350000 | 福建省                   | NULL |
    | 360000 | 江西省                   | NULL |
    | 370000 | 山东省                   | NULL |
    | 410000 | 河南省                   | NULL |
    | 420000 | 湖北省                   | NULL |
    | 430000 | 湖南省                   | NULL |
    | 440000 | 广东省                   | NULL |
    | 450000 | 广西壮族自治区           | NULL |
    | 460000 | 海南省                   | NULL |
    | 500000 | 重庆市                   | NULL |
    | 510000 | 四川省                   | NULL |
    | 520000 | 贵州省                   | NULL |
    | 530000 | 云南省                   | NULL |
    | 540000 | 西藏自治区               | NULL |
    | 610000 | 陕西省                   | NULL |
    | 620000 | 甘肃省                   | NULL |
    | 630000 | 青海省                   | NULL |
    | 640000 | 宁夏回族自治区           | NULL |
    | 650000 | 新疆维吾尔自治区         | NULL |
    | 990000 | 新疆建设兵团             | NULL |
    +--------+--------------------------+------+
    33 rows in set (0.00 sec

2：查询广东省下面所有的市。

mysql> select * from areas as c inner join areas as p on c.pid=p.id where p.title = "广东省";
    +--------+-----------+--------+--------+-----------+------+
    | id     | title     | pid    | id     | title     | pid  |
    +--------+-----------+--------+--------+-----------+------+
    | 440100 | 广州市    | 440000 | 440000 | 广东省    | NULL |
    | 440200 | 韶关市    | 440000 | 440000 | 广东省    | NULL |
    | 440300 | 深圳市    | 440000 | 440000 | 广东省    | NULL |
    | 440400 | 珠海市    | 440000 | 440000 | 广东省    | NULL |
    | 440500 | 汕头市    | 440000 | 440000 | 广东省    | NULL |
    | 440600 | 佛山市    | 440000 | 440000 | 广东省    | NULL |
    | 440700 | 江门市    | 440000 | 440000 | 广东省    | NULL |
    | 440800 | 湛江市    | 440000 | 440000 | 广东省    | NULL |
    | 440900 | 茂名市    | 440000 | 440000 | 广东省    | NULL |
    | 441200 | 肇庆市    | 440000 | 440000 | 广东省    | NULL |
    | 441300 | 惠州市    | 440000 | 440000 | 广东省    | NULL |
    | 441400 | 梅州市    | 440000 | 440000 | 广东省    | NULL |
    | 441500 | 汕尾市    | 440000 | 440000 | 广东省    | NULL |
    | 441600 | 河源市    | 440000 | 440000 | 广东省    | NULL |
    | 441700 | 阳江市    | 440000 | 440000 | 广东省    | NULL |
    | 441800 | 清远市    | 440000 | 440000 | 广东省    | NULL |
    | 441900 | 东莞市    | 440000 | 440000 | 广东省    | NULL |
    | 442000 | 中山市    | 440000 | 440000 | 广东省    | NULL |
    | 445100 | 潮州市    | 440000 | 440000 | 广东省    | NULL |
    | 445200 | 揭阳市    | 440000 | 440000 | 广东省    | NULL |
    | 445300 | 云浮市    | 440000 | 440000 | 广东省    | NULL |
    +--------+-----------+--------+--------+-----------+------+
    21 rows in set (0.01 sec)

3：查询深圳市下面所有的区；

二：子查询：  
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.

主查询和子查询的关系:

子查询是嵌入到主查询中
    子查询是辅助主查询的,要么充当条件,要么充当数据源
    子查询是可以独立存在的语句,是一条完整的 select 语句

1：查询高于学生平均身高的学生信息。  
分析：学生平均身高可以用一个查询语句。这个语句作为查询学生信息的子语句。

mysql> mysql> select * from students where height > (select avg(height) from students);
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |
    |  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
    |  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
    | 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
    | 14 | 周杰      |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小    |   28 | 180.00 | 女     |      1 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    10 rows in set (0.00 sec)

2：查询学生在班的所有班级名称。  
分析：查询出学生选的所有班级，再看所有班级在不在学生选的班级里面。

mysql> select id,name from classes where id in(select cls_id from students);
    +----+--------------+
    | id | name         |
    +----+--------------+
    |  1 | python_01期  |
    |  2 | python_02期  |
    +----+--------------+
    2 rows in set (0.00 sec)

3：查询学生的班级和班级号，所对应的学生信息。（查询有班可上的学生信息）  
分析：先查出所有的班级号，然后作为条件查询里面的学生。

mysql> select * from students where cls_id in(select id from classes);
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    12 rows in set (0.00 sec)

4：查询年龄最大，身高最高的学生。  
分析：用一个查询先查询出最大的年龄和最大的身高，再查询所有信息里面有没有查询出的两个最大值的数据。

mysql> select * from students where (age,height)= (select max(age),max(height) from students);
    Empty set (0.00 sec)

查询到里面没有这个数据。

mysql> select * from students;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | height | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          |
    | 12 | 静香         |   12 | 180.00 | 女     |      4 |           |
    | 13 | 郭靖         |   12 | 170.00 | 男     |      4 |           |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +----+--------------+------+--------+--------+--------+-----------+
    16 rows in set (0.00 sec)

现在将年龄最大的刘德华更改成190岁，再次查询.

mysql> update students set height = 190 where name = "刘德华";
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from students where (age,height)= (select max(age),max(height) from students);
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | height | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  4 | 刘德华    |   59 | 190.00 | 男     |      2 |          |
    +----+-----------+------+--------+--------+--------+-----------+
    1 row in set (0.00 sec)

三：三范式：

1：第一范式：列的原子性---列不可再分。
    2：第二范式：满足第一范式，非主键必须完全依赖于主键，不能部分依赖。
    3：第三范式：满足第二范式，非主键必须直接依赖于主键，不能传递依赖。

四：ER模型图：实体关系模型图：描述数据库存储数据的结构模型。

实体: 用矩形表示，并标注实体名称
    属性: 用椭圆表示，并标注属性名称，
    关系: 用菱形表示，并标注关系名称
    一对一
    一对多
    多对多

注意：对于外键的设置：

一对一：外键可以设置在任何地方。
    一对多：外键需要设置在多的地方。
    多对多：外键需要存储在第三张表，第三章表同时具有那两张表的主键。

五：外键：  
1：区分外键和外键约束：  
外键指的是一个字段，真正起作用的是外键约束。  
2：外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性。  
3. 对于已经存在的字段添加外键约束

-- 为cls_id字段添加外键约束
    alter table students add foreign key(cls_id) references classes(id);

1.  在创建数据表时设置外键约束

-- 创建学校表
    create table school(
        id int not null primary key auto_increment, 
        name varchar(10)
    );
    
    -- 创建老师表
    create table teacher(
        id int not null primary key auto_increment, 
        name varchar(10), 
        s_id int not null, 
        foreign key(s_id) references school(id)
    );

1.  删除外键约束  
    – 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称

show create table teacher;
    
    -- 获取名称之后就可以根据名称来删除外键约束
    alter table teacher drop foreign key 外键名;