4、约束和多表查询*

Why约束

　　数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等

What约束

　　约束是表级的强制规定。可以在创建表时规定约束，或者在表创建之后通过 ALTER TABLE 语句规定约束。

Classify约束

• 根据约束数据列的限制，约束可分为：
  • 单列约束：每个约束只约束一列
  • 多列约束：每个约束可约束多列数据
• 根据约束的作用范围，约束可分为：
  • 列级约束：只能作用在一个列上，跟在列的定义后面
  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义

	位置	支持的约束类型	是否可以起约束名
列级约束	类的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其它支持	可以（主键没有效果）

• 根据约束起的作用，约束可分为：
  • NOT NULL 非空约束，规定某个字段不能为空
  • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
  • PRIMARY KEY 主键(非空且唯一)约束
  • FOREIGN KEY 外键约束
  • CHECK 检查约束
  • DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

查看某个表已有的约束

-- information_schema数据库名（系统库）
-- table_constraints表名称（专门存储各个表的约束）
-- SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

select * from information_schema.table_constraints
where table_name = 'emp'; 

非空约束

• 作用：限定某个字段/某列的值不允许为空
• 关键字：NOT NULL
• 特点：一个表可以有很多列都分别限定了非空
  • 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
• 空字符串''不等于NULL，0也不等于NULL

• • 创建表时添加约束 

    /*CREATE TABLE 表名称(    
        字段名 数据类型 NOT NULL
    );*/
    CREATE TABLE student( id int, name varchar(20) NOT NULL);-- name为非空

  • 删除name的非空约束

    -- ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
    ALTER TABLE stuudent MODIFY name varchar(20) NULL;

  • 创建表完后，添加非空约束

    -- ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
    ALTER TABLE student MODIFY name varchar(20) NOT NULL;

唯一约束

• 作用：用来限制某个字段/某列的值不能重复。
• 关键字：UNIQUE
• 特点：
  • 同一个表可以有多个唯一约束。
  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
  • 唯一性约束允许列值为空。
  • 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
  • MySQL会给唯一约束的列上默认创建一个唯一索引。

• • 创建表时，添加唯一约束 

    create table 表名称(
        字段名  数据类型  UNIQUE [KEY]
    );
    create table 表名称(
        字段名  数据类型,
        [CONSTRAINT 约束名] UNIQUE KEY(字段名)
    );

    CREATE TABLE student( 
        id INT, 
        phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
         ); 
        desc student; -- 显示表 
        show index from student; -- 显示所有的索引，唯一约束也是索引之一 

    注意：mysql中，唯一约束限定的列的值可以有多个null

  • 删除唯一约束  

    ALTER TABLE 表名  DROP INDEX 唯一索引名;

    • 添加唯一性约束的列上也会自动创建唯一索引。
    • 删除唯一约束只能通过删除唯一索引的方式删除。
    • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
    • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；
    • 如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
    • 可以通过SHOW INDEX FROM 表名称;查看表的索引
  • 在创建表后，添加唯一约束 

    ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表); ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;

    ALTER TABLE stu2 MODIFY phone_number VARCHAR(20) UNIQUE;

 

主键约束：primary key

 

• • 注意：
    • 非空且唯一，主键约束相当于唯一约束+非空约束的组合
      
    • 一张表只能有一个字段为主键
    • 主键就是表中记录的唯一标识
  • 在创建表时，添加主键约束

    CREATE TABLE  表名称(
        字段名  数据类型  PRIMARY KEY
    );
    CREATE TABLE 表名称(
        字段名  数据类型,
        [CONSTRAINT 约束名] PRIMARY KEY(字段名)
    );

    create table student( 
        id int primary key,-- 给id添加主键约束 
        name varchar(20)
    );

  • 删除主键 

    ALTER TABLE student DROP PRIMARY KEY;

  • 创建完表后，添加主键 

    ALTER TABLE student MODIFY id INT PRIMARY KEY;

  • 自动增长：
    • 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长
    • 在创建表时，添加主键约束，并且完成主键自增长 

      create table stu4(
          id int primary key auto_increment,-- 给id添加主键约束 
          name varchar(20)
      );

 

外键约束：foreign key

• 作用：关键字：FOREIGN KEY
  • 限定某个表的某个字段的引用完整性。
    • 比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。
• 主表和从表/父表和子表
  • 主表（父表）：被引用的表，被参考的表
  • 从表（子表）：引用别人的表，参考别人的表
    • 例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
    • 例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。
• 特点：
  • 从表的外键列，必须引用/参考主表的主键或唯一约束的列
  • 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名
  • 创建表时就指定外键约束的话，先创建主表，再创建从表
  • 删表时，先删从表（或先删除外键约束），再删除主表
  • 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据
  • 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
  • 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
  • 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。
  • 删除外键约束后，必须手动删除对应的索引

• • 在创建表时，可以添加外键 

    create table 表名( 
        .... 外键列 
        constraint 外键名称 foreign key (外键列名称) references 主表名称(主表 列名称) 
    );

  • 删除外键 

    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

  • 创建表之后，添加外键

    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

 

自增列：AUTO_INCREMENT

 

• • 作用：某个字段的值自增
  • 特点：
    • （1）一个表只能有一个自增约束，因为一个表只有一个维护自增值的变量。
    • （2）自增约束的列只能是整数列
    • （3）自增约束的列必须是键列（主键，唯一键，外键），
      　　一般是主键自增最多
  • 建表时指定自增约束

    create table 【数据库名】表名称(
        字段名1 xxInt  primary key auto_increment,
        字段名2 数据类型 【unique key】【not null】 default 默认值,
        字段名3 数据类型 default 默认值，
        ······
    );

多表查询

一、笛卡尔积 （或交叉连接）

　　笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。SQL 92中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN。在 SQL 99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

笛卡尔积的错误会在下面条件下产生

• 省略多个表的连接条件（或关联条件）
• 连接条件（或关联条件）无效
• 所有表中的所有行互相连接

二、关联查询

• 前提条件：
  • 一起查询的表之间是有联系的（一对一、一对多）,有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。
• 等值连接 VS 非等值连接
  • 等值连接：

    SELECT 字段名 FROM 表名1,表名2 WHERE 表名1.字段名1 = 表名2.字段名1;

  • 非等值连接：

    SELECT 字段名 FROM 表名1,表名2 WHERE 非等值连接条件;

• 自连接 VS 非自连接
  • 自连接：

    SELECT 字段名 FROM 表名1 别名1,表名1 别名2 WHERE 连接条件;

  • 非自连接：

    SELECT 字段名 FROM 表名1,表名2 WHERE 连接条件;

• 内连接 VS 外连接　　在表中有相同列时，在列名之前加上表名前缀。
  • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

    -- 隐式内连接：使用where条件消除无用数据
    SELECT 字段名 FROM 表名1,表名2 WHERE 连接条件;
    -- 显示内连接
    SELECT 字段名 FROM 表名1 [INNER] JOIN 表名2 ON 连接条件; 

• • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（右）表中不满足条件的行，这种方式称为左（右）外连接，没有匹配的行时，结果表中相应的列为空（NULL）

    -- 左外连接：连接条件中左边的表称为主表，右边的表称为从表
    SELECT 字段名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 连接条件;      
    -- 右外连接：连接条件中右边的表称为主表，左边的表称为从表
    SELECT 字段名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 连接条件;

• 如果给表起了别名后，一旦在SELECT或WHERE中使用别名的话，则必须使用表的别名，而不能使用表的原名
• 如果有n个表实现多表的查询，则至少需要n-1个连接条件