SQL语句学习

创建2021-09-22更新2021-09-22阅读时间30m

学习链接：
可在线输入sql语句
廖雪峰SQL教程
菜鸟教程
lookroot博客

基本了解

为什么需要数据库？

  • 随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题：
  • 应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据

数据库一共有三种模型

  • 层次模型 - 树
  • 网状模型 - 网状
  • 关系模型 - 二维表格 - 学这个。。

主流的关系数据

  • 商用数据库，例如：Oracle，SQL Server，DB2等；
  • 开源数据库，例如：MySQL，PostgreSQL等；
  • 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
  • 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

SQL

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

SQL语法特点

  • SQL语言关键字不区分大小写
  • 针对不同的数据库，对于表名和列名，有的数据库区分大小写
  • 规范 - SQL关键字总是大写，以示突出，表名和列名均使用小写。

关系模型

关系数据库
列：同一个表的每一行记录都拥有相同的若干字段。
行：记录是一个逻辑意义上的数据。
字段：定义了数据类型

通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

关系数据库的表和表之间的关系

  • 一对多 - 一个班对应多个学生
  • 多对一 - 学生对应班级
  • 一对一 - 老师对应班级

主键

  • 能够通过某个字段唯一区分出不同的记录，这个字段被称为主键
  • 记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。
  • 选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
  • 因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。
  • 主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有
    • 自增整数类型
    • 全局唯一GUID类型

GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。
对于大部分应用来说，通常自增类型的主键就能满足需求。
如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

主键是关系表中记录的唯一标识。
主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。
主键也不应该允许NULL。

外键

外键并不是通过列名实现的，而是通过定义外键约束实现的： 也可不设置约束，靠应用程序的逻辑来保证

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

总结：
主键唯一
外键关联

索引

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

索引的优点是提高了查询效率
缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢
使用主键索引的效率是最高的，因为主键会保证绝对唯一

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

总结：

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

查询数据

基本查询

查询students表的所有数据
SELECT * FROM students;

不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

条件查询

SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
SELECT * FROM students WHERE NOT class_id = 2;
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。
例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是SELECT * FROM students WHERE score >= 80。

投影查询

SELECT id, score, name FROM students;
SELECT id, score points, name FROM students;
SELECT id, score points, name FROM students WHERE gender = 'M';

让结果集仅包含指定列。这种操作称为投影查询。
还可以给每一列起个别名

排序

SELECT id, name, gender, score FROM students ORDER BY score; 默认升序
SELECT id, name, gender, score FROM students ORDER BY score DESC;
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender; 先按成绩降序，再按性别升序

1
2
3
4
5
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

分页

SELECT id, name, gender, score FROM students ORDER BY score DESC;

1
2
3
4
5
6
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0; - 这里offset是从0-n排的
LIMIT 3 OFFSET 6;
LIMIT 3 OFFSET 20; - 超过了 - 为空

LIMIT - 每页分多少， OFFSET - 第几个。

总页数 = 总记录数 / 每页记录数 - 向上取正。

聚合查询

使用函数，对 列进行统计
如COUNT（），AVG（）， SUM（），MAX（）， MIN（）

SUM,AVG该列必须为数值类型
MAX,MIN不限于数值类型，如果是字符类型，返回排序最后或最前的字符

SELECT COUNT(*) num FROM students; - 表示查询所有列的行数
SELECT COUNT(*) boys FROM students WHERE gender = 'M';

COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：
注意别名不能和函数名一样！！

如果没有匹配到
COUNT会返回0， 而SUM()、AVG()、MAX()和MIN()会返回NULL：

多表查询

SELECT * FROM students, classes; - 乘积

结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。
这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，
对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

还会用同样的名字 - 需要利用投影查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes; // 可以设置别名

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

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1; // 两表统一查询

连接查询

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

SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

1
2
3
4
5
6
7
8
9
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;

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

这里左右要分清谁是主表，主表在左侧。

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

总结：

JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；
INNER JOIN是最常用的一种JOIN查询
JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

修改数据

CRUD - Create, Retrieve, Update, Delete

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

INSERT

需要向数据库表中插入一条新记录时，就必须使用INSERT语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 查询并观察结果:
SELECT * FROM students;

字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致

还可以一次性添加多条记录

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

SELECT * FROM students;

UPDATE

要更新数据库表中的记录，我们就必须使用UPDATE语句

如果存在就修改，没有就返回空，可修改一填或多条（符合条件）
如果不写WHERE， 全部修改，谨慎！！

1
2
3
4
5
6
7
8
9
10
11
12
13
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

UPDATE students SET name='大牛', score=66 WHERE id=1; // 根据id修改，搜索的时候查id返回数据
-- 查询并观察结果:
SELECT * FROM students WHERE id=1;

UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7; // 可以修改多个值
-- 查询并观察结果:
SELECT * FROM students;

UPDATE students SET score=score+10 WHERE score<80; // 使用表达式
-- 查询并观察结果:
SELECT * FROM students;

DELETE

要删除数据库表中的记录，我们可以使用DELETE语句。
如果没有写删除条件，全部删除 - 同UPDATE

，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。

1
2
3
4
5
6
7
8
9
10
11
12
DELETE FROM <表名> WHERE ...;

DELETE FROM students WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students;

DELETE FROM students WHERE id=999; // 如果没有匹配到，没有任何数据被删除
-- 查询并观察结果:
SELECT * FROM students;

DELETE FROM students; // 删除全部。。

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> SHOW DATABASES; 列出所有数据库 

mysql> CREATE DATABASE test; 创建一个新数据库

mysql> DROP DATABASE test; 删除一个数据库

mysql> USE test; 对一个数据库进行操作时，要首先将其切换为当前数据库

---------------- 表

mysql> SHOW TABLES; 列出当前数据库的所有表

mysql> DESC students; 查看一个表的结构 - 字段，类型，默认值等。。

mysql> SHOW CREATE TABLE students; 查看创建表的SQL语句 - 表里定义了那些字段，字段类型，默认值等。

mysql> DROP TABLE students; 创建表使用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; 修改列名 birth - birthday 和类型

ALTER TABLE students DROP COLUMN birthday; 删除列

mysql> EXIT 退出MySQL
注意EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。

实用SQL语句

实际代码

组合 或新命令 - 需要结合实际案例。。

写一下功能：

  • 插入或替换 - 使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：
  • 插入或更新 - 希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录 DUPLICATE
  • 插入或忽略 - 希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略 IGNORE
  • 快照 - 对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：
  • 写入查询结果 - 果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中
  • 强制使用指定索引 - 如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引

事务

对于业务的要求。

把多条语句作为一个整体进行操作的功能，被称为数据库事务 - 整体概念来了。
对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

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

某些业务，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：
如果第一条语句成功，第二条语句失败，就必须全部撤销。

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

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

四个特性总结：
Atomic，原子性，想象一个原子，整体
Consistent，一致性，想象一个原子中的物质改变了，大家都知道了
Isolation，隔离性，好多个原子
Duration，持久性，原子存在，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务

COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存 - 思考VUE里面的commit。。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败

隔离级别：

  • 脏读： 一个事务读取到了另外一个事务中没有提交的数据，这个数据还没有提交，所以可能是错误的，所以称为 脏数据
  • 不可重复读：同一个事务中有多次读取数据的请求，在读取间隔中数据被其他事务修改了，所以读取到的不一样
  • 幻读：也是同一个事务多次读取，读取间隔中其他事务删除或者新增数据，后来读取到的就和一开始读取到的条数不同，就像幻觉

隔离级别总结：

  • 脏读 - 读到别的事务未提交的数据，有可能错的。- 比较内容
  • 不可重复读 - 同一个事务多次读取，读取间被修改了，读的数据不一样 - 比较内容
  • 幻读 - 同一个事务多次读取，期间被其他事务删除或新增了，和一开始读的数目不同 - 比较个数

事务的隔离级别

  • READ-UNCOMMITTED(读未提交)：最低的隔离级别，允许读取尚未提交的数据变更，*可能发生脏读、幻读或不可重复读**。
  • READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，可能发生幻读或不可重复读。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，*可能发生幻读。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，能阻止以上问题
  • 总结 - 读未提交 - 读以提交 - 可重复读 - 可串行化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，
如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

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

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

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

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

总结

收获经验：
熟悉了SQL语句，具体还要结合案例，用语义化的方式理解SQL语句，通关什么方式，在什么表中，找到了什么内容，
学习多表查询的时候，多思考两个表交叉的样子。常用的还是公共部分
最后一个事务，还需要结合案例看看。