MySQL数据表数据操作

MySQL数据表数据操作

0 引言

  MySQL中对数据表中数据记录的操作（CRUD），主要包括插入(Insert or Create)、更新(Update)、删除(Delete)、查询(Read OR Select)四类操作。此四类SQL操作语句，在数据库中称之为DML（Data Manipulation Language）语句。

1 插入记录

  根据在数据表插入记录的情形，可分为：插入完整记录、插入部分记录、插入多条记录、插入查询记录、批量导入记录等。

1.1 插入完整字段记录

  所谓插入完整记录，即是一次性插入数据表中全部字段的记录数据。

  （1）按字段顺序插入字段值

    语法：insert  into  table_name(field1,field2,field3,......,fieldn) values(value1,value2,value3,......,valuen)

    此语句中，fieldn与valuen是对应的，其语句中字段指向顺序很重要，可以不与数据表中字段排列顺序相同。

  （2）直接插入字段值

    语法：insert  into  table_name values(value1,value2,value3,......,valuen)

    此语句中，字段值排列顺序，须与数据表中字段排列顺序相同。

1.2 插入部分字段记录

  插入部分字段记录，基本语法同插入完整字段记录一致，仅部分字段无需插入字段值。有以下要点：

  （1）实现插入部分字段记录时，所插入数值的个数必须与所选插入字段个数一致，且一一对应。

  （2）自增字段和默认字段可不需要插入数值。

  （3）非空字段，须在插入新纪录时，必须携带字段值。

  （4）自增字段、主键字段，须确保唯一性，但若强制插入须与相应约束不得冲突，否则会插入失败。

  语法：insert  into  table_name(oldfield1,oldfield3,oldfield6,......,fieldn) values(value1,value3,value6,......,valuen)

1.3 插入多条记录

  一次性插入多条数据记录，可区分为一次性插入多条完整记录和一次性插入多条部分记录。

  （1）插入多条完整数据记录

    语法：

    insert into table_name(field1,field2,field3,...,fieldn)

       values(value11,value21,value31,...,valueN1),

        (value12,value22,value32,...,valueN2),

          ......

        (value1M,value2M,value31,...,valueNM)

  （2）插入多条部分数据记录

    语法：

    insert into table_name(field1,field2,field3,...,fieldn)

       values(value11,value31,...,valueN1),

        (value12,value32,...,valueN2),

          ......

        (value1M,value31,...,valueNM)

1.4 插入查询记录

  此种方式，是将查询的结果插入到数据表中。

  语法：

  insert  into  table_name(field1,field1,field3,......,fieldn) 

  select （o_field1,o_field2,o_field3,...,o_fieldn）from table_name2 where ......

1.5 批量导入记录

  批量导入数据，通常是指将大量数据一次性导入数据库表中，以提高数据处理的效率。但批量导入容易出现错误。可借鉴的方法如下：
  第一，确认目标数据表的表结构是否匹配待导入数据格式，尤其是数据类型定义是否正确。

  第二，拷贝一份目标数据表结构为新表，测试目标文件数据导入是否成功。

  第三，若导入成功，则将数据采用相同步骤导入原目标数据表中，或者从拷贝表中导入到目标数据表中。

     若导入不成功，则说明待导入文件数据与数据表结构内容不匹配，应从重新导入。

     若部分导入成功，则说明待导入文件中，存在特殊数值内容，无法被数据表完全识别，数据类型不完全兼容。此种原因，有可能是待导入文件编码格式不正确，或者内容的数据类型不被兼容，或者是本身数据表结构定义存在问题。此种问题应当仔细谨慎对待，确保数据正确导入。

  （1）使用 Load  Data Infile 语句

    Load data infile '/path/file.csv'

    into table table_name 

    field terminated by  ',' 

    enclosed by '"'

    lines terminated by '\n'

    ignore 1 lines

    --single-transaction;

  （2）使用mysqlimport工具

    mysqlimport  -u username -p database_name data.txt

2 更新记录

  更新记录，是指操作更新表中已经存在数据记录中的值。从更新操作来看，可区分为更新特定记录和更新全部记录类别。

  关键在于条件的控制，即where子句的过滤设置。

2.1 更新特定记录

  基本语法：

    update table_name set field1=value1,field2=value2,field3=value3,...  where  condition;

2.2 更新全部记录

  基本语法：其中where关键字非必须。

    update table_name set field1=value1,field2=value2,field3=value3,...  where  condition;

3 删除记录

  删除记录，即删除数据表中已经存在的特定数据记录的操作。删除操作，可区分为删除特定数据记录和删除所有数据记录。

3.1 删除特定记录

  基本语法：

    delete  from  table_name where condition;

3.2 删除全部记录

  基本语法：

    delete  from  table_name where condition;

4 查询记录

  查询记录，是指从数据库中获取所要求的数据记录。此操作使用频率极高、最重要的数据操作。

4.1 单表数据记录查询

4.1.1 简单查询  

  （1）快速查询

    通常用于快速查询数据表记录，尤其是全部字段的查询。

    1）通配符查询【危险的查询】

      语法：select  *  from  tab_name;

    2）全字段查询【推荐的查询】

      语法：select  field1,field2,field3,......,fieldn from tab_name;

    3）指定查询【常用的查询】

      语法：select  field1,field3,......,fieldn from tab_name;

  （2）去重查询——distinct[不同的]

    数据表中存在重复数据，通过关键字“distinct”去除重复。

    MySQL内部去重的原理，应该是边查边去重复，不同于先查询后去重复【此处待验证确认】。

    语法：select distinct  field1,field2,......,fieldn from  tab_name；

  （3）四则运算查询

    即是在查询语句中，加入数学中的四则运算（+、-、*、/）。

    MySQL支持的关系运算符有：+，-，*，/【Div】，%【Mod】。

    示例1：select  id,name,salary*12 from employee;

    示例2：select  id,name,salary*12 as year_salary from employee;

  （4）查询显示

    在查询记录时，为了便于结果的浏览呈现，有时需对输出设置显示格式。

    示例：select id,concat(name,'雇员的年薪为：',salary*12) as year_salary from employee;

4.1.2 条件查询

  条件查询，较为重要，主要两个目的：一是，按需查询；二是，提高查询效率。合理设置条件，可提高查询速度，尤其是当数据表存在大量的记录时。

  基本语法：select field1,field1,field3,...,fieldn from tab_name where condition;

  （1）带运算符的条件查询

    所谓运算符即指关系运算符和逻辑运算符。

    MySQL支持的关系运算符有：>、<、=、!>(<>)、>=、<=

    MySQL支持的逻辑运算符有：AND(&&)【逻辑与】、OR(||)【逻辑或】、XOR【异或】、NOT(!)【逻辑非】

    1）单条件查询

      示例：select ename from employee where job='sale';

    2）多条件查询

      示例：select ename from employee where job='sale'&&salary>5000;

  （2）带 Between and 关键字的范围查询

    Between min_value and max_value：表示某个字段的取值范围，仅针对数值类型。

    语法：select field1,field2,...,fieldn from tab_name where field2 between min_value and max_value;

    1）符合范围的记录查询

      示例：select ename from employee where salary between 2000 and 5000;

    2）不符合范围的记录查询

      示例：select ename from employee where salary not between 2000 and5000;

  （3）带 Is Null 关键字的空值查询

 　　　　MySQL软件提供了关键字is null,用来实现判断字段的数值是否为空的条件查询。

　　　　语法：select  field1,field2,...,fieldn from tab_name where field2 is null;

    注：此语句，是将数据表tab_name中字段field2是否为空的记录查出来。

    1）空值记录查询

      示例：select  name from employee where salary is null;

      通过双非逻辑运算实现查询【不赞成】：

      示例：select  name from employee where not salary is not null;

    2）非空记录查询

      示例：select  name from employee where salary is not null;

      通过逻辑非运算进行非空记录查询：【此种方式效率可能偏低】

      示例：select  name from employee where not salary is null;

  （4）带 IN 关键字的集合查询

    关键字IN，用来实现判断字段值是否在指定集合中的条件查询。

    语法：select field1,field2,field3,......,fieldn from tab_name where field2 in (value1,value2,value3,...,valueN);

    1）在集合中记录查询

      示例：select  name  from employee where career in ('sales','IT','manager');

    2）不在集合中记录查询

      示例：select  name  from employee where career not in ('sales','IT','manager');

      通过逻辑非运算进行查询：【此种方式效率有可能偏低】

      示例：select  name  from employee where not career in ('sales','IT','manager');

    注意：1）在具体使用关键字In时，查询的集合中若存在Null，则不会影响查询结果；

        示例：select  name  from employee where career in ('sales','IT','manager',Null);

       2）但如果使用关键字Not In时，查询的集合中若存在Null，则不会查询出任何结果。

        示例：select  name  from employee where career not  in ('sales','IT','manager',Null);

        原因是：not Null 表示所有记录，存在矛盾。

  （5）带 Like 关键字的模糊查询

    即使用通配符实现模糊查询。

    like关键字支持的通配符有：

      1）“_”通配符，该通配符值能匹配单个字符；

      2）“%”通配符，该通配符可以匹配任意长度的字符串，可以是0个字符，也可以是1个字符，还可以是多个字符。

    语法：select field1,field2,...,fieldn from tab_name  where field2 like value;

    1）带“_”通配符的查询

      示例：select name from employee where name like '_A%';     #此表示第二个字符为‘A’的结果匹配

    2）带“%”通配符的查询

      示例1：select name from employee where name like 'A%';     #此表示以‘A’开头的结果匹配

      示例2：select name from employee where name like '%A%';  #此表示包含字符‘A’的结果匹配

      示例3：select name from employee where name like '%%';    #此表示包括全部记录的结果匹配

    3）逻辑非的模糊查询

      示例1：select name from employee where name  not like 'A%';   #此表示不是以‘A’开头的结果匹配

      示例2：select name from employee where not name  like 'A%';   #此表示不是以‘A’开头的结果匹配

      示例3：select name from employee where not name  like '_A%';  #此表示第二个字符不是‘A’的结果匹配

4.1.3 排序查询

  即对查询结果做适当排序，然后输出结果。

  MySQL提供关键字 Order by 设置查询结果的顺序。关键字参数ASC表示按升序排序，参数DESC表示按降序排序。

  Order by 后面可添加多个字段进行排序，即第一顺序，第二顺序，第三顺序......

  语法：select field1,field2,...,fieldn from tab_name where condition order by fieldm1 [ASC|DESC],fieldm2 [ASC|DESC]

  （1）单字段排序

    select  *  from employee  order  by salary ASC;   

    select  *  from employee  order  by  salary;         #MySQL默认即为升序排序

    select  *  from employee  order  by salary DESC;  

    注：若字段值存在空值（Null），则该值为最小值，故升序排序将显示在最前面，而降序排序将显示在最后面。

  （2）多字段排序

    select * from employee order by salary ASC, hiredate DESC;   #即第一字段为升序，第二字段为降序

4.1.4 设限查询

  条件查询的记录太多，则可以通过关键字Limit来限制查询结果的显示数量。

  语法：select field1,field2,...,fieldn  from tab_name where condition limit  offset_start,row_count;

   注：通过关键字limit来限制查询结果的数量，offset_start表示数据记录的起始偏移量，row_count表示显示的记录数量。

  （1）不指定初始位置

    1）显示记录数<查询结果：全部显示

      示例：select * from employee where comm is null limit 11;

    2）显示记录数>查询结果：限制数量显示

      示例：select * from employee where comm is null limit 5;

  （2）指定初始位置

  Limit 关键字经常被用于分页系统中，对于第1页的数据记录，通常不需要指定初始位置即可实现；但对于第2页等其他页面，则须要指定初始位置（offset_start）,否则将无法实现分页功能。

  Limit 关键字还经常与order by关键字一起使用，即对查询结果进行排序，然后显示其中部分数据记录。

  示例1：select  * from employee where comm is null order by hiredate limit 0,5;

  示例2：select  * from employee where comm is null order by hiredate limit 5;   #limit关键字参数中offset_start的值为0。

4.1.5 统计分组查询

  多数查询，是需要进行统计分组进行汇总查询。

  （1）MySQL支持的统计函数

  　　1）count：该函数实现数据表中记录的计数统计；

  　　2）avg：该函数实现查询字段值的均值统计；

 　　 3）sum：该函数实现查询字段值的求和统计；

 　　 4）max：该函数实现查询字段值的最大值统计；

  　　5）min：该函数实现查询字段值的最小值统计。

　　　　语法：select  function(field) from tab_name where condition;

    1）统计记录条数——count()

      count(*)：此方式，可实现对按条件筛选出的表中记录进行计数，统计范畴包括null值和非null值。

      count(field)：此方式，可实现对按条件筛选出的表中记录指定字段进行计数，统计范畴不包括null值。

    2）统计计算均值——avg()

      avg(field)：此方式，可实现对按条件筛选出的表中记录指定字段的均值计算，在统计时忽略null值。

    3）统计计算求和——sum()

      sum(field)：此方式，可实现对按条件筛选出的表中记录指定字段的求和计算，在统计时忽略null值。

    4）统计计算最大值——max()

      max(field)：此方式，可实现对按条件筛选出的表中记录指定字段的最大值计算，在统计时忽略null值。

    5）统计计算最小值——min()

      min(field)：此方式，可实现对按条件筛选出的表中记录指定字段的最小值计算，在统计时忽略null值。

    关于统计函数注意事项：

    1）MySQL所支持的统计函数，如果表中没有任何记录，则count函数返回值为0，其他函数返回为null；

    2）若对非数值类型字段进行统计，则会返回意外的结果或者错误；

      例如：对字符串类型字段进行统计，则avg、sum均返回0，而max、min则按照字典顺序进行比较，返回不符合预期的结果。

  （2）MySQL支持的分组查询

    多数实际情况下，使用统计函数，都是针对表中所有记录或者特定条件下的记录进行分组统计计算。

    语法：select function() from table_name  where  condition  group by  field;

    1）实现简单分组查询

      示例1：select  *  from employee  group by  field;

      示例2：select  field  from employee  group by  field;

      示例3：select  otherfield,field  from employee  group by  field;

      注：此种查询往往是简单粗暴的，通常仅分组字段的结果有意义，其他非分组字段采用暴力取舍（随机选择），即代表性显示。

    2）实现统计功能分组查询

      如果需要将分组字段相关联的记录的其他字段信息一并显示，则需要通过函数Group_Concat来实现；

      语法：select  field,group_concat(otherfield)  from table_name  group by  field;

      示例：select age,group_concat(name)  from employee group by age;

    3）实现多字段分组查询

      MySQL中使用关键字Group By时，其子句除了可以是一个字段外，还可以是多个字段，即可以按多个字段进行分组。

      多字段分组，其实质是一种数学组合分组的方式，非常适合于多字段组合构成唯一组合的情形。

      语法：

      select group_concat(field2),function(field1) from table_name where condition group by field1,field2,...,fieldn；

      注意：上述分组，对按条件筛选出的记录，首先按field1分组，然后是field2，依次类推。

      示例：select age,gender,group_concat(name) as namelist from employee group by age,gender;

    4）实现Having子句限定分组查询

      MySQL中where关键字是用来实现限制数据记录，但不能用于实现对分组进行条件限制，即对未分组的记录进行抽取限制。

      MySQL提供了Having关键字，用来实现对分组进行条件限制，即对已分组后的结果再次限制。

      语法：select function(field) from table_name where condition group by field1,field2,...,fieldn  having contition;

      示例：

      select age,gender,count(name),group_concat(name) from employee where age>=30 group by age,gender

      having count(name)>=2;

 

4.2 多表数据记录查询

  数据库数据查询，在具体应用中，通常还需要显示多张数据表的数据，即实现多张数据表记录的连接查询。

  所谓连接查询，即是将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录。

  连接查询，分为内连接查询和外连接查询，即内查询和外查询。

  MySQL还提供一种子查询的方式，用以替换多表数据采用连接查询时效率偏低的问题。

4.2.1 关系数据操作

  对两张表及两张以上的表进行连接查询，其操作涉及到数据表或数据记录之间的关系运算，即并运算、笛卡尔积运算，以及连接操作。  

  连接（Join）,是指两张表不同内容数据组合的方式。

  （1）合并（Union）

    合并，是把相同字段和字段类型的数据合并在一起。通常称之为并运算，或并操作。

    并运算，通常会涉及到两张表相同字段内容的去重复。

  （2）笛卡尔积（Cartesian Product）

    笛卡尔积运算（Cartesian Product），即是两张表不同内容数据之间的组合；

    即在A表中不同内容数据与B表中不同内容数据的两两组合，形成两个表不同内容数据的集合乘积，称之为笛卡尔积。

  （3）连接（Join）

    所谓连接，即是在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件生成新的关系。

    其连接又分为内连接（Inner Join）、外连接（Outer Join）和交叉连接（Cross Join）。

4.2.2 内连接查询

  所谓内连接（Inner Join），即是在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。

  按照匹配的条件，内连接可以分为自然连接、等值连接和不等连接。

  MySQL默认的连接类型即是内连接。

  语法1：select field1,field2,...,fieldn from table_name1 inner join table_name2 [inner join table_name] on condition;

  语法1：select field1,field2,...,fieldn from table_name1 join table_name2 [join table_name] on condition;

  （1）自然连接（Natural Join）

    自然连接，在表关系的笛卡尔积中，首先根据表关系中相同名称与数据类型的字段自动进行记录等值匹配，合并等值匹配的列。

    实现自然连接，是通过Natural join或者Using子句来实现。

    自然连接是基于两个或多个表具有相同名称与数据类型的字段自动进行连接的方式，相等字段值的表记录被用来合并连接。

    不同表所自然匹配的字段，其字段值在所在表记录中存在重复，且与所在表其他字段值存在一对一、一对多、多对多的关系，再与其他表相匹配的字段进行合并连接时，两张表自然连接匹配合并的关系也自然形成一对一、一对多、多对多的关系。因此在不同表采用自然连接时，应当谨慎操作：其一，须确保连接合并符合业务事实的实际需要，才能确保结果的正确性，否则将出现不符合基本事实的连接；其二，尽可能选择唯一字段进行一对一的连接合并，确保合并结果的唯一性，若非十分之必要，不采用一对多和多对多的关系连接合并。故而在采用连接时，应当基于业务实际需要，检查不同表之间所连接字段的对应关系，确保连接合并符合所需。

    自然连接是等值连接的一种特例，自动对所有共有属性字段进行等值比较，即在不同表中必须都存在。

    1）使用Natural join

      此种方式，会自动匹配两个表中所有同名的字段进行连接，数据表进行左右拼接。

      当两个表存在多个同名的字段时，容易导致出现结果出现包含重复字段值的记录，出现结果往往是错误的。

      示例：select * from employee natural join department;

    2）使用Using

      此种方式，是通过using子句或在查询中明确指定字段名实现表连接，可更加精确地控制输出结果。

      即使指定了字段名实现表连接，其结果依然可能存在业务事实逻辑上的错误，应谨慎操作。

      尤其是当记录字段值存在重复，且与其他字段存在一对多或多对多关系时，所连接的结果可能存在业务事实逻辑上的错误。

      示例：select * from employee join department using(department_id);

    注：自然连接可以简化查询，但容易导致性能问题，尤其是当数据表非常大时，且连接结果通常存在业务事实逻辑上的错误。

      而数据库设计和查询得当，可以避免性能瓶颈，且应当尽可能选择表唯一字段作为指定连接字段。

  （2）等值连接（Equi-join）

    等值连接，在表关系的笛卡尔积中，选择所匹配字段值相等（=）的数据记录。

    等值连接，即是在关键词On后的匹配条件中通过关系运算符等号（=）实现等值条件的连接合并。

    等值连接关系数据操作，需要在执行过程中用“=”指定匹配条件，在新关系中不会去掉重复字段。

     示例1：select employee.name,department.name from employee join department                              

          on employee.department=department.department;

     示例2：select employee.name,department.name from employee join department                              

          on employee.WorkID=department.WorkID;

     注：上述示例2中，因WorkID具有唯一性，是的连接合并结果精简了许多，且更符合业务实际需要一些。

     示例3：select e.name EmployeeName,e.JobTitle,d.name,d. Department

          from employee e  inner join  department d  on e.WorkID=d.WorkID;

     等值连接中存在一种特例，那就是自连接，等同于自然连接。所谓自连接，即是从表中抽取字段与表自身相连接匹配。

     示例4：select e.name EmployeeName,e.JobTitle,t.name,t. Department

          from employee as e  inner join  employee as t  on e.WorkID=t.WorkID;

  （3）不等连接

    不等链接，就是在关键字ON后的匹配条件中通过非等于关系运算符来实现表连接合并，包括：“>、>=、<、<=、!=”。

　　　　不等连接，在表关系的笛卡尔积中，选择所匹配字段值不相等（!=、>、>=、<、<=）的数据记录。

    不等连接关系数据操作，需要在执行过程中用“!=、>、>=、<、<=”指定匹配条件，在新关系中不会去掉重复字段。

    不等连接，往往存在多对多的关系，应当谨慎操作。 

    示例1：select employee.WorkID,employee.name,department.Department from employee join department                       

         on employee.WorkID!=department.WorkID;

    示例2.1：select e.name employeename,e.job,l.name loadername from employee  e  inner join t_employee  l  on

         e.WorkID=l.WorkID and e.empno>l.empno;

    示例2.2：select e.name employeename,e.job,l.name loadername from employee e ,t_employee l where 

         e.WorkID=l.WorkID and e.empno>l.empno;

    注：示例2.1实现方式，也可以通过示例2.2来实现。

4.2.3 外连接查询

  所谓外连接（Outer Join），就是在表关系的笛卡尔积数据记录中，不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为左连接（left join）、右连接（Right join）和全连接（Full join）。

  语法：select field1,field2,...,fieldn from tablename1 left|right|full  [outer] join tablename2 on join_condition;

  （1）左连接（left join）

    所谓左连接，即在表关系的笛卡尔积中，除了选择相匹配的数据记录，还包括关联左边表中不匹配的数据记录。

    左连接，左边全包括，右边匹配包括。即以关键字left join左边的表为参考表。

    示例1【左连接】：select e.WorkID,e.Name,e.job,l.Department,l.DepartmentID from employee e left join department l

              on e.Department=l.Department;

    示例2【内连接】：select e.WorkID,e.Name,e.job,l.Department,l.DepartmentID from employee e inner join department l

              on e.Department=l.Department;

    示例1与示例2对比，可发现示例1要比示例2的记录多，原因是示例1包含了左边表中全部记录，示例2中仅包含了相匹配的记录。

  （2）右连接（left join）

    所谓右连接，即在表关系的笛卡尔积中，除了选择相匹配的数据记录，还包括关联右边表中不匹配的数据记录。

    右连接，右边全包括，左边匹配包括。即以关键字right join左边的表为参考表。

    示例1【右连接】：select e.WorkID,e.Name,e.job,l.Department,l.DepartmentID from employee e right join department l

              on e.Department=l.Department;

    示例2【内连接】：select e.WorkID,e.Name,e.job,l.Department,l.DepartmentID from employee e inner join department l

              on e.Department=l.Department;

    示例1与示例2对比，可发现示例1要比示例2的记录多，原因是示例1包含了右边表中全部记录，示例2中仅包含了相匹配的记录。

  （3）全连接（full join）

    所谓全连接，即在表关系的笛卡尔积中，除了选择相匹配的数据记录，还包括关联左右两边表中不匹配的数据记录。

    MySQL中并没有直接实现全连接的语法，而是采用关键词Union来实现合并查询的一种方式，可近似实现全连接的查询。

    关键词Union，可将多个select语句的查询结果合并在一起组成新的关系。

    语法：select  field1,field2,...,fieldn from tablename1 union|union all  select field1,field2,...,fieldn from tablename2 

         union|union all select field1,field2,...,fieldn from tablename3......

    1）带有关键词Union的合并操作

      关键词Union会把查询结果集直接合并在一起，同时将会去掉重复数据记录。

      注：Union两边所选择的字段列数必须相等，否则无法成功合并。其合并是上下合并，而非左右合并。

        Union所合并的表所选字段属性与字段类型，以及排列顺序，最好是一致的，否则缺乏实际意义。

      示例1【出错】：select * from  employee union select * from department;

      示例2【正确】：select  JobTitle from employee union select JobTitle from department;

      示例3【正确】：select  JobTitle,WorkID from employee union select JobTitle,WorkID from department;

      注：多字段合并时，去重复是将多个字段共同考虑进行去重，即多个字段共同构成唯一字段。

    2）带关键词Union All的合并操作

      关键字Union All会把查询结果集直接合并在一起，不去重复。

      示例1【出错】：select * from  employee union all select * from department;

      示例2【正确】：select  JobTitle employee union all select JobTitle from department;

      示例3【正确】：select  JobTitle,WorkID employee union all select JobTitle,WorkID from department;

      注：存在记录统计时，通常采用此方法，即不去重统计。

  

4.2.4 子查询

  （1）为什么使用子查询

    示例：select  *  from employee e, department d where e.workID=d.workID;

    注：上述语句执行过程是，首先对两张表进行笛卡尔乘积匹配操作，然后在选取符合匹配条件的数据记录。

      进行笛卡尔积操作时，会生成两张表数据记录数的乘积值的结果记录；

      若此两张表的数据记录比较大，则进行笛卡尔积操作时会造成MySQL宕机。

    因此，较为经验的做法是，先计算关联表笛卡尔积的数值，然后根据其值的大小确定是否在MySQL及运算环境所能接受的范畴内，再决定是否进行多表连接查询操作。

    查询关联表笛卡尔积的方法：采用count（*）进行统计。示例：select  count(*)  from employee,department ;

    为解决上述因笛卡尔积过大导致性能或效率偏低的问题，使用子查询是较为妥当可取的方式。

    所谓子查询，是指在一个查询中，嵌套其他若干个查询。即：在一个select 查询语句where或from子句中包含另一个select查询语句。

    在子查询语句中，外层select查询语句为主查询，而where或from子句中的select查询语句称之为子查询，也称之为嵌套查询。

    通过子查询可以实现多表查询，查询语句中可能包括IN、ANY、ALL、Exists等关键字，还可能包括比较运算符。

    理论上子查询可以出现在语句的任意位置，但实际应用中多出现在where和from子句中。

    where子句中的子查询：该位置处的子查询一般返回单行单列、多行单列、单行多列的数据记录；

    From子句中的子查询：该位置处的子查询一般返回多行多列的数据记录。

  （2）子查询返回结果为单行单列和单行多列的查询

    1）子查询返回结果为单行单列的查询

      示例：select  Name,Salary from employee where Salary<(select Salary from employee where Name='Jack');

      注：子句“(select Salary from employee where Name='Jack')”返回结果为单行单列。

    2）子查询返回结果为单行多列的查询

      示例1：Select Name,Salary,JobTitle  from employee  where

           (Salary,JobTitle)=(Select Salary,JobTitle from employee where Name='Jack');

      示例2：Select Name,Salary as Sal, JobTitle  from  employee  where

           (Salary,JobTitle)=(Select Salary as Sal, JobTitle  Job from employee where Name='Jack');

      注：上述示例1和示例2中，红色的字段必须与相应表中字段名相同。

      注：多字段同时匹配查询，须注意子查询返回字段的顺序。

  （3）子查询返回结果为多行单列的查询

    子查询返回结果为多行单列的结果时，子查询语句一般在主查询语句的Where子句里出现，包含In、Any、All、Exists等关键词。

    1）带有关键字In的子查询

      当主查询的条件是属于子查询的结果中时，通过关键词In来实现。

      反之，若想实现主查询的条件不是子查询的结果中时，用关键词Not In来实现。

      示例1：select  *  from employee where Name in (select Manager from department);

      示例2：select  *  from employee where Name not  in (select Manager from department);

    2）带有关键字Any的子查询

      当主查询的条件是符合子查询返回结果中任意一条结果时，通过关键词Any来实现。

      关键字Any用来表示主查询的条件为满足子查询返回查询结果中任意一条数据记录时，该关键字有三种匹配方式：

      A、"=Any"：其功能与关键字In一样；

      B、">Any（>=Any）"：比子查询中返回数据记录中最小值还要大于（大于等于）数据记录；

      C、"<Any（<=Any）"：比子查询中返回数据记录中最大值还要小于（小于等于）数据记录。

      示例1：select name,salary from employee where salary>any(select salary from employee where job='IT');

      示例2：select name,salary from employee where salary>=any(select salary from employee where job='IT');

    3）带有关键字All的子查询

      当主查询的条件是符合子查询返回结果中所有结果时，通过关键词All来实现。

      关键字All用来表示主查询的条件为满足子查询返回查询结果中所有数据记录，该关键字有以下两种匹配方式：

      A、">All（>=All）"：比子查询中返回数据记录中最大值还要大于（大于等于）数据记录；

      C、"<All（<=All）"：比子查询中返回数据记录中最小值还要小于（小于等于）数据记录。

      示例1：select name,salary from employee where salary>all(select salary from employee where job='IT');

      示例2：select name,salary from employee where salary>=all(select salary from employee where job='IT');

    4）带有关键字Exists的子查询

      关键词Exists是一个布尔类型，返回结果为True，或者返回结果时为False；Exists子句内部查询有记录为True，否则为False。

　　　　　　带Exists的查询语句查询过程是，对外表采用遍历方式逐条查询，每次查询都会比较Exists的条件语句，当Exists子句内部条件语句返回记录行时，则条件为True且此时返回外表当前遍历到的记录，否则条件为False且此时返回外表记录为空，即不返回外表当前遍历到的记录；

　　　　　　反之，如果Exists里的条件语句不能返回记录行，则丢弃当前遍历到的记录。

      Exists内部查询为真时，内部查询即停止查询，否则遍历所有记录。

　　　　　　语法：select  field1,...,fieldn  from tab_A  where exists(select field1,...,fieldn from tab_B where condition)

      释义：当子查询exists语句返回至少一条结果时，exists返回结果为True，否则返回false。

      Exists查询分为两种情况：

      其一，子查询Exists子句中所查询的内部表跟主查寻外部表没有关联；

      其二，子查询Exists子句中所查询的内部表跟主查寻外部表存在关联；

      示例1【主查寻为全集】：select * from employee where exists(select * from department where Manager='Jack');

      示例2【主查寻为空集】：select * from employee where exists(select * from department where Manager='Jone');

      示例3【主查寻为交集】：select * from employee E where exists(select * from department where Manager=E.Name);

      示例1，子查询为True，主查询外部表与子查询内部表在内部条件上没有关联，故外部查询为全集，即外部表的记录全部显示；

      示例2，子查询为False，主查询外部表与子查询内部表在内部条件上没有关联，故外部查询为空集，即外部表查询结果为空。、

      示例3，子查询在查询过程中，其结果可能为True，也可能为False。

           因为整个查询过程，是主查询外部表每条记录的条件字段逐一同内部表的记录相关字段进行比较，且内部条件判断与外部表

           存在依耐关系，故而其比较结果可能为True，也可能为False。

           因此，实际上是外部表与内部表按条件取交集【非集合概念上的交集，不去重】。

  （4）子查询返回结果为多行多列的查询

    当子查询的返回结果为多行多列的数据记录时，该子查询语句一般会在主查询语句的From子句里，被当做一张临时表的方式来处理。

    临时表相当于是子查询的结果，作为中间表同主查询进行比较。

    示例：select  d.Department,d.Manager,CountNum,Average  from  department  d  left  join 
         (select Department,count(Department) CountNum,avg(Salary) Average from employee group by Department) ee
         on d.Department=ee.Department  order by  Average desc;

       注：上述示例，实质上是对部门人数和平均薪资进行统计，并按薪资进行排序。

  （5）子查询与主查寻的关系及性能优化推荐

    

    1）主查询与子查询在内部查询条件上没有关联关系时，sql语句查询优化如下：

    当子查询的内部表与主查询的外部表，在子查询内部条件上没有关联关系，可将子查询单独查询结果赋给独立的中间变量，主查询条件与

    变量进行比较，可提高查询速度，原因是：避免主查询遍历每条记录时，都需要遍历子查询中的内部表。即使是外部表与内部表是同一张

    数据表也应当如此。

    2）主查询与子查询在内部查询条件上存在关联关系时

    不可采取上述1）中定义变量的方式，但实际查询时MySQL也是按照上述查询过程的逻辑采取了定义中间临时变量的方式。