SELECT Statement Fundamentals

The SELECT statement is fundamental for retrieving data from databases. When used alone, it can access system parameters and configuration settings.

-- View system parameters
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES LIKE 'innodb%';

For comprehensive MySQL function documentation, refer to the official manual:

https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

SQL Statement Structure

MySQL follows this logical order when processing queries:

SELECT columns
FROM tables
WHERE conditions
GROUP BY conditions
HAVING conditions
ORDER BY conditions
LIMIT number;

Understanding Table Structures

Before querying, it's essential to understand the table structuers. Here's the locations table structure:

DESCRIBE locations;

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| Region      | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

Quick ways to understand table structures:

DESCRIBE locations;
SHOW CREATE TABLE locations;
SELECT * FROM locations LIMIT 5;

Basic SELECT with FROM Clause

Retrieve all information from a table:

SELECT id, name, countrycode, region, population FROM locations;
-- Or using wildcard
SELECT * FROM locations;

Retrieve specific columns:

SELECT name, population FROM locations;

Filtering Data with WHERE Clause

Filter data based on conditions:

-- Cities in China with population
SELECT name, population FROM locations WHERE countrycode = 'CHN';

-- Cities with population less than 100
SELECT name, population FROM locations WHERE population < 100;

-- Chinese cities with population over 8 million
SELECT name, population FROM locations 
WHERE countrycode = 'CHN' AND population > 8000000;

-- Cities in China or USA
SELECT name, population FROM locations 
WHERE countrycode = 'CHN' OR countrycode = 'USA';

-- Cities with population between 5M and 6M
SELECT name, population FROM locations 
WHERE population > 5000000 AND population < 6000000;
-- Alternative syntax
SELECT name, population FROM locations 
WHERE population BETWEEN 5000000 AND 6000000;

-- Country codes starting with 'CH'
SELECT * FROM locations WHERE countrycode LIKE 'CH%';

Note: Avoid patterns like '%CH%' as they typically don't use indexes and perform poorly. For frequent searches, consider using Elasticsearch.

Alternative to OR conditions:

SELECT name, population FROM locations 
WHERE countrycode IN ('CHN', 'USA');

Grouping Data with GROUP BY

GROUP BY combines rows with identical values in specified columns:

-- Count cities per country
SELECT countrycode, COUNT(id) FROM locations GROUP BY countrycode;

-- Total population per country
SELECT countrycode, SUM(population) FROM locations GROUP BY countrycode;

-- Count distinct regions per country
SELECT countrycode, COUNT(DISTINCT region) FROM locations GROUP BY countrycode;

-- Total population per Chinese region
SELECT region, SUM(population) FROM locations 
WHERE countrycode = 'CHN' GROUP BY region;

-- Count cities per Chinese region
SELECT region, COUNT(name) FROM locations 
WHERE countrycode = 'CHN' GROUP BY region;

-- List cities per Chinese region
SELECT region, GROUP_CONCAT(name) FROM locations 
WHERE countrycode = 'CHN' GROUP BY region;

-- Formatted output
SELECT CONCAT(region, ":", GROUP_CONCAT(name)) FROM locations 
WHERE countrycode = 'CHN' GROUP BY region;

Sorting and Limiting Results

Combine GROUP BY with HAVING, ORDER BY, and LIMIT:

-- Countries with population over 50M, sorted by population
SELECT countrycode, SUM(population) FROM locations 
GROUP BY countrycode 
HAVING SUM(population) > 50000000 
ORDER BY SUM(population) DESC;

-- Top 3 countries by population over 50M
SELECT countrycode, SUM(population) FROM locations 
GROUP BY countrycode 
HAVING SUM(population) > 50000000 
ORDER BY SUM(population) DESC 
LIMIT 3 OFFSET 0;

-- Alternative pagination syntax
SELECT countrycode, SUM(population) FROM locations 
GROUP BY countrycode 
HAVING SUM(population) > 50000000 
ORDER BY SUM(population) DESC 
LIMIT 3, 3; -- Skip 3, show 3

Practice Exercises

-- Chinese regions with total population under 1M
SELECT region, SUM(population) FROM locations 
WHERE countrycode = 'CHN' 
GROUP BY region 
HAVING SUM(population) < 1000000;

-- All Chinese cities sorted by population
SELECT * FROM locations WHERE countrycode = 'CHN' 
ORDER BY population DESC;

-- Chinese regions by total population
SELECT region, SUM(population) FROM locations 
WHERE countrycode = 'CHN' 
GROUP BY region 
ORDER BY SUM(population) DESC;

-- Top 3 Chinese regions with population over 5M
SELECT region, SUM(population) FROM locations 
WHERE countrycode = 'CHN' 
GROUP BY region 
HAVING SUM(population) > 5000000 
ORDER BY SUM(population) DESC 
LIMIT 3;

Combining Result Sets

UNION and UNION ALL combine multiple result sets:

-- UNION ALL (includes duplicates)
SELECT * FROM locations WHERE countrycode = 'CHN'
UNION ALL 
SELECT * FROM locations WHERE countrycode = 'USA';

-- UNION (removes duplicates)
SELECT * FROM locations WHERE countrycode = 'CHN'
UNION 
SELECT * FROM locations WHERE countrycode = 'USA';

Multi-Table Joins

Joins combine data from multiple tables:

-- Create sample tables
CREATE DATABASE education;
USE education;

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age TINYINT,
    gender ENUM('M', 'F')
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    instructor_id INT
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    grade INT
);

CREATE TABLE instructors (
    instructor_id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Insert sample data
INSERT INTO students (name, age, gender) VALUES
('Zhang San', 20, 'M'),
('Li Si', 21, 'F'),
('Wang Wu', 19, 'M');

INSERT INTO instructors (instructor_id, name) VALUES
(101, 'Professor Zhang'),
(102, 'Professor Li');

INSERT INTO courses (course_id, course_name, instructor_id) VALUES
(1001, 'Database Systems', 101),
(1002, 'Web Development', 102);

INSERT INTO enrollments (student_id, course_id, grade) VALUES
(1, 1001, 85),
(1, 1002, 78),
(2, 1002, 92),
(3, 1001, 88);

Join examples:

-- Count courses taken by Zhang San
SELECT s.name, COUNT(e.course_id)
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE s.name = 'Zhang San';

-- Courses taken by Zhang San
SELECT s.name, GROUP_CONCAT(c.course_name)
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.name = 'Zhang San'
GROUP BY s.name;

-- Students taught by Professor Li
SELECT i.name AS instructor, GROUP_CONCAT(s.name), COUNT(s.name)
FROM instructors i
JOIN courses c ON i.instructor_id = c.instructor_id
JOIN enrollments e ON c.course_id = e.course_id
JOIN students s ON e.student_id = s.student_id
WHERE i.name = 'Professor Li'
GROUP BY i.name;

-- Average grade for Professor Li's courses
SELECT i.name, AVG(e.grade)
FROM instructors i
JOIN courses c ON i.instructor_id = c.instructor_id
JOIN enrollments e ON c.course_id = e.course_id
WHERE i.name = 'Professor Li'
GROUP BY c.course_id;

-- Average grade per instructor and course
SELECT i.name AS instructor, c.course_name, AVG(e.grade)
FROM instructors i
JOIN courses c ON i.instructor_id = c.instructor_id
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY i.name, c.course_name
ORDER BY AVG(e.grade);

-- Students failing Professor Li's course
SELECT i.name AS instructor, s.name AS student, e.grade
FROM instructors i
JOIN courses c ON i.instructor_id = c.instructor_id
JOIN enrollments e ON c.course_id = e.course_id
JOIN students s ON e.student_id = s.student_id
WHERE i.name = 'Professor Li' AND e.grade < 60;

-- All instructors' failing students
SELECT i.name, GROUP_CONCAT(CONCAT(s.name, ":", e.grade))
FROM instructors i
JOIN courses c ON i.instructor_id = c.instructor_id
JOIN enrollments e ON c.course_id = e.course_id
JOIN students s ON e.student_id = s.student_id
WHERE e.grade < 60
GROUP BY i.instructor_id;

Using Aliases

Aliases improve query readability:

-- Table aliases
SELECT t.name AS instructor, GROUP_CONCAT(CONCAT(st.name, ":", e.grade))
FROM instructors AS t
JOIN courses AS c ON t.instructor_id = c.instructor_id
JOIN enrollments AS e ON c.course_id = e.course_id
JOIN students AS st ON e.student_id = st.student_id
WHERE e.grade < 60
GROUP BY t.instructor_id;

-- Column aliases
SELECT t.name AS instructor_name, 
       GROUP_CONCAT(CONCAT(st.name, ":", e.grade)) AS failing_students
FROM instructors AS t
JOIN courses AS c ON t.instructor_id = c.instructor_id
JOIN enrollments AS e ON c.course_id = e.course_id
JOIN students AS st ON e.student_id = st.student_id
WHERE e.grade < 60
GROUP BY t.instructor_id;

Metadata Queries

Access database metadata through information_schema:

-- Table metadata structure
DESCRIBE information_schema.tables;

+-----------------------+---------------------+------+-----+---------+-------+
| Field                 | Type                | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG         | varchar(512)        | YES  |     | NULL    |       |
| TABLE_SCHEMA          | varchar(64)         | NO   |     |         |       |
| TABLE_NAME            | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE            | varchar(64)         | NO   |     |         |       |
| ENGINE                | varchar(64)         | NO   |     |         |       |
| VERSION               | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT            | varchar(10)         | NO   |     |         |       |
| TABLE_ROWS            | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH        | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH           | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH       | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH          | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE             | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME           | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME           | datetime            | YES  |     | NULL    |       |
| CHECK_TIME            | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION       | varchar(32)         | NO   |     |         |       |
| CHECKSUM              | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS        | varchar(255)        | YES  |     | NULL    |       |
| COMMENT               | varchar(2048)       | NO   |     |         |       |
| MAX_INDEX_LENGTH      | bigint(21) unsigned | YES  |     | NULL    |       |
| INSERT_METHOD         | varchar(6)          | YES  |     | NULL    |       |
| DATA_DIRECTORY        | varchar(255)        | YES  |     | NULL    |       |
| INDEX_DIRECTORY       | varchar(255)        | YES  |     | NULL    |       |
| TRAINING_INSTANCE     | varchar(255)        | YES  |     | NULL    |       |
+-----------------------+---------------------+------+-----+---------+-------+

-- All databases and tables
SELECT table_schema, table_name FROM information_schema.tables;

-- All tables in world database
SELECT table_schema, GROUP_CONCAT(table_name) 
FROM information_schema.tables 
GROUP BY table_schema \G;

-- All InnoDB tables
SELECT table_schema, table_name, ENGINE 
FROM information_schema.tables 
WHERE ENGINE = 'InnoDB';

-- Calculate table size (KB) = (avg_row_length * rows + index_length) / 1024
SELECT table_name, 
       (AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH) / 1024 AS size_kb 
FROM information_schema.TABLES 
WHERE table_schema = 'education' AND table_name = 'students';

-- Total size of education database
SELECT table_schema, 
       SUM((AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH)) / 1024 AS total_kb 
FROM information_schema.TABLES 
WHERE table_schema = 'education';

-- Database sizes sorted by size
SELECT table_schema, 
       SUM((AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH)) / 1024 AS total_kb 
FROM information_schema.TABLES 
GROUP BY table_schema 
ORDER BY total_kb DESC;

Dynamic SQL with CONCAT()

Generate SQL statements dynamically:

-- Backup all tables in world database
SELECT CONCAT("mysqldump -uroot -p123 ", table_schema, " ", table_name, 
             " >/backups/", table_schema, "_", table_name, ".sql")
FROM information_schema.tables;

-- Discard tablespace for all tables in education database
SELECT CONCAT("ALTER TABLE ", table_schema, ".", table_name, " DISCARD TABLESPACE;")
FROM information_schema.tables 
WHERE table_schema = 'education';

SHOW Commands

Various SHOW commands provide database information:

SHOW DATABASES;           -- List databases
SHOW TABLES;              -- List tables in current database
SHOW CREATE DATABASE db;  -- Show database creation statement
SHOW CREATE TABLE tbl;    -- Show table creation statement
SHOW PROCESSLIST;         -- Current connections
SHOW CHARSET;             -- Supported character sets
SHOW COLLATION;           -- Supported collations
SHOW GRANTS FOR user;     -- User permissions
SHOW VARIABLES LIKE '%var%'; -- System variables
SHOW ENGINES;             -- Supported storage engines
SHOW INDEX FROM tbl;      -- Table indexes
SHOW ENGINE InnoDB STATUS\G -- InnoDB engine status
SHOW BINARY LOGS;         -- Binary log files
SHOW MASTER STATUS;       -- Current binary log info
SHOW SLAVE STATUS\G;      -- Replication slave status
SHOW STATUS LIKE '%stat%'; -- Database status

GROUP BY Considerations

MySQL's sql_mode affects GROUP BY behavior:

-- Check current sql_mode
SELECT @@sql_mode;

-- Example with ONLY_FULL_GROUP_BY enabled
SELECT user, GROUP_CONCAT(host) FROM mysql.user GROUP BY User;

-- Solution 1: Use functions
SELECT user, GROUP_CONCAT(host) FROM mysql.user GROUP BY User;

-- Solution 2: Disable ONLY_FULL_GROUP_BY
-- In configuration file:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION