SELECT Syntax (Other Than SELECT *)
Create a lab environment:
mysql> CREATE DATABASE db1;
mysql> use db1;
mysql> CREATE TABLE tb (empid VARCHAR(10), sales INT, month INT);
mysql> INSERT INTO tb VALUES ('A103', 101, 4);
mysql> INSERT INTO tb VALUES ('A102', 54, 5);
mysql> INSERT INTO tb VALUES ('A104', 181, 4);
mysql> INSERT INTO tb VALUES ('A101', 184, 4);
mysql> INSERT INTO tb VALUES ('A103', 17, 5);
mysql> INSERT INTO tb VALUES ('A101', 300, 5);
mysql> INSERT INTO tb VALUES ('A102', 205, 6);
mysql> INSERT INTO tb VALUES ('A104', 93, 5);
mysql> INSERT INTO tb VALUES ('A103', 12, 6);
mysql> INSERT INTO tb VALUES ('A107', 87, 6);
Select columns in a specific order:
SELECT sales, empid FROM tb;
Alias for column names:
SELECT empid AS employee_id, sales AS sold_items FROM tb;
Arithmetics based on column value:
SELECT sales*10000 FROM tb;
MySQL Functions
Functions for computation (names are self-explanatory):
mysql> SELECT AVG(sales) FROM tb;
mysql> SELECT SUM(sales) FROM tb;
mysql> SELECT COUNT(sales) FROM tb;
Functions for displaying information (names are self-explanatory):
mysql> SELECT PI();
mysql> SELECT VERSION();
mysql> SELECT DATABASE();
mysql> SELECT USER();
String Manipulation
Create a lab environment:
mysql> CREATE TABLE tb1 (empid VARCHAR(10), name VARCHAR(10), age INT);
mysql> INSERT INTO tb1 VALUES('A101', 'ABC', 40);
mysql> INSERT INTO tb1 VALUES('A102', 'DEF', 28);
mysql> INSERT INTO tb1 VALUES('A103', 'GHI', 20);
mysql> INSERT INTO tb1 VALUES('A104', 'JKL', 23);
mysql> INSERT INTO tb1 VALUES('A105', 'MNO', 35);
String concatenation:
SELECT CONCAT(empid, 'Mr.', name) FROM tb1;
Slice from the right:
SELECT RIGHT(empid, 2) FROM tb1;
Slice from the left:
SELECT LEFT(empid, 2) FROM tb1;
Slice starting from the 2nd character of empid and get a substring of length 3:
SELECT SUBSTRING(empid, 2, 3) FROM tb1;
Repeat for a variable times (used for drawing graphs):
SELECT REPEAT('.', age) FROM tb1;
Print a string reversely:
SELECT REVERSE(name) FROM tb1;
Date and Time
Get current time with NOW():
mysql> CREATE TABLE t_now (a INT AUTO_INCREMENT PRIMARY KEY, b DATETIME);
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
SELECT with Conditions
Limit the number of records:
SELECT * FROM tb LIMIT 3;
