数据库基本操作

DBCC DROPCLEANBUFFERS　　--清缓存

查询条件：and的优化级 高于 or。优化将and的括号起来。

执行存储过程：EXEC 名称 'A392771D-05EB-43EF-9A95-B0D50000BFFF';

------------------------------------------------------------------------------------------------------------------------------------------------------------------

数据库删除数据的三种代码方式：

1、删除表结构及所有数据：drop table table_name

2、恢复表格出厂设置，id清空：truncate table table_name

3、删除表数据：delete from table_name

------------------------------------------------------------------------------------------------------------------------------------------------------------------

增：alter table tableName add columnName varchar(30)

修改列类型或长度：alter table tableName alter column columnName varchar(4000)

修改列的名称：EXEC sp_rename 'tableName.column1' , 'column2' (把表名为tableName的column1列名修改为column2)

删除列：alter table tableName drop column columnName

增加外键：alter table tableName  add constraint constraintName 【primary key(ID)】

删除外键：alter table tableName  drop constraint constraintName 【primary key(ID)】

 

增加索引：

 1 if not exists(select * from sysindexes where id=object_id('Game_CoinSetMealLog') 
 2             and name='index_CoinSetMealLog_Parent')
 3 begin
 4     CREATE NONCLUSTERED INDEX index_CoinSetMealLog_Parent
 5     ON Game_CoinSetMealLog ([Parent])
 6     INCLUDE ([CoinSetMeal],[CoinAmount])
 7 end
 8 if not exists(select * from sysindexes where id=object_id('Mall_OrderItem') 
 9             and name='index_OrderItem_GoodsType|IsDelete')
10 begin
11     CREATE NONCLUSTERED INDEX [index_OrderItem_GoodsType|IsDelete]
12     ON [dbo].[Mall_OrderItem] ([GoodsType],[IsDelete])
13     INCLUDE ([ID],[ForOrder],[SellMoney])
14 end
View Code

 

删除两表关联：

delete a
from a
left join b on b.pid= a.id
View Code

更新两表关联：

1 update  a
2 set IsExchangeRate = 1
3 from Mall_ExchangeGoodsPrice a,Mall_GoodBase b
4 where a.Goods = b.ID 
5   and b.GoodsType = 0
6     and a.IsExchangeRate = 0
View Code

 case end用法：

1 select sg.FID,sg.FStagName,
2         case 
3             when sg.FHigh is null then 'None'
4         end 'FHigh',
5         case 
6             when sg.FLower is null then 'None'
7         end 'FLower',sg.FGuidID,s.FName
View Code

 Stuff用法：  STUFF (source_string, start, length, change_string) 指定位置替换字符串

for xml用法:把表格转化成xml，再去做处理。

select * from master..spt_values
select * from master..spt_values for xml raw
select * from master..spt_values for xml auto
select * from master..spt_values for xml path('')
select * from master..spt_values for xml path('row')
View Code

UNPIVOT用法：

WITH t1 AS
(
    select CONVERT(VARCHAR(25),mallConfig) as q1,CONVERT(VARCHAR(25),IsDelete) as q2
    from Mall_ConfigSet 
    where MallConfig = 85
) 
SELECT Amount
FROM t1
UNPIVOT
(
    Amount FOR [Subject] IN (q1,q2)
) AS UnpivotedTable
for xml path('')
View Code

apply用法：apply 允许我们将前面结果集每一行的数据作为参数，传递到后面的表达式，后面的表达式可以是一个表值函数，或者select结果集

select * from table1 cross apply MyFunction(前一个数据作为参数)

将一周的第一天设置为从 1 到 7 之间的一个数字：   SET DATEFIRST 1

 

posted @ 2023-04-23 14:21  ac楚  Views(5)  Comments(0)  Edit  收藏  举报