MySQL learning notes

-- 【  Equivalent connection  sql99 standard  】
--  Case study 1. Query employee name 、 Department name 
SELECT last_name,department_name
from employees e
INNER JOIN departments d
on e.department_id = d.department_id;

--  Case study 2. The query name contains e Name of the employee and type of work 
SELECT last_name,job_title
from employees e
INNER JOIN jobs j
on e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';

--  Case study 3. Number of inquiry departments >3 The name of the city and the number of departments （ grouping + Screening ）
SELECT city,COUNT(*)  Number of departments 
from departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT(*)>3;

--  Case study 4. Query the number of employees in which department >3 Department name and number of employees , And in descending order of number （ Add sort ）
SELECT department_name,COUNT(*)  Number of employees 
FROM employees e
INNER JOIN departments d
ON d.department_id = e.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) desc;
---------------------------------------------------------------------------------
-- 【  Non equivalent connection  】

---------------------------------------------------------------------------------
-- 【  Self join  】
--  Case study 5. Query the employee's name and the superior's name 
SELECT e.last_name, m.last_name  The name of the superior 
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;

2、 External connection
Application scenarios ： Used to query a table with , The other table doesn't have records
characteristic ：

• The query result of external connection is all records in the main table
  • If there is a match from the table , The value of the match
  • If there is no match from the table , Is displayed null
  • External connection query results = Internal connection result + Records that exist in the master table but not in the slave table
• The left outer join ,left join On the left is the main watch
• Right connection ,right join On the right is the main table
• Left outer and right outer exchange the order of two tables , Can achieve the same effect
• Full outer join = The result of internal connection + surface 1 Yes, but the watch 2 No, + surface 2 Yes, but the watch 1 There is no the

-- 1)、 The left outer join ：left【outer】
--  Query the names of girls without boyfriends 
 SELECT b.name
 FROM beauty b
 LEFT JOIN boys bo
 ON b.`boyfriend_id` = bo.`id`
 WHERE bo.`id` IS NULL;
 
-- 2)、 Right connection ：right 【outer】
--  Query the names of girls without boyfriends 
SELECT b.name
FROM boys bo
RIGHT JOIN beauty b
ON bo.`id` = b.`boyfriend_id`
WHERE bo.`id` IS NULL

-- 3)、 Full outer join ：full【outer】
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;

3、 Cross connect ：cross

 SELECT b.*,bo.*
 FROM beauty b
 CROSS JOIN boys bo;