noteforW3schoolsSQL

Some of The Most Important SQL Commands

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
note:DROP statement

SQL UPDATE Example

Assume we wish to update the customer “Alfreds Futterkiste” with a new contact person and city.

We use the following SQL statement:

Example

UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';

**
note:
1. I would firstly SELECT object out of table, then update it, and double check it.
2. must follow WHERE clause.
**

SQL DELETE Example

Assume we wish to delete the customer “Alfreds Futterkiste” from the “Customers” table.

We use the following SQL statement:

Example

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

**
1. you can not undo it.
2. can not DELETE a column of a row, but you can update it.
3. must follow WHERE clause if do not want delete all rows.
**

SQL Wildcard Characters

In SQL, wildcard characters are used with the SQL LIKE operator.

SQL wildcards are used to search for data within a table.

With SQL, the wildcards are:

Wildcard / Description
———:/:————–
% / A substitute for zero or more characters
_ / A substitute for a single character
[charlist]/ Sets and ranges of characters to match
[^charlist] or [!charlist]/ Matches only a character NOT specified within the brackets

Alias Example for Tables

The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the “Customers” and “Orders” tables, and give them the table aliases of “c” and “o” respectively (Here we have used aliases to make the SQL shorter):

Example

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

The same SQL statement without aliases:

Example

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;

Different SQL JOINs

Before we continue with examples, we will list the types of the different SQL JOINs you can use:

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION. which is strange(now understand, combine two or more SELECT)

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a new table.

SQL SELECT INTO Syntax
We can copy all columns into the new table:

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Or we can copy only the columns we want into the new table:

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.
test fail on SQL tryit editor v1.5, orrer “could not prepare statement”, with unkown reason

The SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected.

SQL INSERT INTO SELECT Syntax
We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

** this is similar to INSERT INTO statement, others column set null

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

**

SQL CREATE TABLE Example

Now we want to create a table called “Persons” that contains five columns: PersonID, LastName, FirstName, Address, and City.

We use the following CREATE TABLE statement:

Example

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

feel like it is a class in C++