SQL入门

这几天暂停了对Java的学习，转过头看了一下廖雪峰的SQL教程，简单入个门。
链接：https://www.liaoxuefeng.com/wiki/1177760294764384

SQL的基本概念

~ 数据库是一种专门管理数据的软件，应用程序通过接口来读写数据。

~数据库一共有三种模型：树构成的层次关系、图构成的网状模型、和二维表构成的关系模型。其中关系数据库获得了最为广泛的应用。

~主流的数据类型： image.png

~SQL （Structure Query Language）结构化查询语言。所有的数据库都支持SQL，并在标准的SQL上进行扩展形成扩展SQL。总的来说，SQL语句定义了 DDL、DML、DQL等操作数据库的能力
SQL不区分大小写，我们假定SQL关键字总是大写，表名和列名均小写

P.S 平时在练习时我会使用MySQL，而在工作中我将会使用到Oracle

~关系模型本质上是若干个存储数据的二维表（类似excel）；表的每一行称为记录record，每一列称为字段column
字段定义了数据类型以及是否为NULL（通常情况下，应该避免设置NULL，加快查询条件）
关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系。

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

能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。
对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。
选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
一般把该字段命名为 id 常见作为id的类型有BIGINT自增整数类型 和 全局唯一GUID类型。
联合主键：关系数据库还允许多个字段唯一标识记录（用的少）

关系数据库通过某个字段可以把数据与另一张表关联起来，这种列称为外键。
外键并不是通过列名实现的，而是通过定义约束实现的（为了追求速度，往往不设置外键约束，而是通过程序自身来保证逻辑的正确性）
定义与删除一个外键约束实例

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

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

~索引是对数据库中某一列或多列的值进行预排序的数据结构，可以加块查询速度。

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。索引越多，插入、更新和删除记录的速度就越慢。对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
通过创建唯一索引，可以保证某一列的值具有唯一性。
数据库索引对于用户和应用程序来说都是透明的。

强制使用指定索引

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

测试是否链接到数据库

SELECT 1;

SQL基本操作：增删改查CRUD

查询数据

~基本查询语句

SELECT * FROM  <表名>

• *表示 “所有列”，将查询出表中的所有数据。
• 如果只希望返回某些列的数据，可以把 * 改成想要的字段名，还可以给每一列取个别名。

SELECT 列1 别名1, 列2 别名2 FROM ...

~条件查询语句 WHERE

SELECT * FROM <表名> WHERE <条件表达式>

表达式语句多种多样，常见的有 AND OR NOT
要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。没有（）是，要注意运算的优先级。

常用表达式

~排序语句
使用SELECT是，默认是按照 id 进行排序的；如果要根据指定列进行排序，可以使用 ORDER BY 子句。
升序序列加上 ASE 标志（默认），降序序列加上 DESC 标志
如果要进一步排序，可以继续添加列名

~分页查询语句
可以控制每页显示多少条记录，

LIMIT <M> OFFSET <N>

其中，M表示每页最多显示的数量pageSize，N表示当前页的索引pageIndex 。
OFFSET是可选的，默认为0。
注意SQL记录集的索引从0开始。
使用分页时，随着N越来越大，查询效率也会越来越低。

~聚合查询语句
SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
COUNT()可以用来统计总数，查询的结果仍然是一个二维表。

除了COUNT()外的其他聚合函数
要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

分组聚合 （可以使用多个列进行分组） GROUP BY

~多表查询
SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。又称笛卡尔查询.

SELECT * FROM <表1> <表2>

它是两个表的“乘积”即<表1>的每一行与<表2>的每一行都两两拼在一起返回。结果集的列数是<表1>和<表2>的列数之和，行数是<表1>和<表2>的行数之积。

简洁代码
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

多表查询的结果集可能非常巨大，要小心使用。

~连接查询 JOIN
简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

操作顺序

SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>

插入语句 INSERT

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

如果一个字段有默认值，那么在INSERT语句中也可以不出现。
还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值。

• 如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

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

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

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 ...语句：

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

• 如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

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

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;

更新语句 UPDATE

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

在UPDATE语句中，更新字段时可以使用表达式。
要特别小心的是，UPDATE语句可以没有WHERE条件,这时，整个表的所有记录都会被更新。
在使用MySQL这类真正的关系数据库时，UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。

删除语句DELETE

DELETE FROM <表名> WHERE ...;

要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据。
在使用MySQL这类真正的关系数据库时，DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。

MySQL客户端与MySQL图形化界面

客户端和MySQL交互，唯一的接口就是SQL。因此，MySQL提供了大量的SQL语句用于管理。很多时候，通过SSH远程连接时，只能使用SQL命令，所以，了解并掌握常用的SQL管理操作是必须的。

对于数据库

• 列出所有数据库，使用命令 :
  SHOW DATABASES
• 创建一个新数据库，使用命令：
  CREATE DATABASE <数据库名>
• 删除一个数据库，使用命令：
  DROP DATABASE <数据库名>;
• 对一个数据库进行操作时，要首先将其切换为当前数据库：
  USE <数据库名>;

对于表

• 列出当前数据库所有表，使用命令：
  SHOW TABLES;

• 查看一个表的结构，使用命令：DESC students;

• 使用以下命令查看创建表的SQL语句：
  SHOW CREATE TABLE <表名>;

• 创建、删除表，使用命令：
  CREATE TABLE <表名>
  DROP TABLE <表名>;

• 修改表：

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

ALTER TABLE students DROP COLUMN birthday;

事物

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

• 对于单条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;

隔离级别

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

不同的隔离级别