Numeric Functions(数学函数)
SELECT ROUND ( 5.1201 , 2 ) ;
SELECT TRUNCATE ( 5.1201 , 2 ) ;
SELECT CEILING( 5.2 )
SELECT FLOOR( 5.2 )
SELECT ABS( 5.2 )
SELECT RAND( )
String Functions(字符串相关函数)
SELECT LENGTH( 'sky' )
SELECT RTRIM( 'Sky ' )
SELECT LTRIM( ' Sky' )
SELECT TRIM( ' SKY ' )
SELECT LEFT ( 'Kindergarten' , 4 )
SELECT RIGHT ( 'Kindergarten' , 6 )
SELECT SUBSTRING( 'Kindergarten' , 3 , 5 )
SELECT SUBSTRING( 'Kindergarten ' , 3 )
SELECT LOCATE( 'n' , 'Kindergarten' )
SELECT LOCATE( 'garten' , 'Kindergarten' )
SELECT CONCAT( 'first' , 'last' )
SELECT CONCAT( first_ name, ' ' , last_name) AS full name
FROM customer
SELECT REPLACE ( 'Kindergarten' , 'garten' , 'garden' )
SELECT UPPER( 'sky' )
SELECT LOWER( 'SKy' )
Date Functions(日期函数)
SELECT NOW ( ) , CURDATE( ) ,CURTIME( )
SELECT MONTH ( NOW ( ) )
SELECT DAY ( NOW ( ) )
SELECT YEAR ( NOW ( ) )
SELECT HOUR ( NOW ( ) )
SELECT SECOND ( NOW ( ) )
SELECT MINUTE ( NOW ( ) )
SELECT DAYNAME( NOW ( ) )
SELECT MONTHNAME( NOW ( ) )
SELECT EXTRACT( YEAR FROM NOW ( ) )
SELECT *
FROM orders
WHERE YEAR ( order_date) = YEAR ( NOW ( ) )
Formatting Dates and Times
SELECT DATE_FORMAT( NOW ( ) , '%M %d %Y' )
SELECT DATE_FORMAT( NOW ( ) , '%m %D %y' )
SELECT TIME_FORMAT( NOW ( ) , '%H:%i %p' )
Calculating Dates and Times(计算日期值)
SELECT DATE_ADD( NOW ( ) , INTERVAL 1 YEAR )
SELECT DATE_ADD( NOW ( ) , INTERVAL - 1 YEAR )
SELECT DATE_SUB( NOW ( ) , INTERVAL 1 YEAR )
SELECT DATEDIFF( '2014-01-04' , '2014-01-02' )
SELECT DATEDIFF( '2014-01-04 7:00' , '2014-01-02 9:00' )
SELECT TIME_TO_SEC( '9:00' ) - TIME_TO_SEC( '8:00' )
The IFNULL and COALESCE Functions
SELECT
CONCAT( first_name, ' ' , last_name) AS customer,
IFNULL( phone, 'Unknown' ) AS phone
FROM customers
The IF Function(类似三元表达式)
SELECT
product_id,
` name` ,
COUNT ( * ) AS orders,
IF ( COUNT ( * ) > 1 , 'Many times' , 'Once' ) AS frequency
FROM products p
JOIN order_items oi USING ( product_id)
GROUP BY product_id, ` name`
The CASE Operator
SELECT
CONCAT( first_name, ' ' , last_name) AS customer,
points,
CASE
WHEN points > 3000 THEN
'Gold'
WHEN points > 2000 THEN
'Silver'
ELSE 'Bronze'
END AS category
FROM customers
ORDER BY points DESC