MySQL初阶-数据表操作

MySQL 初阶 · 数据表操作一、数据库操作承接上节1. 整体结构客户端 ───────▶ 服务器 └─ database按业务来划分 └─ 数据表table └─ 数据行row └─ 数据字段field服务器中把数据按业务划分成一个个 database。层级关系database → 数据表 → 数据行 → 数据字段。2. 数据库的四个基本操作创建数据库createdatabase[ifnotexists]数据库名charsetutf8mb4;if not exists不存在时才创建。charset utf8mb4指定字符集完全体的 utf8。查看所有数据库showdatabases;选中数据库use数据库名;删除数据库dropdatabase数据库名;⚠️非常危险的操作删库会立刻被发现后续程序一操作数据库就会立即报错。二、数据类型与 C / Java 的类型系统非常相似。2.1 数值类型类型说明FLOAT(M,D)单精度浮点型遵循 IEEE754 标准DOUBLE(M,D)双精度浮点型遵循 IEEE754 标准DECIMAL(M,D)精确数值用于科学计算、金额浮点数的经典问题0.1 0.2 0.3的结果是false二进制无法精确表示。用于金额计算时推荐DECIMALJava 中有BigDecimal这样效果完全一样的类。结论如果用 float / double 做金额计算也容易引起误差。2.2 字符串类型类型说明CHAR(M)固定长度字符串M表示长度以字符为单位取值范围 0 ~ 255M省略则长度为 1VARCHAR(M)可变长度字符串M表示字符最大长度取值范围 0 ~ 65535有效字符个数取决于实际字符数和使用的字符集char(10)长度固定为 10 个字符。varchar(10)最大长度是 10最大长度 64KB最常用的字符串类型。字符 ≠ 字节一个字符可能是多个字节取决于编码方式。2.3 文本类型TEXT类型说明TINYTEXT小文本类型最大长度为 2552^8 - 1个字节有效字符个数取决于使用的字符集TEXT文本类型最大长度为 655352^16 - 1个字节MEDIUMTEXT中文本类型最大长度为 16,777,2152^24 - 1个字节LONGTEXT大文本类型最大长度为 4,294,967,295 即 4GB2^32 - 1个字节上面这几个类型都是用来存储文本的。通常不会给数据库存这么大的数据存这么大的数据很可能会大幅度降低数据库的访问效率。2.4 二进制类型类型说明BINARY[(M)]固定长度二进制字符串与CHAR类似但存储的是二进制字符串M表示长度以字节为单位取值范围 0 ~ 255M省略则长度为 1VARBINARY(M)可变长度二进制字符串与VARCHAR类似但存储的是二进制字符串M表示长度以字节为单位取值范围 0 ~ 65535TINYBLOB小二进制字节类型最大长度为 2552^8 - 1个字节BLOB[(M)]二进制字节类型最大长度为 655352^16 - 1个字节MEDIUMBLOB中二进制字节类型最大长度为 16,777,2152^24 - 1个字节LONGBLOB大二进制字节类型最大长度为 4,294,967,295 即 4GB2^32 - 1个字节⚠️明确禁止数据库存储二进制数据!!!一旦给数据库表里存储了二进制极可能会大幅度拖慢数据库的速度。数据库关系型数据库一般来说就是一个后端服务器系统中最慢的一环。2.5 文本 vs 二进制txt、md、c、java这些都是文本文本文件中的每个数据都能在字符编码表上查询到对应结果。png、mp3、exe、dll这些都是二进制二进制文件中的数据有很多都在字符编码表上查询不到。简单粗暴的区分方式用记事本打开这个文件看里面是否是乱码不是乱码 文本是乱码 二进制记事本软件本质上是按照文本的方式去解析的相当于帮我们把「查编码表」这个动作自动做了。C 语言的文件操作可作类比fopen(xxxx,r);// 文本方式读打开fopen(xxxx,rb);// 二进制方式读打开2.6 枚举与集合ENUM / SET类型说明ENUM(value1,value2,...)有限个值把每个情况都列出来使用枚举类型比直接使用 1、2、3、4 整数可读性更好SET(value1,value2,...)从列表value1,value2...中选择 0 个或多个值最多 64 个元素单个元素最大长度 M 255M × N 1020M 是元素长度N 是字符集中字符所需的最大字节数SET 值在内部表示为整数相关范式数据库设计的第一范式要求进行数据库设计的时候每个属性字段都得是「原子」的不能再拆分。SET这种把多个值放进一个字段的做法违背了第一范式。2.7 日期时间类型类型大小说明0 值TIMESTAMP[(fsp)]4 bytes时间戳类型支持范围 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07.4999990 值为 0000-00-00 00:00:000000-00-00 00:00:00DATETIME[(fsp)]8 bytes日期类型和时间类型的组合支持范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59.499999显示格式YYYY-MM-DD hh:mm:ss[.fraction]0 值为 0000-00-00 00:00:000000-00-00 00:00:00DATE3 bytes日期类型支持范围 1000-01-01 ~ 9999-12-31显示格式YYYY-MM-DD0 值为 0000-00-000000-00-00TIME[(fsp)]3 bytes时间类型支持范围 -838:59:59.000000 ~ 838:59:59.000000显示格式hh:mm:ss[.fraction]0 值为 00:00:0000:00:00YEAR[(4)]1 byte4 位格式的年份支持范围 1901 ~ 2155显示格式YYYY0 值为 00数据库底层存储的是「时间戳」查询数据、显示给用户看的时候能够自动转成格式化时间年月日时分秒。时间戳的含义以1970 年 1 月 1 日 0 时 0 分 0 秒为基准计算当前时刻和基准时刻的秒数 / 毫秒数 / 微秒数……之差。例1789991053当前时间戳。使用整数的方式表示方便存储也方便计算。2038 问题历史上有很多程序都在使用 4 个字节的时间戳到了2038-01-19 03:14:07这个时刻4 个字节的时间戳直接越界软件程序会有什么后果、产生怎样的 bug完全不得而知——堪比千年虫的威力。2.8 关于排序字符串类型以字符为单位可以单独指定字符集和排序规则比如字符集是utf8mb4排序规则是utf8mb4_0900_ai_ci。二进制以字节为单位可以指定bin结尾的排序规则比如utf8mb4_bin这时比较和排序基于数字字节码值。字典序以前学的「简单粗暴」的比较方式适合英文系的国家不适合咱们使用如AAA 建材批发、* 建材批发。拼音序数据库不会原生自带一般都是业务代码Java通过第三方库来实现的通讯录 / 微信根据联系人名字的汉语拼音来决定顺序。三、表操作3.1 创建表createtable表名(列名 类型,列名 类型,......);示例mysql create table student3 ( - id int, - name varchar(20), - age int); Query OK, 0 rows affected (0.01 sec)需在use 数据库名之后执行先选中数据库。;表示 SQL 结束了。如果一个 SQL 太长了允许换行输入。类型与变量名的顺序与 C / Java 对比C / Java类型 变量名如int a 10;Python / Go 都是把类型写到后面a: int 10、var a int 10;3.2 查看该数据库中的所有表showtables;先use3.3 查看表结构desc表名;-- desc describe 描述作用查看都有哪些列每个列叫什么名字每个列是啥类型每个列有啥约束。NULL表示格子中是啥都没有的。SQL 中的关键字不区分大小写NULL/null/Null都可以。3.4 删除表droptable表名;删除表万一删错了也会造成严重后果!!!千万不要乱搞非常危险的操作!!!删库操作一旦触发立即就会被发现此时后续程序一旦操作了数据库就会立即报错。删表操作不一定能立即发现拖着拖着就会从小问题拖成大问题数据库恢复的时候都难受。3.5 修改表选学ALTERTABLEtbl_name[alter_option...];常用选项|ADD[COLUMN]col_name column_definition[FIRST|AFTERcol_name]|MODIFY[COLUMN]col_name column_definition[FIRST|AFTERcol_name]|DROP[COLUMN]col_name|RENAMECOLUMNold_col_nameTOnew_col_name|RENAME[TO|AS]new_tbl_name键盘上alt键全名就是alter帮助记忆。可以让 AI 给我们写 SQL。表结构的要求关系型数据库对于数据的格式要求比较严格。建表之后定义好表结构后续的数据都需要遵守这些结构。关于「列」的叫法数据库中的列有多种叫法列column、字段field、属性property以前的叫法。四、表在磁盘上对应的文件数据库把数据保存在硬盘上的。MySQL 会把每个数据库作为一个目录把每个表作为目录中的一个文件。Data目录下每个.ibd文件对应一个表例如student.ibd、student2.ibd、student3.ibd。这些是二进制文件不能用记事本打开MySQL 高阶课程中详细解释。目录示例javal19数据库目录、mysql、order、performance_schema、#innodb_redo、#innodb_temp等。找到 MySQL 客户端的快捷方式右键快捷方式 属性可以看到启动命令“D:\Program Files\MySQL\MySQL Server 8.3\bin\mysql.exe” “–defaults-fileC:\ProgramData\MySQL\MySQL Server 8.3\my.ini” “-uroot” “-p” “–default-character-setutf8mb4”- 如果觉得上面找的过程太麻烦还可以安装 everything 这样的软件直接去搜索 my.ini快速找到刚才的 Data 目录。 ### 生产环境的安全意识 - 对于中大型公司来说生产环境的数据库非常重视安全性会把数据库服务器的**端口号用防火墙保护起来**在服务器之外的机器去访问访问不了。 - 只能登录到该服务器之后使用**命令行客户端**来操作了。 - navicat 只能安装到你的办公环境的 Windows 电脑很可能你的 Windows 电脑**无法直接访问**数据库服务器。 --- ## 五、综合案例store 数据库 创建一个 store 数据库用来管理商场的商品、顾客和订单数据这个数据库有以下三个表 ### goods商品表 | 列名 | 类型 | 描述 | | --- | --- | --- | | id | bigint | 商品编号 | | name | varchar(50) | 商品名 | | unitprice | decimal(12, 2) | 零售单价 | | costprice | decimal(12, 2) | 成本价 | | category | varchar(20) | 商品类型 | | provider | varchar(50) | 供应商 | ### customer客户表 | 列名 | 类型 | 描述 | | --- | --- | --- | | id | bigint | 客户编号 | | name | varchar(20) | 客户姓名 | | gender | bool | 性别 | | phone_num | varchar(20) | 电话号码 | | email | varchar(50) | 电子邮件 | | address | varchar(255) | 地址 | | create_time | datetime | 注册时间 | ### order订单表 | 列名 | 类型 | 描述 | | --- | --- | --- | | order_id | varchar(32) | 订单编号 | | customer_id | bigint | 客户编号 | | goods_id | bigint | 商品编号 | | nums | int | 购买商品数量 | | amount | decimal(12, 2) | 总金额 | | create_time | datetime | 订单创建时间 | ### 要点 - 创建很多表都会分配 id 列作为「**数据的身份标识**」。 - 建 id 列时**更青睐使用 bigint** - 如果**一开始不考虑好后面再alter的时候 就不仅仅是一条命令的事情了。。** - int 的范围-21 亿 ~ 21 亿。 - bigint 的范围远远超过 int。 - **金额也可以直接用 int 表示**某些情况下 decimal 计算比较慢、存储消耗更多空间算钱的场景可以直接使用 int把单位设为「分」。 - 例某个商品的价格 5.98 598整数。 --- ## 六、增删改查操作CRUD CURDCRUD是对数据库中数据进行的四种基本增删改查操作 | 操作 | 含义 | | --- | --- | | **C**reate | 创建 | | **R**etrieve | 读取 | | **U**pdate | 更新 | | **D**elete | 删除 | - 数据库软件核心功能就是把数据**存储**起来然后进行**增删改查**。 - 实际工作中和数据库操作相关的 **90%** 的内容都是本章节中涉及到的内容。 - 指定某个表之后如何对表里面的数据操作**下一篇**具体介绍select。 - 秋招一般也不会直接考 SQL太基础了。 ---