SQL & MySQL 用法速通

概述

image-20220305180750607

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

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

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

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

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

image-20220305180159205

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

  1. MySQL:开源免费跨平台、稳定高性能

  2. Oracle:也挺适合大型系统,比如「银行系统、医院系统、…」

  3. SQL Server:常见于 c# 和 .net 体系,适合在 Windows Server 中使用

MySQL

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

实验环境

ubuntu-20.04-lts、mysql8

1
2
3
4
5
6
7
8
# 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

  • 方法一
1
2
3
4
5
mysql -u root

OR

mysql -u root -p # passwd is needed.
  • 方法二:使用图形化客户端,eg:dbeaver

SQL

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

分类

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

    • createdropalter
  2. 数据操作语言(Data Manipulation Language),用于操作数据库中的数据

    • insertdeleteupdate
  3. 数据查询语言(Data Query Language)

    • select

注释

1
2
3
4
5
6
-- 单行注释

/*
多行
注释
*/

数据类型

1
2
3
4
5
6
7
8
9
10
11
int     -- 整型

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

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

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

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

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

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 显示所有数据库
show databases;

-- 使用某数据库
use dbname;

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

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

-- 将某数据库的编解码方式设置为 utf8 以便支持中文字符
alter database dbname character set utf8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 查看当前数据库中的所有表
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);

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
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%” 的索引,此方法无效

1
2
3
4
5
select * from student where name like '%三';

-- 我们可以改造为

select * from student where reverse(name) like reverse('%三');
  1. 如下
1
2
3
4
5
6
7
select id from xxx where from_unixtime(start_day)>='2020-10-08';

-- 这会导致全表扫描

-- 正确写法是

select id from xxx where start_day>=unix_timestamp('2018-08-14');
  1. 一般情况下,手机号推荐使用 varchar(32) 存储,因为会有地区等属性;如果 phone 是 varchar,则如下写法会触发隐式转换,导致全表扫描
1
select id from xxx where phone = 15635638290;

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

  1. 避免使用 select *

    • select * 会获取不必要的列,这会增加 cpu,io,net 的消耗

    • 在有索引覆盖的情况下,会导致无法有效利用覆盖索引

    • 程序开发过程中容易因为忘记添加或删除字段,导致出现 bug

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

  3. 不允许在主库上执行统计 SQL(count/sum)

  4. 不允许大表使用 join,子查询

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

image-20220320100805557

处理 bug

ERROR 1819 (HY000)

Your password does not satisfy the current policy requirements

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

1
2
3
4
5
6
-- 查看 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

1
2
3
4
5
6
7
8
9
10
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

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

service mysql restart

mysql -u root -p
# 遇见输入密码的提示直接回车即可
1
2
3
4
5
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 后执行如下操作
1
2
3
4
5
6
7
8
9
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