Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL common functions summary     - df show disk space usage (Linux)

- Learning OpenCV: (VS2010-openCV2.4.3-win7 configuration instructions) (Linux)

- Openfire Hazelcast cluster Detailed (Server)

- Boost notes --Thread - problems encountered in the initial use on Ubuntu (Programming)

- Linux hybrid character device (Linux)

- See how --nand flash timing diagram of a read operation Comments (Programming)

- Zabbix configure DataGuard monitoring (Database)

- Apache Linux firewall reverse proxy configuration (Linux)

- Attic-- delete duplicate data backup program (Linux)

- Zabbix API and PHP configuration (Server)

- Android recyclerview cardview (Programming)

- Installation and configuration under Linux SVN server - Backup - Recovery - Scheduled Tasks (Server)

- Why HBase need to build SQL engine layer (Database)

- Linux installed and tested the deployment of Kafka distributed cluster (Server)

- Linux Network Programming - signal blocking and shielding (block, unblock) (Programming)

- How to install Git client in Ubuntu (Linux)

- Linux 101 hack book reading notes (Linux)

- Each catalog Detailed Linux (Linux)

- Build Python3.4 + PyQt5.3.2 + Eric 6.0 development platform Ubuntu 14.04 (Server)

- How to install and configure the KVM and Open vSwitch on Linux distributions (Linux)

 
         
  MySQL common functions summary
     
  Add Date : 2018-11-21      
         
       
         
  First, Mathematical Functions

1. The absolute value function abs (value)

 mysql> select abs (-120);
+ ----------- +
| Abs (-120) |
+ ----------- +
| 120 |
+ ----------- +
1 row in set (0.00 sec)

2. Floor function floor (value), get less than the maximum integer value

mysql> select floor (23.8);
+ ------------- +
| Floor (23.8) |
+ ------------- +
| 23 |
+ ------------- +
1 row in set (0.00 sec)

3. The ceiling function ceiling (value), the smallest integer value greater than acquisition

mysql> select ceiling (23.8);
+ --------------- +
| Ceiling (23.8) |
+ --------------- +
| 24 |
+ --------------- +
1 row in set (0.00 sec)

4. rounding function round (value, position), rounded to decimal places

mysql> select round (23.27); - default is 0
+ -------------- +
| Round (23.27) |
+ -------------- +
| 23 |
+ -------------- +
1 row in set (0.00 sec)

mysql> select round (23.27,1);
+ ---------------- +
| Round (23.27,1) |
+ ---------------- +
| 23.3 |
+ ---------------- +
1 row in set (0.00 sec)

5. The random number function rand (), get a random number between 0-1

mysql> select rand ();
+ --------------------- +
| Rand () |
+ --------------------- +
| 0.12718137365503365 |
+ --------------------- +
1 row in set (0.00 sec)

Second, String Functions

1. Connect function concat (str1, str2)

mysql> select concat ( 'hello', 'world');
+ ------------------------- +
| Concat ( 'hello', 'world') |
+ ------------------------- +
| Helloworld |
+ ------------------------- +
1 row in set (0.00 sec)

2. delimited copula concat_ws (separator, str1, str2)

mysql> select concat_ws ( '#', 'hello', 'world');
+ -------------------------------- +
| Concat_ws ( '#', 'hello', 'world') |
+ -------------------------------- +
| Hello # world |
+ -------------------------------- +
1 row in set (0.00 sec)

3. Number of Bytes string length (str)

mysql> select length ( 'helloworld I');
+ ------------------------ +
| Length ( 'helloworld I') |
+ ------------------------ +
| 12 |
+ ------------------------ +
1 row in set (0.00 sec)

4. The number of characters in a string char_length (str)

mysql> select char_length ( 'helloworld I');
+ ----------------------------- +
| Char_length ( 'helloworld I') |
+ ----------------------------- +
| 12 |
+ ----------------------------- +
1 row in set (0.00 sec)

5. String Functions judgment

. A IF (exp1, exp2, exp3): If exp1 true return exp2; exp1 if false, return exp3

mysql> select if (2> 1, 'hello', 'world');
+ ------------------------- +
| If (2> 1, 'hello', 'world') |
+ ------------------------- +
| Hello |
+ ------------------------- +
1 row in set (0.00 sec)

mysql> select if (null = 1, 'hello', 'world');
+ ---------------------------- +
| If (null = 1, 'hello', 'world') |
+ ---------------------------- +
| World |
+ ---------------------------- +
1 row in set (0.08 sec)

. B IFNULL (exp1, exp2): If exp1 IS NOT NULL, returns exp1, otherwise exp2

 mysql> select IFNULL ( 'hello', 'world');
+ ------------------------- +
| IFNULL ( 'hello', 'world') |
+ ------------------------- +
| Hello |
+ ------------------------- +
1 row in set (0.00 sec)

mysql> select IFNULL (null, 'world');
+ ---------------------- +
| IFNULL (null, 'world') |
+ ---------------------- +
| World |
+ ---------------------- +
1 row in set (0.06 sec)

. C NULLIF (exp1, exp2): If exp1 = exp2, returns NULL, otherwise exp1

mysql> select NULLIF ( 'hello', 'hello');
+ ------------------------- +
| NULLIF ( 'hello', 'hello') |
+ ------------------------- +
| NULL |
+ ------------------------- +
1 row in set (0.00 sec)

mysql> select NULLIF ( 'hello', 'hello11');
+ --------------------------- +
| NULLIF ( 'hello', 'hello11') |
+ --------------------------- +
| Hello |
+ --------------------------- +
1 row in set (0.00 sec)

6. string conversion functions

. A LTRIM (exp1): removed beginning exp1 string (LEFT) space

mysql> select LTRIM ( 'HELLO');
+ ------------------ +
| LTRIM ( 'HELLO') |
+ ------------------ +
| HELLO |
+ ------------------ +
1 row in set (0.00 sec)

b.RTRIM (exp1): remove exp1 the end of the string (RIGHT) space

mysql> select LTRIM ( 'HELLO');
+ -------------------------- +
| LTRIM ( 'HELLO') |
+ -------------------------- +
| HELLO |
+ -------------------------- +
1 row in set (0.00 sec)

c.TRIM (exp1): remove exp1 the leading and trailing spaces

mysql> select TRIM ( 'HELLO');
+ ------------------------- +
| TRIM ( 'HELLO') |
+ ------------------------- +
| HELLO |
+ ------------------------- +
1 row in set (0.00 sec)

7. string search function

a.SUBSTRING_INDEX (exp1, delim, count), which exp1 a string, delim as a split symbol, count represents the first of several style symbol

mysql> select substring_index ( '1121.qq..com',, 1 '.');
+ --------------------------------------- +
| Substring_index ( '1121.qq..com',, 1 '.') |
+ --------------------------------------- +
| 1121 |
+ --------------------------------------- +
1 row in set (0.00 sec)

. B SUBSTRING (exp1, pos, len): exp1 a string, pos for the location, len is the length

mysql> select substring ( '1121.qq.com', 1,9);
+ ------------------------------ +
| Substring ( '1121.qq.com', 1,9) |
+ ------------------------------ +
| 1121.qq.c |
+ ---------------------------- +
1 row in set (0.00 sec)

8. Date Manipulation Functions
Date Format Conversion Functions
Way to convert a string to a date format DATE_FORMAT () or STR_TO_DATE (), the following two functions
DATE_FORMAT (expr1, format)
STR_TO_DATE (expr1, format) a
Common date format YYYY-MM-DD HH: MM: SS format corresponding to% Y-% m-% d% H:% i:% S
Date arithmetic functions
CURDATE (): Returns the day's date in the format: YYYY-MM-DD
NOW (): Returns the current date and time in the format: YYYY-MM-DD HH: MM: SS
DATE_ADD (date, INTERVAL expr unit): expr expression, unit to unit,

E.g:
DATE_ADD ( '2013-08-20', INTERVAL -1 DAY), returns: 2013-08-19

9. The date arithmetic functions
MONTH (date): Returns the number of months of the date such MONTY ( '2013-08-20'), returns: 8
DAY (date): Returns the number of days of date of such DAY ( '2013-08-20'), return: 20
YEAR (date): Returns the number of years of the date, such as YEAR ( '2013-08-20'), returns: 2013
TO_DAYS (date): date corresponding to the number of days, for example TO_DAYS ( '2008-10-07'), return: 733 687
WEEK (date): Returns the date corresponding to the day of the week, such as: WEEK ( '2013-08-20'), returns: 2
DATEDIFF (date1, date2): Returns the number of days difference between date1-date2, for example:
DATEDIFF ( '2013-08-20', '2013-08-18 13:30:14'), returns: 2

10. Type Conversion Functions
CAST (expr AS type): expr converted to a certain type
CONVERT (expr, type): expr converted to a certain type
CONVERT (expr USING transcoding_name): expr character set is converted to a character set
     
         
       
         
  More:      
 
- C ++ pointer two third memory model (Programming)
- 11G ASM disk group does not automatically handle MOUNT (Database)
- Linux system security reinforcement system by masquerading method (Linux)
- Linux, Oracle listen address modification (Database)
- XP virtual machine under VirtualBox solve occupy 100% CPU problem (Linux)
- Puppet installation and testing (Server)
- Automatic batch resolve dependencies problem locally installed rpm package (Linux)
- Hadoop 2.7.1 Installation and Configuration under RedHat Linux 6.5 (Server)
- Simple Linux file system - df, du, ln (Linux)
- Python basis: os module on the file / directory using methods commonly used functions (Programming)
- Linux Powerful IDE - Geany configuration instructions (Linux)
- Generic mechanism C11 standard (Programming)
- Oracle Standby Redo Log experiment (Database)
- CV: Linux command displays the progress of the run command (Linux)
- Ubuntu 14.10 used ifconfig commands to manage your network configuration (Linux)
- CentOS 6.6 shortcut command execution (Linux)
- CentOS 6.7 install Nagios Tutorials (Server)
- Getting CentOS Learning Notes (Linux)
- Linux Mint 17.2 64 bit installation Docker and management software seagull (Linux)
- Basic Tutorial: Linux novice should know 26 commands (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.