数据分析师-数据库管理-MySQL_MySQL高级特性.docxVIP

PAGE1

PAGE1

高级查询技术

1子查询的使用与优化

子查询是在一个查询语句中嵌套另一个查询语句，用于获取更复杂的数据。子查询可以出现在SELECT、FROM、WHERE和HAVING子句中。

1.1示例：在WHERE子句中使用子查询

假设我们有一个employees表和一个departments表，我们想要找出所有在部门ID为10的部门工作的员工。

--建立employees表

CREATETABLEemployees(

idINTAUTO_INCREMENTPRIMARYKEY,

nameVARCHAR(100),

department_idINT

);

--插入数据

INSERTINTOemployees(name,department_id)

VALUES(JohnDoe,10),

(JaneSmith,20),

(MikeJohnson,10);

--建立departments表

CREATETABLEdepartments(

idINTAUTO_INCREMENTPRIMARYKEY,

nameVARCHAR(100)

);

--插入数据

INSERTINTOdepartments(name)

VALUES(Sales),

(Engineering);

--使用子查询找出在部门ID为10工作的员工

SELECT*FROMemployees

WHEREdepartment_idIN(SELECTidFROMdepartmentsWHEREid=10);

1.2优化子查询

子查询的性能优化通常涉及减少内部查询的执行次数。使用JOIN代替子查询可以提高查询效率。

--使用JOIN代替子查询

SELECTe.*FROMemployeese

JOINdepartmentsdONe.department_id=d.id

WHEREd.id=10;

2连接查询：内连接与外连接

连接查询用于从两个或多个表中获取数据。内连接返回两个表中匹配的行，而外连接返回一个表中的所有行，即使在另一个表中没有匹配的行。

2.1内连接示例

--使用内连接找出所有员工及其所在部门

SELECTe.name,d.nameASdepartment_name

FROMemployeese

INNERJOINdepartmentsdONe.department_id=d.id;

2.2外连接示例

--使用左外连接找出所有员工及其所在部门，即使部门不存在

SELECTe.name,d.nameASdepartment_name

FROMemployeese

LEFTJOINdepartmentsdONe.department_id=d.id;

3使用UNION和INTERSECT进行数据合并

UNION用于合并两个或多个SELECT语句的结果集，但会去除重复的行。INTERSECT用于找出两个或多个SELECT语句结果集中的公共行。

3.1UNION示例

假设我们有两个表employees和managers，我们想要找出所有员工和经理的名字。

--建立managers表

CREATETABLEmanagers(

idINTAUTO_INCREMENTPRIMARYKEY,

nameVARCHAR(100)

);

--插入数据

INSERTINTOmanagers(name)

VALUES(JohnDoe),

(JaneSmith);

--使用UNION合并员工和经理的名字

SELECTnameFROMemployees

UNION

SELECTnameFROMmanagers;

3.2INTERSECT示例

假设我们想要找出既是员工又是经理的人。

--使用INTERSECT找出既是员工又是经理的人

SELECTnameFROMemployees

INTERSECT

SELECTnameFROMmanagers;

4分组与聚合函数的高级应用

分组和聚合函数用于对数据进行分组和计算，如计算每个部门的平均工资。

4.1示例：计算每个部门的平均工资

--计算每个部门的平均工资

SELECTd.nameASdepartment_name,AVG(e.salary)ASaverage_salary

FROMemployeese

JOINdepart