面试题

经典50题图解：https://zhuanlan.zhihu.com/p/38354000

单表基本查询

登录

-- 做登录验证：查询user表中用户为admin、密码为123456的用户
-- 查得到就有一条结果数据，查不到就是错误密码或用户名不存在
select * from company.`user`
WHERE loginId = 'admin' AND loginPwd = '123123';

翻页数据查询

获取员工数据，按照入职时间降序，并且分页数据为第二页（每页10条）

select * from company.employee
ORDER BY employee.joinDate DESC
LIMIT 10,10

查询-工资最高的女员工

select *,
CASE employee.ismale
WHEN 0 
THEN '女'
END sex
from company.employee
WHERE employee.ismale = 0
ORDER BY employee.salary DESC
LIMIT 0, 1

联表查询

笛卡尔积

解释

获取出来的表，表现形式上是两个表的表头相乘。看下边的例子解释：
表一：

表二：

笛卡尔积结果：观察结果表，可以发现，是company的每一行，乘以user的每一行得到的结果。

连接示意图：
拿出user表的第一行，对应company的每一行。拿出user表的第二行，对应company的每一行。拿出user表的第三行，对应company的每一行。知道user表每一行都拿完，得到最终结果。
感觉像是两层for循环得到的九九乘法表的结果。

三表的笛卡尔积联表查询

再加一个表，查询顺序和结果会是怎样呢？下图，加一个表三（学生表）

执行结果如下：

做题：创建一张表，记录足球队。查询出对阵表。

输出对阵信息：各个队除了自己以外，和其他队都比赛一次
赋值一张表
查询语句：

SELECT  football_team_copy1.name AS 'guest',football_team.name AS 'master' -- 将结果表的列数据修改
FROM `football_team`,football_team_copy1
-- 拿到两个表的笛卡尔积结果
WHERE football_team.name != football_team_copy1.name
-- 过滤掉自己和自己对打的数据

查询结果：

用同一张表做笛卡尔积——通过给表定义别名实现

SELECT  football_team_copy.name AS 'guest',football_team.name AS 'master'
FROM football_team,football_team AS football_team_copy -- 定义别名football_team_copy，然后使用
WHERE football_team.name != football_team_copy.name

左连接，左外连接， left join

以其中一张表为基准，去连接另一张表。每次连接的时候判断条件是否满足。
学习了左连接，就会了右连接。

写法：

SELECT  * FROM department AS d LEFT JOIN employee AS e
ON d.id = e.deptId
-- 连接方法：把department部门表的每一行依次拿出来，然后每次拿当前行与employee表的每一行进行对比，当前行的id与e表中对应行的deptId一致就生成结果表的一行。
-- 像是两个数组的嵌套循环

类似效果：

arr = ['a', 'b', 'c']
arr1 = ['d', 'e', 'f']
for(var i=0; i < arr.length; i++){
    for(var j=0; j < arr1.length; j++){
        return arr[i] = arr1[i]
    }
}

左连接特点：ON条件不成立，导致数据连接失败的时候

以左表为基准，如果不满足条件，左表至少出现一次。

右连接，右外连接，right join

写法同左连接。结果相反

SELECT  * FROM employee AS e RIGHT JOIN department AS d
ON d.id = e.deptId
ORDER BY d.id DESC
-- 反过来写，以右边为基准。如果右表满足不了条件，则右表至少出现一行。（这里右表是department）

内连接，inner join 【常用】

两个表的on条件必须满足，才会生成结果表。不满足的就不会出现

SELECT  * FROM employee AS e INNER JOIN department AS d
ON d.id = e.deptId
ORDER BY d.id DESC

连接多个表

SELECT  * FROM employee AS e INNER JOIN department AS d INNER JOIN company AS c -- 连接了三个表
ON d.id = e.deptId AND c.id < 2 -- 同时卡了两个条件
ORDER BY d.id DESC - 对结果进行排序
-- 上边的写法改造如下：
SELECT  * 
FROM employee AS e 
INNER JOIN department AS d ON d.id = e.deptId
INNER JOIN company AS c ON c.id < 2
ORDER BY d.id DESC

题目

联表查询指定字段，并将外键显示为外键对应主键的name

显示出所有员工的姓名、性别（使用男或女表示）、入职时间、薪水、所属部门（显示部门名称）、所属公司（显示公司名称）

SELECT e.`name`,
CASE e.ismale -- 将性别数据处理，判断条件展示中文中文
    WHEN 1 THEN
        '女'
    ELSE
        '男'
END sex,
c.`name` AS '公司名称', -- 将公司名称列进行处理，起别名展示
d.`name` AS '部门名称',
e.joinDate,
e.salary
FROM employee AS e 
INNER JOIN department AS d ON e.deptId = d.id -- 联部门表，找出当前人的外键deptId与部门表中主键id一致的那条数据
INNER JOIN company AS c ON d.companyId = c.id -- 继续，联公司表，找出当前部门的外键deptId与公司表中主键id一致的那条数据

联表查询并过滤数据

查询腾讯和蚂蚁金服的所有员工姓名、性别、入职时间、部门名、公司名

SELECT e.`name`,
CASE e.ismale
    WHEN 1 THEN
        '女'
    ELSE
        '男'
END sex,
c.`name` AS '公司名称',
d.`name` AS '部门名称',
e.joinDate
FROM employee AS e 
INNER JOIN department AS d ON e.deptId = d.id
INNER JOIN company AS c ON d.companyId = c.id
-- 第一种，模糊匹配的写法
WHERE c.name LIKE '腾讯%' OR c.name LIKE '蚂蚁%' -- 模糊匹配要用like关键字
-- 最后一句，或者用下边IN的写法，需要精确匹配
WHERE c.name IN ('腾讯科技','蚂蚁金服')

联表查询并过滤数据

查询渡一教学部的所有员工姓名、性别、入职时间、部门名、公司名

SELECT e.`name`,
CASE e.ismale
    WHEN 1 THEN
        '女'
    ELSE
        '男'
END sex,
c.`name` AS '公司名称',
d.`name` AS '部门名称',
e.joinDate
FROM employee AS e 
INNER JOIN department AS d ON e.deptId = d.id
INNER JOIN company AS c ON d.companyId = c.id
WHERE c.name LIKE '渡一%' AND d.name = '教学部'

联表查询并去重

列出所有公司员工居住的地址（要去掉重复数据处理）

SELECT DISTINCT e.location FROM employee AS e