SQL、MySQL学习笔记——基础语法与指令

本学习笔记基于 廖雪峰老师的SQL教程以及菜鸟教程的SQL/MYSQL教程
链接：
廖雪峰：https://www.liaoxuefeng.com/wiki/1177760294764384
菜鸟教程：https://www.runoob.com/mysql/mysql-tutorial.html

命令行下的常用指令

根目录常用指令

目录盘符的切换： cd /d F:

mysql的启动与关闭： net start mysql / net stop mysql

登陆MySQL：mysql -h 主机名 -u 用户名 -p

• -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
• -u : 登录的用户名;
• -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

如果我们要登录本机的 MySQL 数据库，只需要输入以下命令即可：

mysql -u root -p 随后键入密码即可登陆

该语句的拓展：
运行脚本命令
mysql -u root -p < C:\Users\miaowen\Desktop\init-test-data.sql
完整指令应该为： mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】

• -p后可直接接密码，但注意不要有空格，
• -D接上数据库名称，如果在sql脚本文件中使用了use 数据库，则-D选项可以忽略

SQL语法

切换到某数据库： use 数据库名;

展示数据库：show databases;

---

查询数据

查询数据库表的数据: SELECT * FROM 表名; :SELECT是关键字,表示将要执行一个查询;*表示“所有列/行”;FROM表示将要从哪个表查询;

不带 FROM 的SELECT：

• 可以用于计算： SELECT 100+200;
• 也可以用于判断当前数据库的连接是否有效： SELECT 1;

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

因此，条件查询的语法就是：
SELECT * FROM <表名> WHERE <条件表达式>
条件表达式的几种方式：

1. <条件1> AND <条件2> 表达满足条件1并且满足条件2
2. <条件1> OR <条件2> 表示满足条件1或者满足条件2
3. NOT <条件> 表示“不符合该条件”的记录 如：NOT class_id = 2
4. xxx <> x 判断 不相等，与 NOT 类似 如： class_id <> 2
5. xxx LIKE 'x%' LIKE判断相似，%表示任意字符，例如’ab%‘将匹配’ab’，‘abc’，‘abcd’ 如：name LIKE 'ab%'

投影查询
若只要返回某些列的数据: SELECT 列1,列2 FROM ...

还可以对 列名 进行重命名： SELECT 列1 别名1，列2，列3 别名三 FROM ...

排序
ORDER BY
对列名a增序排列： SELECT 列1,列2 FROM 表名 ORDER BY 列名a
对列名a倒序排列： SELECT 列1,列2 FROM 表名 ORDER BY 列名a DESC

对列a增序排列，如a有相同数据，则再按列b增序排列： SELECT 列1,列2 FROM 表名 ORDER BY 列名a,列名b

分页
LIMIT M OFFSET N ：从表的 第N个索引开始，共显示 M 条数据
这条语句也可以简写为： LIMIT N,M

统计一张表的数据量
查询 表 共有多少条记录： 使用聚合查询 COUNT()
SELECT COUNT(*) FROM students;

使用聚合查询 id列的总数并将结果的表名改为 num： SELECT COUNT(id) num FROM students;

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

注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

如：统计男生的平均成绩 SELECT AVG(score) average FROM students WHERE gender = 'M';

分组聚合
GROUP BY语法：
依次将每个班级的人数打印到表格中，并添加班级id一列：
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

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

内连接——INNER JOIN
SELECT 列名 列名 FROM 主表名 INNER JOIN 其他表名 ON 主表的某一列名 = 其他表的某一列（条件）

INNER JOIN查询的写法是：

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

外连接——RIGHT OUTER JOIN、LEFT OUTER JOIN、FULL OUTER JOIN

• INNER JOIN 只返回同时存在于两张表的行数据。
• RIGHT OUTER JOIN 返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充一行中剩下的字段。
• LEFT OUTER JOIN 则返回左表都存在的行。如果某一行仅在左表存在，那么结果集就会以NULL填充一行中剩下的字段。
• FULL OUTER JOIN 它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

---

修改数据

增、删、改 对应的SQL语句分别是：

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

INSERT 增
语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
如：INSERT INTO students (class_id, name, gender, score) VALUES (1, '大宝', 'M', 87), (2, '二宝', 'M', 81);

UPDATE 改
语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE 更新的行的筛选条件;
如：UPDATE students SET score=score+10 WHERE score<80;

DELETE 删
语句的基本语法是：

DELETE FROM <表名> WHERE 删除的行的筛选条件;

如：DELETE FROM students WHERE score >= 100;

要特别小心的是，不带WHERE条件的UPDATE/DELETE语句会 更新/删除 整个表的数据：
DELETE FROM students;
这时，整个表的所有记录都会被 更新/删除。所以，在执行UPDATE/DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE/DELETE。

REPLACE 插入-替换

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用 REPLACE 语句，这样就不必先查询，再决定是否先删除再插入。
语句的基本语法是：

REPLACE INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

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

DUPLICATE插入-更新
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO … ON DUPLICATE KEY UPDATE …语句。
语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...) 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指定。

IGNORE插入-忽略
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就直接忽略，此时，可以使用INSERT IGNORE INTO …语句。
语句的基本语法是：
INSERT IGNORE INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...) ;

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

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

---

增删改查

要列出所有数据库，使用命令： SHOW DATABASES;
要创建一个新数据库，使用命令：CREATE DATABASE 数据库名;
要删除一个数据库，使用命令：DROP DATABASE 数据库名;

切换 数据库：USE 数据库名;

列出当前数据库的所有表：SHOW TABLES;
查看一个 表 的结构： DESC 表名;

创建 表：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;

退出 MySQL：EXIT

快照
如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合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语句的结果集直接插入到指定表中。
INSERT INTO 表名 (列名1, 列名2) SELECT 原列名1, 原列名2 FROM 原表

事务

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

-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这两条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;

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

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

隔离级别
对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

脏读
一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

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

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

Serializable
是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。