SQL for beginner

http://www.codeceo.com/article/15-basic-sql-for-beginner.html

 

创建表和数据插入

create table,

insert into ... values()

简单select查询

select

合计与标量

count, avg, max, min, sum

ucase, lcase, substring, len, round

 

select * from ItemMasters

-- Aggregate

-- COUNT() -> returns the Total no of records from table , AVG() returns the Average Value from Colum,MAX() Returns MaX Value from Column

-- ,MIN() returns Min Value from Column,SUM()  sum of total from Column

Select Count(*)  TotalRows,AVG(Price) AVGPrice

,MAX(Price) MAXPrice,MIN(Price) MinPrice,Sum(price) PriceTotal 

FROM ItemMasters

 

-- Scalar 

-- UCASE() -> Convert to  Upper Case  ,LCASE() -> Convert to Lower Case,

-- SUBSTRING() ->Display selected char from column ->SUBSTRING(ColumnName,StartIndex,LenthofChartoDisplay)

--,LEN() -> lenth of column date,

-- ROUND()  -> Which will round the value

SELECT  UPPER(Item_NAME) Uppers,LOWER(Item_NAME) Lowers,

SUBSTRING(Item_NAME,2,3) MidValue,LEN(Item_NAME) Lenths 

  ,SUBSTRING(Item_NAME,2,LEN(Item_NAME)) MidValuewithLenFunction, 

   ROUND(Price,0) as Rounded

FROM ItemMasters

 

日期函数

其它 select函数

top, order by, distinct, 

Where子句

where in, where between, 

Group by 子句

 having

子查询

where

连接查询

join

Inner Join,Left Outer Join,Right Outer Join and Full outer Join

union合并查询

公用表表达式(with)

视图

pivot行转列

存储过程

函数function