返回
数据库 基础能力

SQL学习总结

昨天学习了SQL的基础知识，今天做一记录总结。

参考文献：廖雪峰的官方网站

基本概念

  • SQL : 结构化查询语言（Structured Query Language）, 是一种操作关系数据库的语言。

  • **记录：**表中的一行

  • **字段：**表中的一列

  • NULL: 表示字段数据不存在

    tips:

    • 若字段为NULL不代表其值为0或空串‘ ’

    • 通常情况下，为了加快查询速度（查询时无需判断），应该避免字段允许为NULL

主键

主键可以区分出不同记录的字段，是表中记录的唯一标识

对主键最关键的一点要求是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响

选取主键的一个基本原则是：不使用业务相关的字段作为主键，业务相关的字段有时可能会需要变更

作为主键最好是完全业务无关的字段，一般把这个字段命名为id。常见的可作为id字段的类型有：

  1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样就完全不用担心主键重复，也不用自己预先生成主键；
  2. 全局唯一GUID类型：也称UUID，使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键

注意如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录

联合主键

通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可

非必要尽量不使用联合主键，因为它给关系表带来了复杂度的上升

小结

  • 主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。
  • 可以使用多个列作为联合主键，但联合主键并不常用。

外键

外键是可以把数据与其他表关联起来的字段

一对多

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。

为了表达这种一对多的关系，可以在“多”的那个students表中加入一列class_id，让它的值与classes表的某条记录相对应，这样就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录

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

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表的主键）。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

1
2

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列，只是删除了外键的约束。删除列是通过DROP COLUMN ...实现的。

多对多

通过一个表的外键关联到另一个表，可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：通过中间表teacher_class可知teachers到classes的关系，同理可知classes到teachers的关系

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

例如，students表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表contacts，就可以得到一个“一对一”关系

既然是一对一关系，为什么不给students表增加一个mobile列，这样就能合二为一了？

  • 如果业务允许，完全可以把两个表合为一个表。但是有些时候，如果某个学生没有手机号，那么，contacts表中就不存在对应的记录。实际上，一对一关系准确地说，是contacts表一对一对应students表。
  • 还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

小结

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

例如，对于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 INDEX idx_name_score (name, score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，假设students表的name不能重复：

1
2

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
  • 通过UNIQUE关键字添加了一个唯一索引

也可以只对某一列添加一个唯一约束而不创建唯一索引：

1
2

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);
  • 这种情况下，name列没有索引，但仍然具有唯一性保证

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。这里的意思是说：在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

小结

  • 通过对数据库表创建索引，可以提高查询速度；
  • 通过创建唯一索引，可以保证某一列的值具有唯一性；
  • 数据库索引对于用户和应用程序来说都是透明的。

查询数据

基本查询

1

select * from <表名>;

  • select：表示查询操作

  • *：表示返回所有字段

    • SELECT 列1, 列2, 列3可以仅返回指定字段，这种操作称为投影
    • SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是

  • from: 后跟表名，表示从哪个表查询

条件查询

1
2
3
4

select <字段名> from <表名> 
where <条件表达式>
order by <字段名> desc, <字段名>
limit <pageSize> offset <pageSize*(pageIndex-1)>;

  • where：后跟字段的具体条件，表示结果集仅包含符合条件的记录

  • 条件表达式：

    • 若字段存储的是字符串，则需要用单引号括起来
    • 优先级：NOT、AND、OR
    • <>等同于NOT
    • between and：前闭后闭
    • in (a, b)：取a或b，不是范围
    • LIKE判断相似，例：name LIKE 'ab%'，其中%表示任意字符，ab%可以匹配ab、abc、abcd

  • order by : 后跟字段名，表示按照该字段中的值升序排序

    • desc : 降序排序
    • 若有同一字段内有相同数据，要进一步排序，可以继续添加字段名。

  • 使用LIMIT <pageSize> OFFSET <pageSize * (pageIndex - 1)>可以对结果集进行分页，每次查询返回结果集的一部分

    • 分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值

      • **pageSize：**每页需要显示的结果数
      • **pageIndex：**当前页的索引

    • 在MySQL中，可以省略offset，如：LIMIT 15 OFFSET 30 还可以简写成LIMIT 30, 15

聚合查询

1
2
3

select <字段名>, COUNT(*) 别名 
from <表名> 
group by class_id;

  • 聚合查询：对于统计总数、平均数、最值这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

  • 聚合函数：

    • count(字段名)：查询该字段的记录数
    • sum(字段名)：计算该字段的合计值，该字段值必须为数值类型
    • avg(字段名)：计算该字段的平均值，该字段值必须为数值类型
    • max(字段名)：计算该字段的最大值
    • min(字段名)：计算该字段的最小值

注意：

  1. MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。
  2. 通常，使用聚合查询时，我们应该给字段名设置一个别名，便于处理结果。
  3. 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL
  • group by：后跟字段名，表示按照该字段分组聚合
    • 也可以使用多个列进行分组，例：想统计各班的男生和女生人数
      • SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

连接查询

1
2
3
4

select <字段名>
from <主表名>
inner join <从表名>
on <连接条件>

  • 连接查询：确定一个主表作为结果集，然后，把其他表的记录有选择性地“连接”在主表结果集上。

    • inner join：只返回两张表都存在的记录
    • left outer join：返回主表都存在的记录，如果某一记录仅在主表存在，那么结果集就会以NULL填充剩下的字段。
    • right outer join：返回从表都存在的记录，如果某一记录仅在从表存在，那么结果集就会以NULL填充剩下的字段。
    • full outer join：返回两张表所有记录，自动把对方不存在的列填充为NULL

  • JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

  • JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

修改数据

增

1
2
3

insert into <表名> 
(字段1, 字段2, ...) 
values (值1, 值2, ...)

改

1
2
3

update <表名> 
set 字段1 = 值1, 字段2 = 值2  /  表达式
where <条件表达式>;

在WHERE子句中写出需要更新的行的筛选条件

注意：

  1. UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录
  2. 若UPDATE语句没有WHERE条件，则整个表的所有记录都会被更新
  3. 在执行UPDATE语句时，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新

删

1
2

delete from <表名> 
where <条件表达式>;

WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录

注意：

  1. 和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据
  2. 在执行DELETE语句时，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除

MySQL

命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行

  • 登录
1

mysql -u root -p
  • 退出
1

exit

EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行

数据库

  • 列出所有数据库
1

SHOW DATABASES;

其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们。其他的是用户创建的数据库。

  • 创建一个新的数据库
1

CREATE DATABASE <数据库名>;
  • 删除一个数据库
1

DROP DATABASE <数据库名>;
  • 对一个数据库进行操作时，要首先将其切换为当前数据库
1

USE <数据库名>;

表

  • 列出当前数据库的所有表
1

SHOW TABLES;
  • 查看一个表的结构
1

DESC <表名>;
  • 查看创建表的SQL语句
1

SHOW CREATE TABLE <表名>;
  • 删除表
1

DROP TABLE <表名>;
  • 修改表：新增字段、修改字段名、删除字段
1
2
3

ALTER TABLE <表名> ADD COLUMN <字段名> 字段属性;
ALTER TABLE <表名> CHANGE COLUMN <旧字段名> <新字段名> 字段属性;
ALTER TABLE <表名> DROP COLUMN <字段名>;
  • 快照：复制一份当前表的数据到一个新表；新创建的表结构和SELECT使用的表结构完全一致。
1
2

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
  • 写入查询结果集：若查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中
1

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

记录

  • 插入或替换：若不存在，则插入；若存在，则删除再插入
1

REPLACE INTO <表名> <字段名> VALUES 字段属性;
  • 插入或更新：若不存在，则插入；若存在，则更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：

1

INSERT INTO <表名> <字段名> VALUES 字段属性 ON DUPLICATE KEY UPDATE 字段1 = 值1, 字段2 = 值2;
  • 插入或忽略：若不存在，则插入；若存在，则不做操作
1

INSERT IGNORE INTO <表名> <字段名> VALUES 字段属性;
  • 强制使用指定索引：可以使用FORCE INDEX强制使用指定的索引；在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。指定索引的前提是该索引必须存在。
1

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

事务

事务：多条sql语句作为一个整体，要么全部成功或者全部失败。若事务失败，相当于没有执行这些sql语句，不会对数据库数据有任何改动。

  • 隐式事务：单条SQL语句，数据库系统自动将其作为一个事务执行
  • 显式事务：多条SQL语句，使用BEGIN开启一个事务，使用COMMIT提交一个事务
1
2
3
4

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

用ROLLBACK替换COMMIT，可以回滚事务，整个事务会失败

1
2
3
4

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

隔离级别

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

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level 脏读（Dirty Read） 不可重复读（Non Repeatable Read） 幻读（Phantom Read）
Read Uncommitted Yes Yes Yes
Read Committed - Yes Yes
Repeatable Read - - Yes
Serializable - - -

Read Uncommitted：隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）

Read Committed：在该隔离级别下，一个事务不会读到另一个事务还没有提交的数据，但可能会遇到不可重复读（Non Repeatable Read）的问题。不可重复读是指在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

Repeatable Read：在该隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。幻读就是一个事务在另一个事务第一次查询某个记录之后插入了该记录，另一个事务更新该记录，更新后就能查询到该记录

Serializable：最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

© 2021 - 2024 YI · 0 Days
共叭叭了 2186927字 · 173篇文章

Built with Hugo
Theme Stack designed by Jimmy