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 RecordDatas201609
		where VarName='epi' and CONVERT(char(8),Time,112)=20160903
		group 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_qyb
union all
select 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

六、数据类型

查看SqlServer某张表的物理空间占用情况
包括
[ROWS] 内容的行数..
[reserved] 保留的磁盘大小..
[data] 数据占用的磁盘大小..
[index_size] 索引占用的磁盘大小..
[unused] 未使用的磁盘大小

exec sp_spaceused '表名'

1、int            范围：-2,147,483,648 到 2,147,483,647   占空间：4B

     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      占空间：8B

 

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

 

 

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

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

     nvarchar(2)=4B=一个字符     占空间：根据存入的内容长度决定

    含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar

      NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

4、datetime  时间   占空间：8B

     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

select CONVERT(varchar(8),GETDATE(),14) --09:35:08
select CONVERT(varchar(8),GETDATE(),108) --09:35:08
--两个日期之间的时间
select datediff(minute,Convert(datetime,'2017-09-23 01:00:00'),Convert(datetime,'2017-09-23 08:00:00')) --420

八、标量值函数

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、创建外键的方法二：

表，新建外键