目录
一.创建库、创建表、注释增删改、字段增删改、常用数据类型
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,NULLexec执行，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 ProductAndModel2.存储过程
--创建存储过程
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
end5.函数
表值函数
--创建
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
end2.if语句
declare @i int
set @i=1
if @i>0
begin
print 'yes'
end
else
begin
print 'no'
end3.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=12.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 13.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()
9149
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
