SQL基础教程-优快云博客

这几天自己找些资料学了SQL，个人觉得很简单，把我的笔记写在下面，供大家参考～

本文中使用的SQL脚本在此～

SQL基础教程

关系数据库概述

数据模型

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

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

数据类型：

选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

SQL

SQL是结构化查询语言的缩写，用来访问和操作数据库系统。

语法特点

SQL语言关键字不区分大小写
但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

关系模型

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。
字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串’’。（通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。）
关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。
在关系数据库中，关系是通过主键和外键来维护的

主键

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。例如，假设我们把name字段作为主键，那么通过名字小明或小红就能唯一确定一条记录。但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。
对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。
所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。
主键最好是完全业务无关的字段，我们一般把这个字段命名为id。对于大部分应用来说，通常自增类型（即BIGINT NOT NULL AUTO_INCREMENT）的主键就能满足需求。
主键也不应该允许NULL。

联合主键

关系数据库还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。
联合主键允许一列有重复，只要不是所有主键列都重复即可。
没有必要的情况下，尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键

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

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的记录。
要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。

删除列是通过DROP COLUMN …实现的。

多对多

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。
一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

索引

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

ALTER TABLE students
ADD INDEX idx_score (score);

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

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。
但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

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

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

关系模型

基本查询

使用SELECT * FROM表名 时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询。
该SQL将查询出表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据。
不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

条件查询

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

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：
条件1：根据score列的数据判断：score >= 80；
条件2：根据gender列的数据判断：gender = ‘M’，注意gender列存储的是字符串，需要用单引号括起来。
就可以写出WHERE条件：score >= 80 AND gender = ‘M’
第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2。
第三种条件是NOT <条件>，表示“不符合该条件”的记录。例如，写一个“不是2班的学生”这个条件，可以先写出“是2班的学生”：class_id = 2，再加上NOT：NOT class_id = 2 （等价于class_id <> 2）
要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。
如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。
[image:FCB234B3-61BE-4B39-BBFB-8F98E48FF595-327-000049816A700557/69CCFF83-DBD2-4713-A091-F53B9CCD88AA.png]

投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM …，让结果集仅包含指定列。
使用SELECT 列1, 列2, 列3 FROM …时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同：SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …

排序

使用SELECT查询时，查询结果集通常是按照主键排序的。如果我们要根据其他条件排序，可以加上ORDER BY子句。例如按照成绩从低到高进行排序：
SELECT id, name, gender, score FROM students ORDER BY score;
如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：
SELECT id, name, gender, score FROM students ORDER BY score DESC;
如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序
默认的排序规则是ASC ：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC 和ORDER BY score效果一样。
如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。

分页

要实现分页功能，实际上就是从结果集中显示第1_{100条记录作为第1页，显示第101}200条记录作为第2页，以此类推。因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过*LIMIT OFFSET *子句实现。
LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条（即每页三条记录，查询第一页）。注意SQL记录集的索引从0开始。
查询第二页：LIMIT 3 OFFSET 3
查询第三页：LIMIT 3 OFFSET 6
分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

LIMIT总是设定为pageSize
OFFSET计算公式为pageSize * (pageIndex - 1)

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
以查询students表一共有多少条记录为例，我们可以使用SQL内置的*COUNT()*函数查询：
SELECT COUNT(*) FROM students;
COUNT( * )表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT( * )。
通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
SELECT COUNT(*) num FROM students;
聚合查询同样可以使用WHERE条件
[image:14EC9513-806E-4E57-8826-BC37BE4D3A13-327-00004C58625C4F9A/CCD94FAC-AA92-46EE-9D10-3F7E09469CE1.png]
（MAX()和MIN()函数并不限于数值类型，如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符）
要统计男生的平均成绩，我们用下面的聚合查询：
SELECT AVG(score) average FROM students WHERE gender = 'M';
如果聚合查询的WHERE 条件没有匹配到任何行，COUNT() 会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

分组聚合

GROUP BY子句指定了按某一列分组，因此，执行该SELECT语句时，会把这一列中相同的列先分组，再分别计算，例如：
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;。GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算
聚合查询的列中，只能放入分组的列
聚合查询也可以添加WHERE条件

多表查询

查询多张表：SELECT * FROM <表1> <表2>
查询的结果也是一个二维表，它是两个表的“乘积”，即两个表的每一行都两两拼在一起返回。结果集的列数是两个表的列数之和，行数是两个表的行数之积。
可以利用投影查询的“设置列的别名”来给两个表各自的列起别名

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列非常麻烦，所以SQL还允许给表设置一个别名：

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

给表设置别名：FROM <表名1> <别名1>, <表名2> <别名2>

多表查询也是可以添加WHERE条件的

连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

SELECT … FROM tableA 
??? JOIN tableB 
ON tableA.column1 = tableB.column2;

内连接——INNER JOIN：

先确定主表，仍然使用FROM <表1>的语法；
再确定需要连接的表，使用INNER JOIN <表2>的语法；
然后确定连接条件，使用ON <条件…>，例如s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
可选：加上WHERE子句、ORDER BY等子句。

INNER JOIN只返回同时存在于两张表的行数据
外连接——OUTER JOIN：RIGHT OUTER JOIN返回右表都存在的行，LEFT OUTER JOIN则返回左表都存在的行，FULL OUTER JOIN会把两张表的所有记录全部选择出来，并且自动把对方不存在的列填充为NULL。

修改数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。

INSERT：插入新记录；
UPDATE：更新已有记录；
DELETE：删除已有记录。

INSERT

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

字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。
可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(…)包含的一组值，例如：

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

UPDATE

UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;
例如，我们想更新students表id=1的记录的name和score这两个字段，先写出UPDATE students SET name=‘大牛’, score=66，然后在WHERE子句中写出需要更新的行的筛选条件id=1。

在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：
UPDATE students SET score=score+10 WHERE score<80;
如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。
UPDATE语句可以没有WHERE条件，这时整个表的所有记录都会被更新。

DELETE

DELETE ... FROM <表名> WHERE ...;

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。
不带WHERE条件的DELETE语句会删除整个表的数据.

MySQL

管理MySQL

数据库

列出所有数据库：SHOW DATABASES;
创建一个新数据库：CREATE DATABASE 数据库名;
删除一个数据库：DROP DATABASE 数据库名;
（注意：删除一个数据库将导致该数据库的所有表全部被删除。）
对一个数据库进行操作时，要首先将其切换为当前数据库：USE 数据库名;

表

列出当前数据库的所有表：SHOW TABLES;
查看一个表的结构：DESC 表名;
查看创建表的SQL语句：SHOW CREATE TABLE 表名;
创建表：CREATE TABLE;
删除表：DROP TABLE;
修改表：

给students表新增一列birth：ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
删除列：ALTER TABLE students DROP COLUMN birthday;

实用SQL语句

插入或替换

REPLACE语句：
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明’, ‘F’, 99);
若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

插入或更新

如果我们希望插入一条新记录，但如果记录已经存在，可以使用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;
若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

插入或忽略

如果我们希望插入一条新记录，但如果记录已经存在，可以使用INSERT IGNORE INTO …语句直接忽略：
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明’, ‘F’, 99);
若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

快照

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

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

(新创建的表结构和SELECT使用的表结构完全一致)

写入查询结果集

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。
例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

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;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果

事务

把多条语句作为一个整体进行操作的功能，被称为数据库事务。
对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务：

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

如果COMMIT语句执行失败了，整个事务也会失败。
如果希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

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

隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：
[image:3C84618D-1E79-417E-BB02-D4E7671E60C7-327-000052FEF0C987AF/4CB259CC-1167-46D5-9B8F-12B9DF7FD0B2.png]

Read Uncommitted

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

Read Committed

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

Repeatable Read

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

Serializable

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

好啦～SQL的基础部分就这些内容啦～语法虽然很简单，但是也要勤加练习才能融会贯通