MySQL notes (three) SQL classic instance (in)

The structure and data of the table used in the SQL example are in the article

String processing

Understand what SQL can and cannot do with string processing.

1. Sort by substring

select ename from emporder by substr(ename, length(ename)-1, 2);

Reference link

2. Create a delimited list

I want to turn the row data into a list separated by some kind of symbol, for example, separated by a comma.

select deptno, group_concat(ename order by empno separator ',') as empsfrom empgroup by deptno;

3. Convert separated data into multi-valued in list

For example, there is a string "7654,7698,7782,7788". I want to use this string after the in in the where clause.

Numerical processing

计算平均值select avg(sal) as avg_sal from emp; 找最大、最小值select min(sal) as min_sal, max(sal) as max_sal from emp; 求和select sum(sal) from emp; 计算行数select count(*) from emp; 计算非null值的个数select count(comm) from emp;  

Date calculation

1. Addition and subtraction of year, month and day

select hiredate - interval 5 day as hd_minus_5D,hiredate + interval 5 day as hd_plus_5D,hiredate - interval 5 month as hd_minus_5M,hiredate + interval 5 month as hd_plus_5M,hiredate - interval 5 year as hd_minus_5Y,hiredate + interval 5 year as hd_plus_5Yfrom empwhere deptno = 10;

2. Calculate the number of days between two dates

For example, you want to know how many days are the difference between the HIREDATE of the employee ALLEN and WARD.

select datediff( allen_hd, ward_hd)from (select hiredate as ward_hdfrom empwhere ename = 'WARD') x, (select hiredate as allen_hdfrom empwhere ename = 'ALLEN') y;

3. Calculate the month and year between the two dates

I want to find out how many months or years are the difference between two dates. For example, I want to know how many months are the difference between the first and last employee’s start date, and I want to convert this difference into years.

select mnth, mnth/12from (select (year(max_hd) - year(min_hd))*12 + (month(max_hd) - month(min_hd)) as mnthfrom (select min(hiredate) as min_hd, max(hiredate) as max_hdfrom emp) x) y;

4. Calculate the number of seconds, minutes, and hours between two dates

For example, calculate the time difference between ALLEN and WARD hiredate, in seconds, minutes, and hours.

Calculate the difference in days first, and then convert the time unit.

select datediff(allen_hd, ward_hd)*24 hr,datediff(allen_hd, ward_hd)*24*60 min,datediff(allen_hd, ward_hd)*24*60*60 secfrom (select max(case when ename = 'WARD' then hiredate end) as ward_hd,max(case when ename = 'ALLEN' then hiredate end) as allen_hdfrom emp) x;

5. Calculate the date difference between the current record and the next record

Calculate the difference in days between two dates. For example, for each employee in a department with deptno equal to 10, I want to calculate how many days are the difference between their entry dates.

Tips: Find the minimum value of hiredate in all records earlier than the current entry time, and then calculate the number of days between the two dates

select x.*, datediff(x.hiredate, x.next_hd) difffrom (select e.deptno, e.ename, e.hiredate,  (select min(d.hiredate) from emp d   where d.hiredate > e.hiredate) next_hdfrom emp ewhere e.deptno = 10) x;

6. Calculate the date of the first and last day of the month

select date_add(current_date, interval -day(current_date)+1 day) firstday,last_day(current_date) lastday;

interval  DATE_ADD()

7. List the start date and end date of each quarter of the year

select quarter(adddate(dy, -1)) QTR, date_add(dy, interval -3 month) Q_start, adddate(dy, -1) Q_endfrom (select date_add(dy, interval (3*id) month) dyfrom (  select id, adddate(current_date, -dayofyear(current_date)+1) dy  from t100  where id <= 4) x) y;

dayofyear   DATE_ADD and ADDDATE functions: add a specified time interval to the date   QUARTER() function

8. Retrieve data according to a specific time unit

For example, you want to find all employees whose entry month is February or December, and the entry day is Tuesday.

select ename from empwhere monthname(hiredate) in ('February', 'December')or dayname(hiredate) = 'Tuesday';

Interval query

1. Position the continuous value space

select * from V; 使用自连接找出包含连续值的行 select v1.proj_id, v1.proj_start, v1.proj_endfrom V v1, V v2where v1.proj_end = v2.proj_start;

1, the result set paging

select sal from emporder by sal limit 5 offset 0; select sal from emporder by sal limit 5 offset 5;

2. Skip n rows of records