-- 在sql中只有别名时用的是双引号
select employee_id,last_name,12*salary as yearSalary from employees;
select last_name from employees;
select department_id from department;
select last_name,department_id from employees;
select last_name from employees;
select employee_id,last_name,department_id from employees;
select employee_id,last_name,department_id from employees where employee_id > 200 ;
-- 日期和字符串是放在的单引号中的在字符串中区分大小写的
select employee_id,last_name from employees where last_name = 'Chen' ;
select employee_id,last_name from employees where hire_date = '7-6月-1994' ;
select last_name from employees where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07' and salary >4000;
select last_name ,salary from employees where
select last_name from employees where salary between 4000 and 7000;
select last_name,salary from employees where salary in (4000 , 7000);
select last_name from employees where last_name like 's' ;
select last_name,commission_pct from employees where commission_pct no is null ;
-- 单行函数
select count(employee_id) from employees;
select lower('ATGUIFU'),UPPER('atguigu'),initcap('Atguigu java') from dual;
select last_name from employees where lower(last_name) = 'king';
select substr('hello world',0,5) from dual
select instr('hello java','L') from dual;
select employee_id last_name lpad(salary,10,' ') from employees;
select trim('h' from 'hsdfsfsfasdh') lpad(salary,10,' ') from employees;
select replace('abcsdbb','b','X') from dual;
-- 数字函数
select round(435.45,2),round(435.45),round(435.45,-2) from dual;
select trunc(435.45,2),trunc(435.45),trunc(435.45,-2) from dual;
select sysdate,sysdate+1 ,sysdate-3,from dual;
select employee_id ,last_name, sysdate-hire_date as workDays from employees;
select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期日') from dual:
select employee_id ,last_name, hire_date from employees where hire_date=last_day-1;
select employee_id, hire_date from employees where hire_date = to_char(hire_date,'yyyy-mm-dd') = '1997-06-07';
select employee_id, hire_date from employees where to_date('1997-06-25','yyyy-mm-dd') = hire_date;
-- number to char
select to_char(122323444.89,'999,999,9999,.99') from dual;
-- 前缀加当地的货币符号
select to_char(122323444.89,'L999,999,9999,.99') from dual;
-- 前缀加$符号
select to_char(122323444.89,'$999,999,9999,.99') from dual;
-- 用0填充
select to_char(122323444.89,'000,999,9999,.99') from dual;
select to_number('$001,234,234.89','$000,000,999.99') from dual;
-- 求员工的年薪
select employee_id,last_name,salary*12*(1+commission_pct) from employees;
select employee_id,last_name,nvl(department_id,'没有部门') from employees;
select last_name,commission_pct ,nvl(commission_pct+0.015,commission_pct+0.01) as tt from employees;
select last_name,department_id,case department_id
when 10 then salary *1.1
when 20 then salary *1.2
when 30 then salary *1.3
else salary
end as ss
from employees;
select last_name,department_id,decode(department_id,10 ,salary *1.1
,20 ,salary *1.2
,30 ,salary *1.3
,salary
) as ss
from employees;
select TO_CHAR(sysdate,'yyyy"年"MM"月"dd"日" HH:mm:ss') from dual;
-- 多表查询
SQL> select employee_id,departments.department_id ,department_name from employee
s ,departments;
select employee_id,departments.department_id ,departments.department_name
from employees ,departments where departments.department_id = employees.department_id;
select q.employee_id,w.department_id ,w.department_name
from employees as q,departments as w
where w.department_id = q.department_id;
select e.last_name,e.department_id,d.department_name from employees e, departments d where e.department_id = d.department_id;
-- 左外连接
select e.last_name,e.department_id,d.department_name from employees e, departments d where e.department_id = d.department_id(+);
-- 内连接，等值&不等值的
--外连接 左外连接，又外连接
select e.last_name,e.department_id,d.department_name from employees e, departments d where e.department_id(+) = d.department_id;
select e.last_name,e.department_id,d.department_name,city from employees e left join departments d on e.department_id = d.department_id ;
-- 全连接
select e.last_name,e.department_id,d.department_name from employees e full
join departments d on e.department_id = d.department_id ;
-- 自连接
select e1.employee_id,e1.last_name,e1.manager_id from employees e join employees e1 on e.manager_id = e.employee_id where e.last_name = 'Chen';
-- 分组函数
select salary from employees group by salary;
select max(salary)from employees group by salary;
select min(salary),avg(salary),count(1),sum(salary) from employees ;
-- count 计算的是不为空的值
select count(commission_pct) from employees;
select commission_pct from employees WHERE commission_pct is ;
select avg (nvl(commission_pct，0)) from employees;
select avg(salary) from employees group by department_id;
select distinct department_id from employees;
-- group by 后面的字段之间用逗号连接
select department_id ,job_id,avg(salary) from employees group by department_id,job_id;
select avg(salary) from employees group by department_id;
select department_id ,job_id,avg(salary) from employees group by department_id;