JAVA training notes (9) mysql

After suffering from the past few days, everyone is exhausted. For the sake of our java programmer's path, this is just the beginning. For the sake of our dream, everyone continues to cheer up, today we will learn some advanced knowledge about mysql. First, go to view learning. View concept: the view is a virtual table, the display mode of data, and whether the data is stored in the original

After suffering from the past few days, everyone is exhausted. For the sake of our java programmer's path, this is just the beginning. For the sake of our dream, everyone continues to cheer up, today we will learn some advanced knowledge about mysql. First, go to view learning. View concept: the view is a virtual table, the display mode of data, and whether the data is stored in the original

After suffering from the past few days, everyone is exhausted. For the sake of our java programmer's path, this is just the beginning. For the sake of our dream, everyone continues to cheer up, today we will learn some advanced knowledge about mysql.

First, go to view learning. The concept of view: the view is a virtual table, the display mode of data, and whether the data is stored in the original base table.

Syntax for creating a view:

Meaning of the create syntax or replace: When a view with the same name exists, it overwrites the original view. Algorithm defines how to process a view. The default value is undefined. Only merge is used. If it is temptable, the view result is placed in a temporary table, you cannot modify the view in this way.

Example 1

Example 2

To implement a database function in mysql, you can use stored procedures and functions.

	Stored Procedure	Function
Concept	Set of pre-compiled SQL statements	SQL statement set
Parameters	Input and Output Parameters	Parameter, Return Value
Return Value	You can use multiple output parameters	Return only one
Call	Call (input parameter, save the variable of the output parameter)	Call function name (parameter)

Stored Procedure Syntax:

Function Definition Syntax:

Example 1:

Example 2: stored procedure with input parameters

Example 3: stored procedure with Input and Output Parameters

Example 4: Use paging implemented by stored procedures. Here, you need to execute dynamic SQL statements and execute them using prepare and execute. The code here is not complete. You can do it all on your own.

In the following example, we will first introduce how to execute an SQL statement with parameters. Here, the question mark represents the parameter, and prepare represents preprocessing. The program will be precompiled and placed in the cache, the stored procedure is pre-compiled.

Cursor: used to process a single row of data. In mysql, the cursor can only be used in stored procedures or functions.

To use a cursor, follow these four steps:

1. declare the cursor for select statement

2. open cursor name

3. Extract the cursor fetch cursor name into variable

4. close the cursor name

Trigger: You can use a trigger to implement the constraints between tables. A trigger is an automatic operation mechanism.

Syntax:

Trigger_name trigger name.

Trigger_time is the trigger time, which can be before or after, indicating before and after the trigger.

Trigger_event is a trigger event, which can be insert, update, or delete.

Q: How can I increase the number of posting points through a trigger?

Solution: when the user posts for the first time, the insert operation is performed on the integral table. If there is a posting record, the update operation is executed.

Example: rank table and topic post table

Use the new operator for the new value here, if the original value is old.

The knowledge of the mysql database has been fully explained here. Below we need to practice through practical examples to consolidate this knowledge. We 'd better find some interview questions to test our database level, you can also improve yourself through projects.