SQL（二）

修改数据

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

INSERT

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
多条记录：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 name='大牛', score=66 WHERE id=1;
多：UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
其中，SET score=score+10就是给当前行的score字段的值加上了10。UPDATE students SET score=100 WHERE id=999;若去掉WHERE，则全部更新，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。
如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。

DELETE

DELETE FROM <表名> WHERE ...;
删除students表中id=1的记录：DELETE FROM students WHERE id=1;
注意到DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录：DELETE FROM students WHERE id>=5 AND id<=7;
如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。
和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据。

MySQL

安装完MySQL后，除了MySQL Server，即真正的MySQL服务器外，还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行。

打开命令提示符，输入命令mysql -u root -p，提示输入口令。填入MySQL的root口令，如果正确，就连上了MySQL Server，同时提示符变为mysql>。
输入exit断开与MySQL Server的连接并返回到命令提示符。
在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。默认端口号是3306，即如果发送到本机MySQL Server，地址就是127.0.0.1:3306。

也可以只安装MySQL Client，然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：

mysql -h 10.0.1.99 -u root -p
命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。
管理MySQL，可以使用可视化图形界面MySQL Workbench。
注意：很多时候，通过SSH远程连接时，只能使用SQL命令。
SHOW DATABASES;列出所有数据库
CREATE DATABASE test;创建一个新数据库
DROP DATABASE test;删除一个数据库
USE test;对一个数据库进行操作时，要首先将其切换为当前数据库

表

SHOW TABLES;列出当前数据库的所有表
DESC students;查看一个表的结构
SHOW CREATE TABLE students;查看创建表的SQL语句
DROP TABLE students;创建表使用CREATE TABLE语句，而删除表使用DROP TABLE语句
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;修改表就比较复杂。给students表新增一列birth
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;要修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)
ALTER TABLE students DROP COLUMN birthday;删除列

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

实用SQL语句

插入或替换

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

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

插入或更新

插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用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;
若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

插入或忽略

插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

快照
对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合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语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表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;
确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average |
+----+----------+--------------+
| 1 | 1 | 86.5 |
| 2 | 2 | 73.666666666 |
| 3 | 3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)
强制使用指定索引
在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
指定索引的前提是索引idx_class_id必须存在。

事务

在执行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语句作为一个事务执行，使用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;

隔离级别

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

Read Uncommitted

Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

Read Committed

在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。

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

Repeatable Read

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。

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

Serializable

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

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