Install a database example--employees
Use tool for navicat--(CTRL + Q: Open new Query window
CTRL + R: Runs all statements within the current window)
Reference http://www.cnblogs.com/chenyucong/p/5734800.html
There are 6 associations in the database, and the recorded data is the payroll for all employees in 9 departments of a company, including turnover employees, and the following is a diagram. First sheet: Departments
The department number and department name are recorded for 9 departments.
Table Two: Dept_emp
Department employee data, employee ID and Department ID, in fact time and end time (note: 9999 means still in service)
Count the amount of data on this watch, 331,603 records.
SELECT COUNT (*
from Dept_emp
And then count the number of Emp_no of the employee ID, 300,024 records
SELECT COUNT (DISTINCT emp_no) from Dept_emp
Difference 3W, why? There are duplicate employee IDs, we try to filter out duplicate employee IDs
SELECT Emp_no from dept_emp GROUP by Emp_no having COUNT (EMP_NO) > 1
Okay, now with that nearly 3W employee ID with duplicate values, but we still don't know the reason for the repetition, now we need to filter out the complete information by repeating the employee ID.
SELECT * from dept_emp WHERE inch (SELECT emp_no from dept_empGROUP by emp_no having COUNT (EMP_NO) > 1 )
Now the results are clear, they are the department! So the table record is the true meaning of the data is the time span each employee has to spend in each department.
But at the same time, there is an interesting phenomenon, that is, no employee has been adjusted two times, the following proof
SELECT * from dept_emp WHERE inch (SELECT emp_no from dept_empGROUP by emp_no having COUNT (EMP_NO) > 2 )
Let's see if we can find it in the table we're looking at.
Third sheet: Dept_manger
Similar to the second table structure, each manager in each department has a total of 24 people, each with at least two managers in each department.
SELECT COUNT (DISTINCT as manger_sum from dept_managerGROUP by Dept_no
Table Four: Employees
Employee Information Sheet, Emp_no is the unique key value,
The statistical results are consistent with the data obtained from table Ii.
SELECT COUNT (* from employees;
Fifth sheet: Salaries
Record each employee's salary for each period of time!
Sixth table: Title
Record job titles for each period of time for each employee! However, please note that the period is different from the fifth table, because you will pay a raise in the same position.
MySQL Practice-employees Database