CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    score DECIMAL(5,2) DEFAULT 0,
    stu_id INT,
    sub_id INT,
    FOREIGN KEY (stu_id) REFERENCES students(id),
    FOREIGN KEY (sub_id) REFERENCES subjects(id)
) CHARSET=utf8;

INSERT INTO scores (id, score, stu_id, sub_id) 
VALUES (1, 90, 1, 2);

CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    score DECIMAL(5,2) DEFAULT 0,
    stu_id INT,
    sub_id INT,
    FOREIGN KEY (stu_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (sub_id) REFERENCES subjects(id) ON DELETE CASCADE
) CHARSET=utf8;

-- 写法1
SELECT students.name, subjects.title, scores.score
FROM scores
INNER JOIN students ON scores.stu_id = students.id
INNER JOIN subjects ON scores.sub_id = subjects.id;

-- 写法2
SELECT students.name, subjects.title, scores.score
FROM students
INNER JOIN scores ON scores.stu_id = students.id
INNER JOIN subjects ON scores.sub_id = subjects.id;

-- 写法3
SELECT students.name, subjects.title, scores.score
FROM subjects
INNER JOIN scores ON scores.sub_id = subjects.id
INNER JOIN students ON scores.stu_id = students.id;

SELECT *
FROM students
LEFT JOIN scores ON students.id = scores.stu_id;

SELECT *
FROM scores
LEFT JOIN students ON students.id = scores.stu_id;

SELECT *
FROM students
RIGHT JOIN scores ON students.id = scores.stu_id;

SELECT *
FROM scores
RIGHT JOIN students ON students.id = scores.sub_id;

-- 省略重复列名的表名前缀
SELECT name, title, score
FROM scores
INNER JOIN students ON scores.stu_id = students.id
INNER JOIN subjects ON scores.sub_id = subjects.id;

-- 使用表别名简化查询
SELECT name, score
FROM scores AS sco
RIGHT JOIN students AS stu ON stu.id = sco.stu_id;

-- 查询学生的姓名、平均分
SELECT students.name AS 姓名, AVG(scores.score) AS 平均分
FROM scores
INNER JOIN students ON scores.stu_id = students.id
GROUP BY students.name;

-- 查询男生的姓名、总分  
SELECT students.name, SUM(scores.score)
FROM scores
INNER JOIN students ON scores.stu_id = students.id
WHERE students.gender = 1
GROUP BY students.name;

-- 查询科目的名称、平均分
SELECT subjects.title, AVG(scores.score)
FROM scores
INNER JOIN subjects ON scores.sub_id = subjects.id
GROUP BY subjects.title;

-- 查询未删除科目的名称、平均分、最高分
SELECT subjects.title, AVG(scores.score), MAX(scores.score)
FROM scores
INNER JOIN subjects ON scores.sub_id = subjects.id
WHERE subjects.is_deleted = 0
GROUP BY subjects.title;

CREATE TABLE areas (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    title VARCHAR(40),
    pid INT,
    FOREIGN KEY (pid) REFERENCES areas(id)
);

# 拷贝SQL文件到服务器
scp areas.sql user@server:/path/to/

# 登录MySQL，选择数据库
mysql -u root -p
USE mydb;

# 执行SQL文件
SOURCE /path/to/areas.sql;

-- 查询省级单位数量
SELECT COUNT(*) FROM areas WHERE pid IS NULL;

-- 查询名为"山西省"的所有城市
SELECT city.*
FROM areas AS city
INNER JOIN areas AS province ON city.pid = province.id
WHERE province.title = '山西省';

-- 查询名为"广州市"的所有区县
SELECT district.*
FROM areas AS district
INNER JOIN areas AS city ON city.id = district.pid
INNER JOIN areas AS province ON province.id = city.pid
WHERE city.title = '广州市';

CREATE VIEW view_name AS
SELECT ...

DROP VIEW view_name;

-- 开启事务
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 查看数据库引擎
SHOW ENGINES;

-- 查看表的创建语句
SHOW CREATE TABLE table_name;

-- 修改表引擎为InnoDB  
ALTER TABLE table_name ENGINE = InnoDB;

-- 查看索引
SHOW INDEX FROM table_name;

-- 创建索引
CREATE INDEX index_name ON table_name (column_name);

-- 删除索引
DROP INDEX index_name ON table_name;

-- 开启查询分析
SET profiling = 1;

-- 执行查询语句
SELECT ...

-- 查看查询耗时
SHOW PROFILES;

-- 为areas表的title列创建索引
CREATE INDEX idx_areas_title ON areas (title(20));

-- 执行查询语句
SELECT ...

-- 查看查询耗时
SHOW PROFILES;