1.索引
drop index idx_name on user;
create index idx_name on user;
show index from user;
2.查询
select * from user limit 0,10;
select * from user order by id desc,addtime asc;
SELECT DISTINCT JOB FROM T_EMP;
select 10+ifnull(null,0) ;
select * from t_emp where DATEDIFF(now(), hiredate)/365>=39;
select * from t_emp where ename regexp "[a-zA-Z]{4}";
select * from t_emp where ename regexp "^[\\u4e00-\\u9fa5]{2,4}$";
select avg(sal+ifnull(comm,0)) from t_emp;
select deptno,round(avg(sal)) from t_emp group by deptno;
select deptno,group_concat(ename),count(*) from t_emp where sal>2000 group by deptno;
select deptno from t_emp group by deptno having avg(sal)>2000;
update t_emp e,(select avg(sal) as avg from t_emp) t set e.sal=e.sal+150 where e.sal<t.avg;
select ename from t_emp where deptno=(select deptno from t_emp where ename="SCOTT") and ename!="SCOTT";（效率低）
select t2.ename from t_emp t1,t_emp t2 where t1.deptno=t2.deptno and t1.ename="SCOTT" AND t2.ename!="SCOTT";（效率高）
select * from t_emp e,(select avg(sal) avg from t_emp) t where e.sal<t.avg;
SELECT * FROM t_emp where sal>= ANY
(select sal from t_emp where ename in("ALLEN","MARTIN"));
SELECT * FROM t_emp where sal>= ALL
(select sal from t_emp where ename in("ALLEN","MARTIN"));
3.函数
abs()绝对值 floor()向下取整 ceil()向上取整 round()四舍五入 power(2,3)幂函数 log(7,3) ln(10)对数;
select now(),curdate(),curtime();
select date_format(now(),"%D")
select COUNT(*) from t_emp where date_format(`hiredate`,"%Y")=1981 AND DATE_FORMAT(hiredate,"%m")<=6;
SELECT DATE_ADD(NOW(),INTERVAL 15 DAY);某个时间加上偏移量后的时间。
select datediff(now(),"1990-2-16");计算两个日期相差多少天。
4.加密
select hex(aes_encrypt("hello","abc123"));加密
select aes_decrypt(unhex("2345ABEA5CEADE0FD427E1670D9BED01"),"abc123")；解密