一、查询语句
1、字符串截取:
update sys_area set short_name = substring_index(short_name, '地区', 1) where type = 3 and short_name is not null and short_name like '%地区'
substring(str, pos); substring(str, pos, len)
2、rowNum实现
SELECT @rownum:=@rownum+1 rownum, CollectSn From
(SELECT @rownum:=0,bbgmain.* FROM qbdb.bbgmain WHERE collectsn!='' ORDER BY collectsn limit 10) t
3、获取分组中最新的信息
mysql执行顺序:
写的顺序:select … from… where…. group by… having… order by..
执行顺序:from… where…group by… having…. select … order by…
所以group by到select时只取到分组里的第一条信息
解决方法:
- where+group by(对小组进行排序)
select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc
- 从form返回的数据下手脚(即用子查询)
select * from (select * from `test` order by `date` desc) `temp` group by category_id order by `date` desc
5、查询重复字段
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多余的重复记录(多个字段)
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
6、MySQL order null 0 - 把null和0(零)排在最后
SELECT `vcenter_ip`, `status`, `sla_id` FROM vm_list ORDER BY sla_id is NULL, sla_id = 0, sla_id ASC;
7、获取上一次插入的自增id
select LAST_INSERT_ID()
二、数据库更新
1、根据查询出来的值更新表多个字段
UPDATE student D
LEFT JOIN (SELECT
B.studentId,
SUM(B.score) AS s_sum,
ROUND(AVG(B.score),1) AS s_avg
FROM score B
WHERE b.examTime >= '2015-03-10'
GROUP BY B.studentId) C
ON (C.studentId = D.id)
SET D.score_sum = c.s_sum,
D.score_avg = c.s_avg
WHERE D.id = ''
三、数据库插入
1、批量插入
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);
四、数据库功能
2、数据库定时:
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;
3、表结构分析
select * from go_run_record procedure analyse();
4、存储过程
DROP PROCEDURE IF EXISTS `p_update_praise_week`;
DELIMITER ;;
CREATE PROCEDURE p_update_praise_week()
BEGIN
DECLARE w_user_id VARCHAR(64);
DECLARE w_count INT;
DECLARE h_user_id VARCHAR(64);
DECLARE stops INT DEFAULT 0;
DECLARE cur_account CURSOR FOR select a.user_id,a.count from go_user_praise_week a where a.del_flag = 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stops=1;
OPEN cur_account;
read_loop: LOOP
SET stops = 0;
FETCH NEXT from cur_account INTO w_user_id,w_count;
if stops = 1 then
leave read_loop;
end if;
select a.user_id into h_user_id from go_user_praise_history a where a.user_id = w_user_id and a.del_flag = 0;
if h_user_id is null then
insert go_user_praise_history values(w_user_id,w_count,now(),'0',now());
ELSE
update go_user_praise_history set `count` = `count` + w_count where user_id = w_user_id;
end if;
END LOOP;
CLOSE cur_account;
END
;
DELIMITER ;
执行动态sql
--存储过程名和参数,参数中in表示传入参数,out标示传出参数,inout表示传入传出参数
create procedure p_procedurecode(in sumdate varchar(10))
begin
declare v_sql varchar(500); --需要执行的SQL语句
declare sym varchar(6);
declare var1 varchar(20);
declare var2 varchar(70);
declare var3 integer;
--定义游标遍历时,作为判断是否遍历完全部记录的标记
declare no_more_departments integer DEFAULT 0;
--定义游标名字为C_RESULT
DECLARE C_RESULT CURSOR FOR
SELECT barcode,barname,barnum FROM tmp_table;
--声明当游标遍历完全部记录后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_departments=1;
set sym=substring(sumdate,1,6); --截取字符串,并将其赋值给一个遍历
--连接字符串构成完整SQL语句,动态SQL执行后的结果记录集,在MySQL中无法获取,因此需要转变思路将其放置到一个临时表中(注意代码中的写法)。一般写法如下:
-- 'Create TEMPORARY Table 表名(Select的查询语句);
set v_sql= concat('Create TEMPORARY Table tmp_table(select aa as aacode,bb as aaname,count(cc) as ccnum from h',sym,' where substring(dd,1,8)=''',sumdate,''' group by aa,bb)');
set @v_sql=v_sql; --注意很重要,将连成成的字符串赋值给一个变量(可以之前没有定义,但要以@开头)
prepare stmt from @v_sql; --预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt; --执行SQL语句
deallocate prepare stmt; --释放掉预处理段
OPEN C_RESULT; --打开之前定义的游标
REPEAT --循环语句的关键词
FETCH C_RESULT INTO VAR1, VAR2, VAR3; --取出每条记录并赋值给相关变量,注意顺序
--执行查询语句,并将获得的值付给一个变量 @oldaacode(注意如果以@开头的变量可以不用通过declare语句事先声明)
select @oldaacode:=vcaaCode from T_sum where vcaaCode=var1 and dtDate=sumdate;
if @oldaacode=var1 then --判断
update T_sum set iNum=var3 where vcaaCode=var1 and dtDate=sumdate;
else
insert into T_sum(vcaaCode,vcaaName,iNum,dtDate) values(var1,var2,var3,sumdate);
end if;
UNTIL no_more_departments END REPEAT; --循环语句结束
CLOSE C_RESULT; --关闭游标
DROP TEMPORARY TABLE tmp_table; --删除临时表
end;
5、触发器
新数据:new.字段名,旧数据:old.字段名
DROP TRIGGER IF EXISTS t_afterinsert_on_record;
CREATE TRIGGER t_afterinsert_on_record
AFTER INSERT ON go_run_record
FOR EACH ROW
BEGIN
end;