SQL基础

目录

 

1 SQL简介

1.1 SQL

1.2 数据模型

1.3 主流关系数据库

2 关系模型

2.1 关系模型

2.2 主键

2.3 外键

2.4 索引

3 查询数据

3.1 基本查询

3.2 条件查询

3.3 投影查询

3.4 排序

3.5 分页查询

3.6 聚合查询

3.7 多表查询

3.8 连接查询

4 修改数据

4.1 INSERT

4.2 UPDATE

4.3 DELETE

5 实用SQL语句

6 事务

6.1 简介

---

1 SQL简介

1.1 SQL

SQL就是访问和处理关系数据库的计算机标准语言。

操作数据库的能力：

• DDL：Data Definition Language；DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
• DML：Data Manipulation Language；DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
• DQL：Data Query Language；DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

1.2 数据模型

层级模型：以“上下级”的层次关系来组织数据的一种方式

网状模型：每个数据节点和其他很多节点都连接起来

关系模型：把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表。关系数据库的关系模型是基于数学理论建立起来的，更易理解和使用。

1.3 主流关系数据库

商用数据库：Oracle、SQL Server、DB2

开源数据库：MySQL、PostgreSQL

桌面数据库：以微软Access为代表，适用于桌面应用程序

嵌入式数据库：以Sqlite为代表，适用于手机应用和桌面程序

 

2 关系模型

2.1 关系模型

关系数据库是建立在关系模型上的，而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

在关系数据库中，关系是通过主键和外键来维护的。

2.2 主键

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

记录一旦被插入表中，主键最好不要修改，因为主键是用来唯一定位记录的，修改了主键会造成一系列的影响。

选取主键的原则：不能使用任何和业务相关的字段作为主键，主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

可以使用多个列作为联合主键，但联合主键并不常用。

2.3 外键

关系数据库通过外键来实现一对多、多对一、多对多的关系。

外键可以通过数据库来进行约束，也可以不设置约束，通过业务逻辑来保证。

2.4 索引

在关系数据库中，如果有上万条或者上亿条记录，在查找记录时，需要获得较快的速度可以用索引。

索引是关系数据库中，一列或多列的预排列的数据结构。通过索引就不必查询整张表，可以只查询某几列，直接定位符合条件的记录。

通过创建唯一索引，可以保证某一列的值具有唯一性。

创建索引：

CREATE INDEX index_name ON table_name (column_name);

 

3 查询数据

3.1 基本查询

整张表的数据，二维表

select * from <表名>;

3.2 条件查询

筛选出符合条件的记录

select * from <表名> where 条件;

3.3 投影查询

返回指定列

select 列1，列2，列3 from 表;

3.4 排序

desc：降序；不写/asc：升序。

select * from 表名 order by 字段名;

3.5 分页查询

m：一页展示几条记录；n：从第几条记录开始展示。缩写：LIMIT n,m。

select * from 表名 LIMIT<m> OFFSET<n>;

每页3条记录，如何通过聚合查询获得总页数？CEILING()向上取整。

SELECT CEILING(COUNT(*) / 3) FROM students;

3.6 聚合查询

查询一张表有几条数据

select count（*）from 表名;

• count() 总计
• SUM() 计算某一列的合计值，该列必须为数值类型
• AVG() 计算某一列的平均值，该列必须为数值类型
• MAX() 计算某一列的最大值
• MIN() 计算某一列的最小值

3.7 多表查询

使用多表查询可以获取M x N行记录  

select * from 表1,表2;

3.8 连接查询

1、先确定主表，仍然使用FROM <表1>的语法；

2、再确定需要连接的表，使用INNER JOIN <表2>的语法；

3、然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；

4、可选：加上WHERE子句、ORDER BY等子句。

连接方式： INNER JOIN(内连接)、LEFT OUTER JOIN（左连接）、RIGHT OUTER JOIN（右连接）、FULL OUTER JOIN（外连接）。

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;

 

4 修改数据

4.1 INSERT

插入一条新记录

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

4.2 UPDATE

更新数据库表中的记录

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

4.3 DELETE

删除数据库表的记录

DELETE FROM <表名> WHERE ...;

 

5 实用SQL语句

插入一条已存在的新数据，原本需要先删除后插入，现在可以直接用replace

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

更新记录

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略。

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

对一个表进行快照，即复制一份当前表的数据到一个新表

CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

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

可以使用FORCE INDEX强制查询使用指定的索引

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

 

6 事务

6.1 简介

例如：在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：账户a减去100，账户b必须增加100

数据库事务：把多条语句作为一个整体进行操作的功能。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

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

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

隐形事务：对于单条SQL语句，数据库系统自动将其作为一个事务执行。

显性事务：手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务。

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

回滚事务：我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败。

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

隔离：对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

隔离级别：Read Uncommitted、Read Committed、Repeatable Read、Serializable。

• Read Uncommitted：遇到脏读的问题。一个事务可能读取到另一个事务更新但未提交的数据，这个数据有可能是脏数据。
• Read Committed：遇到不可重复读的问题。事务不可重复读同一条记录，因为很可能读到的结果不一致。
• Repeatable Read：遇到幻读的问题。幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
• Serializable：在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。