Difference between revisions of "Mysql query syntax and examples"

Latest revision as of 12:43, 20 October 2023

^ OVERVIEW

This page to hold notes on MYSQL syntax and practical examples.

^ Simple MYSQL Statements

Opencart disable all existing categories -- useful at initial cart set up time because Opencart ships with about forty example categories that don't necessarily apply to given end user's on-line store:

 update oc_category as t1 set t1.status = 0 where t1.status = 1;

Enable categories whose category_id is greater than 100:

 update oc_category as t1 set t1.status = 1 where t1.category_id > 100;

Set quantities to zero for a group of products with unique tables IDs in a certain range:

 update oc_product as t1 set t1.quantity = 0 where product_id > 2021000 and product_id < 2021500;

Series of four table-scoped delete statements to remove products in a range of record IDs. This four step delete process is necessary in Opencart versions 2.3.0.2 and 3.0.3.7 (likely other versions too) as a product must have at minimum the product attributes associated with it in each of these tables, in order to be active on the store front.

 > delete from oc_product_to_store where product_id > 2021500;
 > delete from oc_product_to_category where product_id > 2021500;
 > delete from oc_product_description where product_id > 2021500;
 > delete from oc_product where product_id > 2021500;

MYSQL count statement

As a sanity check a developer can obtain a count of items selected by a given MYSQL statement:

 > select count(product_id) from oc_product as t1 where t1.product_id > 2023500;

^ MYSQL Keywords and Clauses

. . .

MYSQL join statements - inner, left, right, full join

Excerpt from MYSQL documentation at [https://dev.mysql.com/doc/refman/5.7/en/join.html MYSQL 5.7 Reference Manual, JOIN syntax . . .

" The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set. "

MYSQL, and more general SQL syntax for JOIN ... ON ... WHERE ... type queries are given on the web site sql-join.com site. The next figure or text excerpt comes from this site and relates to a couple of tables, one for customers and one for orders, given near the top of the tutorial page there:

Figure x - SQL example from sql-join.com site:

select order_date, order_amount
from customers
join orders
   on customers.customer_id = orders.customer_id
where customer_id = 3;

Worth noting the table for customers and the table for orders each have primary keys (PK)s. The site also explains the concept of foreign key (FK), which in the orders table appears has a field whose name is 'customer_id'. This foreign key relates orders to specific customers. Foreign keys will be an important part of JOIN type queries in Standard Query Language . . .

^ Examples

Some general text to go here . . .</i>

^ MYSQL max() function

- EXAMPLE - mysql max() function

oc_test_cart> select product_id, stock_status_id, max(product_id) from oc_product;

^ NOT LIKE '%pattern%'

To select records based on text field, such as OC products based on their model, one manner of filtering for such records is:

  mysql> select product_id, model, status from oc_product where model not like '2019 seed sale%';

^ INNER JOIN syntax

- EXAMPLE - inner join

In Opencart 2.3.0.2 the primary products table oc_product has thirty one columns, but none of these hold a product's name. To see product names unsorted the following MYSQL query using JOIN syntax provides a list:

  mysql> SELECT t1.product_id, t2.name FROM oc_product AS t1 INNER JOIN oc_product_description AS t2 ON t1.product_id = t2.product_id;

Another example of inner join, to obtain Opencart category names mapped one to one with unique category ids:

  mysql> select oc_category.category_id, oc_category_description.name from oc_category inner join oc_category_description on oc_category.category_id = oc_category_description.category_id;

2020-07-10 - To search for the image path of a store product whose name is like a certain pattern:

  mysql> select oc_product.product_id, oc_product_description.name, oc_product.image from oc_product INNER JOIN oc_product_description ON oc_product.product_id = oc_product_description.product_id AND oc_product_description.name like '%Dardanus%';

Example 1 - enabling / disabling store items by item category

Opencart categories have part of their attributes stored in a table named [opencart_table_name_prefix_]category_description:

oc_test_cart> select category_id, language_id, name from oc_category_description;
+-------------+-------------+--------------------------+
| category_id | language_id | name                     |
+-------------+-------------+--------------------------+
|          59 |           1 | arilbred iris            |
|          60 |           1 | aril iris                |
|          65 |           1 | TB-onco                  |
|          61 |           1 | ASI plant sale item      |
|          62 |           1 | iris basket              |
|          63 |           1 | items for cart testing   |
|          66 |           1 | species iris             |
|          67 |           1 | seeds                    |
|          68 |           1 | 2017 seed sale           |
|          69 |           1 | (O) oncocyclus           |
|          70 |           1 | (OGB) onco-gelia bred    |
|          71 |           1 | (OH) oncocyclus hybrid   |
|          72 |           1 | (J) juno iris            |
|          73 |           1 | (RC) regio-cyclus hybrid |
|          74 |           1 | 2017 plant sale item     |
|          75 |           1 | 2018 plant sale item     |
|          64 |           1 | ASI memberships          |
|          76 |           1 | seed sale item           |
|          77 |           1 | 2018 seed sale           |
+-------------+-------------+--------------------------+
19 rows in set (0.00 sec)

oc_test_cart>

MYSQL's IN clause, useful to combine OR statements in queries and useful to express subqueries. The following example query shows how to, in OpenCart's database tables, change the status of products which associated with a particular category. Product statae live in one table, and numeric identifiers (ids) associated with products live in another table. MYSQL's IN clause allows us to update product statae in one table, for those prooducts whose ids match conditions in another table. This is a very simple use of MYSQL IN clause:

Code example x - MYSQL IN clause, two examples, to disable Opencart products in particular categories:

    mysql> update oc_product as t1 set t1.status = 0 where t1.product_id in (select t2.product_id from
        oc_product_to_category as t2 where t2.category_id = 60);
    Query OK, 18 rows affected (0.00 sec)
    Rows matched: 18  Changed: 18  Warnings: 0

    mysql> update oc_product as t1 set t1.status = 0 where t1.product_id in (select t2.product_id from
        oc_product_to_category as t2 where t2.category_id = 64);
    Query OK, 6 rows affected (0.00 sec)
    Rows matched: 6  Changed: 6  Warnings: 0

    mysql>

Category 60 is 'aril iris' and category 64 is 'ASI memberships'. The above statements set the state of products in these categories -- in Opencart -- to zero, which disables them so they don't appear in the store or store searches.

The first updating SQL statement above reads, in more human language "Update status of products in table oc_product, where id of product is any of ids in product-to-category table where category id equals 60." We're short-hand calling oc_product table t1, and calling oc_product_to_category table t2. Short-hand names t1 and t2 live only in the present query, and are often needed in queries which refer to two or more tables, to that our references to columns clearly express in which table those columns live.

To obtain category names, Ted runs the following query:

    mysql> select t1.category_id, t1.name from oc_category_description t1;

- 2017-09-08 FRI -

Example 2 - attributing items in one store to a second different store

Situation:  Need to add "to store" reference to existing cart items, so that items in test store (store_id = 1) appear in production store (store_id = 4). Table "products_to_store" is a table separate from "products", and not all products or items in the 2017 ASI cart installation are attributed to go to the test store. So we need to select items going to one store, a query selection from one table, and for each item (product id) we want to insert a new, or ignore an existing like record in the "products_to_store" table. One difficulty is that we're querying the table to which we need to add records.

How to add rows to destination table (t2), with one column's values selected from source table (t1) and another column's value set to a constant and like value for each added row. Reference https://dev.mysql.com/doc/refman/5.5/en/insert.html . . .

If you want to combine insert..select with setting an explicit value for a column - you can use join:

INSERT INTO TargetTable (col1, col2, col3)
SELECT col1,col2,col3
FROM SourceTable JOIN (SELECT 'ExplicitValue' AS col3) AS AnyAlias

This looks quite simple but it took me several hours to understand that there's no need for a special statement to handle such cases.

Regards!



 Posted by Diego d'Ippolito on December 15, 2006
To Jan Jędrzejczyk:

> INSERT INTO TargetTable (col1, col2, col3)
> SELECT col1,col2,col3
> FROM SourceTable
> JOIN (SELECT 'ExplicitValue' AS col3) AS AnyAlias

You could easily do the same thing just by using:

INSERT INTO TargetTable (col1, col2, col3)
SELECT col1,col2, 'ExplicitValue'
FROM SourceTable

hth,

Lokar

edit point

Using above examples, developed following insert statement to create table of all products which OpenCart table has "going" to store with id 1. Difference in our work here is that we create a named table, which persists until we drop it, to hold the results of the first query. We then use that table, whose name begins with 'z' to distinguish it from OpenCart's originally configured tables, to add desired new records to table "product to store":

mysql> create table `z-products-to-nn-test-store` (`category_id` int, `store_id` int);

mysql> insert into `z-products-to-nn-test-store` (product_id, store_id) select product_id, '4' from oc_product_to_store where (store_id = 1);
Query OK, 22 rows affected (0.00 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> select count(*) from `z-products-to-nn-test-store`;
+----------+
| count(*) |
+----------+
|       22 |
+----------+
1 row in set (0.00 sec)

mysql> insert into oc_product_to_store (product_id, store_id) select * from `z-products-to-nn-test-store` on duplicate key update oc_product_to_store.product_id = oc_product_to_store.product_id;
Query OK, 11 rows affected (0.00 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> drop table `z-products-to-nn-store`;

mysql>

^ REFERENCES

Standard Query Language syntax and examples:

• https://dev.mysql.com/doc/refman/5.7/en/join.html . . . MYSQL 'join' syntax
• SQL 'join' explanation
• Dashes in MYSQL table names
• ON DUPLICATE KEY and INSERT IGNORE

• https://dev.mysql.com/doc/refman/8.0/en/join.html

General MYSQL history and overview articles:

• MYSQL software history on Wikipedia
• Oracle Corporation

MYSQL CLI use:

Golden mysql> pager less -SFX . . .

• https://stackoverflow.com/questions/924729/how-to-best-display-in-terminal-a-mysql-select-returning-too-many-fields . . . display help for large record sets