Jico

Sql

2023/10/30 共 2841 字，约 9 分钟

base information

1、数据一般分三种存储模型

  • 层次模型：以上下级的层次关系来组织数据
  • 网络模型：把每个数据节点和其他很多节点连接起来
  • 关系模型：关系模型可以吧数据看做一个二维表格，任何数据都可以通过行号+列号唯一确定 2、SQL
  • SQL是结构化查询语言(Structured Query Language)的缩写
  • SQL语言关键字不区分大小写，但是不同的数据库表现可能不同，推荐的做法是，所有的关键字总是大写 3、关系模型
  • 通常情况下，字段应该避免允许为NULL，不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL
  • 在关系数据库中，关系是通过主键和外键来维护的
  • 主键：能够通过某个字段唯一区分不同的数据记录
  • 选取主键原则：不使用任何业务相关的字段作为主键，而应该使用BIGINT自增或者GUID类型。这里是因为一旦因为某些业务场景发生主键的变更，会很麻烦。身份证号、手机号、邮箱等，都不能用作主键。

  • 一般定义主键为BIGINT NOT NULL AUTO_INCREMENT类型。

  • 当两个表通过主键关联时，一般将一个表的主键显式关联到另一个表中，这样方便知道两个表的两条记录对应关系。这个关联的列称为外键。
  • 外键不是通过额外维护一列的方式实现的，而是通过定义外键约束实现的，如下语法 ALTER TABLE students ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes (id); 在students表中定义一个外键名为fk_class_id(名称任意)，指定class_id为外键，并将这个外键关联到classes表的id列。 通过定义外键约束，关系数据可可以保证无法插入无效的数据，即如果classes表不存在id=99的记录，则students表就无法插入class_id=99的记录。 但由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅依靠程序自身来保证逻辑的正确性。这种情况下，class_id就是一个普通、依靠程序维护的列，模拟外键的作用。
  • 要删除一个外键约束，也通过ALTER TABLE实现 ALTER TABLE students DROP FOREIGN KEY fk_class_id; 需要注意这里只是删除外键，并不删除这列数据，删除列通过DROP COLUMN实现

  • 存在对应关系的表大部分都能合并为一个表，多几列数据而已，之所以拆分，有时候是因为部分信息并不作为常用信息，通过关联的方式，既方便查询，又保证了常用信息的查询性能。

  • 索引
  • 在关系型数据库中，如果存在大量数据时，为了查询效率，就需要使用索引。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。 通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，大大提高了查询速度。 ALTER TABLE students ADD INDEX idx_score (score); 这里给students表的score列创建了一个名为idx_score的索引。 ALTER TABLE students ADD INDEX idx_name_score (name, score); 索引支持多列。 身份证号码、邮箱等具有唯一性的数据列最适合当做索引，但是不一定适合作为主键。
  • 索引的效率取决于索引列的值是否散列，即该列的值越互不相同，则索引效率越高。如果某列的值大多数都是相同的，那创建索引就没有意义。
  • 可以对一张表创建多个索引，索引的优势是可以提高查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除数据的速度就越慢。
  • 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

  • 数据库索引对于用户和应用程序来说都是透明的，是在数据库内部维护的。

  • SELECT不一定必须要接FROM语句，可以直接执行SELECT 1+2; 虽然SELECT可以用作计算，但这不是SQL的强项。

  • 一般会使用SELECT 1;来测试是否有数据库连接。 SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。

  • 条件查询 SELECT * FROM <表名> WHERE <条件表达式> 其中条件表达式，可以用并列条件的形式<条件1> AND <条件2> 或可以用OR 非可以用NOT 如果不加括号，条件运算按照NOT、AND、OR的优先级进行，NOT优先级最高。加上括号可以改变优先级。 SELECT * FROM students WHERE (score < 80 OR score > 80) AND gender = 'M' AND NOT score=55;

  • 投影查询 我们可以通过制定列的形式，使查询结果只返回指定的几列数据，这种操作成为投影查询。 SELECT 列1, 列2, 列3 FROM <表名> WHERE <条件>; 投影查询可以给查询的列起别名，投影查询可以结合WHERE实现复杂的查询 SELECT id, score points, name, gender sex FROM students WHERE gender = 'M'; 这里从students表中选定id列、score列重命名为points，name列，gender列重命名为sex，同时gender为M的数据。

  • 排序 一般情况下，查询都是按照主键排序的，如果想改变排序，可以声明排序字段，默认为ASC升序 SELECT id, name, gender, score FROM students ORDER BY score; 通过添加DESC声明倒序 SELECT id, name, gender, score FROM students ORDER BY score DESC; 当其中几列的score相同时，此时可以额外声明二次排序字段，如下 SELECT id, name, gender, score FROM students ORDER BY score DESC, gender; 当score相同时，通过gender排序

如果配合WHERE查询时，排序语句需要后置 SELECT id, name, gender, score FROM students WHERE class_id = 1 ORDER BY score DESC;

  • 分页 分页一般通过如下语法实现：LIMIT OFFSET 依据主键，偏移M条，查询N条 example：每页3条，查询第三页 SELECT id, name, score FROM students WHERE gender = 'M' ORDER BY score DESC LIMIT 3 OFFSET 6; 当OFFSET越大时，查询效率越低； 当OFFSET超过实际条目时，会返回Empty result set，并不会报错； OFFSET可选，当不写时，等价于OFFSET 0； LIMIT 3 OFFSET 6 可以简写为 LIMIT 6, 3; 如何得知共有多少页或者共有多少条呢？ SELECT CEILING(COUNT(*) / 3) from students; 其中SELECT count(*)表示查询数据有多少条；CEILING表示向上取整

  • 聚合查询

Search

    Table of Contents