Oracle基础知识笔记之一

1、建表
CREATE TABLE ##(
  ...
)


删表
DROP TABLE ##;;


CREATE TABLE a_students(
 name char(8)
 
);


CREATE TABLE  a_employee1(
    id NUMBER(4),
    name CHAR(8) DEFAULT 'M',
    age CHAR(1),
    salary NUMBER(8),
    department VARCHAR2(20)
);


2、加列
ALTER TABLE ##
ADD (。。。)


删列
ALTER TABLE ##
DROP (...)


ALTER TABLE a_employee3
ADD (hriedate DATE DEFAULT SYSDATE);


ALTER TABLE a_employee3
DROP (hriedate);


3、修改列
ALTER TABLE ##
MODIFY (...);




ALTER TABLE a_employee3
MODIFY (agee NUMBER(3) DEFAULT 0); // error 不能修改表中没有的列




4、插入数据
INSERT INTO VALUES(...);


INSERT INTO ##(...) VALUES(...);




INSERT INTO a_employee3
VALUES(1, 'AAA', 33, 'F', 22222, 'CLERK', TO_DATE('2009-11-11', 'YYYY-MM-DD'), TO_DATE('2009-3-4', 'YYYY-MM-DD'));


INSERT INTO a_employee3(name, age, hiredate
) VALUES('bbb', 21, TO_DATE('2009-8-31', 'YYYY-MM-DD'));


5、修改数据
UPDATE ##
SET ...
WHERE


UPDATE a_employee3
SET age = 22
WHERE salary = 22222;


UPDATE a_employee3
SET name = 'fff'
WHERE age = 22;




6、删除数据
DELETE FROM ##
WHERE ...


DELETE FROM a_employee3
WHERE id = 1;






TRUNCATE TABLE a_employee1;


DROP TABLE a_employee1;


7、查询
SELECT * FROM ##;
SELECT ... FROM ##;


8、||  等价于 +   CONCAT() 函数也有同样的功能
SELECT name||'"的工资是"'||salary
FROM a_employee3;


SELECT CONCAT(CONCAT(name, '"的工资是"'), salary)
FROM a_employee3;


9、SELECT 'hello'||'world'
FROM DUAL(虚表)


DUAL：虚表，没有这么一个表，只为了满足
        SELECT的语法要求。
我们常用虚表来测试表达式的结果。
在数据库中，我们想测试某个表达式的结果只能
使用SELECT语句来实现。


SELECT 'HELLO' FROM DUAL;
SELECT 'hello'||'world'
FROM DUAL;


10、LENGTH() 函数  若数据不为空， 则输出这个数据所占字节数
SELECT name, LENGTH(name), LENGTH(department), LENGTH(salary) FROM a_employee3;


11、 INITCAP()  函数 若数据不为空， 则把数据的首字母变成大字母
SELECT INITCAP(name), INITCAP(department) FROM a_employee3;


12、TRIM() 函数  参数中from前面只能是单一字符
若没有from以及前面的字符，则是去除空白
SELECT TRIM('e' from 'kkkedddeeellleliteeeeee')
FROM DUAL;


result： kkkedddeeelllelit


13、LTRIM(), RTRIM()
SELECT LTRIM('kkkedddeeellleliteeeeee','e')
FROM DUAL;


result:kkkedddeeellleliteeeeee


SELECT RTRIM('kkkedddeeellleliteeeeee','e')
FROM DUAL;


result:kkkedddeeelllelit
同样的，不指定第二个参数，默认是去除空白


14、
SELECT LPAD(name,20,'$')
FROM a_employee3;
作用:要求显示20个字符，若sal的值不足长度，则
补充若干个'$'，以达到20个字符


result:$$$$$$$$$$$$Bbb     


SELECT RPAD('aaaaAAAAAA',5,'$')
FROM DUAL;


result:aaaaA


15、INSTR()
SELECT 
  INSTR('Doctor Who Who Who', 'Who',9,2) 
FROM DUAL;


result：16


16、SYSTIMESTAMP
SELECT SYSTIMESTAMP FROM DUAL;


result：19-1月 -17 05.51.26.878000000 下午 +08:00


17、  与date操作关系最大的就是两个转换函数：to_date(),to_char()
      to_date() 作用将字符类型按一定格式转化为日期类型：
      具体用法:to_date(''2004-11-27'',''yyyy-mm-dd''),前者为字符串，后者为转换日期格式，注意，前后两者要以一对应。
      如;to_date(''2004-11-27 13:34:43'', ''yyyy-mm-dd hh24:mi:ss'') 将得到具体的时间
      多种日期格式：
      YYYY：四位表示的年份 
      YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪 
      MM：01~12的月份编号 
      MONTH：九个字符表示的月份，右边用空格填补 
      MON：三位字符的月份缩写 
      WW：一年中的星期 
      D：星期中的第几天 
      DD：月份中的第几天 
      DDD：年所中的第几天 
      DAY：九个字符表示的天的全称，右边用空格补齐 
      HH，HH12：一天中的第几个小时，12进制表示法 
      HH24：一天中的第几个小时，取值为00~23 
      MI：一小时中的分钟 
      SS：一分钟中的秒 
      SSSS：从午夜开始过去的秒数
      to_char():将日期转按一定格式换成字符类型
      
      
SELECT 
  TO_DATE(
    '2008-08-12 22:45:33',
    'yyyy-mm-dd hh24:mi:ss'
  )
FROM 
  DUAL;
  
  result：12-8月 -08
  
  SELECT 
  TO_CHAR(
    sysdate,
   'yyyy"年"mm"月"dd"日" hh:mi:ss' 
  )
FROM dual;


result：2017年01月19日 06:06:35


18、两个日期可以进行减法操作，差为相差的天数。
SELECT sysdate-hiredate,name
FROM a_employee3;


init:ID NAME AGE GENDER SALARY DEPARTMENT HIREDATE HRIEDATE
Bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
ggg     0  M 32444 clerk 19-1月 -17 19-1月 -17
ggg     0   M 32444 clerk 19-1月 -17 19-1月 -17


result:
2698.755393518518518518518518518518518519 Bbb     
2698.755393518518518518518518518518518519 bbb     
2698.755393518518518518518518518518518519 bbb     
2698.755393518518518518518518518518518519 bbb     
2698.755393518518518518518518518518518519 bbb     
0.0521990740740740740740740740740740740741 ggg     
0.0511689814814814814814814814814814814815 ggg     




19、MONTHS_BETWEEN()


SELECT name, 
  MONTHS_BETWEEN(SYSDATE, hiredate)
FROM a_employee3;


init:
init:ID NAME AGE GENDER SALARY DEPARTMENT HIREDATE HRIEDATE
Bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
bbb     21 M          31-8月 -09 19-1月 -17
ggg     0  M 32444 clerk 19-1月 -17 19-1月 -17
ggg     0   M 32444 clerk 19-1月 -17 19-1月 -17


result:
Bbb     88.63737455197132616487455197132616487455
bbb     88.63737455197132616487455197132616487455
bbb     88.63737455197132616487455197132616487455
bbb     88.63737455197132616487455197132616487455
bbb     88.63737455197132616487455197132616487455
ggg     0
ggg     0


20、NEXT_DAY()
SELECT NEXT_DAY(SYSDATE,6)
FROM dual;


result:20-1月 -17
获取距离当前时间最近的周4，若给定的当前时间
恰巧是周4，则表示的是下周的周4


21、IS NOT NULL
SELECT * 
FROM a_employee3
WHERE salary IS NOT NULL;
任何值都不能等于null


22、NVL()  NVL函数的作用:将null替换为给定值
SELECT name,NVL(gender,'M')
FROM a_employee3;


result：
Bbb     M
bbb     M
bbb     M
bbb     M
bbb     M
ggg     M
ggg     M


SELECT name,salary,NVL(age,0)
FROM a_employee3;


result:
Bbb     21
bbb     21
bbb     21
bbb     21
bbb     21
ggg     32444 0
ggg     32444 0


23、
SELECT name,salary+NVL(salary,0)
FROM a_employee3;


result:
Bbb    
bbb    
bbb    
bbb    
bbb    
ggg     64888
ggg     64888


SELECT name,NVL2(salary,'有奖金','没奖金')
FROM a_employee3;


result:
Bbb     没奖金
bbb     没奖金
bbb     没奖金
bbb     没奖金
bbb     没奖金
ggg     有奖金
ggg     有奖金


COMMIT;


DROP TABLE students;


DESC a_employee1;