Khỏe Đẹp Son What is table

Row in a table that contains specific information about a person, product or event

Read "How to Install MySQL and Get Started" on how to install, customize, and get started with MySQL.

Summary of MySQL Commands Used in this Tutorial

For detailed syntax, check MySQL manual "SQL Statement Syntax" @ //dev.mysql.com/doc/refman/5.5/en/sql-syntax.html.

An Example for the Beginners (But NOT for the dummies)

A MySQL database server contains many databases (or schemas). Each database consists of one or more tables. A table is made up of columns (or fields) and rows (records).

The SQL keywords and commands are NOT case-sensitive. For clarity, they are shown in uppercase. The names or identifiers (database names, table names, column names, etc.) are case-sensitive in some systems, but not in other systems. Hence, it is best to treat identifiers as case-sensitive.

SHOW DATABASES

The databases "mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)7", "mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)8" and "mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)9" are system databases used internally by MySQL. A "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+0" database is provided during installation for your testing.

Let us begin with a simple example - a product sales database. A product sales database typically consists of many tables, e.g., products, customers, suppliers, orders, payments, employees, among others. Let's call our database "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1" (inspired from Microsoft's Northwind Trader sample database). We shall begin with the first table called "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2" with the following columns (having data types as indicated) and rows:

Database: southwind
Table: productsproductID
INTproductCode
CHAR(3)name
VARCHAR(30)quantity
INTprice
DECIMAL(10,2)1001PENPen Red50001.231002PENPen Blue80001.251003PENPen Black20001.251004PECPencil 2B100000.481005PECPencil 2H80000.49

Creating and Deleting a Database - CREATE DATABASE and DROP DATABASE

You can create a new database using SQL command "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+3"; and delete a database using "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+4". You could optionally apply condition "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+5" or "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+6" to these commands. For example,

IMPORTANT: Use SQL mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+7 (and mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8) commands with extreme care, as the deleted entities are irrecoverable. THERE IS NO UNDO!!!

SHOW CREATE DATABASE

The mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+9 commands uses some defaults. You can issue a "mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+0" to display the full command and check these default values. We use mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+1 (instead of mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+2) to display the results vertically. (Try comparing the outputs produced by mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+2 and mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+1.)

Unquoted names or identifiers (such as database name, table name and column name) cannot contain blank and special characters, or crash with MySQL keywords (such as mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+5 and mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+6). You can include blanks and special characters or use MySQL keyword as identifier by enclosing it with a pair of back-quote, in the form of mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+7.

For robustness, the mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+8command back-quotes all the identifiers, as illustrated in the above example.

Comments and Version Comments

MySQL multi-line comments are enclosed within mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+9 and mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+0; end-of-line comments begins with mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+1 (followed by a space) or mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+2.

The mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+3 is known as version comment, which will only be run if the server is at or above this version number mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+4. To check the version of your MySQL server, issue query "mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+5".

Setting the Default Database - USE

The command "mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+6" sets a particular database as the default (or current) database. You can reference a table in the default database using mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+7 directly. But you need to use the fully-qualified mysql> SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+8 to reference a table NOT in the default database.

In our example, we have a database named "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1" with a table named "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2". If we issue "mysql> SELECT * FROM products WHERE productCode IS NULL; Empty set (0.00 sec)1" to set mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1 as the default database, we can simply call the table as "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2". Otherwise, we need to reference the table as "mysql> SELECT * FROM products WHERE productCode IS NULL; Empty set (0.00 sec)4".

To display the current default database, issue command "mysql> SELECT * FROM products WHERE productCode IS NULL; Empty set (0.00 sec)5".

Creating and Deleting a Table - CREATE TABLE and DROP TABLE

You can create a new table in the default database using command "mysql> SELECT * FROM products WHERE productCode IS NULL; Empty set (0.00 sec)6" and "mysql> SELECT * FROM products WHERE productCode IS NULL; Empty set (0.00 sec)7". You can also apply condition "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+5" or "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+6". To create a table, you need to define all its columns, by providing the columns' name, type, and attributes.

Let's create a table "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2" in our database "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1".

Explanations

We define 5 columns in the table mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2: SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3, SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 4, SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 5, SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 6 and SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 7. The types are:

SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 isSELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 9 - non-negative integers.
SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 4 is mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)01 - a fixed-length alphanumeric string of 3 characters.
SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 5 is mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)03 - a variable-length string of up to 30 characters.
We use fixed-length string for SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 4, as we assume that the SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 4 contains exactly 3 characters. On the other hand, we use variable-length string for SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 5, as its length varies - mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)07 is more efficient than mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)08.
SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 6 is also SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 9 (non-negative integers).
SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 7 ismysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)12- a decimal number with 2 decimal places.
mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)13 is precise (represented as integer with a fix decimal point). On the other hand, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)14 and mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)15 (real numbers) are not precise and are approximated. mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)13 type is recommended for currency.

The attribute "mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)17" specifies that the column cannot contain the mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18 value. mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18 is a special value indicating "no value", "unknown value" or "missing value". In our case, these columns shall have a proper value. We also set the default value of the columns. The column will take on its default value, if no value is specified during the record creation.

We set the column SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 as the so-called primary key. Values of the primary-key column must be unique. Every table shall contain a primary key. This ensures that every row can be distinguished from other rows. You can specify a single column or a set of columns (e.g., mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)21 and mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)22) as the primary key. An index is build automatically on the primary-key column to facilitate fast search. Primary key is also used as reference by other tables.

We set the column SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 to mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)24. with default starting value of 1. When you insert a row with mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18 (recommended) (or 0, or a missing value) for the mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)24 column, the maximum value of that column plus 1 would be inserted. You can also insert a valid value to an mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)24 column, bypassing the auto-increment.

Inserting Rows - INSERT INTO

Let's fill up our "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2" table with rows. We set the SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 of the first record to 1001, and use mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)24 for the rest of records by inserting a mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18, or with a missing column value. Take note that strings must be enclosed with a pair of single quotes (or double quotes).

INSERT INTO Syntax

We can use the mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)32 statement to insert a new row with all the column values, using the following syntax:

You need to list the values in the same order in which the columns are defined in the mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)33, separated by commas. For columns of string data type (mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)08, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)07), enclosed the value with a pair of single quotes (or double quotes). For columns of numeric data type (mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)36, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)13, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)14, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)15), simply place the number.

You can also insert multiple rows in one mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)32 statement:

INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...

To insert a row with values on selected columns only, use:

The remaining columns will receive their default value, such as mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)24, default, or mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18.

Querying the Database - SELECT

The most common, important and complex task is to query a database for a subset of data that meets your needs - with the mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 command. The mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 command has the following syntax:

For examples,

SELECT without Table

You can also issue mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 without a table. For example, you can mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 an expression or evaluate a built-in function.

Comparison Operators

For numbers (mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)36, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)13, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)14), you could use comparison operators: mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)50 (equal to), mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)51 or mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)52 (not equal to), mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)53 (greater than), mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)54 (less than), mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)55 (greater than or equal to), mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)56 (less than or equal to), to compare two numbers. For example, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)57, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)58.

CAUTION: Do not compare mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)14s (real numbers) for equality (mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)50 or mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)51), as they are not precise. On the other hand, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)13 are precise.

For strings, you could also use mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)50, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)51, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)53, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)54, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)55, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)56 to compare two strings (e.g., mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)69 The ordering of string depends on the so-called collation chosen. For example,

String Pattern Matching - LIKE and NOT LIKE

For strings, in addition to full matching using operators like mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)50 and mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)51, we can perform pattern matching using operator mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)72 (or mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)73) with wildcard characters. The wildcard mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)74 matches any single character; mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)75 matches any number of characters (including zero). For example,

mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)76 matches strings beginning with mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)77;
mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)78 matches strings ending with mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)79;
mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)80 matches strings containing mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)81;
mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)82 matches strings containing exactly three characters; and
mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)83 matches strings beginning with mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)84, followed by any single character, followed by mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)85, followed by zero or more characters.

MySQL also support regular expression matching via the mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)86 operator.

Arithmetic Operators

You can perform arithmetic operations on numeric fields using arithmetic operators, as tabulated below:

OperatorDescription+Addition-Subtraction*Multiplication/DivisionDIVInteger Division%Modulus (Remainder)Logical Operators - AND, OR, NOT, XOR

You can combine multiple conditions with boolean operators mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)87, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)88, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)89. You can also invert a condition using operator mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)90. For examples,

You can select from members of a set with mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)91 (or mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)92) operator. This is easier and clearer than the equivalent mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)93 expression.

mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+BETWEEN, NOT BETWEEN

To check if the value is within a range, you could use mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)94 operator. Again, this is easier and clearer than the equivalent mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)93 expression.

mysql> SELECT * FROM products WHERE productCode IS NULL; Empty set (0.00 sec)

Using comparison operator (such as mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */00 or mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */01) to check for mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18 is a mistake - a very common mistake. For example,

ORDER BY Clause

You can order the rows selected using mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */03 clause, with the following syntax:

SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...

The selected row will be ordered according to the values in mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */04, in either ascending (mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */05) (default) or descending (mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1003 | PEN | Pen Black | 2000 | 1.25 | +-----------+-------------+-----------+----------+-------+6) order. If several rows have the same value in mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */04, it will be ordered according to mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */08, and so on. For strings, the ordering could be case-sensitive or case-insensitive, depending on the so-called character collating sequence used. For examples,

You can randomize the returned records via function mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */09, e.g.,

A mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 query on a large database may produce many rows. You could use the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */11 clause to limit the number of rows displayed, e.g.,

To continue to the following records , you could specify the number of rows to be skipped, followed by the number of rows to be displayed in the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */11 clause, as follows:

AS - Alias

You could use the keyword mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */13 to define an alias for an identifier (such as column name, table name). The alias will be used in displaying the name. It can also be used as reference. For example,

Take note that the identifier "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */14" contains a blank and must be back-quoted.

Function CONCAT()

You can also concatenate a few columns as one (e.g., joining the last name and first name) using function mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */15. For example,

Producing Summary Reports

To produce a summary report, we often need to aggregate related rows.

DISTINCT

A column may have duplicate values, we could use keyword mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */16 to select only distinct values. We can also apply mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */16 to several columns to select distinct combinations of these columns. For examples,

GROUP BY Clause

The mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */18 clause allows you to collapse multiple records with a common value into groups. For example,

mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */18 by itself is not meaningful. It is used together with mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */18 aggregate functions (such as mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */21, mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */22, mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */23) to produce group summary.

GROUP BY Aggregate Functions: COUNT, MAX, MIN, AVG, SUM, STD, GROUP_CONCAT

We can apply mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */18 Aggregate functions to each group to produce group summary report.

The function mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */25 returns the rows selected; mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */26 counts only the non-mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18 values of the given column. For example,

Besides mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */21, there are many other mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */18 aggregate functions such as mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */22, mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */31, mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */32 and mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */23. For example,

HAVING clause

mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */34 is similar to mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */35, but it can operate on the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */18 aggregate functions; whereas mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */35 operates only on columns.

WITH ROLLUP

The mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */38 clause shows the summary of group summary, e.g.,

Modifying Data - UPDATE

To modify existing data, use mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */39 command, with the following syntax:

For example,

CAUTION: If the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */35 clause is omitted in the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41 command, ALL ROWS will be updated. Hence, it is a good practice to issue a mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 query, using the same criteria, to check the result set before issuing the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41. This also applies to the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 statement in the following section.

Deleting Rows - DELETE FROM

Use the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */45 command to delete row(s) from a table, with the following syntax:

For example,

Beware that mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */46" without a mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */35 clause deletes ALL records from the table. Even with a mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */35 clause, you might have deleted some records unintentionally. It is always advisable to issue a mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 command with the same mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */35 clause to check the result set before issuing the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 (and mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41).

Loading/Exporting Data from/to a Text File

There are several ways to add data into the database: (a) manually issue the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */53 commands; (b) run the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */53 commands from a script; or (c) load raw data from a file using mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */55 or via mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */56 utility.

LOAD DATA LOCAL INFILE ... INTO TABLE ...

Besides using mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */53 commands to insert rows, you could keep your raw data in a text file, and load them into the table via the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */55 command. For example, use a text editor to CREATE a NEW FILE called "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */59", under "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */60" (for Windows) or "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */61" (for Mac), containing the following records, where the values are separated by mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */62. The file extension of "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */63" stands for Comma-Separated Values text file.

You can load the raw data into the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table as follows:

Notes:

You need to provide the path (absolute or relative) and the filename. Use Unix-style forward-slash mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */65 as the directory separator, instead of Windows-style back-slash mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */66.
The default line delimiter (or end-of-line) is mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */67 (Unix-style). If the text file is prepared in Windows, you need to include mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */68.
The default column delimiter is "tab" (in a so-called TSV file - Tab-Separated Values). If you use another delimiter, e.g. mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */62, include mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */70.
You need to use mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */71 for mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18.

mysqlimport Utility Program

You can also use the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */56 utility program to load data from a text file.

Complimenting mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */55 command, you can use mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */75 to export data from a table to a text file. For example,

Running a SQL Script

Instead of manually entering each of the SQL statements, you can keep many SQL statements in a text file, called SQL script, and run the script. For example, use a programming text editor to prepare the following script and save as "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */76" under "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */60" (for Windows) or "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */61" (for Mac).

You can run the script either:

via the "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */79" command in a MySQL client. For example, to restore the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1 backup earlier:
via the "batch mode" of the mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)7 client program, by re-directing the input from the script:mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)8

More Than One Tables

Our example so far involves only one table "mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2". A practical database contains many related tables.

Products have suppliers. If each product has one supplier, and each supplier supplies only one product (known as one-to-one relationship), we can simply add the supplier's data (name, address, phone number) into the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table. Suppose that each product has one supplier, and a supplier may supply zero or more products (known as one-to-many relationship). Putting the supplier's data into the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table results in duplication of data. This is because one supplier may supply many products, hence, the same supplier's data appear in many rows. This not only wastes the storage but also easily leads to inconsistency (as all duplicate data must be updated simultaneously). The situation is even more complicated if one product has many suppliers, and each supplier can supply many products, in a many-to-many relationship.

One-To-Many Relationship

Suppose that each product has one supplier, and each supplier supplies one or more products. We could create a table called mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 to store suppliers' data (e.g., name, address and phone number). We create a column with unique value called mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 to identify every suppliers. We set mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 as the primary key for the table mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 (to ensure uniqueness and facilitate fast search).

To relate the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table to the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table, we add a new column into the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table - the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86. We then set the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 column of the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table as a foreign key references the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 column of the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table to ensure the so-called referential integrity.

Database: southwind
Table: supplierssupplierID
INTname
VARCHAR(3)phone
CHAR(8)501ABC Traders88881111502XYZ Company88882222503QQ Corp88883333
Database: southwind
Table: productsproductID
INTproductCode
CHAR(3)name
VARCHAR(30)quantity
INTprice
DECIMAL(10,2)supplierID
INT
(Foreign Key)2001PECPencil 3B5000.525012002PECPencil 4B2000.625012003PECPencil 5B1000.735012004PECPencil 6B5000.47502

We need to first create the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table, because the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table references the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table. The mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table is known as the parent table; while the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table is known as the child table in this relationship.

Instead of deleting and re-creating the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table, we shall use "INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...03" to add a new column mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 into the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table.

Next, we shall add a foreign key constraint on the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 columns of the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 child table to the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 parent table, to ensure that every mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 in the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table always refers to a valid mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 in the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table - this is called referential integrity.

Before we can add the foreign key, we need to set the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 of the existing records in the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table to a valid mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 in the table (say INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...16).

SELECT with JOIN

mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 command can be used to query and join data from two related tables. For example, to list the product's SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 5 (in mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table) and supplier's SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 5 (in mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table), we could join the two table via the two common mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 columns:

In the above query result, two of the columns have the same heading "SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 5". We could create aliases for headings.

The database diagram is as illustrated. The link indicates a one-to-many relationship between mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 and mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85.

Many-To-Many Relationship

Suppose that a product has many suppliers; and a supplier supplies many products in a so-called many-to-many relationship. The above solution breaks. You cannot include the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 in the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table, as you cannot determine the number of suppliers, and hence, the number of columns needed for the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86s. Similarly, you cannot include the SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 in the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table, as you cannot determine the number of products.

To resolve this problem, you need to create a new table, known as a junction table (or joint table), to provide the linkage. Let's call the junction table INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...31, as illustrated.

Database: southwind
Table: products_suppliersproductID
INT
(Foreign Key)supplierID
INT
(Foreign Key)20015012002501200350120045022001503
Database: southwind
Table: supplierssupplierID
INTname
VARCHAR(30)phone
CHAR(8)501ABC Traders88881111502XYZ Company88882222503QQ Corp88883333
Database: southwind
Table: productsproductID
INTproductCode
CHAR(3)name
VARCHAR(30)quantity
INTprice
DECIMAL(10,2)2001PECPencil 3B5000.522002PECPencil 4B2000.622003PECPencil 5B1000.732004PECPencil 6B5000.47

Let's create the INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...31 table. The primary key of the table consists of two columns: SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 and mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86, as their combination uniquely identifies each rows. This primary key is defined to ensure uniqueness. Two foreign keys are defined to set the constraint to the two parent tables.

Next, remove the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 column from the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table. (This column was added to establish the one-to-many relationship. It is no longer needed in the many-to-many relationship.)

Before this column can be removed, you need to remove the foreign key that builds on this column. To remove a key in MySQL, you need to know its constraint name, which was generated by the system. To find the constraint name, issue a "INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...37" and take note of the foreign key's constraint name in the clause "INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...38". You can then drop the foreign key using "INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...39"

Now, we can remove the column redundant mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */86 column.

Similarly, we can use mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 with INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...42 to query data from the 3 tables, for examples,

The database diagram is as follows. Both mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 and mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 tables exhibit a one-to-many relationship to the junction table. The many-to-many relationship is supported via the junction table.

One-to-one Relationship

Suppose that some products have optional data (e.g., photo, comment). Instead of keeping these optional data in the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table, it is more efficient to create another table called INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...46s, and link it to mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 with a one-to-one relationship, as illustrated.

Backup and Restore

Backup: Before we conclude this example, let's run the INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...48 utility program to dump out (backup) the entire mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1 database.

Study the output file, which contains mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+9, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)33 and mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */53 statements to re-create the tables dumped.

The SYNTAX for the INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...48 utility program is as follows:

Restore: The utility INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...48 produces a SQL script (consisting of mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)33 and mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */53 commands to re-create the tables and loading their data). You can restore from the backup by running the script either:

via the "mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */79" command in an interactive client. For example, to restore the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1 backup earlier:
via the "batch mode" of the mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)7 client program by re-directing the input from the script:

More on Primary Key, Foreign Key and Index

Primary Key

In the relational model, a table shall not contain duplicate rows, because that would create ambiguity in retrieval. To ensure uniqueness, each table should have a column (or a set of columns), called primary key, that uniquely identifies every record of the table. For example, an unique number INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...60 can be used as the primary key for the INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...61 table; SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 4 for mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table; INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...64 for INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...65 table. A primary key is called a simple key if it is a single column; it is called a composite key if it is made up of several columns. Most RDBMSs build an index on the primary key to facilitate fast search. The primary key is often used to relate to other tables.

Foreign Key

A foreign key of a child table is used to reference the parent table. Foreign key constraint can be imposed to ensure so-called referential integrity - values in the child table must be valid values in the parent table.

We define the foreign key when defining the child table, which references a parent table, as follows:

You can specify the reference action for mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41 and mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 via the optional INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...68 and INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...69 clauses:

INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...70 (default): disallow mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 or mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41 of the parent's row, if there are matching rows in child table.
INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...73: cascade the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 or mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41 action to the matching rows in the child table.
INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...76: set the foreign key value in the child table to mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18 (if mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18 is allowed).
INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...79: a SQL term which means no action on the parent's row. Same as INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...70 in MySQL, which disallows mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 or mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41 (do nothing).

Try deleting a record in the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 (parent) table that is referenced by INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...31 (child) table, e.g.,

The record cannot be deleted as the default "INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...85" constraint was imposed.

Indexes (or Keys)

Indexes (or Keys) can be created on selected column(s) to facilitate fast search. Without index, a "INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...86" needs to match with the SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 column of all the records in the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 table. If SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ... 3 column is indexed (e.g., using a binary tree), the matching can be greatly improved (via the binary tree search).

You should index columns which are frequently used in the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */35 clause; and as INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...42 columns.

The drawback about indexing is cost and space. Building and maintaining indexes require computations and memory spaces. Indexes facilitate fast search but deplete the performance on modifying the table (INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...92), and need to be justified. Nevertheless, relational databases are typically optimized for queries and retrievals, but NOT for updates.

In MySQL, the keyword INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...93 is synonym to INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...94.

In MySQL, indexes can be built on:

a single column (column-index)
a set of columns (concatenated-index)
on unique-value column (INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...95 or INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...96)
on a prefix of a column for strings (mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)07 or mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)08), e.g., first 5 characters.

There can be more than one indexes in a table. Index are automatically built on the primary-key column(s).

You can build index via mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)33, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)00 or INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...03.

More SQL

Sub-Query

Results of one query can be used in another SQL statement. Subquery is useful if more than one tables are involved.

SELECT with Subquery

In the previous many-to-many product sales example, how to find the suppliers that do not supply any product? You can query for the suppliers that supply at least one product in the INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...31 table, and then query the mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */85 table for those that are not in the previous result set.

Can you do this without sub-query?

A subquery may return a scalar, a single column, a single row, or a table. You can use comparison operator (e.g., mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)50, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)53) on scalar, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)91 or mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)92 for single row or column, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)08 or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)09 to test for empty set.

INSERT|UPDATE|DELETE with Subquery

You can also use a subquery with other SQL statements such as mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */53, mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8, or mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */41. For example,

Working with Date and Time

With date/time data types, you can sort the results by date, search for a particular date or a range of dates, calculate the difference between dates, compute a new date by adding/subtracting an interval from a given date.

Date By Example

Date/Time Functions

MySQL provides these built-in functions for getting the current date, time and datetime:

mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)19: returns the current date and time in the format of mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)20.
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)21 (or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)22, or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)23): returns the current date in the format of mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)24.
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)25 (or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)26, or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)27): returns the current time in the format of mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)28.

For examples,

MySQL provides these date/time data types:

mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)29: stores both date and time in the format of mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)20. The valid range is mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)31 to mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)32. You can set a value using the valid format (e.g., mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)33). You could also apply functions mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)19 or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)21 (time will be set to mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)36), but not mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)25.
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)38: stores date only in the format of mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)24. The range is mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)40 to mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)41. You could apply mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)21 or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)19 (the time discarded) on this field.
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)44: stores time only in the format of mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)28. You could apply mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)25 or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)19 (the date discarded) for this field.
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)48: in mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)49 or mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)50. The range of years is 1901 to 2155. Use mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)51 type for year outside this range. You could apply mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)21 to this field (month and day discarded).

mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)53: similar to mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)29 but stored the number of seconds since January 1, 1970 UTC (Unix-style). The range is mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)55 to mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)56.
The differences between mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)29 and mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)58 are:
1. the range,
2. support for time zone,
3. mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)58 column could be declared with mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)60 to set the default value to the current date/time. (All other data types' default, including mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)29, must be a constant and not a function return value). You can also declare a mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)58 column with "mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)63" to capture the timestamp of the last update.

More Date/Time Functions

Reference: MySQL's "Date and Time Functions" @ //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html.

There are many date/time functions:

Extracting part of a date/time: mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)69, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)70, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)71, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)72, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)73, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)74, e.g.,mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */8
Extracting information: mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)75 (e.g., mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)76), mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)77 (e.g., mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)78), mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)79 (1=Sunday, …, 7=Saturday), mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)80 (1-366), ...mysql> CREATE DATABASE IF NOT EXISTS southwind; mysql> SHOW CREATE DATABASE southwind \G *************************** 1. row *************************** Database: southwind Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */9
Computing another date/time: mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)81, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)82, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)83, e.g.,INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...0
Computing interval: mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)84, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)85, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)86, e.g.,INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...1
Representation: mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)87 (days since year 0), mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)88, e.g.,INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...2
Formatting: mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)89, e.g.,

Create a table with various date/time columns. Only the mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)58 column can have the mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)60 and mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)63.INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...3Notes:
- Don't use mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)93 anymore.
- From MySQL 5.7, the supported range for datetime is mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)31 to mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)32.
Insert values manually using string literals.INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...4
Checking the on-update for mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)96.INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...5
Insert values using MySQL built-in functions mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)97, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)98, mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)99.INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...6
Insert invalid or out-of-range values. MySQL replaces with all zeros.INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...7Note: Might not work in MySQL 5.7?!
An useful built-in function mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+00 can be used to compute a future date, e.g.,INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...8

View

A view is a virtual table that contains no physical data. It provide an alternative way to look at the data.

INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...9

Transactions

A atomic transaction is a set of SQL statements that either ALL succeed or ALL fail. Transaction is important to ensure that there is no partial update to the database, given an atomic of SQL statements. Transactions are carried out via mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+01 and mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+02.

If you start another mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, quantity FROM products WHERE quantity <= 2000; +-----------+----------+ | name | quantity | +-----------+----------+ | Pen Black | 2000 | +-----------+----------+ 1 row in set (0.00 sec)7 client and do a mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 during the transaction (before the commit or rollback), you will not see the changes.

Alternatively, you can also disable the so-called mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+05 mode, which is set by default and commit every single SQL statement.

A transaction groups a set of operations into a unit that meets the ACID test:

Atomicity: If all the operations succeed, changes are committed to the database. If any of the operations fails, the entire transaction is rolled back, and no change is made to the database. In other words, there is no partial update.
Consistency: A transaction transform the database from one consistent state to another consistent state.
Isolation: Changes to a transaction are not visible to another transaction until they are committed.
Durability: Committed changes are durable and never lost.

User Variables

In MySQL, you can define user variables via:

mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+06 in a mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)43 command, or
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+08 or mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+09 command.

For examples,

More on JOIN

INNER JOIN

In an inner join of two tables, each row of the first table is combined (joined) with every row of second table. Suppose that there are n1 rows in the first table and n2 rows in the second table, mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+10 produces all combinations of n1×n2 rows - it is known as Cartesian Product or Cross Product.

You can impose constrain by using the mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+11 clause, for example,

Take note that the following are equivalent:

OUTER JOIN - LEFT JOIN and RIGHT JOIN

mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+10 with constrain (mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+11 or mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+14) produces rows that are found in both tables. On the other hand, mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+15 can produce rows that are in one table, but not in another table. There are two kinds of mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+15s: mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+17 produces rows that are in the left table, but may not in the right table; whereas mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+18 produces rows that are in the right table but may not in the left table.

In a mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+17, when a row in the left table does not match with the right table, it is still selected but by combining with a "fake" record of all mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) mysql> DROP DATABASE southwind; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE IF NOT EXISTS southwind; Query OK, 1 row affected (0.01 sec) mysql> DROP DATABASE IF EXISTS southwind; Query OK, 0 rows affected (0.00 sec)18s for the right table.

As the result, mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+17 ensures that the result set contains every row on the left table. This is important, as in some queries, you are interested to have result on every row on the left table, with no match in the right table, e.g., searching for items without supplier. For example,

Take note that the followings are equivalent:

Exercises

Rental System

Peter runs a small car rental company with 10 cars and 5 trucks. He engages you to design a web portal to put his operation online.

For the initial phase, the web portal shall provide these basic functions:

Maintaining the records of the vehicles and customers.
Inquiring about the availability of vehicle, and
Reserving a vehicle for rental.

A customer record contains his/her name, address and phone number.

A vehicle, identified by the vehicle registration number, can be rented on a daily basis. The rental rate is different for different vehicles. There is a discount of 20% for rental of 7 days or more.

A customer can rental a vehicle from a start date to an end date. A special customer discount, ranging from 0-50%, can be given to preferred customers.

Database

The initial database contains 3 tables: mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+22, INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...61, and mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+24. The mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+24 is a junction table supporting many-to-many relationship between mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %'); +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+22 and INSERT INTO tableName VALUES (row1FirstColumnValue, ..., row1lastColumnValue), (row2FirstColumnValue, ..., row2lastColumnValue), ...61.

Is a row in a table that contains specific information about a given person place things in database?

The correct answer is Field.

What is the row in a table called?

Each row of a table is called a data record.

What is a row of information called in a database?

Each row in a table is referred to as a record. Records are where the individual pieces of information are stored. Each record consists of one or more fields. Fields correspond to the columns in the table.

Which of the following contains information about a given person product or event?

CCC2 Final Exam Flash Cards.

Summary of MySQL Commands Used in this Tutorial

An Example for the Beginners (But NOT for the dummies)

Creating and Deleting a Database - CREATE DATABASE and DROP DATABASE

Setting the Default Database - USE

Creating and Deleting a Table - CREATE TABLE and DROP TABLE

Inserting Rows - INSERT INTO

Querying the Database - SELECT

Producing Summary Reports

Modifying Data - UPDATE

Deleting Rows - DELETE FROM

Loading/Exporting Data from/to a Text File

Running a SQL Script

More Than One Tables

One-To-Many Relationship

Many-To-Many Relationship

One-to-one Relationship

Backup and Restore

More on Primary Key, Foreign Key and Index

Primary Key

Foreign Key

Indexes (or Keys)

More SQL

Sub-Query

Working with Date and Time

View

Transactions

User Variables

More on JOIN

INNER JOIN

OUTER JOIN - LEFT JOIN and RIGHT JOIN

Exercises

Rental System

Is a row in a table that contains specific information about a given person place things in database?

What is the row in a table called?

What is a row of information called in a database?

Which of the following contains information about a given person product or event?

Bài Viết Liên Quan

Toplist

Bài mới nhất

Chủ đề