Subscripted assignment into large table variables is significantly faster. Performance is now essentially constant with the number of elements in each table variable.
For example, when you use dot indexing to assign elements to a variable with 106 elements, performance in R2019b is approximately 40x times faster, as shown below.
function timingTest[] t = table[zeros[1e6,1]]; indices = randi[1e6,1,10000]; tic; for i = indices t.Var1[i] = rand; end toc end
The approximate execution times are:
R2019a: 47.83 s
R2019b: 1.20 s
Similarly, assignment using curly braces is faster. For example, when you assign into three table variables with 106 elements, performance in R2019b is approximately 18x faster.
function timingTest[] t = table[zeros[1e6,1], ones[1e6,1], nan[1e6,1]]; indices = randi[1e6,1,10000]; tic; for i = indices t{i,:} = rand; end toc end
The approximate execution times are:
R2019a: 156.39 s
R2019b: 8.51 s
The code was timed on a Windows 10 system with a 3.6 GHz Intel Xeon W-2133 CPU by calling each version of the timingTest
function.
The larger the table variables are, the greater the performance improvement becomes. However, the performance improvement occurs only when you make table subscripted assignments within a function. There is no improvement when subscripting into tables at the command line, or within try-catch blocks.
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 DATABASESYou can 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 SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
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 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 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 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,
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]
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.]
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 */Back-Quoted Identifiers [`name`]
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 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]
01 - a fixed-length alphanumeric string of 3 characters.SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...
5 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]
03 - a variable-length string of up to 30 characters.
We use fixed-length string forSELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...
4, as we assume that theSELECT ... 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 forSELECT ... 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 thanmysql> 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 alsoSELECT ... 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 andmysql> 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 TableYou 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.
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 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 withmysql> 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 withmysql> 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 containingmysql> 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; andmysql> 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 withmysql> 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 bymysql> 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, XORYou 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,
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 | +-----------+-------------+-----------+----------+-------+IN, NOT IN
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 [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 | +-----------+-------------+-----------+----------+-------+IS NULL, IS NOT NULL
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, which represent "no value", "missing value" or "unknown value". You can checking if a column contains
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 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]98 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]99. For example,
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.,
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]0LIMIT Clause
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,
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]1
Producing Summary Reports
To produce a summary report, we often need to aggregate related rows.
DISTINCTA 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:
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]2
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.
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]3
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:
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]4
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-slashmysql> 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 includemysql> 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, includemysql> 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 formysql> 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.
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.
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]5SELECT ... INTO OUTFILE ...
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,
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]6
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].
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]7
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 themysql> 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 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.
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]9ALTER TABLE
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.
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 */0
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"
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 */1
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.
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 */2Querying
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.
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 */3
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.
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 */4
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:
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 */5
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 themysql> 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 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 forINSERT 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 andmysql> 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 optionalINSERT INTO tableName VALUES [row1FirstColumnValue, ..., row1lastColumnValue], [row2FirstColumnValue, ..., row2lastColumnValue], ...
68 andINSERT INTO tableName VALUES [row1FirstColumnValue, ..., row1lastColumnValue], [row2FirstColumnValue, ..., row2lastColumnValue], ...
69 clauses:INSERT INTO tableName VALUES [row1FirstColumnValue, ..., row1lastColumnValue], [row2FirstColumnValue, ..., row2lastColumnValue], ...
70 [default]: disallowmysql> 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 ormysql> 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 themysql> 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 ormysql> 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 tomysql> 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 [ifmysql> 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 asINSERT INTO tableName VALUES [row1FirstColumnValue, ..., row1lastColumnValue], [row2FirstColumnValue, ..., row2lastColumnValue], ...
70 in MySQL, which disallowsmysql> 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 ormysql> 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 byINSERT 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 theSELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...
3 column of all the records in themysql> 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. IfSELECT ... 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 asINSERT 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 toINSERT 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 orINSERT 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 ormysql> 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 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.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 */
6Can 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 ormysql> 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 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 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, ormysql> 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
Date and time are of particular interest for database applications. This is because business records often carry date/time information [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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 amysql> 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, ormysql> 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 ormysql> 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,
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 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,
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 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.
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 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,
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 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.