SELECT

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;