SQL学习路线地图

 

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 插入新数据 (Create)
INSERT INTO students (id, name, age) VALUES (1, '张三', 20);


-- 查找年龄大于 18 的学生，按年龄降序排列
SELECT DISTINCT name, age 
FROM students 
WHERE age > 18 
ORDER BY age DESC 
LIMIT 5;


-- 计算平均年龄
SELECT AVG(age) FROM students;

-- 合并姓名与固定字符串
SELECT CONCAT('学生姓名: ', name) AS student_info FROM students;


-- 查询学生及其选修的课程名
SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;


-- 查找成绩高于平均分的学生 ID
SELECT student_id 
FROM grades 
WHERE score > (SELECT AVG(score) FROM grades);


CREATE TABLE orders (
    order_id INT PRIMARY KEY,  -- 主键：唯一标识
    user_id INT NOT NULL,      -- 非空约束
    FOREIGN KEY (user_id) REFERENCES users(id) -- 外键：关联用户表
);


-- 为姓名列创建索引以加速查询
CREATE INDEX idx_student_name ON students(name);

-- 创建一个只显示优秀学生的视图
CREATE VIEW elite_students AS
SELECT name, score FROM students WHERE score > 90;


START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 如果没有错误则提交，否则执行 ROLLBACK;
COMMIT;

-- 当删除学生时，自动记录日志
CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
INSERT INTO logs (action, student_id) VALUES ('DELETE', OLD.id);