SQL_study_notes3：the_common_function

1 the function related to string
1.1 right(aa, num) or left(aa, num);
###num=1 2 3, use right() and left() to select a specified number of characters from a column.
1.2 substring_index(aa, ‘,’, num);
###num=1 2 3, use substring_index() to grab part of the column, or substring. This one will find everything in front of a specific character or string.
1.3 substring(your_string, start_position, length);
1.4 upper(your_string) or lower(your_string);
###this function will change everything in the string to uppercase or lowercase respectively.
1.5 reverse(your_string);
###it would reverse the order of letters in your string
1.6 ltrim(your_string) or rtrim(your_string) or trim(your_string);
###this function will return your string with extra spaces removed from before (to the left of) or after (to the right of ) a string.
1.7 length(your_string);
###this function would return a count of how many characters are in your string.

IMPORTANT:
string function do not change the data stored in the table; the simply return the altered strings as a result of your query.
So if I want to chnage the data stored in my table, I could use the following sentence:

update table_name
set existing column_name = string_function; ###e.g. right(aa, 2)

But impotantly, the previous column value would be deleted after using the following sentence.

2 the case function

update movie_table 
set category =
case
when aa = 'a' then 'aa'
when bb = 'a' then 'bb'
else 'cc'
end;

3 the order & group function

select a, b, c
from aa
order by a, b desc;
### In general, the order rule is shown the below.
### ! " # $ % & ' () * + , - . / 0123 : ; < = > ? @ ABCD [ \ ] ^ _ ` abcd { | } ~
### desc: the up sequence would be reversed totally

### IN ADDITION
select a, b, sum(aa) as c
from abc
group by a
order by c desc;

4 the common arithmetic function
avg(aa), sum(aa), max(aa), min(aa), count(aa)

5 the distinct function which is used for deleting duplication function

select distinct aa
from a
order by aa;

which would get the following outcome: a aa b c d e
from the following column aa:
aa c c a b c a d c d d b e a b

select count(distinct sale_date)
from cookie_sales;
### with using this sentence, we could get the counting number of sale_date after deleting duplication values

6 the limit function

select first_name, sum(sales)
from cookie_sales
group by first_name
order by sum(sales) desc
limit 2; 
### or limit 1,4
select first_name, sum(sales)
from cookie_sales
group by first_name
order by sum(sales) desc
limit 1,4; 
###limit 2: means that only show the outcomes of the first two rows
###limit 1,4: means that show the raws which starts counting from 1 and return 4 results.
###IN SUMMARY: order by, count, avg, sum, limit, distinct(returns each unique value only once, without no duplicates)

7 a bunch of conditions

select first_name, last_name
from my_contacts
where (a bunch of conditions);
###a bunch of conditions: it is just to show me how the data from one table can be used to pull out data from another.