**sql server**
選擇：
select * from table1 where 范圍
插入：
insert into table1(field1,field2) values(value1,value2)
洗掉：
delete from table1 where 范圍
更新：
update table1 set field1=value1,field2=value2 where 范圍
查找：
select * from table1 where field1 like ’%value1%’ ---like的語法很精妙，查資料!
排序：
select * from table1 order by field1,field2 [desc]
總數：
select count as totalcount from table1
求和：
select sum(field1) as sumvalue from table1
平均：
select avg(field1) as avgvalue from table1
最大：
select max(field1) as maxvalue from table1
最小：
select min(field1) as minvalue from table1
模糊查詢：
select * from table1 where field1 like '%武漢%'
string Create = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");//創建時間
分頁：
public ActionResult a (int page)
{
string sql = " select top {0} * from(select row_number() over(order by m_id) as rownumber, * from [sys_Menu] where m_name like '%{1}%')temp_row where rownumber > {2}";
List<sysMenu> AllData = https://www.cnblogs.com/jsll/p/DBManage.GetListBySql("select * from [sys_Menu]");
List data = DBManage.GetListBySql(string.Format(sql, 20,"", page));
var total = AllData.Count;
var count = data.Count;
var JsonData = new { total, count, data };
return Json(JsonData);
}
**Oracle**
ASP.NET web.config 中<appSettings>的設定Oracle連線tns
<add key="OCDT" value=https://www.cnblogs.com/jsll/p/"Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = ***)(PORT = 1521)) ) (CONNECT_DATA= (SID = ***)));User Id=****;Password=****;" />
1.查詢:
select * FROM userview where 1=1 and USER_KEY in (190,186)
2.洗掉:
delete from userview where USER_KEY in (190,186)
3.插入資訊:
insert into BIGJJ (age,BIG_NAME) values(26,'第八大街');--插入資訊,此時已實作主鍵自增
Oracle實作自增方式：序列+觸發器
--1.建表BIGJJ
select * from BIGJJ t
--2.建立自增序列
CREATE SEQUENCE BIGJJ_BIGJJ_ID_SEQ -- 建議使用表名+欄位名+seq后綴 insert into BIGJJ ( BIGJJ_ID ,FUCKK_NAME) values( BIGJJ_BIGJJ_ID_SEQ.Nextval ,'張三')==>單獨使用序列也可以實作自增
INCREMENT BY 1 -- 每次加幾個
START WITH 1 -- 從1開始計數
NOMAXVALUE -- 不設定最大值
NOCYCLE -- 一直累加，不回圈
NOCACHE -- 不建緩沖區
--3.建立觸發器
CREATE TRIGGER BIGJJ_TRI
BEFORE INSERT ON BIGJJ FOR EACH ROW WHEN (NEW.BIGJJ_ID IS NULL)
BEGIN
SELECT BIGJJ_BIGJJ_ID_SEQ.NEXTVAL INTO:NEW.BIGJJ_ID FROM DUAL;
END;
--4.測驗
insert into BIGJJ(BIG_NAME) values('第八大街');--插入資訊,此時已實作主鍵自增
-------------------------------------------
--一、建表
--二、建立自增序列
CREATE SEQUENCE 序列名 -- 建議使用表名+欄位名+seq后綴
INCREMENT BY 1 -- 每次加幾個
START WITH 1 -- 從1開始計數
NOMAXVALUE -- 不設定最大值
NOCYCLE -- 一直累加，不回圈
NOCACHE -- 不建緩沖區
--三、建立觸發器
CREATE TRIGGER 觸發器名
BEFORE INSERT ON 表名 FOR EACH ROW WHEN (NEW.主鍵名 IS NULL)
BEGIN
SELECT 序列名.NEXTVAL INTO:NEW.主鍵名 FROM DUAL;
END;
---------------------------------------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36968.html
標籤：SQL Server
上一篇：SQL Server如何找出一個表包含的頁資訊（Page)
下一篇：SQLServer之服務器連接