D34

上周练车去了，终于考到驾照了，太累了。旷了三天，本来上周应该把MySQL基础看完，看来要这周看了，加油OVO，下周再搞其他的.

Day34

#今日练习，练习之前学的语法。
#1.查询员工姓名，入职日期并按入职日期升序
​
SELECT last_name,hiredate
FROM employees
ORDER BY hiredate ASC
​
#2.将当前日期显示成xxxx年xx月xx日
SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日') AS 日期
FROM employees
​
#3查询所有男生的姓名、专业名和成绩，使用SQL92和SQL99两种方式实现
##sql99
SELECT studentname,majorname,score
FROM student s JOIN result r JOIN major m
ON s.majorid = m.`majorid` AND s.`studentno`=r.`studentno`
WHERE sex = '男'
​
#sql02
SELECT studentname,majorname,score
FROM student s , result r , major m
WHERE s.majorid = m.`majorid` AND s.`studentno`=r.`studentno`
AND sex = '男'
​
#4.查询每个性别的每个专业的平均成绩，并按平均成绩排序
SELECT AVG(score) 平均成绩,majorname,studentname
FROM student s JOIN result r JOIN major m
ON s.majorid = m.`majorid` AND s.`studentno`=r.`studentno`
GROUP BY sex,majorname
ORDER BY 平均成绩
​
#99语法：外连接，
/*
1.把表分成主表和从表，顺序不能更改
 
 /*
 应用场景：用于查询一个表中有，另一个表没有的记录
 
 特点：
 1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
​
 语法：
 select 查询列表
 from 表1 别名
 left|right (outer) join 表2 别名
 on 连接条件
 where 筛选条件；
 */
​
#案例1：查询所有女神的记录,以及对应的男神名， 如果无，就显示null
SELECT b.*,bo.boyname
FROM beauty b
LEFT JOIN boys bo 
ON b.boyfriend_id = bo.id
​
#案例二：查询谁没男朋友
SELECT b.name
FROM beauty b
LEFT JOIN boys bo 
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL
​
#右连接
SELECT b.*,bo.boyname
FROM boys bo 
RIGHT JOIN beauty b
ON b.boyfriend_id = bo.id
​
#案例三：查询那个部门没有员工，并显示其部门编号和部门名字
SELECT department_name,e.department_id
LEFT JOIN employees e
ON e.department_id =d.department_id
WHERE e.employee_id IS NULL
​
#练习
#1.查询编号大于3的女神的男朋友信息，如果有列出详细，没有就null填充
SELECT b.id,b.name,bo.*
FROM boys bo 
RIGHT JOIN beauty b
ON bo.id =b.boyfriend_id
WHERE b.id > 3
​
#2.查询那个城市没有部门
SELECT d.department_id,l.city
FROM locations l
LEFT JOIN departments d
ON d.location_id = l.location_id
WHERE d.department_id IS NULL
​
#3.查询部门名为SAL或IT的员工信息
SELECT e.*,d.department_name
FROM employees e
LEFT JOIN departments d
ON d.department_id = e.department_id
WHERE d.department_name LIKE '%SAL%' OR d.department_name LIKE '%IT%'
​
​
#子查询
/*
当一个查询语句中嵌套了另一个完整的select语句，则被嵌套的select语句被称为子查询或内查询
外面的select查询称为主查询或外查询。
​
分类：
按子查询出现的位置进行分类
1.select后面
         要求：子查询的结果为单行单列(标量子查询)
2.from 后面：
     要求：子查询的结果可以为多行多列
3.where 或 having 后面：✳！！！！！
     要求：子查询的结果必须为单列
        单行子查询
        多行子查询
4.exists后面
     要求： 子查询结果必须为单列(相关子查询)
     
     特点：
        ①子查询放在小括号内
        ②子查询一般放在条件的右侧
        ③单行子查询，一般搭配着单行操作符使用> < >= <= = <>
         多行子查询：IN ,ANY/SOME,ALL
*/
​
#1.标量子查询★
​
#案例1：谁的工资比 Abel 高?
​
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name ='Abel'
#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
)
​
#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
SELECT job_id ,salary,last_name
FROM employees
WHERE job_id =(
    SELECT job_id
    FROM employees
    WHERE employee_id =141
 )
 AND salary>(
    SELECT salary
    FROM employees
    WHERE employee_id = 143
)
​
​
#案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT salary,job_id,last_name
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
)
​
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
​
#①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id =50
​
#②查询每个部门的最低工资
​
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
​
#3.筛选
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id =50
    )
​
#4.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。
​
/*
非法使用
单行子查询，一般搭配着单行操作符使用> < >= <= = <>
多行子查询：IN ,ANY/SOME,ALL
不可以非法使用。

​
*/
​
#小练习
#1.查询和zlotkey 相同部门的员工姓名和工资
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name ='zlotkey'
    )

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT last_name,salary,employee_id
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
    )

/*
二、多行子查询
​
in：判断某字段是否在指定列表内
​
any/some：判断某字段的值是否满足其中任意一个
x = any（10，30，50）
x in（10，30，50）
​
all：判断某字段的值是否满足里面所有的
x>max(10,20,30)
x = all(10,20,30)
*/
​
#案例1 ：返回location_id是1400或1700的部门中的所有员工姓名
#内连接也可以做
SELECT last_name
FROM departments d
JOIN employees e
ON e.department_id =d.department_id
WHERE location_id IN(1400,1700)
#子查询也可以
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
    )

#案例2：返回其他部门中比job_id为‘IT_PROG'部门任一工资低的员工的员工号，姓名，job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary <ANY(
SELECT salary
FROM employees
WHERE job_id ='IT_PROG'
    )
HAVING job_id <> 'IT_PROG'
​
#1.先找出job_id为这个的工资
SELECT salary
FROM employees
WHERE job_id ='IT_PROG'
​
#案例3：返回其他部门中比job_id为‘IT_PROG'部门所有工资低的员工的员工号，姓名，job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary <ALL(
SELECT salary
FROM employees
WHERE job_id ='IT_PROG'
    )
HAVING job_id <> 'IT_PROG'
​
#==========================================================================
#其他子查询
#一、放在select后面
#案例：查询部门编号是50的员工个数
SELECT 
(
SELECT COUNT(*)
FROM employees
WHERE department_id =50
)个数
​
#二、放在from后面
#把它想成一张表
#案例：查询每个部门的平均工资的工资级别
​
​
​
SELECT d.department_id,d.ag,j.grade_level
FROM job_grades j
JOIN(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) d 
ON d.ag BETWEEN j.lowest_sal AND j.highest_sal
​
#三、放在exists后面
#案例1：查询有无名字叫张三丰的员工信息
SELECT EXISTS(
SELECT * FROM employees
WHERE last_name ='张三丰'
) 有无这个人
​
#案例2：查询没有女朋友的男神信息
SELECT bo.* 
FROM boys bo
WHERE bo.id IN(
SELECT boyfriend_id
FROM beauty b
    )

/*
1. 查询和 Zlotkey 相同部门的员工姓名和工资
2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
5. 查询在部门的 location_id 为 1700 的部门工作的员工的员工号
6. 查询管理者是 King 的员工姓名和工资
7. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名
*/
​
#1. 查询和 Zlotkey 相同部门的员工姓名和工资
SELECT last_name,salary,department_id
FROM employees
WHERE department_id =(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
    )

#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT last_name,salary,employee_id
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
    )

#3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT last_name,salary,employee_id,department_id
FROM employees
WHERE department_id IN(
SELECT department_id
FROM employees
    )
HAVING salary>(
SELECT AVG(salary)
FROM employees
    )

#4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id IN(
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%'
    )
#5. 查询在部门的 location_id 为 1700 的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id =1700
    )
#6. 查询管理者是 King 的员工姓名和工资
SELECT last_name,salary,manager_id 
FROM employees
WHERE manager_id IN (
SELECT  manager_id
FROM employees
WHERE last_name ='K_ing'
    )
​
​
#7. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名
SELECT CONCAT(last_name,first_name) '姓.名',salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
    )