MySQL must know must learn notes

MySQL must know must learn notes

1. SQL basics

  • Database concept: a container for storing organized data.
  • Table: A structured list of a certain type of data.
  • Schema: Information about the layout and characteristics of databases and tables. The table has some characteristics, which define how the data is stored in the table, such as what kind of data can be stored, how the data is decomposed, how to name each part of the information, and so on.
  • Column: A field in the table. All tables are composed of one or more columns.
  • Data Type: The type of data allowed. Each table column has a corresponding data type, which limits (or allows) the data stored in the column.
  • Row: A record in the table.
  • Primary key: A column (or a set of columns) whose value can uniquely distinguish each row in the table.
    • The primary key is used to represent a specific row. Without a primary key, it is difficult to update or delete specific rows in a table because there is no safe way to ensure that only related rows are involved .
    • The best habit of the primary key: do not update the value in the primary key column; do not reuse the value of the primary key column; do not use the value that may change in the primary key column.
  • SQL (Structured Qurey Language), a structured query language.

2. MySQL basics

  • The server part is a piece of software responsible for all data access and processing. This software runs on a computer called a database server.
  • Only server software deals with data files . All requests for data, data addition, deletion, and data update are completed by the server software. These requests come from or change from the computer running the client software.
  • mysql -u root -proot -P 3306
    Log in to the root user, the password is root, and the port number is 3306
  • The command is entered in
    after that
  • Command
    the end
  • help
    Get help. Such as
    help select
    \h select
  • enter
    Exit the command line utility.

3. SQL syntax

3.1 Select the database.

  • You must open the database before you can read the data.
Database_name the USE; - select the database, called database_name copy the code

3.2 Display the names of all existing databases


3.3 Display all tables in a database

USE bookstore; SHOW TABLES; copy code

3.4 Display the information listed in the table

SHOW the COLUMNS the FROM book_list; - information about all the columns in a table, the DESCRIBE book_list; - upper and lower two commands are equivalent to copy the code

Output, respectively display the field name, data type, whether to allow null, key information, default value and other information (such as field


+-----------+------------------------+------+----- +---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------------+------+----- +---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | price | decimal(10,2) unsigned | NO | | NULL | | | author | varchar(100) | YES | | NULL | | | sales | int(11) | YES | | NULL | | | inventory | int(11) | YES | | NULL | | +-----------+------------------------+------+----- +---------+----------------+ Copy code

3.5 Auto increment

  • Some table columns require unique values. When each row is added to the table, MySQL can automatically assign the next available number to each row, instead of manually assigning a unique value when adding a row.

3.6 Display extensive server status information

SHOW STATUS; copy code

4. retrieve data

4.1 Retrieving a single column

SELECT id FROM book_list; copy code

The order in which the data is returned may or may not be the order in which the data is added to the table. As long as it returns the same number of rows, it is normal.

4.2 Retrieve multiple columns

The SELECT ID, `name` the FROM book_list; duplicated code

4.3 Retrieve all columns

SELECT * FROM book_list; copy the code

Although using wildcards may save you trouble by not explicitly listing the required columns, retrieving unneeded columns usually reduces the performance of retrieval and unused programs. Therefore, it is best not to use it.

4.4 Retrieving different rows

SELECT DISTINCT author FROM book_list; copy code

Only show different rows, not duplicates.

4.5 Limit the number of rows of output results

SELECT * FROM book_list LIMIT 5 ; - Only display the first 5 lines of copy code

SQL defaults to the first line as 0.

SELECT * FROM book_list The LIMIT . 5 , . 5 ; - the starting line, the number of lines copy the code
SELECT * FROM book_list LIMIT 4 OFFSET 3 ; - Display 4 lines, copy the code from line 4

5. sort

5.1 Sorting in ascending order

SELECT id, `name`, price FROM book_list ORDER BY price; - sort the code in ascending order by price

5.2 Sort by multiple columns

The SELECT the above mentioned id, `name`,. Price, Sales the FROM book_list the ORDER BY . Price, Sales; - first Sort by price, followed by sorting by sales, are ascending copy the code

5.3 Sort in descending order

The SELECT ID, `name`,. Price, Sales the FROM book_list the ORDER BY . Price DESC ; - DESC in descending order, ASC ascending duplicated code

In sorting, the default is case-insensitive

5.4 Find the maximum value

The SELECT ID, `name`,. Price, Sales the FROM book_list the ORDER BY . Price DESC the LIMIT . 1 ; - show only the first line, it is to find the maximum value of the duplicated code

6. filter

6.1 Filter out the rows that meet the conditions

The SELECT the above mentioned id, `name`,. Price, Sales the FROM book_list the WHERE . Price = 20 ; - 20 screened price of all goods copy the code
  • Single quotation marks are used to qualify strings. If you compare a value with a column of string type, you need to qualify quotation marks. The value used for comparison with the numeric column does not need to be quoted.
  • At the same time
    Clause, you should let
    lie in
    After that, otherwise an error will be generated.
    SELECT id, `name`, price, sales FROM book_list WHERE `name` REGEXP '[az]' ORDER BY price; Copy code

6.2 Range value check

The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE . Price the BETWEEN 20 is the AND 100 ; - closing the left and right closed interval copy the code

6.3 Null value check

The SELECT ID, `name`,. Price, sales the FROM book_list the WHERE sales the IS NULL ; - the selection of the control content of the sales copy the code

6.4 Combination

The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ID <= 30 the AND . Price <= 20 is ; - a combination of multiple conditions is determined duplicated code
The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ID = 26 is OR ID = 30 ; - or determination, with the number equal to remember =` ` duplicated code
  • SQL is processing
    Prior to operator processing
    Operator. Therefore, to be on the safe side, if you are dealing with multiple logical operators, it is best to use
    Expand up.


The SELECT id, `name`,. Price, Sales the FROM book_list the WHERE id the IN ( 20 , 30 ); - id of all rows 20 or 30, the conditions enumerated in parentheses duplicated code

7. wildcard

7.1 Use wildcards to filter

7.1.1 Percent sign

Represents 0, 1, or more characters at a given position.

The SELECT the above mentioned id, `name`,. Price, Sales the FROM book_list the WHERE ` name` the LIKE '% the Java' ; -% `` tell MySQL to accept any characters after the java, no matter how many characters. Copy code
SELECT id, `name`, price, sales FROM book_list WHERE `name` LIKE '%java%' ; - `%` tells MySQL to accept any character that contains "java", no matter how many characters it has. Copy code

7.1.2 Underscore

Match a single character, no more and no less

The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ` name` the LIKE 'java_' ; - ``% MySQL told to accept "java" back with only a character string and copy the code

7.1.3 Tips for using wildcards

  • The processing of wildcard searches generally takes longer than the other searches discussed earlier.
  • Don't overuse wildcards. If other operators can achieve the same purpose, other operators should be used.
  • When you really need to use wildcards, don't use them at the beginning of the search pattern unless absolutely necessary. Put the wildcard at the beginning of the search pattern, the search is the slowest.
  • Pay careful attention to the location of wildcards. If it is misplaced, the desired data may not be returned.

8. regular expressions

  • Regular expression: It is a special string (character set) used to match text. All kinds of programming languages, text editors, operating systems, etc. support regular expressions.

8.1 Basic character matching

The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ` name` REGEXP, 'java' ; - matches the column containing 'java' string row copy the code
The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ` name` REGEXP, '.ava' ; -. Regular expression language is a special character, it matches any character duplicated code

Regular expressions are not case-sensitive in MySQL. If you want to be case-sensitive, you need to add it in front of the matched string


The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ` name` REGEXP, BINARY 'Java' ; duplicated code

8.2 Proceed

The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ` name` REGEXP, BINARY 'Java | the JAVA' ; - `|` character represents an OR operator, or represents `java` comprising matching string` JAVA` copy the code
The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ` name` REGEXP, '[TJ]' ; - t contains the matching string or row j copy the code
The SELECT ID, `name`,. Price, Sales the FROM book_list the WHERE ` name` REGEXP, '[AZ]' ; - the line matching the string of letters contain any copy the code
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' - special characters, need to pass escape character `\\` to indicate copy code

Match character class

Any character, same as
Any lowercase letters, the same
Any uppercase letter, same as
Any letters and numbers, the same
Any number, the same
Any hexadecimal number, same as
Space and tabulation, the same
Any blank characters including spaces, the same as
ASCII control characters (ASCII 0 to 31 and 127)
Any printable character
Same, but excluding spaces
, Not in
Any character in

8.3 Matching multiple instances

Repeating metacharacters

0 or more matches
1 or more matches, equal to
0 or 1 match, equal to
{0, 1}
Specified number of matches
Not less than the specified number of matches
{n, m}
Range of matching number (m does not exceed 255)
SELECT prob_name FROM products -\\( match ( - [0-9] match numbers -? Match one or 0 arbitrary characters -\\) match) WHERE prob_name REGEXP '\\([0-9] sticks ?\\)' Copy code
The SELECT prob_name the FROM Products - [: digit for:] matches any number - {4} exactly in front of the character appears in claim 4 times the WHERE prob_name REGEXP, '[[: digit for:]] {4}' the ORDER BY prob_name; duplicated code

8.4 Locator

Start of text
End of text
The beginning of the word
End of word
The SELECT PROD_NAME the FROM Products the WHERE PROD_NAME REGEXP, '^ [0-9\\.]' // Match begin or decimal string copy the code

Means to negate the set and take the opposite of the set, such as
; Otherwise it refers to the beginning of the string

9. calculated fields

9.1 Calculated fields

  • Many of the conversion and formatting tasks that can be done in SQL statements can be done directly in the client application. But generally speaking, completing these operations on the database server is much faster than on the client, because the DBMS is designed to complete these processes quickly and efficiently.

9.2 Splicing fields

The SELECT CONCAT ( 'A' , 'B' , 'C' ); - the result is 'abc' duplicated code

Most DBMS use + or || to achieve splicing, MySQL uses

Function to achieve.

9.3 Clear the extra spaces on the left and right sides

The SELECT RTRIM ( 'FADFA' ); - clear a space on the right of the SELECT LTRIM ( 'FADFA' ); - clear a space on the left the SELECT the TRIM ( 'FADFA' ); - remove all spaces copy the code

9.4 Using aliases

In order to better refer to a column, you can take an alias for a column

SELECT CONCAT(RTRIM(vend_name), '(' , RTRIM(vend_country), ')' ) AS vend_title - behind AS is the alias FROM vendors; copy the code

10. Function

10.1 Functions

  • Functions are not as portable as SQL. Almost every major DBMS implementation supports functions that other implementations do not support, and sometimes the differences are quite large. After using some functions that are not very portable, it is not conducive to dynamically specifying the database management system.

10.2 Text processing functions

Returns the character to the left of the string
Returns the character on the right side of the string
Returns the length of the string
Convert string to lowercase
Convert string to uppercase
Remove the characters on the left side of the string
Remove the characters on the right side of the string
Find a string of string
Returns the SOUNDEX value of the string
Returns the characters of the string
- Counting from the left, intercept the first n SELECT LEFT ( 'foobarbar' , 5 ) - ->'fooba' - Count from the right, intercept the n SELECT RIGHT ( 'foobarbar' , . 4 ) - -> 'RBAR' duplicated code
- Locate the first position of the substring in the string, starting from 1. If not found, return 0 SELECT LOCATE( 'bar' , 'foobarbar' ) ---> 4 SELECT LOCATE( 'xbar' , 'foobar' ) - -> 0 SELECT LOCATE( 'bar' , 'foobarbar' , 5 ) - -> 7 duplicated code
- Substring, intercepted from the nth SELECT SUBSTRING ( 'Quadratically' , 5 ) - ->'ratically' - Substring, intercepted from the nth SELECT SUBSTRING ( 'foobarbar' FROM 4 ) - -->'barbar' - substring, intercept m SELECT SUBSTRING ( 'Quadratically' , 5 , 6 ) --->'ratica' - substring starting from the nth Truncated to the end of the string SELECT SUBSTRING ( 'Sakila' , -3 ) - ->'ila' - Substring, starting from the nth from the bottom, intercepting m SELECT SUBSTRING ('Sakila' , -5 , 3 ) - ->'aki' - substring, starting from the nth from the bottom, intercepting m SELECT SUBSTRING ( 'Sakila' FROM -4 FOR 2 ) ---> 'ki'Copy code
SELECT SOUNDEX( 'Hello' ) - ->'H400' SELECT SOUNDEX( 'Quadratically' ) --->'Q36324' - Find matching all similarly pronounced strings SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex( 'Y Lie' ); copy the code

10.3 Date and time processing functions

Return current date
Return current time
Return the current date and time
Returns the date part of the date and time
Returns the time part of the date and time
Returns the year part of the date and time
Returns the month part of the date and time
Returns the days part of the date and time
Returns the hour part of the date and time
Returns the minute part of the date and time
Returns the second part of the date and time
Add a date (day, week, etc.)
Add a time (hour, minute, etc.)
Returns the difference between two dates
Highly flexible date calculation function
Return a formatted date or time string
For a date, return the corresponding day of the week
SELECT NOW() - -> '2007-12-15 23:50:26' SELECT NOW() + 0 - -> 20071215235026.000000 SELECT ADDDATE( '2008-01-02' , 31 ) - -> '2008 -02-02' SELECT ADDTIME( '2007-12-31 23:59:59.999999' , '1 1:1:1.000002' ) - -> '2008-01-02 01:01:01.000001' SELECT ADDTIME( ' 01:00:00.999999' , '02:00:00.999998' ) - -> '03:00:01.999997' SELECT DATEDIFF( '2007-12-31 23:59:59' , '2007-12-30') - -> 1 SELECTThe DATEDIFF ( '2010-11-30 23:59:59' , '2010-12-31' ) ---> -31 the SELECT the DAYOFWEEK ( '2007-02-03' ) - ->. 7 duplicated code
  • Whether it is to insert or update table values or use
    Clause to filter, the date must be
  • However, use
    WHERE order_date = '2005-09-01'
    Unreliable. because
    The data type is
    , This type stores date and time values. The default time value is
    . Therefore, by
    When filtering the date, it is easy to be unable to filter out because the time is not correct. The safer approach is:
SELECT cust_id, order_num FROM orders WHERE Date (order_date) = '2005-09-01' ; Copy code
  • If you want the date, use
    , This is a good habit.
- Filter rows of a certain month SELECT cust_id, order_num FROM orders WHERE Date (order_date) BETWEEN '2005-09-01' AND '2005-09-30' ; SELECT cust_id, order_num FROM orders WHERE Year (order_date) = 2005 AND Month (order_date) = 9 ; Copy code

10.4 Numerical processing functions

Return absolute value
Returns the sine of an angle (in radians)
Returns the cosine of an angle
Returns the tangent of an angle
Returns the index value of an angle
Returns the remainder of the division operation
Returns the square root of a number
Return to pi
Returns a random number

10.5 Aggregate functions

Returns the number of rows in a column
Returns the maximum value of a column
Returns the minimum value of a column
Returns the average value of a column
Returns the sum of values in a column
  • When calculating, ignore the value
  • Such as using
    Function, if you use
    , Will count all rows, but when using
    Time, if a certain behavior in the column
    , It will be ignored directly.
  • use
    Function, if a certain behavior
    , It will not be included in the calculation range, it will neither account for the number nor affect the value, and will be directly ignored.
    Only the calculation contains different values.
SELECT COUNT ( DISTINCT price) FROM book_list; SELECT AVG ( DISTINCT price) FROM book_list; copy code
  • These functions are designed to be highly efficient, and they generally return results much faster than client applications.

11. Data grouping

11.1 Packet count

  • Calculate how many have the same price.
SELECT price, COUNT (price) FROM book_list GROUP BY price; copy code

Clause must appear in
After the clause,
Before the clause. That is, first filter, then group, and finally sort.

SELECT price, COUNT (price) FROM book_list GROUP BY price ORDER BY price; copy code

11.2 Filter group

SELECT price, COUNT (price) FROM book_list GROUP BY price HAVING COUNT (price) > 8 ; Copy code
    Support all
    Operator. What you have learned about
    All these technologies and options are applicable to
    . They have the same sentence structure, but the keywords are different.
  • Another way of understanding,
    Filter before data grouping,
    Filter after data grouping. This is an important difference,
    The excluded rows are not included in the grouping. This may change the calculated value, thereby affecting
    The groupings filtered out based on these values in the clause.
  • Generally in use
    Clause, it should also be given
    Clause, this is the only way to ensure that the data is sorted correctly. Don't just rely on
    Sort the data.
  • Until now,
    The order of clauses in the statement.
ClauseDescriptionDo you have to use
The column or expression to returnYes
The table from which to retrieve dataOnly used when selecting data from the table
Row-level filteringno
Group descriptionOnly used when calculating aggregation by group
Group level filteringno
Output sort orderno
The number of rows to retrieveno

12. Subqueries

12.1 Subqueries

  • That is, queries nested in other queries.

12.2 Application scenarios

  • Two tables, one table stores user information, and the other table stores order information. The connection between the two tables is through the user ID. When you need to query the user information corresponding to the user ID in the specified order, you need to pass a subquery.
- Query the names of all users who purchased `TN2` SELECT user_name FROM user_info WHERE id in ( SELECT id FROM orders WHERE prod_id = 'TN2' ); Copy code
  • Subqueries are always processed from the inside out, first
    The query inside the brackets is then processed outside the query.
  • in
    Using subqueries in clauses can write powerful and flexible SQL statements. But in actual use, due to performance limitations, too many subqueries cannot be nested
  • Related subqueries: subqueries involving external queries. Need to use fully qualified column names
WHERE orders.cust_id = customers.cust_id; copy code

13. Connection

13.1 Connection

  • One of the most powerful features of SQL is the ability to connect in the execution of data retrieval queries (
  • It is by no means a good thing that the same data appears multiple times. This factor is the basis of relational database design. The design of relational tables is to ensure that the information is broken down into multiple tables, one table for each type of data. The tables are related to each other through some commonly used values (that is, relationships in relationship design).
  • Each row in a table has a unique identifier, which is called the primary key. Another table is connected with the previous table through this identifier, and this identifier is called a foreign key in this table.

13.2 Foreign key (foreign key)

  • A foreign key is a column in a table that contains another primary key value and defines the relationship between the two tables.
CREATE TABLE departments( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR ( 20 ) NOT NULL ); CREATE TABLE employees ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR ( 20 ) NOT NULL , gender VARCHAR ( 10 ), email VARCHAR ( 30 ), dept_id INT , CONSTRAINT fk_emp_dept - FOREIGN KEY(dept_id) REFERENCES departments(dept_id) ); Copy code

13.3 Create a connection

The SELECT vend_name, PROD_NAME, prod_price the FROM vendors, Products the WHERE vendors.vend_id = products.vend_id the ORDER BY vend_name, PROD_NAME; duplicated code
  • Of this sentence
    The clause lists two tables, they are this
    The names of the two tables joined by the statement. For two tables
    The clauses are connected normally.
    As a filter condition after linking, if you don t use
    If it is, each row of the first table and each row of the second table will appear one by one in a combined form. (Cartesian Product)
  • Cartesian product: The result returned by the table relationship without join conditions is the Cartesian product. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table.

13.4 Internal connection

The SELECT vend_name, PROD_NAME, prod_price the FROM vendors the INNER the JOIN Products the ON vendors.vend_id = products.vend_id; duplicated code
  • by
    INNER JOIN...ON...
    The method connection.
  • ANSI SQL specification preferred

13.5 Equivalent connection

  • The effects of the above two methods are exactly the same. In fact, the parts that have the same value in the two tables are taken, and the parts that do not have the same value are directly discarded. (That is, the intersection)

13.6 Join multiple tables

SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_name = 20005 ; Copy code

13.7 Self-connection

  • That is to connect with oneself. It is usually used when a row of data has a certain attribute, and it is necessary to find all data with the same attribute in the table.
  • It can be realized by subquery or by self-join.
- Subquery method SELECT prod_id, prod_name FROM products WHERE vend_id = ( SELECT vend_id FROM products WHERE prod_id = 'DTNTR' ); - Self-connection method SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNDR' ; Copy code
  • Use self-join instead of sub-query : Self-join is usually used as an external statement to replace the sub-query used when retrieving data from the same table. Although the final result is the same, sometimes processing joins is much faster than processing subqueries. You should try two methods to determine which one has better performance.

13.8 Natural Connection

  • There should be at least one list. Now in more than one table, the standard join returns all data, even if the same column appears multiple times, the natural join excludes multiple occurrences, so that each column returns only once.
  • Implementation method: generally through the use of wildcards for the table (
    SELECT *
    ), to complete the use of explicit subsets of the columns of other tables.
SELECT c. * , o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o .order_num AND prod_id = 'FB' ; Copy code

13.9 External connection

  • Many joins associate rows in one table with rows in another table, but sometimes it is necessary to include rows that are not related. (For example, some customers do not have orders, but they exist in the customer list and need to be displayed.) Connections include rows that have no associated rows in the related table. This type of connection is called an external connection.
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id; copy code
  • currently using
    When you must use
    The keyword specifies the table including all its rows.
    Pointed out that
    The table on the right,
    Pointed out is the table on the left. The effect of the two types is actually the same, but the order is different.
  • External connection is to take
    All of the intersections with the other side are merged. Use the part that does not exist on the other side
    To represent.

13.9 Using joins with aggregate functions

The SELECT customers.cust_name, customers.cust_id, COUNT (orders.order_name) the AS num_ord the FROM the Customers the INNER the JOIN Orders the ON customers.cust_id = orders.cust_id the GROUP BY customers.cust_id; duplicated code

14. Combination query

  • Combined query: After executing multiple queries, pass and (
    ), to achieve the merging of multiple query results.
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION - combined keywords SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN ( 1001 , 1002 ); copy code


  • Must consist of two or more
    Statement composition, use keywords between statements
    Each query in must contain the same columns, expressions, or aggregate functions. (However, the columns do not need to appear in the same order.)
  • Column data must be compatible: the types need not be exactly the same, but they must be types that the DBMS can implicitly convert.
  • in
    , The duplicate rows are automatically cancelled. If you don t want to remove duplicate rows, use
  • Sort the combined query results: add at the end

15. Full text search

15.1 Full text search

  • Not all search engines support full text search. The two most commonly used search engines: MyISAM supports full-text search and InnoDB does not support full-text search.
  • In order to perform a full-text search, the indexed column must be indexed, and the index must be continuously reindexed as the data changes. After proper design of the table columns, MySQL will automatically perform all indexing and re-indexing.

15.2 Start a full-text search when creating a table

CREATE TABLE productnotes ( note_id int NOT NULL AUTO_INCREMENT, prod_id char ( 10 ) NOT NULL , note_date datetime NOT NULL , note_text text NULL , PRIMARY KEY(note_id), - specify the primary key of the table FULLTEXT(note_id) - full text search index ) ENGINE = MyISAM; - specify the search engine duplicated code
  • After the definition, MySQL automatically maintains the index. When adding, changing, or deleting rows, the index is automatically updated accordingly.
  • Do not start full text search before importing data. You should start the full-text search after importing the data. Because every time data is imported, the index needs to be updated once, and the time to update each data index separately will be greater than the time to update all data indexes.

15.2 Perform a full text search

SELECT note_text FROM productnotes WHERE Match (note_text) Against( 'rabbit' ); - Search all columns containing'rabbit' in the note_text column copy the code
  • The search will not be case sensitive.
  • Full-text search returns data sorted by how well the text matches. If both lines contain'rabbit', then the third word'rabbit' will have a higher priority than the twentieth word'rabbit'.

15.3 Perform query expansion

  • Query expansion is used to try to broaden the range of full-text search results returned.
  • 1. perform a basic full-text search to find all lines that match the search criteria.
  • 2. MySQL checks these matching rows and selects all useful words.
  • Secondly, MySQL performed a full-text search again, this time using not only the original conditions, but also all useful words.
The SELECT note_text the FROM productnotes the WHERE Match (note_text) Against ( 'anvils' the WITH the QUERY EXPANSION); duplicated code

15.4 Boolean text search

  • In Boolean mode, you can provide details about the following:
    • Word to match
    • Words to be excluded
    • Arrangement hints (specify certain words are more important than others, and more important words have a higher level)
    • Expression grouping
    • Some other content
  • Even if not
    Indexes can also be used, but this is a very slow operation.
The SELECT note_text the FROM productnotes the WHERE Match (note_text) Against ( 'Heavy' the IN BOOLEAN the MODE); - text searches using Boolean duplicated code
The SELECT note_text the FROM productnotes the WHERE Match (note_text) aganist ( 'Heavy -rope *' the IN BOOLEAN the MODE); - sentence matching 'heavy' but does not match contain 'rope' at the beginning of the word copy the code
Boolean operatorsDescription
+Contains, word must exist
-Exclude, the word must not appear
>Include, and increase the level value
<Include, and reduce the level value
()Group words into sub-expressions (allow these sub-expressions as a group to be included, excluded, arranged, etc.)
~Cancel the sort value of a word
*Wildcard at the end of a word
""Define a phrase (not the same as a list of single words, it matches the entire phrase in order to include or exclude the phrase)
The SELECT note_text the FROM productnotes the WHERE Match (note_text) Against ( '+ + Rabbit Bait' the IN BOOLEAN the MODE); - comprising word 'rabbit', and 'bait' lines (both must) copying the code
The SELECT note_text the FROM productnotes the WHERE Match (note_text) Against ( 'Rabbit Bait' the IN BOOLEAN the MODE); - the line containing the word 'rabbit' or 'bait' to (at least one) copy the code
The SELECT note_text the FROM productnotes the WHERE Match (note_text) Against ( ' "Rabbit Bait"' the IN BOOLEAN the MODE) - Search comprising matching phrase 'rabbit bait' instead of matching the two words 'rabbit', and 'bait' duplicated code
The SELECT note_text the FROM productnotes the WHERE Match (note_text) Against ( '> rabbit <carrot' the IN BOOLEAN the MODE) - or rabbit comprising carrot, and increased levels of rabbit, carrot reduced level duplicated code
The SELECT note_text the FROM productnotes the WHERE Match (note_text) Against ( 'Safe + + (<Combination)' the IN BOOLEAN the MODE) - must contain two words, and reduces the 'combination' level duplicated code

16. Insert data

16.1 Insert data

The INSERT the INTO the Customers the VALUES ( NULL , 'of Pep' , '100' , 'Los Angeles' , 'the CA' , '90046' , 'USA' , NULL , NULL ); duplicated code
  • You don t want to give a value, but you can t ignore a column, then you can give
    value. Although this syntax is simple, it is not safe and should be avoided as much as possible. The above SQL is highly dependent on the definition order of the columns in the table, and also relies on the information that is easily available in its order, and it cannot guarantee that the columns will remain in exactly the same order after the next table structure change.
  • The safer method is as follows:
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_eamil) VALUES ( 'Pep' , '100' , 'Los Angeles' , 'CA' , '90046' , 'USA' , NULL , NULL ); copy the code
  • When inserting a row, the first value in MySQL corresponds to the first specified column name. The second value corresponds to the second column name, and so on.
  • Generally do not use the list without explicit columns
    Statements, using a list of columns can make the SQL code continue to function, even if the table structure changes.
  • If no value is given for a column that does not allow NULL values and does not have a default value in the table, MySQL will generate an error message and the corresponding row will not be inserted successfully.
  • If data retrieval is the most important, you can
    Add keywords between
    , Instructs MySQL to lower
    The priority of the statement (also applies to

16.2 Insert multiple rows

  • Single
    Handling multiple inserts than using multiple
    The statement is fast.
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_eamil) VALUES ( 'Pep' , '100' , 'Los Angeles' , 'CA' , '90046' , 'USA' , NULL , NULL ), ( 'M' , '42 is' , 'New York' , 'NY' , '11213' , 'USA' ); duplicated code

16.3 Insert the result of the query

  • Coming soon
    Insert the result into the table
The INSERT the INTO the Customers (the cust_id, cust_contact, the CUST_EMAIL, CUST_NAME, cust_address, cust_city, cust_state, cust_zip, cust_country) the SELECT the cust_id, cust_contact, the CUST_EMAIL, CUST_NAME, cust_address, cust_city, cust_state, cust_zip, cust_country the FROM custnew; duplicated code

Insert through the one-to-one correspondence of column positions, the column names do not have to be the same.

17. Update data


  • Table to update
  • Column names and their new values
  • Determine the filter criteria for the rows to be updated
The Customers the UPDATE the SET the CUST_EMAIL = '' - setting a new value of the WHERE the cust_id = 1005 ; - Filters duplicated code
The Customers the UPDATE the SET CUST_NAME = 'of The Fudds' , - plurality of rows the CUST_EMAIL = '' the WHERE the cust_id = 1005 ; - Filters duplicated code


  • If you use
    When the statement updates multiple rows, one or more rows have an error, and continue to update, you can use
UPDATE IGNORE customers... copy code

17.3 Delete a value in a column

  • Can be set to
UPDATE customers SET cust_email = NULL - remove the value of a column WHERE cust_id = 10005 ; copy the code

18. Delete data

18.1 Delete a row

DELETE FROM customers WHERE cust_id = 10006 ; Copy code

18.2 Delete all rows in the table

DELETE FROM customers; copy code
  • A faster method, this method directly deletes the entire table and then creates a new table, and this method cannot be rolled back:
TRUNCATE TABLE customers; copy code

18.3 Use
Habits followed when:

  • Unless you really intend to update or delete every row, don t leave it without
  • Ensure that each table has a primary key,
    The clause uses the primary key to filter
  • in
    Before, use
    The clauses are tested to ensure that the filtered data is correct.

19. Create table and manipulate table

19.1 Create Table

  • Use a comma between each column. The definition of each column starts with the column name, followed by the data type of the column.
CREATE TABLE customers ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char ( 50 ) NOT NULL , cust_address char ( 50 ) NULL , cust_city char ( 50 ) NULL , cust_state char ( 5 ) NULL , cust_zip char ( 10 ) NULL , cust_country char ( 50 ) NULL , cust_contact char ( 50 ) NULL , cust_email char ( 255 ) NULL , PRIMARY KEY(cust_id) ) ENGINE = the InnoDB; duplicated code
  • The column definitions are properly indented for easy reading and editing.
  • When creating a new table, the specified table name must not exist, otherwise an error will occur. To prevent errors, you can delete and then create.
The DROP TABLE the IF EXISTS the Customers; duplicated code
  • Can be created when the table does not exist
CREATE TABLE customers IF NOT EXISTS ( ... ); Copy code

19.2 Use

  • Each table column is or
    Column, or
    Column, this state is specified by the definition of the table when it is created. use
    The column of does not accept columns that do not have a value in the column, in other words, the column must have a value when inserting or updating a row.
  • NULL
    Is the default setting, if not specified
    , It is assumed that the specified

19.3 Using the primary key

  • The primary key value must be unique. That is, each row in the table must have a unique primary key value. If a single column is used for the primary key, its value must be unique. If multiple columns are used, the combined value of these columns must be unique.
A PRIMARY KEY (vend_id) a PRIMARY KEY (order_num, Order_Item) copy the code

19.4 Use

    Tell MySQL that this column automatically increments whenever a row is added. Only one per table is allowed
    Column, and it must be indexed.
  • If a column is specified as
    , Then it can simply
    Specify a value as long as it is unique. Subsequent increments will begin to use the manually inserted value.
  • Next
    Value of

19.5 Specify default values

CREATE TABLE orderitems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char ( 10 ) NOT NULL , quantity int NOT NULL DEFAULT 1 , - The default value is 1 item_price decimal ( 8 , 2 ) NOT NULL , PRIMARY KEY (order_num, order_item) ) ENGINE = the InnoDB; duplicated code
  • Like most DBMSs, MySQL does not allow functions to be used as default values, it only supports constants.
  • Many database developers use default values instead of
    Columns, especially columns used for calculations or data grouping.

19.6 Engine type

  • InnoDB: Is a reliable transaction processing engine, it does not support full text search.
  • MEMORY: The function is equivalent to MyISAM, but because the data is stored in memory (not disk), the speed is very fast (especially suitable for temporary tables)
  • MyISAM is an extremely high-performance engine, it supports full-text search , but does not support transaction processing.

19.7 Update table

  • In an ideal state, when data is stored in the table, the table should not be updated. In the design process of the table, it takes a lot of time to consider, so that no major changes are made to the table later.
  • Add column
The ALTER TABLE vendors the ADD vend_phone CHAR ( 20 is ); - adding columns duplicated code
  • Delete column
The ALTER TABLE vendors DROP the COLUMN vend_phone; - delete columns copy the code
  • Define foreign keys
The ALTER TABLE OrderItems the ADD CONSTRAINT fk_orderitems_orders a FOREIGN KEY (order_num) the REFERENCES Orders (order_num); duplicated code

Complex table structure generally requires manual deletion process, which involves the following steps:

  • Create a new table with new column layout
  • use
    The statement copies data from the old table to the new table.
  • Verify the new table containing the required data
  • Rename/delete old table
  • Rename the new table with the name of the old table
  • Re-create triggers, stored procedures, indexes, and foreign keys as needed.

19.8 Delete table

The DROP TABLE Customers2; duplicated code

19.9 Rename table

RENAME TABLE customers2 to customers; RENAME TABLE customers2 to customers, customers3 to customers2, customers4 to customers3; copy code

Twenty, use the view

20.1 View

  • It is a virtual table. Unlike a table that contains data, a view only contains queries that dynamically retrieve data when used.

20.2 View common applications

  • Reuse SQL statements
  • Simplify complex SQL operations. After writing a query, you can easily reuse it without knowing its basic query details
  • Use part of the table instead of the entire table
  • Protect data. You can grant users access to specific parts of the table instead of access to the entire table
  • Change the data format and presentation. The view can return data that is different from the representation and format of the underlying table

20.3 The view itself does not contain data

  • Therefore, the data they return is retrieved from other tables. When adding or changing data in these tables, the view will return the changed data.

20.4 View rules and restrictions

  • Views can be nested, that is, you can use queries that retrieve data from other views to construct a view
    Can be used in the view, but if the data is retrieved from the view
    The sentence also contains
    , Then the
    Will be overwritten
  • The view cannot be indexed, nor can it have associated triggers or default values
  • Views can be used with tables. For example, write a link table and view

20.5 Using views

  • View use
    Statement to create
  • use
    SHOW CREATE VIEW viewname
    To see the statement that created the view
  • DROP VIEW viewname
    Delete view
  • When updating the view, you can use
    , Can also be used directly
CREATE VIEW productcustomers AS - create a view SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id - join three tables AND orderitems.order_num = orders.order_num; copy code
  • use
The SELECT CUST_NAME, cust_contact - column the FROM productcustomers - the VIEW the WHERE the prod_id = 'TNT2' ; - Filters duplicated code

Using views, basic SQL can be written once and then used as many times as needed.

20.6 Format the retrieved data

The CREATE the VIEW vendorlocations the AS - Create View the SELECT Concat (RTrim (vend_name), '(' , RTrim (vend_country), ')' ) - formatted data retrieved the FROM vendors the ORDER BY vend_name; duplicated code
The SELECT * - calling view the FROM vendorlocations copy the code

20.7 Filter unwanted data

The CREATE the VIEW customeremaillist the AS the SELECT the cust_id, CUST_NAME, the CUST_EMAIL the FROM the Customers the WHERE the CUST_EMAIL the IS the NOT NULL ; duplicated code

20.8 Using calculated fields

CREATE VIEW orderitemsexpanded AS SELECT order_num, prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems; copy code

20.9 Update view

  • Views are updatable, you can use them
    operating. If you add or delete rows to the view, you are actually adding or deleting rows to its base table.
  • If the view contains the following operations, it cannot be updated: grouping, join, subquery, union, aggregate function (
  • Export column

Generally, the view should be used for retrieval, not for update

Twenty-one, stored procedures

21.1 Stored Procedure

  • It is a collection of one or more MySQL statements saved for future use. Think of them as batch files, although their role is not limited to batch processing

21.2 Reasons for using stored procedures

  • Simplify complex operations by encapsulating processing in easy-to-use units
  • All developers use the same stored procedure to prevent errors
  • Simplify the management of changes. If the table name, column name or business logic changes, only the code of the stored procedure needs to be changed, and the people who use it do not even need to know these changes (encapsulation)
  • Improve performance. Using stored procedures is faster than using separate SQL statements

21.3 Create a stored procedure

The CREATE PROCEDURE productpricing () - definition of stored procedures, if accepted parameters are defined in the parentheses the BEGIN - body of limiting the SELECT Avg. (Prod_price) the AS priceaverage the FROM Products; the END ; duplicated code
  • However, this operation will cause an error because
    As a statement separator, there are also internal
    , Will cause the misunderstanding that the process body ends early. Therefore, you need to temporarily modify the statement separator.
DELIMITER // - Temporarily modify the separator to'//' CREATE PROCEDURE productpricing() BEGIN SELECT Avg (prod_price) AS priceaverage FROM products; END // DELIMITER; - modified delimiters back copy the code

21.4 Executing a stored procedure

CALL productpricing(); Copy code

21.5 Delete stored procedures

DROP PROCEDURE productpricing; DROP PROCEDURE IF EXISTS productpricing; - if it exists delete copy the code

21.6 Use parameters

CREATE PROCEDURE productpricing( OUT pl DECIMAL ( 8 , 2 ), - output variable OUT ph DECIMAL ( 8 , 2 ), OUT pa DECIMAL ( 8 , 2 ) ) BEGIN SELECT Min (prod_price) INTO pl - save the query results in pl FROM products; SELECT Max (prod_price) INTO ph FROM products; SELECT Avg (prod_price) INTO pa FROM products; END ; copy code
  • MySQL support
    (Passed to the stored procedure),
    (From the stored procedure) and
    Parameters of type (incoming and outgoing to stored procedures).
CALL productpricing( @pricelow , - All MySQL variables must start with @@pricehigh , @priceage ); Copy code
The SELECT @pricelow ; - displays the search results duplicated code
CREATE PROCEDURE ordertotal( IN onumber INT , OUT ototal DECIMAL ( 8 , 2 ) ) The BEGIN the SELECT the Sum (ITEM_PRICE * Quantity) the FROM OrderItems the WHERE order_num = onumber the INTO ototal; the END ; duplicated code
The CALL OrderTotal ( 20005. @total ); - call a stored procedure the SELECT @total ; - View Results copy the code

21.7 Establishing an intelligent stored procedure

CREATE PROCEDURE ordertotal( IN onumber INT , IN taxable BOOLEAN , OUT ototal DECIMAL ( 8 , 2 ) ) COMMENT'Obtain order total, optionally adding tax' - This sentence will be displayed on `SHOW PROCEDURE STATUS` BEGIN DECLARE total DECIMAL ( 8 , 2 ); - Declare the variable DECLARE taxrate INT DEFAULT 6 ; - Declared tax rate percentage The SELECT the Sum (ITEM_PRICE * Quantity) the FROM OrderItems the WHERE order_num = onumber the INTO total; - the calculated amount is stored in the variable total IF taxable THEN - SELECT total + (total /100 * taxrate) INTO total; END IF; SELECT total INTO ototal; - save the result on ototal END ; copy the code


The CALL OrderTotal ( 20005 , 0 , @total ); - stored procedure calls the SELECT @total ; - shows the results of duplicated code

21.8 Check the stored procedure

SHOW CREATE PROCEDURE OrderTotal; - displaying a CREATE statement to create a stored procedure SHOW PROCEDURE the STATUS; - all the information stored in the existing procedure, including when, by whom to create other details are shown SHOW PROCEDURE the STATUS the LIKE 'search_user ' ; - wildcard screened by copying the code

Twenty-two, use the cursor

22.1 Cursor

  • Cursors are mainly used in interactive applications, where users need to scroll data on the screen and browse or make changes to the data.

22.2 Using cursors

CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR -- FOR SELECT order_num FROM orders; OPEN ordernumbers; -- CLOSE ordernumbers; -- END;


DECLARE o INT; -- OPEN ordernumbers; -- FETCH ordernumbers INTO o; -- o CLOSE ordernumbers; --
CREATE PROCEDURE processorders() BEGIN DECLARE done BOOLEAN DEFAULT 0; -- DECLARE o INT; -- DECLARE ordernumbers CURSOR -- FOR SELECT order_num FROM orders; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; -- SQLSTATE = '02000' done 1 OPEN ordernumbers; -- -- REPEAT FETCH ordernumbers INTO o; - save the data in a local variable UNTIL done END REPEAT; - close the loop condition (done=1) CLOSE ordernumbers; - close the cursor END ; copy the code

This sentence defines a
, The statement that is executed when the condition occurs. Here, it states that when
SQLSTATE '02000'
When it appears,
SET done=1

Twenty-three, use triggers

  • If you want to automatically execute a certain statement (or certain statements) when an event occurs, you need to use a trigger.
  • The trigger is triggered when the specified table is changed, and the targeted operations are
    , Triggers can be set before and after the event.
  • To create a trigger, 4 pieces of information need to be given
    • Unique trigger name
    • Trigger associated table
    • The activity that the trigger should respond to (
    • When the trigger is executed (before or after processing)

23.1 Create Trigger

CREATE TRIGGER newproduct AFTER INSERT ON products -- 'newproduct' 'products' FOR EACH ROW SELECT 'Product added'; -- 'Product added'



DROP TRIGGER newproduct;


    CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num; -- order_num insert


    Within the trigger code, you can reference a file named
    The virtual table to access the deleted row.
  • OLD
    The values in are read-only and cannot be updated.
- Use OLD to save the rows to be deleted in an archive table CREATE TRIGGER deleteorder BEFORE DELETE ON orders - FOR EACH ROW BEGIN INSERT INTO achieve_orders(order_num, order_datae, cust_id) before deleting - Save the old values in a file called 'achieve_orders' table the VALUES (OLD.order_num, OLD.order_date, OLD.cust_id); the END ; duplicated code


Without using
The advantage is to prevent the order from being archived for some reason.


  • in
    In the trigger code, you can reference
    Access the newly updated value, you can also reference
    Access the previous value.
  • in
    In the trigger,
    The value in may also be updated (allowing the update to be used for
  • OLD
    The values are all read-only and cannot be updated
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state); --


  • MySQL


  • InnoDB

24.3 transaction

  • SQL

24.4 rollback

  • SQL

24.5 commit

  • SQL

24.6 savepoint

  • place-holder


SELECT * FROM ordertotals; START TRANSACTION; -- DELETE FROM ordertotals; -- SELECT * FROM ordertotals; -- ROLLBACK; -- SELECT * FROM ordertotals; --


START TRANSACTION; DELETE FROM orderitems WHERE order_num = 20010; DELETE FROM orders WHERE order_num = 20010; COMMIT; --


SAVEPOINT delete1; --
ROLLBACK TO delete1; --


SET autocommit=0;
-- SET autocommit=0; -- START TRANSACTION; -- USE girls; -- UPDATE boys SET userCP=2000 WHERE id=7; UPDATE boys SET boyName=' ' WHERE id=6; COMMIT; -- -- rollback; --









CREATE TABLE mytable ( column1 INT, column2 VARCHAR(10) ) DEFAULT CHARACTER SET hebrew, -- COLLATE hebrew_general_ci; --

26. Safety Management

26.1 Security Fundamentals

  • Users should have proper access to the data they need, neither more nor less. example:
    • Most users only need to read and write to the table, but a few users need to create and delete tables
    • Some users need to read the table, but may not need to update the table
    • Allow some users to add data, but not delete data
    • Some users (administrators) need permissions to handle user accounts, but most users do not
    • Let users access data through stored procedures, but not allow them to directly access data
    • Restrict access to certain functions based on where the user logs in

26.2 Manage users

MySQL the USE; the SELECT the User the FROM the User ; - obtain a list of all user accounts Copy the code
  • User accounts and information are stored in a MySQL database named mysql

26.3 Create User Account

The CREATE the USER ben IDENTIFIED BY 'ben' ; - Create a new user named ben, ben password to copy the code

The specified password will be encrypted

26.4 Rename account

RENAME USER ben TO ben1; copy code

26.5 Delete user account

DROP USER ben1; copy code

26.6 Set access permissions

After creating a user account, you must then assign access rights. The newly created user account does not have access rights. They can log in to MySQL, but they cannot see the data and cannot perform any database operations. View user account permissions


-- -- --

GRANT SELECT ON crashcourse.* TO ben; -- crashcourse SELECT ben ben crashcourse REVOKE SELECT ON crashcourse.* FROM ben; -- ben crashcourse
GRANT ALL ON bookstore.* TO ben; -- ben bookstore REVOKE ALL ON bookstore.* FROM ben;
GRANT SELECT, INSERT ON bookstore.* TO ben; --


SET PASSWORD FOR ben = Password('ben'); -- SET PASSWORD = Password('ben'); --


  • mysqldump
  • mysqlhotcopy
  • MySQL


ANALYZE TABLE orders; -- CHECK TABLE orders, orderitems; --


--help # --safe-mode # --verbose # --version #


  • hostname.err
  • MySQL
  • hostname-bin
  • hostname-slow.log

  • DBMS
  • MySQL
  • SELECT *
  • LIKE