SOURCE 文件路径

create database db_name; #增

drop database db_name; #删

alter database exampleDb charset utf8; #改

show databases; #查

#创建表
create table table_name();

#查所有表
show tables;

#查表结构
describle table_name;  

SELECT 列名1, 列名2 FROM 表名;
SELECT * FROM 表名;   -- * 表示所有列

SELECT * FROM 表名 WHERE 条件;

FROM drivers_license AS dl           -- 表别名
SELECT height AS h FROM ...          -- 列别名
FROM drivers_license dl              -- 省略 AS

WHERE name LIKE '%John%'   -- 名字里包含 John

WHERE height BETWEEN 65 AND 67;

WHERE height >= 65 AND height <= 67;

-- ✅ 正确
WHERE sno IN (SELECT sno FROM sc)

-- ❌ 错误（如果子查询返回多行）
WHERE sno = (SELECT sno FROM sc)

-- ✅ 正确
WHERE price IS NULL
WHERE price IS NOT NULL

-- ❌ 错误（永远返回空）
WHERE price = NULL
WHERE price != NULL

SELECT * FROM 表名
ORDER BY 列名 ASC;   -- 升序（默认）
ORDER BY 列名 DESC;  -- 降序

SELECT 列名, COUNT(*)
FROM 表名
GROUP BY 列名;

-- 以下两条语句结果相同
SELECT City FROM customers GROUP BY City;
SELECT DISTINCT City FROM customers;

SELECT person_id, COUNT(*) AS times
FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert'   -- 先过滤行
GROUP BY person_id                          -- 再分组
HAVING COUNT(*) = 3;                        -- 最后过滤组

SELECT DISTINCT 列名1, 列名2 FROM 表名;

-- ❌ 错误
SELECT * FROM sc WHERE AVG(grade) > 75;

-- ✅ 正确
SELECT sno, AVG(grade) FROM sc GROUP BY sno HAVING AVG(grade) > 75;

PRAGMA table_info(表名);          -- 简洁版
SELECT sql FROM sqlite_master WHERE name = '表名';  -- 完整版

SELECT *
FROM 表A
JOIN 表B ON 表A.外键 = 表B.主键;

SELECT *
FROM person
JOIN drivers_license ON person.license_id = drivers_license.id;

SELECT * FROM A JOIN B ON ...      -- 等价于
SELECT * FROM A INNER JOIN B ON ...

FROM 表A
JOIN 表B ON 表A.某列 = 表B.某列
JOIN 表C ON 表B.某列 = 表C.某列;

SELECT person.*, drivers_license.plate_number
FROM person
JOIN drivers_license ON person.license_id = drivers_license.id;

SELECT *
FROM 表A
LEFT JOIN 表B ON 表A.外键 = 表B.主键;

SELECT m.mname, COUNT(s.sno) AS student_count
FROM major m
LEFT JOIN stu s ON m.mno = s.mno
GROUP BY m.mno, m.mname;

-- 标量子查询（返回单个值）
SELECT *
FROM person
WHERE address_number = (
    SELECT MAX(address_number)
    FROM person
    WHERE address_street_name = 'Northwestern Dr'
);

-- 查询超过自己平均成绩的课程
SELECT s.sno, c.cname, s.grade
FROM sc s
JOIN cou c ON c.cno = s.cno
WHERE s.grade > (
    SELECT AVG(grade)
    FROM sc s2
    WHERE s2.sno = s.sno   -- 关联到外层当前学生
);

-- 用派生表实现同上需求
SELECT s.sno, c.cname, s.grade
FROM sc s
JOIN cou c ON c.cno = s.cno
JOIN (
    SELECT sno, AVG(grade) AS avg_grade
    FROM sc
    GROUP BY sno
) AS avg_t ON s.sno = avg_t.sno
WHERE s.grade > avg_t.avg_grade;

-- 找出选了课程 C01 的学生
SELECT sname FROM stu
WHERE EXISTS (
    SELECT 1 FROM sc WHERE sc.sno = stu.sno AND cno = 'C01'
);

-- 找出没有任何评论的商品
SELECT g.id, g.name
FROM goods g
WHERE NOT EXISTS (
    SELECT 1
    FROM goods_comment c
    WHERE c.goods_id = g.id
);

SELECT * FROM drivers_license WHERE id = 423327;

-- 子查询
SELECT * FROM drivers_license
WHERE id = (SELECT license_id FROM person WHERE name = 'Jeremy Bowers');

-- JOIN
SELECT dl.*
FROM person p
JOIN drivers_license dl ON p.license_id = dl.id
WHERE p.name = 'Jeremy Bowers';

SELECT s.sname
FROM stu s
JOIN sc ON s.sno = sc.sno
JOIN cou c ON sc.cno = c.cno
WHERE c.teacher = '张老师'
GROUP BY s.sno, s.sname
HAVING COUNT(DISTINCT c.cno) = (
    SELECT COUNT(*) FROM cou WHERE teacher = '张老师'
);

SELECT sname FROM stu s
WHERE NOT EXISTS (
    SELECT cno FROM cou WHERE teacher = '张老师'
    EXCEPT
    SELECT cno FROM sc WHERE sno = s.sno
);

SELECT *
FROM orders
WHERE Freight = (
    SELECT MAX(Freight) FROM orders
);

SELECT *
FROM orders
ORDER BY Freight DESC
LIMIT 1;

SELECT *
FROM (
    SELECT *, RANK() OVER (ORDER BY Freight DESC) AS rk
    FROM orders
) AS t
WHERE rk = 1;

SELECT s.sno, c.cname, s.grade
FROM sc s
JOIN cou c ON c.cno = s.cno
WHERE s.grade > (
    SELECT AVG(grade)
    FROM sc s2
    WHERE s2.sno = s.sno
);

SELECT s.sno, c.cname, s.grade
FROM sc s
JOIN cou c ON c.cno = s.cno
JOIN (
    SELECT sno, AVG(grade) AS avg_grade
    FROM sc
    GROUP BY sno
) AS avg_t ON s.sno = avg_t.sno
WHERE s.grade > avg_t.avg_grade;

-- 查询 2017年12月的所有记录
WHERE date BETWEEN 20171201 AND 20171231

-- 查询 2018年1月15日
WHERE date = 20180115

-- 比较日期（忽略时间部分）
WHERE DATE(birdate) = '2001-10-01'

-- 查询年龄最大的学生（生日最小）
SELECT * FROM stu ORDER BY birdate ASC LIMIT 1;

-- 查询某年出生的学生
WHERE YEAR(birdate) = 2001;

SELECT      -- 5
FROM        -- 1
WHERE       -- 2
GROUP BY    -- 3
HAVING      -- 4
ORDER BY    -- 6

FROM        -- 1. 确定查询哪张表
WHERE       -- 2. 过滤行（分组前）
GROUP BY    -- 3. 分组
HAVING      -- 4. 过滤组（分组后）
SELECT      -- 5. 选择要显示的列
ORDER BY    -- 6. 排序

-- ❌ 错误：WHERE 放在 GROUP BY 之后
SELECT CustomerID, AVG(Freight)
FROM orders
GROUP BY CustomerID
WHERE ShipCountry = 'Belgium';

-- ✅ 正确
SELECT CustomerID, AVG(Freight)
FROM orders
WHERE ShipCountry = 'Belgium'
GROUP BY CustomerID;

SELECT City
FROM customers
GROUP BY City
ORDER BY FIELD(City, 'Berlin', 'London', 'Paris', 'Madrid');

SELECT City
FROM customers
GROUP BY City
ORDER BY 
    CASE City
        WHEN 'Berlin' THEN 1
        WHEN 'London' THEN 2
        WHEN 'Paris' THEN 3
        WHEN 'Madrid' THEN 4
        ELSE 10
    END;

INSERT INTO 表名 (列1, 列2, 列3) VALUES (值1, 值2, 值3);

INSERT INTO stu (sno, sname, sex, mno, birdate, memo) VALUES
('S001', '张三', 1, '01', '2001-10-01 00:00:00', '二年级，菜'),
('S002', '李四', 0, '02', '2001-05-13 00:00:00', NULL);