Difference between revisions of "Mysql query syntax and examples"

Revision as of 15:12, 8 September 2017

OVERVIEW

This page to hold Ted's notes and 2017 studies of MYSQL syntax and practical examples.

MYSQL Keywords and Clauses

 
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 ..., excerpt 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

EXAMPLES

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:

    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>

- 2017-09-08 FRI -

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

Using above examples, developed following insert statement to create table of all products which OpenCart table has "going" to store with id 1:

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>

REFERENCES

• MYSQL 'join' syntax
• SQL 'join' explanation
• Dashes in MYSQL table names
• ON DUPLICATE KEY and INSERT IGNORE