Java Learning Summary (18)--mysql database (4) Views in MySQL database, functions, common loops in stored procedures

A Common loops in MySQL stored procedures
1.while loop: While ..... Do ..... END while
Example 1:
Create stored procedures (for 1+2+.......+num and):

To create a success, make a call:

Show Results:

2.REPEAT loop: REPEAT ..... Untll END REPEAT
Example 2:
To create a stored procedure:

Create complete, call stored procedure:

Show Results:

3.LOOP loop: Loop END loop
Example 3:
To create a stored procedure:

Create successful, call stored procedure:

Show Results:

Two View in MySQL
1. Concept: There is a structure (with rows and columns), but there is no result (the structure of the Unreal storage data) of the virtual table, the structure of the virtual table is not the source of its own definition, but from the corresponding base table (the source of the view data)
2. Create the View syntax:

Example 1 (Create a single table view):

Example 2 (Creating a multi-table view)

3. View view, in fact, the view is a virtual table, that the query table statements, views are available
For example: show tables; Desc My_view1;

Query the table data in view My_view2:

Show Results:

But when viewing the view creation statement, change the tables to view
Cases:

Show Results:

4. Once the view is created, the system creates a corresponding structure file under the database file for the view: frm file
5. Use of Views: the use of views, just for querying, you can use the view as a table, for example: SELECTFrom My_view1;

Show Results:

(Execution of the view: its essence is to execute the encapsulated SELECT statement)
6. Deletion of Views: Drop View Name
7. View modification: The view itself can not be modified, but the source of the view can be modified (in fact, modify the SELECT statement)
Syntax: Alter VIEW view name as new SELECT statement;
Cases:

Show Results:

8. The meaning of the view:
(1) The View saves SQL statements, saves a complex query statement, uses a view, and can be manipulated later
(2) Data security, view operations Note that for query statements, if the views structure is processed (such as delete), the data of the base table will not be affected.
So the relative data is relatively safe.
(3) Views are often used in large projects and are used in multiple systems. I can provide some useful data to hide some key data.
(4) Views can provide friendly data externally: Different views provide different data, and externally supplied data seems to be specially designed.
(5) View for better control of permissions such as hiding some of my base table names from the outside
9. Operation of the view data: The view is capable of data manipulation (e.g., add, delete, change, view data), but there are many limitations
(1) Insert data data:
#1. Multiple table views cannot insert data
#2. You can insert data in a single table view (if the field in the view does not have a field that cannot be empty in the base table and does not have a default value, the insertion is unsuccessful)
#3. View is the ability to insert data into the base table (the operation of the view affects the base table)
(2) View Data deletion:
#1. Multiple table views cannot delete data
#2. A single-table view can delete data and also affect the base table
(3) View Update data:
#1. Single-table view, multiple table views can update data
Update restrictions: With CHECK option
Example: CREATE View my_view1 as selectFrom students where age>30 with CHECK option;

Indicates that the source of the view data is older than 30, with CHECK option deciding to update through the view, you cannot change the student who has obtained the data age>30 to age less than 30, then: Update my_v1 set age=20 where id=1; The error will not be allowed to change because of the restrictions

Three Functions in the MySQL database

1. System functions: direct call, any function has a return value, function call is called through SELECT, because there is a return value, so can only be called with SELECT
The string in MySQL is in a single character unit:

(1) Substring (the original string, from which place, intercept a few); MySQL subscript starts from 1
For example: Define a variable:

Show Results:

(2) Character length and byte length:

Results:

Results:

(3) InStr (@username, ' People '): finds the position of a character in a string if it finds an index that returns this character if no return 0 is found
Cases:

Show Results:

(4) Lpad (@username, 10, ' character to fill ') left padding: Fills the string to the left of the original string by the specified length

Show Results:

(5) Rpad right padding

Show Results:

(6) Insert replacement string
Example: replace two characters from the second character and replace it with ' hehe '

Show Results:

(7) strcmp (string 1, String 2) compares the size of two strings and compares them in dictionary order
Cases:

Show Results:

2. Custom functions: (including function name function parameter return value function body (scope))

(1) syntax for creating a function:

Cases:
To create a simple function:

To invoke a simple function:

Show Results:

(2) Basic function syntax:
--View all functions show functions status;
--View create function test;
--drop function test;
Cases:
To view all functions:

Show Results:

(3) Composite structure definition syntax:
In the body of a function, if you have more than one statement, we need to put multiple statements to begin ... The End statement block
Cases:
To create a function:

function creation succeeded, calling function:

Show Results:

Four Common time functions in MySQL database
1. Common date Functions:

Server three time zone settings:
(1) System time zone---saved in system variable System_time_zone
(2) The server time zone---stored in the global system variable Global.time_zone
(3) The time zone---each client connection is saved in the session variable Session.time_zone
Note: The client time zone setting affects the display of some date function return values, for example: Now (), Curtime (), Curdate (), and the display of timestamp column values.
By default, the time zone of the client and server is the same, and the value is System, which indicates the time zone is used.

2. Returns the current time of the server: Now ([FSP]); Returns the current time and date of the server, the FSP refers to the accuracy of the second, the value 0~6
Cases:

(1) The display format for now () is: ' Yyyy-mm-dd HH:MM:SS '
Cases:

(2) The display format for now () +0 is: ' Yyyymmddhhmmss '
Cases:

Note: Sysdate () returns the current time and date of the server, unlike now (): Br/>@1.sysdate () returns the execution time of the function
< p="">

than the time:

3. Time-Date Calculation function: Add (add) or subtract (sub) A time interval value (expr) for a given time date, respectively
Format:

(1) interval is the key word for time interval
(2) expr is an expression that corresponds to the following type
(3) unit is the interval of units (interval type) (20), as follows:

Example 1: (the current time plus one day)

Example 2: (Minus one minute a day)

4. Personalized Display Date:

(1) DayOfWeek (date); --The day of the week

(2) DayOfMonth (data); --The day of the first one

(3) Dayofuyear (date)-The day ordinal of a year

                        【本次总结完毕】

Java Learning Summary (18)--mysql database (4) Views in MySQL database, functions, common loops in stored procedures