/* 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;
Oracle DB SQL Query 基础
最新推荐文章于 2021-04-15 08:44:01 发布