Java Learning Summary (17)--mysql database (3) stored procedures, triggers, database permissions, database design three main paradigms

A. MySQL stored procedure
1. Concept: SQL statement with logic, there is no condition in the previous SQL statement, no loop, the stored procedure with the Process Control statements.
2. Features:
(1) Execution efficiency is very fast, the stored procedure is executed on the service side of the database;
(2) Transplant is very poor, different database stored procedures can not always;
3. Syntax for stored procedures:

Example (we take the employee table as an example):
(1) Stored procedures without parameters:
To create a stored procedure:

The stored procedure was created successfully and called Execute:

Show Results:

(2) Stored procedures with parameters:
#1. Create a stored procedure with input (use employee number to query employee information):

The stored procedure was created successfully and called Execute:

Show Results:

#2. Create a stored procedure with output parameters (output Hello Word):

Stored procedure creation succeeded
To delete a stored procedure:

Front: After:
#3. Stored procedure calls with output parameters
MySQL variables
1) Global variables (built-in variables): MySQL database built-in variables (all connections are functioning)

2) Session variable: exists only in one connection between the current client and the database server side. If the connection is broken, then all session variables are lost!

3) Local variables: variables used in stored procedures are called local variables. Local variables are lost as long as the stored procedure is executed!!
First establish the stored procedure:

Establish success, call now, define a session variable a:

Call succeeded, Access variable A:

Show Results:

#4. Stored procedures with input and output parameters:
To create a stored procedure:

Create successful, start calling:
* Input Parameters

Show Results:

Output parameters:

Show Results:

(3) Stored procedure with conditional judgment
Complete the stored procedure setup as required (enter an integer if 1, then return "Monday" if 2, return "Tuesday", if 3, return "Wednesday". Other numbers, return "error input"):
To establish a stored procedure:

To call a stored procedure:

Show Results:

(4) A stored procedure with loop function:
Note: Circular control leave equivalent to break,iterate equivalent to Contiune;
Create a stored procedure based on your requirements (enter an integer and sum.) For example, enter 100, statistics 1-100, and):

The stored procedure is created, and the call begins:

Show Results:

(5) Stored procedure with cyclic control function

The stored procedure was established successfully and started calling:

Show Results:

Two Trigger
1. Trigger action: When you manipulate a table, you want to trigger some actions/behaviors at the same time, you can use the trigger to complete;
2. Syntax:

Example 1: (When you insert a record into an employee table, you want to insert data into the log table at the same time)
To create a log table:

To create a trigger:

To insert new data into a table:

To view the log table:

Show Results:

Example 2. (Trigger Log table when table is modified)
To create a modify trigger:

To modify data in a table:

To view the log table:

Example 3. (Touch Log table when table data)
To create a DELETE trigger:

To delete a single piece of data from a table:

Query Log table data:

Three. mysql Permissions
1.mysql Database permissions Issue: Root: Have all permissions (can do anything)
(1) Rights account, only partial permissions (curd) For example, you can only manipulate a table of a database
(2) How to modify the MySQL user password?

2.mysql database, User configuration: Users table

3. Assigning a rights account
(1) Permissions: Select Insert Delete Update drop create/or, all
(2) @ after can be localhost can also be IP can also give% that% that any computer can be connected up
(3) Syntax:

4. Database backup and restore note Backup Restore does not require logging into the database

Four Design of data
1. Follow the three main paradigms:
(1) First paradigm: each field of a requirement table must be an indivisible independent unit

(2) The second paradigm: on the basis of the first paradigm, each table is required to express only one meaning. Each field of the table is dependent on the primary key of the table.

(3) The third paradigm: on the basis of the second paradigm, all fields other than the primary key of each table are required to directly determine the dependency relationship with the primary key.

Cases:
Project number project name employee number name Job salary hours
A1 Garden Building 1001 Yang Guoming Engineer 65 13
A1 Garden Building 1002 Ban Jianbin Technician 60 16
A1 Garden Building 1004 Wu Yuelin lawyer 100 19
A2 overpass 1001 Yang Guoming Engineer 65 13
A2 overpass 1003 Ming Mingliang Workers 55 17
A3 Riverside Hotel 1002 Ban Jianbin Technician 60 18
A3 Riverside Hotel 1004 Wu Yuelin lawyer 100 14

Suppose a construction company wants to design a database. The company's business rules are summarized as follows:
The Company undertakes a number of projects, each project has: Project number, project name, construction personnel, etc.
The company has more than one employee, each employee has: Employee number, name, gender, position (engineer, technician), etc.
The company pays the wages according to the working hours and hourly rate, the hourly rate is determined by the employee's position (for example, the technician's hourly rate differs from the engineer)
Correct parsing:

                                                                                        【本次总结完毕】

Java Learning Summary (17)--mysql database (3) stored procedures, triggers, database permissions, database design three main paradigms