【mysql】MySQL的数据库简单搭建

前言

最近想搞一下用户排名显示的业务，发现mysql的操作忘得差不多的，趁着现在实现用户排名需求，记录下mysql的学习之旅。
本篇 默认已经安装好mysql，且已经初始化mysql，并使用mysql账号登录了。若没有，请参考其他博文安装好mysql。
mysql不区分大小写，建议关键字用大写（不要学我，我懒我喜欢用小写），取得数据库名、表名和字段中得单词用下划线连接。

基础知识

数据库

数据库是存放数据的仓库。Mysql是关系型数据库，存储的格式可以直观地反映实体间的关系。关系型数据库和常见的表格比较相似，关系型数据库中表与表之间是有很多复杂的关联关系的。

数据表

数据表就是存放一组具有一定规则组成的二维数据的表格，它存放到数据库中。数据表由表名、数据表结构和符合数据表结构规范的数据（记录）组成。

数据表结构

数据表结构就是多个字段和约束组成的数据结构。一个字段由字段名、数据类型、字段约束组成。MySQL一个表最多可以有4096个字段。‌这个限制是由数据库管理系统的实现决定的，‌与具体的MySQL版本和配置有关。‌在MySQL 8.0版本中，‌默认情况下，‌一个表可以添加的字段数量最多为4096个。‌

记录（数据）

记录就是数据表中的数据。一条记录就是表中的一行数据。

数据类型

mysql支持多种数据类型，主要有数值类型、字符串类型、日期类型、二进制类型、空间数据类型和特定数据格式的类型（例如json）等。

数值类型

类型名称	大小	描述
TINYINT	1个字节	很小的整数，如果你的数据不过百，可以用这个
SMALLINT	2个字节	小的整数
MEDIUMINT	3个字节	稍小的整数
INT	4个字节	整数
BIGINT	8个字节	大的整数
FLOAT	4个字节	单精度浮点数
DOUBLE	8 个字节	双精度浮点数

字符串类型

类型名称	大小	描述
CHAR(M)	M个字节，M:[1,255]	固定长度字符串
VARCHAR(M)	L+1字节,在此L<=M和1<=M<=255	变长字符串，具体存储大小取决于实际的数据大小，性能稍慢
TINYTEXT	L+1字节，在此L<2^8	小文本字符串
TEXT	L+1字节，在此L<2^8	文本字符串 ，适用于存储文章内容、用户评论等较长的文本信息
MEDIUMTEXT	L+3字节,在此L<2^24	大型文本字符串 ，适合存放更大一些的文本字符串，如大型文档、代码等
LONGTEXT	L+4字节,在此L<2^32	超大型文本字符串， 适合存储大型日志文件等
ENUM	1或2个字节,取决于枚举值数目(最大值65535)	枚举类型
SET	1,2,3,4或8个字节，取决于集合成员的数量（最多64个成员）	一个设置,字符串对象可以有0个或多个SET成员

日期类型

类型名称	格式	范围	描述
YEAR	YYYY	1901~2155	年数，1个字节
TIME	HH:MM:SS	-838:59:59~838:59:59	时分秒， 3个字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	年月日，3个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	年月日时分秒，8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 UTC ~ 2038-01-19 03:14:07 UTC	年月日时分秒，4字节，带时区转换

二进制类型

类型名称	大小	描述
BIT(M)	大约(M+7)/8个字节	位字段类型
BINARY(M)	M个字节	固定长度二进制字符串类型
VARBINARY(M)	M+1个字节	可变长度二进制字符串
TINYBLOB(M)	L+1个字节,在此L<2^8	非常小的BLOB
BLOB(M)	L+2字节,在此L<2^16	blob类型的二进制字符串
MEDIUMBLOB	L+3字节,在此L<2^24	稍大的 blob
LONGBLOB(M)	L+4字节,在此L<2^32	超大的blob

其他数据类型

除上面的类型外，mysql还有一些关于图形、地理信息等空间数据类型，以及json等具有特定格式的字符串数据类型。

约束

MySQL中的约束是用来保证数据的完整性和一致性的规则。常见的约束包括：

• PRIMARY KEY (PK)：标识该字段为表的主键，可以唯一地标识表中的每条记录。
• NOT NULL：标识该字段不能有NULL值。
• UNIQUE：保证该字段在整个表中的每条记录中都是唯一的。
• FOREIGN KEY (FK)：标识该字段为表的外键，它用来建立和加强两个表之间的联系。
• CHECK：确保字段值符合指定的条件。

where子句

WHERE 子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。
常用条件操作类型：

操作符	描述	短语
=	等号，检测两个值是否相等，如果相等返回true。	相等
<>,!=	不等于，检查两个值是否不相等，如果不相等返回true。	不相等
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true。	大于
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true。	小于
>=	大于等于，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true。	大于等于
<=	小于等于，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true。	小于等于

其他条件

其他条件有AND(和)、OR(或)、like(模糊匹配) 等关键词。

操作符	描述	短语
AND	与，也就是和的意思，当同时满足左侧条件和右侧条件时为true。	与（和）
OR	或，当只需满足左侧条件和右侧条件一项时为true。	或
NOT	非 ， 当不满足这个条件时为true。例如 not id='123，表示id不等于123	非
IN	当满足给定的某一组范围值时，为true。	包含
BETWEEN	BETWEEN 运算符确定一个值是否介于某两个值之间。一般和and搭配使用，例如 value between 10 and 100.则value值在[10,100]之间为true。	两者之间
IS NULL	是否为空，若为空则为true。	判空
IS NOT NULL	是否非空，若非空则为true。	判非空

除上述的条外外，还有like模糊匹配条件子句。
LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一起使用，用于搜索符合某种模式的字符串。
通配符：

• %（百分号） —— 通配符表示零个或多个字符。例如，‘a%’ 匹配以字母 ‘a’ 开头的任何字符串。
• _（下划线）—— _ 通配符表示一个字符。例如，‘_r%’ 匹配第二个字母为 ‘r’ 的任何字符串。

通常like模糊匹配字符串搭配%和_使用。
例如： 查找id以123开头的数据 .

id like '123%'

查看用户名包含abC和abc的数据

u_name like '%abC%'  or u_name like '%abc%'

order by 语句 （排序）

MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序（ASC）或降序（DESC）排序。

ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], …;

column1和column2是用于指定排序顺序的列。ASC 表示升序（默认），DESC 表示降序。
例如 ： id ASC, value DESC,则先id升序排序，然后value按降序排序。

group by语句（分组）

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY column_name;

GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具，尤其在处理大量数据时，它能够提供有用的汇总信息。
例如 ：我们使用 GROUP BY customer_id 将结果按 customer_id 列分组，然后使用 SUM(order_amount) 计算每个组中 order_amount 列的总和。

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

数据库操作

数据库操作主要分为新建、删除和使用，例如：

-- 查看game数据库是否存在，存在就删除
drop database if exists game; 
-- 新建game数据库
create database game;
-- 使用game数据库 
use game;

新建数据库

新建数据库使用create关键词创建，。

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

• database_name——数据库名称，前面添加IF NOT EXISTS，可以先检查该数据库是否存在，不存在则新建。
• charset_name——数据库的字符集，可以省略使用默认。
• collation_name——排序规则，可以省略使用默认。

创建一个game数据库：

create database game;

删除数据库

删除数据库使用drop关键词进行删除，删除后该数据库和数据表都将消失，需注意删除操作不可逆，删除前建议备份数据。

DROP DATABASE <database_name>; – 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;

删除game数据库

drop database if exists game;

使用数据库

选择使用的数据库，使用use关键词：

USE database_name;

执行后，即可对该数据库的内容进行操作。

例如，使用game数据库。

use game;

数据表操作

选择指定的数据库后，可以在数据库中新建数据表。
数据表的操作一般分为新建数据表、更新数据表、删除数据表、数据表查询等操作。

新建数据表

创建数据表使用create table 关键词进行操作：

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
…
[column configuration]
) [table configuration];

• table_name —— 数据表名。
• column1 datatype—— column1为数据表中某一列得字段名，字段名在表中不能重复；datatype 为这个列的一些配置，例如字段类型、约束啊之类的。
• column configuration —— 数据表的中列配置，例如列约束、索引啊之类的。
• table configuration —— 数据表的配置，例如字符集、描述、存储引擎啊之类的。
  

创建表比较复杂，因为它是存储数据的地方，为了满足各种需求的数据，肯定需要添加各种限制或功能来存储数据。这里不考虑这么多，从最简单的数据表开始，后续碰到其他功能再说。

创建表之前，应该提前考虑表的字段合理性，设计好表的结构，遵循第三范式啥的，尽量降低数据的冗余，提高数据完整性。

第一范式（1NF）：数据库表中的每一列（或者每个属性）必须是不可分割的最小单元，也就是确保每列的原子性。
第二范式（2NF）：在满足第一范式的前提下，表中的每一行必须可以被唯一地区分，通常通过设置主键来实现。
第三范式（3NF）：在满足第二范式的前提下，表中的每一列数据都应该依赖于主键，而不是传递依赖于主键。简单来说，就是确保表中的每列都和主键直接相关，而不是间接相关。

创建一个游戏用户数据表和游戏分数排行表。

分析：一个简单的用户信息表只包含用户id、用户名即可，用户id为主键，用户名是为了我们更好的区分用户。然后再根据用户信息表的用处进行扩展，如果是基本信息表就包含用户的基本信息，将最小单元作为列，不是最小的再建一个表存储，并用用户id作为外键进行关联。
创建游戏用户表，列名用户id、用户名、用户头像、用户性别、用户创建时间、用户状态：

-- 用户表 
drop table if exists user_info;
CREATE TABLE user_info (
    id VARCHAR(30) PRIMARY KEY,
    u_name VARCHAR(20) NOT NULL,
    avatar VARCHAR(100),
    sex INT DEFAULT 1,
    active BOOLEAN DEFAULT TRUE,
    create_time DATE
);

• id —— 用户id ,变长字符串类型，为主键。
• u_name —— 用户名，变长字符串，不为空。
• avatat —— 用户头像，变长字符串（这里我想存图像uri地址,所以用的字符串）。
• sex —— 用户性别， 整形，1为男，2为女，默认为1。（你可以使用enum枚举类型）。
• active —— 用户状态，布尔型，true为正常，false为异常,默认为true。
• create_time —— 创建日期，日期类型。

创建游戏分数排行表。
分析：游戏分数要包括这个用户的信息和用户装备信息，以及游戏分数，所以表字段为：表id、用户id、用户装备id、游戏分数。

create table user_score (
	id int primary key auto_increment ,
    u_id varchar(30),
    e_id varchar(30),
    score int default 0 ,
    create_time timestamp default current_timestamp,
    constraint scoreUserKey foreign key (u_id) references user_info(id),	
    constraint scoreEquipKey foreign key (e_id) references user_equip(id)
);

• id —— 用户游戏分数id,整形，主键，且自增。
• u_id —— 用户id,与用户信息表形成外键关联。
• e_id —— 用户的装备信息id,与用户装备信息表形成外键关联。
• score —— 用户分数，整形，默认0.

另外还有用户的装备信息表，这个可以简化称用户使用默认武器，然后指显示武器名字即可，这里不做设计了。

修改数据表

数据表在新增后，若不满足后续需求可以更改数据表结构，修改表结构语法：

alter table 数据表名 [修改表名 | 修改表字段 | 添加、删除表字段];

修改数据表结构，根据想要修改的表结构内容有多种语法。

修改数据表名

修改某个数据表名，可以使用下面语法

alter table 旧表名 rename to 新表名;

alter table user_info rename to user_base;

数据表中添加新字段

想要往某个数据表添加一个新字段，可以使用语法：

alter table 表名 add [字段名 类型 约束] [first | after] [已存在字段名];

alter table user_info add address varchar(50) ;

修改数据表某个字段

修改数据表中某个字段，可以使用以下语法：

alter table 表名 modify [字段名 类型 约束] [first | after] [已存在字段名];

alter table user_info modify address varchar(100) default '' after sex ;

删除数据表某个字段

删除数据表字段，可以使用以下语法：

alter table 表名 drop 字段名；

alter table user_info drop address;

查看数据表

查看数据表有两种方式，
查看表结构语句 ：

show create table 数据表名

查看表字段列表

describe 数据表名;

use  game;
show create table user_score;
describe user_score;

删除数据表

要想删除某个数据表，使用语法：

drop table 数据表名

drop table user_score;

DROP TABLE 是 SQL 中用于删除表的命令。使用 DROP TABLE 可以从数据库中彻底删除一个表及其所有数据、索引、触发器、约束等。该操作不可逆，执行后无法恢复，因此使用时需要非常谨慎。
另外，执行该语句需要有相应的权限，重要提醒：执行删除语句前务必要备份数据。

数据记录操作

数据记录就是数据表中一行行的数据，一行数据就是一条记录，数据有增删查改等操作。

添加数据

mysql表要使用 insert into 语句来插入数据：

insert into 表名 (column1,column2, …) values (value1,value2, …);

• 表名 —— 要插入数据的数据表名称。
• column1 —— 数据表列名1。
• column2 —— 数据表列名2。
• value1 —— 这条记录中数据表列名1的值。
• value2 —— 这条记录中数据表列名2的值。

例如 ：

insert into user_info (id,u_name,create_time) values ('123',"张三", curdate());
insert into user_info (id,u_name,create_time) values 
('1234',"张三2", curdate()),
('12345',"张三3", curdate())
;

注意事项：

• 如果该列为自增列，添加时该列不需要赋值。
• 如果该列什么都没有约束，那么添加时若该列没赋值，则默认为NULL，如果有默认值，则为默认值；若有赋值，则为赋的值。
• 如果该列设为主键，则该列值不能与表中的其他记录的主键值重复。
• 如果该列设为外键，则该列值需要为外键关联表的某条记录主键值。
• 如果该列设为必填，则插入数据时必需为该列赋值。

数据插入失败会有相应提示，根据提示调整插入语句即可。

删除数据

使用 DELETE FROM 命令来删除 MySQL 数据表中的记录。

delete from 表名 [where 条件子句];

例如 ： 删除user_info表所有数据（记录）。

delete from user_info;

删除某一项或多项数据的话，可以添加where子句来限制删除数据的范围。
例如：删除user_info表中id=123的数据。

delete from user_info id='123';

执行后发现报错，报错描述是因为该表的id有作为外键和其他表相关联，正常情况下应该先删除关联表的相关数据，然后再删除指定行数据。这里由于相关表没有数据，所以我们先关闭外键约束检查，然后直接删除该表的指定行数据，即可执行成功，最后开启外键约束检查。

-- 关闭约束检查
set foreign_key_checks =0;
delete from game.user_info where id='123';
-- 开启约束检查
set foreign_key_checks = 1;

更新数据

我们需要修改或更新 MySQL 中的数据，我们可以使用 UPDATE 命令来操作。

update 表名 set column1=value1,column2=value2… [where 条件子句];

例如，更新user_info表中所有记录的avatar字段值。

update game.user_info set avatar = 'https://xxxx';

执行可能会报错，这个意思是现在使用的是安全更新模式，并且尝试更新一个没有使用KEY列的WHERE表。
那我们可以在执行前关闭这个安全更新模式。

set sql_safe_updates = 0;
update game.user_info set avatar = 'https://xxxx';
set sql_safe_updates = 1;

带where子句

带where子句可以约束更新数据的范围。
例如：假如想更新id=123的数据，修改它的avatar和sex字段。

update game.user_info set avatar = 'https://xxxx',sex = 2 where id='123';

使用表达式

如果我们想在字段值得基础上修改值，可以使用表达式来修改某字段的值。
例如：在id=123的数据中，avatar字段的基础上添加后续的相对上传路径。

update game.user_info set avatar = concat(avatar,'/upload/xxx') where id='123';

concat()是字符串拼接函数，这里的+只能做数值相加，不能字符串拼接。

使用子查询

set后面的字段赋值和where子句都可以使用子查询语句。
例如：
通过子查询计算每个 ‘Premium’ 类型客户的总购买金额，并将该值更新到 total_purchases 列中。

UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

查询数据

MySQL 数据库使用 SELECT 语句来查询数据。

select column1,column2,… from 表名 [where condition] [ order by column_name [ASC | DESC ]] [limit limit子句];

参数说明：

• column1, column2, … 是你想要选择的列的名称，如果使用 * 表示选择所有列。
• 表名 是你要从中查询数据的表的名称。
• WHERE condition 是一个可选的子句，用于指定过滤条件，只返回符合条件的行。
• ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC），DESC是降序。
• LIMIT number 是一个可选的子句，用于限制返回的行数。

例如：查看user_info 表的所有数据。

SELECT * FROM game.user_info;

查看特定列数据。

select id, u_name,avatar from game.user_info;

查看id为456的特定列数据。

select id, u_name,avatar from game.user_info where id='456';

查看id降序的数据（id排序没啥用，这里作演示）。

select id, u_name,avatar from game.user_info  order by id DESC;

只查询一条数据：

select id, u_name,avatar from game.user_info  limit 1;

多表查询

涉及到多个表查询时，可以在from后面添加多个表进行查询。

select * from user_info,user_equip,equip;

多表查看指定列时，需要指定该列是属于哪个表。

select 
a.id,a.u_name,b.e_name,b.id as e_id,c.level_num,c.active
from 
user_info as a,user_equip as c,equip as b;

这里将user_info重命名为a,equip重命名为b,user_equip重命名为c,这样比较方便，equip表的id重命名为e_id。

多表查询时，查询结果一般都会有许多冗余记录，这个现象称为笛卡尔积，意思就是多个表中每个表的每一行都会和其他表的其他行进行组合，然后显示查询结果。为了避免这种现象，我们一般都是指定限制条件，来正确查询到我们需要的数据。
可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。

内连接（等值连接）

获取两个表中字段匹配关系的记录。
内连接使用 INNER JOIN 返回两个表中满足连接条件的匹配行，以下是 INNER JOIN 语句的基本语法：

SELECT column1, column2, …
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

参数说明：

• column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table1, table2 是要连接的两个表的名称。
• table1.column_name = table2.column_name 是连接条件，指定了两个表中用于匹配的列。

例如：想user_info和user_equip内连接查找用户的装备情况，然后user_equip和equip连接显示装备信息。

select 
a.id,a.u_name,b.e_name,b.id as e_id,c.level_num,c.active 
from 
user_info as a inner join user_equip as c on a.id = c.u_id ,
user_equip inner join  equip as b on user_equip.e_id = b.id;

两张图片相差数据很大，我们发现user_equip中只有user_id=123且e_id=1的一条数据，通过内连接就将其不符合要求的查询结果过滤掉了。

左连接

获取左表所有记录，即使右表没有对应匹配的记录，左连接使用 LEFT JOIN 关键词。
LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值，以下是 LEFT JOIN 语句的基本语法：

SELECT column1, column2, …
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

参数说明：

• column1, column2, … ——显示列。
• table1 —— 表1,为左表。
• table2 —— 表2，为右表。

例如,user_info和user_equip表左连接。

select
 * 
from 
user_info left join user_equip on user_info.id = user_equip.u_id;

右连接

与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值，以下是 RIGHT JOIN 语句的基本语法：：

SELECT column1, column2, …
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

例如。

select
 * 
from 
user_info right join user_equip on user_info.id = user_equip.u_id
;

UNION 操作符模拟全外连接

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。
全外连接的意思就是左连接和右连接合并的结果。

例如 ：

select
 * 
from 
user_info right join user_equip on user_info.id = user_equip.u_id 
union 
select
 * 
from 
user_info left join user_equip on user_info.id = user_equip.u_id;

结语

就这样，一个简单的数据库就搭建好了。