--2.查看表结构 desc wx_monitor_excption; --3.从表中查询数据 select * from wx_monitor_excption; --7.双引号保持原来的格式 select id "id1" from wx_monitor_excption; --6.查询当前系统的日期 select sysdate from dual; --8.查询数字时,把为空值的换为0,oracle中空值运算结果都为null select id,remark from wx_monitor_excption where remark is null; select id,plate_number,nvl(remark,'null') from wx_monitor_excption; --9.字符串连接(把两个字段查询出来的数据作为一条字符串输出)两个单引号代替一个 select id||openid from wx_monitor_excption; select id||'nimei' from wx_monitor_excption; --10.去掉重复的值(也会去掉多个字段组合重复的值) select distinct plate_number from wx_monitor_excption; --12.条件 between and (包含800和1500) 'or' and 'and' select * from wx_monitor_excption where (create_time between '26-12月-14' and '30-12月-14') or exception_category='堵车上报'; select * from wx_monitor_excption where create_time>='26-12月-14' and create_time<='30-12月-14'; --15. 模糊查询 %零个或多个,下横线_代表一个 select * from wx_monitor_excption where position like '%上海%'; --17.排序 order by 默认升序asc select * from wx_monitor_excption order by create_time asc; --18.函数 转化为小写lower() select lower(openid) from wx_monitor_excption; --19.函数 截子串substr(ename,2,3) 从字符串ename中第2个开始截,一个截3个字符 select substr(openid,6,4) from wx_monitor_excption; --20.函数 把数字转化为相应的字母,相反 ascii('A') select chr(77) from dual; --21.函数 四舍五入 select round(23.77) from dual; select round(23.456,2) from dual; select round(23.456,-1) from dual; --22.函数 把数字或字母或日期转化为特定的格式 to_char(sal,'$99,999.9999'), $换成L,显示¥ select to_char(create_time,'yyyy-MM-dd hh24:mm:ss') from wx_monitor_excption; select * from wx_monitor_picstatus; select to_char(len,'$99,999.99') from wx_monitor_picstatus; select to_char(len,'$00,000.00') from wx_monitor_picstatus; --23.函数 把特定的字符转化为日期 to_date('', '') select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; --24.函数 把特定的字符转化为数字 to_number('$1,250.00', '$9,999.99') select * from wx_monitor_picstatus where len>to_number('2','99'); --27.组函数 求出总共多少条数据 count(*),count(ename), 凡是不是空值的字段一共有几个 select count(*) from wx_monitor_picstatus; --28.函数 分组查询 group by select openid,count(openid) from wx_monitor_excption group by openid; --45.创建视图 create view v$name as 重复使用的语句. 视图就是一个子查询,就是一张表 create view nimei as select * from wx_monitor_excption where plate_number='沪A12312'; --46.插入语句 insert into wx_monitor_excption(id,openid,carline,plate_number,phone_number) values('123','324','45','554','4545'); select * from wx_monitor_excption where id='123'; --47.备份表 create table tab1 as select * from wx_monitor_excption; --删除表中的数据 delete from tab1; select * from tabl; --完全删除表 drop table TAB1; --48.伪字段 rownum 默认从第一行往后排列序号1,2,3等,必须< = 号; select * from wx_monitor_excption where rownum<10; rollback; commit; --54.约束 自定义名字 非空约束 create table TAB1(id number(5),name varchar(50) not null); select * from tab1; desc tab1; insert into tab1 values(123,'wooooo','1515'); --58.修改现有表的表结构 alter table tab1 drop(create_time); alter table tab1 add(create_time varchar2(50)); alter table tab1 modify(create_time varchar(10)); --64.索引 index create index syname on wx_monitor_excption(plate_number,openid); desc wx_monitor_excption; --65.删除索引 drop index syname; --66.序列 oracle独特的 自动递增 create sequence sename; drop sequence sename; select sename.nextval from dual;