SQL基础

2023-12-15 10:48:32
作者简介:大家好,我是smart哥,前中兴通讯、美团架构师,现某互联网公司CTO

联系qq:184480602,加我进群,大家一起学习,一起进步,一起对抗互联网寒冬

先来复习一下SQL基础,为后面的SQL优化做准备。

范式与反范式

实际上数据库范式不止3种,但大家熟知的就三种。

第一范式

所有列应该不可再分

比如,往contact列存储"18257500000,杭州,523839000@qq.com"是比较糟糕的做法,因为此时该列包含了phone、address、email三个维度的数据,应该拆成phone、address、email三个字段分别存储,这样对更新和查询都有好处。

第二范式

必须存在业务主键,且非主键字段应该依赖于全部业务主键(之所以写“全部”,因为可能存在复合主键)

说人话就是:每张表最好都设定主键。虽然某些列可能具备主键的特质(比如user表的id_card),但个人认为主键最好与业务无关,比如自增id。

id

xxx

yyy

zzz

...

1

...

...

...

...

第三范式:

非主键列不能依赖除主键列以外的其他列

听起来很抽象,举个例子就明白了:

t_student

id

stu_name

stu_age

teacher_name

teacher_age

1

张三

18

李四

40

上面这张t_student表就违反了第三范式:非主键列teacher_name、teacher_age并不依赖id(学生的)。这种做法被称为“冗余”,它的弊端是有可能导致数据不一致。比如t_teacher表数据更新了,李四的age变成41,而代码里却没有及时维护t_student表的数据,就会导致从t_student表查出来的数据中,李四还是40岁。

改写成符合第三范式的设计:

t_student

id

stu_name

stu_age

teacher_id

1

张三

18

10086

t_teacher

id

teacher_name

teacher_age

address

10086

李四

40

hangzhou

总结一下三范式:

  • 每一列字段应该不可再分,职责单一
  • 要有主键,最好是与业务无关的自增id
  • 不要有冗余字段,为了避免数据更新不一致,应该拆成两张表,用(逻辑)外键关联

一般来说,前两个范式大家都会遵守,但第三范式有时会被打破(就像上面的t_student一样)。因为实际工作中,越遵从范式化设计,表的拆分越细致,查询时需要关联的表就越多。

比如:

SELECT t1.aaa, t2.bbb, t3.ccc
FROM t1 
LEFT JOIN t2 ON xxxx
LEFT JOIN t3 ON xxxx;

此时我们可以适当反范式化设计(反第三范式),目的是减少查询时需要关联的表的数量从而提升查询性能:

SELECT t1.aaa, t1.bbb, t1.ccc
FROM t1;

所以才会出现上面t_student表里冗余teacher_name和teacher_age的设计。

但不论范式化设计还是反范式化设计,都不能过度:

  • 遵守第三范式,有时会让查询变得非常麻烦,要么JOIN关联,要么内存中匹配,甚至干脆无法满足需求
  • 不遵守第三范式,则需要主动维护冗余数据,避免造成数据更新不一致

但有些场景下,冗余数据百利而无一害。举个例子,比如订单表中的商品价格。商品价格会随着时间发生改变(促销等),而订单表只需记录当前下单的价格即可,不需要更新,否则你双11花了2999买的手机,过几天查询订单发现价格变成了3200,会怀疑自己是不是多付了。

数据类型选择

分类的方法很多,但这里只按自己的理解及使用频率分为4大类:

  • 整数类型
  • 字符类型
  • 小数类型
  • 时间类型

整数类型

数值类型唯一需要注意的3点:

  • 如果业务允许,尽量设置unsigned
  • int(11)里的11和占用字节大小无关
  • 注意各个类型的选取标准

所谓unsigned,即无符号。比如tinyint,正常取值范围是-128~127。但实际业务中很少需要用到负数,比如年龄、身高等都是整数,最小为0。此时使用unsigned可以让正向范围翻倍:0~255。

如果业务需要,可以为当前字段设置默认值,比如文章状态status默认0,表示“待审核”。

另外,关于int(11)里的11,很多人都不是很清楚。其实括号里的数字和占用字节大小无关,哪怕你写成int(1)也不代表它比int(11)省空间,这只是列宽表示,比如位数不够就前面补零啥的,但对实际数值大小没有影响。总之,对于数值类型来说,每种类型占用空间大小是固定的。

来看一下各种数值类型的占用空间:

数据类型

占据空间

范围(有符号)

范围(无符号)

描述

tinyint

1 个字节

-2^7 ~ 2^7-1

0 - 255

小整数值

smallint

2 个字节

-2^15 ~ 2^15-1

0 - 65535

大整数值

mediumint

3 个字节

-2^23 ~ 2^23-1

0 - 16777215

大整数值

int

4 个字节

-2^31 ~ 2^31-1

0 - 4294967295

大整数值

bigint

8 个字节

-2^63 ~ 2^63-1

0 - 18446744073709551615

极大整数值

选择数值类型时,最重要的规则是“够用就好”。比如对于“性别”或“年龄”,用tinyint足够了,毕竟还没听过有人活过250岁的。这里并不是为了省磁盘空间而去扣这些细节,毕竟磁盘是最不值钱的,主要关系到索引。后面会解释,总之记住“够用就好”。

通常来说:

  • 主键id用bigint
  • age、height等普通数据用int
  • deleted、status、type用tinyint

之前听说有些公司对于只有0、1两种状态的字段使用bit,也...行吧,按公司的约定来吧。隐约听过有坑,但我自己试了下没发现。大家没啥事可以自己去试试各种类型,做做实验

字符类型

平时大家会经常看到char(8)或者varchar(255)这样的形式对吧?经过上面的学习,你可能会觉得:哦,这也是显示作用,和实际大小无关。

那你就错了。

字符类型的数字和实际大小有关,准确地说这里的数值和实际存储大小的上限有关。比如char(3),表示会固定占用3个字符空间,即使存储的值不够3个字符,照样会占着那块空间,但不能超过3个字符:

实际开发中,一不小心就会出现上面的报错信息,此时你应该要意识到这是字符超过规定长度了。

至于varchar(255),表示最多存储255个字符。看起来好像和char(255)没区别?实际上,char和varchar分别代表着两种类型:定长与变长。

比如int、bigint这些都是定长,而varchar是变长。

varchar作为“变长字符”,它的占用空间是可伸缩的。 varchar(255)表示最多能存储255个字符,但最终占用空间以实际存储的值为准,可能实际占用M个字符(M<=255),而char(255)则一定会占用255个字符的空间。

看起来好像varchar是百利而无一害,完爆char对吧?

char VS varchar

  • char长度固定,不需要考虑边界问题,读写效率高于varchar,适合存储长度固定、频繁读写的数据
  • varchar长度不固定,但可以通过varchar(10)的方式指定上限,适合存储长度波动、更新不频繁的数据
  • char的存储长度不够灵活,而varchar则需要浪费1~2个字节来存储当前值的实际长度,且更新会导致重新计算

关于第一点,你可以简单理解为:

char是定长,说了一个字段用3个格子存储就一定是3个格子,所以当你要找第3个数据时,只需要往右数6个格子,那么7~9就存着你要找的数据。而varchar(3)的“3”只代表上限,实际不一定占用3个格子,所以不能直接计算得到位置。

没有最完美的类型,只有最合适的类型。比如,当你需要存储手机号码或者身份证号时,用char(11)、char(18)显然更合适。但存储“个人介绍”时,用varchar更好,因为个人介绍的长度是可变的。

小数类型

数据类型

占据空间

是够精确

float

4个字节

非精确

double

8 个字节

非精确

decimal

每4个字节存9个数字,小数点占一个字节

精确

对于decimal的大小,比如123456789.987654321,用decimal(18,9)存储,占9个字节,前后各4个字节,小数点一个字节。decimal的效率不如float和double。

当然,很多电商公司其实都是直接存最小单位“分”,也就没有精度问题了。

时间类型

数据类型

占据空间

取值范围

date

3个字节

1000-01-01 ~ 9999-12-31

time

3~6个字节

-838:59:59 ~ 838:59:59

datetime

5~8个字节

1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

timestamp

4~7个字节

1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

DATETIME 和 TIMESTAMP的区别:

  • 时间范围不同,DATETIME更大,内存稍微大一点
  • TIMESTAMP的时间会根据时区变化。比如 SET time_zone='+10:00',那么查询后会自动增加10小时

具体跟着公司走就好了,比如我们公司甚至没用时间类型,直接用Long存秒数。

类型选择小结

  • 更小的通常更好
  • 简单合适就好
  • 尽量避免null(设置NOT NULL,除非业务要求可能NULL)
  • 如果确定不会出现负数,可以使用unsigned

NOT NULL:一定要传递值,且不能为NULL,否则报错

DEFAULT 'xx':传不传都可以,不传就使用默认值xx,可以传NULL

NOT NULL DEFAULT 'xx':传不传都可以,不传就使用默认值xx,不能传NULL

可以做个实验:

CREATE TABLE `test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `age_not_null_default` int(10) NOT NULL DEFAULT '0',
  `age_not_null` int(10) NOT NULL,
  `age_default` int(10) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

测试:

# age_not_null_default 要么不传,要么传非NULL值,这里选择不传,则插入默认值0
# age_not_null 一定要传值,且不能为NULL
# age_default 传不传都可以,可以传NULL
INSERT INTO `test` (`age_not_null`, `age_default`) VALUES(1, null);

语句书写顺序

SELECT ... FROM table WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

除了SELECT,后面几个顺序可以记忆为:温哥华OL,意思是温哥华白领。

关联查询

隐式连接

什么是隐式连接?不用写JOIN关键字的连接。

格式是:

FROM t_a, t_b WHERE或ON 等值连接条件

隐式连接属于内连接,效果等同于:

FROM t_a [INNER] JOIN t_b ON 连接条件

显式连接

内连接

格式:

FROM t_a [INNER] JOIN t_b ON 连接条件

INNER写不写都可以,查询效果和隐式连接一样,连接条件写在ON后面。

如果两个表做等值判断的字段相同,比如 ON t1.id = t2.id 可以改写为 USING(id),但没什么卵用,我要不是这次复习,都不知道这是啥意思。所以尽量别用这种乱七八糟的写法,给同事添堵。

外连接

  • LEFT JOIN
  • RIGHT JOIN?

本质是一样的,换个位置而已。

自连接

格式:

FROM t_a child, t_a parent ON 连接条件

自连接不是一种新的连接形式,它可以用上面的任意一种连接方式,只不过是把同一张表当做两张表,自己和自己关联。

其他的什么自然连接(NATURAL JOIN)、交叉连接(CROSS JOIN)不提了,我反正从来没用过,大家有兴趣自行了解,不徒增大家的记忆负担。

子查询

子查询指的就是在一个查询之中嵌套了其他若干个查询。

子查询通常出现在

  • WHERE后面:SELECT name FROM table_a WHERE id IN (SELECT id FROM table_b)
  • FROM后面:SELECT name FROM (SELECT name, age FROM table_a) temp LEFT JOIN....
  • EXISTST后面:没用过,简单说一下

EXISTS和NOT EXISTS道理差不多,这里用NOT EXISTS举例。假设有一张积分转换记录表,用户经常会输错订单号,但经过多次尝试最终都会兑换成功。作为开发人员,我们每天都要时不时去观察一下今天有没有转换失败的记录。注意,这里的转换失败指的是自始至终都没成功的记录,那种失败多次最终成功的不算。假设t_order_convert_points表就2个字段:order_no、convert_status,你要如何筛选出转换失败的订单呢?(EXISTS/NOT EXISTS后面的子句只要有返回记录,就算条件满足)

SELECT
  *
FROM
  t_order_convert_points fail_record
WHERE
  fail_record.convert_status != 1  # 转换失败
  and NOT EXISTS ( # 且不存在成功记录
    SELECT
      *
    FROM
      t_order_convert_points success_record
    WHERE
      fail_record.order_no = success_record.order_no
      AND success_record.convert_status = 1
  );

放在FROM后的子查询可以看做一张临时表,WHERE后面的子查询就是动态的查询条件而已。

Navicat使用小技巧

在日常工作中,我们经常需要建表、修改表结构。建表时,需要向运维提供建表语句;DDL修改时,则需要提供修改语句。对于MySQL基础不扎实的同学,会造成一定困扰。好在Navicat有很方便的工具可以帮助我们进行可视化的操作。

建表语句就不说了,直接拷贝即可:

如果需要改字段,可以先在本地通过可视化界面进行修改(建议从线上拷贝SQL在本地建表,然后在本地表上修改,避免误操作):

下方可以设置默认值、无符号等:

设置完成后,借助工具查看刚才操作对应的DDL语句:

ctrl+s保存修改,然后查看:

当然,这个是日志,如果你只是想看DDL语句,可以直接看这:

本章简单地带大家复习了一下基础,关于常用函数、聚合函数以及更多的连接查询都没有涉及

作者简介:大家好,我是smart哥,前中兴通讯、美团架构师,现某互联网公司CTO

进群,大家一起学习,一起进步,一起对抗互联网寒冬

文章来源:https://blog.csdn.net/smart_an/article/details/135009214
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。