Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL common functions summary     - Java Concurrency: synchronized (Programming)

- Oracle 12c of the auto-increment Identity Columns (Database)

- Talk about Java EE Learning (Programming)

- Firewall chapter of Linux server security configuration (Linux)

- Linux Programming memory mapping (Programming)

- Unix system security configuration (Linux)

- CentOS 6.6 installation certification system based on the ftp service (Server)

- Ubuntu 14.10 Server configuration wireless Internet access (Server)

- Necessity in Java packages (Programming)

- Linux operating system security settings initial understanding (Linux)

- Using LLVM Clang and Blocks under Linux (Programming)

- Use top to monitor the remote server (Server)

- Use calcurse schedule appointments and to-do in the Linux terminal (Linux)

- AngularJS (Programming)

- Access.log Apache access log analysis and how to import it into MySQL (Server)

- Use Oracle 11g show spparameter command (Database)

- Docker commonly used commands Description (Linux)

- CentOS 7.0 Automatic installation CD-ROM production Comments (Linux)

- Mumble installation source VoIP application on Ubuntu (Linux)

- VPS xen openvz kvm (Server)

 
         
  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:      
 
- How apt-get limited use IPv4 or IPv6 protocol to download (Linux)
- Linux System Getting Started Learning: Disable HTTP forwarding wget in (Linux)
- KVM add virtual disks (Linux)
- CentOS 6.5 Telnet SecureCRT use management tools (Linux)
- Linux System Getting Started Learning: Statistical process a number of threads in Linux (Linux)
- LVS + Keepalived the DR mode (Server)
- Vim useful plugin: vundle (Linux)
- Use Aptik Battery Monitor monitoring Ubuntu laptop battery usage (Linux)
- Solve the compatibility problem between Linux and Java at the source in bold font (Linux)
- CentOS 6 Install Linux kernel source (Linux)
- CentOS yum configuration under local sources (Linux)
- Java data structures - the linear form of the single-chain applications (Programming)
- Linux SVN account password to save your settings (Linux)
- C # assembly calls across constants, variables and functions (Programming)
- Five Linux user space debugging tool (Linux)
- MySQL performance view and configure finishing Daquan (Database)
- MySQL related statements (CRUD) (SQLyog software) (Database)
- Debian 7 and Debian 8 users how to install Oracle Java 8 (Linux)
- Two minutes thoroughly so that you understand Android Activity Lifecycle (Programming)
- Manage SQL Server services login (start) account and password (Database)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.