1.login: 
mysql -u root -p

2.SHOW DATABASES;

3.use a database: 
USE w3schools_practice;

4.SHOW TABLES;

5.print info of a table: 
DESCRIBE customers;

6.SELECT * FROM customers;

7.SELECT CustomerID,City FROM customers WHERE CustomerID % 2 = 1;

8.SELECT COUNT(*) FROM customers;

9.Combine SELECT and functions:
SELECT SUM(colname) FROM customers;
SELECT AVG(colname) FROM customers;
SELECT MIN(colname) FROM customers;

10.One can also SELECT some functions and mathematical expressions unrelated to TABLES:
SELECT Address, Now() FROM customers;
SELECT 1+2;

11.SELECT something in alphabetical order:
SELECT * FROM customers ORDER BY Country ASC; #Default is ascending order, ASC;
SELECT * FROM customers ORDER BY Country DESC;#Descending order;

12.SELECT some rows which satisfy some specific conditions:
SELECT * FROM customers WHERE City LIKE '%m%';#Cannot identify upper case or lower case;
SELECT * FROM customers WHERE City LIKE BINARY '%m%';

13 SELECT and modify column names:
SELECT NOW() AS "current time";
SELECT col1 AS new1, col2 AS new2 WHERE ... FROM customers;

14 SELECT...BETWEEN...:
SELECT * FROM customers WHERE CustomerID BETWEEN 2 AND 5;

15 SELECT... IN...:
SELECT * FROM customers WHERE City IN ('México D.F.','Beijing');
15* SELECT... NOT IN...:
SELECT * FROM customers WHERE City IN ('México D.F.','London');

16 Copy some rows and create a new table from an existing table:
SELECT INTO result * FROM customers;# OTHER VERSIONS OF SQL.
CREATE TABLE result SELECT * FROM customers;#mysql does not support SELECT INTO.

17 Delete an existing table:
DROP TABLE result;

18 SELECT...GROUP BY...:
SELECT Country, SUM(CustomerID) FROM customers GROUP BY Country;
18* Frequency table:
SELECT City, COUNT(City) FROM customers GROUP BY City;

19 Function MID(arg1,arg2,arg3): returns a sub_string (from the middle of a given string).
arg1 specifies strings from which column; arg2 specifies staring index of the selected strings; arg3 specifies the length of the sub_string:
SELECT CustomerName,MID(CustomerName, 1,1) AS 'CN First Letter' FROM customers;

20 Print a frequency table for initial letters of a column, use
 SELECT MID(...) AS...COUNT(*) FROM...GROUP BY MID(...):
 SELECT MID(CustomerName,1,1) AS 'CN', COUNT(*) AS Frequency FROM customers GROUP BY CN;

21 Modify table values:
UPDATE table1 SET City='Sunnyvale' WHERE CustomerID = 1;
UPDATE table1 SET PostCode=94086 WHERE City='Sunnyvale';
UPDATE table1 SET City = 'San Jose' WHERE City IS NULL;

22 Delete a row from table:
DELETE FROM table1 WHERE CustomerID = 0;

23 alter and modify some basics of a column in a table:
ALTER TABLE table1 MODIFY COLUMN CustomerID INT NOT NULL PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE table1 MODIFY COLUMN CustomerName VARCHAR(255) DEFAULT 'Victor';

24 Insert a row into a table:
INSERT INTO table1(City,PostalCode) VALUES('SAN DIEGO',920920);

25 String concatenation:
SELECT CONCAT('Current time is ', NOW(),'.');

26 Create a blank table:
CREATE TABLE names(StudentID INT PRIMARY KEY AUTO_INCREMENT, Name varchar(255) NOT NULL);
CREATE TABLE grades(ID INT PRIMARY KEY AUTO_INCREMENT, GRADE varchar(255), Points DOUBLE);

27 Insert rows:
INSERT INTO grades(ID,Grade,Points) VALUES (1, 'A', 95), (2, 'A+', 100), (3, 'F', 37.5), (4, 'B', 75);

28 an application of SELECT...CONCAT...AS...FROM...:
SELECT ID, CONCAT(Points, '(', Grade, ')') AS Performance FROM grades;

29 Join two tables, Left join two tables, Right join two tables:
SELECT grades.ID,names.name,grades.Grade FROM grades INNER JOIN names on grades.ID=names.StudentID;
SELECT grades.ID,names.name,grades.Grade FROM grades LEFT JOIN names on grades.ID=names.StudentID;
SELECT grades.ID,names.name,grades.Grade FROM grades RIGHT JOIN names on grades.ID=names.StudentID;

30 Self-Join(INNER JOIN, LEFT JOIN, RIGHT JOIN) those rows which have same parity:
SELECT names1.StudentID as ID1, names2.StudentID as ID2 FROM names as names1 INNER JOIN names as names2 ON (names1.StudentID + names2.StudentID) % 2 = 0 AND names1.StudentID != names2.StudentID ORDER BY ID1;

31 Combine CONCAT with INNER JOIN and ORDER BY
SELECT names1.StudentID as ID1, names2.StudentID as ID2, CONCAT(names1.Name, ' vs ',names2.Name) AS Names 
FROM names as names1 
INNER JOIN names as names2 
ON (names1.StudentID + names2.StudentID) % 2 = 0 AND names1.StudentID != names2.StudentID 
ORDER BY ID1;