SQLserver 增删改查

小鱼儿 2022-05-15 13:08 263阅读 0赞

\--use Study  
\--go  
\--CREATE TABLE zhangwu  
\--(  
\-- id INT PRIMARY KEY ,  
\-- Name1 VARCHAR(200),  
\-- money float  
\--)  
\--go

\--set IDENTITY\_INSERT zhangwu ON  
\--go

\--use Study  
\--go  
\--insert into zhangwu(id,name1,money)  
\--VALUES(1,'吃饭支出',247)  
\--insert into zhangwu  
\--VALUES (2,'工资收入',12345)  
\--insert into zhangwu  
\--VALUES (3,'服装支出',1000)  
\--insert into zhangwu  
\--VALUES (4,'吃饭支出',325)  
\--insert into zhangwu  
\--VALUES (5,'股票收入',8000)  
\--insert into zhangwu  
\--VALUES (6,'打麻将支出',8000)  
\--insert into zhangwu  
\--VALUES (7,null,5000)  
\--go

\--1.将表明修改为order1  
\--EXEC SP\_RENAME 'zhangwu', 'order1';

\--2.将字段money 更名为money1，设置为非空  
\--EXEC sp\_rename 'order1.\[money\]', 'money1';

\--3.将id为5的账务名称改为游戏支出  
\--update order1 set Name1='游戏支出' where id=5;

\--4.删除账务名称为空的数据  
\--delete from order1 where name1 is null;

\--5.查询id,name1字段信息  
\--select id,name1 from order1;

\--6.查询表中所有字段  
\--select \* from order1;

\--7.将所有账务的金额+10000元进行显示  
\--select name1,money1+10000 from order1

\--8.查询所有吃饭支出记录  
\--select \* from order1 where name1='吃饭支出' ;

\--9.查询出金额大于1000的信息  
\--select \* from order1 where money1>=1000;

\--10.查询出金额在2000-5000之间的账务信息  
\--select \* from order1 where money1>=2000 and money1<=5000;

\--11.查询出金额是1000或5000或3500的商品信息  
\--select \* from order1 where money1=1000 or money1=5000 or money1=3500;

\--12.查询出账务名称包含”支出”的账务信息。  
\--select \* from order1 where name1 like '%支出%'