Database---Data Control language (DCL)

Data Control , in fact, is "Assigning permissions"--involving users.
The main problem is 2:
User management:
Permission Assignment:
What permissions are available.
Permissions in MySQL
In MySQL, permissions are the system's default number of "nouns" (words), about 30, and each permission represents "what can be done."
Then assigning permissions is the equivalent of letting a user do what they can.
The main permissions are as follows:

users in MySQL : The user information in MySQL is stored in the users table of the system database MySQL.
Create user
Grammatical form:
　　Create user ' username ' @ ' allows its login address ' identified by ' password ';
Description
　　1, the created user needs to specify at which address the user can log on at the same time.
　　　　Where "%" stands for "any address".
　　2, after the user is created, a record is automatically added to the MySQL user table, but that person does not yet have permission .

Delete User
　　Drop user ' username ' @ ' allow its login address ';

Modify user password
Modify your password: set password = password (' new password ');
Modify someone else's password (must have Modify permission):
set password for ' username ' @ ' allow it to log in address ' = password (' New password ') ;

Permission Assignment
Add Permissions:
　　Grant permission name 1, permission Name 2, .... on database name. The object name to ' user name ' @ ' allows its login address ' identified by ' password ';
Description
The 1 permission names are: ' Select ', ' Update ', ' delete ', and so on. Where all means "all permissions", or all privileges is the same
2 Object name: is a database "loaded" things, the table is the most common, can also be views, stored procedures, storage functions and so on.
Which:*. * represents all objects in all data
　　　　A database name. * represents all objects in the database-this is commonly used commercially.
　3,identified by ' password ' is used for a user to change the password at this time, do not write, then do not change the password .
4, but the statement can also create a user (if it does not exist), but at this point identified by ' password ' must be written.

Delete permissions
　　Revoke permission name 1, permission Name 2, .... on database name. Object name from ' username ' @ ' allow it to log in address ';
means "Cancel" some of the permissions (and perhaps other permissions) from a user.

transaction Control Language (DTL)
What is a transaction
Normally, before we say, A statement uses a and get executed.
Then we say that this "one-time execution" process can be called a "transaction."
Simply put, "an SQL statement is a transaction."
Then:
The transaction in the database (naturally including the MySQL database), which means that "multiple statements" can be executed as a "statement" to see an internal mechanism.
that is, a "transaction" is a mechanism that guarantees that "multiple statements can be executed at once" or "none". A
transaction can be thought of as a "container," in which multiple statements are placed into the container, and finally, as long as a command line is used to determine whether all statements in it are "executed"

Features of the transaction
　　atomicity: All statements in a transaction should be done either wholly or not;
　　Consistency: Let the data remain logically "reasonable", for example: When a product is out of the library, it is necessary to reduce the number of goods in the commodity library by 1, but also the corresponding user's shopping cart in the product plus 1;
　　Isolation: If multiple transactions execute concurrently, each transaction is executed as if it were performed independently.
　　Persistence: When a transaction executes successfully, it should be a clear drive data change (not just a change in memory) for the data.

Transaction Mode: is the setting to see if each execution statement is treated as a "transaction".

After MySQL is installed by default, its transaction pattern is : A statement as a transaction.
The basic implementation process of a transaction:
1, DECLARE transaction start: Starttransaction;
2, set a number of specific statements to execute, such as: INSERT, UPDATE, delete, select ... In fact, execution, just the statements that are executed, do not "take effect"-it is only execution in the memory state, not the execution of the physical state.

3, determine if the statement needs to be executed    :if(judging if there is an        error) {// Execute--only for the physical changes performed-that is, effective.          commit;    }    eslse{        // rollback -no statements are executed.          rollback;    }            

Judging whether there are errors is usually divided between the two environments:
1, if it is cmd, directly observe whether there is an error.
2, if it is a PHP program, then you need to use the mysql_error () function to determine whether the error.
A transaction has more than one statement, if all is correct, then commit, if there is any one or more than one of the errors, then rollback;

MySQL Programming
Basic grammatical form
Statement block mode:

In MySQL programming, Begin....end is basically replacing the {...} in the original programming statement. Grammar.
But there's a difference:
　　　　A bigin...end; block that can be given an "identifier", and you can use the Leave statement to "exit" the statement block.
Basic Flow Control Statements:
If statement:

Case statement: (similar to switch in PHP)

While Loop statement:

variables in MySQL
Variable It has a certain usage scenario limit (in fact, including the previous Process Control statement):
In the usual "environment" of statements such as executing various create,insert,delete,update,select, you do not use a regular variable or use a Process Control statement.
In fact, these statements can only be used in the programming environment-the usual additions and deletions, can be called the command environment.　　
general mysql command environment, can be performed: additions and deletions
programming Environment: stored functions, stored procedures, triggers.
Declaration syntax for common variables:
Declare variable name variable type [default initial value]
Variable assignment syntax:
set variable name = variable value
mysql there is also a variant form, can be referred to as a "session variable"
Session variable in the form of: @ variable name,
Session variable without a separate declaration, but directly assigned to, similar to the assignment of PHP
Session variables: Set Variable name = value; The
Session variable can be used in a normal command-line environment or in a programming environment.

In Summary: Syntax 1:Set Variable name = Expression ; #此语法中的变量必须先use declare declaration
Syntax 2:SET @ variable name = Expression , #此方式可以无需declare语法声明, but direct assignment , similar to PHP defining variables and assigning values.
Syntax 3:SELECT @ variable name: = Expression , #此语句会给该变量赋值, and output result set as a SELECT statement.
Syntax 4:select expression into @ variable name , #此语句虽然看起来是select语句, but does not output "result set", but only assigns a value to the variable.

Summarize---Two variables:
　　normal variable: without the @ character, directly write the name, must first declare, after use (assignment, etc.), only for the programming environment
　　Session Variables: with the @ character, no declaration, direct use (first assignment), can be used in 2 environments.

Storage functions
Invocation form:
As with the call form of a system function, the name is used directly, with parentheses, and with arguments as needed.
Like what:
Select 3, now (), F1 ();//f1 is a custom storage function.
Select 3, now (), F2 (3, ' abc ', @v1);//with 3 arguments, where @v1 is a previously assigned variable.
Delete: Drop function [if exists] the name of the stored function;
Stored Procedures
A stored procedure is a "function" that does not return a value for a data value.
Although a stored procedure does not return a single data value, it can "return" the result set-the return starting here is the normal result of the SELECT statement.
Definition form:

which
In,out,inout is used to modify the "Data flow" of a formal parameter:
In: Only as external input data, the calling environment is passed inside the stored procedure, the default value.
Out: Only as output data, that is, the stored procedure is outgoing to the calling environment.
InOut: With dual.
Stored Procedure Call form:
　　Call stored procedure name (argument 1, Argument 2, ...). ）；

Delete: drop procedure [if exists] stored procedure name;

Trigger
What do you mean trigger:
is a "trigger" machine (mechanism) in MySQL.
It's just a pre-defined piece of code. This code does not need to be called manually, but will be executed automatically in a "expected" good situation.
This is usually the case:
　　before (or after) the increase (or deletion or modification) of a data table. There are only 6 scenarios visible for each table .

The inside of a trigger is the same as a stored procedure or stored function, which belongs to the programming environment.
Definition form:

 for  each row begin    The code to execute, the statement block, the programming environment, but you cannot use the SELECT statement here either.  End;

Inside the trigger, there are two keywords with a specific meaning and data acquisition:
NEW: Represents a newly inserted piece of data that is valid at the time of the Insert event.
Old: Represents an older piece of data that is valid at update or delete.

Delete trigger: drop TRIGGER Trigger name

Database---Data Control language (DCL)