吐血整理-Oracle总结笔记全【文末含PDF】

oracle.jpg

一. 查询,排序,限制查询行

1. as (列的别名,可省略)

select last_name as name,salary*12 as total  //别名关键字as可以省略,默认大写
from s_emp;

select last_name as name,salary*12 as “total” //原样用双引号

2. || (列的连接)

select first_name||' '||last_name name    //连接两列
NAME
--------------------
Carmen Velasquez

3.sqlplus命令( plsql 语言)

column name format a20    //sqlplus命令 不进入缓存


//sqlplus可以简写  标准的sql不可以简写
desc s_dept
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(7)
 NAME                                      NOT NULL VARCHAR2(25)
 REGION_ID                                          NUMBER(7)
 
 
 1.describe
获取表的结构信息(列名、非空约束、数据类型)
 SQL> desc dual

 Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
 DUMMY(哑巴-哑表)                                     VARCHAR2(1)
 
2.数据类型
        数值类型:number(p,s)
        p:有效位数,s:小数点后的取值位数
        
   字符串类型:
        char(p):定长字符串,char(10)->'briup'(不够的地方不上空白)
        varchar(p):变长字符串,varchar(10)->'briup'(标准的,所有的关系型数据库都可以用)
        varchar2(p):也是变长字符串(只在Oracle数据库中可以用)最多存放p个
        
   日期类型:date :存放年,月,日,小时,分,秒(没有位数限制)
3.save:将缓冲区内容写入文件
    save D:\oracle\my.sql
    
    //list缓冲区,存放上一条语句
    //save:可以将缓冲区里的sql语句能被存放到指定文件中
    SQL> l
      1  select dept_id,title distinct
      2* from s_emp
    SQL> list
      1  select dept_id,title distinct
      2* from s_emp
    SQL> save D:\my.sql
    已创建 file D:\my.sql
4.start:执行sql脚本,并将内容写入缓冲区
    start D:\oracle\my.sql
5.@功能同start
    @ D:\oracle\my.sql
6./ 
作用:执行缓冲区中的sql命令(即上一次执行的命令)
7.exit:退出sqlplus工具

4. nvl ( 列 , 值 )函数

nvl(列,0)函数。此列中的空值用指定的值(0)替代

<提成率 -销售人员才有>

(1)Oracle中空值用null表示,但打印的报表中什么都不显示

(2)空值是一个不知道,不确定的东西,没法进行比较(null=!0,null=!’’,null=!null)

(3)算术表达式中有空值参与运算,整体表达式的结果为空

(4)nvl函数可以将空值用指定值来替代

(5)nvl两个参数的数据类型必须匹配

查询出每个销售人员的年薪
select first_name||' '||last_name name,
            title,salary*12*(1+nvl(commission_pct,0)/100) total
from s_emp;

NAME                 TITLE                     TOTAL
-------------------- -------------------- ----------
Carmen Velasquez     President                 30000
LaDoris Ngao         VP, Operations            17400
Midori Nagayama      VP, Sales                 16800
Mark Quick-To-See    VP, Finance               17400
Audry Ropeburn       VP, Administration        18600
Molly Urguhart       Warehouse Manager         14400

5. distinct (去除重复行)

select语句中,如果没有出现函数时,distinct关键字紧跟在select后面,表示对后面的所有的列进行限制

select distinct name //distinct去除重复行
from s_dept;

NAME
--------------------
Administration
Finance
Operations
Sales

//限制两个列的组合不能重复
select distinct dept_id,title  
from s_emp;

//错,关系型数据库不可以单独将一个列去除重复
select dept_id,distinct title
from s_emp;

6. order by(排序)

order by (排序)后跟

​ 跟列名,
​ 跟列的别名,
​ 跟表达式(带列表的),
​ 跟数字,表示select列表的位置(编号)

asc 升序(默认值); desc 降序

注意:asc 空值在后面,desc 空值在前面

//按员工的工资升序排列
select last_name,salary
from s_emp
order by salary;
LAST_NAME                                              SALARY
-------------------------------------------------- ----------
Newman                                                    750
Patel                                                     795
Patel                                                     795

select last_name,salary
from s_emp
order by salary desc;
LAST_NAME                                              SALARY
-------------------------------------------------- ----------
Velasquez                                                2500
Ropeburn                                                 1550
Nguyen                                                   1525

select last_name,commission_pct
from s_emp
order by commission_pct desc;
LAST_NAME                                          COMMISSION_PCT
-------------------------------------------------- --------------
Markarian
Dumas                                                        17.5
Nguyen                                                         15
Giljum                                                       12.5

7. where (限制条件)

(where子句中不能使用别名)

// where 限制取出条件的数据
select last_name,salary,dept_id
from s_emp
where dept_id=41;

LAST_NAME                                              SALARY    DEPT_ID
-------------------------------------------------- ---------- ----------
Ngao                                                     1450         41
Urguhart                                                 1200         41
Maduro                                                   1400         41
Smith                                                     940         41

//放到单引号''里的严格区分大小写
select last_name,salary,dept_id
from s_emp
where last_name='smith';
查询结果:未选定行(不存在)

select last_name,salary,dept_id
from s_emp
where last_name='Smith';
LAST_NAME                   SALARY    DEPT_ID
----------------------- ---------- ----------
Smith                          940         41

between

// between A AND B 
(原则:
    1.A必须小于B
    2.闭区间
)

select last_name,dept_id,salary
from s_emp
where salary between 1000 and 2000;
LAST_NAME                                             DEPT_ID     SALARY
-------------------------------------------------- ---------- ----------
Ngao                                                       41       2000
Nagayama                                                   31       1400
Quick-To-See                                               10       1450
Ropeburn                                                   50       1550
Urguhart                                                   41       1200
Menchu                                                     42       1250
Biri                                                       43       1100
Catchpole                                                  44       1000

in

// in 给定一个列表范围,从中匹配任意一个
select last_name,salary,dept_id
from s_emp
where dept_id in(41,43,45);
LAST_NAME                                              SALARY    DEPT_ID
-------------------------------------------------- ---------- ----------
Ngao                                                     1450         41
Urguhart                                                 1200         41
Biri                                                     1100         43
Havel                                                    1307         45
Maduro                                                   1400         41
Smith                                                     940         41
Newman                                                    750         43
Markarian                                                 850         43
Dancs                                                     860         45
Schwartz                                                 1100         45

已选择10行。

% ,_(模糊查询)

//模糊查询:找出员工名字,以M开头
%-代表0或多个字符
_-代表单一字符

select last_name,salary
from s_emp
where last_name like 'M%';
LAST_NAME                                              SALARY
-------------------------------------------------- ----------
Menchu                                                   1250
Magee                                                    1400
Maduro                                                   1400
Markarian                                                 850

select last_name,salary
from s_emp
where last_name not like 'M%';
LAST_NAME                                              SALARY
-------------------------------------------------- ----------
Velasquez                                                2500
Ngao                                                     1450
Nagayama                                                 1400
Quick-To-See                                             1450
Ropeburn                                                 1550

escape ' ' (转义字符)

//转义字符
syso("\"");输出”  \失去原来预先定义好的具有特殊意义的字符,原样输出
Oracle自定义转移字符escape:用escape来定义\为转义字符

select last_name,salary
from s_emp
where last_name like '\_a%' escape '\';

is null / is not null / !=

// is null 找出commission_pct为空的
select last_name,salary
from s_emp
where commission_pct is null;
LAST_NAME                                              SALARY
-------------------------------------------------- ----------
Velasquez                                                2500
Ngao                                                     1450
Nagayama                                                 1400
Quick-To-See                                             1450
Ropeburn                                                 1550
Urguhart                                                 1200
Menchu                                                   1250
Biri                                                     1100

select last_name,salary
from s_emp
where commission_pct is not null;

select last_name,salary,dept_id
from s_emp
where dept_id!= 41;
LAST_NAME                                              SALARY    DEPT_ID
-------------------------------------------------- ---------- ----------
Velasquez                                                2500         50
Nagayama                                                 1400         31
Quick-To-See                                             1450         10
Ropeburn                                                 1550         50

all / and / or (优先级减小)

//salary>=1000只限制dept_id=44.括号可以去掉
select last_name,salary,dept_id
from s_emp
where (salary>=1000
AND dept_id=44)
OR dept_id=42;
LAST_NAME                                              SALARY    DEPT_ID
-------------------------------------------------- ---------- ----------
Menchu                                                   1250         42
Catchpole                                                1300         44
Nozaki                                                   1200         42
Patel                                                     795         42

8.整合

//选出来所需数据再进行排序
//先where后oreder ,对不满足条件的剔除,满足条件的order by 排序
select last_name,salary,dept_id
from s_emp
where (salary>=1000
AND dept_id=44)
OR dept_id=42
order by salary;
select title,salary
from s_emp
where (dept_id=41 and title='Warehouse Manager') or ( dept_id=43 and title='Stock Clerk')
or (title not in('Warehouse Manager','Stock Clerk') and salary>=2000);

二. 函数(单行函数,select和where)

---------------Character--------------

1)lower('SQL Course')

select lower(last_name)
from s_emp;

LOWER(LAST_NAME)
--------------------------------------------------

velasquez
ngao

select last_name,salary,dept_id
from s_emp
where lower(last_name)='smith';
LAST_NAME                                              SALARY    DEPT_ID

-------------------------------------------------- ---------- ----------

Smith                                                     940         41

SQL> colum last_name format a20
SQL> /

LAST_NAME                SALARY    DEPT_ID

-------------------- ---------- ----------

Smith                       940         41

2).upper('SQL Course')

3).initcap('SQL Course')<单词首字母大写,其他小写>

4).concat('Good','Morning')

5).substr('String',1,3)

取出员工名字的后两个字符

select substr(last_name,length(last_name)-1,2)
from s_emp;

SUBSTR(LAST_NAME
----------------
ez
ao
ma

6).length('String')

---------------Number------------------

7).round(number,进位规则数)

默认第二位为0,
若第二个参数为0:对小数点后一位按位进1后只保留整数部分
若第二个参数为正数n:对小数点后n+1位四舍五入后只保留小数点后n位
若第二个参数为负数-n:对整数部分第n位四舍五入后保留n+1及大于n+1位的,其余位清0
select round(45.67) from dual;    46
select round(45.67,1) from dual;  45.7
select round(45.67,2) from dual;   45.67
select round(45.67,-1) from dual;  50
select round(45.67,-2) from dual;  0
select round(55.67,-2) from dual;  100

8).trunc(number,舍位规则数)<舍位/截断>

截断,如果第二个参数为负数,表示小数点虚拟左移相应的位数,最后将虚拟小数点后全部清零
select trunc(45.67) from dual;     45
select trunc(45.67,1) from dual;   45.6
select trunc(45.67,2) from dual;   45.67
select trunc(45.67,-1) from dual; 40
select trunc(45.67,-2) from dual;  0
select trunc(55.67,-2) from dual;   0

9.mod(除数,被除数)

取余:1500/400=3...300
select mod(100,400) from dual; 300

-----------------Date--------------------

YYYY:4位数字的年

MM:2位数字的月

DD:2位数字的天

YEAR:年的英文全拼

MONTH:月的英文全拼

DDSP:天的英文全拼,基数词

DDSPTH:天的英文全拼,序数词

D:1位数字的星期,0表示星期天

DDD:3位数字的天,一年中的第几天

DAY:星期的英文全拼

DY:星期的英文简写,3个字母

HH24:小时的24制表示,2位数字的小时

MI:2位数字的分钟

SS:2位数字的秒

将当前会话语言改成英文的,Oracle默认日期显示格式:DD-MON-YY
alter session set nls_language='american';

 SQL> desc dual
 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY(哑巴-哑表)                                     VARCHAR2(1)
 
 DUMMY(哑巴-哑表) 不是一个类 是一个函数
 sysdate是一个无参数的函数看起来像列一样,称之为伪列,同systimestamp 
 
 伪列还有rownum,rowid

select rownum,rowid,last_name,salary
from s_emp;
    ROWNUM ROWID              LAST_NAME                SALARY
---------- ------------------ -------------------- ----------
         1 AAAD2ZAABAAAKhyAAA Velasquez                  2500
         2 AAAD2ZAABAAAKhyAAB Ngao                       1450
         3 AAAD2ZAABAAAKhyAAC Nagayama                   1400 

sysdate(伪列)

获取当前时间(小时分秒都被隐藏了)

select sysdate
from dual;       
SYSDATE
------------
17-OCT-19

【时间和数值】
当前+1,-1天(是以天为基本单位)       
select sysdate-1,sysdate,sysdate+1
from dual;     
SYSDATE-1    SYSDATE      SYSDATE+1
------------ ------------ ------------
16-OCT-19    17-OCT-19    18-OCT-19

当前时间往后推1小时(小时未被显示)   
select sysdate-1/24,sysdate+1/24
from dual;
SYSDATE-1/24 SYSDATE+1/24
------------ ------------
17-OCT-19    17-OCT-19

【时间和时间】
到今天为止员工入职的天数?       
select last_name,start_date,sysdate-start_date days
from s_emp;

LAST_NAME            START_DATE         DAYS
-------------------- ------------ ----------
Velasquez            03-MAR-90     -25704.92(千年虫问题)      
Ngao                 08-MAR-90    10815.4343
Nagayama             17-JUN-91    10349.4343
Quick-To-See         07-APR-90    10785.4343

systimestamp(伪列)

显示出比sysdate更全面的时间戳

select systimestamp
from dual;   
SYSTIMESTAMP
---------------------------------------
19-10月-19 10.18.11.529000 上午 +08:00

(当前+1,-1天(是以天为基本单位)
select systimestamp+1 
from dual; 
SYSTIMESTAMP+1
--------------
20-10月-19
 
当前时间往后推1小时(小时未被显示)
select systimestamp+1/24
from dual; 
SYSTIMESTAMP+1
--------------
19-10月-19

rownum(伪列)

(行号)使用注意:只能1~n

​ <、<=无限制

​ =、>=只对1有意义

​ >无意义

rowid(伪列)

行地址(没有 order by指定排序规则时,默认按每行数据在底层的地址rowid排序)

10.months_between('01-SEP-95',11-JAN-94)

两日期之间相差多少个月(先小后大返回负数)

select months_between('01-SEP-95','11-JAN-94')
from dual;

11.add_months('11-JAN-94',6)

在当前日期的基础上,往后退6个月

12.next_day('01-SEP-95','FRIDAY')

找出95年09月01号之后最近的星期五是几号

next_day('01-SEP-95','FRIDAY') -7则是最近的星期五的前一个星期五

13.last_day('01-SEP-95')

找出某个月(95年的09月)的最后一天是多少号

15.round()

round('25-MAY-95','MONTH') 天往月里进(逢16) 01-JUN-95

select round (to_date('16-MAY-95','DD-MM-YY'),'month')
from dual;

ROUND(TO_DAT
------------
01-JUN-95

round('25-MAY-95','YEAR') 月往年里进(逢7) 01-JAN-95

select round (to_date('16-JUL-95','DD-MM-YY'),'year')
from dual; 

ROUND(TO_DAT
------------
01-JAN-96

16.trunc(舍位)

trunc('25-MAY-95','MONTH') 对天进行舍位到1号 01-MAY-95

trunc('25-JUL-95','YEAR' ) 对月进行舍位到1月 01-JAN-95

--------------Conversion-------------

17.tochar(‘数值或日期’,‘格式’)

9 不会强制补

0 会强制补0

$ 指定货币符号

L 默认本地货币符号¥(可设置)

.

,

日期格式

YYYY:4位数字的年

MM:2位数字的月

DD:2位数字的天

YEAR:年的英文全拼

MONTH:月的英文全拼

DDSP:天的英文全拼,基数词

DDSPTH:天的英文全拼,序数词

D:1位数字的星期,0表示星期天

DDD:3位数字的天,一年中的第几天

DAY:星期的英文全拼

DY:星期的英文简写,3个字母

HH24:小时的24制表示,2位数字的小时

MI:2位数字的分钟

SS:2位数字的秒

查询当前时间,并按指定格式显示
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
--------------------------------------
2019-10-17 11:13:18


select to_char(sysdate,'YYYY-MM-DDSP DAY DY')
from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDSPDAYDY')
--------------------------------------------------------------------------------
2019-10-SEVENTEEN THURSDAY  THU


select to_char(sysdate,'YYYY-MM-DDSPTH D DDD AM')
from dual;        
TO_CHAR(SYSDATE,'YYYY-MM-DDSPTHDDDDAM')
--------------------------------------------------------
2019-10-SEVENTEENTH 5(这个礼拜的第五天) 290(今年的第290天)  AM


“”原样输出,
fm将前导的无用的0剔除掉
select to_char(sysdate,'fmYYYYMMDD HH24:MI:SS AM "YYYYMMDD"')
from dual;     
TO_CHAR(SYSDATE,'FMYYYYMMDDHH24:MI:SSAM"YYYYMMDD"')
----------------------------------------------------------
20191017 11:19:55 AM YYYYMMDD

select last_name,to_char(salary,'$999,999.99')
from s_emp;
LAST_NAME            TO_CHAR(SALARY,'$999,999
-------------------- ------------------------
Velasquez               $2,500.00
Ngao                    $1,450.00
Nagayama                $1,400.00
Quick-To-See            $1,450.00

18.to_number(‘字符串’ )

把字符串形式的数值转换成数值类型

19.to_date(’字符串‘,‘格式’)

select round (to_date('16-JUL-95','DD-MM-YY'),'year')
from dual; 

ROUND(TO_DAT
------------
01-JAN-96
      

三. 多表查询

解决两表关联,即A表中id(外键)和B表中id(主键)关联中的问题

查询显示A表和B表条件关联的数据 和 A表里B表没有的数据

查询显示A表和B表条件关联的数据 和 B表里A表没有的数据

(查询显示A表和B表条件关联的数据 和 A表里B表没有的数据) 与 (查询显示A表和B表条件关联的数据 和 B表里A表没有的数据) 之间的 1.<u>交集(intersect)</u>, 2.<u>并集(nuion)-重复内容取1次</u>, 3.<u>并集(nuion all)-重复内容取2次-即全组合</u>, 4.(minu)<u>第一个集合减去和第二个集合相交的内容,即得到第一个里面第二个没有的内容</u>

1.笛卡尔积

“笛卡尔乘积”:多个表中数据记录的全组合

避免:给出正确的连接条件

查询员工名、员工部门名?(两表通过外键和主键查信息)
select s_emp.last_name,s_dept.name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id;
LAST_NAME            NAME
-------------------- --------------------
Velasquez            Administration
Ngao                 Operations
。。。
25 rows selected.

select * from s_dept;  // 12 rows
select * from s_emp;  // 25 rows
select s_emp.last_name,s_dept.name
from s_emp,s_dept;
300 rows selected.(12*25 rows-->笛卡尔乘积) 

2.等连接,非等连接

3.内连接,外连接(*)

1)内连接

根据指定的连接条件进行连接查询,满足连接条件的数据才会出现在结果集

select ... from A (inner join) B (on) 条件;

Oracle写法:

select ... from A,B where A.id=B.id;

select e.last_name,d.name
from s_emp e inner join s_dept d
on e.dept_id=d.id;
------------------
...
25 rows selected.
(同笛卡尔积里的第一个,同Oracl写法结果一样)

2)外连接:

在内连接的基础上,将某个连接表不符合连接条件的记录加入结果集

①左外连接

select ... from A (left outer join) B (on) 条件;

Oracle写法:

select ... from A,B where A.id=B.id(+);

即把左边表A当作基准,在内连接操作方式下,即遇到符合连接条件的记录加入结果集,遇到A中不符合条件的记录也加入(因为它是基准),但对应B表的记录用null填充

A表s_emp的dept_id(外键)和B表s_dept的id(主键)一一对应
向A表中插入一条数据,此数据在B表中没有对应的数据
即该员工不属于任何部门
用内连接时查不出来的,
select e.last_name,d.name
from s_emp e inner join s_dept d
on e.dept_id=d.id;
LAST_NAME            NAME
-------------------- --------------------
。。。
Schwartz             Operations
25 rows selected.
此时用外连接
即在内连接结果的基础上,
把A表中不满足A.dept_id=B.id条件的数据也查询出来
insert into s_emp(id,last_name)
values(26,'briup');

select e.last_name,d.name
from s_emp e left outer join s_dept d
on e.dept_id=d.id;
LAST_NAME            NAME
-------------------- --------------------
。。。
Schwartz             Operations
briup
26 rows selected.

oracle写法:(+)放到值为空的那一方
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+);

②右外连接

select ... from A (right outer join) B (on) 条件;

Oracle写法:

select ... from A,B where A.id(+)=B.id;

同理左外连接
insert into s_dept(id,name)
values(60,'Develepment');

select e.last_name,d.name
from s_emp e right outer join s_dept d
on e.dept_id=d.id;
LAST_NAME            NAME
-------------------- --------------------
...
Schwartz             Operations
                     Development
26 rows selected.

oracle写法:(+)放到值为空的那一方
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id;

③全外连接

select ... from A (full outer join) B (on) 条件;

Oracle写法(利用集合操作符):

select ... from A,B where A.id(+)=B.id(+);==》错

select ... from A,B where A.id=B.id(+)

union

select ... from A,B where A.id=B.id(+);==>对

即把右边表B当作基准,在内连接操作方式下,遇到符合连接条件的记录加入结果集,遇到B中不符合条件的记录也加入(因为它是基准),但对应A表的记录用null填充

select e.last_name,d.name
from s_emp e full outer join s_dept d
on e.dept_id=d.id;
LAST_NAME            NAME
-------------------- --------------------
...
Schwartz             Operations
briup
                     Development
27 rows selected.



oracle错误写法:
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id(+);
oracle正确写法:
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id
union
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+);
LAST_NAME            NAME
-------------------- --------------------
。。。
Velasquez            Administration
briup
                     Development
27 rows selected.

4.自连接

将自身表的一个镜像当作另一个表来对待,一般有自身表中的列关联自身表中的其它列

1571456720727

查询员工,经理的名字

select worker.last_name,manager.last_name
from s_emp worker,s_emp manager
where worker.manager_id=manager.id;
LAST_NAME  LAST_NAME
---------- ----------
Ngao       Velasquez
Nagayama   Velasquez
Quick-To-S Velasquez
ee

Ropeburn   Velasquez
Urguhart   Ngao
...
已选择24行。


查询员工,经理的名字(即便员工没有经理)--自连接的基础上使用了外连接

select worker.last_name,manager.last_name
from s_emp worker,s_emp manager
where worker.manager_id=manager.id(+);
LAST_NAME  LAST_NAME
---------- ----------
Velasquez
Ngao       Velasquez
Nagayama   Velasquez
Quick-To-S Velasquez
ee

Ropeburn   Velasquez
Urguhart   Ngao
Menchu     Ngao
Biri       Ngao

已选择26行。
查询员工,员工部门 ,员工经理,员工 经理部门的名字
select worker.last_name,manager.last_name,wdept.name,mdept.name
from s_emp worker,s_emp manager,s_dept wdept,s_dept mdept
where worker.manager_id=manager.id 
and worker.dept_id=wdept.id
and manager.dept_id=mdept.id;
LAST_NAME  LAST_NAME  NAME       NAME
---------- ---------- ---------- ----------
Ngao       Velasquez  Operations Administra
                                 tion

Nagayama   Velasquez  Sales      Administra
                                 tion

Quick-To-S Velasquez  Finance    Administra
ee                               tion

Ropeburn   Velasquez  Administra Administra
                      tion       tion

LAST_NAME  LAST_NAME  NAME       NAME
---------- ---------- ---------- ----------

Urguhart   Ngao       Operations Operations
Menchu     Ngao       Operations Operations
Biri       Ngao       Operations Operations
Catchpole  Ngao       Operations Operations
Havel      Ngao       Operations Operations
Magee      Nagayama   Sales      Sales
Giljum     Nagayama   Sales      Sales
Sedeghi    Nagayama   Sales      Sales
Nguyen     Nagayama   Sales      Sales
Dumas      Nagayama   Sales      Sales

LAST_NAME  LAST_NAME  NAME       NAME
---------- ---------- ---------- ----------
Maduro     Urguhart   Operations Operations
Smith      Urguhart   Operations Operations
Nozaki     Menchu     Operations Operations
Patel      Menchu     Operations Operations
Newman     Biri       Operations Operations
Markarian  Biri       Operations Operations
Chang      Catchpole  Operations Operations
Patel      Catchpole  Sales      Operations
Dancs      Havel      Operations Operations
Schwartz   Havel      Operations Operations

已选择24行。

5.集合操作符

union

两个集合的并集,重复内容取1次。26 union 26 其中25重复-->26+26-25=25+1+1=27

union all

全组合,即两个集合的并集,重复内容取2次。 26 union 26 其中25重复-->26+26=52

minu

第一个集合减去和第二个集合相交的内容,

即得到第一个里面第二个没有的内容 -->1

intersect

两个集合的交集->25

四. 组函数(多行函数,聚合函数)

avg(),sum(),min(),max(),count()*

select avg(salary),sum(salary),min(salary),max(salary)
from s_emp;
AVG(SALARY) SUM(SALARY) MIN(SALARY) MAX(SALARY)
----------- ----------- ----------- -----------
    1255.08       31377         750        2500

max和min除了可以操作数字还可以操作字符串类型(按ASSIC值的先后顺序)
select min(last_name),max(last_name)
from s_emp;
MIN(LAST_NAME)        MAX(LAST_NAME)
------------        ------------
Biriup                briup
-----------------------------------count------------------------------------------------------------
count(*) 统计表中数据的行数(最终还是按列进行匹配)(有多个列按嵌套,层层递进)
count(1) -->count(PK)速度更快    
select count(*)
from s_emp;
  COUNT(*)
----------
        26
        
count也可以跟列名(统计列中非空数据的列数)
select count(commission_pct)
from s_emp;
COUNT(COMMISSION_PCT)
---------------------
                   5

查询部门id,每个部门员工数量(降序)<order by 中可以出现组函数>
select dept_id,count(*)
from s_emp
group by dept_id
order by count(*) desc;
   DEPT_ID   COUNT(*)
---------- ----------
        41          4
        42          3
        45          3
        43          3
        50          2
        44          2
        31          2
        34          2
        33          1
        35          1
        10          1

   DEPT_ID   COUNT(*)
---------- ----------
                    1
        32          1

已选择13行。


select dept_id,count(*)
from s_emp;
错。要符合单一的组函数则,
即第一范式,行列交叉处要有一个单一的值 
dept_id---26条
count(*)---1条

在select语句中,
如果出现了组函数,所有没有放到组函数中的列必须放到group by子句中,
否则会生成不满足关系型数据库的报表

select dept_id,title,count(*)
from s_emp
group by dept_id,title;

select dept_id,title,count(*)
               *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

//先以部门id分组,在同部门里再以title分组,(有多个列按嵌套,层层递进)
即查出同部门中职称相同的人的数量
select dept_id,title,count(*)
from s_emp
group by dept_id,title;

查询每个部门的平均薪资
平均薪资大于两千
(    where avg(salary)>2000---》错。where 里不能出现组函数,
    要对分组后的条件进行限制使用having子句
)
select  dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>2000;

   DEPT_ID AVG(SALARY)
---------- -----------
        50        2025



整合

在薪资大于1500的雇员中,按部门分组,查询平均工资小于2000的部门的平均工资,并按部门平均工资排序排序
   
select dept_id,name,avg(salary)
from s_emp,s_dept
where salary>1500
group by dept_id,name
having avg(salary)<2000
order by avg(salary); 

DEPT_ID AVG(SALARY)
       33        1515
    34        1525
    
    
在select语句(包含所有子句,不只是select列表,还包含组函数)中,
如果出现了组函数,所有没有放到组函数中的列,必须放到 group by 子句中,
否则会形成不满足关系型数据库的报表    


子查询-(from,where,having)

把select语句嵌套在其他sql语句中

where子句中不能使用别名

where,having都是返回表中的一行数据

where可以返回多行的表数据

子查询当返回一行数据,直接进行比较

查询smith所在部门员工的信息
1.找出smith所在部门
2.查询满足条件的员工信息
select dept_id
from s_emp
where lower(last_name)='smith';
   DEPT_ID
----------
        41
        
select last_name,salary,dept_id
from s_emp
where dept_id=(
    select dept_id
    from s_emp
    where lower(last_name)='smith'
);
LAST_NAME         SALARY    DEPT_ID
----------------- ---------- ----------
Ngao                    1450         41
Urguhart                1200         41
Maduro                  1400         41
Smith                    940         41
(写法结果同上)(子查询的中间条件即()里的,不允许加 order by排序,是没有意义的)
where (
    select dept_id
    from s_emp
    where lower(last_name)='smith'
)=dept_id;


查询员工工资低于平均平均工资的员工信息
select last_name,salary,dept_id
from s_emp
where salary<(
    select avg(salary)
    from s_emp
);
LAST_NAME                                              SALARY    DEPT_ID
-------------------------------------------------- ---------- ----------
Urguhart                                                 1200         41
Menchu                                                   1250         42
Biri                                                     1100         43
Smith                                                     940         41
...






子查询当返回多行数据,不能直接进行比较,则用in

查询和41号部门任意员工工资相等的员工的信息
(where子句中可以出现子查询)
select salary
from s_emp
where dept_id=41;
    SALARY
----------
      1450
      1200
      1400
       940
       
select last_name,salary,dept_id
from s_emp
where salary in (
    select salary
    from s_emp
    where dept_id=41
);
LAST_NAME                                              SALARY    DEPT_ID
-------------------------------------------------- ---------- ----------
Dumas                                                    1450         35
Quick-To-See                                             1450         10
Ngao                                                     1450         41
Nozaki                                                   1200         42
Urguhart                                                 1200         41
Maduro                                                   1400         41
Magee                                                    1400         31
Nagayama                                                 1400         31
Smith                                                     940         41


查询平均工资大于32号部门平均工资的部门id ,部门平均工资
(heaving子句中可以出现子查询)
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(
    select avg(salary)
    from s_emp
    where dept_id=32
);
   DEPT_ID AVG(SALARY)
---------- -----------
        50        2025
        33        1515


查询每个部门最低工资,最低工资员工名,部门名
( from子句中可以出现子查询)
(不同于其他子句,,表里面没有 minsal这个列,此子查询相当于创建了一个表,并起表别名为t)
(三张表,至少给出两个条件避免笛卡尔乘积)
(前面的子查询,中间结果不能加order排序 而此处 from 子句 可以使用 order by)
select e.dept_id,t.minsal,e.last_name,d.name
from(
    select min(salary) minsal,dept_id
    from s_emp
    group by dept_id
)t,s_emp e,s_dept d
where t.dept_id=e.dept_id 
and e.dept_id=d.id
and t.minsal=e.salary;

   DEPT_ID     MINSAL LAST_NAME            NAME
---------- ---------- -------------------- --------------------
        31       1400 Nagayama             Sales
        10       1450 Quick-To-See         Finance
        50       1550 Ropeburn             Administration
        31       1400 Magee                Sales
        32       1490 Giljum               Sales
        33       1515 Sedeghi              Sales
        35       1450 Dumas                Sales
        41        940 Smith                Operations
        42        795 Patel                Operations
        43        750 Newman               Operations
        44        800 Chang                Operations

   DEPT_ID     MINSAL LAST_NAME            NAME
---------- ---------- -------------------- --------------------
        34        795 Patel                Sales
        45        860 Dancs                Operations

13 rows selected.


查询和simth相同部门并且相同职称的员工的信息
select last_name,salary,dept_id,title
from s_emp
where dept_id=(
    select dept_id
    from s_emp
    where lower(last_name)='smith'
)
and title=(
    select title
    from s_emp
    where lower(last_name)='smith'
)
或
select last_name,salary,dept_id,title
from s_emp
where (dept_id,title)=(
    select dept_id,title
    from s_emp
    where lower(last_name)='smith'
);

LAST_NAME      SALARY    DEPT_ID TITLE
---------- ---------- ---------- -----------
Maduro           1400         41 Stock Clerk
Smith             940         41 Stock Clerk

(分页查询)

查询表中10到13条数据?

表中100条数据,每页显示10条,查询第二页数据?

方法一:利用集合操作符

select rownum,last_name,salary
from s_emp
where rownum<=13
minus
select rownum,last_name,salary
from s_emp
where rownum<=10;

    ROWNUM LAST_NAME      SALARY
---------- ---------- ----------
        11 Magee            1400
        12 Giljum           1490
        13 Sedeghi          1515


方法二:使用子查询(先做了一张符合要求的表 再从表里取出限定条件的表)

select r,last_name,salary
from(
    select rownum r,last_name,salary
    from s_emp
)
where r between 11 and 13;
         R LAST_NAME      SALARY
---------- ---------- ----------
        11 Magee            1400
        12 Giljum           1490
        13 Sedeghi          1515
        
        
方法三:子查询的基础上,更快的一种方法

select r,last_name,salary
from(
    select rownum r,last_name,salary
    from s_emp
    where rownum <=13
)
where r >10;
         R LAST_NAME      SALARY
---------- ---------- ----------
        11 Magee            1400
        12 Giljum           1490
        13 Sedeghi          1515
以上条件下,按行号升序,按工资降序排列(排序在内层排序,中层操作rownum)
select r,last_name,salary
from(
    select rownum r,last_name,salary
    from (
        select last_name,salary
        from s_emp
        order by salary desc
    ) where rownum <=20  
    order by rownum asc
)
where r >10;
         R LAST_NAME      SALARY
---------- ---------- ----------
        11 Magee            1400
        12 Maduro           1400
        13 Havel            1307
        14 Catchpole        1300
        15 Menchu           1250
        16 Urguhart         1200
        17 Nozaki           1200
        18 Biri             1100
        19 Schwartz         1100
        20 Smith             940

已选择10行。




select count(*)
from S_EMP,(
    select i ii,n,REGION_ID,id idd
        from s_dept,(
                select id i,name n 
                from s_region
        )
    where i=REGION_ID
)
where idd=DEPT_ID;

25

五.数据库设计(读懂)

1.E - R图(实体关系图,Entity relationship diagram)

Recursive -递归

①实体名--》表名

​ 主表(父表):没有外键的表(先建)

​ 从表(子表):有外键的表(后建)

②属性名--》列名

​ #主要标识,唯一识别

​ *强制标识,出现必须右值,对应数据库not null约束

​ o可选标识,可以有值,也可以没有值

​ #*一般表示主键

③关__系--》反映实体内部或实体之间的关联,对应数据库中的关联约束

虚线表示可有可无;实线表示必须要有

​ 1对1关联:都是虚线、都是实线,外键建在哪边都可以;半虚半实,建在实的一方

​ 1对多关联:要在多的一方建外键(指向一的一方的主键)

​ 多对多关联:需要引入“桥表”,将多对多关系,转换为2个1对多关系

​ UID Bar:将远的一方实体的主键,拿到近的一方实体中,作为联合主键

2.范式

可以保证数据的冗余度比较低,易于维护,在实际中并不是把所有范式都遵循了,操作过于繁琐

1).第一范式:所有的属性必须单值(行列交叉处一个值)<必须满足>

2).第二范式:所有的属性依赖于主属性 <可有可无>

3).第三范式:除了主属性,其它属性间没有依赖关系 <可有可无>

3.完整性约束

①主键(PK):

​ 1)唯一标识表中记录

​ 2)取值非空、唯一

​ 3)一个表上,主键只能有一个

​ 4)主键可以为多列,称为联合主键

​ 5)主键分自然主键、逻辑主键

​ 自然主键:主键有商业意义

​ 逻辑主键:主键没有商业意义,低耦合,仅起到唯一识别作用

②外键(FK):

​ 1)建立表和表间的关联关系

​ 2)所关联的列要求是PK或UK中的值,或者NULL

​ 取值必须是管关联列的取值或者是null值

​ 3)一个表上,外键可以有多个

​ 4)外键可以为多列,称为联合外键

​ 5)外键可以关联其它表,也可以关联自身表

4.表的实例图

六.建表(DDL)

1.建表语法

①建表

schema:一个用户连同其下的所有对象,称为一个schema
schema的名字就是用户名

show user
USER 为 "BRIUP"  //当前用户名字

create table briup.student(
id number(6),
name varchar2(25),
age number(3)
);

create table student(
id number(7),
name varchar2(25) default user,
age number(3)
);

desc student
 名称          是否为空? 类型
 ------------ -------- ---------------
 ID                    NUMBER(7)
 NAME                  VARCHAR2(25)
 AGE                   NUMBER(3)
 
insert into student(id)
values(100);
已创建 1 行。

select * from student;
        ID NAME                   AGE
---------- --------------- ----------
       100 BRIUP(默认是用户名)

drop table student;
表已删除。


②数据类型

number,number(p,s)

char(减少 磁盘 碎片,以此提升效率)、varchar、varchar2

date

CLOB:Charactor Large OBject,字符型大对象,如存一部小说

BLOB:Binary Large OBject,二进制大对象,如存一集电影

③约束

约束有名字,默认是SYS_Cn

约束可以加在列级别、也可以加在表级别

列级别约束:约束只涉及一个列

表级别:约束涉及多个列

(1)not null(列级别)

注意:not null只能加在列级别上!没联合为空的概念

desc user_constraints;  和约束相关的数据字典表
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)
 
 
查看student表上有什么约束相关名
select  constraint_name
from user_constraints
where lower(table_name)='student';
CONSTRAINT_NAME
-------------------
SYS_C004542
即自己不指定约束名,系统会自动分配一个,以SYS_C+一长串数字

自己定义约束名
create table BRIUP.student(
id number(7) constraint student_id_nn not null,
name varchar2(25) default user,
age number(3)
);
查看
select  constraint_name
from user_constraints
where lower(table_name)='student';
CONSTRAINT_NAME
------------------------------------------------------------
STUDENT_ID_NN

(2)unique

限制取值唯一,可以插入空值

既可以加在列级别,也可以加在表级别

唯一性约束上会自动创建唯一性索引,比对思虑更快

列级别约束:
drop table student;
create table student(
id number(7) constraint student_id_nn not null,
name varchar2(25) constraint student_name_u unique,
age number(3)
);
查看
select  constraint_name
from user_constraints
where lower(table_name)='student';
CONSTRAINT_NAME
------------------------------------------------------------
STUDENT_ID_NN
STUDENT_NAME_U

insert into student 
values(100,'briup',10);
已创建 1 行。
SQL> /
insert into student
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (BRIUP.STUDENT_NAME_U)


表级别约束:
(当约束涉及多个列时,只能使用表级别约束)
drop table student;
create table BRIUP.student(
id number(7),
name varchar2(25) ,
age number(3),
constraint student_id_u unique(id,name)
);

insert into student 
values(1,'briup',10);
已创建 1 行。            
SQL> /
insert into student
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (BRIUP.STUDENT_NAME_U)            

(3)primary key

限定取值非空、唯一

既可以加在列级别,也可以加在表级别

Oracle底层会自动创建唯一性索引

列级别约束
drop table student;
create table student(
id number(7) constraint student_id_pk primary key,
name varchar2(25),
age number(3)
);

表
drop table student;
create table BRIUP.student(
id number(7),
name varchar2(25),
age number(3),
constraint student_id_pk primary key(id,name)
);

(4)foreign key

先建父表(外键所依赖的表),再建子表(外键所在的表)

先删除子表,再删除父表

既可以加在列级别,也可以加在表级别

on delete cascade:删除父表中数据时,会将子表中相关联的数据一起删除

列(外键在本表)
drop table student;
create table student(
id number(7) constraint student_id_pk primary key,
name varchar2(25),
age number(3),
gid number(7) constraint student_gid_fk references student(id)    
);

表级别 <FK需要使用foreign key 外键列名 (关键字)>
drop table student;
create table BRIUP.student(
id number(7) constraint student_id_pk primary key,
name varchar2(25),
age number(3),
gid number(6),
constraint student_gid_fk foreign key(gid) references student(id)  
);

先建父表(外键所依赖的表),再建子表(外键所在的表)
create table dept(
id number(7) constraint dept_id_pk primary key,
name varchar2(25)
);
create table emp(
id number(7) constraint emp_id_pk primary key,
name varchar2(25),
dept_id number(7) constraint emp_dept_id_fk references dept(id)
);

插入数据
insert into dept
values(1,'Sale');
insert into emp
values(1,'Briup',1);

删除表中数据
delete from dept; //错
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (BRIUP.EMP_DEPT_ID_FK) - 已找到子记录

先删除子表(数据),再删除父表(数据)
delete from emp;
delete from dept;
drop table emp;
drop table dept;


on delete cascade  级联(主表(父表)级联子表)
删除主表中数据时,级联地将子表中关联的数据一起删除
删除表顺序还是先子表后父表

create table dept(
id number(7) constraint dept_id_pk primary key,
name varchar2(25)
);
create table emp(
id number(7) constraint emp_id_pk primary key,
name varchar2(25),
dept_id number(7) constraint emp_dept_id_fk references dept(id) on delete cascade
);
插入数据
insert into dept
values(1,'Sale');
insert into emp
values(1,'Briup',1);


可以直接删除父表数据 
delete from dept;
SQL> select * from emp;
未选定行
SQL> select * from dept;
未选定行

补充:
create table A(
id1 number(7),
id2 number(7),
name varchar2(25),
constraint a_id1_id2_pk primary key(id1,id2)
);
联合外键关联到上面表的联合主键
create table B(
    id number(7) constraint b_id_pk primary key,
    name varchar2(25),
    aid1 number(7),
    aid2 number(7),
    constraint b_aid1_aid2_fk foreign key(aid1,aid2)
    references A(id1,id2)
);

(5)check

列级别
drop table student;
create table student(
id number(7) constraint student_id_pk primary key,
name varchar2(25),
age number(3) constraint student_age_c check(age>=1 and age<=100),
gender varchar2(10) constraint student_gender_c check(gender in ('Male','Famale'))
);

表级别
drop table student;
create table student(
id number(7) constraint student_id_pk primary key,
name varchar2(25),
gender varchar2(10),
constraint student_gender_c check(gender in ('Male','Famale'))
);

2.读实例图,写建表语句

1571734354751

create table s_dept(
id number(7) constraint s_dept_id_pk primary key,
name varchar2(25) constraint s_dept_name_nn not null,
region_id number(7) constraint s_dept_region_id_fk references s_region(id),
constraint s_dept_name_region_id_u unique (name,region_id)
)  

3.基于已有表创建新表

1)拷贝源表结构(只拷贝非空约束,其他约束丢弃)、表数据
<因为:只有not null约束不能跨列,PK由联合主键组成,包含其他多个列,把一个列拷贝过来,又把其主键约束拷贝过来
了,主键约束涉及到多个列,拷贝过来一个列就不是很合理>
create table emp41(id,name,salary,did)
as
select id,last_name,salary,dept_id
from s_emp
where dept_id=41;

select *
from emp41;

        ID NAME           SALARY        DID
---------- ---------- ---------- ----------
         2 Ngao             1450         41
         6 Urguhart         1200         41
        16 Maduro           1400         41
        17 Smith             940         41

select constraint_name
from user_constraints
where lower(table_name)='emp41';
CONSTRAINT_NAME
------------------------------------------------------------
SYS_C004549
SYS_C004548

desc user_cons_columns;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 POSITION                                           NUMBER
 
 select constraint_name,column_name
 from user_cons_columns
 where lower(table_name)='emp41';
CONSTRAINT_NAME COLUMN_NAME
--------------- ---------------
SYS_C004548     ID
SYS_C004549     NAME
 
 进一步查询约束类型
desc user_constraints;(里面有个CONSTRAINT_TYPE)
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
select constraint_name,CONSTRAINT_TYPE
from user_constraints
where lower(table_name)='emp41';
CONSTRAINT_NAME CO
--------------- --
SYS_C004549     C
SYS_C004548     C
C-check约束(NN都用C来表示)
R-reference参考引用;
P-Primary key主键;
U-Unique key唯一键

SQL> select constraint_name,CONSTRAINT_TYPE
  2  from user_constraints
  3  where lower(table_name)='s_emp';
CONSTRAINT_NAME           CO
------------------------- --
S_EMP_USERID_UK           U
S_EMP_ID_PK               P
S_EMP_COMMISSION_PCT_CK   C
S_EMP_LAST_NAME_NN        C

即:id在原表s_emp里是P主键约束(包含非空和唯一),但到了新的表里变成了C(NN都用C来表示)非空约束


2)基于已有表创建新表,只拷贝表结构,不拷贝数据,造一个恒假的条件(恒真1=1,拷贝全部数据)
drop table emp41;
create table emp41(id,name,salary,did)
as
select id,last_name,salary,dept_id
from s_emp
where 1=2;

SQL> select * from emp41
  2  ;

未选定行

七.数据操作

1.DML(insert,update,delete)

1.insert into 表[(列名)] values(对应数据);

insert into student values(1,'briup1',20,'Male');
insert into student(id,name) values(1,'briup1');
insert into student values(1,'briup1',20,'Male',to_date('2015-09-01','yyyy-mm-dd'));

有主,外键关系:先往父表中插入数据,再往子表中插入有关联关系的数据 

从已有表取数据,插入了另外一个表中(insert语句中使用子查询)
insert into emp41
select id,last_name,salary_dept_id
from se_emp
where dept_id=41;

2.update 表 set 列名='要修改的内容' where 条件
update student
set name='briup2',age=21
where id=1;
修改外键列中的值时,一定确保其在父表中有对应值

3.delete from 表 where 条件
delete from student
where id=1;
有主,外键关系:先删除子表中的数据,在删除父表中的数据
执行DML操作时,需要注意一下数据的主外键关联

2.TCL(commit,rollback,savepoint)

1.事务(原理)

​ 一组相关联的的操作,这组操作不可分隔,要么同时成功,要么同时失败

​ 特点:ACID -Automicity(原子性)-Consistency(一致性)-Isolation(隔离性) -Durability(持久性)

oracle默认隔离级别是read commited,即一个事务只能读取其他事务已经修改提交了数据或记录。(只解决了脏读问题,解决不了不可重复读和幻影读问题)

oracle允许设置隔离级别为Serilizable,串行化的隔离级别,一个事务执行完了,另外一个事务执行,这样效率就比较低

多个事务同时执行,可能出现:

1)脏读(dirty read):事务1读取了事务2未提交的数据

2)不可重复读(unrepeatable read):同一查询在同一事务中多次进行,由于其它事务提交所做的修改,每次返回不同的结果集

由于事务2对数据的修改提交了,造成事务1多次读取的数据内容不一致

3)幻影读/虚读(phantom read):同一查询在同一事务中多次进行,由于其它事务提交所做的插入操作,每次返回不同的结果集

由于事务2插入新的记录,造成事务1多次读取的记录数量不一致

事务未提交前:

1)数据被写到数据库缓冲区

2)DML操作只在当前会话可见

3)其它会话看不到DML操作结果

4)多个事务都操作的数据行会被锁定(其他事务对数据操作不了),commit;提交之后结束(其他事务自动提交操作)

2.commit

提交事务,事务操作持久保存

drop table student;
create table student(
id number(7) constraint student_id_pk primary key,
name varchar2(25)
);
insert into student values(1,'briup1');
insert into student values(2,'briup2');
insert into student values(3,'briup3');
commit;
提交完成。

3.rollback

回滚事务,事务操作撤销

select * from student;
        ID NAME
---------- ----------
         1 briup1
         2 briup2
         3 briup3
insert into student values(4,'briup4');
insert into student values(5,'briup5');
select * from student;
SQL> select * from student;
        ID NAME
---------- ----------
         1 briup1
         2 briup2
         3 briup3
         4 briup4
         5 briup5
rollback;
回退已完成
SQL> select * from student;
        ID NAME
---------- ----------
         1 briup1
         2 briup2
         3 briup3

4.savepoint ...; roolback to ...;

设置回滚点,让事务回滚到中间的某个回滚点位置,而不是回滚所有操作
回滚到某个回滚点,事务并未结束

insert into student values(6,'briup6');
savepoint p6;
insert into student values(7,'briup7');
savepoint p7;
insert into student values(8,'briup8');
rollback to p6;

八.修改表(DDL-create,alter,drop,rename,truncate)

1.修改列(alter table...add,drop,modify)

1.添加列
alter table student
add(
age number(3),
start_date date default sysdate constraint  student_start_date_nn not null
);

2.删除列
alter table student
drop column age;

3.修改列
alter table student
modify(
name varchar2(50) default user not null
);

2.修改约束(alter table...add [modify] /drop [cascade] /disable [cascade] /enable)

1.添加约束
drop table student;
create table student(
id number(7),
name varchar2(25)
);
alter table student
add constraint student_id_pk primary key(id);

select constraint_name,constraint_type
from user_constraints
where lower(table_name)='student';
CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
STUDENT_ID_PK                                                P

SQL> desc student
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(7)
 NAME                                               VARCHAR2(25)
 
//添加 not null约束不能用add 用modify
alter table student
add constraint student_name_nn not null(name); //erro

alter table student
modify(
name varchar(25) constraint student_name_nn not null      
);
SQL> desc student
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(7)
 NAME                                      NOT NULL VARCHAR2(25)
 
select constraint_name,constraint_type
from user_constraints
where lower(table_name)='student';
CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
STUDENT_NAME_NN                                              C
STUDENT_ID_PK                                                P

2.删除约束
删除上面的name非空约束
alter table student
drop constraint student_name_nn;

select * from student;
SQL> desc student
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(7)
 NAME                                               VARCHAR2(25)
 
select constraint_name,constraint_type
from user_constraints
where lower(table_name)='student';
CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
STUDENT_ID_PK                                                P

cascade:删除父表中的主键约束,同时级联地将子表中的外键约束一起删除
级联。父表级联子表
alter table student
drop primary key cascade;
SQL> desc student
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(7)
 NAME                                               VARCHAR2(25)

3.使约束失效
alter table student
disable constraint student_id_pk cascade;

cascade:使父表中的主键约束失效,同时级联地使子表中相关的外键约束一起失效

4.使约束生效
alter table student
enable constraint student_id_pk;

3.其他命令

1)删除表(drop table..[cascade comstraints])

cascade constraints:删除父表的同时,把子表的约束删除掉

drop table student cascade constraints;

2)重命名表(rename...to)

rename student to stu;

3)重命名列(allter table ... rename column ... to ...)

alter table stu rename column name to last_name;

4)清空表中所有数据(truncate table...)

truncate table emp_42;

truncate和delete区别?

​ (1)truncate是DDL,自动提交事务,不能回滚;delete是DML,可以回滚

​ (2)truncate删除表中所有数据;delete可以删除表中部分数据

​ (3)truncate会释放表空间;delete不会释放表空间

1571793802908

​ 硬盘的特点:只覆盖,不擦除

​ HWM: High Water Mark,高水位线(用于标记在分配的表空间中实际用了多少表空间)

​ 查询高水位线以下的地方

​ 插入最高水位线以下,最高水位线继续往上增长的位置

​ 删除并不是真正的删除,只是做了一下标记,并不会降低最高水位线

​ truncate删除表的时候会释放表空间,delete不会

5)给表加注释

给表加注释
comment on table stu
is 'This is student table';
注释已创建。

查看(数据字典:用户表上的注释内容)
desc user_tab_comments
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(11)
 COMMENTS
查comments
select comments
from user_tab_comments
where lower(table_name)='student';
COMMENTS
---------------------------------------
This is a student table!


给列加注释
comment on column stu.id
is 'This is student id column';

九.序列数(对象)-sequence

1.概念

产生唯一数值,经常用来作为主键值

100->步长1->101

2.创建,查看

1572146548567

create sequence my_seq
increment by 2
start with 100
maxvalue 1000
minvalue 100
nocycle
nocache;

使用(刚开始的时候currval要在nextval执行一次以后才可以使用) 
select my_seq.nextval
from dual;
select my_seq.currval
from dual;
。。。。
select my_seq.currval
from dual;
   CURRVAL
----------
       106

1.建一个student表
drop table student;
create table student(
id number(7) constraint student_id_pk primary key,
name varchar2(25)
);
2.插入数据
insert into student
values(my_seq.nextval,'briup');
/
/
/
/
3.查询 
select * from student;
        ID NAME
---------- ---------
       108 briup
       110 briup
       112 briup
       114 briup
       116 briup

desc user_sequences
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)
 MIN_VALUE                                          NUMBER
 MAX_VALUE                                          NUMBER
 INCREMENT_BY                              NOT NULL NUMBER
 CYCLE_FLAG                                         VARCHAR2(1)
 ORDER_FLAG                                         VARCHAR2(1)
 CACHE_SIZE                                NOT NULL NUMBER
 LAST_NUMBER                               NOT NULL NUMBER
 
select min_value,max_value,increment_by,cycle_flag
from user_sequences
where lower(sequence_name)='my_seq';
 MIN_VALUE  MAX_VALUE INCREMENT_BY CY
---------- ---------- ------------ --
       100       1000            2 N
       
表下面的数据字典
desc user_tables;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(40)
 INSTANCES                                          VARCHAR2(40)
 CACHE                                              VARCHAR2(20)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 DROPPED                                            VARCHAR2(3)
 
 
查看当前用户下有哪些表
select table_name
from user_tables;
TABLE_NAME
------------------------------------------------------------
T_ACCOUNT
S_DEPT
S_EMP
S_REGION
STUDENT1
STUDENT
    

4.修改(start with不能修改)

alter sequence my_seq
increment by 1
maxvalue 2000
minvalue 200
cycle
cache 10;
*
第 1 行出现错误:
ORA-04007: MINVALUE 不能大于当前值

alter sequence my_seq
increment by 1
maxvalue 2000
minvalue 116
cycle
cache 10;
序列已更改。


修改时注意:
(1)minvalue<=currval
(2)startwith不能修改

4.删除

drop sequence my_seq;
序列已删除。

十.视图(一个有名字的子查询,简化多表连接查询)简单视图可以执行DML操作

1.概念

数据库中单张或多张表的映像

1572151474290

2.创建、使用

1572151861759

or replace :表示视图如果不存在,新建视图,视图如果已经存在,替换视图(修改了视图)

with read only:视图是只读的,不能对视图进行DML操作

force:没有表,可以先将视图创建出来,但不能使用,还需源表创建好后,才能用

with check option :用视图所做的DML操作,通过该视图可以查看到修改后的结果

简单视图

drop table myemp;
create table myemp
as
select id,last_name,salary,dept_id
from s_emp;

create view my_view
as
select id,last_name,dept_id
from myemp
where dept_id=41;
            *
第 1 行出现错误:
ORA-01031: 权限不足

system/system下
SQL> grant create view to briup;
授权成功。

回到briup用户下
create view my_view
as
select id,last_name,dept_id
from myemp
where dept_id=41;
视图已创建。

查看视图
select * from my_view;
        ID LAST_NAME                                             DEPT_ID
---------- -------------------------------------------------- ----------
         2 Ngao                                                       41
         6 Urguhart                                                   41
        16 Maduro                                                     41
        17 Smith                                                      41


insert into my_view
values(100,'briup',41);
视图my_view和表myemp中都有该数据

操作视图,最终影响的是表
视图里是没有数据的,它只是对表中的数据做了一下映像,
所谓映像实际上就是一个子查询,视图可以看成一个有名字的子查询
简单视图是可以执行DML操作的

替换修改视图且只读:
create or replace view my_view
as
select id,last_name,dept_id
from myemp
where dept_id=41
with read only;

with check option:用视图所作的DML操作,通过该视图可以查看到修改后的结果

create or replace view my_view
as
select id,last_name,dept_id
from myemp
where dept_id=41;

update my_view
set dept_id=42
where last_name='briup';

update my_view
set dept_id=42
where id=2;
---------------------------------------------------------------------------------
create or replace view my_view
as
select id,last_name,dept_id
from myemp
where dept_id=41
with check option;

update my_view
set dept_id=41
where id=2;
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
即:若改过之后该数据不会存在在视图中(与视图的查询条件相关联),则不会允许修改该内容

update my_view
set    last_name='Oracle'
where id=2;
即:若改过之后该数据不会存在在视图中(与视图的查询条件相关联),则不会允许修改该内容

复杂视图

sql查询多表连接查询,组函数
select d.name,avg(salary),sum(salary),max(salary)
from s_emp e,s_dept d
group by d.name;

create or replace view my_dept_view(dname,avgsal,sumsal,maxsal)
as
select d.name,avg(salary),sum(salary),max(salary)
from s_emp e,s_dept d
group by d.name;

select * from my_dept_view;

select dname,avgsal
from my_dept_view;
DNAME                                                  AVGSAL
-------------------------------------------------- ----------
Administration                                        1255.08
Development                                           1255.08
Finance                                               1255.08
Operations                                            1255.08
Sales                                                 1255.08

不能对复杂视图执行DML操作
update my_dept_view
set avgsal=2000
where dname='Sale';
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法

视图对应的数据字典

desc user_view;
名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)
 
select view_name
from user_views
where lower(view_name)='my_view';
VIEW_NAME
---------------------
MY_VIEW

3.删除

drop view my_view;
视图已删除。

十一.索引

1.概念

数据库中的对象,用来加快查询、比对速度

采用BTree树(多路搜索树),减少比较次数,提高效率

自动创建的索引:加primary key、unique约束时

手动创建的索引:自己写sql

索引的不足:

创建和维护索引,比较耗费时间,随着数据量的增大而增大

创建索引,占一定的物理空间

在对表进行增删改的时候,索引相应的也需要进行动态的更新

2.创建

create index my_index
on myemp(last_name);
索引已创建。

SQL> select rowid,id,last_name
  2  from myemp;
ROWID                      ID LAST_NAME
------------------ ---------- ------------------------
AAADWxAABAAAKbyAAA          1 Velasquez
AAADWxAABAAAKbyAAB          2 Ngao
AAADWxAABAAAKbyAAC          3 Nagayama
AAADWxAABAAAKbyAAD          4 Quick-To-See
AAADWxAABAAAKbyAAE          5 Ropeburn
....

3.使用条件

什么时候建立索引?

1)表中数据量大

2)索引列经常作为查询条件

3)查询出的数据量小(从大量数据中查询小量数据)

4)列上没有大量的空值

5)修改操作少

4.数据字典

desc  user_indexes;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 INDEX_TYPE                                         VARCHAR2(27)
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(11)
 UNIQUENESS                                         VARCHAR2(9)
 COMPRESSION                                        VARCHAR2(8)
 PREFIX_LENGTH                                      NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 PCT_THRESHOLD                                      NUMBER
 INCLUDE_COLUMN                                     NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 PCT_FREE                                           NUMBER
 LOGGING                                            VARCHAR2(3)
 BLEVEL                                             NUMBER
 LEAF_BLOCKS                                        NUMBER
 DISTINCT_KEYS                                      NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                            NUMBER
 AVG_DATA_BLOCKS_PER_KEY                            NUMBER
 CLUSTERING_FACTOR                                  NUMBER
 STATUS                                             VARCHAR2(8)
 NUM_ROWS                                           NUMBER
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 DEGREE                                             VARCHAR2(40)
 INSTANCES                                          VARCHAR2(40)
 PARTITIONED                                        VARCHAR2(3)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 BUFFER_POOL                                        VARCHAR2(7)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 PCT_DIRECT_ACCESS                                  NUMBER
 ITYP_OWNER                                         VARCHAR2(30)
 ITYP_NAME                                          VARCHAR2(30)
 PARAMETERS                                         VARCHAR2(1000)
 GLOBAL_STATS                                       VARCHAR2(3)
 DOMIDX_STATUS                                      VARCHAR2(12)
 DOMIDX_OPSTATUS                                    VARCHAR2(6)
 FUNCIDX_STATUS                                     VARCHAR2(8)
 JOIN_INDEX                                         VARCHAR2(3)
 IOT_REDUNDANT_PKEY_ELIM                            VARCHAR2(3)
 DROPPED                                            VARCHAR2(3)

SQL> desc user_ind_columns;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME                                         VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 COLUMN_POSITION                                    NUMBER
 COLUMN_LENGTH                                      NUMBER
 CHAR_LENGTH                                        NUMBER
 DESCEND                                            VARCHAR2(4)

select INDEX_NAME
from USER_INDEXES
where lower(table_name)='myemp';
或
where lower(index_name)='my_index';
INDEX_NAME
------------------------------------------------------------
MY_INDEX

5.删除索引

SQL> drop index my_index;
索引已删除。

十二.读E-R图,写出实例图、建表语句

1.

1572158735278

一部电影可以拷贝成光盘,也可以不拷贝<虚线>

有一个电影拷贝盘,一定有一个电影(被拷贝进去)<实线>

一个电影可以被拷贝成多份拷贝盘 <发散的>

一对多的关联关系,外键建在多的一方(拷贝盘)

把离竖线远的一方的主键拿到离竖线近的一方作为联合主键

即tid充当双重角色,拷贝盘的外键;和主键id一起作为联合主键

被租赁出去一了,租赁信息一定对应一个拷贝盘信息,同一时间只能被一个人租赁走,所以是 一对一

<虚线><实线>

将电影拷贝盘的主键充当租赁信息的外键,即此外键由tid和tcid(拷贝盘的id)组成

把离竖线远的一方的主键拿到离竖线近的一方作为联合主键

即(由tid和tcid(拷贝盘的id)组成的)外键充当双重角色

他们三个合到一起共同做租赁信息的PK

<虚线><实线>

一个会员可以有多个租赁信息,即可以租赁多次

一个租赁信息只有一个会员与之对应

一对多,外键建立在多的一端即租赁信息的一端

将mid(会员的主键id)充当租赁信息的外键

没有竖线,即mid(会员的主键id)只充当租赁信息的外键,没有联合外键

<虚线><实线>

会员可以预定也可以不预定<虚线>

一个预定信息对应一个会员<实线>

一个会员可以多次预定<发散>

一对多,外键建立在多的一端即预定信息的一端

将mid充当预定信息的外键

把离竖线远的一方的主键拿到离竖线近的一方作为联合主键

这个主键由两个列组成,即联合主键

即(mid)外键充当双重角色

<虚线><实线>

一个电影可以被预定多次

一次只能预定一个电影

外键建在多的一方,把tid(一部电影信息的主键id)作为外键放到预定信息的一方

把离竖线远的一方的主键拿到离竖线近的一方作为联合主键

tid+(mid+res date)联合主键

1572161732173

1.建没有外键的表MEMBER和TITLE
CREATE TABLE MEMBER
(member_id NUMBER (10)
CONSTRAINT member_id_pk PRIMARY KEY,
last_name VARCHAR2(25)
CONSTRAINT member_last_nn NOT NULL,
first_name VARCHAR2(25),
address VARCHAR2(100),
city VARCHAR2(30),
phone VARCHAR2(15),
join_date DATE DEFAULT SYSDATE
CONSTRAINT join_date_nn NOT NULL);
表已创建。

 CREATE TABLE title
(title_id NUMBER(10)
CONSTRAINT title_id_pk PRIMARY KEY,
title VARCHAR2(60)
CONSTRAINT title_nn NOT NULL,
description VARCHAR2(400)
CONSTRAINT title_desc_nn NOT NULL,
rating VARCHAR2(4)
CONSTRAINT title_rating_ck CHECK
(rating IN ('G','PG','R','NC17','NR')),
category VARCHAR2(20) DEFAULT 'DRAMA'
CONSTRAINT title_categ_ck CHECK
(category IN ('DRAMA', 'COMEDY', 'ACTION',
'CHILD', 'SCIFI', 'DOCUMENTARY')),
release_date DATE,
price number(10));
表已创建。

2.创建TITLE_COPY(表中外键少的)
CREATE TABLE title_copy
(copy_id NUMBER(10),
title_id NUMBER(10)
CONSTRAINT copy_title_id_fk REFERENCES
title(title_id),
status VARCHAR2(15)
CONSTRAINT copy_status_nn NOT NULL
CONSTRAINT copy_status_ck CHECK
(status IN ('AVAILABLE', 'DESTROYED',
'RENTED', 'RESERVED')),
CONSTRAINT copy_title_id_pk
PRIMARY KEY(copy_id, title_id));
表已创建。

3.建RENTAL表
 CREATE TABLE rental
(book_date DATE DEFAULT SYSDATE,
 title_id NUMBER(10),
member_id NUMBER(10)
CONSTRAINT rental_mbr_id_fk
REFERENCES member(member_id),
copy_id NUMBER(10),
act_ret_date DATE,
exp_ret_date DATE DEFAULT SYSDATE + 2,
CONSTRAINT rental_copy_title_id_fk
FOREIGN KEY (copy_id, title_id)
REFERENCES title_copy(copy_id, title_id),
CONSTRAINT rental_id_pk PRIMARY KEY
(book_date, copy_id, title_id));
表已创建。

4.建
CREATE TABLE reservation
(res_date DATE,
member_id NUMBER(10)
CONSTRAINT res_member_id_fk
REFERENCES member (member_id),
title_id NUMBER(10)
CONSTRAINT res_title_id_fk
REFERENCES title (title_id),
CONSTRAINT res_id_pk PRIMARY KEY
(res_date, member_id, title_id));
表已创建。


SELECT object_name
FROM user_objects
WHERE object_name IN ('MEMBER','TITLE',
'TITLE_COPY','RENTAL','RESERVATION');


SELECT constraint_name, constraint_type,
table_name, search_condition,
r_constraint_name
FROM user_constraints
 WHERE table_name IN ('MEMBER','TITLE',
'TITLE_COPY','RENTAL','RESERVATION');


CREATE SEQUENCE member_id_seq
START WITH 101
NOCACHE;

CREATE SEQUENCE title_id_seq
START WITH 92
NOCACHE;

SELECT sequence_name, increment_by,
last_number
FROM user_sequences
WHERE sequence_name IN ('MEMBER_ID_SEQ','TITLE_ID_SEQ');
SEQUENCE_NAME    INCREMENT_BY LAST_NUMBER
---------------- ------------ -----------
MEMBER_ID_SEQ               1         101
TITLE_ID_SEQ                1          92


SET ECHO OFF
INSERT INTO title (title_id, title, description,
rating, category, release_date)
VALUES (title_id_seq.nextval,
'Willie and Christmas Too',
'All of Willie''s friends made a Christmas list
for Santa, but Willie has yet to add his own
wish list.',
'G','CHILD','15-OCT-95');

INSERT INTO title (title_id, title, description,
rating, category, release_date)
VALUES (title_id_seq.nextval, 'Alien Again',
'Yet another installment of science fiction
history. Can the heroine save the planet from the
alien life form?',
'R', 'SCIFI', '19-MAY-95');

INSERT INTO title (title_id, title, description,
rating, category, release_date)
VALUES (title_id_seq.nextval, 'The Glob', 'A meteor
crashes near a small American town and unleashes
carnivorous goo in this classic.',
'NR', 'SCIFI', '12-AUG-95');

INSERT INTO title (title_id, title, description,
rating, category, release_date)
VALUES (title_id_seq.nextval, 'My Day Off',
'With a little luck and a lot of ingenuity, a
teenager skips school for a day in New York.',
'PG', 'COMEDY', '12-JUL-95');

SQL> commit;
Commit complete.

INSERT INTO title (title_id, title, description,
rating, category, release_date)
VALUES (title_id_seq.nextval, 'Miracles on Ice',
'A six-year-old has doubts about Santa Claus.
But she discovers that miracles really do exist.',
'PG', 'DRAMA', '12-SEP-95')

INSERT INTO title (title_id, title, description,
rating, category, release_date)
VALUES (title_id_seq.nextval, 'Soda Gang',
'After discovering a cache of drugs, a young couple
find themselves pitted against a vicious gang.',
'NR', 'ACTION', '01-JUN-95');

SQL> commit;
Commit complete.


SELECT *
FROM title
ORDER BY title_id;

2.

给定一个学生选课系统,存在如下模式:
S(Sno,Sname ,age,sex),
SC(Sno,Cno,grade),
C(Cno,Cname,hour,credit),
T(Tno,Tname,major)。
其中S描述了学生的学号,姓名,年龄和性别,
SC描述了学号,课程号,成绩,
C描述了课程号,课程名字,学时和学分,
T描述了教师的编号,姓名,专业。
一名教师可以讲多门课程,
一个老师可以教多个学生,
一个学生可以被多名老师教,
一门课程对应一门成绩,
一个学生可以学多个课程。
Q1:按照上面关系,画E-R图。
Q2:画完E-R图,根据图建相应表。

create table student 
(sno number(15) primary key,
 sname varchar2(20) not null,
 age number(2),
 sex number(2)
);


create table teacher
(tno number(15) primary key,
 tname varchar2(20) not null,
 major varchar2(20)
);

create table course(
  cno number(15) primary key,
  cname varchar2(20) not null,
  hours number(2) not null,
  credit number(2),
  s_sno number(15) constraint student_sno_fk references student(sno),
  s_tno number(15) constraint teacher_tno_fk references teacher(tno)
);

create table score(
  cno number(15),
  grade number(2) not null,
  sno number(15) constraint score_sno_fk references student(sno),
  con number(15) constraint score_con_fk references course(cno),
  constraint sc_cno_sno_pk primary key(cno,sno)
);

create table teacher_student(
  tno number(15) constraint te_stu_tno_fk references student(sno),
  sno number(15) constraint te_stu_sno_fk references teacher(tno),
  constraint te_stu_pk primary key(tno,sno)
);

SQL> commit;
Commit complete.

附上在线PDF文档:

Oracle

我来吐槽

*

*