MySQL Learning--mysql must be known

Classification for database operations:

The following operation reference (MySQL must know)

To create a database execution script build table:

mysql> CREATE DATABASE mytest; Query OK, 1 row affected (0.07 sec) mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | mytest             | | Performance_schema |+-------- ------------+4 rows in Set (0.00 sec) mysql> use Myteerror 1049 (42000): Unknown database ' myTE ' mysql> use Mytest;dat Abase changedmysql> show tables; Empty Set (0.00 sec) Mysql> Source/home/huangcd/mysql_scripts/create.sql

Mysql> Source/home/huangcd/mysql_scripts/populate.sql

Mysql> Show tables;+------------------+| Tables_in_mytest |+------------------+| Customers        | | OrderItems       | | orders           | | productnotes     | | products         | | Vendors          |+---------------- --+6 rows in Set (0.02 sec)

To delete duplicate rows:

Mysql> Select vend_id from the products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1001 |
| 1001 |
| 1002 |
| 1002 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1005 |
| 1005 |
+---------+
Rows in Set (0.00 sec)

Mysql> SELECT DISTINCT vend_id from the products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
4 rows in Set (0.04 sec)

Returns no more than 5 rows in the result:

Mysql> Select Prod_name from Products
, limit 5;
+--------------+
| Prod_name |
+--------------+
|. 5 Ton Anvil |
| 1 Ton Anvil |
| 2 Ton Anvil |
| Detonator |
| Bird Seed |
+--------------+
5 rows in Set (0.00 sec)

5 lines starting at line 5th:

Mysql> Select Prod_name
From Products
, limit 5, 5;
+--------------+
| Prod_name |
+--------------+
| Carrots |
| fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil Can |
+--------------+
5 rows in Set (0.00 sec)

The ORDER BY clause sorts the output of one or more columns:

Mysql> Select Prod_name
From Products
Order BY Prod_name;
+----------------+
| Prod_name |
+----------------+
|. 5 Ton Anvil |
| 1 Ton Anvil |
| 2 Ton Anvil |
| Bird Seed |
| Carrots |
| Detonator |
| fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil Can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+

Sort by price first, only if the price is the same, sort by name:

Mysql> Select prod_id, Prod_price, Prod_name
From Products
Order by Prod_price, Prod_name;
+---------+------------+----------------+
| prod_id | Prod_price | Prod_name |
+---------+------------+----------------+
|       FC | 2.50 | Carrots |
|       TNT1 | 2.50 | TNT (1 stick) |
|       FU1 | 3.42 | fuses |
|       SLING | 4.49 | Sling |
|       ANV01 | 5.99 |. 5 Ton Anvil |
|       OL1 | 8.99 | Oil Can |
|       ANV02 | 9.99 | 1 Ton Anvil |
|      FB | 10.00 | Bird Seed |
|      TNT2 | 10.00 | TNT (5 sticks) |
|      Dtntr | 13.00 | Detonator |
|      ANV03 | 14.99 | 2 Ton Anvil |
|      JP1000 | 35.00 | JetPack 1000 |
|      SAFE | 50.00 | Safe |
|      JP2000 | 55.00 | JetPack 2000 |
+---------+------------+----------------+
Rows in Set (0.05 sec)

The default sort is ascending, in descending order you must use the DESC keyword:

Mysql> Select prod_id, Prod_price, prod_name from the products order by Prod_price DESC;
+---------+------------+----------------+
| prod_id | Prod_price | Prod_name |
+---------+------------+----------------+
|      JP2000 | 55.00 | JetPack 2000 |
|      SAFE | 50.00 | Safe |
|      JP1000 | 35.00 | JetPack 1000 |
|      ANV03 | 14.99 | 2 Ton Anvil |
|      Dtntr | 13.00 | Detonator |
|      TNT2 | 10.00 | TNT (5 sticks) |
|      FB | 10.00 | Bird Seed |
|       ANV02 | 9.99 | 1 Ton Anvil |
|       OL1 | 8.99 | Oil Can |
|       ANV01 | 5.99 |. 5 Ton Anvil |
|       SLING | 4.49 | Sling |
|       FU1 | 3.42 | fuses |
|       FC | 2.50 | Carrots |
|       TNT1 | 2.50 | TNT (1 stick) |
+---------+------------+----------------+
Rows in Set (0.02 sec)

Mysql> Select prod_id, Prod_price, prod_name from the products order by Prod_price DESC, Prod_name;
+---------+------------+----------------+
| prod_id | Prod_price | Prod_name |
+---------+------------+----------------+
|      JP2000 | 55.00 | JetPack 2000 |
|      SAFE | 50.00 | Safe |
|      JP1000 | 35.00 | JetPack 1000 |
|      ANV03 | 14.99 | 2 Ton Anvil |
|      Dtntr | 13.00 | Detonator |
|      FB | 10.00 | Bird Seed |
|      TNT2 | 10.00 | TNT (5 sticks) |
|       ANV02 | 9.99 | 1 Ton Anvil |
|       OL1 | 8.99 | Oil Can |
|       ANV01 | 5.99 |. 5 Ton Anvil |
|       SLING | 4.49 | Sling |
|       FU1 | 3.42 | fuses |

The one with the highest price:

Mysql> Select Prod_price, from Products,    ORDER by prod_price desc,    limit 1;+------------+| Prod_price |+------------+|      55.00 |+------------+1 row in Set (0.12 sec)

Mysql> Select Prod_name, Prod_price-I,    where prod_price between 5 and 10;+------------- ---+------------+| Prod_name      | prod_price |+----------------+------------+|. 5 Ton Anvil   |       5.99 | | 1 Ton Anvil    |       9.99 | | Bird seed      |      10.00 | | Oil Can        |       8.99 | | TNT (5 sticks) |      10.00 |+----------------+------------+5 rows in Set (0.09 sec)

Null value check:

Mysql> Select Prod_name    , from Products    , where prod_price is null; Empty Set (0.01 sec) mysql> Select cust_id, from    customers,    where Cust_email is null;+---------+| cu st_id |+---------+|   10002 | |   10005 |+---------+2 rows in Set (0.00 sec)

Or operator:

Mysql> Select Prod_name,prod_price from Products where vend_id =1002 or vend_id = 1003;+----------------+------------+ | Prod_name      | prod_price |+----------------+------------+| Detonator      |      13.00 | | Bird seed      |      10.00 | | Carrots        |       2.50 | | Fuses          |       3.42 | | Oil Can        |       8.99 | | Safe           |      50.00 | | Sling          |       4.49 | | TNT (1 stick)  |       2.50 | | TNT (5 sticks) |      10.00 |+----------------+------------+9 rows in Set (0.05 sec)

In operator:

Mysql> Select Prod_name, Prod_price-I,     where vend_id in (1002, 1003),    ORDER BY prod_name;+----------------+------------+| Prod_name      | prod_price |+----------------+------------+| Bird seed      |      10.00 | | Carrots        |       2.50 | | Detonator      |      13.00 | | Fuses          |       3.42 | | Oil Can        |       8.99 | | Safe           |      50.00 | | Sling          |       4.49 | | TNT (1 stick)  |       2.50 | | TNT (5 sticks) |      10.00 |+----------------+------------+9 rows in Set (0.07 sec)

In the biggest advantage is that you can include other SELECT clauses.

Not operator:

Mysql> Select Prod_name, Prod_price-I,    where vend_id not in (1002, 1003),    order by prod_name;+--------------+------------+| Prod_name    | prod_price |+--------------+------------+|. 5 Ton Anvil |       5.99 | | 1 Ton Anvil  |       9.99 | | 2 Ton Anvil  |      14.99 | | JetPack |      35.00 | | JetPack |      55.00 |+--------------+------------+5 rows in Set (0.00 sec)

To use wildcards in a search clause, you must use the LIKE operator. The search pattern followed by the like indication is a wildcard character rather than a direct equality match.

% wildcard character: Any number of occurrences of any character

Mysql> Select prod_id, Prod_name-I,    where prod_name like ' jet% ',    +---------+ --------------+| prod_id | Prod_name    |+---------+--------------+| JP1000  | JetPack 1000 | | JP2000  | JetPack |+---------+--------------+2 rows in Set (0.05 sec)

Mysql> Select prod_id, prod_name from products where prod_name like '%anvil% '; +---------+--------------+| prod_id | Prod_name    |+---------+--------------+| ANV01   |. 5 Ton Anvil | | ANV02   | 1 ton Anvil  | | ANV03   | 2 ton anvil  |+---------+--------------+3 rows in Set (0.00 sec)

_ Wildcard: matches any single character

Mysql> Select prod_id, prod_name from the products where prod_name like ' _ton anvil '; Empty Set (0.00 sec) mysql> Select prod_id, prod_name from products where prod_name like ' _ Ton anvil '; +---------+------ -------+| prod_id | Prod_name   |+---------+-------------+| ANV02   | 1 ton Anvil | | ANV03   | 2 ton anvil |+---------+-------------+2 rows in Set (0.00 sec)

MySQL must know before the eight chapters of the session.