[MySQL] Note: Row CURD
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 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 products
GROUP 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 products
GROUP 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 products
ADD 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 :
- add supplierID column with INT type in product table
- set all the supplierID of the existing records
- set supplierID as a foreign key column, by related products table to parent suppliers table
mysql> ALTER TABLE products
ADD COLUMN supplierID INT UNSIGNED NOT NULL;mysql> UPDATE products SET supplierID = 501;mysql> ALTER TABLE products
ADD 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.
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.
- Default JOIN is INNER JOIN in MySQL
- set all the supplierID of the existing records
- set supplierID as a foreign key column, by related products table to parent suppliers table
mysql> SELECT products.name, price, suppliers.name
FROM products
JOIN suppliers ON products.supplierID = suppliers.supplierID
WHERE price < 5;
You may also like
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
Hacker Noon is how hackers start their afternoons. We’re a part of the @AMI family. We are now accepting submissions and happy to discuss advertising & sponsorship opportunities.
If you enjoyed this story, we recommend reading our latest tech stories and trending tech stories. Until next time, don’t take the realities of the world for granted!