What do you do with results after you retrieve data with query?-CodePudding

I am sorry about the novice nature of questions, but I could not find a post related to this elementary question. Say, I select the name of all customers from Germany in table "Customers".

SELECT c.customer_name, c.Country
FROM Customers c
WHERE c.Country = 'Germany'

So, I have a nice result with all customers from Germany. So, what's next? Here is one way that I could potentially save data that I selected by creating a new table:

CREATE TABLE name_country AS
SELECT Name, Country
FROM Customers
WHERE Country = 'Germany'

and I created a table called "name_country" in the database. Is this the only way of saving selected name-country in the database?

Question 2: is there a difference between the code above and the following code?

INSERT INTO name_country
SELECT Name, Country
FROM Customers
WHERE Country = 'Germany'

CodePudding user response：

usually, sql queries are executed in one of two ways/reasons:

manually by typing out the query directly in sql (or phpmyadmin, etc) as it appears you have done. Usually done because there isn't already an easy way to answer whatever question you need answered, or you're testing a query for correctness to be used in #2. Maybe you want to know if a specific customer has an account or not for example, and you don't expect to need to execute the query again. It's a "one off".
From inside a program written in (virtually) any programming language. All major programming languages have a way to connect to databases and execute SQL queries. Then you can iterated through the result in code where you can either show the results in some form on the screen, or do some useful computation with it (or both of course). This can happen in a couple different "layers". For example, PHP is (unfortunately) a popular server side language which many people use for the sole purpose of executing a sql query and returning the result over the web. The PHP script can be triggered by a POST/GET/etc request from a client's web browser, so you can create a web page that populates with your customers that are from Germany for example.

I don't think you want to actually save the result of the query (because that kind of defeats the purpose of using a database in my opinion. What happens when the data changes?) but rather the query itself and execute the query again when you want to see the answer to your question. Usually we "save" queries in a program as discussed in #2.

Question 2: To my knowledge, the only difference is in performance.

You might be more interested in a VIEW, however, because that will update when the underlying data changes, but the created table from a query will not change if the data later changes. Then a program could read from the VIEW as if it were like any other table as mentioned.