数据库粗略回顾（三）
MySQL SQL
数据库
发布日期:   2020-03-03
作者:   Adward
文章字数:   3k
阅读时长:   12 分
阅读次数:  

五、修改数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。其中，对于查询，我们已经详细讲述了SELECT语句的详细用法。

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

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

5.1 Insert

当我们需要向数据库表中插入一条新记录时，就必须使用INSERT语句。

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;

//RESULTS
11    2    大牛    M    80

我们并没有列出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;

小结

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

5.2 UPDATE

UPDATE语句的基本语法是：

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

eg:更新id=1的记录

UPDATE students
    SET name = '大牛', score = 66
    WHERE id =1;
-- 查询并观察结果:
SELECT * FROM students where id = 1;

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

-- 更新id=5,6,7的记录
UPDATE students 
    SET name = '小牛', score = 77
    WHERE  id >=5   AND id <=7;

-- 查询并观察结果:
SELECT * FROM students;

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

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

SET score=score+10就是给当前行的score字段的值加上了10。

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

UPDATE students 
SET score=100 
WHERE id=999;

最后，要特别小心的是，UPDATE语句可以没有WHERE条件，例如：

UPDATE students 
SET score=60;

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

MySQL

在使用MySQL这类真正的关系数据库时，UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。

例如，更新id=1的记录时：

mysql> UPDATE students SET name='大宝' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL会返回1，可以从打印的结果Rows matched: 1 Changed: 1看到。

当更新id=999的记录时：

mysql> UPDATE students SET name='大宝' WHERE id=999;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MySQL会返回0，可以从打印的结果Rows matched: 0 Changed: 0看到。

小结

使用UPDATE，我们就可以一次更新表中的一条或多条记录。

5.3 DELETE

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;

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

DELETE FROM students WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students;

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

-- 删除id=5,6,7的记录
DELETE FROM students WHERE id>= 5 AND id <= 7;

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

最后，要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：

DELETE FROM students;

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

MySQL

在使用MySQL这类真正的关系数据库时，DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。

例如，分别执行删除id=1和id=999的记录：

mysql> DELETE FROM students WHERE id=1;
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM students WHERE id=999;
Query OK, 0 rows affected (0.01 sec)

小结

使用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的可执行程序是mysql，MySQL Server的可执行程序是mysqld。

MySQL Client和MySQL Server的关系如下： 

┌──────────────┐  SQL   ┌──────────────┐
│ MySQL Client │───────>│ MySQL Server │
└──────────────┘  TCP   └──────────────┘

在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，在后台运行。

6.1 管理MySQL

要管理MySQL，可以使用可视化图形界面MySQL Workbench。

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;
+--------------------+
| Database           |
+--------------------+
| 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) //not DELETE

注意：删除一个数据库将导致该数据库的所有表全部被删除。

对一个数据库进行操作时，要首先将其切换为当前数据库：

mysql> USE test;
Database changed

表

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

mysql> SHOW TABLES;
+---------------------+
| Tables_in_test      |
+---------------------+
| 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;
//改变表 students  加一列 birth 数据类型 非空

要修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

要删除列，使用：

ALTER TABLE students DROP COLUMN birthday;

退出MySQL

mysql> EXIT
Bye

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

6.2 实用SQL语句

在编写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 VALUE 
    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语句的结果集直接插入到指定表中。

eg:创建一个统计成绩的表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必须存在。

赏

   转载规则


《数据库粗略回顾（三）》 由 Adward 采用 知识共享署名 4.0 国际许可协议 进行许可。
 上一篇
Java 基础 Java 基础
Day04IntelliJ使用小技巧 输入Psvm自动生成主方法入口 输入sout自动生成输出语句 Alt 加数字 4 打开运行输出框 快捷键设置 Settings - Keymap - Default copy - Code - Comp
2020-03-14 编程语言
Java
下一篇 
数据库粗略回顾（二） 数据库粗略回顾（二）
四、查询数据4.1 基本查询要查询数据库表的数据，我们使用如下的SQL语句： SELECT * FROM <表名>假设表名是students，要查询students表的所有行，我们用如下SQL语句： SELECT * F
2020-03-03 数据库
MySQL SQL
  目录