MySQL基础-02查询

最新推荐文章于 2024-06-02 14:54:06 发布

ficklee_

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量68

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_57316910/article/details/130351447

版权

MySQL基础-02查询

简单查询

查询一个字段

select 字段名 from 表名

mysql> select name from ev_article_cate;
+------+
| name |
+------+
| 历史 |
| 数学 |
| 科学 |
+------+
3 rows in set (0.00 sec)

注意：SQL语句不区分大小写

mysql> SELECT NAME FROM EV_ARTICLE_CATE;
+------+
| NAME |
+------+
| 历史 |
| 数学 |
| 科学 |
+------+
3 rows in set (0.00 sec)

查询多个字段

select 字段名1,字段名2 from 表名

mysql> select id,name from ev_article_cate;
+----+------+
| id | name |
+----+------+
|  2 | 历史 |
|  3 | 数学 |
|  1 | 科学 |
+----+------+
3 rows in set (0.00 sec)

mysql> select name,id from ev_article_cate;
+------+----+
| name | id |
+------+----+
| 历史 |  2 |
| 数学 |  3 |
| 科学 |  1 |
+------+----+
3 rows in set (0.00 sec)

语句中字段名的先后也决定了查询返回结果中字段的先后顺序

查询所有字段

# 第一种
select a,b,c.... from tablename;
# 第二种
select * from tablename;

第二种方式的效率低、可读性差，实际开发中不建议使用

给查询的列取别名

通过as关键字实现

select 字段原名 as 字段别名 from tablename;

mysql> select id as cate_id,name as cate_name from ev_article_cate;
+---------+-----------+
| cate_id | cate_name |
+---------+-----------+
|       2 | 历史      |
|       3 | 数学      |
|       1 | 科学      |
+---------+-----------+
3 rows in set (0.00 sec)

注意：1.只是将显示的查询列名进行更改，并没有对原表的字段进行更改。select语句永远都不会进行修改操作，只负责查询

2.as可以用空格来代替，但是别名里面不能有空格，可以用下划线_来分隔，如上例，或者是将别名用单引号or双引号包裹（最好用单引号）。

mysql> select id cate_id,name cate_name from ev_article_cate;
+---------+-----------+
| cate_id | cate_name |
+---------+-----------+
|       2 | 历史      |
|       3 | 数学      |
|       1 | 科学      |
+---------+-----------+
3 rows in set (0.00 sec)

列也可以参与数学运算

select salary*12 as year_salary from tablename;

结果去重

distinct把查询结果去除重复记录（原表数据不会被修改）

select distinct 字段名1（，字段名2） from ...

distinct是作用于所有列出的字段

限制结果

limit限制返回结果的条数不超过规定的值

select ... 
from ...
------------
limit n; # 不多于n行
limit m,n; # 从第m行开始检索n行，如果剩余的行数不足n行，就只返回剩余的
limit n offset m # 和上面的含义相同

条件查询

不是简单地将表中所有的数据都查询出来，而是只查询出符合我们给定条件的数据

select 字段1,字段2... from tablename where 条件;

条件一般有哪些？

= 等于
<> 、 != 不等于
<、>、<=、>=
between … and … 两个值之间（左闭右闭，左小右大）
is (not) null 是否为null

数据库中null不能使用等号来进行衡量，必须使用is (not) null
and 相当于编程语言里的&&
or 相当于编程语言里的||

and 的优先级比 or 高
(not) in 是否在集合中
相当于多个or，in后面跟的不是一个区间，而是一个‘集合’，里面是具体的值
```
select name,salary from table where salary in(800,5000)
# 查询工资是800或者5000的员工姓名、工资
```

like 模糊匹配

%匹配任意多个字符，_匹配任意一个字符

select * from table where name like '%B%';
# 查询名字里含有B的
'%T' 以T结尾的
'D%' 以D开头的
'_A%' 第二个字母是A的
# 转义用\

regexp 正则匹配，与like类似

MySQL中能使用少部分的正则表达式，regexp匹配字串

排序

select 
	* 
from 
	table 
where 
	条件 
order by 
	字段 asc(desc); # 默认是升序
	# 下面是不建议的一种写法
	2 # 以第二列来排序，健壮性较差

多个字段排序

用逗号分隔，依次排序，如果有多个字段都要降序排列，则每个需要降序排列的字段后面都要加desc，否则依旧默认为升序排列。

函数

单行处理函数

特点：一个输入对应一个输出

与此对应的：多行处理函数----->多个输入对应一个输出

常见函数

lower 转换小写
upper 转换大写
substr 取子串

格式：substr(字符串，起始下标，截取的长度)
length 取长度
trim 去掉字符串左右两边的空格

Rtrim()删除数据右侧多余的空格

Ltrim()删除数据左侧多余的空格
concat 字符串拼接

格式：concat(字段1,字段2,…)
round 四舍五入
rand 生成随机数
ifnull 空处理函数

注意：只要有NULL参与的数学运算，最终结果就是NULL，为了避免这个现象，引入ifnull函数）

用法：ifnull(x,0)如果x是NULL，把x当作0处理。

时间函数

Date() 返回日期格式

MySQL使用的日期格式必须为yyyy-mm-dd（4位数表示年份更可靠）
Time() 返回时间格式如00:00:00

其他常用：

请添加图片描述

数值处理函数

请添加图片描述

聚集函数（分组函数）（多行处理函数）

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有以下几种：

确定表中行数（或满足某个条件…）
获取表中行组的和
找出最大值、最小值和平均值

count 计数

sum 求和

avg 平均值

max 最大值

min 最小值

注意：

在使用分组函数前必须先分组，否则默认整张表为一组
分组函数自动忽略NULL，不需要对NULL提前处理
count(*) 和count(某个具体字段)的区别

count(某个具体字段)：统计该字段下所有不为NULL的元素总数

count(*)：统计表中的总行数（因为不存在一整行数据全为NULL的情况）
分组函数不能直接使用在where语句中

见下述执行顺序，where语句执行时还未分组，故分组函数不能执行

分组查询

顺序

1.from 2.where 3. group by 4.having 5.select 6.order by

select ... 
from ... 
where ... 
group by ...
having ...
order by ...

注意事项

group by 后面可以跟多个字段
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
在一条select语句中如果有group by的话，select后面只能跟参与分组的字段以及分组函数
分组后还想要条件筛选的话用having

优化策略：where和having优先选择where，where实在不行的时候再用having

除了效率之外，having和where唯一的区别在于having过滤分组，where过滤行

rollup

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，可以理解为加了一行分组前的值

mysql> select vend_id,count(*) from products group by vend_id with rollup;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
|    NULL |       14 |
+---------+----------+
5 rows in set (0.00 sec)

子查询

嵌套在其他查询中的查询，即一条查询语句中包含了不止一次select

通过select出的数据作为条件来再一次select

举个例子：想要检索出订购了TNT2的所有客户的信息

但是我们并不能从一张表中得到TNT2与顾客的直接关系

二者的关系连接为：物品（TNT2）<—> 订单编号 <—> 客户ID <—> 客户其他信息

不适用子查询的方法是通过三条sql语句（下面举出物品（TNT2）<—> 订单编号 <—> 客户ID这一条线）

mysql> select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
2 rows in set (0.00 sec)

mysql> select cust_id from orders where order_num in (20005,20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)

上面两条sql语句就等价于下面这条子查询：

mysql> select cust_id from orders where order_num in (select order_num from orderitems where prod_id='TNT2');
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)

tips：包含子查询的SELECT语句难以阅读和调试，特别是它们较为复杂时更是如此。建议把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。

在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询

子查询最常见的使用是在WHERE子句的IN操作符中，以及用来填充计算列

子查询嵌套的位置可以是select、from和where的后面

连接查询

前文的子查询用多个select语句实现了跨表查询，那么有没有一种方式只用一条select语句就能实现呢？那就要引出连接查询：多张表联合起来查询数据的跨表查询

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果的条数=两张表条数的乘积，这种现象是笛卡尔积现象

语法格式有两种：

# sql92
select ...
from table1,table2...
(where condition)

缺点

结构不清晰
表的连接条件，和后期进一步筛选的条件，都放到了where后面

# sql99
select ...
from table1
join table2
(on condition)
(join ... on ...)

()里的内容代表可以省略不写，即没有任何条件限制

分类（根据表连接的方式）

内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接（左连接）
- 右外连接（右连接）
全连接

内连接

等值连接

select ...
from t1
inner join t2
on t1.x = t2.y

inner join表示内连接，使用时 inner 可以省略不写
为了规范，虽然使用where能达到相同的效果，但是推荐使用 inner join
MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

学了内连接后我们可以发现此前子查询中的例子，也可以用内连接来处理。

为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法

前面我们讲了可以给列取别名，同样的我们也可以给表取别名，语法和列是一致的，一般用空格隔开

在连接查询中，因为涉及多个表，最好是给表也取个别名

缩短SQL语句
允许在单条select语句中多次使用相同的表

非等值连接

和等值连接类似，只不过是连接条件不是一个等量关系，常见的是 between and等结构

自连接

顾名思义，自己和自己进行连接

什么情况下会用到自连接呢，打个比方，一张表里存储了学生id与班级id的关系，现在我们知道了一名学生的id，我想知道ta的同班同学有哪些？

select t2.sid,t1.cid
from table as t1,table as t2
where t1.sid = 给定值 and t1.cid =t2.cid

而且我们需要注意的是，自连接相当于是将一张表复制了一份进行操作，所以我们必须要取别名以区分这两张一模一样的表，否则就会有二义性，MySQL会报错

自连接通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是

相同的，但有时候处理连接远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然连接

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次

这个工作由你自己完成。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成。

外连接

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行

从上述描述可以推测出：同样两张表连接，外连接返回的数据条数是大于等于内连接的

select ...
from table1 (left/right) outer join table2
on condition

同样地，outer可以省略不写
左右连接是类似的，左连接就是以左边的表为主表，在左表中选择所有的行，右连接同理
MySQL不支持左右连接的简写形式 *= 和 =*
左右连接可以相互转换

tips：

在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单

组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询。

union 类似于使用多个where语句，union在多个查询语句中设定不同的筛选条件，最后再把它们的结果并（union）起来

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）
union 能对结果自动去重，但是如果您不希望它自动去重（显示所有的结果，哪怕相同）的话，可以使用 union all 来代替 union。在这一点上，这是where所不能做到的
对于组合查询中的order by语句必须出现在最后一条select语句之后，并且不能使用多条order by语句

全文本搜索

解决的限制

此前介绍了like关键字和正则表达式这两种搜索机制，但其仍存在一些限制

性能

通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时
明确控制

使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配
智能化的结果

虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行

而以上的限制都可以通过全文本搜索来解决：

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等

全文本搜索在create table时开启，定义一个FULLTEX()索引
在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。
```
select ...
from ...
where match(列) against(匹配词);
```
除非使用binary，否则不区分大小写
全文本搜索匹配返回以文本匹配的良好程度排序的数据，且速度相当快

查询扩展功能

我们搜索a的时候，不止想搜索a，还想搜索含a的数据中与a相关的b，此时就需要全文本搜索来发挥作用

操作步骤：

首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。
再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

select ...
from ...
where match(列) against(匹配词 with query expansion);

查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目

布尔文本搜索

这种方式可以提供更多细节：

要匹配的词
要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）
排列提示（指定某些词比其他词更重要，更重要的词等级更高）
表达式分组
else

布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。

select ...
from ...
where match(列) against('匹配词 -不想匹配的词' in boolean mode);

请添加图片描述

使用说明

在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
忽略词中的单引号。例如，don’t索引为dont。
不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
仅在MyISAM数据库引擎中支持全文本搜索。