MySQL 系统学习系列 - SQL 语句 DQL 语句的使用（3）《MySQL系列篇-05》

SQL 语句 DQL

多表连接查询

连接与多表查询：连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据

基本简介与表之间的搭建（用于使用多表查询语句-即准备工作）

• 

• 单词普及(名称)	单词
  连接	join
  内	inner
  外	left/right
  基本语法[内连接示例]	inner join on ...

• # 表与数据的准备
    CREATE TABLE IF NOT EXISTS t1( -- 左表
        id INT NOT NULL,
        name VARCHAR(15)
    );
    CREATE TABLE IF NOT EXISTS t2( -- 右表
        id INT NOT NULL,
        name VARCHAR(15)
    );
  
    # 插入数据
      INSERT INTO t1 VALUES(1,'t1a');
      INSERT INTO t1 VALUES(2,'t1b');
      INSERT INTO t1 VALUES(3,'t1c');
      INSERT INTO t1 VALUES(4,'t1d');
      INSERT INTO t1 VALUES(5,'t1e');
      INSERT INTO t1 VALUES(6,'t1f');
  
      INSERT INTO t2 VALUES(1,'t2b');
      INSERT INTO t2 VALUES(2,'t2c');
      INSERT INTO t2 VALUES(3,'t2d');
      INSERT INTO t2 VALUES(4,'t2e');
      INSERT INTO t2 VALUES(5,'t2f');
  

笛卡尔积与内连接：可以理解没有LEFT|RIGHT的JOIN就为内连接，具体语法如下面的外连接处

• # 笛卡尔积 [每一个连接都会当做一行]
    -- 笛卡尔积:当有一张表中有3行数据，一张有5行数据，则连接后的数据则有 3*5 数据
      SELECT * FROM t1,t2; -- 多表查询连接，会有30条数据
      SELECT * FROM t1 join t2; -- 直接使用连接join：该多表连接，等价与上面 [同样属于笛卡尔积]
    
  # 内连接 [相当于求两表的交集]
      SELECT * FROM t1 JOIN t2 ON t1.id=t2.id; -- 此时该两张表，就可以根据id相等的进行查询连接，只有5条数据 
      SELECT t1.id,t1.name,t2.name FROM t1 JOIN t2 ON t1.id=t2.id; -- 因为id的值是相同的，所以也可以将id过滤掉一个
  

外连接：SELECT * FROM 表1 LEFT/RIGHT JOIN 表2 ON 连接条件[基准]

• # 左外连接 [相当于取两圈的左圈]
      SELECT * FROM t1 LEFT JOIN t2 on t1.id=t2.id; -- 左表全部保留，右表关联不上的用null表示 [而且只会查询到左表列的最大值]
      SELECT * FROM t1 LEFT OUTER JOIN t2 on t1.id=t2.id; -- 加个OUTER同理，下面同理
    
  # 右外连接 [相当于取两圈的右圈]
      SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id; -- 右表全部保留，左表关联不上的用null表示
    
  # 取左表/右表的独有数据 [直接判断右表或左表为null即可   
  	SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; -- 取左表独有的数据 [ 即判断处右表是空的 ]
  	SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE t1.id IS NULL; -- 取右表独有的数据 [ 即判断处左表是空的 ]
  

全连接&并集去交集

• # 全连接（mysql不支持全连接，但可以通过union变相实现全连接；oracle支持全连接）
    -- union 可以将两张表的数据进行连接，并且会自动去重
    -- 所以我们就可以通过获取左/右连接的数据，再进行union进行两表的连接
      SELECT * FROM t1 LEFT JOIN t2 on t1.id=t2.id -- 左表的数据
        union -- 可以进行多表的数据连接，并会自动进行去重操作
      SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id; -- 右表的数据
    /**
      除了union外，还有一个union all语句与union一样可以进行多表的数据的谅解
          不同的是union会对连接的数据进行去重，而union all不会自动进行去重
          union all 的效率要比 union 高很多
     */
  

• # 并集去交集 [扩]
    -- 可以直接使用union语句，来对左表与右表独有的数据进行连接即可
  	SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL -- 左表独有的数据
  	  union -- 连接两表的数据
  	SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE t1.id IS NULL; -- 右表独有的数据
  

• **union连接两个及两个以上的SELECT的结果字符集**
  
  <!-- ## DQL（3） -->
  
  ## 子查询
  
  - 子查询是SQL嵌套查询的程序模块。当一个查询是另一个查询的条件时，称之为子查询。
  - 语法：比较运算符{ALL|SOME|ANY} （子查询运算符）
  - ```sql
      SELECT * FROM 表名 WHERE 比较列名 = ( -- 括号中的为子查询语句
          SELECT 列名s FROM 表名 .....
       );
  
      # 如
          SELECT * FROM stu WHERE age = (
  			SELECT DISTINCT age FROM stu ORDER BY age DESC LIMIT 2,1
  		 );
  
      -- .....................................................
  

子查询与多表连接练习

• 表结构与数据的准备

  • # 表结构定义
        CREATE TABLE IF NOT EXISTS grade ( # 定义一个班级表
            g_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            g_name VARCHAR(25) NOT NULL
        );
      # 因为一个班级可有多名学生，而一名学生只能有一个班级（所以班级表与学生表是一个一对多的关系，即需要再学生表中加上班级表的主键作为外键）
        CREATE TABLE IF NOT EXISTS students ( # 定义一个学生表
            id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(11) NOT NULL,
            grade_id int, # 定义外键列               
            CONSTRAINT fk_students_grade FOREIGN KEY(grade_id) REFERENCES grade(g_id) # 定义外键
        );
    
    # 数据插入
        INSERT INTO grade VALUES(0,'JAVA班'),(0,'PYTHON班'),(0,'JS班'),(0,'HTML班'); # 插入班级信息
        INSERT INTO students VALUES(0,"li",4),(0,"hello",4),(0,"wang",4),(0,"kong",1),(0,"deng",3),(0,"2kong",2); # 插入学生信息
    

• 练习

  • # 1.查询Java班的所有学生信息
        SELECT g_id FROM grade WHERE g_name='JAVA班'; -- 查询JAVA班的id，再通过该id去学生表中查询所有的学生信息 【该语句就为下面语句的子查询】
        SELECT * FROM students WHERE grade_id in (SELECT g_id FROM grade WHERE g_name='JAVA班'); -- 根据查询到的班级id，来查询对应的学生信息 
        
    # 2.查询所有学生信息中名字长度最长的学生
        SELECT * FROM students WHERE id in (SELECT MAX(LENGTH(name)) FROM students); -- 获取所有名字的最大长度值
        SELECT * FROM students HAVING LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM students); -- 根据最大长度值，获取对应的学生信息
        
    # 3.查询所有学生信息中名字长度小于5的学生
        SELECT * FROM students HAVING LENGTH(name)<5;
        
    # 4.查询名字长度大于5的学生，来自哪一个班级
        SELECT s.id,s.name,g.g_name FROM students s LEFT OUTER JOIN grade g ON s.grade_id=g.g_id HAVING LENGTH(s.name)>5;
        
    # 5.查询JAVA与PYTHON班的所有学生姓名
        SELECT id FROM grade WHERE g_name='JAVA班' or g_name='PYTHON班'; -- 查询JAVA与PYTHON班的班级id
        SELECT * FROM students WHERE grade_id in (SELECT g_id FROM grade WHERE g_name='JAVA班' or g_name='PYTHON班');
      
    # 6.查询名字长度小于5的学生，包括班级信息
        SELECT * FROM students s LEFT OUTER JOIN grade g ON g.g_id=s.grade_id HAVING LENGTH(s.name)<5;