SQL review, grammar notes, fresh out

1. left outer join , The left outer join ,outer Omission , In addition to matching rows in the result table , It also includes rows with mismatches in the left table and in the right table , For such a line , Select the column in the right table and set it to null .left join Used frequently in development ！
2. from clause on Conditions are mainly used to represent the same fields in the table , Other conditions that do not belong to the join table can use where Clause to specify .
3. SQL When no sort method is specified , Default ascending order ！
4. The last day each user logged in ?
   

select user_id, max(date) as id from login
group by user_id;

5. Support syntax ： LIMIT # OFFSET #

SELECT * FROM table LIMIT 2 OFFSET 1;

OFFSET 1 Skip the first 1 Data ,LIMIT 2 Read 2 strip , The reading is the 2 strip , The first 3 strip .

SELECT * FROM table LIMIT 2,1;

Skip the former 2 Data , take 1 strip , Take the second 3 strip .

SELECT * FROM table LIMIT 0,5;     

Take before 5 Pieces of data .

SELECT * FROM table LIMIT 2;

Before acquisition 2 Data .

6. Keep the result after the decimal point 3 position (3 Round off the digits ):

round(avg(score)*1.0, 3)

7. where Used before grouping , Cannot contain aggregate function .having Used after grouping , Filter the results after grouping . Yes having, There must be group by.
8. The query in 2025-10-15 In the future, the status is successfully purchased C++ Course or Java Course or Python The order of , And in accordance with the order_info Of id Ascending sort .

select * from order_info
where date > '2025-10-15' and status='completed' and product_name in ('C++','Java','Python');

9. The query in 2025 Posts and number of resumes delivered during the year , How to express 2025 During the year ？

10. year(date)=2025 //where Conditions 
11. date between '2025-01-01' and '2025-12-31'
12. date >= '2025-01-01' and date <= '2025-12-31'
13. date like '2025%'

14. count() and sum() The difference between
    

sum What counts is the sum ：

select job, sum(num) as cnt
from resume_info
where date like '2025%'
group by job
order by cnt desc;

sum result ：

count Count the number of times ：

select job, count(num) as cnt
from resume_info
where date like '2025%'
group by job
order by cnt desc;

count result ：

15. Eliminate a data , Inquire about schools other than Fudan University

where university not in (' Fudan University ');

16. There are three types that can be followed by aggregate functions

1、select
2、order by
3、having
appear having The reason is that ,where Keyword cannot be used with aggregate function ,having Keywords in group by After keyword , Filter the grouped data

17. Three ways to filter null values ：

1、where Name is not null
2、where Name != ‘null’
3、where Name != ‘’

18. Modify the name of the table ：alter table Table name rename 【to】 The new name of the table ;to Omission .
19. take id=5 as well as emp_no=10001 Replace the row data with id=5 as well as emp_no=10005, Other data remain unchanged , Use replace Realization , Use it directly update Will report a mistake .
    

replace into titles_test 
values ('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01');

master replace into···values Usage of
replace into Follow insert into The function is similar to , The difference is ：replace into First try inserting data into the table ,
If you find that this row of data already exists in the table （ Based on the primary key or unique index ） Then delete this row of data first , Then insert the new data ;
otherwise , Insert new data directly .
It should be noted that ： The table that inserts data must have a primary key or a unique index ！ Otherwise ,replace into Will insert data directly , This will result in duplicate data in the table .

20. Be careful set There are multiple objects behind it ’,' instead of ’and’ Connect .

update titles_test set to_date=null, from_date='2001-01-01'
where to_date='2022-01-01';

21. Delete emp_no Duplicate records , Keep only the smallest id Corresponding records .

delete from titles_test t where t.id not in 
(select * from (select MIN(tt.id) from titles_test tt group by tt.emp_no) a);

pit ：mysql Neutron query and delete The same table is not allowed , It is not allowed to check and delete , You need to alias the subquery .

22. take employees Table for all employees last_name and first_name Spliced together as Name, Use a space in the middle to distinguish .

select concat(last_name," ", first_name) as Name from employees;

concat() Function is used to return the string after multiple string connections . If any parameter in the function is NULL, The return result is NULL. For string constants , You can also write them together directly .SELECT ‘MySQL’ ‘Java’ ‘ab’ AS str;

23. The second highest paid employee emp_no And the corresponding salary salary,
    If there are multiple employees, the salary is the second highest salary , The corresponding employee's emp_no and salary All output , And press emp_no Ascending sort .

SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary FROM salaries
                GROUP BY salary
                ORDER BY salary DESC LIMIT 1,1 );

24. emp_no An odd number can be expressed as where emp_no%2 != 0
25. Find all employees who are not department leaders emp_no.

select emp_no from employees
where emp_no not in (select emp_no from dept_manager);

26. lookup employees All the information of the employee who ranks the third from the bottom in terms of time of entry into the company . Be careful ： There may be employees on the same date , Therefore, there may be more than one employee who ranks the third from the bottom in terms of time of entry .

select * from employees
where hire_date = (select distinct(hire_date) 
                   from employees 
                   order by hire_date desc limit 2,1);

hire_date Third from the bottom , There may be more than one... In the last two , So remember to redo ！

27. on The condition is the condition used to generate the temporary table , It doesn't care on Is the condition in true , Will return to the records in the table on the left .where The condition is that after the temporary table is generated , Then filter the temporary table .
28. Three tables are related .

select e.last_name, e.first_name, d.dept_name from employees e
left join dept_emp de on e.emp_no=de.emp_no 
left join  departments d on d.dept_no=de.dept_no;

29. If not, insert , Ignore... If it exists .

INSERT IGNORE INTO tablename VALUES(...);

If not, insert , If it exists, replace .

INSERT REPLACE INTO tablename VALUES(...);

The existence here means unique When the column value of the attribute exists ,unique Indicates that the key value is unique .
30.

create table if not exists actor_name (
	first_name varchar(45) not null comment ' name ',
	last_name varchar(45) not null comment ' surname '
);
insert into actor_name select first_name,last_name from actor;

31. Add unique index .

alter table actor add unique index uniq_idx_firstname(first_name);

Add a normal index .

alter table actor add index idx_lastname(last_name);

Add primary key index .

alter table actor add primary key index idx_lastname(last_name);

32. in the light of salaries surface emp_no Field creation index idx_emp_no, Inquire about emp_no by 10005, Use force index .

select * from salaries FORCE INDEX (idx_emp_no) where emp_no = 10005;

33. Add columns ,alter table Table name add Name type ;（ By default, it is added to the last column of the table ）

alter table actor add create_date datetime NOT NULL default '2020-10-01 00:00:00';