MySQL基础篇2

MySQL基础篇2

---

SQL----多表设计

---

• 为什么需要多表关联？
  消除数据冗余

编号	姓名	民族	年级
1	张三	汉族	一年级
2	李四	汉族	一年级

编号	姓名	民族编号	年级编号
1	张三	1	1
2	李四	1	1

民族编号	民族
1	汉族
2	回族

年级编号	民族
1	一年级
2	二年级

• 什么情况下用到多表？
  eg：教务管理系统
  学生信息，专业，课程…
  学生信息表
  专业信息表
  课程信息表
  在一个比较复杂的系统中，有许多种信息，建议把不同的信息存储在不同的表中，但表与表之间又互相有联系，那么就需要进行表的设计
• 系统的本质是什么？
  对数据的操作
• 如何设计多张表？
  数据库设计范式：建议的数据库设计规则/原则
  目前我们所要掌握前三种数据库设计范式即可（数据库三范式）
• 第一范式
  要求列保持原子性特征（不可拆分）
  eg：

学号	姓名	性别	联系方式
100	tim	男	邮箱/手机号/QQ？想

学号	姓名	性别	手机号	QQ
100	tim	男	15248489482	1739558556

• 第二范式
  表中应该有一个列能够保证唯一（主键），其他的列都依赖于唯一的列，这样就便于查找
• 第三范式
  要求一张表中存储一类信息，然后继续关联即可，消除数据冗余

订单编号	数量	商品编号	商品名称	单价	订单金额
1000	2	2020	手机	2000	4000

订单编号	数量	订单金额	商品编号
1000	2	4000	2020

商品编号	商品名称	单价
2020	手机	2000

• 设计好的表如下

CREATE TABLE student(
    num INT PRIMARY KEY AUTO_INCREMENT,,
    sname VARCHAR(5),
    gender CHAR(1),
    height DOUBLE(3,2) 
)


 CREATE TABLE major(
    id INT PRIMARY KEY AUTO_INCREMENT,
    mname VARCHAR(20)   
   )  
  
  
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(20)   
)  

表中数据自己加

-- 向学生信息表中添加一个专业编号,可以通过专业编号去关联到专业名称等信息
  ALTER TABLE student ADD COLUMN majorid INT

• 外键：引用另外一个数据表的某条记录。
  通过学生表中的专业id,关联到专业，majorid可以成为外键
  但是外键有两种情况，哟中是没有添加任何约束的， 一种是添加外键约束的
  无外键约束关联，表与表之间本身上是没有任何约束关系，五外键约束的关联也称为弱关联。
  无约束外键，删除主表信息（专业）是，没有任何约束。
  为外键添加一个外键约束，这样，两种表之间，就会存在强的约束关系，如果删除主表信息，不能随便删除

 -- 添加外键的约束
   ALTER TABLE student ADD CONSTRAINT majorid_fk FOREIGN KEY(majorid) REFERENCES major(id)

• 为外键添加约束后的条件
  1.当主表没有对应的记录时，不能将记录添加到从表
  2.不能更改主表中的值而导致从表的记录孤立
  3.从表存在与主表对应的记录，不能从主表删除该行
  4.删除主表前，先删除从表
• 各表之间关系
  学生关联专业 ---- 多对一，一对多关系
  一个身份证号，对应一个人---- 一对一关系（外键不重复即可）
  专业和课程关系 学生选修课 ---- 多对多关系
  多对多的关系需要再创建一个关系表，通过关系表来保存多条记录的关系

  CREATE TABLE major_course(
            majorid INT,
            courseid INT       
    )

SQL----多表关联查询

• 多个表之间如何关联查询？
  查询学生信息
  学号,姓名,性别,专业名称(专业名称是在专业表中存放) ，此时需要用到多表关联查询

 SELECT s.num,s.sname,s.gender,m.mname FROM student s,major m

查询结果如下：

此现象称为笛卡尔积现象，是不对的，把表i 的每行与表2进行关联
原因就是两个表关联时没有给关联的条件
所以我们在关联查找时，需要为关联数据给予条件，把条件满足的数据查询出来

• 关联查询，又分为三种关联
  1.内关联（等值连接，非等值连接，子链接）
  2.左外关联
  3.右外关联
• 内关联（等值连接）
  把两张表中满足条件的数据查询出来

  SELECT s.num,s.sname,s.gender,m.mname FROM student s INNER JOIN major m ON s.majorid=m.id

查询结果

• 左外连接
  把左边表中的所以数据查询出来，即使不满足条件

  SELECT s.num,s.sname,s.gender,m.mname FROM student s LEFT JOIN major m ON s.majorid = m.id

查询结果

• 右外连接
  把右边表中的所有数据查询处理，即使不满足条件

  SELECT s.num,s.sname,s.gender,m.mname FROM student s RIGHT JOIN major m ON s.majorid = m.id

查询结果

• 查询学生信息，专业，课程
  因为每个专业有多门课程，所以我们需要再进行分组连接，把同一专业的多门课程进行连接，适用于多对多情况

SELECT
  s.num,
  s.sname,
  s.gender,
  m.mname,
  GROUP_CONCAT(c.cname)  -- 分组连接,把同一个组中的多个进行连接 适用于多对多情况
FROM
  student s  INNER JOIN major m  ON s.majorid = m.id
             INNER JOIN major_course mc ON m.id = mc.majorid
             INNER JOIN course c ON mc.courseid = c.id
  GROUP BY
    s.num,
    s.sname,
    s.gender,
    m.mname 

查询结果

• 非等值连接
  非等值的连接,用于区间范围 积分等级
  案例：身高等级表

-- 创建一个身高等级表
 CREATE TABLE height_level(
        level_name VARCHAR(10),
        level_lower DOUBLE(3,2),
        level_upper DOUBLE(3,2) 
 )

查询语句

-- 非等值的连接,用于区间范围 积分等级 
SELECT
  s.num,
  s.sname,
  s.gender,
  h.level_name
FROM
  student s
  INNER JOIN height_level h ON s.height BETWEEN h.level_lower AND h.level_upper   
   

查询结果

• 自连接
  案例：中国行政单位表 也就是我们平时网购的收货地址 省----市----县/区----街道…

CREATE TABLE  areas(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    pid INT   -- 父级id
 )

 SELECT * FROM areas WHERE pid = 0
 SELECT * FROM areas WHERE pid = 610
 SELECT * FROM areas WHERE pid = 61001

我们只需要建一个表，就可以查询省行政级别下边的市行政级别，市行政级别下边的区/县行政区

 -- 自连接
 SELECT * FROM areas a1 INNER JOIN areas a2 ON a1.pid = a2.id 
                       INNER JOIN areas a3 ON a2.pid = a3.id 
                     WHERE a1.id =6100101

• 子查询
  出现在其他语句中的查询称为内查询/子查询
  一般在mysql中,建议在查询语句中使用子查询
  子查询可以出现在select 语句后面 支持标量子查询(结果是一行一列)
  子查询也可以出现在from 语句后面 支持表子查询(结果是多行多列的)
  子查询也可以出现在WHERE 语句后面 支持标量子查询和列子查询(一列多行)

• 子查询出现在select语句后面

 -- 子查询出现在select语句后面
SELECT
  s.num,
  s.sname,
  s.gender,
 (SELECT m.mname FROM  major m WHERE m.id = s.majorid)mname
FROM
  student s

• 在from 语句后面出现
  表子查询(结果是多行多列的 就像一张表 可以把查询到的数据作为数据源)

-- 查询学生性别大于3个人数的是哪个性别   先来一个查询语句统计出男生和女生各自的人数
  SELECT * FROM (SELECT COUNT(*) c,gender  FROM student   GROUP BY gender)temp
           WHERE temp.c>3

• 在where后面出现子查询

-- 求身高最高的学生   最高身高是多少    标量子查询
 SELECT  * FROM student WHERE height = (SELECT MAX(height) FROM student)

 

                               -- 列子查询
 SELECT  * FROM student WHERE height IN (SELECT height FROM student WHERE height=1.90 OR height=1.80)