sql基本知识点

Posted on | In

SQL

SQL就是处理关系数据库的标准语言.
非关系数据库(MongoDB, Cassandra, Dynamo等等)

数据库模型

  1. 层次模型
    以“上下级”的层次关系来组织数据的一种方式, 层次模型的数据结构看起来就像一棵树
    层次模型
  2. 网状模型
    把每个数据节点和其他很多节点都连接起来, 它的数据结构看起来就像很多城市之间的路网
    网状模型
  3. 关系模型
    把数据看作一个二维表格, 任何数据都可以通过行号+列号来唯一确定

关系模型

主流关系数据库

  1. 商用数据库, Oracle, SQL Server, DB2等
  2. 开源数据库, MySQL, PostgreSQL等
  3. 桌面数据库, Access等, 适用桌面应用程序使用
  4. 嵌入式数据库, Sqlite等, 适合手机应用和桌面程序

MySQL

MySQL是目前应用最广泛的开源关系数据库. 本身实际上只是一个SQL接口, 内部还包含了多种数据引擎：

  1. InnoDB
  2. MyISAM(不支持事务)

使用MySQL时, 不同的表可以使用不同的数据库引擎. 如果不知道采用哪一种, 可以选择InnoDB就可以了.

MariaDB

使用XtraDB引擎

Aurora

由Amazon改进的一个MySQL版本, 专门提供给在AWS托管MySQL用户

PolarDB

Alibaba改进的MySQL版本

关系模型

关系数据库建立在关系模型上, 关系是通过主键和外键来维护的.

主键

对于关系表, 任意两条记录不能重复. 指能够通过某个字段唯一区分出不同的记录, 这个字段称为主键
如果我们把名字作为主键, 那么在数据库中就不能存在两个名字是一样的人的数据
一旦记录插入到表中, 最好不要再修改主键内容. 所以对于主键的选择原则：不适用任何业务相关的字段作为主键
作为主键最好是业务无关的字段, 一般我们会使用id. 常见的可作为id字段的类型有:

  1. 自增整数类型：数据库会再插入数据时自动为每一条记录分配一个自增整数
  2. 全局唯一GUID类型：使用一种全局唯一的字符串作为主键. GUID算法通过网卡MAC地址, 时间戳和随机数保证任意计算机再任意时间生成的字符串都是不同的.

大部分情况下, 使用自增类型的主键就能满足需求. 如果使用INT自增类型, 那么记录数超过21亿的时候, 会到达上限而出错. 使用BIGINT自增类型可以最多存储922亿亿条数据

联合主键

关系数据库还允许通过多个字段唯一标识记录, 即两个或更多的字段都设置为主键, 这种主键被称为联合主键.对于联合主键只要不是所有主键都重复即可.(但是使用联合主键增加了表的复杂度, 尽量不使用)

外键

可以通过一个字段把数据与另一张表关联起来, 这种列称为外键
外键并不是通过列名实现的, 而是通过定义外键约束实现的

1
2
3
4
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

外键约束的名称fk_class_id可以是任意, FOREIGN KEY (class_id)指定了class_id作为外键, REFERENCES classes (id)指定了这个外键将关联表classes中的id列(也是就该表的主键)
通过定义外键约束, 关系数据库可以保证无法插入无效的数据. 如果classes表内不存在id=99的记录, 那么students表中就无法插入class_id=99的记录.
一般情况下外键约束会降低数据库的性能, 所以有些时候class_id仅仅是一个普通的列, 只是它起到了外键的作用.

多对多

通过一个表的外键关联到另一个表, 我们可以定义出一对多关系. 有些时候, 还需要定义多对多关系.
多对多关系实际上是通过两个一对多关系实现的, 即通过一个中间表, 关联两个一对多关系, 就形成了多对多关系.
teacher表：

id name
1 张老师
2 王老师
3 李老师

classes表:

id name
1 一班
2 二班

teacher_class：

id teacher_id class_id
1 1 1
2 1 2
3 2 1
4 2 2

通过中间表teacher_class可以知道teachers和classes之间的关系

一对一

一般情况下, 会将一个大表拆成两个一对一的表, 目的是把经常读取和不经常读取的字段分开, 以获得更高的性能.

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构. 通过索引, 可以让数据库系统不扫描整个表, 直接定位到符合条件的记录, 可以大大加快查询速度.
对于students表,
students
如果经常根据score进行查询, 就可以对score创建索引:

1
2
ALTER TABLE students
ADD INDEX idx_score (score)

使用ADD INDEX idx_score (score) 就创建了一个名称为idx_score, 使用列score的索引. 索引名称是任意的, 如果索引有多列, 可以再括号内依次写上.
索引的效率取决于索引列的值是否散列, 该列的值越不相同, 那么索引效率越高.
可以对一个表创建多个索引, 索引的优点是调高了查询效率, 缺点是插入,更新和删除记录时, 需要同时修改索引. 所以索引越多, 插入、更新和删除记录的速度越慢.

唯一索引

设计关系数据表的时候, 看上去唯一的列 因为具有业务含义不能作为主键.
但是这些列根据业务要求具有唯一性约束, 那么可以给该列添加一个唯一索引.

1
2
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们添加了一个唯一索引, 也可以支队某一列添加一个唯一约束而不创建唯一索引

1
2
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这个时候name列没有索引, 但是仍然具有唯一性保证
无论是否创建索引, 对于用户和应用程序来说, 没有区别. 只不过存在索引的时候, 我们的查询速度会变快.

查询数据

我们有两张表students和classes, 结构如下:
students表：

id class_id name gender score

classes表:

id name

条件查询

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM classes;
# 查询classes表中的所有数据
SELECT * FROM students WHERE score >= 80;
# 查询students中所有score大于等于80的学生信息
SELECT * FROM students WHERE score >= 80 OR gengder = 'M';
SELECT * FROM students WHERE score >= 80 AND gengder = 'M';
SELECT * FROM students WHERE NOT class_id = 2;
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
# 多条件查询的时候, 可以用()来表示如何进行条件运算
SELECT * FROM students WHERE score LIKE '%8';
# 查询所有成绩是8结尾的学生信息

投影查询

1
2
SELECT id, name FROM students WHERE score >= 80;
# 仅返回指定列, 这种操作称为投影

排序

1
2
3
4
5
6
SELECT id, name, gender FROM students ORDER BY score;
# 默认按照分数从低到高排列
SELECT id, name, gender FROM students ORDER BY score DESC;
# 如果要从高到低排列, 在最后添加DESC参数就可以了
SELECT id, name, gender FROM students ORDER BY score DESC, gender;
# 如果有score一样的情况下, 按gender排列

分页

1
2
3
4
5
6
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
# LIMIT 3 OFFSET 0表示对结果集从0号记录开始, 最多取3条.
# 查询第二页内容的话 LIMIT 3 OFFSET 3

聚合查询

1
2
3
4
SELECT COUNT(*) FROM students;
# 查询students中总共有多少条数据
SELECT COUNT(*) num FROM students;
# 可以将查询的结果集列名设置为num

除了COUNT(), SQL还提供如下的聚合函数:

函数 说明
SUM 计算某一列的合计值, 该列必须为数值类型
AVG 计算某一列的平均值, 该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值 
1
2
3
4
5
SELECT AVG(score) average FROM students WHERE gender = 'M';
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
# 查询所有班级内学生的数量
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
# 查询所有班级内男女学生的数量

多表查询

1
2
SELECT * FROM students, classes;
# 查询两张表内的所有内容(笛卡尔查询)

多表查询又称为笛卡尔查询, 返回的结果集是目标表的行数乘积.

1
2
3
4
5
6
7
8
9
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
# 设置别名

连接查询

对多个表进行JOIN计算

1
2
3
4
5
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
# 根据students中的class_id找到classes中对应的行, 再取出name列

INNER JOIN查询的写法是:

  1. 先确定主表, 仍然使用FROM <表1>的语法
  2. 再确定需要连接的表, 使用INNER JOIN <表2>的语法
  3. 然后再确定连接条件, 使用ON <条件…>
  4. 可选, 加上WHERE或者 ORDER BY等子句
1
2
3
4
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

INNER JOIN只返回同时存在与=于两张表的行数据.
RIGHT OUTER JOIN 返回右表都存在的行
LEFT OUTER JOIN 返回左表都存在的行
FULL OUTER JOIN 返回两张表所有的记录, 并自动把对方不存在的列填充为NULL

修改数据

INSERT

INSERT语句的基本语法: INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);
也可以一次插入多条数据

1
2
3
INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

UPDATE

UPDATE语句的基本语法: UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;

1
2
3
UPDATE students SET name='大牛', score=66 WHERE id=1;  
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
UPDATE students SET score=score+10 WHERE score<80;

UPDATE语句不需要WHERE条件就可以执行, 如果不加条件就会更新表内的所有数据.

DELETE

DELETE语句的基本语法: DELETE FROM <表名> WHERE …;
和UPDATE类似, 不加WHERE条件就会删除整个表的信息

管理MySQL

此处后面补充内容

实用SQL语句

  1. 插入和替换
    如果我们要插入一条新记录, 但是如果记录已经存在就需要先删除再插入
    REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明’, ‘F’, 99);
    如果id为1的记录不存在就直接插入, 如果存在则删除后插入
  2. 插入或更新
    1
    INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

如果id=1的记录不存在, 则插入数据; 如果存在, 则更新指定的字段的内容

  1. 插入或忽略
    1
    INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

如果id=1的记录不存在则插入数据; 如果存在则忽略该操作

  1. 快照
    CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
    复制当前表的数据到一个新表(新表的内容就是select的内容)
  2. 写入查询结果集
    创建一个新表, 把查询的内容写入新表中 
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE statistics (
        id BIGINT NOT NULL AUTO_INCREMENT,
        class_id BIGINT NOT NULL,
        average DOUBLE NOT NULL,
        PRIMARY KEY (id)
    );
    INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

事务

在执行业务语句的时候要求, 几个操作都要执行, 如果有操作执行失败, 那么其他操作全部撤销

1
2
3
4
5
-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这种把多个SQL语句作为一个整体进行操作的功能, 被称为数据库事务. 如果事务操作失败, 那么效果就和没有执行SQL语句一样, 不会对数据库数据有任何改动
数据库事务具有ACID这4个特性：

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

对于单条SQL语句, 数据库系统自动将其作为一个事务执行, 这种事务被称为隐式事务
使用BEGIN开启一个事务, 使用COMMIT提交一个事务, 这种事务被称为显式事务

1
2
3
4
5
# 将上述操作改为一个显式事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

COMMIT是指提交事务, 把事务内所有SQL所作的需改永久保存, 如果COMMIT失败, 那么整个事务也会失败
使用ROLLBACK回滚事务, 整个事务会失败

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

隔离级别

对于两个并发执行的事务, 如果涉及到操作同一条记录的时候, 可以能会发生问题. 因为并发操作会带来数据的不一致性, 包括脏读、不可重复读、幻读等. 数据库系统提供隔离级别让我们有针对性地选择事务的隔离级别, 避免数据不一致的问题.

Isolation 脏读(Dirty Read) 不可重复读(Non Repeatedable Read) 幻读(Phantom Read)
Read Uncommitted YES YES YES
Read Committed - YES YES
Repeatable - - YES
Serializable - - -

Read Uncommitted

隔离级别最低的事务. 这个级别下, 一个事务会读到另一个事务更新后但是未提交的数据. 如果另一个事务回滚, 那么当前事务读到的就是脏数据.

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2 BEGIN; BEGIN;
3 UPDATE students SET name = ‘Bob’ WHERE id = 1;
4 SELECT * FROM students WHERE id = 1;
5 ROLLBACK;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;

Read Committed

在该隔离级别下, 一个事务可能遇到不可重复读(Non Repeatable Read)问题
不可重复读,是指一个事务内多次读同一数据, 这个事务还没有结束时, 另一个事务恰好修改了这个数据, 那么第一个事务中两次读取的数据就可能不一致

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 1;
4 UPDATE students SET name = ‘Bob’ WHERE id = 1;
5 COMMIT;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;

Repeatable Read

这个隔离级别下, 一个事务可能遇到幻读的问题
幻读, 是指一个事务中, 第一次查询某条记录, 发现没有, 但是当试图更新这条不存在的记录时竟能成功, 并且再次读取到同一条记录就出现了

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 99;
4 INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5 COMMIT;
6 SELECT * FROM students WHERE id = 99;
7 UPDATE students SET name = ‘Alice’ WHERE id = 99;
8 SELECT * FROM students WHERE id = 99;
9 COMMIT;

Serializable

最严格的隔离级别, 在Serializable级别下, 所有事务按照次序依次执行.
但是由于事务是串行执行, 效率会大大下降, 一般只有特别重要的情景才会使用该隔离级别

默认隔离级别

如果没有指定隔离级别, 数据库会使用默认的隔离级别. 在MySQL中, 使用InnoDB, 默认的隔离级别为Repeatable Read