MySQL stored procedures and common functions

Original: MySQL stored procedures and common 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 and 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 has 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); compares strings 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 case insensitive

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 day of the week, 0 represents 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 number

SELECT connection_id (); Number of connections to 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 passwords

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

Add password password to add 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, which can only be converted to the following value when it is empty

Two. Stored Procedures

Put SQL statements in the collection, and then call stored procedures and functions to execute the SQL statements that have already been defined, stored procedures and functions that prevent program developers from repeatedly writing the same SQL statement stored procedures and functions stored and executed in a MySQL server. Can reduce the consumption of data transfer between client and server side.

A group of SQL statements is grouped together and these SQL statements are stored as a whole in the MySQL server.

A stored procedure is a collection of SQL statements stored on a MySQL server as a whole set of executions. The stored procedure runs on the server side, runs faster, and the stored procedure executes once, its execution specification resides in the buffer memory, in the future operation only needs to call the compiled binary code from the buffer memory, may avoid the program developer to write the same SQL statement repeatedly, Reducing the consumption of data transfer between client and server, improving system performance and system response time can also ensure that stored procedures are secure, because we use stored procedures to complete all operations of the database, and you can programmatically control access to database operations.

CREATE PROCEDURE

Sp_name

([proc_parameter[...])

[Characteristic ...]

Routine_body

Parameters: Three parts:

(1) input and output parameters in out INOUT

(2) Name

(3) Type

Characteristics:

LANGUAGE sql: The execution body consists of SQL

Deterministic execution result OK: Same input gets same output

Not deterministic execution results are not deterministic

One of the limitations of using SQL for subroutines:

CONTAINS sql: subroutine contains SQL statement (default)

No SQL: The subroutine does not contain SQL statements

READS SQL Data subroutine contains query data statements

Modifies SQL Data subroutine contains write data statements

Who has permission to execute stored procedures

SQL SECURITY definer| INVOKER

Definer: Defined by (default)

INVOKER: Caller

Comments

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;

Definition of specific 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> Modifying 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;

Stored procedures run on the server side, run faster, and the stored procedure executes once, its execution specification resides in the buffer memory, in the future

The operation only needs to call the compiled binary code from the buffer memory to improve the system performance and system response time. You can also ensure that stored procedures are secure, because we use stored procedures to complete all operations on the database, or you can programmatically control access to database operations.

MySQL stored procedures and common functions