MySQL Classification and transaction rollback

Main content :

Data Definition Language DDL Focus

Data manipulation language DML Focus

DQL Focus of data query language

---transaction control language TCL

---database control Language DCL

---primary key (primary key)

---data redundancy (a separate table that links an extranet to the primary key relationship of another table)

---transactions

data Definition Language DDL Focus

Definition: DDL (data definition Language): The language responsible for defining data structures and creating database objects.

The language responsible for defining data structures and creating database objects.

Common with create alter drop TRUNCATE (delete data and automatically rebuild table)

Note : The DDL does not support transactions, and DDL statement operations cannot be rolled back (rollback). Interview Common Test Centers

* * Data manipulation language DML Focus

DML (Data maniplution Language)

role: An instruction that is responsible for manipulating the data in the database. such as: additions and deletions to change the insert Delete Update select.

Note : DML supports transactions, which can be used to roll the rollback when not automatically submitted, and is also the focus of the interview.

data Query Language DQL Focus

DQL (Data Query Language)

definition : An instruction for querying data within a table, including a select command.

As part of a DML is not a transactional matter.

DQL is the most commonly used sentence in the work, interview must test.

# # #TCL

Transaction control Language: the transaction control language. Includes: Commit, rollback, savepoint, rollback to etc.

Assign user data permissions.

# # #DCL

Data Control Language: Controls language

PRIMARY KEY constraint :

Primary key: ...

Constraint: Adds a constraint to a field in a table when the table is created.

Note: The PRIMARY KEY constraint, the saved value must be unique and NOT null .

Format: CREATE table t (ID int primary key,name varchar (10));

INSERT into T VALUES (101, ' Zhangfei ');

INSERT into T VALUES (101, ' Liubei ');

INSERT into T3 values (null, ' Guanyu ');

# # # PRIMARY key + self-increment constraint

---CREATE TABLE t3 (ID int primary KEY auto_increment,name varchar (10));

INSERT into T3 values (null, ' Guanyu ');

select * from T3;
+----+--------+
| ID | name |
+----+--------+
| 1 | Guanyu |
+----+--------+

How do I get the self-increment value zeroed out? Use the TRUNCATE keyword to delete and create a new table

TRUNCATE TABLE T2;

# # # Comment Comment

---you can create a table by describing the field

---format: CREATE table t4 (ID int primary key auto_increment, comment ' This field is the primary key ', ename varchar (ten), Sal int comment ' This is wages ');

Show create table T4;

The difference between # # # ' and '

Create table ' t4 ' (id int, ' age ' int);

' function is used to modify the string's

# ##数据冗余

---due to the unreasonable design of the table, there is a large number of duplicate data, become data redundancy. Save the data that may be duplicated in a new table. Save the data that may be duplicated

A new table, which establishes a relationship by ID, the field of the relationship is called a foreign key.

Exercise two: Please design the table to save the following data: Save the Teaching department of the Department of Java Education teacher information: Cang Teacher Salary 200 age

Then save to the Group headquarters under the sales department, sales department a staff Li Bai salary 50, age 28 years.

1. Creating a Departmental table : CREATE TABLE dept (ID int PRIMARY key auto_increment,name varchar (), parent_id int);

INSERT INTO dept values (NULL, ' group total department ', null);

INSERT INTO dept values (NULL, ' teaching department ', 1);

INSERT INTO dept values (NULL, ' Java Research Department ', 2);

INSERT INTO dept values (NULL, ' Sales department ', 1);

INSERT INTO dept values (NULL, ' Sales Part A ', 4);

2. Create an Employee table: Creation table EMP (ID int primary key auto_increment, name varchar (ten), Sal int,age int,dept_id int);

INSERT into EMP values (NULL, ' Ms. Cang ', 200,18,3), (null, ' Li Bai ', 50,28,5);

# # #事务 (Automatically submit ---> Database , instead manually submit ---> Memory -to-database, enhanced data storage security)

What is a thing? The smallest unit of work that a database executes, putting multiple SQL statements into things can guarantee that multiple SQL will either succeed or fail altogether.

1. Create the person table: the CREATE TABLE person (ID int primary KEY auto_increment,name varchar (ten), Money int);

2. Insert data: INSERT into the person values (null, ' Superman ', ' $ '), (null, ' Iron Man ', 1000);

Transactions seem to have several features: ... (check)

---View the auto-commit status of a database

Show variables like '%autocommit% ';

---Turn off autocommit 0, turn on auto-commit 1;

Set atuocommit=0;

# # #转账验证过程:

1. Turn off auto-commit: Set autocommit=0;

2. Let Superman +300:update person set money=500 where id=1;

3. Open another terminal use DB2 at this time; query data select * from person; (the data in the database is not modified at this time)

4. Go back to the first middle terminal window and let Iron Man -300 update person set money=700 where id=2; (Current window query

Data is queried because the data in memory has not changed)

5. Commit commits manually in the first window, at which time the in-memory SQL execution results are committed to the database

(At this point two terminals change)

# # # Transaction rollback
-rollback:commit the result before the operation.
-savepoint: Sets the rollback point.
---format: savepoint logo;

-rollback to S1; Specifies that rolls to a certain roll point.

Test steps: 1. First the Superman money=100;   2. Put Superman money=200;  3. At this time savepoint S1; 4. Put Superman money=300;

5. Rollback to S1 at this time (data will be rolled to money=200)

# # #事务的应用场景 ( similar to multithreading concurrent execution in Java )

When you do something that requires more than one row of SQL statements, and you require multiple rows of SQL to either all succeed or all fail, you must use the transaction, or the successful partial failure of the SQL section will occur. (Multiple SQL statements do one thing)

# # # Transaction summary :

1. View autocommit Status

Show variables like '%autocommit% ';

2. Modify the submission status

Set AUTOCOMMIT=0/1;

3. Manual Submission

Commit

4. Will roll

Rollback

5. Set the roll point

SavePoint S1;

6. Roll to a certain roll point

Rollback to S1;

MySQL Classification and transaction rollback