MySQL from the beginning to practice, 10,000 word details!

Database is to the full stack development has to cross a hurdle, we will inevitably learn to use relevant knowledge, recently found that a lot of content on the Internet is either particularly deep, or not systematic, to some of the hope of a shallow taste of just use the people are not very friendly. Recently I happened to have a chance to learn MySQL. I spent some time learning MySQL and took some notes. There are code examples for each concept, and each line is a start

The knowledge introduced in this article is not particularly deep, the target users are zero or weak foundation of MySQL partners, can help to establish some concepts of MySQL, at least encounter related problems know how to go to Baidu, also won’t encounter back-end to the database file can not understand.

For Docker and CentOS related knowledge do not know partners can look at the hand with your Docker from the entry to practice and half an hour to get a CentOS entry essential basic knowledge > two articles, anyway Docker and CentOS will also use 😂 sooner or later

All code is stored on Github and can be cloned to read and execute.

CentOS version: 7.6

MySQL version: 8.0.21

You can find the xmind source file by clicking “mysql Brain map”.

What is a database

A database is a collection of data stored in some organized fashion, which can be thought of as a filing cabinet.

1.1 Basic Information

MySQL database is owned by MySQL AB, a Swedish company that was later acquired by Oracle. Is the most popular relational database.

Advantages:

Low cost: open source, generally free trial;
High performance: fast execution;
Simple: Easy to install and use.

1.2 the MySQL installation

MySQL recommended using Docker installation, a few lines of command to install, see the < hand to take you Docker from the beginning to practice > – install MySQL

My order here is:

Create a mysql container
docker run -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=888888 \
-v /Users/sherlocked93/Personal/configs/mysql.d:/etc/mysql/conf.d \
-v /Users/sherlocked93/Personal/configs/data:/var/lib/mysql \
--name localhost-mysql mysql

Mysql > create mysql;
docker exec -it localhost-mysql bash

# login mysql
mysql -u root -p888888
Copy the code

Use this configuration if you have navicate installed on your phone

After selecting New Connection, fill in the configuration:

You can see what’s in your database.

The result is as follows:

You can download the MySQL Community Server installation package from the official website without Docker. The Community Server version is free, and you can also download and install it. Basically, the next step is the next step.

Nonsense less say, the following direct start knowledge irrigation body!

2. MySQL is easy to use

2.1 Database-related terms

Database related concepts and terms:

Database A container that holds organized data;
A structured list of data of a particular type;
Schema Information about the layout and features of databases and tables;
A column is a field in a table. All tables consist of one or more columns;
Datatype (datatype) the type of data allowed;
A record in a row table;
Primary key A column (or group of columns) whose value uniquely distinguishes each row in a table;
A column in a foreign key table that contains the primary key value of another table and defines the relationship between two tables.
SQL statements consist of clauses, some of which are required, others optional. For example, the FROM clause of the SELECT statement.

2.2 the primary key

The concept of a primary key is important because it uniquely identifies one or more columns per row in a table as primary keys. A primary key is used to represent a particular row.

Although primary keys are not always required, you should try to ensure that each table has a primary key defined for future data manipulation and management. Without a primary key, rows cannot be distinguished from one another, and updating or deleting a particular row in a table is difficult.

Any column in the table can be used as a primary key if it meets the following criteria:

No two rows have the same primary key;
Each row must have a primary key value (NULL values are not allowed for primary key columns).

When multiple columns are used as primary keys, the above conditions must apply to all columns that make up the primary key, and the combination of all column values must be unique (individual column values may not be unique).

Some of the best habits that are universally acknowledged are:

Values in primary key columns are not updated;
Do not reuse primary key column values;
Do not use values that may change in primary key columns.

2.3 Syntax Specifications

Syntax specification:

The inputhelp 或 \hGet help;
It is not case sensitive, but the keyword is uppercase, and the table name and column name are lowercase.
It is best to use a semicolon for each command; 或 \gAt the end of the command, only press Enter.
Each command can be indent or newline as required.
with#Start with a multi-line comment with/ *... * /Multi-line comments;
The inputquit 或 exitMySQL command line;

Grammatical features:

Case insensitive;
Can be written on one or more lines, can be divided into multiple lines for easy reading and debugging;
Keywords cannot be abbreviated or brandished;
Each clause is generally written in line;
Recommend the use of indentation to improve readability;

Common simple commands:

mysql -u root -p      # -h host name -u user name -p port number -p password. Note that no space can be added between -p and password
select version(a);Check the mysql server version
show databases;       # check all databases, note that there is s at the end

create database[name];# create the library
use[name];Open the specified library

show tables;           Check all tables in the current library
show tables from[name];Check all tables in other librariesDesc [table name];Check table structure

create table[table name] (# to create tableColumn name column type, column name column type,);drop database[name];# delete library
drop table[table name].# delete table

exit;                    # exit
Copy the code

2.4 Create tables and populate data

First we point data, convenient for the following code demonstration.

mysql -u root -p888888     Enter the user name and password to enter the mysql command line
Copy the code

Then download the create. SQL file on Github and run it (you can also copy the contents of the file to the MySQL command line).

If you use navicate, run the following command after creating a connection to localhost-mysql in the previous chapter:

Similarly, run another file orders.sql to populate each table.

Show tables shows the tables in the library from the command line, as shown below:

2.5 a relational table

A brief introduction to the tables you just created.

For data classification purposes, customers, vendors, order Orders, order information OrderItems, product records ProductNotes, and product Products tables store different information.

For example, the vendors information table would each vendor always have a unique identity, the vend_id primary key, and the Products table would each product also have a prod_id primary key, There is also a field vend_id vendor ID that corresponds to vend_id in the vendor table, which is the foreign key.

If you want to look up vendor information by product ID, use the foreign key to find information in another table. The foreign key avoids the repeated saving of supplier details for each product, as long as the supplier ID is saved. When the supplier information changes, such as email, address change, there is no need to change each row of data, but only the corresponding supplier information in the supplier table.

The benefits of this:

Supplier information is not repeated, so as not to waste time and space;
If vendor information changes, only individual records in the VENDORS table can be updated, leaving the data in the vendors table unchanged;
Since there is no duplication of data, it is obvious that the data is consistent, which makes processing the data easier.

Scalability, the ability to adapt to increasing workloads without failure. A well-designed database or application is called scale well.

2.6 Data Types

MySQL data types define the rules for what data can be stored in a column and how that data can be stored.

numeric

Integer types: Tinyint, Smallint, Mediumint, Int (Integer), Bigint. The value can be unsigned or signed. The default value is signed.

If you do not set unsigned, the default value is signed. If you want to set unsigned, you can add itunsignedKey words;
If the value inserted is outside the range of the integer, an out of range exception is raised and the critical value is inserted.
If the length is not set, the default length is used.

The decimal

Fixed-point number:Dec (M, D),A decimal (M, D)
Floating point Numbers:float(M, D),double(M, D)

M = integer part + decimal part, D = decimal part, both M and D can be omitted. If decimal, M defaults to 10 and D defaults to 0.

character

Shorter text:char(n),varchar(n)N indicates the number of characters, not bytes.
Longer text:text(long text data),blob(Longer binary data)
binary,varbinaryUsed to hold shorter binaries.
enumUsed to hold enumerations.
setUsed to save collections.

Date and time types

dateFormat YYYY-MM-DD, save the date.
timeFormat: HH:MM:SS, save time;
yearFormat YYYY, save year;
datetimeFormat yyyY-MM-DD HH:MM:SS, save the date + time, range1000-9999., not impressed by time zones;
timestampTimestamp, format save date + time, range1970-2038., affected by time zone;

3. Retrieve data select

Select statements used for queries are probably the most commonly used to retrieve information from one or more tables. A SELECT statement must give at least two information: what you want to select and from where.

# Basic syntax
select[Query list]from[table name].# query single/multiple/all fields
select cust_name from customers;
select cust_name,cust_city,cust_address from customers;
select `select` from customers;               If a field is a keyword, it can be wrapped in a 'symbol
select * from customers;                      # * means all

Query constant values/expressions/functions
select 100;
select 'zhangsan';
select 100%98;
select version(a);Copy the code

3.1 去重 distinct

The query result may have multiple duplicate values. You can use the distinct keyword to delete the duplicate values

select order_num from orderitems;           # there are some duplicate values
select distinct order_num from orderitems;  # deduplicate duplicate values
Copy the code

3.2 Limit results limit

The SELECT statement returns all matching rows, which may be each row in the specified table. To return the first or first few lines, use the limit clause.

Limit m: return the first m rows, limit m,n: return the first n rows, you can also use limit m offset n.

Notice that the first row retrieved has an index of 0.

3.3 Fully qualify table and column names

In some cases, statements may use fully qualified column and table names:

select orderitems.order_num from mysql_demo1.orderitems;
# The above statement is equivalent to
select order_num from orderitems;
Copy the code

4. Order by

The data retrieved from the table orderItems in the previous chapter is not sorted; it is generally returned in the order that appears in the underlying table. The retrieved data can be sorted using the Order BY clause.

You can use the asC, desc keyword to specify the sort direction. Order by ASC order by desc

The ORDER BY clause can support a single field, multiple fields, expressions, functions, aliases, and is usually placed at the end of the sentence, except limit.

select * from orderitems order by item_price;           Order by item_price in ascending order

Order quantity in ascending order; if quantity values are the same, order item_price in ascending order
select * from orderitems order by quantity,item_price;  

Order quantity in descending order; if quantity values are the same, order item_price in ascending order
select * from orderitems order by quantity desc,item_price;  

Find the most expensive order
select * from orderitems order by item_price desc limit 1;
Copy the code

5. Filter data where

Use the WHERE keyword after the FROM clause to add filters to filter the data.

# Basic syntax
select[Query list]from[table name]where[Screening criteria]order by[Sorting conditions];Copy the code

Filter >, =, <, >=, <=,! By conditional expressions = <> <=>

Find the name of the product that costs 2.5
select prod_name, prod_price from products where prod_price=2.5;

Find the names of the products whose prices are less than or equal to 10 and rank them in descending order
select prod_name, prod_price from products where prod_price <= 10 order by prod_price desc;

Select * from supplier where id = 1003 = = = = = = = = = =
select vend_id, prod_name from products where vend_id <> 1003;
Copy the code

5.1 Scope Check between and

The between… and … You can achieve the effect of a range query by specifying the start and end values for the desired range.

Notice that the numbers between and are in the order of smaller and larger.

Select * from product where the price is within 3 to 10
select prod_name, prod_price from products where prod_price between 3 and 10;

This effect can also be achieved by using and alone
select prod_name, prod_price from products where prod_price <= 10 and prod_price >= 3;
Copy the code

5.2 Checking Null Values IS (not) null

When you create a table, you can specify that certain columns may contain no values, as null, which means no value, unlike a field that contains 0, an empty string, or just Spaces.

Is null or is not NULL can be used to determine whether a value is null.

Description:

Is equal to the=And is not equal to<>,! =Can’t be used to judgenull, can only useis null 和 is not nullTo judgenull
< = >The safety equals sign can be used to determinenull

# find customer information where the mailbox is not null
select * from customers where cust_email is not null;

It is also possible to use the safety equal sign to determine null
select * from customers where cust_email <=> null;
Copy the code

5.3 Logic and operators and

The key used to join or change clauses within a WHERE clause is also called a logical operator.

The and operator mentioned above allows you to add qualifiers:

# Find the products that the supplier provides for 1003 at a price less than or equal to 10
select * from products where vend_id = 1003 and prod_price <= 10;
Copy the code

5.4 The logic or operator or

The OR operator is the opposite of the AND operator, which is the logical or operator that returns a line matching any condition:

Find the vendor with id 1003 or 1001
select * from products where vend_id = 1003 or vend_id = 1001;
Copy the code

When and and or are present together, the and is processed first, as in this sentence:

select * from products where vend_id = 1001 or vend_id = 1003 and prod_price >= 10;
Copy the code

Vend_id = 1003 and prod_price = 10 or greater, or vend_id = 1001

In this case, you can add parentheses:

select * from products where (vend_id = 1001 or vend_id = 1003) and prod_price >= 10;
Copy the code

The result of this search is a list of all products whose vend_id is 1001 or 1003 and whose prod_price is greater than or equal to 10.

Any time you use a WHERE clause with and and OR operators, you should explicitly group the operators with parentheses. Don’t rely too much on the default order, even if it’s exactly what you want, and use parentheses to disambiguate and increase readability.

5.5 Range Operator in (set)

You can specify a range of conditions using the IN operator, and each condition in the range can be matched. The value to be matched by in is followed by parentheses:

Find the vendor with id 1003 or 1001
select * from products where vend_id in (1001.1003);
Copy the code

The in operator can be replaced by or, which is recommended when:

When there are a lot of options,inClearer and more intuitive;
useinIs easier to manage (because fewer operators are used);
inGeneral thanorOperator lists execute faster;
inThe greatest advantage of is that it can include otherselectStatement to enable more dynamic creationwhereClause.

5.6 The Logical No operator not

The not no operator can be used with the preceding in and between and to indicate the inverse of the range:

Select * from product where id is not 1001 1003
select * from products where vend_id not in (1001.1003);

# Choose products that don't cost between 5 and 15
select * from products where prod_price not between 5 and 15;
Copy the code

5.7 Like operator

For example, if you want to find all products with anvil in their name, you can do a search using the like operator. Like indicates that the following search pattern uses wildcard matches rather than direct equality matches.

Operator %

The most commonly used wildcard is the % operator, which represents any number of characters, including none.

# Find products whose names start with JET
select * from products where prod_name like 'jet%';

Find products with on in their name
select * from products where prod_name like '%on%';

Find products whose product names begin with s and end with e
select * from products where prod_name like 's%e';
Copy the code

Note that % does not match null.

Operator _

_ indicates any single character.

select * from products where prod_name like '_ ton anvil';
Copy the code

In addition, the translation uses \, such as \_

Find the product with % in the description
select * from products where prod_desc like '% \ % %';
Copy the code

Note:

Don’t overuse wildcards. Other operators should be used if they serve the same purpose.
When you do need to use wildcards, don’t use them at the beginning of a search pattern unless absolutely necessary. Placing wildcards at the beginning of a search pattern is the slowest.

5.8 Regular expression regexp

For regular expressions, take a quick look at the regular expressions must-know, must-Know blog.

Use the regexp keyword to indicate the regular expression following the match:

Find products whose product names end in 1000 or anvil
select * from products where prod_name regexp '1000|anvil$';
Copy the code

Regular expression translations use \\, such as looking for the. Character instead of the. Character in the regular expression. For the wildcard character, use \\. To transfer the character \\, use \\

Find products whose product names begin with the. Character
select * from products where prod_name regexp '^ \ \.;
Copy the code

6. Calculate fields

Sometimes we need to calculate fields when we need to retrieve transformed, computed, or formatted data directly from the database, rather than retrieving it and then reformatting it in a client application or reporter.

6.1 the alias as

The query virtual table can be given an alias to facilitate understanding and readability. In addition, if the query fields have the same name, you can use the alias AS to distinguish them.

Use the as keyword
select cust_name as name from customers;

The # as keyword can also be omitted
select cust_name name from customers;

You can alias different fields separately
select cust_name name, cust_city location from customers;
Copy the code

6.2 splicing concat

To concatenate multiple fields into one field, use the concatenate field function concat:

# concatenate the vendor's name and location and return it with the name vend
select concat(vend_name, '(', vend_country, ') ') vend from vendors;
Copy the code

Note that if any data is null, the result of concatenation is also null.

The first argument is the field to be checked, and the second argument is the result to be returned ifnull:

# Combine customer information
select concat(cust_name, '('.ifnull(cust_email, The '-'), ') ') customerInfo from customers;
Copy the code

If there are Spaces before and after the table, you can use rtrim() to remove Spaces on the right, ltrim() to remove Spaces on the left, or trim() to remove Spaces before and after:

# Combine customer information after processing
select concat(rtrim(vend_name), '('.trim(vend_country), ') ') vend from vendors;
Copy the code

6.3 Arithmetic calculation +-*/

Basic arithmetic operators are also supported in SELECT statements:

Calculate the total amount of each order and arrange it in descending order
select prod_id as id, quantity, quantity*item_price as totalPrice 
from orderitems order by totalPrice desc;
Copy the code

The basic operators addition, subtraction, multiplication and division are supported +, -, *, and /.

7. Data processing functions

The trim() function introduced earlier, which removes whitespace from the data header, is a data handler. There are many other types of data handlers:

Text functions used to process text strings, such as deleting or filling values, converting values to upper or lower case.
A numerical function used to perform arithmetic operations on numeric data, such as returning absolute values, to perform algebraic operations.
Date and time functions that process date and time values and extract specific components from those values, for example, returning the difference between two dates, checking date validity, and so on.
System functions, such as returning user login information and checking version details.

When you don’t know how to use a function, you can use the help command, such as help substr, to get an example of how substr is used.

7.1 Character Functions

function	instructions
`left()`,`right()`	Returns the left and right characters of the string
`length()`	Return string length
`lower()`,`upper()`	Returns lowercase and uppercase of the string
`rtrim()`,`ltrim()`,`trim()`	Remove Spaces on the right, left, and sides
`locate()`	Find the substring of a string
`soundex()`	Returns the sundex value of the string
`substring()`	Returns the character of a substring
`subset()`	Returns the characters of the substring (and`substring`Use it differently)
`instr()`	Returns the index of the first occurrence of the substring, without returning 0
`replace()`	String substitution
`lpad()`,`rpad()`	Left fill, right fill

Example:

# upper, lower uppercase last names, smaller first names, and then splice
select concat(upper(last_name), lowerName (first_name))from employees;

The first character of the # name is capitalized, the other characters are lowercase and then concatenated with _
select concat(upper(substr(last_name, 1.1)), '_'.lower(substr(last_name, 2))) from employees;

# substr intercepts a string. SQL indexes start at 1 instead of 0
select substr('hello world'.3);      # llo world
select substr('hello world'.2.3);   # ell

# instr returns the index of the first occurrence of the substring, 0 if not found
select instr('abcdefg'.'cd');        # 3

# trim Subtracts the first and last Spaces or specified characters from the string
select trim(' hello ');              # hello
select trim('aa' from 'aaabaabaaaaaa');   # abaab

# lpad implements the left padding of the specified length with the specified character
select lpad('he'.5.The '-');   # ---he

# rpad implements the left padding of the specified length with the specified character
select rpad('he'.5.'- *');   # he-*-

# replace replace
select replace('abcabcabc'.'bc'.The '-');   # a--a--a--
Copy the code

7.2 Mathematical Functions

function	instructions
`round()`	rounded
`ceil()`	Take up the whole
`floor()`	Take down the whole
`truncate()`	Keep a few decimal places
`mod()`	Take more than
`abs()`	Return absolute value
`rand()`	Returns a random number

Example:

Round. The second argument is the number of digits reserved after the decimal point
select round(1.55);      # 2 -
select round(1.446.2);   # 1.45

# ceil rounded up
select ceil(1.001);   # 2
select ceil(1.000);   # 1
select ceil(1.001);  # 1

# floor is rounded down
select floor(1.001);   # 1
select floor(1.000);   # 1
select floor(1.001);  # 2 -

Truncate The number of digits after the decimal point
select truncate(1.297.1);  # 1.2
select truncate(1.297.2);  # 1.29

The symbol is the same as the dividend
select mod(10.- 3);  # 1
select mod(- 10.- 3); # 1
select mod(- 10.3);  # 1
select 10%3;         # 1
Copy the code

7.3 Date Function

function	instructions
`now()`	Returns the current system date and time
`curate()`,`current_date`	Returns the current system date, excluding time
`curtime()`,`current_time`	Returns the current time, excluding dates
`year()`,`month()`,`day()`,`hour()`,`minute()`,`second()`	Gets the specified portion of time, year, month, day, hour, minute, second
`str_todate()`	Converts a date-formatted character to a date in the specified format
`date_format()`	Converts the date to the specified format character

Example:

# now Returns the current system date and time
select now(a);# 2020-07-08 12:29:56

# curdate,current_date Returns the current system date, excluding the time
select curdate(a);# 2020-07-08

# curtime,current_time returns the current time, excluding the date
select curtime(a);# 12:29:56

# year... Gets the specified portion of time, year, month, day, hour, minute, second
select year(now());      # 2020
select month(now());     # 7
select monthname(now()); # July
select day(now());       # 8
select dayname(now());   # Wednesday
select hour(now());      # 12
select minute(now());    # 29
select second(now());    # 56
select month(order_date) from orders;

# str_to_date converts date-formatted characters to dates in the specified format
select str_to_date('1-9-2021'.'%d-%c-%Y');    # 2020-09-01
select * from orders where order_date = str_to_date('2005-09-01'.'%Y-%m-%d');

# date_format A character that converts a date to a specified format
select date_format(now(), '%Y年%m月%d日');   # September 01, 2020
select order_num orderId,date_format(order_date, '%Y/%m') orderMonth from orders;
Copy the code

Date format:

Format character	function
`%Y`	The four year
`%y`	Two years
`%m`	(by, 01 month… 12)
`%c`	In (1, 2,… 12)
`%d`	(by, 01…).
`%e`	(1, 2,…).
`%H`	Hours (24 hours)
`%h`	Hours (12-hour system)
`%i`	Minutes (00,01,… 59)
`%s`	Seconds (00,01,… 59)

7.4 Aggregation Function

An aggregate function that runs on a row group and calculates and returns a single value.

function	instructions
`avg()`	Returns the average value of a column
`count()`	Returns the number of rows in a column
`max()`,`min()`	Returns the maximum and minimum value of a column (ignoring null)
`sum()`	Returns the sum of a column (ignoring null)

Example:

# Calculate the average product price
select avg(prod_price) as avgPrice from products;

Calculate the average price of the product provided by supplier ID 1003
select avg(prod_price) as avgPrice from products where vend_id = 1003;

# Calculate the product price with the largest price
select max(prod_price) as maxPrice from products;

# Count customers
select count(*) from customers;

# count the number of customers with email
select count(cust_email) from cutomers;

# Calculate the sum of product prices
select sum(prod_price) from products;

Calculate the total number of orders for 20005
select sum(item_price * quantity) totalPrice from orderitems where order_num = 20005;

# Calculate the average of the different prices a product has
select avg(distinct prod_price) avgPrice from products where vend_id = 1003;

# Calculate the total number of products, minimum, maximum and average price at the same time
select count(*) prodNums, min(prod_price) priceMin, max(prod_price) priceMax, avg(prod_price) priceAvg from products;
Copy the code

8. Grouping data

Previous aggregations were based on all the data queried in the WHERE clause, such as the average price of a product from a particular vendor, but what if you wanted to return the average price of a product from each vendor separately? This is where grouping comes in, which allows you to divide data into multiple logical groups so that you can perform aggregate calculations on each group.

8.1 Creating a Group by

The group by clause instructs MySQL to sort and group data by some data, and then aggregate each group instead of the entire result set.

# Query the number of product types provided by each supplier separately
select vend_id, count(*) num_prods from products group by vend_id;

# Query the average price of products offered by each supplier
select vend_id, avg(prod_price) avg_price from products group by vend_id;
Copy the code

Note:

group byA clause can contain any number of columns. This enables grouping to be nested, providing finer control over data grouping.
If thegroup byClauses have nested groups, and the data will be summarized at the last specified group. In other words, when a group is created, all columns specified are evaluated together (so no data can be retrieved from individual columns).
group byEach column listed in the clause must be a retrieval column or a valid expression (but not an aggregation function). If theselectIf the expression is used ingroup byClause specifying the same expression. Aliases cannot be used.
In addition to aggregating computed statements,selectEach column in the statement must be ingroup byClause.
If there is a NULL value in the grouping column, null is returned as a grouping. If there are multiple rows of NULL values in a column, they are grouped.
group byClause must appear inwhereAfter the clause,order byClause before.

8.2 Filtering Group HAVING

In addition to grouping data using group by, MySQL also allows you to filter groups using the HAVING keyword, specifying which groups to include and which to exclude.

The syntax order is as follows:

# syntax order
select[Query list]from[table name]where[Screening criteria]group by[Grouping list]having[Screening after grouping]order by[Sort list]limit[Number of rows to retrieve];Copy the code

Where filtering has no concept of groups, specifying rows instead of groups, and using a HAVING clause for groups. In fact, all the types of WHERE clauses you’ve seen so far can be replaced with having.

There is another way to understand the difference between HAVING and WHERE. Where is filtered before grouping and HAVING after grouping. Rows excluded by WHERE are not included in the group, which may change the calculated values and affect the groups filtered out based on those values in the HAVING clause.

Those who can filter where before grouping are preferred.

# Find suppliers with more than 2 products and list the number of products they offer
select vend_id, count(*) prodCount from products group by vend_id having prodCount > 2;

Find the suppliers whose average price is greater than 10 and rank them in descending order by average price
select vend_id, avg(prod_price) avgPrice from products group by vend_id having avgPrice > 10 order by avgPrice desc;
Copy the code

9. The subquery

Subquery, a query nested within another query.

9.1 Filtering Using SubQueries

When another complete SELECT statement is nested in a query statement, the nested SELECT statement is called a subquery or an inner query, and the outer SELECT statement is called a primary query or an outer query.

Before, all the queries were in the same table. What if the information we want to obtain is scattered in two or more tables? For example, we need to obtain customer ID from the order table Orders, and then use the customer ID to find the corresponding customer information from the customer table Custormers.

First find the order number for product TNT2 in orderItems table
select order_num from orderitems where prod_id = 'TNT2'

Then find the customer ID corresponding to the order number in the Orders table
select cust_id from orders where order_num in (
  select order_num from orderitems where prod_id = 'TNT2');
  
Then go to the Customers table to find the customer name corresponding to the customer ID
select cust_id, cust_name from customers where cust_id in ( 
  select cust_id from orders where order_num in (  
    select order_num from orderitems where prod_id = 'TNT2'));
Copy the code

There are actually three statements. The innermost subquery returns a list of order numbers used in the WHERE clause of its outer subquery. The outer subquery returns a list of customer ids used in the WHERE clause of the outermost query. The outermost query eventually returns the required data.

There is no limit to the number of subqueries that can be nested, although in practice performance constraints prevent too many subqueries from being nested.

9.2 Related Sub-Query

Correlated subqueries (Correlated Subqueries) Subqueries that involve external queries.

Another way to use subqueries is to create computed fields. Suppose you need to display the total number of orders for each customer in the Customers table. The order is stored in the Orders table with the corresponding customer ID.

First find the order quantity corresponding to the user ID
select count(*) from orders where cust_id = 10003;

# then use this as a SELECT clause, passing the user ID
select cust_name, cust_state, (
  select count(*) from orders where orders.cust_id = customers.cust_id) as order_count 
  from customers order by order_count desc;
Copy the code

Note the above where Orders.cust_id = customers. Cust_id, this type of subquery is called a correlation subquery, and the fully qualified syntax must be used any time the column names can be ambiguous (table and column names are separated by a period).

10. Join table

If the data you want to look up is spread across multiple tables, how do you use a single SELECT statement to look up the data? Use joins.

Join is a mechanism for associating tables in a SELECT statement, hence the name join. Using special syntax, you can join multiple tables to return a set of output, joining the correct rows in the associated table at run time.

Maintain referential integrity: When using relational tables, it is important to insert only valid data into relational columns. If you insert products from vendors that have vendor ids that do not appear in the Vendors table into the Products table, those products are not accessible because they are not associated with a vendor.

To prevent this from happening, you can instruct MySQL to allow only valid values in the vendor ID column of the Products table (that is, vendors in the Vendors table). This is maintaining referential integrity, which is achieved by specifying primary and foreign keys in the table definition.

10.1 Creating a Connection

Join creation is simple, specifying all the tables to join and how they are related.

# List the suppliers of the products and their prices
select vend_name, prod_name, prod_price from vendors, products 
where vendors.vend_id = products.vend_id order by prod_price desc;
Copy the code

MySQL matches vend_id from vender and vend_id from Products with fully qualified column names.

When the referenced column may be ambiguous, you must fully qualify the column name because MySQL does not know which column you are referring to.

When joining two tables, what you’re really doing is pairing each row of one table with each row of the other table, so the WHERE clause acts as a filter condition, filtering out columns that only contain the specified join conditions, VENDORS. Vend_id = products.vend_id, without the WHERE clause, Return the cartesian product of the length of two tables.

Return the product row of two table lengths
select vend_name, prod_name, prod_price from vendors, products;
Copy the code

All joins should always use join conditions, otherwise the Cartesian product is obtained.

10.2 Join multiple tables

A select statement can also join multiple tables. For example, if you want to list the product information, order information, and supplier information for an order, the product information is scattered among the supplier, product, and order tables.

Find out the product information, order information and supplier information of order 20005
select prod_name, vend_name, prod_price, quantity
from orderitems,
     products,
     vendors
where products.vend_id = vendors.vend_id
  and orderitems.prod_id = products.prod_id
  and order_num = 20005;
Copy the code

Here we use and to join multiple join conditions, defining what to use as an association between the three tables.

Note: It can be very expensive for MySQL to associate multiple tables to handle joins at run time. Do not join unnecessary tables. The more tables are joined, the more performance degrades.

Here you can use joins to implement the example in Section 9.1, which was previously implemented using a subquery to get the customer ID from the order table Orders and then use the customer ID to go to the customer table Custormers to find the corresponding customer information.

Use joins to implement the 9.1 example
select customers.cust_id, cust_name
from orders, customers, orderitems
where orders.order_num = orderitems.order_num
  and customers.cust_id = orders.cust_id
  and prod_id = 'TNT2';        						Since only one of the three tables has prod_ID, there is no need to qualify the table name
Copy the code

Not only columns can be aliased, but tables can also be aliased in the same way as column aliases:

# Alias the preceding sentence
select c.cust_id, cust_name
from orders o, customers c, orderitems oi
where o.order_num = oi.order_num
  and c.cust_id = o.cust_id
  and prod_id = 'TNT2';
Copy the code

This not only shortens SQL statements, but also allows the same table to be used multiple times in a single SELECT statement. Simultaneous aliases can be used not only in the SELECT clause, but also in the WHERE, ORDER by clause, and other parts of the statement.

Inner join

The join used so far is called equiJoin, which is based on an equality test between two tables, also known as an internal join. In fact, a slightly different syntax can be used for this type of join to explicitly specify the type of join. The following SELECT statement returns exactly the same data as in the previous example:

# List the suppliers of the products and their prices
select vend_name, prod_name, prod_price
from vendors
         inner join products
                    on vendors.vend_id = products.vend_id;
Copy the code

The join condition here uses the ON clause instead of the WHERE clause, which would work either way. Although it is true that defining joins using the WHERE clause is simple, using explicit join syntax ensures that join conditions are not forgotten, and sometimes this can affect performance.

10.4 since the connection

For example, a product has a quality problem, and now we want to find out all the product information provided by the supplier of this product. Following the subquery described earlier, we can first find the supplier of the corresponding product, and then find the list of products with this supplier ID:

# First find the supplier ID whose product ID is TNT1, then find the list of products provided by the corresponding supplier ID
select prod_id, prod_name, vend_id
from products
where vend_id in (
    select vend_id
    from products
    where prod_id = 'TNT1'
);
Copy the code

This can be done with subqueries, but it can also be done with joins, which are self-joins:

# the coupling
select p1.prod_id, p1.prod_name, p1.vend_id
from products p1,
     products p2
where p1.vend_id = p2.vend_id
  and p2.prod_id = 'TNT1';
Copy the code

The two tables in the self-join query are the same table, so the Products table needs to be aliased separately to distinguish it, and the column names that appear in the SELECT clause need to be qualified because the same field appears in both tables.

Self-join is often used as an external statement in place of a subquery statement used to retrieve data from the same table. Although the end result is the same, sometimes processing joins is much faster than processing subqueries. Two methods should be tried to determine which performs better.

10.5 Natural Connection

Whenever a table is joined, there should be at least one list in more than one table (joined columns). Standard joins return all data, even if the same column appears multiple times. Natural join rules out multiple occurrences so that each column returns only once.

Natural join is when you select only those columns that are unique, usually by using wildcards for tables and an explicit subset of all other table columns.

Select a unique wildcard for the first table only. All other columns are explicitly listed, so no duplicate columns are retrieved.
select v.*, p.prod_id
from vendors v,
     products p
where v.vend_id = p.vend_id;
Copy the code

10.6 External Links Outer Join

In some cases, joins contain rows that have no associated rows in related tables. This type of join is called an external join.

Such as:

Count how many orders each customer has placed, including those that have not so far;
List all products and order quantity, including products that no one ordered;
Calculate the average sales size, including customers who have not placed orders so far.

At this point the join needs to contain which rows are not associated.

For example, retrieve all users and their orders, and list those that do not have orders:

# internal join to find the user's corresponding order
select c.cust_id, o.order_num
from customers c
         inner join orders o
                    on c.cust_id = o.cust_id;
                    
# left outer join to list all customer rows that have not been ordered
select c.cust_id, o.order_num
from customers c
         left outer join orders o
                         on c.cust_id = o.cust_id;
                         
# right outer link lists all orders and their customers, so that customers who have not placed an order are not listed
select c.cust_id, o.order_num
from customers c
         right outer join orders o
                          on c.cust_id = o.cust_id;
Copy the code

When using outer Join syntax, you must specify a table that contains all its rows using the right or left keyword. Right indicates the right table of the outer join, and left indicates the left table of the outer join. Above, use the left outer JOIN to select all rows from the left table custermers of the FROM clause. To select all rows from the table on the right, use the right Outer JOIN.

The left outer join can be converted to the right outer join by reversing the order of the tables in the FROM or WHERE clause, whichever is convenient for you.

10.7 Use joins with aggregation functions

For example, if you want to retrieve the number of orders placed by a customer, write 0 even if there is none, then use the grouping and count aggregation functions to count the number:

Find the number of orders placed by each customer and rank them in descending order
select c.cust_id, c.cust_name, count(o.order_num) count_orders
from customers c
         left outer join orders o on c.cust_id = o.cust_id
group by c.cust_id
order by count_orders desc;
Copy the code

Because customers are still in the results even if they don’t place an order, put the customer table on the left, with the left outer join.

11. Combined query

MySQL allows you to execute multiple SELECT statements and return the results as a single query result set. These combined queries are often called union or compound Queries.

There are two situations where you need to use a combined query:

Return similarly structured data from different tables in a single query;
Perform multiple queries on a single table, returning data as a single query.

In most cases, a composite query can be replaced with a single query with multiple WHERE clause conditions. You can try both approaches in a specific scenario to see which one performs better for a particular query.

11.1 Creating a Combined Query Union

Composite queries are often used when the query results come from multiple tables, but the tables are not associated. Place the keyword union between each select statement.

Select * from supplier where the price is less than or equal to 10 and the supplier ID is 1005 or 1003
select prod_id, prod_name, prod_price, vend_id from products where prod_price <= 10
union
select prod_id, prod_name, prod_price, vend_id from products where vend_id in (1005.1003);

# This can also be replaced by the WHERE statement
select prod_id, prod_name, prod_price from products where prod_price <= 10 or vend_id in (1005.1003);
Copy the code

In some cases, such as more complex filtering conditions, where data needs to be retrieved from multiple tablesunionIt might be easier.
unionEach query must contain the same columns, expressions, and aggregate functions, but each column does not need to be listed in the same order.
Column data types must be compatible, not necessarily identical, but must be converted implicitly by the database management system.
Sorting of composite queriesorder byIt can only appear on the last itemselectStatements cannot be followed on differentselectStatements are sorted separately.

11.2 Containing or Canceling Duplicate Rows union (all)

Two lines separated by union may return duplicate lines, but the previous example does not contain duplicate lines. This is because the union keyword automatically removes duplicate lines. If you do not want duplicate lines, you can use the union all keyword.

# do not duplicate lines
select prod_id, prod_name, prod_price, vend_id from products where prod_price <= 10
union all
select prod_id, prod_name, prod_price, vend_id from products where vend_id in (1005.1003);
Copy the code

If repeated lines are required, the WHERE keyword cannot be used to achieve the same effect.

12. Data addition, deletion and modification

Before said is the data of the query, this chapter will say the data of the increase, deletion and change.

12.1 Data Insertion Insert into

Data insertion uses the INSERT keyword, which can insert one row, multiple rows of data, or the results of some queries.

Insert a table into the customer table
insert into customers
values (null.'Zhang San'.'001 street'.'ShangHai'.'SH'.'666666'.'ZH'.null.null);
Copy the code

The data stored in each table column needs to be given in the VALUES clause in the order in which the table was created. Null if a column has no value. Although the attribute of the cust_ID column corresponding to the first data is not null, this column is auto_INCREMENT, so MySQL will automatically ignore your null and increment the value.

However, using the values clause above is not a safe way to inject data because it depends entirely on the order in which the data is entered. If the table structure changes, the input data will be misplaced.

The safe way to insert data is like this:

# Safe but tedious insertion method
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values ('Zhang San'.'001 street'.'ShangHai'.'SH'.'666666'.'ZH'.null.null);
Copy the code

The preceding parentheses give the column names corresponding to the data in the following parentheses, so that data can be inserted correctly even if the table structure or order changes.

You can see that column CUST_ID is omitted and the column can be omitted if the following conditions are met:

Columns are defined to allow null values;
This column gives the default value when the table is defined, indicating that the default value will be used if no value is given.

If it cannot be omitted, an error is reported.

The INSERT operation can be time-consuming, especially if there are many indexes that need to be updated, and it can degrade the performance of select statements waiting to be processed. If data retrieval is of Paramount importance, you can lower the priority of insert statements by adding the keyword low_priority between INSERT and INTO, which also applies to the UPDATE and DELETE statements mentioned below.

12.2 Inserting Multiple Rows

The insert statement described above can insert one row at a time. If you want to insert more than one row at a time, listing the column names each time is tedious, you can use the following method:

Insert multiple rows
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values ('Zhang San'.'001 street'.'ShangHai'.'SH'.'666666'.'ZH'.null.null),
       ('Li Si'.'002 street'.'BeiJing'.'BJ'.'878787'.'ZH'.null.'[email protected]');
Copy the code

The values clause is followed by a new line enclosing the fields in parentheses, followed by a comma. This can improve the performance of database processing because it is faster to process multiple inserts with a single INSERT statement than with multiple INSERT statements.

12.3 Inserting the Retrieved data INSERT SELECT

Insert Can insert the result of a SELECT statement into a table, which is called insert SELECT. Let’s say you want to insert data from a query in another table into this table:

Insert data from other tables into customers table
insert into customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
select id.name, address, city, state, zip, country, contact, email
from custnew;
Copy the code

The new row of select can also omit CUST_ID so that the new CUST_ID can be automatically generated during the INSERT. In addition, you can see that the column name of the SELECT statement does not correspond to the column name after the INSERT INTO statement, which is ok because the insert SELECT statement uses the position mapping. The first column returned by the SELECT statement corresponds to CUST_ID, and the second column corresponds to CUST_NAME. Correspond in turn. You can also add a WHERE clause after the SELECT statement for filtering.

12.4 Modifying Data Update

The UPDATE statement is used to modify the data in the table. Be careful when using the UPDATE statement. Do not forget to add the WHERE clause, because you can accidentally update all the rows in the table.

Update user id 10005
update customers set cust_email = '[email protected]' where cust_id = 10005;
Copy the code

If the WHERE clause is not used here, UPDATE will update the CUST_EMAIL fields of all rows in the table, so be careful.

To delete a value for a row or column, change the value to NULL.

Updating multiple fields is also simple:

# Update multiple fields
update customers
set cust_email   = '[email protected]',
    cust_contact = 'S Zhang'
where cust_id = 10005;
Copy the code

If multiple rows are updated with an UPDATE statement and an error occurs when one or more of these rows are updated, the entire update operation is canceled (all rows updated before the error occurred are restored to their original values). To continue updating even if an error occurs, use the ignore keyword after the update.

The UPDATE statement can use subqueries to update column data with the data retrieved by the SELECT statement.

12.5 Deleting Data Delete

The DELETE statement can be used to delete specific rows or all rows from a table. When using delete statements, be careful not to add the WHERE clause, because you can delete all rows in the table.

Select * from customer where id = 10008
delete from customers where cust_id = 10008;
Copy the code

If you drop the WHERE clause, you drop all rows in the table, but instead of dropping the table, you drop the table using another statement, DROP. Another faster statement to delete all rows in a table is truncate table, because delete deletes data row by row, while TRUNCate deletes the original table and creates a new table.

Note that you should be very careful before using UPDATE and DELETE, because MySQL does not undo, it is recommended to test with SELECT before using it to prevent data loss due to incorrect WHERE clauses.

13. Create and manipulate tables

13.1 Creating a table Create table

To the top of the hour before we can open the data to perform the create. Look at the SQL files, use VSCode/Webstorm/Navivate/text can be opened, the file in addition to the comments of the first line is to create table statements:

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 DEFAULT 'ZH'.# specify a default value
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)          # specify primary key
) ENGINE=InnoDB;
Copy the code

Here you can see the format of the Create table.

If you want to create a table that does not exist, add if not exists before the table name and after create table. This checks to see if the table name already exists and creates it if it doesn’t.

For auto_INCREMENT, there can only be one AUTO_INCREMENT per table, and it must be indexed. When you insert a new value using the INSERT statement, subsequent automatic increments start over from that value. If you want to obtain the auto_increment value of a column from a table, use last_insert_id() to obtain the last auto_increment value.

Null after the column name of the above statement indicates that the column is allowed to be inserted or modified without a value. If not NULL, the value must be given when the column is inserted or modified, otherwise an error will be reported. The default is null, or null if not explicitly given.

The primary key indicates the value of the primary key, which must be unique at insert time. The primary key can also be multiple fields: Primary Key (CUST_id, CUST_NAME) separated by commas. Null is not allowed for primary key columns.

The default keyword can specify a default value. If no default value is specified for an inserted row, the default value is used by default.

The final engine field specifies different engines. Here are a few common engines supported by MySQL:

InnoDB’s reliable transaction engine does not support full text search.
MEMORY functions as MyISAM, but because the data is stored in MEMORY, it is fast and suitable for temporary tables.
MyISAM default is this, high performance, full text search support, no transaction support.

You can choose different engines according to different needs.

13.2 Alter TABLE ALTER table

Alter tables Use the ALTER TABLE statement. In general, after data is stored in a table, you should not modify the table. So the table in the design of the need for a lot of time to consider, try not to make big changes in the late table.

# add a vend_phone column to the vendor table
alter table vendors
    add vend_phone char(20) default 12212341234;
    
# Delete the vend_phone field
alter table vendors
    drop column vend_phone;
Copy the code

Alter TABLE is often used to define a foreign key, which is used to restrict the relationship between two tables and ensure that the value of this field must come from the associated column value of the primary table. Foreign keys have a mechanism to maintain data integrity and consistency, and have a good verification function for service processing.

Foreign keys are used to add foreign key constraints to slave tables that reference the values of a column in the master table, such as the student table’s major number, the employee table’s department number, the employee table’s job number, the product table’s supplier number, and so on.

An example of a foreign key can be seen at the bottom of the create.sql file, listing one line:

Set order_num as the foreign key of the order table
alter table orderitems
    add constraint fk_orderitems_orders foreign key (order_num) references orders (order_num);
Copy the code

Set up foreign keys, if foreign keys have corresponding data, cannot directly delete the main table of this foreign key line:

Delete foreign key line error
delete from orders where order_num = 20009;

Delete rows from orderItems and foreign key rows from Orders
delete from orders where order_num = 20009;
delete from orderitems where order_num = 20009;
Copy the code

Therefore, when inserting data, the primary table is inserted first, and then the secondary table. When deleting data, delete the secondary table first and then the primary table.

Note: Be careful with ALTER tables. It is best to make a full backup before making any changes. If you accidentally add columns that are not needed, you may not be able to delete them, and if you delete columns that shouldn’t be deleted, you may lose data.

13.3 Deleting a Table Drop table

You can use the drop table keyword to drop a table.

drop table customers2;
Copy the code

The table is deleted without confirmation or cancellation, and will be permanently deleted after execution.

Drop table (if exists); drop table (if exists); drop table (if exists);

# drop a table, if exists does not exist
drop table if exists customers2;
Copy the code

When creating a new table, the specified table name must not exist, otherwise an error will be reported, so you can also execute this sentence before creating a new table.

13.4 Renaming a Table

The keyword rename table can be used to rename a table.

Rename a table
rename table customers to customers2;

Rename multiple tables
rename table customers to customers2,
             vendors to vendors2;
Copy the code

14. View

A view is a virtual table. The row and column data is from the table used in the query that defines the view. The data is dynamically generated when a view is used, saving only SQL logic but not query results.

14.1 Creating a View Create view

For example, if you want to query the information of customers who have bought TNT2 product, you can use join to find the information from the three tables according to the knowledge introduced above:

# Find the information of customers who bought TNT2
select cust_name, cust_contact, cust_email, 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';
Copy the code

Now if you want to find the information of a customer who bought another product, rewriting the query seems a bit repetitive. Programmers never duplicate things. If you have a virtual table named prod_cust, you can easily find rows using select * from prod_cust where prod_id = ‘TNT1’. This is the view.

Create a view
create view prod_cust as
select cust_name, cust_contact, cust_email, prod_id
from customers c,
     orders o,
     orderitems oi
where c.cust_id = o.cust_id
  and o.order_num = oi.order_num;
  
Query the customer information of product TNT2 with view
select cust_name, cust_email from prod_cust
where prod_id = 'TNT2';
Copy the code

It’s easy to use, and you can write reusable views as needed for easy query.

The use of views is as follows:

create view prod_cust  as. ;Create a view
show create view prod_cust;          View the statement that creates the view
drop view prod_cust;                 # delete view
create or replace view prod_cust  as. ;Update the view
Copy the code

If you want to modify a view, you can delete it and then create it, or you can create or replace it directly. If the view exists, it will be replaced, and if it does not exist, it will be created.

The view does not contain data directly, but rather an SQL query. The relationship between a view and an ordinary table is like the relationship between an AD hoc singing group and an ordinary class.

But because views don’t contain data, they have to be reexecuted every time, so if you have a complex scenario that uses views, such as nested views, you will see a significant performance degradation, so you may need to pay attention to performance in scenarios that use a lot of views.

Once a view is created, you can use the view like a table, select, filter, sort, join, and so on.

Using views you can:

Reuse SQL statements.
Simplify complex SQL operations. After you write a query, you can easily reuse it without knowing its basic query details.
Use parts of a table rather than the entire table.
Protect data. Users can be granted access to specific parts of a table rather than the entire table.
Change the data format and presentation. Views can return data that is different from the presentation and format of the underlying table.

By the way, show Tables also shows views after they are created, so you can query all base tables or views as follows:

Display all base tables in the current database that do not include views
select table_name
from information_schema.tables
where table_schema = 'mysql_demo1'
  and table_type = 'BASE TABLE';

Display all views in the current database
select table_name
from information_schema.tables
where table_schema = 'mysql_demo1'
  and table_type = 'VIEW';
Copy the code

14.2 Reformat retrieved data using views

For example, in a scenario where formatted data is often used, you can use views to concatenate the formatted data:

The most frequently used supplier information can be assembled into a view first
create or replace view vend_infos as
select vend_id, concat(vend_name, '(', vend_city, ', ', vend_country, ') ') vend_info
from vendors;

Use the view to get the supplier information directly
select prod_id, prod_name, vend_info
from products, vend_infos
where products.vend_id = vend_infos.vend_id;
Copy the code

14.3 Filtering Unwanted Data using Views

For example, in a scenario where we need to find orders from customers whose email addresses are not null:

# find orders from customers whose email is not null
select order_num, cust_name, cust_email
from customers, orders
where customers.cust_id = orders.cust_id
  and cust_email is not null;
Copy the code

However, in another scenario, we need to find the list of all commodities purchased by customers whose email addresses are not null. In this case, we can use view to create a view for customers whose email addresses are not null, and use it in other scenarios:

Create a customer view where the email address is not null
create view cust_has_email as
select * from customers
where cust_email is not null;

# find orders from customers whose email is not null
select order_num, cust_name, cust_email
from cust_has_email c, orders o
where c.cust_id = o.cust_id;

Find a list of all items purchased by customers whose email is not null
select c.cust_name, c.cust_email, c.cust_name, prod_name
from cust_has_email c, orders o, orderitems oi, products p
where c.cust_id = o.cust_id
  and oi.order_num = o.order_num
  and p.prod_id = oi.prod_id;
Copy the code

You can see that the view here is treated as a table entirely to be used with other tables.

14.4 Using Views and Computed Fields

Views are useful for simplifying the use of computed fields, such as order totals for orders that you want to find for 20008:

Order amount for order 20008
select order_num, sum(quantity * item_price) sum_price
from orderitems
where order_num = 20008;
Copy the code

So when you want to find another order total, you can use the view to change it:

Find the total order view
create or replace view sum_orders as
select order_num, sum(quantity * item_price) sum_price
from orderitems group by order_num;

# Find the total amount of another order
select order_num, sum_price
from sum_orders
where order_num = 20009;
Copy the code

Seeing a view is very useful, and using a view on demand in real life can greatly facilitate database operations.

14.5 Updating a View

Views can also update data using INSERT, UPDATE, and DELETE, even though a view is an SQL sentence rather than actual data.

Data that updates a view updates its base table, but not all views can be updated, and updates are not allowed if the database does not determine the base data to be updated. Such as grouping, join, subquery, union, aggregate function, distinct, and so on.

Add a row to a view where email is not null
insert into cust_has_email(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
values (10010.'Zhang San'.'001 street'.'ShangHai'.'SH'.'9999'.'ZH'.'Li S');

SQL > alter table order total view add row failed, because there is grouping, database does not know where to insert
insert into sum_orders(order_num, order_item, prod_id, quantity, item_price)
values (20009.5.'OL1'.2.8.99);
Copy the code

In general, however, views should be used to retrieve data select, not insert, update, or delete.

15. Stored procedures

Most of the SQL statements described above are single queries on one or more tables, but in practice a complete operation can be composed of multiple statements. Consider the following ordering process:

In order to process the order, it is necessary to check to ensure that the appropriate item is in stock.
If there are items in stock, these items need to be pre-ordered so that they are not resold to others, and the number of items available should be reduced to reflect the correct inventory.
Items that are not in stock need to be ordered and require some interaction with suppliers.
The appropriate customer needs to be notified about which items are in storage (and can be shipped immediately) and which items are out of order.

Stored procedures can be described as code encapsulation and reuse at the SQL language level of the database, with values returned and parameters accepted. You can think of it as a batch file, but not just for batch processing.

The stored procedure is simple, secure, and high-performance. However, some database administrators restrict the creation of stored procedures to users, but do not allow users to create stored procedures.

15.1 Creating a Stored procedure create PROCEDURE

To create a stored procedure, use create PROCEDURE to set parameters. The stored procedure body uses begin… End is separated and the call uses call

Create a stored procedure that calculates the average price
create procedure product_pricing(vend_id int)
begin
    select avg(prod_price) as priceaverage
    from products
    where products.vend_id = vend_id;
end;

View the statement that creates the stored procedure
show create procedure product_pricing;

Call the stored procedure to query the average price
call product_pricing(1002);
Copy the code

The stored procedure here takes or does not take arguments, similar to functions in other languages.

15.2 Deleting a Stored Procedure DROP PROCEDURE

If the stored procedure does not exist, an error is reported. If exists, add the if exists keyword:

Delete the stored procedure
drop procedure product_pricing;

Check before deleting
drop procedure if exists product_pricing;
Copy the code

15.3 Using Parameters

Variable A specific location in memory used to temporarily store data.

The stored procedure takes four parameters, one input parameter, and three parameters for storage, each specified with in (passed to the stored procedure), out (passed out from the stored procedure), and INout (passed in and out to the stored procedure).

All variables in MySQL must start with @. The value retrieved from the stored procedure is stored in the corresponding variable using into.

Stored procedure input and output parameters
create procedure product_pricing(
    in vend_id int.out min_price decimal(8.2),
    out max_price decimal(8.2),
    out avg_price decimal(8.2))begin
    select min(prod_price)
    into min_price
    from products where products.vend_id = vend_id;
    select max(prod_price)
    into max_price
    from products where products.vend_id = vend_id;
    select avg(prod_price)
    into avg_price
    from products where products.vend_id = vend_id;
end;

Call the stored procedure to query the average product price
call product_pricing(1002, @minprice, @maxprice, @avgprice);

Query for the variable just printed
select @minprice, @maxprice, @avgprice;
Copy the code

Try another example, using a stored procedure to calculate the total price of the specified order number and print it to a variable:

Calculate the total price of the specified order number and print it to a variable
create procedure order_pricing(
    in order_num int.out total_price decimal(8.2))begin
    select sum(quantity * item_price)
    into total_price
    from orderitems
    where orderitems.order_num = order_num;
end;

Calculate the total price of order 20005
call order_pricing(20005, @totalprice);

# query total price
select @totalprice;
Copy the code

15.4 Using conditional Statements

Stored procedures can also use if (condition) then… Elseif (condition) then… Else statement, for example, now we want to calculate the price of the item after the discount. The total number of items is 20% off for 3 items and 30% off for 4 items, using the stored procedure:

Create a view to query the total amount of the order
create or replace view sum_order_price as
select sum(quantity * item_price) as total_price, order_num
from orderitems group by order_num;

# 20% off for 3 pieces of total product quantity and 30% off for 4 pieces of total product quantity. Calculate the product price after discount.
create procedure total_discount_price(
    in order_num int.out discount_price decimal(8.2))begin
    Create a variable to hold the total number of items
    declare prod_count int;

    Calculate the total number of items for this order number
    select sum(quantity)
    into prod_count
    from orderitems where orderitems.order_num = order_num;

    No discount for less than 3 pieces
    if prod_count < 3 then
        select total_price
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    # is equal to 3 pieces 20% off
    elseif prod_count = 3 then
        select total_price * 0.8
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    30% off for 3 pieces or more
    else
        select total_price * 0.7
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    end if;
end;

Call the stored procedure to query the amount after the discount
call total_discount_price(20005, @discountprice);

select @discountprice;
Copy the code

In this example, we use a temporary variable prod_count. After calculating the total number of pieces of the order, we assign it to this temporary variable, and then judge it in the if else condition, and then calculate the total amount through the view, and finally save it to the output variable.

16. The cursor

Sometimes, you need to move one or more rows forward or backward in the retrieved rows, which is why cursors are used. A cursor is a database query stored on the MySQL server. It is not a SELECT statement, but a result set retrieved by the statement. After the cursor is stored, the application can scroll through or browse the data as needed.

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

Cursors in MySQL can only be used for stored procedures or functions.

Cursor processing is divided into the following steps:

Declare cursor declare: No data is retrieved, only what is defined to be usedselectStatements;
Open cursor open: Opens the cursor for use, as defined in the previous stepselectThe statement actually retrieves the data;
Fetch: For a cursor filled with data, fetch (retrieve) rows as needed;
Close: You must close the cursor when you end the cursor use. If you do not close the cursor, MySQL will arrive at the end of the cursorendStatement to close the cursor.

Let’s go straight to the example:

drop procedure if exists process_orders;

Fill the actual price for each order into a table using a cursor
create procedure process_orders()
begin
    Define local variables
    declare o_num int;                    # Order number variable in loop
    declare d_price decimal(8.2);        # The actual price variable in the loop
    declare done boolean default false;   Terminating condition Boolean value

    Define the cursor
    declare order_numbers cursor for
        select order_num from orders;

    # terminate condition, not found if there are no more lines to loop through, then assign true to done
    declare continue handler for not found set done = true;

    Create a new table to store the actual price of the order
    create table if not exists ordertotals
    (
        order_num   int,
        order_price decimal(8.2),
        primary key (order_num)
    );

    open order_numbers;         Open the cursor

    Fill rows for table ORDERTotals in order in the loop
    repeat
        fetch order_numbers into o_num;
        call total_discount_price(o_num, d_price); 
        insert into ordertotals(order_num, order_price)
        values (o_num, d_price);
    until done end repeat;              # if done is true, the loop is broken

    close order_numbers;
end;

call process_orders();

select * from ordertotals;
Copy the code

Local variables are first defined with DECLARE. These variables are used to hold intermediate values. Done, which defaults to false, is the termination condition of the loop and will be used as the flag bit in the repeat statement to determine whether to continue the loop. Not found is satisfied if repeat has no more rows to loop over, and true is assigned to done to terminate the loop. Use the stored procedure from the previous chapter in the body of the loop to populate the table OrderTotals with the calculated actual order prices.

Declare statements are sequential; local variables need to be defined after the handle, and the handle must be defined after the cursor, otherwise an error is reported.

In addition to repeat loop, MySQL also supports loop, while loop, basically similar, you can query and learn by yourself.

17. The trigger

If you want certain statements to be executed automatically when an event occurs, consider triggers.

It can only responddelete,insert,updateStatements;
Only tables support triggers, temporary tables and views do not.

17.1 Creating a Trigger Create trigger

To create a trigger, use the create trigger keyword in the following format:

# grammar
create trigger[Trigger name] [after/before] [insert/update/delete] on[table name]for each row 
begin
 [sqlStatement]end;

# remove
drop trigger [if exists[trigger name];Copy the code

For each row means to execute a trigger for each insert row. Only tables

Create a trigger that assigns values to temporary variables when a new product is inserted
create trigger newproduct
    after insert
    on products
    for each row select 'Product added' into @newprod;

Insert a statement
insert into products(prod_id, vend_id, prod_name, prod_price, prod_desc)
values ('XP2000'.1005.'JetPack 200'.55.'JetPack 200, multi-use');

select @newprod;
Copy the code

17.2 Using a Trigger

Use triggers with caution. Since triggers are per-row, do not use triggers on tables that are frequently added, deleted, or modified.

The insert trigger

insertTrigger can be accessed by namenewThe virtual table accesses the inserted row.
before insertStatements can be changednewValues in the virtual table to modify the inserted row;
forauto_incrementAutoincrement column, inbefore 中 newThe value is 0 inafterIs the automatically generated self-value-added.

Get the automatically generated ID of the new user after inserting the user and assign it to the temporary variable
create trigger newcust
    after insert
    on customers
    for each row select new.cust_id
                 into @newcust_after;

# insert user
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values ('Zhang San'.'001 street'.'ShangHai'.'SH'.'666666'.'ZH'.null.null);

# check the new value
select @newcust_after;
Copy the code

Before is often used for data validation.

The delete trigger

deleteTrigger can be accessed by nameoldTo access deleted rows.
oldThe fields in the virtual table are read-only and cannot be modified.

drop trigger if exists deletecustomer;

Create a trigger that inserts the deleted data into another archive table when it is deleted from the customer table
create trigger deletecustomer
    before delete on customers for each row
begin
    insert into customers2(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    values (old.cust_id, old.cust_name, old.cust_address, old.cust_city, old.cust_state, old.cust_zip, old.cust_country, old.cust_contact, old.cust_email);
end;

Delete the customer data just created
delete from customers where cust_id = 10013;

Check whether the customer data in the archive table exists
select * from customers2;
Copy the code

The reason for using before instead of after is that if for some reason the customer information cannot be archived, the DELETE operation will be abandoned to avoid information loss.

The update trigger

updateTrigger can be accessed by nameoldVirtual table access to the pre-update value, access namenewTo access the updated values;
before updateIn the flip-flop,newValues in can be modified;
oldThe fields in the virtual table are read-only and cannot be modified.

drop trigger if exists updatecustomer;

Use a trigger to convert cust_COUNTRY to uppercase for each update
create trigger updatecustomer
    before update
    on customers
    for each row
begin
    set new.cust_country = upper(new.cust_country);
end;

# change data
update customers set cust_country ='zh' where cust_id = 10005;
Copy the code

18. Management services

Transaction processing can be used to maintain database integrity by ensuring that batch MySQL operations are either performed completely or not at all.

For example, if we want to transfer money to someone else, first add money to someone else’s account, then deduct money from our account, if there is a problem in the middle, then we will be in big trouble.

Or in the current database, if we want to add an order information, there are the following steps:

Check if the corresponding customer exists in the database (queried from the Customers table), and add this user information if not.
Retrieve the customer ID, CUST_id.
Add a row of order information to the Orders table, associating it with the customer ID.
Retrieves the new order ID, order_ID, given in the Orders table.
Add a row to the OrderItems table for each ordered item, associating it with the Orders table by the retrieved ID, and with the Products table by the product ID.

What happens if some kind of database failure (exceeding disk limits, security limits, table locks, and so on) prevents a complete process. If the failure occurs between 1 and 2, it doesn’t matter, because it is legal for a customer to have no order information. If it occurs between 3 and 4, then an empty order with no product information will appear, which is serious. If it occurs at 5 and there is a problem adding OrderItems, Then there may be incomplete order information, also very serious.

Using transactions can avoid this, and if problems occur in the middle, fall back to some safe state.

18.1 Transaction Processing

So how to handle this process using transactions:

Check whether the corresponding customer exists in the database, if not, add this user information;
Submit customer information;
Retrieve the customer ID;
Add a row to the Orders table;
If a failure occurs while adding rows to the Orders table, fall back;
Retrieves the new order ID given in the Orders table;
For each item ordered, add a new row to the OrderItems table;
If a failure occurs while adding a new row to OrderItems, roll back all orderItems and Orders rows added;
Submit order information.

Here are a few concepts:

A transaction is a group of SQL statements;
Rollback refers to the process of revoking specified SQL statements.
Commit refers to writing the result of an unstored SQL statement to a database table.
A savepoint is a temporary placeholder set in a transaction to which you can issue a rollback (as opposed to a rollback of the entire transaction).

18.2 Control transaction processing

Use rollback

Start transaction indicates the start of a transaction. Using rollback, you can rollback all statements from start to rollback.

# start transaction
start transaction; 
delete from customers where cust_id > 10005;   Delete a few lines
select * from customers;

Rollback rollback delete statement
rollback;
Copy the code

Rollback can rollback insert, update, and delete statements, but cannot rollback create and drop statements. These two statements can be used in the transaction block, but rollback has no effect.

Using commit commit

Every update (write) performed by a MySQL user is treated as a transaction. This is called an implicit commit, and MySQL commits it for you in the background.

You can use set AutoCOMMIT =0 for each connection to set MySQL not tocommit changes automatically. After setting this, each SQL statement or transaction in which a block of statements is present will require an explicit COMMIT tocommit the transaction.

In a transaction block, however, the commit does not happen implicitly, requiring you to explicitly call it yourself:

Delete 20007 from order details table and then delete 20007 from order details table
start transaction;
delete from orderitems where order_num = 20007;
delete from orders where order_num = 20007;
commit;
Copy the code

Because of the design to the order and the order details, transactions are used to ensure that the order is deleted in its entirety, not in part. If an error occurs in both DELETE statements, the COMMIT will not be executed.

Transactions are automatically closed when commit or ROLLBACK is performed.

Use the reservation point savePoint

Rollback and commit can only commit or rollback the entire transaction block. In some complex scenarios, partial rollback or partial recovery may be required. For example, if the order information fails to be added, the rollback may be performed after the user information is added.

You can use reservation points at this point so that you can roll back to them in case of a problem. Reservation points are relatively simple to use:

Create a reservation point
savepoint sav1;

Roll back to the retention point
rollback to sav1;
Copy the code

Reserve points can be used more often, they are automatically released when the transaction completes, or manually released using Release SavPoint.

19. Safety management

19.1 Access Control

For a database, users should have adequate access to the data they need, no more or no less.

Most users only need to read and write tables, while a few users need to be able to create and delete tables.
Some users may need to read tables, but may not need to update tables;
You might want to allow users to add data, but not delete it;
Some users (administrators) may need access to user accounts, but most do not;
You might want to give users access to data through stored procedures, but not directly;
You may want to limit access to certain features based on where the user is logged in.

Providing different access rights to different users is called access control.

Use the root account only when absolutely necessary. Do not use the root account for routine MySQL operations.

19.2 Managing Users

User information is stored in MySQL’s MySQL library:

View the user list
use mysql;
select user from user;
Copy the code

Create a user account:

Create a user and its password
create user zhangsan identified by '888888';

# change user name
rename user zhangsan to lisi;

# delete user
drop user if exists lisi;
Copy the code

Grant grant to grant permission

# display user's permissions
show grants for zhangsan;

Select * from mysql_demo1
grant select on mysql_demo1.* to zhangsan;

Mysql > delete select from mysql_demo1
revoke select on mysql_demo1.* from zhangsan;
Copy the code

Permission Settings and user Settings are not the focus of this article, you can search or read the documentation.

20. Database maintenance

20.1 Backing up Data

Databases are often backed up. You can use the following methods:

Use the command-line utility mysqldump to dump all database contents to an external file. This utility should run properly before a regular backup so that the dump files can be backed up correctly.
Copy all data from a database with the command-line utility mysqlHotCopy, which is not supported by all database engines.
You can use MySQLbackup table 或 select into outfileDump all data to an external file. Both statements accept the system file name to be created, which must not exist or an error will occur. The data can be usedrestore tableTo recuperate.

20.2 Database Maintenance

analyze tableUsed to check if the key is correct.
check tableUsed to check tables for a number of problems.
For MyISAM table access generated incorrect and inconsistent problems, can be usedrepair tableTo repair.
If you are deleting a large amount of data from a table, use this optionoptimize tableTo reclaim the space used to optimize table performance.

20.3 Viewing Logs

Error Log Error Log

This log records Error, Warning, and Note information about the Mysql system. You can view Error logs when the system fails or a record is faulty.

Show variables like “log_error”; To see where the error log is stored.

Daily Log General Query Log

Record each statement that contains queries, modifications, updates, and so on.

Show global variables like “%genera%”; You can run the set global general_log=on command to query the location where daily logs are stored. If the general_log value is off, the logs cannot be queried. Tail -f /var/lib/mysql-vm-0-17-centos.log; To look at it.

Binary Log

Contains events that describe database changes, such as table creation and data changes. These events are used for backup, restoration, and rollback operations.

You can run show variables like “%log_bin%”; To see where binlogs exist, there will be multiple files, use show master logs; You can view the list of all binlogs in the format bingo.000008. It is important to use Flush logs to manually generate new files when the Binlog is full or when the database is restarted. If you have a problem, you can roll back using Binlog.

Slow Query Log

Logging any queries that perform slowly can be useful when tuning a database.

Show variables like “%slow%”; To see where the slow log is stored.

21. Improve performance

Performance is an eternal pursuit of databases. Here are some Tips on performance:

The database has certain requirements for hardware, so it is far better to run on the old host than on the dedicated server.
MySQL has many configurations, such as memory allocation and cache size. You can adjust the configurations to achieve better performance. View the configuration availableshow variables; 和 show status;Check the configuration
Different statements that perform the same function have different performance, and you can find ways to perform better.
Try not to retrieve data that is not needed, such as canselect param, don’tselect *.
General combination query than inselectThe use oforFast.
generalfulltext 比 likeFast.

Online posts are mostly different in depth, and even some inconsistent, the following article is a summary of the learning process, if you find mistakes, welcome to point out that if this article helps you, don’t forget to support oh, your praise is the biggest power OF my update! ~

Reference Documents:

MySQL Must Know must Know

MySQL 中文版

PS: This article is included in the following blog Github – SHERlocked93/blog series of articles, welcome you to pay attention to my public account front-end afternoon tea, direct search can be added or click here to add, continue to push for you front-end and front-end surrounding related quality technical articles, common progress, come together ~

In addition, you can join the wechat group of “front-end afternoon tea Communication Group”. Search sherlocked_93 and add me as friends on wechat. If you add comments to the group, I will pull you into the group