MySQL-10-mysql programming, linuxmysql Programming

MySQL-10-mysql programming, linuxmysql Programming

MySQL Programming

1. Statement Terminator

There are two default values:; and \ g (which can only be used in the command line)

You can use the delimiter command to modify the statement Terminator, for example, delimiter $ (Note: You can manually modify the Terminator and then change it back to the default value ;)

 

2. Notes

A) line comment: # and -- [space]

B) block comment :/**/

 

3. Variables

A) system variable: show variables [like pattern]; view

B) User-Defined variables:

I. set variable name = variable value. Note: To distinguish between User-Defined variables and system variables, add the @ symbol before the User-Defined variable name. For example, set @ name = 'john'; Use select @ variable name; To obtain custom variables.

The variable value used for Set assignment is also the result of a scalar query, for example:

Set @ total = (select count (*) from student );

Ii. You can also use the select field from table name into @ variable name. For example, select name from student into @ stuName;

Iii. select @ who = 'lily'; // relationship judgment. Be sure not to assign values. If yes, return 1 if the variable @ who's Sino-German value is equal to "Lili, otherwise, 0 is returned.

The third way to define variables: Use the select @ variable name: = variable value method

For example, select @ who: = 'lil'; // assign a value to the variable and retrieve it.

C) variable Validity Period

After the session ends, the variable becomes invalid (that is, after the connection is closed, the variable becomes invalid)

D) Scope of the Variable

User-Defined variables are global. But the variables defined in the function are local.

 

4. Data Type

The data type of the variable is the same as that of the field!

 

5. Operators

The operators in general programming languages are basically the same. Arithmetic Operators and logical operators.

 

6. Functions

1. built-in functions

A) value processing functions

Abs (x): returns the absolute value.

Ceil (x): returns the smallest integer greater than x.

Floor (x): returns the largest integer smaller than x.

Mod (x, y): returns the modulo of x and y.

Rand (): returns the random number select round (rand () * 100) between 0 and 1 );

Round (x, y): returns the rounding result of the y decimal places of x.

Truncate (x, y): returns the result string processing function with x truncated to y decimal places.

B) string processing functions

1. concat (s1, s2.... sn): concatenates input parameters into a string.

2. insert (str, x, y, insert): Starting from the position x of str, replace the string of y with insert

Select insert ('abcdefg', 2, 3, 'Hello ');

3. lower (str), upper (str): converts a string to uppercase and lowercase letters.

4. left (str, x) right (str, x) returns x characters on the left (right) of str. If x is null, null is returned.

5. lpad (str, n, pad) rpad (str, n, pad) pads string str from the leftmost (rightmost) with pad until the total length reaches n

Select name, lpad (name, 10, '#'), rpad (name, 10, '(') from cats;

6. trim (), ltrim (), rtrim () Remove the two sides, left and right Spaces

Select concat ('1', trim ('abc'), '2'), concat ('1', ltrim ('abc'), '2 '), concat ('1', rtrim ('abc'), '2') \ G;

7. replace (str, a, B) replace string a with string B in string str

8. strcmp (s1, s2): If S1 is smaller than S2,-1 is returned; If S1 is larger than S2, 1 is returned; If s1 is equal, 0 is returned (ASC2 code is compared)

9. substring (str, x, y) returns the substring from position x in str with the length of y.

C) Date and Time

Curdate ()

Curtime ()

Now (); select curdate ();

Select unix_timestamp (now ());

Select from_unixtime (1331110656 );

Select week (now (), year (now ());

Select hour (curtime (), minute (curtime ());

Select monthname (now ());

Select date_format (now (), "% Y-% m-% d % H: % I: % s ");

D) process control functions

Create table salary (id int, salary decimal (9, 2 ));

If (value, true, false); select id, salary, if (salary> 300, 'high', 'low') from salary;

Ifnull (t, false) select id, salary, ifnull (salary, 0) from salary;

Case when [value1] then [result1]... else [default] end;

Select

Case when salary <= 300 then 'low' else 'high' end

From salary;

E) Others

Database () select database (); // returns the currently used database

Version () // return the current database version

User () // returns the user currently connected to the database

Inet_aton (ip) converts string addresses to network addresses

Password () encryption for mysql users

Md5 () encryption of user passwords

Select * from mysql. user \ G;

 

2. User-Defined Functions (Note: functions are bound to a database. functions created in a database can only be used in the database. The database is deleted and the functions are also deleted)

Syntax:

Create function Name (parameter list) returns Return Value Type

Function body

Example:

Drop function if exists sayHello;

 

Delimiter $ // first change the terminator to $, because the function body contains a semicolon. If you do not modify the Terminator, mysql considers that a statement ends halfway and reports an error.

Create function sayHello () returns varchar (20)

Begin

Return 'Hello world! ';

End

$ // The function is also a statement and must end with an Terminator.

Delimiter; // change the terminator to the default !!!

Use select sayHello (); To execute

 

7. Process Control

A) Branch statement

If condition 1 then

Statement 1

Elseif condition 2 then

Statement 2

Else

Statements executed when none of the above conditions are met

End if;

Example:

Drop function if exists func1;

Delimiter $

Create function func1 () returns varchar (10)

Begin

If hour (now () <12 then

Return 'morning ';

Else

Return 'afternoon ';

End if;

End

$

Delimiter;

B) Cyclic statements

While condition do

Loop body

End while;

Early cycle termination:

Leave: Terminate the entire cycle (equivalent to the break in C)

Iterate: Terminate this cycle and enter the next cycle (equivalent to the C language's continue)

Note: When Using leave and iterate, you need to add a tag to the loop and terminate it by using the tag (equivalent to the number required for the goto statement in C)

End while label;

Example 1:

Drop function if exists func2;

Delimiter $

Create function func2 () returns int

Begin

Set @ I = 1;

Set @ sum = 0;

While @ I <= 10 do

Set @ sum = @ sum + @ I;

Set @ I = @ I + 1;

End while;

 

Return @ sum;

End

$

Delimiter;

 

Example 2:

Drop function if exists func;

 

Delimiter $

Create function func () returns int

Begin

Set @ I = 1;

Set @ sum = 0;

W: while @ I <= 10 do

If @ I = 5 then

Leave w;

End if;

Set @ sum = @ sum + @ I;

Set @ I = @ I + 1;

End while w;

 

Return @ sum;

End

$

Delimiter;

 

Example 3:

Drop function if exists func;

 

Delimiter $

Create function func () returns int

Begin

Set @ I = 1;

Set @ sum = 0;

W: while @ I <= 10 do

If @ I = 5 then

Set @ I = @ I + 1;

Iterate w;

End if;

Set @ sum = @ sum + @ I;

Set @ I = @ I + 1;

End while w;

 

Return @ sum;

End

$

Delimiter;

 

Note: even if the variable declared inside the function is in the form of @ variable, it is also a global variable and can be accessed outside the function. To use local variables, you can use either of the following methods:

1. function parameters: Format: parameter name Type

2. function declaration local variables: Use declare to declare local variables

Declare I int default 0; (the local variable does not have the @ symbol)

 

Example 1 (using parameters ):

Drop function if exists sayHello2;

Delimiter $

Create function sayHello2 (user_name varchar (10) returns varchar (20)

Begin

Return concat ('hello', user_name );

End

$

Delimiter;

 

You can also use functions as follows:

Select id, sayHello2 (userName) from student;

Example 2 (Use declare to declare a local variable ):

Drop function if exists mySum;

 

Delimiter $

Create function mySum () returns int

Begin

Declare I int default 1;

Declare sum int default 0;

While I <= 100 do

Set sum = sum + I;

Set I = I + 1;

End while;

Return sum;

End

$

 

Delimiter;

 

 

Application:

1. Stored Procedure

2. Storage Functions

3. Trigger