SQL 学习笔记

hcz
188 阅读9分钟

基本知识

三种数据库模型：层次模型(树)，网状模型(网)，关系模型(二维表格) 在关系型数据库中，关系是通过 主键 和 外键 来维护的 数据类型：

image.png

SQL 语言关键字不区分大小写，但是建议 SQL 关键字大写，其他字段（表名和列名等）使用小写

主键：

  1. 通过主键能唯一区分出不同的记录
  2. 记录插入到表中后这个字段最后就不要修改了
  3. 不使用任何业务相关的字段作为主键

联合主键：

  1. 通过多个字段唯一标识记录，即两个或更多的字段都设置为主键
  2. 可以有重复，只要不是所有主键列都重复即可

外键：

  1. 通过某个字段，可以把当前表中的数据与另外一张表关联起来，这种列叫做外键
  2. 外键并不是通过列名实现的，而是通过定义外键约束实现的：
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
  1. 大部分应用为了速度不会设置外键，而是通过程序自身来保证逻辑的正确性
  2. 删除外键：
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

索引：

  1. 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。使用索引可以大大加快查询的速度
  2. 创建索引：
单列：
ALTER TABLE students
ADD INDEX idx_score (score);
多列：
ALTER TABLE students
ADD INDEX idx_name_score (name, score);
  1. 唯一索引，比如不能出现两条记录存储同一个名字：
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
只添加唯一约束不创建唯一索引：
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

涉及到的命令：

Tips: 结尾记得加分号

登陆： mysql -u root -p
退出： exit
Windows 执行 SQL 脚本：
source D:\work\learn\init-test-data.sql // 已登录
mysql -u root - p < D:\work\learn\init-test-data.sql // 未登录

查询数据

查询：SELECT * FROM <表名>

eg: SELECT * FROM students;
或计算： SELECT 100 + 200;

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

eg：
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 class_id <> 2;
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

多个条件需要用小括号 () 表示如何进行条件计算

常用条件表达式：

image.png

投影查询：让结果集只包含指定列，并且可以将列名进行重命名

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

排序：

  1. 查询结果通常是根据主键进行排序，我们也可以通过其他条件进行排序
  2. ASC 表示’升序’，默认就为 ASC, 所以不需要指定，DESC 表示’倒序’
  3. 有 WHERE 的时候, ORDER BY 需要放到 WHERE 后面
eg：
SELECT id, name, gender, score FROM students ORDER BY score; // score 从低到高
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 排序
SELECT id, name, gender, score FROM students WHERE class_id = 1 ORDER BY score DESC;

分页：

  1. 从结果中“截取”出第 M-N 条记录，可以通过 LIMIT <N-M> OFFSET <M> 实现
  2. LIMIT 3 表示最多 3 条记录，最后一页的话可能不没有 3 条记录
  3. LIMIT 总是设定为 pageSize，OFFSET 计算公式为 pageSize * (pageIndex - 1)
  4. OFFSET 超过查询的最大数量不会报错，只会得到一个空的结果集
  5. OFFSET 是可选的，如果只写 LIMIT 15，那么相当于LIMIT 15 OFFSET 0
  6. 在MySQL中，LIMIT 15 OFFSET 30 还可以简写成 LIMIT 30, 15
  7. 使用 LIMIT <M> OFFSET <N> 分页时，随着 N 越来越大，查询效率也会越来越低
eg: // 每页 3 条记录，获取第一页的记录
SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;

聚合查询：

查询 students 表一共有多少男生为例，我们可以使用SQL内置的 COUNT() 函数查询：SELECT COUNT(*) boys FROM students WHERE gender = 'M';

聚合函数：

image.png

分组：

  1. 通过某个条件来对聚合查询的结果进行分组
  2. 除了聚合函数的结果外，只能展示分组条件相关的列
eg: // 按照 class_id 分组
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

多表查询：

  1. 同时从多张表查询数据：SELECT * FROM <表1> <表2>
  2. 查询结果是两个表的乘积，结果集的列数是 表1 和 表2 的列数之和，行数是 表1 和 表2 的行数之积
  3. 可以用别名来给两个表中都有的字段进行命名
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;
  1. 多表查询的结果集可能非常巨大，要小心使用

连接查询：

  1. 连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
eg: // 选出所有学生，同时返回班级名称
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;

image.png

image.png

修改数据(INSERT/UPDATE/DELETE)

INSERT:

  1. 基本语法为：**INSERT** **INTO** <表名> (字段1, 字段2, ...) **VALUES** (值1, 值2, ...);
eg: INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

UPDATE:

  1. 基本语法为：**UPDATE** <表名> **SET** 字段1=值1, 字段2=值2, ... **WHERE** ...;
  2. UPDATE 语句可以没有 WHERE 条件，这时候整个表的所有记录都会被更新，所以执行 UPDATE 的时候需要非常小心
eg: UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

DELETE:

  1. 基本语法为：**DELETE** **FROM** <表名> **WHERE** ...;
  2. 和 UPDATE 一样，不加 WHERE 条件的话整个表的数据都会被删除

MySQL

连接远程 Server IP MySQL：
mysql -h 10.0.1.99 -u root -p

数据库:

列出所有数据库：SHOW DATABASES;
创建数据库：CREATE DATABASE test;
删除数据库：DROP DATABASE test;
切换至当前数据库：USE test;

表:

列出所有表：SHOW TABLES;
查看某个表的结构：DESC test;
查看创建表的 SQL 语句: SHOW CREATE TABLE test;

创建表：CREATE TABLE test;
eg: CREATE TABLE `students` (                             
	`id` bigint(20) NOT NULL AUTO_INCREMENT,            
	`class_id` bigint(20) NOT NULL,                     
	`name` varchar(100) NOT NULL,                       
	`gender` varchar(1) NOT NULL,                       
	`score` int(11) NOT NULL,                           
	PRIMARY KEY (`id`)                                  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 
删除表：DROP TABLE test;
修改表：
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;

退出 MySQL：EXIT

插入或替换：

使用 REPLACE 插入一条新纪录，这样的话如果原来的记录已经存在了就会先删除原纪录

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

插入或更新：

语句为 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 IGNORE INTO …

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

快照：

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

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

写入查询结果集：

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

eg: 
创建一个统计成绩的表 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;

强制使用指定索引：

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引（前提是指定的索引必须存在）

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

事务

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

image.png

隐式事务：单条 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 是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果 COMMIT 语句执行失败了，整个事务也会失败

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

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

隔离级别：

image.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 隔离级别

默认隔离级别：

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

avatar
文章
阅读
粉丝