常用的SQL语句
涉及的系统表user_tables ,user_col_comments ,user_tab_comments
注意:表名区分大小写不然无法查出
通过系统表查询连接的数据库的表信息等
- 查询所有表名:select t.table_name from user_tables t;
- 查询所有字段名:select t.column_name from user_col_comments t;
- 查询指定表的所有字段名:select t.column_name from user_col_comments t where t.table_name = ‘表名’;
- 查询指定表的所有字段名和字段说明:select t.column_name, t.column_name from user_col_comments t where t.table_name = ‘表名’;
- 查询所有表的表名和表说明:select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;
- 查询模糊表名的表名和表说明:select t.table_name from user_tables t where t.table_name like ‘ACCOU%’;
或者select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name where t.table_name like ‘ACCOU%’; - 查询表的数据条数、表名、中文表名:select a.num_rows, a.TABLE_NAME, b.COMMENTS from user_tables a, user_tab_comments b WHERE a.TABLE_NAME = b.TABLE_NAME order by TABLE_NAME;
SQL INSERT INTO 语句
-
INSERT INTO 表名称 VALUES (值1, 值2,值3,值)
-
从一个表中复制所有的列插入到另一个已存在的表中:INSERT INTO table2 SELECT * FROM table1
-
只复制希望的列插入到另一个已存在的表中:INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;
-
通过某一个字段值查找后将此条数据插入同一个表的数据
一、复制同表数据,不做修改INSERT INTO 表名 (字段)SELECT 字段 FROM 表名 WHERE 条件;
二、复制同表数据同时作出修改
如果要修改其中某一个字段,在查询语句中:x(要改变的值) as 字段名。
举例:
INSERT INTO IMES.M_BLOCK_CONFIG_VALUE(CONFIG_TYPE_ID, CONFIG_ID, CONFIG_NAME, CONFIG_DESC, CONFIG_VALUE, ROUTE_NAME, LINE, STATION_TYPE, STATION_NAME, CLIENT_ID, UPDATE_EMPNO, CREATE_EMPNO, CREATE_TIME, ENABLED)
SELECT CONFIG_TYPE_ID, SYS_GUID(),CONFIG_NAME,CONFIG_DESC,CONFIG_VALUE,:ROUTE_NAME,LINE,STATION_TYPE,STATION_NAME,CLIENT_ID,UPDATE_EMPNO,CREATE_EMPNO,CREATE_TIME,ENABLED FROM IMES.M_BLOCK_CONFIG_VALUE WHERE ROWID =:strRID
SELECT数据记录筛选
- 查询单一值:select * from 数据表 where 字段名 = 字段值 order by 字段名
- 模糊查询:select * from 数据表 where 字段名 like '%字段值 'order by 字段名
- 查询符合条件的前10条数据 : select * from 表名 where rownum<=10;
- select * from 表名where rownum<=10 order by 字段名 desc
- 查询多个符合条件的值:select * from 数据表 where 字段名 in (‘值1’,‘值2’,‘值3’)
- 查询字段值范围内的数据: select * from 表名 where 字段名 between ‘值1’ and ‘值2’
- 筛选字段长度 length(字段)函数可以判断某字段的长度: select * from tbName where length(nvl(alias,’’))<= 4 length( nvl(str1,’’))使用nvl函数是为了筛选时把结果为null的值过滤掉
举例: 实际使用 SELECT * FROM SYS_EMP WHERE length(EMP_NO)<9; - oracle中查询不在某范围内的数据信息:select * from 表名 where 字段 not in (‘值’); not in子句中过滤掉空值,为了避免not in子查询中出现空值,影响查询结果,可以对这部分子查询的结果进行非空过滤。如下:Select * From A where A.col not in (Select B.col from B where B.col is not null)
SELECT查询的数据字段重新赋值名称 - SELECT GLUETYPENAME “耗材型号”,GLUEVENDOR “供应商”,APN “料号”,GLUETYPE “物料类型” FROM IMES.M_GLUE_TYPE
- SELECT GLUETYPENAME as “耗材型号”,GLUEVENDOR as"供应商",APN as “料号”,GLUETYPE as “物料类型” FROM IMES.M_GLUE_TYPE
- C# 中查询
SELECT GLUETYPENAME as 耗材型号 ,GLUEVENDOR as 供应商 ,APN as 料号,GLUETYPE as 物料类型 FROM IMES.M_GLUE_TYPE
UPDATE数据更新记录
- update 数据表 set 字段名 = 字段值 where 条件表达式
- update 数据表 set 字段名1 = 字段值1,字段名2 = 字段值2 where 条件表达式
DELETE 删除语句
- delete from 数据表 where 条件表达式
- delete from 数据表 (将数据库表所有的记录删除)
- drop table test 就是把整个表 移除.里面的数据都消失
INSERT插入语句
- insert into 数据表 (字段1,字段2,字段3…) values (值1,值2,值3…)
- insert into 目标数据表 select * from 源数据表 (把源数据表的记录加到目标数据表)
数据记录统计函数
- AVG(字段名) 得出一个表格栏平均值
- COUNT (**|字段名) 对数据行数的统计或对某一栏有值的数据统计 注:括号内输入一个*
select count(*) from 表名;统计出查询出的行数 - MAX(字段名)取得一个表格栏最大的值
- SUM(字段名)把数据栏的值相加
- MIN(字段名)取得一个表格栏最小的值
举例:其他使用一致
select sum (NEWVENDOR) from ANDY where NEWVENDOR between ‘1000345’ and ‘1000366’; 将此范围内的NEWVENDOR值加总输出
多表查询,连接查询
-
内连接查询:隐式内连接 select * from A,B where 条件隐式连接使用别名:select * from A 别名1,B 别名2 where 别名1.xx=别名2.xx;显示内连接 select * from A inner join B on 条件 (inner可以省略)显示连接使用别名: select * from A 别名1 inner join B 别名2 on 别名1.xx=别名2.xx
举例:SELECT * FROM category c,product p WHERE c.cid=p.category_id; -
外连接:外连接有两种方式,一种是左外连接,一种是右外连接 。外连接解决的问题:当条件不成立时,任然希望在结果中包含不成立的记录
左外连接: where d.deptno=e.deptno 当不成立时,等号左边代表的表的信息任然被包含
写法: where d.deptno=e.deptno(+)
右外连接: where d.deptno=e.deptno 当不成立时,等号右边代表的表的信息任然被包含
写法:where d.deptno(+)=e.deptno
** 通过SELECT 查询出其他表的值并显示在报表
SELECT A.WO_STOCK as “区域”, A.IPN as “料号” ,
(SELECT B.MODEL from IMES.P_WO_BASE B where A.WORK_ORDER=B.WORK_ORDER ) “机种”,
(SELECT count(SERIAL_NUMBER) FROM P_SAP_UTS ) “SN”,
(SELECT B.WO_BATCH_NO from IMES.P_WO_BASE B where A.WORK_ORDER=B.WORK_ORDER ) “批次”,
A.STORAGE_NO as"储位",
(SELECT count(SERIAL_NUMBER) FROM P_SAP_UTS WHERE SERIAL_NUMBER NOT IN(SELECT SERIAL_NUMBER FROM P_SHIPPING_DETAIL WHERE CONFIRM_TIME IS NOT NULL)) “仓库数”
FROM IMES.P_SAP_UTS A
在TOAD中查询数据时日期的问题
- 如你时间那列的类型是date型的话,就这样
select to_char(时间度,‘yyyy-mm-dd hh24:mm:ss’) from table - 如果是字符型,那就只能把时间给拆开了,用内substr
select substr(时间,7,2)||’-’||substr(时间,4,2)||’-’
update 两个表关联 更新
- update table1 a set a.c = (select b.b from table2 b where a.a=b.a)
例如:update BAT_TOOLING_STORE B set B.STORE_CODE=(select s.STORECODE from STORE s where s.STORENAME=B.STORE_CODE)
在Oracle中查找某条件是否存在重复值
- select 字段值 from 表 group by 字段值 having(count(字段值))>1
举例:
select STORENAME from STORE group by STORENAME having(count(STORENAME))>1
创建表和删除等的字段
如需在表中添加列,请使用下列语法:
ALTER TABLE table_name
ADD column_name datatype
要删除表中的列,请使用下列语法:
ALTER TABLE table_name
DROP COLUMN column_name
要改变表中列的数据类型,请使用下列语法:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
表中添加字段
1、alter table 表名 ADD 字段 类型baidu NOT NULL Default 0
2、ALTER TABLE employee ADD spbh varchar(20) NOT NULL Default 0
3、在表employee 中加入 spbh 类型是varchar大小20 不为空 默认值是zhi0
ALTER TABLE G_LITEON_FIFO_REELID ADD AREA varchar(20)
更改Oracle数据库表字段的类型长度
添加一个varchar2类型的临时字段
alter table A add new_temp varchar2(10);
备份一下字段a
update A set new_temp = a;
事务提交
commit;
清空字段a数据
update A set a = ‘’;
事务提交
commit;
这时a字段是没数据的
alter table A modify a varchar2(10);
将临时字段的数据搬过来
update A set a = new_temp;
事务提交
commit;
drop一下临时字段
alter table appr_control_info_ex drop column new_temp;
复制表中的数据到相同表结构中
通过DBlink
insert into FUNCTIONS select * from FUNCTIONS@LINK_EMS_TESTDB where LEVEL2 in( ‘校验系统’)
ORACLE计算
select MOD(110,50) from dual --取余
select trunc( 90/33) from dual – 取整去1
select ceil(70/60) from dual – 取整进1