SQL语句进阶学习五
- 使用视图
- 1)视图是虚拟的表。将查询的数据封装成一个表格。
- 2)创建视图
- 3)用视图过滤掉不想要的数据
- 4)视图数据的更新
- 使用储存过程
- 1)储存过程的作用:储存过程实质是一个函数,为将一条或多条select语句封装成一个函数,以便于后期的使用。
- 2)创建储存过程和使用
- 1.无变量名储存过程的创建 使用
- A.无变量名创建储存过程
- B.使用无变量名的储存过程
- 2.有变量名储存过程的创建和使用
- A.创建有变量名的储存过程
- B.使用无变量名的储存过程
- 3.带有out类型储存过程的创建和使用
- A.带有out类型储存过程的建立
- B.带有out类型储存过程的使用
- 4.智能储存过程的建立和使用
- 3)删除储存过程
- 4)检查储存过程
- 项目经验SQL语句学习
- 1.cast()函数:可以将某种数据类型的表达式转化为另一种数据类型
- 2.SQL语句中由数据库中的时间戳类型转成时间标准化格式类型。to_char
- 3.Mybatis中,使用like进行模糊查询
- 4.MyBatis中,使用jdbcType=VARCAHR,使其当插入值为null时,不会报错。
- 5.SQL语句对IP进行配对城市,学会SQL语句中的字符串切割(168.127.1.2)【oracle】
- 6.truncate关键词对整个表格数据进行删除
- 7.单表的字段值进行关联
- 8.to_timestamp()函数
- 9.not exists的使用
- 10.union和union all的区别
- 11. 左外连接 left outer join … on …
- 9. 右外连接 right outer join … on …
- 10.SQL带有括号的语句
- 11.distinct语句
- 12.实现分组后取每组中的第一条数据(oracle中partition by和row_number函数的使用)
- 13.concat字段
- 14.join ... on ...句式
- 12.批量更新SQL写法
- 1)SQL写法
- 2)mybatis写法
- 13.SQL插入数据后返回主键
- 14.数据库查询一个汉字占用字节情况(对于oracle库)
- 15.merge into的使用
- 16.delete与exists的结合使用,并且拼接表名后缀
- 17.ibatis批量更新SQL写法
- 18.嵌套子查询语句
- 19.以select语句创建表
- 19.以select语句批量插入
- 20.mysql创建触发器
- 21.mysql安装某字段大小排序,当该字段为空时,则按最大值(或最小值)进行排序
使用视图
1)视图是虚拟的表。将查询的数据封装成一个表格。
如:
使用视图后,SQL语句为:
1.使用视图可将关联的表格关系封装成一个表格。
2.性能方面,由于视图没有数据,所以处理执行查询时,必须执行任一个检索。关联多个表格时,性能会下降很厉害。
2)创建视图
3)用视图过滤掉不想要的数据
将cust_email为空的数据过滤。
4)视图数据的更新
当基表的数据更新时,视图数据亦会更新,因此更新操作可在基表中进行。
使用储存过程
1)储存过程的作用:储存过程实质是一个函数,为将一条或多条select语句封装成一个函数,以便于后期的使用。
2)创建储存过程和使用
1.无变量名储存过程的创建 使用
A.无变量名创建储存过程
注意:当基表products的prod_price数据值变化时,其productpricing()的函数结果值也将变化。
B.使用无变量名的储存过程
2.有变量名储存过程的创建和使用
A.创建有变量名的储存过程
B.使用无变量名的储存过程
此条语句不会返回数据,想要返回数据需执行以下select语句:
3.带有out类型储存过程的创建和使用
A.带有out类型储存过程的建立
B.带有out类型储存过程的使用
4.智能储存过程的建立和使用
3)删除储存过程
4)检查储存过程
show create procedure productpricing;
项目经验SQL语句学习
1.cast()函数:可以将某种数据类型的表达式转化为另一种数据类型
如:SELECT CAST(NOW() AS DATE)
结果:2017-11-27
2.SQL语句中由数据库中的时间戳类型转成时间标准化格式类型。to_char
如:select to_char(p.login_time,‘YYYY-MM-DD-HH24:MI:SS’) as login_time from history p where p.apply_no = #{applyNo};
结果:可以直接将数据库中时间戳格式转换成时间格式(oracle才能这样用,请搜索mysql中to_char()函数)
3.Mybatis中,使用like进行模糊查询
如:@Select(“select * from t_user where name = ‘%’||#{username}”)
@Select注解中,||表示连接符号
4.MyBatis中,使用jdbcType=VARCAHR,使其当插入值为null时,不会报错。
批量插入的示例:
@Insert({"<script>" +
"insert into t_user (name,age,sex) values" +
"<foreach collection='userList' item='item' index='index separator=','>" +
"(#{item.name,jdbcType=VARCHAR},#{item.age,jdbcType=VARCHAR},#{item.sex,jdbcType=VARCHAR})" +
"</foreach>" +
"</script>"})
5.SQL语句对IP进行配对城市,学会SQL语句中的字符串切割(168.127.1.2)【oracle】
@Select("<script>select s.ipprovince,s.ipCity from t_base_city " +
"where (select to_number(regexp_substr(s.ip_start,'[^.]+',1,1) from dual)) = " +
"(select to_number(regexp_substr(#{paramMap.ip},'[^.]+',1,1))) from dual " +
"and where (select to_number(regexp_substr(s.ip_start,'[^.]+',1,2) from dual)) = " +
"(select to_number(regexp_substr(#{paramMap.ip},'[^.]+',1,2))) from dual " +
"and where (select to_number(regexp_substr(s.ip_start,'[^.]+',1,3) from dual)) = " +
"(select to_number(regexp_substr(#{paramMap.ip},'[^.]+',1,3))) from dual " +
"and where (select to_number(regexp_substr(s.ip_start,'[^.]+',1,4) from dual)) = " +
"(select to_number(regexp_substr(#{paramMap.ip},'[^.]+',1,4))) from dual " +
"where rownum = 1</script>")
6.truncate关键词对整个表格数据进行删除
7.单表的字段值进行关联
select * from t_fenshu t1 where t1.score = (select max(t2.score) from t_fenshu t2 where t1.major = t2.major);
解释:找出分数表中,每科的最高分的基本信息(姓名,分数,科目等等)
试试:找出分数表中,每班每科的最高分的基本信息?
8.to_timestamp()函数
update t_student set created_date = to_timestamp(‘2019-12-29 14:20:00’) where 1=1;
update t_timetest set t_time = TIMESTAMP('2019-12-29 14:20:00') where id = 1;
update t_timetest set t_time = to_timestamp('2019-12-29 14:20:00','YYYY-MM-dd hh24:mi:ss') where id = 1;
select * from t_timetest where t_time > STR_TO_DATE('2019-10-22','%Y-%m-%d %H:%i:%s');(mysql中时间格式化语句:%Y-%m-%d %H:%i:%s)
select DATE_ADD(t_time,INTERVAL 3 month),t_time from t_timetest; 【完成对时间的增减】
解释:用具体的时间更新时间的类型字段
mysql中使用date()和timestamp()函数。
9.not exists的使用
select ar.appoint_no as “appointNo”,ar.name as “custName” from appoint_register ar where ar.um = #{um} and not exists (select s.appoint_no from el_city_channel_info s where s.appoint_no = ar.appoint_no ) and ar.ap_loan_valid is null;
解释:not exists:筛除这些预约号出现el_city_channel_info的数据。
exists代表存在量词,带有exists谓词的子查询不返回任何数据,只产生逻辑真值ture或者逻辑假值false
与EXISTS谓词相对的是NOT EXISTS谓词。使用存在量词NOT EXISTS后,若对应查询结果为空,则外层的WHERE子语句返回值为真值,否则返回假值。
EXISTS与IN查询很像,当EXISTS作为WHERE查询条件时,其SQL执行逻辑为先执行EXISTS前面主查询,然后再将查询条件一一代入EXISTS中,检查查询结果
exists的参考文章
10.union和union all的区别
SELECT name from t_fenshu UNION all SELECT name FROM t_right
如果我们需要将两个select语句的结果作为一个整体显示出来,我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。
union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
Union All:对两个结果集进行并集操作,包括重复行,不进行排序;
11. 左外连接 left outer join … on …
左边的表是基本表,将左边的表的信息全部显示(不论是否可以有匹配,即为空也显示)。
SELECT * from emp e LEFT outer join dept d on e.DEPTNO=d.DEPTNO
9. 右外连接 right outer join … on …
右边的表是基本表,将右边的表的信息全部显示(不论是否可以有匹配,即为空也显示)。
SELECT * from emp e right outer join dept d on e.DEPTNO=d.DEPTNO
点击查看更多连接查询
10.SQL带有括号的语句
select * from t_fenshu where sex='woman' and class = '1' and score > 60 AND NAME = 'SHE' OR NAME = 'ZHANGSAN'
select * from t_fenshu where sex='woman' and class = '1' and score > 60 AND (NAME = 'SHE' OR NAME = 'ZHANGSAN')
解析:第一条不带括号的SQL语句表示:select * from t_fenshu where sex=‘woman’ and class = ‘1’ and score > 60 AND NAME = ‘SHE’ union all select * from t_fenshu where NAME = ‘ZHANGSAN’。而第二条带有括号的SQL语句表示:select * from t_fenshu where sex=‘woman’ and class = ‘1’ and score > 60 AND NAME = ‘SHE’ union all select * from t_fenshu where sex=‘woman’ and class = ‘1’ and score > 60 AND NAME = ‘zhangsan’。其作用域不同
11.distinct语句
筛选出一个字段,不同值的数据
12.实现分组后取每组中的第一条数据(oracle中partition by和row_number函数的使用)
SELECT
*
FROM
(
SELECT
h.main_apply_no,
lb.putout_date, ---放款时间
count( 1 ) AS num,
row_number () over ( PARTITION BY putout_date ORDER BY putout_date DESC ) rn
FROM
wrk_lob_lb lb,
wrk_apply_history h
WHERE
h.apply_no = lb.apply_no
AND lb.loan_status >= '04'
GROUP BY
h.main_apply_no,
lb.putout_date
) t
WHERE
t.num > 1
AND rn = 1;
解释:1.当使用row_number()函数时,会生成按数据放款时间排序的列。
2.PARTITION BY putout_date ORDER BY putout_date DESC 表示以字段putout_date分组并按数据的放款时间降序排序
3.tab2.rn = 1则是在取每个分组排序后中的第一条数据
4.over partition by和group by都可以实现分组,两者的区别两者的区别参看文章
13.concat字段
sql中,连接两个字段的关键字。
14.join … on …句式
SELECT a.name as Aname, b.name as Bname ,a.score,b.content from t_fenshu a JOIN t_souyin b ON a.name = b.name ;
解释:join 表名 on 联接条件
12.批量更新SQL写法
1)SQL写法
UPDATE uke_hwork_upload set teacher_name = case id
when 787513 then '张衡璇' when 866956 then '田璐' end where id in (787513,866956)
解释:当id值为787513则更新老师名字为张衡璇,当id值为866956时,更新老师名字为田璐。where条件加快筛选速度
2)mybatis写法
<update id="updateTeacherNameByTeacherId">
UPDATE uke_hwork_upload set teacher_name =
<foreach collection="idNameVoList" item="idName" index="index" separator=" "
open="case teacher_id" close="end">
when #{idName.tutorId} then #{idName.tutorName}
</foreach>
where teacher_id in (
<foreach collection="teacherIdList" item="teacherId" separator=",">
#{teacherId}
</foreach>
)
</update>
13.SQL插入数据后返回主键
<insert id="insertDetail" parameterType="java.util.Map" >
<selectKey resultType="java.lang.String">
SELECT sys_guid() as id from t_student
</selectKey>
INSERT INTO t_student
(id,name,age)
VALUES
(#{id},#{name},#{age})
</insert>
14.数据库查询一个汉字占用字节情况(对于oracle库)
Select lengthb('爱') from dual -----------字节长度
select length('爱') from dual ------------字符长度
15.merge into的使用
merge into report_list t using (
select distinct main_apply_no from t_temp temp where temp.date_collect = '' and temp.date_created > to_date(sysdate)
and temp.product_no in (select product_no from t_config inf where inf.remark = 'ZED') ) a
on (a.main_apply_no = t.main_apply_no and t.product_no in
(select product_no from t_config config
where config.remark = 'ZED'))
when not watched then insert(
report_body,
rpt_date
)
values (
with t1 as (select prpduct_no ,putout_date,policy_no,
row_number() over (partition by a.main_apply_no
order by a.putout_date desc) rn from t_temp t2
where t2.main_apply_no = t.main_apply_no) A where A.rn = 1)
select 'CX',t1.poutout_date from t1
)
16.delete与exists的结合使用,并且拼接表名后缀
delete from sec_b_bs$bodySuffix$ a where exists
(select 1 from (select temp.id_sec_b_bs$bodySuffix$ from (select t.id_sec_b_bs$bodySuffic$,
row_number() over ( partition by t.id_num,t.inf_surc_code order by t.date_updated desc)
as row_flg from sec_b_bs_$bodySuffix$ t where
t.date_collect = #reportDate# and t.rpt_date_code
= (select min(m.rpt_date_code) from
sec_b_bs$bodySuffix$ m where m.date_collect =
#reportDate#) and t,report_body = #reportDate# ) temp where temp.row_flg <> 1 ) p
where p.id_sec_b_bs$bodySuffix$ =
a.id_sec_b_bs$bodySuffix$);
17.ibatis批量更新SQL写法
<insert id = "batchInsertReword" parameterClass = "java.util,List">
insert all
<iterate conjunction = " ">
into record
(id,batchNo)
values
(sys_guid(),'PH_DRP')
</iterate>
select 1 from dual
</insert>
18.嵌套子查询语句
查询条件带有子查询
select * from SMS_SEND p where p.date_created > sysdate - (
select nvl(max(t.filed),7) + nvl(max(t.value),7) from config_info t where t.config_value = p.send_subject
)
and p.send_subject in (
select config_value from config_info t where t.config_type = 'smsSendType' and t.config_id = 'SMS_SEND_FLAG' and t.reserved_filed = 'Y'
)
查询列带有子查询
select count(t.idNum) from (
select distinct (
case when (
select 1 from P2P_apply p where p.apply_no = l.apply_no ) is not null then (
select app.certid idNum from p2p_apply app,wrk_history his where app.apply_no = his.history_apply_no and l.apply_no = his.apply_no and l.apply_no = his.apply_no )
else (
select cust.id idNum from appl_cust cust where cust.apply_no l.apply_no end)
) idNum
from wrk_lon_lb l,wrk_lon_increase_credit t where l.loan_status >= '04'
and l.product_no = '15'
) t
19.以临时表作为连接表进行查询
SELECT
b.NAME,
b.id_num,
inf.amt,
dbt2.acct_bal
FROM
sec_l_acct_bs b
LEFT JOIN sec_l_bsinf inf ON inf.id_bs = b.id_bs
INNER JOIN (
----子表作为连接表 SELECT
dbt.loan_no,
dbt.acct_bal,
ROW_NUMBER() over ( PARTITION FROM sec_l_dbt dbt dbt BY dbt.loan_no ORDER BY dbt.date_collect DESC ) AS num
) dbt2 ON dbt2.loan_no = b.loan_no
AND num = 1
WHERE
b.loan_no = '110'
AND b.date_collect = to_date ( '20210430', 'yyyyMMdd' );
19.以select语句创建表
create table table_1 as select * from t_copy where 1= 0 ;
19.以select语句批量插入
insert into t1 select * from t_copy where t.subject = 'CX';
20.mysql创建触发器
在表tqjc_lg_message_record 每次插入时,其列id_key触发自动生成UUID且不含‘-’
delimiter $$
create trigger tqjc_lg_message_record_id_key -- 触发器的名称
before insert
on tqjc_lg_message_record ---触发器建立的表位置
for EACH row begin
set new.ID_key = replace(UUID(),'-','');
END $$
delimiter ;
21.mysql安装某字段大小排序,当该字段为空时,则按最大值(或最小值)进行排序
if(exp1,A,B) 函数。如果exp1返回真,则返回A,否则返回B。
SELECT
id_key,
area_name,
area_num,
index_left_top_x,
index_left_top_y,
index_right_bottom_x,
index_right_bottom_y,
stock_material_no,
stock_material_name,
span_name,
is_effective,
span_no,
parent_id,
CREATE_NAME,
CREATE_DATE,
UPDATE_NAME,
UPDATE_DATE
FROM
tqjc_xg_area_info
ORDER BY
span_no DESC,
IF( ISNULL( index_left_top_x ), 20000000, 1 ),
index_left_top_x;