Theme NexT works best with JavaScript enabled

From a Beginner to a Disaster

Xiao.Y

0%

MySQL Manual

Posted on Edited on

IMPORTANT:
Some of the content here is a personal summary/abbreviation of contents on the Offical MySQL Guide. Feel free to refer to the official site if you think some of the sections written here are not clear.

MySQL Intro

The MySQL client program can be used to create and use a simple database. MySQL (sometimes referred to as the “terminal monitor” or just “monitor”) is an interactive program that enables you to connect to a MySQL server, run queries, and view the results.

The MySQL server package will install the MySQL database server which you can interact with using a MySQL client. You can use the MySQL client to send commands to any MySQL server; on a remote computer or your own.

The MySQL server is used to persist the data and provide a query interface for it (SQL). The MySQL clients purpose is to allow you to use that query interface.

On how to setup your MySQL Server, please refer to here

Connecting to a MySQL Server

  • If you have installed the MySQL server on Docker using the guide here, then you should be already connected to your local SQL server. If not, try to run (assuming you have the container running):

    1
    docker exec -it <yourSQLServerName> mysql -u root -p

    This will prompt to enter the password.

    If you entered successfully, you will see:

    1
    mysql>

  • If you want to connect to a remote SQL server, then you need to specify a host name. Contact your administrator to find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Then, you would run:

    1
    2
    shell> mysql -h <hostName> -u <userName> -p
    Enter password: <yourPasswordHere>

  • To disconnect from that SQL server, simply run:

    1
    QUIT

Basic Queries in MySQL Client

Make sure that you are connected to the server, as discussed in the previous section. Doing so does not in itself select any database to work with, but that is okay. There are still several queries you can do to have a basic sense of how MySQL works:

1
2
3
4
5
6
7
mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 8.0.20    | 2020-05-26   |
+-----------+--------------+
1 row in set (0.00 sec)

Note:

  • You need to have the semi-colon “;“ for ending your query, before pressing enter. If not, you will automatically goes to the next line to continue input. However, there are some exceptions where a semicolon may be omitted. QUIT, mentioned earlier, is one of them. We’ll get to others later.)
  • The query may be entered in any lettered case. The following queries are equivalent: mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;

From the simple command, you can see several things about a query:

  • When you issue a query, MySQL sends it to the server for execution and displays the results, then prints another mysql> prompt to indicate that it is ready for another query.

  • MySQL displays query output in tabular form (rows and columns). The first row contains labels/titles for the columns. The rows following are the query results. Normally, column labels are the names of the columns you fetch from database tables. If you’re retrieving the value of an expression rather than an actual label/title, mysql labels the column using the expression itself.

  • MySQL shows, in the end, how many rows were returned and how long the query took to execute, which gives you a rough idea of server performance. These values are imprecise because they represent wall clock time (not CPU or machine time), and because they are affected by factors such as server load and network latency.

Other examples of a query include:

1
2
3
4
5
6
7
select cos(pi()/2), (4+1)*2;
+-----------------------+---------+
| cos(pi()/2)           | (4+1)*2 |
+-----------------------+---------+
| 6.123233995736766e-17 |      10 |
+-----------------------+---------+
1 row in set (0.00 sec)

and you can make two or more queries simultaneouly by using the delimiter “;“, for example to seperate the above query into two:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select cos(pi()/2);select (4+1)*2;
+-----------------------+
| cos(pi()/2)           |
+-----------------------+
| 6.123233995736766e-17 |
+-----------------------+
1 row in set (0.00 sec)

+---------+
| (4+1)*2 |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

finally, you can also use a multiline query as well, given that you had the correct punctuations in place :

1
2
3
4
5
6
7
8
9
10
mysql> select
    -> cos(pi()/2)
    -> , (4+1)*2
    -> ;
+-----------------------+---------+
| cos(pi()/2)           | (4+1)*2 |
+-----------------------+---------+
| 6.123233995736766e-17 |      10 |
+-----------------------+---------+
1 row in set (0.01 sec)

or

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select cos(pi()/2); select
+-----------------------+
| cos(pi()/2)           |
+-----------------------+
| 6.123233995736766e-17 |
+-----------------------+
1 row in set (0.00 sec)

    -> (4+1)*2;
+---------+
| (4+1)*2 |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

Note

  • If your line ended with a “;“, you cannot enter further input but to start another new command. In the above examples, notice how the prompt changes from mysql> to ->, which indicates you are at a line of a multiple-line query.
  • To cancel the multiline query, you could use \c:
1
2
3
4
5
mysql> select user()
    -> \c
\c
^C
mysql>

Here is a table summarizing the prompts you will see in MySQL:

Prompt Meaning
mysql> Ready for new query
-> Waiting for next line of multiple-line query
'> Waiting for next line, waiting for completion of a string that began with a single quote (')
"> Waiting for next line, waiting for completion of a string that began with a double quote (")
``> ` Waiting for next line, waiting for completion of an identifier that began with a backtick (`)
/*> Waiting for next line, waiting for completion of a comment that began with /*

For example, if you have missed a ' at the end of the line:

1
2
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>

This means that mysql is expecting another ' at the end, which you have missed in this case.

The solution, sadly, is to cancel the input and do it corretcly the next time:

1
2
3
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '> \c
mysql>

Viewing and Creating a Database

  • To first see what databases you have access to, you can use the command:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+

    These databases are the ones that you have access to. If you don’t have access to a database, you will not see it here.

    Then, to switch to use a database (for example, sys), you can run the command:

    1
    mysql> use sys

    Note:

    • The use command is another command (exception) that does not require a semi-colon to end. However, it does no harm to put one in the end anyway.
    • Having access to a database means a lot. It means that you could insert or delete anything in that database. So usually to have an access to a database you would need to ask your administrator.

  • Now, to create a database, say you call it test, you could run:

    1
    mysql> create database test;

    Note:

    • Under Unix, database names are case-sensitive (unlike SQL keywords), so you must always refer to your database as test, not as Test, TEST, or some other variant.

    However, at this point you have only created a database, and you are still in the database sys (follows from the previous example). To switch to the database test you created, you need to explicitly call again:

    1
    2
    mysql> use test;
    Database changed

    Now, the next time you want to connect to the database test, you could directly run:

    1
    2
    shell> mysql -u root -p test
    Enter password: ********

    and in general, to connect to a database databaseName from start up, you could do:

    1
    2
    shell> mysql -h <hostName> -u <userName> -p <databaseName>
    Enter password: ********

    Note:

    • <databaseName> in the command just shown is not your password. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -p, not as -p ). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

  • To see which database you are currently in, you could use the query:

    1
    2
    3
    4
    5
    6
    7
    mysql> select database();
    +------------+
    | database() |
    +------------+
    | test       |
    +------------+
    1 row in set (0.00 sec)

Viewing and Creating a Table

  • To see what tables you have in a database you are currently in, you can use:

    1
    2
    mysql> show tables;
    Empty set (0.00 sec)

    In this case, we are in the test database that we just created so there is nothing.

  • Now, consider the example on the MySQL site, where you would like to record information for each of your pets (if you don’t have many, imagine you do). Then this can be called the pet table, and it should contain, as a bare minimum, each animal’s name (as an entry). Then, you could add other information about each of your pet in the table. For example, if more than one person in your family keeps pets, you might want to list each animal’s owner. You might also want to record some basic descriptive information such as species and sex.

    How about age? That might be of interest, but it is not a good thing to store in a database. Age changes as time passes, which means you’d have to update your records often. Instead, it is better to store a fixed value such as date of birth. Then, whenever you need age, you can calculate it as the difference between the current date and the birth date. MySQL provides functions for doing date arithmetic (as you saw before), so this is not difficult.

    You can probably think of other types of information that would be useful in the pet table, but the ones identified so far are sufficient and will be used in this example: name, owner, species, sex, birth, and death:

    1
    2
    mysql> create table pet (name varchar(20), owner varchar(20), species varchar(20), sex char(1), birth date, death date);
    Query OK, 0 rows affected (0.03 sec)

    Notice that we use char() and varchar():

    • The length of a CHAR/char column is fixed to the length that you declare when you create the table. For example, char(1) means it can hold up to 1 character. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, 8trailing spaces are removed* unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

    • Values in VARCHAR columns are variable-length strings.??? The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

      To make sure you have your table pet created as the way you wanted, you can run:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      mysql> describe pet;
      +---------+-------------+------+-----+---------+-------+
      | Field   | Type        | Null | Key | Default | Extra |
      +---------+-------------+------+-----+---------+-------+
      | name    | varchar(20) | YES  |     | NULL    |       |
      | owner   | varchar(20) | YES  |     | NULL    |       |
      | species | varchar(20) | YES  |     | NULL    |       |
      | sex     | char(1)     | YES  |     | NULL    |       |
      | birth   | date        | YES  |     | NULL    |       |
      | death   | date        | YES  |     | NULL    |       |
      +---------+-------------+------+-----+---------+-------+
      6 rows in set (0.00 sec)

      If you have made a mistake somewhere, and you want to change the type or the field name, you could use:

      1
      mysql> ALTER TABLE pet CHANGE <fromFieldName> <toFieldName> <Type>

      or if you only want to change the type, you can use:

      1
      mysql> ALTER TABLE pet MODIFY <fieldName> <newType>

      (you cannot only modify the field name) More examples on modifying the table please refer to here.

Loading and Inserting Data into a Table

After creating a table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

Suppose you have the following data as shown below (notice that MySQL expects dates in YYYY-MM-DD format):

Because you are beginning with an empty table, an easy way to populate it is to create a text file containing a row for each of your animals, then load the contents of the file into the table.

For exapmle, create a text file pet.txt containing one record per line, with values separated by tabs, (for csv files, refer to this) and given in the order in which the columns were listed in the table. For missing values (such as unknown sexes or death dates for animals that are still living), you can use \N, which represent null values:

1
Whistler        Gwen    bird    \N      1997-12-09      \N

Then, you can import by:

1
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

Note:

  • If you run into problems with importing a file, then probably you need to configure your SQL server for permission with file import. By default, this is disabled due to safety concerns. To configure this, please follow the guide here

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. 

1
2
mysql> INSERT INTO pet VALUES ('Fluffy','Harold','cat','f','1993-02-04',NULL);
Query OK, 1 row affected (0.02 sec)

Notice that String and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value. You do not use \N like you do with LOAD DATA.

Note:

  • The values in the table does not have to be distinct. This means that you can INSERT two same rows into the table without any trouble. However, to delete the non-distinct rows, you might need to do some work.

Retrieving/Selecting All Information from a Table

The SELECT statement is used to pull information from a table. The general form of the statement is:

1
2
3
SELECT <what_to_select>
FROM <which_table>
WHERE <conditions_to_satisfy>;

where

  • <what_to_select> indicates what you want to see. This can be a list of columns, or * to indicate “all columns.”
  • <which_table> indicates the table from which you want to retrieve data. The WHERE clause is optional.
  • <conditions_to_satisfy> specifies one or more conditions that rows must satisfy to qualify for retrieval, if it is present.

This means you can select all data by simply running:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

However, if at this point, you have found that some values are wrong in a specific row, perhaps you miss-typed in your txt file, then you could either delete the entire table and re-insert the correct txt, or updating that specific value:

  1. You can first fix your txt file and then run this:

    1
    2
    mysql> DELETE FROM pet;
    mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

  2. You could also only fix the wrong one (Bowser‘s birth, for example) by:

    1
    mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

Selecting a Particular Row

You can select only particular rows from your table, by specifying the WHERE option. For example, if you want to verify the change that you made to Bowser’s birth date, select Bowser’s record like this:

1
2
3
4
5
6
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

or 

1
2
3
4
5
6
mysql> SELECT birth FROM pet WHERE name = 'Bowser';
+------------+
| birth      |
+------------+
| 1989-08-31 |
+------------+

In general, you can specify any expression for the WHERE option as long as it can be evaluted to either true or false. This means, for example, you can specify inequality:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

and you can use logical operators such as AND and OR:

1
2
3
4
5
6
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

You can even combine the operators like this:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Note:

  • AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it is a good idea to use parentheses to indicate explicitly how conditions should be grouped.
  • If you want querey for pets that are not dead, then you should use death IS NOT NULL rather than death<>NULL (yes, <> means not equal to) because NULL is a special value that cannot be compared using the usual comparison operators. This is discussed later.

Selecting Particular Columns

You can select a particular column by:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

If you want to see more than one column, use thet “,“ as the delimiter:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

You can combine WHERE clause (row selection) with column selection. For example, to get birth dates for dogs and cats only, use this query:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT name, species, birth FROM pet
       WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+

Sorting the Table by Rows

  • To sort a table (which is only meaningful w.r.t a particular row), use an ORDER BY clause, which by default sorts a comparable entry in a ascending order.

    For example, we could sort the animals by their birth date:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> SELECT name, birth FROM pet ORDER BY birth;
    +----------+------------+
    | name     | birth      |
    +----------+------------+
    | Buffy    | 1989-05-13 |
    | Bowser   | 1989-08-31 |
    | Fang     | 1990-08-27 |
    | Fluffy   | 1993-02-04 |
    | Claws    | 1994-03-17 |
    | Slim     | 1996-04-29 |
    | Whistler | 1997-12-09 |
    | Chirpy   | 1998-09-11 |
    | Puffball | 1999-03-30 |
    +----------+------------+

    You can also sort by name, which would mean invoking comparison on character type entries. In this case, sorting — like all other comparison operations — is normally performed in a case-insensitive fashion. However, you can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY <col_name>.

  • To sort in a descending order, you need to add the option DESC to the <col_name> you are sorting:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
    +----------+------------+
    | name     | birth      |
    +----------+------------+
    | Puffball | 1999-03-30 |
    | Chirpy   | 1998-09-11 |
    | Whistler | 1997-12-09 |
    | Slim     | 1996-04-29 |
    | Claws    | 1994-03-17 |
    | Fluffy   | 1993-02-04 |
    | Fang     | 1990-08-27 |
    | Bowser   | 1989-08-31 |
    | Buffy    | 1989-05-13 |
    +----------+------------+

  • You can also perform multiple sortings. This works by first sorting by the first criteria you specified. Then, within rows of the same first criteria, sort by the second criteria. This can propagate depending on your data.

    For example, to sort first by type of animal in ascending order, then by birth date within the same animal type in descending order (youngest animals first):

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> SELECT name, species, birth FROM pet
           ORDER BY species, birth DESC;
    +----------+---------+------------+
    | name     | species | birth      |
    +----------+---------+------------+
    | Chirpy   | bird    | 1998-09-11 |
    | Whistler | bird    | 1997-12-09 |
    | Claws    | cat     | 1994-03-17 |
    | Fluffy   | cat     | 1993-02-04 |
    | Fang     | dog     | 1990-08-27 |
    | Bowser   | dog     | 1989-08-31 |
    | Buffy    | dog     | 1989-05-13 |
    | Puffball | hamster | 1999-03-30 |
    | Slim     | snake   | 1996-04-29 |
    +----------+---------+------------+

    However, if all data are distinct, for example, then sorting by two criterias would be meaningless, as the result would be the same if you only sorted it by the first criteria you specified (you can try this yourself).

  • Lastly, you could combine row selections as well. However, here you need to specify WHERE before you specify ORDER BY, which makes sense because this can save computation time.

    For example, in the above sorting, I would only want to see cat and dogs, I could do:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> SELECT name, species, birth FROM pet
           WHERE species='cat' OR species='DOG' ORDER BY species, birth DESC;
    +----------+---------+------------+
    | name     | species | birth      |
    +----------+---------+------------+
    | Claws    | cat     | 1994-03-17 |
    | Fluffy   | cat     | 1993-02-04 |
    | Fang     | dog     | 1990-08-27 |
    | Bowser   | dog     | 1989-08-31 |
    | Buffy    | dog     | 1989-05-13 |
    +----------+---------+------------+

Data Calculations

MySQL provides several functions that you can use to perform calculations. In this case, if we want to do calculations on dates, for example, to calculate ages or extract parts of dates, we could use TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) function:

  • Argument
    • unit, which will be the format of the result, has the following legal values: MICROSECOND(microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
    • datetime_expr1 and datetime_expr2 are date or datetime expressions.
  • Returns
    • datetime_expr2 − datetime_expr1 in the unit specified by unit. However, one expression may be a date and the other a datetime; in that case, a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument.

Also, an alias (AS age) is used to make the final output column label more meaningful:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

You can then order it by age:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

But what if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides several functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and MONTH(date):

  • Argument
    • date, for example '2008-02-03'
  • Returns
    • the month for date, in the range 1 to 12 for January to December, or 0 for dates such as ‘0000-00-00’ or ‘2008-00-00’ that have a zero month part.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

and if you want to see pets that have birthday the current month (suppose it is May now), then you can do:

1
2
3
4
5
6
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

Note:

  • You might think that you could write SELECT name, birth, MONTH(birth) as month FROM pet WHERE month= 5; would be another option, as it works for sorting. Unfortunately, this would not work because standard SQL disallows references to column aliases in a WHERE clause.

To see pets that have birthday the next month, you can use either the DATE_ADD() function or the MOD() function. For saving space, details for their arguments and return values are not explained here.

  1. To use the DATE_ADD() function to add a month to the current date:

    1
    2
    mysql> SELECT name, birth FROM pet
           WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

  2. To use the MOD() function to calculate the value of the next month:

    1
    2
    mysql> SELECT name, birth FROM pet
           WHERE MONTH(birth) = MOD(MONTH(CURDATE())+1, 12);

If a calculation fails, it will produce warnings:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+

Working with NULL Values

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:

1
2
3
4
5
6
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

where you can think of 0 meaning false and 1 meaning true.

If you use arithmetic comparison operators such as =, <, or <> to test for NULL, you will get the following result:

1
2
3
4
5
6
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

This is because the result of any arithmetic comparison with NULL is also NULL, so you cannot obtain any meaningful results from such comparisons.

Note:

  • In MySQL, 0 or NULL means false and anything else means true. The default true value from a boolean operation is 1.
  • Also, when doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

Pattern Matching

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

  • _ can be used to match/replace any single character
  • % to match/replace an arbitrary number of characters (including zero characters).

Note:

  • In MySQL, SQL patterns are case-insensitive by default.

Some examples are shown here. Do not use = or <> when you use SQL patterns. Use the LIKE or NOT LIKE comparison operators instead.

  • To find names beginning with b or B (because it is case-insensitive):

    1
    2
    3
    4
    5
    6
    7
    mysql> SELECT * FROM pet WHERE name LIKE 'b%';
    +--------+--------+---------+------+------------+------------+
    | name   | owner  | species | sex  | birth      | death      |
    +--------+--------+---------+------+------------+------------+
    | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
    | Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
    +--------+--------+---------+------+------------+------------+

  • To find names ending with fy:

    1
    2
    3
    4
    5
    6
    7
    mysql> SELECT * FROM pet WHERE name LIKE '%fy';
    +--------+--------+---------+------+------------+-------+
    | name   | owner  | species | sex  | birth      | death |
    +--------+--------+---------+------+------------+-------+
    | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
    | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
    +--------+--------+---------+------+------------+-------+

  • To find names containing a w:

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SELECT * FROM pet WHERE name LIKE '%w%';
    +----------+-------+---------+------+------------+------------+
    | name     | owner | species | sex  | birth      | death      |
    +----------+-------+---------+------+------------+------------+
    | Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
    | Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
    | Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
    +----------+-------+---------+------+------------+------------+

  • To find names containing exactly five characters, use five instances of the _ pattern character:

    1
    2
    3
    4
    5
    6
    7
    mysql> SELECT * FROM pet WHERE name LIKE '_____';
    +-------+--------+---------+------+------------+-------+
    | name  | owner  | species | sex  | birth      | death |
    +-------+--------+---------+------+------------+-------+
    | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    +-------+--------+---------+------+------------+-------+

The other type of pattern matching provided by MySQL uses extended regular expressions. Those will be much more extensive and enables you to do much more. When you test for a match for this type of pattern, use the REGEXP_LIKE(expr, pat[, match_type]) function (or the REGEXP or RLIKE operators, which are synonyms for REGEXP_LIKE()).

  • Arguement
    • expr a string of input
    • pat a regular expression for matching the input. Examples of common regular expressions include:
      • . matches any single character.
      • A character class [...] matches any character for once within the brackets. For example, [abc] matches a, b, or c. To name a range of characters, use a dash. [a-z] matches any letter, whereas [0-9] matches any digit. Use + to match a character more than once. For example, [a-z]+ would match any word.
      • * matches zero or more instances of the thing preceding it. For example, x* matches any number of x characters, [0-9]* matches any number of digits, and .* matches any number of anything.
      • Use ^ at the beginning or $ at the end of the pattern to anchor a pattern so that it must match the beginning or end of the value being tested, respectively.
      • A regular expression pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)
      • for a complete reference, visit Regexp Syntax by MySQL
    • optional match_type is a string that may contain any or all the following characters specifying how to perform matching:
      • c: Case sensitive matching.
      • i: Case-insensitive matching.
      • m: Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.
      • n: The . character matches line terminators. The default is for . matching to stop at the end of a line.
      • u: Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.
  • Returns
    • Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

For example, to find names beginning with b, use ^ to match the beginning of the name:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find names ending with fy, use $ to match the end of the name:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

To force a regular expression comparison to be case sensitive, use a case-sensitive collation, or use the BINARY keyword to make one of the strings a binary string, or specify the c match-control character (as mentioend above). Each of these queries matches only lowercase b at the beginning of a name:

1
2
3
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

To find names containing a w, use this query:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value as would be true with an SQL pattern.

To find names containing exactly five characters, use ^ and $ to match the beginning and end of the name, and five instances of . in between (simply ..... would not work due to the fact that REGEXP() matches if it occurs anywhere in the value):

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Counting Rows and Grouping Frequencies

Databases are often used to answer the question, “How often does a certain type of data occur in a table?” For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of census operations on your animals.

  • Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet. COUNT(*) counts the number of rows, so the query to count your animals looks like this:

    1
    2
    3
    4
    5
    6
    mysql> SELECT COUNT(*) FROM pet;
    +----------+
    | COUNT(*) |
    +----------+
    |        9 |
    +----------+

  • The more useful case come when you use COUNT() in conjunction with GROUP BY for characterizing your data under various groupings.

    • For example, if you want to find out how many pets each owner has:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
      +--------+----------+
      | owner  | COUNT(*) |
      +--------+----------+
      | Benny  |        2 |
      | Diane  |        2 |
      | Gwen   |        3 |
      | Harold |        2 |
      +--------+----------+

      This works because COUNT() is a window function, whose result rows are determined from the FROM clause, after WHERE, GROUP BY, and HAVING processing, and windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT.

      This means that it first GROUP BY owner, and then shows owner and COUNT(*). For a complete guide of how to use a COUNT(), please follow this link. For now, you just need to know that COUNT():

      • Return
        • Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement

    • Similarly, you can also GROUP BY species:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
      +---------+----------+
      | species | COUNT(*) |
      +---------+----------+
      | bird    |        2 |
      | cat     |        2 |
      | dog     |        3 |
      | hamster |        1 |
      | snake   |        1 |
      +---------+----------+

    • And number of animals per sex:

      1
      2
      3
      4
      5
      6
      7
      8
      mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
      +------+----------+
      | sex  | COUNT(*) |
      +------+----------+
      | NULL |        1 |
      | f    |        4 |
      | m    |        4 |
      +------+----------+

      where NULL means the sex is unknown.

    • You could also group by one than one categories. For example, you can group by species first and then sex:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
      +---------+------+----------+
      | species | sex  | COUNT(*) |
      +---------+------+----------+
      | bird    | NULL |        1 |
      | bird    | f    |        1 |
      | cat     | f    |        1 |
      | cat     | m    |        1 |
      | dog     | f    |        1 |
      | dog     | m    |        2 |
      | hamster | f    |        1 |
      | snake   | m    |        1 |
      +---------+------+----------+

    • and finally, you can also use the WHERE clause to select only a proportion of data you want (WHERE executes before GROUP BY). For example, if you only want to see cat and dog data:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      mysql> SELECT species, sex, COUNT(*) FROM pet
             WHERE species = 'dog' OR species = 'cat'
             GROUP BY species, sex;
      +---------+------+----------+
      | species | sex  | COUNT(*) |
      +---------+------+----------+
      | cat     | f    |        1 |
      | cat     | m    |        1 |
      | dog     | f    |        1 |
      | dog     | m    |        2 |
      +---------+------+----------+

Using Multiple Tables

Suppose you have another table. Then a problem arise when you need to deal with data on both tables, namely, how do you combine and operate on data from multiple tables?

Now suppose you created another table to record other information about them, such as events in their lives like visits to the vet or when litters are born, and you put the following information in a event table:

Suppose that you want to find out the ages at which each pet had its litters. We saw earlier how to calculate ages from two dates. The litter date of the mother is in the event table, but to calculate her age on that date you need her birth date, which is stored in the pet table. This means the query requires both tables:

  • First, you need to know that tables are distinct in a database. This means that any column in a specific table in a database can be referred as: <tableName>.<colName>.

  • Now, to combine data from multiple tables, you can use INNER JOIN (for more details on JOIN clause, see here), which can be used to combine the tables by permitsting rows from either table to appear in the result if and only if both tables meet the conditions specified in the ON clause. For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

where you see in this example:

  • When combining (joining) information from multiple tables, you need to specify how records in one table can be matched to records in the other (e.g. using the ON clause). This is easy because they both have a name column. The query uses an ON pet.name=event.name to match up records in the two tables if they have the same name value.

However, you do not need to have two different tables to use the INNER JOIN ... ON functionality. You can use alias `AS. For example, to find possible **breeding pairs among your pets**, you can join thepet` table with itself to produce candidate pairs of live males and females of like species:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM pet AS p1 INNER JOIN pet AS p2
         ON p1.species = p2.species
         AND p1.sex = 'f' AND p1.death IS NULL
         AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

Using MySQL in Batch Mode

Sometimes, if you have a batch file that you would like MySQL to execute, you can run mysql in batch mode. To do this, put the statements you want to run in a batch file (with .sql ending), then tell mysql to read its input from the file.

  • Outside of mysql:

    1
    shell> mysql < <your-batch-file>

    If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:

    1
    C:\> mysql -e "source <your-batch-file>"

    If you need to specify connection parameters on the command line, the command might look like this:

    1
    2
    shell> mysql -h host -u user -p < <your-batch-file>
    Enter password: ********

  • You can also use scripts from inside the mysql prompt by using the source command or \. command:

    1
    2
    mysql> source filename;
    mysql> \. filename

Note:

  • If you want the script to continue even if some of the statements in it produce errors, you should use the --force command-line option.

In general, you would use scriptings in a batch mode if you need to:

  • run a query repeatedly (say, every day or every week), making it a script enables you to avoid retyping it each time you execute it.
  • generate new queries from existing ones that are similar by copying and editing script files.
  • distribute your script to other people so that they can also run the statements

You have several options when using the batch mode as well. For example:

  • if you have a query that produces a lot of output, you can run the output through a pager rather than watching it scroll off the top of your screen:

    1
    shell> mysql < batch-file | more

  • You can catch the output in a file for further processing:

    1
    shell> mysql < batch-file > mysql.out

Note:

  • The output of mysql in batch mode is more concise than the output in a normal interative mysql prompt. For example, output of SELECT DISTINCT species FROM pet looks like this when mysql is run interactively:
1
2
3
4
5
6
7
8
9
+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

In batch mode, the output looks like this instead:

1
2
3
4
5
6
species
bird
cat
dog
hamster
snake

Examples of Common Query

Here, I only list some that I think are relatively less-straightforward than thought. For more examples, please refer to the MySQL documentation.

All examples are based on this table:

1
2
3
4
5
6
7
8
mysql> CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
mysql> INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

After issuing the statements, the table should have the following contents:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM shop ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+

Notice: for each example, there are more than one solution. Here I only picked the ones that I think are easier to understand and remember:

  • For each article, find the dealer or dealers with the most expensive price.

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT s1.article, dealer, s1.price
    FROM shop s1
    JOIN (
      SELECT article, MAX(price) AS price
      FROM shop
      GROUP BY article) AS s2
      ON s1.article = s2.article AND s1.price = s2.price
    ORDER BY article;

    This basically works by first finding the simple MAX(price) for each article in one table, and then JOIN it with the other table (the same table but aliased differently) to display other data such as dealer.

  • Storing Data in a User-Defined Variable

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
    mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
    +---------+--------+-------+
    | article | dealer | price |
    +---------+--------+-------+
    |    0003 | D      |  1.25 |
    |    0004 | D      | 19.95 |
    +---------+--------+-------+

    Properly, the variables would be written in the below format:

    1
    mysql> SET @<var_name> = <expr [, @var_name = expr] ...>

    For example, you would properly write:

    1
    mysql> SET @min_price=(SELECT MIN(price) FROM shop), @max_price=(SELECT MAX(price) FROM shop);

    But as seen in the above example, there is also a shortcut of writting it as:

    1
    mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

    (for more details on user-defined variables, refer to here.)

  • Searching Two Keys in a table

    This mostly happens when you use the keyword OR. And you might use the following to search for two keys:

    1
    2
    SELECT field1_index, field2_index FROM test_table
    WHERE field1_index = '1' OR  field2_index = '1'

    However, it is clearer and efficient by using a UNION that combines the output of two separate SELECT statements. See Section MySQL UNION Clause for more details.

    1
    2
    3
    4
    5
    SELECT field1_index, field2_index
        FROM test_table WHERE field1_index = '1'
    UNION
    SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

  • Counting Frequencies of a Distinct Number in a Table

    This can be done beautifully using the BIT-WISE operators (in this example, using the keyword DISTINCT would not work.)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
           GROUP BY year,month;
    +------+-------+------+
    | year | month | days |
    +------+-------+------+
    | 2000 |     1 |    3 |
    | 2000 |     2 |    2 |
    +------+-------+------+
    2 rows in set (0.00 sec)

    This would:

    • First left shift the binary 1 for number of day times (1<<day). For example, 1<<3 means you get 1000. This means that each day would have 1 at a distinct position.

    • Then, it does a BIT_OR(), which returns a binary number where each position would be 1 if one of the number had a 1 at that position,0 otherwise. For instance, 1000 and 1 would give 1001.

    • Lastly, it counts how many 1 there are in the number with the BIT_COUNT() function. At this point, this would only return the number of distinct days.

      As a result, the query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.

  • Auto-Increment

    The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> CREATE TABLE animals (
         id MEDIUMINT NOT NULL AUTO_INCREMENT,
         name CHAR(30) NOT NULL,
         PRIMARY KEY (id));

    mysql> INSERT INTO animals (name) VALUES
        ('dog'),('cat'),('penguin'),
        ('lax'),('whale'),('ostrich');

    Then if you run:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> SELECT * FROM animals;
    +----+---------+
    | id | name    |
    +----+---------+
    |  1 | dog     |
    |  2 | cat     |
    |  3 | penguin |
    |  4 | lax     |
    |  5 | whale   |
    |  6 | ostrich |
    +----+---------+

    where

    • you see other ways of INSERT INTO:

      1
      2
      INSERT INTO table_name (column1, column2, column3, ...)
      VALUES (value1, value2, value3, ...);

      so that you speicify which columns to insert into with the (column1, column2, column3, ...), meaning that you no longer have to insert into the table sequencially as the original label suggests.

    • a PRIMARY KEY () is used. A primary key is used as a unique identifier to quickly parse data within the database and find the relation between different tables. A relational database cannot have more than one primary key, but it can be based on more than one field (for example, PRIMARY KEY(id, name)).

      A primary key’s main features are:

      • It must contain a unique value for each row of data.
      • It cannot contain null values.
      • Every row must have a primary key value.

      A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence

    • No value was specified for the AUTO_INCREMENT column

      • so MySQL assigned sequence numbers automatically starting from 1. You can only change it after creation with ALTER TABLE animals AUTO_INCREMENT=10;, for example.

When you have AUTO_INCREMENT enabled, you still can explicitly assign other values to the column to generate sequence numbers. However, this will affect future data as well so they also start from that number. For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

Also if you try to explicitly assign 0 or NULL to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. For example:

1
INSERT INTO animals (id,name) VALUES(0,'groundhog');

or 

1
INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

You will obtain:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
| 100 | rabbit    |
| 101 | mouse     |
| 102 | groundhog |
| 103 | squirrel  |
+-----+-----------+

Auto-Increment in MyISAM Tables

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

1
2
3
4
5
6
mysql> CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
1
2
3
4
mysql> INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

Now, if you look at the table:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM animals ORDER BY grp,id;

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+