MySQL stored procedures and frequently used functions

A. function

1. Mathematical Functions

Ceil () into a rounding

SELECT ceil (1.2); 2

Floor () Rounding and rounding

SELECT floor (1.9); 9

MoD take remainder (modulo)

SELECT MOD (3,8); 3--3 to 8 modulo

Power () Power operation

SELECT POWER (3,2); 9

ROUND () rounding

SELECT ROUND (123.456,2); 123.46

Trunctate Digital Intercept

SELECT TRUNCATE (123.456,2); 123.45

ABS () Take absolute value

SELECT ABS (-123); 123

Pi () π

SELECT PI (); 3.14

RAND () and rand (X)

SELECT RAND (); a random number between 0 and 1

SELECT RAND (2); a random number from 0 to 1

Order by RAND (); random sort

Sign (X) gets a value that matches positive 0 and negative 1

EXP (x) e X-side

2. String functions

Length returns string lengths

SELECT LENGTH (' Kaikeba '); 7

SELECT LENGTH (' Commencement Bar '); 6

Char_length returns the number of characters in a string

SELECT char_length (' Kaikeba '); 7

SELECT char_length (' Commencement Bar '); 3

CONCAT (S1,s2,......) To concatenate strings together

SELECT CONCAT (' s ', ' s ', ' M '); Ssm

SELECT CONCAT (' S ', ', ' M '); Sm

SELECT CONCAT (' s ', ' s ', NULL); There is null in the null string. The result is null.

Concat_ws (S1,s2,......) Concatenate strings together with the specified delimiter

SELECT concat_ws (' # ', ' S ', ' w ', ' M '); S#w#m

Selectconcat_ws (' # ', ' S ', ' w ', ' M ', NULL); S#w#m

SELECT Concat_ws (NULL, ' S ', ' w ', ' M '); Null

UPPER (s)/ucase (s) to capitalize lowercase in a string

Selectupper (' Randongmei '), UCASE (' Randongmei '); Randongmei Randongmei

LOWER (s)/lcase (s) lowercase uppercase in a string

Left (s,n)/right (s,n) returns the first n characters of the string S, the last n characters

SELECT left (' Kaikeba ', 3); Kai

SELECT Right (' Day up. Refueling ', 2); oil

lpad| Rpad left or right padding to the specified number of characters using a given character

SELECT lpad (' KAI ', 5, '! ');!! KAI

SELECT rpad (' KAI ', 5, '! '); kai!!

ltrim| rtrim| Trim Remove string left | right | space on both sides

SELECT ' abc ';

SELECT CONCAT (' + ', ' AVC ', ' + '); + AVC +

SELECT CONCAT (' + ', LTRIM (' AVC '), ' + '); +avc+

SELECT CONCAT (' + ', RTRIM (' AVC '), ' + '); +avc+

SELECT CONCAT (' + ', TRIM (' AVC '), ' + '); +avc+

Only trim is available in the following two forms, LTrim and RTrim No

SELECT TRIM (' ABCBA ' from ' A '); A

SELECT TRIM (' A ' from ' Abcna '); BCN

SELECT TRIM (' A ' from ' Abcana '); bcan

REPEAT (S,n) repeats the string S N times

SELECT REPEAT (' A ', 10); Aaaaaaaaaa

Space (n) fills n spaces

SELECT SPACE (5);

SELECT CONCAT (' + ', SPACE (5), ' + '); + +

Replace (S,S1,S2); replaces S1 in string S with S2

Selectreplace (' Aaaasddfo ', ' A ', ' R '); Rrrrsddfo

STRCMP (S1,S2); string S1 and S2, if equal returns 0, if S1>s2 returns 1. If S1<s2 returns-1.

SELECT STRCMP (' A ', ' a '); 0

SELECT STRCMP (' A ', ' B ');-1

SELECT STRCMP (' B ', ' A '); 1 The first string is greater than the second string

SELECT STRCMP (' A ', ' a '); 0 no distinction between uppercase and lowercase

Substrint (S,A,B) from the first character of a string to the B character

SELECT SUBSTRING (' Kaikeba ', 1, 3); KAI

Starting from 1, intercept three characters

SELECT SUBSTRING (' Kaikeba ', -2,3); BA

SELECT SUBSTRING (' Kaikeba ', -2,1); B

REVERSE (s); Invert string s

SELECT REVERSE (' ABCDE '); Edcba

ELT (N,s1,s2,s3,......); Returns a string at the specified position

SELECT ELT (3, ' A ', ' B ', ' C ', ' d '); c

3. Date-time functions

Returns the current date

SELECT curdate ();

SELECT current_date ();

Return Current time

SELECT Curtime ();

SELECT Current_time ();

Returns the current date time

SELECT now (2014-11-07);

SELECT sysdate ();

Return month

SELECT MONTH (' 2014-12-16 '); 12

SELECT MONTHNAME (now ()); November

Return day of the week

SELECT Dayname (now ()); Friday

Day of the week, 1: Sunday

SELECT DAYOFWEEK (now ()); 6

The return date is the day of the week. 0 Delegates Monday

SELECT WEEKDAY (now ()); 4

SELECT WEEK (now ()); 44th Week of 441 years

SELECT year (now ()); 2014

SELECT HOUR (now ());

SELECT MINUTE (now ());

SELECT SECOND (now ());

Calculates the number of days between two dates

Selectdatediff (' 1988-12-16 ', ' 2014-11-07 ');-9,457 days

Selectdatediff (' 1964-04-16 ', ' 2014-11-07 ');-18,467 days

4. System functions

SELECT version (); Current database version

SELECT connection_id (); Number of connections for the current server

Current database:

SELECT DATABASE ();

SELECT SCHEMA ();

Current user

SELECT USER ();

SELECT System_user ();

SELECT Current_User ();

SELECT Current_User;

Returns the character set of the string str

SELECT CHARSET (' AFA ');

SELECT last_insert_id (); Last entry insert record ID number

5. Cryptographic functions

MD5 ()

PASSWORD ();

SELECT MD5 (' ROOT ');

SELECT PASSWORD (' ROOT ');

Password encryption for user password

SELECT * from user WHERE user= ' root ' \g;

Join Passwordpassword when adding permissions

6. Process functions

IF

SELECT IF (3>1, ' A ', ' B ');

SELECT id,username,if (age>18, ' adult ', ' underage ') from employee;

SELECT ifnull (NULL, ' This is null '); This ISNULL

SELECT ifnull (1, ' This is NULL '); 1, only if it is empty, talent becomes the value behind

Two. Stored Procedures

Place the SQL statements in the collection, and then call the stored procedures and functions to run the SQL statements that are already defined. stored procedures and functions prevent program developers from repeatedly writing the same SQL statements stored procedures and functions stored and run in MySQLServer. Can reduce the consumption of data transmitted by client and server side.

Combines a set of SQL statements, and treats these SQL statements as a population, stored in MySQLServer.

A stored procedure is a collection of SQL statements stored in the MySQLServer as an overall run.

The stored procedure runs on the server side. Run faster, and the stored procedure runs once, its running specification resides in the fast buffer memory, in the future operation only need to call the compiled binary code from the fast buffer memory, can avoid the program developer repeatedly writes the same SQL statement, Reduce the consumption of data transmitted by client and server, improve system performance and system response time. It also ensures that stored procedures are secure. Because we can use stored procedures to complete the operation of the database, it is also possible to programmatically control access to database operations.

CREATE PROCEDURE

Sp_name

([proc_parameter[...])

[Characteristic ...]

Routine_body

Participation: Three parts:

(1) input and output parameters in out INOUT

(2) Name

(3) Type

Characteristics:

LANGUAGE sql: Running body consists of SQL

Deterministic run result OK: same input to get same output

Not deterministic run result indeterminate

One of the limitations of using SQL for subroutines:

CONTAINS sql: subroutine includes SQL statement (default)

No SQL: SQL statements are not included in subroutines

READS SQL Data subroutine includes query data statement

Modifies SQL data subroutines include write data statements

Who has permission to run stored procedures

SQL SECURITY definer| INVOKER

Definer: Defined by (default)

INVOKER: Caller

Gaze

COMMENT ' String '

1> Creating a stored procedure

Change the decomposition character

DELIMITER//

CREATE PROCEDURE Sp_demo ()

BEGIN

SELECT * from user;

END

//

DELIMITER;

DELIMITER//

CREATE PROCEDURE age_from_user (in User_idint,out user_age INT)

READS SQL DATA

BEGIN

SELECT age from user WHERE id=user_id;

END

//

DELIMITER;

2> Creating a storage function

CREATE FUNCTION Sp_name

[func_parameter[.]]

RETURNS type

DELIMITER//

CREATE FUNCTION Username_from_user (user_idint)

RETURNS VARCHAR (20)

BEGIN

RETURN (SELECT username from user WHERE id=user_id);

END

//

DELIMITER;

3> calling stored procedures and functions

Call Sp_name ([Paramer..])

Must have permission when calling

Call Sp_demo ();

Call Age_from_user (1, @user_age);

Call Age_from_user (2, @user_age);

Calling a stored function

SELECT Username_from_user (3);

4> viewing created stored procedures and functions

SHOW PROCEDURE STATUS like ' Sp_demo ' \g;

SHOW FUNCTION STATUS like ' Username_from_user ' \g;

Detailed definition of stored procedures and functions

SHOW CREATE PROCEDURE sp_demo\g;

SHOW CREATE FUNCTION username_from_user\g;

The stored procedures and functions that are created exist in the routines table below the INFORMATION_SCHEMA database

Use INFORMATION_SCHEMA;

SELECT * from Routines\g;

5> changing stored procedures and functions

ALTER procedure| FUNCTION

ALTER PROCEDURE Sp_demo COMMENT ' This is atest PROCEDURE ';

SHOW CREATE PROCEDURE sp_demo\g;

ALTER function Username_from_user COMMENT ' This is A TEST FUNCTION ';

SHOW CREATE FUNCTION username_from_user\g;

6> Deleting stored procedures and functions

DROP PROCEDURE Sp_demo;

DROP PROCEDURE IF EXISTS Sp_demo;

SHOW WARNINGS;

DROP FUNCTION Username_from_user;

The stored procedure executes on the server side and executes faster. And the stored procedure executes once, its execution specification resides in the fast buffer memory, in the future

The operation only needs to call the compiled binary code from the fast buffer memory, improve the system performance and system response time. It also ensures that the stored procedure is secure, because we are able to complete all operations of the database using stored procedures. It is also possible to programmatically control access to database operations.

MySQL stored procedures and frequently used functions