• MySQL-SQL基础-查询1


    #子查询-某些情况下,当进行查询的时候,需要的条件是另外一个select语句的结果,这个时候就要用到子查询。用于子查询的关键字主要包括:
    innot in=!=existsnot exists等等。
    #从emp表中查询出所有部门在dept表中的所有记录
    
    mysql> select * from dept;
    +--------+----------+
    | deptno | deptname |
    +--------+----------+
    |      1 | tech     |
    |      2 | sale     |
    |      3 | hr       |
    |      4 | sl       |
    +--------+----------+
    4 rows in set (0.01 sec)
    
    mysql> select * from emp;
    +-------+------------+------------+---------+--------+------+
    | ename | birth      | hirdate    | sal     | deptno | age1 |
    +-------+------------+------------+---------+--------+------+
    | zzx1  | 2000-01-01 | 2000-01-01 | 2000.00 |      1 |   21 |
    | zzx1  | 2002-03-09 | 2009-04-03 | 2001.00 |      3 |   22 |
    | ttx2  | 2023-04-10 | 2010-03-04 | 4000.00 |      4 |   23 |
    | ssss  | 2019-01-01 | 2018-01-01 | 5000.00 |      2 |   24 |
    +-------+------------+------------+---------+--------+------+
    4 rows in set (0.00 sec)
    
    mysql> select * from emp where deptno in(select deptno from dept);
    +-------+------------+------------+---------+--------+------+
    | ename | birth      | hirdate    | sal     | deptno | age1 |
    +-------+------------+------------+---------+--------+------+
    | zzx1  | 2000-01-01 | 2000-01-01 | 2000.00 |      1 |   21 |
    | ssss  | 2019-01-01 | 2018-01-01 | 5000.00 |      2 |   24 |
    | zzx1  | 2002-03-09 | 2009-04-03 | 2001.00 |      3 |   22 |
    | ttx2  | 2023-04-10 | 2010-03-04 | 4000.00 |      4 |   23 |
    +-------+------------+------------+---------+--------+------+
    4 rows in set (0.00 sec)
    
    #如果子查询记录数唯一,还可以用=代替in
    
    mysql> select * from emp where deptno=(select deptno from dept limit 1);
    
    +-------+------------+------------+---------+--------+------+
    
    | ename | birth | hirdate | sal | deptno | age1 |
    
    +-------+------------+------------+---------+--------+------+
    
    | zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 |
    
    +-------+------------+------------+---------+--------+------+
    
    1 row in set (0.00 sec)
    
     
    
    # 某些情况下,子查询可转化为表连接
    
    
    mysql> select * from emp where deptno in(select deptno from dept);
    
    +-------+------------+------------+---------+--------+------+
    
    | ename | birth | hirdate | sal | deptno | age1 |
    
    +-------+------------+------------+---------+--------+------+
    
    | zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 |
    
    | ssss | 2019-01-01 | 2018-01-01 | 5000.00 | 2 | 24 |
    
    | zzx1 | 2002-03-09 | 2009-04-03 | 2001.00 | 3 | 22 |
    
    | ttx2 | 2023-04-10 | 2010-03-04 | 4000.00 | 4 | 23 |
    
    +-------+------------+------------+---------+--------+------+
    
    4 rows in set (0.00 sec)
    
    
    #转换为表连接后
    mysql> select emp.* from emp,dept where emp.deptno=dept.deptno;
    +-------+------------+------------+---------+--------+------+
    | ename | birth      | hirdate    | sal     | deptno | age1 |
    +-------+------------+------------+---------+--------+------+
    | zzx1  | 2000-01-01 | 2000-01-01 | 2000.00 |      1 |   21 |
    | ssss  | 2019-01-01 | 2018-01-01 | 5000.00 |      2 |   24 |
    | zzx1  | 2002-03-09 | 2009-04-03 | 2001.00 |      3 |   22 |
    | ttx2  | 2023-04-10 | 2010-03-04 | 4000.00 |      4 |   23 |
    +-------+------------+------------+---------+--------+------+
    4 rows in set (0.00 sec)
    
    #union和union all的主要区别是union all是把结果集直接合并在一起
    union是将union all后的结果在进行一次distinct,去除重复记录后的结果。
    mysql> select deptno from emp union all select deptno from dept;
    +--------+
    | deptno |
    +--------+
    |      1 |
    |      3 |
    |      4 |
    |      2 |
    |      1 |
    |      2 |
    |      3 |
    |      4 |
    +--------+
    8 rows in set (0.00 sec)
    
    mysql> select deptno from emp union  select deptno from dept;
    +--------+
    | deptno |
    +--------+
    |      1 |
    |      3 |
    |      4 |
    |      2 |
    +--------+
    4 rows in set (0.00 sec)
     
  • 相关阅读:
    交易之道
    走出幻觉,走向成熟(阅读心得一)
    爱由心生
    正则资料整理(转载)
    需要学习的.NET技术(转载)
    抽象类与接口的区别
    心存感激
    MYSQL 远程访问被限制
    PHP积累
    Git积累
  • 原文地址:https://www.cnblogs.com/drizzle-xu/p/10254091.html
Copyright © 2020-2023  润新知