mysql 基础（三）

一、多表设计

数据库设计范式

1. 第一范式(确保每列保持原子性)
   第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
2. 第二范式就是要有主键，要求其他字段都依赖于主键。
   • 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
   • 其他字段为什么要依赖于主键？
     因为不依赖于主键，就找不到他们。更重要的是，其 他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依 赖于主键，也就成了唯一的
3. 第三范式,确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含 已在其它表中包含的非主关键字信息

外键 

外键：引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键 （foreign key）建立起来的

约束名改则（约定俗成）：

注： 

• 当主表中没有对应的记录时，不能将记录添加到从表。
• 不能更改主表中的值而导致从表中的记录孤立。
• 从表存在与主表对应的记录，不能从主表中删除该行。
• 删除主表前，先删从表

代码示例如下：

/*
   数据库概念
   mysql数据库
   安装
   
   sql
     ddl
      创建数据库
      创建表 
     dml
       insert into 
       update 
       delete
     dql  
      select 结果 from 表 where 条件  [group by 条件  order by 列  limit ]
      
      函数 单行函数  字符 逻辑  日期 数学函数
           分组函数
           
           
   数据库设计范式(数据库设计规则) 如何将多个信息合理的进行组织]
   
   设计3范式
    第一范式: 满足列的原子性(不可拆分)
                联系范式-->邮箱,电话,qq 可以拆分
                
    第二范式: 数据库表中必须要有主键,能唯一的表示某条记录的列(有些表中可以没有主=键)
    
    第三范式: 一张表中存储一类信息, 在一张表中关联其他表中的数据时,只需要关联主键列即可.
    
    
    专业信息表
       专业编号(主键)
       专业名称
     
     
    学生与专业是有关系的,如何建立学生和专业表的关系.
           
*/
   DROP TABLE stu
   
   CREATE  TABLE major(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20)
   )

-- 修改表  为表添加一个列  一对多 多对一
   ALTER TABLE student ADD majorid INT

-- 外键  在一个表中外键是用来与另一个表的主键关联的
-- 主键 不能为空  唯一 检查 
-- 外键有两种情况:
-- 1.不加外键约束
--     可以任意的对表数据进行操作,即使两个表中的数据对应不上也没有问题 

-- 2.添加外键约束
--  添加外键约束后,两张表操作时,不能导致外键列和主键列对应关系不成立
  ALTER TABLE student  ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid) REFERENCES major(id)

 -- 删除外键约束
 ALTER TABLE student DROP FOREIGN KEY fk_student_major_majorid
  
  -- 学生选课   一个学生至少选择2个课程
  -- 课程信息表  多对对关系设计
  
  CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)  
  )
  
  
  -- 添加一个学生选课表   学生和课程关系表   放一个学生学号外键, 放一个课程外键
  CREATE TABLE student_course(
     stunumber INT,
     courseid INT
  )

ALTER TABLE student_course  ADD CONSTRAINT fk_student_course_student_stunumber FOREIGN KEY(stunumber) REFERENCES student(number)
ALTER TABLE student_course  ADD CONSTRAINT fk_student_course_course_courseid FOREIGN KEY(courseid) REFERENCES course(id)

 二、关联查询

内连接(inner join)

• 把满足了条件的两张表中的交集数据查询出来
• 语法:
  

左外关联(left join) 

• 把左表的全部元素和满足左表中的右表元素查询出来
• 语法：
  

右外关联(right join) 

• 把右表的全部元素和满足右表中的右表元素查询出来
• 语法：
  

子查询 

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询。 

分类：

• 按子查询出现的位置：
  • from后面：支持表子查询
  • where：支持标量子查询，列子查询
• 按功能、结果集的行列数不同：
  • 标量子查询（结果集只有一行一列）
  • 列子查询（结果集只有一列多行）
  • 表子查询（结果集一般为多行多列

代码示例如下：

-- 关联查询  多表关联在一起查询
-- 学号 姓名 性别  电话 专业名称   -- 信息来自于两张表中

 -- 关联时没有任何的限制,会产生笛卡尔乘积现象 ,这是我们不想要的结果
 SELECT * FROM student,major

-- 关联查询  内关联  左外关联  右外关联

-- select number,name,gender,phone,name from student inner join major on majorid = id

 SELECT 
   s.number,s.name,s.gender,s.phone,m.name  mname
 FROM student s 
 INNER JOIN major m ON s.majorid = m.id   -- 把满足条件关联在一起
 
 SELECT 
   s.number,s.name,s.gender,s.phone,m.name  mname 
 FROM student s,major m 
 WHERE s.majorid = m.id   -- 先产生一个笛卡尔乘积  然后在条件筛选
 
 
 
 -- 左外连接   特点  即使不满足连接条件,也会把左边表中的所有数据查询出来
 
 SELECT 
   s.number,
   s.name,
   s.gender,
   s.phone,
   m.name  mname
 FROM 
   student s LEFT JOIN major m ON s.majorid = m.id
 
 
 -- 右外连接  即使不满足连接条, 也要把右边表中的所有数据查询出来
 SELECT 
   s.number,
   s.name,
   s.gender,
   s.phone,
   m.name  mname
 FROM 
   student s RIGHT JOIN major m ON s.majorid = m.id
  
 -- 统计每个专业下有多少学生
 SELECT 
   COUNT(number),
   m.name  mname
 FROM 
   student s RIGHT JOIN major m ON s.majorid = m.id
  GROUP BY m.name