hello everyone , I am jacky Zhu Yuanlu , I'm glad to continue to share with you 《MySQL Data analysis practice 》, This section ,jacky Will share single table operation with friends 8 The last four sentences of a proverb ： namely ： Don't take it SQL
Do analysis , display slight skill before an expert ; What to do if you want to analyze , Programming language is the main force ,SQL There are so many functions , Really can't use too much , Screening perspective is the foundation , Group aggregation should be kept in mind .
We shared it earlier 8 The first four sentences of a proverb , that is SQL Data preprocessing and SQL Data cleaning , What about the last four sentences , That's it SQL Data analysis ,SQL Data analysis is about two aspects ： The first aspect is SQL The problem of function , The second aspect is the problem of grouping aggregation .
Let's talk about it first SQL function , We take notes
<>（ One ）SQL What is a function
To make it clear SQL
Function problems in grammar , Let's talk about functions in mathematics first . Some friends will ask , Why say function in mathematics , This is with us SQL Is the function relation in grammar big ? Besides, we knew the function in junior high school , What is there to say ? that
jacky say , Understand SQL Function in grammar , I really need to understand what function means in mathematics , We studied mathematics for many years as a student , You don't really understand the definition of functions .
Let's take a look at the definition of functions in mathematics .
<>1.1 What is a function
What exactly is a function ?
The word function comes from translation , Li Shanlan is a mathematician in Qing Dynasty （1859 year ） In Translation 《 Algebra 》 One book time , hold “function” Translated into “ function ” Of . But I personally think this translation is inappropriate , Such obscure translation directly leads to the difficulty in popularizing mathematics in China . I personally have done data modeling for several years , Every day with this so-called “ function ” Dealing with , In fact, the function is
function ,function What is the literal translation of English , It's the function . That function is function , This kind of translation will be much better . that SQL
function , namely SQL Function of , It's called SQL function , The function corresponding to this function is called , It's much better for us to understand this way .
good , So, what is a function , Let's talk about it again SQL What are the functions in ?
<>1.2 SQL function
Here I am right SQL Function to do a further explanation , The function is not only right SQL Come on , For all programming languages , Functions play an important role . Functions are like programming languages “ Props box ”, Every programming language has a lot of functions . It's like the front jacky Yes , Function is function , Functions can help us implement various functions , For example, functions can help us to do calculations , String operation , Date calculation and other budget functions .
We learned about aggregate functions , Do you remember , So aggregate functions can aggregate data, right . A function like the aggregate function SQL There are altogether 200 varied , This is it. jacky stay 8 What is said in a proverb “SQL There are so many functions ”, Why do you say that “ Really can't use too much ”?
<>1.3 Don't use functions
front jacky Share it with one class MySQL In the end what is it? ?jacky say MySQL It's a software that stores data, right . in other words MySQL The main function of is to store data , Then analyze the data , In other words, the function of calling data is not its main business , Is that so? ; Since the calling function is not MySQL Main business , That is to say MySQL It is not optimized for calling functions , What do you mean ? Because databases, especially mysql, Although these methods are provided , But if the traffic is particularly large , There's a lot of data , It is easy to slow down by calling some complex functions , Even a database crash . If it's for internal use only , The number of visits is small , It doesn't matter . The bigger the project, the less complex sql sentence , Try to find out all the data , Processing in code .
How should we learn SQL What about the function ?
jacky The advice given is , We just need to know some representative functions , These representative functions can be roughly divided into the following categories ：
For numerical calculation ： arithmetic function
For string manipulation ： String function
Used for date operation ： Date function
Used to convert data types and values ： Conversion function
Aggregate function for data aggregation
The above functions are only for understanding , Know what they do , Check the relevant information when using it .
<>（ Two ） Group aggregation
What is packet aggregation , Here are two keywords , One is called grouping and the other is aggregation , These two key words are not easy to understand . What is grouping , What is aggregation ?
Two ,group by and having explain ： The premise must be understood sql A special function in language ： Aggregate function ,
for example SUM, COUNT, MAX, AVG etc . The fundamental difference between these functions and others is that they generally act on multiple records .
having It's grouping （group by） Filter conditions after , In group re screening after grouping .
Three ,having and where meaning ：
having It's grouping （group by） Filter conditions after , In group re screening after grouping ;where Filter before grouping .
where Aggregate functions cannot be used in clause , and having Clause can be used , So we add in the set function HAVING To test whether the query results meet the requirements . Namely having The applicable scenario of clause is that aggregate function can be used .
having Clauses restrict groups , Not the line .having Each element in the clause must also appear in the select In the list . Some database exceptions , as oracle.
hello everyone , I am jacky Zhu Yuanlu , Let's move on to our last share , This section jacky First of all, we will explain the grouping aggregation having
clause ; here jacky In a word ：jacky We talked about group aggregation for half a day , Let me give you another popular explanation of what is grouping aggregation . We remember 8 How to say the last sentence of a proverb , Right? ： Screening perspective is the foundation , Group aggregation should be kept in mind . What do you think of this sentence , Everyone must have used it
EXCEL This software ,EXCEL Everyone has used it
Filter and perspective these two functions , Is that so? . Let's say SQL Group aggregation in is equivalent to EXCEl Filter and perspective are the two functions in , In essence ,EXCEL It's also a database . good , By analogy EXCEL Functions in , Do you have a more intuitive understanding of the role of group aggregation .
good , Let's go back to the topic of this lesson , Let's talk about packet aggregation having What do clauses do ? that jacky say ,having clause , And where Clause , They are all conditional judgments
; that jacky I said it last time ,where and having Can be used in group aggregation statements .
that , Conditional judgment of grouping results , When do we use it having , When to use where What about ?
（1）where Can do it ,having Almost everything , I don't want to remember too much , Just use it having;
（2） Something , only having Can do it ,where be unable to do sth. ：
1）where Aggregate functions cannot be used in clause , and having Clause can be used ;
2）where Field aliases cannot be used in clause , and having You can ;