sql笔记

最新推荐文章于 2023-07-15 18:19:08 发布

朝夕与溟

最新推荐文章于 2023-07-15 18:19:08 发布

阅读量140

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_45093343/article/details/119541530

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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：无限制扩展大小（以磁盘为准）

--查看所有表空间
select * from v$tablespace;

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

2. 用户相关

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

connect 临时用户权限
dba 管理员权限
resource 正式数据库用户权限

session 登录权限
create table 创建表的权限
drop table 删除表权限（不需要create）

-- 授予view视图权限
grant create view to scott;

-- 授予debug权限
grant DEBUG CONNECT SESSION, DEBUG ANY PROCEDURE to scott;

-- 查看当前用户
select user from dual;

-- 查看当前用户权限
select * from user_sys_privs;

语法：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（列名类型 [,列名类型..]） modify——只修改字段的属性

删除列：- drop column

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

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

distinct 去重

4. 约束

约束类型：
主键约束：primary key
唯一约束：unique 允许为空
选择约束：check
外键约束：foreign key

not null 非空约束使用modify修改列，为列添加非空约束。

表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 test1 add constraint   UK_job  unique (job);

-- 语法：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') 格式化时间

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…])

分析函数

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

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

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

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

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

7. 连接与查询

内连接 inner join 等值连接找出相关联的满足条件的所有数据

等值连接 inner join…on，常用的就是等值连接。

自然连接 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
 end 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 返回的数据过多（返回多行数据）
-- dup_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-j8awU9PK-1628498674656)(C:\Users\Administrator\Desktop\资料\笔记\img\exception.png)]

5. 游标

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

5.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;
```

显式游标

--语法
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;