mysql语句

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")；解密

 

5.修改字段名称

alter table tbl_bank_count RENAME  COLUMN  organization to dqdm