MySQL

0x01 简介

• 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库
• 数据库管理系统（Database Management System，又称 DBMS）主要用于对数据库的操作与管理，是一种软件，如 MySQL

0x02 安装配置

（1）Mac

• 使用命令 brew install mysql 下载并安装
• 使用命令 mysql.server start 启动 MySQL 服务端
• 使用命令 brew services start mysql 设置开机自启动
• 使用命令 mysql -u [username] -p 并输入密码后，可以在客户端连接服务端
  • 使用 homebrew 安装则默认没有密码

（2）Linux

以 Ubuntu 22.04 为例

• 使用命令 apt install mysql-server 下载并安装 MySQL 服务

• 使用命令 systemctl status mysql 查看服务的状态

• 使用命令 systemctl start mysql 启动 MySQL 服务

• 使用命令 cat /etc/mysql/debian.cnf 查看 MySQL 中默认的用户与密码

• 使用命令 mysql -u [username] -p 连接 MySQL 服务

• 在 MySQL 中使用命令 alter user 'root'@'%' identified with mysql_native_password by '123456'; 修改 root 用户的密码

  • 修改后使用命令 flush privileges; 刷新权限

• 在 MySQL 中使用命令 show variables like '%port%'; 查看远程 MySQL 连接端口

• 在 MySQL 中使用命令 select user, host from user where user='root'; 查看 root 用户的权限

  • 使用命令 update user set host='%' where user='root' 开放 root 用户远程访问权限

• 查看配置文件监听地址

  • 使用命令 vi /etc/mysql/mysql.conf.d/mysqld.cnf
  • 在打开的文件中找到 bind-address 与 mysqlx-bind-address
  • 修改两个变量的值为 0.0.0.0，表示监听所有 IP 地址
  • 使用命令 systemctl restart mysql 重启 MySQL 服务

（3）Windows

• 命令行方法：使用命令 choco install -y mysql
• 安装包方法：在官网下载 MySQL 安装程序并按需安装

（4）Docker

• Docker 中可以使用命令 docker pull mysql 配置 MySQL 服务

0x03 Shell

• MySQL 是一个交互式的 JavaScript、Python 和 SQL 的终端，可以用于执行数据库管理任务，提供了自动语法高亮、语法检查、自动补全和上下文感知的提示
• 在官网进行下载
• 在命令行中使用命令 mysqlsh 启动 MySQL Shell
• Shell 常用指令：
  • \help：帮助提示
  • \connect root@localhost：连接 MySQL 本地服务器
  • \use schema：指定需要使用的数据库
  • 语言切换
    • JavaScript：\js，默认使用该语言
    • Python：\py
    • SQL：\sql
• VS Code 中有官方的 MySQL Shell 插件

0x04 SQL 基础

• 数据库管理系统分为以下两种
  1. 关系型数据库管理系统（RDBMS）
     • 采用了关系模型来组织数据
     • 借助于集合、代数等数学概念和方法来处理数据
     • 通过二维表来展示数据之间的联系
     • 如：MySQL、Oracle 等
  2. 非关系型数据库管理系统（NON-RDBMS）
     • NoSQL（Not Only SQL）
     • 是对 RDBMS 的补充与拓展
     • 如：Redis、mongoDB 等
• 结构化查询语言（Structured Query Language，简称 SQL）是一种用来操作关系型数据库的语言
  • 数据一般以表的形式存储的，而 SQL 可以用来操作表中的数据
  • SQL 语言一般分为以下四种：
    • 数据定义语言，DDL，Data Definition Language
      • 定义数据库对象
      • 常见关键字：CREATE、DROP、ALTER、TRUNCATE
    • 数据操作语言，DML，Data Manipulation Language
      • 对数据库中的记录进行新增、删除、修改等操作
      • 常见关键字：INSERT、DELETE、UPDATE、CALL
    • 数据查询语言，DQL，Data Query Language
      • 查询数据库中的记录
      • 常见关键字：SELECT、WHERE
    • 数据控制语言，DCL，Data Control Language
      • 定义数据库的访问权限与安全级别以及创建用户等
      • 常见关键字：GRANT、REVOKE

0x05 数据库操作

• 查看所有已有数据库

  show databases;

• 创建数据库

  create database datas;

• 删除数据库

  drop database datas;

0x06 表的操作

• 选择数据库

  use datas;

• 创建表并预设字段

  create table customer (
      id int,
      name varchar(100),
  );

  • MySQL 的数据类型大类：数值、日期和时间、字符串、JSON、空间

• 描述表的结构

  desc customer;

• 修改表的结构

  # 修改数据类型
  alter table customer modify column name varchar(50);
  # 修改字段名称
  alter table customer rename column name to cus_name;
  # 设置字段默认值
  alter table customer modify id int default 0;
  # 新增字段
  alter table customer add column create_time datetime;
  # 删除字段
  alter table customer drop column create_time;

• 删除表

  drop table customer;

0x07 数据操作

• 插入数据

  • 单条数据

    insert into customer (id, name) value (1, 'Alex');

  • 多条数据

    insert into customer (id, name) values (2, 'Bob'), (3, 'Charles');

• 更新数据

  • 单条数据

    update customer set id=4 where name='Alex';

  • 多条数据

    update customer set id=0;

• 删除数据

  • 单条数据

    delete from customer where name='Alex';

  • 多条数据

    delete from customer;

0x08 数据导出导入

数据的导入导出操作有利于进行数据的迁移、备份与还原

• 导出：使用命令 mysqldump -u [username] -p [schema name] [table name] > xxx.sql
  • 如：mysqldump -u root -p datas customer > customer.sql
• 导入：使用命令 mysql -u [username] -p [schema name] < xxx.sql
  • 如：mysql -u root -p datas < customer.sql

0x09 数据查询

select 语句基本格式

SELECT {* | <column>} [
 FROM <table 1>, <table 2>…
 [WHERE <expression>
     [GROUP BY <group by definition>
     [HAVING <expression> [{<operator> <expression>}…]
 ]
 [ORDER BY <order by definition>]
 [LIMIT[<offset>,] <row count>]
]

（1）条件 where

• 比较

  • 小于、大于、等于、不等于、小于等于、大于等于

  select * from table1 where id < 10;

• 逻辑

  • 与、或、非（优先级递减）

  select * from table1 where id < 10 OR id > 100;

• 多值

  select * from table1 where id IN (1, 5, 10);

• 范围

  select * from table1 where id BETWEEN 5 AND 10;

• 反向

  select * from table1 where id NOT in (1, 5, 10);
  select * from table1 where id NOT between 5 and 10;

• 模糊

  • %：任意个字符
  • _：任意一个字符

  select * from table1 where name LIKE 'A_';

• 正则

  • .：任意一个字符
  • ^：开头
  • $：结尾
  • [abc]：其中任意字符
  • [a-z]：范围内任意字符
  • a|b：a 或 b

  select * from table1 where name REGEXP '^A.$';

• 空值

  • 字符串空值

    select * from table1 where name = '';

  • 值为 null

    select * from table1 where name IS NULL;
    # 不推荐以下用法
    select * from table1 where name <=> NULL;

（2）排序 order by

• 升序（未说明则默认升序）

  select * from table1 ORDER BY id ASC;

• 降序

  select * from table1 order by id DESC;

• 混合

  select * from table1 order by id desc, name asc;

（3）聚合函数

• 平均值 AVG、记录数 COUNT、最大值 MAX、最小值 MIN、求和 SUM、舍入 ROUND、切片 SUBSTR

# 计数
select COUNT(*) from table1;
# 字符串切片
select SUBSTR(name, 1, 2) from table1;

（4）分组 group by

select sex, count(*) from table1 GROUP BY sex;

（5）筛选 having

常与 group by 搭配使用

select sex, count(*) from table1 group by sex HAVING　count(sex) > 10;

（6）限制 limit

• 前三名

  select * from table1 order by id LIMIT 3;

• 第四名到第六名

  select * from table1 order by id LIMIT 3, 3;

（7）去重 distinct

select DISTINCT sex from table1;

（8）并集 union

合并查询结果集，默认自动去重，使用 union all 取消去重

select * from table1 where id < 10
UNION ALL
select * from table1 where id > 20;

（9）交集 intersect

合并结果集

select * from table1 where id > 10
INTERSECT
select * from table1 where id < 20;

（10）差集 except

select * from table1 where id < 10
EXCEPT
select * from table1 where id > 20;

0x0A 子查询

子查询可以与 select、update、delete 联合使用

• 一般子查询

  select * from table1 where name=(
      select name from table2
  );

• 取别名 as

  select age, (select avg(age) from table2) AS aver from table1;

• 将查询结果提取，创建并导入结果

  create table table3 select * from table1 where age<10;

• 存在性 exists

  select EXISTS(
      select * from table1 where age<10
  );

0x0B 表关联

表关联用于查询多个表的数据，关联的表之间必须有相同名称的字段

• 内连接 inner join：只返回两个表中都有的数据

  select * from table1 INNER JOIN table2 ON table1.id=table2.id;

• 左连接 left join：返回左表中所有和右表中匹配的数据，右表中没有的数据用 NULL 填充

  select * from table1 LEFT join table2 on table1.id=table2.id;

• 右连接 right join：返回右表中所有和左表中匹配的数据，左表中没有的数据用 NULL 填充

  select * from table1 RIGHT join table2 on table1.id=table2.id;

0x0C 索引

索引是一种用来提高查询效率的数据结构，可以快速定位到指定的数据，适用于数据量较大的情况

（1）创建索引

• 基本语法

  create index [unique|fulltext|spatial] index index_name on tbl_name (index_col_name, ...);

  • unique：唯一索引
  • fulltext：全文索引
  • spatial：空间索引

• 创建索引

  1. CREATE INDEX name_index ON table1(name);

  2. alter table table1 ADD INDEX name_index (name);

（2）查看索引

SHOW INDEX FROM table1;

（3）使用索引

select * from table1 where name like 'A%';

（4）删除索引

DROP INDEX name_index ON table1;

0x0D 视图

• 视图是一种虚拟存在的表，本身并不包含数据，而是作为一种查询语句保存在数据字典中
• 视图中的数据是动态的，会随着表中的数据变化而变化

（1）创建视图

CREATE VIEW view_name AS select * from table1 where age<10;

（2）使用视图

select * from view_name;

（3）修改视图

ALTER VIEW view_name AS select * from table2 where age<10;

（4）删除视图

DROP VIEW view_name;

-End-