刘硕的技术查阅手册

Python 全栈开发学习笔记

0%

数据库、数据表和数据的基本操作

发表于 更新于 分类于
SQL，中文全称是结构化查询语言（Structured Query Language），简称 SQL（发音：/ˈes kjuː ˈel/，即 `S-Q-L`），是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统关系数据库系统

SQL 概述

SQL，中文全称是结构化查询语言（Structured Query Language），简称 SQL（发音：/ˈes kjuː ˈel/，即 S-Q-L），是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统关系数据库系统

SQL 语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统。SQL 语言由 IBM 开发。SQL 语言分为 3 种类型：

  1. DDL 语句，数据库定义语言： 数据库、表、视图、索引、存储过程，例如 CREATE DROP ALTER
  2. DML 语句，数据库操纵语言： 插入数据 INSERT、删除数据 DELETE、更新数据 UPDATE、查询数据 SELECT
  3. DCL 语句，数据库控制语言： 例如控制用户的访问权限 GRANT、REVOKE

数据库操作

数据库，database，db，其本质上就是一个文件夹。

要想查看我们都有哪些数据库，可以使用这个命令：

1
show databases;

输出的结果为：

1
2
3
4
5
6
7
8
9
+--------------------+
| Database           |
+--------------------+
| information_schema |
| helloworld         |
| mysql              |
| performance_schema |
| public             |
+--------------------+

如果想要创建一个数据库，可以使用命令：

1
create database 数据库名;

例如，我们创建一个名为 test 的数据库，其命令为：

1
create database test;

回车之后，提示创建成功：

1
Query OK, 1 row affected (0.00 sec)

我们在数据存放的目录中，也发现新创建了一个名为 test 的文件夹。

需要注意的是，我们无论如何也不可以改动 MySQL 安装目录中的文件。因为这些文件都是按照很复杂的规则编写的，文件之间的关联也很紧密。修改一个数据，有可能造成整个数据库无法正常运行，甚至会造成数据的错误和丢失。

另外，创建的数据库名字中不能出现中文和特殊字符。一定要按照变量命名规范，使用数字、字母和下划线命名。

如果我们需要使用一个数据库，只需要使用命令：

1
use 数据库名;

这里的分号可以不加。在MySQL中，几乎只有use和exit、quit这三个命令后面不需要加分号了。但是稳妥起见，还是建议后面加上一个分号。

例如，我们要使用刚刚创建的 test 数据库，就可以这样写：

1
use test;

返回结果如下，说明成功切换到 test 数据库：

1
Database changed

如果想知道当前使用的是哪一个数据库，可以输入命令：

1
select database();

当前运行的数据库名顺利返回：

1
2
3
4
5
+------------+
| database() |
+------------+
| test       |
+------------+

我们日后几乎不会有删除数据库的需求。删除数据库是一个非常不好的行为，所以不在这里介绍删除数据库的方法。如果一定要学习，或者真的有删除数据库的需求，可以自行搜索，还是很简单的。

表操作

表，table，本质上就是数据库中的每一个文件。

创建表的方式和创建数据库极其相似，也是要用到 create 语句。不过创建表时，我们需要指定表头内容，以及其中的数据类型：

1
2
3
4
create table 表名(
id int,
name char(20),
age int);

需要注意，最后一项的后面不可以加逗号。

在 MySQL 中，如果不在语句的后面加分号，就会认为命令没有完结，就还可以继续输入。多行输入可以让内容开起来更直观。

上面的例子中，id、name 和 age 是表头，int的意思是，id 和 age 必须是整数。char(20) 的意思是，名字必须是不超过 20 位的字符串。

除了 int 和 char 外，MySQL 中还有其他数据类型，我们以后会有讨论。

于是，我们可以建立这样两个表：

1
2
3
4
5
6
7
8
9
create table table1(
id int,
name char(20),
age int);

create table table2(
id int,
username char(20),
password char(32));

每次创建完表格，都会有提示创建成功：

1
Query OK, 0 rows affected (0.04 sec)

要想查看当前数据库中都有哪些表，我们可以使用命令：

1
show tables;

我们就可以看到，我们创建的两个表就在其中：

1
2
3
4
5
6
+----------------+
| Tables_in_test |
+----------------+
| table1         |
| table2         |
+----------------+

正如我们从前讨论的，表本质上是储存在数据库文件夹中的文件。

如果有一个表，我们不想要了，删除表格是很容易的：

1
drop table 表名;

例如，我们想删除刚刚创建的table2，可以使用这样的命令：

1
drop table table2;

敲下回车后，提示删除成功：

1
Query OK, 0 rows affected (0.01 sec)

使用show tables;查询，以及看不见table2：

1
2
3
4
5
+----------------+
| Tables_in_test |
+----------------+
| table1         |
+----------------+

在数据库文件夹中，也不见 table2 的踪迹。

删除表虽然不像删除数据库那样严重，但依然不是好习惯。除非你十分确定需要删除那个表，而且名字没有错误，否则不可以轻易使用这个命令。

表格创建好之后，如果表头很长，我们或许无法记住标头的内容。可以通过这样的方法查看表结构：

1
desc 表名;

desc是describe的缩写，也就是描述的意思。

例如，要查看table1的表结构，可以用这个办法：

1
desc table1;

table1的表结构就会呈现出来：

1
2
3
4
5
6
7
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

除此之外，我们也可以通过查看创建表的语句来查看表头信息：

1
show create table 表名;

对于table1而言，具体的命令就是：

1
show create table table1;

table1的创建表语句为：

1
2
3
4
5
6
7
8
9
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                           |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

我们看到，查看表结构和创建表语句都可以达到查看表头信息的目的。不同的是，查看创建表语句还能看到诸如引擎、编码之类的更加详细的信息。

数据操作

表中的每一行内容，就是一条数据（data）。数据库从根本上来讲，就是用来管理这些数据的。数据存放在表中，而表存放在数据库中。

记住一句话：数据操作一定要指定操作的是哪一张数据表。

对数据的操作，无外乎增删改查四种。接下来，我们将逐一探讨。

增

向表中增加数据的基本结构为：

1
insert into 表名 values(数据1,数据2,数据3...);

例如，我们可以向table1中插入数据。注意数据要与表头一一对应：

1
insert into table1 values(1,'alex',83);

回车过后，提示操作成功：

1
Query OK, 1 row affected, 1 warning (0.00 sec)

我们可以通过命令 select * from table1 查看表中的内容，此时的结果为：

1
2
3
4
5
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | alex |   83 |
+------+------+------+

数据添加成功。

如果我们只想输入 id 和 name 两项内容，不想输入 age。如果这样输入会报错：

1
insert into table1 values (2,'wusir');

报错信息为，输入的数据条数和表格的列数不匹配：

1
ERROR 1136 (21S01): Column count doesn't match value count at row 1

这时，我们可以在表名后面指定我们要插入的数据的标题。同样地，指定的标题数目和我们插入的数据条数要一一对应，比如这样：

1
insert into table1(id,name) values(2,'wusir');

成功插入，查看 table1 中的内容是这样的：

1
2
3
4
5
6
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | alex  |   83 |
|    2 | wusir | NULL |
+------+-------+------+

第二行的 age 显示为 NULL，意味着本行为空。

其实，我们可以一次性在 values 后面指定多条数据，批量插入，每条数据之间，需要使用逗号隔开。例如：

1
insert into table1 values(3,'baoyuan',18),(4,'沙河吴彦祖',16);

此时table1中的内容为：

1
2
3
4
5
6
7
8
+------+-----------------+------+
| id   | name            | age  |
+------+-----------------+------+
|    1 | alex            |   83 |
|    2 | wusir           | NULL |
|    3 | baoyuan         |   18 |
|    4 | 沙河吴彦祖      |   16 |
+------+-----------------+------+

说明一点，虽然存放数据库的路径和数据库名字、表名字中不可以出现中文，但数据中是可以存放中文的。

同样地，我们也可以通过指定标题的方式，只插入部分数据：

1
insert into table1(id,name) values(5,'baoyuan'),(6,'沙河吴彦祖');

成功插入后，table1 的内容为：

1
2
3
4
5
6
7
8
9
10
+------+-----------------+------+
| id   | name            | age  |
+------+-----------------+------+
|    1 | alex            |   83 |
|    2 | wusir           | NULL |
|    3 | baoyuan         |   18 |
|    4 | 沙河吴彦祖      |   16 |
|    5 | baoyuan         | NULL |
|    6 | 沙河吴彦祖      | NULL |
+------+-----------------+------+

删

有的时候，我们需要删除表格中的某些数据。删除数据的标准格式为：

1
delete from 表名 where 条件

注意，这里面一定要指定条件，否则的话，这个命令就是清空整个表格。

对于上面的例子中，后两条命令与前面的重复，我们想要把它们都删掉。我们发现，它们的 id 都大于 4，且只有它们的 id 大于 4。于是，我们就可以用这个作为条件，把它们删除掉：

1
delete from table1 where id>4;

后面两条内容被成功删除：

1
2
3
4
5
6
7
8
+------+-----------------+------+
| id   | name            | age  |
+------+-----------------+------+
|    1 | alex            |   83 |
|    2 | wusir           | NULL |
|    3 | baoyuan         |   18 |
|    4 | 沙河吴彦祖      |   16 |
+------+-----------------+------+

改

我们看到，第二行 wusir 的年龄我们没有指定。这就需要修改他的年龄。

MySQL 标准的修改数据的方式为：

1
update 表名 set 字段名 = 值 where 条件;

同样地，这里的条件是必须的。否则，该字段下的所有数据都会被修改成为新的值。

如果我们想设置wusir的年纪，可以这样操作：

1
update table1 set age=35 where id=2;

设置成功：

1
2
3
4
5
6
7
8
+------+-----------------+------+
| id   | name            | age  |
+------+-----------------+------+
|    1 | alex            |   83 |
|    2 | wusir           |   35 |
|    3 | baoyuan         |   18 |
|    4 | 沙河吴彦祖      |   16 |
+------+-----------------+------+

这里的条件不一定要指定 id=2，我们也可以使用 name='wusir'。需要注意的是，字符串的 'wusir' 要用引号引起来。

查

表格中数据的查找我们一直都在用，其基本结构为：

1
selct * from 表名;

这条命令用来查看表格中所有的内容。

不过对于一些列数特别多的表格来说，这种查看方式会很乱，甚至可能会乱到无法阅读。

比如对于 mysql 数据库的 user 表来说，我们用这个方式查看，得到的结果会是这样：

1
2
3
4
5
6
7
8
9
10
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+
| Host      | User    | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+
| localhost | root    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
| 127.0.0.1 | root    |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
| ::1       | root    |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
| localhost |         |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | NULL                  | N                |
| %         | guest   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
| %         | xianhui | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+

除非使用一个很大的显示器，我们很难看懂这一大串数据究竟是什么东西。

不过如果我们只对表格中的某几列感兴趣，就可以将*替换成列的名字，列明之间用逗号隔开，例如我们只想查看Host、User和Password这三列，就可以这样查看：

1
select host,user,password from user;

结果就变成了：

1
2
3
4
5
6
7
8
9
10
+-----------+---------+-------------------------------------------+
| host      | user    | password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root    |                                           |
| ::1       | root    |                                           |
| localhost |         |                                           |
| %         | guest   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| %         | xianhui | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+---------+-------------------------------------------+

看起来就简单了许多。

你或许已经发现，在上面的显示中，Host、User 和 Password 的首字母都大写，而我输入的标题全部用的小写。这是因为 MySQL 的标题中是不区分大小写的。