DV-พื้นฐาน SQL Statement

คำสั่งพื้นฐาน
INSERT INTO <table> (field, field,...) VALUES (data, data,...)
เช่น 
INSERT INTO Emp VALUES (001,"Surapun")
INSERT INTO Emp(name) VALUES ("Surapun")

INSERT INTO <table> SELECT <field, field,...> FROM <table> WHERE <condition>
เช่น 
INSERT INTO faculty 
  SELECT facultyID,facultyName FROM faculty_tab 
  WHERE facultyID NOT IN (SELECT facultyID FROM faculty_test)
INSERT INTO faculty  (facultyName)
  SELECT facultyName FROM faculty_tab 
  WHERE facultyID NOT IN (SELECT facultyID FROM faculty_test)


UPDATE <table> SET field=value, field=value WHERE <condition>
เช่น 
UPDATE Emp SET name="Pick" WHERE name="Surapun"

UPDATE <table> SET field=value FROM <table> WHERE <condition>
เช่น 
UPDATE Table1 SET Table1.salary=Table2.salary FROM Table1,Table2 WHERE Table1.Id=Table2.ID

DELETE FROM <table> WHERE <condition>
DELETE <table> WHERE <condition>
เช่น 
DELETE FROM Emp
DELETE Emp WHERE name="Surapun"

SELECT <field> INTO <new table> FROM <table> WHERE <condition>
เช่น
SELECT * INTO tmp_Emp FROM Emp 


ตัวอย่างการใช้ CASE TSQL
SELECT   Pres_name,yrs_serv = 
      CASE yrs_serv
         WHEN '0' THEN 'Zero Year'
         WHEN '1' THEN 'One Year'
         WHEN '2' THEN 'Two Year'
         WHEN '3' THEN 'Tree Year'
         WHEN '4' THEN 'Four Year'
         ELSE 'Five or More Year'
      END
FROM President;

SELECT  'Price Category' = 
      CASE 
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 AND price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price;

Reset Auto Number in SQL Server

DBCC CHECKIDENT (<Table Name>, RESEED, <Value>)

เช่น 

DBCC CHECKIDENT (Products, RESEED, 1) -- เมื่อ Insert จะได้หมายเลข 2

Temp table in SQL Server

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Loop Cursor in SQL Server

DECLARE @SiteNumber INT;

DECLARE @SiteID NVARCHAR(25);

DECLARE db_cursor CURSOR FOR SELECT SiteNumber, SiteID  FROM Sites;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @SiteNumber, @SiteID;

WHILE @@FETCH_STATUS = 0   

BEGIN   

        PRINT CONVERT(NVARCHAR(25), @SiteNumber) + ' ' + @SiteID;

        FETCH NEXT FROM db_cursor INTO @SiteNumber, @SiteID;   

END   

CLOSE db_cursor;

DEALLOCATE db_cursor;

Standard function in SQL Server

LOWER(field)

UPER(field)

LEN(field)

RIGHT(field, num)

LEFT(field, num)

SUBSTRING(field, posit, num)

dateADD()

dateDIFF()

dateNAME()

datePART()

getDATE()

iiF (condition, true_value, false_value)
CONVERT (data_type, field_value) เช่น CONVERT (int, 1234.56) -- 1234

FORMAT (value, format string)

เช่น 

SELECT FORMAT(getDATE(), 'yyyy/MM/dd hh:mm:ss tt') -- 2017/02/28 09:30:59 PM

SELECT FORMAT(123456789, '#,#.00') -- 123,456,789.00

SELECT FORMAT(123456789, '###-##-####') -- 123-45-6789