Mysql
本文最后更新于70 天前,其中的信息可能已经过时,如有错误请发送邮件到big_fw@foxmail.com

MySQL速成

MySQL-Shell的使用

启动后默认进入\JS模式,更适合数据库管理员、开发人员用于自动化运维、集群管理、数据迁移等高级任务

支持JS、Python、SQL三种语言

  • 默认使用JS
  • 使用\+语言缩写来切换语言,从而执行该语言的代码
    • \js
    • \py
    • \sql 切换成功即可执行sql语句,与在终端或Navicat中执行效果一致

官方提供的VSCode的MySQL-Shell插件:MySQL Shell for VS Code,用ctrl+enter运行

以下是一些简单命令:

查询信息 \help

连接到MySQL数据库 \connect

连接本地MySQL服务器 \connect root@localhost

创建数据库 create database 库名;

选择数据库 \use 库名

SQL基础

数据库管理系统分类

关系型数据库管理系统ROBMS

  • 采用关系模型组织数据,通过二维表表示数据之间的联系
  • 数据一般都是用表的形式来存储,SQL用来操作表中数据
  • 二维表每一行表示一条数据记录
  • 二维表每一列表示一个字段
  • 不同表通过关联字段来建立联系
  • 常见代表:MySQL、ORACLE、PostgreSQL

非关系型数据库管理系统NON-ROBMS

  • 针对数据量大、数据结构复杂、数据类型多样的数据对ROBMS进行补充和扩展而来
  • 常见代表:Redis、mongoDB、neo4j、cassandra、CouchDB

SQL分类

语句不区分大小写,但为了提高可读性,会把关键字大写,表名列名其他名称小写

通常把 SQL 分为三类:

  • DDL (Data Definition Language): 数据定义语言,如 CREATE、DROP、ALTER、TRUNCATE
  • DML (Data Manipulation Language): 数据操作语言,如 INSERT、UPDATE、DELETE、CALL
    • 细分还可以分出一类 DQL (Data Query Language): 数据查询语言,如 SELECT
  • DCL (Data Contral Language): 数据查询语言,如 GRANT、REVOKE

MySQL数据类型

  • 数值类型
    • 整数类型:TINYINT、SMALINT、INT、BIGINT(范围小→大)
    • 浮点数类型:FLOAT、DOUBLE、DECIMAL(a,b) 长度为a,保留b位小数
  • 日期和时间类型
    • DATE:仅存储日期,格式为YYYY-MM-DD
    • TIME:仅存储时间,格式为HH:MM:SS
    • DATETIME:存储完整的日期和时间
    • TIMESTAMP:存储时间戳
  • 字符串类型
    • 定长字符串:CHAR(n) 实际占用字节只取决于n,无论是否存满都一定为n
    • 变长字符串:VARCHAR(n) 实际占用字节取决于字符,最大为n
    • TEXT/BLOB: 用于存储大段文本或二进制数据(如文章、图片),不建议用于频繁查询的字段
  • JSON类型
    • JSON:原生支持JSOM格式数据存储,适合灵活结构数据,如用户配置、日志信息
  • 空间类型
    • POINT(点)
    • LINESTRING(线)
    • POLYGON(多边形)
    • GEOMETRY:通用基础类型
  • 集合类型
    • SET:如SET(’阅读‘,’运动’,’音乐’),适合多选标签

SQL常用命令

在MySQL命令行客户端或者GUI工具中执行命令,命令结尾记得带分号

命令行客户端:

  • MySQL 8.0 Command Line Client(仅支持SQL)
  • MySQL Shell(支持sql、js、py,默认js,使用前记得执行\sql切换)

查看当前存在的数据库

 show databases;

创建数据库

 create 数据库名字;

删除数据库

 drop database 数据库名字;

选择数据库

 //创建表之前需要选择数据库,再在数据库内创建表
 use game;

创建表

 create table 表名(字段名称 数据类型,字段名称 数据类型)
 ​
 create table player(
  id INT,
    name VARCHAR(100),
     level INT,
    exp INT,
    gold DECIMAL(10,2)
 );

查看表的结构

 DESC 表名;
 -- 会显示每列名称、数据类型、是否允许为空、默认值等等
image-20260713212548410

修改表结构

 -- 修改列类型长度  
 -- 关键字     表名     关键字     列名 新的数据类型
 ALTER TABLE player MODIFY COLUMN name VARCHAR(200);-- 后面再跟上数据表示初始值为该数据
 ​
 -- 修改字段名
 -- 关键字     表名     关键字     列名 关键字 新列名
 ALTER TABLE player RENAME COLUMN name to nick_name;
 ​
 -- 增加新字段(列)
 -- 关键字     表名     关键字   新增字段名 数据类型
 ALTER TABLE player ADD COLUMN last_login DATETIME;
 ​
 -- 删某列
 -- 关键字     表名     关键字     待删字段
 ALTER TABLE player DROP COLUMN last_login;

删除表

 -- 关键字     表名
 DROP TABLE player

表中数据的增删改查


增加表中数据

INSERT INTO player(id,name,level,exp,gold) VALUES (1,'张三',1,1,1);
INSERT INTO player(id,name,level,exp,gold) VALUES (1,'张三',1,1,1),(2,'李四',2,2,2);

如果表名后面的列名和表结构完全一致 (顺序也一致),就可以省略,直接写关键字 VALUE+ 值,注意可以同时插入多条数据,逗号分隔即可。如下

INSERT INTO player VALUES (1,'张三',1,1,1);

也可以只写出部分列名称,相应的后面的值也只写部分,这样其他值就会使用创建表时设定的默认值来填充,不设置就是空值。如下

INSERT INTO player(name,gold) VALUES ('张三',1);

查看表中数据

SELECT * FROM player;
SELECT id,name FROM player;

注意 * 代表所有列,如果不想要所有列,就逐一枚举,逗号分隔。


表中数据默认值

ALTER TABLE player MODIFY level INT DEFAULT 1;

这里添加默认值之后就发现省略输入默认值不再为 Null。


修改表中数据

-- 关键字 表名 关键字 列名   改值 关键字 列名     人名
UPDATE player set level = 1 where name = '李四';

修改所有玩家的数据,多个字段用逗号隔开,如下

UPDATE player set exp=0,gold=0;

删除表中数据

DELETE FROM player WHERE gold = 0;

数据的导入导出

常用语句

where子句

用来提取满足指定标准的记录

关键字为where,后接需要筛选的字段

-- 1. 查询等级大于 1 的所有玩家信息
SELECT * FROM player WHERE level > 1;

-- 2. 多条件筛选 (AND):查询等级在 1 到 5 之间的玩家
SELECT * FROM player WHERE level > 1 AND level < 5;

-- 3. 逻辑组合 (AND/OR/括号):
-- 同时满足以下三个条件:
-- a) 等级必须大于 1
-- b) 经验值必须小于 5
-- c) 并且(等级小于 5 或者 经验值大于 1)—— 只要括号内任一条件成立即可
SELECT * FROM player WHERE level > 1 AND (level < 5 OR exp > 1) AND exp < 5;

-- 4. 多值查询 (IN):查询等级恰好为 1、3 或 5 的玩家(等同于 level=1 OR level=3 OR level=5)
SELECT * FROM player WHERE level IN (1,3,5);

-- 5.区间查询(between...and...):查询1到10之间的数据
SELECT *FROM player WHERE level BETWEEN 1 AND 10;

-- 6.取反(NOT):加在各种条件语句或比较运算符前面
SELECT *FROM player WHERE level NOT BETWEEN 1 AND 10;

-- 7.模糊查询(LIKE):后面加上用来匹配的模式,使用以下二种通配符
-- % 零个或多个任意字符
-- _ 一个任意字符
-- 查找姓王的玩家
SELECT *FROM player WHERE name LIKE '王%'
-- 查找名字包含王的玩家
SELECT *FROM player WHERE name LIKE '%王%'
-- 查找姓王的二字玩家
SELECT *FROM player WHERE name LIKE '王_'

-- 8.正则表达式(REGEXP/regexp)
-- Tips: 常用通配符
-- . : 匹配任意一个字符
-- ^ : 匹配字符串的开头
-- $ : 匹配字符串的结尾
-- [abc] : 匹配方括号内列出的任意一个字符 (例如 a, b 或 c)
-- [a-z] : 匹配指定范围内的任意一个字符 (例如小写字母 a 到 z或者数字1到9)
-- A|B : 匹配 A 或者 B (逻辑“或”)
-- 查找姓王的玩家
SELECT *FROM player WHERE name REGEXP '^王';
-- 查找名字以王结尾的玩家
SELECT *FROM player WHERE name REGEXP '王$';
-- 查找姓王的二字玩家
SELECT *FROM player WHERE name REGEXP '^王.$';
-- 查找名字包含王的玩家
SELECT *FROM player WHERE name REGEXP '王';
-- 查找名字包含王或者张的玩家
SELECT *FROM player WHERE name REGEXP '[王张]';
SELECT *FROM player WHERE name REGEXP '王|张';
-- 练习
-- 查找邮件地址以 "zhangsan" 开头的玩家
SELECT * FROM player WHERE email REGEXP '^zhangsan';
SELECT * FROM player WHERE email LIKE 'zhangsan%';
-- 查找邮件地址以 a、b 或 c 开头的玩家
SELECT * FROM player WHERE email REGEXP '^[abc]';
SELECT * FROM player WHERE email REGEXP '^[a-c]';
-- 查找邮件地址以 "net" 结尾的玩家
SELECT * FROM player WHERE email REGEXP 'net$';
SELECT * FROM player WHERE email LIKE '%net';

-- 9.查找空值/非空值(is NULL/is NOT NULL)
-- 或者使用NULL比较操作符 <=>NULL
-- 查找邮箱为空的玩家
SELECT * FROM player WHERE email is NULL;
SELECT * FROM player WHERE email <=> NULL;
-- 空字符串和NULL不一样,前者用=来判断,如下
SELECT * FROM player WHERE email = '';

逻辑运算符优先级顺序:NOT>AND>OR

排序ORDER BY

排序:关键字order by,对查询结果进行排序

-- 1.默认升序
SELECT * FROM player ORDER BY level;
-- 2.字段后加DESC则降序
SELECT * FROM player ORDER BY level DESC;
-- 3.多条件排序 按等级降序的前提下再按照经验值升序,默认或者字段后加ASC表示升序
SELECT * FROM player ORDER BY level DESC,exp;
SELECT * FROM player ORDER BY level DESC,exp ASC;
-- 4.使用列的序号排序 level字段在第五列时
SELECT * FROM player ORDER BY 5 DESC,exp;

聚合函数

用来对某列进行一些计算

-- Tips: 常用聚合函数
-- 1.AVG() : 返回集合的平均值
-- 返回所有玩家的等级平均值
SELECT AVG(level) FROM player;

-- 2.COUNT() : 返回集合中的项目数(记录行数)
-- 求所有玩家总人数
SELECT COUNT(*) FROM player;

-- 3.MAX() : 返回最大值
SELECT MAX(level) FROM player;
-- 4.MIN() : 返回最小值
SELECT MIN(level) FROM player;
-- 5.SUM() : 求和
SELECT SUM(exp) FROM player;

分组group by

用于将查询结果按一个或多个列进行分组的子句,常与聚合函数配合使用,实现对每组数– 据的统计汇总。

-- 分组前
SELECT COUNT(*) FROM player;
-- 分组后:按 level(等级)把玩家分成不同的组,然后数一数每组里有多少人。
SELECT level, COUNT(*) FROM player GROUP BY level;
SELECT level, COUNT(level) FROM player GROUP BY level;

count()和count(level)区别:后者不计算level的null数 count() 不关注任何列的具体值,包括 NULL count(level) 会忽略 level为 NULL 的行

分组后筛选having

-- 按 level(等级)把玩家分成不同的组,然后数等级大于4的每组里有多少人。
SELECT level, COUNT(level) FROM player GROUP BY level HAVING count(level)>4;
-- 按 level(等级)把玩家分成不同的组,然后数等级大于4的每组里有多少人并降序
SELECT level, COUNT(level) FROM player GROUP BY level HAVING count(level)>4 ORDER BY count(level) DESC;

子查询

有的时候我们会使用另一个查询结果来作为当前查询的条件,这个时候就要用到子查询。

 SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player);
 SELECT level, ROUND((SELECT AVG(level) FROM player)) AS average,
 level - ROUND((SELECT AVG(level) FROM player)) AS diff
 FROM player;

给列取别名,这样查询结果就会显示为你自定义的别名。

当然也可以用来创建新表,比如我们可以把原表中的某些玩家数据用来创建新表

 CREATE TABLE new_player SELECT * FROM player WHERE level < 5;

那么如何使用子查询插入数据呢?

 INSERT INTO new_player SELECT * FROM player WHERE level BETWEEN 6 AND 10;

EXISTS

用来判断查询结果是否存在,返回 0/1

 SELECT EXISTS(SELECT * FROM player WHERE level > 100);

表关联

表关联用来查询多个表中的数据,关联的表之间必须有相同的字段,一般会使用表的主键和外键来关联。主要分为:INNER JOIN、LEFT JOIN、RIGHT JOIN。

这里只展示两个表的关联。

内连接

返回两表都有的数据,不存在的字段用 null 填充。

这里假设存在一个 equip 表,存在 id, name, player_id 三个字段,这样我们可以使用玩家 id 这个字段来关联两个表。

 SELECT * FROM player
 INNER JOIN equip
 ON player.id = equip.player_id;

内连接还可以用 WHERE 来指定用于关联的字段。

 SELECT * FROM player, equip
 WHERE player.id = equip.player_id;

也可以有别名:

 SELECT * FROM player p, equip e
 WHERE p.id = e.player_id;

左连接

返回左表中所有的数据和右表中匹配的数据,左表中不存在的字段用 NULL 填充。

 SELECT * FROM player
 LEFT JOIN equip
 ON player.id = equip.player_id;

右连接

和左连接类似,不多说

 SELECT * FROM player
 RIGHT JOIN equip
 ON player.id = equip.player_id;

笛卡儿积

笛卡儿积是指两个集合中所有可能的元素有序组成的新集合,如果把表连接的条件子句去除,就会输出笛卡儿积。

 SELECT * FROM player, equip;

其实从这里也能看出,表连接的本质是笛卡儿积加条件过滤,我们把条件过滤去掉就是笛卡儿积了。

索引

索引是一种用来提高查询效率的数据结构,它可以帮助我们快速定位到我们想要的数据,如果没用索引,就只能从头开始遍历所有的数据,直到找到满足条件的数据为止,当数据非常少的时候,这样没什么问题,但是当数据量极大,查询耗时就会非常恐怖。

 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 ON tbl_name (index_col_name, ...)
 ALTER TABLE tbl_name ADD INDEX index_name (col_name);

以上是创建索引的两种语法格式,注意方括号内是可选参数,索引分三类:唯一索引、全文索引、空间索引,我们通常对主键或者经常用来查询的字段创建索引,也就是 where 后面放的字段,这样可以提高查询效率。

 DROP INDEX index_name ON tbl_name;

以上是删除索引的语句格式。

视图

视图是一种虚拟的表,它本身并不包含数据,而是作为一个查询语句,保存至数据字典中,当我们查询视图的时候,它会根据视图定义来动态生成数据。

 CREATE VIEW view_name
 AS
 [查询语句];

以上是格式,以下展示具体如何使用:

 CREATE VIEW top10
 AS
 SELECT * FROM player ORDER BY level DESC LIMIT 10;
 ​
 SELECT * FROM top10;

修改视图的语法如下:

 ALTER VIEW view_name
 AS
 [新查询];

删除语法如下:

 DROP VIEW view_name;
文末附加内容
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇