MySQL（3）

十、条件查询(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

              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')

              #查询张姓员工信息 通配符：_表示一个任意字符，%表示n个任意字符

              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 or 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(empno) from emp

              select count(*) from emp

              select count(1) from emp

              select count(comm) from emp #忽略null

              #查询最高的工资

              select max(sal) from emp

              #查询最低的工资

              select min(sal) from emp

              #查询最高工资

              select max(sal) from emp

              #查询平均工资

              select avg(sal) from emp

              #查询工资总合

              select sum(sal) from emp

              #查询工资总合含奖金

              select sum(sal+comm) from emp

              #查询入职最早和最晚的员工信息

              select min(hiredate), max(hiredate) from emp

              #查询每个部门的平均工资 --分组时select后只能出现分组字段(deptno)和分组函数(avg)

              select deptno,avg(sal) from emp group by deptno

              select ename,deptno,avg(sal) from emp group by deptno -- 非法，因为ename无意义

              #查询每个部门的工资共和、部门人数、最高工资、最低工资、平均工资，并按工资综合排序和部门号排序

              select

                     deptno,sum(sal) sum_sal,count(1),max(sal),min(sal),avg(sal)

              from

                     emp group by deptno order by sum_sal desc,deptno asc

              #查询平均工资>2000的部门

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

              #查询所有部门中领导们的平均工资

              select deptno,avg(sal) avg_sal from emp group by deptno having job='MANAGER' #非法，group by后结果中没有job字段

              select deptno,avg(sal) 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

              */

       3、单行函数

              #查询大于x的最大整数、小于x的最大整合、四舍五入

              select avg(sal),ceil(avg(sal)),floor(avg(sal)),round(avg(sal),2) from emp

              select avg(sal),ceil(avg(sal)),floor(avg(sal)),round(avg(sal),2) from emp

              #查询员工的名字有几个字

              select length(ename)/3 from emp

              #查询员工的'姓名-工作'

              select ename,'-',job,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 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;

              #mysqladmin -u root password 1111;

              #ALTER USER 'root'@'localhost' IDENTIFIED BY '1111';

              UPDATE user SET authentication_string=PASSWORD('1111') WHERE User='root' AND Host='localhost';

              FLUSH PRIVILEGES;

十二、关联查询

       关联查询：有关联关系的多张表一起查询，比如：员工表和部门部可以通过'deptno'关联

       1、内连接

              关键字：inner join | join

              查询结果：A∩B，展示两张表符合关联条件的记录

              案例：

                     #查询员工的姓名、部门编号、部门名称

                      /**

                            1052 - Column 'deptno' in field list is ambiguous

                            原因：'deptno'两张表都有，mysql不知道从那张表取了

                      */

                      select ename,deptno,dname from emp inner join dept #报错

                      /**

                            结果：语法正确，但是结果是‘笛卡尔积’，即A表记录数 * B表的记录数

                            结论：关联查询必须有‘关联条件’

                      */

                      select e.ename,e.deptno,d.dname from emp e INSERT JOIN dept d

            select e.ename,e.deptno,d.dname from emp e INNER JOIN dept d ON e.deptno=d.deptno #推荐

                      select e.ename,e.deptno,d.dname from emp e INNER JOIN dept d WHERE e.deptno=d.deptno #不推荐，因为left和right连接会影响结果

                      #查询部门编号为1的工资》3000员工的姓名、部门编号、部门名称、薪资

                      select e.ename,e.sal,d.deptno,d.dname

                      from emp e INNER JOIN dept d ON e.deptno=d.deptno

                      where e.deptno=1 and e.sal>3000

                      select e.ename,e.sal,d.deptno,d.dname

                      from emp e JOIN dept d ON e.deptno=d.deptno

                      where e.deptno=1 and e.sal>3000

                      select e.ename,e.sal,d.deptno,d.dname

                      from emp e JOIN dept d ON e.deptno=d.deptno and e.deptno=1 and e.sal>3000

       2、左连接

              关键字：left join

              查询结果：A，以左表为基准，左表全部展示右表展示符合关联条件的记录

              案例：

                     #查询所有员工的姓名、部门、部门编号、薪资

                     select e.ename ,e.sal,d.dname , d . deptno from emp e left join dept d on e.deptno=d.deptno

                     #查询部门编号为1的所有员工的姓名、部门、部门编号、薪资

                     /**

                     纯粹的左连接,结果:左边全部展示，右边展示符合条件的记录

                     */

                     select e.ename,e .sal,d.dname,d. deptno from emp e left join dept d on e.deptno=d.deptno and d.deptno=1

                     /**

                     左连接已经结束,但是where条件又过滤了一把

                     */

                     select e.ename,e.sal, d. dname,d. deptno from emp e left join dept d on e.deptno=d.deptno where d.deptno=1

       3、右连接

              关键字：right join

              查询结果：B，以右表为基准，右表全部展示，左表展示符合关联条件的记录

              案例：

               #查询所有部门，包括没有对应员工的部门，员工的姓名、薪资、部门编号、部门名称

          select e.ename,e.sal,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno

        #查询所有员工，包括没有指定部门的员工，他们的姓名、薪资、部门编号、部门名称

                     select * from dept d right join emp e on d.deptno=e.deptno

       4、等值连接

              特点：等值连接是内连接的一种特殊实现方式，使用where条件关联数据

              查询结果：A∩B，等同于内连接

              案例：

                     #查询员工的所有信息及部门名称

                     select * from emp e JOIN dept d ON e.deptno=d.deptno

                     select * from emp e, dept d WHERE e.deptno=d.deptno

                     #查询武汉分公司的员工及部门信息

                     select * from emp e, dept d WHERE e.deptno=d.deptno and d.loc='武汉'

                     select * from emp e join dept d ON e.deptno=d.deptno and d.loc='武汉'

                     #查询员工的工资等级

                     select e.*,sg.grade from emp e, salgrade sg WHERE e.sal>=losal and e.sal<=hisal

                     #查询领导的工资等级

                     select e.*,sg.grade

                     from emp e, salgrade sg

                     WHERE e.sal>=losal and e.sal<=hisal and e.job='MANAGER'

                     #查询每个员工的工号、姓名、直接领导姓名

            select e1.empno,e1.ename,e2.ename from emp e1, emp e2 where e1.mgr=e2.empno