Meet SQL
A database is an organized collection of data, and MySQL is one of the most popular relational database systems.
SQL (Structured Query Language) is the standard language for working with databases. This course covers core operations such as querying, inserting, updating, and deleting data.
Let's first see which tables the database has:
SHOW TABLES;Take a look at the structure of the students table:
DESC students;SELECT Basics
SELECT retrieves data from a table, and * means all columns.
SELECT * FROM students;You can also list specific column names, separated by commas:
SELECT name, age, gender FROM students;Filtering with WHERE
The WHERE clause filters rows that meet a condition.
SELECT * FROM students WHERE age > 20;Supported comparison operators: = > < >= <= <> !=.
SELECT * FROM students WHERE gender = '女';Combine multiple conditions with AND / OR:
SELECT * FROM students WHERE age >= 20 AND gender = '男';Sorting with ORDER BY
ORDER BY sorts by a column; ASC is ascending (the default) and DESC is descending.
SELECT * FROM students ORDER BY age DESC;You can sort by multiple columns; priority goes from left to right:
SELECT * FROM students ORDER BY dept_id ASC, age DESC;LIMIT & Pagination
LIMIT restricts how many rows are returned, and is often used for pagination.
SELECT * FROM students LIMIT 3;Use LIMIT offset, count for paginated queries:
SELECT * FROM students LIMIT 2, 3;Aggregate Functions
Aggregate functions compute over a set of values and return a single value.
SELECT COUNT(*) FROM students;SELECT AVG(age) FROM students;SELECT MAX(score), MIN(score) FROM enrollments;GROUP BY
GROUP BY groups rows by a column and is usually combined with aggregate functions.
SELECT dept_id, COUNT(*) FROM students GROUP BY dept_id;SELECT gender, AVG(age) FROM students GROUP BY gender;Filtering Groups with HAVING
HAVING filters the results after grouping — think of it as WHERE for groups.
SELECT dept_id, COUNT(*) AS cnt FROM students GROUP BY dept_id HAVING cnt > 2;DISTINCT
DISTINCT removes duplicate rows from the query result.
SELECT DISTINCT gender FROM students;SELECT DISTINCT dept_id, gender FROM students;INSERT
INSERT INTO adds rows to a table.
INSERT INTO students (name, gender, age, dept_id, enroll_year) VALUES ('测试同学', '男', 20, 1, 2024);Query to verify after inserting:
SELECT * FROM students ORDER BY id DESC LIMIT 1;UPDATE
UPDATE modifies rows that meet a condition.
UPDATE students SET age = 21 WHERE name = '赵小明';DELETE
DELETE removes rows that meet a condition.
DELETE FROM students WHERE name = '测试同学';CREATE and DROP TABLE
CREATE TABLE creates a new table; you must define column names and types.
CREATE TABLE clubs (id INT, name VARCHAR, leader VARCHAR);Look at the table you just created:
DESC clubs;DROP TABLE deletes the entire table (structure and data):
DROP TABLE clubs;INNER JOIN
INNER JOIN combines two tables on a join condition and returns only rows that match in both.
SELECT students.name, departments.name AS dept FROM students INNER JOIN departments ON students.dept_id = departments.id;Use table aliases to keep the query short:
SELECT s.name, d.name AS dept FROM students s INNER JOIN departments d ON s.dept_id = d.id LIMIT 5;LEFT JOIN
LEFT JOIN keeps all rows from the left table; when the right table has no match, the result is filled with NULL.
SELECT t.name, d.name AS dept FROM teachers t LEFT JOIN departments d ON t.dept_id = d.id;Joining Multiple Tables
Chaining multiple JOINs lets you combine several tables. Here is a report card (student name + course name + score):
SELECT s.name AS student, c.name AS course, e.score FROM enrollments e INNER JOIN students s ON e.student_id = s.id INNER JOIN courses c ON e.course_id = c.id ORDER BY e.score DESC LIMIT 5;Subquery Basics
A subquery is a query nested inside another query, often used in WHERE conditions.
SELECT name FROM students WHERE age > (SELECT AVG(age) FROM students);Find students older than the average age.
IN and NOT IN
IN matches any value in a list; NOT IN matches values not in the list.
SELECT name FROM students WHERE dept_id IN (1, 2);A list returned by a subquery can also be used with IN:
SELECT name FROM teachers WHERE dept_id IN (SELECT id FROM departments WHERE building = '理学楼');Handling NULL
NULL means missing data. You cannot test it with = NULL; you must use IS NULL.
SELECT * FROM students WHERE age IS NOT NULL;COALESCE returns the first non-NULL value:
SELECT name, COALESCE(age, 0) AS age FROM students LIMIT 3;Useful Functions
UPPER/LOWER change case, LENGTH returns the length, and CONCAT joins strings.
SELECT name, UPPER(name) AS upper_name, LENGTH(name) AS len FROM students LIMIT 3;ROUND rounds a number and ABS returns the absolute value.
SELECT AVG(score) AS avg_score, ROUND(AVG(score), 2) AS rounded FROM enrollments;Putting It All Together
Get the average score of each course, sorted by the average in descending order:
SELECT c.name AS course, ROUND(AVG(e.score), 1) AS avg_score FROM enrollments e INNER JOIN courses c ON e.course_id = c.id GROUP BY c.name ORDER BY avg_score DESC;Find the student with the highest average score:
SELECT s.name, AVG(e.score) AS avg_score FROM enrollments e INNER JOIN students s ON e.student_id = s.id GROUP BY s.name ORDER BY avg_score DESC LIMIT 1;