数据库(一):MySQL

了解MySQL,一个开源免费的关系型数据库,掌握其登录、基本使用、数据库与表的创建及索引应用,学习如何通过SQL执行增删改查操作,并探索Sequelize ORM框架在Node.js中的数据库操作应用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

数据库(一):MySQL

MySQL 是一款开源免费的关系型数据库,特点如下:

  • 每个表都是独立的
  • 表和表之间通过公共字段来建立关系

下载地址:MySQL Community Downloads

  1. 自定义
  2. 版本号
  3. 路径
  4. 账号/密码
  5. 环境变量

基本使用

通常情况下,MySQL 被安装在远程服务器上,所以必须连接到远程服务器并登录,才可以使用 MySQL,格式如下:

mysql -h <serverIP> -P<port> -u<username> -p<password>

示例如下:

mysql -h 127.0.0.1 -P3306 -uroot -p1234

如果 MySQL 服务器在本地,且默认使用 3306 端口号,那么主机地址(-h)和端口号(-P)可以省略,示例如下:

mysql -uroot -p1234

如果在参数 -p 之后未输入任何内容,那么将以隐藏密码的方式登录,示例如下:

mysql -uroot -p

综上所述,最常用的登录远程 MySQL 服务器(端口号:3306)时的指令格式如下:

mysql -h <serverIP> -uroot -p

此外,可以通过 exit;quit\q 中的任何一个指令退出 MySQL 连接

在登录 MySQL 数据库后,可以通过 show databases; 指令查看所有数据库,输出内容如下:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

以上所有的数据库均为系统数据库,内容如下所示:

数据库 内容
information_schema MySQL 服务器所维护的所有另外数据库的信息
mysql MySQL 服务器的用户名、密码和权限等信息
performance_schema MySQL 服务器性能相关的参数
sys 以视图的形式将 info_schema 和 perf_schema 合并,从而使数据更容易让人理解

基本概念

任何一个数据库的核心内容即为增删改查(CRUD)

  1. 数据库
  2. 字段、记录

MySQL 利用 SQL(Structured Query Language)语言实现此功能

SQL 语言是关系型数据库的通用语言,各个厂商通常会在标准 SQL 的基础之上扩展独特的内容,例如 SQL Server 实际上使用的是在 SQL 基础之上扩展的 T-SQL

SQL 语句以功能可以划分为如下类别:

功能 名称 含义
数据定义 DDL 定义实例(库、表、字段等)
数据操纵 DML 操纵记录
数据查询 DQL 查询数据
数据控制 DCL 访问权限、安全级别

SQL 语言中常用的数据类型如下所示:

数据类型 含义
double 浮点数
char 固定长度字符串
varchar 可变长度字符串
text 字符串
blob 二进制
date 日期
time 时间
datetime 日期/时间

数据库

创建

格式如下:

create database [if not exists] <DBName> [charset=<utf8/gbk>];

示例如下:

create database db01;

上述示例为创建数据库最简单的方式,缺点在于如果创建已存在的数据库,那么将出现错误,此时可以使用 if not exists 约束,示例如下:

create database if not exists db01;

输出内容如下:

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

上述示例的含义是如果存在 db01 数据库,那么自动忽略此语句,如果不存在则创建数据库,此外,在创建数据库时可以指定字符集,默认的字符集为 utf8,示例如下:

create database if not exists db02 charset='gbk';

此时可以通过如下语句查看字符集:

show create database db02;

输出内容如下:

+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                 |
+----------+-------------------------------------------------------------------------------------------------+
| db02     | CREATE DATABASE `db02` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

另外,如果创建数据库时以关键字或特殊字符命名,那么必须将数据库名称以 ` 字符包围,示例如下:

create database `create`;

查询

查看数据库的格式如下:

  1. 所有数据库
show databases;
  1. 某个数据库
show create database <DBName>;

示例如下:

show create database db01;

输入内容如下:

+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| db01     | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除

格式如下:

drop database [if exists] <DBName>;

示例如下:

drop database db02;

类似于创建数据库,if exists 表示如果待删除数据库存在则删除,否则自动忽略此语句,示例如下:

drop database if exists db02;

输出内容如下:

Query OK, 0 rows affected, 1 warning (0.01 sec)

修改

MySQL 中可以修改数据库的字符集,格式如下:

alter database <DBName> charset=<utf8/gbk>;

示例如下:

alter database db01 charset=gbk;

数据库中的每一张表都存储在某个具体的数据库中,所以在操作某个表之前,必须切换到表所在的数据库,MySQL 中切换数据库的格式如下:

use <DBName>;

示例如下:

use db01;

输出内容如下:

Database changed

创建

格式如下:

create table [if not exists] <TableName> (
    FieldName DataType,
    FieldName DataType,
    FieldName DataType,
    ...
    FieldName DataType,
    FieldName DataType
);

if not exists 含义表示如果当前数据库中不存在待创建的表则创建,否则跳过此语句

示例如下:

create table if not exists student (
    id      int,
    name    text
);

必须注意的是,最后一个字段末尾不能写 , 字符,此时若想查看创建的表,可以通过如下命令:

show tables;

示例如下:

show tables;

输入内容如下:

+----------------+
| Tables_in_db01 |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)
存储引擎

在 MySQL 中创建表时,可以指定存储引擎类型,格式如下:

create table <TableName> (
    FieldName DataType,
    FieldName DataType,
    ...
    FieldName DataType
) engine=<EngineName>;

示例如下:

create talbe stu (
    id      int,
    name    text
) engine=MyISAM;

MySQL 中存储引擎的相关内容如下所示:

存储引擎 特点
MyISAM 安全性低、不提供事务和外键
InnoDB 安全性高、提供事务和外键
Memory 访问速度极快、不会永久存储数据

默认情况下,表的存储引擎为 InnoDB,实际上,数据库的本质是一个文件,在创建数据库时,系统将自动创建一个目录,在数据库中创建表时,系统将自动创建一系列文件,不同的存储引擎有不同的文件结构:

  • InnoDB
    • 如果以 InnoDB 为存储引擎,那么在创建表时系统将自动创建以 .ibd 为扩展名的文件,此文件保存了表的结构
    • 如果向此表中存储数据,那么数据将被存储在名称为 ibdata1 的文件中,如果数据较多,系统将自动创建 ibdata2 以存储更多的数据
  • MyISAM
    • 如果以 MyISAM 为存储引擎,那么在创建表时系统将自动创建如下文件:
      • .sdi
        • 表的结构
      • .MYD
        • 表的数据
      • .MYI
        • 表的索引
  • Memory
    • 如果以 Memory 为存储引擎,那么在创建表时将自动创建以 .sdi 为扩展名的文件,此文件保存了表的结构
    • 必须注意的是,以 Memory 为存储引擎的表的数据不会保存到文件中,而是将直接被保存到内存中,此外,Memory 存储引擎不支持 text/blob 数据类型

查询

除了可以查询当前数据库中存在哪些表,也可以查看表中的具体结构,格式如下:

desc <TableName>;

示例如下:

desc student;

输出内容如下:

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| name  | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

删除

格式如下:

drop table [if exists] <TableName>;

示例如下:

drop table if exists student;

此时数据库中已不存在表,查看表后的内容如下:

Empty set (0.00 sec)

修改

数据库 db01 最初的样式如下所示:

+----------------+
| Tables_in_db01 |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

表 student 最初的样式如下所示:

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| name  | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
  1. 修改表名

格式如下:

rename table <OriginalTableName> to <NewTableName>;

示例如下:

rename table student to person;

输出内容如下:

+----------------+
| Tables_in_db01 |
+----------------+
| person         |
+----------------+
1 row in set (0.00 sec)
  1. 添加字段

格式如下:

alter table <TableName> add <NewFieldName> <NewFieldDataType> [Location];

示例如下:

alter table person add age int;

输出内容如下:

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| name  | text | YES  |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

默认情况下,新添加的字段位于表的末尾,如果让新增字段位于第一行,可以设置可选参数 Location 为 first 实现,示例如下:

alter table person add score float first;

输出内容如下:

+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| score | float | YES  |     | NULL    |       |
| id    | int   | YES  |     | NULL    |       |
| name  | text  | YES  |     | NULL    |       |
| age   | int   | YES  |     | NULL    |       |
+-------+-------+------+-----+---------+-------+
4 rows in set (0.00 sec)

如果让新增字段位于某一行之后,可以设置可选参数 Location 为 after (FieldName) 实现,示例如下:

alter table person add phone int after name;

输出内容如下:

+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| score | float | YES  |     | NULL    |       |
| id    | int   | YES  |     | NULL    |       |
| name  | text  | YES  |     | NULL    |       |
| phone | int   | YES  |     | NULL    |       |
| age   | int   | YES  |     | NULL    |       |
+-------+-------+------+-----+---------+-------+
5 rows in set (0.00 sec)
  1. 删除字段

格式如下:

alter table <TableName> drop <FieldName>;

示例如下:

alter table person drop phone;

输出内容如下:

+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| score | float | YES  |     | NULL    |       |
| id    | int   | YES  |     | NULL    |       |
| name  | text  | YES  |     | NULL    |       |
| age   | int   | YES  |     | NULL    |       |
+-------+-------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  1. 修改字段

    1. 数据类型

    格式如下:

    alter table <TableName> modify <FieldName> <NewDataType>;
    

    示例如下:

    alter table person modify score double;
    

    输出内容如下:

    +-------+--------+------+-----+---------+-------+
    | Field | Type   | Null | Key | Default | Extra |
    +-------+--------+------+-----+---------+-------+
    | score | double | YES  |     | NULL    |       |
    | id    | int    | YES  |     | NULL    |       |
    | name  | text   | YES  |     | NULL    |       |
    | age   | int    | YES  |     | NULL    |       |
    +-------+--------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
    1. 名称 & 数据类型

    格式如下:

    alter table <TableName> change <OriginalFieldName> <NewFieldName> <NewDataType>;
    

    示例如下:

    alter table person change age address text;
    

    输出内容如下:

    +---------+--------+------+-----+---------+-------+
    | Field   | Type   | Null | Key | Default | Extra |
    +---------+--------+------+-----+---------+-------+
    | score   | double | YES  |     | NULL    |       |
    | id      | int    | YES  |     | NULL    |       |
    | name    | text   | YES  |     | NULL    |       |
    | address | text   | YES  |     | NULL    |       |
    +---------+--------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
  2. 修改引擎

格式如下:

alter table <TableName> engine=<EngineName>;

示例如下:

alter table person engine=MyISAM;

记录

记录相关的表的结构如下所示:

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| name  | text | YES  |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

创建

格式如下:

insert into <TableName> (FieldName-1, FieldName-2, ..., FieldName-N) values (Value-1, Value-2, ..., Value-N) ... (Value-1, Value-2, ..., Value-N);

示例如下:

insert into student (id, name, age) values (1, 'reyn', 21);

此时若想查看创建的记录,可以通过如下命令:

select * from student;

输出内容如下:

+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | reyn |   21 |
+------+------+------+
1 row in set (0.00 sec)

实际上,在创建记录时,若添加数据时的字段顺序和表的结构相同,那么可以省略字段说明,示例如下:

insert into student values (2, 'lily', 20);

输出内容如下:

+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | reyn |   21 |
|    2 | lily |   20 |
+------+------+------+
2 rows in set (0.00 sec)

此外,在创建记录时,可以同时添加多条记录,示例如下:

insert into student values (3, 'jack', 22), (4, 'jhon', 21);

输出内容如下:

+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | reyn |   21 |
|    2 | lily |   20 |
|    3 | jack |   22 |
|    4 | jhon |   21 |
+------+------+------+
4 rows in set (0.00 sec)

实际上,在创建记录时,字段的说明顺序可以和表的结构不一致,但记录值的顺序必须和字段的说明顺序一致,示例如下:

insert into student (name, age, id) values ('jobs', 19, 5);

输出内容如下:

+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | reyn |   21 |
|    2 | lily |   20 |
|    3 | jack |   22 |
|    4 | jhon |   21 |
|    5 | jobs |   19 |
+------+------+------+
5 rows in set (0.00 sec)

若不一致将出现错误,示例如下:

insert into student (name, age, id) values (6, 'miles', 25);

输出内容如下:

ERROR 1366 (HY000): Incorrect integer value: 'miles' for column 'age' at row 1

查询

除了可以查询一个表中的所有数据之外,也可以查询某个或某些在具体条件下的特定字段的取值,格式如下:

select (FieldName-1, FieldName-2, ... FieldName-N) from <TableName> [where <Condition>]

示例如下:

select name, age from student;

输出内容如下:

+------+------+
| name | age  |
+------+------+
| reyn |   21 |
| lily |   20 |
| jack |   22 |
| jhon |   21 |
| jobs |   19 |
+------+------+
5 rows in set (0.00 sec)

实际上,可以通过 where 选项附加条件以查询更加准确的内容,条件通常由字段名称和取值组成,由关键字和逻辑运算符连接,如下所示:

连接符 含义
= 等于
!= 不等于
<> 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
in(...) 是否在特定集合中
between ... and ... 是否在某个区间
and 逻辑与
or 逻辑或
not 逻辑非
like 模糊查询
is null
is not null 非空

查询原理是遍历表中的所有记录,判断记录是否满足 where 附加条件,如果结果为真,那么将记录添加到查询结果,示例如下:

  • 查询姓名为 reyn 的学生
select * from student where name='reyn';

输出内容如下:

+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | reyn |   21 |
+------+------+------+
1 row in set (0.00 sec)
  • 查询年龄大于等于 20 岁的学生姓名
select name from student where age>=20;

输出内容如下:

+------+
| name |
+------+
| reyn |
| lily |
| jack |
| jhon |
+------+
4 rows in set (0.04 sec)
  • 查询年龄在 18 到 21 岁的所有学生姓名及年龄
select name,age from student where age between 18 and 21;

输出内容如下:

+------+------+
| name | age  |
+------+------+
| reyn |   21 |
| lily |   20 |
| jhon |   21 |
| jobs |   19 |
+------+------+
4 rows in set (0.01 sec)
  • 查询年龄为 21 且姓名为 jhon 的学生
select * from student where age=21 and name='jhon';

输出内容如下:

+------+------+------+
| id   | name | age  |
+------+------+------+
|    4 |
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值