This article has been included in the 1.1K Star number open source learning guide — “DACHang Interview refers to north”, if you want to know more about the dachang interview related content and obtain the offline PDF version of “Dachang Interview refers to North”, please scan the following QR code to follow the public account “Dachang interview”, thank you!
Dachang Interview refers to the North
Notfound9. Making. IO/interviewGu…
Project Address:
Github.com/NotFound9/i…
To obtain the offline PDF version of DACHang Interview guide, please scan the qr code below to follow the public account “Dachang Interview”.
Screenshots of DACHang Interview Pointing north Project:
Why write this article
Because I am close to some of the recent work in data analysis and comparison, so need to use more proficient SQL, so read the MySQL will know will this book, to examine their reading effect and help some like I need to learn the knowledge of MySQL related friend, so every read a chapter, I began to write a chapter of reading notes, And post boiling points in the Nuggets’ reading notes section. There are thirty chapters in total. After all the chapters are published, they are summarized into this article. Interested friends can read this article to get a quick preview of the book and learn MySQL related knowledge. If you think this book is helpful to you, I hope you can click on my attention, and I will continue to read other technical books in the future, and organize them into reading notes to share with you. At the same time, welcome to add my homepage wechat, we discuss learning together.
Chapter 1: Understanding SQL
This chapter mainly introduces some database-related concepts:
Database: Container for holding organized data.
Table: A structured list of data of a particular type.
Schema: Information about the layout and features of databases and tables. In MYSQL, schemas are synonymous with databases.
Primary key: The column that uniquely identifies each row in the table is called the primary key. A column as a primary key must satisfy the following conditions:
1. Uniqueness. No two rows have the same primary key value.
2. Not empty. Each row must have a primary key value.
Chapter 2 introduction to MySQL
DBMSS (database management software) generally fall into two categories:
1. DBMS based on shared file system. Commonly used on the desktop (such as Microsoft Access and FileMaker)
2. Client – Sever DBMS. Everyday MySQL, Oracle, and SQL Server databases are of this type. The Client interacts with the user, receives the user’s instructions, and sends requests to the Server. The Server accesses and processes data, and then returns the results to the Client.
Chapter 3 using MySQL
This paper mainly introduces some MySQL commands
use crashcourse; Select a database named CrashCourse (when connecting to a database from the command line, we need to select a database before we can proceed)
show DATABASES; // Show Tables; Show COLUMNS FROM customers; // Show COLUMNS FROM customers; // Show all the column information of the Customers table (including field names, types, whether NULL is allowed, key information, default values, and other information),Copy the codeDESCRIBE customers; Same as show COLUMNS FROM, used to display the column information of a table
SHOW STATUS; SHOW CREATE DATABASE crashcourse;Copy the codeIn the same way that SHOW CREATE TABLE shows the SQL statements used to CREATE the crashCourse database, SHOW CREATE TABLE also shows the SQL statements used to CREATE a TABLE
SHOW GRANTS FOR 'jeffrey'@'localhost'; // Display Jeffrey account permissions SHOW ERRORS and SHOW WARNINGS, // display server error or warning messagesCopy the codeHELP the SHOW; When you are not familiar with a command, you can use HELP+ to get some information about what the command is used for. Here HELP SHOW prints the usage of the SHOW command
Chapter 4 Data Retrieval
These two chapters focus on queries.
Default data order when querying:
SELECT prod_name FROM products;
Copy the codeIf you do not set any sort criteria, the order in which the data is returned is based on the order in which it appears in the underlying table (this can be the order in which the data was originally added to the table, but if the data has been updated or deleted, the order is affected by MySQL reuse of reclaimed storage space).
Use DISTINCT de-duplication:
SELECT DISTINCT vend_id FROM products;
Copy the codeIf you want to return data that does not contain duplicate values, you can use DISTINCT to modify the column
SELECT DISTINCT vend_id, prod_price FROM products;Copy the codeThe DISTINCT keyword modifies all columns and excludes only if all columns are identical. In the above example, only vend_id and prod_price are the same, so some vend_id is allowed to be identical. Prod_price different data appears.
Use limits to LIMIT the results
SELECT prod_name FROM products LIMIT 5;Copy the codeYou can limit the number of returns to five
SELECT prod_name FROM products LIMIT 4,5;Copy the codeYou can limit the data returned from line 4 to five
MySQL 5 will support another, more understandable, way to write this query
SELECT prod_name FROM products LIMIT 4, offset 5;Copy the codeUse fully qualified table names
SELECT products.prod_name FROM crash_course.products;
Copy the codeYou can restrict a query to a table in a database, as in the example above, you must fetch the prod_NAME column in the Products table of the crash_course database
Chapter 5 sorting retrieval data
ORDER BY; ORDER BY; ORDER BY;
Use ORDER BY for sorting
SELECT prod_name FROM products ORDER BY product_name;
Copy the codeSort by multiple columns
SELECT prod_id， prod_price, prod_name FROM products ORDER BY product_price, product_name;
Copy the codeSpecified in the ORDER BY multiple fields may, in accordance with the provisions of the ORDER, according to the multiple column sorting, examples of data will be from low to height were sorted according to product_price does, if product_price does the same, then product_name, ordered BY comparison from A to Z, As shown in the figure below
Specify sort direction
The default sort direction is ascending, that is, ASC. Sometimes you need to sort in descending order. For example, to sort prices from highest to lowest, you can use descending DESC
Chapter 6 Data Filtering
This chapter is really about WHERE statements that filter data.
Conditional judge
Some of the common WHERE statement condition judges are already known. Such as:
= equal to! = not equal to < less than > greater than <= Less than or equal to >= greater than or equal to BETWEEN BETWEEN two specified values In addition to the above, there is a less common operation symbol <>, which stands for not equal to, and! Synonymous =Copy the code! = and IS NULL
! = returns rows that do not have a particular value. NULL means unknown, so NULL is not compared to a particular value, so no rows that have a NULL value. If you want to get a row with a NULL value, you must use IS NULL for example:
SELECT * FROM table WHERE value! = 100;
| id | value |
|---|---|
| 1 | 100 |
| 2 | NULL |
| 3 | 200 |
Result returned:
Only the row with value 200 is returned, not the row with value NULL
| id | value |
|---|---|
| 3 | 200 |
BETWEEN
Using the BETWEEN operator matches all values in the range, including the specified start and end values
Such as:
Chapter 7 Data Filtering
This chapter focuses on the four operators AND, OR, IN, AND NOT.
Calculate the order
When combined with AND AND OR, the condition on both sides of AND will be extracted because the AND operator will be processed first when the highest priority of AND is computed. Therefore, the above SQL statement is actually equivalent to
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR (vend_id = 1003 AND prod_price >= 10);
Copy the codeIt’s gonna be a little bit different than what we want, which we want
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR (vend_id = 1003 AND prod_price >= 10);
Copy the codeWe want vend_id 1002 or 1002, and prod_price > 10, so in everyday use, it is better to explicitly group the corresponding operators with (), rather than relying on the precedence of the operators, as follows:
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
Copy the codeThe IN operator
The OR operator
Both IN and OR satisfy the requirements when specifying a range of conditions for matching, but IN has some advantages:
1. The syntax of the IN operator is more concise, intuitive and easy to manage
2. The IN operator executes faster.
3. The biggest advantage of IN is that it can include other SELECT statements, which makes it possible to dynamically resume WHERE statements, as discussed IN Chapter 14.
The NOT operator
Other DBMSS allow the use of NOT to negate various conditions, but IN MySQL, NOT is only supported to negate IN, BETWEEN, and EXISTS clauses.
Chapter 8 Filters with wildcards
This chapter mainly introduces the LIKE operator and the two wildcards % and _.
The LIKE operator
LIKE is used primarily in conjunction with wildcards, and the LIKE operator is used to search for a specified pattern in a column in a WHERE clause.
% wildcards
% represents 0, 1, or more characters at a given position in the search pattern.
The tail Spaces
May interfere with wildcard matching. For example, when saving the word anvil, if it is followed by one or more Spaces, the clause WHERE prod_name LIKE ‘%anvil’ will not match them because there are extra characters after the last L. A simple solution to this problem is to append a % to the end of the search pattern. A better approach is to use functions (covered in Chapter 11) to remove the leading and trailing whitespace. Note that NULL although it seems that the % wildcard can match anything, there is one exception: NULL. Even WHERE prod_name LIKE ‘%’ does not match a row with the value NULL as the product name. The underscore (_) wildcard _ is similar to the % wildcard, except that it can only match a single character, not zero characters, or multiple characters
The technique of using wildcards
1. Try not to use wildcards in requests where other operators are available, because their search events are longer than those of other operators
2. Try not to use wildcards at the beginning of a search pattern. Placed at the beginning of the search pattern, the search is the slowest.
3. Check the wildcard positions. If misplaced, the desired data may not be returned.
Chapter 9 uses regular expressions to search
This chapter mainly talks about the knowledge related to regularity. I personally think it would be better to learn regularity as a separate technology, so I suggest you learn it specifically
Chapter 10 Using computed fields
In some cases, the data stored in the table is not what we need, and we need to transform, calculate, or format it, which is what the calculated fields are for.
Concatenate fields using the Concat() function
Most DBMS using + or | | to implement stitching, MySQL is using Concat () function to splice was carried out on the field. Concat() can concatenate multiple strings into one, as shown below:
Use the Trim() function to remove whitespace
The Trim() function removes whitespace from the left and right sides of a string. The LTrim() function removes whitespace from the left side of a string
Use the alias
Concat() is used to concatenate a field without a name. You can use the AS keyword to give it a name. If an existing field contains an invalid character, the AS keyword can alias an existing field.
Perform arithmetic calculations
In addition to using the Concat() function to get a computed character, you can also use +, -,, / to get a computed field. As shown in the figure:
Expanded_price is listed as a calculated field, which is made up of quantity
Chapter 11 uses data processing functions
In addition to using SQL statements to process data, you can also use functions to process data. It is important to note that functions are not as portable as SQL.
Text processing function
Use case: This is a case of uppercase text processing using the Upper() function
These common functions are probably understandable except for Soundex(), which is an algorithm that converts any text string into an alphanumeric pattern describing its phonetic representation.
Common date and time handlers
Numerical processing function
Chapter xii Summary data
In daily development, in addition to obtaining the retrieved data, we can also use the aggregation function to summarize the data and get the results after processing.
SQL aggregate function
AVG () function
AVG() computes the average of a particular column, ignoring NULL columns.
The COUNT () function
Two uses: 1. COUNT(*) is used to COUNT the number of rows in a table, regardless of whether the table columns contain NULL or non-null values. 2. Use COUNT(column) to COUNT rows with values in a specific column, ignoring NULL values.
Chapter 13 Grouped Data
This chapter mainly describes how to use GROUP BY to GROUP data.
Create a group
Packet filtering
If you want to filter groups, you can use the WHERE statement to filter the data in the table and then GROUP it with GROUP BY, or you can use the HAVING statement to filter out groups after GROUP BY. Such as:
| id | value |
|---|---|
| 1 | 100 |
| 2 | 150 |
| 3 | 200 |
| 3 | 500 |
For the above table, if we wanted to filter out the group with id 3, we could write it as a WHERE statement:
SELECT id, COUNT(*) FROM table WHERE id! = 3 GROUP BY id;Copy the codeIt can also be written as HAVING:
SELECT id, COUNT(*) FROM table GROUP BY id HAVING ID! = 3;Copy the codeOf course, HAVING is more powerful than WHERE in filtering groups. For example, if we want to group data and get groups of more than 2, WHERE cannot be implemented. As follows:
Grouping and sorting
After grouping data, the output groups are usually in ascending ORDER from A to Z, but the SQL specification does not specify this, so it may not be in ORDER. You can use ORDER BY to sort the groups in ascending or descending ORDER.
SELECT clause order
When using these statements, they should be written in the order shown in the table below
Chapter 14 using subqueries
The subquery is the condition of the WHERE clause
Sometimes a SINGLE SELECT statement does not satisfy our requirements. We can implement complex queries by applying the results of one SELECT statement to the WHERE clause of another SELECT statement.
For example: We want to get the names and contact information of all customers who ordered the item TNT2: this can be done with a complex query as shown in the following figure:
(1) Query the number of all orders containing item TNT2. (2) Query all customer ids according to the order number. (3) Query the name and contact information according to the customer ID.
Using subqueries in the WHERE clause can make for powerful and flexible SQL statements. There is no limit to the number of subqueries that can be nested, although in practice performance constraints prevent too many subqueries from being nested.
Points to note:
1. There is no limit to the number of subqueries that can be nested, but in practice, due to performance limitations, too many subqueries cannot be nested.
2. Columns must match. When using a subquery in a WHERE clause, ensure that the SELECT statement has the same number of columns as in the WHERE clause. Typically, a subquery will return a single column and match a single column, but multiple columns can be used if desired.
3. Subqueries are generally used IN conjunction with the IN operator, but can also be used to test for equal (=), not equal (<>), and so on.
Subquery results are used as calculation fields
For example, if we want to obtain customer information and order number at the same time, we can use sub-query to achieve this, as shown in the figure below:
Chapter 15 Join table
Sometimes a query against a single table does not meet our requirements, and we need to join multiple tables and return a set of output. A connection is not a physical entity, but is established at query time.
The cartesian product
If no WHERE condition is specified when a join query is performed, the result returned is a Cartesian product that pairs the number of rows in the first table with all the rows in the second table, resulting in the total number of rows in the first table multiplied by the number of rows in the second table.
WHERE conditions
If the WHERE condition is specified, the result is filtered by filtering the results of the Cartesian product based on the condition. For example, in this example, the vend_id of the Vendors table is specified to be equal to vend_id of the Vendors table as a filter condition, so that the result of the join is to find matching data from the Vend_id of vendors table in the Products table.
Equivalent connection (internal connection)
The above type of join is actually equivalent join, can be written in the syntax of join, can be more explicit join type
Join multiple tables
There is no limit to the number of tables that a SELECT statement can join. Multiple tables can be joined for query purposes
Use joins instead of subqueries
Multi-table queries that were previously nested by subqueries can now be implemented using joins
Chapter 16 Creating high-level joins
This chapter explains outer joins and how to use table aliases and aggregation functions on joined tables.
Using table aliases
You can alias tables as well as columns and computed fields. The main benefits are as follows:
1. Shorten SQL statements (some table names are too long, you can use short aliases)
2. Allow the same table to be used more than once in a single SELECT statement.
In addition to the internal connection (equivalent connection) mentioned in the previous chapter, there are three kinds of connection: self-connection, natural connection and external connection:
Since the connection
Self-join means that a table joins itself for information query. For example, an item (with ID DTNTR) has problems, and you want to know if the supplier that produced the item has problems with other items. This query requires that you first find the vendor that produces the item with ID DTNTR, and then find out what other items that vendor produces. Self-wired implementations can be used:
The two tables needed in this query are actually the same tables, so the Products table appears twice in the FROM clause. Although this is perfectly legal, references to Products are ambiguous, so use table aliases to avoid ambiguity. Of course, to solve the above query requirements can also be used to achieve the sub-query, as shown in the following figure:
Outer join
An inner join associates rows in one table with rows in another table, sometimes including rows that do not meet the conditions of the association. This is called an outer join. Such as:
Join aggregation functions can also be used in conjunction with joins.
Matters needing attention:
1. Note the type of join used. We usually use inner joins, but using outer joins is also valid.
2. Ensure that the correct join conditions are used, otherwise incorrect data will be returned.
3. Join conditions should always be provided, otherwise cartesian products will be obtained.
4. You can have multiple tables in a join, and you can even use different join types for each join. While this is legal and generally useful, you should test each join separately before testing them together. This will make troubleshooting easier.
Chapter 17 Combined Query
In MySQL, multiple query statements can be executed, and multiple result sets can be combined into a single query result set using the UNION statement. The application scenarios are as follows: 1. In a single query, data with similar structure is returned from different tables. 2. Perform multiple queries on a single table to combine result sets into a single result set.
The use of the UNION
For example, we need a list of all items with a price less than or equal to 5, and we also want to include all items made by suppliers 1001 and 1002 (regardless of price),
This statement consists of the previous two SELECT statements separated by the UNION keyword. UNION instructs MySQL to execute two SELECT statements and combine the output into a single query result set. This requirement can also be implemented using multiple WHERE statements.
Matters needing attention:
1. The UNION must consist of two or more SELECT statements separated by the UNION key (therefore, if you combine four SELECT statements, you will use three UNION keywords).
2. Each query in the UNION must contain the same column, expression, or aggregate function (except 3. Column data types must be compatible: the types do not have to be identical, but they must be types that the DBMS can implicitly convert (for example, different numeric types or different date types). If these basic rules or restrictions are followed, they can be used for any data retrieval task.
4. If you want to sort the results with a UNION query, only one ORDER BY clause can be used, which must appear after the last SELECT statement. It is not possible to sort part of a result set in one way and part of a result set in another way, so multiple ORDER BY clauses are not allowed.
5. The UNION automatically removes duplicate rows from the query result set (in other words, it behaves the same as using multiple WHERE clause conditions in a single SELECT statement). Because vendor 1002 also produces an item for less than 5, both SELECT statements return that line. When using UNION, duplicate lines are automatically cancelled. This is the default behavior of the UNION, but if repetition is allowed, you can use UNION ALL instead of UNION. As shown below:
Chapter 18 Combined Query
When we need to match text, we can use LIKE+ wildcards or regular expressions to do so, but there are many limitations:
1. Poor performance — Wildcard and regular expression matches typically require MySQL to attempt to match all rows in a table (and these searches rarely use table indexes). Therefore, these searches can be time-consuming due to the increasing number of rows being searched.
2. Not very flexible — With wildcards and regular expression matching, it is difficult (and not always possible) to have clear control over what is and is not matched. For example, specify that a word must match, a word must not match, and a word can match or not match only if the first word does match.
3. Not smart — While wildcard and regular expression based searches provide a very flexible search, neither of them provides an intelligent way to select results. For example, a search for a particular word will return all the rows containing that word, regardless of the row containing a single match and the row containing multiple matches (sorting them by what might be a better match). Similarly, a search for a particular word will not find lines that do not contain that word but contain other related words. This is where full-text search comes in. To do a full-text search, the columns being searched must be indexed and reindexed continuously as the data changes. After the table columns are properly designed, MySQL automatically does all indexing and re-indexing. After indexing, SELECT can be used with Match() and Against() to actually perform the search.
Enable full-text search support
MySQL indexes the column as instructed by the FULLTEXT clause FULLTEXT(note_text). When the column is added, updated, or deleted, the index is automatically updated as shown in the following figure:
Matters needing attention:
If you are importing data into a new table, FULLTEXT indexes should not be enabled at this point. It would take less time to import all the data first, and then modify the table to define FULLTEXT.
Conduct full-text search
After indexing, a full-text search is performed using two functions Match() and Against(), where Match() specifies the column to be searched and Against() specifies the search expression to be used, as shown below: The SELECT statement retrieves a single column, note_TEXT, and returns the row containing rabbit. (Full-text search is case-insensitive by default, unless the BINARY statement is used to modify it)
Full-text search can also Rank results using Rank, and Match() and Against() are used to create a computed column (alias Rank) that contains the Rank values computed by full-text search. The hierarchy is calculated by MySQL based on the number of words in a row, the number of unique words, the total number of words in the entire index, and the number of rows containing that word. Rows that do not contain search terms are rated 0(and therefore not selected by the WHERE clause in the previous example). The two lines that do contain the search term have a rank value for each line, with a higher rank value for the line leading the word in the text than for the line following the word. As shown below:
If multiple search terms are specified, the rows that contain most match words have higher rank values than those that contain fewer words (or only one match).
Using query extensions
Query extensions try to broaden the range of full-text search results returned by starting with a basic full-text search to find all rows that match the search criteria. Second, MySQL checks the matching lines and selects all the useful words. Third, MySQL performs a full-text search again, this time using not only the original criteria, but also all the useful words. As shown in the figure below;
Boolean text search
MySQL supports another form of full-text search, called Boolean mode. You can specify words to match, words to exclude, permutation hints (specifying that some words are more important than others and that more important words are ranked higher), expression grouping, and so on. It can be used even if the FULLTEXT index is not defined. But it is a very slow operation. For example, in the query below, the word heavy matches, but -rope explicitly instructs MySQL to exclude rows containing rope(any word that starts with rope, including ropes).
In addition to the Boolean operators – and *, – excludes a word, while * is the truncation operator (think of it as a wildcard for the end of a word). There are also the following full-text Boolean operators:
Here are some full-text Boolean operator use cases:
Instructions for full-text search
1. Short words are ignored and excluded from the index when indexing full-text data. Short words are defined as those with three or fewer characters (this number can be changed if needed).
MySQL comes with a built-in list of non-stopwords that are always ignored when indexing full-text data. You can override this list if you want (see the MySQL documentation for how to do this).
3. Many words appear so frequently that searching for them is useless (returns too many results). Therefore, MySQL has a 50% rule that ignores a word as a non-word if it appears in more than 50% of the lines. The 50% rule does not apply to IN BOOLEAN MODE.
4. If the number of rows in the table is less than 3, the full-text search does not return results (because each word either does not appear, or at least appears in 50% of the rows).
5. Ignore single quotation marks. For example, don’t is indexed as dont.
6. Languages that do not have word separators (including Japanese and Chinese) cannot return full-text search results properly.
7. As mentioned earlier, full-text search is supported only in the MyISAM database engine.
8. Without the proximity operator, proximity search is a feature supported by many full-text searches for adjacent words (in the same sentence, in the same paragraph, in parts of a specific number of words, etc.). MySQL full-text search does not currently support the proximity operator.
Chapter 19 Insert Data
Inserting data using the INSERT statement is familiar. For example, there are two ways to insert a data item whose name is Tom and age is 29 into the Customers table:
INSERT INTO Customers VALUES("tom"."29")；
INSERT INTO Customers(name, age) VALUES("tom"."29");Copy the codeThe second method is recommended because the order of data in the first method must be the same as that in the table, which is easy to write errors. When the table structure changes, the order of data in the first method needs to be changed, while the second method does not.
Insert the retrieved data
INSERT is used to INSERT a row into a table with a specified column value. However, there is another form of INSERT that you can use to INSERT the result of a SELECT statement into a table. As shown in the figure below, this example uses INSERT SELECT to import all data from CUSTNew into the CUSTOMERS table
Chapter 20 Update and Delete Data
Update the data
UPDATE statement UPDATE statement UPDATE statement UPDATE statement UPDATE statement
UPDATE table_name SET table_name = new value WHERE table_name = new value;Copy the codeAs shown below:
1. When using UPDATE statements, do not omit the WHERE clause, otherwise all rows in the table will be updated.
IGNORE keyword. If you UPDATE multiple rows with an UPDATE statement and an error occurs when one or more of these rows are updated, the entire UPDATE operation is cancelled (all rows updated before the error occurred are restored to their original values). To continue updating even if errors occur, use the IGNORE keyword, as shown below: UPDATE IGNORE customers…
Delete the data
Update data using DELETE statements, we are also very skilled, the general DELETE statement components are as follows:
DELETE FROM table name WHERE filter condition;
As shown below:
1. Do not omit the DELETE clause when using the DELETE statement. Otherwise, all rows in the table will be deleted.
2. The DELETE statement deletes rows from a table, or even all rows in a table. However, DELETE does not DELETE the table itself.
3. If you want to DELETE all rows from a table, do not use DELETE. The TRUNCATE TABLE statement can be used, which does the same job but is faster because TRUNCATE actually deletes the original TABLE and recreates a TABLE, rather than deleting the data in the TABLE row by row
Update and delete guidelines
1. Never use an UPDATE or DELETE statement without a WHERE clause unless you really intend to UPDATE and DELETE every row.
2. Ensure that each table has a primary key (see Chapter 15 if you forget this) and use it as much as possible like the WHERE clause (you can specify individual primary keys, multiple values, or ranges of values).
3. Before using the WHERE clause in an UPDATE or DELETE statement, you should test SELECT to ensure that it filters the correct records, in case you write an incorrect WHERE clause.
4. Use a database that enforces referential integrity (see Chapter 15 on this) so that MySQL will not allow rows that have data associated with other tables to be deleted.
MySQL does not have an undo button. UPDATE and DELETE should be used very carefully, otherwise you may find yourself updating or deleting the wrong data.
Chapter 21 Creating and manipulating tables
Create a table
Creating a table using the CREATE statement is familiar, as shown in the figure below
The following points need to be noted:
1. When a table is being built, each column can be a NULL column or a NOT NULL column. If this parameter is NOT specified, the column can be NULL by default.
2. The primary key must be unique and cannot be NULL. If one column is used as the primary key, the value must be unique, and if multiple columns are used as the primary key, the value must be unique in combination of multiple columns.
InnoDB = InnoDB = InnoDB = InnoDB = InnoDB = InnoDB = InnoDB = InnoDB = InnoDB = InnoDB
Is a reliable service engine (see chapter 26), it does not support full text search;
MEMORY
In function equivalent to MyISAM, but because the data is stored in memory (not disk), fast, so it is particularly suitable for temporary tables;
MyISAM
Is a very high-performance engine that supports full-text search (see Chapter 18), but does not support transaction processing.
Update the table
After the TABLE is created, if the TABLE structure needs to be modified, we can use the ALTER TABLE statement to modify the TABLE. Such as:
Complex table structure changes typically require a manual deletion process, which involves the following steps:
Create a new table with the new column layout.
2. Use the INSERT SELECT statement to copy data from the old table to the new table. Conversion functions and computed fields can be used if necessary.
3. Verify the new table that contains the required data.
4. Rename the old table (delete it if you are sure).
5. Rename the new table with the original name of the old table.
6. Re-create triggers, stored procedures, indexes, and foreign keys as required.
Delete table
Dropping a TABLE (the entire TABLE rather than its contents) is simple, using the DROP TABLE language, for example, DROP the Customers2 TABLE
DROP TABLE customers2;
Copy the codeRename table
Use the RENAME TABLE statement to RENAME a TABLE. For example, change the table customers2 name to CUSTOMERS
RENAME TABLE customers2 to customers;
Copy the codeChapter 22 Using Views
The view is a virtual table. They contain not data but queries that retrieve the data as needed. Views provide an encapsulation of MySQL’s SELECT statement level, which can be used to simplify data processing and reformat or protect underlying data.
Main uses of views:
1. Reuse SQL statements.
2. Simplify complex SQL operations. After you write a query, you can easily reuse it without knowing its basic query details.
3. Use parts of a table instead of the entire table.
4. Protect data. Users can be granted access to specific parts of a table rather than the entire table.
5. Change the data format and presentation. Views can return data that is different from the presentation and format of the underlying table.
View rules and limitations:
1. Like tables, views must be uniquely named (do not name a view the same as another view or table).
2. There is no limit on the number of views that can be created.
3. To create a view, you must have sufficient access rights. These restrictions are typically granted by the database administrator.
4. Views can be nested, that is, one view can be constructed using queries that retrieve data from other views.
5.ORDER BY can be used in a view, but the ORDER BY in that view will be overwritten if the SELECT from that view also contains ORDER BY.
6. Views cannot be indexed or have associated triggers or default values.
7. Views can be used with tables. For example, write a SELECT statement that joins tables and views.
Common view action statements
1. CREATE a VIEW using the CREATE VIEW statement.
2. Run SHOW CREATE VIEW viewname. To view the statement that creates the view.
DROP VIEW viewName DROP VIEW viewname .
4. When updating a VIEW, you can use DROP before CREATE, OR CREATE OR REPLACE VIEW directly. If the view to be updated does not exist, the second update statement creates a view; If the view to be updated exists, the second update statement replaces the original view.
In the example above, we used views to simplify the use of complex SQL, but there are other uses for views, such as:
Reformat the retrieved data with views
Filter unwanted data with views
Update the view
Views are updatable (that is, you can use INSERT, UPDATE, and DELETE on them). Updating a view updates its base table (recall that the view itself has no data). If you add or remove rows to a view, you are actually adding or removing rows to its base table. However, the view cannot be updated if the view definition has the following operations:
1. GROUP (using GROUP BY and HAVING); Bond;
2. Subquery;
3. And;
4. Aggregate functions (Min(), Count(), Sum(), etc.);
5.DISTINCT;
6. Export (compute) columns.
Chapter 23 Using Stored Procedures
A stored procedure can be a combination of one or more MySQL statements and can add some business logic.
Create and execute stored procedures
Simple example: CREATE a stored PROCEDURE using the CREATE PROCEDURE statement, wrap a SELECT statement, and then execute the stored PROCEDURE using the CALL statement.
Deleting a stored procedure
You can use the DROP PROCEDURE statement to DROP a stored PROCEDURE, for example, productpricing
DROP PROCEDURE productpricing;Copy the codeUsing parameter
When creating a stored procedure, you can use an IN statement to store the incoming parameters and an OUT statement to store the returned results. In the following example, 20005 is the passed argument and @total is the return result. Passing in parameters and returning results can also be defined as multiple.
Build intelligent stored procedures
When creating a stored procedure, you can also use IF, THEN, and END IF statements to set criteria. This is the biggest difference between a stored procedure and a simple statement encapsulation.
Such as:
Checking stored Procedures
You can use the SHOW CREATE PROCEDURE statement to display the CREATE statement used to CREATE a stored PROCEDURE or SHOW PROCEDURE STATUS to list all stored procedures. To limit its output, specify a filtering mode using LIKE, for example :SHOW PROCEDURE STATUS LIKE ‘orderTotal ‘;
Chapter 24 Using cursors
A cursor is a database query stored on the MySQL server. It is not a SELECT statement, but a result set retrieved by the statement. Once the cursor is stored, the application can scroll or browse through the data in the result set as needed.
Create a cursor
Defines a cursor named OrderNumbers
Open and close the cursor
Open a cursor named OrderNumbers
OPEN ordernumbers;
Copy the codeClose the cursor named OrderNumbers
CLOSE ordernumbers;
Copy the codeIf you do not explicitly close the cursor, MySQL will automatically close it when the END statement is reached.
Use cursor data
Chapter 25 using triggers
Triggers can be used to automatically execute a MySQL statement before or after the MySQL response to the INSERT UPDATE DELETE statement.
Create trigger
The format FOR creating a TRIGGER statement is CREATE TRIGGER name TRIGGER timing TRIGGER operation FOR EACH ROW. Such as:
CREATE TRIGGER newproduct AFTER INSERT FOR EACH ROW SELECT 'Product added';Copy the codeCREATE TRIGGER is used to CREATE a new TRIGGER named newProduct. A trigger can be executed before or AFTER an operation occurs; AFTER INSERT is shown here, so the trigger will execute AFTER the INSERT statement has successfully executed. This trigger also specifies FOR EACH ROW, so the code executes on EACH insert ROW. In this example, the text Product Added is displayed once for each inserted line.
Delete trigger
DROP TRIGGER newproduct; Delete the trigger named newProductCopy the codeThe INSERT trigger
-
Within the INSERT trigger code, a virtual table named NEW can be referenced to access the inserted row;
-
In the BEFORE INSERT trigger, the value in NEW can also be updated (allowing the inserted value to change);
-
For AUTO_INCREMENT columns, NEW will have a value of 0 at the beginning of the INSERT and include a NEW auto-generated value after the INSERT.
The above example creates a trigger named Neworder that executes AFTER INSERT ON Orders. When inserting a new order into the Orders table, MySQL generates a new order number and stores it in order_num. The trigger retrieves this value from new.order_num and returns it.
The DELETE trigger
DELETE triggers are executed before or after the DELETE statement. Within the DELETE trigger code, you can reference a virtual table named OLD and access the deleted row. Values in OLD are all read-only and cannot be updated.
The UPDATE trigger
UPDATE triggers execute before or after an UPDATE statement. In the UPDATE trigger code, you can reference a virtual table named OLD to access the previous (pre-UPDATE) value and a virtual table named NEW to access the newly updated value. In the BEFORE UPDATE trigger, the value in NEW may also be updated (allowing you to change the value that will be used in the UPDATE statement). Values in OLD are all read-only and cannot be updated.
Matters needing attention:
1. Only tables support triggers, not views (nor temporary tables).
2. If the BEFORE trigger fails, MySQL will not perform the requested operation. Also, if the BEFORE trigger or statement itself fails, MySQL will not execute the AFTER trigger (if any).
3. Compared to other DBMSS, the triggers supported in MySQL 5 are quite rudimentary. There are plans to improve and enhance trigger support in future releases of MySQL.
4. Creating triggers may require special security access, but trigger execution is automatic. If an INSERT, UPDATE, or DELETE statement can execute, then the associated trigger can execute as well.
5. Triggers should be used to ensure data consistency (case, format, etc.). The advantage of performing this type of processing in a trigger is that it is always done, and it is done transparently, independent of the client application.
6. One very interesting use of triggers is to create an audit trail. Using triggers, it is very easy to log changes (and even before and after states, if needed) to another table.
7. Unfortunately, MySQL triggers do not support CALL statements. This means that the stored procedure cannot be called from within the trigger. The required stored procedure code needs to be copied into the trigger.
Chapter 26 Handling of Management Affairs
Transaction processing can be used to maintain database integrity by ensuring that a set of SQL statements are either executed completely or not executed at all. With transactions, it is possible to ensure that a group of operations will not be stopped in the middle, and that they will either be executed as a whole or not at all (unless explicitly directed). If no errors occur, the entire set of statements is submitted to (written to) the database table. If an error occurs, a rollback (write-off) is performed to restore the database to a known and safe state. A transaction is a group of SQL statements; Rollback refers to the process of revoking specified SQL statements. Commit refers to writing the result of an unstored SQL statement to a database table. A savepoint is a temporary place-holder set up in a transaction to which you can issue a rollback (as opposed to a rollback of the entire transaction).
Control transaction processing
The TRANSACTION startsCopy the codeUse the ROLLBACK
The MySQL ROLLBACK command is used to ROLLBACK (undo)MySQL statements. Such as:
Use the COMMIT
Normal MySQL statements are executed and written directly against database tables. This is known as implicit commit, where commit (write or save) operations happen automatically. However, in a transaction block, the commit does not occur implicitly. To make an explicit COMMIT, use the COMMIT statement.
Use retention points
Simple ROLLBACK and COMMIT statements can write or undo the entire transaction. However, this is only possible for simple transactions; more complex transactions may require partial commits or fallbacks. To support rolling back part of a transaction, you must be able to place placeholders in the transaction block at an appropriate place. This way, if you need to fall back, you can fall back to a placeholder.
Create placeholders
SAVEPOINT delete1; Create a placeholder named delete1 ROLLBACK TO delete1. Fall back to the placeholder for delete1Copy the codeRetention points are automatically released after the transaction is completed (performing a ROLLBACK or COMMIT). Since MySQL 5, reservation points can also be explicitly released with RELEASE SAVEPOINT.
Change the default commit behavior
The default MySQL behavior is to commit all changes automatically. In other words, any time you execute a MySQL statement, the statement is actually executed against the table, and the changes take effect immediately. To indicate that MySQL does not commit changes automatically, you can use the
SET autocommit=0;
Copy the codeThe autoCOMMIT flag determines whether to COMMIT changes automatically, with or without a COMMIT statement. Setting autoCOMMIT to 0(false) instructs MySQL not tocommit changes automatically (until autoCOMMIT is set to true).
Chapter 27 Globalization and Localization
Database tables are used to store and retrieve data. Different languages and character sets need to be stored and retrieved differently. Therefore, MySQL needs to accommodate different character sets (different letters and characters) and different ways of sorting and retrieving data.
Use character set and collate order
show CHARACTER SET;
Copy the codeMySQL’s default character set is latin1, and utF8 is commonly used
show COLLATION;
Copy the codeYou can display a complete list of supported collations and the character sets to which they apply, some of which have more than one collation.
Usually the system administration defines a default character set and collation at installation time. In addition, you can specify the default character set and collation when creating the database. And check, you can use the following statements to view:
show VARIABLES like 'character%'; Check the character set configuration'collation%'; View configurations related to proofreadingCopy the codeAssign character sets and collate tables
1. If you specify CHARACTER SET and COLLATE, these values are used.
2. If only the CHARACTER SET is specified, this CHARACTER SET and its default collation are used (as shown in the result of SHOW CHARACTER SET).
3. If neither CHARACTER SET nor COLLATE is specified, the database default is used.
Specifies character set and collation for columns
Specify check order when querying
Chapter 28 Safety Administration
MySQL user accounts and information are stored in a MySQL database called MySQL. Get a list of all user accounts
Creating a User Account
CREATE USER ben IDENTIFIED BY ‘passwOrd’; CREATE USER Creates a new USER account. A passwOrd is not necessarily required to create a user account, but this example gives a passwOrd with IDENTIFIED BY ‘passwOrd’.
Rename a user account
RENAME USER ben TO bforta;
Copy the codeDeleting a User Account
DROP USER bforta;
Copy the codeSetting Access Rights
To see the permissions granted to user accounts, use SHOW GRANTS FOR, as shown below:
.
GRANT statements
The general format of the GRANT statement is GRANT permission ON scope TO user; Such as:
GRANT SELECT ON crashhouse.* TO bforta;
Copy the codeGRANT allows SELECT statements on crashcourse.*(all tables in the Crashcourse database).
SHOW GRANTS can be used to SHOW the permissions of the Bforta user
REVOKE
The reverse action of GRANT is REVOKE, which is used to REVOKE a specific permission.
REVOKE SELECT ON crashhouse.* FROM bforta;
Copy the codeREVOKE This REVOKE statement revokes the SELECT access privileges just granted to the user bforta. Revoked access must exist or an error will occur.
GRANT and REVOKE control access at several levels:
GRANT ALL and REVOKE ALL;
ON database.*;
3. For a specific table, use ON database.table.
4. Specific columns;
5. Specific stored procedures.
The following are each of the permissions that can be granted or revoked:
Matters needing attention:
Authorized in advance
To use GRANT and REVOKE, a user account must exist, but there is no requirement for the objects involved. This allows administrators to design and implement security measures before creating databases and tables. The side effect of this is that when a database or table is dropped (using the DROP clause), the associated access rights remain. Moreover, these permissions are still in effect if the database or table is recreated in the future.
Change password
Change the password of a specific user
SET PASSWORD FOR bforta = Password('123456');
Copy the codeChange the password of the current user
SET PASSWORD = Password('123456');
Copy the codeChapter 29 Database Maintenance
The following methods are used to back up data:
1. Run the command-line utility mysqldump to dump all database contents to an external file. This utility should run properly before a regular backup so that the dump can be backed up correctly.
2. Copy all data from a database with the command-line utility mysqlHotCopy (not all database engines support this utility).
3. You can use MySQL BACKUP TABLE or SELECT INTO OUTFILE to dump all data to an external file. Both statements accept the system file name to be created, which must not exist or an error will occur. The data can be restored using the RESTORE TABLE.
Database maintenance
The ANALYZE TABLE is used to check whether the TABLE key is correct.
CHECK TABLE is used to CHECK the TABLE for many problems. Indexes are also checked on MyISAM tables. CHECK TABLE supports a number of CHECK options (only for MyISAM tables) :
CHANGED Checks the tables that have CHANGED since the last check
EXTENDED performs the most thorough checks
FAST only checks tables that are not closed properly
MEDIUM checks all deleted links and performs key checks
QUICK does only a QUICK scan as shown below, and CHECK TABLE finds and fixes problems:
If the MyISAM TABLE access results in incorrect or inconsistent results, you may need to use the REPAIR TABLE to REPAIR the corresponding TABLE. This statement should not be used too often, and if it is, there may be a bigger problem to solve. If a large amount of data is removed from a TABLE, the OPTIMIZE TABLE should be used to retrieve the space used to OPTIMIZE the TABLE’s performance.
Diagnosing startup problems
Server startup problems usually occur when changes are made to the MySQL configuration or to the server itself. MySQL reports errors when this problem occurs, but since most MySQL servers are automatically started as system processes or services, these messages may not be visible. When troubleshooting system startup problems, you should first try to start the server manually. The MySQL server itself is started by executing mysqld on the command line. Here are a few important mysqld command-line options:
–help Displays help
–safe-mode loads minus some optimally configured servers
–verbose Displays full text messages (used in conjunction with –help for more detailed help messages)
–version Displays the version information and exits
Viewing log Files
A series of log files that the MySQL maintenance administrator relies on. The main log files are as follows.
The error log
It contains details of startup and shutdown problems and any critical errors. This log is usually named hostname.err and is located in the data directory. This log name can be changed with the –log-error command line option.
Query log
It logs all MySQL activity and is very useful when diagnosing problems. This log file can become very large very quickly, so it should not be used for long periods. This log is usually named hostname.log and is located in the data directory. This name can be changed with the –log command line option.
Binary log
It records all statements that have updated (or may have updated) data. This log is usually named hostname-bin and is located in the data directory. This name can be changed with the –log-bin command-line option. Note that this log file was added in MySQL 5. Previous versions of MySQL used update logs.
Slowly Querying Logs
As the name implies, this log logs any queries that are performing slowly. This log is useful in determining where the database needs tuning. This log is usually named hostname-slow.log and is located in the data directory. This name can be changed with the –log-slow-queries command line option. When logging is used, the FLUSH LOGS statement can be used to FLUSH and restart all log files.
Chapter 30 Improving Performance
Some ways to improve performance:
1. First, MySQL(like all DBMSS) has specific hardware recommendations. Use any old computer as a server when learning and researching MySQL. But for production servers, you should stick to these hardware recommendations.
2. In general, a critical production DBMS should run on its own dedicated server.
3.MySQL is pre-configured with a set of default Settings, and it’s usually a good place to start. But after a while you may need to adjust memory allocation, buffer size, and so on. (To see the current Settings, use SHOW VARIABLES; And SHOW STATUS)
MySQL a multi-user, multi-threaded DBMS, in other words, it often performs multiple tasks at the same time. If one of these tasks executes slowly, all requests will execute slowly. If you experience significant poor performance, use SHOW PROCESSLIST to SHOW all active processes (along with their thread ids and execution times). You can also KILL a particular process with the KILL command (which requires you to log in as an administrator).
There is always more than one way to write the same SELECT statement. You should experiment with joins, unions, subqueries, and so on to find the best way.
6. Use the EXPLAIN statement to have MySQL EXPLAIN how it will execute a SELECT statement.
7. In general, stored procedures execute faster than each MySQL statement executed one by one
8. Always use the right data type.
9. Never retrieve more data than you need. In other words, don’t use SELECT *(unless you really need every column).
10. Some operations (including INSERT) support an optional DELAYED keyword that, if used, will immediately return control to the calling program and actually execute the action as soon as possible.
11. Autocommit should be turned off when importing data. You may also want to delete indexes (including FULLTEXT indexes) and then rebuild them after the import is complete.
12. Database tables must be indexed to improve data retrieval performance. Determining what to index is not a trivial task, and you need to analyze the SELECT statements used to find duplicate WHERE and ORDER BY clauses. If a simple WHERE clause takes too long to return a result, you can conclude that the columns (or columns) used in it are the objects that need to be indexed.
13. Do you have a complex set of OR conditions in your SELECT statement? You can see significant performance improvements by using multiple SELECT statements and the UNION statement that joins them.
14. Indexes improve the performance of data retrieval but impair the performance of data insertion, deletion, and update. If you have tables that collect data and are not often searched, do not index them until it is necessary. (Indexes can be added and removed as needed.)
15. LIKE very slow. In general, it’s best to use FULLTEXT instead of LIKE.
16. Databases are constantly changing entities. A well-optimized set of tables can be unrecognizable after a while. The ideal optimizations and configurations change as tables are used and their contents change.
17. The most important rule is that every rule has to be broken at some point.