Python/mysql (iv. MySQL database operation)

Python/mysql (iv. MySQL database operation) I. Database condition statement:

1 Else False

Two or three-dollar operation:

1 if(IsNull (XX) 0,1)

Third, up and down connected table:

 from ta1union       from tb2========================================== from ta1union      tb2----------------------------------------------------------------------------  Note: The upper and lower connections must ensure that the number of rows is equal

Iv. Database Temp Table:

1  from student) as B2

V. Database views:

1 Create a View 2 CREATE VIEW name as SQL statement 3 Note: Because a view is a virtual table, creating a view is equivalent to binding a dynamic table in an entity table, so depending on the data of the bound entity table, the chart changes as well. (The table cannot be modified because the view represents a virtual table.) )4 Modify View 5alter VIEW view name as SQL statement 6 here Modify views (Modify the View-bound entity table, Cannot modify the virtual table)7 Delete View 8 Drop View name

VI. database triggers:

A trigger can be bound when a table is modified (you can write the SQL statement you want to execute in the trigger)

1Delimiter//2 Modify the sign of the end statement in the MySQL statement3Create trigger T1 before INSERT on student forEach ROW4 Create an offending device (insert a data to execute the code below once)5 BEGIN6 Start7 INSERT into teacher (tname) values (new.sname);8 A teacher's name is inserted into the teacher's table9 INSERT into teacher (tname) values (new.sname);Ten here new (the value to be inserted by the binding trigger is obtained) One INSERT into teacher (tname) values (new.sname); A INSERT into teacher (tname) values (new.sname); -END// - End the delimiter;  -Modify the sign of the end statement in the MySQL statement

New---means old data---

vii. Database functions:

1, built-in functions (use the built-in function to go to MySQL official website to view)

2. Custom Functions

  1  delimiter \   2   Create function f1 (i1 int i2 int)   3  returns int   4  Span style= "COLOR: #000000" > Defines a function that can pass parameters and also has a return value   5   Begin function Body start   6   declare num int default 0;   7  set Num=i1+I2;   8 return   (num)    end \ \ function body ends  10  delimiter; Reset the closing sentence 

3. Calling function methods

1 Select function name parentheses to invoke 2 Select F1 ()

Note: When defining a function, two parameters are set but only one is used, the other parameter must be passed, and the selsect operation cannot be performed in the function body! Viii. Database Stored procedures:

A stored procedure is an alias (collection) that is stored on MySQL--->sql statement

Stored procedures implement the process of writing code for developers.

Stored Procedure one:

MySQL stored procedure, in the program or client side only need to know the name of the stored procedure can invoke execution.

Stored procedure two:

The MySQL server starts, and the client writes the SQL statement itself.

Create an argument-free stored procedure:

1 CREATE PROCEDURE P1 ()   creating a non-parametric stored procedure 23    from Student: 4     Insert into teacher (TNAEM) VALUES ('Alex')5End 6 7 call a stored procedure 8 call P1 ()

Create a parameter stored procedure: parameters are divided (in (input) out (output) inout (can be input output))

1Delimiter//2 CREATE PROCEDURE P2 (3         inchN1 int,4         inchn2 int creates a stored procedure with parameters, (parameters are in input)5             )6 BEGIN7                 8SELECT * fromStudent where Sid >N1;9END//Ten delimiter; One              ACall P2 (12,2) Call execution stored procedure (pass parameter) -Note: When defining a stored procedure, two parameters are set but only one is used, and the other parameter must be passed

Create a stored procedure with input parameters and output parameters:

  1  delimiter \   2   create Procedrue P3 (  3  in   N1 int   4   out N2 int   5  )   6   begin  Span style= "COLOR: #008080" > 7  set N2=1212 8  SELECT * from  student where Sid > N1   9   end \  10  delimiter; 

Note: The value of the out corresponding must be a variable, there is no return value in the stored procedure (only use out to forge a return value) in the stored procedure can also be selsect operation! ix. Database transactions:

1 delimiter \2 CREATE PROCEDURE P5 (3 Out TSE tinyint4 )5 begin6Declare exit Handler forSqlException7 begin8--Error9Set tse=1Ten rollback One end; A strat TRANSACTION; -Delete fromtb1; -Insert into TB2 (name) VALUES ('Seven') the Commit: ---Success -Set tse=2; - end; +delimiter;

10. Database cursors:

1Delimiter//2 CREATE PROCEDURE P6 ()3 begin4declare row_id int; --Custom Variable 15declare row_num int; --Custom Variable 26 declare done INT DEFAULT FALSE;7 declare temp int;8                 9Declare my_cursor cursor FOR select Id,num fromA;TenDeclare CONTINUE HANDLER for don't FOUND SET done =TRUE; One                  A                  -                  - open my_cursor; the Xxoo:loop - fetch my_cursor into row_id,row_num; -                         if Done and then - leave Xxoo; + END IF; -Set temp = row_id +row_num; + INSERT into B (number) values (temp); A end loop Xxoo; at close my_cursor; -                  -                  -End// - Delimter; -     

XI. dynamic execution of SQL (anti-SQL injection)

There are two ways to prevent SQL injection, the first of which is set in Pycharm, and the second is set in MySQL

1 delimiter \2 CREATE PROCEDURE P8 (3                 inchnid int4             )5 BEGIN6Set @nid =nid;7PREPARE prod from'SELECT * FROM student where SID >?';8 EXECUTE prod USING @nid;9 deallocate prepare prod;Ten end\ Onedelimiter;

Python/mysql (iv. MySQL database operation)