The text before

Think of their noon every day to play a mobile phone for an hour, I blame myself, today as a good study book, quite harvest, in the evening to everyone to see, now or thank Mysql, to the back of the feeling more and more difficult!!

The body of the

Mysql transaction

Mysql transactions are mainly used to process data with large operation volume and high complexity. For example, in the personnel management system, you delete a personnel, you need to delete the personnel’s basic information, but also to delete the personnel related information, such as mailbox, articles and so on, these database operation statements constitute a transaction! Simply put, a transaction is a series of operations that you perform. Every time you type an instruction, you’re doing a transaction. By default on the ### Mysql command line, transactions are committed automatically, that is, the COMMIT operation is performed immediately after the SQL statement is executed. Therefore, to explicitly START a TRANSACTION, use the commands begin or START TRANSACTION, or execute the command Set AUTOCOMMIT=0, which disables automatic commit using the current session.

Too specific content temporarily do not say, say something usually used on.

Mysql transaction processing: begin, rollback, COMMIT

  • Begin Starts a transaction
  • Rollback Rollback transaction
  • COMMIT transaction confirmation

Mysql > alter database set autocommit mode

  • Set AUTOCOMMIT=0 disables AUTOCOMMIT, meaning your actions are backed up by default, and you can back up if you need to

  • Set AUTOCOMMIT=1 Enables automatic commit

To sum up, it is better for us to turn on autolift, unless you can be very strong control of yourself to remember to submit, or other needs, otherwise the general learning and practice, the rollback operation is not so desirable. But this thing, it’s like every operation is backed up, and if you can roll back in real time, it’s really nice. So turning autosubmit off seems like a good work habit!! There is also support for marking points, which means that every rollback is just a return to the marking point, rather than throwing away all the previous work

Mysql alter command

The Mysql ALTER command is used when we need to change the name of a table or change the field of a table. Based on previous knowledge, a framework should be established:

Can see from the above that the database is made of the table and the relationship between tables in each table, there are ranks, column attributes: name, serial number, these lines are specific to each person, corresponds to what student id, what name, we use the delete cooperate the where to delete people, with the alter modify column, popular point said, if a table is a student management system, So alter means that a new course is opened, and all students have one more course, and the score is temporarily NULL. If delete is used, it means that a student is opened, which has no effect on other students, but only one line is missing. See below for details:

If you need to specify the position of a new field, you can use the Mysql keyword first, after field name (after field name). The first and after keywords are only used in the ADD clause, so if you want to reset the position of a table field you need to drop the field first and then add the field and set the position using add.

If you need to change the field type and name, you can use the modify or change clause in the ALTER command.

In fact, change can directly not change the name only change the type to modify the effect, right?

Alter TABLE effect on Null and default values

Modify the name of the table

WTF??? Why can’t it be changed? No matter. I’ll just copy the column and delete the original column.

Mysql index

  • 1. Create a unique index or primary key index to ensure the uniqueness of each row in a database table.

  • 2. Index building can greatly improve the data retrieved and reduce the number of rows retrieved from the table

  • 3. The connection conditions in the table connection can accelerate the direct connection between the table and the table

  • 4. In grouping and sorting terms for data retrieval, can reduce the query time in grouping and sorting time consumption (database records will be reordered)

  • 5. Create indexes. Indexes can improve query performance

Alternatively, you can use ALTER to create indexes

alter table table_name add index index_name ON (column(length))
Copy the code

Alter table drop index;

drop index [indexName] ON mytable; 
Copy the code

I thought the index was a pointer, but later I looked it up and realized I was wrong. Index does not exist other actual reference, we simply indexing, one or more columns of individual combined, forming an ordered arrangement, when we need to retrieve our indexed columns, for example, we established the index on the tableid so when we need to use the where to retrieve the tableid, It will first retrieve the index, and then quickly find the address of the row that meets our criteria, and then directly query the information. This process is not visible to us, perhaps when your table reaches a certain point, it will show up in the retrieval time. But the acceleration is truly terrifying, and a 100-fold improvement is no joke.

See mysql-index for more information about indexes

Mysql replication table

If we need to copy the Mysql table completely, including the table structure, index, default values, etc. If you just use create table… The select command is not possible.

  • Use the SHOW create table command to obtain the create table statement, which contains the structure of the original table, indexes, and so on.
  • Copy the SQL statement displayed in the following command, change the name of the data table, and execute the SQL statement to replicate the data table structure.
  • If you want to copy the contents of a table, you can use insert into… Select statement.

The first step:

show create table tableuse;
Copy the code

Step 2: Copy the above output exactly. Then change the table name directly.

Step 3: Copy the data

insert into tableclone (tableid,createtime,Tableuse) select tableid,createtime,Tableuse from tableuse;
Copy the code

See below the tutorial, cry yourself to death: 😭

create table targetTable LIKE sourceTable;
insert into targetTable select * FROM sourceTable;
Copy the code

Mysql metadata

After the body

The party branch secretary of the class take an examination of postgraduate, and then the class members cast dice to decide who will succeed, finally he yao incredibly I top, heartache.

I this protect grind the person that go to outside courtyard, it is monitor, it is graduate set person in charge, it is grade graduate set person in charge, it is grade student union small guy, all sorts of busy work, have a class group team still took an examination of grind team, the BOSS of computer does not seek me to assign a task again, ah, dedicate oneself to my great student work wholeheartedly yao? Bullshit, it doesn’t exist. When I graduate and start whoever wants to do it, I’m leaving next year anyway. I was also a little guilty of the college, took the place to run to another college. Work a little harder and pay it back. If only I were in a good mood!