untung99play.xyz: MySQL By Examples for Beginners

Untung99 menawarkan beragam permainan yang menarik, termasuk slot online, poker, roulette, blackjack, dan taruhan olahraga langsung. Dengan koleksi permainan yang lengkap dan terus diperbarui, pemain memiliki banyak pilihan untuk menjaga kegembiraan mereka. Selain itu, Untung99 juga menyediakan bonus dan promosi menarik yang meningkatkan peluang kemenangan dan memberikan nilai tambah kepada pemain.

Berikut adalah artikel atau berita tentang Harian untung99play.xyz dengan judul untung99play.xyz: MySQL By Examples for Beginners yang telah tayang di untung99play.xyz terimakasih telah menyimak. Bila ada masukan atau komplain mengenai artikel berikut silahkan hubungi email kami di [email protected], Terimakasih.

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” @ http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html.

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

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

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

SHOW DATABASES

You can use SHOW DATABASES to list all the existing databases in the server.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
........

The databases “mysql“, “information_schema” and “performance_schema” are system databases used internally by MySQL. A “test” 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 “southwind” (inspired from Microsoft’s Northwind Trader sample database). We shall begin with the first table called “products” with the following columns (having data types as indicated) and rows:

Creating and Deleting a Database – CREATE DATABASE and DROP DATABASE

You can create a new database using SQL command “CREATE DATABASE databaseName“; and delete a database using “DROP DATABASE databaseName“. You could optionally apply condition “IF EXISTS” or “IF NOT EXISTS” 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 DROP (and DELETE) commands with extreme care, as the deleted entities are irrecoverable. THERE IS NO UNDO!!!

SHOW CREATE DATABASE

The CREATE DATABASE commands uses some defaults. You can issue a “SHOW CREATE DATABASE databaseName” to display the full command and check these default values. We use \G (instead of ';') to display the results vertically. (Try comparing the outputs produced by ';' and \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 */

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 ORDER and DESC). 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 `name`.

For robustness, the SHOW command back-quotes all the identifiers, as illustrated in the above example.

Comments and Version Comments

MySQL multi-line comments are enclosed within /* and */; end-of-line comments begins with -- (followed by a space) or #.

The /*!40100 ...... */ is known as version comment, which will only be run if the server is at or above this version number 4.01.00. To check the version of your MySQL server, issue query “SELECT version()“.

Setting the Default Database – USE

The command “USE databaseName” sets a particular database as the default (or current) database. You can reference a table in the default database using tableName directly. But you need to use the fully-qualified databaseName.tableName to reference a table NOT in the default database.

In our example, we have a database named “southwind” with a table named “products“. If we issue “USE southwind” to set southwind as the default database, we can simply call the table as “products“. Otherwise, we need to reference the table as “southwind.products“.

To display the current default database, issue command “SELECT DATABASE()“.

Creating and Deleting a Table – CREATE TABLE and DROP TABLE

You can create a new table in the default database using command “CREATE TABLE tableName” and “DROP TABLE tableName“. You can also apply condition “IF EXISTS” or “IF NOT EXISTS“. To create a table, you need to define all its columns, by providing the columns’ name, type, and attributes.

Let’s create a table “products” in our database “southwind“.

Explanations

We define 5 columns in the table products: productID, productCode, name, quantity and price. The types are:

• productID is INT UNSIGNED – non-negative integers.
• productCode is CHAR(3) – a fixed-length alphanumeric string of 3 characters.
• name is VARCHAR(30) – a variable-length string of up to 30 characters.
  We use fixed-length string for productCode, as we assume that the productCode contains exactly 3 characters. On the other hand, we use variable-length string for name, as its length varies – VARCHAR is more efficient than CHAR.
  
• quantity is also INT UNSIGNED (non-negative integers).
• price is DECIMAL(10,2) – a decimal number with 2 decimal places.
  DECIMAL is precise (represented as integer with a fix decimal point). On the other hand, FLOAT and DOUBLE (real numbers) are not precise and are approximated. DECIMAL type is recommended for currency.
  

The attribute “NOT NULL” specifies that the column cannot contain the NULL value. NULL 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 productID 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., firstName and lastName) 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 productID to AUTO_INCREMENT.
with default starting value of 1. When you insert a row with NULL (recommended) (or 0, or a missing value) for the AUTO_INCREMENT column, the maximum value of that column plus 1 would be inserted. You can also insert a valid value to an AUTO_INCREMENT column, bypassing the auto-increment.

Inserting Rows – INSERT INTO

Let’s fill up our “products” table with rows. We set the productID of the first record to 1001, and use AUTO_INCREMENT for the rest of records by inserting a NULL, 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 INSERT INTO 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 CREATE TABLE, separated by commas. For columns of string data type (CHAR, VARCHAR), enclosed the value with a pair of single quotes (or double quotes). For columns of numeric data type (INT, DECIMAL, FLOAT, DOUBLE), simply place the number.

You can also insert multiple rows in one INSERT INTO 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 AUTO_INCREMENT, default, or NULL.

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 SELECT command. The SELECT command has the following syntax:

For examples,

SELECT without Table

You can also issue SELECT without a table. For example, you can SELECT an expression or evaluate a built-in function.

Comparison Operators

For numbers (INT, DECIMAL, FLOAT), you could use comparison operators: '=' (equal to), '<>' or '!=' (not equal to), '>' (greater than), '<'<> (less than), '>=' (greater than or equal to), '<='<> (less than or equal to), to compare two numbers. For example, price > 1.0, quantity <=>.

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 <=>;
+-----------+----------+
| name      | quantity |
+-----------+----------+
| Pen Black |     2000 |
+-----------+----------+
1 row in set (0.00 sec)

CAUTION: Do not compare FLOATs (real numbers) for equality ('=' or '<>'), as they are not precise. On the other hand, DECIMAL are precise.

For strings, you could also use '=', '<>', '>', '<'<>, '>=', '<='<> to compare two strings (e.g., productCode = 'PEC'). 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 '=' and '<>', we can perform pattern matching using operator LIKE (or NOT LIKE) with wildcard characters. The wildcard '_' matches any single character; '%' matches any number of characters (including zero). For example,

• 'abc%' matches strings beginning with 'abc';
• '%xyz' matches strings ending with 'xyz';
• '%aaa%' matches strings containing 'aaa';
• '___' matches strings containing exactly three characters; and
• 'a_b%' matches strings beginning with 'a', followed by any single character, followed by 'b', followed by zero or more characters.

MySQL also support regular expression matching via the REGEXE operator.

Arithmetic Operators

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

Logical Operators – AND, OR, NOT, XOR

You can combine multiple conditions with boolean operators AND, OR, XOR. You can also invert a condition using operator NOT. 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>;
+-----------+-------------+---------+----------+-------+
| 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 IN (or NOT IN) operator. This is easier and clearer than the equivalent AND-OR 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 BETWEEN ... AND ... operator. Again, this is easier and clearer than the equivalent AND-OR 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

NULL is a special value, which represent “no value”, “missing value” or “unknown value”. You can checking if a column contains NULL by IS NULL or IS NOT NULL. For example,

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

Using comparison operator (such as = or <>) to check for NULL is a mistake – a very common mistake. For example,

ORDER BY Clause

You can order the rows selected using ORDER BY 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 columnA, in either ascending (ASC) (default) or descending (DESC) order. If several rows have the same value in columnA, it will be ordered according to columnB, 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 RAND(), e.g.,

mysql> SELECT * FROM products ORDER BY RAND();

LIMIT Clause

A SELECT query on a large database may produce many rows. You could use the LIMIT 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 LIMIT clause, as follows:

AS – Alias

You could use the keyword AS 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 “Unit Price” 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 CONCAT(). For example,

mysql> SELECT CONCAT(productCode, ' - ', name) AS `Product Description`, price FROM products;
+---------------------+-------+
| Product Description | price |
+---------------------+-------+
| PEN - Pen Red       |  1.23 |
| PEN - Pen Blue      |  1.25 |
| PEN - Pen Black     |  1.25 |
| PEC - Pencil 2B     |  0.48 |
| PEC - Pencil 2H     |  0.49 |
+---------------------+-------+

Producing Summary Reports

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

DISTINCT

A column may have duplicate values, we could use keyword DISTINCT to select only distinct values. We can also apply DISTINCT to several columns to select distinct combinations of these columns. For examples,

GROUP BY Clause

The GROUP BY clause allows you to collapse multiple records with a common value into groups. For example,

GROUP BY by itself is not meaningful. It is used together with GROUP BY aggregate functions (such as COUNT(), AVG(), SUM()) to produce group summary.

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

We can apply GROUP BY Aggregate functions to each group to produce group summary report.

The function COUNT(*) returns the rows selected; COUNT(columnName) counts only the non-NULL values of the given column. For example,

Besides COUNT(), there are many other GROUP BY aggregate functions such as AVG(), MAX(), MIN() and SUM(). For example,

HAVING clause

HAVING is similar to WHERE, but it can operate on the GROUP BY aggregate functions; whereas WHERE operates only on columns.

WITH ROLLUP

The WITH ROLLUP clause shows the summary of group summary, e.g.,

Modifying Data – UPDATE

To modify existing data, use UPDATE ... SET command, with the following syntax:

UPDATE tableName SET columnName = NULL, ... WHERE criteria

For example,

CAUTION: If the WHERE clause is omitted in the UPDATE command, ALL ROWS will be updated. Hence, it is a good practice to issue a SELECT query, using the same criteria, to check the result set before issuing the UPDATE. This also applies to the DELETE statement in the following section.

Deleting Rows – DELETE FROM

Use the DELELE FROM command to delete row(s) from a table, with the following syntax:

For example,

Beware that "DELETE FROM tableName” without a WHERE clause deletes ALL records from the table. Even with a WHERE clause, you might have deleted some records unintentionally. It is always advisable to issue a SELECT command with the same WHERE clause to check the result set before issuing the DELETE (and UPDATE).

Loading/Exporting Data from/to a Text File

There are several ways to add data into the database: (a) manually issue the INSERT commands; (b) run the INSERT commands from a script; or (c) load raw data from a file using LOAD DATA or via mysqlimport utility.

LOAD DATA LOCAL INFILE … INTO TABLE …

Besides using INSERT commands to insert rows, you could keep your raw data in a text file, and load them into the table via the LOAD DATA command. For example, use a text editor to CREATE a NEW FILE called “products_in.csv“, under “d:\myProject” (for Windows) or “Documents” (for Mac), containing the following records, where the values are separated by ','. The file extension of “.csv” stands for Comma-Separated Values text file.

\N,PEC,Pencil 3B,500,0.52
\N,PEC,Pencil 4B,200,0.62
\N,PEC,Pencil 5B,100,0.73
\N,PEC,Pencil 6B,500,0.47

You can load the raw data into the products table as follows:

mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1007 | PEC         | Pencil 3B |      500 |  0.52 |
|      1008 | PEC         | Pencil 4B |      200 |  0.62 |
|      1009 | PEC         | Pencil 5B |      100 |  0.73 |
|      1010 | PEC         | Pencil 6B |      500 |  0.47 |
+-----------+-------------+-----------+----------+-------+

Notes:

• You need to provide the path (absolute or relative) and the filename. Use Unix-style forward-slash '/' as the directory separator, instead of Windows-style back-slash '\'.
• The default line delimiter (or end-of-line) is '\n' (Unix-style). If the text file is prepared in Windows, you need to include LINES TERMINATED BY '\r\n'.
• The default column delimiter is “tab” (in a so-called TSV file – Tab-Separated Values). If you use another delimiter, e.g. ',', include COLUMNS TERMINATED BY ','.
• You need to use \N for NULL.

mysqlimport Utility Program

You can also use the mysqlimport utility program to load data from a text file.

-- SYNTAX
> mysqlimport -u username -p --local databaseName tableName.tsv
   -- The raw data must be kept in a TSV (Tab-Separated Values) file with filename the same as tablename

-- EXAMPLES
-- Create a new file called "products.tsv" containing the following record,
--  and saved under "d:\myProject" (for Windows) or "Documents" (for Mac)
-- The values are separated by tab (not spaces).
\N  PEC  Pencil 3B  500  0.52
\N  PEC  Pencil 4B  200  0.62
\N  PEC  Pencil 5B  100  0.73
\N  PEC  Pencil 6B  500  0.47

(For Windows)
> cd path-to-mysql-bin
> mysqlimport -u root -p --local southwind d:/myProject/products.tsv

(For Macs)
$ cd /usr/local/mysql/bin
$ ./mysqlimport -u root -p --local southwind ~/Documents/products.tsv

SELECT … INTO OUTFILE …

Complimenting LOAD DATA command, you can use SELECT ... INTO OUTFILE fileName FROM tableName to export data from a table to a text file. For example,

(For Windows)
mysql> SELECT * FROM products INTO OUTFILE 'd:/myProject/products_out.csv' 
         COLUMNS TERMINATED BY ','
         LINES TERMINATED BY '\r\n';

(For Macs)
mysql> SELECT * FROM products INTO OUTFILE '~/Documents/products_out.csv'
         COLUMNS TERMINATED BY ',';

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 “load_products.sql” under “d:\myProject” (for Windows) or “Documents” (for Mac).

DELETE FROM products;
INSERT INTO products VALUES (2001, 'PEC', 'Pencil 3B', 500, 0.52),
                            (NULL, 'PEC', 'Pencil 4B', 200, 0.62),
                            (NULL, 'PEC', 'Pencil 5B', 100, 0.73),
                            (NULL, 'PEC', 'Pencil 6B', 500, 0.47);
SELECT * FROM products;

You can run the script either:

1. via the “source” command in a MySQL client. For example, to restore the southwind backup earlier:
2. via the “batch mode” of the mysql client program, by re-directing the input from the script:

   (For Windows)
   > cd path-to-mysql-bin
   > mysql -u root -p southwind 
   
   (For Macs)
   $ cd /usr/local/mysql/bin
   $ ./mysql -u root -p southwind <~\documents\load_products.sql<>

More Than One Tables

Our example so far involves only one table “products“. 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 products 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 products 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 suppliers to store suppliers’ data (e.g., name, address and phone number). We create a column with unique value called supplierID to identify every suppliers. We set supplierID as the primary key for the table suppliers (to ensure uniqueness and facilitate fast search).

To relate the suppliers table to the products table, we add a new column into the products table – the supplierID. We then set the supplierID column of the products table as a foreign key references the supplierID column of the suppliers table to ensure the so-called referential integrity.

We need to first create the suppliers table, because the products table references the suppliers table. The suppliers table is known as the parent table; while the products table is known as the child table in this relationship.

mysql> USE southwind;

mysql> DROP TABLE IF EXISTS suppliers;

mysql> CREATE TABLE suppliers (
         supplierID  INT UNSIGNED  NOT NULL AUTO_INCREMENT, 
         name        VARCHAR(30)   NOT NULL DEFAULT '', 
         phone       CHAR(8)       NOT NULL DEFAULT '',
         PRIMARY KEY (supplierID)
       );

mysql> DESCRIBE suppliers;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| supplierID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(30)      | NO   |     |         |                |
| phone      | char(8)          | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+

mysql> INSERT INTO suppliers VALUE
          (501, 'ABC Traders', '88881111'), 
          (502, 'XYZ Company', '88882222'), 
          (503, 'QQ Corp', '88883333');

mysql> SELECT * FROM suppliers;
+------------+-------------+----------+
| supplierID | name        | phone    |
+------------+-------------+----------+
|        501 | ABC Traders | 88881111 |
|        502 | XYZ Company | 88882222 |
|        503 | QQ Corp     | 88883333 |
+------------+-------------+----------+

ALTER TABLE

Instead of deleting and re-creating the products table, we shall use “ALTER TABLE” to add a new column supplierID into the products table.

mysql> ALTER TABLE products
       ADD COLUMN supplierID INT UNSIGNED NOT NULL;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
| productID   | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| productCode | char(3)          | NO   |     |            |                |
| name        | varchar(30)      | NO   |     |            |                |
| quantity    | int(10) unsigned | NO   |     | 0          |                |
| price       | decimal(10,2)    | NO   |     | 9999999.99 |                |
| supplierID  | int(10) unsigned | NO   |     | NULL       |                |
+-------------+------------------+------+-----+------------+----------------+

Next, we shall add a foreign key constraint on the supplierID columns of the products child table

to the suppliers parent table, to ensure that every supplierID in the products table always refers to a valid supplierID in the suppliers table – this is called referential integrity.

Before we can add the foreign key, we need to set the supplierID of the existing records in the products table to a valid supplierID in the table (say supplierID=501).

SELECT with JOIN

SELECT command can be used to query and join data from two related tables. For example, to list the product’s name (in products table) and supplier’s name (in suppliers table), we could join the two table via the two common supplierID columns:

In the above query result, two of the columns have the same heading “name“. We could create aliases for headings.

mysql> SHOW CREATE TABLE products \G
Create Table: CREATE TABLE `products` (
  `productID`   int(10) unsigned  NOT NULL AUTO_INCREMENT,
  `productCode` char(3)           NOT NULL DEFAULT '',
  `name`        varchar(30)       NOT NULL DEFAULT '',
  `quantity`    int(10) unsigned  NOT NULL DEFAULT '0',
  `price`       decimal(7,2)      NOT NULL DEFAULT '99999.99',
  `supplierID`  int(10) unsigned   NOT NULL DEFAULT '501',
  PRIMARY KEY (`productID`),
  KEY `supplierID` (`supplierID`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`supplierID`) 
     REFERENCES `suppliers` (`supplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1

mysql> ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;

mysql> SHOW CREATE TABLE products \G

mysql> ALTER TABLE products DROP supplierID;

mysql> DESC products;

mysql> 

mysql> 

mysql> DESCRIBE product_details;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| productID | int(10) unsigned | NO   | PRI | NULL    |       |
| comment   | text             | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+

mysql> SHOW CREATE TABLE product_details \G
*************************** 1. row ***************************
       Table: product_details
Create Table: CREATE TABLE `product_details` (
  `productID`  int(10) unsigned  NOT NULL,
  `comment`    text,
  PRIMARY KEY (`productID`),
  CONSTRAINT `product_details_ibfk_1` FOREIGN KEY (`productID`) REFERENCES `products` (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

(For Windows)
-- Start a NEW "cmd"
> cd path-to-mysql-bin
> mysqldump -u root -p --databases southwind > "d:\myProject\backup_southwind.sql"

(For Macs)
-- Start a NEW "terminal"
$ cd /usr/local/mysql/bin
$ ./mysqldump -u root -p --databases southwind > ~/Documents/backup_southwind.sql

-- Dump selected databases with --databases option
> mysqldump -u username -p --databases database1Name [database2Name ...] > backupFile.sql
-- Dump all databases in the server with --all-databases option, except mysql.user table (for security)
> mysqldump -u root -p --all-databases --ignore-table=mysql.user > backupServer.sql

-- Dump all the tables of a particular database
> mysqldump -u username -p databaseName > backupFile.sql
-- Dump selected tables of a particular database
> mysqldump -u username -p databaseName table1Name [table2Name ...] > backupFile.sql

mysql> SELECT suppliers.name from suppliers
       WHERE suppliers.supplierID
          NOT IN (SELECT DISTINCT supplierID from products_suppliers);

mysql> select now(), curdate(), curtime();
+---------------------+------------+-----------+
| now()               | curdate()  | curtime() |
+---------------------+------------+-----------+
| 2012-10-19 19:53:20 | 2012-10-19 | 19:53:20  |
+---------------------+------------+-----------+

mysql> DROP VIEW IF EXISTS patient_view;

mysql> CREATE VIEW patient_view
       AS
       SELECT 
          patientID AS ID, 
          name AS Name, 
          dateOfBirth AS DOB,
          TIMESTAMPDIFF(YEAR, dateOfBirth, NOW()) AS Age
       FROM patients
       ORDER BY Age, DOB;

mysql> SELECT * FROM patient_view WHERE Name LIKE 'A%';
+------+---------+------------+------+
| ID   | Name    | DOB        | Age  |
+------+---------+------------+------+
| 1003 | Ali     | 2011-01-30 |    1 |
| 1001 | Ah Teck | 1991-12-31 |   20 |
+------+---------+------------+------+

mysql> SELECT * FROM patient_view WHERE age >= 18;
+------+---------+------------+------+
| ID   | Name    | DOB        | Age  |
+------+---------+------------+------+
| 1001 | Ah Teck | 1991-12-31 |   20 |
+------+---------+------------+------+

mysql> SELECT @ali_dob := dateOfBirth FROM patients WHERE name = 'Ali';
mysql> SELECT name WHERE dateOfBirth <@ali_dob;

mysql> SET @today := CURDATE();
mysql> SELECT name FROM patients WHERE nextVisitDate = @today;

mysql> SELECT *
       FROM t1 INNER JOIN t2 ON t1.id = t2.id;
+----+------------+----+------------+
| id | desc       | id | desc       |
+----+------------+----+------------+
|  2 | ID 2 in t1 |  2 | ID 2 in t2 |
|  3 | ID 3 in t1 |  3 | ID 3 in t2 |
+----+------------+----+------------+

mysql> SELECT *
       FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
+----+------------+------+------------+
| id | desc       | id   | desc       |
+----+------------+------+------------+
|  1 | ID 1 in t1 | NULL | NULL       |
|  2 | ID 2 in t1 |    2 | ID 2 in t2 |
|  3 | ID 3 in t1 |    3 | ID 3 in t2 |
+----+------------+------+------------+

mysql> SELECT *
       FROM t1 LEFT JOIN t2 USING (id);
+----+------------+------------+
| id | desc       | desc       |
+----+------------+------------+
|  1 | ID 1 in t1 | NULL       |
|  2 | ID 2 in t1 | ID 2 in t2 |
|  3 | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+

mysql> SELECT *
       FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
+------+------------+----+------------+
| id   | desc       | id | desc       |
+------+------------+----+------------+
|    2 | ID 2 in t1 |  2 | ID 2 in t2 |
|    3 | ID 3 in t1 |  3 | ID 3 in t2 |
| NULL | NULL       |  4 | ID 4 in t2 |
+------+------------+----+------------+

mysql> SELECT *
       FROM t1 RIGHT JOIN t2 USING (id);
+----+------------+------------+
| id | desc       | desc       |
+----+------------+------------+
|  2 | ID 2 in t2 | ID 2 in t1 |
|  3 | ID 3 in t2 | ID 3 in t1 |
|  4 | ID 4 in t2 | NULL       |
+----+------------+------------+

mysql> SELECT t1.id, t1.desc
       FROM t1 LEFT JOIN t2 USING (id)
       WHERE t2.id IS NULL;
+----+------------+
| id | desc       |
+----+------------+
|  1 | ID 1 in t1 |
+----+------------+