关于Oracle数据库的一些自学整理

结构化查询语句(SQL)Structured Query Language
    数据定义语言(DDL)Data Definition Language
      create alter drop truncate
    数据操纵语言(DML)Data Manipulation Language
      insert update delete
    事务操纵语言(TCL)Transaction Control Language
      commit rollback savepoint
    数据查询语言(DQL)Data Query Control Language
      select 语句
    数据控制语言(DCL)Data Control Language
      grant 授权 revoke 收回权限 create user 创建用户
Oracle 数据类型
    number(p,s)--数字类型  p表示数字的总位数   s表示小数点后面的位数
    char(n)--占用n个字节的字符类型(上限2000)
    varchar2(n)--最多占用n个字节的字符类型(上限4000)
    date--用于定义日期时间的数据  长度7字节默认DD-MON-RR
创建表
    CREATE TABLE employee(
        id NUMBER(4),
        name VARCHAR2(20),--添加NOT NULL非空约束
        gender CHAR(1),--添加DEFAULT 'M'表示赋默认值M
        birth DATE,
        salary NUMBER(6,2),
        job VARCHAR2(30),
        deptno NUMBER(2)
    );
    DESC employee;
修改表
    RENAME employee TO employee1;--(更改表名)
    ALTER TABLE employee1 ADD(hiredate DATE DEFAULT sysdate);
    --添加列,且默认系统时间
    ALTER TABLE employee1 DROP(hiredate);
    --删除列
    ALTER TABLE employee1 MODIFY(gender CHAR(2) DEFAULT 'F');
    --修改列,只对后续插入的数据生效,而且长度由大改小可能失败
DML语句
    insert插入语句
      INSERT INTO employee1(id,name,gender,birth,salary,job,deptno) 
      VALUES(1001,'JACK','M',null,3000,'clerk',10);
    关于自定义格式日期
      TO_DATE('2017-3-30','YYYY-MM-DD')
    update更新语句
      UPDATE employee1 SET salary=4000 
      WHERE name='JACK';
    delete删除语句
      DELETE FROM employee1 WHERE ID=1001;
字符串类型
    CHAR定长,浪费空间,节省时间,最多1000个GBK汉字,默认为1字节
    VARVHAR2不定长,浪费时间,节省空间,最多2000个GBK汉字,不可缺省长度
    LONG 是VARCHAR2rTRI加长版,最多储存2GB字符串数据(限制多,每个表只能有一个,不能作为主键,不嗯呢该简历索引,等等)
    CLOB 储存4GB字符串字符串数据
  字符串函数
    CONCAT和"||"
      都是用来拼接字符串的,多个字符串连接操作符比函数更直观
    LENGTH(char)用于返回字符串的长度
    UPPER(char)大写LOWER(char)小写INITCAP(char)首字母大写
    TRIM(c2 FROM c1)从c1前后去除c2
    LTRIM(c1[,c2]) RTRIM(c1,[c2]) 如果没有c2,就去除空格
    LPAD(c1,n,c2)左补位函数RPAD(c1,n,c2)
    SUBSTR(c,m[,n])返回char中从m位开始取n个字符
    INSTR(char1,char2[,n[,m]])
      返回子串char2在源字符串char1中的位置
      从n位开始搜索,第m次出现
数值类型
    NUMBER(p,s)--p数字总位数,取值范围1-38;s小数位,没有默认整数
  数值函数
    ROUND(n[,m])四舍五入至小数后m位
    TRUNC(n[,m])用于截取小数后m位
    MOD(m,n)取余
    CEIL()向上取整 FLOOR()向下取整
日期类型
    DATE
      7个字节,①世纪+100②年③月④日⑤小时+1⑥分+1⑦秒+1
    TIMESTAMP
      前7个字节和DATE一样,第8-11字节纳秒
  日期关键字
    SYSDATE 系统时间 SYSTIMESTAMP
  日期转换函数
    字符串--->日期
      TO_DATE('2017-3-30','YYYY-MM-DD')
    日期--->字符串
      TO_CHAR(date,'YYYY"年"MM"月"DD"日"')
  日期常用函数
    LAST_DAY(date)返回日期当月的最后一天
    ADD_MONTHS(date,i)返回日期加上i个月
    MONTHS_BETWEEN(date1,date2)两个日期间相隔多少个月
    NEXT_DAY(date,char)返回下面的一个周几(注意Sunday是1,Monday是2)
    LEAST()最小日期GREASTEST()最大日期
    EXTRACT(date FROM datetime)从日期中提取指定数据
空值NULL的操作 
  NULL条件查询
    --NULL不等于任何值   IS   NULL
      SELECT * FROM student WHERE gender IS NULL
    非空约束 NOT NULL 
  空值函数
    NVL(e1,e2)--e1不为空,返回e1;e1为空,返回e2
    NVL2(e1,e2,e3)--e1不为空,返回e2;e1为空,返回e3
查询条件
  AND 并且(优先级高)  OR  或
  --模糊查询
  LIKE --%表示0到多个字符   _表示单个字符
  IN(list)--符合list中   
  NOT IN(list)--不符合list
  BETWEEN ...AND...--区间之间
  ANY--任一(例如:>ANY 大于最小值  <ANY  小与最大值)
  ALL--全部(例如:>ALL 大于最大值  <ALL  小于最小值)
  DISTINCT --过滤重复
排序
  ORDER BY 子句(NULL视作最大值)
    ASC升序(缺省值) DESC降序
聚合函数
  MAX()最大值 MIN()最小值
  AVG()平均值 SUM()总和
  COUNT()计算记录条数(忽略NULL值)
分组
  GROUP BY 子句
  --按条件进行分组
  HAVING 子句
  --只能跟在GROUP BY之后,限制分组结果
  SELECT deptno,MAX(sal) max_sal FROM emp 
  GROUP BY deptno HAVING MAX(sal)>4000;
  --查询每个部门的最高薪水,只显示部门最高薪水大于4000的部门及最大薪水
查询语句执行顺序
  from 一一>where 一一>group by 一一>having 一一>select 一一>order by
SQL(关联查询)
  笛卡尔积(t1中4条记录,t2中6条数据,查询结果24条记录)
    select t1.a,t2.b from t1,t2;
  内连接--返回所有满足连接条件的记录
    select t1.a,t2.b from t1 join t2 on t1.id=t2.id;
  外链接--除了满足连接条件之外的一些不满足条件的也会返回
    select t1.a,t2.b from t1 LEFT OUTER JOIN t2 ON t1.id=t2.id;
    LEFT|RIGHT|FULL [OUTER] JOIN  --理解驱动表的概念
  自连接
    select t.a,t.b from t t1 join t t2 on t1.pid=t2.uid;
子查询(select的结果作为select语句中的一部分)
oracle的分页查询
  ROWNUM 伪列(根据查出来的结果给上的序号,注意:是查出结果后才有的序号)
    select * from emp where rownum>3;--查不出数据
    select * from (select rownum rn,e.* from emp e)
    where rn BETWEEN 8 AND 10; 
    --每页5条数据,查询第二页的数据
    select * from(select e.*,rownum rn form (select * from emp)e  where rownum <=10)
    where rn>=6;
DECODE函数
    --当职位是'MANAGER'、'ANALYST'、'SALESMAN'时,分别为sal*1.2,sal*1.3,sal*1.5
    SELECT ename,job,sal,DECODE(job,
                                'MANAGER',sal*1.2,
                                'ANALYST',sal*1.3,
                                'SALESMAN',sal*1.5,
                                sal) bonus
    FROM emp;
CASE语句
    SELECT ename,job,sal,
        CASE job WHEN 'MANAGER' THEN sal*1.2
                  WHEN 'ANALYST' THEN sal*1.3
                  WHEN 'SALESMAN' THEN sal*1.5
                  ELSE sal END
        bonus
    FROM emp;
排序函数
  ROW_NUMBER ()OVER (PARTITION BY col1 ORDER BY col2);
    --组内序号连续且唯一(12345)
  --按照部门编码分组显示,每组内按照职员编码排序,并赋予组内编码
  SELECT deptno,ename,empno,
      ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS emp_id FROM empl
 
  RANK () OVER (PARTITION BY col1 ORDER BY col2);
   --跳跃排名(12245)
  DENSE_RANK()OVER(PARTITION BY col1 ORDER BY col2);
   --有并列且连续(12234) 
集合操作
  SELECT statement1
    [UNION|UNION ALL|INTERSECT|MINUS]
  SELECT statement2;
  UNION 与 UNION ALL 的差异--并集
    UNION 自动去掉合并后的重复记录,且对结果排序
    UNION ALL 返回所有行,包括重复,对结果不排序
  INTERSECT --交集
  MINUS --差集
高级分组函数
  GROUP BY ROLLUP(a,b,c)[/CUBE()/GROUPING SETS((a),(b))]
  ROLLUP的列从右到左以一次少一列进行分组直到所有列都去掉后的分组(全表分组)
  CUBE(a,b,c)就是以(a,b,c)(a,b)(a,c)(b,c)(a)(b)(c)+全表分组查询的集合
  GROUPING SETS  自定义分组
视图
  VIEW(虚表/视图)
    简单视图:基于单表,无任何函数运算,表达式或分组函数
    复杂视图:基于单表,但是包含了函数运算,表达式或分组函数
    连接视图:基于多表的视图
      作用:①简化复杂查询②限制数据访问
      语法: CREATE/REPLACE VIEW vname +select语句
    WITH CHECK OPTION 通过视图所做的修改,必须在视图的可见范围内
    WITH READ ONLY 只读
序列(SEQUENCE)
  CREATE SEQUENCE seq_name 
  [START WITH i]从i开始  默认序列第一个值是1,步进1
  [INCREMENT BY j]以j递增
  [MAXVALUE m]最大值为m [NOMAXVALUE]
  [MINVALUE n]最小值为n [NOMINVALUE]
  [CYCLE|NOCYCLE]循环/不循环;默认NOCYCLE
  [CACHE p]缓存p [NOCACHE]默认缓存20
    NEXTVAL获取序列下个值
    CURRVAL获取序列当前值
索引(INDEX)
  CREATE [UNIQUE]INDEX index_name ON  table(column[,column...])
  ALTER修改  DROP删除
约束(CONSTRAINT)
  -非空约束(NN) NOT NULL
  -唯一性约束(UK) Unique
  -主键约束(PK) Primary Key
    非空且唯一,一个表上只允许一个主键
  -外键约束(FK) Foreign Key
  ADD CONSTRAINT fk_name FOREIGN KEY(colname)REFERENCE dadTable(colname)
  -检查约束(CK) Check(条件)自定义条件