oracle

create user mytest identified by .;--创建用户mytest密码.
drop user test;--删除用户test
select * 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 --3
from employees  --1
where salary>5000 and last_name like 'A%' --2
order by salary desc; --4

select first_name ,salary
from employees
order by first_name;

select first_name,salary, job_id
from employees
where department_id=60;

--单行函数
select sysdate
from dual;

select round((sysdate- hire_date)/7) as weeks
from employees;

select trunc(months_between(sysdate, hire_date)) months, hire_date
from 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 employees
group by salary;

select department_name,count(e.department_id)
from departments d
left join employees e on d.department_id=e.department_id
group by d.department_name
order 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_id
group by j.job_title
order by count(e.first_name) desc;