MySQL (2)

1. Create a calculated field:

Concat, select Concat (Vend_name, ' (', Vend_country, ') ') from vendors;

Rtrim, remove the right space

Select A+b as C from table order by C;

2. Data processing functions:

Text processing functions: Upper Ex:select Upper (a) as a_upper_case from table;

Trim,rtrim,trim

Left, ex: Select Left (' ABCDEFG ', 3), return ABC

Locate: Returns the position of the substring substr the first occurrence of the string str, Ex:select locate (' A ', ' BAC '); Get 2

Substring:http://www.yiibai.com/sql/sql_function_substring.html

Time: Fields are datetime type, stored as date + seconds, filtered by date,time function

Ex:select xx from table date (XXX) = ' 2015-01-01 ';

Year,month,day Filter out Month Day

Data processing: Ex:select Pi (); Select rand (); Select ABS (-1);

3. Aggregation function: AVG, ignore null fields)

Count, Count (*), whether empty or Null;count (column) evaluates to a specific column with a value

Max,min,sum

4.group by, ... from table where xxx GROUP by xxx ORDER by xxxx

, each column in the SELECT statement must be given in the GROUP BY clause

Having xxx, must use the expression, cannot use Alias, ex: The following cannot be used having OrderTotal

Ex, select Order_num,sum (quantity*item_price) as OrderTotal

From OrderItems

GROUP BY Order_num

Having sum (quantity*item_price) >=50

Order BY OrderTotal;

PS: For the data in OrderItems, according to the Order_num group, each group calculates sum (Quantity*item_price),

When sum (quantity*item_price) is >=50, it is filtered and displayed in ascending order of OrderTotal

Select Student_id,avg (Score) as Average_score from scores

GROUP BY STUDENT_ID have AVG (score) <60

Order BY Average_score;

MySQL (2)