Jimmy那些事儿

MySQL梳理_结构与关联

发表 | 更新 | 分类 ， ， | 阅读次数

1. 数据库设计

1.1 外键

  • What：表中的某一列；但该列是引用到另一个表的主键列
  • Why ：用于确定一张表中的行与另一张表中的行相对应的值
  • How ：创建外键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
（1）创建带有外键的表
CREATE TABLE intersts(
  int_id INT NOT NULL ATUO_INCREMENT PARIMARY KEY,
  interests VARCHAR(50) NOT NULL,
  cont_id INT NOT NULL,  # 新建的列名
  CONSTRAINT my_contacts_fk  # 约束名_fk为父表中的列
  FOREIGN KEY (cont_id)  # 指定外键
  REFREENCE my_contacts (contact_id)  # 引用外键的 父表名 (列名)
  ON DELETE casecade ON UPDATEA casecade  # 设参数casecade跟随父键改动
);
---
（2）在已有表中建立外键
ALTER TABLE interests
ADD CONSTRAINT 约束名_fk
FOREIGN KEY (外键名)
REFERENCE 父表名 （列名）

1.2 联接表

  • What：储存两个相关表的主键的表
  • How : 用Select来创建
1
2
3
4
CREATE TABLE link AS
  SELECT var FROM table_name
  GROUP BY var
  ORDER BY var;


2. 联接

2.1 内联接

  • What：通过查询的条件，移除某些结果数据行后的交叉联接；

2.1.1 交叉联接

CROSS JOIN

  • What：返回两张表每一行相乘的所有结果
    • 可用逗号（，）来表示
1
2
SELECT * FROM table1 , table2 
  WHERE table1.name = 'xiaoming';

2.1.2 自然联接

NATURAL JOIN

  • What：自然识别出表中相同名称的列，并返回符合记录的值；

2.1.3 相等联接

INNER JOIN…ON…

  • What：利用条件判断中的比较运算符结合两张表，只有符合记录的值才会返回；
1
2
3
SELECT * FROM table1
  INNER JOIN table2 ON table1.id = table2.id
  WHERE table1.name = 'xiaoming';

2.1.4 自联接

  • What：同一张表引用两次
1
2
SELECT c1.name, c2.name AS boss FROM clown AS c1
  INNER JOIN clown AS c2 ON c1.boss_id = c2.id;


2.2 外联接

  • What：返回某张表所有行 + 带有另一张表的条件相符的行
    • 一定会提供数据行，若出现NULL则表示未发现匹配的行
    • 顺序很重要

2.2.1 左外联接

  • What：以左表为基准，右表与之匹配
1
2
3
SELECT g.girl, t.toy FROM girls AS g  # girls为左表，即基准表
  LEFT OUTER JOIN toys AS t  # toys为右表，去匹配左表的值
  ON g.toy_id = t.toy_id;

2.2.2 右外联接

1
2
3
SELECT g.girl, t.toy FROM girls AS g  # girls为左表，去匹配右表的值
  RIGHT OUTER JOIN toys AS t  # toys为右表，即基准表
  ON g.toy_id = t.toy_id;


3. 联合

  • What：将多个行合并都一张表中，并自动清除重复值
  • Why ：可以把从不同表中查询的内容，放到同一个表中
  • How：注意内容
    • 每个select语句中列的数量、列的类型、统计函数必须相同
    • 只接受一个ORDER BY语句，且位于最后
    • 若使用UNION ALL则返回未清除重复值的所有行
1
2
3
4
5
6
SELECT title FROM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings
ORDER BY title;


4. 子查询

4.1 内查询

1
2
SELECT name FROM job_current
  WHERE jc.title NOT IN (SELECT title FROM job_listings);

IN 可以替换为其他符号；=, !=, >, >=

4.2 外查询

  • What：子查询在第一个SELECT语句中，表示某个欲选取的列；
1
SELECT name, (SELECT state FROM zip_code WHERE mc.zip_code = zip_code) FROM my_contact AS mc;


5. 子查询 VS. 联接

  • 联接比子查询更有效率，并且子查询只能返回当前表中的变量
1
2
3
4
5
查询“张旭“教师任课的学生成绩；对应教师表(teacher)，课程表(course)，成绩表(socre)
---
# 子查询
SELECT DEGREE FROM score  #
  WHERE CNO = (SELECT CNO FROM course WHERE TNO = (SELECT TNO FROM teacher WHERE TNAME = '张旭'));

【联接，并可返回不同表中的变量】
SELECT score.degree, teacher.tname FROM score ...

1
2
3
4
5
6
7
8
9
10
# 联接 
SELECT score.DEGREE FROM course  # 自然联接 
  NATURAL JOIN score 
  NATURAL JOIN teacher 
  WHERE teacher.TNAME = '张旭';
  
--
SELECT score.DEGREE FROM score,course,teacher  # 交叉联接
  WHERE course.CNO = score.CNO AND course.TNO = teacher.TNO
  AND teacher.TNAME = '张旭';


  • 当使用统计函数时，子查询更有效率
1
2
3
4
5
查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序
SELECT * FROM score 
  WHERE CNO = '3-105' 
  AND DEGREE > (SELECT MIN(DEGREE) FROM score WHERE CNO = '3-245') 
  ORDER BY DEGREE DESC;

6.#