Brother Lian Learning Python (1)--mysql

MySQL official website: http://www.mysql.com

MySQL is one of the best RDBMS applications in Web applications

Rdbms:relational database Management system relational databases management systems

Brother Lian Learning Python (1)--mysql

Using UNION | UNION all syntax

UNION is used to combine the result set of multiple queries, and I am currently encountering the following two scenarios that are more effective:

1. Complex queries of the same table are difficult to solve by a SELECT statement
2. Multi-table query, but the return of the data consistent, some common aggregated data statistical requirements

Union can also add a limit, an ORDER BY clause, to sort and filter the result set after UNION.

With these features of UNION, we can hand over some of the work that would otherwise have been required to write code to the database, while also reducing the number of SQL and improving performance.

For example, the following is a sample:

Select ' Product ' as type, COUNT (*) as count from ' products '
Union
Select ' Comment ' as type, COUNT (*) as count from ' comments '
Order by Count;

We also queried the total number of records for the Products table and the comments table through the UNION syntax, and sorted by count.

Combining UPDATE | DELETE and JOIN

All along, we benefited from the convenience of a federated query (SELECT + JOIN), but there was a mindset in the invisible (at least for me), but the UPDATE DELETE could also be used in conjunction with JOIN, simplifying SQL writing.

When you use UPDATE | Delete + JOIN, we may either query the ID of the record to be deleted and then delete it based on the ID, or use the in subquery. The former needs to write two SQL statements that process logic in the program, which sometimes does not work correctly.

In the latter case, someone should have encountered such a mistake:

ERROR 1093 (HY000): You can ' t specify target table ' xxx ' for update

in FROM clause

This error occurs because MySQL does not support the same SQL statement attempting to query and modify two operations on the same table.

For example, delete an article without a comment this statement

Delete from articles
where ID in (
Select a.ID from articles as a LEFT join comments as C on a.id=c.article_id
where C.is is NULL
)

The articles table is both queried and updated, and the above error will occur.

However, if the DELETE is in conjunction with a JOIN, you can write the SQL statement directly, much more concise:

Delete s from articles as a
Left join comments as C on a.id=c.article_id
Where c.is is NULL

Of course, UPDATE is the same:

update articles as a
Left join comments as C on a.id=c.article_id
Set A.deleted=1
Where c.is is NULL

Case syntax

Case syntax allows you to make simple branch judgments within SQL and return different values depending on different conditions. For example, consider this requirement:

There are multiple orders for a product, there are two statuses for the order to be paid and not paid, and now given a list of items, returns the number of paid and unpaid orders for each item.

At this point we can use a case statement and GROUP by through a SQL implementation:

Select
PRODUCT_ID,
Count
Case Is_paid
When 1 then 1
else null
End
) as Total_paid,
Count
Case Is_paid
When 0 then 1
else null
End
) as Total_not_paid
From Orders
where product_id in (1, 2, 3, 4)
Group by product_id;

With ORM library, this kind of writing can help us implement eager loading, avoid n + 1 query.

Because this scenario is relatively straightforward, we can also use the Process Control function provided by MySQL (control flow Functions) to make the SQL more concise:

Select
PRODUCT_ID,
Count (if (Is_paid = 1, 1, null)) as Total_paid,
Count (if (is_paid = 0, 1, null)) as Total_not_paid
From Orders
where product_id in (1, 2, 3, 4)
Group by product_id;

Use INSERT into ... SELECT syntax

by INSERT into ... Select syntax, we can write the result set of a select directly into another table without the need for program processing. With this syntax, plus some modifications, we can easily implement more requirements scenarios.

For example, we want to give all customers who buy a product a coupon worth 10 yuan, we can write:

Insert into tickets (user_id, Price, expires_in)
Select
USER_ID, as Price, ' 2017-09-09 ' as expires_in
From Orders
where product_id=123 and is_paid=1;

For example, in the course of elective courses, we have to assign a group of people a class, assuming that 1 classes of people to allocate physical education and art classes, we can use this syntax plus cross JOIN implementation:

INSERT INTO Class_members (class_id, user_id, status)
Select
C.id as class_id,
U.id as user_id,
1 as Status
From classes as C cross join users as U
where C.name in (' Physical education ', ' art class ') and U.class_name= ' 1 class ';

----------------Python learning communication, resource Sharing Group: 563626388 QQ

Brother Lian Learning Python (1)--mysql