2021-11-02 RD4 review SQL

安装

• 服务mysql
• 可视化软件 navicat

关系模型

• 主键

PRIMARY KEY BIGINT NOT NULL AUTO_INCREMENT

• 外键

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

• 索引

ALTER TABLE students
ADD INDEX idx_score (score);

查询数据

• 基本查询

SELECT * FROM students;

• 条件查询

SELECT * FROM students WHERE score >=80;

• 投影查询

SELECT id, name, score FROM students

• 排序

SELECT id, name, score FROM students 
ORDER BY score DESC

• 分页查询

SELECT id, name, score FROM students 
ORDER BY score DESC
LIMIT 3 OFFSET 0;

• 聚合查询

SELECT count(*) FROM students;

• 多表查询

SELECT * FROM students, classes;

• 连接查询

1. 内连接

SELECT s.id, s.name, c.name class_name, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

2. 外连接

SELECT s.id, s.name, c.name class_name, s.score
FROM students s
RIGHT OUT JOIN classes c
ON s.class_id = c.id;

修改数据

• INSERT

1. 单条记录

INSERT INTO students( class_id, name, score) VALUES (2, 'wang', 80);

2. 多条记录

INSERT INTO students( class_id, name, score) VALUES 
(2, 'wang', 80),
(2, 'wang', 80);

• UPDATE

UPDATE students SET score = '88' WHERE id = 1

• DELETE

DELETE FROM students WHERE score = '99'

事务

数据库事务具有ACID这4个特性

• A: Atomic 原子性
  将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行
• C: Consistent 一致性
  事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100
• I: Isolation 隔离性
  如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离
• D: Duration 持久性
  事务完成后，对数据库数据的修改被持久化存储

普通事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

回滚

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

资料参考：

• SQL教程