Basic commands for SQL and summary of several common functions

Function
--Create objects (tables, views, stored procedures, functions) commands
CREATE table/view/procedure/function
--Create a table
CREATE TABLE TABTESTVB
(Vbname varchar (), value numeric (10))
Go
CREATE TABLE tabTestVB1
(Vbname varchar (), value1 Numeric (10))
Go
--Inserting data (two ways)
INSERT into TABTESTVB (vbname,value)
SELECT ' AAA ', 123
INSERT into TabTestVB1 (vbname,value1)
SELECT ' AAA ', 456

INSERT into TABTESTVB (vbname,value) VALUES (' BBB ', 345)
INSERT into TabTestVB1 (vbname,value1) VALUES (' CCC ', 1002)
--Change data
UPDATE tabtestvb SET value=798 WHERE vbname= ' AAA '
--Associating changes
UPDATE TABTESTVB SET value=tabtestvb1.value1
From TabTestVB1 WHERE tabtestvb.vbname=tabtestvb1.vbname
--Delete data
DELETE tabtestvb WHERE vbname= ' AAA '
--No log delete data
TRUNCATE TABLE TABTESTVB
--Delete objects (tables, views, stored procedures, functions) commands
DROP table/view/proc/function
--Delete Table
DROP TABLE TABTESTVB
DROP TABLE tabTestVB1
--Assignment command
SET
--Defining variables
DECLARE


--Process Control statement
While ... Break
BEGIN ... End
IF ... ELSE
----1...100 and
DECLARE @NN NUMERIC (3)
DECLARE @SUM NUMERIC (8)
SET @NN =1
SET @SUM =0
While @NN <=100
BEGIN
SET @SUM = @SUM + @NN
SET @NN = @NN +1
End
SELECT @SUM

--Plus condition: Exit loop when @nn=20 (calculate 1 ...) 19 's and)
DECLARE @NN NUMERIC (3)
DECLARE @SUM NUMERIC (8)
SET @NN =1
SET @SUM =0
While @NN <=100
BEGIN
IF @NN <>20
--begin
SET @SUM = @SUM + @NN
--end
ELSE
--begin
Break
--end
SET @NN = @NN +1
End
SELECT @SUM


--Global variables
@ @ROWCOUNT
--Returns the number of rows affected by the previous statement
SELECT ' 1 '
UNION All
SELECT ' 3 '
SELECT @ @ROWCOUNT

@ @ERROR
--Returns the error code for the last Transact-SQL statement executed.
SET @n =1
SELECT @ @ERROR

Use of----functions
--Returns the current date
SELECT GETDATE ()

--Generates a 16-uniqueidentifier flag column
SELECT NEWID ()

--Convert data types and formats
SELECT CONVERT (VARCHAR), GETDATE (), 120)