SQL & MySQL 用法速通

概述

什么是数据库 ？数据库有啥用 ？

假设我们要做一个购物网站

如果我们将「用户信息、商品信息、…」这些重要数据放到内存中，那么服务器断电或重启都会导致数据的全部丢失

所以，我们需要将这些数据放到数据库中，达到数据持久化保存的目的

表

关系型数据库使用「表」来存储数据

常见的关系型数据库管理系统

1. MySQL：开源免费跨平台、稳定高性能
2. Oracle：也挺适合大型系统，比如「银行系统、医院系统、…」
3. SQL Server：常见于 c# 和 .net 体系，适合在 Windows Server 中使用

MySQL

一些高级知识和技巧本文不作介绍，如「一些常用函数、给表设置约束、表之间的关系、多表查询、子查询」

实验环境

ubuntu-20.04-lts、mysql8

# intallation
sudo apt update
sudo apt upgrade
sudo apt install mysql-server
mysql --version

sudo mysql_secure_installation # MySQL 的安全性配置
systemctl status mysql.service # 查验 MySQL 的运行状态

如何接入 MySQL

• 方法一

mysql -u root

OR

mysql -u root -p # passwd is needed.

• 方法二：使用图形化客户端，eg：dbeaver

SQL

结构化查询语言，用于操作关系型数据库

分类

1. 数据定义语言（Data Definition Language），用于操作数据库对象「库、表、列…」

   • create、drop、alter

2. 数据操作语言（Data Manipulation Language），用于操作数据库中的数据

   • insert、delete、update

3. 数据查询语言（Data Query Language）

   • select

注释

-- 单行注释

/*
    多行
    注释
*/

数据类型

int     -- 整型

double  -- 浮点型，double(6,3) => 最长长度是 6 位，小数点后 3 位，999.999

char    -- 固定长度字符串类型，char(10) => 不足 10 位的话会补足到 10 位，比如身份证号

varchar -- 可变长度字符串类型，char(10) => 不足 10 位不会补足，性能低于 char，比如个性签名

text    -- 字符串，适用于大文本内容，例如文章内容

date    -- 日期类型，yyyy-MM-dd

常用命令

-- 显示所有数据库
show databases;

-- 使用某数据库
use dbname;

-- 创建一个数据库
create database dbname;

-- 删除某数据库
drop database dbname;

-- 将某数据库的编解码方式设置为 utf8 以便支持中文字符
alter database dbname character set utf8

# 查看当前数据库中的所有表
show tables;

# 创建表
create table tbname(id int,name varchar(20),sex char(1));

# 查看某个表的格式
desc tbname;

# 查看某个表中的数据
select * from tbname;

# 向某表中新增一条数据
insert into tbname (id,name,sex) values (1,'小明','0');
# 注意，如果一条数据的每一个字段都要修改，则 tbname 后 values 前的内容（列名）可以省略

# 修改数据
update tbname set name='小王', sex='1' where id=1;

# 删除数据
delete from tbname where id=2;

# 删除表
drop table tbname;

# 修改表：改变字段名称
alter table tbname change name username varchar(20);

# 修改表：删除字段
alter table tbname drop sex;

# 修改表：添加字段
alter table tbname add achievement double(5,2);

练习

SELECT
    team_id, SUM(achievement)
FROM
    students
WHERE
    sex = 0
GROUP BY
    team_id
HAVING
    SUM(achievement) > 100
ORDER BY
    SUM(achievement) DESC
LIMIT 2;

规范

基础规范

1. 大部分场景适合 Innodb，部分日志写入场景适合 tokudb 或 myrockets 引擎
2. 禁止大文件大照片直接存储在 Mysql 下（考虑下 mosaic）
3. 由于 emoji 表情等使用场景的普及，大部分存储需求可能需要使用 utf8mb4 来进行存储，utf8mb4 是 utf8 的超集
4. 高并发的互联网使用场景下，架构设计原则应该是 “解放 DB 的 CPU，将计算上移到服务层”，防止高并发的场景下，这些功能拖死数据库
5. 表名使用小写，表名中尽量不包含特殊字符或 / 符号等
6. 为方便业务开发以及后期维护，应该简单明了地标记用途
7. 数据表字段最好也有中文注释

SQL 规范

1. 避免负向查询，主要指 not in，<>，not like 等，因为如果查询条件中只有这类条件，会导致全表扫描
2. % 开头的模糊查询，会导致查询无法使用索引来定位数据，会导致全表扫描
3. 如果只 select 索引字段，或者只 select 索引字段和主键，会全扫描索引树找到相应字段，但这不是利用索引来定位数据，因为要查询的字段就在索引树上
4. 模糊查询利用索引定位数据的解决方案：反转模糊查询的字段，但是对于 “%keyword%” 的索引，此方法无效

select * from student where name like '%三';

-- 我们可以改造为

select * from student where reverse(name) like reverse('%三');

5. 如下

select id from xxx where from_unixtime(start_day)>='2020-10-08';

-- 这会导致全表扫描

-- 正确写法是

select id from xxx where start_day>=unix_timestamp('2018-08-14');

6. 一般情况下，手机号推荐使用 varchar(32) 存储，因为会有地区等属性；如果 phone 是 varchar，则如下写法会触发隐式转换，导致全表扫描

select id from xxx where phone = 15635638290;

（隐式转换的情况下，全表扫描一般是发生在字符类型字段，数字类型一般不会影响执行计划）

7. 避免使用 select *

   • select * 会获取不必要的列，这会增加 cpu，io，net 的消耗
   • 在有索引覆盖的情况下，会导致无法有效利用覆盖索引
   • 程序开发过程中容易因为忘记添加或删除字段，导致出现 bug

8. 分片库的场景下，查询不带分片键容易引起查询放大的问题，会导致所有分片做无效查询

9. 不允许在主库上执行统计 SQL（count/sum）

10. 不允许大表使用 join，子查询

11. 不允许超过 3 个表的 join 查询

处理 bug

ERROR 1819 (HY000)

Your password does not satisfy the current policy requirements

其实这是 mysql 的默认安全级别导致的

-- 查看 mysql 的默认安全级别
show variables like "%validate%";

-- 由于是日常练习，所以可以将安全策略和密码长度的要求降低
set global validate_password.policy=0;
set global validate_password.length=4;

• 搞定～

ERROR 1698 (28000)

Access denied for user root@localhost

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 在 [mysqld] 下写入
skip-grant-tables
# 保存退出，这样你就能免密接入 mysql

service mysql restart

mysql -u root -p
# 遇见输入密码的提示直接回车即可

use mysql;
flush privileges;
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassward';
flush privileges;
quit

• 将skip-grant-tables注释或删除，更改密码成功～

---

• 现在，如果你在尝试接入 mysql 时依然报错，则需要再次让skip-grant-tables生效，并再次执行service mysql restart，无密接入 mysql 后执行如下操作

use mysql;
select user, plugin from user;

-- 将 root 的 plugin 字段改为和其他 user 的 plugin 字段一致，比如
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'mysql';

-- 复查确认是否修改成功
select user, plugin from user;
quit

• 将skip-grant-tables注释或删除，更改密码成功～

参考了

1. https://www.cnblogs.com/cpl9412290130/p/9583868.html
2. https://blog.csdn.net/r527665047/article/details/107056941
3. https://my.oschina.net/u/4327623/blog/4325113

（完）