SQL分类:
DQL: select
DML: insert delete update
DDL: create drop alter
DCL: grant rewoke
DTL: commit rollback savepoint
select的七个部分:
表--二维关系表
行(row)--对应一条完整的信息记录
列(column)--一条信息的一个字段
Aselect子句
查询某个字段
SELECT salary FROM s_emp;
查询某个几字段
SELECT first_name, salary FROM s_emp;
注意:查询多个字段用逗号分开
!clear ----- clear screan;
*号可以代表所有的字段
SELECT * FROM s_emp;
注意:公司不推荐*号代替,业务含义不明确
SELECT first_name, salary FROM s_emp;
SELECT * FROM s_emp;
如果想显示年薪
对字段进行数学运算 + - * /
SELECT salary*12 FROM s_emp;
可以用别名控制表头显示
(如果想显示成小写,或别名中有特殊字符,可以加双引号处理)
SELECT salary*12 AS year_salary FROM s_emp;
SELECT salary*12 AS "year salary" FROM s_emp;
如果想拼接字符
SELECT last_name || '_' || first_name FROM s_emp;
注意:oracle中表达字符串用单引号
如果是想表达单引号
SELECTlast_name || '''' || first_name FROM s_emp;
null值的处理
SELECT first_name,
salary+salary*12*(nvl(commission_pct,0)/100) AS"salary"
FROM s_emp;
null值要尽早处理,否则后果很严重
nvl(p1, p2)-- 处理null
p1就是要处理的字段或者值
p2如果是null就返回p2的值,不为null则返回p1
重复数据的处理
SELECT DISTINCT last_name FROM s_emp;
通用SQL
SELECT [DISTINCT] [*, column [alias], column [alias], ...]
FROM tablename;
B where子句
WHRER 子句 -- 限制查询的记录
查询的工资大于1000的员工名字
SELECT last_name||first_name FROM s_emp WHERE salary>1000;
数学比较
> < <= >= =
逻辑运算符
and or not
SQL的比较运算符
查询工资在这个区间的[1000,1500]人的名和工资
SELECT last_name, salary
FROM s_emp
WHERE salary>=1000 AND salary<=1500;
or
SELECT last_name, salary
FROM s_emp
WHERE salary BETWEEN 1000 AND 1500;
IN, IS NULL, LIKE
IN(list) 查询id是1, 或3, 或5的id, last_name
SELECT id, last_name FROM s_emp WHERE id IN(1,3,5);
注意:list中数组排放对结果没有影响,对效率可能有影响
因为以1,3,5依次比较,如果5的出现几率较大,
则会多次与1,3比较之后才与5比较,浪费时间,若5在前,则可提高效率
LIKE 模糊查询
通配符:
% 0-N个任意字符
_ 一个任意字符
user_tables 数据字典表
table_name 数据库中表的名字
找出以S_开头的表名
1,数据字典中的数据都是大写
2,下划线有双重含义
SELECTtable_name
FROMuser_tables
WHEREtable_name LIKE 'S\_%' ESCAPE '\';
补充:
如果有多个条件 条件的优先级可能改变
数据库中也可以用小写括号改变优先级
SQL中的
> <=
>= <
= !=^= <>
not [between and/in/is/like]
and or
注:ISNULL的否定:IS NOT NULL
C orderby
D singlefunction
1,对数据进行计算
2,修改非法数据
3,对一组数据进行统一处理(组函数的作用)
4,改变数据类型
5,改变数据的显式格式
函数:
组函数:对一组数据处理之后得到一个结果
COUNT()
单行函数:对一个数据处理之后得到一个结果
分类:
UPPER(str) 转换成大写
LOWER(str) 转换成小写
INITCAP(str)把每个单词的首字母大写
COMCAT(str1,str2) 连接字符串
SUBSTR(str,index,num) 截取字符串(*)
SELECTSUBSTR('helloworld',2,3) FROM dual;
注意:起始位置是1
若第二个参数为负数则从后往前数的位置开始
若第三个参数超出范围,则以字符结束为止
LENGTH(str) 求字符串长度
TRIM(str) 去掉首尾空格
REPLACE(str,oldstr,newstr)替换字符串
数组处理函数:
TO_NUMBER(numstr)ORACLE会做隐式类型转换,所以此函数无太大用
ROUND(num)四舍五入
TRUNC(num)截取(丢弃尾数)
格式控制函数:
格式:fm
9: 任意数字
0: 前导0
$: 美元符
l: local货币符号¥ RMB
,: 国际数字分割符
.: 小数点
to_char(salary,'格式')
SELECTTO_CHAR(salary,'fmL099,999.99') FROM s_emp;
将语言设置为中文:
NLS_LANG='SIMPLFIEDCHINESE_CHINA.ZHS16GBK'
为了测试单行函数,提供了一张测试表:dula
SELECTLOWER('abcdef') FROM dual;
E 多表查询
col name for a15
name 是列名 a15代表宽度
表的连接:
内连接:
a)等值连接
用等号做的表连接
b)非等值连接
不用等号做的连接
c)自连接
外连接:
外连接=内连接+匹配不上的(一个也不能少)
SQL99如何表达上式
哪张表发起连接,那么这张表的数据就会被全部匹配出来
F 组函数
count() 统计一组数据的个数
max() 求一组数据的最大值
min() 求一组数据的最小值
avg() 求一组数据的平均值
sum() 求一组数据的和
注意:
select sum(distinct salary) from s_emp;
null和组函数的关系:
组函数不对null进行处理
如果不想忽略掉null,应用NVL先处理null值
G 子查询
数据库设计:
E-R
实体-关系
# 唯一
* 非空
o 可选
#* 唯一非空
主键一个表只有一个,但非空唯一的可以有多个
对象和对象的关系
1:1
1:n
m:n
------- 非强制关系
_______ 强制关系
三范式:
第一范式:每个字段不可再分
关系型数据库必需满足第一范式
第二范式:在满足第一范式的基础上,非主属性完全依赖于主属性
第三范式:在满足第二范式的基础上,消除属性之间的传递依赖
Oracle的数据类型:
number(m,n) m[1-38]长度 n[-84-127]精度
varchar(n) n[1-4000]长度
date 日期类型
char(n) n[1-4000]长度
clob 大字符类型(4G)
blob 大二进制类型(4G)
//复制s_emp表,并复制数据
CREATE TABLE ecps_emp
AS SELECT * FROM s_emp WHERE 1=1;
//复制s_emp表,但只要表结构
CREATE TABLE ecps_emp
AS SELECT * FROM s_emp WHERE 1=2;
CREATE TABLE 表名(最大30)
(字段名 类型, 字段名 类型, ...);
当查询'abc '时,CHAR可以查出,因为定长在之后补空格
而VARCHAR不能查出,应为不定长不补空格
当字段长度不变时,选定长(CHAR),可使得查询效率提高
其他情况一般使用VARCHAR,可以节省空间
日期类型:
当前日期的表达:
sysdate 表达当前日期
默认的英文格式: dd-三位月份的英文缩写-yy
默认的中文格式: dd-阿拉伯数字 月-yy
当环境为中文:
'12-SEP-11' 不能插入成功
解决方法:
1,改为'13-9月-11'
2,将环境变量的语言环境变成英文
日期处理函数:
TO_CHAR(日期,'')
yyyy 四位年
mm 两位月
dd 两位天
hh 默认12小时制,hh24表达24小时制
mi 分钟
ss 秒
day 星期几
month 英文全写
mon 英文缩写
插入日期类型
TO_DATE('要处理的字符串','日期格式')
ROUND(sysdate,'dd')以天为单位进行四舍五入
TRUNC(sysdate,'mm')以月为单位进行截取
ADD_MONTHS(sysdate,2)
LAST_DAY(sysdate) 这个月的最后一天
NEXT_DAY(sysdate,'FRIDAY')下一个星期几
MONTHS_BETWEEN(d1,d2) 计算两个日期相差几个月
SQL的约束:
主键: primary key 对一条记录的唯一标识(非空+唯一,一张表只有一个)
外键: foreign key
非空: not null//只有列级
唯一: unique
检查: check
列级约束: 约束直接跟在列后面
CREATE TABLE ed_test_pk (
id NUMBER CONSTRAINT pk_ed_test_pk PRIMARY KEY,
name VARCHAR(30)
);
删除约束:
ALTER TABLE ed_test_pk DROP CONSTRAINT pk_ed_test_pk;
使约束失效:
ALTER TABLE ed_test_pk DISABLE CONSTRAINT pk_ed_test_pk;
使约束生效:
ALTER TABLE ed_test_pk ENABLE CONSTRAINT pk_ed_test_pk;
表级约束:主要解决联合约束问题
CREATE TABLE ed_test_pk (
id NUMBER,
name VARCHAR(30),
CONSTRAINT pk_ed_test_pk PRIMARY KEY(id)
);
如果一个字段要做为另一个表的外键,那么这个表必需唯一
ON DELETE SETNULL 删除外键所引用的主键时,相应的外键置空
ON DELETECASCADE 删除外键所引用的主键时,相应的外键所在记录被删除
INSERTINTO...VALUES
将查询出的结果插入到表中作为一条记录
INSERT INTO s_emp(id, first_name, last_name)
SLELCT MAX(id),MAX(name),MIN(name) FROM s_dept;
UPDATE..SET..
UPDATE s_emp SET first_name='a', last_name='b' WHERE id=1;
DELETEFROM...WHERE
DML语言需要commit才能提交,否则使用rollback回滚
而DDL语言是自动提交的
序列:
一个自动增长的数字对象
一个共享对象
比较典型的应用是做表的主键
可以减少我们的程序代码
可以通过缓存加速主键的生成
CREATE SEQUENCE ed_s;
SELECT ed_s.NEXTVAL FROM dual;
SELECT ed_s.CURRVAL FROM dual;
一般一个表一个SEQUENCE
CREATE SEQUENCE ed_s
START WITHn 默认值为1
INCREMENT BYn 默认值为1
MAXVALUE/NOMAXVALUEn 默认是pow(10,27)
MINVALUE/NOMINVALUEn pow(10,-28)
nocycle/cycle 默认是nocycle,如果有循环回到默认的1
nocache/cachen 默认cache20
索引:
rowid: 伪列
是供DBMS底层查找使用的,是一条记录的物理位置
CREATE INDEX ed_index ON s_emp(dept_id);
DROP INDEX ed_inedx;
SET AUTOTRACE ON 打开跟踪
UNIQUE的字段是自动创建索引
视图:
CREATE VIEW ed_view AS SELECT sname FROM ed_student;
简单视图可以增删改源数据
复杂视图限制很多:
GROUP BY, DISTINCT ,组函数, NOT NULL 未被选的, 表连接
满足上面一条的就是复杂视图
如果按某个字段排序,取第n夜的数据时,必需三层嵌套
要先排序,后编号
Oracle相关的编程技术:
JDBC SQLJ
OCI Oracle底层的通讯接口
ODBC
PL/SQL 过程化的SQL
PROC/C++ 用C/C++这种高效语言访问数据库
PL/SQL:
声明区 declare ... begin
执行区 begin ...
异常区 exception ...
变量的声明:在声明区定义
constant 常量
not null 必需有初值
:= 赋值
= 代表相等(==)
如果没有赋初值,任何变量都是null
例: DECLARE
var_n CONSTANT NUMBER NOT NULL :=100;
注释:
单行注释:--
多行注释:/* ... */
数据类型:
标量类型:number char varchar2 date boolean
组合类型:record table
大数据类型:blob, clob
引用类型:ref
%type 取得对应字段的类型:
var_name s_emp.first_name%type;
RECORD类型:
record类型的变量可以直接相互赋值,也可以逐个字段赋值
DECLARE
TYPE reco IS RECORD (
var_no dept.deptno%type,
var_namedept.dname%type
);
var_record reco;
var_reco2 reco;
BEGIN
SELECT deptno,dnameINTO var_record FROM dept WHERE deptno=10;
var_reco2 :=var_record;
dbms_output.put_line
(var_record.var_no ||',' || var_record.var_name);
dbms_output.put_line
(var_reco2.var_no ||',' || var_reco2.var_name);
END;
取一行的类型:%rowtype
DECLARE
var_dept dept%rowtype;
BEGIN
SELECT * INTO var_dept FROM dept WHERE deptno=20;
DBMS_OUTPUT.PUT_LINE(var_dept.deptno || ',' || var_dept.dname);
END;
TABLE类型:
first(): 第一个元素下标
last(): 最后一个元素下标
next(n); key=n的元素的下一个元素坐标
DECLARE
TYPE table_dept IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
var_nums table_dept;
BEGIN
var_nums(1) := 100;
var_nums(20) := 500;
var_nums(101) := 1000;
DBMS_OUTPUT.PUT_LINE(var_nums(1));
DBMS_OUTPUT.PUT_LINE(var_nums(20));
DBMS_OUTPUT.PUT_LINE(var_nums(101));
END;
如果装的类型是字符串,那要指明长度
不指明长度,就只能存取1个字符
只有 DML 和 DTL 可以直接在PL/SQL中使用 注意SELECT
DDL需要特殊的语法
之前学过的所有的内置函数可以直接在PL/SQL中使用
PL/SQL中不支持BOOL类型输出
CURSOR:
四步:
1,声明CURSOR
CURSOR cursor_ed IS SELECT * FROM emp;
2,打开CURSOR
OPEN cursor_ed;
3,提取所需元素
FETCH cursor_ed INTO var_emp;
4,关闭CURSOR
CLOSE cursor_ed;
FOR循环中的REVERSE,对CURSOR无影响
DECLARE
var_dept dept%ROWTYPE;
CURSOR cursor_dept ISSELECT * FROM dept;
BEGIN
OPEN cursor_dept;
FETCH cursor_dept INTOvar_dept;
WHILEcursor_dept%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(var_dept.deptno||', '||var_dept.dname);
FETCH cursor_deptINTO var_dept;
END LOOP;
CLOSE cursor_dept;
END;
可以给CURSOR传参数:
CURSOR cursor_dept(no NUMBER) IS SELECT * FROM dept WHEREdeptno>no;
OPEN cursor_dept(20);
注意:
参数定义中不能有()来指明类型长度,如NUMBER(5,2),
但可以通过%TYPE来变相指明
FOR UPDATE在查询时锁定查询的记录集
EXCEPTION:
一般异常处理过程:
1,自定义异常
2,根据实际情况抛出异常 RAISE
3,异常处理块中处理异常
1)回滚之前的事务
2)释放资源
3)记录日志
一旦发生系统异常,代码直接转到EXCEPTION块,
WHEN就是捕捉相应异常,以作相应处理
SQLCODE:代表一条sql的执行情况,0表示成功
SQLERRM:代表异常的相关信息
PROCEDURE
定义:
CREATE OR REPLACE PROCEDURE hello
IS
BEGIN
END;
执行:exec procedure_name;
若PROCEDURE有参数,desc procedure_name可以查看参数
in参数可以是常量,也可以是变量,但不能在过程中赋值
out参数必需是变量,可以赋值
in out参数必需是变量,可以赋值
过程的赋值:
1,顺序赋值--位置参数
2,名字赋值
3,混合赋值,但第一个参数必需是位置赋值
注:
如果参数都是in参数
exec 两种赋值均支持
call 不支持名字赋值
在编译PROCEDURE时,出现警告则使用show errors
FUNCTION
CREATE OR REPLACE FUNCTION hello
RETURN NUMBER IS
BEGIN
...
RETURN ...;
END;
函数和过程的区别:
函数也返回值,过程没有
函数是表达式的一部分,过程是PL/SQL语句的一部分(函数若有返回值不能直接调用,而过程没有返回值所以可以)
PACKAGE:
将相关的函数过程,变量,类型等组织在一起的对象
DBMS_OUTPUT 输出包
DBMS_RANDOM 随机包
DBMS_JOB 定时任务包
生成0-10的数:
SELECT DBMS_RANDOM.value(0,10) FROM dual;
1.建一个序列
create sequencetest_r_s;
2.建一张表 id name money
create table test_moni(
id number primary key,
name varchar(30),
money number
);
3.写一个存储过程有一个参数name
向数据库中存入一条数据id 通过序列
name是传入的参数money是1-100的随机整数
create or replace procedure insert_data
is
begin
insert into test_moni values
(test_r_s.nextval,
'test'||trunc(dbms_random.value(0,100)),
trunc(dbms_random.value(0,100)));
commit;
end;
callinsert_data('zhangsan');
4.定时调用这个存储过程
declare
jobno binary_integer;
begin
dbms_job.submit(jobno,'insert_data();',
sysdate,'sysdate+1/(24*30)');
dbms_job.run(jobno);
dbms_output.put_line(jobno);
commit;
end;
dbms_job.remove(jobno);
定义:
1,包头部分
CREATE OR REPLACE PACKAGE test_pack IS
FUNCTION test_fun RETURN NUMBER; --函数申明
PROCEDURE test_pro;--过程申明
END test_pace;
2,包体
CREATE OR REPLACE PACKAGE BODY test_pack IS
FUNCTION test_fun RETURNNUMBER IS
BEGIN
RETURN 1;
END;
PROCEDURE test_pro IS
BEGIN
DBMS_OUTPUT.PUT_LINE('hellopackage body');
END;
END test_pack;
触发器:
1,更加完备的数据完整性解决方案
2,跟踪用户操作,审计
3,可以向其他程序发信号
DML 3
before/after 2
row/statement 2
定义:
CREATE OR REPLACE TRIGGER up_dept BEFORE UPDATE ON dept
BEGIN
DBMS_OUTPUT.PUT_LINE('update dept');
END;
FOR EACH ROW
这个触发器影响多少条 就触发多少次
如果没有不管触发器影响多少行 只触发一次