一、SQL语言介绍

SQL-结构化查询语言(Structured Query Language)

1.常见的SQL语句类型

DML：select、insert、update、delete

2.SQL的重要性

	1. 增加数据库处理效率，减少应用响应时间
 	2. 减少数据库服务器负载，增加服务器稳定性
 	3. 减少服务器间通讯的网络流量

3.join从句

二、Join从句

---

1. 测试数据库

CREATE DATABASE IF NOT EXISTS `join_test` CHARACTER SET = utf8mb4;
USE `join_test`;

2. 测试数据表user1

CREATE TABLE IF NOT EXISTS `user1` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
 `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user1`(`user_name`,`over`) VALUES ('唐僧','旃檀功德佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('猪八戒','净坛使者');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('孙悟空','斗战圣佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('沙僧','金身罗汉');

3. 测试数据表user2

CREATE TABLE IF NOT EXISTS `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user2`(`user_name`,`over`) VALUES ('孙悟空','斗战胜佛');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('牛魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('蛟魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('鹏魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('狮驼王','被降服');

---

1.Join操作的类型–Inner Join内连接

内连接Inner Join基于连接谓词将两张表(tb_A,tb_B)的列组合在一起，产生新的结果表–新的结果集/表是指两张表的公共部分。

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a INNER JOIN `user2` b ON a.`user_name` = b.`user_name`;

等价

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a ,`user2` b WHERE a.`user_name` = b.`user_name`;

2.Join操作的类型-Left Outer Join 左外连接/左连接

• 1.查询的结果包含两张表的字段信息，但是如果不符合的部分，则显示为null-以左表user1为基础

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`;

• user1

SELECT a.`id`,a.`user_name`,a.`over` FROM `user1` a LIMIT 0,4;

• user2

SELECT b.`id`,b.`user_name`,b.`over` FROM `user2` b LIMIT 0,5;

• left join查询结果–查询所有
  
• 2.查询的结果包含两张表的字段信息，但是如果不符合的部分，则不显示-以左表user1为基础

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name` WHERE b.`user_name` IS NOT NULL;

• left join查询结果–去除null
  

3.Join操作的类型–Right Outer Join右外连接/右连接

• 1.查询的结果包含两张表的字段信息，但是如果不符合的部分，则显示为null-以右表user2为基础

SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;

• right outer join查询结果–查询所有
  
• 2.查询的结果包含两张表的字段信息，但是如果不符合的部分，则不显示-以右表user2为基础

SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name` WHERE b.`user_name` IS NOT NULL;

4.Join操作的类型-Full Join 全连接-左外连接和右外连接的合集

MySQL中本身不支持Full Join

• SQL实现： 使用UNION ALL合集来实现Full Join效果

SELECT a.`user_name` 'a_username',a.`over` 'a_over',b.`user_name` 'b_username',b.`over` 'b_over' FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`
UNION ALL 
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;

5.Join操作的类型-Cross Join 交叉连接/笛卡尔集

交叉连接（cross join）,又称笛卡尔集连接（cartesian join）或叉乘（product）；
如果A和B是两个集合，他们的交叉连接记为：A x B；
笛卡尔集中，没有ON从句（没有连接关键词）；

SELECT a.`id`,a.`user_name`,a.`over`,b.`id`,b.`user_name`,b.`over` FROM `user1` a CROSS JOIN `user2` b;

完整SQL【mysql】代码

# CREATE database
CREATE DATABASE IF NOT EXISTS `join_test` CHARACTER SET = utf8mb4;
# SWITCH database
USE `join_test`;
# CREATE Table
CREATE TABLE IF NOT EXISTS `user1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user1`(`user_name`,`over`) VALUES ('唐僧','旃檀功德佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('猪八戒','净坛使者');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('孙悟空','斗战圣佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('沙僧','金身罗汉');
# SELECT all
SELECT a.`id`,a.`user_name`,a.`over` FROM `user1` a LIMIT 0,4;
# CREATE Table
CREATE TABLE IF NOT EXISTS `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user2`(`user_name`,`over`) VALUES ('孙悟空','斗战胜佛');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('牛魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('蛟魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('鹏魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('狮驼王','被降服');
# SELECT all
SELECT b.`id`,b.`user_name`,b.`over` FROM `user2` b LIMIT 0,5;
# Inner Join
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a INNER JOIN `user2` b ON a.`user_name` = b.`user_name`;
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a ,`user2` b WHERE a.`user_name` = b.`user_name`;
# LEFT Outer Join
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`;
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name` WHERE b.`user_name` IS NOT NULL;
# RIGTH Outer Join
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name` WHERE b.`user_name` IS NOT NULL;
# Full Join
SELECT a.`user_name` 'a_username',a.`over` 'a_over',b.`user_name` 'b_username',b.`over` 'b_over' FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`
UNION ALL 
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;
# Cross JOIN
SELECT a.`id`,a.`user_name`,a.`over`,b.`id`,b.`user_name`,b.`over` FROM `user1` a CROSS JOIN `user2` b;