MariaDB Selecting Data

Posted on In

Select Statement in details

Setup

Set up the table and data first before running the following query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    activeDays INT,
    city VARCHAR(50),
    verified TINYINT(1),
    PRIMARY KEY(id)
);

INSERT INTO customer (name, email, activeDays, city, verified) VALUES('alice', 'alice@example.com', 55, 'Boston', 1);
INSERT INTO customer (name, email, activeDays, city, verified) VALUES('dave', 'dave@example.com', 20, 'New York', 0);
INSERT INTO customer (name, email, activeDays, city, verified) VALUES('bop', 'bop@example.com', 100, 'Boston', 0);
INSERT INTO customer (name, email, activeDays, city, verified) VALUES('cindy', 'cindy@example.com', 1, 'Las Vegas', 0);

LIMIT

Use the LIMIT clause to restrict the number of returned rows.

When you provide an offset m with a limit n, the first m rows will be ignored, and the following n rows will be returned.

syntax

1
LIMIT offset, row_count

example

1
2
SELECT * FROM customer LIMIT 2;
SELECT * FROM customer LIMIT 2,2

ORDER BY

Use the ORDER BY clause to order a resultset, such as that are returned from a SELECT statement. 

1
2
SELECT * FROM customer ORDER BY email;
SELECT * FROM customer ORDER BY city DESC, name;

GROUP BY

use GROUP BY clause in a SELECT statement to group rows together that have the same value in one or more column.

Example:

1
SELECT city, count(*), avg(activeDays) from customer GROUP BY city;

Reference