Foreword by the author
Personal homepage:: small page
gitee page: David Qin
A small blogger who loves to share Welcome all the cuties to learn from
________________________________________________________
Table of Contents
SQL Improvement
Date functions
length
round
reverse
substring
ifnull
case when
cast
grouping sets
Sort function
Window function
______________________________________________________________________
Built-in functions
Date functions
now()
select now();
Get the current time (get to seconds)
year()
select year(now());
Get the current year, pay attention to the () in the year () to fill in now ()
month()
select month(now());
Get the current month
day()
select day(now());
get the current date
length
select first_name, length(first_name) from new_employees limit 0,10;
View character length
round
select round(1.22, 3)
Note that the reserved decimals here will not be automatically completed, and will be rounded
reverse()
string flip
select first_name, reverse(first_name) from new_employees limit 0,10;
substring()
Intercept string
-- intercept string -- The starting position of start, if not, it will start from the first position by default, the length intercepted by length SUBSTRING(column, start, length)
column: field
start: which one to start from
length: the length of the interception
select first_name, substring(first_name,1,5) from new_employees limit 0,10;
ifnull()
Empty judgment
will turn null (empty) into an alias
select ifnull(`daa`, '111') from aaa;;
isnull
nvl
coalesce
All three have the same effect
case when (when…)
This function is a bit special. Take python’s if…elif….else (or C language if…..else if…..else), we only need to write the conditions and code The block is fine, but the way this function is written is like this
select
case when condition
then 'run content 1'
when condition
then 'run content 2'
else "run content"
end as alias
In fact, the sql statement has no format. It is written in this way to make everyone look good. when is equivalent to else if()
cast
Different types of data in the previous database could not be added and subtracted, but some of the current databases can, mysql is one of them
It is no longer supported and cannot be demonstrated here
-- cast(column as data_type) -- change field type select cast(column as int(10)) from table select cast(column as char(10)) from table
grouping sets
We have learned a group before, group by, this method divides all qualified ones into a group, only shows the group, does not show the individual, simply understood as grouping plus aggregation
As shown in the figure, while grouping sets
This is also unavailable in mysql, but we can make such an effect
select depart ,gender ,count(id) cnt from table grouping sets( (depart) -- department dimension ,(gender) -- gender dimension ,(depart, gender) -- the dimension of department and gender ,() -- no grouping, all people ?)
But we can analyze using this method compared to grouping three times and then using union distinct to connect
The first time is to group depart
The second time is grouping gender
The third grouping depart, gender
code:
select coalesce(gender, 'people and 1') , null `age` , count(0) from new_employees group by `gender` with rollup union distinct select null `gender` , coalesce(age, 'renhe 2') , count(0) from new_employees group by `age` with rollup union distinct select `gender` , `age` , count(0) from new_employees group by `gender`, `age` with rollup;
result:
Sorting function
We have learned an order by in the front
And what this sorting function does is to give a number so that we can find it easily
Divided into three categories
row_number() over() as alias ==== “from small to large, in turn down
rank() over() as alias ====== “From small to large, the same data ranks the same, but the ranking of different data remains the same
dense_rank() over() as alias ===== “From small to large, the same data ranks the same, and the ranking starts from 1 without faults
-- row_number() Sort according to the selected field, if there is the same data, the order will be randomly sorted -- Sort by age from oldest to youngest select row_number() over (order by age desc) rk from table -- rank() Sort according to the selection field, if there is the same data, the rankings will be tied, but the number of rankings still occupies -- Sort by age from youngest to oldest select rank() over (order by age) rk from table -- dense_rank() Sort according to the selection field, if there is the same data, the ranking will be tied, but the number of rankings will not be occupied -- Sort by age from youngest to oldest select dense_rank() over (order by age) rk from table
Window function
-- function([column]) OVER(partition by column [order by column]) -- function is usually an aggregate function, and it can also be a sorted function -- Sort heights by gender and age group select row_number() over(partition by gender, age order by high) rk from table -- Group by gender and age to find the number of people select count(id) over(partition by gender, age) cnt from table where id is not null
Remember that there must be a function function, which can be avg() sum(), etc.
partition by isWe can understand it as the front body of group by, only grouping without aggregation, because group by has the effect of aggregation, and the window function lacks the effect of aggregation. Simply put, grouping male and female gender will show Who is male and related information
row_number() over () as alias
select emp_no , age , dept_no ,gender ,row_number() over(partition by dept_no,gender order by age desc) as kkk from ( select a.emp_no ,a.age , a.gender , b.dept_no from new_employees as a join dept_emp as b on a.emp_no =b.emp_no ) as c limit 30;
count(0) over() as alias
select emp_no , age , dept_no ,gender ,count(1) over(partition by dept_no,gender) as kkk from ( select a.emp_no ,a.age , a.gender , b.dept_no from new_employees as a join dept_emp as b on a.emp_no =b.emp_no limit 30 ) as c;
It can be seen that kkk has duplicate data, so let’s take the first piece of data and say that this piece of data belongs to these 19 data. If you replace the count function with avg (age), you can clarify who is above the average and who is not.
Summary:
The sql statement will be introduced here first, if you don’t understand the cuteness, you can chat privately