-- 1.多表查询
-- 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUES ('表演部'),('策划部'),('鸡脚部');
-- 创建员工表
CREATE TABLE emp01(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1),
salary DOUBLE,
join_date DATE,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id)-- 外键关联部门表的主键
);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('蔡徐坤','男',10000,'2020-10-3',3);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('蔡旭','男',8000,'2002-1-1',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('坤哥','男',6000,'2010-1-30',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('鸡坤','男',12000,'2016-4-3',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('鸡哥','男',8000,'2010-12-3',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('只因哥','男',9000,'2008-5-3',1);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('太美哥','男',22000,'2022-4-3',1);
-- 查询
SELECT *FROM emp01;
SELECT *FROM dept;
-- 如果这样查询,出现的是笛卡尔积形式
SELECT *FROM emp01,dept;
-- 多表查询消除无用数据
-- 1.内连接查询
-- 1).隐式内连接:使用where消除无用句子;
SELECT *FROM emp01,dept WHERE emp01.dept_id = dept.id;
-- 查询员工表的名称,性别,部门表的名称
SELECT emp01.name,emp01.gender,dept.name FROM emp01,dept WHERE emp01.dept_id = dept.id;
-- 简化一下
SELECT t1.`name`,t1.`gender`,t2.name FROM emp01 t1 ,dept t2 WHERE t1.`dept_id` = t2.id;
-- 2).显示内连接
SELECT *FROM emp01 JOIN dept ON emp01.dept_id = dept_id;
-- 2.外连接查询
-- 1).左外连接(查询左表所有数据以及交集部分)
-- select 字段列表 from 表1 left join 表2 on 条件;
SELECT t1.*,t2.name FROM emp01 t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.id;
-- 2).右外连接(右表所有记录以及交集部分)
-- select 字段列表 from 表1 right join 表2 on 条件;
SELECT t1.*,t2.name FROM emp01 t1 RIGHT JOIN dept t2 ON t1.`dept_id` = t2.id;
-- 3.子查询(查询中嵌套查询,嵌套的查询为子查询)
-- 查询最高的工资员工信息(先查询最高工资多少在查询信息)
SELECT MAX(salary) FROM emp01;
SELECT * FROM emp01 WHERE emp01.salary = 22000;
-- 简化
SELECT * FROM emp01 WHERE emp01.salary = (SELECT MAX(salary) FROM emp01);
-- 子查询作为条件去判断
SELECT * FROM emp01 WHERE emp01.`salary` < (SELECT AVG(salary) FROM emp01);
-- 查询表演部的所有员工信息
SELECT id FROM dept WHERE NAME = '表演部' OR NAME = '鸡脚部';
SELECT * FROM emp01 WHERE dept_id = 3 OR dept_id= 2 ;
-- 简化
SELECT *FROM emp01 WHERE dept_id IN (3,2);
-- 查询入职日期是2011-11-11之后的员工和部门信息
SELECT *FROM emp01 WHERE emp01.join_date > '2011-11-11';
-- 子查询(多行多列)作为虚拟表进行查询
SELECT *FROM dept t1 ,(SELECT *FROM emp01 WHERE emp01.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
)
mysql多表查询(内连接,外连接,子查询)
最新推荐文章于 2024-07-30 03:45:20 发布