MySQL Learning notes-common statements

1. Retrieving Data
1.1. Retrieving a single column: SELECT prod_name from Products;
1.2. Retrieving multiple columns: SELECT prod_id, Prod_name, Prod_price from products;
1.3. Retrieve all Columns: SELECT * from Products;
1.4. Retrieve different lines: SELECT DISTINCT vend_id from Products;
1.5, limit the result: SELECT prod_name from the products limit 5;
SELECT Prod_name from Products LIMIT 3, 4; Returns 4 rows starting at line 3
Note that the first row retrieved is row 0, not line 1.

2. Retrieving sorted data
2.1. Sort data: SELECT Prod_name from the products ORDER by Prod_name;
2.2. Sort by multiple columns: SELECT prod_id, Prod_price, prod_name from the products ORDER by Prod_price, Prod_name;
2.3, specify the sort direction: SELECT prod_id, Prod_price, prod_name from the products ORDER by Prod_price DESC;
sorting multiple columns: SELECT prod_id, Prod_price, prod_name from Products ORDER by Prod_price DESC, Prod_name ASC;
2.4. Find the highest or lowest value: SELECT Prod_price from the products ORDER by prod_price DESC limit 1;

3. Filtering Data
3.1. Use the WHERE clause: SELECT prod_name, prod_price from products WHERE prod_price = 2.50;
3.2. Where clause operator: =, <>,! =, <, <=,;, >=, between
3.2.1, check single value: SELECT Prod_name, prod_price from Products WHERE prod_name= ' fuses ';
3.2.2, mismatch check: SELECT vend_id, prod_name from Products WHERE vend_id <> 1003;
3.2.3, Range value check: SELECT prod_name, prod_price from Products WHERE Prod_price between 5 and ten;
3.2.4, null check: SELECT prod_name from Products WHERE Prod_price is NULL;

4. Data filtering
4.1. Combining WHERE clauses
4.1.1, and Operator: SELECT prod_id, Prod_price, prod_name from products where vend_id = 1003 and Prod_price <=;
4.1.2, or operator: SELECT Prod_name, prod_price from Products WHERE vend_id=1002 OR vend_id=1003;
4.1.3, Calculation Order: SELECT Prod_name, prod_price from Products WHERE (vend_id = 1002 OR vend_id=1003) and Prod_price >= 10 ;
4.2, in operator: SELECT Prod_name, prod_price from Products WHERE vend_id in (1002, 1003) ORDER by Prod_name;
4.3, not operator: SELECT Prod_name, prod_price from Products WHERE vend_id not in (1002, 1003) ORDER by Prod_name;

5. Filter with wildcard characters
5.1. Like operator
5.1.1, percent semicolon (%) wildcard character: represents any number of occurrences of any character, SELECT prod_id, prod_name from the products WHERE prod_name like ' jet% ';
5.1.2, underscore (_) wildcard: Matches a single character, SELECT prod_id, prod_name from the products WHERE prod_name like ' _ Ton anvil ';

6. Search with regular Expressions
6.1. Using the MySQL regular expression
6.1.1, basic character matching: SELECT prod_name from Products WHERE prod_name REGEXP ' n ' ORDER by Prod_name;
SELECT prod_name from Products WHERE prod_name REGEXP ' "ORDER by Prod_name; With the entire column, regexp matches the values in the column)
6.1.2, perform or match: SELECT Prod_name from Products WHERE prod_name REGEXP ' 1000|2000 ' ORDER by Prod_name;
6.1.3, match one of several characters: SELECT prod_name from Products WHERE prod_name REGEXP ' [123] Ton ' ORDER by Prod_name;
6.1.4, matching range: SELECT prod_name from Products WHERE prod_name REGEXP ' [1-5] Ton ' ORDER by Prod_name;
6.1.5, matching special what Suzis asking: SELECT prod_name from Products WHERE prod_name REGEXP ' \ \. ' ORDER by Prod_name;

7. Create a calculated field
7.1, splicing field: SELECT Concat (Vend_name, ' (', Vend_country, ') ') from vendors ORDER by Vend_name;
7.2. Using aliases: SELECT Concat (RTrim (Vend_name), ' (', RTrim (vend_country), ') ') as Vend_title from vendors ORDER by Vend_name ;
7.3. Perform arithmetic calculations: SELECT prod_id, Quantity, Item_price, Quantity*item_price as Expanded_price from order items WHERE Order_n um=20005;

8. Using Data processing functions
8.1. Text processing function: SELECT vend_name, Upper (vend_name) as vend_name_upcase from vendors ORDER by Vend_name;
common text Processing functions: Left (), Length (), Locate (), Lower (), LTrim (), right (), RTrim (), Soundex (), SubString (), Upper
8.2. Date and time processing function: SELECT cust_id, order_num from Orders WHERE year (order_date) = 2005 and Month (order_date) = 9;
8.3, numerical processing function: ABS (), cos (), exp () and so on.

9. Summary data
9.1. AVG () Function: SELECT avg (prod_price) as Avg_price from products;
9.2. Count () function: SELECT count (*) as num_cust from customers;
9.3. Max () function: SELECT MAX (Prod_price) as Max_price from products;
9.4, MIN () function: SELECT MIN (Prod_price) as Min_price from the products;
9.5. SUM () function: SELECT SUM (quantity) as items_ordered from OrderItems WHERE order_num = 2005;
9.6. Aggregation of different values: SELECT MIN (DISTINCT prod_price) as Min_price from the products;

10. Grouped Data
10.2. Create a group: SELECT vend_id, COUNT (*) as Num_prods from the products group by vend_id;
10.3. Filter group: SELECT cust_id, COUNT (*) as orders from orders GROUP by cust_id have COUNT (*) >= 2;
SELECT vend_id, COUNT (*) as Num_prods from Products WHERE Prod_price >= ten GROUP by ve nd_id;
10.4, grouping and sequencing: SELECT order_num, SUM (quantity*item_price) as OrderTotal from OrderItems GROUP by Order_num have SUM (qua Ntity*item_price) >= ORDER by OrderTotal;
10.5. Select clause Order:
SELECT, from, WHERE, GROUP by, have, ORDER by, LIMIT


11. Using sub-query
Select cust_id from Orders where Order_num in (SELECT order_num from order items where prod_id= ' TNT2 ');

Select Cust_name, Cust_state, (select COUNT (*) from orders WHERE orders.cust_id = customers.cust_id) as orders from CU  Stomers ORDER by Dust_name;


12. Junction table
SELECT vend_name, Prod_name, prod_price from vendors, products WHERE vendors.vend_id = products.vend_id ORDER by vend_ Name, Prod_name;

SELECT vend_name, Prod_name, prod_price from vendors INNER JOIN products on vendors.vend_id = products.vend_id ORDER B Y Vend_name, Prod_name;

SELECT vend_name, Prod_name, prod_price from vendors, products, orderitems WHERE vendors.vend_id = products.vend_id an D orderitems.prod_id=produts.prod_id and order_num=20005;

13. Create an advanced junction
13.1. Self-coupling: SELECT p1.prod_id, p1.prod_name from products as P1, products as P2 WHERE p1.vend_id=p2.vend_id and P2.prod_i D= ' dtntr ';
13.2. Outer coupling: SELECT customers.cust_id, orders.order_num from orders left OUTER JOIN orders on customers.cust_id= orders.cust_id;
13.3. Using a junction with aggregation function: SELECT customers.cust_name, customers.cust_id, COUNT (orders.order_num) as Num_ord from customers    INNER JOIN orders on customers.cust_id = orders.cust_id GROUP by customers.cust_id;

14. Inserting Data
INSERT into Customers (Cuts_name, cuts_contact) VALUES (' PPP ', ' PPP ');
INSERT into Customers (Cuts_name, cuts_contact) VALUES (' PPP ', ' PPP ') (' ttt ', ' TTT ') (' QQQ ', ' QQQ ');

15. Update and delete data
UPDATE customers SET cuts_name= ' TTT ', cuts_email= ' [email protected] ' WHERE cuts_id = 10005;
DELETE from Customers WHERE cuts_id=10006;

MySQL Learning notes-common statements