乐趣区

关于mysql:MySQL-select查询教程

查问 Syntax:

SELECT
[* | DISTINCT | col_name | col_name as new_name]
FROM table
WHERE condition
GROUP BY {col_name} HAVING where_condition
ORDER BY {col_name} [ASC | DESC]
LIMIT {[offset,] row_count | row_count OFFSET offset}

查问列

​。select * from ..

​ select from emp; 会查问所有的记录行;生产中杜绝应用(select from emp; 会做全表扫描)

​ select * from emp where empno=’7900′; 会取出所有的字段;生产中杜绝应用

​ 起因 1:占用更多的内存空间(缓冲池 innodb buffer pool)

​ 起因 2:查问条件是一般索引,就会进行回表查问(随机 io,查问性能升高)

​。select col_name from ..

​ select ename, job from emp where empno=’7900′;

​ 索引笼罩(using index)

​。select col_name as new_col_name from ..

​ select sum(sal) as t_sal from emp; 取别名

​。select distinct col_name from ..

​ select distinct job from emp; 去重

where 子句

​。作用:用于条件过滤,有条件的从表中获取数据

​。构造:where col_name 操作符 值

​ where name = ‘ergou’;

​。where 子句中的操作符

罕用操作符
< , <= , > , >= , = , !=
between..and..
in(set)
like
not like
is null
and
or
not

​ 范畴操作符:< , <= , > , >= , between..and..,in(set),not in(set)

​ select * from emp where empno > 7900;

​ select * from emp where empno >= 7900;

​ select * from emp where empno between 7900 and 9999; 查问后果蕴含两边的值

​ select * from emp where empno in(7900,7902);

​ select * from emp where empno not in(7900,7902); 在生产中杜绝应用;

​ 等值查问:=

​ select * from emp where ename = ‘smith’;

​ 注意事项:

​ 字符串类型、日期类型的 sql 语句 where 条件中,肯定要加引号,否则将会产生隐式转换,将进行全表扫描,效率极低。

​ explain select * from emp where ename = ‘007’; 应用到索引,索引利用级别为 type:ref

​ explain select * from emp where ename = 007; 没有应用到索引,将会进行全表扫描,sql 效率非常低

​ 不等值查问:!=

​ 生产中不倡议应用;如果是一般索引作为查问条件,将进行全表扫描;

​ select * from emp where empno != 7900;

​ 含糊查问:like,not like

​ select * from emp where ename like ‘s%’;

​ explain select * from emp where ename like ‘%n’; 生产中杜绝应用右边含糊查问

​ select * from emp where mgr is null;

​ 逻辑操作符:and or not

​ select ename from emp where empno > 7900 and job= ‘clerk’;

​ select ename from emp where empno=7934 or empno=9999;

​。where 子句查问案例

​ 查问工资高于 2000 的员工

​ select * from emp where sal > 2000;

​ 查问工资在 2000 到 3500 的员工状况

​ select * from emp where sal between 2000 and 3500;

​ 查问员工姓名首字母为 S 的员工姓名和工资

​ select ename ,sal from emp where ename like ‘s%’;

​ 查问 empno 为 7839,7902,7934 的员工状况

​ select * from emp where empno in(7839,7902,7934);

​ 查问没有下级的员工状况

​ select * from emp where mgr is null;

​ 查问工资高于 500 或是岗位为 MANAGER,同时还要满足他们的姓名首写字母为 J 的员工信息

​ select * from emp where (sal > 500 or job = ‘MANAGER’) and ename like ‘j%’;

group by .. having 子句

​。作用:对查问后果进行分组统计,having 用于限度分组后果的显示

​。构造:group by col_name1, col_name2 having ..

​。注意事项:select @@sql_mode; ONLY_FULL_GROUP_BY

查问列必须是 group by 前面的字段或者是聚合函数,呈现其余列将报错

​。group by 子句查问案例

​ 查问每个部门的平均工资和最高工资

​ select deptno,avg(sal), max(sal) from emp group by deptno;

​ 查问每个部门的每种岗位的平均工资和最低工资

​ select deptno, job, avg(sal),min(sal) from emp group by deptno,job;

​ 查问部门平均工资低于 2000 的部门号和平均工资

​ select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

order by 子句

​。作用:用于排序

​。构造:order by col_name [asc | desc];

​ select sal from emp order by sal ;

​。注意事项:排序字段是索引,如果指定排序为降序,无奈应用索引的排序,须要从新排序

​。order by 子句案例

​ 查问工资的从低到高的程序显示员工的信息

​ select * from emp order by sal asc;

​ 查问依照员工的年工资进行降序排序显示员工的姓名和年工资

​ select ename , sal*12 as y_sal from emp order by y_sal desc;

limit 子句

​。作用:用于限度取出的记录数,也能够用于分页

​ select * from emp limit 2;

​ 分页:limit (pagenow-1)*pagesize,pagesize

​。分页查问案例:

​ 按员工号升序查问员工姓名、工资,每页显示 3 条记录。请别离显示 第一页,第二页,第三页

​ select ename,sal from emp order by empno asc limit 0,3;

​ select ename,sal from emp order by empno asc limit 3,3;

​ select ename,sal from emp order by empno asc limit 6,3;

退出移动版