--更改用户口令
alter user test identified by test

--DDL 数据定义语言：操作数据库对象的语言，对数据库对象进行的创建（create），修改（alter），删除（drop）
--DML 数据操纵语言：对表的数据进行增（insert），删（delete），改（update），查（select）
--TCL 事物控制语言：事物的提交、回滚等。（commit，rollback，savepoint）
--DCL 数据控制语言：Grant、revoke

--sysdate? & sysTimestamp
select sysdate from dual;
select to_char(sysdate,'yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyyMMdd') from? dual;
select to_date('20010203','yyyy-MM-dd') from dual;
select systimestamp from dual;

-- rowID &? rowNum
select rowId,sysdate from dual;
select Rownum ,sysdate from dual;
select * from g_jobinfo where rownum <10;

--trancate & delete
--desc
desc tablename
--DML
select * from g_jobinfo;
--使用现有表创建新表 // create table tablename as select
create table gg_jobinfo as select ID,Name,City,DUty from g_jobInfo;
select *? from gg_jobinfo;
create table ggg_jobinfo as select ID,Name ,city,duty from g_jobinfo where 1=2;
select * from ggg_jobinfo;

--ROLLBACK
--rollback to savepoint svt;
select * from gg_jobinfo where rownum<10;
update gg_jobinfo set city = 'WH' where id = 63;
savepoint marker1;
delete from gg_jobinfo where id = 62;
savepoint marker2;
rollback to savepoint marker1;

select * from gg_jobinfo where id = 63;
select * from gg_jobinfo where id = 62;

--union
select count(*) from (select ID,Name,City,DUty? from g_jobinfo union select * from gg_jobinfo);
select count(*) from gg_jobinfo;
select count(*) from g_jobinfo;
?
--union all
select count(*) from (select ID,Name,city,duty from g_jobinfo union all select * from gg_jobinfo);
select b.* from (select ID,name ,city ,duty from g_jobinfo
????? union all
????? select * from gg_jobinfo) b order by b.id DESC

--intersect
select count(*) from (select id,name ,city ,duty from g_jobinfo
?????? intersect
?????? select * from gg_jobinfo) ;
??????
--minus
select Id ,name ,city ,duty from g_jobinfo
?????? minus
?????? select * from gg_jobinfo;
??????
--单行函数：即对表中的没行记录，他们都返回一个值，单行函数可以用在select子句和where子句当中
--????????? 主要包含日期函数、字符串函数、数字函数、转换函数，其他函数
--日期函数
select sysdate ,add_months(sysdate,2) from dual;
select sysdate,add_months(sysdate,-5) from dual;
select sysdate,add_months(sysdate,-9.981) from dual;

select months_between(sysdate,add_months(sysdate,-1)) from dual;

--last_day :返回指定日期当月的最后一天
select last_day(sysdate) from dual;
select last_day(add_months(sysdate,-9)) from dual;

--round
select ROUND(sysdate) from dual; --默认为不加参数，舍到最近的星期日
select round(sysdate ,'day') from dual;
select round(sysdate,'YEAR') from dual;--舍入到最近的年的开始：1月1日
select round(sysdate,'month') from dual;--月的第一日

select round(-1) from dual;

--next_day
select next_day(sysdate,'星期二') from dual;

--trunc
select trunc(sysdate,'YEAR') from dual;
select trunc(sysdate,'Month') from dual;
select trunc(add_months(sysdate,-15),'day') from dual;

--extract
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
--select extract(hour from sysdate) from dual;
--select extract(minute from sysdate)from dual;
--select extract(second from sysdate) from dual;
select to_char(sysdate,'YYYY') from dual;

--字符串函数
select chr(65) from dual;
select ascii('A') from dual;
select initcap('hello') from dual;--首字母大写
select ltrim('abdcsss','ab') from dual;
select Rtrim('abdcsss','sss') from dual;
select trim('abc_abe_abd_ea') from dual;

select translate('我是谁abcdef','我abde','1245') from dual;
select? replace('我是谁abcdef','我','yyy') from dual;
select instr('我是谁abcdef','c') from dual;
select subSTR('我是谁abcdef',3,4) from dual;--从第三个开始，截取4个字符，索引从1开始
select concat('我是谁abcdef','我是谁abcdef') from dual;
select LP