查看表结构 : desc emp;
虚表 dual 如 select sysdate from dual;
关于给字段取别名:
select 字段 别名 from 表;
select 字段 as 别名 from 表;
如果别名需要用到空格:select 字段 "含空格别名" from 表;
含有空值的数学表达式结果都是空值。
字符串连接符: || 如果连接的字符串中含有单引号,使用 2个单引号即可,如 select ename||'abc''def' from emp;
distinct
消除重复的值 select distinc 字段 from 表;
where
筛选:
基本筛选:> < = <>is (not) nullin(...)/not in(...)...and/or...日期按照格式...where hiredate>'03-12月-81'红色部分必须格式一致
模糊查询:like
...like 'ALL';
...like '%ALL';取出所有以 ALL 结尾的。
...like 'ALL%';取出所有以 ALL 开头的。
...like '%ALL%';取出所有含有ALL的。
...like '_A%';取出所有第二个是字符A的,一个_代表一个字符。
如果检索包含 % 的字符串,使用转义符 \ :...like '%\%%';转义符可以指定:...like '%$%%' escape '$';
order by
排序
select 字段 from 表 order by 字段 desc/asc; desc:降序 asc:升序,不写表示升序。where 在order by 前面:select 字段 from 表 where ... order by ...
可以按照多个字段排序:...order by 字段1 desc/asc,字段2 desc/asc...;表示的意思是,当字段1相同时,数据按照字段2指定顺序排列
函数:可以用在检索的字段,可以用在where条件中
如:
转换成小写 lower():select lower(ename) from emp;select ename from emp where lower(ename) like...
字符串截取substr(字段名,初始位置,截取个数):select substr(ename,2,1) from emp;其中初始位置从1开始,不是从0开始
字符chr(数字) :select chr(65) from dual;结果为 A;相反,ascii('A'),结果为65
四舍五入round():round(23.653)结果为24,round(23.653,2)结果为23.65,round(23.653,1)结果为23.7,round(23.653,-1)结果为20
函数有很多,可以不记得具体使用,但是需要知道有哪些函数
其中,需要牢记的函数:
to_char()
to_date()
to_number()
nvl()
空值处理nvl(字段,0):如果字段为空,用0代替
max()
min()
avg()
组合函数:
sum()
count()可以和distinct一起用:count(distinct 字段)
group by
当需要用到组合函数時,同时又需要分类计算,需要用到group by:例如班级平均成绩,进行科目分类。
having
对分组进行限制,在goup by之后
总结一下sql语句的顺序:
select * from 表
where ...
group by...
having...
order by...
子查询:将一个查询结果用括号括起来当作一个表来使用。
连接查询:
92年标准: select ... from 表1,表2...where ...
存在一个不易读的问题,where后面连接的条件和逻辑过滤条件混合在一起。
99年建立新的标准:使用 join
各种复杂的查询,可以通过一步一步从内而外来拆解成简单的查询
create - insert - update - delete
创建用户:
exp 导出数据
drop user hcz;
create user hcz identified by hcz default tablespace user quota 10m on users;
grant create session,create table,create view to hcz;
imp 导入数据
关于rownum:select * from emp where rownum </<= 5;只能跟< or <=
解决办法:用子查询 select * from (select rownum r,e.* from emp e) where r<10
insert
insert into 表 values (...);
insert into 表(....) values (...);
insert into 表 select ....; 检索的整个结果插入
update
update 表 set 字段1 = ...,字段2 = ... where ...
delete
delete from 表 where... 不加where条件删除整个表的数据,删除表结构:drop table 表
事物transaction:
起始于一个DML语句,结束于commit、rollback、DDL/DCL语句自动commit、正常断开自动commit、异常断开自动rollback
建表
create table class
(
id number(4) primary key,
name varchar2(20) not null
)
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_nn not null, --非空约束
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50),
constraint stu_class_fk foreign key (class) references class(id), --外键
constraint stu_id_pk primary key (id),-- 主键
constraint stu_name_email_uni unique(email,name), -- 复合唯一约束
)
其中5中约束:not null、unique、primary key、foreign key、check
修改表结构:
alter table stu add(addr varchar2(100));
alter table stu modify(addr varchar2(150));
修改约束:一般是先删掉,再重新写约束
alter table stu drop constraint stu_class_fk;
alter table stu add constraint stu_class_fk foreign key (class) references class(id)
数据字典表:查看当前用户下有哪些表,都有什么视图等等之类的信息。
user_tables
user_views
user_constraints
.....
这些表的名字存在于 dictionary 表中。dictionary 中有table_name 和 comments 2个字段。
索引 index
只是为了访问字段更加快捷,只是访问快。
create index 索引名 on 表(字段)
drop index 索引名
查找索引:
select * from user_indexes
什么时候建立索引?
不要轻易建立索引:影响其他操作的效率,并且会占用空间。
视图view
视图就是一个子查询。
create view 视图名 as select .....
查找视图
select * from user_views
视图可以简化查询,但是不便于维护。如果表结构发生变化,视图也需要修改。
视图里面的数据是可以修改的,但是不建议在视图里面修改数据。
序列sequence
oracle 特有
create sequence seq;
select seq.nextval from dual;--1
select seq.nextval from dual;--2
select seq.nextval from dual;--3
是线程安全的
语法:
create sequence emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10;
一旦定义了emp_sequence,你就可以用CURRVAL,NEXTVAL
CURRVAL=返回sequence的当前值
NEXTVAL=增加sequence的值,然后返回sequence值
2道经典面试题:
1.有三个表s,c,sc
S(sno,sname)代表(学号,姓名)
C(cno,cname,cteacher)代表(课号,课名,教师)
SC(sno,cno,scgrade)代表(学号,课号,成绩)
问题:
1.找出没有选过“黎明”老师的所有学生姓名(简单)
2.列出2门以上(含2门)不及格学生姓名及平均成绩(group by...having..)
3.即学过1号课程,也学过2号课程的所有学生姓名(学过1的学生 in (学过2的学生))
2.一个简单的表table ,包含100条以上的信息,其中包括:
产品 颜色 数量
产品1 红色 123
产品1 蓝色 126
产品2 蓝色 103
产品2 红色 null
产品2 红色 89
产品1 红色 203
..........
请用sql完成以下问题:
1.按产品分类,仅列出各类商品中红色多于蓝色的商品名称及差额数量
2.按产品分类,将数据按照下列方式进行统计显示
产品 红色蓝色
select 产品,
sum(case when 颜色 = '红色' then 数量 end) as 红色,
sum(case when 颜色 = '蓝色' then 数量 end) as蓝色
from table group by 产品
数据库设计三范式:
三范式虽然很有用,但是还是具体问题具体对待。
1. 要有主键,列不可分
2.当一个表含有复合主键時,非主键的数据不能依赖部分主键(必须全部依赖),即不能存在部分依赖
3.属性不依赖于其他非主属性
PL/SQL oracle特有
也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)
PL/SQL块由四个基本部分组成:声明、执行体开始、异常处理、执行体结束
set serveroutput on;
begin
dbms_output.put_line('hello world'); --输出hello world
end;
declare
v_name varchar2(20);
begin
v_name := 'myname';
dbms_output.put_line(v_name); -- 输出 myname
end;
异常处理
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then -- 没有写原来的情况,那么其他情况(others)就包括所有情况
dbms_output.put_line('error');
end;
--table变量类型 table表示的是一个数组,指定数组的类型必须重新定义一种数组类型
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
--type表示定义一种类型,名为type_table_emp_empno,类型为emp.empno,下标为binary_integer类型
v_empnos type_table_emp_empno;-- 定义一个变量v_emonos,类型为type_table_emp_empno
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999; --oracle中下标可以是负数
dbms_output.put_line(v_empnos(-1));
end;
record 变量类型:类似于java中的类
declare
type type_record_dept is record -- 声明类型
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept; -- 声明该类型的变量
begin
v_temp.deptno := 50; -- 成员变量赋值
v_temp.dname := 'aaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname); --取值
end;
使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'bbb';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
SQL语句的运用
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno = 7369;--不用游标的时候,select语句里面必须有into,select需有且仅有一条数据
dbms_output.put_line(v_ename || ' ' || v_sal);
end;
-- 表变量
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename);
end;
insert
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept values(v_deptno,v_dname,v_loc);
commit;
end;
update
declare
v_deptno emp.deptno%type := 50;
v_count number;
begin
update emp set sal = sal/2 where deptno = v_deptno;
dbms_output.put_line(sql%rowcount || '条数据被影响');--sql表示刚刚执行的sql语句,rowcount表示被影响的语句条数
commit;
end;
在输出中文的时候,可能会出现乱码,这是工具的原因,在命令行中则不会出现这个问题,解决的办法:
1.查看注册表:开始-运行-输入regedit-回车进入注册表,依次单击HKEY_LOCAL_MACHINE--->SOFTWARE ---> ORACLE--->KEY_OraDb11g_home1(不同版本的Oracle显示的都不太一样,但都会包含home这个单词),找到“NLS_LANG”,查看数值数据是否为:“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”,如果不是就将它设置为“SIMPLIFIED CHINESE_CHINA.ZHS16GBK。”我的就是这个。没有修改
2.设置完注册表后,接下来设置我们的环境变量,计算机(右键) --->属性--->高级系统设置--->高级--->环境变量--->新建,个人建议新建用户变量,变量名输入:“NLS_LANG”,变量值输入:“SIMPLIFIEDCHINESE_CHINA.ZHS16GBK”。重启pl/sql developer,登录時可能会报错:ora-12705:cannot access NLS data ...什么的,需要把之前的乱码那条数据删掉就好了。
create
begin
execute immediate 'create table T(name varchar2(20) default ''abc'')';
end;
分支循环语句
if
--if 取出7369的薪水,如果小于1200,则输出low,如果小于2000则输出middle,否则high
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if(v_sal<1200) then
dbms_output.put_line('low');
elsif(v_sal<2000) then -- 注意:elsif 写法,不是 else if
dbms_output.put_line('middle');
else -- else 后面没有then
dbms_output.put_line('high');
end if; -- 有分号 注意
end;
循环
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
exit when (i >=11);
end loop;
end;
相当于java中的do-while循环,结果是 1 2 3 4...10
declare
j binary_integer := 1;
begin
while j<=11 loop
dbms_output.put_line(j);
j := j+1;
end loop;
end;
相当于java中的while循环,结果是1 2 3 4...11
for循环
begin
for k in 1..10 loop -- 1-10循环输出
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop -- 10-1循环输出
dbms_output.put_line(k);
end loop;
end;
游标(重点)
oracle里面遍历数据,类似于java中的迭代器Iterator
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp; --fetch 取得 的意思
dbms_output.put_line(v_emp.ename);
close c;
end;
游标循环遍历
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
需要说明一下,游标属性:ISOPEN FOUND/NOTFOUND ROWCOUNT
declare
cursor c is
select * from emp;
v_emp emp%Rowtype;
begin
open c;
fetch c into v_emp; --先fetch,否则c%found一直是false
while(c%found) loop
dbms_output.put_line(c%rowcount || v_emp.ename); --输出结果为 1XXX 2XXX 3XXX 4XXX...14XXX
fetch c into v_emp;
end loop;
close c;
end;
最方便的遍历方法for循环:不用定义变量了,不用手动close了。这里的for循环类似于java的foreach
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
帯参数的游标
declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
select ename,sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c(30,'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
大多数的游标是只读的,但是也是可以修改的
declare
cursor c is
select * from emp for update; -- for update
begin
for v_temp in c loop
if(v_temp.sal<2000) then
update emp set sal = sal*2 where current of c; --where条件注意 当前的
elsif(v_temp.sal=5000) then -- =用于判断,既不是‘:=’,也不是‘==’
delete from emp where current of c;
end if;
end loop;
commit; -- 注意提交
end;
存储过程procedure
由于存在需要直接调用某个程序的需求,所以产生了存储过程。
create or replace procedure p is
cursor c is
select * from emp for update;
begin
for v_emp in c loop
if(v_emp.deptno = 10) then
update emp set sal = sal+10 where current of c;
elsif(v_emp.deptno = 20) then
update emp set sal = sal+20 where current of c;
else
update emp set sal = sal+30 where current of c;
end if;
end loop;
commit;
end;
调用procedure的方式
exec p;
或者
begin
p;
end;
帯参数的存储过程
create or replace procedure
p(v_a in number,v_b number,v_ret out number,v_temp in out number) -- in输入参数,out输出参数,不写默认输入
is
begin
if(v_a>v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp+1;
end;
-- 调用存储过程
declare
v_a number := 3;
v_b number :=4;
v_ret number;
v_temp number :=5;
begin
p(v_a,v_b,v_ret,v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
往往在创建存储过程之中,代码会有错误,但是oracle只是提示:警告:创建的过程带有编译错误,此时可以查看错误:
show error;
删除存储过程
drop procedure p;
function
create or replace function sal_tax(v_sal number) return number
is
begin
if(v_sal<2000) then
return 0.10;
elsif(v_sal<2750) then
return 0.15;
else
return 0.20;
end if;
end;
select ename,sal_tax(sal) from emp;
触发器
create or replace trigger trig
after insert or delete or update on emp for each row --after表示操作之后,before表示之前;for each row表示每一行,可以不写就只会出发一次
begin
if inserting then
insert into emp_log values(USER,'insert',sysdate);
elsif updating then
insert into emp_log values(USER,'update',sysdate);
elsif deleting then
insert into emp_log values(USER,'delete',sysdate);
end if;
end;
有个小问题:
update dept set deptno=99 where dept=10;--这个操作是不能执行的,但是可以写一个触发器,这条语句就可以用了:
create or replace trigger trig after update on dept for each row
begin
update emp set deptno = :NEW.deptno where deptno = :OLD.deptno; -- :NEW 新状态 :OLD 旧状态
end;
update dept set deptno = 99 where deptno = 10;--可以执行
但是这种做法不常用,除非被逼无奈使用
思考:
树状结构的存储:
create table article
(
id number primary key, --主键
content varchar2(4000), -- 内容
pid number, --父节点主键
isleaf number(1), --冗余字段 便于开发 :是否是叶子节点 0非叶子节点 1叶子节点
alevel number(2) --冗余字段 便于开发 :节点等级 ,根节点0,根节点的子节点1...以此类推
);
字段的说明只是个例子