sql_oracle

-------sqlserver-------
新建表:CREATE TABLE table_name(column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY], column2 DATATYPE [NOT NULL],...） 
删除表:DROP table_name;
---------
增加:INSERT INTO table_name (column1,column2,...) values (value1,value2, ...)
把其它表的数据插入进来:INSERT INTO table_name (column1,column2,...) SELECT columnx,columny,... FROM another_table
修改:UPDATE table_name SET column1=’xxx’WHERE conditoins
删除:DELETE FROM table_name WHERE conditions
//最主要应该是查询:
select * from tableName;
select table_id,table_name from tableName (where conditoins);
SELECT column1,column2 FROM table_name ORDER BY column2 [DESC] 说明：ORDER BY 是指定以某个栏位做排序，[DESC]是指从大到小排列，若没有指明，则是从小到大..
1.整合性的查询:SELECT COUNT (*) FROM table_name WHERE column_name = xxx;
2.除此以外还有SUM()求和,AVG() 是计算平均、MAX()、MIN()计算最大最小值
SELECT * FROM table_name1 WHERE column1 BETWEEN xx AND yy;
SELECT * FROM table_name1 WHERE column1 IN (’xxx’,’yyy’,..);
SELECT * FROM table_name1 WHERE column1 LIKE ’x%';
SELECT * FROM table1,table2 WHERE table1.colum1=table2.column1 
求表中记录和:SELECT SUM(column1) FROM table_name;

----orcale----
--------------
conn hr/hr 
SELECT table_name from user_tables;
set linesize 200;/设置窗体属性...
set pagesize 15;
desc emp; //查看表的结构

创建表：
CREATE TABLE dept (deptno NUMBER(2),
		dname VARCHAR2(14),
		loc VARCHAR2(13));
drop table table_Name;
---
更新列:
/alter table [table_name] modify [column] [type];
删除列：
/drop column column_name;
------------
1: 有以下五种约束:
 not null:  last_name VARCHAR2(25) NOT NULL,
 unique :唯一性约束.
    alter table [table_name] add constraint [constraint_name] unique([column_name]);
 primary key:
 foreign key:
 check: alter table [table_name] add check ("SEX" IN('1','0'));
//缺省约束：
 alter table table_name modify [column_name] [type] default([sysdate])
--添加约束----
ALTER TABLE     employees
  add constraint emp_manager_fk 
  FOREIGN KEY(manager_id) 
  REFERENCES employees(employee_id);
----删除约束------
（1）：ALTER TABLE      employees
     DROP CONSTRAINT  emp_manager_fk;
（2）：ALTER TABLE	departments
     DROP PRIMARY KEY CASCADE;
-------------------
第一章: 
        基本查询...
        过滤where和排序order by数据---
-------
SELECT distinct deptno FROM emp;
between and 
SELECT ename FROM emp WHERE  ename like 'K%'  (%__)
SELECT * FROM emp where deptno in (10,20,30);
SELECT ename FROM emp WHERE comm is null;
(and or not)
SELECT * FROM emp order by hiredate;
select ename '姓名' from emp;
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:Ss)
----------------
1：分组函数：[AVG COUNT MAX MIN SUM] group by
2:使用 HAVING 过滤分组:
	(1):行已经被分组。
	(2):使用了组函数。
	(3):满足HAVING 子句中条件的分组将被显示。
SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;
-----------------
1： 多表查询：
   （1）：外连接：
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id(+) = d.department_id ; 
   （2）：自连接：
SELECT worker.last_name || ' works for ' 
       || manager.last_name
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;
------
叉集：使用CROSS JOIN 子句使连接的表产生叉集。
叉集和笛卡尔集是相同的。 
自然连接：NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
使用 USING 可以在有多个列满足条件时进行选择。
内连接：??????怎么这里始终不懂????
------
2:子查询:
子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询使用 (外查询)。
[in,any,all]
-------------------
第二章：
     单行函数:
1：字符控制函数：
    lower('SQL Course')_sql course
    upper('SQL Course')_SQL COURSE
    initcap('SQL Course')_Sql Course
2： 数字函数
    (1):四舍五入round(45.926, 2);
    (2):截断(保留小数点)trunc(45.926, 2);
    (3):求余mod(1600, 300)
3:  日期函数(日期格式的元素)
    (1):函数sysdate返回:日期,时间;
    (2):YYYY_MM_DD HH24:MI:SS AM
    (3)......
TO_CHAR 函数对日期的转换: to_char(hiredate,'YYYY_MM_DD HH24:MI:SS')
TO_CHAR 函数对数字的转换:
    SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees;
TO_NUMBER 和 TO_DATE 函数:
    select to_date('2010-10-12','YYYY-MM-DD') from dual;

<嵌套函数:
通用函数:
NVL函数:将空值转换成一个已知的值：
  (1):NVL(commission_pct,0)
  (2):NVL(hire_date,'01-JAN-97')
  (3):NVL(job_id,'No Job Yet')
>
<多表查询:
  
<分组查询:
----------------
第三章：控制语句：
 
(1): 条件表达式：
   在 SQL 语句中使用IF-THEN-ELSE 逻辑。
   使用两种方法: CASE 表达式，DECODE 函数；
SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "REVISED_SALARY"
FROM   employees;
------------
(2)：循环语句:
---
   declare
    v_no number := 0;
    begin
      loop
        v_no := v_no + 1;
        dbms_output.put_line('你当前的值是: ' || v_no);
      exit when v_no = 20;
      end loop;
    end;
------------
WHILE <布尔表达式> LOOP
    要执行的语句;
END LOOP;
-----------------
第四章：iSQL*Plus
    (1): 在查询中使用变量。
    (2): 熟悉 iSQL*Plus 环境。
    (3): 在变量名前加前缀 (&) 使用户输入值;
-----事务--------
UPDATE...
savepoint update_done;
//Savepoint created.
INSERT...
rollback to update_done;
//Rollback complete.
commit(提交...);
--------------
第五章：常见的数据库对象

(1): 表:基本的数据存储集合，由行和列组成。
(2): 视图: 表中抽出的逻辑上相关的数据集合。
(3): 序列: 提供有规律的数值（自动提供唯一的数值，主要用于提供主键值）。
(4): 索引: 提高查询的效率
(5): 同义词: 给对象起别名
-----
创建视图：
CREATE VIEW 	empvu80
 AS SELECT  employee_id, last_name, salary
    FROM    employees
    WHERE   department_id = 80;
<<View created>>
----
描述视图：DESCRIBE empvu80
查询视图：SELECT * FROM salvu50;
删除视图：DROP VIEW view;
-----创建序列-----
CREATE SEQUENCE dept_deptid_seq
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NOCACHE
                NOCYCLE;
----
SELECT	sequence_name, min_value, max_value, 
	increment_by, last_number
FROM	user_sequences;
----
nextval: 返回序列中下一个有效的值，任何用户都可以引用
currval: 中存放序列的当前值 
NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效
----
删除序列：DROP SEQUENCE dept_deptid_seq;
-------------------
----------------------

第六章：集合处理：
UNION/UNION ALL；
INTERSECT；
MINUS



-----------------
----pl/sql:-----
PL/SQL提高了性能;PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成 
-------------
 1: 声明变量...
   （1）：DECLARE
       	   V_Description  VARCHAR2(50);
	   V_Number       NUMBER := 45;
	   V_Counter      BINARY_INTEGER := 0;

           T_no emp.empno%TYPE; 
 2：%TYPE:定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同:
-----
declare
 v_empno emp.empno%type := &no;
 rec emp%rowtype;
 begin 
  select * into rec from emp where empno = v_empno;
  dbms_output.put_line('员工姓名: ' || rec.ename || '员工工资: ' || rec.sal
      ||'员工部门: ' || rec.deptno);
 end;
------
（1）：if语句：
IF <布尔表达式> THEN
		PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
		其它语句
ELSIF < 其它布尔表达式> THEN
		其它语句
…… ……
ELSE
		其它语句
END IF;
--------------
（2）：case语句：
---
DECLARE
	V_grade char(1) := UPPER(‘&p_grade’);
	V_appraisal VARCHAR2(20);
BEGIN
	V_appraisal :=
	CASE v_grade
		WHEN ‘A’ THEN ‘Excellent’
		WHEN ‘B’ THEN ‘Very Good’
		WHEN ‘C’ THEN ‘Good’
		ELSE ‘No such grade’
	END;
  DBMS_OUTPUT.PUT_LINE(‘Grade:‘||v_grade||’  Appraisal: ‘|| v_appraisal);

-------------
（3）：循环语句:
---
   declare
    v_no number := 0;
    begin
      loop
        v_no := v_no + 1;
        dbms_output.put_line('你当前的值是: ' || v_no);
      exit when v_no = 20;
      end loop;
    end;
------------
WHILE <布尔表达式> LOOP
    要执行的语句;
END LOOP;
-------
---------------
0：异常处理：
--
DECLARE
   v_empno emp.empno%TYPE :=&empno;
   v_sal    emp.sal%TYPE;
BEGIN
   SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
   IF v_sal<=1500 THEN 
        UPDATE emp SET sal=sal+100 WHERE empno=v_empno; 
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');     
   ELSE
			  DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');
   END IF; 
-------------
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
     DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');
   WHEN TOO_MANY_ROWS THEN
     DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);
END; 
-------------
用户自定义的异常处理：
DECLARE
   v_empno emp.empno%TYPE :=&empno;
   no_result  EXCEPTION;
BEGIN
   UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
EXCEPTION
   WHEN no_result THEN 
      DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);
END; 
---------------

第六章： 
    游标：[显示游标，隐示游标]
----
序列........

创建和删除序列

  例1：创建序列：

  CREATE SEQUENCE ABC INCREMENT BY 1 START WITH 10 MAXVALUE 9999999 NOCYCLE NOCACHE;

  执行结果：

  序列已创建。

      例2：删除序列：

         drop SEQUENCE ABC;

      执行结果：   序列已删除.

注意：操作者必须是序列的拥有者、或拥有drop该sequence权限

伪列--->nextval、currval

select 序列名.currval from dual;得到序列的“当前值”

select 序列名.nextval from dual;得到序列数值中下一个值

向数据库中插入时、如下使用：

insert into 表1 values(序列名.nextval,'tom');

修改序列--->

alter sequence +序列名 


第七章：
     函数//过程//
     过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

--变量----
declare
 --v_name emp.ename%type;
 v_empno emp.empno%type := &no;
 rec emp%rowtype; 
 begin
  -- select ename into v_name from emp where empno = v_empno;
   select * into rec from emp where empno = v_empno;
   --dbms_output.put_line('您查询的人的姓名为: ' || v_name);
   dbms_output.put_line('姓名' ||rec.ename ||'工作'|| rec.job||'工资'||rec.sal);
   end;
-------循环语句--------
LOOP
  要执行的语句;
  EXIT WHEN <条件语句> /*条件满足，退出循环语句*/
END LOOP; 
-------
declare
 v_number number := 0;
 begin
   while v_number < 10 loop
     dbms_output.put_line(v_number);
     v_number := v_number + 1;
     end loop;
   end;
---判断语句--
declare
 v_sal emp.sal%type;
 v_empno emp.empno%type := &no;
begin
  select sal into v_sal from emp where empno = v_empno;
  if v_sal > 2000 then
    dbms_output.put_line('工资一般:' || v_sal);
    elsif v_sal > 1000 then
    dbms_output.put_line('很少的工资: ' || v_sal);
    else
      dbms_output.put_line('换工作吧...');
      end if;
  end;
------case语句-----------
declare
 v_char char(1) := '&no_char';
 v_getGrade varchar2(10);
begin
  v_getGrade :=
    case v_char
      when 'A' then 'very good'
      when 'B' then 'good'
      when 'C' then 'ok'
      else 'very bead!' 
     end;
   dbms_output.put_line(v_getGrade);
  end;
-------Exception----------
declare
 v_mgr emp.mgr%type := &no;
 v_name emp.ename%type;
 v_sal emp.sal%type;
begin
  select ename,sal into v_name,v_sal from emp where mgr = v_mgr;
  if v_sal > 2000 then
    dbms_output.put_line('2000+:' || v_name ||'工资:'||v_sal);
    elsif v_sal > 1500 then 
    dbms_output.put_line('1500+:' || v_name ||'工资:'||v_sal);
    else
      dbms_output.put_line('800+:' || v_name ||'工资:'||v_sal);
    end if;
    exception 
      when no_data_found then 
        dbms_output.put_line('没有这个管理者!');
      when too_many_rows then 
        dbms_output.put_line('多条记录,请使用游标!');          
      when others then 
        dbms_output.put_line(sqlcode ||' '||sqlerrm);
  end;
------游标----
--显示游戏,隐示游标
declare
 v_name emp.ename%type;
 v_sal emp.sal%type;
 v_count number;
 --v_empno emp.empno%type := &no;
 cursor emp_cursor is select ename,sal from emp; 
--cursor c_cursor is select ename,sal from emp where rownum < 5;
begin
  open emp_cursor;
  loop
   fetch emp_cursor into v_name,v_sal;    
    exit when emp_cursor%notfound;
    dbms_output.put_line('姓名: ' || v_name ||'工资: ' || v_sal);
  end loop;
  dbms_output.put_line('总记录数为: '||emp_cursor%rowcount);
  close emp_cursor;
end;
-----游标检索!---------
declare
 v_empno emp.empno%type;
 v_sal emp.sal%type;
 v_count number := 0;
 cursor e_cursor is select empno,sal from emp;
 begin
   open e_cursor;
     fetch e_cursor into v_empno,v_sal;
     while e_cursor%found loop
     if v_sal > 2000 then
       update emp set sal = sal + 50 where empno = v_empno;
       dbms_output.put_line('编号为'||v_empno||'的工资已经更新');
       v_count := v_count + 1;
       end if;
       fetch e_cursor into v_empno,v_sal;
     end loop;     
      dbms_output.put_line('更新记录条数是: '||v_count);
      dbms_output.put_line('总记录数是: '||e_cursor%rowcount);
     close e_cursor;
   end;
-------------------
--创建函数--
  create or replace function get_sal(
 dept_no number ,emp_count out number)
 return number
 is
  v_sum number;
begin
  select sum(sal),count(*) into v_sum,emp_count
    from emp where deptno = dept_no;
    return v_sum;
  exception
    when no_data_found then
      dbms_output.put_line('没有你耍要的数据');
    when others then
      dbms_output.put_line(sqlcode||':'||sqlerrm);
  end get_sal;
--调用函数--
  declare
   v_sum number;
   v_count number;
  begin
    v_sum := get_sal(20,v_count);
    dbms_output.put_line('20号部门工资总合为:' ||v_sum ||'总记录为: ' ||v_count);
    end;
------------------------
--创建过程--
create or replace procedure getDelete(
 v_empno in emp.empno%type)
 as  --is-- 
  no_result exception;
begin
  delete from emp where empno = v_empno;
  if sql%notfound then
    raise no_result;
  end if;
  dbms_output.put_line('编码为'||v_empno||'的员工已除名!');
  exception
    when no_result then
      dbms_output.put_line('你耍要的数据不存在');
    when others then
      dbms_output.put_line(sqlcode||' '||sqlerrm);
  end getDelete;
--调用过程--
declare
 begin
   getDelete(7369);
   end;
--------触发器----------- 
create or replace trigger del_emp
  before delete on emp for each row
begin
  insert into emp_his(empno,ename,job,mgr,sal,hiredate,comm)
   values(:old.empno,:old.ename,:old.job,:old.mgr,
     :old.sal,:old.hiredate,:old.comm);
  end;
----------新建一个表---------
create table emp_his 
 as
 select * from emp where 1 = 2;
----
--delete from emp where deptno=20;
----
--rollback;
-------------------------------
--select * from emp_his