[MySQL] Note: Row CURD

pixabay.com

Morden APPs means: 90% CRUD, user interface, and reports, and 10% complex code evaluation for a dozen languages.

There is 2.31 billion social media users over the world, we all love listening and sharing, we love platform. The fact is 90% of operation happens in platform is about DB/Cache CRUD, this story has collected 12 most comment used MySQL ROW operation, to help understanding and finding all we need about ROW.

Content

1. Create Row
2. Query By Condition
3. Query By Order
4. Query By limi, offset
5. Alias, AS
6. Aggregate Function
7. *Group By
8. Update Row
9. Delete Row
10. Alert Column (add new column)
11. *Foreign Key
12. *Select with JOIN

To have a default Database before operation table inside a Database

mysql> **SHOW DATABASES;**mysql> **USE yourdatabse;**mysql> CREATE TABLE IF NOT EXISTS products (productID INT UNSIGNED NOT NULL AUTO_INCREMENT,productCode CHAR(3) NOT NULL DEFAULT '',name VARCHAR(30) NOT NULL DEFAULT '',quantity INT UNSIGNED NOT NULL DEFAULT 0,price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,quantity INT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY (productID));

1 — Create Row

Insert a row with full column values

mysql> **INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);**Query OK, 1 row affected (0.00 sec)

Inserting NULL to the auto_increment column results in max_value + 1

mysql> **INSERT INTO products VALUES (NULL, 'PEN', 'Pen Red', 5000, 1.23);**Query OK, 1 row affected (0.00 sec)

To insert a row with values on Missing value or selected columns only

mysql> **INSERT INTO products (productCode, name, quantity, price) VALUES ('PEC', 'Pencil 2B', 10000, 0.48),;**Query OK, 1 row affected (0.00 sec)

2 — Query By Condition

Show

List all the rows of the **_specified columns_**mysql> SELECT * FROM products;

List all the rows of the **_specified columns_**mysql> SELECT productID, name FROM products

Comparison Operators — =, <, >

mysql> SELECT * FROM products WHERE price >1;

mysql> SELECT * FROM products WHERE productCode = 'PEN';

Logical Operators — AND, OR, NOT, XOR

mysql> SELECT * FROM products WHERE productCode = 'PEN' AND price >1;

Contain Operators —IN, NOT IN

mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');

Between Operators —BETWEEN, NOT BETWEEN

mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');

IS NULL, IS NOT NULL

mysql> SELECT * FROM products WHERE productCode IS NULL;

3 — Query By Order

Order in either ascending (ASC) (default) or descending (DESC) order.

mysql> SELECT * FROM products WHERE price >1 ORDER BY price DESC;

mysql> SELECT * FROM products WHERE price >1 ORDER BY price DESC, quantity;

4 — Query By limit, offset

mysql> SELECT * FROM products LIMIT 2,1;//limit =2//skip =1

5 — Alias, AS

mysql> SELECT productID AS ID, price AS `Unit Price`

mysql> SELECT * FROM products ORDER BY ID;

6— Aggregate Functions

COUNT, MAX, MIN, AVG, SUM, STD, GROUP_CONCAT,

mysql> SELECT COUNT(*) AS `Count` FROM products**;**

+-------+| Count |+-------+| 5 |+-------+

7— *Group By

GROUP BY is a very cool weapon in generating organised result and sort out data from entangle columns.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.

First Example

mysql> SELECT name, ANY_VALUE(price) FROM products GROUP BY name;

Multi-Columns

mysql> SELECT productCode, MAX(price) AS `Highest Price`, MIN(price) AS `Lowest Price`FROM productsGROUP BY productCode;

+-------------+---------------+--------------+| productCode | Highest Price | Lowest Price |+-------------+---------------+--------------+| PEC | 0.49 | 0.48 || PEN | 1.25 | 1.23 |+-------------+---------------+--------------+

Use CAST(... AS ...) function to format floating-point numbers

mysql> SELECT productCode, MAX(price), MIN(price),CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,SUM(quantity)FROM productsGROUP BY productCode;

8— Update Row

Update All row

mysql> UPDATE products SET price = price * 1.1;

Update by Condition

mysql> UPDATE products SET quantity = quantity - 100 WHERE name = 'Pen Red';

Update Multi-columns

mysql> UPDATE products SET quantity = quantity + 50, price = 1.23 WHERE name = 'Pen Red';

9 — Delete Row

Delete All rows

mysql> DELETE FROM products;

Delete by Condition

mysql> DELETE FROM products WHERE name = 'Pen Red';

10 — Alert Column (add new column)

mysql> ALTER TABLE productsADD COLUMN supplierID INT UNSIGNED NOT NULL;

11— Create Foreign Key Column

To add a Foreign Key to supplierID columns of the products child table to the suppliers parent table :

1. add supplierID column with INT type in product table
2. set all the supplierID of the existing records
3. set supplierID as a foreign key column, by related products table to parent suppliers table

mysql> ALTER TABLE productsADD COLUMN supplierID INT UNSIGNED NOT NULL;

mysql> UPDATE products SET supplierID = 501;

mysql> ALTER TABLE productsADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);

12— *Select with JOIN

Magic JOIN(foreign key) is my favourite part of MySQL. As I have been using MongoDB for long, its relational query has problem in blood. There was one time, we has having a big refactor project which include schema refactor, one of the biggest pain is turning 1–1 relation to 1–n or n-n relation, it is so much time consuming.

I love foreign key and select, which make n-n relation as the best thing since sliced bread. Combo JION-table1-table2-result multi complicated hit by just one SQL query.

JOIN is 1 click with 30 HIT

SQL joins are used to combine rows from two or more tables. Default JOIN is INNER JOIN in MySQL, which given keywords then selects all rows from both tables as long as there is a match between the columns in both tables.

1. Default JOIN is INNER JOIN in MySQL
2. set all the supplierID of the existing records
3. set supplierID as a foreign key column, by related products table to parent suppliers table

mysql> SELECT products.name, price, suppliers.nameFROM productsJOIN suppliers ON products.supplierID = suppliers.supplierIDWHERE price < 5;

You may also like

• MySQL Handbook
• [MySQL] Note: Fast Setup and running in Node.js
• [MySQL] Note: Create Admin User
• [MySQL] Note: Database CURD
• [MySQL] Note: Row CURD
• [MySQL] Note: Relation: 1–1, 1-n, n-n, nest

Very thankful to the community of NTU, who contribute such a good tutorial and valuable examples, which is used in this story as tutorial.

Reference:

https://www.cyberciti.biz/faq/mysql-list-databases/

https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html