数据库(一):MySQL
MySQL 是一款开源免费的关系型数据库,特点如下:
- 每个表都是独立的
- 表和表之间通过公共字段来建立关系
下载地址:MySQL Community Downloads
- 自定义
- 版本号
- 路径
- 账号/密码
- 环境变量
基本使用
通常情况下,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)
- 数据库
- 表
- 字段、记录
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`;
查询
查看数据库的格式如下:
- 所有数据库
show databases;
- 某个数据库
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
- 表的索引
- .sdi
- 如果以 MyISAM 为存储引擎,那么在创建表时系统将自动创建如下文件:
- 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)
- 修改表名
格式如下:
rename table <OriginalTableName> to <NewTableName>;
示例如下:
rename table student to person;
输出内容如下:
+----------------+
| Tables_in_db01 |
+----------------+
| person |
+----------------+
1 row in set (0.00 sec)
- 添加字段
格式如下:
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)
- 删除字段
格式如下:
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)
-
修改字段
- 数据类型
格式如下:
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)
- 名称 & 数据类型
格式如下:
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)
-
修改引擎
格式如下:
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 |