mysql约束,表关系,连接查询,子查询

MySQL

一、约束

概述

• 什么是约束？
  为了确保表中数据的完整性（即准确性、正确性），为表添加的一些限制。是数据库中表设计最基本的一个规则。使用约束可以使数据操作更加精准，并且减少冗余数据（脏数据）。

（一）主键约束

• 主键
  • 概述：理论上说每一个数据表都必须有一个唯一主键作为数据的唯一标识，设置主键的列不允许为空。主键习惯用id表示，可以在创建数据表时直接指定，也可以通过修改表结构添加，设置为主键的列在添加数据时就不能重复，即唯一性。一张表只能有一个主键，但是可以通过多个字段设置联合主键。
  • 语法：
    写法1： 制度名 字段类型 primary key；
    写法2： primary key （字段1,字段2，…）;
    
    
• 自动增长：
  由于主键是一个额外的字段，目的是为了确保数据的唯一性，因此通过自动增长可以实现插入数据时，自动添加主键。
  • 语法： 主键字段 字段类型 primary key auto_increment;
    • 实例：
      
  • 自增列AUTO_INCREMENT特点：
    • （1）只有整型数据列才可以设置为自增列
    • （2）只有主键才能设置为自增列
    • （3）自动增长，新增数据时可以不赋值
    • （4）初始值默认为1，增量为1
    • （5）自增列一旦被使用过就不会再出现

（二）唯一约束

• 概述：唯一约束用来限定表中某列数据不允许重复，与主键约束类似，但级别没有主键高。一个表中唯一约束可以创建多个，并且唯一约束的列允许为空。
  唯一约束一般用于约束手机号、账号、邮箱等信息。
• 语法： 字段名 字段类型 unique ;
  • navicate查看或者设置唯一约束：
• 实例：
  

（三）非空约束

• 概述：可以通过not null设置数据表中某一列是必填字段，即不允许为空。
• 语法： 字段名 字段类型 not null ；

（四）默认约束

• 概述：可能通过default设置默认值约束，设置了默认约束的列，如果不给值就会使用默认值来填充。
• 语法： 字段名 字段类型 not null default “默认值”；
  • 实例：
    

（五）外键约束

• 概述：外键约束用来在两个表的数据之间建立关联，MySQL数据库是关系型数据库，即表与表之间是存在关系的，而这种关系我们就称之为外键关系。
• 语法： Foreign key（外键字段） references 参考表（参考字段）

二、表关系

（一）一对一关联

• 一对一的表关系指一张表中的一条数据和另一张表中一条数据一一对应。
  常见一对一表关系有： 丈夫和妻子、用户和身份证，
  对于一对一的表关系，在建立外键时可以用任意一张表中作为主表，另一张表作为从表。
  • 实例：
    

（二）一对多关联

• 一对多表关系指一张表中的一条数据对应另一张表的多条数据。
  常见的一对多的表关系：
  老师和学生、班级和学生、一个国家有多个城市，
  对于一对多的表关系在设置外键时，外键需要添加在多方表中，即主表是一行，从表是多方
  • 实例：
    

（三）多对多关联

• 一张表一条数据对应另一张表的多条数据，反过来也成立，向这样的表关系就称为多对多的表关系常见的多对多的表关系： 学生和课程、商品和订单、员工和部门。
  对于多对多的表关系，在添加外键时，引入第三张表（外键表），将多对多的表关系，变成一对多的表关系。引入的中间表是多方表。原来的表是主表（一方）。
  • 实例：
    
    外键的注意事项：

• 外键参考的表称为主表，
• 设置外键的表称为从表。
• 外键参考的字段必须保证唯一不能重复，通常将主键作为参考字段。
• 从表中外键字段的数据类型必须和主表（参考表）对应参考字段类型匹配。
• 在创建表时，需要先创建主表在创建从表。
• 在插入数据时，需要先插入主表中的数据，从表中插入数据必须是主表中参考字段有的值。
• 在删除数据时，需要先删除从表中的数据，在删除主表中的数据。
• 对于有外键关系的表，通常需要设置外键关系，但是可以不用加外键约束（提高查询性能）

三、连接查询

概述：连接查询就是将多张表中的数据按照指定的连接条件（外键关系）一起查询出来。连接查询分为内连接和外连接。

资料准备：以下面两张表为例讲解连接查询：

班级表：clazz
学生表：student
其中班级表为主表，学生表为从表，二者是一对多关系，通过gid实现两表的关联。

• 班级表：
  
• 学生表：
  

（一）内连接

• 内连接查询是将多张表中的数据按照指定的连接条件获取多张表的交集，去除没有交集的数据。
• 分类：
  • 隐式的内连接： 将两张表通过逗号分隔，使用where来设置连接条件的连接查询。
    语法： select * from A，B， C where 连接条件 ；
    • 实例：
      
    • 1、获取班上密码为125的学生的班级和姓名：
      
  • 显示的内连接： 将两张表通过inner join 关键字进行连接，使用on 设置连接条件的连接查询。
    关键字： inner join … on …
    语法： select * from A [inner] join B on 连接条件 [ 筛选条件] ；
    • 实例：
      
    • 1、获取班上密码为125的学生的班级和姓名：
      

（二）外连接

• 概念： 对于多张表的外连接查询，一其中一张表为主表，另一张表为从表，获取主表中的所有数据以及和从表匹配的数据，如果主表有，从表没有，则从表中数据用null来补齐。
• 分类：
  • 左外连接
    • 左外连接一左表为主表，去匹配右表中的数据，如果左表右，而右表（从表）没有，则右表用null补齐左表数据。
    • 关键字： left join … on …
      语法： select * from A left join B on 连接条件 [筛选条件]
      • 实例：获取所有的班级以及对应的学生信息
        
      • 实例： 获取班上年龄为20的学生的姓名和班级信息
        
  • 右外连接：
    • 右外连接一右表为主表，去匹配左表中的数据，如果右表有，而左表（从表）没有，则左表用null补齐右表数据。
    • 关键字：right join … on …
      语法： select * from A right join B on 连接条件 [筛选条件]
      • 实例：获取所有的班级以及对应的学生信息
        
      • 实例： 获取班上年龄为20的学生的姓名和班级信息
        

四、子查询

概述：当一个查询是另一个查询的条件时，称之为子查询，也叫嵌套查询。

在多表操作中，子查询是一种常见的查询方式。
子查询可以作为查询条件也可以作为一张表，如果作为一张表，必须给给表起别名。

案例：将子查询作为一张表

案例：将子查询作为条件