MySQL database programming, built-in functions, stored procedures (loop Insert)

MySQL Programming
Note: line: # OR--

Defining variables
Set Variable name = variable name
Note: In order to differentiate system variables and fields at the plus of an @ identifier
For example Set @who = ' Korean ';

Use Select to get the value of the current variable


Select 10,15,20 into @a,@b,@c;

Note: = should be an assignment, but within the SELECT statement, the relationship equals, using the specialized assignment operator: =
Same use as set

1, scope, user-defined functions, is global (within the function can), village local scope variables, functions defined within the variable
2. Expiry date (end of connection)


Built-in functions

numeric functions
RAND () gets a random number of 0-1
Floor () Rounding down
Cell () rounding up
Round () rounding
Format () formatting (thousands of bits plus commas)

Time and date functions

Now () Current time
Unix_timestamp () Unix timestamp
From_unixtime ()

String functions

Concat () String connection
Length (), representing the number of bytes char_length () characters
SUBSTRING () Intercept string
Position starting from 1

Lpad () Left complement example Select Lpad (' 1 ', 3, ' 0 '); Output ' 001 '

MD5 () Common encryption, irreversible, all support
Password () encryption, MySQL alone (2 results from SHA1 ())
SHA1 ()

Custom functions



Delimiter $$
Create function SayHello () return varchar (20)
Begin
Return ' Hello World ';
End
$$
Delimiter

Stored procedure insertion (loop insert)

DROP PROCEDURE Test_insert; If this deletion already exists
DELIMITER;; Set the default Terminator to;;
Create PROCEDURE Test_insert () Creating a stored procedure
Begin

DECLARE i INT DEFAULT 0; Define the variable i and set the default value to 0

While I<10 Loop 10 times
Do
INSERT into CMS. Cms_module Inserting data
(
Moduletype,
ModuleName,
Moduledescription,
Designhtml,
QUERY
)
VALUES
(
' 2 ',
CONCAT (' Activity ', I),
' Test join ',
' Aaaaaaaaaa ',
‘‘
);
SET i=i+1; I+1 Guaranteed Cycle 10 times
END while; Exiting while loop

END;; Receive
DELIMITER; Change back to default Terminator
Call Test_insert (); Call

MySQL database programming, built-in functions, stored procedures (loop Insert)