MySQL Learning 18: operator and function numeric operators and functions, comparison operators and functions, date-time functions

numeric operators and functions

Some of the common numeric functions are the following:

(1) numeric operator The common numeric operators for MySQL databases are: + 、-、 * 、、、%, and so on. Here we will not enumerate, just to see two examples:

SELECT 3+4;

SELECT 3%4;

(2) in one rounding and one rounding

SELECT ceil (3.01);

SELECT floor (3.99);

(3) integer Division

SELECT 3/4;

SELECT 3 DIV 4;

(4) Take the remainder (modulo) SELECT 5% 3;

SELECT 5 MOD 3;

(5) Power Operation SELECT 5.3 MOD 3;

SELECT POWER (3,3);

(6) Rounding

SELECT ROUND (3.652,2);

SELECT ROUND (3.652,1);

SELECT ROUND (3.652,0);

(7) digital interception SELECT TRUNCATE (125.89,1);

SELECT TRUNCATE (125.89,0);

SELECT TRUNCATE (125.89,-1);

comparison operators and functions

Common comparison operators and functions:

(1) [not] within the range

SELECT between 1 and;

SELECT between 1 and 22;

SELECT between 1 and 22;

(2) [not] within the scope of the list SELECT Ten in (5,10,15,20);

SELECT in (5,10,15,20);

(3) [not] empty

SELECT null is null;

SELECT ' is NULL;

SELECT 0 is NULL;

(4)[not] empty in query statement

SELECT * FROM Test WHERE first_name is NULL;

SELECT * FROM Test WHERE first_name are not NULL;

Date-time functions

Commonly used date-time functions:

(1) Current date and time and current date and current time

SELECT now ();

SELECT curdate ();

SELECT Curtime ();

(2) Date change

SELECT date_add (' 2016-5-1 ', INTERVAL);

SELECT date_add (' 2016-5-1 ', INTERVAL-23 Day);

SELECT date_add (' 2016-5-1 ', INTERVAL 3 WEEK);

(3) Date difference and date formatting

SELECT DATEDIFF (' 2016-5-1 ', ' 2016-5-23 ');

SELECT DATEDIFF (' 2016-5-15 ', ' 2016-5-3 ');

SELECT date_format (' 2016-5-2 ', '%m/%d/%y ');

MySQL Learning 18: operator and function numeric operators and functions, comparison operators and functions, date-time functions