MySQL summary
1.MySQl execution sequence
from join on where group by count,max,sum having select distinct order by limit 1,FROM table1 left join table2 on take table1 and table2 The Cartesian product is generated from the data in Temp1 2,JOIN table2 Therefore, first determine the table, and then determine the association conditions 3,ON table1.column = table2.columu Determine the binding condition of the table by Temp1 Generate intermediate table Temp2 4,WHERE For intermediate table Temp2 The generated results are filtered to generate an intermediate table Temp3 5,GROUP BY For intermediate table Temp3 Group to generate an intermediate table Temp4 6,HAVING Aggregate the grouped records to generate an intermediate table Temp5 7,SELECT For intermediate table Temp5 Perform column filtering to generate intermediate tables Temp6 8,DISTINCT For intermediate table Temp6 Perform de duplication to generate an intermediate table Temp7 9,ORDER BY yes Temp7 Sort the data in to generate an intermediate table Temp8 10,LIMIT For intermediate table Temp8 Pagination to generate intermediate tables Temp9
2. Keywords
2.1 group by
Group by means to group by group. The displayed data is group by group, so the data selected by select is either inside the aggregation function or in group by
2.2 Join
2.2.1 inner join
Inner join: inner join returns only rows with equal join fields in two tables
The number of inner join s is less than or equal to the number of records in the left and right tables.
Record attributes with insufficient inner join will be discarded directly.
2.2.2 left join
The left join returns records including all records in the left table and those with equal join fields in the right table.
left join: the number of left joins is the same as the number of records in the left table.
Left join: record properties with insufficient left join are filled with NULL.
2.3 in,between,is null
in include xxx
select last_name
from employees
where department_id not in(30,50,90)
between and be situated between a And b
select last_name,salary,salary*12*(1 + ifnull(commission_pct,0)) Annual salary
from employees
where salary*12*(1 + ifnull(commission_pct,0)) not between 100000 and 200000;
is null/is not null
= Judge common content
is Can only judge null value
<=> Security is equal to, which can judge both ordinary content and content null value
MySQL question brushing
1.SQL1 finds all the information of the latest employee
select * from employees order by hire_date asc limit 1;
desc: descending asc: ascending
Limit: limit the number of displays, from top to bottom
2.SQL2 finds the employee whose time rank is the third from the bottom
select * from employees order by hire_date desc limit 2, 1;
limt 2,1 indicates 3-3
limit 5, 10 means 6 - 15
3.SQL3 find current salary details and department number dept_no
select s.*,d.dept_no from dept_manager d inner join salaries s on d.emp_no=s.emp_no order by s.emp_no;
The third case
A. B makes an internal connection through the public id to obtain the internal information of B that is not contained in a
4.SQL4 find the last of all employees who have been assigned to the Department_ nam
select e.last_name,e.first_name,d.dept_no from employees e inner join dept_emp d on d.emp_no = e.emp_no;
The third case
Similar to the third question, A and B are connected internally through A public id to obtain the internal information of B that is not contained in A
5.SQL5 find the last of all employees_ Name and first_na
SELECT ep.last_name, ep.first_name, dp.dept_no FROM employees ep LEFT JOIN dept_emp dp ON ep.emp_no = dp.emp_no
First case
The tables on both sides of the INNER JOIN have corresponding data at the same time, that is, if there is missing data on either side, it will not be displayed.
LEFT JOIN will read all data in the left data table, even if there is no corresponding data in the right table, it will be null when there is no data.
RIGHT JOIN will read all data in the right data table, even if there is no corresponding data in the left table, no data is null.
7.SQL7 find the employee number EMP of salary records more than 15 times_ no
SELECT emp_no,count(emp_no) as t from salaries group by emp_no having t>15;
count: sum and return the number of results
Order: group by count having
8.SQL8 find out the current salary of all employees
select distinct(salary) from salaries order by salary desc
select salary from salaries group by salary order by salary desc;
1. group by is used when the performance of the system is high and the amount of data is large
2. When the performance of the system is not high, both can be used when the amount of data is small
3. Try to use group by
10.SQL10 get EMP of all non manager employees_ no
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager ON employees.emp_no = dept_manager.emp_no WHERE dept_no IS NULL
The fourth case
SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
Not in is used in practice, because not in will be converted into multi table connections and does not use indexes. Here, I think left is still used_ It would be better to join instead
11. Get the current manager of all employees
select de.emp_no ,dm.emp_no manager from dept_emp de Left join dept_manager dm on de.dept_no = dm.dept_no where de.emp_no <> dm.emp_no
Key points: how to connect the two tables, what you want to output, and what you want to delete
Not equal to sign: < >
15.SQL15 find the employees table emp_no and last_nam
select * from employees where emp_no % 2 <> 0 and last_name !='Mary' order by hire_date desc
1. If the employee number is odd, then EMP_ The remainder of no should be 1
2,last_name is not Mary, use '! =' express
3. According to hire_date is arranged in reverse order with desc
16.SQL16 counts the current employees corresponding to each title type
select t.title,avg(s.salary) avg_s from titles t left join salaries s on t.emp_no = s.emp_no group by t.title order by avg_s asc
17.SQL17 obtain EMP of the employee with the second highest salary_ no
select emp_no, salary from salaries where salary = (select salary from salaries group by salary order by salary desc limit 1,1)
Note the reason for group by. Only one sub query can be added here
19.SQL19 find the last of all employees_ Name and first_name
SELECT em.last_name, em.first_name, dp.dept_name FROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no) LEFT JOIN departments AS dp ON de.dept_no = dp.dept_no