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 Create Row Query By Condition Query By Order Query By limi, offset Alias, AS Aggregate Function *Group By Update Row Delete Row Alert Column (add new column) *Foreign Key *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 or Missing value 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 **_specified columns_**mysql> List all the rows of the SELECT * FROM products; **_specified columns_**mysql> productID, name List all the rows of the SELECT 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//skip =1 LIMIT 2,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 FROM products ; name, ANY_VALUE(price) 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 to columns of the child table to the parent table : Foreign Key supplierID products suppliers add column with INT type in product table supplierID set all the of the existing records supplierID set as a foreign key column, by related table to parent supplierID products 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 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. JOIN(foreign key) I love and which make n-n relation as the best thing since sliced bread. Combo multi complicated hit by just one SQL query. foreign key select, JION-table1-table2-result JOIN is 1 click with 30 HIT SQL joins are used to combine rows from two or more tables. Default is 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. JOIN INNER JOIN Default JOIN is INNER JOIN in MySQL set all the of the existing records supplierID set as a foreign key column, by related table to parent supplierID products suppliers table mysql> SELECT products.name, price, suppliers.nameFROM products WHERE price < 5; JOIN suppliers ON products.supplierID = suppliers.supplierID 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
![featured image - [MySQL] Note: Row CURD](https://hackernoon.imgix.net/fallback-feat.png?auto=format&fit=max&w=2048)
![[Note] 19 Coding Rules that every coder must know](https://hackernoon.imgix.net/hn-images/1*LwkasdSEFs7rhv21ayrfxA.png?auto=format&fit=max&w=3840)
![[MySQL] Note: Database CURD](https://hackernoon.imgix.net/fallback-feat.png?auto=format&fit=max&w=3840)
