MySQL**数值类型，表操作，CRUD**超详细入门宝典 一篇全搞懂

文章目录*1. 前言**2. 正文*1. 数据库的数值类型1.1 整型1.2 字符串类型1.3 日期类型2. 表操作2.1 创建表2.2 查看表结构2.3 删除表2.4 表操作的练习3. CRUD 新增查询3.1 CRUD介绍3.2 Create(新增)3.3 Retrieve (查询) 重点*3. 结语*“相信自己一路风景一路歌人生之美正在于此”1. 前言前面我们学习了数据库的基础知识以及创建库删除库的操作今天我们继续来学习数值类型与表2. 正文1. 数据库的数值类型1.1 整型SQL语言与JAVA语言都有属于自己的数据类型比如Java中的int long short 代表整型的数值类型SQL与JAVA整型上的数值类型是相似的对应java类型数据类型大小说明对应Java类型BIT[(M)]M指定位数默认为1二进制数M范围从1到64存储数值范围从0到2^M-1常用Boolean对应BIT此时默认是1位即只能存0和1TINYINT1字节ByteSMALLINT2字节ShortINT4字节IntegerBIGINT8字节LongFLOAT(M, D)4字节单精度M指定长度D指定小数位数。会发生精度丢失FloatDOUBLE(M, D)8字节DoubleDECIMAL(M, D)M/D最大值2双精度M指定长度D表示小数点位数。精确数值BigDecimalNUMERIC(M, D)M/D最大值2和DECIMAL一样INT和BIGINT没什么可说的与我们JAVA所学习的int是完全相同的只是BIGINT等同于Long类型这里我们注意一下tinyint(1)bool这里的bool就是boolean类型 我们最常用的有INT BIGINT DECIMAL(M,D)这里我们还需要注意decimal(M,D)中M是不计算小数和负数的D是计算小数位的也就是如果有一个数字为 -123.456则M为6 D为3M的最大位数为65D最大位数为30如果省略D则默认为0省略M则默认为10了解即可所以我们得知decimal可以保存很大很大的小数在Mysql中我们很少使用float(M,D)或者DOUBLE(M,D),因为会发生精度的丢失我们统一使用decimal类型即可1.2 字符串类型在Java语言中我们常用的字符串类型有StringStringBuiderStringBuffer而在SQL语言中字符串类型就与我们所知的大相径庭了数据类型大小说明对应java类型VARCHAR(SIZE)0-65,535 字节可变长度字符串StringTEXT0-65,535 字节长文本数据StringMEDIUMTEXT0-16 777 215 字节中等长度文本数据StringBLOB0-65,535 字节二进制形式的长文本数据byte[]varchar(SIZE)是我们最常用的字符串类型例如要存放10个字符的字符串类型则使varchar(10),数据库的编码集为utf8mb4字符占1-4个字节所以在utf8mb4编码集的情况下varchar(SIZE)中至少可以存放16383个字符TEXT是文本文件中使用的字符串类型那么什么是文本文件呢文本文件是人类直接可以看懂的文件例如Java源代码配置文件Windows系统中文本文件常以.txt为后缀名而BLOB代表二进制文件中所使用的数据类型二进制文件是人类看不懂的文件都是一堆乱码1.3 日期类型数据类型大小说明DATETIME8字节范围从1000到9999不会进行时区转换TIMESTAMP4字节范围从1970到2038自动检索当前时区并进行转换首先我们明确一点TIMESTAMP是一个过时的日期数据类型代表时间戳我们以后最好不使用它我们统一使用DATETIME即可我们可以看一下官网对日期类型的解读我们可以看到这里官网给出了几种日期类型DATE 日期精确到日TIME 时间精确到秒DATETIME 日期精确到秒2. 表操作2.1 创建表我们在前面学习了数据库服务器的如何提供数据库服务以及数据库服务中数据库的操作包括(建库删除库下面我们就要接着学习数据库中对数据表的操作我们这次使用终端来完成操作部分操作使用图形化界面工具小编使用的是Navicat图形化界面工具跟紧小编的步伐1. 首先我们先检查一下数据库中都有哪些数据库我们发现现在数据库服务中只有系统原本有的数据库那么接下来我们要创建库我们创建了名为panxon123的数据库并指定了字符集和编码规则下面我们要使用这个数据库select database()是查询当前使用的是哪个数据库use数据库名称代表要使用这个名字的数据库2. 创建数据表我们也可以搜索官网的创建数据表的标准语法下面我们举一个例子来帮助理解创建一个名字为 student 的学生表表中只包含两个字段id代表编号name代表姓名长度为 50。这样我们的学生表就创建好了!id 要存放很多同学的编号所以我们使用了 bigint 的数据类型以后的项目中对于编号这一数据行我们都尽量使用 bigint 数据类型。我们可以查看数据库中都有哪些表也使用 show 关键字当然我们也可以加入 if not exists 关键字去完善我们创建表的 SQL 语句我们接下来用图形化编辑工具去尝试然后我们先检查一下数据库服务中的数据库我们发现在终端创建的 panxon123 确实存在接着我们创建数据表student也是没有任何问题的2.2 查看表结构查看表结构使用的关键字是“desc表名”我们直接来看看示例我们可以看到显示出student表中有两行数据分别为idname这里的TypeNULLKey我们会在之后的学习中重点讲到2.3 删除表这个删除表的操作与删除库操作一样能不用就不用是个非常危险的操作语法格式为 drop table [if exists] 表名 [ , 表名] …这里我们可以同时删掉多个表只需要在表名后加再加下一个表名即可那么下面让我们删掉学生表删除数据表后数据库即为空了。如果我们重复删除则会报警告查看警告信息我们会发现出来一个 panxon.student这个是库名.表名 来制定一张表在Mysql服务中的完全路径!库名.表名这一操作会在后续学习中经常用到2.4 表操作的练习上面我们梳理完对数据表的三种操作 创建查询删除。下面有一道练习题可以尝试一下有一个商店的数据记录客户及购物情况有以下三个表组成商品 goods (商品编号 goods_id商品名 goods_name, 单价 unitprice, 商品类别 category, 供应商 provider)客户 customer (客户号 customer_id, 姓名 name, 住址 address, 邮箱 email, 性别 gender身份证 card_id)购买 purchase (购买订单号 order_id, 客户号 customer_id, 商品号 goods_id, 购买数量 nums)创建出这是三个表这里我们使用Navicat去完成也是非常容易的这里我们注意一点gender性别的表示我们使用了bool类型这相当于tinyint1我们可以查看表结构当我们定义字段为 BOOL 或 BOOLEAN 时MySQL 会自动将其转换为 TINYINT(1) 类型。TINYINT(1) 中0 代表 FALSE1 代表 TRUE也可以存储其他整数-128 到 127。3. CRUD 新增查询3.1 CRUD介绍首先什么是CRUD?C代表 Create(新增)R代表 Retrieve(查询)U代表 Update(更新)D代表 Delete(删除)这里用中文翻译为增删改查其次我们要明确一下CRUD主要操作的是Mysql数据库体系的哪一部分CRUD操作主要针对数据表中数据行记录程序员最基本的操作也是对数据记录进行增删查改3.2 Create(新增)1.单行全列新增对于新增我们使用的语法为insert into 表名[(字段1字段2)] values(值值);其中values中的值要按照字段的顺序排放下面我们看实例我们注意一点SQL语句中的字符串统一用单引号而不是双引号字段名和赋值一定要一一匹配 !不匹配的话就会报错单行新增还有一个简写的方式insert into 表名 values(值 [值…])这里注意我们填写值时必须匹配表中所包含的数据类型如果有未被填入的数据或者与其对应的数据类型不符则会报警告select 为查询语句后面会说这里我们可以看到简写的方式确实可以往表中添加数据如果填入值未一一对应或缺少时同样会报 doesn’t match 这一错误2.单行指定列新增我们只需在表名后添加我们指定的新增数据的字段名即可如上图我们可以看到只指定了name 列的插入id 列系统会自动给予 NULL 默认值3.多行新增语法为insert into 表名(指定列…) values (值, [值…]) [(值, [值…]) …]与单行插入不同的仅为 values 可以当做一个个集合用逗号隔开即可也是没有任何问题的那么下面问题来了一次插入一条数据和一次插入多条数据哪个效率很高为什么结论批量插入效率更高速度效率稍微高一些​​​原因​​​​减少网络开销​​单条插入需反复与数据库通信批量插入一次性传输多行数据降低网络往返次数。​​降低事务开销​​单条插入每条需独立提交事务批量插入可合并为 1 个事务减少磁盘 I/O 和日志写入。​数据库优化​​批量操作可触发数据库内部优化如批量解析、磁盘 I/O 合并提升处理效率。4.插入时所要注意的编码集问题首先我们查看一下当前panxon123数据库的编码集利用SQL语句“show variables like ’%character%‘”当前的字符集为utf8mb4我们之前强调此字符集是支持中文的字符集也是Mysql8.0版本默认的字符集如果版本为Mysql5.3或者手动指定字符集为latin1的话我们添加的学生信息就会报错下面我们创建一个测试数据库并把字符集设置为latin1下面我们在这个数据库建立数据表以及插入数据我们发现插入学生数据的name为中文就会报错认识这个报错是因为字符集不匹配造成的3.3 Retrieve (查询)重点1. 全列查询语法select * from 表名其中 * 代表要查询表中的所有列我们直接举一个例子利用select 语句就可以查询表中的数据但是全列查询是一个非常危险的操作 如果数据非常多的情况下全列查询会造成磁盘开销网络开销甚至可能把服务器资源全部吃光所以不加任何限制的查询在生产环境不要使用2. 指定列查询语法select 列名[列名] from 表名首先有一张exam的数据表里面包含一些信息如果我们只想查询idname语文成绩那么我们就要用到指定列查询语法也是没有任何问题的3. 查询的结果是表达式如果我们的查询语句为SELECT id,name,chinese ,10 from exam;那么显示的结果会是什么样呢其中10 是一个数值类型让所有列中都包含一个表达式中的值但是本身不在真实的表里所以这里我们也能看到查询出来的结果都是被放入临时表中那么接下来我们要把所有学生的语文成绩在原来的基础上➕10分并查询出结果对比原表确实实现了➕10操作这说明查询语句中是可以携带表达式的也是我们经常使用的操作如果想计算总分的话是否能进行列与列的计算呢确实是可以的由于我们的表达式太长Navicat求总分的名称只取表达式的开头部分了如果想把名称改成‘总分’则需要一个全新的关键字’as‘注意这里的as是可以省略的‘’双单引号也可以省略但是如果字符集是含有空格的中文的话是不能省略的4. 去重查询利用关键字distinct语法select distinct 列名 from 表名如果在学生表中新添加了一个重复的数据在查询的结果中不想显示就要使用distinct关键字使用distinct关键字需要注意查询结果中每一列都相同Mysql才会认为是重复数据我们在exam表中新插入一个天命人的成绩其中数学成绩和英语成绩与孙悟空相同这时我们去重查询此时天命人这一数据并不会因为math 和 English 成绩相同而被去重5. 排序查询语法规则select 列名 from 表名 order by 列名 [asc/ desc]order by是排序的关键字 asc代表升序排列desc代表降序排列若不添加asc/desc那么Mysql默认的排序规则为升序排列asc这里我们注意排序语句中的降序排序关键字desc与我们查看表结构的关键字重合了这是编程界的大忌我们一定要注意区分下面我们举例子来更好地理解1 按语文成绩从高到低排序(2) 对数学成绩进行升序排序这里也可以省略asc 但是不建议(3)下面问题来了如果没有order by子句Mysql返回的结果集是按哪个字段排序的呢Mysql根据哪个字段排序是不确定的所以永远不要依赖默认排序返回结果集(4) 如果排序中出现NULLMysql会怎么处理NULL会视为比任何值都小升序出现在最上面降序出现在最下面所以NULL在Mysql中会被特殊处理以下三点要牢记不论和什么值进行运算返回值都是NULLNULL始终被判定为falseNULL的值不是我们以前学习过的其他编程语言中的0在Mysql它就是NULL(5) 可以使用表达式或别名进行排序吗答案也是可以的例如求总分并降序排列这里我们也可以看到孙大圣的英语成绩为NULL求出总分的成绩仍然为NULL(6) order by子句是否可以对多个字段排序也是可以的可以对多个字段进行排序排序的优先级与书写顺序有关可以为每个字段制定不同的排序规则例如对数学降序排列语文升序排列英语再降序排列每次的排序都是在上一个基础上继续进行排序的6. 条件查询可以通过指定一些条件过滤掉不符合条件的记录再把符合条件的记录返回给用户下面我们看看条件查询中所用到的运算符(1) 比较运算符运算符说明, , , 大于大于等于小于小于等于与JAVA一样等于,类比于JAVA中的 NULL不安全例如NULLNULL返回结果是NULL等于, NULL安全NULLNULL返回结果为TRUE(1)!,不等于(Mysql中更推荐第二种写法)BETWEEN a0 AND a1范围匹配,[a0,a1]在其范围之间返回TRUEvalue IN (option, …)如果value 在optoin列表中则返回TRUE(1)NOT IN则取反IS NULL是NULLIS NOT NULL不是NULLLIKE模糊匹配% 表示任意多个(包括0个)字符_ 表示任意一个字符NOT LIKE则取反注意Mysql中赋值或判断相等的运算符均是“” 剩下与Java类似LIKE比较特殊我们稍后详解(2) 逻辑运算符运算符说明AND多个条件必须都为 TRUE (1)结果才是 TRUE (1)类比于JAVA的OR任意一个条件为 TRUE (1), 结果为 TRUE (1)类比于JAVA的,NOT条件为 TRUE (1)结果为 FALSE (0)类比于JAVA的,(3) 条件查询语法select 列名 from 表名where 列名/表达式 运算符 条件(4)示例1 查询英语不及格的同学及英译成绩60(普通查询)这里我们发现结果集中没有 NULL说明 NULL 被 Mysql 自动过滤了2 查询语文成绩好于英语成绩的同学(表达式查询)以上我们可以发现一行中的两个列是可以比较的但是不能跨行比较这里作为重点条件查询只可以做列与列的比较不可以做行与行的比较我们后面会学习跨行的比较3 查询总分在 200 分以下的同学那么问题来了我们在学习排序查询时可以采用别名的方式来简化名称那么条件查询可否使用别名报错了所以是不可以的这是为什么呢这有关于 SQL 语句的执行顺序我们来拆解一下这是一句稍微复杂的 SQL 查询语句包含了条件查询和排序查询还有别名我们来分解一下它各个部分的执行顺序执行顺序为2 1 3 4如果要在数据中查某些数据首先要确定表先执行 from2在查询的过程中要根据指定的查询条件把符合条件的数据过滤出来这是执行的就是 where 语句1执行 select 后面的指定列这些列都是要加入最后的结果集中3排序操作根据 order by 子句中指定的列名和排序规则进行最后的排序44 查询语文成绩大于 80 分且英语成绩大于 80 分的同学 (条件查询 AND)这里 OR 和 NOT 的运用与 AND 相似小编就不列举例子了这里注意 AND OR NOT 也是有优先级区分的从这条语句的执行结果来看AND 优先级OR 优先级的NOT 肯定是最先被执行的所以优先级的顺序为NOTANDOR我们还是建议使用的时候自己加5 查询语文成绩在[80,90]分的同学及语文成绩 (between… and)这里使用 chinese80 and chinese90 也是可以的6 查询数学成绩是 58 或者59 或者 98 或者 99 分的同学及数学成绩 (in)(5) LIKE 关键字应用(模糊查询)我们之前学习过查看 mysql 版本号的语句“show variables like ‘% character %’”其中这里面的% 代表匹配任意多个字符也就是可以有任意的字符出现在 character 的前面或者后面所以用了 %character%当然这里面的结果集没有出现 character 前有字符的情况所以我们可以简写为 character%下面我们直接来看例子一个是孙后有匹配字符 %和孙前有匹配字符 %由于表中name 列没有叫什么什么孙的人所以匹配不到数据% 和_有什么区别呢_ 也是匹配字符但是它只严格匹配一个字符如果我想匹配孙悟空该怎么做利用两个_ 所以_ 相当于占位符结果集的个数与条件相符(6) NULL 的查询判断是否为 NULL这里不能用 ; 可以用也是没有任何问题分页查询之前我们学习 select* from 表名; 时说过不加限制记录条数的查询是不安全的所以我们用到了分页查询分页查询的使用也非常广泛通过分页查询可以有效的控制一次查询出来的结果集中的记录条数并可以有效地减少数据库服务器的压力我们可以看上图百度游览器的界面下方也使用了分页查询(1) 语法结构分页查询的语法结构有三种1SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT num;– (从 start 开 始筛选 num 条 结 果)2SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT start, num;–( start开始筛选 num条结果比第 一种用法更明确建议使用3SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT num OFFSET start;–(最标准的写法)(2) 练习1 从第 1 条数据开始查询两条数据为一页同理剩下两种方式也可以我们注意这里的 0 是从第 0 条记录开始2 代表往后读两条那么起始位置超过了结果集范围还能被执行吗这里就出现分歧了Navicat 直接会报错而终端会返回空结果集也是可以执行的小伙伴们这里注意一下即可2 分页关系我们知道分页查询语句SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT start, num;其中 start 和 num 是有关系的start(当前页号-1)* 每页显示的记录数(num)3. 结语以上就是本文主要的内容我们学习了数据库中最重要的CRUD的CREATE和RETRIEW部分下面小编会讲解CRUD中剩下的两种操作以及数据库特殊的约束有不明白的地方可以留言小编会回复希望读者们多提建议小编会改正共同进步谢谢大家。