> For the complete documentation index, see [llms.txt](https://ret2basic.gitbook.io/ctfnote/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ret2basic.gitbook.io/ctfnote/computer-science/databases/mysql/select.md).

# SELECT

## SELECT Syntax (Other Than SELECT \*)

Create a lab environment:

```sql
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:

```sql
SELECT sales, empid FROM tb;
```

Alias for column names:

```sql
SELECT empid AS employee_id, sales AS sold_items FROM tb;
```

Arithmetics based on column value:

```sql
SELECT sales*10000 FROM tb;
```

## MySQL Functions

Functions for computation (names are self-explanatory):

```sql
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):

```sql
mysql> SELECT PI();
mysql> SELECT VERSION();
mysql> SELECT DATABASE();
mysql> SELECT USER();
```

## String Manipulation

Create a lab environment:

```sql
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:

```sql
SELECT CONCAT(empid, 'Mr.', name) FROM tb1;
```

Slice from the right:

```sql
SELECT RIGHT(empid, 2) FROM tb1;
```

Slice from the left:

```sql
SELECT LEFT(empid, 2) FROM tb1;
```

Slice starting from the 2nd character of `empid` and get a substring of length 3:

```sql
SELECT SUBSTRING(empid, 2, 3) FROM tb1;
```

Repeat for a variable times (used for drawing graphs):

```sql
SELECT REPEAT('.', age) FROM tb1;
```

Print a string reversely:

```sql
SELECT REVERSE(name) FROM tb1;
```

## Date and Time

Get current time with `NOW()`:

```sql
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:

```sql
SELECT * FROM tb LIMIT 3;
```