Combine command line and visualization software to sort out mysql knowledge points in detail

MySQL database knowledge points combing

Manual anti-crawler: original blog address


If reprinted, please indicate the source, thank you!


MySQL is a relational database management system developed by Sweden's MySQL AB and currently belongs to Oracle's products. MySQL is one of the most popular relational database management systems. In terms of web applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.
MySQL is a relational database management system. Relational databases store data in different tables instead of putting all data in a large warehouse, which increases speed and flexibility.

This blog sorts out the knowledge points in the MySQL database, using MySQL 5.x as the demo software.
The installation process can refer to the link: window version extraction code: ms64 ; mac version URL

MySQL graphical visualization software, Navicat for MySQL is selected here , the default is 14 days, if you need to crack, you can refer to Navicat cracking .

Insert picture description here

One, MySQL basic syntax

1.1 Daily Use of MySQL

Written in the previous words: SQL statements are not case sensitive .

1.1.1 MySQL client connection

After configuring the environment variables, in order to connect to MySQL, you need to know the following information

  • Host name (computer name): If you connect to a local MySQL server, it is generally localhost;
  • Port: The default listening port of MySQL is 3306;
  • Username: legal user ID
  • User password: login password (if required)

After opening the Navicat for MySQL software, click the Connection button on the upper left, and the following interface will pop up. The above four information corresponds to the content to be filled in the red frame. If the input is correct, click the "OK" button to connect to the MySQL database.

Insert picture description here

If the connection is normal , The MySQL database will be loaded on the left side of the software. Note that this little whale will turn green, which means a successful connection

Insert picture description here

1.1.2 Understand MySQL database and table

There is no use of the command line to perform MySQL-related query operations, but to execute directly in the visualization software, which is more convenient and fast. Here are
6 common query statements

  • View the database:show databases;
  • View the form:show tables;
  • View the field names in the form:show columns from xxx table;
  • View current status:show status;
  • View authorization:show grants;
  • View errors/warnings:show errors / warnings;

Click the New Query button at the top left of the software to enter the query page, and then perform the statement query, execute the above six statements respectively, the results are as follows, friendly reminder: In order to facilitate review, it is recommended to query for each query (for example, this query contains seven query queries) ) Named and saved. In addition, the query interface supports the execution of a single query statement (the mouse selects the code to be executed and clicks the run button), or it can be executed all (the default is to execute all the codes in the code box)

Save this query (can contain multiple query statements, the shortcut key is Ctrl + s) The

Insert picture description here

six query commands and their output results are as follows: (Some of the output results exceed three lines. For display convenience, only the first three lines of data are intercepted)

Insert picture description here

1.2 MySQL system security

The existence of data has a certain value. If the data we are dealing with is very important, it is not a big problem to use the root administrator authority on a personal computer. If it is in a collaborative environment, then there will be data security issues (such as Others steal or delete data by mistake), so you must fully understand the MySQL system security before formal data processing to ensure the smooth progress of subsequent work.

1.2.1 MySQL Security Principles

Here are three security principles, as follows

  • Database servers usually contain critical data. To ensure the security and integrity of these data, access control is required
  • Users should have proper access to the data they need, neither more nor less. In other words, users cannot have too much access to too much data.
  • Try not to use root, and the use of root login should be taken seriously. Use it only when you absolutely need it (perhaps when you can't log in to another management account). Root should not be used in daily MySQL operations

1.2.2 MySQL security operation example

Example 1: Manage users

Enter the command line, then log in to MySQL, use the use mysql;command to enter it, mysql databasesand finally you can view the Host, User and Password in the user table inside. The input result interface is as follows

Insert picture description here

Security analysis: First, understand the basic network knowledge. The access between different computers is through the ip address (or DNS). Each machine has an address of, which represents itself, and the address also has another The name is localhost, and the latest version of window10 has one more :::1, which can be regarded as the local machine, so the three contents in the Host represent the local machine. Then when you want to log in with the root administrator account, it means that you cannot leave your current computer. Even if you do not set a password, as long as your computer is safe (others cannot operate your computer), then the database is very safe. Unless your computer does not set a password, and you do not pay attention to privacy protection, open it to others when you are going to eat or not paying attention, then this is a human fault, and data security problems are deserved~

Example 2: Create a user account

Continue to the command line interface above to create a user. The code format is: create user 用户名 identified by 密码;after creating a user, and then executing the query statement just now, you can find that although the password is set to 123456, the encrypted data is displayed in the system. The% in the previous Host represents any machine. You can also specify the ip address of a specific machine. Then it is equivalent to that only this person can access the database.

Insert picture description here

Open a new command line and test it. You can find that the newly created user and password can be used. Log in to the database

Insert picture description here

If you want to specify a specific ip address and name, you can use the code:create user 用户名@host名 identified by 密码;

Insert picture description here

Example 3: Delete account

If we don’t need some accounts, we can delete them. Code: For drop user 用户名@host名;example, the account in the above example can be processed through the delete operation. Note that if the host name is specified, then the specified user name and host name can be deleted when the specified user name corresponds to the host name.

Insert picture description here

The introduction of account management in the command line is finished, then how to operate in the visualization software? (However, it is recommended to use the visual interface for user management in the future)

Open the software, click the User task avatar button, you can enter the visual interface of user management, and then directly click the New User button to add users.

Insert picture description here

Click the button to pop up the following interface, enter the relevant information and click Save to realize the user Create, avoid the operation of using code instructions.

Insert picture description here

Of course, you can also view the SQL instructions. After the input is complete, don't worry about clicking the Save button. You can click the SQL Preview in the tab to view the SQL statement of the created user (note the portrait The small asterisk indicates that the SQL statement is not saved.

Insert picture description here

If you want to delete the account, it is also very simple. Simply select the account to be deleted, click the Delete User button and confirm the deletion.

Insert picture description here

Example 4: Set access permissions

First create a test user, create user [email protected]'localhost' identified by '123';and then create two test databases, named test and test2, manually create a form named demo and demo2 under the corresponding library, set the field names of the data to id, if you enter 1, The three data of 2,3 and 3,4,5 are as follows (here you can try to create the database and form field data through the software, and how to create the database and form will be introduced later)

Insert picture description here

Then the user3 account was created, and the single-open command was used Run the window to view the database situation, as follows. It can be found that the user3 account can view the test database without any given permissions, mainly because the test database is designed as a guest database in MySQL, and the user is created by default. As long as the test database exists in the system, other users You can access it, which is much more convenient. Sometimes you want to take out part of the data without setting permissions, so that you can share it with others. Just throw the data into the test database.

Insert picture description here

Before you manually create the form field data, it is to verify uesr3 Whether the user can normally access the data in the test database is verified as follows. The way to view all the data in the form is used here: It select * from 表名;

Insert picture description here

should be noted that user3 belongs to the system. An account does not have administrator rights. If it belongs to the guest, it can only access the test database. There is no access to the test2 database, so this permission is required. , You need to return to the main command line window and use the root administrator to grant. Code instruction: grant 权限 on 数据库 to 用户;

Insert picture description here

Since you can grant guest permissions, you can also withdraw the granted permissions. Code instruction: revoke 权限 on 数据库 from 用户;There is one thing to pay attention to, in the management The user interface has withdrawn the user's authority. If the user is still accessing the test2 database, it will not take effect immediately. It is like entering the company with an access card. When you are inside the company, you can still see the scene inside the company. , But once it comes out, the access control card becomes invalid, and the test2 database cannot be viewed. The code operation is as follows

Insert picture description here

The above is the operation of using the command line to set the permissions. If you use the software to operate, it is very simple. Enter the user interface, double-click the user to be granted permissions, jump to the page, click the Privileges option in the tab, and then click Add Privilege And formulate database-related permissions, and finally click OK. The process is as follows

Insert picture description here

Example 5: Change the password

First introduce the code instructions to modify the password in the command line (note that the main window of the root administrator has the authority): set password for 用户名@host名 = Password(新密码);

Insert picture description here

If you use the software to modify the password, double-click the user directly, and then enter the new password in the input box corresponding to the Password and confirm it again That's it, and then you can see the corresponding statement in the SQL statement preview option (for example, change abcdef to abc)

Insert picture description here

1.2.3 Recovery of Lost MySQL Password Data

Example 6: Recovery in the case of common user password loss

This part of the operation is equivalent to the above password change. Since the password of the ordinary user is lost, it is equivalent to the loss of the access control card. It is fine to apply for another one. It is not a big deal. The operation has been given above, just enter root Under the administrator authority, reset the password or modify the password of the specified user after logging in with the root administrator authority in the software interface, and then click Save.

If you accidentally lose the root administrator's password (forgotten), then naturally there will be no way to perform many operations, and you will be in trouble~

Example 7: Recovery in the case of a lost root password

"Leave one hand when doing things, and see you later." Naturally, MySQL also has a way out. At this time, we must turn on the emergency mode. First, stop our MySQL operation on the computer service (the first MySQL service operation), and then enter Initially install the initial file my.ini set in the MySQL database (the file name I set here is my.ini, open the corresponding file according to my initial settings), add the following command under [mysqld] skip-grant-tables,

Insert picture description here

click save as follows , Then turn on our MySQL service (the second MySQL service operation), and then change the password in MySQL after it is successfully turned on

Insert picture description here

To modify the password here, you need to use another command in case of emergency, first enter it use mysql;and then enter it again update user set password=password('abc') where user='root;'(you can find through the following operation that if you still use the original password modification method, there is no way to change the root password, so an emergency is needed. The password update instruction below)

Insert picture description here

Now that the password has been modified, the next step is to stop the MySQL service again (the third MySQL service operation), comment out the line of code added in the ini file, then save and exit, and finally start normally After clicking on the MySQL service (the fourth MySQL service operation), enter the MySQL login page, enter the password, and then you can enter

Insert picture description here

Two, MySQL case database and database table operations

2.1 MySQL case database

The first part is a detailed explanation of the MySQL configuration environment, then the next step is to deal with the data. Here, for the convenience of display, the case database (which has been uploaded to the resource) is directly introduced. The sample table used is a simple small shopping mall. The order entry system used, the classification of these sample forms is as follows:

Insert picture description here

These four types of data forms almost contain the prototype version of the current company’s database, including: supply (supplier information), production (product catalog), customer information (customer information) ), order (order list) four aspects. Then it can be expanded according to specific needs

Set up specific tables to meet four requirements: (a total of six tables)

Table NameTypes ofDescription
vendors tableSupplier InformationStore the vendor who sells the product. Each supplier has a record in this table, and the vendor ID (vend_id) column is used to match the product and the supplier
products tableproduct informationContains a product catalog, one product per line. Each product has a unique ID (prod_id column), which is linked to its supplier through vend_id (the unique ID of the supplier)
customers tableCustomer informationStore all customer information. Each customer has a unique ID (cust_id column)
orders tableorder informationBut no order details are involved, each order is uniquely numbered (order_num column). The order is associated with the corresponding customer with the cust_id column (it is associated with the unique ID of the customer in the customer table)
orderitems tableOrder product informationEach item in each order occupies a row. For each row in orders, there are one or more rows in orderitems. Each order item is uniquely identified by the order number plus the order item (first item, second item, etc.)
productnotes tableProduct annotation informationStore comments related to a specific product. Not all products have related notes, and some products may have many related notes.

For the productnotes table, it can be understood as the addition of remarks in daily operations and the labeling of products.

Can the orders table and orderitems table be combined into one table? Here you can bring in actual scenes. For example, when you go to the supermarket to buy something, everyone will have an order when they go shopping and pay, but the product purchased on each order is not necessarily, sometimes the same, then this is OK Just set up one table. Then when customers buy multiple products, it is one-to-many. The problem cannot be solved well with only one order table. Therefore, two tables are used, one is a simple order number and The customer information is associated, and the other is the product information, which is associated with the order number to realize the one-to-many problem, as shown in the figure below

Insert picture description here

2.2 MySQL database construction operation

Before building a library, first understand what this library means? , Then here is to introduce the understanding of some terms in MySQL, and compare real life analogies with examples

MySQL systemEnterprise Park
databasesAll company buildings in the park
databaseSingle building
tablesAll floors in the building
tableSingle storey in the building
columnsAll the rooms on the floor
columnSingle room on the floor

The next step is to create a database in MySQL


Explanation of parameters:

  • [DEFAULT] CHARACTER SET: Specify the default character set of the database. In the case of Chinese, use the utf-8 character set.
  • [DEFAULT] COLLATE: Specify the default collation rules for the character set.
  • MySQL's character set (CHARACTER) and collation rules (COLLATION) are two different concepts: character set is used to define the way MySQL stores strings, and collation rules define the way to compare strings and solve the problems of sorting and character grouping.

Example 1: Create a database

Use the command line to create, code instruction: create database 数据库名;(character set and proofreading rules are specified by default, the test and test2 databases created before have been manually deleted)

Insert picture description here

If the operation is as follows on the software, specify the name of the database to be created in the pop-up page, and the following The two are to specify the character set and proofreading method (you can choose not), and then click OK directly to create a database named db2

Insert picture description here

Example 2: Create a database with a specified character set and collation rules

As shown in the figure above, when creating the database, you can specify the character set and proofreading rules. For example, when we design in China, we will deal with Chinese to some extent, so we can set the character set to utf-8 when building the database, and then proofread You can use the default rules (that is, if you don’t select one, the system will automatically match one). For example, click OK directly, and then right-click the created database to edit it. It is found that the system specifies a proofreading rule for the utf-8 character set by default.

Note: When the character set is changed, it is equivalent to data reconstruction. When the amount of data is large, the burden is heavy. Therefore, before entering, determine the saved data character set. If there is a problem with the character set, in order to ensure the security of the data, the recommended way is to re-create a database, and then import the data into it, instead of modifying it in the original database

Insert picture description here

Example 3: Delete the database

Code instruction to delete the database: drop database 数据库名;

Insert picture description here

The operation on the software is to select the database to be deleted, right-click to select Delete Databaseand confirm the deletion.

Insert picture description here

2.3 MySQL table creation operation

To use the CREATE TABLEcreation table, the following information must be given:

  • The name of the new table, given after the keyword CREATE TABLE;
  • The names and definitions of the table columns are separated by commas.

For example, create a table of customer information:

Insert picture description here

2.3.1 Primary key

In the above figure, in addition to defining the necessary elements for the table, the PRIMART KEY(cust_id)primary key and ENGINE=InnoDBengine are also found . Then let's first introduce what is the primary key?

There is no correlation between each row of data in the database. When you need to use it, you must be able to accurately locate the location of the data. Then this function is completed by the primary key, which is generally the field corresponding to the id, directly through the id Search to find the corresponding data

  • The primary key value must be unique , and 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 (joint primary key);
  • Only NULLcolumns with disallowed values can be used in the primary key . NULLColumns with allowed values ​​cannot be used as unique identifiers;
  • AUTO_INCREMENTSince it is a unique value in the primary key, the data inserted each time is guaranteed not to be numbered repeatedly, so there is no need to manually specify

In addition to the primary key, if no value is given when inserting the row, MySQL allows you to specify the default value to be used at this time. The default value is specified CREATE TABLEby the DEFAULTkeyword in the column definition of the statement

2.3.2 Storage Engine

The primary key was introduced above, and then the ENGINEstorage engine is introduced . About the storage engine: Like other DBMSs, MySQL has an internal engine that specifically manages and processes data. When you use a CREATE TABLEstatement, the engine specifically creates a table, and when you use a SELECT statement or other database processing, the engine processes your request internally. Most of the time, this engine is hidden in the DBMS, and there is no need to pay too much attention to it. Take a look at the three commonly used storage engines.

  • InnoDB is a reliable transaction processing engine, it does not support full-text search;
  • MEMORY is equivalent to MyISAM in function, 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

Later, when we introduce full-text retrieval and transaction management in data security, we will again involve the part of the storage engine. You only need to know about it when you read this.

2.3.3 Change table operation

Sometimes after creating a table, you need to add or change data, you can use ALTER TABLEstatements. However, ideally, when data is stored in the table, the table should not be updated. It takes a lot of time to consider in the design process of the table, so that no major changes are made to the table later.
In order to use the ALTER TABLEchange table structure, the following information must be given:

  • In the ALTER TABLEfollowing table you want to change given name (the table must exist, otherwise an error)
  • ALTER TABLEA common use of is to define foreign keys (introduced in the following section)

If it is a complicated table structure change, a manual deletion process is generally required:

  • Create a new table with the new column layout;
  • Use the INSERT SELECT statement to copy data from the old table to the new table. If necessary, use conversion functions and calculated fields;
  • Verify the new table containing the required data;
  • Rename the old table (if you are sure, you can delete it);
  • Rename the new table with the original name of the old table;

2.3.4 Delete table operation

And delete database operations similar code instructions as follows: drop table 表名;Note Deleting the entire table, not the table of contents deleted only if it is in the software, then it is to choose to delete the table, right click and choose OK when Delete Table can be deleted

Insert picture description here

heavy The operation of naming the table and deleting the table only need to change the keyword to RENAME. Right-click the table on the software and there is also an option for Rename, click to enter the renamed table name.

Example 4: Create a complete table

Before creating a table, you must first understand the types of fields. The main summary is 4 types (Baidu will have a lot of explanations on this part of the type, so I won’t go into details here)

  • Numerical types: int and float
  • String types: varchar and text
  • Boolean: 0 and 1
  • Date type: date and timestamp

Note: If you use utf-8 Chinese input when the length is specified, one Chinese character is equivalent to two English characters, that is to say, when length=200, the number of Chinese characters that can be stored is about 100; for transactions or calculations The value can be of decimal type

Here is created by software, as follows (in this way, you can directly input by mouse selection)

Insert picture description here

After inputting, do not worry about clicking the save button, you can check the corresponding SQL statement, that is, click the SQL Preview in the tab to view it. The code is as follows.

Insert picture description here

If you find that the SQL Preview is empty after saving, don’t worry, right-click the created table name, and then select Dump SQL File -> Structure only, select a place to store the file, and then you can see it after opening it. when construction of the table corresponding to the SQL statement

Insert picture description here

corresponding to the following statement found that some type is not specified length, automatically assigned; and other settings, such as the character set, the storage engine, calibration mode and format have default values

Insert picture description here

than is The table is created by manually operating the software, and then the SQL statement is obtained. Then there is a question-can I directly use the generated SQL statement to create the table directly on the software? Because there are also many SQL table creation statements on the Internet, you can create a table directly by copying it and it is not beautiful. The answer is yes, click on the New Query button at the top left, and then you can enter the SQL code in the editing area. You can copy the generated code and modify the name of the table (for example, change it to demo1 to avoid overwriting the original demo table. ), and finally click Run to see how the output results

Insert picture description here

2.4 Create a case data table

The design of the case data table has been introduced above, followed by the creation of the table, then the next step is to create the form according to the requirements (or use the New Query method above to create). Note: Since it was a test demonstration before, the following is the creation of an example form. It is recommended to create a new database to store the form. For example, the database table I created here is named erp, because Chinese data may be added later, so the specified character set is utf -8

# Create customers table
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)

# Create orderitems table
CREATE TABLE orderitems
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item) #联合主键

# Create orders table
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)

# Create products table
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)

# Create vendors table
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)

# Create productnotes 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),

# Define foreign keys
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

The output result is: (The operation of the foreign key will be explained later, here is directly loaded. Note that you have to select the new erp database to be created, and then copy and paste the code in the database into the Query, and finally run)

Insert picture description here

You can check it through the software The relationship of these six tables (double-click Tables under the erp database), as follows. So

Insert picture description here

far, all the required forms in the case database are created, but there is no data in the form, so you need to load the data into the corresponding form, because If the amount of data is large, pasting is not performed here (the data has been uploaded to the resource, and you can download it if you need it), and the page where the data is loaded is directly given. Then you can perform processing operations on the data in the database.

Insert picture description here

Three, MySQL basic method of processing data

3.1 MySQL data retrieval

Example 1: View the design information of the table

That is, to view the relevant information when the table was originally created, the code instruction: for desc 表名;example, view the product table here

Insert picture description here

Example 2: All data query of the table

SQL sentences are composed of simple English words. These words are called keywords, and each SQL statement is composed of one or more keywords. Probably, the most frequently used SQL statement is the SELECT statement. Its purpose is to retrieve information from one or more tables.

In order to use SELECT to retrieve table data, you must give at least two pieces of information-what you want to choose and where to choose.

You can choose to view all the data in the table, or you can specify some fields. The code command to view all the data is:select * from 表名;

Insert picture description here

Example 3: Multi-field data query

Select the content of some fields to view (if you operate directly after importing the data, the field name code completion will not appear, this function will be available after restarting the Navicat software), the code instruction:select 字段1,字段2,... from 表名;

Insert picture description here

Example 4: Field unique value query

In the vend_id field, I found that there are many duplicate values. If you want to query for unique values, the code command: SELECT DISTINCT 字段名 FROM 表名;(Uppercase and lowercase words appear. Obviously lowercase words are entered manually, and uppercase ones are automatically completed using the system's tab. Features)

Insert picture description here

Example 5: Qualified query

Limit the number of rows to query, the limit is placed after the table name, the code command: select * from 表名 LIMIT 4;

Insert picture description here

"page" query, according to the specified length, query the next data of the same length, the code command: select * from 表名 LIMIT 数值1,数值2;(Compare the table for querying all data, the prod_id is FB data in Article 5, so the output result is Article 5-8 data)

Insert picture description here

3.2 MySQL data sorting

Example 6: Sorting of single and multiple fields

The first is to sort a single field, the code instruction: SELECT 字段1,字段2 from 表名 ORDER BY 字段 升降序;(the default is ascending asc, you can specify descending desc)

Insert picture description here

What if it is not a number, but a sort of character data? You can try to use the prod_name field to view. Here you can see that if you sort in ascending order, first display the first character, then compare the value, and finally compare the order of the letters.

Insert picture description here

The sorting of multiple fields is also required, for example, there is more When they are the same, they will consider sorting according to the rules of another field, the code instruction:SELECT 字段1,字段2 from 表名 ORDER BY 字段,字段;

Insert picture description here

Example 7: Sort by non-view field data

Is there such a requirement-for example, the data I choose to view, not the data sorted by this field? Of course it also exists, such as when we want to get the id and name of the supplier sorted by price.

If you only perform a single-field query output result, there is definitely no problem (because a single field does not have the same part, naturally there is no comparison), but when you want to output multiple fields, there may be changes in sorting, such as choosing to output all fields When only the supplier id and name are output, the order of the results may be different. The

Insert picture description here

solution is to sort by multiple fields. If you want to output the secondary sorting standard by which field, select this field as the second field for sorting. , For example, the second choice here is prod_name, and the output result is as follows, which solves this problem (when querying multiple fields, if part of the information content is the same, it is recommended to add multiple sets of sorts)

Insert picture description here

Example 8: Find the maximum and minimum values

Corresponding to finding the most expensive and cheapest products, this practical skill is to sort the prices of the products and select the first result in combination with limit. Code instruction:
minimum value: SELECT * from products ORDER BY prod_price LIMIT 1;
maximum value:SELECT * from products ORDER BY prod_price desc LIMIT 1;

Insert picture description here

3.3 MySQL data filtering

Basic knowledge of data filtering:

  • Database tables generally contain a large amount of data, and it is rarely necessary to retrieve all rows in the table. Usually only a subset of the table data is extracted based on the needs of a specific operation or report. To retrieve only the required data, you need to specify search criteria.
  • Search conditions are also called filter conditions. In the SELECT statement, the data can be filtered according to the search criteria specified in the WHERE clause. The WHERE clause is given after the table name (FROM clause)

Example 9: where condition filtering

For example, if you find a product with a price of 2.5 in the product table, this is a conditional filter. The code instruction: SELECT * FROM products WHERE prod_price = 2.5;

Insert picture description here

The operator of where conditional filter, when the condition is judged, the operator needs to be compared with the given value, which is common Operators are: =, >, <, >=, <=, !=, between num1 and num2etc.

  • SELECT * FROM products WHERE prod_price >= 2.5;
  • SELECT * FROM products WHERE prod_price BETWEEN 5 AND 10;
  • select vend_id,prod_name from products where vend_id <> 1003;(!= and <> are both operators that indicate culling)

★★ Null value check

For example, here was added manually in the products table row of data, and the data input field does not prod_desc, to query the last row of data where this value is null, code instructions: SELECT * FROM products WHERE prod_desc is NULL;

Insert picture description here

★★★★ advanced operator using

The above commonly used operators are used for numerical judgments. Next, I will introduce some advanced (complex) operations. The above WHERE statement uses a single condition when filtering data. For stronger filtering control, MySQL allows multiple WHERE clauses to be given. These clauses can be used in two ways: in the form of an AND clause or in the form of an OR clause.

1) The use of logical operators

  • select prod_id , prod_price,prod_name from products where vend_id= 1003 and prod_price <=10;
  • select vend_id,prod_id , prod_price,prod_name from products where vend_id = 1002 or vend_id = 1003;

The above code instructions realize the logical judgment of and and or, and narrow the search range, or expand the search range

Insert picture description here

2) Calculation sequence operation

When performing logical operator operations, two operators, and and or may appear at the same time. Then there will be a question, which one is calculated first? It also involves the issue of calculation order

  • select vend_id,prod_id , prod_price,prod_name from products where vend_id = 1002 or vend_id = 1003 and prod_price <= 10;
  • select vend_id,prod_id , prod_price,prod_name from products where vend_id = 1002 and prod_price <= 10 or vend_id = 1003 ;

The conditions of and and or at the end here are swapped. Look at the output result. The first type is 7 pieces of data, and the second type is 9 pieces of data. The line of code instruction to be written is to complete: select supply The trade number is 1002 and the product price does not exceed 10 or all the products numbered 1003 (corresponding to the second, the first is the wrong way of use) when

Insert picture description here

and and or appear at the same time after where, it is recommended The written specification is that the first part to be satisfied is connected with and in the front, and then the or connection is used to meet the following conditions (and then or is recommended). If you must use or in the front, it is recommended that the following content use parentheses to indicate the scope, so There will be no problems with understanding

  • select vend_id,prod_id , prod_price,prod_name from products where vend_id = 1002 and prod_price <= 10 or vend_id = 1003 ;
  • select vend_id,prod_id , prod_price,prod_name from products where vend_id = 1003 or (vend_id = 1002 and prod_price <= 10) ;

3) The member judgment operator is
used to judge whether the data exists or not. Usually, given a data range and then judge whether the data to be searched is in this range, it belongs to the category of member judgment. The operators used are: in, not in

  • SELECT * from products where vend_id in (1002,1003,1005);
  • SELECT * from products where vend_id not in (1002,1003,1005);
Insert picture description here

Example 10: Wildcard filter data

All the operators introduced earlier filter against known values. Whether it is matching one or more values, testing greater than or less than a known value, or checking a range of values, the common point is that the values ​​used in filtering are all known. However, this filtering method is not always easy to use, such as how to search for all products that contain the text anvil in the product name? Simple comparison operators will definitely not work, wildcards must be used. Use wildcards to create search patterns that compare specific data.

Knowledge about wildcards:

  • Wildcard (wildcard) is a special character used to match part of a value;
  • Search pattern (search pattern) is a search condition composed of literal values, wildcards, or a combination of both;
  • In order to use wildcards in the search clause, the LIKE operator must be used. The search pattern followed by LIKE uses wildcard matching instead of direct equal matching for comparison

1) Use the like operator to filter data

比如筛选产品名称中以Jet开头的数据,代码指令:SELECT * from products where prod_name LIKE 'jet%'; 其中待匹配的字符大小写均可,%代表着是任意长度的字符

Insert picture description here


  • SELECT * from products where prod_name LIKE '%anvil';
  • SELECT * from products where prod_desc LIKE '%anvil%';


Insert picture description here


通过上面的示例可以发现我们可以准确的根据给出的内容去匹配原数据,但是有时候人的记忆不是那么好用,就只记得部分残缺的字符,那么这时候要匹配数据就可以使用到模糊匹配,比如在prod_name字段中之前扫了一眼记得有个数据里面包含sead还是seed的,想要筛选出这条数据,代码指令:SELECT * from products where prod_name LIKE '%s%d%'; 不记得部分直接使用%替代即可

Insert picture description here

3)_ 下划线通配符

采用模糊匹配是很无脑简单,但是吃计算机的性能,特别是在数据量较大的时候,这样无脑的匹配就很话费时间,因此如果记忆具体的单词的字符个数,就可以使用下划线逐字匹配的方式,还是以上面的内容为例,知道是sead或者是seed,确定是四个字符了,代码指令:SELECT * from products where prod_name LIKE '%s__d%'; 将不确定的位置用下划线代替,这样会很精准,运行效率会比模糊匹配较高。

Insert picture description here


4.1 MySQL的正则表达式搜索



  • select prod_name from products where prod_name like '%1000';
  • select prod_name from products where prod_name REGEXP '1000';


Insert picture description here

2) 匹配单个字符

  • select prod_name from products where prod_name like 'JetPack _000';
  • select prod_name from products where prod_name REGEXP '.000';


Insert picture description here


  • select prod_name from products where prod_name like '\\.%';
  • select prod_name from products where prod_name REGEXP '\\.';


Insert picture description here

还有一些字符属于这类的原生字符,比如\f、 \n、 \r 、\t、 \v 、\、(、)等,在使用匹配的时候注意前面加斜杠


  • select prod_name from products where prod_name like 'JetPack _000';
  • select prod_name from products where prod_name REGEXP '1000|2000';


Insert picture description here


  • select prod_name from products where prod_name like 'JetPack _000';
  • select prod_name from products where prod_name REGEXP '[12] ton';
  • select prod_name from products where prod_name REGEXP '[1-9] ton';


Insert picture description here

6) 剔除匹配

  • select prod_name from products where prod_name like 'JetPack _000';
  • select prod_name from products where prod_name REGEXP '[^345] ton';


Insert picture description here

7) 困难的精准匹配

  • select prod_name from products where prod_name like 'JetPack _000';
  • select prod_name from products where prod_name REGEXP '\\([0-9] sticks?\\)';


Insert picture description here


4.2 MySQL计算字段




比如供应商的名称与所在的国家进行数据的拼接,最后为了方便识别,国家使用括号进行分隔,代码指令:select vend_name,vend_country, CONCAT(vend_name , ' (' ,vend_country , ')' ) from vendors;

Insert picture description here


这里就是对于存在空格的数据进行格式的标准化,去掉左右的空格后,在进行拼接组合,代码指令:select vend_name as v_n,vend_country as v_c,CONCAT(vend_name , ' (' ,LTRIM(RTRIM(vend_country)) , ')' ) as vend_title from vendors; LTRIM和RTRIM一般配合使用,去掉左右的空格,对于要查看的字段数据,可以使用as的方式进行重命名(as可以省略,当然写了更容易理解)。字段格式化还可以使用到其他的函数,接下来就会进行详细的介绍

Insert picture description here


进行产品的单价和数量的计算,从而可以获取总价的数据,代码指令:SELECT quantity q,item_price i, quantity*item_price sum FROM orderitems;

Insert picture description here

4.3 MySQL数据处理函数


  • 用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数
  • 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数
  • 用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数
  • 返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数


该函数功能主要是针对英文数据来操作,代码指令:SELECT vend_name,UPPER(vend_name) as vend_name_upper,LOWER(vend_name) as vend_name_lower from vendors;

Insert picture description here


把字符串的内容取出部分,就是指定切割位置,然后拿出切割位置之间的数据,代码指令:select vend_name ,SUBSTRING(vend_name ,1,5) as vend_name_upper from vendors order by vend_name;




Insert picture description here


这个函数的功能在处理英文数据时候是可以用到的,但是中文就比较差,原理就是根据发音的相似性进行数据的匹配,比如根据li来找lee,或者use寻找usa等,代码指令:select cust_name ,cust_contact from customers where soundex(cust_contact) = soundex('Y Li');

Insert picture description here


如果只需要提取日期的信息,也就是截止到日,那么可以使用Date()函数进行转化,还可以借用之前的判断语句between and筛选出一定日期范围内的数据,代码指令如下:

  • select cust_id ,order_num ,order_date from orders where Date(order_date) = '2005-09-01';
  • select cust_id ,order_num ,order_date from orders where Date(order_date) BETWEEN '2005-09-01' AND '2006-09-01';


Insert picture description here


比如指定年月的查询:select cust_id ,order_num ,order_date from orders where YEAR(order_date) = '2005 'AND MONTH(order_date) = '9';

Insert picture description here


Insert picture description here

4.4 MySQL数据聚集


  • 确定表中行数(或者满足某个条件或包含某个特定值的行数);
  • 获得表中行组的和;
  • 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值



使用count()函数用来统计数据的多少(可以填入*,也可以填入某一字段的名称),代码指令: select count(prod_id) as num_count from products;

Insert picture description here


使用sum()函数统计最后的数据总和,可以配合着条件判断指定满足条件下的数据求和,代码指令:select sum(quantity) from orderitems where order_num = 20005;

Insert picture description here



select avg(prod_price) from products;

select avg(prod_price) from products where vend_id = 1003;

Insert picture description here



  • select max(prod_price) from products;
  • select min(prod_price) from products where vend_id = 1003;
Insert picture description here

最后可以将多个汇总函数都在查询中进行输出,代码指令:select count(*) as num_items , min(prod_price), max(prod_price) , avg(prod_price) from products;

Insert picture description here

4.5 MySQL数据分组


这时候就需要对数据进行分组了,分组允许把数据分为多个逻辑组,SQL中通过使用GROUP BY关键字以便能对每个组进行聚集计算。

  • GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制;
  • 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据);
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名;
  • 除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出;
  • 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组;
  • GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前



比如查看一下不同的供应商提供的商品的总数量,代码指令:select vend_id,count(*) from products GROUP BY vend_id; 通过这个执行的代码可以看出分组的基本格式select…from之间的内容是要进行分组的字段(这个字段不能是计算字段),然后指出分组对应的汇总统计方法,group by后面就为指定分组的这个字段(前后字段是一致的)

Insert picture description here

在进行表格处理的时候,有时会在字段的最后面的一个格子上进行汇总数据的输入,这里就可以通过添加with rollup解决

Insert picture description here


光有数据分组还是远远不够的,比如要对分组的数据中进行筛选,提取可以提供产品数量超过3种的数据,这里就需要进一步过滤数据了,SQL中使用having关键字进行分组过滤(having后的内容就是之前的汇总函数),代码指令:select vend_id,count(*) from products GROUP BY vend_id HAVING count(*)>3;

Insert picture description here

既然都已经过滤完数据了,那么顺带进行数据的排序不过分吧,只需要最后加上order by就行(order by后的内容也是之前的汇总函数),可以对使用的汇总函数进行重命名来减少代码量,代码指令:select vend_id as v,count(*) as c from products GROUP BY v HAVING c>3 ORDER BY c desc;

Insert picture description here


首先是顺序:select > from > where > group by > having > order by > limit

规律就是在使用分组中代码指令可以归纳为:select 字段,汇总函数 from 表 GROUP BY 字段 HAVING 汇总函数>3 ORDER BY 汇总函数 desc limit 1; 对照上面的输出方便进行理解,核心要记住:分组是针对于目标字段、过滤和排序是针对于汇总函数、最后面放置升降序和限制输出。


5.1 MySQL子查询




Insert picture description here


  • SELECT * from orderitems where prod_id = 'TNT2';
  • select * from orders WHERE order_num in (20005,20007);
Insert picture description here

以上就实现了最初我们想要的结果,但是要执行两次,如果使用子查询的话就可以将两次执行的语句进行结合,代码指令如下:select cust_name ,cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id ='TNT2')); 究极套娃,既然可以找到购买用户的id,那么顺便就找一下详细的姓名和地址了。


Insert picture description here


前面可以根据商品信息查询到用户的信息,那么现在尝试把用户和他们的消费记录关联起来(也就是有多少份订单),所以查询结果的最后一列应该就属于计算字段,那么也是可以使用子查询操作的,代码指令:select cust_name ,cust_state ,(select count(*) from orders where orders.cust_id= customers.cust_id ) as frequence from customers; 这里使用了联结查询,接下来就介绍

Insert picture description here


5.2 MySQL联结表




Insert picture description here

每个表中都会有一个唯一的标识字段,也称主键(Primary Key),这里都是id,然而发现上图右侧下方的表中还有一个company_id字段,里面的内容刚好是和它上方的供应商信息单中的id对应,这种连接两个表的字段(列)就成为外键(Foreign Key),外键包含另一个表的主键值(也就是这里产品表中的company_id值包含了供应商中的id值)


  • 供应商信息不重复,从而不浪费时间和空间;
  • 如果供应商信息变动,可以只更新单表中的单个记录,相关表中的数据不用改动;
  • 由于数据无重复,显然数据是一致的,这使得处理数据更简单。

实例3 连接多个关联表

比如查询供应商名称,商品名称和商品价格,这三个数据明显是在供应商表和产品表中,通过联结操作就可以直接进行查询,代码指令:select vend_name,prod_name,prod_price from vendors ,products where vendors.vend_id = products.vend_id; 可以查询的前提就是where语句后的内容(也就是两个表有共同的部分)

Insert picture description here

之前已经使用过重命名的方式,这里也可以对表名进行重命名,有些单词比较长的表名就可以使用简写的方式进行,代码指令:select vend_name,prod_name,prod_price from vendors v ,products p where v.vend_id = p.vend_id; 结果输出是一致的

Insert picture description here

这种使用表名.列名 = 表名.列名的方式属于内连接(inner join),标准默认的连接方式。上面举例了两表的连接,那么接下来看看三表连接如何实现,这里选择客户表、订单表、商品信息表进行操作,比如查询一下什么人在什么时间买了’TNT2’这个商品,代码指令:SELECT cust_name,order_date,prod_id from customers c,orders o, orderitems oi where c.cust_id = o.cust_id and o.order_num = oi.order_num and prod_id = 'TNT2';

Insert picture description here

5.3 MySQL高级联结表




Insert picture description here

要展示出员工和其上级的关系,因此就只能使用自联结进行查询了,代码指令:select,(select from emp e2 where e2.job_num = e1.head_num) as boss,e1.level from emp e1; 使用自身的表,然后要求匹配到工号与主管号一直的情况,这样就知道员工的层级关系了

Insert picture description here


就是直接使用两张表,让其野蛮匹配,如果不加限制,最后就是两张表中的每行数据都会相互匹配,假如表中有十行数据,那么自然联结后就是100行数据,比如进行产品表自然联结,代码指令:select p1.prod_id, p2.prod_name from products p1 , products p2; 产品表中15行数据,最终自然联结后就是225行数据,这种方式的使用不是很多,一般是在测试、做实验的时候会用到,也就是要考虑不同的工况,使用自然联结就会把所有的情况都考虑到

Insert picture description here



Insert picture description here


  • SELECT * from customers c,orders o where c.cust_id = o.cust_id; (内联结)
  • SELECT * from customers c LEFT JOIN orders o on c.cust_id =o.cust_id;(外联结)


Insert picture description here



  • select cust_name,c.cust_id,count(*) from customers c ,orders o where c.cust_id = o.cust_id GROUP BY c.cust_id;(内联结分组)
  • select cust_name,c.cust_id,count(*) from customers c LEFT JOIN orders o on c.cust_id = o.cust_id GROUP BY c.cust_id;(外联结分组)


Insert picture description here


6.1 MySQL组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句),并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)


  • 在单个查询中从不同的表返回类似结构的数据;
  • 对单个表执行多个查询,按单个查询返回数据;



  • select vend_id,prod_id from products where prod_price <= 5;
  • select vend_id,prod_id from products where vend_id in (‘1001’,‘1002’);


Insert picture description here

基于结果发现,那么就可以将查询的结果进行组合(union),只需要把第一个查询语句后的’;'去掉然后加上union关键词即可,代码指令:select vend_id,prod_id from products where prod_price <= 5 UNION select vend_id,prod_id from products where vend_id in ('1001','1002'); 组合后的结果会被自动去重

Insert picture description here


Insert picture description here


按照之前梳理的关键词的使用顺序,那么要对组合结果排序自然是要使用order by,union两个查询自然也就变成了一个整体,那么order by的位置就应该在最后一个where后面(没有group by和having,其前面的一个关键词就是where),如果忘记了,可以查看分组过滤最后关于关键词使用顺序的总结。代码指令:select vend_id,prod_id ,prod_price from products where prod_price <= 5 union select vend_id,prod_id,prod_price from products where vend_id in (1001,1002) order by vend_id , prod_price;

Insert picture description here


select vend_id,prod_id ,prod_price from products
where prod_price <= 5
select vend_id,prod_id,prod_price from products
where vend_id in (1001,1002)
select vend_id,prod_id,prod_price from products
where vend_id in (1003,1005)
order by vend_id , prod_price;


Insert picture description here

6.2 MySQL全文检索


  • 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引)。因此,由于被搜索行数不断增加,这些搜索可能非常耗时;
  • 明确控制——使用通配符和正则表达式匹配,很难(而且并不总是能)明确地控制匹配什么和不匹配什么。例如,指定一个词必须匹配,一个词必须不匹配,而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配




  • like
  • regex
  • match against

开启全文索引除了引擎的要求,还有对于字段数据的要求,必须为text类型,索引类型为Full Text类型,这三项设置完毕后就可以开启

Insert picture description here


Insert picture description here


  • select note_text from productnotes where note_text like '%rabbit%';
  • select note_text from productnotes where Match(note_text) AGAINST ('rabbit');


Insert picture description here


操作的方式就是把match…against语句放置在from前面,然后删除where即可,代码指令:select note_text,MATCH(note_text) AGAINST ('rabbit') from productnotes;

Insert picture description here


使用布尔查询模式,只需要在上面的语句最后括号内添加in boolean mode。首先介绍一下这里进行布尔操作的符号

  • +:必须包含;
  • -:必须不包含;
  • >:增加优先等级;
  • <:降低优先等级;
  • *:词尾通配符;
  • “”:定义短句


  • select note_text from productnotes where note_text like '%rabbit%bait%';
  • select note_text from productnotes where MATCH(note_text) AGAINST ('+rabbit +bait*' IN BOOLEAN mode );

比如再进行优先级查询,代码指令:select note_text, MATCH(note_text) AGAINST ('+safe +(<combination)' IN BOOLEAN mode ) from productnotes where MATCH(note_text) AGAINST ('+safe +(<combination)' IN BOOLEAN mode ); <代表符号后面的单词要在指定的单词的后面(combination必须在safe后)

Insert picture description here

6.3 插入数据

select是最常使用的SQL语句了,也就是前面花这么多时间进行梳理的原因, 此外还有其它的关键词,首先就是要介绍的insert,是用来插入(或添加)行到数据库表的,可以完成的工作包括如下:

  • 插入完整的行;
  • 插入行的一部分;
  • 插入多行;
  • 插入某些查询的结果。


1)单行数据的插入,代码指令:insert into customers values ( null,'ABC','100 Main street','Los angeles','CA','90046','USA',null,null); 需要指定要插入的表单名称,然后values()括号中要填写对应字段的数据,插入的数据的个数应该与原表每行数据的个数相等,允许部分字段有空值。

Insert picture description here

2)多行数据的插入,就是多次执行insert into语句的操作,中间记得以 ‘;’ 分割,还有一点就是部分数据的插入,如果不想指定的字段数据就得一直写null,那么就可以直接在表名后面加个括号,后面添加对应要添加的字段,最后插入的数据只要和括号中的内容对应即可,就可以节省代码量的书写

Insert picture description here

可能我觉着这种方式还是比较麻烦的,insert into语句写了好几遍,有没有直接把数据合并在一起进行写入的方法,这里就可以使用括号中间以逗号分隔的方式进行合并,代码指令:insert into customers (cust_name,cust_zip) values ('2CDEF','199878'),('3CDEF','399878') ('4CDEF','599878'), ('5CDEF','799878'); 这种方式就方便多了,今后使用python进行数据插入的时候也可用

Insert picture description here


查询的结果有时需要插入到目的表单中,也可以使用insert into操作。代码指令:

  • create table customers2 as SELECT * FROM customers;(根据目标表创建备份表,操作备份的表)
  • DELETE FROM customers2;(删除备份表中的数据,一会就讲)
  • insert into customers2 select * from customers;(将查询的结果插入到备份的表中)


Insert picture description here

6.4 更新与删除


  • 更新表中特定行
  • 更新表中所有行



  • update customers2 set cust_email = '[email protected]' where cust_id =10005;
  • select * from customers2 where cust_id =10005;


Insert picture description here


删除操作就和更新类似,由于不要设置数据,set就不需要了,代码指令:delete from customers where cust_id = 10005;

Insert picture description here

6.5 MySQL视图


  • 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节;
  • 使用表的组成部分而不是整个表;
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限;
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据


其核心功能:简化sql语句,实现代码的复用,隐藏业务逻辑。比如将最初三表联结的例子拿过来演示,判断顾客到底买了多少东西?之前的SQL语句如下:select cust_name,c.cust_id,count(*) from customers c ,orders o where c.cust_id = o.cust_id GROUP BY c.cust_id; 那么如果直接创建视图(只需要在原来的代码前面加上create views 视图名 as),就可以把这个查询结果作为一个虚拟的表,供后来的人查询(有种数据分析师要数据,然后数据库工程师就开放所需部分数据的味道了)。执行创建视图的代码就可以发现在左侧的Views出刷新,就会出现刚刚创建的视图,这时候在查询区就可以直接对这个虚拟表进行查询了,不用再管之前怎样的业务逻辑。

Insert picture description here

如果需要查看一下创建视图的SQL语句,可以通过show create view 视图名;然后输出的第二个字段中的内容就是创建视图的SQL语句了

Insert picture description here

如果要删除的话,就是直接drop view 视图名; 通过以上操作发现对于业务逻辑来说,不管是专业还是新手,在工作处理的时候,任务来了,你只要整个视图把最终的结果给到数据分析师们就可以了。那么这里面的字符串的处理(格式化的操作,去空值什么的),在python中操作要比在MySQL中要方便的。还是要用好本质工具,MysSQL数据库,数据库主要就是用来进出数据的,数据清洗,就用python或者其他的方式就行了。

6.6 存储过程




delimiter //
create PROCEDURE productpricing()
select avg(prod_price) as avgprice 
from products;
end //
delimiter ;

call productpricing()


  • 创建存储过程为create PROCEDURE 名称()
  • SQL实现功能的语句要放在BEGIN和END之间;
  • 为了避免存储过程中存在一些原生字符(比如引号,分号等)造成错误,所以建议使用一个指定的标识符,一般上都是为delimiter //,最后再把标识符改回来;
  • 最后存储过程定义之后会在左侧的Functions中出现(记得刷新),使用时候调用一下即可
Insert picture description here

删除存储过程:drop procedure 过程名;




create PROCEDURE productpricing2(
out pl decimal(8,2),
out ph decimal(8,2),
out pa decimal(8,2)
   select min(prod_price) into pl 
   from products;
   select max(prod_price) into ph 
   from products;
   select avg(prod_price) into pa 
   from products;


call productpricing2(@p1,@p2,@p3);
select @p1,@p2,@p3;


Insert picture description here


create procedure ordertotal (
in onumber int,
out ototal decimal(8,2)

   select sum(item_price* quantity)
    from orderitems
    where order_num=onumber
    into ototal;

CALL ordertotal('20005',@p);



Insert picture description here

6.7 游标



1) 创建游标

CREATE PROCEDURE processorders()
  declare ordernumbers CURSOR
  select order_num from orders;

  open ordernumbers;

  close ordernumbers;



CREATE PROCEDURE processorders4()

  -- declare VARIABLES
  DECLARE o int;
  DECLARE done boolean DEFAULT 0;

  declare ordernumbers CURSOR
  select order_num from orders;

  declare continue handler for sqlstate '02000' set done=1;

  -- OPEN
  open ordernumbers;


      FETCH ordernumbers into o;
      select o;
  until done end repeat;

  -- CLOSE
  close ordernumbers;


call processorders4()


Insert picture description here

6.8 触发器


实例11 创建触发器

触发器使命:在我们执行delete update insert语句时自动执行另外一组sql语句的功能。比如进行插入提醒

create trigger newproduct after insert on products
for each row select 'Product add' into @ee;

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV21', 1001, '3 ton anvil', 19.99,
 '3 ton anvil, black, complete with handy hook and carrying case');
 SELECT @ee;


Insert picture description here

实例12 触发器应用



drop TRIGGER  newproduct;
create trigger newproduct2 after insert on products
for each row select NEW.prod_id into @ee;

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV22', 1001, '3 ton anvil', 19.99,
 '3 ton anvil, black, complete with handy hook and carrying case');


Insert picture description here


create trigger deleteorder  before  delete on orders
for each row 
   insert into archive_orders(order_num,order_date,cust_id)
    values (old.order_num,old.order_date,old.cust_id);


CREATE table archive_orders as SELECT * from orders;
DELETE from archive_orders;

SELECT * from archive_orders;


Insert picture description here


7.1 MySQL事务管理


  • 事务(transaction)指一组SQL语句;
  • 回退(rollback)指撤销指定SQL语句的过程;
  • 提交(commit)指将未存储的SQL语句结果写入数据库表;
  • 保留点(savepoint)指事务处理中设置的临时占位符(place_holder),你可以对它发布回退(与回退整个事务处理不同)





select * from customers2;
delete from customers2;
select * from customers2;
select * from customers2;

输出结果:(首先查询时候会自动查询到全部的数据,然后开启事务,并删除里面的所有数据,在查看时候结果2中就是空的,但是又来了一个rollback进行事物回滚,也就是回到了start transaction之前的状态,所以在此查询的时候就是相当于第一条查询语句,结果也就和最初的一样了)

Insert picture description here



Insert picture description here

实例3: savepoint存档


CREATE table customers2 as SELECT * from customers;
select * from customers2;
delete from customers2 where cust_id = 10001;
delete from customers2 where cust_id = 10002;
select * from customers2;
select * from customers2;


Insert picture description here

7.2 数据备份与性能管理

7.2.1 数据备份操纵与文件加载与导出


  • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行,以便能正确地备份转储文件;
  • 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序);


如果选择使用软件备份,操作如下,选择要备份的数据库,然后点击右上方的Backup按钮后,选项卡上面显示的New Backup就是用来创建一个备份的操作,在弹出的界面核实一下是确定的数据库后点击备份就可以了。

Insert picture description here


Insert picture description here



mysqldump.exe -h -P 3306 -uroot -p --database erp > c:\mysql\createdb.sql


Insert picture description here


mysqldump.exe -h -P 3306 -uroot -p  erp1 orders  > c:\mysql\createdb2.sql


mysqldump.exe -h -P 3306 -uroot -p --database erp1 erp2 erp3 > c:\mysql\createdb3.sql


mysqldump.exe -h -P 3306 -uroot -p --all-database  > c:\mysql\createdb4.sql


mysqldump.exe -h -P 3306 -uroot -p --no-data --database erp1 > c:\mysql\createdb.sql


mysqldump.exe -h -P 3306 -uroot -p --add-drop-table --add-drop-database erp1  > c:\mysql\createdb2.sql

实例5: 数据的导入和导出


比如把查询的数据直接保存在本地,代码指令:select * from customers into OUTFILE 'c:/mysql/data1.csv';

Insert picture description here


比如把刚刚导出的数据重新导入到customers2中(首先删除表数据),代码指令:load data INFILE 'c:/mysql/data1.csv' into table customers2;

Insert picture description here

7.2.1 性能改善


  • 利用索引Index可以大幅改善查询时候的性能问题;
  • 利用一些操作系统的参数也可以提供数据访问的性能;
  • 使用Explain语句让MySQL解释它将如何执行一条SELECT语句;
  • 一般来说,存储过程执行得比一条一条地执行其中的各条MySQL语句快。

Here, perform several more operations when importing the data above. If you perform 20 times and then manually add a piece of data for the convenience of searching, then the final data in the customers2 table should be 101 pieces.

Insert picture description here

Next, let’s look at using the traditional method (the default method). ) To query the added data, use the Explain statement to let MySQL explain how it will execute a SELECT statement. Code instruction: In the EXPLAIN select * from customers2 where cust_id = 99999;output result, you can find 86 rows of the query, and find all the field types.

Insert picture description here

Click on the status bar to take a look. In this query, we sent a total of 387 bytes of data and received 60 bytes of data. Let’s take a

Insert picture description here

look at how the performance is optimized. The operating system parameters mentioned earlier should not be messed with (after all, your computer configuration is also very scumbag~ ), then try to simply use the index to improve speed. For example, the data I inserted here only has data in the cust_id field, so the cust_id field can be set as an index separately. The operation is as follows, enter the table design interface, click the indexes tab, and give it a name. , And then select Fields as the field name for the data to be queried (here cust_id). After clicking save, the Index Type and Index Method will be automatically configured. At

Insert picture description here

this time, re-run the query code just now to find the result of the query. Only one line of query is performed here and the result is obtained. This way of querying greatly improves the performance of the data and also improves the efficiency of the program.

Insert picture description here

At this point, the knowledge points of the entire MySQL are sorted out. If you need to be proficient in database operations The mastery, there will be a lot of practice later to consolidate

to sum up

In the blog, the basic knowledge points of MySQL are sorted out in detail. The command line operation and the visual graphic interface are combined to explain the operation, and the graphics are configured for the visual description of the places that are difficult to understand. Each example has not only the code display but also the final output graphic. The interface is displayed for easy reading and understanding. The place that I don’t feel is very easy to understand is the introduction of cursors. In the future, I will update the case of python operation database for comparative study.

It’s not easy to burst the liver, but we still have to live strong~

Insert picture description here