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虚拟表