博主在之前的开发中发现这几条SQL语句特别实用,于是记录下来现写于此篇。本人才疏学浅,文中如果有不对或值得讨论的地方,欢迎大家提出来,一起探讨共同进步。👇👇👇
文章目录
查询某字段是否存在某表
select count(0) as aanum
from user_tab_columns
where UPPER(column_name)='PT_DIETARYHABIT' AND TABLE_NAME = 'table'
查询一个表中总共有多少字段
select count(*) from user_tab_columns where table_name=upper('表名')
查询某个字段都存在在哪些表里
select table_name from dba_tab_columns where column_name='字段名'
Oracle数据库的分页查询(三种)
select * from (select a1.*,rownum rn from (select * from student) a1 where rownum <=5) where rn>=2;
select a1.* from (select student.*,rownum rn from student where rownum <=5) a1 where rn >=3;
select a1.* from (select student.*,rownum rn from student) a1 where rn between 3 and 5;
查询当前日期
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
INSERT INTO SELECT语句
Insert into Table2(field1,field2,...) select value1,value2,... from Table1
或
Insert into Table2 select * from Table1
- 注意:
(1)要求目标表Table2必须存在,并且字段field,field2…也必须存在
(2)注意Table2的主键约束,如果Table2有主键而且不为空,则 field1, field2…中必须包括主键
(3)注意语法,不要加values,和插入一条数据的sql混了,不要写成:Insert into
Table2(field1,field2,…) values (select value1,value2,… from Table1) 由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。示例如下:+ expand sourceview plaincopy to clipboardprint
测试如下:
for (int i = 0; i < ChtPatTemp.Columns.Count - 1; i++)
{
if (sSelect.Trim() != "")
sSelect += "," + Environment.NewLine;
if (i == 55)
sSelect += ":Time" + "," + Environment.NewLine;
sSelect += ChtPatTemp.Columns[i].ColumnName;
}
string SQL = @"INSERT INTO Table1 SELECT " + sSelect + " FROM Table2 WHERE field =:fieldtest";
INSERT INTO Table1
SELECT
(.....省略其他字段)
field1,
field2,
field3,
field4
FROM Table2
WHERE ch.field=:fieldtest
用sql截取特殊符号之前或之后的值
//截取REP_MEMO字段中_符号中间的内容,例值为:1_R_263,则截取的内容为R
SUBSTR(REP_MEMO,INSTR(REP_MEMO,'_',1,1)+1,INSTR(REP_MEMO,'_',1,2)-1-INSTR(REP_MEMO,'_',1,1))
//截取REP_MEMO字段中最后一个_符号之后的内容,例值为:1_R_263,则截取的内容为263
SUBSTR(REP_MEMO,INSTR(REP_MEMO,'_',2,2)+1,LENGTH(REP_MEMO)-INSTR(REP_MEMO,'_'))
好啦!到这里已经成功啦,快去试试吧!✌
〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰〰
更多博客内容请查看 👉 可可西里的博客
如果喜欢的话可以点个赞和关注嗷~ 🤞🤞🤞