MYSQL数据库中的各种复杂查询操作

DQL语言的学习

1. 基础查询

   • 语法: select 查询列表 from 表名;
     • 查询列表可以是: 表中的字段、常量值、表达式、函数
     • 查询的结构是一个虚拟的表格
   • 查询表中的单个字段

       select 字段名 from 表名;
   

   • 查询表中的多个字段

       select 字段1,字段2 from 表名;
   

   • 查询表中的所有字段

       select * from 表名;
   

   • 查询常量值

       select 100;
       select 'john';
   

   • 查询表达式

   select 100%98;
   

   • 查询函数

   select version();
   

   • 起别名 as关键字可以省略

   #方式一
   select 100%98 as 结果;
   select 字段名 as 别名 from 表名;
   
   #方式二
   select 字段名  别名 from 表名;
   

   • 去重复

   select distinct 需要去重复的字段名 from 表名;
   

   • +号的作用：加法运算
     • 如果两个操作数都为数值型,则做加法运算
     • 如果其中一方为字符型,那么试图将字符型数值转换成数值型
       • 转换成功,则继续做加法运算
       • 如果转换失败,则将字符型数值转换为0
     • 如果一方为null,则结果肯定为null

   select 字段1+字段2 from 表名;
   

   • 连接字段值

   select concat(字段1,字段2) from 表名;
   

   • 判断是否为null

   select IFNULL(字段,如果为null的值) from 表名;
   

2. 条件查询

   • 语法: select 查询列表 from 表名 where 筛选条件
   • 运行顺序:
     1. 查询表名
     2. 筛选条件
     3. 查询列表
   • 分类:

     1. 按条件表达式筛选

        • 条件运算符: >(大于) <(小于) =(等于) !=(不等于) <>(不等于) >=(大于等于) <=(小于等于)
          • 筛选出员工表当中工资> 12000的员工信息

          select * from 员工表表名 where 工资字段 > 12000;
          

          • 筛选出员工表中员工名字不等于test的数据

          #方式一
          select * from 员工表 where 员工姓名 != 'test';
          
          #方式二
          select * from 员工表 where 员工姓名 <> 'test';
          

     2. 按逻辑表达式筛选

        • 逻辑运算符: 用于连接条件表达式
        • and(并且): 两个条件都为true,结果为true,反之为false

          #查询员工的工资大于1000并且小于5000的所有员工
          select * from 员工表  where 工资> 1000 and 工资<5000;
          

        • or(或者): 两个条件只要有一个为true,结果为true,反之为false

          #查询部门编号不是在90到110之间,或者工资高于5000的员工信息
          #方式一
          select * from 员工表 where 部门编号<=90 or 部门编号=>110 or工资> 5000;
          #方式二
          select * from 员工表 where not (部门编号=>90 or 部门编号<=110) or 工资 > 5000;
          

        • not(非): 如果连接的条件本身为false,结果为true,反之为false

     3. 模糊查询

        • like:一般和通配符一起
          • %代表任意多个字符,包含0个字符
          • _代表单个字符
          • \转义字符

        #查询员工表中姓名包含a的员工信息
        select * from 员工表 where 姓名 like '%a%';
        #查询员工表中姓名第二个字符为a的员工信息
        select * from 员工表 where 姓名 like '_a%';
        #查询员工表中姓名第二字符为_的员工信息
        #方式一
        select * from 员工表 where 姓名 like '_\_%';
        #方式二,使用ESCAPE指定某个符号位转义字符
        select * from 员工表 where 姓名 like '_$_%' ESCAPE '$';
        

        • between and:在…之间

        #查询员工表的编号在100到120之间的员工信息
        select * from 员工表 where 编号 between 100 and 120;
        #等价于
        select * from 员工表 where 编号>= 100 and 编号<=120;
        

        • in:判断某个字段的值是否属于in列表中的某一项,要求在in中的列表必须为同一类型,并且in当中不支持通配符

        #查询员工表当中姓名为test1,test2,test3的行
        select * from 员工表 where 姓名 in ('test1','test2','test3');
        

        • is null:是否为null

        #查询员工表当中没有奖金的员工
        select * from 员工表 where 奖金 is null;
        #查询员工表当中有奖金的员工
        select * from 员工表 where 奖金 is not null;
        

        • 安全等于<=> : 判断是否等于

        #查询工资为1200的员工信息
        select * from 员工表 where 工资 <=> 1200;
        

3. 排序

   1. 语法
      • select 查询列表 from 表 where 筛选条件 order by 排序列表 [asc/desc]
      • ASC 升序排序
      • DESC 降序
      • 如果不写那么默认为升序
      • 多个字段进行排序

        select 查询列表 from 表 where 筛选条件 order by 字段名 asc,字段名 desc
        

4. 多表查询

   1. 普通多表查询

      #没有筛选条件,会产生笛卡尔乘积
      select a.*,b* from a,b 
      #有筛选条件,假设以ID
      select a.*,b* from a,b  where a.id = b.id
      

   2. 内连接多表查询
      1. 等值查询(sql192标准)

         #查询a表中对应b表中的id
         select a.*,b* from a,b  where a.id = b.id
         #查询员工名和对应的部门名
         select 员工名,部门名 from 员工表 a,部门表 b  where a.部门ID = b.部门ID
         

      2. 非等值连接: 在等值连接的基础上替换等号
      3. 自连接: 与等值查询一致
   3. 外连接多表查询
      1. 左外连接(sql99语法): 以左边为基准连接右边,即左边是主表,右边为从表

         #查询员工名和对应的部门名
         select 员工名,部门名 from  员工表 a left join 部门表 b on a.部门ID = b.部门ID
         

      2. 右外连接: 将left join 换为right join: 以右边边为基准连接左边,即右边是主表,左边为从表

         #查询员工名和对应的部门名
         select 员工名,部门名 from  员工表 a right join 部门表 b on a.部门ID = b.部门ID
         

      3. 全外连接: 等于内连接的结果,将left join 换为FULL JOIN
      4. 内连接: 将left join 换为inner join

         #查询员工名和对应的部门名
         select 员工名,部门名 from  员工表 a inner join 部门表 b on a.部门ID = b.部门ID
         

   4. 交叉连接:cross join

5. 子查询

   1. 出现在其他语句内部的select 语句
   2. 可以出现在select标量子查询、from表子查询、where或having标量子查询 列子查询 列子查询、exists后面表子查询
   3. 按结果集的行列书不同:
      • 标量子查询(结果只有一行一列)
      • 列子查询(结果集)
      • 行子查询(结果集有一个多列)
      • 表子查询(结果集一般为多行)
   4. 查询示例

      select * from 表1 where 表1.某个字段 = (select 字段 from 表2 where 表2字段 =  '')
      

6. 分页查询

   1. 分页查询示例

      查询前5条员工信息
      select * from 表 limit 0,5
      

7. 联合查询

   1. union : 将多条查询语句的结果合并成一个结果
   2. 语句: 查询语句1 union 查询语句2
   3. 要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
   4. 联合查询列数必须一致
   5. 联合查询的顺序最好一致
   6. 联合查询当中会自动去重
   7. 联合查询当中如果不想去重 那么使用union all