SQL Basics (1)

1.SQL Introduction

(1) What is SQL?

• SQL refers to Structured Query language

• SQL gives us the ability to access a database

• SQL is an ANSI standard computer language

(2) What can SQL do?

• SQL database-oriented query execution

• SQL can retrieve data from the database

• SQL can insert new records in the database

• SQL can update data in a database

• SQL to delete records from the database

• SQL to create a new database

• SQL to create a new table in the database

• SQL can create stored procedures in the database

• SQL to create views in the database

• SQL can set permissions for tables, stored procedures, and views

2.SQL syntax

(1) Database tables

A database typically contains one or more tables. Each table is identified by a name (for example, "customer" or "order"). The table contains records (rows) with data.

(2) Important matters

Be sure to remember that SQL is not case sensitive!

(3) The semicolon after the SQL statement?

Some database systems require semicolons to be used at the end of each SQL command.

Semicolons are the standard way to separate each SQL statement in a database system so that more than one statement can be executed in the same request to the server.

If you are using MS Access and SQL Server 2000, you do not have to use semicolons after each SQL statement, although some database software requirements must use semicolons.

(4) SQL DML and DDL

SQL can be divided into two parts: Data manipulation Language (DML) and data definition language (DDL).

SQL (Structured Query language) is the syntax for executing queries. However, the SQL language also contains syntax for updating, inserting, and deleting records.

The query and update Directives form the DML portion of sql:

• SELECT-Get data from a database table

• Update-updating data in a database table

• Delete-Deletes data from the database table

• INSERT into-inserts data into a database table

The Data definition language (DDL) portion of SQL gives us the ability to create or delete tables. We can also define indexes (keys), specify links between tables, and impose constraints between tables.

The most important DDL statement in SQL:

• Create database-Creating new databases

• ALTER DATABASE-Modify databases

• CREATE table-Creates a new table

• ALTER TABLE-Change (change) database table

• drop table-Delete tables

• Create index-Creating indexes (search key)

• Drop INDEX-Delete indexes

3.SQL SELECT Statement

Select statements are used to select data from a table. The result is stored in a result table, called a result set.

(1) syntax

• SELECT column name from table name

• SELECT * FROM table name

Tip: An asterisk (*) is a shortcut to select all columns.

(2) Navigating in the result set (Result-set)

The results obtained by the SQL query program are stored in a result set. Most database software systems allow the use of programming functions to navigate the result set, such as: Move-to-first-record, Get-record-content, Move-to-next-record, and so on.

4.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 .

(1) syntax

• SELECT DISTINCT column name from table name

5.SQL WHERE clause

The WHERE clause is used to specify the criteria for selection.

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

(1) syntax

• SELECT column name from table name WHERE column operator value

(2) operator

The following operators can be used in the WHERE clause:

Operator	Describe
=	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! =.

(3) 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.

Eg:

• Text value:

Correct: SELECT * from Persons wherefirstname= ' Bush '

Error: SELECT * from Persons Wherefirstname=bush

• Numerical:

Correct: SELECT * from Persons whereyear>1965

Error: SELECT * from Persons whereyear> ' 1965 '

6.SQL and & OR operators

The and and or operators are used to filter records based on more than one condition.

(1) and and OR operators

• And and or can combine two or more conditions in a where sub-statement.

• If both the first condition and the second condition are true, the AND operator displays a record.

• If only one of the first and second conditions is true, the OR operator displays a record.

(2) and operator instances

• SELECT * from Persons WHERE firstname= ' Thomas ' andlastname= ' Carter '

(3) An OR operator instance

• SELECT * from Persons WHERE firstname= ' Thomas ' OR lastname= ' Carter '

(4) Combining and and OR operators

We can also combine and and or together (using parentheses to form complex expressions):

• SELECT * from Persons WHERE (firstname= ' Thomas ' orfirstname= ' William ') andlastname= ' Carter '

7.SQL ORDER BY clause

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

(1) 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.

(2) Example

• Show company name in alphabetical order:

SELECT Company, OrderNumber from Ordersorder to company

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

SELECT Company, OrderNumber from Ordersorder by company, OrderNumber

• Show company name in reverse alphabetical order:

SELECT Company, OrderNumber from Orders ORDER by company DES

C

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

SELECT Company, OrderNumber from Ordersorder by company DESC, OrderNumber ASC

8.SQL INSERT into statement

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

(1) syntax

• 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,....)

(2) Example

• INSERT into Persons VALUES (' Gates ', ' Bill ', ' Xuanwumen10 ', ' Beijing ')

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

9.SQL UPDATE Statement

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

(1) syntax

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

(2) Example

• To update a column in a row:

UPDATE person SET FirstName = ' Fred ' wherelastname = ' Wilson '

• To update several columns in a row:

Updateperson SET Address = ' Zhongshan ', city = ' nanjing ' WHERE LastName = ' Wilson '

10.SQL DELETE Statement

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

(1) syntax

• DELETE from table name WHERE column name = value

(2) Example

• Delete a row:

DELETE from person WHERE LastName = ' Wilson '

• Delete all rows:

DELETE from table_name

DELETE * FROM table_name

SQL Basics (1)