MySQL学习笔记汇总（一）——简单查询、条件查询、数据排序。

红太狼 2023-10-06 14:24 17阅读 0赞

## 一、简单查询 ##

*  语法格式：  
    **select 字段名1,字段名2,字段名3,… from 表名;**  
    注意：1、任何一条sql语句以“;”结尾。2、sql语句不区分大小写。

### 案例： ###

*  **查询员工姓名**  
    select ename from emp;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70]
 *  **查询员工的编号和姓名**  
    select empno, ename from emp;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 1]
 *  **列出员工的编号，姓名和年薪**  
    select empno,ename,sal\*12 as ‘年薪’ from emp;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 2]

## 二、条件查询 ##

*  **语法格式：**  
    **select 字段,字段… from 表名 where 条件;**
 *  **执行顺序：先from，然后where，最后select**
 *  **支持的运算符：**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 3]
 *  **in和or效率问题：**  
    **in比or快**

> a)OR，就是从 a1 匹配，匹配失败，去匹配 a2，直到匹配成功或者一个都匹配不上，时间复杂度是 O(N)  
> b)IN，先将 a1, a2, …, an 变成二叉搜索树，用过二叉树查找，时间复杂度为O(LogN)  
> c)IN 的效率要高于 OR，参数集合范围越大，IN 的性能不会太大下降，而 OR 会下降非常厉害。

*  **模糊查询like**

> %代表任意多个字符，\_代表任意1个字符。

### 案例： ###

*  **查询薪水为 5000 的员工**  
    select ename,sal from emp where sal=5000;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 4]
 *  **查询 job 为 MANAGER 的员工**  
    select ename,job from emp where job='MANAGER ';  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 5]
 *  **找出工资高于3000的员工**  
    select ename,sal from emp where sal>3000;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 6]
 *  **查询薪水为 1600 到 3000 的员工**  
    select ename,sal from emp where sal between 1600 and 3000;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 7]
 *  **查询津贴为空的员工**  
    select ename,comm from emp where comm is null;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 8]
 *  **工作岗位为 MANAGER,薪水大于 2500 的员工**  
    select ename,job,sal from emp where job=‘MANAGER’ and sal>2500;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 9]
 *  **查询出 job 为 manager 或者 job 为 salesman 的员工**  
    select \* from emp where job='manager ’ or job='salesman ';  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 10]
 *  **查询薪水大于 1800，并且部门代码为 20 或 30 的员工**  
    select \* from emp where sal>1800 and (deptno=20 or deptno=30);  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 11]
 *  **查询出薪水包含 1600 和薪水包含 3000 的员工**  
    select \* from emp where sal in(1600,3000);  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 12]
 *  **查询出薪水不包含 1600 和薪水不包含 3000 的员工**  
    select \* from emp where sal not in(1600,3000);  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 13]
 *  **查询姓名以 M 开头所有的员工**  
    select \* from emp where ename like ‘M%’;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 14]
 *  **查询姓名中第二个字符为 A 的所有员工**  
    select \* from emp where ename like ‘\_A%’;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 15]

## 三、数据排序 ##

*  **排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号 间隔，order by 默认采用升序，如果存在 where 子句那么 order by 必须放到 where 语句的后 面**
 *  **ASC：升序 DESC： 降序**
 *  **执行顺序：**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 16]

### 案 例： ###

*  **按照薪水由小到大排序**  
    select \* from emp order by sal;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 17]
 *  **取得 job 为 MANAGER 的员工，按照薪水由小到大排序**  
    select \* from emp where job='MANAGER ’ order by sal;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 18]
 *  **按照工资的降序排列，当工资相同的时候再按照名字的升序排列。**  
    select \* from emp order by sal desc ,ename asc;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 19]

**今天的分享就到这里啦！！~感谢大家的观看，希望对大家有帮助的话麻烦给个丝滑三连击。（点赞＋转发+关注）  
一起加油，一起努力，一起秃见成效！！**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020080621272294.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200806212805141.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200806213104186.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2020080621344695.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200806214334716.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200806214652676.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200806214853607.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200806215115788.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200806215336510.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200806215605491.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200806215855287.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200806220140183.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200806220329499.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20200806220509861.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/20200806220919903.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/2020080622112368.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20200806222443224.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/20200806221544606.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 18]: https://img-blog.csdnimg.cn/20200806221748777.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 19]: https://img-blog.csdnimg.cn/20200806222038486.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70