sql笔记1

sql

DDL defination 数据定义语言（crate创建，alter更改，drop删除）
DCL 数据控制语言（grant授权，revoke回收）
DML manipulation 数据操作语言（增删改查）
DQL 数据查询语言（排序，分组）
TCL Transaction Control Language 事务控制语言（commit提交，rollback回滚）

1. 表空间的创建

create tablespace 名 datafile ‘文件地址.dbf’ size 大小 autoextend on next 大小 maxsize unlimited;
autoextend on next：分配空间用完之后再次拓展的大小
maxsize：最大可为多少
unlimited：无限制扩展大小（以磁盘为准）

注：使用drop删除表空间不会删除系统中的文件，需要加上：
including contents and datafiles

2. 用户相关

create 创建用户 drop 删除 alter 修改
lock 加锁 unlock解锁
grant 授权 revoke 回收

`connect 临时用户权限`

`resource 正式数据库用户权限`

​ dba 管理员权限

**语法：**grant 权限[or 权限] to 用户名
revoke 权限 from 用户名

3. 表常用操作

表user_tables 用于查看用户下的所有表
表user_tab_cols 用于查看表结构
create table emp_form as select * from emp; 复制表格全部数据到一个新的表（可复制部分数据）
重命名表：alter table 表名 rename to 新表名
添加备注：
comment on table/column 表名/表名.字段名 is 新名
改表结构：
添加列：alter table 表 add（列名 类型）
修改列名：- rename column 列 to 新列名
修改列：- modify（列名 类型 [,列名 类型..]）
删除列：- drop column

​ - drop (列名 [,列名..])

删除表数据（不能回滚）：truncate table 表名

distinct 去重

4. 约束

约束类型：
主键约束：primary key
唯一约束：unique 允许为空
选择约束：check
外键约束：foreign key
表user_constraints 用于查看约束
表外添加约束：
add constraint…
使用drop删除约束

select * from user_constraints;
-- 添加表约束
alter table test1 add constraint   PK_sal  primary key (sal);
-- 外键约束需要引入外表
alter table student add constraint fk_stno foreign key (stno) references grade(stno) ;

alter table test1 add constraint   ck_comm  check (comm='男' or comm ='女' or comm = '未知');

--语法：alter table 表名 drop constraint 约束名
alter table student drop constraint ck_stage;

5. 序列

创建：create sequence 序列名
当前序列为序列名.currval，下一个序列nextval
oracle中的空表：dual
创建指定参数的序列：
create sequence 序列名
start with 10 --开始值
maxvalue 1000 --最大值
increment by 5 --增量值
cache; --缓存值

6. 常用函数

字符

to_char(hiredate,'yyyy-mm-dd') 转化为特定格式的字符

to_char(sal,'99,999.999') 9代表任意数字

to_date(hiredate,'yyyy-mm-dd') 格式化时间

trunc() 取时间

initcap(待转换的字符串) 字符首字母大写，其余小写

concat(str1,str2) 将两个字符串拼接返回新的字符串（可直接使用||拼接字符串）

substr(str,开始位置,保留数量) 截取字符串

lpad(str,总位数,'x') 左补齐函数，不足位数使用x补齐，rpad() 右补齐函数

instr(str,查找字符，开始位置) 返回该字符出现的第一个位置

length(str) 返回字符串长度

数字

ceil(待取值) 向上取整（有小数则+1） floor()相下取整

round(值,要保留的小数点位数) 四舍五入

trunc(值，保留的小数位) 不会四舍五入

日期函数

add_month(输入的时间，要增加的月份数) 给日期增加指定的月份数后返回

next_day(指定的日期,星期几) 返回指定日期的下一个星期几

last_day(日期) 返回指定日期的所在月份的最后一天

trunc(日期[,'yyyy'/'mm'/'day']) 截断时分秒，返回年月日[返回指定日期所在年份的第一天 / 返回指定日期所在月份的第一天 / 返回指定日期所在星期的第一天]

round(日期[,'yyyy'/'mm'/'day']) 当天已过12小时，四舍五入到下一天

转换函数

year：y表示年的最后一位，类推

month：mm

day：dd表示当月第几天，ddd表示当年第几天，dy表示当周第几天

hour：hh表示12小时制，hh24表示24小时制 --MySQL中HH

minute：mi表示分钟 --MySQL mm

second：ss表示秒

to_date(日期,指定格式时间)

to_char(日期,截取时间)

聚合函数 5个：max(), min(), avg(), sum(), count()

nvl(值，补全值) 值为空时显示补全值

decode(列名，判断1，输出1 [, 判断1，输出1…])

分析函数

1. row_number() 排名，值重复时会选择一个值靠前，然后依次排名

​ row_number() over() 默认从第一行到最后一行排名

​ row_number() over(order by 排序)：根据某一列排序，然后排名

​ row_number() over(partition by 分组 order by 排序)：根据某个值分组，然后某一列排序，然后排名

2. rank() 值重复时排名并列，然后跳过原有排名，排名总数不变（后接over()与上同）
3. dense_rank() 值重复时排名并列，不跳过原来的排名

7. 连接与查询

内连接 inner join 等值连接找出相关联的满足条件的所有数据
自然连接 natural join 找共同的列名，没有则笛卡尔积
外连接：做（外）连接，右（外）连接，全（外）连接
全连接：full join 查询所有数据
自连接：连接自身表格
交叉连接：笛卡尔积

子查询：在查询中嵌套查询
exists() 判断是否存在

联合查询

​ 并集 union all 连接两个查询，取并集
​ union 去重取并集
​ 交集 intersect 显示交集
​ 补集 A minus B 减去查询结果B的数据，减去交集，显示剩余结果

分页查询

​ rownum 相当于行号，只能从1开始，放在子查询中可以在外部查询中使用别名调用，显示范围内数据。

备份

–命令方式：
CMD exp 导出用户/密码@数据库名称 file = “导出地址+文件名.dmp”

CMD imp 导入用户/密码@数据库名称 fromuser = “导出用户名” touser = “导入用户名” file = “D:/scott.dmp”

过程

1. plsql匿名块

declare
--声明变量区
· 变量名 变量类型;
· ~      emp.ename%type; 获取表中的变量类型
· ~(相当于表的变量名)   emp%rowtype;  获取表中所有行类型及列，通过变量名.表中的列调用或赋值。
· type 包名 is record( --打包
    变量名 类型;
);
变量名 包名;    --必须给包重命名，调用时必须通过新的名调用。
begin
 --代码执行区（执行sql、存储过程）
 --注意：select语句中需要有into赋值
exception
--异常处理部分
end;

--输出语句dbms_output.put_line();
--使用 := 赋值
--使用 & 获取输入值

2. 分支语句

/* 1. if语句 */
--语法
if(条件) then
	
elsif(条件) then
	
else
	--必须加可执行语句，可使用null; 不会做任何事情
end if;	--必须结束if

/* 2. case */
case
 when 条件 then
 	--满足条件执行
 else
 	----注意case中的语句不要分号；

3. 循环语句

--1. while循环 语法
while (条件) loop
	--代码区
end loop;
--2. for
for (条件) loop
end loop;
--3. loop
loop
	exit when 条件; --满足条件退出循环
end loop;

注意：循环中的赋值使用 := 给变量赋值，for循环时使用a…b代表可取闭区间a到b的值，所有的循环都必须退出。

***结束循环的语句：***3个

​ continue; 退出本次循环

​ exit; 退出循环

​ return; 直接退出程序（过程，方法，匿名块）

goto标签

使用goto（不区分大小写）标签可直接跳到指定的位置，语法如下：

if a>b then
	goto targ;
else
	return;
end if;
<<targ>>	--标签分隔符，定义标签名
c := a-b;

4. 异常的处理

plsql块中，会产生三种错误：

1. 预定义错误：系统定义好的异常
-- no_data_found 无数据返回
-- too_many_rows 返回的数据过多（返回多行数据）
-- duo_val_on_index 唯一索插入重复值，违反主键约束
-- zero_divide 除数为0
-- invalid_cursor 失效的游标
调用：
...
exception
	when no_data_found then
		--处理异常
end;

2. 非预定义错误：违反了Oracle规范，有错误编号和信息，但系统并未定义异常名称，我们将为这种错误定义一个名字，并绑定他的错误代码。
-- 语法格式
declare
	--定义异常
	异常名 exception;
	pragma exception_init(异常名,-22222); 	--绑定异常编码(不能与Oracle的异常编码相同)
begin
	--过程操作
exception
	when 异常名 then
		--处理异常
end;

3. 自定义错误：出现某种情况，我们将其定义为异常，违反了用户的自定义异常
--自定义异常,将异常抛出
declare
	vsal emp.sal%type;
	vsal_error exception;	--定义异常
begin
	select sal into vsal from emp where empno=&empno
	if vsal>3000 then
		--定义抛出异常编码值、错误信息，会弹窗提示错误信息及编码
		raise_application_error(code, '错误信息');
	end if;
end;

--自定义异常，处理
declare
	v_sal emp.sal%type;
	v_sal_error exception;
begin
	select sal into v_sal from emp where empno = &empno;
	if v_sal < 3000 then
		-- 抛出异常 抛出的异常一定要进行处理
		raise v_sal_error;
		-- 定义异常编码值
		raise_application_error(-20000,'薪资问题');
	end if;
exception
	--处理抛出的异常
	when v_sal_error then
		--处理异常
end;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qXPCz1Fr-1628438379821)(C:\Users\Administrator\Desktop\资料\笔记\exception)]

5. 游标

游标是一个存储数据的集合

5.1 静态游标

静态游标分为 隐式游标 和 显式游标

1. 隐式游标

   plsql程序执行dml语句时，会自动创建隐式游标，名称为sql

   隐式游标的属性：

   ​ %found sql语句影响一行或多行 返回true

   ​ %notfound sql没有影响行返回true

   ​ %rowcount 返回sql影响的行数

   ​ %isopen 游标是否打开，始终为false（隐式游标是关闭的）

   begin
   	update emp set comm = 400 where deptno = 10;
   	if (sql%found) then		--使用sql%属性名 调用
   		--执行操作
   	end if;
   end;
   

2. 显式游标

   --语法
   declare
   	--声明游标(默认关闭，是一个结果集，使用时需要打开)
   	cursor 游标名 is select * from emp;
   	--声明行变量类型
   	v_emp emp%rowtype;
   begin
   	--打开游标
   	open 名;
   	--从游标中提取一行数据
   	fetch youbiao into v_emp;
   	--关闭游标
   	close youbiao;
   end;
   

在循环中使用游标

​ for for循环自动打开游标，并提取第一行数据，提取完成后结束循环，自动关闭游标。

for a in youbiao loop
	--处理
end loop;

​ while 循环 使用while需要定义的行变量，需要打开游标，并需要手动提取数据

declare
	cursor youbiao is select * from emp;
	v_emp emp%rowtype;
begin
	open youbiao;
	fetch youbiao into v_emp;
	while(youbiao%found) loop	--找不到数据退出循环
		--处理
		fetch youbiao into v_emp;
	end loop;
	close youbiao;	--游标一定要关闭，不关闭不会报错，占内存
end;

​ loop 循环与while同

5.2 动态游标

ref 动态游标可以在运行的时候与不同的语句进行关联，他是动态的。
ref 动态游标被用于处理多行的查询结果集，ref 动态游标是ref 类型的变量，类似于指针。
定义ref 动态游标类型：type<类型名> is ref cursor return <返回类型>;
声明ref 动态游标：<游标名> <类型名>;
打开ref 动态游标：OPEN<游标名> FOR <查询语句>;

--强类型的动态游标 带有return语句
declare
    type v_leixing is ref cursor return emp%ROWTYPE;
    v_youbiao v_leixing;
    v_emp emp%rowtype;
begin
    open v_youbiao for select * from emp;
       loop
          fetch v_youbiao into v_emp;
          exit when v_youbiao%notfound;
          dbms_output.put_line(v_youbiao%rowcount||'==='||v_emp.ename);   
       end loop;
    
    close v_youbiao;
end;

--弱类型的动态游标 不带return
declare
	type v_leixing is ref cursor;
	v_youbiao v_leixing;
	v_emp     emp%rowtype;
begin
  open v_youbiao for select * from emp;
  
  loop
    fetch v_youbiao into v_emp;
    exit when v_youbiao%notfound;
    dbms_output.put_line(v_youbiao%rowcount || '===' || v_emp.ename);
  end loop;
  
  close v_youbiao;
end;	

6. procedure

语法：

create procedure 名(参数 in type,参数 out type)
     --in表示输入参数（外部传入，值不可更改），out表示输出参数，in out表示输入输出函数，可更改。
     --参数可设置默认值，调用时需要传入参数，
     --若创建时无传入参数则不使用()
as/is  --可不声明变量。过程中可以不使用变量
     --声明变量块
     变量名  变量类型;
begin   --过程
     --可为某些变量赋初值
     --可嵌套内部块 declare...
     --sql语句
     --pl/sql语句
exception
     --异常时执行
end;

或者直接点击过程—>测试。
使用 过程名（参数名 := 需要输出的参数名）
as和is的区别:
在视图view中只能使用as，在游标中cursor只能使用is。

———— 过程批量插入数据：

--存储过程中插入数据
create or replace procedure add_d
is
begin
  for i in 1..100 loop
    insert into t_emp values
    (
       i+1000,
       dbms_random.string('u',5),
       'ClERK',
       i+1,
       trunc(sysdate-i),
       1000+i,
       10,
       2000
     );
   end loop;
end;

函数

无参存储函数 有参存储函数

语法：

create function 函数名(参数名 类型[,参数])	--可创建无参存储函数，不可加括号
return 返回类型		--不加分号，不可为空
is
	--定义变量区;
begin
	--函数实现
	return 返回值;
end;

--调用
declare
	--定义变量
begin
	--函数需要使用变量接收返回值
end;

--可以直接使用查询语句调用
select 函数名(参数) from dual;