当前位置：首页 > news >正文

MySQL基础（三）

news 2024/11/3 0:59:53

一. 插入内容insert

1.1 默认插入

1.2 指定某些列插入数据

1.3 一次插入多行

1.4 insert 插入时间

二. 查询数据select（比较复杂）

2.1 全列查询

2.2 指定列查询

2.3 查询字段为表达式

2.4 别名 as

2.5 去重查询 distinct

2.6 排序查询 order by

2.7 条件查询 where

2.8 分页查询 limit

一. 插入内容insert

tips：

（一）SQL中表示字符串，可以用 ' 也可以用 "

C/C++、Java中，' 表示字符，" 表示字符串
SQL/Python/JS，没有字符类型，只有字符串，' 和 " 都可以用。

（二）在控制台中，通过向上方向键可以切换输入的命令

（三）注：如果要输入汉字，需确保创建数据库的时候，指定了字符集是 utf8 / gbk 等能支持中文的字符集，否则中文数据会出现报错。

（四）查看表的内容：select * from 表名;

1.1 默认插入

insert into 表名(值, 值, 值......);：列的个数和类型要和表的结构匹配

要点一：输入的数据类型必须要与参数列表一致

要点二：隐式转换问题

SQL中的类型系统，和Java差异很大。上述插入的数据，虽然 values 后面第一列写的是字符串类型，SQL 会尝试自动转成int，‘100’ --> 100；第二列写的是整数类型，会尝试自动转成int，200-->'200' 。
上述这样的转换，就是 “隐式类型转换”，对于Java来说是非常排斥的。比较支持隐式类型转换的称为 “弱类型系统”，不太支持隐式类型转换的称为 “强类型系统”。（注意，此处并不绝对，Java是“强类型系统”，但也存在自动装箱、自动拆箱的隐式操作，而 C / SQL 隐式类型转换触发的情况会多一些）

强类型和弱类型，一般认为强类型要更好，检查比较严格，问题可以提前发现解决；弱类型属于之前的探索尝试。

C++属于强类型还是弱类型存在争议，C++兼容C的隐式转换类型，但在现代的语法标准中，有对于类型强度有了一定的加强了。

除此之外，还有两个相关类型：

动态类型：程序运行过程中，变量的类型是可以改变的（Python , JS , Ruby , PHP....）。优势：非常灵活，表达能力更强；缺点：程序在运行之前，难以做很丰富的检查，很多问题要执行到对应的代码才能发现。
静态类型：程序运行过程中，变量的类型不能改变（C/C++ , Java....）

动态/静态具体哪个好，要结合实际情况。有的场景认为动态类型好（比如初创公司想尽快开发出产品原型，非常适合动态类型），有的场景认为静态类型更好（比如产品具有一定规模，代码和业务也越来越复杂，非常适合静态类型）

（失败的原因是 SQL 把 lisi 尝试转换成 int 失败了。）

1.2 指定某些列插入数据

insert into 表名(列名, 列名, .....) values (值, 值, .....); ：值的个数、类型、顺序等要和 ( ) 中的列名相匹配。

（NULL 在 SQL 中，表示“空”，这个单元格啥都没写）

（列名可以交换顺序，但是要对应）

1.3 一次插入多行

insert into 表名 values (值, 值, ....), (值, 值, ...), (值, 值, ...); ：每个括号对应一行

（也可以指定列，一次插入多行）

插入 3 条记录：
（1）一次插入一行

（2）一次插入 3 行

MySQL 是一个 “客户端 - 服务器” 结构的程序，数据库通常运行在服务器上，服务器提供必要的硬件资源（如CPU、内存、硬盘）来存储数据库文件，并通过数据库管理系统来管理这些数据。客户端-服务器交互过程中，交互次数越多，整体开销就越大，花的时间就越长。一个sql包含数据的多少，不是主要矛盾，除非数据差异太大了，可能有明显影响；如果只是差了几条，区别是不大的。

1.4 insert 插入时间

有的时候，插入的时间日期，希望就是 “当前时刻”，SQL 作为一个编程语言，也支持一些库函数。

now() --> 获取当前的日期时间

二. 查询数据select（比较复杂）

所有 select 操作都只是针对查询结果，得到“临时表”，做出计算 / 调整，不会影响到硬盘上的原始数据。

2.1 全列查询

select * from 表名; ：查询出这个表中的所有的行和所有的列（ * 称为 “通配符”，可以指代所有的列。）

select * 是一个很危险的操作

如果表比较小，select * 都无所谓
一旦表非常的大，千万 / 亿级别的数据量，此时，进行 select 就会产生大量的硬盘IO 和网络IO

再次强调，MySQL 是一个 “客户端 - 服务器” 结构的程序，数据库通常运行在服务器上，服务器提供必要的硬件资源（如CPU、内存、硬盘）来存储数据库文件，并通过数据库管理系统来管理这些数据。硬盘和网卡，读写速度都是存在上限的。一旦触发大规模的 select * ，意味着很可能就把你的硬盘/网卡带宽吃满了（堵车），其他的客户端尝试访问数据库，访问操作就无法正常进行了。（如果针对 公司生产环境 进行select * 操作，就很有可能使其他的用户访问数据库的时候出现访问失败的情况）

硬盘本身，存储空间是越来越便宜了，但是硬盘的带宽（访问速度），并没有特别的质变，尤其是机械硬盘，速度很多年都没有提升了。网络带宽往往是比较稀缺的资源，比硬盘带宽还贵（计算机中最贵的资源）

拓展常识——网络带宽：

网络带宽是指在单位时间内网络能够传输的数据量。它通常用来衡量网络连接的最大传输能力，以比特每秒（bps）为单位来表示。带宽的概念可以类比于高速公路的车道数，车道越多，通行能力越强。在网络中，带宽越大，理论上能够传输的数据量也就越多。

带宽与网速是两个相关但不同的概念。带宽是网络的最大传输能力，而网速是实际的数据传输速率，这个速率会受到网络拥堵、硬件性能等因素的影响。例如，一个100Mbps的带宽意味着理论上每秒可以传输100兆比特的数据，但实际网速可能会因为网络高峰时段的拥堵而降低。

上行带宽：

指的是从个人或企业的网络设备（如计算机、手机等）发送数据到互联网或其他网络的能力。
它决定了你可以多快地将数据上传到服务器或与其他网络用户共享信息。
在家庭网络（“共享带宽”）中，上行带宽通常比下行带宽要小，因为用户通常下载的数据比上传的数据多。

下行带宽：

指的是从互联网或其他网络接收数据到个人或企业的网络设备的能力。
它决定了你可以多快地从互联网下载文件、观看视频或浏览网页。
在大多数宽带服务中，下行带宽通常比上行带宽要大，以适应用户大量下载内容的需求。

如果你有一个提供100Mbps（兆比特每秒）下行带宽和10Mbps上行带宽的互联网连接，那么理论上，你可以在最理想的情况下每秒下载100兆比特的数据，而上传的速度则为每秒10兆比特。

2.2 指定列查询

select 列名, 列名.... from 表名; ：查询的时候手动指定列表，得到的结果就是和列名关联的。

实际开发中，一个表有十几列，甚至几十列都是很有可能的。比如当前只需要关注两列，一共有20列，使用指定列查询，得到的数据量就比全列查询要少很多。

2.3 查询字段为表达式

select 表达式 from 表名;：基于列名，进行加减乘除运算（不会修改服务器硬盘上的数据，只是把返回的结果进行了计算）

如果+20，意味着有些成绩的结果就会超出约定的类型范围 decimal(3,1)，为什么不会报错呢？—— 表的类型，是针对硬盘上存储的数据进行的制约，但是当前是查询操作，此处计算的结果，是“临时表”的数据，不会影响到硬盘上的数据。

在一个表达式中，还可以引入多个列参与运算：
（逐行遍历表中的数据，针对每一行，把这三个列进行相加即可。）

表达式查询，只能针对列和列之间进行计算，行和行之间的运算，后面会介绍到 “聚合查询”。

2.4 别名 as

select 表达式 as 别名 from 表名;：在表达式查询中，如果表达式简单，一眼就能看懂；如果表达式比较复杂，就需要通过给表达式取别名，方便观察。

（虽然 as 可以省略，但是不建议省略，以防看错）

2.5 去重查询 distinct

select distinct 列名 from 表名; ：去重的意思，多个行的数据如果出现相同的值，就只会保留一份。如果只选中一列，直接触发去重；如果是多列，必须指定的每一列都要重复，才能触发去重。

（这里的去重都是针对 “临时表”，硬盘上的数据没有任何影响）

2.6 排序查询 order by

select * from 表名 order by 列名; （默认为升序）：针对查询结果进行排序（排序也是针对临时表进行排序，不会对硬盘上的数据造成影响。）查询的时候，指定按照某个列进行排序，也可以指定升序还是降序。

注意：

数据库不会对于查询得到的结果集的顺序做出任何承诺，除非 SQL 中包含 order by（如果不写 order by，得到的结果的顺序，是不可预期的.... 写代码就不能依赖这样的顺序）

（进行排序的时候，select 后面写啥，对于最终排序结果都没有影响）

降序排序，在 order by 列名后面写上 desc 关键字。（此处的 desc 不是 describe 的缩写，而是 descent（降序）的缩写。）

（如果数学成绩相同，排序顺序都是不可预期的）

显示的升序排序，在 order by 列名后面写上 asc 关键字（可省略）。

order by 可以指定多个列来排序，多个列之间，使用 , 进行分割：先按照第一列来排序，如果第一列相同，再按照第二列排序.....（如果第一列均不相同，那后面的列都用不上）

2.7 条件查询 where

select * / 列名 / 表达式 / 去重 from 表名 where 条件;：查询过程中，指定筛选条件，满足条件的记录就保留，不满足条件的就跳过。

举例：

（遍历表的每个记录(每一行)，把每一行的数据带入到条件中。如果条件成立，这个记录就添加到结果集合中；如果不成立，就直接跳过。）

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL=NULL 的结果是 NULL，相当于FALSE(0)
<=>	等于， NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
between a0 and a1	范围匹配，[a0,a1]，如果 a0<= value <=a1，返回 TRUE(1)
IN (option, ......)	如果是option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配。%表示任意多个（包括0个）任意字符；_表示任意一个字符

（一）SQL 中，null 是一个特殊值，null 参与运算（加减乘除...）得到的结果还是 null。

（二）空值和空值之间是否认为是 “相同的”，可能存在差别。使用 = 比较 null 是否相等是不安全的，因此不要直接使用 = 和 null 比较。

解决办法：

使用 <=> 还可以比较两个列都是null的情况，但是 is null 就不可以了。

（三）where后面的条件语句中不可以使用别名

（显示 total 不在表中）

根据sql的执行过程进行解释：

遍历表，取出每一行
把当前行，带入到 where 条件，根据条件的真假，决定这一行是否要查询出来
再根据 select 后面的列名 / 表达式，进行选择列/进行计算
order by 进行排序

因此在执行第二步的时候，就会出现错误。

（四）between a0 and a1 的范围是 [ a0, a1 ]（闭区间）

编程中，大部分谈到的 “区间” 都是 “前闭后开” 区间[ a, b )，像Java标准库，C++标准库，Python标准库，大部分的设定都是“前闭后开”；但是少数的情况下，是有闭区间的，像 SQL 的between and ，Redis 针对列表取子区间，Linux shell 取子区间......前闭后开最大的好处，就是通过 b - a 得到区间的长度，就不用思考 +1 -1 这样的问题了。

（五）

（六） like 模糊匹配，不要求完全相等，只要满足一定条件就可以了

此处还是需要搭配通配符，描述这样的条件：

%：匹配 0 个或者任意个任意的字符
_：匹配 1 个特定的字符

例如：
查询姓名中 “孙” 开头的记录

查询姓名中 “孙” 结尾的记录

查询姓名中包含 “孙” 的记录

查询姓名中姓“孙”且两个字的

MySQL 中自带的模糊匹配功能相对比较弱，实际开发中，会使用到更强大的字符串匹配工具（正则表达式）比如，Java中String的方法split( "-" )，括号中也可以是正则表达式。

正则表达式是一种强大的文本处理工具，它使用单个字符串来描述、匹配一系列符合某个句法规则的字符串。正则表达式常用于字符串的搜索、替换、分割和校验等操作。在MySQL中，like不能支持正则的，只能简单使用 % 或者 _ 来匹配，但是可以使用其他语言间接操作SQL。对于数据库来说，like 模糊查询是一个比较低效的操作，实际开发中，使用 like就要节制一些（数据库本身就慢，再频繁使用like这种低效操作是不建议的）

逻辑运算符：

运算符	说明
AND	多个条件必须都为TRUE(1)，结果才是TRUE(1)
OR	任意一个条件为TRUE(1),结果为TRUE(1)
NOT	条件为TRUE(1)，结果为FALSE(O)