mysql-data and practice control language

Tag: Date user starts the address of the message instead of the statement does not perform the basic implementation

first, Data Control Language (DCL)

1, the data control, in fact, is "Assigning permissions"-related to users.

2, the main problem is 2:

User management:

Rights Assignment: What permissions are available.

1. Permissions in MySQL

(1) in MySQL, the authority is the system default of some "noun" (word), about 30, each permission means "what can be done."

(2) Assigning permissions is equivalent to allowing a user to do something.

(3) The main authority is as follows:

2. Users in MySQL

The user information in MySQL is stored in the system database in the user table of MySQL:

(1) Create a 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.

(2) Delete user

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

(3) change user password log in

① Modify your password: set password = password (' new password ');

② change someone else's password (must have Modify permission):

Set password for ' username ' @ ' allow its login address ' = password (' new password ');

3. Permission Assignment (1) Increase the privileges:

grant permission name 1, permission Name 2, .... on database name. Object name to ' user name '@' allow it to log in address ' identified by ' Password ' ;

Description

The ① permission name is: ' SELECT ', ' Update ', ' delete ', and so on. Where all means "all permissions", or all privileges is the same

② Object name: is a database "loaded" things, the table is the most common, can also be views, stored procedures, storage functions and so on. Now ()

Which:*. * represents all objects in all data

a database name. * represents all objects in the database-this is commonly used commercially.

③identified by ' password ' is used for a user to change the password at this time, do not write, then do not change the password.

④ but at the same time the statement can also create a user (if it does not exist), but at this point identified by ' password ' must be written.

(2) 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.

second, transaction control Language (DTL) 1. What is a transaction

Usually, before we say, a statement uses a semicolon (;) to end it and gets executed. Then we say that this "one-time execution" process can be called "a transaction".

Simply put, "an SQL statement is a transaction." : A transaction in a database (naturally including a MySQL database), which means that the execution of "multiple statements" can be seen as an internal mechanism of "one statement".

That is, a "transaction" is a mechanism that guarantees that "multiple statements are executed at once" or "none".

2. Why do I need a business?

See a real-world application requirement (Bank Deposit): Table name Cunkuan

Id	Account name	Deposit
1	Test1	1000
2	Test2	5000
3	Test3	3000

Now, Test1 suddenly need a sum of money, buy IPhone8, to test2 borrow 4000.

Bank Transfer:

First step: Update Cunkuan Set deposit = Deposit -4000 where id=2;

The first step is done, suddenly power off!

Step two: Update Cunkuan set deposit = Deposit +4000 where id=1;

If there is no transaction, this can happen (a tragedy).

If you have a transaction, you can avoid the issue.

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".

3, the characteristics of the business

(1) Atomicity: All statements in a transaction should be done: either wholly or not;

(2) Consistency: Let the data remain logically "reasonable", such as: When a commodity out of the library, both the product in the commodity library to reduce the number of 1, but also the corresponding user's shopping cart in the product plus 1;

(3) Isolation: If multiple transactions execute concurrently, each transaction is executed as if it were performed independently.

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

4, the basic implementation process of the transaction:

(1) Declaration of transaction start: Starttransaction;

(2) Set up 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 statements need to be executed:

If (judging if there is an error) {

Enforcement – Only for the physical changes that have been made-that is, effective.

Commit

}

eslse{

Rollback--no statements are executed.

Rollback

}

　　

(4) Judging whether there are errors are usually divided into these two environments:

① if it is cmd, directly observe if there is an error.

② if it is a PHP program, you need to use the mysql_error () function to determine if there is an error.

Down to use transaction full transfer this thing (assumed to be cmd mode):

Start transaction;

#第一步:

Update Cunkuan Set deposit = Deposit -4000 where id=2;

#第一步做完, suddenly power down?

#第二步:

Update Cunkuan Set deposit = Deposit +4000 where id=1;

If there are no errors in these two statements, you can:

Commit

However, if an error occurs, you can:

Rollback

mysql-data and practice control language