Mysql 学习笔记

xinhong
174 阅读7分钟

MySQL-Shell的使用

官网：dev.mysql.com/downloads/

作用：是一个更高级的 MySQL 命令行工具，用来连接数据库、执行 SQL、管理集群和做运维操作。

1.命令行中输入mysqlsh命令来启动
mysqlsh

2.查看帮助信息
\help

3.连接数据库
\connect

连接本地数据库
\connect root@localhost
输入上面的命令后，再输入密码，左边显示localhost...表示连接上数据库

4.选择数据库
\use 数据库名称

5.切换语言（js,sql,py）
\sql

SQL基础

  1. 关系型数据库和非关系型数据库的区别

  • 关系型数据库像 Excel 表格，必须按固定格式存数据；

  • 非关系型数据库像一个大杂物柜，什么形状的数据都能往里放。

  1. SQL语句的分类

  • DDL 数据定义语言

  • DML 数据操作语言

  • DQL 数据查询语言

  • DCL 数据控制语言

  1. 常用的数据类型
  • 数值类型
类型用途取值范围常用场景
INT整数（4 字节）-2^31 ~ 2^31-1（约 ±21 亿）存储 ID、数量、年龄等
TINYINT小整数（1 字节）-128 ~ 127存储状态（0/1）、性别等
BIGINT大整数（8 字节）-2^63 ~ 2^63-1存储超大 ID、时间戳等
DECIMAL(M,D)高精度小数M 是总位数，D 是小数位数存储金额、价格（如 DECIMAL (10,2)）
FLOAT/DOUBLE浮点小数精度有限（可能有误差）存储无需高精度的数值（如身高、体重）
  • 字符串类型
类型用途长度限制常用场景
VARCHAR(n)可变长度字符串n 最大 65535（实际受编码影响）存储姓名、手机号、地址等（长度不固定）
CHAR(n)固定长度字符串n 最大 255存储固定长度内容（如手机号、邮编）
TEXT长文本最大 65535 字符存储文章内容、备注等
LONGTEXT超长文本最大 4GB 字符存储大段文本、日志等
  • 日期类型
类型用途格式常用场景
DATE日期'YYYY-MM-DD'存储生日、下单日期等
TIME时间'HH:MM:SS'存储打卡时间、时长等
DATETIME日期 + 时间'YYYY-MM-DD HH:MM:SS'存储创建时间、更新时间（范围：1000-9999 年）
TIMESTAMP日期 + 时间（时间戳）'YYYY-MM-DD HH:MM:SS'存储记录修改时间（范围：1970-2038 年，支持自动更新）
YEAR年份YYYY存储年份（如出生年份）

创建数据库

1.连接数据库
mysql -u root -p

2.参看当前数据库
show databases;

3.创建一个数据库
create database xx;

4.删除一个数据库
drop database xx;

创建和管理表

1.使用数据库
use 数据库名;

2.创建数据库表
create table xx(
列名 类型，
列名 类型
)

3.查看表的结构
decs 表名；

4.修改表的某个列的数据类型
alter table 表名 modify column 列名 类型

5.修改表的某个列的名称
alter table 表名 modify column 列名 to 新列名

6.表添加一个新的列
alter table 表名 add column 列名

7.表删除某个列
alter table 表名 drop column 列名

8.删除某个表
drop table 表名

数据的增删改查

1.插入数据
insert into 表名(列名,列名) values(x,x);

插入多条数据
insert into 表名（列名，列名）values（x，x），（x，x）；

2.查找所有数据
select * from player

3.修改某行数据
update player set 列名 = 值 where 列名 = 值

4.修改所有数据某一列数据
update player set 列名 = 值

5.删除表中的数据
delete from 表名 where 列名 = 值

数据的导入导出

在系统终端执行，到sql所在的目录

1.使用命令导入
mysql -u root -p 数据库名 < sql文件

2.使用命令导出
mysql -u root -p 数据库名 表名 > sql文件

在系统的终端导入数据比在idea使用sql要快非常多

常用语句

1.where子句用来提取满足指标准的记录，可以和select,update,delete一起使用
select * from 表名 where 条件

2.and查询多个条件的数据
select * from 表名 where 条件1 and 条件2

3.优先级顺序 NOT>AND>OR
合并两种查询结果,and的优先级更高，所以执行的两个and
select * from 表名 where 条件1 and 条件2 OR 条件1 and 条件2

4.in指定多个值
select * from 表名 where 列名 in （x,x,x）

5.between...and...指定连续的范围
select * from 表名 where between x and x

6.not 表示取反，可以加在任何一个条件语句前面
表示不在这个范围区间
select * from 表名 where not between x and x

7.like模糊查询,通配符%表示任意字符，_表示任意一个字符
查找名字开头为w的数据
select * from 表名 where name like 'w%'

8.regexp正则表达式
常用通配符：
.:任意一个字符 
^:开头
$:结尾
[abc]：其中任意一个字符
[a-z]：范围内的任意一个字符
A｜B：A或B

查找名字有两字符，并且开头为w的
select * from 表名 where name regexp '^w.$'

9.查询没有值的数据，需要用is null来判断
select * from 表名 where 列名 is null;

10.排序 order by(升序)，降序排列的话在后面加desc
select * from 表名 order by 列名;

11.聚合函数
AVG（）   返回集合的平均值
COUNT（） 返回集合中的项目数
MAX（）   返回最大值
MIN（）   返回最小值
SUM（）   求和
SUBSTR(列名，起始位置，截取的长度)  截取某段数据

select count(*) from 表名

12.分组group by
按照某个列进行分组
select 列名 ,count(列名) from 表名 group by 列名

13.having筛选分组后的数据
筛选某个列分组后大于某个值的数据
select 列名 ,count(列名) from 表名 group by 列名 having count(列名) > 某个值

14.limit 限制数量，限制取多少条数据
limit x offset x 从x个数据开始限制，限制的条数为

15.distinct 去重重复的记录
select distinct 列名 from player

16.union 合并查询结果集（并集）
去除重复的数据
select * from 表名 where 列名 between x and x
union
select * from 表名 where 列名 between x and x

17.intersect 查找两个结果的交集

18.except 合并结果集（差集）

子查询

使用一个查询的结果作为另一个查询的条件

1.select AVG(列名) from 表名
2.select * from 表名 where 列名 >(select AVG(列名) from 表名)

3.标量子查询，查询的为空就显示null
SELECT
    -- 核心：标量子查询（返回单个值）作为查询结果的一列
    (SELECT DISTINCT Salary 
     FROM Employee 
     ORDER BY Salary DESC 
     LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

表关联

用来查询多个表中的数据，一般会使用表的主键和外键来关联

1.inner join 内连接，只返回两个表中都有的数据
select * from 表名
inner join 要关联的表名
on 表名.列名 = 表名.列名

2.left join左连接，返回左表中所有的数据和右表中匹配的数据，右表中没有的数据用null填充

索引

1.create [要创建索引的类型] index 创建索引的名称 on 表名 （要创建索引的列） 
create [unique] index 索引的名称
on 表名 （列名，列名）

2.drop index删除索引
drop index 索引名称 on 表名

ssh连接数据库

🏢 场景设定

假设你在家里，想连接公司的内网数据库，但公司数据库不开放外网访问。

公司网络结构：

text

家里电脑 → [公司跳板机] → 内网数据库服务器

(可外网访问) (仅内网访问)

10.0.0.100 192.168.1.50

🔧 配置步骤分解

第一步：获取信息

问公司运维得到：

  1. 跳板机（SSH服务器）信息：

    1. 主机：company-jump.com 或 10.0.0.100
    2. 端口：2222（公司改了默认端口）
    3. 用户名：zhangsan
    4. 密码：Jump@123456

  2. 数据库信息：

    1. 数据库类型：MySQL
    2. 内网地址：192.168.1.50
    3. 端口：3306
    4. 用户名：dev_user
    5. 密码：Db@2024
    6. 数据库名：project_db

第二步：Navicat 填写

  1. SSH 标签页（填跳板机信息）

text

使用 SSH 隧道：✅ 勾选

主机：company-jump.com

端口：2222

用户名：zhangsan

验证方法：密码

密码：Jump@123456

保存密码：✅ 勾选

👉 这就是你截图中要填的部分

  1. 常规标签页（填数据库信息）

text

连接名：公司项目数据库

主机：localhost ← 关键！因为通过隧道后，数据库在跳板机“本地”

端口：3306

用户名：dev_user ← 数据库账号，不是跳板机账号！

密码：Db@2024 ← 数据库密码，不是跳板机密码！

数据库：project_db

avatar
文章
阅读
粉丝