数据库学习篇五

修改数据

关系数据库的基本操作就是增删改查，而对于增、删、改，对应的SQL语句分别是：
INSERT：插入新记录；
UPDATE：更新已有记录；
DELETE：删除已有记录。

插入数据

INSERT语句的基本语法是：

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

例如，向students表插入一条新记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值：

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

查询并观察结果:

SELECT * FROM students;

部分结果展示：

id	class_id	name	gender	score
9	   3	    小王	       M	89
10	   3	    小丽	       F	88
11	   2	    大牛	       M	80

注意到这里并没有列出id字段，也没有列出id字段对应的值，这是因为id字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。
要注意的是，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。 也就是说，可以写INSERT INTO students (score, gender, name, class_id) ...，但是对应的VALUES就得变成(80, 'M', '大牛', 2)。

还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(…)包含的一组值：

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

查询并观察结果：

SELECT * FROM students;

部分结果展示：

id	class_id	name	gender	score
10	    3	    小丽	       F	88
11	    2	    大牛	       M	80
12	    1	    大宝	       M	87
13	    2	    二宝	       M	81

更新数据

UPDATE语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

例如，想更新students表id=1的记录的name和score这两个字段，先写出UPDATE students SET name=‘大牛’, score=66，然后在WHERE子句中写出需要更新的行的筛选条件id=1：

UPDATE students SET name='大牛', score=66 WHERE id=1;

查询并观察结果:

SELECT * FROM students WHERE id=1;

id	class_id	name	gender	score
1	    1	    大牛	       M	66

注意到UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录，更新id=5,6,7的记录：

UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

查询并观察结果:

SELECT * FROM students;

id	class_id	name	gender	score
5	   2	   小牛	       F	77
6	   2	   小牛	       M	77
7	   2	   小牛	       M	77

在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：

UPDATE students SET score=score+10 WHERE score<80;

查询并观察结果:

SELECT * FROM students;

id	class_id	name	gender	score
5	   2	   小牛	       F	87
6	   2	   小牛	       M	87
7	   2	   小牛	       M	87

其中，SET score=score+10就是给当前行的score字段的值加上了10。
如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。例如，更新id=999的记录：

UPDATE students SET score=100 WHERE id=999;

最后可以关注的是，UPDATE语句可以没有WHERE条件，例如：
UPDATE students SET score=60;这表示整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

删除数据

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;

例如，想删除students表中id=1的记录：

DELETE FROM students WHERE id=1;

查询并观察结果:

SELECT * FROM students;

id	class_id	name	gender	score
2	    1	    小红	       F	95
3	    1	    小军	       M	88

注意到DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录：

DELETE FROM students WHERE id>=5 AND id<=7;

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。例如：

DELETE FROM students WHERE id=999;

最后，需要注意的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：

DELETE FROM students;

这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。

小结说明

可以对误删误改操作进行回滚，因为数据库有事务功能，因为默认是自动提交的，所以不能回滚，可以把auto_commit给关掉，然后 commit才生效。

select @@autocommit;
-- 禁止自动提交
SET AUTOCOMMIT=0;
-- 开启自动提交
SET AUTOCOMMIT=1;

Mysql使用

管理MySQL

安装完MySQL后，除了MySQL Server，即真正的MySQL服务器外，还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行。打开命令提示符，输入命令mysql -u root -p，提示输入口令。填入MySQL的root口令，如果正确，就连上了MySQL Server，同时提示符变为mysql>：

C:\> mysql -u root -p                                   
Enter password: ******                                                                                      
Server version: 5.7                                     
Copyright (c) 2000, 2018.                        
Type 'help;' or '\h' for help.                                                                              
mysql>                   

输入exit断开与MySQL Server的连接并返回到命令提示符。MySQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld。MySQL Client和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 Workbench。它可以用可视化的方式查询、创建和修改数据库表，但是，归根到底，MySQL Workbench是一个图形客户端，它对MySQL的操作仍然是发送SQL语句并执行。因此，本质上，MySQL Workbench和MySQL Client命令行都是客户端，和MySQL交互，唯一的接口就是SQL。
因此，MySQL提供了大量的SQL语句用于管理。虽然可以使用MySQL Workbench图形界面来直接管理MySQL，但是，很多时候，通过SSH远程连接时，只能使用SQL命令，所以，了解并掌握常用的SQL管理操作是必须的。在一个运行MySQL的服务器上，实际上可以创建多个数据库（Database）。要列出所有数据库，使用命令：

mysql> SHOW DATABASES;         
 information_schema 
 mysql              
 performance_schema 
 shici              
 sys                
 test               
 school  

其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们。其他的是用户创建的数据库。
要创建一个新数据库，使用命令：

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

要删除一个数据库，使用命令：

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

注意：删除一个数据库将导致该数据库的所有表全部被删除。
对一个数据库进行操作时，要首先将其切换为当前数据库：

mysql> USE test;
Database changed

列出当前数据库的所有表，使用命令：

mysql> SHOW TABLES;
 classes             
 statistics          
 students            
 students_of_class1  

要查看一个表的结构，使用命令：

mysql> DESC students;
| Field    | Type         | Null | Key | Default | Extra          |
| id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| class_id | bigint(20)   | NO   |     | NULL    |                |
| name     | varchar(100) | NO   |     | NULL    |                |
| gender   | varchar(1)   | NO   |     | NULL    |                |
| score    | int(11)      | NO   |     | NULL    |                |
5 rows in set (0.00 sec)

还可以使用以下命令查看创建表的SQL语句：

mysql> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------+
| students | 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 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)

创建表使用CREATE TABLE语句，而删除表使用DROP TABLE语句：

mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)

修改表就比较复杂。如果要给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;

最后使用EXIT命令退出MySQL：

mysql> EXIT
Bye

使用MySQL

插入或替换
如果希望插入一条新记录（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必须存在。