十、条件查询(where)

1、运算符

比较运算符：>、>=、=、<、<=、!=、<>、like、in、is null、between and
逻辑运算符：and、or、not

2、比较运算符案例

#查询1号部门的员工信息
SELECT * FROM emp WHERE deptno=1
#查询灭绝师太的所有信息
SELECT * FROM emp WHERE ename='灭绝师太'
#查询2025-12-20入职的员工信息
SELECT * FROM emp WHERE hiredate='2025-12-20'
#查询工资大于2000的员工信息
SELECT * FROM emp WHERE sal>2000
#查询部门号不等于3号的员工信息
SELECT * FROM emp WHERE deptno<>3
#查询工资是1000到2000之间的员工信息
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000
#查询岗位是'CLERK CLERK ANALYST'的员工信息
SELECT * FROM emp WHERE job IN('CLERK','CLERK','ANALYST')
#查询张姓员工的信息
SELECT * FROM emp WHERE ename LIKE '张%'
SELECT * FROM emp WHERE ename LIKE '张_'
#查询没有奖金的员工信息
SELECT * FROM emp WHERE comm IS NULL
#查询没有部门的员工信息
SELECT * FROM emp WHERE deptno IS NULL
#查询有奖金的员工信息
SELECT * FROM emp WHERE comm IS NOT NULL

3、逻辑运算符案例

#查询工资是1000到2000的员工信息
SELECT * FROM emp WHERE sal>=1000 AND sal<=2000
#查询工资小于2000 并且 入职日期大于1987-01-01
SELECT * FROM emp WHERE sal<2000 AND hiredate>'1987-01-01'
#查询1号部门中 并且 工资小于2000的员工信息
SELECT * FROM emp WHERE deptno=1 AND sal<2000
#查询工资大于3000且入职日期早于1987-01-01
SELECT * FROM emp WHERE sal>3000 AND hiredate<'1987-01-01'
#查询不是张姓的员工
SELECT * FROM emp WHERE ename NOT LIKE '张%'
#查询不是1号部门的员工
SELECT * FROM emp WHERE NOT deptno=1
#查询部门号不是1，3，5的员工信息
SELECT * FROM emp WHERE deptno NOT IN(1,3,5)

十一、函数

1、函数分类

分组函数：max、min、avg、count、sum
单行函数：数学函数、字符串函数、日期函数、加密函数

2、分组函数案例

#查询总共有多少个员工
SELECT COUNT(*) FROM emp
#查询最高工资
SELECT MAX(sal) FROM emp
#查询最低工资
SELECT MIN(sal) FROM emp
#查询平均工资
SELECT AVG(sal) FETCH emp
#查询工资总和
SELECT SUM(sal) FROM emp
#查询工资总和含奖金
SELECT SUM(sal+comm) FROM emp
#查询入职最早和最晚的员工信息
SELECT MAX(hiredate),MIN(hiredate) FROM emp
#查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno
#查询每个部门的工资总和，部门人数，最高工资，最低工资，平均工资，并按总和排序和部门号排序
SELECT deptno,SUM(sal) sum_sal,COUNT(*),MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY deptno ORDER BY sum_sal,deptno DESC
#查询平均工资大于2000的部门
SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno HAVING avg_sal>2000
#查询所有部门中领导的平均工资
SELECT deptno,AVG(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno

/*
思考联系：
#1.统计人数小于4的部门的平均工资。
select deptno,avg(sal),count(0) num from emp group by deptno having num<4
#2.统计各部门的最高工资，排除最高工资小于2000的部门。
select deptno,max(sal) max_sal from emp group by deptno having max_sal>2000
#3.显示部门编号大于2 的部门的人数，要求人数大于3
select deptno,count(0) count from emp where deptno>2 group by deptno having count>3
*/

#统计人数小于4的部门的平均工资
SELECT deptno,AVG(sal),COUNT(*) count FROM emp GROUP BY deptno HAVING count<4 
#统计各部门的最高工资，排除最高工资小于8000的部门
SELECT deptno,MAX(sal) max_sal FROM emp GROUP BY deptno HAVING max_sal<8000
#显示部门编号大于1的部门人数，要求人数大于3
SELECT deptno,COUNT(*) count FROM emp WHERE empno>1 GROUP BY deptno HAVING count>3

3、单行函数

SELECT empno,CONCAT_WS('_',ename,job) '姓名_工作',CONCAT(sal,deptno) FROM emp
#查询大于x的最小整数，小于x的最大整数，四舍五入
SELECT AVG(sal),CEIL(AVG(sal)),FLOOR(AVG(sal)),ROUND(AVG(sal),2) FROM emp
#查询员工名字有几个字
SELECT LENGTH(ename)/3 FROM emp
#查询员工的“名字-工作”
SELECT CONCAT(ename,'-',job) FROM emp
#查询当前时间，查询当前日期
SELECT CURRENT_TIME(),CURRENT_DATE() FROM DUAL
#查询当前日期时间
SELECT NOW()
SELECT SYSDATE()
#格式化日期
SELECT hiredate,DATE_FORMAT(hiredate,'%Y/%m/%d') FROM emp
SELECT NOW(),CONCAT(DATE_FORMAT(NOW(),'%Y/%m/%d'),' ',TIME_FORMAT(NOW(),'%H:%i:%s')) 
SELECT hiredate,YEAR(hiredate),MONTH(hiredate),DAY(hiredate) FROM emp
#常用加密算法
SELECT PASSWORD('1111'),MD5('1111'),SHA('1111')
#模拟登录
CREATE TABLE t_user(
    id INT PRIMARY KEY auto_increment,
    username VARCHAR(20),
    PASSWORD VARCHAR(100)
);
INSERT INTO t_user VALUES(NULL,'chai',MD5('123456'));
SELECT * FROM t_user WHERE username='chai' AND PASSWORD='123456'#登录失败
SELECT * FROM t_user WHERE username='chai' AND PASSWORD=MD5('123456')#登陆成功
#mysql修改密码
USE mysql;
UPDATE USER SET authentication_string=PASSWORD('1111') WHERE USER='root' AND HOST='localhost';
FLUSH PRIVILEGES;