The environment

CMD command line

MySQL 8.0.25





GROUP BY GROUP

GRUOP BY can be grouped according to the items you specify, remember our my_table2:

What if I want to see how many times different poets appear in my_table2? (Or how many poets’ works are in my_table2?) In this case we can useSELECTClauseGROUP BY:

SELECT my_author, COUNT(*) FROM my_table2 GROUP BY my_author;

It can be seen from the table that Li Bai and Su Shi each have two works, while others only have one.





Group statistics WITH ROLLUP

WITH ROLLUP allows us to perform statistics based on grouping (SUM,AVG,COUNT).

For example, use:

SELECT my_author, SUM(my_id) AS id_count FROM my_table2 GROUP BY my_author WITH ROLLUP;
Copy the code

SUM(my_id) AS id_count = id_count

Even though it doesn’t make sense, we added up the ids of the same my_author and summed them up, NULL representing the sum of all author ids.

But it’s still too ugly. We can use coalesce:

SELECT coalesce(my_author, 'total number of ids '), SUM(my_id) AS ID_count FROM my_table2 GROUP BY my_author WITH ROLLUP;Copy the code





Filter Group HAVING

HAVING can be used to filter groups BY adding conditions, similar to WHERE except that HAVING can be used in groups.

SELECT my_author, SUM(my_id) AS id_count FROM my_table2 
GROUP BY my_author HAVING SUM(my_id) <= 10;
Copy the code





Connect the JOIN

Remember we have my_table2 and my_table3: JOINIn theSELECTJoin different tables under the query clause and join the query results horizontally (instead of vertically).

JOIN (my_table2, my_table3) JOIN (my_table2, my_table3)

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author
FROM my_table2 a JOIN my_table3 b ON a.my_id = b.my_id;
Copy the code

Here we refer to my_table2 as a and my_table3 as B, and print the columns a.my_id, a.m.TITLE, a.m.author, B.M.title, b.M.author. The condition is to take the ones in A (my_table2) and B (my_table3) whose ids are equal.

Here ON and WHERE are the same, so it’s the same if you switch:

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author
FROM my_table2 a JOIN my_table3 b WHERE a.my_id = b.my_id;
Copy the code

JOIN is the same as INNER JOIN

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author
FROM my_table2 a INNER JOIN my_table3 b WHERE a.my_id = b.my_id;
Copy the code

You can even use a comma instead of a JOIN:

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author 
FROM my_table2 a, my_table3 b WHERE a.my_id = b.my_id;
Copy the code

SQL4 find all employees that have been assigned to a department

SQL4 find all the employees who have been assigned departments





Set LIMIT to LIMIT

LIMIT can LIMIT the number of queries in SELECT, for example, I only want the first data in my_table2:

SELECT * FROM my_table2 LIMIT 1;

Or I want the 1st through 3rd data (counting from 0 when used as an index, with no right margin) :

SELECT * FROM my_table2 LIMIT 0,3;





A NULL value processing

My_table2 submission_date does NOT define NOT NULL, insert NULL at this position:

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" 下 载 ", "下 载 ", NULL);Copy the code

Select * from table where column IS NOT NULL;

SELECT * FROM my_table2 WHERE submission_date IS NULL;
Copy the code

Successfully found the row that just joined.

You can also find non-null rows:

SELECT * FROM my_table2 WHERE submission_date IS NOT NULL;
Copy the code





Update the table definition ALTER

The ALTER TABLE command can be used to update the TABLE definition

Remember that my_table2 looks like this:

We can ALTER TABLE to remove submission_date column

ALTER TABLE my_table2 DROP submission_date;
Copy the code

Look at my_table2, submission_date is deleted.

Add submission_date back:

ALTER TABLE my_table2 ADD submission_date DATE;
Copy the code

We can set the default value:

ALTER TABLE my_table2 ALTER submission_date SET DEFAULT '2021-07-10';
Copy the code

You can’t see it here,ALTERJust changed the definition:

DESC to view the definition of the table:

DESC my_table2;

You can see that the default value is changed to ‘2021-07-10’. So let’s get rid of that again. We can DROP DEFAULT

ALTER TABLE my_table2 ALTER submission_date DROP DEFAULT;
Copy the code

You can also change the engine, but I won’t show you if I don’t:

ALTER TABLE my_table2 ENGINE = MYISAM;
Copy the code

You can also change the type:

ALTER TABLE my_table2 MODIFY submission_date YEAR;
Copy the code

Change the type back to:

ALTER TABLE my_table2 CHANGE submission_date submission_date DATE;
Copy the code

You can also change your name:

ALTER TABLE my_table2 RENAME TO my_table4;
Copy the code

And then I changed it back





Transaction START BIGIN/START

The deep meaning of transactions is not mentioned here, but can be simply understood as a series of operations on data packaging. The operations in a transaction either execute together with no interruption at all, or none at all (atomicity).

You can use either BEGIN or START TRANSACTION to START a TRANSACTION.

We start a transaction:





Transaction ROLLBACK

Transaction ROLLBACK ROLLBACK or ROLLBACK WORK (both are the same) allows us to go back to the previous statement,

We just started the transaction, now let’s modify the data:

UPDATE my_table2 SET my_title = "UPDATE my_table2 SET my_title =" WHERE my_id = 6;Copy the code

Let’s change the sixth poem from “Yearning for a Slave · Chibi remembering the past” to “Jiangchengzi · Michigan hunting”. Check it out:

Ok ~

ROLLBACK allows us to go back to before executing the UPDATE command by typing ROLLBACK:

You can see that our sixth poem has changed to “Niannujiao · Chibi is nostalgic for the past”.

Note: ROLLBACK only rolls back INSERT, UPDATE, and DELETE statements. After we just called rollback, the SELECT operation will not be rolled back, but will be ignored automatically to find the UPDATE rollback prior to SELECT. (There’s no point in rolling back SELECT.)

I changed my mind again. I still want to change the sixth poem to “River Chengzi” :

UPDATE my_table2 SET my_title = "UPDATE my_table2 SET my_title =" WHERE my_id = 6;Copy the code





Transaction COMMIT COMMIT

COMMIT transactions with COMMIT or COMMIT WORK (both are the same).

As you can see, our first change was rolled back and removed. But the second change worked.

After a TRANSACTION is committed, operations are actually performed (unless an error occurs) from BEGIN or START TRANSACTION to COMMIT TRANSACTION, resulting in permanent changes to the database.





The SAVEPOINT SAVEPOINT

A SAVEPOINT, like a game save, marks a SAVEPOINT in a transaction. If we are not satisfied with the changes made after the SAVEPOINT, we can simply ROLLBACK back to the previous SAVEPOINT.

BEGIN Starts another transaction:

Go ahead and change my_table2:

But before we do that we record a SAVEPOINT save1; , named save1:

Then we insert a new character:

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" 新 年 完 全 ", "新 年 完 全 ", NOW());Copy the code

One more:

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" 新 月 ", "新 月 "," 新 月 ", NOW());Copy the code

Save another savepointSAVEPOINT save2:

Li Shangyin’s poem is too difficult and I don’t want to see him anymore. At this point, you can go back to the save point save1:

ROLLBACK TO save1;
Copy the code

Yes, we are back to the time without Li Shangyin.

But later I changed my mind. I still think we should bring Li Shangyin back, so I rolled back TO save2.

No, we can’t go back.

Because when I go back to the first save1, according to the timeline, save2 hasn’t appeared yet, so it really hasn’t! Therefore, the backtracking of savepoints will make all savepoints after the backtracking time disappear automatically.

Savepoints disappear automatically when a COMMIT commits a transaction. You can also actively RELEASE savepoints by releasing SAVEPOINT.

Let’s commit the transaction first.





Automatically submit

Without a transaction, all instructions we enter will automatically COMMIT by default.

But we can turn autosubmit off (until autosubmit is turned back on again) :

SET autocommit=0;

SET autocommit=0/1; Connection specific, not server specific.





The INDEX INDEX

Indexes can be created like this:

CREATE INDEX index_id ON my_table (my_id);
Copy the code

We can also ALTER TABLE ADD INDEX to ADD a normal INDEX

ALTER TABLE my_table2 
ADD INDEX index_id (my_id);
Copy the code

Add full-text index:

ALTER TABLE my_table2 
ADD FULLTEXT index_title (my_title);
Copy the code

SHOW INDEX FROM my_table2; To view the index:

You can see that a primary key is itself a kind of index, a primary key index.

The index can be viewed through EXPLAIN:

EXPLAIN SELECT * FROM my_table2 WHERE my_id =1;
Copy the code

Or print vertically

EXPLAIN SELECT * FROM my_table2 WHERE my_id =1 \G;
Copy the code

In addition, dropping indexes can be done (I won’t demonstrate) :

DROP INDEX index_id2 ON my_table2;

EXPLAIN contains information: table: Shows which table this row is about

  1. Type: This is the important column that shows what type the connection is using. The best to worst connection types are const, eq_reg, ref, range, Indexhe, and all

  2. Possible_keys: Displays possible indexes that can be applied to this table. If empty, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain

  3. Key: indicates the actual index. If null, no index is used. In rare cases, mysql will select an index that is underoptimized. In this case, use index (indexName) in the SELECT statement to force the use of an index or ignore index (indexName) to force mysql to ignore the index

  4. Key_len: the length of the index used. With no loss of accuracy, the shorter the length, the better

  5. Ref: Shows which column of the index is used, if possible, as a constant

  6. Rows: The number of rows that mysql deems necessary to check to return the request data

  7. Extra: Additional information about how mysql parses queries. We’ll discuss this in Table 4.3, but the bad examples you can see here are using temporary and using filesort, which means mysql can’t use indexes at all, resulting in slow retrieval

The meaning of the description returned by the extra column:

  1. Distinct: Once mysql finds a joint match with a row, it no longer searches

  2. Not exists: mysql optimizes left Join and does not search once it finds a row that matches the Left Join standard

  3. Range checked for each record (index map:#) : No desired index was found, so for each combination of rows from the previous table, mysql checks which index to use and uses it to return rows from the table. This is one of the slowest connections using an index

  4. Using filesort: When you see this, the query needs to be optimized. Mysql takes additional steps to discover how to sort the rows returned. It sorts all rows based on the connection type and the row pointer that stores all rows with sort key values and matching conditions

  5. Using Index: Column data is returned from a table that only uses the information in the index without reading the actual action. This occurs when all columns requested for the table are part of the same index

  6. When you see this, the query needs to be optimized. In this case, mysql needs to create a temporary table to store the results, which usually happens to order by on different sets of columns rather than group by

  7. Where used uses the WHERE clause to restrict which rows will match the next table or be returned to the user. This can happen if you do not want to return all rows in the table and the join type is all or index, or if the query has a problem explaining the different join types (in order of efficiency)

  8. The system table has only one row: the SYSTEM table. This is a special case of const connection types

  9. Const: The maximum value of a record in the table that can match the query (index can be primary key or unique index). Because there’s only one line, this value is actually a constant, because mysql first reads this value and then treats it as a constant

  10. Eq_ref: In a join, mysql reads a record from the previous table for each record union during a query, which is used when the entire query uses the primary key or unique key of the index

  11. Ref: This join type occurs only when the query uses keys that are not unique or primary keys or parts of those types (for example, using the leftmost prefix). For each row union of the previous table, the entire record is read from the table. This type depends heavily on how many records are matched against the index – the fewer the better

  12. Range: This connection type uses an index to return rows in a range, such as what happens when you use > or < to look up something

  13. Index: This join type does a full scan of each record union in the previous table (better than all because indexes are generally smaller than table data)

  14. All: This connection type does a full scan for each previous record union, which is generally bad and should be avoided

Thank you bosses here www.cnblogs.com/linjiqin/p/… .

Create unique index:

CREATE UNIQUE INDEX index_id ON my_table(my_id);
Copy the code





Viewing execution Time

You need SHOW PROFILES; If profile is enabled, the database is disabled by default. The profiling variable is a user variable that must be re-enabled each time:

It’s easy, just set it to 1:

SET PROFILING = 1;
Copy the code

SHOW VARIABLES LIKE "%PRO%";
Copy the code

You can see it’s on.

We call SHOW PROFILES;

You can view the time.





reference

MySQL > MySQL

MySQL Must Know must Know

www.cnblogs.com/linjiqin/p/…

www.cnblogs.com/youmingkuan…