Mysql basics: You must Know, you must Know

“This is the 15th day of my participation in the November Gwen Challenge. See details of the event: The Last Gwen Challenge 2021”.

The sample table

mysql> DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | NO   |     | NULL    |       |
| pirates | char(10)    | NO   |     | NULL    |       |
| name    | char(10)    | NO   |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| post    | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
Copy the code

Pick up where you left off!

Create a calculated field

1. Merge the name and sex columns in the splicing field. Alias the new column using the AS keyword.

mysql> SELECT Concat(name, '(', sex, ') ') AS new_column
    -> FROM one_piece;
Copy the code

Quantity (price); quantity (price)

mysql> SELECT quantity, price,
    -> quantity * price AS total_price
    -> FROM test
Copy the code

Second, the function

Common text processing functions

function	instructions
LEFT(str, length)	Returns the left portion of the string of the specified length
RIGHT(str, length)	Returns the right-hand part of the string of the specified length
LTRIM(str)	Remove the space to the left of the string
RTRIM(str)	Remove the Spaces to the right of the string
LOWER(str)	Converts a string to lowercase
UPPER(str)	Converts a string to uppercase
LENGTH(str)	Returns the length of the string

Use LENGTH(STR) to get the LENGTH of the string.

mysql> SELECT name, LENGTH(name) AS length
    -> FROM one_piece;
Copy the code

Date and time handlers

Query information about people born in 2000.

mysql> SELECT *
    -> FROM test
    -> WHERE YEAR(brithday)=2000;
Copy the code

Numerical processing function

function	instructions
ABS()	Returns the absolute value of a number
COS()	Returns the cosine of an Angle
SIN()	Returns the sine of an Angle
TAN()	Returns the tangent of an Angle
PI()	Returns PI
EXP()	Returns the exponent value of a number
SQRT()	Returns the square root of a number

Take the ABS() function as an example

sql> SELECT ABS(- 1);
+---------+
| ABS(- 1) |
+---------+
|       1 |
+---------+
Copy the code

Third, data aggregation

Aggregation function

function	instructions
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 of a column

1. The AVG() function queries the average age.

mysql> SELECT AVG(age) AS avg_age
    -> FROM one_piece
Copy the code

2.COUNT() can be used in two ways:

COUNT(*)Count the number of rows in the table, including null values.

mysql> SELECT COUNT(*) AS num_person
    -> FROM one_piece;
Copy the code

COUNT(column)Not for a particular columnNULLRow counts.

mysql> SELECT COUNT(name) AS num_name
    -> FROM one_piece;
Copy the code

When column is a numeric column, MAX(column)/MIN(column) returns the maximum/minimum value of the column.

When column is text data, MAX(column)/MIN(column) returns the last row/first row of the sorted column data.

4.SUM() The SUM() function returns the SUM of the specified column values (ignoring NULL rows).

mysql> SELECT SUM(price * quantity) AS total_price
    -> FROM test
Copy the code

Combinatorial aggregation function

Calculate the number of pieces of data in the one_piece table, the minimum, maximum, and average ages.

mysql> SELECT COUNT(*) AS num_person,
    -> MIN(age) AS age_min,
    -> MAX(age) AS age_max,
    -> AVG(age) AS age_avg
    -> FROM one_piece;
Copy the code

4. Data grouping

The data packet

Groups are used to divide data into logical groups, and aggregate calculations are performed on each group. Example: Count the number of pirates in each group.

mysql> SELECT pirates, COUNT(*) AS num_person
    -> FROM one_piece
    -> GROUP BY pirates;
Copy the code

Group by note:

GROUP BYCan be nested.
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 the grouping column contains hasNULLValue, thenNULLWill be returned as a group. If you have multiple rows in a columnNULLValues, and they will be grouped.
GROUP BYClause must appear inWHEREAfter the clause,ORDER BYClause before.

Packet filtering

Use the HAVING clause to filter data after grouping.

Query the name and number of pirate groups with more than 500 members.

mysql> SELECT pirates, COUNT(*) AS num_person
    -> FROM one_piece
    -> GROUP BY pirates
    -> HAVING COUNT(*) > = 500;
Copy the code

The main differences between WHERE and HAVING:

WHEREFilter data before grouping it,HAVINGFilter data after grouping.

SELECT clause order:

clause	instructions	Is it mandatory to use
SELECT	The column or expression to return	is
FROM	A table from which data is retrieved	Used only when selecting data from a table
WHERE	Row-level filter	no
GROUP BY	Group show	Use only when calculating aggregation by group
HAVING	Group level filter	no
ORDER BY	Output sort order	no

Fifth, sub-query

Use subqueries for filtering

Now query straw hat pirate group ranking information.

mysql> SELECT rank
    -> FROM rank_info
    -> WHERE id IN (SELECT id
    ->                     FROM one_piece
    ->                     WHERE pirates = The Straw Hat Pirates);
Copy the code

Note:

inSELECTStatement, subqueries are alwaysFrom the inside outTo deal with.
As a subquerySELECTStatement can only query a single column. Retrieving more than one column will cause an error.

Use subqueries as computed fields

Select * from one_piece; select * from one_piece; select * from one_piece; select * from one_piece;

mysql> SELECT rank,
    -> (SELECT COUNT(*)
    ->  FROM one_piece AS oe
    ->  WHERE oe.id = ro.id) AS num_person
    -> FROM rank_info AS ro
    -> ORDER BY rank;
Copy the code

Note: The above example uses Oe. id and ro.id, not ID directly, because there are ID columns in both tables and this syntax must be used when there is a chance of confusing column names.

Six, table connection

Since the coupling

Now if someone does not know the pirate group that Joba belongs to, want to know the name of all members of the pirate group that Joba belongs to and the bounty. First look at the way to query:

mysql> SELECT name, bounty
    -> FROM one_piece
    -> WHERE pirates = (SELECT pirates
    ->                    FROM one_piece
    ->                    WHERE name = 'and');
Copy the code

Next, use self-join:

mysql> SELECT c1.name, c1.bounty
    -> FROM Customers AS c1, Customers AS c2
    -> WHERE c1.pirates = c2.pirates
    ->  AND c2.name = 'and';
Copy the code

In general, the self-join approach is much faster than the subquery approach.

Equivalent coupling

Join is a mechanism used to associate 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. Joins are not physical entities. In other words, it does not exist in the actual database table. It exists only during query execution.

Table1, table2, table2, table2, table2

table1                      table2
+------+------+------+ +------+------+------+
| A    | B    | C    |      | C    | D    | E    |
+------+------+------+ +------+------+------+
|    1 |    2 |    3 |      |    2 |    3 |    4 |
|    4 |    5 |    6 |      |    6 |    7 |    8 |
+------+------+------+ +------+------+------+
Copy the code

Now get the data from both tables through a table join.

mysql> SELECT *
    -> FROM table1 AS t1, table2 AS t2
    -> WHERE t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+
Copy the code

Note: in the above exampleWHEREIf there are no conditions, the result is the Cartesian product of the two tables6 × 9A total of 54 pieces of data

In the connection

The above join, which is exactly an equivalent join, or inner join, has another syntax. Return the same result as above.

mysql> SELECT *
    -> FROM table1 AS t1 INNER JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+
Copy the code

The general inner join can be represented by the following figure, taking the same part of the associated fields of two tables.

Natural bond

A natural join is a special equivalent join that automatically compares identical attribute columns in two relational tables without adding join conditions and eliminates duplicate attribute columns from the result.

mysql> SELECT *
    -> FROM table1 AS t1 NATURAL JOIN table2 t2;
+------+------+------+------+------+
| C    | A    | B    | D    | E    |
+------+------+------+------+------+
|    6 |    4 |    5 |    7 |    8 |
+------+------+------+------+------+
Copy the code

Outer joins

Left-outer join left-outer join, the records in the left table (Table1) will be all represented, and the records in the right table (Table2) will only show those that meet the search criteria. The right table is NULL where there are insufficient records.

mysql> SELECT *
    -> FROM table1 AS t1 LEFT JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
|    1 |    2 |    3 | NULL | NULL | NULL |
+------+------+------+------+------+------+
Copy the code

Right-outer join right-outer join, the records in the right table (Table2) will be all represented, and the right-left table (Table1) will only show the records that meet the search criteria. The left table is NULL where there are insufficient records.

mysql> SELECT *
    -> FROM table1 AS t1 RIGHT JOIN table2 AS t2
    ->  ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
| NULL | NULL | NULL |    2 |    3 |    4 |
+------+------+------+------+------+------+
Copy the code

Comparison of four connections

In the connection	Natural connection (de-weighting)
The left outer joins	Right connection

This is what I want to share today. Search Python New Horizons on wechat, bringing you more useful knowledge every day. More organized nearly a thousand sets of resume templates, hundreds of e-books waiting for you to get oh!