SQL Server 学习（1）子查询(in,not in)、多表查询、合并表（union、union all）、分组（group by）、分组的条件（having）、虚拟表、标量值函数、外键

基础知识

一、子查询

select * from kb_qyb  where qybh in ('000000000830359','000000000572791')select * from kb_qyb  where qybh in (select qybh from kb_ssmx)select * from kb_qyb  where qybh not in (select qybh from kb_ssmx)

多列合成一列

select EquipmentID, MaxValue,  Time from RecordDatas201609 where(cast(EquipmentID as varchar)+CONVERT(varchar(100), Time, 20)) in(select cast(EquipmentID as varchar)+CONVERT(varchar(100), MIN(Time), 20) Time from RecordDatas201609where VarName='epi' and CONVERT(char(8),Time,112)=20160903group by EquipmentID)

二、多表查询,多表更新

select A.qybh,B.bmbm       from kb_qyb A,ryk B            where A.fYearMonth=201101 and A.ryname=B.ryname--等同于下面查询select A.qybh,B.bmbm        from kb_qyb A join ryk B on A.ryname=B.ryname              where A.fYearMonth=201101

update A set A.jhyear=B.jhmonth_lj         from khrwb1 A,khrwb1_jh B               where A.lb=B.lb and A.khyear=B.khyear

三、union 和 union all      作用：合并表

select qybh,qyname,'字符串'+ryname as lb from kb_qybunion allselect qybh,qyname,'字符串'        as lb from kb_ssmx

union 去掉重复记录

union all 显示两表全部

四、group by:对数据进行分组

      having ： 分组的条件

select bmbm,MAX(bmname),SUM(hj)            from kb_ssmx where fYearMonth='201101'                  group by bmbm having SUM(hj)>1000000

注意：要显示的字段要在group by的分组中，如果不想在group by的分组中,可在字段加MAX

五、into 和 insert into（表要存在）

1、tmp在数据库中不能存在，运行后会在数据库中建tmp表。

           常用于创建表的备份复件或者用于对记录进行存档

select * into tmp from bmk

2、#tmp虚拟表

select * into #tmp          from bmk

3、tmp表在数据库必须存在

insert into tmp(qybh,qyname)            select qybh,qyname from qyb

六、数据类型

1、int            

     范围：-2,147,483,648 到 2,147,483,647

     tinyint       0到255

     bt             0或1

2、money      小数4位(存储-922337203685477.5808~922337203685477.5807的货币值)

     numeric  

     例如：

          numeric(p,s)其中p 是精度,s 是刻度~~
          Numeric(10,2) 指字段是数字型,长度为10 ，小数为两位的
          精度的范围： 28 到 29 位有效位

     float 

 

        注：在金额的存储上可以优先money，如果金额过大采用decimal（numeric），极端的情况（数据超大）使用float（real）

 

 

3、varchar    可变长度

           如：varchar(60) 那表示可以存60个数字，英文或 可以存30个中文

 

     nvarchar  国际标准（数据库之间转换 如：sql-->Orace）

4、datetime  时间

     date         年月日，没有时分秒(2011-01-01)

5、text           大文本

     ntext       

七、常用函数（用在查询的字段处，或where条件处）

STR()              转换成字符串

upper()          转换成大写

Ltrim()           去左空格

Rtrim()           去右空格

substring()     取字符

charindex()    定位

getdate()       当前时间

字段名=isnull(字段名,0)             如果字段为空，则为0

round() 遵循四舍五入把原值转化为指定小数位数，  如：round(1.45,0) = 1;     round(1.55,0)=2
floor()向下舍入为指定小数位数                                   如：floor(1.45,0)= 1;       floor(1.55,0) = 1
ceiling()向上舍入为指定小数位数                               如：ceiling(1.45,0) = 2;    ceiling(1.55,0)=2

select qybh,'-'+STR(hj),'-'+Ltrim(STR(hj)),qyname ,SUBSTRING(qyname,3,6),CHARINDEX('襄',qyname) from kb_qyb

select distinct qybh,qyname from kb_qyb  --仅选取唯一不同的值

时间函数

select stime,       CONVERT(char,stime,112),       YEAR(stime),--取年       MONTH(stime),--取月       DATEPART(MM,stime),--取月       DATEPART(HH,stime),--取小时       DATEADD(MM,2,stime)--当前月加2个月 from lcmlb

八、标量值函数

1、新建标量值函数

ALTER FUNCTION [dbo].[fun_get_qyname](   @qyh varchar(200),   --参数   @qyname varchar(10))RETURNS varchar(10)     --返回类型（只能返回一个）AS                      --开始BEGIN   declare @str varchar(10);  --变量   set @str=left(@qyh,2)+'-'+left(@qyname,7);   return @str;END

2.调用标量值函数

select dbo.fun_get_qyname(qybh,qyname), qybh,qyname from kb_qyb 

本数据库调用  ： dbo.函数名

其它数据库调用： 数据库名.dbo.函数名

九、主键和外键

主键是指表中的某个字段，对应这个字段的列值能唯一的标识一行记录，具有唯一性。
外键是指表中的某个字段，是引用的另一个表中的主键作为自己的一个字段。一个表只能有一个主键，但可以有多个外键。

外键的作用：保证数据的完整性和一致性。如果外键表有数据，主键表就不能删除。

1、创建外键的方法一：

2、创建外键的方法二：

表，新建外键