MySQL Learning notes

1. Unique SQL commands

1) Modify field name: ALTER TABLE tableName change oldname newName type;

　　　Note: The type can be different from the original field type.

　　2) Modify table name: ALTER TABLE tableName Rename [to] newtablename;

3) Paging query: select * from tableName limit startLine,amount;

Note: startLine-the startline+1 row in the database as the first data of the query result, startLine starting from 0;

Amount--Number of bars displayed

4) Query the database encoding format: Show create databases databaseName;

2. Stored Procedures

1) stored procedures with no parameters　　

--fame TerminatorDELIMITER $--create a stored procedureCREATE PROCEDUREpro_test ()BEGIN    --multiple SQL statements/logic can be added    INSERT  intoEmpVALUES(1,'Huwei'); INSERT  intoEmpVALUES(2,'Xingkong');END $--call a stored procedureCall pro_test ();--To Delete a stored procedure--Note: finally No ()DROP PROCEDUREPro_test;

2) stored procedure with input parameters

--fame TerminatorDELIMITER $--Creating a stored procedure (in representing input parameters)CREATE PROCEDUREPro_test (inchEidINT)BEGIN 　　 SELECT *  fromEmpWHEREId=Eid;END $--call a stored procedureCall Pro_test (1);--To Delete a stored procedure--Note: finally No ()DROP PROCEDUREPro_test;

3) stored procedure with output parameters

--fame TerminatorDELIMITER $--Create a stored procedure (out represents an output parameter)CREATE PROCEDUREPro_out (outStr VARCHAR( -))BEGIN    SET Str='Hello Procedure';END $--call a stored procedureCall Pro_out (@str);--View ResultsSELECT @str;

--Delete stored procedures
--Note: finally No ()
DROP PROCEDURE pro_out;

4) Stored procedures that use query results as OUTPUT parameters

DELIMITER $CREATE PROCEDUREPro_into (out enameVARCHAR( -))BEGIN    --into: Assign name to ename    SELECTNAME intoEname fromEmpWHEREId= 1;END$CALL Pro_into (@ename);SELECT @ename；

5) stored procedure with input and output parameters

--Declaration name TerminatorDELIMITER $--Create a stored procedure (out represents an output parameter)CREATE PROCEDUREPro_inout (InOut numINT)BEGIN    SETNum=Num+ 1;END $--set session variable num=10SET @num = Ten;--call a stored procedureCall Pro_inout (@num);--View Output ResultsSELECT @num;

--Delete stored procedures
--Note: finally No ()
DROP PROCEDURE pro_inout;

6) storage structure with If-else logic

DELIMITER $CREATE PROCEDUREPro_if (inchSorceINT, Out LevVARCHAR( -))BEGIN    IFSorce<=  -  Then        SETLev= 'fail'; ELSEIF Sorce<=  the  Then        SETLev= 'Pass'; ELSEIF Sorce<=  -  Then        SETLev= 'Good'; --else and no then.    ELSE        SETLev= 'Great'; --Note: Do not forget to end the IF and semicolon    END IF;END$CALL pro_if ( -,@lev);SELECT @lev;

7) stored procedure with while logic

DELIMITER $CREATE PROCEDUREPro_while (out resultINT)BEGIN    --Defining local Variables    DECLAREIINT DEFAULT 1; DECLARESummaryINT DEFAULT 0;  whileI<= Ten DoSETSummary=Summary+i; SETI=I+ 1; END  while; SETResult=Summary;END$CALL Pro_while (@result);SELECT @result;

Three variants of 3.MySQL:

1) Global variables: MySQL built-in variables

Query all global variables: show variables;

Querying a global variable:select @@ varname;

Modify the value of the global variable:set varName = value;

2) Session variables: Variables that are valid only in this connection (connection interruption, variable destruction)

Define session Variables: set @varName = value;

Value of the query session variable: SELECT @varName;

3) Local variables: variables defined in the stored procedure

Define local variables: declare varName type [default value];

For more information, please visit: http://www.cnblogs.com/BlueStarWei/

MySQL Learning notes