Oracle 数据库学习笔记01

Oracle 数据库

修改表

1. 修改表名
   RENAME old_name TO new_name

2. 修改表结构

   • 添加列
     为 myemp 添加一列 hiredate :
     ALTER TABLE myemp ADD (hiredate DATE DEFAULT SYSDATE )
   • 删除列
     删除上面添加的字段
     ALTER TABLE myemp DROP(hiredate)
   • 修改列
     ALTER TABLE myemp MODIFY(job VARCHAR2(40) DEFAULT 'CLERK')

---

DML 语句

DML 可以对表中的数据进行操作,分为 : INSERT, UPDATE , DELETE
DML 是伴随事务 ( Transaction ) 使用的.

1. INSERT

INSERT INTO myemp (id, name, salary, job) VALUES (1, 'jack', 5000, 'CLERK' )
若不指定字段,则需全列插入
INSERT INTO myemp VALUES(...)
插入一个日期值
INSERT INTO myemp (id, name, salary, birth, job) VALUES (2, 'tom', 3000, TO_DATE('2008-08-08','YYYY-MM-DD'))

2. UPDATE

修改表中现有数据
UPDATE myemp SET gender='F', salary=9000 WHRER name='jack'

3. DELETE

删除数据
通常使用时添加 WHERE 条件,不添加则是清空表

---

DQL 语句

1. SELECT

查看表中所有字段及其记录
SELECT * FROM emp
查看表中的某几个字段
SELECT ename, job, sal FROM emp
SELECT 子句可以查看:字段,函数,表达式
SELECT ename, sal*12 FROM emp
SELECT 子句结合 WHERE 子句
SELECT ename, job,sal,deptno FROM emp WHERE deptno=20

字符串函数

• CONCAT(P1,P2) : 字符串拼接
  SELECT CONCAT(ename, sal) FROM emp;

• p1 || p2 || p3: 多字符拼接
  SELECT ename||':'||sal FROM emp

• LENGTH (p): 获取字符串长度
  SELECT ename, LENGTH(ename) FROM emp

• LOWER, UPPER, INICAP :小写,大写,首字母大写
  SELECT LOWER('HELLO WORLD'), UPPER('helloworld'), INITCAP('hello world') FROM dual

  伪表 : dual
  当查询内容与任何一张表无关时,为了满足 FROM子句的要求,可以使用伪表,伪表只会查出一条语句

• TRIM , LTRIM, RTRIM:去除字符串两边内容 (注意语法)
  SELECT TRIM('e' FROM 'eeeeliteeee') FROM emp
SELECT LTRIM('esesesliteeee','es') FROM dual
SELECT RTRIM('eeeeliteeee','e') FROM dual

• LPAD, RPAD

  补位函数,可以将指定内容指定位数,不足时则补充若干个指定的定义字符以达到位数

  SELECT LPAD(sal,6,'$') FROM emp
SELECT RPAD(sal,6,' ') FROM emp

• SUBSTR(str,m[,n])

  截取字符串 str 从m处开始,,连续截取n个,若不指定n,则是连续截取到字符串末尾
  注意:数据库中下标从 1 开始

  SELECT SUBSTR('thinking in java',10,2) FROM dual

• INSTR(str1, str2 [,n [,m]])

  查找位置 : 查找 str2 在 str1 中的位置,若指定了位置 n 从 n 处开始查找第一次出现的位置,若指定了 m 则是查找第 m 次出现的位置

  SELECT INSTR('thinking in java','in',4,2) FROM dual


---

数值函数

• ROUND(n [,m])

  对 n 进行四舍五入, m 是保留到小数点后多少位, 若 m 为 0 或不指定,则保留整数,若 m 为负数, 则是保留小数点前的位数

  SELECT ROUND(45.678 , 2), ROUND(45.678) FROM dual
SELECT ROUND(45.678 , -1), ROUND(55.678, -2),ROUND(45.76, -2) FROM dual

• TRUNC(n,[,m])

  数字截取函数,对 n 截取 m 位

  SELECT TRUNC(45.678, 2), TRUNC(45.678), TRUNC(55.678, -1) FROM dual

• MOD(n, m)

  求余数,若 m 为 0 ,则直接返回 n

  SELECT MOD(13,4) FROM dual

• CEIL(n)和 FLOOR(n)

  向上取整,向下取整

  SELECT CEIL(45.67) , FLOOR(45.67) FROM dual

---

日期类型

• DATE 与 TIMESTAMP

  两者都是常用的日期类型, DATE 为7个字节,TIMESTAMP 为11个字节,多出来的4个字节用来表示秒以下的精度

• 日期相关关键字

  SYSDATE : 表示一个内部函数,返回一个表示当前系统时间的 DATE 类型的值
  SYSTIMESTAMP : 返回时间戳类型的当前系统时间

• 日期类型比较大小

  越晚的越大,日期可以进行加减运算,加上一个数字等于加上指定的天数,减法同理.
  两个日期相减,差为相差的天数

  • 查看每个员工入职天数(取整)
    SELECT ename, CEIL(SYSDATE-hiredate) FROM emp
  • 查看 1982-1-1 以后入职的员工
    SELECT ename, hiredate FROM emp
WHERE hiredate > TO_DATE('1982-01-01','YYYY-MM-DD');

• TO_DATE()

  可以将一个字符串按照指定的日期格式转换成DATE

  RR : 两位数字表示年,会自动判定世纪
  1950-02-04
  SELECT TO_CHAR(TO_DATE('50-02-04','RR-MM-DD'),'YYYY-MM-DD') FROM dual
  2049-02-04
  SELECT TO_CHAR(TO_DATE('49-02-04','RR-MM-DD'),'YYYY-MM-DD') FROM dual

• TO_CHAR()

  可以将日期按照指定日期格式转换为字符串
  SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH12:MI:SS') FROM dual

  注意: 日期格式字符串中,出现的字符凡是不是英文与符号,其他字符全部要使用双引号包围
  SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"') FROM dual

• LAST_DAY(date)

  返回给定日期所在月的月底日期

  SELECT LAST_DAY(SYSDATE) FROM dual

• ADD_MONTHS(date , i)

  返回日期date 加上 i 个月后的日期值

  查看每个员工入职20周年纪念日
  SELECT ename, ADD_MONTHS(hiredate,20*12) FROM emp

• MONTHS_BETWEEN (date1, date2)

  查看两个日期之间相差多少月

  每个职工入职多少个月
  SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate) FROM emp

• NEXT_DAY(date , i)

  返回给定日期之后一周内的周几对应的日期

  SELECT NEXT_DAY(SYSDATE,4) FROM dual

• LEAST,GREATEST

  返回参数列表中 最小值 和 最大值

  SELECT LEAST(SYSDATE, TO_DATE('2008-08-08','YYYY-MM-DD')) FROM dual

  82 年以前返回1982-1-1, 82年以后返回入职日期
  SELECT ename, GREATEST(hiredate, TO_DATE('1982-01-01','YYYY-MM-DD')) FROM emp

• EXTRACT

  提取一个日期中指定的时间分量

  SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual
  查看1982年入职的员工
  SELECT ename, hiredate FROM emp WHERE EXTRACT(YEAR FROM hiredate )=1982

NULL

• NULL 的运算

  • 与字符串连接等于什么也没做
  • 与数字运算结果还是 NULL

• NVL(p1, p2)

  当 p1 为 NULL 时,函数返回 p2, 若 p1 不为 NULL ,则函数返回 p1 本身

  SELECT ename ,sal, comm,sal+NVL(comm, 0 ) FROM emp

• NVL2(p1, p2 , p3)

  若 p1 为 NULL 则返回 p3, 若 p1 不为 NULL 则返回 p2
  根据com字段是否为 NULL 判断有没有奖金

  SELECT ename, NVL2(comm,'有奖金','没奖金') FROM emp