oracle

create user mytest identified by .;--创建用户mytest密码.drop user test;--删除用户testselect * from all_tables where owner='HR';--SYS查询HR用户表，HR不能小写

--过滤列select * from employees;select first_name||'.'||last_name as 姓名,salary as 月薪,salary*12+500 年薪 from employees;select distinct manager_id from employees;--过滤行select last_name as lname, salary, job_id, hire_date --3from employees  --1where salary>5000 and last_name like 'A%' --2order by salary desc; --4select first_name ,salaryfrom employeesorder by first_name;select first_name,salary, job_idfrom employeeswhere department_id=60;--单行函数select sysdatefrom dual;select round((sysdate- hire_date)/7) as weeksfrom employees;select trunc(months_between(sysdate, hire_date)) months, hire_datefrom employees;select round(sysdate,'year')--round四舍五入from dual;select substr('hello world!', 7), substr('hello world!', 7,1)from dual;--转换函数select to_char(hire_date,'yyyy-mm-dd')from employees;select months_between(to_date('2014-7','yyyy-mm'), to_date('2014-6','yyyy-mm'))from dual;select to_char(sysdate,'yyyy/mm/dd,hh12:mi:ssam')from dual;select '888'+8 from dual;--分组 --where在group by前执行，having 在group by 后执行select salary,count(*)from employeesgroup by salary;select department_name,count(e.department_id)from departments dleft join employees e on d.department_id=e.department_idgroup by d.department_nameorder by count(e.department_id) desc;select j.job_title, count(e.first_name)from jobs j left join employees e on j.job_id=e.job_idgroup by j.job_titleorder by count(e.first_name) desc;