数据库基础

最新推荐文章于 2024-09-29 17:11:59 发布

原创最新推荐文章于 2024-09-29 17:11:59 发布 · 323 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #开发语言 #后端

MySQL数据库专栏收录该内容

3 篇文章

订阅专栏

1.关于字符集编码的知识

1.1 字符串如何转化成数据

通过指定一张不重复的表格，建立一一对应关系
另一个计算机厂商A <---->03 与上面的表格中A的值不同，为了消除歧义，有一定权威组织指定标准，所有的厂商都遵守标准
ASCII编码：只编码0~127一共127个字符，当时针对以英美为主要语言的国家，
Unicode 通过编码表0–>(2^32 - 1)来进行字符编码
ASCII是Unicode编码的子集，Unicode对ASCII编码是兼容的
Unicode只是一种理想的编码，到真正存储（存到内存or硬盘），面临新问题，2^32完整的保存是需要4个字节，Hello如果直接存储，需要4*5=20个字节；真正存储时为了节省空间又设计出不同的编码格式
只要使用了utf-8编码格式，隐含着背后是Unicode编码了

1.2 字符集编码为什么麻烦

具有传导性的问题，要求整个链条上的人都不能出错
utf-8的标准名称是utf-8（不区分大小写），编程语言一般不好处理-，一般写为utf_8、utf8，MySQL直接把utf8作为其utf-8的标准名称
MySQL为了成本的考虑，实现utf-8编码时，一开始为了节省空间，并没有支持所有的字符，只支持unicode的80%，后来推出了utf8mb4的形式，支持所有字符

1.3 MySQL的字符集继承规则

表的默认字符集继承自库的默认字符集；字段的默认字符集继承自表的默认字符集
只要库的字符集是utf8b4，表的字符集就也是utf8mb4，同时，表中所有字段（字符串类型）的默认值也是utf8mb4
字段的字符集一旦确定，则即使更改库的字符集之后，表和字段也不会改变
在创建的过程中是继承的，在修改的过程中是不继承的，最好删除重新建库

2.关于表

2.1 表中的数据类型

数值类型整型、非整型带符号的
- 整型 int/INT tinyint<smallint < int < bigint
- 浮点（有精度损失的小数） float < double
- 带精度的小数 decimal 、numeric
字符串类型/二进制数据
- char(4) 长度为4个字符（定长）
- varchar（4）最多长度为4个字符（变长）
- 典型使用char（使用较少）的例子：身份证（有字符并且定长）、性别（男、女），剩余场景使用varchar更合适
- varchar（长度有上限），字符串更长的情况：text smalltext < text < longtext
- 二进制数据：图片、声音等 blob (Binary Large OBject) 较大的二进制对象
时间类型
- date 日期年-月-日
- time 时间时：分：秒
- datetime 日期+时间年-月-日时：分：秒
- timestamp 时间戳：约等于datetime 年-月-日时：分：秒，时间戳是指：从1970-1-1 开始经过的秒数，相对于datetime来说，优点：占用空间小；缺点:有限度 1970到2038年
- 凡是涉及到时间，都会有一个时区（timezone）
- MySQL数据库表示字符串，单引号或者双引号都可以 ‘A’ “AA”
- 数据中表示时间：用字符串表示 “2021-11-20 10:07:18” 严格按照该格式去写

2.2 约束（constrait）

用来约定该字段数据的范围，支持哪些数据？不支持哪些数据？

2.2.1 约束类型

在这里插入图片描述

2.2.2 主键（Primary Key/ PK）

关系型数据库要求：对于一张表必须有主键；
主键一条记录的唯一标识，不能重复
通过主键，可以唯一确定一条记录；实际中还会存在一些符合主键，通过几个字段的联合来确定唯一记录
实际中，建议主键使用整型（int，bigint），配合AI(自动增长)系统默认自动增长，不会重复

2.2.3 NN Not Null

勾选：不允许为Null，不勾选，允许为null
一条记录中的某个字段为Null，一般代表的是该字段不可知、不可记录；
允许null && 没有默认值时，Default就是Null；
允许null && 有默认值时，以设定的Default为准；
不允许null && 有默认值时，以设定的Default为准；
不允许null && 没有默认值时，没有默认值；

2.2.4 UQ Unique index

唯一建索引，该字段的值不能重复；

如果允许为null，null不算重复；

CREATE TABLE `db_11_20`.`users` (
  `uid` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  PRIMARY KEY (`uid`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC));
  -- 第一次插入成功
  INSERT INTO users (username,password) VALUES ('A' , '123');  
  -- 第二次插入
-- Error Code: 1062.
-- Duplicate entry 'A' for key 'username_UNIQUE'	0.000 sec
-- 重复 字段的值'A' 
-- 表中username 字段已经有'A'这个值了，不允许再插入一个新的'A';
INSERT INTO users (username,password) VALUES ('A' , '123'); 
-- 因为password字段，没有勾选UQ 所以允许重复 
-- 5	B	123 是递增的，可能会有失败的插入会把id消耗掉
INSERT INTO users (username,password) VALUES ('B' , '123'); 
-- null 不视为一种重复，允许多次插入
INSERT INTO users (username,password) VALUES (null,null); 
INSERT INTO users (username,password) VALUES (null,null); 
INSERT INTO users (username,password) VALUES (null,null);

在这里插入图片描述

2.2.5 Default

当向表中新插入一个字段时，要么为字段插入值，要么该字段有默认值；

在这里插入图片描述

2.2.6 comment

库、表、字段都支持comment，为了说明用途一般要求每个表每个字段都有备注；

CREATE TABLE `db_11_20`.`users` (
  `id` INT NOT NULL COMMENT '用户唯一标识',
  `username` VARCHAR(45) NOT NULL COMMENT '用户名，并且不允许重复',
  `password` VARCHAR(45) NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '用户表';

2.3表结构

字段组成：名称 + 数据类型(int、varchar、text、blob、datetime、time) + 约束(pk、ai、nn、uq、default、comment)；

3.举例

例：有一个商店的数据，记录客户及购物情况，有以下三个表组成：

商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider) ；
客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证 card_id) ；
购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)；

分析：

- 在这里插入图片描述

在这里插入图片描述

SQL：

CREATE TABLE `db_11_20`.`goods` (
  `gid` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `price` INT NOT NULL COMMENT '以分为单位',
  `category` VARCHAR(45) NOT NULL COMMENT '类别',
  `provider` VARCHAR(45) NOT NULL COMMENT '供应商',
  PRIMARY KEY (`gid`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

在这里插入图片描述

- 在这里插入图片描述

在这里插入图片描述

SQL:

CREATE TABLE `db_11_20`.`customers` (
  `cid` INT NOT NULL AUTO_INCREMENT,
  `cname` VARCHAR(45) NOT NULL,
  `address` VARCHAR(200) NOT NULL,
  `email` VARCHAR(200) NOT NULL,
  `sex` CHAR(1) NOT NULL,
  `idcard` CHAR(18) NOT NULL,
  PRIMARY KEY (`cid`),
  UNIQUE INDEX `idcard_UNIQUE` (`idcard` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

SQL：

CREATE TABLE `db_11_20`.`purchase` (
  `pid` INT NOT NULL AUTO_INCREMENT COMMENT '购买订单号',
  `cid` INT NOT NULL COMMENT '购买者',
  `gid` INT NOT NULL COMMENT '买了什么',
  `bought_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '何时购买',
  `price` INT NOT NULL COMMENT '价格',
  `num` INT NOT NULL COMMENT '数量',
  PRIMARY KEY (`pid`))
COMMENT = '购买记录';

插入数据

--  价格以分为单位 '130000' ---->1300元
INSERT INTO goods (name, price, category, provider) VALUES 
		('华为手机', '130000', '家用电器', '华为');
INSERT INTO goods (name, price, category, provider) VALUES 
		('小米电视', '231258', '家用电器', '小米');
INSERT INTO goods (name, price, category, provider) VALUES 
		('水煮牛肉', '5830', '视频', '王师傅');
        
INSERT INTO customers (cname, address, email, sex, idcard) VALUES 
		('小A', 'xx省xx市xx小区', 'a@qq.com', '女', '0101199910130018');
INSERT INTO customers (cname, address, email, sex, idcard) VALUES 
		('小B', 'yy省yy市yy小区', 'b@qq.com', '男', '0101199910130017');
INSERT INTO customers (cname, address, email, sex, idcard) VALUES 
		('小C', 'zz省zz市zz小区', 'c@qq.com', '女', '0101199910130019');
        
-- 注意 bought_at 的默认值 CURRENT_TIMESTAMP 的用法 ---->插入当前时间
INSERT INTO purchase (cid, gid, price, num) VALUES 
		(1, 3, 5830, 2);
INSERT INTO purchase (cid, gid, price, num) VALUES 
		(2, 3, 5830, 1);
INSERT INTO purchase (cid, gid, price, num) VALUES 
		(1, 1, 130000, 1);
INSERT INTO purchase (cid, gid, price, num) VALUES 
		(2, 2, 231258, 1);