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

 知识梳理不易,请尊重劳动成果,文章仅发布在CSDN网站上,在其他网站看到该博文均属于未经作者授权的恶意爬取信息

If reprinted, please indicate the source, thank you!

Preface

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 127.0.0.1, 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

MySQLreality
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

  • CREATE DATABASE [IF NOT EXISTS] <数据库名> [[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>];

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)
) ENGINE=InnoDB;

#########################
# 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) #联合主键
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  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)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  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)
) ENGINE=InnoDB;

###########################
# 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),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# 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


查询中间或者末尾的数据,比如这里查询anvil数据(prod_name字段中是在末尾,prod_desc字段中是在中间),那么就可以把%提到相应的位置就可以了。故使用like通配符时,如果匹配的字符在开头,%在最后面;字符在末尾,%在最前面;字符在中间,%在两端。

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

由于只有这三条数据是包含anvil的,而且prod_name字段中是在末尾,prod_desc字段中是在中间,均都有anvil,所以两次检索的结果都是同样的数据

Insert picture description here


2)模糊匹配

通过上面的示例可以发现我们可以准确的根据给出的内容去匹配原数据,但是有时候人的记忆不是那么好用,就只记得部分残缺的字符,那么这时候要匹配数据就可以使用到模糊匹配,比如在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

四、MySQL数据处理的高级方法

4.1 MySQL的正则表达式搜索

如果对于python数据处理比较了解,re库就是必不可少的核弹工具了,可以为我们减轻大量的工作量,同样在SQL中也是可以使用正则表达式,比如用来从一个文本文件中提取电话号码和邮箱、数据中替换某一个数据、url网页链接等

1)基本字符匹配
比如匹配产品名称中包含100的信息,对比之前的like通配符(下同)

  • 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) 匹配单个字符
比如知道名称里面是有000,前面的千分位数值不管多少,都进行匹配,这里使用到了’.'代表匹配单个字符

  • 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

3)匹配原生字符
比如上面的.匹配单个字符,如果在实际需求中就是要提取包含‘.’的数据,就需要在符号前面使用双斜杠

  • 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 、\、(、)等,在使用匹配的时候注意前面加斜杠

4)使用多条件进行匹配
比如进行两选一条件满足皆可的匹配,这里不是使用or,而是使用’|'符号代替

  • 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

5)范围匹配
比如不限于两个条件,而且不想要匹配任意字符,就是要在指定的范围内进行数据的匹配,数据范围较小的时候可以都写出来,较大时候可以使用’-'来表示范围连接,比如常见的[0-9]、[a-z]等

  • 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


这里的’?‘代表着匹配0个或1个由前面的正则表达式定义的片段,如果不匹配带s的内容可以把上面的匹配模式中的s去掉;还有一个是’+'符号代表着匹配1个或多个的表达式。整体上这里的正则表达式的模式和python中的re库的匹配模式类似,还可以看出like通配符的使用在很多场景下失效的,因此要学会两者的互补。

4.2 MySQL计算字段

什么是计算字段?以创建的案例库中的物品订单表为例

表中储存的有物品的价格和数量、但是不必要存放每个物品的总价格(用价格乘以数量即可)。有时除了总价格,还可以需要根据表数据进行总数、平均数计算或其他计算。存储在表中的数据不是应用程序所直接需要的。我们可以根据需要直接从数据库中检索出转换、计算或格式化过的数据;而不是直接使用检索出数据,最后再在客户机应用程序或报告程序中重新格式化,这就是计算字段发挥作用的所在了。计算字段并不实际存在于数据库表中

实例1:字段拼接

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

Insert picture description here

实例2:字段格式化

这里就是对于存在空格的数据进行格式的标准化,去掉左右的空格后,在进行拼接组合,代码指令: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

实例3:数学计算

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

Insert picture description here

4.3 MySQL数据处理函数

对常见的函数进行一个分类,如下:

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

实例4:数据大小写转换

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

Insert picture description here

实例5:字符串切片

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

但是需要注意:这里的字符串切片和python的字符串切片是有区别的,前一个数值都代表第一刀切下的位置,但是第二个数值python中是指第二刀的位置(方向是不可逆),但是SQL里面是指第一刀切后的往后数第几个数值多对应的位置(最后一个数值始终代表着往右方向)

python中切片必须要从一个数字代表的位置向右到第二个数字代表的位置,比如下图中-4代表这c所在的位置,1代表着b所在的位置,所以切片的结果就是c从右开始到b之间的内容,显然是不可能的,所以最后切片的取值就为空

对比SQL中,第一个位置是相同的含义,但是第二个数字始终代表往右方向取值的多少,是表示最终要取的数量而不是代表位置

Insert picture description here

实例6:发音相似数据匹配

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

Insert picture description here

实例7:日期和具体时间数据处理

如果只需要提取日期的信息,也就是截止到日,那么可以使用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


除此之外:adddate()、addtime()、curdate()、curtime()、date()、datediff()、day()、year()、month()、hour()、minute()、now()、time()等均为常用的时间处理函数

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

Insert picture description here


其它没有详细介绍的时间函数,可以在编辑区直接举个小例子查看一下如何使用的,就是select接函数,函数内部要填写指定格式的数据

Insert picture description here

4.4 MySQL数据聚集

我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提供了专门的函数。使用这些函数,MySQL查询可用于检索数据,以便分析和报表生成。比如

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

实例8:数据汇总计数、求和、求平均、求最值

1)汇总计数

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

Insert picture description here


2)求和

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

Insert picture description here


3)求平均

使用avg()函数统计数据的均值,还可以指定某一字段在满足特定条件下的均值

select avg(prod_price) from products;

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

Insert picture description here


4)求最值

求解最大值使用max()、最小值min()函数,同样也可以指定某一字段在满足特定条件下的最值

  • 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聚集函数可用来汇总数据,能够对行进行计数,计算和与平均数,获得最大和最小值而不用检索所有数据。那么现在有需求如果要返回每个供应商提供的产品数目怎么办?或者返回只提供单项产品的供应商所提供的产品,或返回提供10个以上产品的供应商怎么办?

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

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

以上的文字信息有点抽象,可以配合着下面的实际操作进行演示

实例9:创建数据分组

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

Insert picture description here


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

Insert picture description here

实例10:分组过滤

光有数据分组还是远远不够的,比如要对分组的数据中进行筛选,提取可以提供产品数量超过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; 对照上面的输出方便进行理解,核心要记住:分组是针对于目标字段、过滤和排序是针对于汇总函数、最后面放置升降序和限制输出。

五、MySQL子查询和联结表

5.1 MySQL子查询

SELECT语句是SQL的查询。以上的操作所有SELECT语句都是简单查询,即从单个数据库表中检索数据的单条语句。SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。常见场景:作为计算字段使用子查询和利用子查询进行过滤

实例1:创建子查询

有这么个需求:根据出售的商品信息来找到对应的购买用户信息。结合之前设计的表结构,顾客是直接和订单关联的然后才和订单中的商品信息关联,因此要想根据出售的商品信息来找到对应的购买用户信息,需要进行中间的一个订单编号的查询,才能继续下去,即两次查询语句可以满足要求(别忘了下图,很清晰的展现这个过程)

Insert picture description here

比如这个商品的信息就是‘TNT2’,接着就找这个商品对应的购买用户的信息。首先第一步是查询商品对应的订单编号,然后就是进行条件判断是否订单的编号在筛选后目标数据中,最后得到的数据中就包含了我们要找的购买用户的信息

  • 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,那么顺便就找一下详细的姓名和地址了。

可以发现子查询看上去很复杂,其实就是将要查询的每一步数据直接用SQL语句替代了

Insert picture description here

实例2:子查询作为计算字段使用

前面可以根据商品信息查询到用户的信息,那么现在尝试把用户和他们的消费记录关联起来(也就是有多少份订单),所以查询结果的最后一列应该就属于计算字段,那么也是可以使用子查询操作的,代码指令: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联结表

SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用SQL的SELECT能执行的最重要的操作,很好地理解联结及其语法是学习SQL的一个极为重要的组成部分

为什么会有联结表?
假如有由同一供应商生产的多种物品,那么在何处存储供应商信息(如,供应商名、地址、联系方法等)呢?数据如果都放置在一个表中势必会因为供应商的信息重复多条而极大的浪费存储空间,而且一点发生供应商信息的修改,那么整个表中的信息都必须得修改。

可能字面上不好理解,看一下图示。把一张表拆解成两张表看似分工变多了,实则业务变得更加精确了,比如这里要修改公司的信息,小米修改为小米科技公司,那么只需要改一处就可以了。给出的只是示例,有可能还有公司的地址,邮箱、介绍等,那么这些信息的修改如果再一个表中,修改就是要针对大量数据进行操作,分成两个表后,就修改一行数据就可以了。

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高级联结表

以上使用的联结表,只是内部联结(内连接)或者叫做等值联结的简单联结,还有其他的三种方式,分别是自联结、自然联结和外部联结。

实例4:自联结

顾名思义就是自己和自己联结,那这样有什么必要吗?直接进行输出不就可以了嘛,还要整个自己联结干嘛?其实主要的应用场景是这样的,就是在一张表中,都是平面的关系,如果要展示层级的关系的话,就需要使用自联结的方式,示意图如下

Insert picture description here

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

Insert picture description here

实例5:自然联结

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

Insert picture description here

实例6:外部联结

就是指定某一张表作为联结的依据,做个简单的示意图如下,如果以左边为依据,那么最终结果就会保留John的信息;反之就以右侧为依据

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;(外联结)

外联结数据量为6行,但是内连接只有5条(使用外联结应该是以数据量较多的一份表作为联结依据,这样会保留数据较多的那份数据,不然选择较小的那份数据,就相当于内连接了)

Insert picture description here

实例6:带有聚集函数的联结

这个问题之前已经提到过了,可以看一下子查询作为计算字段使用的实例,这里再进行一些分组和使用外联结的操作。代码指令:

  • 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;(外联结分组)

输出结果和上面的外联结实例类似,对于内联结没有出现cust_id=10002的数据,但是外联结就可以出现。

Insert picture description here

六、MySQL高级数据查询

6.1 MySQL组合查询

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

需求场景:

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

实例1:单个查询从不同的表中返回类似结构的数据

关于相似结果的理解:就是查询的是相同字段,比如分别根据供应商编号和商品价钱返回供应商信息,首先执行两条查询语句

  • 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


如果不希望查询结果自动去重可以在union后面添加all,这样最后的结果就会全部显示出来

Insert picture description here

实例2:组合结果进行排序及union多次使用

按照之前梳理的关键词的使用顺序,那么要对组合结果排序自然是要使用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
union 
select vend_id,prod_id,prod_price from products
where vend_id in (1001,1002)
union 
select vend_id,prod_id,prod_price from products
where vend_id in (1003,1005)
order by vend_id , prod_price;

输出结果证明是可以进行三张表的组合,因此这个unoin关键词可以组合很多表按照自己的需求来进行整理,组合的表越多,最后的输出结果可能也就越多了

Insert picture description here

6.2 MySQL全文检索

之前介绍了关于数据库中的文本数据匹配的知识,但是存在着几个重要的限制,如下:

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

实例3:启动全文索引

这就要填一下最开始介绍引擎时候埋下的坑了前面介绍了innodb不支持全文索引(支持事务),myisam支持全文索引(不支持事务),所以要使用全文索引,就必须开启myisam引擎。而在创建表单的时候对于产品信息注释表(productnotes)选用了该引擎,因此下面就可以对此表进行相关的操作

常见的文本匹配关键词:

  • like
  • regex
  • match against

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

Insert picture description here


查看一下目标字段的信息,可以看出productnotes表中的note_text字段为文本数据

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');

比如上述代码均为检索包含’rabbit’的数据

Insert picture description here

实例2:查询匹配词的优先级

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

Insert picture description here

实例3:布尔查询模式

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

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

进行布尔查询,就可以解决经典模式中无法精准查询的问题。就可以指定某些单词包含和哪些单词不包含,比如在包含’rabbit’情况下还必须要包含’bait’,代码指令:

  • 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,是用来插入(或添加)行到数据库表的,可以完成的工作包括如下:

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

实例4:插入行数据

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

实例5:插入查询结果

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

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

为了保证数据的安全性,养成操作备份数据的习惯,这样不会对原数据造成影响,出问题了也可以随时修改。

Insert picture description here

6.4 更新与删除

上面介绍了插入操作,顺带使用了delete删除指令,现在就介绍一下关于更新和删除的操作。为了更新(修改)表中的数据,可使用UPDATE语句。可采用两种方式使用UPDATE:

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

实例6:数据更新

同样删除操作也是如此,可以删除特定行,也可以删除所有行(上面的已经用过了)。关于更改和删除也应该和插入数据一样,在操作之前尽量使用备份数据进行操作,代码指令:

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

这样就实现了定点数据的更新。注意在进行更改的时候要先写where语句,最后再写select相关语句,不然会把所有的信息都给修改了,这步是不可逆的,一旦发生就麻烦了

Insert picture description here

实例7:数据删除

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

Insert picture description here

6.5 MySQL视图

视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询,重用SQL语句。注意相关细节:

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

实例8:创建视图及使用

其核心功能:简化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 存储过程

看到上面的操作是不是有种熟悉的感觉,就是有点类似于封装了函数,然后之后再调用了函数?其实并不是,上面只是将查询结果作为一个虚拟的表,方便后续人员操作,隐藏业务逻辑。而真实SQL里面真正类似函数的是存储过程(软件里面的名称就是Functions),可以根据输入提供输出,中间的处理过程封装好(多组sql语句形成的组合),直接拿过来用。

实例9:存储过程的使用

首先是存储过程的使用,使用之前应该声明一下这是各存储过程(所有编程语言都是一样,得让别人知道你写的是这个东西),比如把计算平均价格的代码封装一下,指令代码如下

delimiter //
create PROCEDURE productpricing()
BEGIN
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 过程名;

实例10:带参数的存储过程

函数分为有参和无参,上面介绍了无参,接着就是进行有参的函数创建。

1)形参函数,就是参数中的内容只是一个形式上的参数,在最后函数调用上,用指定格式的形式调用,最后输出变量的时候参数保持一致即可

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

end;

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

输出结果为:(调用时候的三个变量名称可以随便起,但是格式要有@,使用时候也得用相同的参数)

Insert picture description here


2)实参函数,就是有真实输入变量的参数,最后在调用函数的时候需要进行输入的

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

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

CALL ordertotal('20005',@p);

SELECT @p;

输出结果:(最后结果的查看就通过设置的输出参数)

Insert picture description here

6.7 游标

游标:在存储过程中,根据需要对数据集合进行前后浏览的一种应用。有时,需要在检索出来的行中前进或后退一行或多行,这就是使用游标的原因。游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改。

1) 创建游标

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

  --OPEN
  open ordernumbers;

  --CLOSE
  close ordernumbers;

end;

2)浏览数据,建议还是使用python操作游标

CREATE PROCEDURE processorders4()
begin 

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

  declare ordernumbers CURSOR
  FOR  
  select order_num from orders;

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

  -- OPEN
  open ordernumbers;

  REPEAT

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

  -- CLOSE
  close ordernumbers;

end;

call processorders4()

输出结果为:(就会把订单编号依次输出,如果使用过python操作数据库,还是建议在python中使用for循环吧)

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 触发器应用

1)插入触发器

以上只是简单的创建触发器并激活,接下来就进行实际场景的使用,比如将插入的产品id输出

#因为刚刚已经创建一个触发器了,这里先删除后再创建
drop TRIGGER  newproduct;
 
 #指定跟新规则和发送的信息
create trigger newproduct2 after insert on products
for each row select NEW.prod_id into @ee;

#这里将产品的编号改成22了
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');
 
 #最后查看一下内容
SELECT @ee;

输出结果:(可以发现最后将修改后的产品的id输出了)

Insert picture description here


2)删除触发器

#这里就是一个模板,archive_orders是要备份的表
create trigger deleteorder  before  delete on orders
for each row 
BEGIN
   insert into archive_orders(order_num,order_date,cust_id)
    values (old.order_num,old.order_date,old.cust_id);

end;

#创建备份的表并删除里面的内容
CREATE table archive_orders as SELECT * from orders;
DELETE from archive_orders;

#最后在查看一下orders表中删除数据后是否备份
SELECT * from archive_orders;

输出结果:(注意为了不破坏orders的数据,这里删除前随便添加了一行数据,然后删除的也是这一行数据)

Insert picture description here

七、MySQL数据安全

7.1 MySQL事务管理

在使用事务和事务处理时,有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语:

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

这些名词可以类比一下玩游戏的过程:首先每一关卡都是一个事务,有不同的经历战斗组成;如果这一关在挑战Boss中失败,就直接回退到开始的时候;如果完成了就恭喜你通过这一关,提交个人成绩;如果打游戏的过程中需要离开一下就可以进行存档,就对应设置保留点。

事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。(这里还可以类比于ATM存款,我存成功了,银行账户上应该多一笔钱,如果失败了,就得把钱退给我)

实例1:rollback事物回滚

前提条件:innodb支持事务,myisam不支持事务。在同一个编辑区内执行如下代码

select * from customers2;
start TRANSACTION;
delete from customers2;
select * from customers2;
ROLLBACK;
select * from customers2;

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

Insert picture description here

实例2:commit提交事务

如果将上面的rollback替换成commit,就相当于把更改的内容进行保存了,最后的结果也就是表中数据为空,但是这个表还是存在的

Insert picture description here

实例3: savepoint存档

程序可以在某一处停止,回到特定的状态,比如

#刚刚提交删除的表,这里再把数据备份过来
CREATE table customers2 as SELECT * from customers;
#第一次查看数据
select * from customers2;
#开始事务,删除顾客id为10001的数据
start TRANSACTION;
delete from customers2 where cust_id = 10001;
#在上面的操作结束存档
SAVEPOINT x1;
#继续删除顾客id为1002的数据
delete from customers2 where cust_id = 10002;
#第二次查看数据
select * from customers2;
#回滚到指定存档位置
ROLLBACK to x1;
#最后查看一下数据
select * from customers2;

输出结果为:(第一次查看数据自然也就是全部的数据,经历过两次删除后结果2中就没有10001和10002的数据,然后经过读取存档,又把1002的数据显示出来了,所以最后的一次查询只是没有了10001的数据)

Insert picture description here

7.2 数据备份与性能管理

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

像所有数据一样,MySQL的数据也必须经常备份。由于MySQL数据库是基于磁盘的文件,普通的备份系统和例程就能备份MySQL的数据。但是,由于这些文件总是处于打开和使用状态,普通的文件副本备份不一定总是有效。

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

实例4:数据库备份

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

Insert picture description here


如果要看一下备份数据所在的位置,都可以右键点击,选择查看对象信息即可。

Insert picture description here

1)备份一个数据库

如果使用命令行操作,需要在bin文件夹下打开命令行窗口,输入下面指令:

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

运行结果图:

Insert picture description here


2)如果要备份一个库中的多个表

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

3)如果要备份多个数据库

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

4)如果备份所有数据库

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

5)如果备份数据库中所有表,但是不含数据

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

6)如果备份数据自带删除老表功能

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

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

1)数据导出

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

Insert picture description here


2)数据导入

比如把刚刚导出的数据重新导入到customers2中(首先删除表数据),代码指令:load data INFILE 'c:/mysql/data1.csv' into table customers2;
第一次查询的结果为空,但是第二次查询的结果就是原来的数据了(下面显示的为result1的结果)

Insert picture description here

7.2.1 性能改善

数据库工作人员把他们工作中的相当一部份时间花在了性能调整上、试验改善DBMS性能。在诊断应用的滞缓现象和性能问题时,性能不良的数据库(以及数据库查询)通常是最常见的祸因。

  • 利用索引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