Fork me on GitHub

Oracle数据库学习笔记(二)

Oracle 数据库

Oracle是一个强大的关系型数据库产品,功能丰富,产品安全性有保障,是Oracle公司的主打产品

本系列笔记可能会遇到的专业词汇有:

  • DB, DataBase, 数据库
  • DBMS,DataBase Management System, 数据库管理系统
  • RDBMS,Relationship DataBase Management System, 关系型数据库管理系统
  • SQL, Standard Query Language, 标准化查询语言

本系列学习笔记包含如下的课程内容:

  • 数据库的基本概念
  • DDL 命令
    • Create
    • ALTER
    • DROP
  • DML 命令
    • insert
    • update
    • delete
  • DQL 命令
    • select 命令
    • 关联查询
    • 分组查询
    • 子查询
  • DCL 命令
  • DTL 命令
    • commit
    • rollback
    • savepoint
  • 数据字典
  • 数据库对象
    • 序列
    • 索引
    • 视图
    • 同义词

单行函数

  • SQL语言是一种描述型的语言,也叫第4代语言。
    而oracle中提供了大量的内置函数,当你要进行一些算法运算的时候
    你只需要调用即可。
    比如: sysdate, 该函数返回系统的当前时间
  • dual是一张虚拟表格,没有任何意义,只是为了充担构建完整的select语句
    如:select sysdate from dual;
    ##数字相关函数
  • round(列|值|表达式, 有效位数);  四舍五入(默认的小数点有效位数为0)

    1
    2
    3
    4
    select round(4.212, 2) from dual;    // 4.21
    select round(4.216, 2) from dual; // 4.22
    select round(4.213) from dual; // 4
    select round(4.613) from dual; // 5
  • trunc(列|值|表达式, 有效位数)  直接截取

    1
    2
    3
    4
    select trunc(4.212, 2) from dual;    // 4.21
    select trunc(4.216, 2) from dual; // 4.21
    select trunc(4.213) from dual; // 4
    select trunc(4.613) from dual; // 4
  • mod(列|值,列|值);  求余,相当于java的’%’符号

    1
    select mod(5, 2) from dual;       // 1
  • abs(列|值);  求绝对值

    1
    2
    select abs(-12) from dual;    // 12
    select abs(10) from dual; // 10
  • ceil(列|值|表达式)  向上取整

    1
    select ceil(4.212) from dual;    // 5
  • floor(列|值|表达式)  向下取整

    1
    select floor(4.212) from dual;    // 5
  • 练习:

    1
    2
    3
    4
    5
    6
    -- 预测下面两条SQL查询语句的结果值
    select round(3.14159,0),round(3.14159,3), round(3.1415,-1) from dual;
    select trunc(3.14159,0),trunc(3.14159,3), trunc(3.1415,-1) from dual;
    -- 查询出订单表中的总金额(取整)
    select floor(total) from s_ord;
    select trunc(total) from s_ord;

字符相关函数

  • length(列名|表达式|字面值)  返回字符的长度

    1
    2
    3
    4
    5
    -- 获取员工表中每个员工的姓氏以及长度
    select last_name, length(last_name) 姓氏长度 from s_emp;
    -- 查看'HelloWorld'字符串的长度
    select length('HelloWorld') from dual; // 10
    select length('Hello World') from dual; // 11
  • lower/upper(列名|表达式|字面值);  全部小写/大写

    1
    2
    3
    4
    select lower('TOM') from dual;    // tom
    select upper('Tom') from dual; // TOM
    -- 查询出员工表中名字是'tom'的员工(忽略大小写)
    select first_name from s_emp where lower(first_name) = 'tom';
  • initcap(列名|表达式|字面值);  首字母大写

    1
    2
    select initcap('tom') from dual;    // Tom
    select initcap('hello world') from dual; // Hello World
  • lpad/rpad(列|值, 宽度, 填充字符);  从左边/右边填充指定的字符到指定的字节宽度

    1
    2
    3
    4
    select lpad('aa', 10, '*') from dual;    // ********aa
    select rpad('aa', 10, '*') from dual; // aa********
    select lpad('张三', 5, '*') from dual; //*张三
    select lpad('张三', 3, '*') from dual; //张
  • ltrim/rtrim(列|值, 截取字符);  从左边/右边截取字符

    注意:’截取字符’不需要按照截取字符顺序,可以是它的子集或者等效集合

    1
    2
    3
    select rtrim('abcdedededede', 'de') from dual;    //abc
    select rtrim('abcededededeee', 'de') from dual; //abc
    select rtrim('abcdedeadedede', 'de') from dual; //abcdedea
  • replace(列|值,被置换的字符,置换的字符)  置换指定字符

    注意:被置换的字符必须和’列|值’中的值进行严格的顺序匹配

    1
    2
    3
    4
    select replace('hello world!', 'o', '*') from dual;    // hell* w*rld!
    select replace('hello world!', 'oe', '*') from dual; // hello world!
    select replace('hello world!', 'lo', '*') from dual; // hel* world!
    select replace('hello world!', 'o', '') from dual; // hell wrld!
  • translate(参数一, 参数二, 参数三)  转换指定字符
    参数一:要处理的内容,列|值
    参数二:要检索的内容
    参数三:检索内容一一对应的替换内容

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select translate('I l6o9ve y8o7u!', 
    'abcdefghijklmnopqrstuvwxyz0123456789',
    'abcdefghijklmnopqrstuvwxyz') from dual; // I love you!
      
    select translate('I l6o9ve y8o7u!',
    'abcdefghijklImnopqrstuvwxyz',
    'abcdefghijkl*mnopqrstu*wxyz0123456789') from dual; // * l6o9*e y8o7u!
     
    -- 被检索的字符重复出现多次,以第一次出现的替换规则为准
    select translate('I love you', 'oo', 'i1') from dual; // I live yiu
  • instr(str,target[,start][,step])  求目标串在原串的中位置
    start表示从第几个位置开始,默认1
    step表示找第几个出现的目标

    1
    2
    3
    4
    5
    select instr('go,go,quickly!', 'go') from dual;    // 1
    select instr('go,go,quickly!', 'go' , 1, 2) from dual; // 4
    select instr('go,go,quickly!', 'go', 2) from dual; // 4
    select instr('go,go,quickly!', 'go', -1) from dual; // 4
    select instr('go,go,quickly!', 'go',-1 ,2) from dual; // 1
  • substr(str,start[,length])  求子串

    1
    2
    3
    select substr('hello world!', 3) from dual;    // llo world!
    select substr('hello world!', 3, 4) from dual; // llo
    select length(substr('hello world!', 3, 4)) from dual; // 4
  • decode()函数
    用法1: decode(字段或字段的运算,值1,值2,值3)  类似一个三目运算符
    字段或字段的运算 的结果等于 值1 时,
    返回 值2
    否则返回 值3

    1
    select decode('A','A','B','C') from dual;   // B

用法2:decode(条件或值,值1,返回值1,值2,返回值2,…值n,返回值n,[缺省值])
如 decode(a,b,c,d,e,f,g,i)
相当于如下的Java伪代码片段

1
2
3
4
5
6
7
8
9
if (a == b) {
return c
} else if (a == d) {
return e
} else if (a == f) {
return g
} else {
return i
}

注意:如果未定义缺省值,则返回空值


- case when then end转换函数  类似于Java中的Switch函数

1
2
3
4
5
6
7
8
9
10
select name "部门名",
case region_id
when 1 then '北美'
when 2 then '南美'
when 3 then '中东/非洲'
when 4 then '亚洲'
when 5 then '欧洲'
else '未知区域'
end "区域名"
from s_dept;


可以使用decode函数进行改写

1
2
3
4
select name 部门名, region_id, 
decode(region_id, 1, '北美', 2, '南美',
3, '中东/非洲', 4, '亚洲', 5, '欧洲',
'未知区域') 区域名 from s_dept;


-

练习:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
-- 查询图片表中文件名的后缀
第一步. 查找最后那个'.'字符的下标位置
instr(filename, '.', -1)
第二步. 从'.'所在下标开始截取后面的所有字符
substr(filename, '.'所在的下标)
第三步. 排重 distinct写出最终的SQL语句
select distinct substr(filename, instr(filename, '.', -1) + 1) 后缀名 from s_image;
 
--- 模拟银行中只显示姓名(s_emp)的第一个字符串(张三 -> 张*)
第一步. 截取首字母
substr(first_name, 1, 1)
第二步. 计算需要进行填充的宽度
length(first_name)
第三步. 使用rpad函数从右往左填充'*'
-- 方法一
select first_name, rpad(substr(first_name, 1, 1),
length(first_name), '*') 显示名字 from s_emp;
-- 方法二,推荐
select first_name,
replace(first_name,
substr(first_name, 2),
rpad('*', length(first_name) -1 , '*')) 显示名字
from s_emp;
-- 方法三,可能有时候会错误
select first_name, translate(first_name,
first_name,
substr(first_name, 1, 1) || rpad('*', length(first_name) -1, '*')) 显示名字
from s_emp;
 
-- 找出名字长度超过5的员工
select * from s_emp where length(first_name) > 5;
 
-- 找出职称是stock clerk的员工
select * from s_emp where lower(title) = 'stock clerk';
 
-- 查询出客户表中的phone列中的最后一个'-'符号后面的串
select phone, substr(phone, instr(phone, '-', -1) + 1 ) from s_customer;
 
--- 请把员工的工资分为3等,超过2000元的为高等,在1500-2000之间的为中等, 低于1500的为低等
select first_name "员工名",
case salary
when salary > 2000 then '高等'
when salary between 1500 and 2000 then '中等'
when salary < 1500 then '低等'
else '未知等级'
end "工资等级"
from s_emp; // 错误写法
 
select first_name "员工名",
case
when salary > 2000 then '高等'
when salary between 1500 and 2000 then '中等'
when salary < 1500 then '低等'
else '未知等级'
end "工资等级"
from s_emp; // 正确写法
 
注: 如果when中已经出现了列名变量且是一个条件,则case后面不能加列名;
反之, 如果when是一些常量用于和case后面的列名做等值比较,case后面需要添加对应列名
 
--- 输出每个订单编号及支付方式,支付方式要么为现金,要么是信用卡,否则就未知
-- decode写法
select payment_type, decode(payment_type,
'CREDIT', '信用卡支付', 'CASH',
'现金支付', '未知') 支付方式 from s_ord;
 
-- case when写法
select payment_type,
case payment_type
when 'CREDIT' then '信用卡支付'
when 'CASH' then '现金支付'
else '未知'
end 支付方式 from s_ord;
 
-- 输出区域表中每个地区对应的中文
select name,
case name
when 'North America' then '北美'
when 'South America' then '南美'
when 'Africa / Middle East' then '非洲/中东'
when 'Asia' then '亚洲'
when 'Europe' then '欧洲'
else '未知'
end 区域名 from s_region;
 
select name,
case id
when 1 then '北美'
when 2 then '南美'
when 3 then '非洲/中东'
when 4 then '亚洲'
when 5 then '欧洲'
else '未知'
end 区域名 from s_region;

日期相关函数

  • 注意:Oracle中日期类型的数值在显示时,将做为特殊字符来显示

    Oracle中,默认的日期格式是:DD-MON-RR
    在Oracle中,日期格式的字符有:
    yy/yyyy 表示2位/4位的数字年份,如:14或2014
    rr/rrrr 同上?
    mm 两位的数字月
    mon/month 可拼读的月份,如:11月,NOV
    dd 两位的数字日, 如:05,18
    dy/day 表示星期几, 如: Wed/Wednesday
    hh 小时
    mi 分钟(因为SQL不区分大小写,不能使用MM和mm进行区分,所以使用mi)
    ss 秒
    fm 表示去掉前面的0,如fm06,以后值6, 如fmmm

  • 如果使用rr进行年份转换,如果当前年份的后两位在0-49之间,
    传入的参数在0-49中,返回本世纪的年份;
    传入参数在50-99中,返回上个世纪的年份
    如果当前年份后两位在50-99之间,
    传入参数在0-49中,返回下个世纪的年份,
    传入参数是在50-99中,返回本世纪的年份

    对于日期来说,可以获取它的任何一个部份,比如:获取年份:
    select to_char(sysdate,'rrrr') from dual;

    Oracle中默认的日期格式:DD-MON-RR
    当然,我们可以改变某个会话[session]的日期格式:
    SQL>alter session set nls_date_format='rrrr-mm-dd hh24:mi:ss';
    同样的道理,我们还可以改变当前会话的语言环境:
    SQL>alter session set nls_language='american';
    SQL>alter session set nls_language='simplified chinese';
     
    常用的日期函数

    函数名称|作用
    -|-
    sysdate|返回当前日期
    last_day(d)|返回给定日期所在月的最后一天的日期
    months_between(d1,d2)|返回d1与d2之间相差多少个月,是一个近似的浮点数
    add_months(d, m)|返回在日期d的基础上,加上m个月的日期[m可为负数]

    1
    2
    3
    select last_day(sysdate) from dual;
    select round(months_between(sysdate, start_date), 2) from s_emp;
    select round(months_between(sysdate, start_date)/12, 2) from s_emp;

    注意:Oracle中日期类型是可以进行加减运算的,结果是两个日期的天数

    利用这些函数,我们可以构建一个任意的日期:
    1天后 => sysdate+1
    1小时后 => sysdate+1/24
    1分钟后 => sysdate+1/24/60

    1个月后 => add_months(sysdate,1)
    1年后 => add_months(sysdate,12)

  • next_day(d,week)  返回在日期d的基础上,下个week的日期
    此处的参数week是指星期几(
    中文要写中文,英文要写英文)
    如: SQL>select next_day(sysdate,'星期三') from dual;
    ##转换函数

  • to_number(c)  把字符转换成数字
    如:SQL>select to_number('123') from dual;
    一般配合to_char使用
  • to_date(c, pattern)  
    把字符c按照pattern模式解析成日期,如果不传pattern,则采用默认格式解析[DD-MON-RR]
  • 练习:

    1
    2
    3
    4
    -- 把字符'2008/08/08'转换成时间
    select to_date('2008/08/08', 'yyyy/mm/dd') from dual;
    -- 把字符'星期四 2014年,6号11月份'转换成时间
    select to_date('星期四 2014年,6号11月份', 'day yyyy"年,"dd"号"mm"月份"') from dual;
  • to_char(d, pattern)  格式化日期
    情况1:to_char(date, pattern);  格式化日期
    情况2:to_char(number, pattern);  格式化数字

  • 练习:

    1
    2
    3
    4
    -- 把当前日期按照形如'2016/12/05 14:04:16'格式转成字符格式
    select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;
    -- 把当前日期按照形如'2016年12月5日'的格式转换成字符格式
    select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;
  • to_char(n,pattern)  格式化数字
    pattern是指用来格式化数字的一些特殊字符,如:

    9 代表任意数字
    0 代表0
    . 代表小数点
    , 代表千分位
    L 代表本地化货币符号
    $ 代表$

  • 练习:

    1
    2
    3
    4
    5
    6
    -- 格式化数字1234.5678保留小数点后一位的字符
    select to_char('1234.5678', 9999.9) from dual;
    -- 说出下列SQL语句的作用
    select to_char(1234.5678,'L9,999,999.9') from dual;
    -- 输出员工的姓名及工资,工资 需要按照'$1,500.00'格式化
    select first_name, to_char(salary, '$9,999.99') from s_emp;
  • 练习:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    -- 查询2014年11月1日和2015年12月9日之间的间隔月数
    select months_between(to_date('2015年12月9日','yyyy"年"mm"月"dd"日"'),to_date('2014年11月1日', 'yyyy"年"mm"月"dd"日"')) from dual;
    -- 查询当前日期下一个星期四是几号
    select next_day(sysdate, 'Thursday') from dual;
    -- 查询半年后的日期
    select add_months(sysdate, 6) from dual;
    -- 查询半年前的日期
    select add_months(sysdate, -6) from dual;
    -- 查询员工的工作月数
    select months_between(sysdate, start_date) from s_emp;
    -- 查询员工的工作天数
    select sysdate - start_date from s_emp;
    -- 计算一年前,当前,一年后的时间
    select add_months(sysdate, -12), sysdate, add_months(sysdate, 12) from dual;
    -- 当前日期前6个月的最后一天
    select last_day(add_months(sysdate, -6)) from dual;
    -- 当前日期前6个月的第一天
    select last_day(add_months(sysdate, -7)) + 1 from dual;
    select trunc(add_months(sysdate, -6), 'mm') from dual;
    -- 显示下个星期5是什么日期
    select next_day(sysdate, 'Friday') from dual;
    -- 找出 5月份入职的员工
    select * from s_emp where to_char(start_date, 'fmmm') = '5';
    -- 找出90年下半年入职的员工
    select * from s_emp where to_char(start_date, 'yy') = '90' and to_number(to_char(start_date, 'fmmm')) > 6;
    --- 查询出员工的姓名,入职日期,并按日的升序排序
    select first_name, start_date from s_emp order by
    to_number(to_char(start_date, 'fmdd'));

#数据库的设计

  • 立项 -> 需求分析 -> 概要设计 -> 详细设计 -> 编码
    (项目经理/高级程序员/中级程序员/初级程序员) -> 测试
    -> 交付 -> 维护
    ##对象到表格的转换
  • 表是用来存储数据的,由行与列组成。它是数据库对象的一种。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    表是怎么来的?
    人的想法[idea]
    \- 利用工具来实现 [草图 -> 图如何画?]
    \- E-R图[Entity - Relationship Diagram]
    面向对象的思想
    \- 找对象 [拥有什么,能干什么]
    属性 -> 对应到的,它就是 列
    把一系列的对象按OO的思想抽象成 “类” -> 归类的过程
    \- 类 -> 对应到的, 它就是 表
    在JVM中,根据类可以实例化出对象 -> 它就是 记录
    那类之间的关系双如何表达呢? -> 它就是 外键[foreign key]
    [不管JAVA类是什么关系,在表中都将通过外键表达]

所以,做数据库设计的基本过程:
找对象 [根据业务的上下文]把对象映射[mapping]成 表关系, 按如下规则:
a). 类名 映射到 表名
b). 属性 映射到 列名
c). 对象标识 映射成 主键[primary key,唯一且非空的]
d). 对象关系 映射成 外键[foreign key]
有了上面的思想,再结束相关的工具软件,就可以把对象与表关系给“描绘”出来,这个就是E-R图:
常用的工具有: PowerDesigner、ERWin、Jude、StarUML…
以上这些工具,不仅仅是做E-R图,主要功能是做UML,
不同的软件所绘制的E-R图的样子可能会有所不同。

  • 比如:经过抽象后,我归得一个 学生类

    1
    2
    3
    4
    5
    6
    7
    8
    public class Student{
    private Integer id;
    private String name;
    private Date birth;
    private int age;
    private char gender;
    ...
    }
  • 如何根据上面的思念映射成 表关系呢?

    1
    2
    3
    4
    5
    6
    7
    8
    表名:tbl_student
    列名:
    id number
    fname varchar2(128)
    lname varchar2(128)
    birth date
    gender char(1)
    ...

##范式

  • 作用:用来规范数据库的设计,遵守它可以使得这个设计更有扩展性,减少冗余。  
  • 第一范式 [1NF]

    1
    2
    3
    4
    所有的列都不可再分,也就是每一列都是原子的。
    如:
    R(id,name,age) 如果此处的name可以分为 前名与后名,则建议:
    R(id,fname,lname,age) 这样就更加合理。
  • 第二范式[2NF]

    1
    2
    3
    4
    5
    6
    7
    8
    9
    在满足1NF的基础上,所有的非关键列必需要完全依赖于关键列,
    也就是不能存在非关键列部份依赖于关键列。
    如:
    R(sid,sname,cid,cname,grade)
    这个关系如果有(sid,cid)为关键列的话,则grade是完全依赖的,
    但 sname与cname都是部份依赖的。所以,它违反了2NF,应该改为:
    R1(sid,sname) sid为主键
    R2(cid,cname) cid为主键
    R3(sid,cid, grade) sid与cid 为联合主键
  • 第三范式[3NF]

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    在满足2NF的基础上,所有的非关键列必需直接依赖于关键列,
    也就是不能存在非关键列传递依赖于关键列。
    假如:A -> B,而 B -> C, 则我们说 A 传递依赖于C。
     
    如:
    R(sid,sname,phone,college,address)
    这个关系中以sid为关键列,则sname,phone,college直接依赖于它,
    但是,address 是依赖collage,所以,address 传递依赖于 sid, 违返了3NF.
    改为:
    R1(sid,sname, phone, college_id) sid为主键,college_id为外键
    R2(id,college,address) id为主键

#DDL之建表、改表、删表

  • 建表语法:

    CREATE TABLE 表名(
    列名 数据类型 [DEFAULT value] [CONSTRAINT 约束名] [约束类型]
    ,列名 数据类型 [DEFAULT value] [CONSTRAINT 约束名] [约束类型]
    ,…
    列名 数据类型 [DEFAULT value] [CONSTRAINT 约束名] [约束类型]
    ) [as subquery]

  • 表名的命名规则
    字母开头,长度在1~30之间,允许使用A-Z,a-z,0-9,_,$,#,同一用户下不能出现同名的表且不能使用oracle关键字

  • 如:

    1
    2
    3
    4
    create table tbl_test(
    id number,
    name varchar2(48)
    );

##约束 [constraint]
约束是一种数据库的对象,它依附于表的列,它的作用是用来限制列的内容。
  
Oracle中提供了5种约束:

约束名称|关键字|缩写|作用
-|-|-|-
主键约束|PRIMARY KEY|pk|列值非空且唯一,每张表中最多只能添加1个主键约束
唯一约束|UNIQUE|uk|列中非空值必须唯一
非空约束|NOT NULL|nn|列值不能为空
外键约束|[REFERENCES] FOREIGN KEY|fk|列值必须为引用表的某一列的值
自定义约束|CHECK|ck|自定义约束条件
  • 那么把如何把约束添加到表格的各列中呢?
    1.列级语法是在列定义结束前添加约束,这种语法不能跨列使用。语法:


    列名 类型 [CONSTRAINT 约束名] [约束类型]
    ….

  • 如:

    1
    2
    3
    4
    create table tbl_test(
    id number CONSTRAINT tbl_test_id_pk primary key,
    name varchar2(48) constraint tbl_test_name_nn NOT NULL
    );
  • 练习:


    使用列级语法创建一张角色表以及一张用户表,要求
    角色表中包含id、rname两列其中id为主键,rname非空
    用户表包含4个字段,分别为id、name、password、age、role_id
    其中id为主键、name非空、password有默认值’test、age大于0小于150、
    role_id为外键指向角色表中的主键

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 列级语法创建两张表
    -- 创建角色表
    create table tbl_role(
    id number(15) constraint tbl_role_id_pk primary key,
    rname varchar2(30) constraint tbl_role_rname not null
    );
    -- 创建用户表
    create table tbl_user(
    id number(15) constraint tbl_user_id_pk primary key,
    name varchar2(30) constraint tbl_user_name_nn not null,
    password varchar2(20) default 'test',
    age number(3) constraint tbl_user_age_ck check(age between 20 and 160),
    role_id number(15) constraint tbl_user_rid_fk references tbl_role(id)
    );
**2.表级语法是指在列定义结束之后再添加约束。它不能用来添加 NOT NULL约束。**语法:


列名 类型,
列名 类型,

,[CONSTRAINT 约束名] 约束类型(列名[,列名])
….

  • 如:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    create table tbl_test(
    id number,
    name varchar2(48) constraint tbl_test_name_nn NOT NULL,
    constraint tbl_test_id_pk PRIMARY KEY(id)
    );
     
    create table s_item(
    item_id number,
    ord_id number,
    customer_id number references S_CUSTOMER(id),
    quantity number,
    PRIMARY KEY(item_id,ord_id),
    FOREIGN KEY(customer_id) references S_CUSTOMER(id)
    );

    注意:如果不指定约束名,则系统会自动生成约束名。
    当然,如果你自定约束名,最好按如下规则:
    表名列名约束类型简称

  • 练习:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    使用表级语法创建用户表和角色表
    -- 表级语法创建两张表
    -- 创建角色表
    create table tbl_role(
    id number(15),
    rname varchar2(30),
    constraint tbl_role_id_pk primary key(id),
    constraint tbl_role_rname_nn check(rname is not null)
    );
    -- 创建用户表
    create table tbl_user(
    id number(15),
    name varchar2(30),
    password varchar2(20) default '123456',
    age number(3),
    role_id number(15),
    constraint tbl_user_id_pk primary key(id),
    constraint tbl_user_name_nn check(name is not null),
    constraint tbl_user_age_ck check(age between 20 and 160),
    constraint tbl_user_rid_fk foreign key(role_id)
    references tbl_role(id)
    );
     
    创建一张员工表表名为jsd_emp包含列
     
    id 类型为字符型 要求该列为主键
    name 类型为字符型 要求该列非空
    age 类型为数字型 要求年龄大于0且小于150
    gender 类型为字符型 要求性别必须为'男'或者'女'
    dept_id 类型为字符型 要求该列作为外键 引用部门表中的主键
     
    create table jsd_emp(
    id varchar2(30),
    name varchar2(25),
    age number(3),
    gender varchar2(6),
    dept_id varchar2(30),
    constraint jsd_emp_id_pk primary key(id),
    constraint jsd_emp_name_nn check(name is not null),
    constraint jsd_emp_age_ck check(age > 0 and age < 150),
    constraint jsd_emp_gender_ck check(gender in ('男', '女')),
    constraint jsd_emp_did_fk foreign key(dept_id)
    references jsd_dept(id)
    );
     
    创建一张部门表 表名为jsd_dept
    包含部门ID(id) 类型为字符型 该列为主键
    部门名称(name) 类型为字符型 部门名字唯一
    部门等级(state) 类型为数字型 且必须分为1 2 3 4 5 五个等级
     
    create table jsd_dept(
    id varchar2(30),
    name varchar2(25),
    state number(2),
    constraint jsd_dept_id_pk primary key(id),
    constraint jsd_dept_name_uk unique(name),
    constraint jsd_dept_state_ck check(state in (1,2,3,4,5))
    );

##删除表

  • 删表语法:

    1
    DROP TABLE 表名 [cascade constraints]

注意:[cascade constraints]表示级联解除有关键的外键约束.


如果删除表时,表之间存在外键关联,
如果是多对一的情况,先删除从表(即属于多的那一张表)
创建表的时候也要注意先要创建属于1的那张表

##修改表

  • 改表语法:

    1
    ALTER TABLE 表名 [子命令]
  • 子命令主要分以下几类:

    1.列相关的

    a. 添加新列
    ALTER TABLE 表名
    ADD 列名 数据类型 [default 值] [constraint 约束名] [约束类型]
    alter table jsd_dept add dept_no varchar2(20) default ‘test01’ not null;
    注意:若添加的列为一个外键列可以省略数据类型的定义.
    b.删除列
    ALTER TABLE 表名 drop column 列名;
    alter table jsd_dept drop column dept_no;
    c. 修改列 【此处不是改列名,而是改列的类型或添加约束】
    ALTER TABLE 表名
    MODIFY 原列名 数据类型 [default 值] [constraint 约束名] [约束类型]
    alter table jsd_dept modify name char(30) not null;
    d. 修改列名 [oracle 9才有]
    ALTER TABLE 表名 rename COLUMLN 原列名 to 新列名;
    alter table jsd_dept rename column name to dep_name;

    2.约束相关的

    a. 添加约束
    ALTER TABLE 表名

    ADD [CONSTRAINT 约束名] 约束类型(列名[,列名][,...]);  
    

     
    create table jsd_dept(
    id varchar2(20),
    name varchar2(20),
    state char(3)
    );
     
    alter table jsd_dept add constraint con_jsd_dept_id_pk primary key(id);
    alter table jsd_dept add constraint con_jsd_dept_name_uk unique(name);
    alter table jsd_dept add constraint con_jsd_dept_state_ck check(state in (1,2,3,4,5));
     
    注:此命令不能加 NOT NULL约束,必须采用 check(列名 is not null) 这样的表级语法
     
    b. 删除约束
    ALTER TABLE 表名 DROP CONSTRAINT 约束名;
    alter table jsd_dept drop constraint con_jsd_dept_name_uk;
     
    c. 关闭约束[使约束失效]
    ALTER TABLE 表名 DISABLE CONSTRAINT 约束名;
    alter table jsd_test2 disable constraint jsd_jsd_test2_id_pk;
      
    d. 打开约束[启用约束]
    ALTER TABLE 表名 ENABLE CONSTRAINT 约束名;
    alter table jsd_test2 ENABLE constraint jsd_jsd_test2_id_pk;

  • 补充:
    1.修改表名, 语法

    1
    2
    alter table 原表名 rename to 新表名;
    rename 原表名 to 新表名;

    2.复制原表中的结构和数据并创建一张新表, 语法

    1
    create table 新表名 as  select 列,列........ from 原表;

    3.只复制表结构不复制数据并创建一张新表, 语法

    1
    create table 新表名 as  select 列,列........ from 原表 where 恒假条件;

注意:以上第2和第3点建标语句相当于创建一张结构和被复制表相同的表,
它是一张新表,没有任何约束

#DML语句[INSERT,DELETE,UPDATE]

##插入表数据
语法:

1
INSERT INTO 表名[(列名[,列名][,列名][,...])] VALUES(值1[,值2][,...]);

注意:如果表名后的列省略,表示要插入所有列值。[默认值无效]

  • 如:

    1
    2
    3
    insert into tbl_class(id,name,capacity) values(1,'A教室',48);
    insert into tbl_class values(1,'A教室',48);
    insert into tbl_class(name,id) values('A教室', 1);

##更新表数据
语法:

1
UPDATE 表名 SET 列1=值[,列1=值][,...] WHERE 条件;
  • 如:

    1
    2
    3
    4
    -- 把所有教室的容量改为 38
    update tbl_class set capacity=38;
    -- 把id为1教室的容量改为 48
    update tbl_class set capacity=48 where id = 1;

##删除表数据
语法:

1
DELETE [FROM] 表名 WHERE 条件
  • 如:

    1
    2
    3
    4
    -- 删除教室表中的所有记录
    delete from tbl_class;
    -- 删除教室表中id为2的记录
    delete from tbl_class where id = 2;