sql刷题错误总结

太过爱你忘了你带给我的痛 2024-03-26 15:06 49阅读 0赞

![8ef9e95b826642a1b207ccc18791f7bb.png][]

select
      *
    from
      employees
    where
      hire_date = (
        select
          max(hire_date)
        from
          employees
      )

> **总结：嵌套查询**

![2d5054589e28438b9848a84f65ecb560.png][]

select
      *
    from
      employees
    where
      hire_date = (
        select
          distinct hire_date
        from
          employees
        order by
          hire_date desc
        limit
          2, 1
      )

> **总结：嵌套查询以及limit使用 limit n,m 从n索引开始的m条数据**

![aa6ce6a9666e4837b0e06b97a99fa562.png][]

select
      salaries.*,dept_manager.dept_no
    from
      salaries
      left JOIN dept_manager on dept_manager.emp_no = salaries.emp_no where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
    order by
      salaries.emp_no asc

> **总结：先查询 后排序**

![2185b14114304747bd67a358fd53520a.png][]

select
      employees.last_name,
      employees.first_name,
      dept_emp.dept_no
    from
      employees
      left join dept_emp on employees.emp_no = dept_emp.emp_no
    where
      dept_no ! = ''

>  **总结：左连接以及不为空**

![d64c7906c4bd48d3a2546b97080efa21.png][]

select distinct salary 
    from salaries
    order by salary desc

> **总结：去重查询**

![bc8d3987870c4db28b70218b1a2412e0.png][]

select employees.emp_no
    from employees
    left join dept_manager
    on employees.emp_no = dept_manager.emp_no
    WHERE dept_manager.dept_no is null

> **总结：不为空的另一种表达**

![ed6d2adc12934dac89b61b83044dd2cb.png][]

SELECT e.emp_no,d.emp_no manager
    FROM dept_emp AS e,dept_manager AS d
    WHERE e.dept_no = d.dept_no
    AND e.emp_no <> d.emp_no

>  **总结：不等于可以用<>或者!=表示 别名最好取de，dm 不相等有三种表示方式：<>、!=、IS NOT**

![d3d612c366654476926de6b21f930458.png][]

方法一：
    select *
    from employees
    where emp_no % 2 = 1
    and last_name != 'Mary'
    order by hire_date desc
    
    
    方法二：
    SELECT *
    FROM employees
    WHERE emp_no & 
    1
    AND last_name<>
    'Mary'
    ORDER BY hire_date DESC;

> **总结：补充：emp\_no % 2=1也可以改成MOD(emp\_no, 2)=1，但是某些sql版本可能不支持后者(比如题库就不支持)**
> 
> **注意：last\_name是varchar类型，所以对它的判断需要加上单引号**
> 
> **补充：sql中/表示标准除法，如101/2得到50.5，而DIV表示整数除法，如101 DIV 2得到50**
> 
> **补充：奇偶数查询：参考资料：[20170922--mysql--奇偶数查询\_ccStroy的博客-CSDN博客\_mysql查询字段值尾数是偶数的][20170922--mysql--_ccStroy_-CSDN_mysql]**
> 
> **查询奇数的一般方法：如上(最好是位运算&)**
> 
> **查询偶数的一般方法：emp\_no=(emp\_no>>1**
> 
> **但是，以上的一般方法，针对的是字段全是数字的情况，如果对于身份证这种中间隐藏了一部分的，积极无法使用**
> 
> **所以更好的方法是使用正则化表达式**

![2794a6de0112416fbecc7019c92f5605.jpeg][]

[8ef9e95b826642a1b207ccc18791f7bb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/5545819af76a4a51bf7c55825e0eb1b9.png
[2d5054589e28438b9848a84f65ecb560.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/88ccfee6c0774208bcd78c00b6387fcf.png
[aa6ce6a9666e4837b0e06b97a99fa562.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/37f0a4925ce34dc6965f1b56b6130eed.png
[2185b14114304747bd67a358fd53520a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/2c0e3ba3dc4a4dc0b46ff7f8fbba0f84.png
[d64c7906c4bd48d3a2546b97080efa21.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/637d0da1a7b6433ca9b80d2b7f08b431.png
[bc8d3987870c4db28b70218b1a2412e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1218f266abd94f28b8325bf74d326931.png
[ed6d2adc12934dac89b61b83044dd2cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/803456f84e3d46bb9c6ed28c46fc15ce.png
[d3d612c366654476926de6b21f930458.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/bfe52162d2ef416e8997aa6f67f1af29.png
[20170922--mysql--_ccStroy_-CSDN_mysql]: https://blog.csdn.net/ccStroy/article/details/78061861
[2794a6de0112416fbecc7019c92f5605.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/458f3ade8f044c12a5565250f51fff3d.png