oracle学习笔记１: SQL核心

关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。当前主流的关系型数据库有Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access、MySQL、浪潮K-DB等。
实体关系模型（Entity-Relationship Model)，简称E-R Model是陈品山（Peter P.S Chen)博士于1976年提出的一套数据库的设计工具，他运用真实世界中事物与关系的观念，来解释数据库中的抽象的数据架构。实体关系模型利用图形的方式（实体-关系图（Entity-Relationship Diagram））来表示数据库的概念设计，有助于设计过程中的构思及沟通讨论。

执行命令的几种方式

select empno, deptno from scott.emp where ename='SMITH';

select empno, deptno from scott.emp where ename='SMITH'
    ;

SQL> select empno, deptno from scott.emp where ename='SMITH'
  2  /

SQL> select empno, deptno from scott.emp where ename='SMITH'
  2  
SQL> /

SQL> @E:\bjc2016\study\list_depts.sql
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

list_depts.sql
select * from scott.dept;

创建数据库

connect system/0529;
-- 创建表空间：
-- 格式:  create tablespace 表间名 datafile '数据文件名' size 表空间大小;
create tablespace oe_tablespace datafile 'E:\bjc2016\study\demo\oe.dbf' size 200M;
--创建用户：
--格式:  create user  用户名 identified by 密码  default tablespace 表空间表;
--创建（新）用户：
--也可以不创建新用户，而仍然用以前的用户，如：继续利用scott用户
--create user username identified by password;
create user tomluo identified by tomluo default tablespace oe_tablespace;
--将空间分配给用户：
--alert user username default tablespace oe_tablespace;
--将名字为oe_tablespace的表空间分配给username 
--授权给新建的用户
--grant create session,create table,unlimited oe_tablespace to username;
grant connect,resource,dba to tomluo;  --表示把 connect,resource权限授予tomluo用户
grant connect,resource,dba to scott;  --表示把 connect,resource权限授予scott用户
--用户登录，登录之后创建表即可。
conn tomluo/tomluo;

建表并准备实验数据

--创建customers表
drop table customers;
create table customers(
  customer_id number,
  gender char(1),
  cust_firt_name varchar(20),
  cust_last_name varchar(20),
  accout_mgr_id number
);
--准备测试数据
delete from customers;
insert into customers VALUES(1,'F','lili','zhang',1);
insert into customers VALUES(2,'M','jack','chen',1);
insert into customers VALUES(3,'F','tom','luo',2);
insert into customers VALUES(4,'F','hanmeimei','wu',2);
insert into customers VALUES(5,'F','polly','li',2);
select * from customers;

--准备测试数据
drop table jobs;
create table jobs(
job_id number primary key,
job_name varchar(20) not null
);
insert into jobs values(1,'soccer');
insert into jobs values(2,'reporter');
insert into jobs values(3,'dirver');


drop table departments;
create table departments(
department_id number primary key,
department_name varchar(20) not null,
manager_id number
);

--准备测试数据
delete from departments;
insert into departments VALUES(90,'Executive',1);
insert into departments VALUES(89,'Dev',90);
insert into departments VALUES(88,'Sales',7);
insert into departments VALUES(60,'Design',7);
select * from departments;
--创建employees表
drop table employees;
create table employees(
  employee_id number not null primary key,
  gender char(1) not null ,
  first_name varchar(20) not null ,
  last_name varchar(20) not null ,
  salary number not null ,
  commission_pct number not null ,
  department_id number   references departments(department_id) on delete cascade not null,
  job_id number not null ，
　email varchar2(30) not null 
);
create index EMP_EMAIL_UK on employees(EMAIL);
create index emp_dept_id_idx on employees(department_id);
--create index EMP_DEPARTMENT_IX on employees(DEPARTMENT_ID);

delete from employees;
insert into employees VALUES(1,'F','dongge','xu',24500,0,90,1,'dongge@gmail.com');
insert into employees VALUES(2,'M','ali','wu',19000,0,90,1,'wu@gmail.com');
insert into employees VALUES(3,'M','miss','want',27000,0,89,1,'SKING@gmail.com');
insert into employees VALUES(4,'F','Zhang','Honold',9000,0,60,1,'Zhang@gmail.com');
insert into employees VALUES(5,'M','wang','Rrnst',6000,0,60,1,'wang@gmail.com');
insert into employees VALUES(6,'M','Li','Austin',8000,0,60,2,'Li@gmail.com');
insert into employees VALUES(103,'F','Liu','Babala',7800,0,60,2,'Liu@gmail.com');
insert into employees VALUES(104,'M','Han','Lorents',6000,0,60,2,'Han@gmail.com');
insert into employees VALUES(105,'M','Li','Tao',8000,0,60,2,'Tao@gmail.com');
insert into employees VALUES(106,'F','Li','Yun',8000,0,88,2,'Yun@gmail.com');
insert into employees VALUES(107,'F','Polly','bird',8000,0,88,2,'Polly@gmail.com');
select * from employees;



--创建orders表
drop table orders;
create table orders(
  order_id number primary key,
  customer_id number，
  sales_rep_id number,
  order_total number
);
drop sequence seq;
create sequence seq;
--准备测试数据
delete from orders;
insert into orders VALUES(seq.nextval,1,4,10000);
insert into orders VALUES(seq.nextval,2,4,10000);
insert into orders VALUES(seq.nextval,2,4,10000);
insert into orders VALUES(seq.nextval,1,4,10000);
insert into orders VALUES(seq.nextval,1,4,10000);
insert into orders VALUES(seq.nextval,2,4,10000);
insert into orders VALUES(seq.nextval,2,4,10000);
insert into orders VALUES(seq.nextval,1,4,10000);
insert into orders VALUES(seq.nextval,1,4,10000);
insert into orders VALUES(seq.nextval,2,4,10000);
insert into orders VALUES(seq.nextval,3,4,10000);
insert into orders VALUES(seq.nextval,3,4,10000);
insert into orders VALUES(seq.nextval,3,4,10000);
insert into orders VALUES(seq.nextval,3,4,10000);
insert into orders VALUES(seq.nextval,3,4,10000);
insert into orders VALUES(seq.nextval,3,4,10000);
insert into orders VALUES(seq.nextval,4,4,10000);
insert into orders VALUES(seq.nextval,4,4,10000);
insert into orders VALUES(seq.nextval,5,4,10000);
insert into orders VALUES(seq.nextval,5,4,10000);
insert into orders VALUES(seq.nextval,5,5,10000);
insert into orders VALUES(seq.nextval,5,4,100);
insert into orders VALUES(seq.nextval,5,7,10800);
insert into orders VALUES(seq.nextval,5,8,1000);
SELECT * from orders;
commit;

５个核心的ＳＱＬ语句

select 语句

下订单超过４次的女顾客

--下订单超过４次的女顾客
select c.customer_id, count(o.order_id) as orders_ct
  from oe.customers c
  join oe.orders o
    on c.customer_id = o.customer_id
 where c.gender = 'F'
 group by c.customer_id
having count(o.order_id) > 4
 order by orders_ct, c.customer_id;

from
from 列出了查询源的对象（表，视图，物化视图，分区或子分区或一个子查询来生成子对象）
如果使用了多个源对象，其逻辑处理会应用到每个联结类型（交叉联结[笛卡儿乘积——内联结，外联结）

仅含from的子句的查询语句

--仅含from的子句的查询语句
select c.customer_id　cust_id, o.order_id as ord_id, c.gender
  from oe.customers c
  join oe.orders o
    on c.customer_id = o.customer_id;

where
where可按条件查询最终返回限制的记录的条数
逻辑比较的结果可能为true,false,未知（当其中结果含空值null,代表相应值的缺失）
where的作用是限制和减少结果集。
group by
group by 是经过from和where后的结果集进行聚合。也就是按group by条件进行分组，将分组的结果进行汇总。

--截至group by子句的部分查询
select c.customer_id, count(o.order_id) as orders_ct
  from oe.customers c
  join oe.orders o
    on c.customer_id = o.customer_id
 where c.gender = 'F'
 group by c.customer_id;

having
having子句将分组汇总后的查询结果限定为该子句中条件为真的数据行

--再将分组后的结果进行条件限制
select c.customer_id, count(o.order_id) as orders_ct
  from oe.customers c
  join oe.orders o
    on c.customer_id = o.customer_id
 where c.gender = 'F'
 group by c.customer_id
 having count(o.order_id) > 4;

select列表

--展示select各种可能的查询
select c.customer_id,
       c.cust_first_name || ' ' || c.cust_last_name name,
       (select e.last_name
          from oe.employees e
         where e.employee_id = c.account_mgr_id) acct_mgr
  from oe.customers c;

用来展示要显示的那些列（实际的列，表达式或一个select语句的结果）
当使用另一个select语句来生成结果中的某一列的值时，这个查询只能返加一行一列的值，称为标量子查询
还可能用用到distinct用来去掉重复值

order by
用来将查询的返回结果排序
注意排序是查询过程中开销相当大的处理步骤，尤其是返回结果集很大的时候。

insert语句

*　单表插入

--单表插入
insert into hr.jobs(job_id,job_title,min_salary,max_salary)
values('IT_PM','Project Manager',5000,10000);
commit;

insert into hr.jobs(job_id,job_title,min_salary,max_salary)
select ename,job,sal*.9,sal*1.1 from scott.emp
commit;

select * from hr.jobs;

*　多表插入

drop table small_customers;
drop table middle_customers;
drop table large_customers;

create table small_customers(
  customer_id number,
  sum_orders number
);

create table middle_customers(
  customer_id number,
  sum_orders number
);

create table large_customers(
  customer_id number,
  sum_orders number
);

insert all --当指定all子句后，会执行无条件的多表插入
when sum_orders<3 then
into small_customers
when sum_orders>=3 and sum_orders<=5 then
into middle_customers
else 
into large_customers
select o.customer_id,count(o.order_id) sum_orders
from orders o
group by o.customer_id;

select * from small_customers;
select * from middle_customers;
select * from large_customers;

update语句

更改表中原有行的列值。（update,set,where）

创建一个重复的表employees2

drop table employees2 ;
create table employees2 as select * from employees;
select * from employees2;
alter table employees2 
add constraint emp2_emp_id_pk primary key (employee_id);

select employee_id,last_name,salary from employees2 where department_id=90;

使用表达式更新某列

update employees2 set salary=salary*1.1  where department_id=90;
commit;
select employee_id,last_name,salary from employees2 where department_id=90;
rollback;

使用子查询更新某列

update employees
set salary=(select employees2.salary from employees2
where employees2.salary!=employees.salary and employees2.employee_id=employees.employee_id)
where department_id=90;
select employee_id,last_name,salary from employees2 where department_id=90;
-- select * from departments;
rollback;

使用in子查询更新表

select employee_id, last_name, salary
  from employees
 where department_id in
       (select department_id
          from departments
         where department_name = 'Executive');
 --更新      
update employees
   set salary = salary * 1.1
 where department_id in
       (select department_id
          from departments
         where department_name = 'Executive');
 --再次验证      
select employee_id, last_name, salary
  from employees
 where department_id in
       (select department_id
          from departments
         where department_name = 'Executive');
--回滚         
rollback;

定义一个表更新

select employee_id, last_name, salary from employees;
--更新
update (select e1.salary, e2.salary new_sal
          from employees e1, employees2 e2
         where e1.employee_id = e2.employee_id
           and e1.department_id = 90)
   set salary = new_sal;
--再次验证    
select employee_id, last_name, salary from employees;
--回滚  
rollback;

使用子查询更新多个列

select employee_id, last_name, salary, commission_pct
  from employees2
 where department_id = 90;
--更新
update employees
   set (salary, commission_pct) =
       (select employees2.salary, employees2.salary * .10 comm_pct
          from employees2
         where employees2.employee_id = employees.employee_id
           and (employees2.salary != employees.salary or
               employees.salary is null))
 where department_id = 90;
--再次验证
select employee_id, last_name, salary, commission_pct
  from employees
 where department_id = 90;
--回滚  
rollback;

delete语句

从表移出行(delete, from,where)

使用where子句中的筛选条件删除行

select employee_id, last_name, salary, department_id from employees;
delete from employees where employee_id = 103;
select employee_id, last_name, salary, department_id from employees;
rollback;

使用子查询删除行

select employee_id, last_name, salary, department_id from employees;
delete from (select * from employees where department_id = 90);
select employee_id, last_name, salary, department_id from employees;
rollback;

用where中的特定子查询来删除特定的行

select employee_id, last_name, salary, department_id from employees2;
delete from employees2
 where department_id in
       (select department_id
          from departments
         where department_name = 'Executive');
select employee_id, last_name, salary, department_id from employees2;
rollback;

merge语句

merge语句可以按条件获取要更新或插入到表中的数据行，然后从１个或多个源头对表进行更新或向表中插入行。

merge <hint>
into <table_name>
using <table_view_or_query>
on (<condition>)
when matched then <update_clause>
delete <where_clause>
When not matched then <insert_clause>
[log erros <log_errors_clause> <reject limit <integer | unlimited> ]


drop table dept60_bonuse;

create table dept60_bonuse(
employee_id number,
bonus_amt number
);

delete from dept60_bonuse;
insert into dept60_bonuse values(103,0);
insert into dept60_bonuse values(104,122);
insert into dept60_bonuse values(105,0);


select * from dept60_bonuse;
select employee_id,salary,department_id from employees where department_id=60;

merge into dept60_bonuse b
using (
select employee_id,salary,department_id from employees where department_id=60
) e
on (b.employee_id = e.employee_id)
when matched then
  update set b.bonus_amt=e.salary*0.2
  where b.bonus_amt=0
  delete where (e.salary>7500)
when not matched then
  insert (b.employee_id,b.bonus_amt)
  values (e.employee_id,e.salary*0.1)
  where (e.salary>7500);
  
  
select * from dept60_bonuse;
select employee_id,salary,department_id from employees where department_id=60;
rollback;

参考

Begin oracle SQL
SQL reference guide

最后编辑于：2017.12.03 07:19:08

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,607评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,047评论 2赞 379
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,496评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,405评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,400评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,479评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,883评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,535评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,743评论 1赞 295
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,544评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,612评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,309评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,881评论 3赞 306
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,891评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,136评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,783评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,316评论 2赞 342