20130318随笔 sql基础

• 数据库设计三范式

1. 确保表中的所有字段都是有意义的，不重复，不可分割的
2. 确保表中每个字段均与主键相关,通过主键可以查到表中所有信息
3. 确保每列与主键直接相关，而非间接相关

• T-SQL基础(Transact Structured Query language)

  • 语句类型

  1. 数据定义语言DDL(Data Definition Language) :Create Alter Drop

  2. 数据处理语言DML(Data Manipulation Language):Select Delete Insert Update Merge

  3. 数据控制类型DCL(Data Control Language):Grant Revoke

  • 查询语句

1. 处理顺序：from-->where-->group by-->having-->select(over-->distinct-->top)-->order by　

2. top 子句：select top 5 with ties * from Employees order by emp_id asc (with ties 后面必须跟order by,筛选与最后一行相同的更多行)

3. over子句:

   

   --1 查询所有行
   SELECT  orderid ,custid ,val
   FROM    Sales.OrderValues
   ORDER BY custid ASC
   --2 所有价格的总和
   SELECT  orderid ,custid ,val ,
   SUM(val) OVER ( ) AS totalValue
   FROM    Sales.OrderValues
   ORDER BY    custid ASC
   --3 partition by 限制或分区 查询当前客户的总价格 根据custid进行分区
   SELECT  orderid ,custid ,val ,
   SUM(val) OVER ( PARTITION BY custid ) AS CurrTotalValue
   FROM    Sales.OrderValues

   
   四种排名函数：ROW_NUMBER()行号,RANK()排名,DENSE_RANK()密集排名, NTILE(5) 对数据手动分组，分为5组，除不尽补前面
   order by 排序 ,partition by 分区

4. 运算符:(and优先级比or高)

   

   SQL运算符的优先级 
   1.()
   2.*,/,%
   3.+,-
   4.=,>,<,>=,<=,<>,!=,!>,!<
   5.not
   6.and
   7.between，in，like，or
   8.=

   

5. case语句:  case when...then...when...then...else...end

   

   SELECT  orderid ,custid ,val ,
           CASE WHEN val < 1000.00 THEN ‘Less then 1000‘
                WHEN val BETWEEN 1000.00 AND 3000.00 THEN ‘Between 1000 and 300‘
                WHEN val > 3000.00 THEN ‘More then 3000‘
                ELSE ‘Unkown‘
           END AS ‘ValueCategory‘
   FROM    Sales.OrderValues

   

6. NULL值 表达式 i>0 , when i =1 ,结果为true,when i = -1 结果false,when i = null 结果 unknown ..当进行分组和排序时两个null值认为相等 T-sql把null值排在有效值之前

7. 同时操作：(All-at-Once-Operation)在同一逻辑查询处理阶段中所有表达式同时进行运算

8. 数据类型:普通字符：char varchar

   　　　　　　　　Unicode字符 Nchar Nvarchar 

   　　　　　　　　其中普通字符用一个字节来保存，而Unicode字符需要两个字节,char和Nchar都是定长的

9. 字符串处理函数:
   substring(string,start,length)： start与length均从1开始 。例:print  substring(‘john‘,1,10)--john
   left(string,n)和right(string,n) ：n 从左边或右边提取的字符字数 。 例:print left(‘john‘,4)--john
   len(string)和datalength(string) ：len返回输入字符串的字符数并且不包含尾随空格,datalength返回字符串字节的长度,例:
   

   PRINT LEN(‘jolinson‘)--8    
   PRINT DATALENGTH(‘jolinson‘)--8    
   PRINT LEN(N‘jolinson‘)--8    
   PRINT DATALENGTH(N‘jolinson‘)--16
   
   PRINT LEN(‘ jolinson ‘)--9
   PRINT DATALENGTH(‘ jolinson ‘)--10
   PRINT LEN(N‘ jolinson ‘)--9
   PRINT DATALENGTH(N‘ jolinson ‘)--20

   
   charindex(substring,string[,start_pos])：substring 要搜索的字符,string输入的字符串,开始位置,可空,例: print charindex(‘on‘,‘jolinson‘,6) -- 7

   patindex(pattern,string) 例：PRINT PATINDEX(‘%[0-9]%‘,‘sdfas1321asdf‘) --6

   repalce(string,substr1,substr2) 将string中substr1替换为substr2。例：

   PRINT REPLACE(‘1-2 , 2-3‘,‘-‘,‘:‘) --1:2 , 2:3

   replicate(string,n) 指定的次数复制字符串

   stuff(string,pos,dellength,insertstr)  例:print stuff(‘jolinson‘,1,2,‘A‘)--Alinson

   upper(string)和lower(string)

   rtrim(string)和ltrim(string)

   cast(value as datatype) convert(datatype,value,[style_number]),推荐cast

   DateAdd(part,n,dt_val) 为dt_val增加指定数量的year,month,day,week...等

   datediff(part,dt_val,dt_val2) 返回两个日期相差的year,month,day,week...等

   datepart(part,dt_val)获取指定部分日期的数值，可简写为year,month,day(dt_val)

   isdate(string) 能否转换为日期类型 ?1:0;

10. 查询元数据
    
    

    --数据库中的各个表
    SELECT  SCHEMA_NAME([schema_id]) AS table_schema_name ,
            name AS table_name
    FROM    sys.tables
    

    --某个表的列信息
    

    SELECT  name AS column_name ,--列名
            TYPE_NAME(system_type_id) AS column_type ,--数据类型
            max_length ,--最大长度
            collation_name ,--排序规则
            is_nullable--名称
    FROM    sys.columns
    WHERE   object_id = OBJECT_ID(N‘Sales.orders‘)
    
    

    

     

20130318随笔 sql基础,古老的榕树,5-wow.com