Select * from UNION

What is a composite query

SQL allows you to execute multiple queries (multiple SELECT statements) and return the results as a single query result set. These combinations are often called union or compound queries. There are two common situations in which you need to use a combined query:

  • Returns structural data from different tables in a query
  • Perform multiple different queries on a table, returning data per query

Creating a composite query

You can use the UNION operator to combine several SQL queries.

1 - statements

SELECT cust_name, cust_contact,cust_email

FROM Customers

WHERE cust_state IN ('IL'.'IN'.'MI');

2 - statements

SELECT cust_name, cust_contact,cust_email

FROM Customers

WHERE cust_name = 'Fun4ALL';

Copy the code

Combine the above two queries together by composing queries:

-- Aggregate query

SELECT cust_name, cust_contact,cust_email

FROM Customers

WHERE cust_state IN ('IL'.'IN'.'MI')

UNION    Key words -

SELECT cust_name, cust_contact,cust_email

FROM Customers

WHERE cust_name = 'Fun4ALL';

Copy the code

We can also use multiple WHERE conditions:

1 - statements

SELECT cust_name, cust_contact,cust_email

FROM Customers

WHERE cust_state IN ('IL'.'IN'.'MI')

AND cust_name = 'Fun4ALL';

Copy the code

UNION usage rules

To summarize the rules for using UNION:

  1. A UNION must consist of two or more SELECT statements; Statements are separated by the UNION keyword
  2. Each query in the UNION must contain the same column, expression, or aggregate function
  3. Column data types must be compatible: the types do not have to be identical
  4. UNION automatically eliminates duplicate rows from the query result set; But if you want to preserve ALL rows, use the UNION ALL implementation

Sort the combined results

The output of the SELECT statement is ordered BY the ORDER BY clause.

-- Aggregate query

SELECT cust_name, cust_contact,cust_email

FROM Customers

WHERE cust_state IN ('IL'.'IN'.'MI')

UNION    Key words -

SELECT cust_name, cust_contact,cust_email

FROM Customers

WHERE cust_name = 'Fun4ALL'

ORDER BY cust_name, cust_contact;   -- Sort after combination

Copy the code

Insert data

Insert data

INSERT is used to INSERT (or add) rows into a database table in three different ways:

  • Insert the full row
  • Part of insert row
  • Insert the results of some queries

Here is a practical example:

1. Insert the full row

INSERT INTO Customers

VALUES('1000000006'.

       'Tony'.

       '123 Any Street'.

       'New York'.

       'NY'.

       '1111'.

       'USA'.

       NULL.

       NULL   

)

Copy the code

Insert the above data into the Customers table, with a value for each column. If the value does not exist, NULL is used instead. The order of simultaneous inserts must be the same as defined in the table.

Safe: List each field name

INSERT INTO Customers(cust_id,   -- List column names explicitly

                      cust_name,

                      cust_address,

                      cust_city,

                      cust_state,

                      cust_zip,

                      cust_country,

                      cust_contact,

                      cust_email               

)

VALUES('1000000006'.-- corresponds to the column names above

       'Tony'.

       '123 Any Street'.

       'New York'.

       'NY'.

       '1111'.

       'USA'.

       NULL.

       NULL   

)

Copy the code

The above column names and the following inserted data must correspond one by one, we change the insertion order:

INSERT INTO Customers(cust_id,   -- List column names explicitly

                      cust_zip,

                      cust_country,

                      cust_contact,

                      cust_email,

                      cust_name,

                      cust_address,

                      cust_city,

                      cust_state                                   

)

VALUES('1000000006'.-- corresponds to the column names above

       '1111'.

       'USA'.

        NULL.

        NULL.

       'Tony'.

       '123 Any Street'.

       'New York'.

       'NY'

)

Copy the code

2. Insert some data

In the above example, we insert all column names. Now we specify partial column names to insert:

INSERT INTO Customers(cust_id,   -- List column names explicitly

                      cust_zip,

                      cust_country,

                      cust_name,

                      cust_address,

                      cust_city,

                      cust_state                                   

)

VALUES('1000000006'.-- corresponds to the column names above

       '1111'.

       'USA'.

       'Tony'.

       '123 Any Street'.

       'New York'.

       'NY'

)

Copy the code

Insert the retrieved data

Another use of INSERT is to INSERT the results retrieved from the SELECT into a table, using the INSERT SELECT statement

INSERT INTO Customers(cust_id,   Insert the result of the SELECT query

                      cust_zip,

                      cust_country,

                      cust_contact,

                      cust_email,

                      cust_name,

                      cust_address,

                      cust_city,

                      cust_state                                   

)

SELECT cust_id,   SELECT * from (SELECT * from)

      cust_zip,

      cust_country,

     cust_contact,

       cust_email,

       cust_name,

       cust_address,

       cust_city,

       cust_state   

FROM CustNew;

Copy the code

The INSERT SELECT statement can also contain a WHERE clause to filter inserted data.

Copy from one table to another

Another way to INSERT data that does not require an INSERT statement is to copy the contents of one table to another, using the SELECT INSERT statement

SELECT * 

INTO CustCopy

FROM Customers;

Copy the code

Four points to note:

  1. Any SELECT options and clauses can be used during replication, including WHERE and GROUP BY clauses
  2. You can use joins to insert data from multiple tables
  3. No matter how many tables you retrieve data from, the data will eventually be inserted into only one table
  4. INSERT INTO; SELECT INSERT exports data

Update and delete data

Update the data

To update (modify) data in a table, you can use the UPDATE statement. There are two common update methods:

  • Updates a specific row in a table
  • Update all rows in the table

The three components of an UPDATE statement are:

  1. The table to update
  2. Column names and their new values
  3. Determine which rows filter criteria to update
UPDATE Customers  -- 1. Table to be updated

SET cust_email = '[email protected]'  Need a finer column name and its new value

WHERE cust_id = '10000000005';  -- 3. Filtration conditions

Copy the code

Update multiple values simultaneously:

UPDATE Customers  -- 1. Table to be updated

SET   Update multiple values simultaneously

 cust_email = '[email protected]'.

 cust_contact = 'Sam Roberts'

WHERE cust_id = '10000000005';  -- 3. Filtration conditions

Copy the code

When updating the values of multiple columns, simply use the submit SET command, with each column = value pair separated by a comma and the last column different.

If you want to delete a column value, you can set it to NULL (if the table definition allows NULL values).

  • Empty string with' 'Represents, is a value
  • NULL has no value
UPDATE Customers

SET cust_email = NULL

WHERE cust_id = '100000000005';

Copy the code

Delete the data

DELETE data from a table using the DELETE statement. There are two ways to delete:

  • Deletes a specific row from a table
  • Deletes all rows from the table
DELETE FROM Customers

WHERE cust_id = '011111111116';

Copy the code

DELETE deletes an entire row rather than a column. To delete a column, use the UPDATE statement

Update and delete guidelines

  • Be sure to include the WHERE clause, otherwise all data will be modified; Unless we really need to update all records (rare)
  • To ensure that each table has a primary key, you can specify individual primary keys, multiple values, or a range of values
  • Before an UPDATE or DELETE statement uses a WHERE statement, test with SELECT to ensure that it filters out the correct records