SQL语句优化和注意点
1.去除冗余字段,只返回使用到的字段参数
2.避免使用*号,统计总数使用count(1)
3.表筛选数据,固定条件参数,写在最底层SQL,避免查找表所有数据
4.筛选条件IN如果使用多个参数,可以使用wehre (a,b,c) in (('1','2','3'),('1','2','3')) 形式。避免返回全部数据和到代码去做筛选查找
5.SQL能确认的参数值,可以使用$注入方式
..... 持续更新中
一:MySQL
- datetime与timestamp类型区别:
datetime:与时区无关,存储的范围广(1001-9999),占用8个字节
timestamp:与时区有关,存储的范围小(1070-2038),占用4个字节 - char与varchar类型区别:
char:长度是固定的。存储时不管实际存储的数据长度,直接按char规定的长度分配存储空间
varchar:长度是可变的。根据实际存储的数据分配最终的存储空间 - mysql慢查询日志:
mysql慢查询日志是mysql提供的一种日志记录,它用来记录在mysql中响应时间超过阈值的语句,具体指运行时间超过long_query_time值的sql,则会被记录在慢查询日志中。
查看启动状态:SHOW VARIABLES LIKE '%slow_query_log%'
开启慢查询日志:
临时配置:临时
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;
永久配置:修改配置文件my.cnf或my.ini,在[mysqlId]一行下面加入两个配置参数
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
- 存储base64字符文件类型(BLOB)
tinyblob:仅255个字符
blob:最大限制到65K字节
mediumblob:限制到16M字节
longblob:可达4G - mysql查询BOLB封装文件数据信息
select CONVERT(字段 USING utf8) AS name from table - mysql奇数和偶数方法
– 奇数
select * from table WHERE id regexp ‘[13579]$’;
– 偶数
select * from table WHERE id regexp ‘[02468]$’; - mysql排序中rank() over,dense_rank() over,row_number() over的区别
rank() over:查询指定条件后进行排名。如有同样数值并列在一起并且下一位则空出所占位置
dense_rank() over:查询指定条件后进行排名,如有同样数值并列在一起并且下一位不空出所占位置
row_number() over:查询指定条件后不需要考虑是否并列,哪怕根据条件查询出来的数值相同也会进行连续排名 - mysql多个字符拼接,使用CONCAT函数
- 语句拼接:union all
- 日期时间格式化:date_format(create_time, ‘%Y-%m-%d %H:%i:%s’)
- 最大max(),最小min(),总合计sum()
- 分组合并:GROUP_CONCAT
- mysql插入数据判断是否已经存在,如存在则忽略,使用函数ignore
insert ignore into table - mysql创建索引
#唯一索引
create unique index 索引名称 on 表(字段);
#普通索引
create index 索引名称 on 表(字段) - mysql创建视图
create view 视图名称(字段别名1,字段别名2) as
select 字段1,字段2 from 表 - mysql强制查询索引 force index()
select * from 表 t1 force index(索引名称) where t1.emp_no = 10005 - mysql给表添加字段 alter table
alter table 表 add column 字段 类型 not null default ‘默认值’; - mysql构造一个触发器
create trigger触发器名称
#触发器名称
after insert on触发表
for each row
begin #开始
#触发动作
insert intoaudit
values(new.ID,new.NAME);
end #结束 - mysql使用replace替换
update 表 set
emp_no = replace(emp_no,旧值,新值) - mysql修改表名
alter table 旧名称 rename as 新名称 - mysql创建外键约束,并对应另外一张表主键id
alter table 外键表 add foreign key (外键约束字段) references 主键表 (主键id); - mysql查询某个字符在字符串中出现的次数
SELECT (LENGTH(字符串字段) - LENGTH(REPLACE(字符串字段,某个字符,‘’))); - mysql获取某个字符串指定段字符函数:SUBSTR(t1.
name
,-2,2) - mysql根据相同的值进行分组,并将值进行拼接:group_concat(number)
- mysql保留指定小数点函数:FORMAT(字段,3)
- mysql获取中位数位置的范围,使用函数FLOOR和CEILING
FLOOR(X)表示向下取整,只返回值X的整数部分,小数部分舍弃
CEILING(X)表示向上取整,只放回值X的整数部分,小数部分舍弃 - cmd命令远程访问远程MySql数据库
mysql -uroot -proot -h192.168.0.10 -P3306 -Dtest
mysql -u用户名 -p密码 -h远程数据库IP地址 -P端口 -D数据库名 - 批量执行遍历语句
use 数据库表;
DROP PROCEDURE IF EXISTS 存储过程名称;
DELIMITER $
CREATE PROCEDURE 存储过程名称()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 执行次数 DO
...执行语句
SET i = i+1;
END WHILE;
END $
DELIMITER ;
CALL 存储过程名称();
DROP PROCEDURE IF EXISTS 存储过程名称;
- Mysql查询结果添加序列号
第一种方法:
select (@i:=@i+1) as i,table_name.* from table_name,(select @i:=0) as it ;
第二种方法:
set @rownum=0;
select @rownum:=@rownum+1 as rownum, t.username from auth_user t limit 1,5;
- 前置条件满足后在执行后置条件
(
case
when FM.[Year] = 2020 and FM.[Month] >= 9 then 1
when FM.[Year] = 2021 and FM.[Month] <= 8 then 1
end
) = 1
- 格式化两位小数点
format(data,2)
二:SqlServer
- 数据累加行值
sum(累加字段) over(order by 分组字段) as nsj