数据库关联查询的几种方式
建表语句
-- MySQL dump 10.13 Distrib 5.5.40, for Win64 (x86)
--
-- Host: 192.168.40.128 Database: study01
-- ------------------------------------------------------
-- Server version 5.6.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `class`
--
DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
`cla_id` bigint(32) NOT NULL COMMENT '班级主键',
`cla_name` varchar(256) NOT NULL COMMENT '班级名称',
PRIMARY KEY (`cla_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `class`
--
LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'六年级1班'),(2,'三年级3班'),(3,'一年级2班'),(4,'五年级3班');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `course`
--
DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`c_id` bigint(32) NOT NULL COMMENT '主键',
`c_name` varchar(265) NOT NULL COMMENT '课程名字',
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `course`
--
LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'数学'),(2,'语文'),(3,'英语'),(4,'物理'),(5,'化学'),(6,'生物');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `customer`
--
DROP TABLE IF EXISTS `customer`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `customer` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID\r\n',
`name` varchar(100) DEFAULT NULL COMMENT '姓名',
`age` bigint(20) DEFAULT NULL COMMENT '年龄',
`salary` double DEFAULT NULL COMMENT '薪水',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工薪资表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `customer`
--
LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
INSERT INTO `customer` VALUES (1,'jack',23,7198.8),(2,'tom',18,3999);
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `emp_role`
--
DROP TABLE IF EXISTS `emp_role`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp_role` (
`empid` int(11) DEFAULT NULL,
`roleid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `emp_role`
--
LOCK TABLES `emp_role` WRITE;
/*!40000 ALTER TABLE `emp_role` DISABLE KEYS */;
INSERT INTO `emp_role` VALUES (1,1),(1,2),(2,1),(2,2),(3,1),(3,2),(4,1),(4,2),(5,1),(5,2),(6,1),(6,2);
/*!40000 ALTER TABLE `emp_role` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `employee`
--
DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`staff_id` varchar(100) DEFAULT NULL COMMENT '员工ID',
`boss_id` varchar(100) DEFAULT NULL COMMENT '上级ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `employee`
--
LOCK TABLES `employee` WRITE;
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `employee` VALUES (1,'1','2'),(2,'2','3'),(3,'3','4'),(4,'4','5'),(5,'5',NULL),(6,'6','7'),(7,'2','5');
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `employees`
--
DROP TABLE IF EXISTS `employees`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employees` (
`empid` int(11) NOT NULL,
`empname` varchar(20) NOT NULL,
PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `employees`
--
LOCK TABLES `employees` WRITE;
/*!40000 ALTER TABLE `employees` DISABLE KEYS */;
INSERT INTO `employees` VALUES (1,'Jacky'),(2,'Wendy'),(3,'David'),(4,'Lucy'),(5,'Tom'),(6,'Ann');
/*!40000 ALTER TABLE `employees` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `numtable`
--
DROP TABLE IF EXISTS `numtable`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `numtable` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`num` bigint(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `numtable`
--
LOCK TABLES `numtable` WRITE;
/*!40000 ALTER TABLE `numtable` DISABLE KEYS */;
INSERT INTO `numtable` VALUES (1,23),(2,22),(3,18),(4,17),(5,25);
/*!40000 ALTER TABLE `numtable` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `roles`
--
DROP TABLE IF EXISTS `roles`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `roles` (
`roleid` int(11) NOT NULL,
`description` varchar(50) NOT NULL,
PRIMARY KEY (`roleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `roles`
--
LOCK TABLES `roles` WRITE;
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
INSERT INTO `roles` VALUES (1,'Create Training'),(2,'Training'),(3,'Manage Employee'),(4,'Manage Deparement');
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `student`
--
DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`stu_id` bigint(32) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`stu_username` varchar(25) DEFAULT NULL COMMENT '学生姓名',
`stu_password` varchar(25) DEFAULT NULL COMMENT '学生账号',
`c_id` bigint(32) DEFAULT NULL COMMENT '课程ID',
`cla_id` bigint(32) DEFAULT NULL COMMENT '班级ID',
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `student`
--
LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'张三','123',1,1),(2,'李四','234',2,2),(3,'王五','258',2,1),(4,'赵六','5689',3,1),(5,'孙七','56896',NULL,3),(7,'悟空','45899',4,2),(8,'李四','234',2,2),(9,'王五','258',2,1);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping routines for database 'study01'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-11-29 22:06:27
1 内连接查询
SELECT t.`stu_username`, c.`c_name` FROM student t
INNER JOIN course c ON t.`c_id` = c.`c_id`;
# 查询结果
stu_username c_name
"张三" "数学"
"李四" "语文"
"王五" "语文"
"李四" "语文"
"王五" "语文"
"赵六" "英语"
"悟空" "物理"
结果可发现inner内连接查询的特点就是 : 只查询在连接的表中能够对应的数据,
也就是只查询对应关系存在的数据(双方对应关系都有值)
2 左外连接查询
SELECT stu.`stu_username`, c.`c_name` FROM student stu
LEFT JOIN course c ON stu.`c_id` = c.`c_id`;
# 查询结果
stu_username c_name
"张三" "数学"
"李四" "语文"
"王五" "语文"
"李四" "语文"
"王五" "语文"
"赵六" "英语"
"悟空" "物理"
"孙七" "[NULL]"
根据左边的表为主表去查询,来匹配右边表的数据,如果左边表有数据,右边表没有数据,查询的结果就是null
3 右外链接
SELECT s.stu_username, c.c_name FROM student s
RIGHT JOIN course c ON s.c_id = c.c_id;
# 查询结果
stu_username c_name
"张三" "数学"
"李四" "语文"
"王五" "语文"
"赵六" "英语"
"悟空" "物理"
"李四" "语文"
"王五" "语文"
"[NULL]" "化学"
"[NULL]" "生物"
以右边表为主表去匹配左边表的数据信息)也就是查询右边表的全部左边表没数据就默认为null
4 全外连接查询
SELECT s.stu_username, c.c_name FROM student s
LEFT JOIN course c ON s.c_id = c.c_id
UNION
SELECT stu.stu_username, cou.c_name FROM student stu
RIGHT JOIN course cou ON stu.c_id = cou.c_id
## 查询结果
stu_username c_name
"张三" "数学"
"李四" "语文"
"王五" "语文"
"赵六" "英语"
"悟空" "物理"
"孙七" "[NULL]"
"[NULL]" "化学"
"[NULL]" "生物"
全外连接查询在mysql中没有full out join on语句 但是可以使用union关键字进行查询(union关键字是用于想要将多张表的全部数据都查询出来进行进行的数据查询
5 自关联查询
SELECT e.staff_id sname, ee.staff_id bname FROM employee e
LEFT JOIN employee ee ON e.boss_id = ee.staff_id;
##
sname bname
"1" "2"
"2" "3"
"3" "4"
"4" "5"
"2" "5"
"1" "2"
"5" "[NULL]"
"6" "[NULL]"
就是自身关联自身的关联查询,关键就是虚拟一张表起一个不一样的别名(一般有自关联的表会用到自关联查询,比如员工表,有员工有领导,领导也是员工)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
