First of all, the article describes the impact of good classification and bad classification on data entry and query in the way of movie classification.

Updating data with UPDATE alone may be slower, using a very large UPDATE method here.

That's CASE

UPDATE my_table
SET new_column =
CASE
  WHEN column1 = somevalue1
THEN newvalue1
  WHEN column2 = somevalue2
THEN newvalue2
  ELSE newvalue3
END;

For example, you can use this method to update the data for the new_column column.

=======================================================================================

Here, if more than one classification is met, the first one is executed.That's the problem.

So we should add AND to the WHEN statement

For example:

WHEN cartoon = 'T' AND rating = 'G' THEN 'family'

Then we need a way to organize the SELECT data.For example, select P251, a movie that starts with A and is categorized as family

SELECT title, category FROM movie_table 
WHERE title LIKE 'A%' AND category = 'Family';

This is cumbersome if the movie is super many, so we'll use the ORDER BY command.

When SELECT, the query results returned by sorting a column (ORDER):

SELECT title, category
FROM movie_table
WHERE
title Like 'A%'
AND
category = 'family'
ORDER BY title;

If it's A-Z, what about numbers?

SELECT title, category
FROM movie_table
WHERE
category = 'family'
ORDER BY title;

Just remove'A%'.

Using ORDER query:

Result:

======================================================================================================

Remember: One after the other

NULL>Numbers>Capital Letters>Lowercase Letters

 

A multi-column sorted query, preferring the first column, finishing the first column and reordering the second column:

SELECT title, category, purchased
FROM movie_table
ORDER BY category, purchased;

Of course, more columns are also possible.

==================================================================

If we want to query the other way around, for example, starting with Z, then add the keyword DESC after the column name

DESC is not a description but descending in ORDER

Correspondingly, the default order is ASC (not writable):P261

SELECT title, purchased
FROM movie_table
ORDER BY title ASC, purchased DESC;

===============================================================

Use SUM to sum up for us.

We're going to calculate biscuit sales for a person named Nicole.

SELECT SUM(sales)
FROM cookie_sales
WHERE first_name = 'Nicole';

Of course, if there are many people here, one by one is still a bit of a hassle.
Here GROUP BY is used to group together:

SELECT first_name, SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC;

AVG with GROUP BY calculated mean:

SELECT first_name, AVG(sales)
FROM cookie_sales
GROUP BY first_name;

MIN and MAX:

SELECT first_name, MAX(sales)

FROM cookie_sales

GROUP BY first_name;

 

SELECT first_name, MIN(sales)

FROM cookie_sales

GROUP BY first_name;

 

COUNT returns the number of rows in the specified column, where is the number of sales days:

SELECT COUNT(sale_date)
FROM cookie_sales;

This actually returns the number of days recorded and should be used in conjunction with GROUP BY:

SELECT first_name, COUNT(sale_data)
FROM cookie_sales
GROUP BY first_name;

===================================================================================

DISTINCT picks a different value:

SELECT DISTINCT sale_date
FROM cookie_sales
ORDER BY sale_date;

Return the person with the most days of sales with COUNT:

SELECT COUNT(DISTINCT sale_date)
FROM cookie_sales;

================================================================

If there are many results, but we only want to query the previous section, use LIMIT to limit the number:

SELECT first_name, SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC
LIMIT 2;

There are only two lines.

What if we only want second place?

SELECT first_name, SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC
LIMIT 1,1;

Here, 1,1 means to start with the second item and display only one record.Note that all numbering starts at 0, meaning 0 refers to the first item.