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)

  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(列|值|表达式, 有效位数)　　直接截取

  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的’%’符号

  select mod(5, 2) from dual;       // 1

• abs(列|值);　　求绝对值

  select abs(-12) from dual;    // 12
  select abs(10) from dual;    // 10

• ceil（列|值|表达式）　　向上取整

  select ceil(4.212) from dual;    // 5

• floor（列|值|表达式）　　向下取整

  select floor(4.212) from dual;    // 5

• 练习：

  -- 预测下面两条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(列名|表达式|字面值)　　返回字符的长度

  -- 获取员工表中每个员工的姓氏以及长度
  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(列名|表达式|字面值);　　全部小写/大写

  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(列名|表达式|字面值);　　首字母大写

  select initcap('tom') from dual;    // Tom
  select initcap('hello world') from dual;    // Hello World

• lpad/rpad(列|值, 宽度, 填充字符);　　从左边/右边填充指定的字符到指定的字节宽度

  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(列|值, 截取字符);　　从左边/右边截取字符

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

  select rtrim('abcdedededede', 'de') from dual;    //abc
  select rtrim('abcededededeee', 'de') from dual;    //abc
  select rtrim('abcdedeadedede', 'de') from dual;    //abcdedea

• replace(列|值,被置换的字符,置换的字符)　　置换指定字符

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

  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(参数一, 参数二, 参数三)　　转换指定字符
  参数一:要处理的内容，列|值
  参数二:要检索的内容
  参数三:检索内容一一对应的替换内容

  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表示找第几个出现的目标

  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])　　求子串

  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

  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伪代码片段

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函数

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函数进行改写

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

-

练习：

-- 查询图片表中文件名的后缀
第一步. 查找最后那个'.'字符的下标位置
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可为负数]

  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]

• 练习：

  -- 把字符'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);　　格式化数字

• 练习：

  -- 把当前日期按照形如'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 代表本地化货币符号
  $ 代表$
  …

• 练习：

  -- 格式化数字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;

• 练习：

  -- 查询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'));

#数据库的设计

• 立项 -> 需求分析 -> 概要设计 -> 详细设计 -> 编码
  (项目经理/高级程序员/中级程序员/初级程序员) -> 测试
  -> 交付 -> 维护
  ##对象到表格的转换

• 表是用来存储数据的，由行与列组成。它是数据库对象的一种。

  表是怎么来的？
      人的想法[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图的样子可能会有所不同。

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

  public class Student{
      private Integer id;
      private String name;
      private Date birth;
      private int age;
      private char gender;
      ...
  }

• 如何根据上面的思念映射成 表关系呢？

  表名：tbl_student
  列名：
  id number
  fname varchar2(128)
  lname varchar2(128)
  birth date
  gender char(1)
  ...

##范式

• 作用：用来规范数据库的设计，遵守它可以使得这个设计更有扩展性，减少冗余。 　

• 第一范式 [1NF]

  所有的列都不可再分，也就是每一列都是原子的。
  如：
  R(id,name,age)  如果此处的name可以分为 前名与后名，则建议：
  R(id,fname,lname,age)  这样就更加合理。

• 第二范式[2NF]

  在满足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]

  在满足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关键字

• 如：

  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 约束名] [约束类型]
  ….

• 如：

  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为外键指向角色表中的主键

  -- 列级语法创建两张表
  -- 创建角色表
  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 约束名] 约束类型(列名[,列名])
….

• 如：

  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)
  );

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

• 练习：

  使用表级语法创建用户表和角色表
  -- 表级语法创建两张表
  -- 创建角色表
  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))
  );

##删除表

• 删表语法：

  DROP TABLE 表名 [cascade constraints]

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

如果删除表时,表之间存在外键关联,
如果是多对一的情况,先删除从表(即属于多的那一张表)
创建表的时候也要注意先要创建属于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.修改表名， 语法

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

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

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

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

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

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

#DML语句[INSERT,DELETE,UPDATE]

##插入表数据
语法：

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

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

• 如：

  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);

##更新表数据
语法：

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

• 如：

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

##删除表数据
语法：

DELETE [FROM] 表名 WHERE 条件

• 如：

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