SQL总结

SQL总结

一、触发器

1、概述
	触发器是由事件来触发某个操作，这些事件包括insert、update、delete事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句的时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。
2、触发器的创建
	2.1、创建语法
		create [or replase] trigger 触发器名称
		{before|after} {insert|update|delete} on 表名
		for each row
		触发器执行的语句块;
	2.2、举例1
		先创建两张表备用
			create table test_trigger(
            	id int primary key auto_increment,
                t_note varchar(30)
            );
            
            create table test_trigger_log(
            	id int primary key auto_increment,
                t_log varchar(30)
            );
            
         创建触发器：在向test_trigger数据表中插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息
         
         delimiter //
         create trigger before_insert
         before insert on test_trigger
         for each row
         begin
         	insert into test_trigger_log (t_log)
         	values('before insert');
         end //
         delimiter ;
         
         向test_trigger数据表中插入数据
         insert into test_trigger(t_note)
         values('测试');
         
         查看test_trigger_log数据表中的日志信息
         select * from test_trigger_log;
	2.2、举例2
		创建触发器：在向test_trigger数据表插入数据之后，向test_trigger_log数据表中插入after_insert的日志信息
		
		delimiter //
		create trigger after_insert
		after insert on test_trigger
		for each row
		begin
			insert into test_trigger_log(t_log)
			values('after_insert');
		end //
		delimiter ;
	2.3、举例3
		create trigger salary_check_trigger
		before insert on employees
		for each row
		begin
			declare mgsalary double;
			select salary into
		end

二、存储过程

1、创建语法
	delimiter //
	create [or replace] procedure 过程名(
    	参数1 [in|out|in out] 数据类型，
        参数2 [in|out|in out] 数据类型，
        ……
    )
    is/as
    声明变量
    begin
    	语句块；
    end //
    delimiter ;
	例子：
		创建一张表
		create table user(
            id number(10) primary key,
            name varchar(100)
        );
       	使用存储过程向user数据表插入数据
       	delimiter //
       	create or replace procedure "insertuser"(
        	id in number,
            name in varchar2
        )
        is
        begin
        	insert into user
        	values(id,name);
        end //
        delimiter ;
        调用存储过程：
        	如果是命令窗口就用 [exec 存储过程名]
        	如果是sql窗口就用 [begin 存储过程名 end]
        	如果是程序中调用就用 [call 存储过程名]
2、定义一个指向select的存储过程
	create or replace procedure pro_name
	as
	--定义一个游标，并且为其指定作用
	CURSOR cur_name is
		select * from tbl_name;
	--定义游标类型
	cur_type cur_name%rowtype;
	begin
		for cur_type in cur_name
		loop
			--当循环到游标内容为空时退出循环
			exit when cur_name%notfount;
			--打印
			dbms_output.put_line('姓名：'||cur_type.name||','||'学号：'||cur_type.id);
		--关闭循环
		end loop;
    end;

三、数据类型

1、数字型
	1.1、number
		长度不限，只要是数字就行
	1.2、number(x)
		为整数，并且长度不超过x位
	1.3、number(x,y)
		存在小数部分，总长度不超过x位，小数部分为y位
2、字符型
	2.1、varchar2(x)
		长度不超过x位
	2.2、char(x)
		固定长度是x位，不够则补空格
3、日期型
	date

四、运算部分

1、关系运算
	=	等号
	！=	不等号
	< <=	小于 小于等于
	> >=	大于 大于等于
	between...and...
	in(值1,值2...)等同于	过滤的对象 = 值1 or 过滤的对象 = 值2 or ...
	distinct	去重
2、集合运算
	intersect	交集
	union	并集，去重
	union all	并集，不去重
	minus	补集
3、算数运算
	+ - * /
4、数据类型间的相互转换
	4.1、转换日期型
		to_date(x,y)	y的长度要和x保持一致
		如：to_date(sysdate,'yyyy-mm-dd hh24:mi:ss')
	4.2、转换字符型
		to_char()
	4.3、转换数字型
		to_number()
5、特殊字符查询运算
	is null
	is not null
	like	'_'表示具体的一个位，'%'表示不确定的位数

五、查询

1、查询格式	
	select 要返回的信息
	from 表
	join...on...
	where 过滤条件
	group by 分组字段
	having 分组后的过滤条件
	order by 排序字段
2、运行顺序
	from
	where
	group by
	having
	select
	order by
	在oracle中没有mysql中的limit，当我们需要找某列的最大值或最小值时，需要借助函数实现
	select * from (
    	select * from student
    	order by id
		)
		where rownum <= 2;
3、子查询
	3.1、子查询使用场景、条件：
		当条件不明确时，使用子查询查出具体的值，再进行过滤
		当我们需要的条件不满足时，通过子查询构造出我们需要的结果集再进行查询
	3.2、子查询的返回值：
		子查询返回单行结果，一个值用等号进行过滤
		子查询返回多行结果集，多个值用in进行过滤
		子查询返回多行多列结果集，用in进行过滤
	优先将查询结果的数据返回过滤到最小，然后再对结果集进行关联查询
4、连接查询
	4.1、格式
		[inner] join...on...	内连接
		left [outer] join...on...	左外连接
		right [outer] join...on...	右外连接
		full [outer] join...on...	全连接
		左/右外连接的结果集是显示查询的主表的所有数据，以及从表跟主表相交的部分，从表不足的部分（行）补NULL
		全连接的结果集是两个表相交的部分正常显示，不同的部分互补NULL
	4.2、oracle独有写法
		内连接
		select ...
		from 表1,表2,...
		where 关联条件1/过滤条件 [and/or 关联条件2 and/or ...]
		左外连接
		select ...
		from 表1,表2,...
		where 表1的某个字段 运算符 表2的某个字段(+)	表1是主表，表2是从表
		右外连接
		select ...
		from 表1,表2,...
		where 表1的某个字段(+) 运算符 表2的某个字段	表2是主表，表1是从表
5、伪列(oracle独有)
	rownum	对某列进行排序，可以使用它来限制查询返回的行数
	rowid	返回当前列的ID值
	删除重复行的rowid写法
		delete from grade t2
		where rowid != (
			select max(rowid)
			from grade t
			where t.name = '张三'
			and t.subject = 'Chinese'
		)
			and t2.name = '张三'
			and t2.subject = 'Chinese';
		commit;

六、操作

1、数据插入
	insert into 表名(列名1,列名2,...)
	values(值1,值2,...);
	列名可以省略，当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序
	值的数量和顺序要与列名的数量和顺序一致，值的类型与列名的类型一致
2、向表中插入一个结果集
	insert into 表名(列名1,列名2,...)
	查询结果集;
	在这种语法下，要求结果集中的每一列的数据类型必须与表中的每一列的数据类型一致，且数量也一致
3、数据更新
	update 表名 set 列名1=值,列名2=值,...where 条件;
	没有where条件的话是全表更新
4、数据删除
	delete from 表名 where 条件;
	truncate table 表名;
	truncate是将表中的数据全部删除(清空数据)

七、建表

1、建表语法
	create table 表名();
2、表的约束
			primary key	主键约束
			foreign key	外键约束
			check	检查约束
			unique	唯一约束
			not null	非空约束
	alter table 表名 add constraint 约束名 约束内容;
	
	alter table 表名 add constraint
	primary key(列名1,列名2,...);	添加主键约束
	
	alter table 表名 add constraint
	foreign key(列名1,列名2,...)
	references 从表名(列名1,列名2,...);	添加外键约束
	
	alter table 表名 add constraint check(条件);	添加check约束
	
	alter table 表名 add constraint unique(列名);	添加唯一约束
	
	alter table 表名 modify 列名 not null;	添加非空约束
	
	alter table 表名 drop constraint 约束名;	删除约束
3、对表的操作
	3.1、创建表
		create table 表名(
    		列名1 类型,
        	列名2 类型,
        	...
    	);
    3.2、复制表结构
    	create table 表名1
    	as select * from 表名2 where 1=2;
    3.3、复制表结构和表中数据
    	create table 表名1
    	as select * from 表名2 where ...;
    3.4、删除表
    	drop table 表名;
    3.5、添加列
    	alter table 表名 add 列名 类型;
    3.6、修改列类型
    	alter table 表名 modify 列名 类型;
    3.7、修改列名
    	alter table 表名 rename column 旧列名 to 新列名;
    3.8、删除列
    	alter table 表名 drop column 列名;
    3.9、修改表名
    	alter table 表名 rename to 新表名;
    3.10、给表添加注释
    	comment on table 表名 is 注释;
    3.11、对列添加注释
    	comment on column 表名.列名 is 注释;
    3.12、创建索引
    	create [unique] index 索引名 on 表名(列名1,列名2,...);
    3.13、删除索引
    	drop index 索引名;
    3.14、创建序列
    	create sequence 序列名
    	[start with num] 从哪开始
    	[increment by increment] 每次增加多少
    	[maxvalue num | nomaxvalue] 最大值
    	[minvalue num | nominvalue] 最小值
    	[cycle | nocycle] 是否循环
    	[cache num | no cache]; 在内存中保留多个序号
    3.15、删除序列，序列不能更改，只能删除重建
    	drop sequence 序列名;
    3.16、创建视图
    	create or replace[{force | noforce}] view 视图名
    	as
    	select 查询
    	[with read only constraint] 表示视图只读，不加则表示视图可以进行增删改
    	force：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用
    	noforce：如果基表不存在，无法创建视图，默认选项
    3.17、删除视图
    	drop view 视图名;
4、对用户的操作
	4.1、创建用户
		create user 用户名 identifed by password [account lock | unlock]
	4.2、给用户授权
		grant connect, resource to 用户名;
		直接将connect角色、resource角色授予用户
		connect角色：连接数据库；resource：正常使用数据库
		例如：
		(1)给用户创建表的权限
		grant create table to 用户;
		(2)给用户创建视图的权限
		grant create view to 用户;
	4.3、收回用户权限
		revoke 角色|权限 from 用户(角色)
	4.4、修改用户密码
		alter user 用户名 identified by 新密码;
	4.5、给用户加锁/解锁
		alter user 用户名 account lock|unlock;
5、DBlink
	5.1、创建DBlink
		create [public] database link dblink名
		connect to 数据库用户名 identified by "数据库密码";
		using 'TNS连接串信息';
		--TNS连接串信息：可以使用整串信息，也可以使用代表这串信息的别名
	5.2、查看用户是否具备创建dblink的权限
		select * from user_sys_privs where privilege like upper('%DATABASE LIKE%') and username='用户名';
	5.3、授权用户创建dblink的权限
		grant
			create public database LINK,
			drop public database LINK
		to 用户名;
	5.4、给dblink创建同义词
		create synonym 同义词名 for dblink连接的数据库里的对象@dblink名;
		如：create synonym TEST for company@TESTLINK;
			--company：数据库里的一张表
			--TESTLINK：创建的连接到数据库的dblink表
6、创建同义词
	create [or replace] [PUBLIC] synonym 同义词名 for 用户名.对象名;
    --对象包括：表、视图、序列、过程、函数、程序包等
    --默认情况下创建私有同义词，只能被当前创建用户使用
    如：create or replace public synonym emp for scott.emp;
    	创建公有同义词，这样的话其他用户直接访问emp就是访问了scott用户下的emp表了
7、删除同义词
	drop synonym 同义词名;
8、给用户授权 创建/删除 同义词
	grant
		create public synonym,
		drop public synonym
	to scott;

八、函数

1、数字函数
	nvl(目标字段，默认值)	判断目标字段里面是否为空，如果不为空，则取目标字段中的值，如果为空，则赋一个默认值
	abs(x)	返回绝对值
	mod(x,y)	返回x除以y的余数
	power(x,y)	返回x的y次方
	ceil(x)		向上取整
	floor(x)	向下取整
	round(x,y)	将x保留y位小数
	trunc(x,y)	y的界限为小数点，断x在第y后的数字，把y位之后的数字全部干掉(全换成0)
2、字符函数
	add_months(d,n)	返回日期d加上n个月后的日期
	last_day(d)		返回d所处月份的最后一天的日期
	months_between(d1,d2)	返回两个日期之间相隔几个月
	round(d,fmt)	对日期d按照不同维度(fmt:yyyy/mm/dd/day[星期]/q[季度]/w[当月第几周]/iw[今年第几周1]/ww[今年第几周2])来进行四舍五入
	trunc(d,fmt)	对日期d按照不同维度(fmt)来截断
3、聚合函数
	max()
	min()
	avg()
	sum()
	count()
4、分析函数
	4.1、聚合类分析函数
		聚合函数名(目标字段) over(partition by ... order by ... asc/desc)
	4.2、排名类分析函数
		row_number() over()		1234
		rank() over()	1224
		dense_rank() over()		1223
	4.3、位移类分析函数
		lag() over()	向上位移
		lead() over()	向下位移
5、判断函数
	nvl(目标字段，默认值)	判断目标字段里面是否为空，如果不为空，则取目标字段中的值，如果为空，则赋一个默认值
	nvl2(目标字段,默认值1,默认值2)	判断目标字段是否为空，如果不为空，择取默认值1，为空取默认值2
	case...when...then...else...end		对某一列数据进行判断，返回一列数据
	decode(目标字段,值1,结果1,值2,结果2,...,其他结果)		作用类似于case...when
	exists(x)	判断x是否有数据返回，如果有则成立，没有则不成立，一般x为子查询结果
	not exists(x)
	in()	exists和in可以相互转换
	not in()
	sign()	返回一个数字的正负标志

九、plsql语法

1、声明变量
	declare
		变量 数据类型 := 初始值;
		变量 数据类型 := &提示字段;	自定义输入数据
		变量 表名.列名%type := 初始值;	将整个表的数据拿到自身，需要使用其中的数据类型时，表名.列名调用即可
	begin
		select...sql取数
		into...对应变量
		
		dbms_output.put_line();		数据输出格式
	end;
2、条件判断
	2.1、if...else...
		declare
			声明变量
		begin
				逻辑操作
				select...
				
				if 条件1 and/or 条件2 then
					操作1;
				elsif 条件3 and/or 条件4 then
					操作2;
				...
				else
					其他操作;
				end if;
		end;
	2.2、case...when...
		declare
			声明变量
		begin
				逻辑操作
				case
					when 条件1 and/or 条件2 then
						操作1;
					when 条件3 and/or 条件4 then
						操作2;
					...
					else
						其他操作;
					end case;
		end;
3、循环控制
	3.1、loop
		declare
			声明变量
		begin
				逻辑操作
				loop
					循环体
					if 退出循环的条件 then
						exit;
					end if;
					
					--退出循环的简写
					exit when 退出循环的条件;
				end loop;
		end;
	3.2、while
		declare
			声明变量
		begin
				while 进入循环的条件 loop
					循环体
				end loop;
		end;
	3.3、for
		declare
			声明变量
		begin
				逻辑操作
				for 循环变量 in 循环下限 .. 循环上限 loop
					循环体
				end loop;
		end;
4、游标
	游标就是一个指向结果集的指针，通过这个指针，可以获取到指向的结果集中的数据
	4.1、显示游标
		declare
			声明变量
			cursor 游标名 is
			select 查询结果集;
		begin
				--使用显示游标结合循环，依次获取到游标指向的结果集的每一行
				for 循环变量 in 游标名 loop
					循环体
					在循环体内可以依次使用结果集的每一行数据，使用方式就是：循环变量.游标指向的结果集的列名
				end loop;
		end;
		开发规范：游标名以c_开头
	4.2、参数游标
		declare
			声明/定义游标
			cursor 游标名(参数1 数据类型,参数2 数据类型,...) is
			select 查询结果集;
		begin
				--使用游标
				for 循环变量 in 游标名(具体的值1,具体的值2,...) loop
					循环体
				end loop;
		end;
		开发规范：参数名以p_开头
	4.3、手动管理游标
		declare
			声明游标
			cursor 游标名(参数1 数据类型,参数2 数据类型,...) is
			select 查询结果集;
			
			--需要定义个游标变量
			变量名 游标名%rowtype;
		begin
				--使用游标
				open 游标名(实际的值1,实际的值2,...); --打开游标
				
				loop
					--提取数据：将获取到的数据分别赋值给变量
					fetch 游标名
					into 游标变量
					
					--退出循环
					if 游标名%notfound then
						exit;
					end if;
					
					--退出循环可以简写成
					exit when 游标名%notfound;
					
					数据操作(增删改查)
					
					close 游标名; --关闭游标
		end;

十、自定义函数

create or replace function 函数名(
	参数1 [in|out|in out] 数据类型,
    参数2 [in|out|in out] 数据类型,
    ...
)
return 返回结果的数据类型
is/as
begin
	自定义函数的计算逻辑
	return 计算结果;	--函数的执行体中一定要包含返回值
end;

十一、package

1、创建包头
	create [or replace] package 包名
	is/as
		--存储过程
		procedure 过程名1;
		procedure 过程名2(参数名 参数类型 数据类型);
		
		--函数
		function 函数名1 return 计算结果的数据类型;
		function 函数名2(参数名 参数类型 数据类型) return 计算结果的数据类型;
	end 包名;
2、创建包体
	create [or replace] package body 包名
	is/as
		--过程
		procedure 过程名
		is
		过程的完整代码
		end 过程名;
		
		--函数
		function 函数名
		return 计算结果的数据类型
		is
		函数的完整代码
		end 函数名;
	end 包名;

十二、SQL复制表结构及其数据

1、只复制表结构
	1.1、create table a 
		 as 
		 select * from b
		 where 1=2;
	1.2、create table a
		 like
		 b;
2、复制表结构及其数据
	create table a
	as
	select * from b
3、只复制数据
	3.1、如果两个表结构一样
		insert into a
		select * from b
	3.2、如果两个表结构不一样
		insert into a(column1,column2...)
		select column1,column2,...
		from b
	

十三、on、where、having

1、SQL执行顺序
		from->on->where->group by->having->select->order by->limit
2、where和having区别（分组前后有区别）
		having可以包含聚合函数的过滤条件，也可以不包含，通常与group by连用
		having先进行计算，再进行过滤
		where不能包含聚合函数，可以与group by连用也可以不用
		where先进行过滤，再进行计算
	优化手段：将having中可以使用where进行过滤的优先使用where进行过滤，减少不必要的计算
3、on和where的区别（连接前后有区别）
		内连接的情况下二者等效
		外连接的情况下，on会在进行外连接前对数据进行筛选，where会在进行外连接之后对数据进行筛选

十四、in、join、exists

1、in的用法
	确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表与外表做一个笛卡尔积，按照条件进行筛选。因此相对内表较小的时候，in的速度较快。
2、exists的用法
	指定一个子查询，检测行的存在。遍历外表，看外表中的记录有没有和内表的数据一样的。
3、in与exists的区别
	如果子查询得出的结果集较少，主查询中的表较大且又有索引时应该用in，反之用exists。如果用exists，那么以外层表为驱动表，首先被访问；如果是in，那么先执行子查询，而且in不对null进行处理。
	in是把外表和内表做hash连接，而exists是对外表做loop循环，每次loop循环再对内表进行查询。
    如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 快。

十五、‘’、’ '、null

在oracle中''与null一样
在db2中不一样

十六、commit、savepoint、rollback

1、commit
	将上一个commit或rollback命令之后的全部事务保存到数据库中
	commit [work];
2、savepoint
	创建一个逻辑点
	2.1、savepoint savepoint_name;
		在实在事务语句之间创建一个保存点
	2.2、rollback to savepoint_name;
		回退到保存点
3、rollback
	撤销上一个commit或rollback之后的命令

十七、hive行转列与列转行

1、列转行
原始数据

SELECT name,
	max(CASE WHEN subjects = '语文' THEN score ELSE NULL END) AS `语文`,
	max(CASE WHEN subjects = '数学' THEN score ELSE NULL END) AS `数学`,
	max(CASE WHEN subjects = '英语' THEN score ELSE NULL END) AS `英语`
FROM score_stu 
GROUP BY name;
结果展示

2、行转列
首先现将行数据转换为一个map的key-value键值对，然后使用炸裂函数
SELECT name, subjects, score
FROM 
(SELECT name, MAP('语文',chinese, '数学',math, '英语',english) s
FROM score_stu1) a
LATERAL view explode(a.s) t AS subjects, score