Соединения в базе данных для персональных данных

Введение

В этом проекте вы научитесь использовать запросы на объединение для извлечения соответствующих данных из таблиц emp, dept и salgrade в базе данных по персоналу.

👀 Предварительный просмотр

🎯 Задачи

В этом проекте вы научитесь:

• Как подключиться к базе данных MySQL и импортировать данные по персоналу
• Как использовать равно-объединение (внутреннее объединение), чтобы извлечь имена, даты найма и соответствующие имена департаментов из таблицы сотрудников
• Как использовать неравно-объединение, чтобы извлечь имена, зарплаты и соответствующую информацию о зарплатном разряде для всех сотрудников из таблицы сотрудников
• Как использовать самообъединение, чтобы извлечь информацию о начальнике сотрудника по имени "SMITH"
• Как использовать левое внешнее объединение, чтобы извлечь всю информацию о департаментах и соответствующую информацию о их сотрудниках

🏆 Достижения

После завершения этого проекта вы сможете:

• Разобраться в различных видах запросов на объединение и когда их использовать
• Извлекать и обрабатывать данные из нескольких таблиц в базе данных с использованием запросов на объединение
• Применять продвинутые концепции SQL для решения сложных задач по извлечению данных

Доступ к MySQL и импорт данных

В этом шаге вы научитесь как подключиться к базе данных MySQL и импортировать данные по персоналу.

1. Откройте терминал и запустите следующую команду, чтобы запустить службу MySQL:

sudo service mysql start

2. Подключитесь к командной строке MySQL с использованием команды sudo без пароля:

sudo mysql

3. Импортируйте данные по персоналу из файла /home/labex/project/personnel.sql в базу данных MySQL:

SOURCE /home/labex/project/personnel.sql;

Теперь вы успешно подключились к базе данных MySQL и импортировали данные по персоналу. Вы можете перейти к следующему шагу.

Извлечь имена сотрудников, даты найма и имена департаментов

В этом шаге вы научитесь использовать равно-объединение (внутреннее объединение), чтобы извлечь имена, даты найма и соответствующие имена департаментов из таблицы сотрудников.

1. Откройте файл join_query.sql в текстовом редакторе и добавьте следующий код:

SELECT e.ename AS `Name`, e.hiredate AS HireDate, d.dname AS DepartmentName
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

Этот запрос выполняет внутреннее объединение между таблицами emp и dept, сопоставляя столбец deptno из таблицы emp с столбцом deptno из таблицы dept. Результирующий вывод будет включать имя сотрудника, дату найма и имя департамента.

2. Сохраните файл join_query.sql.

Извлечь имена сотрудников, зарплаты и категории зарплат

В этом шаге вы научитесь использовать неравно-объединение, чтобы извлечь имена, зарплаты и соответствующую информацию о зарплатном разряде для всех сотрудников из таблицы сотрудников.

1. Откройте файл join_query.sql и добавьте следующий код ниже предыдущего запроса:

SELECT e.ename AS `Name`, e.sal AS Salary,
       CASE s.grade
           WHEN 1 THEN 'A'
           WHEN 2 THEN 'B'
           WHEN 3 THEN 'C'
           WHEN 4 THEN 'D'
           WHEN 5 THEN 'E'
           ELSE 'Unknown'
       END AS Grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

Этот запрос выполняет неравно-объединение между таблицами emp и salgrade, сопоставляя зарплату сотрудника (e.sal) с диапазоном зарплатного разряда (s.losal и s.hisal). В CASE-выражении используется для преобразования числового зарплатного разряда в соответствующую буквенную категорию.

2. Сохраните файл join_query.sql.

Извлечь информацию о руководителе сотрудника по имени 'SMITH'

В этом шаге вы научитесь использовать самообъединение, чтобы извлечь информацию о начальнике сотрудника по имени "SMITH".

1. Откройте файл join_query.sql и добавьте следующий код ниже предыдущих запросов:

SELECT e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
WHERE e2.ename = 'SMITH';

Этот запрос выполняет самообъединение на таблице emp, сопоставляя столбец empno одного сотрудника (e1) с столбцом mgr другого сотрудника (e2). WHERE-условие фильтрует результаты, чтобы включать только информацию о начальнике сотрудника по имени "SMITH".

2. Сохраните файл join_query.sql.

Извлечь всю информацию о департаментах и соответствующую информацию о сотрудниках

В этом шаге вы научитесь использовать левостороннее внешнее объединение, чтобы извлечь всю информацию о департаментах и соответствующую информацию о сотрудниках. Если в департаменте нет сотрудников, информация о сотрудниках будет заполнена NULL.

1. Откройте файл join_query.sql и добавьте следующий код ниже предыдущих запросов:

SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno,
       d.deptno AS deptno_dept, d.dname, d.loc
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno;

Этот запрос выполняет левостороннее внешнее объединение между таблицами dept и emp, сопоставляя столбец deptno из обеих таблиц. Результирующий вывод будет включать всю информацию о департаментах, а также соответствующую информацию о сотрудниках. Если в департаменте нет сотрудников, информация о сотрудниках будет заполнена NULL.

2. Сохраните файл join_query.sql.

Теперь вы завершили все задачи в проекте. Вы можете запустить скрипт join_query.sql в командной строке MySQL, чтобы увидеть результаты запросов:

MariaDB [personnel]> SOURCE /home/labex/project/join_query.sql;

Резюме

Поздравляем! Вы завершили этот проект. Вы можете практиковаться в более многих лабораторных работах в LabEx, чтобы улучшить свои навыки.