数据库高级-----数据完整性与多表查询

数据完整性

      数据库的完整性：
            用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
            确保数据的完整性 = 在创建表时给表中添加约束
      完整性的分类：
            - 实体完整性(行完整性):
                  主键约束：primary key
                  唯一约束：unique [key]
                  自动增长：auto_increment
            - 域完整性(列完整性):
                  非空约束：not null
                  默认约束：default
                  check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)
            - 引用完整性(关联表完整性):
                  外键约束: foreign key
                  constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
                  第二种添加方式：
                        ALTER TABLE 要添加外键列的表名 ADD CONSTRAINT 自定义外键约束名称
                        FOREIGN KEY(外键列) REFERENCES 关联表(主键列);
            建议这些约束应该在创建表的时候设置
            多个约束条件之间使用空格间隔
            外键列的数据类型一定要与主键的类型一致
CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default ‘男’ );
create table score(
id int primary key,
score int,
sid int ,
constraint 自定义外键约束名 foreign key(外键列) references 关联表(主键列) );

多表查询：

      多个表之间是有关系的，那么关系靠谁来维护?
      多表约束：外键列

      多表的关系：

            一对多/多对一关系：
                  一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.
            多对多关系：
                  多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为
                  外键指向各自一 方的主键.
            一对一关系：
                  两种建表原则：
                        唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一
                        的一方的主键，将外键设置为unique.
                  主键对应：让一对一的双方的主键进行建立关系.

      多表查询：

            合并结果集:UNION 、 UNION ALL
                  合并结果集就是把两个select语句的查询结果合并到一起！
                  UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2； l
                  UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT *
                                                                                     FROM t2。
                  注意：被合并的两个结果：列数、列类型必须相同。
            连接查询
                  连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1t2。
                  通过条件过滤去除重复和不想要的数据
                  SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno and…;
            内连接 [INNER] JOIN ON
                  select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 //外键列的关系 where…
                  与上面有同等的效果
                  注: <1>表1和表2的顺序可以互换
                        <2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
                        <3>点操作符表示“的”,格式:表名.列名
                        <4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名
                  三表联查：
                        select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 inner join 表3 on 表
                        1或表2.列名=表3.列名 where
            外连接 OUTER JOIN ON
                  特点：查询出的结果存在不满足条件的可能。
                  -左外连接 LEFT [OUTER] JOIN ON
                        select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
                        – 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成
                                          功的显示null
                        – 2.主表和次表不能随意调换位置
                  - 右外连接 RIGHT [OUTER] JOIN
                              select 列名 from 次表 right join 主表 on 主表.列名=次表.列名
                  - 全外连接（MySQL不支持）FULL JOIN
            自然连接 NATURAL JOIN
                  select * from emp e natural join dept d
                  自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求
                  两个关系表中进行连 接的必须是相同的属性列（名字相同），无须添加连接条件，
                  并且在结果中消除重复的属性列。.下面给 出几个例子。
      子查询:
            一个select语句中包含另一个完整的select语句。
            子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个
            以上SELECT，那么 就是子查询语句了。
                  子查询出现的位置：
                        a. where后，作为条为被查询的一条件的一部分；
                        b. fro m后，作表；
                         当子查询出现在where后作为条件时，还可以使用如下关键字：
                        a. any
                        b. all
                  子查询结果集的形式：
                        a. 单行单列（用于条件）
                        b. 单行多列（用于条件）
                        c. 多行单列（用于条件）
                        d. 多行多列（用于表）

拓展：

      多表新增：
            insert into 表名(列名) values (列值),(列值),(列值)；
      多表更新：
            (1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
            (2)update 表1 inner join 表2 on 表1.列名=表2.列名 set 列名=列值 where 限定条件
      多表删除：
            delete 被删除数据的表 from 删除操作中使用的表 where 限定条件 （连接）
            注:多张表之间使用逗号间隔
      日期运算函数：
            now() 获得当前系统时间
            year(日期值) 获得日期值中的年份
            date_add(日期,interval 计算值 计算的字段);
            注：计算值大于0表示往后推日期，小于0表示往前推日期
      例：
            date_add(now(),interval -40 year);//40年前的日期

数据库优化：

      1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立
            索引
      2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行
            全表扫描
            最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
            备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
      3，应尽量避免在 where 子句中使用 != 或 <> ，否则引擎将放弃使用索引而进行全表扫描。
      4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索
            引，将导致 引擎放弃使用索引而进行全表扫描
      5.in 和 not in 也要慎用，否则会导致全表扫描，
            对于连续的数值，能用 between 就不要用 in 了
            很多时候用 exists 代替 in 是一个好的选择