By looking at Mysql, I will find that the table inquiries are very detailed. The following is my summary after reading.
It's full of Mysql queries. From the most basic to the difficult.
First look at the structure of the database.
MariaDB [CargoWarehouse]> show tables; +--------------------------+ | Tables_in_CargoWarehouse | +--------------------------+ | customers | | orderitems | | orders | | productnotes | | products | | vendors | +--------------------------+ 6 rows in set (0.00 sec)
Namely:
Cusmers table, which stores all customer information
Order items table, actual items for each order
Order table, which stores customer orders
Product notes table, which stores annotations related to a particular product
The products table, which contains the product catalog
venders table, which stores vendors who sell products
1. Simplest select Retrieval
1. Check the product name in products
MariaDB [CargoWarehouse]> select prod_name from products; +----------------+ | prod_name | +----------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | | Detonator | | Bird seed | | Carrots | | Fuses | | JetPack 1000 | | JetPack 2000 | | Oil can | | Safe | | Sling | | TNT (1 stick) | | TNT (5 sticks) | +----------------+ 14 rows in set (0.00 sec)
2. See what values are there (the same duplicate is displayed only once)
If distinct is not used, all id values are displayed. Including repetition. (A total of 14 rows)
MariaDB [CargoWarehouse]> select distinct vend_id from products; +---------+ | vend_id | +---------+ | 1001 | | 1002 | | 1003 | | 1005 | +---------+ 4 rows in set (0.00 sec)
3. Restricted results
This is the six lines (4, 5, 6, 7, 8, 9 lines) that display the output from line 4.
MariaDB [CargoWarehouse]> select vend_id from products limit 3,6; +---------+ | vend_id | +---------+ | 1002 | | 1002 | | 1003 | | 1003 | | 1003 | | 1003 | +---------+ 6 rows in set (0.00 sec)
II. Sorting Retrieval Data
1. Inverse output
Because the default is ascending order (ASC), I will only introduce descending order (DESC) output in descending order of price.
Whose order by is to arrange the whole table?
MariaDB [CargoWarehouse]> select prod_id,prod_price,prod_name from products order by prod_price desc limit 6; +---------+------------+----------------+ | 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) | +---------+------------+----------------+ 6 rows in set (0.00 sec)
2. Use with distinct restrictions
MariaDB [CargoWarehouse]> select distinct vend_id from products order by prod_price desc; +---------+ | vend_id | +---------+ | 1005 | | 1003 | | 1001 | | 1002 | +---------+ 4 rows in set (0.00 sec)
Data filtering
1.where filtering (note the location of order by)
MariaDB [CargoWarehouse]> select prod_name,prod_price from products where prod_price >= 10 order by prod_price desc; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | JetPack 2000 | 55.00 | | Safe | 50.00 | | JetPack 1000 | 35.00 | | 2 ton anvil | 14.99 | | Detonator | 13.00 | | Bird seed | 10.00 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 7 rows in set (0.00 sec)
2. Mismatch Check
Use <> Mismatch Check, or you can use it!= (This is recommended)
MariaDB [CargoWarehouse]> select vend_id,prod_name from products where vend_id <> 13 limit 5; +---------+--------------+ | vend_id | prod_name | +---------+--------------+ | 1001 | .5 ton anvil | | 1001 | 1 ton anvil | | 1001 | 2 ton anvil | | 1003 | Detonator | | 1003 | Bird seed | +---------+--------------+ 5 rows in set (0.00 sec)
3. Within what scope
MariaDB [CargoWarehouse]> select vend_id,prod_name,prod_price from products where prod_price between 5 and 10; +---------+----------------+------------+ | vend_id | prod_name | prod_price | +---------+----------------+------------+ | 1001 | .5 ton anvil | 5.99 | | 1001 | 1 ton anvil | 9.99 | | 1003 | Bird seed | 10.00 | | 1002 | Oil can | 8.99 | | 1003 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 5 rows in set (0.00 sec)
The and or logic operation here. and, or
Of course, you can also use IN (recommended)
MariaDB [CargoWarehouse]> select vend_id,prod_name,prod_price from products where prod_price in (5,10); +---------+----------------+------------+ | vend_id | prod_name | prod_price | +---------+----------------+------------+ | 1003 | Bird seed | 10.00 | | 1003 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 2 rows in set (0.00 sec)
4. Check null values
MariaDB [CargoWarehouse]> select cust_id from customers where cust_email is null; +---------+ | cust_id | +---------+ | 10002 | | 10005 | +---------+ 2 rows in set (0.00 sec)
5. wildcard filtering (LIKE)
Here% is like * in the linux wildcard. Represents any number of occurrences of any character
This is where jet begins.
MariaDB [CargoWarehouse]> select prod_id,prod_name from products where prod_name like 'jet%'; +---------+--------------+ | prod_id | prod_name | +---------+--------------+ | JP1000 | JetPack 1000 | | JP2000 | JetPack 2000 | +---------+--------------+ 2 rows in set (0.00 sec)
This is the beginning of s and the end of e.
MariaDB [CargoWarehouse]> select prod_name from products where prod_name like 's%e'; +-----------+ | prod_name | +-----------+ | Safe | +-----------+ 1 row in set (0.00 sec)
Here''corresponds to the wildcard'?' of linux, indicating that only a single character is matched.
MariaDB [CargoWarehouse]> 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)
6. Regular expression filtering
Regular matching is done with regexp
As we all know by using sed rule, regular rule is very powerful.
A point represents a single character. It's the same as the matching.
MariaDB [CargoWarehouse]> select prod_name from products
-> where prod_name regexp '.000' order by prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
Any one of them is OK.
| Expressing or Meaning
MariaDB [CargoWarehouse]> select prod_name
-> from products
-> where prod_name regexp '1000|2000'
-> order by prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
MariaDB [CargoWarehouse]> select prod_name from products
-> where prod_name regexp '[123]';
+---------------+
| prod_name |
+---------------+
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
+---------------+
5 rows in set (0.00 sec)
Let's make a statement. The following characters represent the previous repetition
* Repeat the previous character 0 times or countless times
+ Repeat the previous character once or countless times
Repeat the character before {n} n times
Repeat the previous character at least n times
Repeat the character before {n,m} n to m times
MariaDB [CargoWarehouse]> select prod_name
-> from products
-> where prod_name regexp '[[:digit:]]{2,}'
-> order by prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
Locating metacharacters
^ The Beginning of Text
The End of the Text
[[:<:]] The beginning of the word
[[:>:]] Ending of the Word
MariaDB [CargoWarehouse]> select prod_name
-> from products
-> where prod_name regexp 'vil[[:>:]]'
-> order by prod_name;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)
IV. Creating Computing Fields
1. Stitching a field
The concat function is used here to merge the two fields into one field output. For example, the name of the product is directly followed by the price output.
You can also use as to give a new name to a newly formed field.
MariaDB [CargoWarehouse]> select concat(prod_name,' (',prod_price,')')
-> as products
-> from products
-> order by prod_price desc;
+--------------------------+
| products |
+--------------------------+
| JetPack 2000 (55.00) |
| Safe (50.00) |
| JetPack 1000 (35.00) |
| 2 ton anvil (14.99) |
| Detonator (13.00) |
| TNT (5 sticks) (10.00) |
| Bird seed (10.00) |
| 1 ton anvil (9.99) |
| Oil can (8.99) |
| .5 ton anvil (5.99) |
| Sling (4.49) |
| Fuses (3.42) |
| Carrots (2.50) |
| TNT (1 stick) (2.50) |
+--------------------------+
14 rows in set (0.00 sec)
2. Perform arithmetic calculations
Use * directly to multiply two numeric fields. It's better to use an as to give the new field a name.
MariaDB [CargoWarehouse]> select prod_id,quantity,item_price,quantity*item_price as expanded_price
-> from orderitems
-> where order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.00 sec)
3. Use functions
Use the now () function to return the current operation time.
MariaDB [CargoWarehouse]> select now() as operate_time,prod_id,item_price
-> from orderitems;
+---------------------+---------+------------+
| operate_time | prod_id | item_price |
+---------------------+---------+------------+
| 2017-01-11 14:14:55 | ANV01 | 5.99 |
| 2017-01-11 14:14:55 | ANV02 | 9.99 |
| 2017-01-11 14:14:55 | TNT2 | 10.00 |
| 2017-01-11 14:14:55 | FB | 10.00 |
| 2017-01-11 14:14:55 | JP2000 | 55.00 |
| 2017-01-11 14:14:55 | TNT2 | 10.00 |
| 2017-01-11 14:14:55 | FC | 2.50 |
| 2017-01-11 14:14:55 | FB | 10.00 |
| 2017-01-11 14:14:55 | OL1 | 8.99 |
| 2017-01-11 14:14:55 | SLING | 4.49 |
| 2017-01-11 14:14:55 | ANV03 | 14.99 |
+---------------------+---------+------------+
11 rows in set (0.00 sec)
5. Using Data Processing Functions
1. Text Processing Function
Here are just a few simple functions
upper () Converts strings to uppercase
lower () converts a string to lowercase
substring () finds the character content in the string
sounddex () returns strings with similar pronunciation
I think everything else is quite understandable. Just the last one. soundex () returns strings with similar pronunciation.
For example, I want to find a customer named Li now. But I don't know if it's Lee or Lie. soundex () is now available.
MariaDB [CargoWarehouse]> select cust_name,cust_contact
-> from customers
-> where soundex(cust_contact) = soundex('Y Lie');
+-------------+--------------+
| cust_name | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee |
+-------------+--------------+
1 row in set (0.00 sec)
2. Time Processing Function
adddate () Add a date
addtime () add a time
curtime () current time
curdate () current date
Date () returns the date part of a date
dateoff () calculates the difference between two times
Year () returns the part of the year of a time
Month () returns the part of a month for a time
Day () Returns the part of the day of a time
Hour () returns an hour part of a time
Minute () returns the minute part of a time
Database changed
MariaDB [CargoWarehouse]> select cust_id,order_date
-> from orders
-> where date(order_date) = '2005-09-01';
+---------+---------------------+
| cust_id | order_date |
+---------+---------------------+
| 10001 | 2005-09-01 00:00:00 |
+---------+---------------------+
1 row in set (0.00 sec)
3. Mathematical Processing Function
Absolute value of abs ()
pi() pi value
rand () returns a random number
sqrt () returns the square root
4. Aggregation function
avg () returns the average value of a column
count () returns the number of rows in a column
max () returns the largest one in a column
min () Not to mention it.
Sum () returns the sum of a column
For example, return the average
MariaDB [CargoWarehouse]> select avg(prod_price) as avg_price
-> from products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.00 sec)
Calculate the total number of customers
MariaDB [CargoWarehouse]> select count(*) as num_cust from customers; +----------+ | num_cust | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)
MariaDB [CargoWarehouse]> select
-> count(prod_name) as count,
-> min(prod_price) as min_price,
-> max(prod_price) as max_price,
-> sum(prod_price) as all_price,
-> avg(prod_price) as avg_price
-> from products;
+-------+-----------+-----------+-----------+-----------+
| count | min_price | max_price | all_price | avg_price |
+-------+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 225.87 | 16.133571 |
+-------+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)
VI. Grouping data
1. grouping
First think about it. If I want to check that the supplier id is 1003, there are several kinds of goods.
So that is
MariaDB [CargoWarehouse]> select count(*) as num_prods
-> from products where vend_id = 1003;
+-----------+
| num_prods |
+-----------+
| 7 |
+-----------+
1 row in set (0.00 sec)group by
MariaDB [CargoWarehouse]> select vend_id,count(*) as num_prods
-> from products group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
4 rows in set (0.00 sec)
2. Grouping selection
The above is based on vend_id grouping, then how to choose suppliers who supply more than or equal to 3 in the supplier.There seems to be a place to choose. But it's not where anymore, it's having.
MariaDB [CargoWarehouse]> select vend_id,count(*) as num_prods
-> from products group by vend_id
-> having count(*) >= 3;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1003 | 7 |
+---------+-----------+
2 rows in set (0.00 sec)
MariaDB [CargoWarehouse]> select vend_id,sum(prod_price) as vend_price
-> from products
-> group by vend_id;
+---------+------------+
| vend_id | vend_price |
+---------+------------+
| 1001 | 30.97 |
| 1002 | 12.41 |
| 1003 | 92.49 |
| 1005 | 90.00 |
+---------+------------+
4 rows in set (0.00 sec)
3. Grouping Selection Filter group by and where Meet
Let's first look at when this happens.
If I want to check the quantity of goods provided by each supplier, but those goods with a value of less than $10 are not counted, but the two or less goods provided are not counted. So how to query?
MariaDB [CargoWarehouse]> select vend_id,count(*) as num_prods
-> from products
-> where prod_price >= 10
-> group by vend_id
-> having count(*) >= 2
-> order by vend_id desc;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1005 | 2 |
| 1003 | 4 |
+---------+-----------+
2 rows in set (0.00 sec)
4. Next, retrieve the order number and the total order price of an order whose total order price is greater than or equal to 50.
MariaDB [CargoWarehouse]> select order_num,sum(item_price*quantity) as total_price
-> from orderitems
-> group by order_num
-> having sum(item_price*quantity) >= 50;
+-----------+-------------+
| order_num | total_price |
+-----------+-------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+-------------+
4 rows in set (0.00 sec)
VII. Use subqueries
1. Use subqueries for filtering
First, let's talk about how to use subqueries.
For example, I want to list all customers whose order id is TNT2.
It should be noted that the customer's id is in the order table, while the item's id is in the order sitems table.
The only way to contact these two tables is the order number order_num. Only the order number of the item id can be found first, and then the order number of the item id can be found in the
The order form looks through the order number to find out which customers have bought items.
MariaDB [CargoWarehouse]> select cust_id from orders
-> where order_num in
-> (select order_num from orderitems
-> where prod_id = 'TNT2' );
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
2 rows in set (0.00 sec)
2. Subqueries as computational fields
Displays the total number of orders per customer in the customers table, and orders in the orders table.
MariaDB [CargoWarehouse]> select cust_name,cust_state,
-> (select count(*)
-> from orders
-> where orders.cust_id = customers.cust_id) as orders
-> from customers
-> order by cust_name;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
5 rows in set (0.00 sec)