unlock user
alter user scott account unlock
第一代 机器语言
第二代 汇编
第三代 java c c++
第四代 sql语言(只用告诉它去干什么,没有条件分支循环)
sql语言分类
DML Commands (Data Manipulation Language)
Statements are used for managing data within schema objects
SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL Commands (Data Definition Language)
Statements are used to define the database structure or schema
CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL Commands (Data Control Language)
GRANT, REVOKE
TCL Commands (Transaction Control Language)
Statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
desc <tablename>
常用的sql函数
lower() 转换成小写
upper()转换成大写
substr(column,1,3) 截取字符串子串
chr(65)
asci(A)
round(23.65) 四舍五入
round(23.65,2)四舍五入到小数点后2位,负数也行
to_char(sal,'$99,999.999') //9代表一位数字,位数不足 不填满
to_char(sal,'L99,999.999') //L代表本地货币
to_char(sal,'$00,000.000') //0代表一位字符,位数不足,自动用0填满
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 格式化日期输出格式
group by
sql1992标准
笛卡尔积
select ename,dname from emp,dept
等值连接
select ename,dname from emp,dept where emp.deptno=dept.deptno;
sql1999标准
笛卡尔积
select ename,dname from emp cross join dept
等值连接
select ename,dname from emp join dept on(emp.deptno=dept.deptno)
where子句只写数据过滤条件
创建新用户
create user <username> identified by <password> deafaul tablespace users quota 10M on users;
desc <tablename> 描述某张表
insert into <tablename> (column,不加为默认全部) values(value);
蛋疼的rownum伪字段
只能和<或者<=合用
取中间几条必须使用子查询
/*求薪水最高的第6-10名雇员,oracle的rownum设计的不是很合理,所以取起来不叫麻烦*/
/*先把工资按照降序排列*/
/*整理rownum*/
/*再从整理后的表中调取数据*/
select ename,sal from emp order by sal desc;
select ename,sal,rownum r from (select ename,sal from emp order by sal desc)
select ename,sal from (select ename,sal,rownum r from (select ename,sal from emp order by sal desc)) where r between 6 and 10;
Transaction
所谓事务即是保证数据前后的统一性
比如转账,从A账号扣除100,给B账户添加100
必须保证2条update语句同时完成,或者同时不完成
保证数据总量的一致性
起始于一条dml
结束与rollback或者显示commit
执行ddl或者dcl(grant,deny,revoke)时,事务隐式commit
当用户正常断开连接(exit)的时候,自动提交
非正常断开连接,事务自动rollback
CREATE TABLE
常用数据类型
VARCHAR2 变长字符串(最大4k)
CHAR 定长字符串(存取效率高,长度固定,查找下一个数据类似于数组下标,牺牲空间换区时间)
NUMBER(8,3)
DATE
LONG 变长字符串(最大2G)
其他
create table student (
id number(8),
name varchar2(20) constraint stu_name_nn not null,
birth date,
grade number(2) default 1
email varchar2(50) ,
constraint stu_name_email_uni unique(email,name),
constraint stu_name_email_pri primary key(id )
);
表级约束,表示字段组合不能重复
约束条件
not null 非空
unique 唯一 null可以作为字段值 且可多个null
primary key 主键
foreign key 外键
check
修改表结构
alter
alter table student add(addr varchar(20));
alter table student drop(addr);
alter table student modify(addr varchar2(20));
删除或者增加约束条件
alter table student drop constraint <约束名>;
alter table student add constraint <约束名> <约束条件>
alter table student add constraint stu-cla_fk foreign key(class) reference class(id);
oracle dictionaries
索引 indexes(主键或者唯一约束.oracle会自动建立索引)
读取数据 提高字段的读取效率(高频率访问字段时,建立索引)
修改效率不高,因为插入数据的同时还要插入对应的索引,索引也会占用空间
索引的使用要仔细慎重
create index idx_stu_email on stu(email)
create index idx_stu_email&addr on stu(email,addr) 字段组合建立索引
drop index idx_stu_email
视图 view(子查询的临时表,简化查询,保护私有数据,往往只读不写)
create view viewname as select * from emp;
序列 sequence(oracle特有,其他数据库的mysql 的auto increament,sql server的identity)
产生唯一的不间断的数字序列,一般都用来做主键
eg:帖子id,帖子标题,帖子内容
往数据库更新一个帖子的时候
"
select max(id) from article;
insert into .....(max(id)+1,,)
",必须一个transaction完成,线程安全
create table article
(
id number,
title varchar2(1024),
context long
);
create sequence seq;
select seq.nextval from dual sequence
数据库设计的三范式
数据库设计的规范,规则尽量遵守,必要时候必须打破
1.不存在冗余数据(同样的数据不存第二遍)
(1)要有主键
eg:设计一张表,存储班级里学生的信息
create table student (id number(8),name varchar2(20),age number(3) )
(2)列不可分、重复()
eg:设计只有一个字段的一张表
存储 zhangsan_23_1,上边三个字段查询比较方便,比如查询上边的年龄
年龄和出生日期也属于冗余
打破情况
发动机钢印(包含大量信息)
2.不能存在部分依赖(也是为了满足第一范式)
当一张表中多个字段作为主键的时候,非主键的字段不能够依赖于部分主键
多对多关系,三张表,老师,学生,如果一张表(学号教师号联合主键),学生的信息 只依赖与学生的学号
3.不能存在传递依赖(多对一,除了主键之外的字段,必须都依赖于主键)
emp表中的deptno字段,雇员有对应的deptno,而不能设计一张表
包含雇员和部门的信息,部门的信息会有大量冗余,所以拆成2张表
通过外键
PLSQL
ORACLE内部使用的编程语言
TSQL
SQL Server内部使用的编程语言
PLSQL(procedure language,过程语言,即是带有分支循环的语言)
对sql的补充,sql中没有分支,没有循环
sql使用简单,但是不够灵活
template eg:
declare /*声明变量游标,not required*/
begin /*程序入口*/
end /*结束*/
declare
v_name varchar(20);
begin
v_name:='myname';
dbms_output.put_line(v_name);
end;
变量名的声明:
不能使用保留字 from select等
第一个字符必须是字母(java为$ 字母或者_)
变量名最多包含30个字符
不要与数据库的表或者列同名
每一行只能声明一个变量
常用变量的类型:
binary_integer:整数,主要用来计数而不是用来表示字段类型 for循环计数 数组下标
number 数字类型
char 定长字符串
varchar2 变长字符串
date 日期
long 长字符串 最大2GB
boolean 布尔类型 可以取值为true false null(不赋初值时 为null) 和java有不同
boolean不能被dbms_output.put_line出来
declare eg:
declare
v_temp number(1);
v_count binary_integer :=0;
v_sal number(7,2) :=4000.00;
v_date date :=sysdate;
v_pi constant number(3,2) :=3.14;
v_valid boolean :=false;
v_name varchar2(20) not null := 'MyName';
begin
dbms_output.put_line('v_temp value:'||v_temp);
end;
exception
declare
v_num number :=0;
begin
v_num:=2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('erroe');
end;
--变量声明,使用%type属性
declare
v_empno numer(4);
--定义和某表中某字段的类型(动态,表结构发生改变后,也无需修改)
v_empno emp.empno%type;
/*PLSQL中的变量*/
--table,类似于数组
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer; --自定义一个table变量
v_empnos type_table_emp_empno;--声明一个自定义类型的变量
begin
v_empnos(0):=7888;
v_empnos(-1):=9999;--index可以是负数
dbms_output.put_line(v_empnos(-1));
end;
/
--record,类似于java中的类
declare
type type_record_dept is record
( deptno dept.deptno%type,
loc dept.loc%type,
dname dept.dname%type);
v_record_dept type_record_dept;
begin
v_record_dept.loc:='NEW YORK';
dbms_output.put_line(v_record_dept.loc);
end;
/
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:=50;
v_temp.loc:='NEW YORK';
dbms_output.put_line(v_temp.deptno||''''||v_temp.loc||''''||v_temp.dname);
end;
--在PLSQL编程中使用dml,dql语句
declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_name,v_sal from emp where empno=7369;
dbms_output.put_line(v_name||' '||v_sal);
end;
declare
v emp%rowtype;
begin
select * into v from emp where empno=7369;
v.empno:=7777;
insert into emp values v;
commit;
dbms_output.put_line(sql%rowcount||'条记录受影响');
end;
--在PLSQL编程中使用dcl,ddl语句
begin
execute immediate 'create table T (name varchar2(20) default ''aaa'')';
end;
--在PLSQL编程中使用分支,循环
--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
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
--取出7839的薪水,小雨2500 X2 , 大于2500 /2, =2500,输出
select * from emp where empno=7839
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7839;
if(v_sal<2500) then
update emp set sal=v_sal*2 where empno=7839;
--PLSQL中的循环
--类似do while
declare
i binary_integer:=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
exit when(i>=11);
end loop;
end;
--类似while
declare
i binary_integer:=1;
begin
while i<11 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
--类似增强for循环
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop;
dbms_output.put_line(k);
end loop;
end;
--错误处理,每一种错误在PLSQL中都有一个名字(类似于java中的每一个异常都有一个自定义的类)
--plsql中有许多中错误 too_many_rows no_data_found等
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno=10;
exception
when too_many_rows then
dbms_output.put_line('太多记录');
when others then
dbms_output.put_line('error');
end;
--DBA处理数据库错误的方法
create sequence seq_errorlog_id start with 1 increment by 1;
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
declare
v_deptno dept.deptno%type :=10;
v_errcode number;
v_errmsg varchar(1024);
begin
delete from dept where deptno=10;
exception
when others then
v_errcode:=SQLCODE;
v_errmsg:=SQLERRM;
insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
commit;
end;
--PLSQL中的游标 cursor 重点
--select语句只能取一条
--游标的属性 cursor%isopen cursor%notfound cursor%found cursor%count(当前找到多少条)
declare
cursor c is select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;--fetch之后 游标自动的往后移,所以通过循环实现遍历数据库表,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;
fetch c into v_emp;
while c%found loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
--for循环 自动处理游标开关
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_emp in c(30,'CLERK') loop
dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
end loop;
end;
--可更新的游标
declare
cursor c is select * from emp 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; --数据库会自动更新和当前游标对应的数据
elsif(v_temp.sal=5000) then
delete fromemp where current of c;
end if
end loop
commit
end;
--stored procedure 存储过程(类似于封装的方法 使用 exec p执行或者在其他plsql中直接调用p)
create or repalce procedure p
is--此块替代declare 其他和完全相同
cursor c is select * from emp 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; --数据库会自动更新和当前游标对应的数据
elsif(v_temp.sal=5000) then
delete fromemp where current of c;
end if
end loop
commit
end;
--带参数的存储过程(参数in类型 调用时需要传入一个值,参数类型不写 默认为 in ,out相当于返回值)
create or replace procedure p
(v_a in number,v_b number,v_ret out number,v_temp in out number)
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;
--使用存储过程计算费波纳茨数列
create or replace procedure p
(v_index in number(10),v_result out number(100))
is
num_forward:=1;
num_backward:=1;
v_result number(100)
begin
if(v_index<3) then
v_result:=1;
else
p(v_index-1)
--函数
create or replace function sal_tax(v_sal emp.sal%type) return emp.sal%type
is
begin
if(v_sal<1200) then
return 0.0;
elsif(v_sal<2000) then
return 0.2;
elsif(v_sal<4000) then
return 0.3;
else
return 0.5;
end if;
end;
--使用函数计算费波纳茨数列
create or replace function fibonacci(v_num number) return number
is
begin
if(v_num<3)then
return 1;
else
return fibonacci(v_num-1)+fibonacci(v_num-2);
end if;
end;
--触发器
create or replace trigger trig
after insert or delete or update on emp for each row --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;
cast 是进行类型转换的, 可以针对各种Oracle数据类型. 修改的是用户的数据类型.
如：
select cast(a as int) from t1
select cast(a as number(8,4)) from t1
浙公网安备 33010602011771号