PHP Learning 13 database Operation--mysql Programming

First, the Data Control language

The Data Control language is a statement that manages MySQL users and their permissions;

1. User Management

　　　Where the user data resides

All users in MySQL are stored in the user table in the system database MySQL;

　　　　　　

　　　Create user

Form: Create user ' username ' @ ' Allow login address/server ' identified by ' password ';

Description

1. Allow the login address server is the location to allow this setting, to use the settings for the user name and password login, other locations do not

2. Visible, MySQL security what authentication requires three information.

　　　　　　

　　　　　　

　　　　Delete User

Drop user ' username ' @ ' Allow login address or server name ';

　　　　　　

　　　Modify User Password

To modify your password:

Set password = password (' password ');

　　　　　　

Modify someone else's password (to have permission):

Set password for ' username ' @ ' Allow login address ' = password (' password ');

　　　　　　　　　

2. Rights Management

　　　What are the permissions?

MySQL database, all of the things that can be done, divided into about 30 permissions, each of which is a "word" only! Like what:

Select representatives can query the data;

Update represents data that can be modified;

Delete represents the ability to delete data;

......

One of these permissions is called All, which represents all permissions

　　　　　　

Another form of expression:

　　　　　　

　　granting permissions

Form:

Grant permission list on a library. An object to ' username ' @ ' login location ' [identified by ' Password '];

Description

1. The permission list is a noun with multiple permissions, separated from each other by commas;

2. A library. An object that is attached to a subordinate unit of a specified database;

3.identified by can be omitted, if not omitted, it can be said to assign permissions and also to modify its password, but the user does not exist, this is the creation of a new user, the password must be set.

　　　　　　

　　　　　　

　　　　Deprivation of authority

Form:

Revoke permission list on a library. An object from ' username ' @ ' Allow login location ';

　　　　　　　　　

Second, the transaction control Language 1. What is called a "transaction"

Things are to ensure that multiple additions and deletions of the execution of the statement of consistency: either execute, or do not execute, only these two cases;

2. Features of the transaction

Atomicity: All the statements in a thing should be done: either wholly or not;

Consistency: Let the data remain logically ' justified ', for example, when a product is out of the library, so that the number of goods in the commodity library-1, but also to the corresponding user's shopping cart in the product +1;

Isolation: If multiple things execute concurrently, each thing is executed as if it were independent;

Persistence: When a thing executes successfully, it should be a clear drive data change (not just a change in memory) to the data.

3. Transaction Mode

　　　Transaction mode refers to the following:

In our cmd command-line mode, whether the "one statement is a transaction" switch is turned on;

By default, this mode is turned on, which is called the "autocommit mode";

Set autocommit = 1;

After this, each adding and deleting the statement, will be effective immediately;

We can turn it off, that is, ' man-made mode '------that requires human submission;

Set autocommit = 0;

After such changes, all additions and deletions to the statement, must use the commit time to take effect;

　　　　　　

　　　　　　

　　　　　　

4. Basic process of transaction execution

　　　1. Start a thing

Start transaction;//here can also be written as begin

　　　　2. Execute multiple additions and deletions to the sentence;

　　　3. Determine the results of the execution of these statements, and commit or rollback;

if (no error) {

commit;//here is the commit transaction, which is done at once;

}else{

rollback;//rollback of a transaction, at which point it is all undone;

}

　　　　　　

Third, the MySQL programming 1.mysql programming statement in the statement block inclusion character

The statement inclusion in the MySQL statement is actually equivalent to the curly braces in JS or PHP;

[Designator] Begin

The statement to execute ....

End [designator]

Example: if (conditional judgment)

Begin

//......

End

End If;

　　　　　　　　　　　　　　

2. Process Control Statements

　　　If statement

　　　　　　

　　　　Case statement

It is equivalent to switch in PHP;

　　　　　　

　　　　Loop Loop statement

　　　　　　

　　　　While loop

　　　　　　

　　　　Repeat cycle

　　　　　　

　　　　Leave statements

Leave identifiers;

The function is to exit the structure of the begin...end structure or other offices with identifiers.　　　

Variables in the 3.mysql

In MySQL, there are two kinds of variable forms;

　　　Normal variable: No "@" symbol;

　　　　　Definition form:

Delare variable type name [default value];//normal variable must be defined first;

　　　　　　Assignment form:

Set variable name = value;

　　　　　　The value is used to directly use the variable name;

Use "place": can only be used in "programming environment";

What is a programming environment? There are only 3 of them:

1. Define the interior of the function;

2. Define the internal of the stored procedure;

3. Define triggers;

　　　　　Session variable: With "@" symbol:

Definition form:

SET @ variable name = value;//similar to PHP, no need to define, direct assignment;

Can be used almost anywhere

　　　　Variable assignment has the following form:

Syntax 1 (for normal variables)

Set variable name = expression; A variable in this syntax must first use the Declare declaration

Syntax 2 (for session variables)

The Set variable name = expression, which does not require a declare syntax declaration, but rather a direct assignment, similar to PHP defining variables and assigning values.

Syntax 3 (for session variables)

SELECT @ Variable name = expression, #此一举会给变量赋值 and output result set as a SELECT statement;

Syntax 4 (for session variables)

The select expression into the @ variable name, which appears to be a SELECT statement, but does not actually output the result set, but only assigns a value to the variable.

　　　　　　

4. Storage (function) process procedure

function, also known as "storage function", is actually JS or PHP in the function described, but its only difference is: The function here must have a return value;

Definition form:

　　　　　　

Attention:

Within the function, the use of various variables and process controls can be used;

Inside the function, can also have a variety of additions and deletions to change the statement;

Inside the function, there can be no select or other query class statement that returns the result set;

　　　　　　

　　　　Invocation form

Like calling an intrinsic function, such as:

Select Now ();

Or, if you are in a programming statement:

　　　　　　

　　　Delete a function

The name of the drop function function;

　　　　Stored Procedure procedure

Stored procedure, which is essentially a function-----but: cannot have a return value

Definition form:

　　　　　　　　

Description

In: Used to set the variable to accept the actual parameter data, that is, the incoming data;

Out: Used to set the variable to "store data in stored procedures", that is, the function must be assigned value;

InOut: is a combination of in and out, with two-way effect;

For, out and in Settings, the corresponding argument is "must" is a variable, because the variable is used for "outgoing incoming data";

　　　Call a stored procedure

Call stored procedure name (argument 1, Argument 2 ...). ）

It should be in the "non-programming environment" Call, that is, adding and deleting the scene under the change:

Example:

Create a stored procedure

The goal of the stored procedure is to store three data in a data table, and return the value of the table

　　　　　　

Example (using in, out, inout)

　　　　　　

　　　　　　　　

To delete a stored procedure

drop procedure stored procedure name;

5. Use of stored functions or stored procedures in PHP

　　　　　　

6. Triggers

　　　　Meaning

Triggers, which are pre-defined programming code (like stored procedures and stored functions), and a right name. It cannot be called, but it is automatically called when a table has an event (incremental deletion).

Definition form

Create TRIGGER Trigger Name trigger event on table name for each row as

Begin

The triggered statement

End

Description: Trigger time only two: before, after

Trigger event, only three: Insert,update,delete

Before or after an insert in a table, go back to the code that is written in it, that is, only 6 cases per table may invoke the trigger;

In general, a trigger is used to perform a pruning operation on a table, and it is necessary to do another thing at the same time;

Inside the trigger, there are two keywords that represent a particular meaning that can be used to get the data:

New IT represents the "newline" data at the time of the insert or update that is currently being executed, through which you can get the value of any field of this new row of data, in the form:

Set @vq = new.id//Gets the ID field value of the newly inserted update row

Old: It represents the "legacy row" data at the time of the delete that is currently being executed, through which you can get the value of any field of this old row data in the form:

Set @va = old.id//Gets the value of the ID field for the newly inserted update row (provided that there is an ID)　　　　　

　　　　　　

　　　　　　　　

　　　　　　　　

　

PHP Learning 13 database Operation--mysql Programming