Basic SQL Learning Tutorial

Tutorial Study Location: http://www.w3school.com.cn/sql/

SQL is a standard, a technical solution, so that data storage, addition, query and other operations and users separated, so that users do not need to focus on implementation details and can be widely used in various versions of the RDBMS (similar to encapsulation).

RDBMS refers to relational database management systems (relation data base managing system). Data in an RDBMS is stored in a database object called a table (tables). A table is a collection of related data items, consisting of columns and rows.

A database typically contains one or more tables, each identified by a name, and the table contains records (rows) with data.

The following example is a table named "Persons":

City

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

It is important to note that SQL is not case sensitive.

SQL can be divided into two parts, one is data manipulation language (DML) and the other is Data definition language (DDL)

DML: querying and updating work form the main part of DML

• SELECT--Get data from a database table

• Update-Updates data in a database table

• Delete--delete data from the database table

• INSERT INTO--inserts data into a database table

DLL: data Definition Language (DDL) is our ability to create or delete tables. We can also define indexes (keys), specify links between tables, and impose constraints between tables.

• Create database--Creating a new databases

• ALTER DATABASE--Modify the databases
  

• CREATE table--Creating a new data table
  

• ALTER TABLE--Modify data table
  

• DROP table--delete data table
  

• Create index--Creates a data index (key)

• Drop INDEX--delete data index (key)

Select and select * Statements

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

• SELECT column name from table name

• SELECT * FROM table name

• SELECT column 1, column 2 from table name

DISTINCT

Keyword DISTINCT is used to return only different values.

• SELECT DISTINCT column name from table name

WHERE clause

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

• SELECT column name from table name WHERE column operator value

The operator contains the following: = <> > < >= <= between like

Example: SELECT * from Persons WHERE city= ' Beijing '

Note: Use single quotation marks to wrap around text values, and if they are numeric values, you do not need to use quotation marks.

And and OR operators

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

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

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

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

• SELECT * from Persons WHERE (firstname= ' Thomas ' OR firstname= ' William ') and lastname= ' Carter '

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.

• SELECT Company, OrderNumber from Orders ORDER by company

• SELECT Company, OrderNumber from Orders ORDER by company, OrderNumber

• SELECT Company, OrderNumber from Orders ORDER by company DESC

• SELECT Company, OrderNumber from Orders ORDER by company DESC, OrderNumber Desc

INSERT into statement

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

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

INSERT into table name (column 1, column 2,...) Values (value 1, value 2,...)

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

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

UPDATE statement

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

UPDATE table name SET column name = new value where column name = value

• UPDATE Persons SET FirstName = ' Fred ' WHERE LastName = ' Wilson '

• UPDATE Persons SET Address = ' Zhongshan ', city = ' nanjing ' WHERE LastName = ' Wilson '

DELETE statement

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

DELETE from table name WHERE column name = value

• DELETE from Persons WHERE LastName = ' Wilson '

• DELETE from Persons

• DELETE * from Persons
  

Basic SQL Learning Tutorial