当前位置：首页 > news >正文

sql server笔记

news 2025/9/13 18:55:14

创建数据库

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