mysql函数

条件查询

SELECT DISTINCT 字段 FROM 表; distinct 去重

select * from emp where ename like 'l%' like 通配符

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is null --过滤字段值为空的

select * from emp where sal<=3000 and sal>=10000--等效

select * from emp where sal between 3000 and 10000--等效

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

聚合 aggregation

select count(*) from emp

select min(sal) min,max(sal) max from emp --最小值最大值

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

select deptno, AVG(sal) from emp group by deptno #按部门分组 having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

tuncate删除所有记录