SQL的优化和技巧

最新推荐文章于 2024-04-22 14:14:06 发布

琼歌

最新推荐文章于 2024-04-22 14:14:06 发布

阅读量4k

点赞数 1

分类专栏：后端技术

本文链接：https://blog.csdn.net/qq_36505948/article/details/81042901

版权

后端技术专栏收录该内容

30 篇文章 1 订阅

订阅专栏

1----加载

每个JDBC模块都是通过Class.forName()来加载Drvier驱动，获取连接从而进行数据库操作.

通过以下方法加载驱动类：

通过以下方法建立连接：

创建执行SQL的语句：

数据库操作模板代码：

-----创建

----查询：

-----更新

----删除

SQL注入问题修改statement为PrepareStatement():

数据库对应基本数据类型：

事务属性：

事务：将多个数据库操作归类为一个逻辑单元，使得事务从一个状态到另外一个状态。多个操作要么一起完成，要么一起失败。常用命令有提交和回滚！

以上所说为最基本的模式，在此基础上还有jdbc的连接池，SpringMvc的数据库sessionFactory工厂!

2----SQL语句

表结构：desc 表名

DDL语句：属于改变变结构的语句，跟数据记录无关

创建数据库：create database databseName；

删除数据库：drop database databaseName；

创建tabe表：

create table tablName(

name1 int(11) PRIMARY KEY ，

name2 varchar(12)

);

tableName在MySQL中不需要带引号，但是在其他数据库中如DB2是需要的

PRIMARY KEY是在表的主键

============================================================================================================

修改表名字：

ALTER TABLE 旧表名称 RENAME 新表名称；

修改字段的数据类型：

ALTER TABLE 表名 MODIFY 字段名称新数据类型；

修改字段名称：

ALTER TABLE 表名 CHANGE 旧字段名称新字段名称新数据类型；

添加新字段：

ALTER TABLE 表名 ADD 新字段名称数据类型 [约束条件 FIRST|AFTER 存在字段名称]

删除字段：

ALTER TABLE 表名 DROP 想删除的字段；

删除外键约束：

ALTER TABLE 表名 DROP PRIMARY KEY；

删除数据库表：
DROP TABLE 表名

主键约束：

单字段主键：PRIMARY KEY，在字段后面追加即可

多字段主键：PRIMARY KEY(id,name) 在建表语句最后追加即可

外建约束：外键约束就相当于铭牌和人，铭牌要跟在人身上，那么至少需要有一个夹在夹在人身上。并且这个夹子要能唯一代表一个铭牌。两个表之间建立连接就是外键约束的目的。

以上认知是的方法是以拥有其他表的关键字段的表为主表，最直观的认知；除此之外还有一种认知即是,以抛出关键字段去其他表中寄生的表为主体,那么抛出字段的表为主表，被寄生的表为从表，相当于寄生虫与被寄生体的关系；两者本质上没有区别，但是注意不要混淆。目前大部分人为第二种认知，大部分的处理方式都是第二种!

------根据第二种认知分析，对于外键约束应该添加的位置为：

拥有外键字段的从表里面：CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)

外键字段到哪里，字段信息写到哪里

引擎：在每个建表语句背后可添加： ENGINE=InnoDB DEFAULT CHARSET=latin1非空约束与默认约束：

需要维护非空的格式：字段名称字段类型 notnull；

需要非空为可默认的：子弹名称字段类型 not null default ‘xxx’；

唯一约束：
字段名字段数据类型 UNIQUE；

自动递增：

字段名数据类型 auto_increment;

。。。。。。。

数据库查询语句

基础查询：select * from batch.batch_transdtl where WORKDATE between 20170101 and 20171231

查询条数：select count(*) from batch.batch_transdtl where WORKDATE between 20170101 and 20171231

模糊查询：select CONDITIONID from batch.batch_condition where SYSID='001100' and VALUE like '001100:00000073%' 不建议使用like,% ,下划线

排序查询：select * from mid.bank_ufs_filedtl where sysid='004028' order by OPERDATE desc desc从大到小

范围查询：SELECT * FROM afa.afa_filemap WHERE FILENO in ('001217','173') with ur

连接查询:

select * from batch.batch_flowcfg as a1 left join batch.BATCH_STEPINFO as a2 on a1.stepid=a2.stepid left join batch.BATCH_STEPMODECFG as a3 on a1.stepid=a3.stepid left join batch.BATCH_FUNCTION as a4 on a3.funcid=a4.funcid where flowMode ='1111000004'

左连接：左连接后的检索结果是显示a1的所有数据和a2中满足where 条件的数据

select * from mmall_order as a1 LEFT JOIN mmall_order_item as a2 on a1.order_no=a2.order_no

右连接：右连接后的检索结果是显示a2的所有数据和a1中满足where 条件的数据

select count(*) from mmall_order as a1 RIGHT JOIN mmall_order_item as a2 on a1.order_no=a2.order_no

有效连接：两张表都有并且满足条件的才会显示

select count(*) from mmall_order as a1 INNER JOIN mmall_order_item as a2 on a1.order_no=a2.order_no

在目前的开发中，INNER JOIN为显性连接，而左右连接为隐性，隐性连接正在被逐渐抛弃

全连接：全部连接

select count(*) from mmall_order as a1 FULL JOIN mmall_order_item as a2 on a1.order_no=a2.order_no

注意事项:

on：过滤掉不符合的记录，再处理其他数据，不管条件是否为真都会返回对应连接的主动表记录

where：直接过滤掉数据，没有左右连接的概念，不符合条件的都会被砍掉，相当于直接获取满足条件的(抽取而不是过滤)

原则上都是对中间进行操作

排重查询：select distinct xxx from mmall.mmall_product

非空查询：select * from batch.batch_info where batchinfono is not null;

日期格式函数：

WHERESTR_TO_DATE (‘08/09/2008’,’%m/%d/%Y’)

基础函数：COUNT() , MIN() , MAX() , AVG() , SUM(),GROUP BY ，IN ，ALL，ANY（任意一个），AND ，OR ，BETWEEN

统一重复：select order_no,user_id,shipping_id,COUNT(1) from mmall_order GROUP BY order_no , user_id , shipping_id HAVING COUNT(1)>1

获取首记录：select * from mmall_order FETCH FIRST 1 ROW only 用于DB2中

工作常用技巧：

左右连接：太简单了，不写

备份数据：export to GRJSH_CASHDECLARE.del of del "select * from GRJSH.GRJSH_CASHDECLARE"

导入数据：import from ALL_NOT_ECIF_END.del of del modified by codepage=1386 coldel0x1b datesiso nochardel replace into BATCH.BATCH_CORPINFO_TMP;

重构数据：REORG table batch.batch_corpinfo_new，当涉及到修改表结构，每三次必须reorg一次

递增维护：insert into batch.batch_maindict (ITEM,ITEMENAME,ITEMCNAME)select right(digits(cast(max(int(ITEM)) + 1 as int)), 5) ,'RHEJ_POSTTYPE','融慧E家有的证件类型' from batch.batch_maindict;

删除表：DROP TABLE 表名

更新数据：update dgxorq.customer set ACCNO='6225682141005192376',ACCCUSTNAME='珊捎娜' where ICCARD='09115370'

查询组数：SELECT CORPIDTYPE,CORPNAME , CORPIDCODE ,count(1) FROM BATCH.BATCH_CORPINFO_NEW
GROUP BY CORPIDTYPE,CORPNAME , CORPIDCODE HAVING count(1) >1 以什么为一组然后查询出多少组

联表更新：

merge into BATCH.BATCH_CORPINFO_NEW as a using(SELECT DISTINCT CORPIDNUM,CORPNAYNAME,CORPIDTYPE,ECIFNUM,ACCNO
from BATCH.BATCH_CORPINFO_TMP2) as b on a.CORPNAME=b.CORPNAYNAME and a.CORPIDCODE = b.CORPIDNUM and a.CORPIDTYPE=b.CORPIDTYPE
WHEN matched then UPDATE SET a.ECIFCUSTNUM=b.ECIFNUM,a.BASEACCNO=b.ACCNO else ignore

SQL掩码技术:
-------->CONCAT(CONCAT(substr(Q1.CORPACCNO,1,4),'*****'),substr(Q1.CORPACCNO,7)) as 基本账号
-------->Q1.CORPACCNO 你要脱敏的账号
-------->substr(Q1.xxx ,1,4)

SQL条件字段切割：
-------->substr(workdate,1,6)='201802' 从1开始计算，此处为条件的时候
-------->substr(code,1,locate('_',code)-1) code为你查询出来的字段，对字段切割和分割
-------->locate('_',code) 是搜索到_的位置是多少，可以对sql语句中字段进行操作

存在的：select * from mmall_order where exists (select user_id from mmall_order_item)

工作的设置：

字典表格的维护通过字典字表+字典母表

--字典表 select * from batch.batch_maindict 下拉列表框的列表名

--字段映射 select * from batch.batch_subdict 下拉列表框的列表项

对账表的设置：

交易有交易自己的交易记录表+明细有明细自己的明细表

系统有自己存在的一个校验模块维护自己的一套校验模块标志

系统维护自己的客户信息需要保存历史，一般情况下如果存在客户信息，那么维护基本的合同信息，外业务协议以另外一张表来挂钩处理

常用的优化技巧：

查询语句字段明确，方便开发确认和缓存维护

IN字段内部是以数组的形式存储，所以尽量少增加查询的字段，以提高效率

对于明确条数的查询，请使用limit

如果排序字段没有添加索引，尽量少用排序

IN字段是内层方法为主，先执行，而exists是以外层方法为主先执行，尽量使得先执行的体积比较小

避免在where条件判断null值，和执行表达式，因为会使得索引失效

尽量少使用%前缀作为条件模糊查询，可以使用%后缀模糊查询，因为如‘%xxx%’ 之类会造成全部扫描而索引失效

对于联合索引，是必须携带首字段作为条件去使用索引，首字段要尽可能多用

如果涉及到联合索引，如果涉及<>或者between会导致，后面的索引字段失效

尽量使用有效连接

尽量用小表驱动大表

索引：索引是对数据库表中一列或多列的值进行排序的一种结构(B树)，使用索引可快速访问数据库表中的特定信息。

主要分为聚集索引(CLUSTERED INDEX)和非聚集索引(NONCLUSTERED INDEX)两种，聚集索引指的是一列或多列的物理顺序和逻辑顺序是一致的，一个数据库表只能有一个聚集索引，我们通常将主键(一般为自增int型)设为聚集索引。而非聚集索引则可以有多个，而且非聚集索引并不会改变数据库表的物理结构。聚集索引诸如自动递增的Int类型即是满足物理顺序！

可以把索引理解为一种目录，常规的聚集索引就是类似于根据拼音排序查找，我们直接根据目标的首字母或者一些信息直接在目录上按照顺序定位；而非聚集索引则是类似于按照目标的偏旁来查找，我们通过偏旁查找到该目标在目录上的位置，后通过其映射到的页码找到目标！两者的区别在于聚集索引本身就是按照自己的目录来查找，而非聚集则是需要借助其他的目录来跳转查找，效率较低!

语法：

-- 创建索引

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Sales.Orders') AND name='idx_orders_orderid_orderdate_shipaddress')
    CREATE NONCLUSTERED INDEX idx_orders_orderid_orderdate_shipaddress ON Sales.Orders(orderid,orderdate,requireddate,shippeddate,shipname,shipaddress);
GO

-- 删除索引
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Sales.Orders') AND name='idx_nc_orderdate')
    DROP INDEX idx_nc_orderdate ON Sales.Orders;
GO

琼歌

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
SQL的优化和技巧

1----加载每个JDBC模块都是通过Class.forName()来加载Drvier驱动，获取连接从而进行数据库操作.通过以下方法加载驱动类：通过以下方法建立连接：创建执行SQL的语句：数据库操作模板代码：-----创建----查询：-----更新----删除SQL注入问题修改statement为PrepareStatement():数据库对应基本数据类型：事务属性：事务：将多个数据库操作归类为...
复制链接

扫一扫