SQL的优化和技巧
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 |
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 |