0%

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

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 |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+

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

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

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 的标题中是不区分大小写的。