MySQL must know and know

Time:2021-10-20

1. Understand SQL

A database is a container (usually a file or group of files) that holds organized data.

A table is a structured list of a particular type of data.

Schema information about the layout and characteristics of databases and tables.

Column a field in a table. All tables consist of one or more columns.

A record in a row table.

The type of data allowed by the data type.

A primary key is a column (or group of columns) whose value can uniquely distinguish each row in the table.

SQL (pronounced s-q-l or sequel) is an abbreviation for structured query language. SQL is a language specially used to communicate with databases.

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

  • Any two rows do not have the same primary key value;
  • Each row must have a primary key value (null values are not allowed for primary key columns).

SQL has the following advantages

  • SQL is not a specific database vendor specific language. Almost all important DBMS support SQL, so learning this language enables you to deal with almost all databases.
  • SQL is easy to learn. Its sentences are all composed of highly descriptive English words, and the number of these words is small.
  • Although SQL looks very simple, it actually makes a powerful language, uses its language elements flexibly, and can perform very complex and advanced database operations.

2. Operation database table

show databases;                   //  Returns a list of the current database
use crash;                        //  Choose to use crash database
show tables;                      //  Returns a list of tables in the currently selected database
show columns from customers;      //  Displays the customers table column information
show status;                      //  Used to display a wide range of server status information
Show create database and show create table are used to display MySQL statements that create databases or tables respectively
Show errors and show warnings are used to display server errors or warnings

3. Retrieve data

Retrieve a single column
//Use the select statement to retrieve a product named prod from the products table_ Name column
select prod_name from products;

Retrieve multiple columns
//When selecting multiple columns, be sure to add a comma between the column names and not after the last column name
select prod_id, prod_name, prod_price from products;

Retrieve all columns
// 使用通配符* Retrieve all columns
select * from products;

Retrieve different rows
//Using the distinct keyword, different values are returned
//Distinct applies to all columns, not just the columns that precede it
select distinct vend_id from products;

Limit results
/*
*Limit 5, 5 returns the 5 lines starting from line 5.
*The first number is the start position and the second number is the number of rows to retrieve
*Limit with a value always starts from the first line, and the number given is the number of returned lines.
*Limit with two values can be specified to start from the position where the line number is the first value.
*/
select prod_name from products limit 5 , 5;

4. Sort and retrieve data

//Sort data
//Order by prod_ The name column sorts the data alphabetically
select prod_name from products order by prod_name;

//Sort by multiple columns
//Order by specifies the column names separated by commas
select prod_id , prod_price , prod_name from products order by prod_price , prod_name;

//Specify sort direction
//Order by default uses ascending order. In order to sort in descending order, you can specify the desc keyword
select prod_id , prod_price , prod_name from products order by prod_price desc;

/*
*If you want to perform descending order on multiple columns, you must specify the desc keyword for each column
*Order by should be after from. If you use limit, it must be after order by
*/

5. Filter data

//The data is filtered according to the search criteria specified in the where clause
select prod_name , prod_price from products where prod_price = 2.1;

// between
select * from prod_price between 5 and 10;

// IS NULL
select cust_id from customers where cust_email IS NULL;

Where clause operator

Operator explain
= be equal to
<> Not equal to
!= Not equal to
< less than
<= Less than or equal to
> greater than
>= Greater than or equal to
between Between two values specified

6. Data filtering

//The and | or operator is used to join or change the keywords of clauses in the where clause. Also known as logical operators
select prod_name , prod_price from products where vend_id = 1002 or vend_id ==1003 and prod_price >= 10;

//Use parentheses to explicitly group operators in the where clause. There is no harm in using parentheses, and ambiguity can be eliminated.
select prod_name , prod_price from products where (vend_id = 1002 or vend_id ==1003) and prod_price >= 10;

//The in operator is used to specify a condition range in which each condition can be matched
select prod_name , prod_price from products where vend_id in (1002,1003);

//The not operator has and has only one function, that is, to negate any conditions that follow it
//Not supports in, between, and exists
select prod_name , prod_price from products where vend_id not in (1002,1003);

7. Filter with wildcards

A wildcard is a special character used to match a portion of a value

A search pattern is a search condition consisting of literals, wildcards, or a combination of both.

The percent sign (%) wildcard,% indicates that any character appears any number of times.

Case sensitive search can be case sensitive according to MySQL configuration.

//Find all products that start with jet
select prod_id , prod_name from products where prod_name like 'ject%';

Underline()Wildcard underscores are used for the same purpose as% but only match a single character rather than multiple characters

Unlike% which can match 0 characters,Always match one character, no more, no less.

//Find the product followed by the specified text content
select prod_id , prod_name from products where prod_name like '_ ton anvil';

Using wildcard techniques

As you can see, MySQL wildcards are useful. But this feature comes at a cost: wildcard searches generally take longer to process than other searches.

  • Do not overuse wildcards. If other operators can achieve the same purpose, you should use other operators.
  • When wildcards are really needed, do not use them at the beginning of the search pattern unless absolutely necessary. Put the wildcard at the beginning of the search mode, and the search is the slowest.
  • Pay careful attention to the position of wildcards. If misplaced, the desired data may not be returned.

8. Search with regular expressions

Use regular expressions to better control data filtering.

//Basic character filtering
//Retrieve column prod_ Name contains all lines of text 1000
select prod_name from products where prod_name regexp '1000';

//(.) points match any character
select prod_name from products where prod_name regexp '.000';

//Or to match one of them
select prod_name from products where prod_name regexp '1000|2000';

//[] matches one of several characters
//In fact, regular expression [123] is the abbreviation of [1|2|3], and the latter can also be used

//Matching range [1-9] starts with 1 to 9
select prod_name from products where prod_name regexp '[1-9] Ton';

//Match special characters with \ escape
//In order to match special characters, you must use \ \ as the leading character\\- Indicates search -, \ \. Indicates search
select vend_name from vendors where vend_name regexp '\\.';

White space metacharacter

Metacharacter explain
\\f Page change
\\n Line feed
\\r enter
\\t Tabulation
\\v Longitudinal tabulation

Match \ to match the backslash (\) character itself, you need to use\\\

Most regular expression implementations use a single backslash to escape characters so that they can use the characters themselves.
However, MySQL requires two backslashes (MySQL interprets one and the regular expression library interprets the other).

Matching character class

Class 1 explain
[ :alnum: ] Any letter and number (same as [a-za-z0-9])
[ :alpha: ] Any character (same as [a-za-z])
[ :blank: ] Spaces and tabs (same as [\ \ t])
[ :cntrl: ] ASCII control characters (ASCII 0 to 31 and 127)
[ :digit: ] Any number (same as [0-9])
[ :graph: ] Same as [: Print:] but excluding spaces
[ :lower: ] Any lowercase letter (same as [A-Z])
[ :print: ] Any printable character
[ :punct: ] Any character that is neither in [: alnum:] nor in [: CNTRL:]
[ :space: ] Any white space character including spaces (same as [\ \ f \ \ n \ \ R \ \ t \ \ v])
[ :upper: ] Any capital letter
[ :xdigit: ] Any hexadecimal digit (the same as [a-fa-f0-9])

Match multiple instances

All regular expressions used so far attempt to match a single occurrence.

Sometimes you need to have more control over the number of matches.

For example, you may want to find a word and be able to adapt to a trailing s, and so on

Repeating metacharacter

Metacharacter explain
* 0 or more matches
+ 1 or more matches (equal to {1,})
0 or 1 matches (equal to {0, 1})
{n} Specified number of matches
{n,} No less than the specified number of matches
{n,m} Range of specified number (m no more than 255)
//[0-9] match any number, sticks? Match stick and sticks
//No, Matching stick and sticks can be very difficult
select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)';

Locator

All the examples so far match any text in a string. In order to match text at a specific location, you need to use locators

Positioning metacharacter

Metacharacter explain
^ Start of text
$ End of text
[ [ :<: ] ] The beginning of the word
[ [ :>: ] ] The end of a word
//Match only if it starts with any number or
select prod_name from products where prod_name regexp '^[0-9\\.]';

The difference between like and regexp

select prod_name from products where prod_name like '1000';
select prod_name from products where prod_name regexp '1000';

Executing the above two statements, you will find that the first statement does not return data, while the second statement returns data. Why?

This is because like matches the entire column, and if the matching text appears in the column value, like will not find it
Regexp matches in the column value. If the matched text appears in the column value, regexp will find it

When regexp uses the locator ^ to start the expression and $to end the expression
The function of regexp is the same as that of like
//Simple regular expression test
select 'hello' regexp '[0-9]';

This example will obviously return 0 (because there is no keyword in the text Hello)

9. Create calculation field

A field basically has the same meaning as a column, but a database column is generally called a column.

Concatenate concatenates values together to form a single value.

  • If you want to display both company name and company address in one field, these two information are generally contained in different table columns.
  • Column data is mixed case, but the reporter needs to express all data in uppercase
  • Total, average or other calculations need to be made according to the table data.

    In the above example, none of the data stored in the table is required by the application. We need to retrieve the converted, calculated or formatted data directly from the database; Instead of retrieving the data and reformatting it in a client application or reporter.

    This is where calculated fields come into play.

//Concat() function, splicing two columns
select concat(vend_name,'(',vend_country,')') from vendors;
Output: Acme (USA)

As you can see from the previous output, the select statement splices address fields well.

But what is the name of this new calculated column? In fact, it has no name, it’s just a value.

However, an unnamed column cannot be used in a client application because the client cannot reference it.

An alias is a field or name that is worth replacing. Aliases are assigned with the as keyword.

//Assign the calculated value to vend_ name
select concat(vend_name,'(',vend_country,')') as vend_title from vendors;
//Perform arithmetic calculations
//The price (unit price multiplied by the ordered quantity) of the summarized items is given the alias expanded_ price
select prod_id , quantity , item_price , quantity*item_price as expanded_price from orderitems;

10. Using data processing functions

SQL supports using functions to process data.

Common text processing functions

function explain
left() Returns the character to the left of the string
length() Returns the length of the string
locate() Find a substring of the string
lower() Convert string to lowercase
ltrim() Remove the space on the left of the string
right() Returns the character to the right of the string
rtrim() Remove the space to the right of the string
soundex() Returns the Soundex value of the string
substring() Returns the character of the substring
upper() Convert string to uppercase
//The soundex() function Soundex is an algorithm that converts any text string into an alphanumeric pattern that describes its phonetic representation.
//Soundex considers similar pronunciation characters and syllables, making it possible to compare strings rather than letters.

//Search using the soundex() function, which matches all contact names that sound like y. lie
select cust_name , cust_contact from customers where cust_contact = 'Y . Lie';
//Query y Lee

Date and time handler

function explain
AddDate() Add a date (day, week, etc.)
AddTime() Add a date (hour, minute, etc.)
CurDate() Returns the current date
CurTime() Returns the current time
Date() Returns the date portion of the datetime
DateDiff() Calculate the difference between two dates
Date_Add() Highly flexible date application function
Date_Format() Returns a formatted date or time string
Day() Returns the days portion of a date
DayOfWeek() For a date, return the day of the week
Hour() Returns the hour portion of a time
Minute() Returns the minute portion of a time
Month() Returns the month portion of a time
Now() Returns the current date and time
Second() Returns the second part of a date
Time() Returns the time portion of a date time
Year() Returns the year portion of a date
//Date comparison
select cust_id , order_num form orders where Date(order_date) = ' 2015-09-01';

Common numerical processing functions

function explain
Abs() Returns the absolute value of a number
Cos() Returns the cosine of an angle
Exp() Returns the exponent of a number
Mod() Returns the remainder of the divide operation
Pi() Return pi
Rand() Returns a random number
Sin() Returns the sine of an angle
Sqrt() Returns the square root of a number
Tan() Returns the tangent of an angle

11. Summary data

Aggregate function is a function that runs on a row group and calculates and returns a single value.

SQL aggregate function

function explain
AVG() Returns the average value of a column
COUNT() Returns the number of rows in a column
MAX() Returns the maximum value of a column
MIN() Returns the minimum value of a column
SUM() Returns the sum of the values in a column

12. Grouped data

Grouping allows data to be divided into multiple logical groups so that aggregation calculations can be performed on each group.

The best way to understand grouping is to look at an example:

//Returns the number of products each supplier can provide
select vend_id , count(*) as num_prods from products group by vend_id;
vend_id num_prods
1001 3
1002 2
1005 7

Before using the group by clause, you need to know some important provisions.

  • The group by clause may contain any number of columns. This makes it possible to nest packets and provide more detailed control over data packets.
  • If a group is nested in the group by clause, the data will be summarized on the last specified group. In other words, when grouping is established, all specified columns are calculated together (so data cannot be retrieved from individual columns).
  • Each column listed in the group by clause must be a retrieval column or a valid expression (but not an aggregate function). If you use an expression in select, you must specify the same expression in the group by clause. Alias cannot be used.
  • Except for the aggregate calculation statement, each column in the select statement must be given in the group by clause.
  • 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.
  • The group by clause must appear after the where clause and before the order by clause.
//Using the rollup keyword, you can get the value of each group and the summary level of each group (for each group)
select vend_id , count(*) as num_prods from products group by vend_id with rollup;

Usage of with Rollup

Filter grouping

In addition to grouping data with group by, MySQL also allows you to filter groups, specify which groups to include and exclude.

However, where cannot complete the task because where filtering specifies rows rather than groups.

In fact, where has no concept of grouping.

MySQL provides another clause for this, that is, having.

In fact, all types of where clauses learned so far can be replaced by having.

The only difference is that where filters rows while having filters groups.

//Filter groups below two orders
select cust_id , count(*) as order from orders group by cust_id having count(*) >= 2;

Difference between having and where

Here is another understanding method. Where filters before data grouping and having filters after data grouping.

Where excluded rows are not included in the group, which affects the groups filtered based on these values in the having clause.

//Filter the grouping of orders whose price is less than 10 yuan and less than two orders
SELECT vend_id , COUNT(*) as num_prods from products where prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

Don’t forget that order by should also be given when using the group by clause. This is the only way to ensure that the data is sorted correctly. Never rely solely on group by to sort data.

//Sort according to the order specified by group by_ Num sort
select order_num , SUM(quantity*item_price) as ordertotal from orderitems group by order_num having SUM(quantity*item_price) >= 50;

//At this time, if you want to sort by order price, just use order by
select order_num , SUM(quantity*item_price) as ordertotal from orderitems group by order_num having SUM(quantity*item_price) >= 50 order by ordertotal;

The select clause uses the order that must be followed

clause explain Must I use
select The column or expression to return yes
from Table from which to retrieve data Used only when selecting data from a table
where Row level filtering no
group by Group description Used only when aggregating by group
having Group level filtering no
order by Output sort order no
limit Number of rows to retrieve no

13. Use subquery

Subquery is a query nested in other queries.

Orders are stored in two tables.

The order table stores a row for each order that contains the order number, customer ID, and order date.

The items for each order are stored in the relevant OrderItems table.

The orders table does not store customer information. It only stores the customer’s ID.

The actual customer information is stored in the customers table.

  1. Retrieve the number of all orders containing item tnt2.
    SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
    order_num
    20005
    20007
  2. Retrieve the IDs of all customers with the order numbers listed in the previous step.
    SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
    cust_id
    10001
    10004
  3. Retrieve the customer information of all customer IDS returned in the previous step.
    SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);
    cust_name cust_contact
    Coyote Inc. Y Lee
    Yosemite Y Sam

    Each of the above steps can be performed separately as a query. The results returned by one select statement can be used in the where clause of another select statement.

    You can also use the where clause of another select statement.

    SELECT cust_name,cust_contact FORM customers WHERE cust_id IN (
    SELECT cust_id 
    FROM orders
    WHERE order_num IN (
    SELECT order_num 
    FROM orderitems
    WHERE prod_id = 'TNT2'
    )
    );                                                                                                                  

    Format SQLSelect statements containing subqueries are difficult to read and debug, especially when they are complex. As shown above, the use of subqueries can be greatly simplified by decomposing subqueries into multiple rows and indenting them appropriately.

13. Use subquery

Columns must matchWhen using a subquery in a where clause, you should ensure that the select statement has the same number of columns as in the where clause. Typically, a subquery will return a single column and match a single column, but you can also use multiple columns if needed.

Use subqueries as calculated fields

SELECT cust_name,
               cust_state,
               (SELECT COUNT(*)
                FROM orders
                WHERE order.cust_id = customers.cust_id) AS order
             FROM customers
             ORDER BY cust_name;

The where clause in the subquery is slightly different from the previous where clause because it uses fully qualified column names.

The subquery SQL retrieves cust from the current customers table_ id。

This type is called related subquery.

Related subquery refers to the subquery of external query.

14. Connection table

This work adoptsCC agreement, reprint must indicate the author and the link to this article