SQL学习记录

 

https://www.liaoxuefeng.com/wiki/001508284671805d39d23243d884b8b99f440bfae87b0f4000

总的来说，SQL语言定义了这么几种操作数据库的能力：

DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作

SELECT * FROM students;

使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询

SELECT 100+200;
300//上述查询会直接计算出表达式的结果。虽然SELECT可以用作计算，但它并不是SQL的强项

-- 按条件查询students:
SELECT * FROM students WHERE score >= 80;
SELECT * FROM <表名> WHERE <条件表达式>
条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2
第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2
第三种条件是NOT <条件>，表示“不符合该条件”的记录。

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

例如，从students表中返回id、score和name这三列：
-- 使用投影查询
SELECT id, score, name FROM students;

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。

小结
使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。

SELECT语句可以对结果集的列进行重命名。

排序
-- 按score从低到高
SELECT id, name, gender, score FROM students ORDER BY score;
-- 按score从高到低
SELECT id, name, gender, score FROM students ORDER BY score DESC;

小结
使用ORDER BY可以对结果集进行排序；
可以对多列进行升序、倒序排序。

注意
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

小结
使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

聚合查询
对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

查询students表一共有多少条记录
SELECT COUNT(*) FROM students;

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
SELECT COUNT(*) num FROM students;

COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：
SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外，SQL还提供了如下聚合函数：

函数    说明
SUM    计算某一列的合计值，该列必须为数值类型
AVG    计算某一列的平均值，该列必须为数值类型
MAX    计算某一列的最大值
MIN    计算某一列的最小值

-- 使用聚合查询计算男生平均成绩:
SELECT AVG(score) average FROM students WHERE gender = 'M';

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

-- 按class_id分组:
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

我们想统计各班的男生和女生人数
-- 按class_id, gender分组:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

小结
使用多表查询可以获取M x N行记录；

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

INSERT

INSERT语句的基本语法是：
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

小结
使用INSERT，我们就可以一次向一个表中插入一条或多条记录。

UPDATE语句的基本语法是：
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

DELETE语句的基本语法是：
DELETE FROM <表名> WHERE ...;

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。

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

可见，数据库事务具有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;
很显然多条SQL语句要想作为一个事务执行，就必须使用显式事务。