Catalog

Classification of SQL statements

  1. DDL (Data Definition Languages) statement: Data Definition Language.These statements define different data segments,
    Definition of database objects such as databases, tables, columns, indexes, etc.Common statement keywords include create, drop, alter
    Wait.
  2. DML (Data Manipulation Language) statements: Data manipulation statements for adding, deleting, updating, and checking
    Query database records and check data integrity. Common statement keywords include insert, delete, udpate, and
    select, etc.
  3. DCL (Data Control Language) statement: A data control statement used to control the direct permission of different data segments and
    Access level statement.These statements define the database, tables, fields, user access rights, and security levels.Major
    Statement keywords include grant, revoke, and so on.

DML statement

DML operation refers to the operation of table records in the database, mainly including insert, update of table records.
delete and select are the most frequently used operations by developers on a daily basis.They will be entered next in turn
Line description.

  1. insert record

    Once the table is created, you can insert records into it. The basic syntax for inserting records is as follows:

    INSERT INTO tablename (field1,field2,......fieldn) VALUES(value1,value2,......valuesn);

    For example, insert the following records into the table emp: ename is zzx1, hiredate is 2000-01-01, sal is 2000, deptno
    1, the command executes as follows:

    mysql> insert into emp (ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);
    Query OK, 1 row affected (0.00 sec)

    You may not specify a field name, but the order after values should be the same as the order in which the fields are arranged:

    mysql> insert into emp values('lisa','2003-02-01','3000',2);
    Query OK, 1 row affected (0.00 sec)

    For fields with nullable fields, non-nullable fields but default values, and self-increasing fields, you do not need a list of fields after insert
    Inside, only the value of the corresponding field name is written after the values, which can be automatically set to NULL.
    Default, self-increasing next number, which in some cases can greatly reduce the complexity of the SQL statement.
    For example, only explicitly insert values for ename and sal fields in a table:

    mysql> insert into emp (ename,sal) values('dony',1000);
    Query OK, 1 row affected (0.00 sec)

    To see the actual insertion value:

    mysql> select * from emp;
    +-------+------------+---------+--------+
    | ename | hiredate   | sal     | deptno |
    +-------+------------+---------+--------+
    | zzx1  | 2000-01-01 | 2000.00 |      1 |
    | lisa  | 2003-02-01 | 3000.00 |      2 |
    | dony  | NULL       | 1000.00 |   NULL |
    +-------+------------+---------+--------+
    3 rows in set (0.00 sec)

    Sure enough, both fields set to nullable display as NULL.
    In MySQL, insert statements also have a good feature that allows you to insert multiple records at once, with the following syntax:

    INSERT INTO tablename (field1, field2,......fieldn)
    VALUES
    (record1_value1, record1_value2,......record1_valuesn),
    (record2_value1, record2_value2,......record2_valuesn),
    ......
    (recordn_value1, recordn_value2,......recordn_valuesn)
    ;

    You can see that each record is separated by a comma.
    In the following example, two records are inserted into the table dept at once:

    mysql> insert into dept values(5,'dept5'),(6,'dept6');
    Query OK, 2 rows affected (0.04 sec)
    Records: 2 Duplicates: 0 Warnings: 0
    
    mysql> select * from dept;
    +--------+----------+
    | deptno | deptname |
    +--------+----------+
    | 1      | tech     |
    | 2      | sale     |
    | 5      | fin      |
    | 5      | dept5    |
    | 6      | dept6    |
    +--------+----------+
    5 rows in set (0.00 sec)

    This feature allows MySQL to save a lot of network overhead and greatly improve insertion efficiency when inserting a large number of records.

  2. Update Records

    For record values in tables, you can change them through the update command with the following syntax:

    UPDATE tablename SET field1=value1,field2.=value2,......fieldn=valuen [WHERE CONDITION]

    For example, change the salary (sal) with ename as "lisa" in table emp from 3000 to 4000:

    mysql> update emp set sal=4000 where ename='lisa';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    View the results:

    mysql> select * from emp;
    +-------+------------+---------+--------+
    | ename | hiredate   | sal     | deptno |
    +-------+------------+---------+--------+
    | zzx1  | 2000-01-01 | 2000.00 |      1 |
    | lisa  | 2003-02-01 | 3000.00 |      2 |
    | dony  | NULL       | 1000.00 |   NULL |
    +-------+------------+---------+--------+
    3 rows in set (0.00 sec)
    
    mysql> update emp set sal=4000 where ename='lisa';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from emp;
    +-------+------------+---------+--------+
    | ename | hiredate   | sal     | deptno |
    +-------+------------+---------+--------+
    | zzx1  | 2000-01-01 | 2000.00 |      1 |
    | lisa  | 2003-02-01 | 4000.00 |      2 |
    | dony  | NULL       | 1000.00 |   NULL |
    +-------+------------+---------+--------+
    3 rows in set (0.00 sec)

    In MySQL, the update command can update data in multiple tables at the same time, with the following syntax:

    UPDATE t1,t2...tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]
  3. Delete Record

    If the record is no longer needed, it can be deleted using the delete command with the following syntax:

    DELETE FROM tablename [WHERE CONDITION]

    For example, delete all records with ename'dony'in emp with the following command:

    mysql> delete from emp where ename='dony';
    Query OK, 1 row affected (0.00 sec)

    You can delete data from multiple tables at once in MySQL with the following syntax:

    DELETE t1,t2...tn FROM t1,t2...tn [WHERE CONDITION]

    If the table name following the front is aliased, the alias following the delete is also used, otherwise a syntax error is raised.
    In the following example, records with deptno 3 in both table emp and dept are deleted:

    mysql> select * from emp;
    41
    +--------+------------+---------+--------+
    | ename | hiredate | sal | deptno |
    +--------+------------+---------+--------+
    | zzx     | 2000-01-01 | 100.00  | 1 |
    | lisa    | 2003-02-01 | 200.00  | 2 |
    | bjguan | 2004-04-02 | 100.00   | 1 |
    | bzshen | 2005-04-01 | 300.00   | 3 |
    | dony    | 2005-02-05 | 2000.00 | 4 |
    +--------+------------+---------+--------+
    5 rows in set (0.00 sec)
    mysql> select * from dept;
    +--------+----------+
    | deptno | deptname |
    +--------+----------+
    | 1   | tech     |
    | 2   | sale     |
    | 3   | hr       |
    | 5   | fin      |
    +--------+----------+
    4 rows in set (0.00 sec)
    
    mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
    Query OK, 2 rows affected (0.04 sec)
    mysql>
    mysql>
    mysql> select * from emp;
    +--------+------------+---------+--------+
    | ename | hiredate | sal | deptno |
    +--------+------------+---------+--------+
    | zzx     | 2000-01-01 | 100.00  | 1      |
    | lisa    | 2003-02-01 | 200.00  | 2      |
    | bjguan | 2004-04-02 | 100.00  | 1   |
    | dony    | 2005-02-05 | 2000.00 | 4      |
    +--------+------------+---------+--------+
    4 rows in set (0.00 sec)
    mysql> select * from dept;
    +--------+----------+
    | deptno | deptname |
    +--------+----------+
    | 1   | tech     |
    | 2   | sale     |
    | 5   | fin      |
    42
    +--------+----------+
    3 rows in set (0.00 sec)
  4. Query Records

    Once the data is inserted into the database, various queries can be made using the SELECT command to make the output consistent
    Our requirements.Because the syntax of SELECT is complex, only the most basic syntax is described here:

    SELECT * FROM tablename [WHERE CONDITION]

    The simplest way to query is to select all the records, and in the following example, query all the records in the table emp:

    mysql> select * from emp;
    +--------+------------+---------+--------+
    | ename  | hiredate   | sal     | deptno |
    +--------+------------+---------+--------+
    | zzx1   | 2000-01-01 | 2000.00 |      1 |
    | lisa   | 2003-02-01 | 4000.00 |      2 |
    | bjguan | 2004-04-02 | 5000.00 |      3 |
    +--------+------------+---------+--------+
    3 rows in set (0.00 sec)

    Where'*'means to select all records, you can also replace them with all fields separated by commas, for example, with
    The next two queries are equivalent:

    mysql> select * from emp;
    +--------+------------+---------+--------+
    | ename  | hiredate   | sal     | deptno |
    +--------+------------+---------+--------+
    | zzx1   | 2000-01-01 | 2000.00 |      1 |
    | lisa   | 2003-02-01 | 4000.00 |      2 |
    | bjguan | 2004-04-02 | 5000.00 |      3 |
    +--------+------------+---------+--------+
    3 rows in set (0.00 sec)
    mysql> select ename,hiredate,sal,deptno from emp;
    +--------+------------+---------+--------+
    | ename  | hiredate   | sal     | deptno |
    +--------+------------+---------+--------+
    | zzx1   | 2000-01-01 | 2000.00 |      1 |
    | lisa   | 2003-02-01 | 4000.00 |      2 |
    | bjguan | 2004-04-02 | 5000.00 |      3 |
    +--------+------------+---------+--------+
    3 rows in set (0.00 sec)

    The benefit of'*'is that when you need to query all the field information, the query statement is simple, but you only need to query some of the fields
    You must list the fields one by one.

    The syntax for querying all records has been described in the above example, but in practice, users will encounter a variety of queries
    Requirements are described below.

    • View non-repeating records

      Sometimes it is necessary to remove duplicates from the table and display them, using the distinct keyword:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      +--------+------------+---------+--------+
      3 rows in set (0.00 sec)
      
      
      mysql> select distinct deptno from emp;
      +--------+
      | deptno |
      +--------+
      |      1 |
      |      2 |
      +--------+
      2 rows in set (0.00 sec)
      
    • Conditional Query

      In many cases, the user does not need to query all the records, but only a portion of the data according to the restrictions.
      Use the where keyword to do this.

      For example, you need to query all records with deptno 1:

      mysql> select * from emp where deptno=1;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      +--------+------------+---------+--------+
      2 rows in set (0.00 sec)

      The eligible records are listed in the result set.In the example above, the condition following where is a field's'='
      In addition to'=', comparison operators such as >, <, >=, <=,!= can also be used for comparison; mu lt iple conditions can also make
      Multiple conditional joint queries are performed using logical operators such as or, and, which will be explained in more detail in a later chapter.
      The following is an example of using a multifield conditional query:

      mysql> select * from emp where deptno=1 and sal<3000;
      +-------+------------+---------+--------+
      | ename | hiredate   | sal     | deptno |
      +-------+------------+---------+--------+
      | zzx1  | 2000-01-01 | 2000.00 |      1 |
      +-------+------------+---------+--------+
      1 row in set (0.01 sec)
    • Sorting and Restrictions

      We often have the requirement to pull out the result set of records sorted by a field, which uses a database.
      The sorting operation is implemented by the keyword ORDER BY with the following syntax:

      SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] , field2
      [DESC|ASC],......fieldn [DESC|ASC]]

      DESC and ASC are sort order keywords, DESC means descending order by field, ASC means ascending order
      Arrange, if not written, the default is ascending.ORDER BY can be followed by several different sort fields, and
      Each sort field can have a different sort order.
      For example, the records in the emp table are shown by salary level:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      
      mysql> select *from emp order by sal;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      

      If the values of the sort fields are the same, the fields with the same values are sorted by the second sort field, and so on.If only
      If there is a sort field, records with the same fields will be sorted out of order.
      For example, the records in the emp table are sorted by the deptno field of the Department number:

      mysql> select *from emp order by deptno;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      

      For the first two records with the same deptno, if you want to sort your wages from high to low, you can use the following commands:

      mysql> select *from emp order by deptno ,sal desc;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)

      For sorted records, you can use the LIMIT keyword if you want to display only a portion, not all
      To achieve this, the syntax of LIMIT is as follows:

      SELECT ......[LIMIT offset_start,row_count]

      Where offset_start represents the starting offset of the record and row_count represents the number of rows displayed.
      By default, the starting offset is 0, and you only need to write the number of rows to record, so the first n is actually displayed
      For records, see the following example:
      For example, show the first three records in the emp table sorted by sal:

      mysql> select *from emp order by sal limit 3;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      +--------+------------+---------+--------+
      3 rows in set (0.00 sec)

      If you want to display emp tables sorted by sal starting with the second record, display three records:

      mysql> select *from emp order by sal limit 1,3;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      +--------+------------+---------+--------+
      3 rows in set (0.00 sec)

      limit is often used in conjunction with order by for paging the display of records.

      PS:limit is a syntax after MySQL extends SQL92 and is not common to other databases.

    • polymerization

      In many cases, we need to do some summary operations, such as counting the number of people throughout the company or the number of people in each department, when SQL aggregation is used.

      The syntax for aggregation operations is as follows:

      SELECT [field1,field2,......fieldn] fun_name
      FROM tablename
      [WHERE where_contition]
      [GROUP BY field1,field2,......fieldn
      [WITH ROLLUP]]
      [HAVING where_contition]

      Its parameters are explained as follows:

      • fun_name denotes the aggregation operation to be performed, that is, the aggregation function, commonly sum, count(*) (remember
        Number of records, max (maximum), min (minimum).
      • The GROUP BY keyword indicates the fields to be aggregated by category, for example, to count the number of employees by department, Department
        It should be written after group by.
      • WITH ROLLUP is an optional syntax indicating whether the results of the aggregation of classifications are summarized again.
      • The HAVING keyword denotes conditional filtering of the classified results.

      PS:having differs from where in that it filters the aggregated results conditionally, whereas it filters the records before aggregation. If logic allows, where filters the records as much as possible first. This will greatly improve the efficiency of aggregation because the result set is smaller, and finally, logically, yesDo not use having for refiltering.

      For example, to count the total number of Companies in the emp table:

      mysql> select *from emp order by sal limit 1,3;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      +--------+------------+---------+--------+
      3 rows in set (0.00 sec)
      
      mysql> select count(*) from emp;
      +----------+
      | count(*) |
      +----------+
      |        4 |
      +----------+
      1 row in set (0.26 sec)
      
      mysql> select count(1) from emp;
      +----------+
      | count(1) |
      +----------+
      |        4 |
      +----------+
      1 row in set (0.00 sec)
      

      On this basis, the number of people in each department will be counted:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      
      mysql> select deptno,count(1) from emp group by deptno;
      +--------+----------+
      | deptno | count(1) |
      +--------+----------+
      |      1 |        2 |
      |      2 |        1 |
      |      3 |        1 |
      +--------+----------+
      3 rows in set (0.00 sec)
      

      More finely, we need to count not only the number of departments but also the total number:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      
      mysql> select deptno,count(1) from emp group by deptno with rollup;
      +--------+----------+
      | deptno | count(1) |
      +--------+----------+
      |      1 |        2 |
      |      2 |        1 |
      |      3 |        1 |
      |   NULL |        4 |
      +--------+----------+
      4 rows in set (0.00 sec)
      

      Departments with more than one statistic:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      
      mysql> select deptno,count(1) from emp group by deptno having count(1)> 1;
      +--------+----------+
      | deptno | count(1) |
      +--------+----------+
      |      1 |        2 |
      +--------+----------+
      1 row in set (0.00 sec)
      

      Finally, the total salary, maximum and minimum salaries of all employees in the company are counted:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      
      
      mysql> select sum(sal),max(sal),min(sal) from emp;
      +----------+----------+----------+
      | sum(sal) | max(sal) | min(sal) |
      +----------+----------+----------+
      | 14000.00 |  5000.00 |  2000.00 |
      +----------+----------+----------+
      1 row in set (0.00 sec)
      
    • Table Connection

      When you need to display fields from multiple tables at the same time, you can use table joins to do this.
      From the broad category, table joins are divided into inner joins and outer joins. The main difference between them is that inner joins only select records that match each other in two tables, while outer joins select other records that do not match.The most common one we use is the internal connection.
      For example, query the name of all employees and the name of the Department in which they belong, because the employee name and the Department are stored in tables EMP and emp, respectively.
      In dept, therefore, you need to use table joins to query:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      
      mysql> select * from dept;
      +--------+----------+
      | deptno | deptname |
      +--------+----------+
      |      1 | tech     |
      |      2 | sale     |
      |      3 | hr       |
      +--------+----------+
      3 rows in set (0.00 sec)
      
      mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;
      +--------+----------+
      | ename  | deptname |
      +--------+----------+
      | zzx1   | tech     |
      | lisa   | sale     |
      | bjguan | tech     |
      | bzshen | hr       |
      +--------+----------+
      4 rows in set (0.00 sec)
      

      Outer connections are divided into left and right connections, which are defined as follows:

      • Left join: Contains all records in the left table or even no records in the right table that match it
      • Right Join: Contains all records in the right table or even no records in the left table that match it

      For example, query emp for all user names and department names

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      | dony   | 2005-02-05 | 2000.00 |      4 |
      +--------+------------+---------+--------+
      5 rows in set (0.00 sec)
      
      
      mysql> select * from dept;
      +--------+----------+
      | deptno | deptname |
      +--------+----------+
      |      1 | tech     |
      |      2 | sale     |
      |      3 | hr       |
      +--------+----------+
      3 rows in set (0.00 sec)
      
      
      mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
      +--------+----------+
      | ename  | deptname |
      +--------+----------+
      | zzx1   | tech     |
      | bjguan | tech     |
      | lisa   | sale     |
      | bzshen | hr       |
      | dony   | NULL     |
      +--------+----------+
      5 rows in set (0.00 sec)
      

      Comparing this query with the query in the example, both query user name and department name, the difference is listed in this example
      Some user names, even if some user names (dony) do not have a legal department name (department number 4, in dept)
      There is no such department); in the example, only the user name and department name of the legal department are listed.
      Right and left joins are similar, and they can be transformed into each other. For example, the example above can be rewritten to the right join as follows:

      mysql>  select ename,deptname from dept right join emp on dept.deptno=emp.deptno; 
      +--------+----------+
      | ename  | deptname |
      +--------+----------+
      | zzx1   | tech     |
      | bjguan | tech     |
      | lisa   | sale     |
      | bzshen | hr       |
      | dony   | NULL     |
      +--------+----------+
      5 rows in set (0.27 sec)
    • Subquery

      In some cases, when we query, the condition we need is the result of another select statement, in which case we need a subquery.The keywords used for subqueries mainly include in, not in, =,!=, exists, not exists, etc.For example, query the emp table for all records of all departments in the dept table:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
       | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
       | bzshen | 2005-04-01 | 3000.00 |      3 |
      | dony   | 2005-02-05 | 2000.00 |      4 |
      +--------+------------+---------+--------+
      5 rows in set (0.00 sec)
      
      mysql> select* from dept;
      +--------+----------+
      | deptno | deptname |
      +--------+----------+
      |      1 | tech     |
      |      2 | sale     |
      |      3 | hr       |
      |      5 | fin      |
      +--------+----------+
      4 rows in set (0.00 sec)
      
      
      mysql> select* from emp where deptno in (select deptno from dept);
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      

      If the number of records in a subquery is unique, you can also use = instead of in;

      mysql> select * from emp where deptno = (select deptno from dept limit 2);
      ERROR 1242 (21000): Subquery returns more than 1 row
      mysql> select * from emp where deptno = (select deptno from dept limit 1);
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      +--------+------------+---------+--------+
      2 rows in set (0.00 sec)
      

      In some cases, subqueries can be converted to table joins, for example:

      mysql> select* from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      | dony   | 2005-02-05 | 2000.00 |      4 |
      +--------+------------+---------+--------+
      5 rows in set (0.00 sec)
      
      mysql> select *from dept;
      +--------+----------+
      | deptno | deptname |
      +--------+----------+
      |      1 | tech     |
      |      2 | sale     |
      |      3 | hr       |
      |      5 | fin      |
      +--------+----------+
      4 rows in set (0.00 sec)
      
      mysql> select *from emp where deptno in (select deptno from dept);
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      
      # After converting to table join
      mysql> select emp.* from emp,dept where emp.deptno = dept.deptno;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      +--------+------------+---------+--------+
      4 rows in set (0.00 sec)
      

      Note: Conversion between subqueries and table joins is mainly used in two ways:

      • Subqueries are not supported in versions prior to MySQL 4.1 and require table joins to implement subqueries
      • Table joins are used in many cases to optimize subqueries
    • Record Union

      We often encounter such an application that combines the data of two tables and displays the results together after querying according to certain query conditions. At this time, Union and union all keywords are needed to perform such functions. The specific syntax is as follows:

      SELECT * FROM t1 
      UNION|UNION ALL 
      SELECT * FROM t2 
      ...... 
      UNION|UNION ALL 
      SELECT * FROM tn; 

      The main difference between UNION and UNION ALL is that UNION ALL merges result sets directly together, while UNION performs a DISTINCT of the result after UNION ALL to remove duplicate records.Take the following example to show a collection of department numbers in the emp and dept tables:

      mysql> select * from emp;
      +--------+------------+---------+--------+
      | ename  | hiredate   | sal     | deptno |
      +--------+------------+---------+--------+
      | zzx1   | 2000-01-01 | 2000.00 |      1 |
      | lisa   | 2003-02-01 | 4000.00 |      2 |
      | bjguan | 2004-04-02 | 5000.00 |      1 |
      | bzshen | 2005-04-01 | 3000.00 |      3 |
      | dony   | 2005-02-05 | 2000.00 |      4 |
      +--------+------------+---------+--------+
      5 rows in set (0.00 sec)
      
      mysql> select *from dept;
      +--------+----------+
      | deptno | deptname |
      +--------+----------+
      |      1 | tech     |
      |      2 | sale     |
      |      3 | hr       |
      |      5 | fin      |
      +--------+----------+
      4 rows in set (0.00 sec)
      
      # Joint Ownership (duplicates)
      mysql> select deptno from emp 
          -> union all
          -> select deptno from dept;
      +--------+
      | deptno |
      +--------+
      |      1 |
      |      2 |
      |      1 |
      |      3 |
      |      4 |
      |      1 |
      |      2 |
      |      3 |
      |      5 |
      +--------+
      9 rows in set (0.00 sec)
      

      If you want the result to be displayed after removing duplicate records:

      # Remove duplicate records
      mysql> select deptno from emp
          -> union
          -> select deptno from dept;
      +--------+
      | deptno |
      +--------+
      |      1 |
      |      2 |
      |      3 |
      |      4 |
      |      5 |
      +--------+
      5 rows in set (0.00 sec)