SQL(Structured Query Language)
由于最近心血来潮，就把SQL重新过了一遍，顺便总结了一份小宝典，应该算是挺全面的哈~~
导语：SQL之路–SQL大全
作者：变优秀的小白
爱好：美式一定加冰！
话不多说，展翅(shi) ！
- 目录
- 数据模型
- 数据类型
- SQL基本能力
- 运行SQL
- 关系模型
- 主键
- 常见的可作为id字段的类型
- 联合主键
- 外键
- 几种关系
- 索引
- 概念
- 目的
- 使用效率
- 优缺点
- 例子ex
- 唯一索引
- 查询数据
- 基本查询
- 条件查询
- 投影查询
- 排序
- 分页查询
- 聚合查询
- 多表查询
- 连接查询
- INNER JOIN 内连接
- OUTER JOIN 外连接
- LEFT OUTER JOIN 左外连接
- RIGHT OUTER JOIN 右外连接
- FULL OUTER JOIN
- 修改数据
- INSERT 插入数据
- UPDATe 更新数据
- DELETE
- 主键
- mysql
- 管理mysql
- 数据库
- 表
- 退出mysql
- 管理mysql
- 实用SQL
- 插入或替换
- 插入或更新
- 插入或忽略
- 快速复制
- 强制索引
- 事务
- 隐式事务
- 显式事务
- 隔离级别
- Read Uncommitted(最低)
- Read Committed(较低)
- Repeatable Read(较高)
- Serializable(最高)
数据模型
数据库按照数据结构来组织、存储和管理数据
- 层次模型
- 网状模型
- 关系模型
数据类型
| 名称 | 类型 | 说明 |
|---|---|---|
| INT | 整型 | 4字节 |
| BIGINT | 长整型 | 8字节 |
| REAL | 浮点型 | 4字节 |
| DOUBLE | 浮点型 | 8字节 |
| DECIMAL | 高精度小数 | DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算 |
| CHAR | 定长字符串 | 存储指定长度的字符串，例如，CHAr(100)总是存储100个字符的字符串 |
| VARCHAR | 变长字符串 | 存储可变长度的字符串 |
| BOOLEAN | 布尔类型 | True或者False |
| DATE | 日期类型 | 日期 |
| TIME | 时间类型 | 时间 |
| DATETIME | 日期和时间类型 | 存储日期+时间 |
SQL基本能力
- DDL：Data Definition Language
DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行 - DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作 - DQL：Data Query Language
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作
运行SQL
若是windows版本，百度上有很多安装教程，
# 若是使用brew安装的(系统为macOS)
# 需要通过brew启动mysql
$ brew services start mysql
$ mysql -u root -p
关系模型
- 关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表
- 表的每一行称为记录（Record），记录是一个逻辑意义上的数据
- 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段
主键
- 最关键的一点：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响
常见的可作为id字段的类型
- 自增整数类型
- BIGINT NOT NULL AUTO_INCREMENT(满足大部分场景)
- INT：一张表的记录数超过2147483647（约21亿）时，会达到上限而出错
- BIGINT：922亿
- 全局唯一GUID类型
联合主键
没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升
- 定义：通过多个字段唯一标识记录，即两个或更多的字段都设置为主键
外键
- 外键是通过定义外键约束实现的
- 外键约束会降低数据库的性能，一般不设置
增加一个外键约束
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
- CONSTRAINT: 外键约束的名称可自定义
- FOREIGN KEY: class_id作为外键
- REFERENCES 关联classes表的id列
删除一个外键约束
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
几种关系
均为表之间的对应关系
- 一对一(q:问题，a:回答，ex: 例子)
- q: 既然是一对一，为啥不把另一个表(只有1、2列)的数据放入表的一个列中
- a: 如果业务运行是可以的，但是一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能
- ex: 把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度
- 一对多
- 多对多
- ex: 两个一对多
索引
概念
关系数据库中对某一列或多个列的值进行预排序的数据结构
目的
可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度
使用效率
- 取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高
- 若存在大量数据具有相同值，索引就没有意义
- 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一
优缺点
- 优点：提高查询效率
- 缺点：插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢
例子ex
| id | class_id | name | gender | score |
|---|---|---|---|---|
| 1 | 1 | 小明 | M | 90 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
场景：要经常根据score列进行查询
解决：对score列创建索引
ALTER TABLE students
ADD INDEX idx_score (score);
则创建了一个名称未idx_score的索引
场景：索引名称是任意的，索引如果有多列
解决：括号内增加列名称
ALTER TABLE students
ADD INDEX idx_name_score (name, score);
唯一索引
-
运用场景：
- 看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键
- 这些列根据业务要求，又具有唯一性约束（即不能出现两条记录存储了同一个身份证号）
-
解决：该列添加一个唯一索引
-
ex例子：
添加一个唯一索引
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
添加一个唯一约束而不创建唯一索引(具有唯一性保证)
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);
查询数据
基本查询
ex. *
SELECT *
FROM students;
条件查询
ex. where & and & or & ()
SELECt *
FROM students
WHERe (score < 80 OR score > 90)
AND gender = 'M';
投影查询
ex. 列1、列2…
SELECt id, score, name
FROM students;
排序
ex. ORDER BY & DESC & ASC
SELECt id, name, gender, score
FROM students
WHERe class_id = 1
ORDER BY score DESC;
分页查询
ex. LIMIT M OFFSET N
- LIMIT M OFFSET N 可简写为 LIMIT N, M
- LIMIT总是设定为pageSize
- OFFSET = pageSize * (pageIndex - 1)
-- 查询第一页
SELECt id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
-- 查询第二页，需要 "跳过" 前三条记录
SELECt id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
-- 查询第四页，类似
SELECt id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;
-- 若查询页没有数据，不报错，得到空结果集
SELECt id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;
聚合查询
注意点：如果聚合查询的WHERe条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL
- ex. COUNT()统计记录数量
-- 使用聚合查询
SELECt COUNT(*) FROM students;
-- 使用聚合查询并设置结果集的列名为num
SELECt COUNT(*) num FROM students;
-- 使用聚合查询并设置WHERe条件
SELECt COUNT(*) boys FROM students WHERe gender = 'M';
-- 分组聚合
SELECt class_id, COUNT(*) num FROM students GROUP BY class_id;
-- 聚合查询的列中，只能放入分组的列
SELECt class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
- ex. AVG()平均值，该列必须为数值类型
- SUM()合计值，类似
- MAX()最大值，类似
- MIN()最小值，类似
-- 使用聚合查询计算男生平均成绩
-- average为自定义名称
SELECt AVG(score) average FROM students WHERe gender = 'M';
-- 每页3条记录，如何通过聚合查询获得总页数
SELECt CEILING(COUNT(*)/3) FROM student;
多表查询
- 语法：SELECt * FROM <表1> <表2>
- 笛卡尔乘积，数据量巨大M*N
-- 两个表的乘积
SELECt * FROM students, classes;
-- 可设置列的别名区分
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;
-- 加上where条件判断
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;
连接查询
INNER JOIN 内连接
- INNER JOIN
- 确定主表，
FROM 表1 - 确定需要连接的表，
INNER JOIN 表2 - 确定连接条件，
ON 条件 - 可选：
WHEReOrORDER BY等
- 确定主表，
-- 内连接，运用别名，id对应取出
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;
OUTER JOIN 外连接
LEFT OUTER JOIN 左外连接
- 返回左表都存在的行，若仅左表存在会返回NULL
SELECt s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;
RIGHT OUTER JOIN 右外连接
- 返回右表都存在的行，若仅右表存在会返回NULL
SELECt s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;
FULL OUTER JOIN
- 所有记录全部选择出来，把对方不存在的列填充为NULL：
SELECt s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;
修改数据
INSERT 插入数据
- 语法：
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...); - 插入数据的字段顺序不需要一致，但字段和值要对应
- 自增主键和默认值可不填
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 ...; - 需十分注意使用！
-- 更新多条记录
UPDATE students SET name='小牛', score=77 WHERe id>=5 AND id<=7;
-- 使用表达式，将所有80下同学成绩加10分
UPDATE students SET score=score+10 WHERe score<80;
-- 查询并观察结果:
SELECT * FROM students;
DELETe
- 语法：DELETE FROM <表名> WHERe …;
- 与UPDATe语法类似，但需十分注意使用！
-- 删除id=1的记录
DELETE FROM students WHERe id=1;
-- 删除id=5,6,7的记录
DELETe FROM students WHERe id>=5 AND id<=7;
-- 查询并观察结果:
SELECt * FROM students;
mysql
管理mysql
- 几个系统库
- imformation_schema
- mysql
- performance_shema
- sys
数据库
-- 创建一个新的DB
mysql> CREATE DATABASE test
Query OK, 1 row affected (0.01 sec)
-- 删除一个DB
mysql> DROp DATABASE test
Query OK, 0 rows affected (0.01 sec)
-- 切换DB
mysql> USE test;
Database changed
表
-- 查看DB的所有表
mysql> SHOW TABLES;
-- 查看一个表的结构
mysql> DESC students;
-- 查看创建表的SQL语句
mysql> SHOW CREATE TABLE students;
-- 删除表
mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)
-- 修改表，如新增一列
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
-- 修改表，列名改为birthday，类型改为VARCHAr(20)
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
-- 修改表，删除列
ALTER TABLE students DROP COLUMN birthday;
退出mysql
mysql> exit
Bye
实用SQL
插入或替换
-- 若记录已经存在，删除原记录，再插入新记录
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 students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
快速复制
-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERe class_id=1;
强制索引
-- FORCE INDEX
SELECt * FROM students FORCE INDEX (idx_class_id) WHERe class_id = 1 ORDER BY id DESC;
事务
某些业务要求必须完成一系列的操作，而不是只执行一部分
可见，数据库事务有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(最低)
解释：是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）
Read Committed(较低)
解释：不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致
Repeatable Read(较高)
- mysql中InnoDB模式级别
解释：一个事务可能会遇到幻读（Phantom Read）的问题。
幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
Serializable(最高)
解释：在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
- 缺点：由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低
