Read "MySQL must know" notes

MySQL must know

2017-12-21

Significance: Record personal unnoticed, or unclear, or unknown details method skills, this book 250 pages

Landing:

Mysql-u root-p-H myserver-p 9999

Show status, used to display extensive server status information

DESCRIBE TableName; is a SHOW COLUMNS from TableName; a quick way to

Show create DATABASE and show create TABLE, respectively, to display the MySQL statement that created the specific databases or tables

Show GRANTS, which displays the security permissions granted to a user (all users or a specific user)

Show errors and show WARNINGS, used to display server errors or warning messages

Help show get more Show related commands

cannot be partially used The distinct,distinct keyword applies to all columns, not just the columns that are pre-built

The in operator is generally faster than the OR operator manifest.

The greatest advantage of in is that it can contain other SELECT statements, enabling more dynamic creation of WHERE clauses

MySQL support causes the in , between, and exists clauses to be reversed with not

Regular :

Although it seems that the% wildcard can match anything, there is one exception, that is, NULL. Even WHERE prod_name like '% ' does not match the line with the value NULL as the product name

regular expression matches in MySQL are case-insensitive and are case- sensitive, using the binary keyword, such as where prod_name REGEXP binary ' JetPack. 000 '

Full-Text Search:

Performs a full-text search using two function match () and against () after specifying fulltext

Want to find out all the notes that refer to anvils. Only one comment contains the word anvils, but you also want to find all the other lines that might be relevant to your search, even if they do not contain the word anvils.

when you use the query extension, MySQL for data and
The index is scanned two times to complete the search:
Q First, a basic full-text search is performed to find all the rows that match the search criteria;
Q Second, MySQL examines these matching rows and selects all the useful words (we will briefly explain how MySQL determines what is useful and what is useless).
Q Second, MySQL searches again for full text, this time not only using the original conditions, but also using all the useful words.
With query extensions, you can find results that may be relevant, even if they do not accurately contain the word you are looking for.

Boolean mode ( booleanmode). Even without Fulltext indexes can also be used , but this is a very slow operation (their performance will decrease as the amount of data increases)

Q to match the words;
Q The word to exclude (if a line contains the word, the row is not returned, even if it contains
The same is true of other specified words);
Q permutation hint (specifies that certain words are more important than others, and that more important words are of higher rank);
Q-expression grouping;
Q Some other content

Matches the word heavy, but-rope* explicitly instructs MySQL to exclude the include rope*

Some important notes for full-text search

ignores single quotes in the word. For example, the don ' t index is don ' t

Full-text search is supported only in the MyISAM database engine (mysql5.6 InnoDB seems to support it)

Select Insert

Does not necessarily require column names to match. In fact, MySQL doesn't even care about The column names returned by SELECT. It uses the position of the column

IGNORE keyword if you update multiple rows with an UPDATE statement and a current error occurs when you update one or more lines in these rows , the entire update The operation was canceled (all rows updated before the error occurred are restored to their original values). To continue updating even if an error occurs, you can use the IGNORE keyword as follows:
UPDATE IGNORE TableName

TRUNCATE actually deletes the original table and re-creates a table instead of deleting the data in the table row by line

Only one auto_increment column is allowed per table, and it must be indexed so that it becomes the primary key.

Q InnoDB is a reliable transaction processing engine (see Chapter 26th), which does not support full-text search;

Q Memory is equivalent to MyISAM in function, But because the data is stored in memory (not disk), it is very fast (especially suitable for temporary table);
Q MyISAM is an extremely high-performance engine that supports full-text search (see Chapter 18th), but does not support transactional processing.

foreign keys cannot cross engine mixing engine types has a big flaw. Foreign keys (used to enforce referential integrity, as described in Chapter 1) cannot span the engine, even if a table with one engine cannot reference a foreign key with a table with a different engine

View:

Views cannot be indexed or have associated triggers or default values.

Stored procedures:

A cursor is a database query stored on a MySQL server that is not a SELECT statement but a result set retrieved by the statement. After the cursor is stored, the application can scroll or browse the data in it as needed.

In this example, we have added another variable named T (the sum of each order is stored). This stored procedure also creates a new table (if it does not exist) in the run, named OrderTotals. This table will save the results generated by the stored procedure. Etch takes each order_num as before, and then uses call to execute another stored procedure (which we created in the previous chapter) to calculate the tax-deductible totals for each order (the result is stored to t). Finally, use Insert to save the order number and totals for each order.

Trigger:

Triggers are supported only by the table and are not supported by the view (temporary tables are not supported).

Other COLLATE clauses of SELECT are used in the ORDER by clause except as seen here , COLLATE can also be used for GROUP by, having, aggregation functions, aliases, and so on.

If absolutely necessary, the strings can be converted between character sets. To do this, use the cast () or convert ()

Show grants for username;

Create user Haogp identified by ' Haogp '

Grant SELECT On *. * to HAOGP;

Revoke SELECT On * * from HAOGP;

Set password for haogp = password (' Haogp ');

Grant SELECT On * * to [e-mail protected] '% ' identified by ' HAOGP ';

If you delete large amounts of data from a table, you should use optimize table to reclaim the space used to optimize the performance of the table.

Important mysqld Command-line options:
Q--help Display Help --a list of options;
Q--safe-mode load minus some of the best configured servers;
Q--verbose Displays full-text messages (used in conjunction with--HELP for more detailed help messages);
Q--version Displays the version information and then exits.

Log:

The main log files are in the following categories.
Q error log. It contains startup and shutdown issues as well as details of any critical errors. This log is typically named Hostname.err, which is located in the data directory. This log name can be changed using the--log-error command-line option.
Q query log. It records all MySQL activities and is useful in diagnosing problems. This log file may quickly become very large, so it should not be used for long periods of time. This log is typically named Hostname.log, which is located in the data directory. This name can be changed with the--log command line option.
Q binary Log. It records all statements that have updated data (or possibly updated data). This log is typically named Hostname-bin, which is located in the data directory. This name can be used--log-bincommand-line options changed. Note that This log file isMySQL5, the previous MySQL version was used in the update log.
Q Slow query log. As the name implies, this log records any queries that are slow to execute. This log is useful in determining where the database needs to be optimized. This log is typically named Hostname-slow.log, bit inDataMesh Record in . this name Word can be in with--log-slow-queries command-line options changed.
Flush logs statements can be used to flush and restart all log files when using the log

 

Main words:

primary key primary key

SQL (pronounced as Letter s-q-l or sequel) is a structured Query language ( structured
Language) abbreviation for

ASC ( ascending) Ascending sort

Read "MySQL must know" notes