MySQL第07章_单行函数
SELECT ABS( - 123 ) , ABS( 32 ) , SIGN( - 23 ) , SIGN( 43 ) , PI( ) , CEIL( 32.32 ) , CEILING( - 43.23 ) , FLOOR( 32.32 ) ,
FLOOR( - 43.23 ) , MOD ( 12 , 5 ) , 12 MOD 5 , 12 % 5
FROM DUAL;
SELECT RAND( ) , RAND( ) , RAND( 10 ) , RAND( 10 ) , RAND( - 1 ) , RAND( - 1 )
FROM DUAL;
SELECT ROUND ( 123.556 ) , ROUND ( 123.456 , 0 ) , ROUND ( 123.456 , 1 ) , ROUND ( 123.456 , 2 ) ,
ROUND ( 123.456 , - 1 ) , ROUND ( 153.456 , - 2 )
FROM DUAL;
SELECT TRUNCATE ( 123.456 , 0 ) , TRUNCATE ( 123.496 , 1 ) , TRUNCATE ( 129.45 , - 1 )
FROM DUAL;
SELECT TRUNCATE ( ROUND ( 123.456 , 2 ) , 0 )
FROM DUAL;
SELECT RADIANS( 30 ) , RADIANS( 45 ) , RADIANS( 60 ) , RADIANS( 90 ) ,
DEGREES( 2 * PI( ) ) , DEGREES( RADIANS( 60 ) )
FROM DUAL;
SELECT SIN( RADIANS( 30 ) ) , DEGREES( ASIN( 1 ) ) , TAN( RADIANS( 45 ) ) , DEGREES( ATAN( 1 ) )
FROM DUAL;
SELECT POW( 2 , 5 ) , POWER( 2 , 4 ) , EXP( 2 )
FROM DUAL;
SELECT LN( EXP( 2 ) ) , LOG( EXP( 2 ) ) , LOG10( 10 ) , LOG2( 4 )
FROM DUAL;
SELECT BIN( 10 ) , HEX( 10 ) , OCT( 10 ) , CONV( 10 , 10 , 8 )
FROM DUAL;
SELECT ASCII( 'Abcdfsf' ) , CHAR_LENGTH( 'hello' ) , CHAR_LENGTH( '我们' ) ,
LENGTH( 'hello' ) , LENGTH( '我们' )
FROM DUAL;
SELECT CONCAT( emp. last_name, ' worked for ' , mgr. last_name) "details"
FROM employees emp JOIN employees mgr
WHERE emp. ` manager_id` = mgr. employee_id;
SELECT CONCAT_WS( '-' , 'hello' , 'world' , 'hello' , 'beijing' )
FROM DUAL;
SELECT INSERT ( 'helloworld' , 2 , 3 , 'aaaaa' ) , REPLACE ( 'hello' , 'lol' , 'mmm' )
FROM DUAL;
SELECT UPPER( 'HelLo' ) , LOWER( 'HelLo' )
FROM DUAL;
SELECT last_name, salary
FROM employees
WHERE LOWER( last_name) = 'King' ;
SELECT LEFT ( 'hello' , 2 ) , RIGHT ( 'hello' , 3 ) , RIGHT ( 'hello' , 13 )
FROM DUAL;
SELECT employee_id, last_name, LPAD( salary, 10 , ' ' )
FROM employees;
SELECT CONCAT( '---' , LTRIM( ' h el lo ' ) , '***' ) ,
TRIM( 'oo' FROM 'ooheollo' )
FROM DUAL;
SELECT REPEAT ( 'hello' , 4 ) , LENGTH( SPACE( 5 ) ) , STRCMP( 'abc' , 'abe' )
FROM DUAL;
SELECT SUBSTR( 'hello' , 2 , 2 ) , LOCATE( 'lll' , 'hello' )
FROM DUAL;
SELECT ELT( 2 , 'a' , 'b' , 'c' , 'd' ) , FIELD( 'mm' , 'gg' , 'jj' , 'mm' , 'dd' , 'mm' ) ,
FIND_IN_SET( 'mm' , 'gg,mm,jj,dd,mm,gg' )
FROM DUAL;
SELECT employee_id, NULLIF ( LENGTH( first_name) , LENGTH( last_name) ) "compare"
FROM employees;
SELECT CURDATE( ) , CURRENT_DATE ( ) , CURTIME( ) , NOW ( ) , SYSDATE( ) ,
UTC_DATE( ) , UTC_TIME( )
FROM DUAL;
SELECT CURDATE( ) , CURDATE( ) + 0 , CURTIME( ) + 0 , NOW ( ) + 0
FROM DUAL;
SELECT UNIX_TIMESTAMP( ) , UNIX_TIMESTAMP( '2021-10-01 12:12:32' ) ,
FROM_UNIXTIME( 1635173853 ) , FROM_UNIXTIME( 1633061552 )
FROM DUAL;
SELECT YEAR ( CURDATE( ) ) , MONTH ( CURDATE( ) ) , DAY ( CURDATE( ) ) ,
HOUR ( CURTIME( ) ) , MINUTE ( NOW ( ) ) , SECOND ( SYSDATE( ) )
FROM DUAL;
SELECT MONTHNAME( '2021-10-26' ) , DAYNAME( '2021-10-26' ) , WEEKDAY( '2021-10-26' ) ,
QUARTER( CURDATE( ) ) , WEEK( CURDATE( ) ) , DAYOFYEAR( NOW ( ) ) ,
DAYOFMONTH( NOW ( ) ) , DAYOFWEEK( NOW ( ) )
FROM DUAL;
SELECT EXTRACT( SECOND FROM NOW ( ) ) , EXTRACT( DAY FROM NOW ( ) ) ,
EXTRACT( HOUR_MINUTE FROM NOW ( ) ) , EXTRACT( QUARTER FROM '2021-05-12' )
FROM DUAL;
SELECT TIME_TO_SEC( CURTIME( ) ) ,
SEC_TO_TIME( 83355 )
FROM DUAL;
SELECT NOW ( ) , DATE_ADD( NOW ( ) , INTERVAL 1 YEAR ) ,
DATE_ADD( NOW ( ) , INTERVAL - 1 YEAR ) ,
DATE_SUB( NOW ( ) , INTERVAL 1 YEAR )
FROM DUAL;
SELECT DATE_ADD( NOW ( ) , INTERVAL 1 DAY ) AS col1, DATE_ADD( '2021-10-21 23:32:12' , INTERVAL 1 SECOND ) AS col2,
ADDDATE( '2021-10-21 23:32:12' , INTERVAL 1 SECOND ) AS col3,
DATE_ADD( '2021-10-21 23:32:12' , INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD( NOW ( ) , INTERVAL - 1 YEAR ) AS col5,
DATE_ADD( NOW ( ) , INTERVAL '1_1' YEAR_MONTH) AS col6
FROM DUAL;
SELECT ADDTIME( NOW ( ) , 20 ) , SUBTIME( NOW ( ) , 30 ) , SUBTIME( NOW ( ) , '1:1:3' ) , DATEDIFF( NOW ( ) , '2021-10-01' ) ,
TIMEDIFF( NOW ( ) , '2021-10-25 22:10:10' ) , FROM_DAYS( 366 ) , TO_DAYS( '0000-12-25' ) ,
LAST_DAY( NOW ( ) ) , MAKEDATE( YEAR ( NOW ( ) ) , 32 ) , MAKETIME( 10 , 21 , 23 ) , PERIOD_ADD( 20200101010101 , 10 )
FROM DUAL;
SELECT *
FROM employees
WHERE hire_date = '1993-01-13' ;
SELECT DATE_FORMAT( CURDATE( ) , '%Y-%M-%D' ) ,
DATE_FORMAT( NOW ( ) , '%Y-%m-%d' ) , TIME_FORMAT( CURTIME( ) , '%h:%i:%S' ) ,
DATE_FORMAT( NOW ( ) , '%Y-%M-%D %h:%i:%S %W %w %T %r' )
FROM DUAL;
SELECT STR_TO_DATE( '2021-October-25th 11:37:30 Monday 1' , '%Y-%M-%D %h:%i:%S %W %w' )
FROM DUAL;
SELECT GET_FORMAT( DATE , 'USA' )
FROM DUAL;
SELECT DATE_FORMAT( CURDATE( ) , GET_FORMAT( DATE , 'USA' ) )
FROM DUAL;
SELECT last_name, salary, IF ( salary >= 6000 , '高工资' , '低工资' ) "details"
FROM employees;
SELECT last_name, commission_pct, IF ( commission_pct IS NOT NULL , commission_pct, 0 ) "details" ,
salary * 12 * ( 1 + IF ( commission_pct IS NOT NULL , commission_pct, 0 ) ) "annual_sal"
FROM employees;
SELECT last_name, commission_pct, IFNULL( commission_pct, 0 ) "details"
FROM employees;
SELECT last_name, salary, CASE WHEN salary >= 15000 THEN '白骨精'
WHEN salary >= 10000 THEN '潜力股'
WHEN salary >= 8000 THEN '小屌丝'
ELSE '草根' END "details" , department_id
FROM employees;
SELECT last_name, salary, CASE WHEN salary >= 15000 THEN '白骨精'
WHEN salary >= 10000 THEN '潜力股'
WHEN salary >= 8000 THEN '小屌丝'
END "details"
FROM employees;
SELECT employee_id, last_name, department_id, salary, CASE department_id WHEN 10 THEN salary * 1.1
WHEN 20 THEN salary * 1.2
WHEN 30 THEN salary * 1.3
ELSE salary * 1.4 END "details"
FROM employees;
SELECT employee_id, last_name, department_id, salary, CASE department_id WHEN 10 THEN salary * 1.1
WHEN 20 THEN salary * 1.2
WHEN 30 THEN salary * 1.3
END "details"
FROM employees
WHERE department_id IN ( 10 , 20 , 30 ) ;
SELECT MD5( 'mysql' ) , SHA( 'mysql' ) , MD5( MD5( 'mysql' ) )
FROM DUAL;
SELECT VERSION( ) , CONNECTION_ID( ) , DATABASE ( ) , SCHEMA ( ) ,
USER ( ) , CURRENT_USER ( ) , CHARSET ( '尚硅谷' ) , COLLATION( '尚硅谷' )
FROM DUAL;
SELECT FORMAT ( 123.125 , 2 ) , FORMAT ( 123.125 , 0 ) , FORMAT ( 123.125 , - 2 )
FROM DUAL;
SELECT CONV( 16 , 10 , 2 ) , CONV( 8888 , 10 , 16 ) , CONV( NULL , 10 , 2 )
FROM DUAL;
SELECT INET_ATON( '192.168.1.100' ) , INET_NTOA( 3232235876 )
FROM DUAL;
SELECT BENCHMARK( 100000 , MD5( 'mysql' ) )
FROM DUAL;
SELECT CHARSET ( 'atguigu' ) , CHARSET ( CONVERT ( 'atguigu' USING 'gbk' ) )
FROM DUAL;