sql经典基础语句不全
**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");//创建时间
sqlserver分页：
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 = DBManage.GetListBySql<sysMenu>("select * from [sys_Menu]");
List<sysMenu> data = DBManage.GetListBySql<sysMenu>(string.Format(sql, 20,"", page));
var total = AllData.Count;
var count = data.Count;
var JsonData = new { total, count, data };
return Json(JsonData);
}
CTE≈视图、派生表：
***
;WITH myCTE (CustID, Co) AS
(
SELECT CustomerID, CompanyName FROM Customers
)
SELECT CustID, Co FROM myCTE
***
CustomerID 和 CompanyName 列的别名为 CustID 和 Co。接着跟随 CTE 其后的是通过列别名引用 CTE 的 SELECT 语句。
CTE 仅能被紧随其后的语句所引用,跟随其后的首个查询便能多次引用它。
CTE 以 WITH 关键字开始。然而，如果 CTE 不是批处理中的第一个语句，则必须在 WITH 关键字前添加一个分号。作为最佳做法，我倾向于在所有的 CTE 之前都加上一个分号作为前缀，我发现这种一致的方式比起必须牢记是否需要添加分号来，要容易得多。
WITH 关键字后面是 CTE 的名称，接着是一个列别名的可选列表。列别名对应于 CTE 内的 SELECT 语句返回的列。可选列别名的后面是 AS 关键字，这是必需的。AS 关键字后面是用括号括起来、定义 CTE 的查询表达式。
**Oracle**
ASP.NET web.config 中<appSettings>的设定Oracle连线tns
<add key="OCDT" value="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,'第八大街');--插入信息,此时已实现主键自增
orcl分页1:
string sqlwhere = " ";
if (USER_NAME != "")
{
sqlwhere += $"and USER_NAME like '%{USER_NAME}%'";
}
int size = 10;
int page = int.Parse(current);
string sqlPage = $@"select* from(
SELECT ROWNUM AS rowno, aa.* FROM
(SELECT* FROM B_LOADHISTORY where 1 = 1 {sqlwhere} order by H_TIME desc) aa
where ROWNUM <= {size} * {page}) bb
where bb.rowno > {size} * ({page} - 1)";
orcl分页2:
public string getTable(int limit, int offset, string search)
{
string sqlAll = $@"select
t.user_id,
t.user_name 姓名,
t.所在部门,
t.岗位,
t.用工制,
t.政治面貌,
t.最高学历,
t.user_degree_highest_name 最高学历院校,
t.user_degree_highest_speciality 专业,
t.user_key 编号,
t.S_IDCARD 身份证号
from USERVIEW t where 1 = 1 {sqlWhere} {sqlWhereJURISDICTION} order by t.DEPT_ORDER,t.s_idcard";
//from USERVIEW t where t.active = 'Y' { sqlWhere}
//{ sqlWhereJURISDICTION}
//order by t.DEPT_ORDER,t.s_idcard";
offset = offset + limit;
int Num = (offset - limit + 1);
string sqlPage = $"select* from(select t.*, rownum num from ({sqlAll}) t where rownum <= {offset} )where num>= {Num}";
int total = DbHelperOra.GetCount($"select count(*) from({ sqlAll})");
var rows = DbHelperOra.GetTable(sqlPage);
var obj = new { total, rows };
return JsonConvert.SerializeObject(obj);
Oracle实现自增方式：序列+触发器
-------------------------------------------
--一、建立自增序列
CREATE SEQUENCE 序列名--后缀_SEQ
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE -- 不建缓冲区
**在sql中使用SEQ序列**
insert into table1(S_DEPT_id,field2) values(S_DEPT_SEQ.NEXTVAL,value2)
--二、建立触发器
CREATE TRIGGER 触发器名--后缀_TRI
BEFORE INSERT ON 表名 FOR EACH ROW WHEN (NEW.主键名 IS NULL)
BEGIN
SELECT 序列名.NEXTVAL INTO:NEW.主键名 FROM DUAL;
END;
---------------------------------------------
Orcl独有递归函数（类似CTE）==》（START WITH where_id = '本部' CONNECT BY PRIOR id = pid --这里写递归）+DUAL虚拟表
