1、约束
主键约束
外键约束
非空约束
唯一约束
检查约束
ALTER TABLE TBL表名
ADD CONSTRAINT 约束的标识名 具体的约束;
ALTER TABLE S
ADD CONSTRAINT CHK_S_001
CHECK ( SSEX IN ('男','女') );
INSERT INTO S VALUES( 1008 , '任盈盈' , '女' );
SC(SID) ---> S(SID)
外键:不能引用不存在的实体(主键)
ALTER TABLE SC
ADD CONSTRAINT FK_SC_001
FOREIGN KEY ( SID )
REFERENCES S(SID);
ALTER TABLE S
ADD CONSTRAINT PK_S1_001
PRIMARY KEY ( SID );
ALTER TABLE S
ADD CONSTRAINT UNQ_S_001
UNIQUE(SNAME);
ALTER TABLE S DROP CONSTRAINT UNQ_S_001
2、视图
为了提供开发效率,减少重复劳动,将复杂的SQL语句
封装起来。
视图本身不存储数据,他的数据都来自于查询语句对应的
基表,所以,我们可以把视图看成是虚表
CREATE OR REPLACE VIEW 视图名(别名)
AS
复杂的SQL语句
例子:
CREATE OR REPLACE VIEW V_EMP(员工编号,员工姓名,工种)
AS
SELECT EMPNO , ENAME ,JOB FROM EMP
使用视图就和使用表一样
SELECT * FROM V_EMP;
UPDATE V_EMP SET 工种 = '秘书'
WHERE 员工编号 = 1001;
SELECT * FROM EMP;
当你的基表的表结构发生变化的时候,需要重新
运行或者编译视图
ALTER VIEW 视图名 COMPILE
3、索引
索引就好像一本书的目录,主要是为了提高
查询效率,快速定位数据,减少磁盘访问次数。
语法
CREATE INDEX S_ENAME_INDEX
ON EMP(ENAME);
DROP INDEX S_ENAME_INDEX
4、什么时候用索引(非常重要)
字段取值分布范围很广
字段中包含大量空值
字段经常出现在where子句或连接条件中
表经常被访问、数据量很大,且通常每次访问的数据量小于记录总量的2%~4%
5、不适合用索引的情况
表很小
字段不经常出现在where子句中
每次访问的数据量大于记录总数的2%~4%
表经常更新
被索引的字段作为表达式的一部分被引用而不是被直接引用(使用频繁的话可考虑创建函数索引)
6、
SELECT * FROM EMP
WHERE lower(ENAME) = 'smith'
CREATE INDEX S_ENAME_INDEX
ON EMP(lower(ENAME));
SELECT * FROM EMP
WHERE lower(ENAME) = 'smith'
SELECT * FROM S
CREATE UNIQUE INDEX S_UINQ_INDEX
ON S(SNAME);
7、序列 有两个属性(NEXTVAL , CURRVAL)
CREATE sequence seq_S ;
SELECT seq_S.NEXTVAL FROM DUAL; --获取下一个序列值
SELECT seq_S.CURRVAL FROM DUAL; --获取当前序列值
序列语法
CREATE sequence 序列名
MAXVALUE 999999 CACHE 20;
一般是在新增的时候使用
INSERT INTO S VALUES( seq_S.NEXTVAL , '令狐冲' , '男'
)
SELECT * FROM S;
8、范式:至少要符合前三范式
第一范式:属性不可分割
第二范式:不存在局部依赖
第三范式:不存在传递依赖
9、PL/SQL
基本结构
[DECLARE]
--声明变量
BEGIN
END;
PL/SQL专属数据类型
%TYPE(字段,属性类型) %ROWTYPE(行类型)
声明一个变量SAL, 赋值800 , 并打印出来 赋值方式 : =
DECLARE
SAL NUMBER(8,2);
BEGIN
SAL := 800;
DBMS_OUTPUT.PUT_LINE(SAL);
END;
开启服务端输出命令
SET SERVEROUTPUT ON;
DECLARE
D_SAL NUMBER(8,2);
BEGIN
--第两种赋值方式 INTO
SELECT SAL INTO D_SAL FROM EMP WHERE EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(D_SAL);
END;
DECLARE
D_SAL NUMBER(8,2);
D_JOB VARCHAR(30);
BEGIN
--第两种赋值方式 INTO
SELECT SAL,JOB INTO D_SAL ,D_JOB
FROM EMP WHERE EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(D_SAL || D_JOB);
END;
10、
%TYPE(字段,属性类型)
DECLARE
D_SAL EMP.SAL%TYPE;
D_JOB EMP.JOB%TYPE;
BEGIN
--第两种赋值方式 INTO
SELECT SAL,JOB INTO D_SAL ,D_JOB
FROM EMP WHERE EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(D_SAL || D_JOB);
END;
%ROWTYPE(行类型)
DECLARE
R_EMP EMP%ROWTYPE;
C_COUNT CONSTANT NUMBER(4) := 100;
BEGIN
--第两种赋值方式 INTO
SELECT * INTO R_EMP
FROM EMP WHERE EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE( R_EMP.JOB );
END;
11、
查询员工7369的工资,判断工资等级
1-1000之间 1001-2500 中等 以上是高工资
DECLARE
D_SAL EMP.SAL%TYPE := 0 ;
BEGIN
--第两种赋值方式 INTO
SELECT SAL INTO D_SAL
FROM EMP WHERE EMPNO = 7369;
IF D_SAL >= 0 AND D_SAL <= 1000 THEN
DBMS_OUTPUT.PUT_LINE( '低工资' );
ELSIF D_SAL > 1000 AND D_SAL <= 2500 THEN
DBMS_OUTPUT.PUT_LINE( '中等工资' );
ELSE
DBMS_OUTPUT.PUT_LINE( '高工资' );
END IF;
END;
--打印五个Hello!
DECLARE
COUNTER NUMBER(1) := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( 'Hello' );
COUNTER := COUNTER + 1;
IF COUNTER >= 5 THEN
EXIT;
END IF;
END LOOP;
END;
DECLARE
COUNTER NUMBER(1) := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( 'Hello' );
COUNTER := COUNTER + 1;
EXIT WHEN COUNTER >= 5;
END LOOP;
END;
FOR IN LOOP
END LOOP
BEGIN
FOR i IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE( 'Hello' || i );
END LOOP;
END;
WHILE 循环条件 LOOP
END LOOP
写一个带异常的语句
DECLARE
D_SAL EMP.SAL%TYPE := 0 ;
BEGIN
--第两种赋值方式 INTO
SELECT SAL INTO D_SAL
FROM EMP ;
IF D_SAL >= 0 AND D_SAL <= 1000 THEN
DBMS_OUTPUT.PUT_LINE( '低工资' );
ELSIF D_SAL > 1000 AND D_SAL <= 2500 THEN
DBMS_OUTPUT.PUT_LINE( '中等工资' );
ELSE
DBMS_OUTPUT.PUT_LINE( '高工资' );
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE( '请指定员工编号!!!' );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( '有异常了!!!' );
END;
12、
存储过程
封装PL/SQL
没有程序级别的返回值
语法
CREATE OR REPLACE PROCEDURE 存储过程名字
( 参数列表...... )
IS
声明变量
BEGIN
PL/SQL
END;
--更新EMP (根据员工编号修改工种)
CREATE OR REPLACE PROCEDURE USP_UPDATE_EMP
( i_job IN VARCHAR2 , i_empno IN NUMBER )
IS
v_job EMP.JOB%TYPE;
BEGIN
v_job := i_job;
UPDATE EMP SET JOB = v_job
WHERE EMPNO = i_empno;
commit;
END;
调用存储过程
EXECUTE USP_UPDATE_EMP( '内勤' , 1001 );
BEGIN
USP_UPDATE_EMP( '保洁' , 1001 );
END;
SELECT * FROM EMP WHERE EMPNO = 1001;
--查询部门为10的员工的平均工资
--存储过程没有提供返回值,所以,只能输出型参数
CREATE OR REPLACE PROCEDURE USP_GETSAL
( i_deptno IN NUMBER , o_sal OUT NUMBER )
IS
BEGIN
SELECT AVG( SAL ) INTO o_sal FROM EMP
WHERE DEPTNO = i_deptno;
END;
DECLARE
o_sal NUMBER(8,2) := 0.0;
BEGIN
USP_GETSAL( 10 ,o_sal );
DBMS_OUTPUT.PUT_LINE( o_sal);
END;
CREATE OR REPLACE PROCEDURE USP_GETSAL2
( i_deptno IN NUMBER , io_sal IN OUT NUMBER )
IS
v_sal EMP.SAL%TYPE := 0.0;
BEGIN
v_sal := io_sal;
DBMS_OUTPUT.PUT_LINE( v_sal);
SELECT AVG( SAL ) INTO io_sal FROM EMP
WHERE DEPTNO = i_deptno;
END;
DECLARE
o_sal NUMBER(8,2) := 150;
BEGIN
USP_GETSAL2( 10 ,o_sal );
DBMS_OUTPUT.PUT_LINE( o_sal);
END;
--删除存储过程
DROP PROCEDURE 存储过程名字