SQL 入门

关键字

• 字段：
• 列
• 记录
• 表
• 主键
• 外键

基本

创建

CREATE DATABASE db;
SHOW DATABSE;
USE db;
DROP DATABASE db;

类型

INT
DECIMAL(m,n)     -- m 位小数, 小数部分 n 位
VARCHAR(n)       -- 字符串
BLOB             -- (Binary Large Object) 图、视频
DATE             -- 'YYYY-MM-DD'
TIMESTAMP        -- 'YYYY-MM-DD HH:MM:SS'

逻辑值

true、false、unknown（与 null 比较）

函数

长度

• length：获取字节长
• char_length：获取字符长

IFNULL(a，b)

如果 a 是 NULL，返回 b，否则返回 a

ROUND(a，n)

保留 a 的 n 位小数，四舍五入

表

CREATE TABLE Student (
    id INT,
    name VARCHAR(32),
    class_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (class_id) REFERENCES CLASS(id) ON DELETE SET NULL
);
CREATE TABLE Class (
    id INT,
    St_id INT
);
INSERT INTO Student (id, name) VALUES (1, 'a');
UPDATE Student SET major = '软件工程' WHERE major IS NULL;
DELETE FROM Student WHERE id = '软件工程'
DROP TABLE IF EXISTS Student

修改表名/字段

ALTER

ALTER TABLE Student RENAME St;
ALTER TABLE St ADD major VARCHAR(32);
ALTER TABLE St MODIFY COLUMN major VARCHAR(16);
ALTER TABLE St RENAME COLUMN major to Major;
ALTER TABLE St DROP COLUMN major;

选择 SELECT

SELECT name FROM Student ORDER BY id DESC LIMIT 3, 2;   --学号排第四和第五的两个学生名字

聚合 COUNT AVG SUM MAX

SELECT COUNT(id) FROM Student;

通配符 %(多字符) _(单字符)

SELECT name FROM Student Where name LIKE '张%';

联合查询 UNION

将多个选择的结果合并在对应列

SELECT name as NAME FROM Student UNION SELECT id FROM Class

连接 JOIN

• INNER JOIN：交（JOIN）
• LEFT JOIN：保留左表记录，未在右表匹配的值 NULL
• RIGHT JOIN：保留右表记录，未在左表匹配的值 NULL
• FULL OUTER JOIN：并，保留所有记录
• CROSS JOIN：笛卡尔积，每条左表记录与每条右表记录组合
• SELF JOIN：
• NATURAL JOIN：同名字段自动匹配连接

SELECT name FROM Student LEFT JOIN Class ON Student.class_id = Class.id

子查询

-- 条件子查询
SELECT name FROM employee WHERE emp_id IN (
    SELECT manager_id FROM branch WHERE branch_name = '研发'
)

-- 列中子查询
select (
    select distinct salary
    from Employee
    order by salary desc
    limit 1, 1
) as SecondHighestSalary

-- 表子查询，必须有别名
select distinct num as ConsecutiveNums
from (
    select num, lead(num, 1) over (order by id) as num1, lead(num, 2) over (order by id) as num2
    from Logs
) t
Where num = num1 and num = num2

WHERE、HAVING、ON

• WHERE 在 GROUP BY 和聚合函数之前对数据进行过滤
• HAVING 对 GROUP BY 和聚合函数之后的数据进行过滤
• 对于外连接，ON 的过滤条件在连接操作之前执行，WHERE 在之后

Python 连接

import mysql.connector
connection = mysql.connector.connect(host='localhost',port='3306',user='root',password='x',database='db')
cursor = connection.cursor()
cursor.execute('SELECT * FROM Student;')
records = cursor.fetchall()
for r in records:
    print(r)