文章目录
前言
最近想搞一下用户排名显示的业务,发现mysql的操作忘得差不多的,趁着现在实现用户排名需求,记录下mysql的学习之旅。
本篇 默认已经安装好mysql,且已经初始化mysql,并使用mysql账号登录了。若没有,请参考其他博文安装好mysql。
mysql不区分大小写,建议关键字用大写(不要学我,我懒我喜欢用小写),取得数据库名、表名和字段中得单词用下划线连接。
基础知识
数据库
数据库是存放数据的仓库。Mysql是关系型数据库,存储的格式可以直观地反映实体间的关系。关系型数据库和常见的表格比较相似,关系型数据库中表与表之间是有很多复杂的关联关系的。
数据表
数据表就是存放一组具有一定规则组成的二维数据的表格,它存放到数据库中。数据表由表名、数据表结构和符合数据表结构规范的数据(记录)组成。
数据表结构
数据表结构就是多个字段和约束组成的数据结构。一个字段由字段名、数据类型、字段约束组成。MySQL一个表最多可以有4096个字段。这个限制是由数据库管理系统的实现决定的,与具体的MySQL版本和配置有关。在MySQL 8.0版本中,默认情况下,一个表可以添加的字段数量最多为4096个。
记录(数据)
记录就是数据表中的数据。一条记录就是表中的一行数据。
数据类型
mysql支持多种数据类型,主要有数值类型、字符串类型、日期类型、二进制类型、空间数据类型和特定数据格式的类型(例如json)等。
数值类型
类型名称大小描述TINYINT1个字节很小的整数,如果你的数据不过百,可以用这个SMALLINT2个字节小的整数MEDIUMINT3个字节稍小的整数INT4个字节整数BIGINT8个字节大的整数FLOAT4个字节单精度浮点数DOUBLE8 个字节双精度浮点数
字符串类型
类型名称大小描述CHAR(M)M个字节,M:[1,255]固定长度字符串VARCHAR(M)L+1字节,在此L<=M和1<=M<=255变长字符串,具体存储大小取决于实际的数据大小,性能稍慢TINYTEXTL+1字节,在此L<2^8小文本字符串TEXTL+1字节,在此L<2^8文本字符串 ,适用于存储文章内容、用户评论等较长的文本信息MEDIUMTEXTL+3字节,在此L<2^24大型文本字符串 ,适合存放更大一些的文本字符串,如大型文档、代码等LONGTEXTL+4字节,在此L<2^32超大型文本字符串, 适合存储大型日志文件等ENUM1或2个字节,取决于枚举值数目(最大值65535)枚举类型SET1,2,3,4或8个字节,取决于集合成员的数量(最多64个成员)一个设置,字符串对象可以有0个或多个SET成员
日期类型
类型名称格式范围描述YEARYYYY19012155年数,1个字节TIMEHH:MM:SS-838:59:59838:59:59时分秒, 3个字节DATEYYYY-MM-DD1000-01-019999-12-3年月日,3个字节DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:009999-12-31 23:59:59年月日时分秒,8字节TIMESTAMPYYYY-MM-DD HH:MM:SS1970-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非常小的BLOBBLOB(M)L+2字节,在此L<2^16blob类型的二进制字符串MEDIUMBLOBL+3字节,在此L<2^24稍大的 blobLONGBLOB(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。包含BETWEENBETWEEN 运算符确定一个值是否介于某两个值之间。一般和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
GROUPBY customer_id;
数据库操作
数据库操作主要分为新建、删除和使用,例如:
-- 查看game数据库是否存在,存在就删除dropdatabaseifexists game;-- 新建game数据库createdatabase 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数据库:
createdatabase game;
删除数据库
删除数据库使用drop关键词进行删除,删除后该数据库和数据表都将消失,需注意删除操作不可逆,删除前建议备份数据。
DROP DATABASE <database_name>; – 直接删除数据库,不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;
删除game数据库
dropdatabaseifexists 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、用户名、用户头像、用户性别、用户创建时间、用户状态:
-- 用户表 droptableifexists user_info;CREATETABLE user_info (
id VARCHAR(30)PRIMARYKEY,
u_name VARCHAR(20)NOTNULL,
avatar VARCHAR(100),
sex INTDEFAULT1,
active BOOLEANDEFAULTTRUE,
create_time DATE);
- id —— 用户id ,变长字符串类型,为主键。
- u_name —— 用户名,变长字符串,不为空。
- avatat —— 用户头像,变长字符串(这里我想存图像uri地址,所以用的字符串)。
- sex —— 用户性别, 整形,1为男,2为女,默认为1。(你可以使用enum枚举类型)。
- active —— 用户状态,布尔型,true为正常,false为异常,默认为true。
- create_time —— 创建日期,日期类型。
创建游戏分数排行表。
分析:游戏分数要包括这个用户的信息和用户装备信息,以及游戏分数,所以表字段为:表id、用户id、用户装备id、游戏分数。
createtable user_score (
id intprimarykeyauto_increment,
u_id varchar(30),
e_id varchar(30),
score intdefault0,
create_time timestampdefaultcurrent_timestamp,constraint scoreUserKey foreignkey(u_id)references user_info(id),constraint scoreEquipKey foreignkey(e_id)references user_equip(id));
- id —— 用户游戏分数id,整形,主键,且自增。
- u_id —— 用户id,与用户信息表形成外键关联。
- e_id —— 用户的装备信息id,与用户装备信息表形成外键关联。
- score —— 用户分数,整形,默认0.
另外还有用户的装备信息表,这个可以简化称用户使用默认武器,然后指显示武器名字即可,这里不做设计了。
修改数据表
数据表在新增后,若不满足后续需求可以更改数据表结构,修改表结构语法:
alter table 数据表名 [修改表名 | 修改表字段 | 添加、删除表字段];
修改数据表结构,根据想要修改的表结构内容有多种语法。
修改数据表名
修改某个数据表名,可以使用下面语法
alter table 旧表名 rename to 新表名;
altertable user_info renameto user_base;
数据表中添加新字段
想要往某个数据表添加一个新字段,可以使用语法:
alter table 表名 add [字段名 类型 约束] [first | after] [已存在字段名];
altertable user_info add address varchar(50);
修改数据表某个字段
修改数据表中某个字段,可以使用以下语法:
alter table 表名 modify [字段名 类型 约束] [first | after] [已存在字段名];
altertable user_info modify address varchar(100)default''after sex ;
删除数据表某个字段
删除数据表字段,可以使用以下语法:
alter table 表名 drop 字段名;
altertable user_info drop address;
查看数据表
查看数据表有两种方式,
查看表结构语句 :
show create table 数据表名
查看表字段列表
describe 数据表名;
use game;showcreatetable user_score;describe user_score;
删除数据表
要想删除某个数据表,使用语法:
drop table 数据表名
droptable 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的值。
例如 :
insertinto user_info (id,u_name,create_time)values('123',"张三", curdate());insertinto user_info (id,u_name,create_time)values('1234',"张三2", curdate()),('12345',"张三3", curdate());
注意事项:
- 如果该列为自增列,添加时该列不需要赋值。
- 如果该列什么都没有约束,那么添加时若该列没赋值,则默认为NULL,如果有默认值,则为默认值;若有赋值,则为赋的值。
- 如果该列设为主键,则该列值不能与表中的其他记录的主键值重复。
- 如果该列设为外键,则该列值需要为外键关联表的某条记录主键值。
- 如果该列设为必填,则插入数据时必需为该列赋值。
数据插入失败会有相应提示,根据提示调整插入语句即可。
删除数据
使用 DELETE FROM 命令来删除 MySQL 数据表中的记录。
delete from 表名 [where 条件子句];
例如 : 删除user_info表所有数据(记录)。
deletefrom user_info;
删除某一项或多项数据的话,可以添加where子句来限制删除数据的范围。
例如:删除user_info表中id=123的数据。
deletefrom user_info id='123';
执行后发现报错,报错描述是因为该表的id有作为外键和其他表相关联,正常情况下应该先删除关联表的相关数据,然后再删除指定行数据。这里由于相关表没有数据,所以我们先关闭外键约束检查,然后直接删除该表的指定行数据,即可执行成功,最后开启外键约束检查。
-- 关闭约束检查set foreign_key_checks =0;deletefrom 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 =2where 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 =(SELECTSUM(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 orderby id DESC;
只查询一条数据:
select id, u_name,avatar from game.user_info limit1;
多表查询
涉及到多个表查询时,可以在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 innerjoin user_equip as c on a.id = c.u_id ,
user_equip innerjoin 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 leftjoin 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 rightjoin user_equip on user_info.id = user_equip.u_id
;
UNION 操作符模拟全外连接
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合,并去除重复的行。
全外连接的意思就是左连接和右连接合并的结果。
例如 :
select*from
user_info rightjoin user_equip on user_info.id = user_equip.u_id
unionselect*from
user_info leftjoin user_equip on user_info.id = user_equip.u_id;
结语
就这样,一个简单的数据库就搭建好了。
版权归原作者 ZSK6 所有, 如有侵权,请联系我们删除。