SQL Quick Start Basics

SQL SELECT Statement

To get the contents of a column named "LastName" and "FirstName" (from a database table named "Persons"), use a SELECT statement like this:

SELECT Lastname,firstname from Persons

"Persons" table: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Results:

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

SQL SELECT DISTINCT Statement

In the table, duplicate values may be included. That's not a problem, but sometimes you might want to just list different values (distinct).

Keyword DISTINCT is used to return only different values.

Grammar:

SELECT DISTINCT column name from table name

Using DISTINCT Keywords

If you want to pick all the values from the company column, we need to use the SELECT statement:

SELECT Company from Orders

"Orders" table: Company

	OrderNumber
Ibm	3532
W3school	2356
Apple	4698
W3school	6953

Results:

Company
Ibm
W3school
Apple
W3school

Note that in the result set, W3school is listed two times.

To select only a different value from the company column, we need to use the Select DISTINCT statement:

DISTINCT

Results:

Company
Ibm
W3school
Apple

WHERE clause

To conditionally select data from a table, you can add a WHERE clause to the SELECT statement.

Grammar

SELECT column name from table name WHERE column operator value

The following operators can be used in the WHERE clause:

operator	Description
=	Equals
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
Between	Within a range
Like	Search for a pattern

Note: In some versions of SQL, the operator <> can be written as! =.

Using the WHERE clause

If you want to select only people who live in the city "Beijing", we need to add a WHERE clause to the SELECT statement:

WHERE City=‘Beijing‘

"Persons" table City Year

LastName	FirstName	Address
Adams	John	Oxford Street	London	1970
Bush	George	Fifth Avenue	New York	1975
Carter	Thomas	Changan Street	Beijing	1980
Gates	Bill	Xuanwumen 10	Beijing	1985

Results: City Year

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing	1980
Gates	Bill	Xuanwumen 10	Beijing	1985

Use of quotation marks

Note that we use single quotes around the condition values in the example.

SQL uses single quotation marks to wrap text values (most database systems also accept double quotes). If it is a numeric value, do not use quotation marks.

The order BY statement is used to sort the result set.

ORDER by statement

The order BY statement is used to sort the result set based on the specified column.

The order BY statement sorts records by default in ascending order.

If you want to sort records in descending order, you can use the DESC keyword.

The original table (used in the example):

Orders table:

Company

	OrderNumber
Ibm	3532
W3school	2356
Apple	4698
W3school	6953

Example 1

Show company name in alphabetical order:

ORDER BY Company

Results: Company

	OrderNumber
Apple	4698
Ibm	3532
W3school	6953
W3school	2356

Example 2

Displays the company name in alphabetical order and displays the order number (OrderNumber) in numerical order:

ORDER BY Company, OrderNumber

Results:

Company

	OrderNumber
Apple	4698
Ibm	3532
W3school	2356
W3school	6953

Example 3

Show company name in reverse alphabetical order:

ORDER BY Company DESC

Results: Company

	OrderNumber
W3school	6953
W3school	2356
Ibm	3532
Apple	4698

Example 4

Displays the company name in reverse alphabetical order and displays the sequential number in numerical order:

ORDER BY Company DESC, OrderNumber ASC

Results: Company

	OrderNumber
W3school	2356
W3school	6953
Ibm	3532
Apple	4698

Note: There are two equal company names (W3school) in the results above. Only this time, when there is the same value in the first column, the second column is in ascending order. This is the case if some of the values in the first column are nulls.

INSERT into statement

The INSERT INTO statement is used to insert a new row into the table.

Grammar

INSERT into table name values (value 1, value 2,....)

We can also specify the columns for which you want to insert data:

INSERT into table_name (column 1, column 2,...) Values (value 1, value 2,....)

Insert a new line "Persons" table: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing

SQL statements:

INSERT into Persons VALUES (' Gates ', ' Bill ', ' xuanwumen ', ' Beijing ')

Results: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing

Insert the data "Persons" table in the specified column: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing

SQL statements:

INSERT into Persons (LastName, Address) VALUES (' Wilson ', ' champs-elysees ')

Results: City

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing
Wilson		Champs-elysees

Update statement

The Update statement is used to modify the data in the table.

Grammar:

UPDATE table name SET column name = new value WHERE Column name = value

Person: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson		Champs-elysees

To update a column in a row

We add FirstName for LastName who is "Wilson":

Results: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Champs-elysees

To update several columns in a row

We will modify the address and add the city name:

UPDATE person SET Address = ' Zhongshan ", city = ' nanjing ' WHERE LastName = ' Wilson '

Results: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Zhongshan 23	Nanjing

DELETE statement

The DELETE statement is used to delete rows in a table.

Grammar

DELETE from table name WHERE column name = value

Person: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Zhongshan 23	Nanjing

Delete a row

"Fred Wilson" will be removed:

Results: City

LastName	FirstName	Address
Gates	Bill	Xuanwumen 10	Beijing

Delete all rows

You can delete all rows without deleting the table. This means that the structure, properties, and indexes of the table are complete:

DELETE from table_name

Or:

DELETE * FROM table_name

SQL Quick Start Basics