Oracle DB SQL Query 基础

/* CREATE TABLE*/
create table it_employees (
employee_id number(10) not null unique,
first_name varchar2(20),
last_name varchar2(20),
email varchar2(30),
phone_number varchar2(20),
job_id number(10),
salary number(10),
manager_id number(10)
);

/*CREATE VIEW*/
create view prog_employees
as
select employee_id, first_name, last_name, email, phone_number, salary, manager_id
from it_employees
where job_id=1001
with check option;/*be with where conditions when use view */

/*CREATE INDEX*/
create index it_lastname on it_employees(last_name ASC);

/*DROP TABLE*/
drop table it_employees;

/*DROP VIEW*/
drop view prog_employees;

/*DROP INDEX*/
drop index it_lastname;

/*ALTER -- AND A NEW COLUMN*/
alter table it_employees add birth_date date;

/*ALTER -- MODIFY DATA TYPE*/
alter table it_employees modify email varchar2(20);

/*ALTER -- MODIFY COLUMN NAME*/
alter table it_employees rename column email to e_mail;

/*ALTER -- DELETE CONSTRAINTS*/
alter table it_employees drop unique (employee_id);

/*ALTER -- ADD CONSTRAINTS*/
alter table it_employees add unique (employee_id);

/*SELECT*/
select * from it_employees;

/*CALCULATE*/
select employee_id, first_name,last_name,e_mail,phone_number,job_id,salary*(1+0.1) salary2, manager_id, birth_date from it_employees;

/*NO DUPLICATED VALUES*/
select distinct job_id from it_employees;

/*WHERE --%*/
select * from it_employees where e_mail like '%@oracle.com';

/*WHERE --AND*/
select * from it_employees where job_id=1001 and salary>10000;

/*WHERE --OR*/
select * from it_employees where job_id=1001 OR salary>12000;

/*ORDER BY*/
select distinct job_id from it_employees order by job_id asc;

/*GROUP BY --COUNT --MAX --AVG --MIN --SUM*/
select job_id,count(*),max(salary),avg(salary),min(salary),sum(salary) from it_employees group by job_id order by job_id desc;

/*GROUP BY --ROLLUP*/
select job_id,count(*),max(salary),avg(salary),min(salary),sum(salary) from it_employees group by rollup(job_id) order by job_id asc;

/*GROUP BY --HAVING*/
select job_id, sum(salary) from it_employees group by job_id having avg(salary)>12000;

/*UNION (Result Set : or)*/
select employee_id, last_name, first_name from it_employees where last_name like 'G%'
union
select employee_id, last_name,first_name from it_employees where first_name like '%t';

/*INTERSECT (Result Set : and)*/
select employee_id, last_name, first_name from it_employees where last_name like 'S%'
intersect
select employee_id, last_name,first_name from it_employees where first_name like '%t';

/*MINUS*/
select employee_id, last_name, first_name from it_employees where last_name like 'S%' or last_name like 'G%'
minus
select employee_id, last_name,first_name from it_employees where first_name like '%t' or first_name like '%s';

/*子查询 --IN  --EXISTS  -- > = <*/
select * from it_employees where department_id in (select department_id from departments where location_id in (10,12));

select * from it_employees where exists (select * from departments where location_id in(10,12));

select * from it_employees where job_id=1001 and salary>(select avg(salary)from it_employees where job_id=1001);

/*DML  --INSERT*/
insert into it_employees (employee_id,first_name,last_name,e_mail,phone_number,job_id,salary,manager_id,birth_date)values(10001,'Wayne','Granger','wayne.G@oracle.com',10163788503,1001,10000,10005,'20-7月 -12');

/*UPDATE*/
update it_employees set department_id=101 where employee_id=10001;

/*DELETE*/
delete from departments where department_id=105;
delete from departments;

/*GRANT*/
create user user1 identified by user1;
drop user aime1 cascade;
grant select on departments to user1;

/*REVOKE*/
revoke select on departments from user1;

/*ASCII()*/
select ascii('A') big_a, ascii('a') small_a from dual;

/*CHR()*/
select chr(65),chr(97)from dual;

/*CONCAT(c1,c2) --连接字符串*/
select concat('Oracle','11g') name from dual;

/*INITCAP(c1) --首字母大写*/
select initcap('i am in oracle !') fin from dual;

/*LENGTH(C1)*/
select length('Oracle DB') length from dual;

/*LOWER(C1)*/
select lower(department_name) from departments where location_id=10;

/*SUBSTR(C1,i,j) --截取一段字符串*/
select substr('NationalDay',0,8) str from dual;