Mysql stored procedures and common functions, mysql stored procedures

Mysql stored procedures and common functions, mysql stored procedures

I. Functions

1. mathematical functions

CEIL () into one integer

Select ceil (1, 1.2); 2

FLOOR () Rounding

Select floor (1, 1.9); 9

MOD remainder (Modulo)

Select mod (3,8); 3-3 modulo 8

POWER () POWER Operation

Select power (3, 2); 9

ROUND () Rounding

Select round (123.456, 2); 123.46

TRUNCTATE

Select truncate (123.456, 2); 123.45

ABS () obtains the absolute value.

Select abs (-123); 123

PI () circumference Rate

Select pi (); 3.14

RAND () and RAND (X)

Select rand (); a random number between 0 and 1

Select rand (2); a random number between 0 and 1

Order by rand (); sort BY random

SIGN (X) to obtain a value that matches the positive number 0 and negative number 1

X of EXP (X) e

2. String Functions

LENGTH returns the string LENGTH.

Select length ('kaikeba '); 7

Select length ('course class'); 6

CHAR_LENGTH returns the number of characters in the string.

SELECT CHAR_LENGTH ('kaikeba '); 7

SELECT CHAR_LENGTH ('course bar'); 3

CONCAT (S1, S2 ,......) Connect strings together

Select concat ('s ','s', 'M'); SSM

Select concat ('s ',', 'M'); SM

Select concat ('s', 's', NULL); NULL indicates that the string contains NULL and the result is NULL.

CONCAT_WS (S1, S2 ,......) Concatenate strings with the specified separator

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) converts lowercase letters in the string to uppercase letters.

SELECTUPPER ('randgmei'), UCASE ('randgmei'); RanDongmei

LOWER (s)/LCASE (S) converts uppercase letters into lowercase letters.

LEFT (S, N)/RIGHT (S, N) returns the first N characters of string S, followed by N characters

Select left ('kaikeba', 3); kai

Select right ('Every day, come on, ', 2); Oil

LPAD | the RPAD is left or right to the specified number of characters.

Select lpad ('Kai', 5 ,'! ');!! KAI

Select rpad ('Kai', 5 ,'! '); KAI !!

LTRIM | RTRIM | TRIM the left side of the string | right side | Spaces 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 supports the following two forms.

Select trim ('abba' FROM 'A');

Select trim ('A' FROM 'abcna '); BCN

Select trim ('A' FROM 'abcana'); BCAN

REPEAT (S, N) repeats string s n times

Select repeat ('A', 10); AAAAAAAAAA

SPACE (N) Fill N Spaces

Select space (5 );

Select concat ('+', SPACE (5), '+'); ++

REPLACE (S, S1, S2); REPLACE S1 in string s with S2

SELECTREPLACE ('aaaasddfo ', 'A', 'R'); RRRRSDDFO

STRCMP (S1, S2); returns 0 if S1 and S2 are equal, 1 If S1> S2 is returned, and-1 If S1 <S2 is returned.

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 is case insensitive

SUBSTRINT (S, a, B) is obtained from character a of the string to character B.

Select substring ('kaikeba ', 1, 3); KAI

Starting from 1, truncate three characters

Select substring ('kaikeba ',-2, 3); BA

Select substring ('kaikeba ',-2, 1); B

REVERSE (S); REVERSE string S

Select reverse ('abcde'); EDCBA

ELT (N, S1, S2, S3 ,......); Returns the string at the specified position.

Select elt (3, 'A', 'B', 'C', 'D'); c

 

3. Date and Time Functions

Returns the current date.

Select curdate ();

SELECT CURRENT_DATE ();

Returns the current time.

Select curtime ();

SELECT CURRENT_TIME ();

Returns the current date and time.

Select now (); 2014-11-07

Select sysdate ();

Return month

Select month ('2014-12-16 '); 12

Select monthname (NOW (); NOVEMBER

Returns the 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 indicates Monday.

Select weekday (NOW (); 4

Select week (NOW (); 44 44th weeks in a year

Select year (NOW (); 2014

Select hour (NOW ());

Select minute (NOW ());

Select second (NOW ());

Calculate the number of days between two days.

SELECTDATEDIFF ('2017-12-16 ', '2017-11-07');-1988 days

SELECTDATEDIFF ('2017-04-16 ', '2017-11-07');-1964 days

 

4. system functions

Select version (); current database VERSION

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 STR character set.

Select charset ('afa ');

SELECT LAST_INSERT_ID (); ID of the last insert record

 

5. encryption functions

MD5 ()

PASSWORD ();

SELECT MD5 ('root ');

Select password ('root ');

PASSWORD encryption for user passwords

SELECT * FROM user WHERE User = 'root' \ G;

Add a PASSWORD when adding Permissions

 

6. Process Functions

IF

Select if (3> 1, 'A', 'B ');

SELECT id, username, IF (age> 18, 'Adult ', 'minor') FROM employee;

Select ifnull (NULL, 'this IS null'); THIS ISNULL

Select ifnull (1, 'this IS null'); 1, can be changed to the following value only when it IS NULL

 

Ii. Stored Procedure

Place SQL statements in the Set, and call stored procedures and functions to execute the defined SQL statements, stored Procedures and functions can prevent program developers from repeatedly writing the same SQL statement stored procedures and functions for one storage and execution on the mysql server, reducing the consumption of data transmission between the client and the server.

A group of SQL statements are combined and stored as a whole on the mysql server.

The stored procedure is a set of SQL statements stored on the mysql server as a whole. The stored procedure runs on the server side, which is fast and executed once. Its execution specifications reside in high-speed buffer storage, in future operations, you only need to call the compiled binary code from the cache, this prevents program developers from repeatedly writing the same SQL statement, reduces the consumption of data transmission between the client and server, improves system performance and system response time, and ensures the security of the stored procedure, because we can use the stored procedure to complete all database operations, we can also control the access permissions for database operations through programming.

CREATE PROCEDURE

Sp_name

([Proc_parameter [...])

[Characteristic...]

Routine_body

Parameters: three parts:

(1) Input and Output Parameters IN OUT INOUT

(2) Name

(3) Type

Features:

Language SQL: The execution body is composed of SQL

The execution result of DETERMINISTIC statement is determined: the same output is obtained from the same input.

Not deterministic execution results are uncertain

 

Subprograms use one of the following SQL restrictions:

Contains SQL: CONTAINS SQL statements in subprograms (default)

No SQL: subprograms do not contain SQL statements

The reads SQL DATA subroutine contains the query DATA statement.

The modifies SQL DATA subroutine contains the write DATA statement.

 

Who has the permission to execute the stored procedure?

SQL SECURITY DEFINER | INVOKER

DEFINER: DEFINER (default)

INVOKER: caller

 

Note

COMMENT 'string'

 

1> Create a stored procedure

Change factorization Operator

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> Create 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> call stored procedures and functions

CALL sp_name ([paramer ..])

You must have the permission for the call.

CALL sp_demo ();

CALL age_from_user (1, @ user_age );

CALL age_from_user (2, @ user_age );

Call storage functions

SELECT username_from_user (3 );

4> View the 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 created stored procedures and functions are in the ROUTINES table under the information_schema database.

USE information_schema;

SELECT * from routines \ G;

5> modify 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> delete stored procedures and functions

Drop procedure sp_demo;

Drop procedure if exists sp_demo;

Show warnings;

Drop function username_from_user;

The stored procedure runs on the server side, which is fast and executed once. Its execution specifications reside in high-speed buffer storage.

In the operation, you only need to call the compiled binary code from the cache to improve system performance and system response time. It can also ensure the security of stored procedures, because we can use stored procedures to complete all database operations, or control the access permissions for database operations through programming.