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.