最基本常用的sql语句，适用初学者

增删改
插入数据
INSERT INTO t_emp(...)
VALUES(...);
更新
UPDATE t_emp SET e_job='teacher',e_name='abc'
WHERE e_id=1001;
删除
DELETE FROM t_emp WHERE e_id=1001;
TRUNCATE TABLE t_emp;
______________________________________________________________________________________________________________________________
简单查询
查询
SELECT * FROM t_emp;
SELECT e_id,e_name FROM t_emp;
条件查询
SELECT * FROM t_emp
WHERE e_job='teacher';

SELECT * FROM t_emp
WHERE e_job<>'teacher';
去重查询
SELECT DISTINCT e_job FROM t_emp;
使用算数表达式
SELECT e_name,e_sal*12 FROM t_emp;
表中若有Null并且需要参加运算，语句如下(e_comm可能为Null):
SELECT e_name,(e_sal+nvl(e_comm,0))*12 FROM t_emp;
给查询结果列起别名
SELECT e_name AS(可以省略) "姓名" FROM t_emp;
查询结果字符串进行拼接
SELECT e_name||'的工资是'||e_sal FROM t_emp;
模糊查询
SELECT * FROM t_emp
WHERE e_name LIKE '_A%';
条件或
SELECT * FROM t_emp
WHERE e_age=19 or e_age=20;

SELECT * FROM t_emp
WHERE e_age in (19,20);

SELECT * FROM t_emp
WHERE e_age not in (17,18);
条件与
SELECT * FROM t_emp
WHERE e_age=19 AND e_age=20;

SELECT * FROM t_emp
WHERE e_age BETWEEN 19 AND 20;(头尾都包含)
包含null查询
SELECT * FROM t_emp
WHERE e_job IS NULL;
排序
SELECT * FROM t_emp
ORDER BY e_age ASC(升序，可以省略);

SELECT * FROM t_emp
ORDER BY e_age DESC(降序，必须写);

SELECT * FROM t_emp
ORDER BY e_age ASC,e_sal DESC;(在年龄升序的前提下，工资降序)
分组
SELECT * FROM t_emp
GROUP BY e_age;
______________________________________________________________________________________________________________________________
复杂查询
查询最大最小值
SELECT MAX(e_sal),MIN(e_sal) FROM t_emp;
查询平均值和总和
SELECT AVG(e_sal),SUM(e_sal) FROM t_emp;
查询出现总次数
SELECT COUNT(e_name) FROM t_emp;

SELECT COUNT(*) FROM t_emp;(获取表中总行数)
查询条件中套分组函数
SELECT e_name,e_sal FROM t_emp
WHERE e_sal=(
SELECT MAX(e_sal) FROM t_emp
);

SELECT e_name,e_sal FROM t_emp
WHERE e_sal>(
SELECT AVG(e_sal) FROM t_emp
);
分组加函数
SELECT MAX(e_sal),AVG(e_sal) FROM t_emp
GROUP BY e_deptno;


SELECT MIN(e_sal) FROM t_emp
GROUP BY e_deptno,e_job
ORDER BY e_deptno;
分组后筛选
SELECT AVG(e_sal) FROM t_emp
GROUP BY e_deptno
HAVING AVG(e_sal)<2000;
______________________________________________________________________________________________________________________________
子查询
单行单列
SELECT e_name FROM t_emp
WEHRE e_age=(
SELECT e_age FROM t_emp
WHERE e_name=xx;
);
多行单列
SELECT * FROM t_emp
WHERE e_job in(
SELECT DISTINCT e_job FROM t_emp
WHERE e_deptno=10;
);
单行多列
SELECT * FROM t_emp
WHERE (e_deptno,e_job)=(
SELECT e_deptno,e_job FROM t_emp
WHERE e_name='Smith'
);
all的使用
SELECT * FROM t_emp
WHERE e_sal>ALL(
SELECT e_sal FROM t_emp
WHERE e_deptno=30;
);
any的使用
SELECT * FROM t_emp
WHERE e_sal>ANY(
SELECT e_sal FROM t_emp
WHERE e_deptno=30
);
查询工资高于自己所在部门平均工资的员工
SELECT * FROM t_emp a,(
SELECT e_deptno,AVG(e_sal) vsal FROM t_emp
GROUP BY e_deptno;
) b
WHERE a.e_deptno=b.e_deptno
AND a.e_sal>b.vsal;
______________________________________________________________________________________________________________________________
分页查询
SELECT * FROM (
SELECT e.*,ROWNUM e_rn FROM (SELECT * FROM t_emp) e
WHERE ROWNUM<=60 ↑需要分页的数据源，此处就为表t_emp
)
WHERE e_rn>=51;

SELECT * FROM (SELECT e.*,ROWNUM e_rn FROM t_emp e)
WHERE e_rn>=51
AND e_rn<=60;
附mysql分页查询:
SELECT * FROM emp LIMIT 21,10;
  ↑从第几行开始，一页多少行
______________________________________________________________________________________________________________________________
多表查询
SELECT e.e_name,e.e_sal,d.d_name
FROM t_emp e,t_dept d
WHERE e.deptno=d.deptno;

SELECT e.e_name d.d_name,s.grade
FROM t_emp e,t_dept d,t_salgrade s
WHERE e.deptno=d.deptno
AND e.e_sal>=s.losal
AND e.e_sal<=s.hisal;
自连接查询
SELECT e.e_name,x.e_ename 
FROM t_emp e,t_emp x
WHERE e.mgr=x.empno;
内连接查询
SELECT e.e_name,e.e_sal,d.d_name 
FROM t_emp e INNER JOIN t_dept d
ON e.deptno=d.deptno;

SELECT e.e_name,d.d_name,s.grade
FROM t_emp e INNER JOIN t_dept d
ON e.deptno=d.deptno
INNER JOIN t_salgrade s
ON e.e_sal BETWEEN s.losal AND s.hisal;
外连接查询
SELECT * FROM t_emp e LEFT JOIN t_dept d
ON e.deptno=d.deptno;

SELECT * FROM t_emp e RIGHT JOIN t_dept d
ON e.deptno=d.deptno;

SELECT * FROM t_emp e FULL JOIN t_dept d
ON e.deptno=d.deptno;
______________________________________________________________________________________________________________________________
合并查询
去重并集(并集不去重、交集、差集)
SELECT e_name FROM t_emp
UNION(UNION ALL\INTERSECT\MINUS)
SELECT e_name FROM t_emp2;
______________________________________________________________________________________________________________________________
约束
已经建好的表添加约束:
给指定列添加非空约束
ALTER TABLE t_emp 
MODIFY e_id NOT NULL;
删除非空约束
ALTER TABLE t_emp 
MODIFY e_id NULL;
给指定列添加检查约束 xx为自定义约束名
ALTER TABLE t_emp 
ADD CONSTRAINT xx CHECK(e_age<150 and e_age>0);
给指定列添加唯一约束
ALTER TABLE t_emp 
ADD CONSTRAINT xx UNIQUE(e_id);
给指定列添加主键约束
ALTER TABLE t_emp 
ADD CONSTRAINT xx PRIMARY KEY(e_id);
给从表t_emp2指定列e2_id添加外键约束
ALTER TABLE t_emp2 
ADD CONSTRAINT xx FOREIGN KEY(e2_id)
REFERENCES t_emp(e_id);
建表时添加约束
列级定义
CREATE TABLE t_emp(
e_id NUMBER(10) PRIMARY KEY,
e_name VARCAHR2(20) NOT NULL,
e_age NUMBER(3) CHECK (e_age>0 and e_age<150),
e_no NUMBER(10) REFERENCES t_class(c_id),
...
);
表级定义
CREATE TABLE t_emp(
e_id NUMBER(10),
e_name VARCHAR2(20),
e_age NUMBER(3),
CONSTRAINT xx PRIMARY KEY(e_id),
CONSTRAINT xx CHECK(e_age<150 and e_age>0),
...
);
删除具有约束名的约束
ALTER TABLE t_emp
DROP CONSTRAINT xx;
______________________________________________________________________________________________________________________________
表
建表
CREATE TABLE t_emp(
e_id NUMBER(12),
e_job VARCHAR2(20),
    ...
);
查询表结构
DESC t_emp;
添加列
ALTER TABLE t_emp 
ADD (e_name VARCHAR2(20));
删除列
ALTER TABLE t_emp 
DROP COLUMN e_name;
修改列的长度和列的数据类型
ALTER TABLE t_emp 
MODIFY(e_name number(12));
修改列名
ALTER TABLE t_emp 
RENAME COLUMN e_name TO new_name;
修改表名
RENAME t_emp TO new_emp;
删除表
DROP TABLE t_emp;
______________________________________________________________________________________________________________________________
序列
创建序列
CREATE SEQUENCE mysequence
START WITH 10001
INCREMENT BY 2
MAXVALUE 10030
MINVALUE 10001;
获取下一个值
SELECT mysequence.NEXTVAL FROM dual;
获取当前序列的值
SELECT mysequence.CURRVAL FROM dual;
作主键
INSERT INTO t_emp VALUES(mysequence.NEXTVAL,'e_name'...);
删除序列
DROP SEQUENCE mysequence;
______________________________________________________________________________________________________________________________
事务
保存点
SAVEPOINT a;
返回保存点
ROLLBACK TO a;
提交
COMMIT;
回滚
ROLLBACK;
______________________________________________________________________________________________________________________________
pl/sql数据类型
查询
DECLARE
v_no NUMBER(10);
v_loc VARCHAR2(20);
BEGIN
SELECT deptno,loc INTO v_no,v_loc
FROM dept
WHERE deptno=30;
END;
有异常处理的
DECLARE
v_loc VARCHAR2(20);
BEGIN
SELECT loc INTO v_loc
FROM dept
WHERE deptno=&no;
dbms_output.put_line(v_loc);
EXCEPTION
WHEN no_data_found
THEN dbms_output.put_line('你找的不存在');
END;
%TYPE的用法
DECLARE
v_loc dept.loc%TYPE;
BEGIN
SELECT loc INTO v_loc
FROM dept
WHERE deptno=&no;
dbms_output.put_line(v_loc);
END;
%ROWTYPE的用法
①
DECLARE
v_erow emp%ROWTYPE;
BEGIN
SELECT * INTO v_erow
FROM emp;
dbms_output.put_line(v_erow.ename);
dbms_output.put_line(v_erow.e_sal);
...
END;
②
DECLARE
v_erow emp%ROWTYPE;
BEGIN
SELECT ename,e_sal INTO v_erow.ename,v_erow.e_sal
FROM emp;
dbms_output.put_line(v_erow.ename);
dbms_output.put_line(v_erow.e_sal);
END;
复合类型(记录类型)
DECLARE
TYPE emp_table IS RECORD(
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE,
v_dname VARCHAR(10)
);
emp_t tmp_table;
BEGIN
SELECT e.ename,e.sal,d.dname
INTO emp_t
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.empno=&no;
dbms_output.put_line(emp_t.v_ename);
dbms_output.put_line(emp_t.v_sal);
dbms_output.put_line(emp_t.v_dname);
END;
复合类型(表类型)
DECLARE
TYPE emp_type_table 
IS TABLE OF VARCHAR2(20) 
INDEX BY BINARY_INTEGER;
emp_table emp_type_table;
BEGIN
SELECT e.ename,e.sal,d.dname
INTO emp_table(1),emp_table(-1),emp_table(0)
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.empno=&no;
dbms_output.put_line(emp_table(1));
dbms_output.put_line(emp_table(-1));
dbms_output.put_line(emp_table(0));
END;
______________________________________________________________________________________________________________________________
pl/sql结构
判断结构
DECLARE
v_num NUMBER(10):=&no;
BEGIN
IF v_num>0 THEN 
dbms_output.put_line(v_num||'是个正数');
ELSEIF v_num=0 THEN 
dbms_output.put_line(v_num||'是0');
ELSE 
dbms_output.put_line(v_num||'是个负数');
END IF;
END;
选择结构
DECLARE
v_score NUMBER(3):=&score;
v_grade VARCHAR2(6);
BEGIN
CASE 
WHEN v_score<60 THEN
dbms_output.put_line('不及格');
WHEN v_score>=60 AND v_score<75 THEN
dbms_output.put_line('中等');
WHEN v_score>=75 AND v_score<90 THEN
dbms_output.put_line('良好');
ELSE
dbms_output.put_line('优秀');
END CASE;
END;
循环结构
① DECLARE
v_num NUMBER(3):=1;
BEGIN
WHILE v_num<=100 LOOP
dbms_output.put_line('v_num='||v_num);
v_num:=v_num+1;
END LOOP;
END;

② DECLARE
v_num NUMBER(3):=1;
BEGIN
LOOP
dbms_output.put_line('v_num='||v_num);
v_num:=v_num+1;
EXIT WHEN v_num>100;
END LOOP;
END;

③ BEGIN
FOR i IN 1..100 LOOP
dbms_output.put_line('i='||i);
END LOOP;
END;
跳转结构
BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line('i='||i);
IF i=5 THEN
GOTO hello;
END IF;
END LOOP;
<<hello>>
dbms_output.put_line('aaa');
dbms_output.put_line('bbb');
END;
______________________________________________________________________________________________________________________________
pl/sql数据对象
创建存储过程
CREATE PROCEDURE addComm(v_no IN NUMBER) IS
v_comm emp.comm%type;
BEGIN
SELECT comm INTO v_comm
FROM emp
WHERE empno=v_no;
IF v_comm IS NULL THEN
UPDATE emp SET comm=200
WHERE empno=v_no;
ELSE
UPDATE emp SET comm=comm+200
WHERE empno=v_no;
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('该员工不存在');
END;
创建函数
CREATE FUNCTION getYearSal(v_no IN NUMBER) RETURN NUMBER IS
v_ysal NUMBER(7,2);
BEGIN
SELECT (sal*12) INTO v_ysal
FROM emp
WHERE empno=v_no;
RETURN v_ysal;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('操作有误');
END;
声明包
CREATE PACKAGE mypack IS
PROCEDURE editComm(v_no IN NUMBER);
FUNCTION getYsByNo(v_no IN NUMBER) RETURN NUMBER;
END;
编写包中的实现
CREATE PACKAGE BODY mypack IS
PROCEDURE editComm(v_no IN NUMBER) IS 
v_comm NUMBER(7,2);
BEGIN
...(该存储过程具体实现方法)
END;
FUNCTION getYsByNo(v_no IN NUMBER) RETURN NUMBER IS 
v_sal NUMBER(7,2);
BEGIN
...(该函数具体实现方法)
END;
END;
游标的使用
① DECLARE
CURSOR emp_cursor IS
SELECT * FROM EMP;
v_emphang emp%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emphang;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_emphang.ename);
END LOOP;
CLOSE emp_cursor;
END;

② DECLARE
CURSOR emp_cursor IS
SELECT * FROM EMP; (类似于java中增强for循环)
BEGIN
FOR cr IN emp_cursor LOOP
dbms_output.put_line(cr.ename);
END LOOP;
END;
______________________________________________________________________________________________________________________________
触发器
语句级触发器
CREATE TRIGGER tg1
BEFORE INSERT OR UPDATE OR DELETE
ON t_emp
BEGIN
IF INSERTING THEN
dbms_output.put_line('您执行的是添加操作');
ELSEIF UPDATING THEN
dbms_output.put_line('您执行的是修改操作');
ELSE
dbms_output.put_line('您执行的是删除操作');
END IF;
END;
行级触发器
CREATE TRIGGER tg2
AFTER DELETE OR UPDATE OR INSERT
OF e_name   -------->(绑定指定列)
ON t_emp
FOR EACH ROW ----------->(指定为行级触发器)
BEGIN
IF UPDATING THEN
dbms_output.put_line('添加了一行');
ELSEIF INSERTING THEN
dbms_output.put_line('插入了一行');
ELSE
dbms_output.put_line('删除了一行');
END IF;
END;
复合触发器
CREATE TRIGGER tg3
FOR UPDATE OF e_sal
ON t_emp
COMPOUND TRIGGER
v_avg NUMBER(7,2)
BEFORE STATEMENT IS
BEGIN
SELECT AVG(e_sal) INTO v_avg
FROM t_emp;
END BEFORE STATEMENT;
BEGIN
IF:new.e_sal-:old.e_sal>v_avg*0.2 THEN
raise_application_error('-20001','数据修改失败');
END IF;
END AFTER EACH ROW;
END;
替换语句触发器
CREATE TRIGGER tg4
INSTEAD OF UPDATE
ON t_emp
BEGIN
dbms_output.put_line('打印这句话代替更新操作');
END;
数据对象操作触发器
CREATE TRIGGER tg5
BEFORE
CREATE OR ALTER OR DROP
ON SCHEMA
BEGIN
IF sysevent='CREATE' THEN
dbms_output.put_line('创建了数据对象，名为：'||dictionary_obj_name);
ELSEIF sysevent='ALTER' THEN
dbms_output.put_line('修改了数据对象，名为：'||dictionary_obj_name);
ELSE
dbms_output.put_line('删除了数据对象，名为：'||dictionary_obj_name);
END IF;
END;
数据库触发器
CREATE TRIGGER tg6
AFTER LOGON
ON SCHEMA
BEGIN
dbms_output.put_line('用户'||sys.login_user||'登陆了数据库');
END;
______________________________________________________________________________________________________________________________


添加联合主键：
ALTER TABLE ADD CONSTRAINTS '约束名' PRIMARY KEY emp(COLUMN,COLUMN,...);
添加联合索引：
CREATE INDEX xxx ON t_emp(eno, ename)
______________________________________________________________________________________________________________________________


循环造数
declare 
  countsize number:= 100;
  rowsize number :=0;
begin 
  for i in 1 .. countsize loop
    insert into emp (...) 
    values (...);
    rowsize := rowsize+1;
    if rowsize > 1000 then
       commit;
       rowsize:=0;
    end if;
    --dbms_output.put_line('第'||i||'执行更新已完成...');
  end loop;
  commit;
end;