C# tutorial-Database support: SQL

SQL (Structured Query Language)

To be able to work with data of the database, it is important to gain basic knowledge of SQL language. In this part, we introduce you the basic sql statements commonly used. In this tutorial, we use Tablename to refer to the table and fieldname to refer to the field of the table that we will work with.

*Retrieve data from a table

SELECT * FROM Tablename;

Example:

SELECT * FROM Products;

*Retrieve data from a table with sorting

SELECT * FROM Tablename

Order by fieldname Asc/Desc;

Example:

SELECT * FROM Products

Order By ProductID DESC;

*Retrieve data from a table with condition

SELECT * FROM Tablename

Where condition;

Example:

SELECT * FROM Products

Where UnitPrice Between 20 And 30;

SELECT * FROM Products

Where UnitPrice>20 And UnitsOnOrder>20;

*Retrieve data from multiple tables

SELECT Tablename1.fieldname1, Tablename1.fieldname2, Tablename2.fieldname1,

Tablename2.fieldname2

FROM Tablename1, Tablename2

Where how these tables are related

Example:

SELECT Products.ProductName,Products.UnitPrice,Products.UnitsInStock, Categories.CategoryName

FROM Products,Categories

Where Products.CategoryID=Categories.CategoryID;

* Insert data into a table

INSERT INTO Tablename Values(val1,val2,value3..)

Example:

INSERT INTO Student_tbl Values('Stu006','Rithy Vansak');

* Insert data into a table by specifying field names

INSERT INTO Tablename(fieldname1, fieldname 2,..)

Values(val1,val2,value3..)

Example:

INSERT INTO Student_tbl(StuID,StuName) Values('Stu007','Rithy Vansak');

* Update data in a table

UPDATE TableName

SET fieldname 1=val1, fieldname 2=val2,f2…

Where condition

Example:

UPDATE Student_tbl

Set StuName='Rithy Chan'

Where StudID='Stu007';

* Delete data from a table

-DELETE FROM Tblname Where condition;

Example:

DELETE FROM Student WHERE StuID='Stu007'

Note: For DELETE and UPDATE statements, you should set conditions(s). If you don’t set condition(s), all of your data in a specified table will be deleted or updated.