数据库增删查改
Lio 2020-05-18 学习笔记
# Sql Server 分页查询
# 1、offset/fetch next
SELECT B.* FROM
(SELECT WR.WONO,WR.WORKORDER,WR.COMPONENT,WR.MATERIALCODE,WR.PRODUCT,WR.SPEC,
WR.UNIT,WR.INVENTORYQUANTITY,WR.WAREHOUSEGUID,WR.STORAGEGUID,WR.ORDERNO,
WR.MODIFIEDTIME,SI.Storage AS STORAGENAME,WI.WAREHOUSE AS WAREHOUSENAME
FROM WareHouse_Real WR
LEFT JOIN Storage_Info SI ON SI.Id=WR.STORAGEGUID
LEFT JOIN WareHouse_Info WI ON WI.ID=WR.WAREHOUSEGUID WHERE 1=1) AS B ORDER BY 1 OFFSET 31 ROWNEXT 15 ROWS ONLY;
***************************************参考*****************************************
-- 设置执行时间开始,用来查看性能的
set statistics time on ;
-- 分页查询(通用型)
select *
from (select top pageSize *
from (select top (pageIndex*pageSize) *
from student
order by sNo asc ) -- 其中里面这层,必须指定按照升序排序,省略的话,查询出的结果是错误的。
as temp_sum_student
order by sNo desc ) temp_order
order by sNo asc
-- 分页查询第2页,每页有10条记录
select *
from (select top 10 *
from (select top 20 *
from student
order by sNo asc ) -- 其中里面这层,必须指定按照升序排序,省略的话,查询出的结果是错误的。
as temp_sum_student
order by sNo desc ) temp_order
order by sNo asc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
offset A rows,将前A条记录舍去,fetch next B rows only,向后在读取B条数据
# Oracle 分页查询
DynamicParameters pars = new DynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT * FROM (SELECT ROWNUM RN2,TEMP1.* FROM(select rownum RN1, sl.guid,sl.storagename,sl.storagecode,storagetype,
sl.warehouseguid,decode(sl.status,'0','无效','1','有效','无')Status,
sl.remark,sl.creater,sl.createdate,sl.lastupdby,sl.lastupddate,
whl.warehousename,
whl.warehousecode,
ui1.LoginUser as CreaterName,
ui2.LoginUser as LastUpdByName
from H2_StorageList sl
left join H2_UserInfo ui1 on ui1.UserGuid = sl.Creater
left join H2_UserInfo ui2 on ui2.UserGuid = sl.LastUpdBy
join h2_warehouselist whl on whl.guid = sl.warehouseguid) TEMP1 WHERE 1=1 ");
if (!string.IsNullOrEmpty(qry.StorageName))
{
sql.Append(" AND TEMP1.STORAGENAME=:Storagename");
pars.AddDynamicParams(new { Storagename = qry.StorageName });
}
if (!string.IsNullOrEmpty(qry.WareHouseGuid))
{
sql.Append(" AND TEMP1.WAREHOUSEGUID=:Warehouseguid");
pars.AddDynamicParams(new { Warehouseguid = qry.WareHouseGuid });
}
if (!string.IsNullOrEmpty(qry.Status))
{
sql.Append(" AND TEMP1.STATUS=:Status");
pars.AddDynamicParams(new { Status = qry.Status });
}
sql.Append(" )TEMP2 WHERE TEMP2.RN2 BETWEEN ((:CurPage - 1) * :PageSize + 1) AND (:CurPage * :PageSize)");
pars.AddDynamicParams(new { CurPage = curPager, PageSize = pagerSize });
list = (await conn.QueryAsync<VMStorageListInfo>(sql.ToString(), pars)).ToList();
result.Data = list;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# Oracle查锁表以及解锁
使用如下语句可以查出锁表的具体原因,这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。
SELECT S.SID, S.SERIAL#, S.USERNAME,S.SCHEMANAME,S.OSUSER,S.PROCESS, S.MACHINE,S.TERMINAL,S.LOGON_TIME,L.TYPE FROM V$SESSION S, V$LOCK L WHERE S.SID = L.SIDAND S.USERNAME IS NOT NULL ORDER BY SID;
1
2
3
4
5杀掉进程sid,serial#,这两个内容可以利用"v$locked_obiect"和"v$session"两个数据字典查询得到
ALTER SYSTEM KILL SESSION'210,11562';
1
# Oracle恢复删除的数据
分为两种方法:scn和时间戳两种方式恢复 引用 (opens new window)
# 1、 通过scn恢复
- 获得当前数据库的scn号
select current_scn from v$database; (切换到sys用户或system用户查询) 查询到的scn号为:1499223
1
2
3 - 查询当前scn号之前的scn
select * from 表名 as of scn 1499220; (确定删除的数据是否存在,如果存在,则恢复数据;如果不是,则继续缩小scn号)
1
2
3 - 恢复删除且已提交的数据
flashback table 表名 to scn 1499220;
1
# 2、 通过时间恢复
查询当前系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
1查询删除数据的时间点的数据
select * from 表名 as of timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss');
1恢复删除且已提交的数据
lashback table 表名 to timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss'); 注意:如果在执行上面的语句,出现错误。可以尝试执行 alter table 表名 enable row movement; //允许更改时间戳
1
2
3
# 索引
# 1、创建索引
创建索引
create index 索引名 on 表名(列名);删除索引
drop index 索引名;创建组合索引
create index 索引名 on 表名(列名1,,列名2);
# 2、查看索引
- 在数据库中查找表名
select * from user_tables where table_name like 'tablename%'; - 查看该表的所有索引
select * from all_indexes where table_name = 'tablename'; - 查看该表的所有索引列
select* from all_ind_columns where table_name = 'tablename';
[引用](https://www.cnblogs.com/superming/p/10978639.html)
# 删除所有数据表
方便删除数据库中的所有的数据表,清空数据库,有些有约束,不能直接delete,需要先删除库中的约束,代码如下:
- 删除所有约束
DECLARE c1 cursor for select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; ' from sysobjects where xtype = 'F' open c1 declare @c1 varchar(8000) fetch next from c1 into @c1 while(@@fetch_status=0) begin exec(@c1) fetch next from c1 into @c1 end close c1 deallocate c1
1
2
3
4
5
6
7
8
9
10
11
12
13
14 - 删除数据库所有表
declare @tname varchar(8000) set @tname='' select @tname=@tname + Name + ',' from sysobjects where xtype='U' select @tname='drop table ' + left(@tname,len(@tname)-1) exec(@tname)
1
2
3
4
5
然后清空数据库中的所有表:
如果需要删除存储过程等只需要将上面的做如下修改就行了的where xtype='U' 改成 where xtype='P',drop table 改成 drop Procedure
# Select Into
SELECT INTO 语句用于创建表的备份复件或者用于对记录进行存档。从一个表中选取数据,然后把数插入到另一个表中。
--把所有的列插入新表
SELECT * INTO newtablename FROM oldtablename
--把部分列插入新表
SELECT COLUMN1,COLUMN2 INTO newtablename FROM oldtablename
--IN子句可用于向另一个数据库中拷贝表
SELECT * INTO Persons IN 'Backup.mdb' FROM Persons
--连表取数据
SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# Insert Into
INSERT INTO tr_user (sname, sage) SELECT name AS sname,age AS sage
FROM USER
1
2
2
# 连表更新
update a set a.F_ORA_PICTURE =b.FIMAGEFILESERVER
from t_eng_bomchild a inner join t_bd_material b on a.fmaterialid=b.fmaterialid
and (a.F_ORA_PICTURE='' or a.F_ORA_PICTURE= ' ' or a.F_ORA_PICTURE=null)
and(b.FIMAGEFILESERVER!='' and b.FIMAGEFILESERVER!='' and b.FIMAGEFILESERVER is not null);
1
2
3
4
2
3
4
# Merge Into
MERGE INTO
T_ENG_BOMCHILD T1
USING(SELECT FMATERIALID,FIMAGEFILESERVER FROM T_BD_MATERIAL) T2
ON (T1.FMATERIALID = T2.FMATERIALID AND (T1.F_ORA_PICTURE='' or T1.F_ORA_PICTURE= ' ' or T1.F_ORA_PICTURE=null)
AND (T2.FIMAGEFILESERVER!='' and T2.FIMAGEFILESERVER!='' and T2.FIMAGEFILESERVER is not null))
WHEN MATCHED THEN UPDATE SET T1.F_ORA_PICTURE=T2.FIMAGEFILESERVER;
1
2
3
4
5
6
2
3
4
5
6