MySQL must know meeting book NOTE _ 2

NOTE _ 2 wildcards in MySQL: (trailing spaces may interfere with wildcard matching)

% Match any character _ match any single character

Regular expression: REGEXP

The usage is to replace the LIKE position, followed by a regular expression.

Case insensitive by default. if case insensitive, add a keyword BINARY such as: WHERE *** regexp binary 'jetpack. 000'

SELECT prod_name FROM products WHERE prod_name REGEXP '. 000' order by prod_name

OR match

SELECT prod_name

FROM products

WHERE prod_name REGEXP '2017 | 100'

Order by prod_name

The results are the same.

Splicing string: CONCAT () connects multiple strings to form a long string.

Select concat (vend_name, '(', vend_country ,')')

FROM vendors

Order by vend_name

RTRIM () deletes extra spaces on the right to sort data

LTRIM () deletes extra spaces on the left to sort data

TRIM () deletes spaces on both sides

AS Alias

Perform arithmetic operations

SELECT prod_id,

Quantity,

Item_price,

Quantity * item_price AS expanded_price

FROM orderitems

WHERE order_num = 20005

Arithmetic operators support + -*/.

Test calculation:

Select trim ('ABC ')

Select now ()

LEFT () returns the LEFT character of the string.

LENGTH () returns the LENGTH of the string.

LOCATE the substring of a string

LOWER () converts string to lowercase

RIGHT () returns the character on the RIGHT of the string.

SOUNDEX () returns the SOUNDEX value of the string

SUBSTRING () returns the character of the SUBSTRING.

UPPER () converts string to uppercase

SOUNDEX is an algorithm that converts any text string to the letter/digit mode that describes its voice representation.

SELECT cust_name, cust_contact

FROM MERS

WHERE cust_contact = 'Y Lie'

SELECT cust_name, cust_contact

FROM MERS

Where soundex (cust_contact) = SOUNDEX ('Y Lil ')

Time functions:

DATE () is mainly a DATE.

TIME () is mainly the TIME

NOW () get the current time

Numeric processing functions:

ABS () returns the absolute value of a number.

COS () returns the cosine of an angle.

EXP () returns the exponential value of a number.

MOD () returns the remainder of the division operation

PI () returns the circumference rate

RAND () returns a random number.

SIN () returns the sine of an angle.

SQRT () returns the square root of a number.

TAN () returns the tangent of an angle.

Aggregate functions:

AVG () returns the average value of a column

COUNT () returns the number of rows in a column.

MAX () returns the maximum value of a column.

MIN () returns the minimum value of a column.

SUM () returns the SUM of values of a column. ignore rows whose column value is NULL.