岗前实训第三天 MySQL的查询

SELECT * FROM `user` WHERE `password` IS NULL             //查询密码为空的行

SELECT * FROM `user` WHERE `password` = ''                //

SELECT * FROM `user` WHERE `password` IS NOT NULL           //查询密码不为空的行

SELECT * FROM `user` WHERE userId > 3 AND userId < 6         //查询大于3小于6的行

SELECT * FROM `user` WHERE userId BETWEEN 3 AND 7         //查询3到7的行

SELECT * FROM `user` WHERE userId IN (3, 5, 6)         //查询3,5,6行

SELECT * FROM `user` WHERE userId = 3 OR userId = 5 OR userId = 6          //查询3,5,6行

SELECT * FROM `user` WHERE userId NOT IN (3, 5, 6)          //查询不是3,5,6的行

SELECT * FROM `user` ORDER BY flag ASC           //flag升序排列，默认升序，ASC可省略

SELECT * FROM `user` ORDER BY flag DESC           //flag降序排列

SELECT * FROM emp 

SELECT UPPER(LOWER(ename)) FROM emp             //把员工名字大写全部转为小写,LOWER转为小写，UPPER转为大写

SELECT LENGTH(ename), ename from emp            //员工名字字母数统计

SELECT COUNT(*)员工数, AVG(sal)平均工资, MAX(sal)最高工资, MIN(sal)最低工资, SUM(sal)工资总和 from emp                          //统计总行数，平均工资，最高工资，最低工资，所有员工工资总和

学校名字叫张三，学校是一个范围，张三为人名

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno  HAVING deptno > 10          //对各部门分组，分组过后的数据查询大于10的部门不能用where只能用having

SELECT * FROM emp WHERE sal > (SELECT avg(sal)FROM emp)           //子查询，查询大于平均工资的员工

SELECT *FROM dept WHERE deptno = 20

SELECT e.ename, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno           //连表查询

SELECT e.*, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno               //连表查询

SELECT e.*, d.dname FROM emp E INNER JOIN dept d ON e.deptno = d.deptno WHERE SAL > 2000            //连表查询，使用频繁

SELECT * FROM `user` LIMIT 3, 3          //检索记录行4-6