不正经

这个屌丝很懒，什么也没留下！

热门标签

sql server 基础

作者：不正经 | 2024-04-17 23:38:59

踩

sql server

一.创建库、创建表、注释增删改、字段增删改、常用数据类型

1.创建库


create database JinDong on primary
(
	name='JinDong_data',
	filename='D:\dataDGY\JinDong_data.mdf',
	size=3MB,
	maxSize=256MB,
	fileGrowth=10%
)
log on
(
	name='JinDong_log',
	filename='D:\dataDGY\JinDong_log.ldf',
	size=3MB,
	maxSize=256MB,
	fileGrowth=20%
)

2. 创建表


use jindong
create table product(
	ID nvarchar(50) primary key,
	Pname nvarchar(20),
	Detail nvarchar(max),
	Price decimal(12,4),
	DiscountPrice numeric(12,4),
	Penable bit,
	Istate tinyint,
	CreateDate datetime,
	MidifiedDate timestamp null
)

3.注释增删改

表注释

exec sp_addextendedproperty N'MS_Description','商品表',N'SCHEMA',N'dbo',N'table',N'product',NULL,NULL

exec执行，sp_addextendedproperty增加扩展属性，后面是参数名和参数值

一共四对,第一对注释名和值，第二对架构，第三对表，第四对字段

字段注释

添加字段注释

exec sp_addextendedproperty N'MS_Description','商品表',N'SCHEMA',N'dbo',N'table',N'product',N'column',N'id'

修改字段注释，格式不变，sp_addextendedproperty改成sp_updateextendedproperty

exec sp_updateextendedproperty N'MS_Description','商品表',N'SCHEMA',N'dbo',N'table',N'product',N'column',N'id'

删除字段注释，格式变化，具体自看

exec sp_dropextendedproperty N'MS_Description',N'SCHEMA',N'dbo',N'table',N'product',N'column',N'id'

4.字段增删改


--增加字段名
alter table product add unit nvarchar(10) not null
--修改字段名
exec sp_rename 'product.unit','uuit'
--删除字段名
alter table product drop column unit 
--修改表名
exec sp_rename 'product','productData'

5.常用数据类型

字符型数据类型

char（） 1~8000个字符

固定长度类型。例如，定义数据类型是char(5)，那么该类型可以存储5个字符，即使存入2个字符，剩下3个字符也会用空格补齐

varchar（） 1~8000个字符

可变长度类型。例如，定义数据类型varchar(5)，表示该类型可以存储5个字符，如果存储了2个字符，字符长度就是2而不是5

text 最多可以存储2147483647个字符

nchar（） Unicode字符数据。n值必须在1~4000之间，每一个存储单位占两个字节

nvarchar （） max指最大存储大小为2的31次方-1字节，Unicode字符数据。n值必须在 1~4000之间，每一个存储单位占两个字节

数值型数据类型

int -2^31到2^31-1

smallint -2^15到2^15-1

tinyint 0到255

bigint -2^63到2^63-1

bit 0，1或者NULL

numeric -10^38+1~10^38-1

dccimal -10^38+1~10^38-1

日期/时间型数据类型

date 公元元年1月1日到公元9999年12月31日，精确到一天

time 00：00：00.0000000到23：59：59.99999999，精确到100纳秒

datetime 公元元年1月1晶到公元9999年12月31日日间范围：00：00：00到23：59： 59.99999999，精确到100纳秒

timestamp 时间戳数据类型，是一个单调上升的计数器，此列的值被自动更新

二、基本增删改查、关联删改查

1.基本增删改查

2.关联删改查


--关联查
select a.id,a.Pname,b.Mid,b.model,b.stock
from product a
join Pmodel b on a.id=b.id
where a.id='A2185FAA-C9F9-4E57-8713-E70781B05570'
 
--关联改
update b set b.stock=b.stock-30
from product a
join Pmodel b on a.id=b.id
where a.id='A2185FAA-C9F9-4E57-8713-E70781B05570'
and b.model='5号'
 
--关联删
delete b 
from product a
join Pmodel b on a.id=b.id
where a.id='A2185FAA-C9F9-4E57-8713-E70781B05570'
and b.model='5号'

三、视图、存储过程、触发器、游标、函数

1.视图


--创建视图
create view ProductAndModel
as
select a.Pname,a.price,b.model,b.stock
from product a
join Pmodel b on a.id=b.id
--查询视图
select * from ProductAndModel

2.存储过程


--创建存储过程
create proc productById( @id nvarchar(50))
as
select a.Pname,a.price,b.model,b.stock
from product a
join Pmodel b on a.id=b.id
where a.id=@id
--执行存储过程
exec productById 'A2185FAA-C9F9-4E57-8713-E70781B05570'

3.触发器


--创建触发器
create trigger dePdePm  on product
after delete
as
delete Pmodel where id=(select id from deleted)
 
--执行触发器
delete product where id='E3B86599-4A35-4AF7-B034-3DF1FE34CCE2'

4.游标


--创建游标
declare PDetail scroll cursor for select * from inventory
--打开游标
open PDetail
--得到第一行数据
fetch first from PDetail
--得到第二行数据
fetch absolute 2 from PDetail
--得到下一行数据
fetch relative 1 from PDetail
--得到上一行数据
fetch prior from PDetail
--关闭游标
close PDetail
--删除游标
deallocate PDetail
 
--得到全部数据
declare @i int,@rows int
select @i=1,@rows=@@cursor_rows
while(@i<=@rows)
begin
fetch absolute @i from PDetail
set @i=@i+1
end

5.函数

表值函数


--创建
create function prduct()
returns table
as 
return
(
select * from inventory
);
--执行，返回的是表
select * from prduct()

标量值函数


--统计一共有多少中商品
create function [dbo].[countProduct]()
returns int
begin
	declare @i int;
	set @i=(select count(*) from inventory where Istate=0)
	return @i;
end
--函数执行
select dbo.countProduct()

四、临时表、表复制

1.临时表,表复制


--全部复制
select a.* into #mode from dbo.inventorySubModel a where 1=1
--只复制字段
select a.* into ##mode from dbo.inventorySubModel a where 1=2
--复制内容
insert into ##mode select * from dbo.inventorySubModel
--创建临时表
create table ##model(
	id nvarchar(50) primary key
)

五、while、if、case when语句

1.while语句


declare @i int
set @i=1
while(@i<=10)
begin
print @i
set @i=@i+1
end

2.if语句


declare @i int
set @i=1
if @i>0
	begin
	print 'yes'
	end
else
	begin
	print 'no'
	end

3.case when语句


declare @i int
set @i=1
print case @i
	when '1' then  'one'
	when '2' then  'two'
	else  '其他'
end

六、set和select变量赋值的区别

1、SELECT可以在一条语句里对多个变量同时赋值,而SET只能一次对一个变量赋值

2、表达式返回多个值时，用SET将会出错，而SELECT将取最后一个值

3、表达式无返回值时，用SET将置变量值为NULL,用SELECT变量将保持原值

4、使用标量子查询时，如果无返回值，SET和SELECT一样，都将置为NULL

七、一些函数的使用

1.output


--output，有返回值的存储过程
alter proc  getZF(@P_id nvarchar(50),@P_semester int,@P_zf int output)
as
begin
	select @P_zf=sum(b.score)
	from student a
	join grading b on a.id=b.id
	where 1=1
	and b.semester=@P_semester
	and a.id=@P_id
end
--也可以被用作修改数据后插入其他表
update a set a.name='mary' output inserted.name into test_back from test a
where id=1

2.is null

作用：判断是否为空


declare @t_a nvarchar(50)
set @t_a='1'
if @t_a is null
print 0
if @t_a is not null
print 1

3.len

作用：计算字符串的长度


declare @t_a nvarchar(50)
set @t_a='hhlj/j/g'
print len(@t_a)

4.right left

作用：截取字符串，向左或向右


print right('www.4399.com',3)
print left('www.4399.com',3)

5.replace

作用：替换字符串

print replace('终 于到五一了','于','')

6.stuff

作用：将字符串插入到另一个字符串中。它从第一个字符串的开始位置删除指定长度的字符；然后将第二个字符串插入到第一个字符串的开始位置

print stuff('终 于到五一了',2,1,'')

7.rtrim ltrim

作用：消除空格


print rtrim('sdf  ')
print ltrim('  sdf')

8.convert

作用：格式转换，比cast好用


print convert(nvarchar(50),convert(int,'1234')+2345)+'abc'
print convert(nvarchar(19),getdate(),20)

9.charIndex

作用：确定字符串再另一个字符串的第几位

print charIndex('h','oasdh')

10.reverse

作用：将字符串反转

print reverse('abcd')

11.substring

作用：截取字符串

print substring('终 于到五一了',1,1)+substring('终 于到五一了',3,7)

12.replicate

作用：将内容重复设定的次数

print replicate('abc',6)

13.<>

作用：做判断，含义为不等于


declare @t_a int
set @t_a=0
if @t_a<>0
print 'abc'
if @t_a<>1
print 'def'

14.union all

作用：将多个结果集合并为一个


select * from xsdd
union all
select 'asldjasl','PC123431','2023-04-28 09:42:41.650','预付','2500.0000','10200.00','......'

15.case when 和行转列


--行转列,第一种
select a.cname,
	convert(nvarchar(7),b.createdate,20) yearMonth,
	sum(case when b.accessrecord=0 then b.amountrecord else 0 end) 'out',
	sum(case when b.accessrecord=1 then b.amountrecord else 0 end) 'in',
	(sum(case when b.accessrecord=0 then b.amountrecord else 0 end)-sum(case when b.accessrecord=1 then b.amountrecord else 0 end)) balance
from costom a 
join balanceaccessrecord b on a.id=b.id
where year(b.createdate)=2022
group by convert(nvarchar(7),b.createdate,20),a.cname
 
--行转列,第二种
with t as
(
select cname,createdate,[0] as 'Pout',[1] as 'Pin' from
(
select a.cname,b.accessrecord,b.amountrecord,b.createdate from costom a 
join balanceaccessrecord b on a.id=b.id
) p
pivot(sum(amountrecord) for accessrecord in ([0],[1])) piv
)
select cname,convert(nvarchar(7),createdate,20) as yearMonth,sum(Pout) Pout,sum(Pin) Pin from t where 1=1
and year(createdate)=2022
and cname='李四'
group by cname,convert(nvarchar(7),createdate,20)
 
select cname,convert(nvarchar(7),createdate,20) as yearMonth,sum(Pout) Pout,sum(Pin) Pin 
from 
	(
		select cname,createdate,[0] as 'Pout',[1] as 'Pin' from
		(
			select a.cname,b.accessrecord,b.amountrecord,b.createdate from costom a 
			join balanceaccessrecord b on a.id=b.id
		) p
		pivot(sum(amountrecord) for accessrecord in ([0],[1])) piv
	) piv
where 1=1
and year(createdate)=2022
and cname='李四'
group by cname,convert(nvarchar(7),createdate,20)
 
--行转列,第三种字符串拼接
--与上不同
declare @sql_str nvarchar(1000)
declare @sql_col nvarchar(100)
--取列中不同的数据当做列
select @sql_col=isNUll(@sql_col+',','')+quotename([model]) from inventorysubmodel order by model
--拼进去
set @sql_str='
select * from
(
	select a.iname,b.model,b.stock from  inventory a join inventorysubmodel b on a.id=b.id
) 
p pivot(sum(stock) for model in ('+@sql_col+')) as piv
ORDER BY piv.iname'
exec (@sql_str)

16.常用的时间函数


YEAR(GetDate())    --年
MONTH(GetDate())    --月
DAY(GetDate())    --日

获取当前系统时间

GetDate()

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/442885