用户管理
--创建用户
create user 用户名 identified by 密码;
--用户授权
grant 权限 to 用户名;
--删除用户
drop user 用户名;
--账号解锁
alter user 用户名 account unlock;
表管理
--创建表
create table users(
id number,
name varchar2(32),
password varchar2(32),
birthday date
);
--添加一个字段
alter table users add(age number(2));
--修改字段
alter table users modify(age varchar2(2));
--删除字段
alter table users drop column age;
--修改表名
rename users to student;
--删除表
drop table users;
基本SQL
--添加数据
INSERT INTO student VALUES('A001', '张三', '男', '01-5月-05',10); --所有字段都插入数据
ALTER SESSIONSETNLS_DATE_FORMAT ='yyyy-mm-dd'; --修改日期的默认格式（临时修改）
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); --插入部分字段
INSERT INTOstudent(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null); --插入空值
--修改数据
UPDATE student SET sex = '女' WHERE xh = 'A001'; --修改一个字段
UPDATE student SET sex = '男',birthday = '1984-04-01' WHERE xh = 'A001'; --修改多个字段
--删除数据
DELETE FROM student; --删除所有记录，表结构还在，写日志，可以恢复的，速度慢。
DROP TABLE student; --删除表的结构和数据；
delete fromstudent WHERExh = 'A001'; --删除一条记录；
truncate TABLE student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
DESC emp; --查看表结构
SELECT * FROM dept; --查询所有列
SELECT ename, sal, job, deptno FROM emp; --查询指定列
SELECT DISTINCT deptno, job FROM emp; --取消重复行
SELECT deptno,job,sal FROM emp WHERE ename= 'SMITH'; --条件查询
SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; --使用算术表达式
SELECT ename "姓名", sal*12 AS "年收入" FROM emp; --使用列的别名
SELECT ename || ' is a ' || job FROM emp; --连接字符串
SELECT ename,sal FROM emp WHERE ename like 'S%'; --使用like操作符
SELECT * FROM emp WHERE empno in (7844,7839,123,456); --在where条件中使用in
SELECT *FROM emp WHERE mgr is null; --使用is null的操作符
SELECT * FROM emp WHERE (sal > 500 or job= 'MANAGER') and ename LIKE 'J%'; --使用逻辑操作符号
SELECT * FROM emp ORDER by deptno, sal DESC; --使用order by字句
select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; --使用列的别名排序
--分组函数
max() 求最大值
min() 求最小值
avg() 求平均值
sum() 求合计
count 求数量
SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; -- 使用 group by
SELECT AVG(sal), MAX(sal), deptno FROM empGROUP by deptno having AVG(sal) < 2000; --使用 having
--多列子查询
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM empWHERE ename = 'SMITH');
--分页查询
select t2.* from
(selectt1.*,rownum rn from
(select * from emp) t1
where rownum<=6) t2 where rn>=4;
--集合操作
union(并集，取消重复行)、union all(并集、不会取消重复行)、intersect(交集)、minus(差集)
--表连接
SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename ='FORD'; --自连接
select emp.ename,dept.dname fromemp,dept where emp.deptno=dept.deptno; --内连接
selectstu.id,stu.name,exam.grade from stu left join exam on stu.id=exam.id; --外连接
常用函数
--字符函数
lower(char)：将字符串转化为小写的格式.
upper(char)：将字符串转化为大写的格式.
length(char)：返回字符串的长度。
substr(char,m,n)：取字符串的子串;n代表取n个的意思，不是代表取到第n个
replace(char1,search_string,replace_string)
instr(char1,char2,[,n[,m]])取子串在字符串的位置
trim() 删除给定字符串或者给定数字中的头部或者尾部的给定字符
TRANSLATE(char, from, to) 返回将出现在from中的每个字符替换为to中的相应字符以后的字符串
decode() 它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。
nvl() 空值判断
--数学函数
round(n,[m])该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是正数，则四舍五入到小数点的m位后。如果m是负数，则四舍五入到小数点的m位前。
trunc(n,[m]) 该函数用于截取数字。若省掉m，就截去小数部分(等价于trunc(n,0))，如果m是正数就截取到小数点的m位后，若m是负数，则截取到小数点的前m位。
mod(m,n) 取模
floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
abs(n) 返回数字n的绝对值
exp(n) 返回e的n次幂
log(m,n) 返回对数值
power(m,n) 返回m的n次幂
--日期函数
sysdate 该函数返回系统时间
add_months(d,n) 在日期d上增加n个月
last_day(d) 返回指定日期所在月份的最后一天
to_char(date,'format') 日期格式化
to_date(string,'format') 将字符串转换成date类型的数据
--类型转换
to_char(),to_date(),to_number()
序列
create sequencemyseq --创建开始
start with 1 --从1开始
increment by 1 --每次增长1
minvalue 1 --最小值
maxvalue 30000 --最大值
cycle //cycle表示当序列增加30000，重新从1开始，如果不希望，就nocycle
nocache; --不缓存，而 cache 10;表示一次产生10个号共你使用，缺点可能会跳号，但提供效率
视图
--创建视图
create view 视图名 as select 语句 [withread only];
--创建或修改视图
create or replace view 视图名 asselect 语句 [with read only];
--删除视图
drop view 视图名;
索引
create index 索引名 on 表名(列名);
存储过程
create procedure pro4(in_empno number)
is
--declare 这个就不要了 块才需要declare指定
--定义变量的格式是：变量名称 变量的类型
--和建表一样
v_ename varchar2(10);
begin
--把指定雇员编号查询得到的姓名值放入v_ename变量中
select ename into v_ename from emp where empno=in_empno;
dbms_output.put_line('雇员名字是'||v_ename);
end;
函数
create or replace function fun1(in_v_ename varchar2)
return number--这里不要分号
is
--定义一个变量来接受年薪
v_annual_sal number;
begin
select sal+nvl(comm,0)*13 into v_annual_sal from emp
where ename=in_v_ename;
return v_annual_sal;
end;
sqlplus调用：select fun1('KING') from dual;
包
--声明
create or replace package mypackage1
is
--声明一个过程
procedure pro5(v_in_ename varchar2,v_in_newsal number);
--声明一个函数
function fun1(v_in_ename varchar2) return number;
end;
--实现
create or replace package body mypackage1
is
--实现一个过程
procedure pro5(v_in_ename varchar2,v_in_newsal number)
is
v_empno varchar2(32);
begin
select empno into v_empno from emp where ename=v_in_ename;
update emp set sal=v_in_newsal where ename=v_in_ename;
exception
when no_data_found then
dbms_output.put_line('朋友,你输入的编号有误!');
end;
--实现一个函数
function fun1(v_in_ename varchar2) return number
is
--定义一个变量来接受年薪
v_annual_sal number;
begin
select sal+nvl(comm,0)*13 into v_annual_sal from emp
where ename=v_in_ename;
return v_annual_sal;
end;
end;
触发器
create or replace trigger tri4
before update on scott.emp
for each row--这个一定要 是行级 不是表级
begin
if :new.sal<:old.sal then
dbms_output.put_line('工资不能低于原来工资');
raise_application_error(-2005,'工资不能低于原来工资');
else
dbms_output.put_line('原来工资是'||:old.sal||'现在工资'||:new.sal);
end if;
end;
PL/SQL数据类型
--简单类型
create or replace procedure pro2(v_in_empno in number) is
--定义变量
v_tax_rate number(3,2):=0.03;
v_sal number;
v_ename varchar2(32);
v_tax number;
begin
select ename,sal into v_ename,v_sal from emp
where empno=v_in_empno;
--计算个人所得税
v_tax:=v_sal*v_tax_rate;
dbms_output.put_line(v_ename||'工资='||v_sal||'个人所得税:'||v_tax);
end;
--复合类型
create or replace procedure pro3(v_in_empno in number)is
--定义一个记录数据类型
type emp_record is record(
v_ename emp.ename%type,
v_sal emp.sal%type,
v_job emp.job%type
);
--定义一个变量，其类型是emp_record
v_emp_record emp_record;
begin
select ename,sal,job into v_emp_record from emp
where empno=v_in_empno;
dbms_output.put_line('名字'||v_emp_record.v_ename||'薪水'||v_emp_record.v_sal||'岗位'||v_emp_record.v_job);
end;
--游标
create or replace procedure pro1(v_in_deptno number) is
--先定义一个游标变量类型 一个变量类型可以定义多个变量
type emp_cursor is ref cursor;
--定义1个游标变量
v_emp_cursor emp_cursor;
--定义2个变量
v_ename emp.ename%type;
v_sal emp.sal%type;
v_empno emp.empno%type;
begin--两个空格
--执行语句
--打开游标
open v_emp_cursor for select ename,sal,empno from emp
where deptno=v_in_deptno;
--取出游标指向的每行数据，用循环语句
loop
fetch v_emp_cursor into v_ename,v_sal,v_empno;--这句 导致游标往下移
--判断当前游标是否达到最后
exit when v_emp_cursor%notfound;
--输出 输出在判断之后，先判断，否则重复输出(最后一行)
dbms_output.put_line('用户名'||v_ename||' 薪水'||v_sal);
----------------------------更改地方-----------------------------
if v_sal<200 then
update emp set sal=sal+100 where empno=v_empno;
end if;
end loop;
--关闭游标[完后一定要关闭游标]
close v_emp_cursor;
end;
PL/SQL控制结构
--条件分支
create or replace procedure pro6(v_in_ename varchar2) is
v_comm emp.comm%type;
begin
--查询补助
select comm into v_comm from emp where ename=v_in_ename;
if v_comm<>0 then--这里把comm空值也加进去了
update emp set comm=comm+100 where ename=v_in_ename;
else
update emp set comm=200 where ename=v_in_ename;
end if;
end;
--循环
declare
i number:=1;
begin
--循环1
for i in reverse 1..10 loop
insert into users values(i,);
end loop;
--循环2
<<start_loop>>
loop
dbms_output.put_line('输出i='||i);
if i=12 then
goto end_loop;
end if;
i:=i+1;
if i=10 then
goto start_loop;
end if;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;