sql server笔记

创建数据库

use master
gocreate database stuuuuu//删除数据库if db_id ('$$$') is not nullDrop database [$$$]
go//新建表USE [studyTest]
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Table_1]([id] [int] NULL,[name] [varchar](10) NULL
) ON [PRIMARY]
GO//删除表
USE [studyTest]
GOIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
DROP TABLE [dbo].[Table_1]
GO

插入  insert into aaa ( name ) values ('asd')

/* 更新*/
update aaa set name='我是' where id=1

/* 删除*/
delete aaa where id =1
go

/*条件查询*/
select id as '标识',
        name as '姓名',
        score as 分数
        from aaa where score >20 and score <90 
        /* score >20 and score <90 可以替换成between 50 and 90
            不为空条件  where score is not null*/

/*百分比查询数据*/
select top(50) percent id as '标识',
        name as '姓名',
        score as 分数
        from aaa where score >20 and score <90         

caseWhen判断

select name,score ,case when score >20 and    score<50 then '不及格' 
                        when   score>60 then '好' 
                        else '零'
                        end as '等级'
                    from aaa order by score asc

// in
select name,score from aaa where id in (2)
// like
select name,score from aaa where name like 'a%'

// with
with tt as (select name,score from aaa where name like 'a%')
select * from tt
//distinct
select distinct id from aaa
//order by 升序 asc 降序 desc
select *from aaa order by score asc

//聚合group by
select  name,sum(score) scoresum from aaa group by name

//左连接   left inner right  full join
select *from Company c left join SysUser s on c.Id=s.CompanyId and .....

// union unionall 把结构相同的表合并,union可以去重, unionall 不会去重
 

//递归查询

with  con(id,meanname,parentid,le) as 

 (

               select id,meanname,parentid,le from meaninfo  where id=1

                union all

                select a.id .... , le = le+1 from meaninfo a join con a.parent = con.id

)

 select  id,meanname,parentid,le  from con

数据类型 

date 年月日3byte  datetime 年月日时分秒8byte time 时分秒5byte

约束 

主键约束，外键约束 ，非空约束，唯一约束，检查约束

存储过程